数据类型:
字符型数据
VARCHAR2:可变长度的字符数据,使用数据库字符集存储;通常设置该字符类型时会指定相应的长度,例如varchar2(100),这里需要注意的是后面的100表示100字节,即100byte,这在保存数字或者英文时没有什么区别,但是保存汉字时,如果数据库字符集是GBK,那么该列可以保存100/2=50个汉字,如果是UTF-8,该列可以保存100/3=33个汉字。数据库字符集是数据库创建时设置的,通常不能更改。
NVARCHAR2:这个和VARCHAR2一样,区别在于该类型使用国家语言字符集存贮;国家语言字符集可以认为是为oracle选择的附加字符集,这通常是为了增强Oracle字符处理能力;通常使用这种字符集存储的数据类型使用频率不高。
CHAR:和VARCHAR2的区别在于是定长类型,同样保存在数据库字符集中,如果保存的数据长度没有达到设定的长度,则会使用空格填充。
数字型数据
NUMBER:数字数据,可以指定它的精度和小数位数;通常格式NUMBER ( precision, scale) ,precision表示精度(1-38),指的有效数字的位数,scale表示小数位数(-84-127)。
FLOAT:一个ANSI数据类型,精度为126位二进制、38位十进制的浮点数。另外Oracle还提供BINARY_FLOAT和BINARY_DOUBLE作为备选。
INTEGER:存储整型数据,小数位数为0,相当于NUMBER(P,0)。
日期型数据
DATE:存储日期类型,可以精确到秒,通常存储格式是年月日时分秒;常用。
TIMESTAMP:和DATE相比,这个可以精确到纳秒,存储时间精度更高;常用。
TIMESTAMP WITH TIMEZONE:和TIMESTAMP一样,只不过可以在设置时候指定时区。
INTERVAL YEAR TO MONTH:记录两个DATE或TIMESTAMP之间的以年和月的时间间隔。
INTERVAL DAY TO SECOND:记录两个DATE或TIMESTAMP之间的以天和秒的时间间隔。
大对象数据类型
CLOB:保存在数据库字符集中的字符数据,大小没有限制;
NCLOB:作用和CLOB一样,但是保存在国家语言字符集中;
RAW和ROWID数据类型
RAW:可变长度的二进制数据;
ROWID:指向表中行的位置的指针,他里面是物理地址,是Oracle专有数据类型。
表的创建:
create table student(
id number,
name varchar2(64),
sex char(2),
birthday date,
score number(4,2),
resume clob
);
数据表的修改
添加列:
alter table 表名 add(新的列名 列的类型);
alter table 表名 add(新的列名 列的类型,新的列名2 列的类型2);
删除列:
alter table 表名 drop column(列名);
alter table 表名 drop (列名,列名2);--删除多列不能添加column
修改字段的名称:
alter table 表名 rename column 旧列名 to 新列名;
修改字段的类型:
alter table 表名 modify(列名 列的新类型);
alter table 表名 modify(列名 列的新类型,列名2 列2的新类型);
修改表名
rename 旧表名to 新表名;
删除表
drop table 表名;
INSERT
插入的数据应与字段数据类型相同。
数据的大小应该在规定范围之内。
在values中列出的数据位置必须与被加入的列的排列位置相对应。
字符和日期型数据应包含在单引号中。
插入空值,不指定’’或给null
insert into stu values(1,'ouyang','男');
insert into stu(id) values(1);
insert into stu values(1,null,’’);
UPDATE
update 表名 set 列名=表达式 {+} where 条件
update语法可以使用新值更新原有表行中的各列。
set子句指示要修改哪些列和给与哪些值。
where子句指定更新哪些行。如没有where子句,则更新所有的行(特别小心)。
DELETE
delete from 表名where 条件
truncate table 表名;删除所有记录,不写日志,速度快,无法恢复。
COMMIT和ROLLBACK
commit 提交
rollback 回滚
比较运算符
> 大于
< 小于
>= 大于等于
<= 小于等于
= 等于
!= 不等于
<>不等于
SELECT
select * from表名 where 条件select 列名 from 表名 where 条件
select 指定查询哪些列的数据。
column指定列名
使用scott用户下的三张表:dept,emp,salgrade进行查询演示。
基本查询语句 select [distinct] *|[column 1,column 2 ……] from表名 [where (条件)]
示例:
查询所有列。 select * from emp;
查询指定列。 select distinct job from emp;
不显示重复列。 select distinct job from emp;
查询中使用算术表达式 select sal*12 from emp;
查询中使用函数 select sal*12+nvl(comm,0) from emp;
说明: NVL(E1, E2)的功能为:如果E1为NULL,则函数返回E2,否则返回E1本身。
拼接字符串select ename||'是一个'||job from emp;
说明:在查询时,如果希望将多列拼接起来作为一个字符串可以使用||
where子句的用法
查询工资高于3000的员工
select * from emp where sal>3000;
查询1982年1月1日之后入职的员
select * from emp where hiredate>to_date('19820101','yyyymmdd');
显示工资在2000和2500之间的员工信息
select * from emp where sal>=2000 and sal<=2500;
between and 在两者之间
select * from emp where sal between 2000 and 2500;--闭区间
select * from emp where sal not between 2000 and 2500;
in -not in
select * from emp where ename in ('ALLEN','WARD','MARTIN');
select * from emp where ename not in ('ALLEN','WARD','MARTIN');
使用like模糊查询
%表示0到多个字符
_表示一个字符
select * from emp where ename like '%ITH';
select * from emp where ename like '_ITH'; --没有数据
select * from emp where ename like '_MITH';
select * from emp where ename not like '%LEN'; --不常用
is null的用法
显示没有上级的员工
select * from emp where mgr is null;
使用逻辑运算符
select * from emp where mgr is null or comm is null;
select * from emp where mgr is null and comm is null;
增加排序功能order by
select [distinct] *|[column 1,column 2 ……] from表名 [where (条件) order by 列名 ]
select * from emp order by sal;--默认升序
select * from emp order by sal asc;--升序
select * from emp order by sal desc;--降序
select * from emp where job = 'CLERK' order by sal desc;--降序
select * from emp where job = 'CLERK' order by empno,sal desc;--按照员工号升序工资降序
select * from emp order by 8;--使用数字排序 按照表列的顺序
select empno,ename,deptno from emp order by 3;--使用数字排序 按照查询列的顺序
别名排序
select sal*12+nvl(comm,0) yearSal from emp order by yearsal;
注意:
oracle 关键字,列名,表名等都不区分大小写,但是表中的数据是区分大小写的。
扩展:
select * from user_tables; 查询用户下的所有表
select * from dba_users;--查询所有用户信息,使用管理员用户
DML,DDL,DCL
DML(data manipulation language):
它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
DDL(data definition language):
DDL比DML要多,主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用
DCL(Data Control Language):
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
统计函数
max,min,sum,avg,count
查询员工数量
select count(*) from emp;
查询有奖金的人
select count(comm) from emp; --空值不统计
查询最高工资。
select max(sal) from emp;
查询最低工资。
select min(sal) from emp;
查询最高年薪。
select max((sal+nvl(comm,0))*12) from emp;
查询平均工资。
select avg(sal) from emp;
select avg(comm) from emp;--奖金不一定有,没有的不参与
查询总工资。
select sum(sal) from emp;
查询真正的平均工资
select sum(sal)/count(*) from emp;
group by 和 having子句
group by 列名 用于分组
having 表达式 针对于一组数据,譬如求
两者一般结合使用,单独使用也可以。
按照部门分组查询部门平均工资和最高工资
select avg(sal),max(sal),deptno from emp group by deptno;
select avg(sal),max(sal),deptno from emp;--错误 不是单组分组函数
查询部门不同岗位的平均工资和最高工资
select avg(sal),max(sal),deptno,job from emp group by deptno,job;
查询部门不同岗位的平均工资和最高工资并按照部门和岗位排序。
select avg(sal),max(sal),deptno from emp group by deptno,job order by deptno,job;
查询部门平均工资低于2000的部门的平均工资和最高工资。【典型用法】
select avg(sal) ,max(sal),deptno from emp group by deptno having avg(sal)<2000;
select avg(sal) ,max(sal),deptno from emp where avg(sal)<2000 group by deptno; --错误,此处不允许使用分组函数
单独使用having
select avg(sal) ,max(sal),deptno from emp group by deptno having 1>2;--扩展 特殊形式
为什么不能使用having替代where
select * from emp having avg(sal)>20;--不是group表达式
当同时使用group by having order by 的顺序问题
推荐使用顺序 group by having order by
查询部门工资低于2000的部门不同岗位的平均工资和最高工资并按照部门和岗位排序。
select avg(sal),max(sal),deptno from emp group by deptno,job having avg(sal)<2000 order by deptno,job;--推荐写法
select avg(sal),max(sal),deptno from emp having avg(sal)<2000 group by deptno,job order by deptno,job;--不推荐写法
select avg(sal),max(sal),deptno from emp order by deptno,job having avg(sal)<2000 group by deptno,job ;--错误写法
其他常用语句
select * from all_tables where owner = 'SCOTT';--查询scott用户下的所有表
select * from user_tables;--查询当前用户的所有表