SQL第四天

emp empno mgr
emp dept  deptno
emp salgrade salary lowsal hisal


1zhangwuji的领导是谁？（内连接，非关联子查询）
select e.ename,m.ename
from emp_hiloo e join emp_hiloo m
on e.mgr = m.empno
and e.ename = 'zhangwuji'


select ename
from emp_hiloo
where empno in
      (select mgr from emp_hiloo
       where ename = 'zhangwuji')


2zhangwuji领导谁？（内连接，非关联子查询）
select e.ename,m.ename
from emp_hiloo e join emp_hiloo m
on e.mgr = m.empno
and m.ename = 'zhangwuji'


select ename
from emp_hiloo
where mgr in 
      (select empno from emp_hiloo
       where ename = 'zhangwuji')


3哪个级别没有员工？
select s.grade
from emp_hiloo e right join salgrade_hiloo s
on e.salary between s.lowsal and s.hisal
where e.empno is null


两张表的数量关系是多对1，内连接的结果集的记录数<=多的那张表的记录数


4哪些人是领导？ 3（join、in、exists）
5哪些人是员工？ （outer join 、not in 、not exists)
select ename from emp_hiloo o
where not exists
      (select 1 from emp_hiloo i
       o.empno = i.mgr)


子查询的表里的所有记录都不满足匹配条件，not exists就是成立的.


分页问题
rownum 
select rownum,ename
from emp_hiloo
where rownum <= 3


select rownum,ename,salary
from emp_hiloo
where rownum between 4 and 6


select rn,ename,salary
from (
select rownum rn,ename,salary
from emp_hiloo
where rownum <= 6
      )
where rn >= 4


排名问题
工资最高的前三个人？
select rownum,ename,salary
from (select ename,salary
      from emp_hiloo
      order by salary desc)
where rownum <= 3


DDL
数据类型（date）
字符类型
varchar2定义时必须指定宽度，按字符串的实际长度存。数据本身是变长，用varchar2。varchar2空格敏感
char定义时可以不指定宽度，缺省值为1.按定义长度存，不足补空格。数据本身是定长，用char。char空格不敏感。
select ename,salary
from emp_hiloo
where to_char(hiredate,'fmMONTH') = 'MARCH'


number类型
create table test(
c1 number,
c2 number(6),  六位整数，小数部分四舍五入
c3 number(4,2),整数位2位，小数位2位，四舍五入
c4 number(2,4),小数位4位，填数字的有效位数是2位，0.0099，四舍五入
c5 number(3,-3))，六位整数，后三位均为0，四舍五入


约束
主键约束 primary key pk 保证表里不会有两条一样的记录。
pk=unique + not null


create table test
(c1 number(2) constraint test_pk_c1 primary key) 列级约束


table 学生选课表
sid  学号
cid  课程号
score 分数


每张表只能有一个primary key。
联合主键，表级约束
create table stu_cour
(sid number(4)，
 cid number(2)，
 constraint stu_cour_sid_cid primary key(sid,cid),
 score number(3)
)


outer join pk的not null特性
where 匹配表.pk列 is null
where 匹配表.nn列 is null


not null约束没有表级约束形式
create table test
(c1 number not null)


unique约束
pk和uk保证唯一
区别：uk可以为null，并且是多个null值。
一张表可以有多个uk
create table test(
c1 number constraint test_c1_pk primary key,
c2 number constraint test_c2_uk unique)


create table test(
c1 number constraint test_c1_pk primary key,
c2 number constraint test_c2_uk unique,
c3 number constraint test_c3_uk unique)


create table test(
c1 number constraint test_c1_pk primary key,
c2 number,
c3 number,
constraint test_c2_c3_uk unique(c2,c3))


create table test(
c1 number constraint test_c1_pk primary key,
c2 number constraint test_c2_uk unique not null)


外键约束


学生表
create table student
(
id number(4) constraint student_id_pk primary key,
name varchar2(20) not null
)


课程表
create table course(
id number(2) constraint course_id_pk primary key,
name varchar2(20) not null
)
学生选课表 2个fk 1个pk
create table stu_cour
(sid number(4) constraint stu_cour_sid_fk references student(id) ，
 cid number(2) constraint stu_cour_cid_fk references course(id)，
 constraint stu_cour_sid_cid primary key(sid,cid),
 score number(3)
)


emp（deptno 取值重 ）fk child table-->dept(deptno 唯一) pk、uk 多对1 parent table
on p.pk列 = c.fk列


emp(mgr)fk  emp(empno) pk
on e.mgr = m.empno


1先create parent table（被引用列必须定义成pk、uk），再create child table（fk）
2先insert into parent table，再insert into child table，否则报错parent key not found
3先delete from child table，再 delete from parent table
4先drop child table，再drop parent table。


drop table student cascade constraints purge;
先将stu_cour表上的fk约束（stu_cour_sid_fk）删除，再drop studnet table.


create table stu_cour
(sid number(4) constraint stu_cour_sid_fk
     references student(id) on delete cascade,
 cid number(2),
 constraint stu_cour_cid_fk
   foreign key (cid) references course(id),
 constraint stu_cour_sid_cid primary key(sid,cid),
 score number(3)
)


delete from student where id = 1;
(delete from stu_cour where sid = 1;
 delete from student where id = 1
)


create table stu_cour
(sid number(4) constraint stu_cour_sid_fk
     references student(id),
 cid number(2),
 constraint stu_cour_cid_fk
   foreign key (cid) references course(id),
 constraint stu_cour_sid_cid primary key(sid,cid),
 score number(3)
)


check
create table stu_cour
(sid number(4) constraint stu_cour_sid_fk
     references student(id),
 cid number(2),
 constraint stu_cour_cid_fk
   foreign key (cid) references course(id),
 constraint stu_cour_sid_cid primary key(sid,cid),
 score number(3) ,check (score between 0 and 100
)


DML（数据类型，约束）
insert into tabname 
select * from 


update tabname 
set col=null（赋值）
where col=null(等号)


delete from tabname
where


transaction(事务)
account
id
balance
卡a-->卡b 3000
update account 
set balance = balance - 3000
where id = 'a'


update account
set balance = balance + 3000
where id = 'b'




update account
set balance = 
      decode(id,'a',balance-3000,
                'b',balance+3000)
where id in ('a','b')


update account
set balance = 
      decode(id,'a',balance-3000,
                'b',balance+3000,
                balance)


所谓事务把一堆DML语句定义成一个逻辑单元，即一个原子操作，对应的现实生活中的一笔交易。事务或者成功提交，或者回滚。


事务将数据库中的数据从一种状态改变成另一种状态。（5000,1000)-->(2000,4000)
不会有（2000,1000），程序员写的事务，不是单独的DML语句。


事务的结束 commit rollback


事务的隔离级别 read committed 读已经提交了的数据


DDL语句自动提交的


并发操作
DML
表级共享锁
行级排他锁


表级共享锁 行级排他锁
s1 ok ok
s2 ok wait(排队)
s3 ok ok


当我们操作同一条记录的时候，彼此等待。


DDL
ddl排他锁
对一张表做ddl，报错
ORA-00054: resource busy and acquire with NOWAIT specified
一定有其他session在dml这张表


DML操作，没反应，一定有其他session在dml同一条记录。


如果不commit，rollback，会出现以下现象：
1每个session里可以读已经提交了的数据，和本session正在修改的数据。
2如果不commit，rollabk，DML占用的锁就不会释放，会阻塞其他session操作那些记录的。
3如果不commit，rollback，DML占用的回滚段资源不释放。


oltp on-line transaction processing
联机事务处理系统


SQL
pl/sql


























外键约束 foreign key fk
非空约束 not null
唯一键约束  unique   uk
检查约束 check




























约束


课堂练习
1 各个部门的平均工资
select max(d.dname),round(avg(salary))
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno
group by d.deptno


select d.dname,a.asal
from dept_hiloo d join
 (select deptno,round(avg(salary)) asal
  from emp_hiloo
  group by depnto) a
on d.deptno = a.deptno
     
课后练习
按工资排名的第4到6名？




openlab    pts/38        9月 23日 10:44 (172.30.10.87)


 root  4459  4456   0 10:47:16 pts/61      0:00 login -p -h 172.30.4.245 -d /
/pts/61
 root  4265  4262   0 10:45:20 pts/48      0:00 login -p -h 172.30.7.95 -d /d


openlab    pts/61        9月 23日 10:48 (172.30.10.249)


openlab    pts/139       9月 23日 10:51 (172.30.7.63)