java：Oracle（级联删除，左右内外交叉自然连接，子查询，all，any，in）
1.级联删除：
-- 级联删除:裁员，公司倒闭
-- 级联删除(cascade)，设置为null(setnull)，放任不管(No action)
-- cascade:(以一对多为例)如果删除多的一方数据，一的一方不受任何影响，但是如果删除一的一方的数据，多的一方所有对应数据全部被删除
select * from staff s inner join department d on d.id = s.department_id;
delete from staff s where s.id = 1;
delete from department d where d.id = 2;
-- set null:如果删除一的一方，多的一方对应的数据会变为null
select * from staff s full join department d on d.id = s.department_id;
delete from department d where d.id = 10000;
-- no cation:最常用
select * from staff s left join department d on d.id = s.department_id;
delete from department d where d.id = 2;
select * from staff;
2.左右内外交叉自然连接：
-- 在实际开发工作中，主需要在表中建立一个外键的列，一定不能在KEY这一栏中，建立主外键关系，使用的时候直接查询即可
-- cross join，nature join，left join，right join，inner join，full join
-- 多表查询
select * from teacher, classes;-- 1992年被定义的sql标准，sql1992
-- cross join:交叉连接
select * from teacher cross join classes;--1999年被定义的sql标准，sql1999 --select * from teacher, classes;
-- natural join 自然连接:等值才会连接: 在两张表中必须要存在一个主外键的关系 -- 自然有一个弊端：两张表中相同列名不能同时存在两个
-- 在自连接中，不能出现任何一个限定条件
-- 虽然不能自己添加限定条件，natural join会自动把dept_id=id给连接起来
-- 只需要理解就可以了，并不需要记忆，在实际开发中，不会有人让你这么写
-- 当时在表中有两个name列，所以会被限制
select * from department s natural join staff d;-->select * from staff s, department d where d.id = t.s.department_id;
-- left join on：左连接:以左表为基准连接到右表:这里的左表为department
select s.name from department d left join staff s on s.department_id = d.id;--这里的on就相当于where s.department_id = d.id
-- right join on:右连接，正好和左连接相反，以右边的表都基准连接到左边的表
select * from department d right join staff s on s.department_id = d.id;
-- inner join on :内连接
-- on:后面跟的条件必须是两张表主外键的关系
-- 在真正实际开发中，内连接用的是最多的
-- 在实际项目开发中，数据库的优化(程序员层面):sql优化:1.多使用where，少用多表查询的xxxx,xxx(表和表用逗号隔开)
select * from department d inner join staff s on s.department_id = d.id;--> select * from staff s, deparment d where s.department_id = d.id
-- full join on：外连接
-- left join：以左边为基准，如果关联的右表没有数据，就显示为空
-- right join:以右表为基准，如果关联的左表没有数据，就显示为空
-- full join:没有任何一张表作为基准，对应的数据就显示，如果对应不上两方都可以为空
-- full join主要用在于统计
select * from staff s full join department d on d.id = s.department_id;
left join，right join，inner join(通过inner可以省略join)
select * from t_order tor
left join t_user tur on tur.id = tor.user_id;
左表和右表的概念：以join为中心，左右划分，在join左边的表就叫做左表，在join右边的表就叫做右表
left join:
以左表为基准，连接到右表(查询右表数据)
首先要把左表的所有数据全部查询出来，然后再去根据查询出来的数据去关联右表
country(国家)和province(省份):典型的一对多
一的一方：country
多的一方：province:所以说外键要建立在province
不管是一对多还是一对一还是多对多，都可以随意建表
right join:
以右表为基准，连接到左表
首先要把右表中的数据(province)全部查询出来，然后再去根据查询出来的数据关联左表(country)
full join:
不会以任何一张表作为基准，只要使用full join连接的表，会把所有数据都会查询出来。
inner join:
不会以任何一张表作为基准，如果这两张表中的数据有对应的关系，就查询出数据。
count的使用：
count查询的是数据的条数
3.子查询：
-- 在oracle中的子查询中，不能进行order by排序，所有的Oder by 排序都要放到最终查询结果中
-- 比zhangsan年龄大的教师的信息
-- 比平均年龄要大的教师的信息
-- 同一部门下，比zhangsan工资低的员工信息
-- all，any，in，union，union all
-- 比zhangsan年龄大的教师的信息
--第一步：查询出张三的年龄
select age from teacher where name='zhangsan';-- 张三的年龄
-- 查询出所有教师的信息，并且年龄都要大于张三
select * from teacher where age > '张三的年龄';
select * from teacher where age > (select age from teacher where name='zhangsan');
--比平均年龄要大的教师的信息
-- 如果显示的年龄为0岁，那么该教师并没有填写实际年龄
-- 第一步：计算所有教师的平均年龄
select round(avg(nvl(age,0))) from teacher;
-- 第二步：查询所有教师信息，并且需要大于所有教师平均年龄
select * from teacher where age > '所有教师的平均年龄';
select * from teacher where age > (select round(avg(nvl(age,0))) from teacher);
-- 同一部门下，比zhangsan工资高的员工信息
-- 第一步：查询出zhangsan的工资
select salary from staff where name='zhangsan';
-- 第二步：查询出zhangsan的部门id
select s.department_id from staff s where s.name='zhangsan';
-- 第三步：查询出所有的员工，并且必须要和zhangsan同一个部门，并且工资必须要比zhangsan高
select * from staff where salary >
( select salary from staff where name='zhangsan')
and staff.department_id =
(select s.department_id from staff s where s.name='zhangsan');
4.any，all，in
-- any:比较任何一个:可以做等值对比，也可以大小对比；书写规范:运算符 any（DIY大于或小于任何一个就输出）
-- 查询出和zhangsan同一部门的所有员工
select * from staff s where s.department_id = any(select department_id from staff where name='zhangsan');
select * from staff s where s.department_id > any(select department_id from staff where name='zhangsan');
--all:比较所有（DIY比最大或最小的）
select id from department f where id>1-- 查询出部门id>1的所有部门id，查出来的是3条数据
select * from staff s where s.department_id > all(select id from department f where id>1);
-- any:其中任意一个进行比较：select id from department f where id>1，(2,3,4)得到数据就是从2开始的,得到3，得到4，id为4得到了会 覆盖前面得到的数据，只会返回一个
select * from staff s where s.department_id > any(select f.id from department f where f.id > 0);
-- select id from department f where id>1，会得到2,3,4
-- select * from staff s where s.department_id，是1,2,3,4，department_id必须要>后面查询出来的id
-- in:等值：使用in关键字的时候不需要运算符，直接用in即可，in就相当于=
select * from staff s where s.department_id in(select id from department f where id>1);
Practice：
1.建立六张表，user_type,user，order，goods_order，goods，goods_type，先分析出这个功能有什么表
user---用户表<----会员机制(铜牌会员，金牌会员，钻石会员)
id,username,password,address,user_type_id
user_type:会员表(用户类型)
id,typ_name,type_level
goods---商品表
id,goods_name,price,goods_type_id
goods_type:商品类型
id,type_name
orders---订单表
id,goods_nums,total_price,user_id
orders_goods订单和商品的关联关系表
goods_id,order_id
2.分析出表之间的关系
user_type----user:一对多
goods_type----goods:一对多
user----orders:一对多
goods----orders:多对多
goods和orders表中间要有关联关系表goods_orders
-- 不管是inner join left join 还是 right join 每出现一次关键字就只能连接一个表,如果想连接多个表多次使用inner join on， left join on，right join on
-- 在使用inner join的情况，通常inner可以省略
-- 查询出所有的订单信息
select tor.goods_nums goodsNums, tor.total_price totoalPrice, tgs.goods_name goodsName, tgs.price price, tgt.type_name goodsTypeName, tur.username username, tur.address address, tut.type_name userTypeName
from t_order tor
inner join t_goods_order tgo on tgo.order_id = tor.id
inner join t_goods tgs on tgs.id = tgo.goods_id
inner join t_goods_type tgt on tgt.id = tgs.goods_type_id
inner join t_user tur on tur.id = tor.user_id
inner join t_user_type tut on tut.id = tur.user_type_id;
-- 列出订单金额数大于1500 的各种会员级别
select tut.type_name from t_order tor
inner join t_user tur on tur.id = tor.user_id
inner join t_user_type tut on tut.id = tur.user_type_id
where tor.total_price > 1500;
-- 列出在金牌会员级别下的所有订单的金额
select tor.total_price from t_order tor
inner join t_user tur on tur.id = tor.user_id
inner join t_user_type tut on tut.id = tur.user_type_id
where tut.type_level < (select tutp.type_level from t_user_type tutp where tutp.type_name='金牌会员');
-- 列出订单金额高于平均订单金额的所有会员名称
select tur.username from t_order tor
inner join t_user tur on tur.id = tor.user_id
where tor.total_price > (select round(avg(total_price)) from t_order);
-- 列出至少有一个会员的所有顾客类型
select count(1), tut.type_name from t_user_type tut
inner join t_user tur on tur.user_type_id = tut.id
group by tut.type_name
having count(1) > 0;
-- 列出各个会员级别的最低订单金额
select min(tor.total_price), tut.type_name from t_order tor
inner join t_user tur on tur.id = tor.user_id
inner join t_user_type tut on tut.id = tur.user_type_id
group by tut.type_name;
-- 列出所有会员级别的详细信息和每个级别下的订单数量
select tut.type_name, tut.type_level, count(1) from t_order tor
inner join t_user tur on tur.id = tor.user_id
inner join t_user_type tut on tut.id = tur.user_type_id
group by tut.type_name, tut.type_level
-- 列出在每个会员级别的会员数量、平均订单金额
select count(1), round(avg(tor.total_price)), tut.type_name from t_user_type tut
inner join t_user tur on tur.user_type_id = tut.id
inner join t_order tor on tor.user_id = tur.id
group by tut.type_name
DIY:
-- 找到比钻石用户消费还要高的非会员用户的名字
select tur.user_name,tut.type_name from t_order tor
inner join t_user tur on tur.id=tor.user_id
inner join t_user_type tut on tut.id=tur.user_type_id
where tor.total_price>(select tor.total_price from t_order tor inner join t_user tur on tur.id=tor.user_id inner join t_user_type tut on tut.id=tur.user_type_id where tut.type_name='钻石会员')and tut.type_name='非会员' group by tur.user_name,tut.type_name
-- 找到平均消费的最低的会员级别名称
create view price_view as (select avg(tor.total_price) price ,tur.user_name,tut.type_name from t_order tor
inner join t_user tur on tur.id=tor.user_id
inner join t_user_type tut on tut.id=tur.user_type_id
group by tur.user_name,tut.type_name)
select pvw.type_name from price_view pvw where price=(select min(price)from price_view)and pvw.type_name!='非会员'
-- 找到平均消费最高的会员等级名称
select pvw.type_name from price_view pvw where price=(select max(price)from price_view)and pvw.type_name!='非会员'
