Oracle表管理
oracle表的管理(数据类型,表结构创建,修改和删除,表数据CRUD操作)
表名和列的命名规则
必须以字母开头
长度不能超过30个字符
不能使用oracle的保留字
只能使用如下字符 A-Z,a-z,0-9,$,#等
oracle支持的数据类型
分类 | 数据类型 | 说明 |
文本、二进制类型 | char(size) char(20) | 定长 最大2000字符 |
varchar(size) varchar(20) | 变长 最大4000字符 | |
nchar(n) | Unicode数据类型 ,定长 最大2000字符 | |
nvarchar2(n) | Unicode数据类型 ,变长 最大4000字符 | |
clob(character large object) | 字符型大对象 ,最大4G
| |
blob(binary large object) | 二进制大对象, 可以存放图片/声音,最大4GB | |
数值类型
| number(p,s)
| p为整数位,s为小数位.范围: 1 <= p <=38, -84 <= s <= 127 保存数据范围:-1.0e-130 <= number value < 1.0e+126 保存在机器内部的范围: 1 ~ 22 bytes |
时间日期 | date | 包含年月日,时分秒。默认格式:DD-MON-YYYY。从公元前4712年1月1日到公元4712年12月31日的所有合法日期 n的取值为0~9.表示指定TIMESTAMP中秒的小数位数。N为可选。如果n为0,timestamp与date等价[不推荐] |
TIMESTAMP(n) |
字符类
char 定长 最大 2000个字符。例子: char(10) ‘switch’ 前5个字符放 ‘switch’ ,后添5个空格补全。
varchar2(20) 变长 最大 4000个字符。
例子: varchar2 ( 10 ) ‘switch’ oracle 分配5个字符。
clob(character large object) 字符型大对象 最大 4G
char 查询的速度极快浪费空间,查询比较多的数据用。
varchar 节省空间
二进制数据
blob 可以存放图片/声音 最大4G
一般来讲,在真实项目中是不会把图片和声音真的往数据库里存放,一般存放图片、视频的路径,如果安全需要比较高的话,则放入数据库。
数字型
number 范围 -10 的 38 次方 到 10 的 38 次方可以表示整数,也可以表示小数
number(5,2)
表示一位小数有 5 位有效数, 2 位小数
范围: -999.99 到 999.99
number(5)
表示一个 5 位整数
范围 99999 到 -99999
日期类型
date 包含年月日和时分秒 oracle 默认格式 1-1 月 -1999timestamp 这是 oracle9i 对 date 数据类型的扩展。可以精确到毫秒。
表结构创建,修改和删除
创建表
--学生表
create table student ( ---表名
xh number(4), --学号
xm varchar2(20), --姓名
sex char(2), --性别
birthday date, --出生日期
sal number(7,2) --奖学金
);
--班级表
CREATE TABLE class(
classId NUMBER(2),
cName VARCHAR2(40)
);
修改表
添加一个字段sql> alter table 表名 add (字段名 字段类型);
SQL>ALTER TABLE student add (classId NUMBER(2));
修改一个字段的长度
sql> alter table 表名 modify (字段名 字段类型);
SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30));
修改字段的类型/或是名字(不能有数据) 不建议做
sql>alter table 表名 modify (字段名 字段类型);
SQL>ALTER TABLE student modify (xm CHAR(30));
删除一个字段 不建议做(删了之后,顺序就变了。加就没问题,因为是加在最后面)
sql>alter table 表名 drop column 字段名;
SQL>ALTER TABLE student DROP COLUMN sal;
修改表的名字 很少有这种需求
sql>rename 原来表名 to 新表名;
SQL>RENAME student TO stu;
删除表
sql>drop table 表名;
SQL>DROP TABLE student;
表数据CRUD(create retrieve update delete)操作
添加数据
INSERT INTO table [(column [, column...])]
VALUES(value [, value...]);
所有字段都插入数据
INSERT INTO student VALUES ('A001', '张三', '男', '01-5月-05', 10);
oracle中默认的日期格式‘dd-mon-yy’
dd日子(天) mon 月份 yy 2位的年
例如:‘09-6月-99’ 是 1999年6月9日
修改日期的默认格式(临时修改,数据库重启后仍为默认;如要修改需要修改注册表)
ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd';
修改后,可以用我们熟悉的格式添加日期类型:
INSERT INTO student VALUES ('A002', 'MIKE', '男', '1905-05-06', 10);
插入部分字段
INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女');
插入空值
INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null);
修改数据
UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行。
修改一个字段
UPDATE student SET sex = '女' WHERE xh = 'A001';
修改多个字段
UPDATE student SET sex = '男', birthday = '1984-04-01' WHERE xh = 'A001';
修改含有null值的数据
SELECT * FROM student WHERE birthday IS null;
删除数据
delete from tbl_name
[WHERE where_definition]
DELETE FROM student;
删除所有记录,表结构还在,写日志,可以恢复的,速度慢。
Delete 的数据可以恢复。
savepoint a; --创建保存点
DELETE FROM student;
rollback to a; --恢复到保存点
一个有经验的DBA,在确保完成无误的情况下要定期创建还原点。
DROP TABLE student; --删除表的结构和数据;
delete from student WHERE xh = 'A001'; --删除一条记录;
truncate TABLE student; --删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快。
查询数据
查询所用的表是Oracle自带的用户Scott下的几张表(emp,dept,salgrade),如果用的是11g及以上要先解锁scott用户。
SELECT [DISTINCT] *|{column1, column2. column3..}
FROM table [where condition] [group by column1[,column2...] [having condition]] [order by column1[,column2...] ASC |[DESC]];
Select 指定查询哪些列的数据。
column指定列名。
*号代表查询所有列。
From指定查询哪张表。
DISTINCT可选,指显示结果时,是否剔除重复数据。
where代表查询要满足什么条件。
group by表示按什么分组,PS:如果在不用子查询的情况下,想在select中显示某列,则必须要放在group by 后。
having 表示分组后要满足什么条件。
order by 表示按什么分组,默认ASC升序,可以使用DECS降序。
查看表结构
sql>desc 表名;
PS:非SQL语句,但是却是Oracle一个好用的表述表结构的命令。
简单查询
--查询所有员工
select * from emp;
使用算术表达式 nvl null
--查询所有员工的年工资
select sal * 13 + nvl(comm * 13, 0) "年工资", ename from emp;
--查询工资高于3000的员工
select * from emp where sal > 3000;
--查询1982年1月1号以后入职的员工
select * from emp where hiredate > '1-1月-1982';
--显示工资在2000-2500的员工
select * from emp where sal > 2000 and sal < 2500;
select * from emp where sal between 2001 and 2499;
使用like操作符
%:表示0到多个字符 _:表示任意单个字符
--显示首字母为S的员工姓名和工资
select ename, sal from emp where ename like 'S%';
--显示第三个字母为O的员工信息
select * from emp where ename like '__O%';
在where条件中使用in
--显示empno为123,345,800的员工信息
select * from emp where empno = 123 or empno = 345 or empno = 800;
select * from emp where empno in (123, 345, 800);
使用is null的操作符
--显示没有上级的人的员工信息
select * from emp where mgr is null;
使用逻辑操作符号
--查询工资高于500或者岗位是MANAGER的员工,同时姓名首写字母是大写的J
select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
使用order by 字句 默认ASC
--按照工资从低到高的顺序显示员工的信息
select * from emp order by sal ASC;
--按照工资从高到低的顺序显示员工信息
select * from emp order by sal DESC;
--按照部门号升序而员工工资降序排列
select * from emp order by deptno ASC, sal DESC;
--按照部门号升序而入职时间降序排列
select * from emp order by deptno ASC, hiredate DESC;
复杂查询
分组查询
数据分组 ——max,min, avg, sum, count
--显示所有员工中的最高工资和最低工资
select max(sal) 最高工资, min(sal) 最低工资 from emp;
--显示所有员工中的最高工资和最低工资并显示它们的名字
select ename,sal from emp where sal = (select max(sal) from emp) or sal = (select min(sal) from emp);
--显示工资高于平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp);
group by 和 having子句
--显示每个部门的平均工资和最高工资
select deptno, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno order by deptno;
--显示每个部门的每种岗位的平均工资和最高工资
select deptno, job, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno, job;
--显示每个部门的每种岗位的平均工资和最高工资,并按部门号升序,平均工资降序排列
select deptno, job, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno, job order by deptno ASC,avg(sal) DESC;
--显示平均工资低于2000的部门号和他的平均工资
select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;
对分组查询的总结
1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中)
2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by
3 在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在group by 子句中,否则就会出错。
如SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000;
这里deptno就一定要出现在group by 中
多表查询
--显示员工名,员工工资以及所在部门名
select ename, sal, dname from emp,dept where emp.deptno = dept.deptno;
--显示部门号为10的部门名、员工名和工资
select dname,ename,sal from emp e, dept d where e.deptno = d.deptno and d.deptno = 10;
--显示各个员工的姓名、工资、工资级别
select e.ename,e.sal,s.grade from emp e,salgrade s where sal between s.losal and s.hisal;
--显示员工名,员工工资及所在部门名字,并按部门排序
select e.ename,e.sal,d.dname from emp e, dept d where e.deptno = d.deptno order by d.deptno;
自连接
--显示某个员工的姓名及其上级领导的姓名
select e1.ename 下级,e2.ename 上级 from emp e1, emp e2 where e1.mgr = e2.empno;
--内连接
select e1.ename 下级,e2.ename 上级 from emp e1 inner join emp e2 on e1.mgr = e2.empno;
子查询
单行子查询
--显示与SMITH同一部门的所有员工
select * from emp where deptno = (select deptno from emp where ename='SMITH') and ename != 'SMITH';
多行子查询
--查询和部门10工作相同的雇员的名字、岗位、工资、部门号
select ename,job,sal,deptno from emp where job in(select job from emp where deptno = 10) and deptno != 10;
select ename,job,sal,deptno from emp where job = any(select job from emp where deptno = 10) and deptno != 10;
--显示工资比部门30的所有员工的工资还要高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno=30);
select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);
--显示工资比部门30的任意一个员工的工资还要高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno=30);
select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);
补充:带有ANY或ALL谓词的子查询
子查询返回单值时可以用比较运算符,返回单列多行值时可以使用ANY或ALL谓词,使用ANY或ALL谓词时则必须同时使用比较运算符,其语义为:
>ANY | 大于子查询结果的某个值 |
>ALL | 大于子查询结果中的所有值 |
<ANY | 小于子查询结果中的某个值 |
<ALL | 小于子查询结果中的所有值 |
>=ANY | 大于等于子查询结果中的某个值 |
>=ALL | 大于等于子查询结果中的所有值 |
<=ANY | 小于等于子查询结果中的某个值 |
<=ALL | 小于等于子查询结果中的所有值 |
=ANY | 等于子查询结果中的某个值 |
=ALL | 等于子查询结果中的所有值(通常没有实际意义) |
!=(或<>)ANY | 不等于子查询结果中的某个值 |
!=(或<>)ALL | 不等于子查询结果中的任何一个值 |
事实上,用聚合函数来实现子查询通常比直接用ANY或ALL查询效率要高,ANY与ALL与聚合函数的对应关系如下所示:
| = | <>或!= | < | <= | > | >= |
ANY | IN | - | <MAX | <=MAX | >MIN | >=MIN |
ALL | -- | NOT IN | <MIN | <=MIN | >MAX | >=MAX |
多列子查询
--查询与SMITH的部门和岗位完全相同的所有员工
select e1.* from emp e1,(select deptno,job from emp where ename='SMITH') e2 where e1.deptno = e2.deptno and e1.job = e2.job and ename != 'SMITH';
在from子句中使用子查询
--显示高于自己部门平均工资的员工信息
select e1.*,e2.avgsal from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) e2 where e1.deptno = e2.deptno and e1.sal > e2.avgsal;
对子查询的总结:
在这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌视图,当在from子句中使用子查询时,必须给子查询指定别名。
注意:别名不能用as,如:SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;
红字部分的as应该去掉,因为表指定别名时,不能加as
PS:给表取别名的时候,不能加as;但是给列取别名,是可以加as的。
分页查询
1.根据rowid来分 执行时间0.03秒
2.按分析函数来分 执行时间1.01秒
3.按rownum来分 执行时间0.1秒
同一语句1执行效率最高,但因为过于麻烦,所以还是掌握3最实在。
select * from (select e1.*,rownum rn from (select * from emp) e1 where rownum <= 10) where rn >= 6;
select * from (select e1.*,rownum rn from (select ename,job,sal from emp order by sal DESC) e1 where rownum <= 10) where rn >=6;
PS:红色字体是可以更改的,其余部分可以当做公式用。想要修改查询范围,按什么排序。。。都是在最内层的select中修改的。那两个数值是获取范围的,
集合查询
1). union(并集∪)
--查询薪水大于2500和job为MANAGER的员工
select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where job='MANAGER';
2).union all(并集∪)(不会取消重复行,而且不会排序)
--查询薪水大于2500和job为MANAGER的员工(重复数据不能除去)
select ename,sal,job from emp where sal > 2500 union all select ename,sal,job from emp where job='MANAGER';
3). intersect(交集∩)
--查询薪水大于2500并且job为MANAGER的员工
select ename,sal,job from emp where sal > 2500 intersect select ename,sal,job from emp where job='MANAGER';
4). minus(差集-)
--查询薪水大于2500但是job不为MANAGER的员工
select ename,sal,job from emp where sal > 2500 minus select ename,sal,job from emp where job='MANAGER';
----------参考《韩顺平玩转Oracle》