实例9(联合查询子查询).sql

经典的sql资料

--上课内容:  第四章 用语句查询表中的数据(联合查询、嵌套查询)

-- 4.3、准备工作：创建表并插入数据

 -- 4.3.1 创建表
create table stu_info
(
t_number char(8),
t_name varchar(10),
t_gender char(2),
t_birthday datetime
)

create table course
(
c_number char(6),
c_name varchar(20),
c_credit int,
c_hour int,
c_teacher varchar(10)
)


create table exam
(
t_number char(8),
c_number char(6),
t_grade decimal(5,2)
)


 -- 4.3.2 为表设置约束

--将要设置为主键的t_number字段设为非空
alter table stu_info alter column t_number char(8) not null
--把t_number字段设为主键
alter table stu_info add constraint stu_pr primary key clustered(t_number)


--将要设置为主键的c_number字段设为非空
alter table course alter column c_number char(6) not null
--把c_number字段设为主键
alter table course add constraint course_pr primary key clustered(c_number)

 -- 4.3.3 插入数据

--向stu_info表插入数据
insert into stu_info values('20040301','张华','女','19840113')
insert into stu_info values('20040302','王立','男','19830624')
insert into stu_info values('20040303','蒋超','男','19841115')
insert into stu_info values('20040304','王浩雨','男','19851020')
insert into stu_info values('20040305','张静','女','19840418')
insert into stu_info values('20050301','李华','女','19830113')
insert into stu_info values('20050302','张立','男','19840624')
insert into stu_info values('20050303','黄超','男','19851125')
insert into stu_info values('20050304','汪雨','男','19861020')
insert into stu_info values('20050305','王静','女','19850418')

--向course表插入数据
insert into course values('100101','高等数学',2,60,'赵金')
insert into course values('100102','大学英语',3,80,'王维')
insert into course values('100103','大学物理',2,60,'李华')
insert into course values('100104','大学英语',4,80,'刘杰')
insert into course values('100105','大学英语',NULL,80,'刘杰')

--向exam表插入数据
insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)


select * from stu_info
select * from course
select * from exam

 -- 4.2.3 联合查询 
  -- 4.2.3.1 UNION ：结果集的合并,将多个结果集组合为单个结果集；
                  -- 规则是所有查询中的列数和列顺序相同，数据类型兼容）

    --例：创建2个表，aaa(a int) bbb(b int),分别插入数据
    insert into aaa values(1)
    insert into aaa values(3)
    insert into aaa values(5)
    insert into aaa values(7)
    insert into aaa values(9)

    insert into bbb values(2)
    insert into bbb values(4)
    insert into bbb values(6)
    insert into bbb values(8)
    insert into bbb values(9)

    -- 查询在aaa表和bbb表进行联合查询
    select a from aaa UNION select b from bbb

    --我们学习 all 参数
    select a from aaa UNION all select b from bbb
    --加入 all 参数后，结果包含所有行，有重复值了

    --例：查询在stu_info表和exam表中所有t_number 的记录
    select t_number from stu_info UNION select t_number from exam

    --我们要确定一下它显示的列标题是stu_info 里的t_number 还是exam里的t_number
    select t_number as a from stu_info union select t_number,c_number as b from exam
    --显示的字段名是 a ,说明显示的是第一个查询语句中的列标题

    --我们要增加显示的列,t_name是字符型和t_grade是数字型的
    select t_number as a,t_name from stu_info union select t_number as b ,t_grade from exam
    -- 提示“将数据类型 varchar 转换为 numeric 时出错”，说明对应位置的数据类型必须是兼容的

    --我们要增加显示的列,t_name是字符型和t_grade是数字型的类型是可以兼容的
    select t_number as a,t_name from stu_info union select t_number as b ,c_number from exam


  -- 4.2.3.2 JOIN  ON 联合查询
    --(例：查询显示学生姓名、课程号、考试成绩）
    select t_name,c_number,t_grade from stu_info join exam on stu_info.t_number=exam.t_number  
    --还可以加查询条件
    --(例：查询考试成绩在80分以上的记录，显示学生姓名、课程号、考试成绩）
    select t_name,c_number,t_grade from stu_info join exam on stu_info.t_number=exam.t_number where t_grade>80 

    -- 4.2.3.2.1 INNER JOIN  ON —— 内联接
      --（联接字段的值在2个表都相同）
      -- 例：把exam表和stu_info表 按照t_number字段连接起来
      select * from exam inner join stu_info on exam.t_number=stu_info.t_number
      --我们发现它显示的把2个表字段并起来了，并且是根据查询语句的要求，inner join 的表对应在from表的后面
      --注意：当有NULL值存在的时候
        insert into exam values('20040301','100103',NULL)
        delete from exam where t_number='20040301' and c_number='100103'

    -- 4.2.3.2.2 LEFT OUTER JOIN  ON ——左向外联接
      --(联接字段的值在2个表都相同的数据 或 联接字段的值在左表有而右表没有)
      select stu_info.t_number,t_name,exam.t_grade from stu_info left join exam on stu_info.t_number=exam.t_number 

    -- 4.2.3.2.3 RIGHT OUTER JOIN  ON ——右向外联接
      --(联接字段的值在2个表都相同的数据 或 联接字段的值在右表有而左表没有)
      select stu_info.t_number,t_name,exam.t_grade from stu_info right join exam on stu_info.t_number=exam.t_number 

    -- 4.2.3.2.4 FULL OUTER JOIN  ON ——全外联接
      --(联接字段的值在2个表都相同的数据 或 联接字段的值在左表有而右表没有 或 联接字段的值在右表有而左表没有)
      select stu_info.t_number,t_name,exam.t_grade from stu_info full join exam on stu_info.t_number=exam.t_number 

    -- 4.2.3.2.5 CROSS OUTER JOIN  ON ——交叉外联接（笛卡尔积）
      select stu_info.t_number,t_name,exam.t_grade from stu_info cross join exam 

  -- 4.2.3.3 多表查询

    -- 例：查询显示学生姓名、课程名、考试成绩
      select stu_info.t_name,course.c_name,exam.t_grade from stu_info,course,exam 
      where stu_info.t_number=exam.t_number and course.c_number=exam.c_number 
    --可以使用别名 
      select s.t_name,c.c_name,e.t_grade from stu_info as s,course as c,exam as e 
      where s.t_number=e.t_number and c.c_number=e.c_number


 -- 4.2.4 嵌套查询：主要用于复杂的查询，上层的查询块称为外层查询或主查询；
                 -- 下层的查询块称为内层查询或子查询

  -- 4.2.4.1 使用子查询的单值进行比较运算
   -- 例：我们可以使用子查询得到平均分，并作为外查询的条件
   select t_number,t_name,t_grade from stu_info where t_grade >(select avg(t_grade) from exam )

  -- 4.2.4.2 WHERE 表达式 [NOT] IN （子查询）
   -- 例：我们要查询stu_info 表中，考试成绩〉90的学生姓名
   select t_number,t_name from stu_info where t_number in (select t_number from exam where t_grade>90)

  -- 4.2.4.3 WHERE 表达式 比较运算符 [ANY] ALL （子查询）
   -- 例：我们要查询stu_info 表中，考试成绩〉90的学生姓名
   select t_number,t_name from stu_info where t_number=any(select t_number from exam where t_grade>90)

  -- 4.2.4.4 WHERE 表达式 比较运算符 [NOT] EXIST （子查询）
   -- 例：我们要查询参加考试的学生学号、姓名
   select t_number,t_name from stu_info where exists (select t_number from exam where stu_info.t_number=exam.t_number )



--练习：

-- 1. 查询sales数据库中的output_detail表和receive_detail表中的goods_id字段进行联合查询

-- 2. 查询sales数据库中的output_detail表和receive_detail表中的goods_id和pici字段均相同,而且价格在700元以上的数据记录

-- 3. 查询sales数据库中receive_detail表的商品信息，要求receive_detail表中的pici是output_detail表中销售利润（(sale_price-price）*quantity)大于2的商品所具有的pici

-- 4. 查询northwind数据库中products表的每种产品的产品编号及供应商名称

-- 5. 查询northwind数据库中没按时送达的订单号、订单操作人姓名（姓和名之间用空格分隔）

-- 6. 查询northwind数据库中，库存不足的类别名称为'Confections'的产品编号和产品名称(products表、categories表)

-- 7. 查询pubs数据库中某店铺的销售情况（sales表、stores表）

-- 8. 使用子查询，查询test数据库中学习了'高等数学'的学生编号、学生姓名

-- 9. 使用标准SQL嵌套语句，查询test数据库中学习了课程编号为'100101'的学生编号、学生姓名、课程编号

-- 10. 使用标准SQL嵌套语句，查询test数据库中不学习课程编号为'100101'的学生编号、学生姓名

-- 11. 查询test数据库中,选修了课程的学员人数

-- 12. 查询test数据库中,选修了课程超过2门的学生编号、学生姓名












--练习参考答案：

-- 1. 查询sales数据库中的output_detail表和receive_detail表中的goods_id字段进行联合查询
select goods_id as a from output_detail union select goods_id as b from receive_detail

-- 2. 查询sales数据库中的output_detail表和receive_detail表中的goods_id和pici字段均相同,而且价格在700元以上的数据记录
select * from receive_detail as r inner join output_detail as o 
on r.goods_id=o.goods_id and r.pici=o.pici and r.price>700 

-- 3. 查询sales数据库中receive_detail表的商品信息，要求receive_detail表中的pici是output_detail表中销售利润（(sale_price-price）*quantity)大于2的商品所具有的pici
select detail_id,goods_id,name,price from receive_detail where pici in
(select pici from output_detail where (sale_price-price)*quantity>2)

-- 4. 查询northwind数据库中products表的每种产品的产品编号及供应商名称
select p.productname,s.companyname from products as p,suppliers as s where p.supplierid=s.supplierid 

-- 5. 查询northwind数据库中没按时送达的订单号、订单操作人姓名（姓和名之间用空格分隔）
select * from orders
select * from employees
select o.orderid,(e.firstname+' '+e.lastname) as '操作人'  from orders as o,employees as e where o.employmeeId=e.employmeeId and  o.requireddate<shippeddate 

-- 6. 查询northwind数据库中，库存不足的类别名称为'Confections'的产品编号和产品名称(products表、categories表)
select p.productid,p.productname from products as p 
where p.categoryid=(select c.categoryid from categories as c 
where c.categoryname='Confections' ) and p.unitsinstock<p.unitsonorder

-- 7. 查询pubs数据库中某店铺的销售情况（sales表、stores表）
select * from sales where stor_id=(select stor_id from stores where stor_name like 'doc%' )
--当查询记录不止一条时，就不能用 = 而是要用 in
select * from sales where stor_id in (select stor_id from stores where stor_name like '%book%' )

-- 8. 使用子查询，查询test数据库中学习了'高等数学'的学生编号、学生姓名
select t_number,t_name from stu_info  where t_number in 
(select t_number from course as c,exam as e where c.c_number=e.c_number and c.c_name='高等数学')

-- 9. 使用标准SQL语句，查询test数据库中学习了课程编号为'100101'的学生编号、学生姓名、课程编号
select s.t_number,s.t_name,e.c_number from stu_info as s ,exam as e where s.t_number=e.t_number and e.c_number='100101'

-- 10. 使用标准SQL嵌套语句，查询test数据库中不学习课程编号为'100101'的学生编号、学生姓名
select t_number,t_name from stu_info  where t_number not in 
(select t_number from exam as e where t_number=e.t_number and e.c_number='100101')

-- 11. 查询test数据库中,选修了课程的学员人数
select 学生人数=COUNT(DISTINCT [t_number]) from  exam

-- 12. 查询test数据库中,选修了课程超过2门的学生编号、学生姓名
select t_number,t_name from stu_info where t_number in( select t_number from exam group by t_number having count(distinct c_number)>2)