--外连接
作用：查询不满足连接条件的数据
select * from emp e,dept d
where e.deptno = d.deptno;
select * from dept;
select * from emp e,dept d
where e.deptno(+) = d.deptno; ---右外
insert into emp(empno) values(1122);
select * from emp e,dept d
where e.deptno = d.deptno(+); ---左外
select * from emp e,dept d
where e.deptno(+) = d.deptno(+); ---不存在这种写法
----外连接的另外一种写法：
select *
from emp e left outer join dept d
on e.deptno = d.deptno; ---left 显示左边表不满足条件的数据
---outer 可以省略
---on 只能写连接条件，其他条件 写到where里
select *
from emp e right outer join dept d
on e.deptno = d.deptno; ---右外
select *
from emp e full outer join dept d
on e.deptno = d.deptno; ---全外
select *
from emp e right outer join dept d
on e.deptno = d.deptno;
select *
from dept d left outer join emp e
on e.deptno = d.deptno;
---查询出没有员工的部门信息
select d.*
from emp e right outer join dept d
on e.deptno = d.deptno
where e.empno is null;
---自连接
---查询员工姓名和他的上级姓名
select * from emp;
员工的mgr = 上级的empno
select *
from emp worker,emp manager
where worker.mgr = manager.empno;
select * from emp worker;
select * from emp manager;
--查询出入职比上级早的员工
select *
from emp worker,emp manager
where worker.mgr = manager.empno
and worker.hiredate < manager.hiredate;
---分组查询
select
from
where 分组前的条件(不允许出现分组函数)
group by 列1,列2,……
having 分组后的条件(关于分组函数的条件)
order by
--分组函数
avg() sum() max() min() count() wm_concat()
平均数 求和 最大 最小 统计 列转行
select avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp;
select avg(comm),sum(comm),count(comm) from emp;
---分组函数不计算空值
--查询每个部门的平均工资
select avg(sal) from emp;
select deptno,avg(sal)
from emp
group by deptno;
--查询每种工作的最高工资
select job,max(sal)
from emp
group by job;
--查询每个部门中每种工作的平均工资
select deptno,job,avg(sal)
from emp
group by deptno,job
order by deptno;
--出现在select中的列，必须出现在group by语句里
select集合包含于group by集合
--查询平均工资大于2000 的部门
select deptno,avg(sal)
from emp
group by deptno
having avg(sal) > 2000;
--查询平均工资大于2000 的部门信息（号、名称、所在地）
select d.*,avg(sal)
from emp e,dept d
where e.deptno = d.deptno
group by d.deptno,d.dname,d.loc
having avg(sal) > 2000
order by d.deptno;
select deptno,wm_concat(ename),count(ename)
from emp
group by deptno;
---子查询
单行子查询 多行子查询 多列子查询
--查询与SCOTT同部门的员工信息
1）select deptno from emp where ename = 'SCOTT';
2）select * from emp where deptno = 20;
select * from emp
where deptno = (select deptno from emp where ename = 'SCOTT');
--查询与JONES 同上级的员工
select * from emp
where mgr = (select mgr from emp where ename = 'JONES');
--查询工资比MILLER 低，奖金比ALLEN 高的员工信息
select *
from emp
where sal < (select sal from emp where ename = 'MILLER')
and comm > (select comm frpm emp where ename = 'ALLEN');
--查询与MARTIN 同工作，并且在1981年5 月之前入职的员工
select *
from emp
where job = (select job from emp where ename = 'MARTIN')
and hiredate < to_date('1981-5-1','YYYY-MM-DD');
--查询 上级是JONES 的员工信息
select * from emp
where mgr = (select empno from emp where ename = 'JONES');
--查询工资比平均工资高的员工
select * from emp
where sal > (select avg(sal) from emp);
--查询工资比10 部门平均工资高的员工
select * from emp
where sal > (select avg(sal) from emp where deptno = 10);
--使用子查询，查询SALES 部门的员工信息
select *
from emp e,dept d
where e.deptno = d.deptno
and d.dname = 'SALES';
select * from emp
where deptno = (select deptno from dept where dname = 'SALES');
--使用子查询，查询出蔡成功同学 四大神术的成绩
select * from score
where ano = (select ano from student where aname='蔡成功')
and bno = (select bno from class where bname = '四大神术');
---多行子查询 >all <all >any <any =any in()
---查询工资比30 部门所有员工工资都要高的员工信息
select * from emp
where sal >all (select sal from emp where deptno = 30);
select * from emp
where sal > (select max(sal) from emp where deptno = 30);
>all 大于最大
<all 小于最小
>any 大于最小
<any 小于最大
=any
in()
select * from emp where sal in(select sal from emp where deptno = 30);
select * from emp where sal in(1600,1250,2850,1500,950)
--查询哪个部门没有员工
存在于dept表，但是不存在于emp表
select * from dept
where deptno not in(select distinct deptno from emp);
select * from emp; --deptno 不能有空值
--
select * from emp
where (sal,job) = (select sal,job from emp where ename = 'SCOTT' );
---查询每个部门的部门信息和部门人数(考虑40 部门)
P237-238 rownum
---不使用组函数，查询最高工资
---查询工资第二高到第八高的员工信息
---DML 数据操作语句：insert update delete
--新增
insert into 表名 values();
insert into 表名(列) 子查询;
insert into emp94(eid,ename) select 1122,'abc' from dual
union
select 1123,'abd' from dual
union
select 1124,'acd' from dual;
create table emp94(eid integer,
ename varchar(10),
birth date,
classno number(2));
select * from emp94;
insert into emp94(eid,classno) values(1234,11);
insert into emp94(eid,classno) select empno,deptno from emp;
--更改 update
update 表名 set 列名=值;
update 表名 set 列名=值 where ……;
update 表名 set 列1=值1,列2=值2,…… where ……;
update 表名 set 列=子查询 where ……;
update 表名 set 列1=子查询,列2=子查询,…… where ……;
update emp set sal = 9000,deptno=40 where job = 'CLERK';
---更改，把emp中，员工的工资翻倍 奖金在原奖金基础上+500
update emp set sal=sal*2,comm=nvl(comm,0)+500;
---更改，把SMITH 的工资改成与KING 一样
update emp set sal = (select sal from emp where ename = 'KING')
where ename = 'SMITH';
---更改，把ALLEN 改成 与CLARK同部门
update emp set deptno = (select deptno from emp where ename='CLARK')
where ename = 'ALLEN';
---更改，把与BLAKE 同工作的员工的上级，改成SCOTT
update emp set mgr = SCOTT的empno
where job = BLAKE的job;
---更改，把处于平均工资以下的员工，都调到BOSTON (部门的loc)
update emp set deptno = (select deptno from dept where loc='BOSTON')
where sal < 平均工资;
---更改，把WARD 的职位与工资，都调到与KING 相同
---删除
delete from 表名; ---删除整张表的数据
delete from 表名 where ……; --删除符合where条件的数据
delete from emp where deptno = 10;
select * from emp;
---数据库事务
事务由一句DDL语句或一组DML语句组成；
DDL事务 create alter drop truncate
DML事务 insert update delete
DML事务，以执行第一句DML语句为开始，以手动commit/rollback为结束
DDL事务，语句执行结果是自动提交commit，不能rollback
DML事务---锁
DML语句执行之后，commit之前：
1）当前用户/当前窗口能够看到语句执行结果
2）其他用户窗口看不到
3）结果是可以回退的(rollback)
4）数据状态，加锁的状态--资源争用
---锁的处理 sysdba
update emp set sal = 9000 where deptno =10;
select * from emp;
--DDL语句
--create 创建表
---默认值
create table emp94(eid number(4) default 1234,
ename varchar2(10) default 'abc',
birth date default to_date('2007-7-1','YYYY-MM-DD'));
insert into emp94(eid) values(1122);
insert into emp94 values(1122,null,null);
---复制表 CTAS
create table 表名 as 子查询语句;
create table db_emp2 as select * from emp;
create table demp as select *
from emp e join dept d
on e.deptno = d.deptno;
--alter 修改表
alter table emp94 add addr varchar2(40); --新增列
alter table emp94 add addr varchar2(40) default 'ZZZ'; --同时设置默认值
alter table emp94 modify addr char(10); --修改列 类型和长度
alter table emp94 modify birth default sysdate; --修改列 默认值
alter table emp94 modify eid default null; --清除默认值
alter table emp94 rename column birth to birthday; --修改列名
alter table emp_94 drop column birthday; --删除列
rename emp94 to emp_94; ---修改表名
select * from emp_94;
--drop 删除表
drop table 表名;
drop table db_emp;
--truncate 截断表
truncate table 表名;
truncate table db_emp; --delete from db_emp
select * from db_emp;
比较drop、truncate、delete
1）drop和truncate属于DDL语句，执行结果自动提交/保存；
delete属于DML语句，执行之后需要手动提交；在commit之前可以rollback
2）drop删除表中数据，同时删除表
truncate和delete只删除表中的数据
3）truncate只能删除整张表的数据；
delete可以删除整张表的数据，也可以删除where条件指定的数据
4）执行速度：drop > truncate > delete
5）drop和truncate释放存储空间，delete不释放
---约束
1）主键约束 primary key，唯一性和非空性
2）唯一约束 unique， 值唯一，但是可以为空
3）非空约束 not null， 值非空，但是可以重复
4）检查约束 check， 规定列的取值范围/值域
check(sal between 6000 and 10000)
check(sex in('男','女','不详'))
5）外键约束 foreign key，表与表之间的关系
select * from emp;
select * from dept;
insert into emp(empno,deptno) values(4972,50);
insert into dept(deptno) values(50);
delete from dept where deptno = 50;
---创建表的同时创建约束
create table db_emp(sid number(4),
sname varchar2(10) not null,
age number(3),
addr varchar2(40),
parents varchar(10),
classno number(2),
constraints pk_db_emp primary key(sid),
constraints uk_db_emp unique(addr),
constraints ck_db_emp check(age between 12 and 18),
constraints fk_db_emp foreign key(classno)
references dept(deptno)
);
insert into db_emp values(14,'XX',18,'NanJing',null,10)
select * from db_emp2;
delete from db_emp where sid = 13;
---表已经存在，创建/删除、约束失效/生效
alter table db_emp drop constraints UK_DB_EMP;
alter table db_emp add constraints uk_db_emp unique(addr);
---主键 外键 唯一 检查约束
alter table db_emp modify sname null;
alter table db_emp modify parents not null;
alter table db_emp enable constraints UK_DB_EMP; --生效
alter table db_emp disable constraints UK_DB_EMP; --失效
---视图 view
view是逻辑表，查看数据的窗口，本质上只是sql语句，不存储数据
table是物理表,占用存储空间，实际存储数据
作用：简化查询，提高安全性
create view 视图名 as 子查询;
create or replace view 视图名 as 子查询;
create or replace view e_view as select ename 姓名 ,sal 工资
from emp where deptno = 20;
select * from e_view;
drop view e_view;
---DCL 数据控制语句
grant 权限 to 用户; ---赋予权限
revoke 权限 from 用户; ---收回权限
--sysdba
grant create view to scott;
grant create user to scott;
grant create session to scott;
……
create user a94 identified by a123;
--普通用户
grant select on emp to a94;
grant delete,update,insert on emp to a94;
revoke delete,update,insert on emp from a94;
---索引 index
作用：提高查询速度
创建索引的规则：
1）经常出现在where中的列 适合创建为索引列
2）经常作为表之间的连接条件的列
3）值域/取值范围广、空值多
4）表中数据量大
关于索引的sql语句：
1、create index 索引名 on 表(列);
2、drop index 索引名;
3、alter index 索引名 rebuild; --重建索引
---存储过程
存储过程，sql语句的集合
一次编译，多次运行
select * from emp;
语法语义检查
权限检查
生成执行计划
create or replace procedure insertUser(uName in varchar2)
is
I integer;
begin
for I in 1..100 loop
insert into db_emp2(empno,ename) values(I,uName||I);
end loop;
commit;
end;
---调用：
begin
insertUser('user');
end;
---触发器
销售表 销售数量 +100
库存表 库存 -100
create table spb( --商品表
spbm integer, --商品编码
spmc varchar2(30), --商品名称
shangbi varchar2(20), --商标
chandi varchar2(12), --产地
guige varchar2(20), --规格
danwei varchar2(4), --单位
danjia number(10,2), --单价
kucun number(10,2) --库存数量
);
create table stock( --采购表
djh integer, --单据号
spbm integer, --商品编码
cgy varchar2(8), --采购员
cgd varchar2(30), --采购地
cgsl number(10,2), --采购数量
cgpc varchar2(16), --采购批次
cgrq date); --采购日期
创建第一个触发器：
一旦采购表中被插入采购商品的记录，就更新商品表的数据，使相应的商品的库存增加：
create or replace trigger stock_ins
after insert on stock
for each row
begin
update spb set kucun=kucun + :NEW.cgsl where spbm=:NEW.spbm;
end;
---------------------------
---查询每个部门的部门信息和部门人数(考虑40 部门)
select d.*,count(e.empno)
from emp e right join dept d
on e.deptno = d.deptno
group by d.deptno,d.dname,d.loc;
select d.deptno,nvl(con,0)
from (select deptno,count(empno) con from emp group by deptno) t
right join dept d
on t.deptno = d.deptno;
---不使用组函数，查询最高工资
rownum 存在查询结果中，不属于任何一张表
rownum 针对查询结果排序，
序号从1开始
rownum 比较运算符 = 1 < <=
select * from emp where rownum = 1 order by sal desc;
select * from (select * from emp order by sal desc) t
where rownum = 1;
---查询工资第二高到第八高的员工信息 分页查询
1)where rownum between 2 and 8
前八名 - 第一名
select * from (select * from emp order by sal desc) t
where rownum <= 8
minus
select * from (select * from emp order by sal desc) t
where rownum = 1;
2)select * from emp where rownum between 2 and 8;
select * from (select emp.*,rownum r from emp) t
where r between 2 and 8;
3)排序函数
select rank() over(order by sal desc) ran,
dense_rank() over(order by sal desc) dran,
row_number() over(order by sal desc) rown,
emp.*
from emp;
浙公网安备 33010602011771号