课堂练习
找到员工表中工资最高的前三名， 要求按如下格式输出
rownum 是行号，伪列，并不是真实存在于表中的列，而是查询过程中生成
select rownum,emp.* from emp order by sal desc; 序号乱了
结论：rownum行号在排序前生成
解决思路：先排序，后生成行号 ，子查询
select rownum,e.*
from
(
select *
from emp
order by sal desc
) e
where rownum <=3
分页问题 按照工资从大到小进行排序，每页4名员工，想看第2页（5-8名)
select rownum,e.*
from
(
select *
from emp
order by sal desc
) e
where rownum between 5 and 8
rownum的比较只能使用< 或者<= 不能使用> >= 除了>=1
这跟行号生成逻辑相关
行号并不是一开始生成然后再使用where进行过滤，而是在where条件判断前一刻生成行号，如果当前where条件没有通过
行号会继续保留给下一条数据
比如 rownum <=3 的
1 KING 满足where条件，筛选出来
2 FORD 也满足 where条件....
如果 rownum >=5 的
1 KING 没有满足where条件，没有通过筛选
1 FORD 因为刚才king没有通过筛选条件，行号1会被保留继续使用 ，第二行数据FORD还是以1为
行号，where条件还是不通过
1 SCOTT ....
分页问题，可以先生成好所有的行号，然后再选择5-8，再嵌套一个自查询，用来生成行号
select *
from
(
select rownum rid,e.*
from
(
select *
from emp
order by sal desc
) e
)
where rid between 5 and 8
找到emp表中薪水大于本部门平均薪水的员工
注意：多表 ， from 后置多表子查询
1 先查询出各部门的平均工资
select deptno , avg(sal)
from emp
group by deptno
2 再进行多表联查，from 后置子查询
select *
from emp a,
(
select deptno , avg(sal) avgsal
from emp
group by deptno
) b
where a.deptno=b.deptno and sal > avgsal
关联子查询
特点，子查询是不能够单独执行，必须依赖父查询
select *
from emp outer
where sal >
(
--查出当前这个员工所在部门的工资
select avg(sal)
from emp inner
where inner.deptno = outer.deptno
)
计算原理跟普通的子查询是不一样
SQL解析：一般先执行子查询（内查询），再执行父查询（外查询）；关联子查询除外
关联子查询就是外部父查询每次where条件进行判断时候都会去执行一次子查询,性能消耗更加大
普通子查询，只是一开始就执行一次子查询，之后父查询都一直沿用这个结果
统计每年入职的员工个数
1 将员工的入职日期转年份 to_char(hiredate,'yyyy')
select to_char(hiredate,'yyyy') hire_year
from emp
2 使用group by 做统计？
使用group by 是会得到以下格式
1980 1
1981 2
1982 ...
select hire_year ,count(*)
from
( select to_char(hiredate,'yyyy') hire_year
from emp
)
group by hire_year
与结果样式不符
3 提示：
制作一个中间的表格，01标记法
1980 1981 1982 ....
1 0 0
0 1 0
...
然后再做一个汇总，sum 进行统计
select count(*) total , sum(hire_1980) "1980" ,
sum(hire_1981) "1981" ,
sum(hire_1982) "1982" ,
sum(hire_1987) "1987"
from
(
select decode(hire_year,'1980',1,0) hire_1980,
decode(hire_year,'1981',1,0) hire_1981,
decode(hire_year,'1982',1,0) hire_1982,
decode(hire_year,'1987',1,0) hire_1987
from
( select to_char(hiredate,'yyyy') hire_year
from emp
)
)
1 oracle表
1 创建表
create table 表名
(
列名1 类型1,
列名2 类型2,
…
)
创建一个员工表t1，员工号id（整数），和姓名name（字符串）
create table t1
(
id number,
name varchar2(30)
)
* 必须以字母开头
* 必须在 1–30 个字符之间
* 必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
* 必须不能和用户定义的其他对象重名
* 必须不能是Oracle 的保留字
* Oracle默认存储表名是都是大写
数据类型 varchar 和 char
同样是 30的长度 varchar(30)
char(30) 固定就占用30个字符的长度
varchar(30) 可变长 ,最长也是30，但是如果存储 "hello" 最终占用的空间是5
遍历数据的时候因为数据库软件要考虑字符串的长度，有额外的计算开销
建议如果字符串是固定长度的就使用char，比如存储身份证号18位
sql标准里边是使用 varchar ，但是oracle自己扩展了 varchar2 ，内部实现跟标准有区别
2 修改表
alter table 表名 .........
1 往t1表里边添加一列 email varchar(40)
alter table t1 add 列名 类型
alter tble t1 add email varchar(40)
2 修改t1表email列名为address
alter table 表名 rename column 旧列名 to 新列名
alter table t1 rename column email to address
3 修改t1表address列类型为varchar(50)
alter table 表名 modify 列名 新类型
alter table t1 modify address varchar(50)
4 删除t1表address列
alter table 表名 drop column 列名
alter table t1 drop column address
5 修改t1表名为t2
rename t1 to t2
3 另一种创建表的方式
create table 表名 as select 子查询
将子查询返回的内容都存到新表里边
创建一张表emp2，数据跟emp表一样
临时备份功能
create table emp2 as select * from emp
创建一张表emp3，数据跟emp表一样，只有表结构，没有数据
create table emp4 as select * from emp where 1=2
因为where条件 1=2 恒为假，所以没有数据筛选出来，但是表结构还是可以保留
4 清空表
delete from 表名 可以清空
truncate table 表名
1. delete 逐条删除表“内容”，truncate 先摧毁表再重建。
(由于delete使用频繁，Oracle对delete优化后delete快于truncate)
2. delete 是DML语句，truncate 是DDL语句。DML语句可以闪回(flashback)和回滚rollback，DDL语句不可以闪回和回滚。
（闪回： 做错了一个操作并且commit了，对应的撤销行为。了解）
3. 由于delete是逐条操作数据，所以delete会产生碎片，truncate不会产生碎片。
（同样是由于Oracle对delete进行了优化，让delete不产生碎片）。两个数据之间的数据被删除，删除的数据——碎片，整理碎片，数据连续，行移动。
4. delete不会释放空间，truncate 会释放空间。用delete删除一张10M的表，空间不会释放。而truncate会。所以当确定表不再使用，应使用truncate。
5 删除表
drop table 表名
show recyclebin
还原回收站中的表
闪回
flashback table 表名 to before drop;
彻底删除表
drop table 表名 purge;
purge recyclebin; 清空回收站
6 表的约束
主键 primary key
非空 not null
唯一 unique
检查 check
外键 foreign key
创表的时候指定约束
create table 表名
(
列名 类型 [constraint 约束名] 约束的类型以及参数,
......
)
create table student
(
sid number constraint pk_student primary key, --学生Id主键约束
sname varchar2(20) constraint nn_student_name not null,--学生姓名非空约束
email varchar2(20) constraint un_student_email unique --学生邮件唯一约束
constraint nn_student_email not null, --同时邮件可再设非空，没有“，”
age number constraint chk_student_age_min check(age > 10), --学生年龄设置check约束
gender varchar2(6) constraint chk_student_gender check(gender in ('男', '女')),
deptno number constraint fk_student references dept (deptno) ON DELETE SET NULL
)
2 视图
create view 视图名
as 子查询
不是所有用户都有权限去创建视图
使用超级管理员给scott用户创建视图的权限
使用超级管理员来执行以下语句 sqlplus / as sysdba
grant create view to scott
创一个视图，用来观看10号部门的员工信息
create view emp_10 as select * from emp where deptno =10
创建视图之后，就当做一个普通表来进行查询
select * from emp_10;
同理创建一个表
create table t_emp_10 as select * from emp where deptno =10
表的数据是实实在在存在硬盘的
但是视图并不存储数据，只是一个查询语句的结果
每次 select * from emp_10; 去查询视图的时候，其实都会执行
select * from emp where deptno =10
可以简单认为就是查询语句的封装
删除视图
drop view 视图名
不会将视图丢到回收站
3 序列
创建一张表
create table t1
(
id number primary key,
name varchar2(30)
)
insert into t1 values(1,'aaa');
insert into t1 values(2,'bbb');
每次都要认为先确定id最大值是什么，再插入一行新数据，就id++
解决方法：最好是数据库自动帮我们记住序号，下次自行加1
创建一个序列
create sequence 序列名
create sequence my_seq;
使用序列
序列名.currval 获取当前序列的值
SQL> select my_seq.currval from dual;
select my_seq.currval from dual
*
第 1 行出现错误:
ORA-08002: 序列 MY_SEQ.CURRVAL 尚未在此会话中定义
结论：使用currval 取值不能早于第一次使用nextval
序列名.nextval 获取下一个值
insert into t1 values(my_seq.nextval,'name-'||my_seq.nextval)
结论：同一条sql语句，序列的取值只取一次
删除序列
drop sequence 序列名
4 索引
原理：就是对表的某一列或者多列进行排序，下次查询表的时候先查询索引，然后再取数据
create index 索引名字 on 表名(列1,列2.....)
create index myindex on emp(deptno)
如何使用索引？只要管创建和删除索引就行，使用上没有特定的语句，数据库会自己判断条件
确定有哪个索引可用，自动使用，同理，插入数据的时候，数据库也会维护索引
select * from emp where deptno = 10; --能够使用索引
select * from emp where job = '' --没有用到索引
复合索引
create index myindex on emp(deptno,job) --对两列创建复合索引
order by deptno , job
select * from emp where job = 'CLERK' --没有用到索引，因为符合索引非第一列也都是无序
select * from emp where job = '' and deptno = '' --会使用索引
select * from emp where deptno = '' --也会使用索引
有唯一约束的列是不需要创建索引，数据库默认都会自动创建(包括主键)
删除索引
drop index 索引名
5 同义词
为hr.employees表创建同义词
create synonym 同义词 for hr.employees;
删除同义词
drop synonym 同义词
3375
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
