常用的sql语句的总结（联合查询）

--union
--查询可以嵌套使用，这叫做子查询
select * from (
select cname,cid from TblClass
union
select DName,DId from Department
--union
--...
) as t1
order by cname

--另一种情况
select cid from TblClass
union
select did from Department
--union all
select cid from TblClass
union all
select did from Department
--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩
use ItCastCn
select '最高成绩',MAX(tEnglish) from TblScore
union all
select '最低成绩',MIN(tenglish) from TblScore
union all
select '平均成绩',AVG(tenglish) from TblScore
--合并成一行
select MAX(tEnglish),MIN(tEnglish),AVG(tEnglish) from TblScore
--查询每位老师的信息，包括姓名、工资，并且在最后一行加上平均工资和最高工资
select tTName,tTSalary from TblTeacher
union all
select '平均工资',AVG(tTSalary) from TblTeacher
union all
select '最高工资',MAX(tTSalary) from TblTeacher
--从MyOrders表中统计每种商品的销售总价，并且在底部做汇总
use hem09
select * from tblorders
insert into tblorders values('瓜子',2,10)
insert into tblorders values('花生',5,14)
insert into tblorders values('糖果',8,65)
insert into tblorders values('二锅头',6,72)
insert into tblorders values('瓜子',27,10)
insert into tblorders values('花生',41,14)
insert into tblorders values('糖果',65,65)
insert into tblorders values('二锅头',12,72)
--==========
select oname,SUM(ocount*oprice) as 销售总价
from tblorders
group by oname
union all
select '合计',SUM(ocount*oprice)
from tblorders
---一次插入多条数据，这也是一种子查询，这种插入不需要写values关键字
insert into tblorders
select '瓜子',10,10 union all
select '二锅头',43,72
--完成表的备份
select * into tblOrders_bak--要求：表tblOrders_bak不存在
from tblorders

select * from tblOrders_bak
--快速插入二
insert into tblOrders_bak
select oname,ocount,oprice from tblOrders