当前位置:Gxlcms > 数据库问题 > java:Oracle(级联删除,左右内外交叉自然连接,子查询,all,any,in)

java:Oracle(级联删除,左右内外交叉自然连接,子查询,all,any,in)

时间:2021-07-01 10:21:17 帮助过:10人阅读

  -- 级联删除:裁员,公司倒闭
  -- 级联删除(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;

 

 

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

  -- 查询出和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:比较所有
  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!=‘非会员‘

java:Oracle(级联删除,左右内外交叉自然连接,子查询,all,any,in)

标签:select   级别   添加   程序   set   zha   from   大小   名称   

人气教程排行