【基础3】SQL数据库的查询： 基本查询 连接查询 子查询 合并查询 XK数据库查询

目录

基本查询

连接查询

子查询、合并查询

XK数据库的查询

---

基本查询

use Sales
go


[]里是可选的
select   distinct // top n // top n // top n percent//列名				--查找哪列
from 表名				--在哪个表中查找
[where  条件表达式]	--满足条件的显示
[group   by  分组列]
[having  条件表达式]			--分组以后对组的筛选
[order   by 排序列 asc/desc] 
[compute 聚合函数 by 分组列]						--分组前必须排序


select * from Goods		--查看Goods表中的所有列
select * from Employees

select 商品名称,进货价,零售价  from Goods			--查看表中指定列
select 部门 from Employees									--查看部门列
select distinct 部门 from Employees						--消除相同的部门值   distinct消除重复值
select top 1 * from Employees								--查询表中第一个行数据（第一个人的数据）
select top 5 * from Employees								--查询前5行数据
select top 50 percent * from Employees				--查找表中50%（一半的数据）


select * from Employees where 性别=1
select * from Employees where 部门='采购部' or 部门='销售部'
select * from Employees where 部门 in ('采购部','销售部')
--如果是多个值，就用in

select * from Goods where 零售价 >=2000
select * from Goods where 零售价 >=2000 and 零售价<=5000
select * from Goods where 零售价 between 2000 and 5000			--这里的between and 包括2000和5000，如果要求不等于2000或5000，则不能使用这种语句

select * from Employees where 姓名='李建国'				--指定查找李建国这个人的数据
select * from Employees where 姓名 like '李%'				--只要是姓李的都找出来，like（像这种格式的），%表示任意字符、
select * from Employees where 姓名 like '[李]%'		    --姓李的
select * from Employees where 姓名 like '[李,王]%'		--姓李的姓王的，都取出来
select * from Employees where 姓名 like '李_'				--姓李，但是姓名只能是两个字的，（_）下划线表示姓和名一共只能是两个字
select * from Employees where 姓名 not like '李%'			--不姓李的都找出来。
select * from Employees where 姓名 like '[^李]%'		    --有[]表示取[]里面的，如果是[^ ]表示不取。不姓李的
select * from Employees where 姓名 like '[^李,王]%'		--不姓李，不姓王的。

select * from Employees where 姓名 like '燕%'	


select top 1 * from Goods											--查找Goods表里的第一列数据
select *from goods order by 零售价				 			    --自动是升序，升序是ase 可以省略不写  第一行最小，最后一行最大  从小到大
select *from goods order by 零售价 desc						--降序需要加个desc  从大到小

--查找最高零售价/最低零售价
select  top 1 *from goods order by 零售价				 			    
select  top 1 *from goods order by 零售价 desc			



--计算列（列别名）
--统计每个部门有有几个人    三种方式
select 部门, count (*)  人数 from Employees group by 部门
--       列名			定义统计出的数据的列名为（人数）				count统计函数，统计每个部门有几行数据
select 部门, count (*) as 人数 from Employees group by 部门
select 部门, 人数=count (*)   from Employees group by 部门



--统计部门人数大于二的
select 部门, 人数=count (*)   from Employees group by 部门 having count(*)>2  --having 分组之后的条件筛选
																							--count(*)>2代表统计的数据行要超过两个

--统计各商品销售的数量和。
select sum(数量) from Sell		--数量的销售总和 
select 商品编号 ,sum(数量) 总计 from Sell group by 商品编号				--以商品编号分组，统计数量
select 商品编号 ,sum(数量) 总计 from Sell group by 商品编号 having sum(数量)>=3				--having后面不能用列别名，只能写函数

select top 3 商品编号 ,sum(数量) 总计 from Sell group by 商品编号 order by sum(数量) desc			--查看前三个畅销产品
select top 3 商品编号 ,sum(数量) 总计 from Sell group by 商品编号 order by sum(数量) asc			--查看后三个滞销产品
select top 3 商品编号 ,sum(数量) 总计 from Sell group by 商品编号 order by 总计 desc			--order by 后面可以用列别名



--聚合函数
count(*/  all/distinct 列名)				--统计某列里数据的个数   *表示所有列
sum(列名)			--求和				
avg(列名)			--求平均           保留小数位-->外面加一个round( ,这里是保留的小数位数)
--avg(grade)求得平均数  round(avg(grade),2)，对平均数四舍五入保留两位小数
max()  min()		--最大值最小值

use Sales
go

select count(*) 总人数 from Employees		
select count(姓名) 总人数 from Employees 
select count(distinct 部门) 部门数目 from Employees	--统计部门数目


select sum(进货价) 总价 ,avg(进货价) 平均 ,max(进货价) 进货最大值 ,min(进货价) 进货最小值  from Goods
select 商品名称, 进货价, 零售价, 数量 , (零售价-进货价)*数量 盈利 from Goods
select sum((零售价-进货价)*数量 ) 总盈利 from Goods


--group by 只能出现聚合函数分组列 
--统计男女员工的人数
select 性别 ,count(性别) 人数 from Employees group by 性别 

--统计各部门人数
select 部门, count(部门) 各部门人数 from Employees group by 部门

--每个销售盈利
select 进货员工编号 , sum((零售价-进货价)*数量 ) 销售盈利 from Goods group by 进货员工编号
--销售冠军
select top 1 进货员工编号 , sum((零售价-进货价)*数量 ) 销售盈利 from Goods group by 进货员工编号 order by 销售盈利 desc
--筛选销售盈利大于两万的
select 进货员工编号 , sum((零售价-进货价)*数量 ) 销售盈利 from Goods group by 进货员工编号 having sum((零售价-进货价)*数量 )>=20000

--进货时间在2005-1-1之前的，进货价大于1000的
select * from Goods
select *from Goods where 进货时间<='2005-01-01' and 进货价<1000

--cumpute by
select * from Employees [compute] count (部门) 
select * from Employees order by 部门 compute count (部门) by 部门

select *,(零售价-进货价)*数量 盈利 from Goods order by 进货员工编号 compute sum((零售价-进货价)*数量 ) by 进货员工编号

连接查询

use Sales
go

select * from Goods
select * from Sell

--  内连接  （inner  join）
select * from Goods G inner join Sell S on  G.商品编号=S.商品编号
--如果内联之后，有列名重复，要使用该列时，往列名前+表名
select 零售价,S.数量 ,售货员工编号 from Goods G inner join Sell S on  G.商品编号=S.商品编号
select 零售价,S.数量 ,零售价*S.数量 销售额 ,售货员工编号 from Goods G inner join Sell S on  G.商品编号=S.商品编号

select '1301' 售货员工编号, sum(零售价*S.数量) 销售总额 from Goods G inner join Sell S on  G.商品编号=S.商品编号 where 售货员工编号='1301'
select '1302' 售货员工编号, sum(零售价*S.数量) 销售总额 from Goods G inner join Sell S on  G.商品编号=S.商品编号 where 售货员工编号='1302'
select '1303' 售货员工编号, sum(零售价*S.数量) 销售总额 from Goods G inner join Sell S on  G.商品编号=S.商品编号 where 售货员工编号='1303'

select 售货员工编号,sum(零售价*S.数量) 销售额 from Goods G inner join Sell S on G.商品编号=S.商品编号 group by 售货员工编号

--上面这行还可以这样写： 不用   inner  join  而用where代替
select 售货员工编号,sum(零售价*S.数量) 销售额 from Goods G , Sell S where G.商品编号=S.商品编号 group by 售货员工编号


-- 多表连接
--显示员工姓名  select 后面 只能 出现 分组列 和 聚合函数列，而新加的 姓名 列 不在两列中，所以报错，而把他加到分组列中就ok了
select 售货员工编号, 姓名,sum(零售价*S.数量) 销售额 from Goods G inner join Sell S on G.商品编号=S.商品编号 inner join Employees E on  S.售货员工编号=E.编号 group by 售货员工编号, 姓名		--姓名与编号对应，所以加到分组列中

--上面这行还可以这样写： 不用   inner  join 三张表用逗号代替，    条件on 用where代替  如果是多表相连接，则条件用and相连
select 售货员工编号, 姓名,sum(零售价*S.数量) 销售额 from Goods G , Sell S , Employees E 
where G.商品编号=S.商品编号 and S.售货员工编号=E.编号 group by 售货员工编号, 姓名

--				左外连接/右外连接/完整的外连接      左连接：左表是主表 ，右表是从表     有连接：左表是从表，右表是主表。
--外连接 （left join / right join / full join）  全写：left outer join   right outer join   full outer  join

select * from Employees e inner join Sell s on e.编号=s.售货员工编号		--内联
--外连：主表里的信息全显示。
--左外联，左边的是主表，全部显示。右边是从表，有相等的显示，没有的显示空值。
select * from Employees e left join Sell s on e.编号=s.售货员工编号 where 售货员工编号 is null			--横向筛选

select e.* from Employees e left join Sell s on e.编号=s.售货员工编号 where 售货员工编号 is null	--只显示Emplioyees表里的，售货员工编号为空的
select 编号,姓名 from Employees e left join Sell s on e.编号=s.售货员工编号 where 售货员工编号 is null

--右右连：右边的是主表，全部显示，左边是从表，有相等的显示，没有的显示空值。
select * from Sell s  right join Employees e on e.编号=s.售货员工编号

--全外连接：左右表地位相等
select * from Sell s  full join Employees e on e.编号=s.售货员工编号



--交叉连接  （cross join 笛卡尔积）   没有连接条件，但是可以用其他条件
--cross join后加条件只能用where,不能用on
--连接结果：两个表中所有的数据互相匹配，列数为：两表列数相乘
--只有select的话，select是sql中的输出语句。
select 11*13

--第一种方法
select * from Employees ,Sell
--第二种方法
select * from Employees cross join Sell
--性别为男的
select * from Employees cross join Sell where 性别=1
--按编号排序
select * from Employees cross join Sell order by 编号 desc


--分组统计之后再统计记录数
--4、统计选课门数大于等于5门的学生人数
select count(*) 超过5门的人数 from (select StuNo 学号,StuName 姓名  from V_StuCou group by StuNo,StuName having count(CouNo)>=5 ) 超过5门的人数
--select 列   from  (已经分组统计好的数据) 表别名
--这里要把查询好的语句嵌套在（）里，把它当成一个表，因为要当成表，所以为他起一个列别名

子查询、合并查询

use Sales
go
select * from Employees
select * from Goods
select * from Sell

select 部门 from Employees where 姓名='赵飞燕'
select 姓名 from Employees where 部门='采购部' and 姓名 != '赵飞燕'		 --!= 表示不等于
select 姓名 from Employees where 部门='采购部' and 姓名 <> '赵飞燕'    --  <>也表示不等于

--括号里的是子查询			左边的是父查询，查询时先执行括号里的，然后作为条件------>执行父查询
select 姓名 from Employees where 部门=(select 部门 from Employees where 姓名='赵飞燕') and  姓名 <> '赵飞燕'

--  使用连接查询   找做过销售的员工姓名
--内连
select distinct 售货员工编号, 姓名 from Employees E inner join Sell s on E.编号=S.售货员工编号 --使用distinct消除重复值
select distinct 售货员工编号, 姓名 from Employees E , Sell s where E.编号=S.售货员工编号	
--外连
select distinct 售货员工编号 ,姓名 from Employees E left join Sell S on E.编号=S.售货员工编号 where 售货员工编号 is not null
--交叉连接
select distinct 售货员工编号, 姓名 from Employees E cross join Sell S where E.编号=S.售货员工编号

--子查询
select 售货员工编号 from Sell --先查找销售过的员工的编号
--因为一个员工销售了很多商品，所以直接引用会出现以下错误
--此时用distinct 来消除重复值。
select distinct 售货员工编号 from Sell 

--select 姓名 from Employees where 编号 =(select distinct 售货员工编号 from Sell ) group by 姓名  出现以下错误：
--子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
--因为=后面的值必须是唯一的,所以这里要用in

select 编号, 姓名 from Employees where 编号 in (select distinct 售货员工编号 from Sell )

--没有销售过的员工
select 编号, 姓名 from Employees where 编号 not in (select distinct 售货员工编号 from Sell )


--存在子查询：EXISTS    exists   是否存在
select 编号, 姓名 from Employees where 编号 in (select distinct 售货员工编号 from Sell )
select 编号, 姓名 from Employees where exists (select * from Sell where 售货员工编号=Employees.编号)
--                                    这里的where后面是一个存在子查询


--if 条件表达式  语句块1 else 语句块2
if exists(select * from sysdatabases where name='Sales')
use Sales
go


--不存在子查询：NOT EXISTS    not exists  
select 编号, 姓名 from Employees where not exists (select * from Sell where 售货员工编号=Employees.编号)

--合并查询( union / intersect / except)
--				合  并 /   交  集   /	减  去
insert into 表名
select 各列值 union
select 各列值 union
select 各列值 union
select 各列值 union
go

use Sales
go

--合并    两个结果合并显示。
select 编号 ,姓名 from Employees where 编号 in(select distinct 售货员工编号 from sell)
union
select 编号 ,姓名 from Employees where 编号 NOT in(select distinct 售货员工编号 from sell)
--合并查询时，列数必须相同，列属性也要相同，如果两列所存储数据属性相同，列数序可以不同，但是不建议这样使用
--最好相匹配  列名可以改，但必须是第一条语句中的别名


--相交		做过销售的与没做过销售的相交  -------->空集
select 编号,姓名 from Employees where 编号 in(select distinct 售货员工编号 from sell)
intersect
select 编号 ,姓名 from Employees where 编号 NOT in(select distinct 售货员工编号 from sell)
 --全部人与没做过销售的相交，显示没做过销售的
 select 编号,姓名 from Employees
 intersect
select 编号,姓名 from Employees where 编号 NOT in(select distinct 售货员工编号 from sell)

--减去   全部减去做过销售的就是没做过的
select 编号,姓名 from Employees 
except
select 编号 ,姓名 from Employees where 编号 in(select distinct 售货员工编号 from sell)

--重复值销不消都没关系
select 编号,姓名 from Employees 
except
select 编号,姓名 from Employees inner join sell on  Employees.编号=Sell.售货员工编号

XK数据库的查询

use XK
go

select * from Department
select * from Class
select * from Student
select * from Course
select * from StuCou

--1、查看所有的班级信息
select * from Class

--2、查询共有多少个班级
select count(ClassNo) 班级数 from Class

--3、查询01年纪共有多少个班级
select count(ClassNo) 班级数 from Class where ClassNo like '2001%'

--4、查看部门编号为03的部门名称
select Departname from Department where DepartNo='03'

--5、查看系部名称中包含“工程”两个字的全名
select Departname from Department where Departname like '%工程%'

--6、查看“周二晚”上课的课程名称和教师
select CouName , Teacher from Course where SchoolTime like '周二晚%'

--7、查看“张，陈，黄”同学们的信息，姓名-->降序
select * from Student where StuName like '[张,陈,黄]%' order by StuName desc

--8、按部门统计课程平均报名人数，显示部门名称，平均报名人数
--convert(decimal(     5   ,     2  ),avg(列名)) 转换函数
--						 总位数,小数位数
--这里平均人数的显示结果有小数位，不会取。
select DepartName 部门名称 ,convert(decimal(5,0),avg(WillNum)) 平均报名人数 from Department D inner join Course C on D.DepartNo=C.DepartNo group by DepartName

--9、统计各个部门班级数，显示部门编号 部门名称，班级数量
select D.DepartNo 部门编号, DepartName 部门名称,  count(ClassNo) 班级数量 from Department D , Class C where D.DepartNo=C.DepartNo group by D.DepartNo ,DepartName

--10、统计班级数大于等于5个的部门 显示编号，名称，班级数量
select D.DepartNo 部门编号, DepartName 部门名称, count(ClassNo) 班级数量 from Department D , Class C where D.DepartNo=C.DepartNo group by D.DepartNo ,DepartName having count(ClassNo)>=5

--11、查询“甘蕾”选修的课程名，学分，上课时间，志愿号，并且按志愿号升序  查询
select CouName , Credit ,SchoolTime ,WillOrder from Student Sd , StuCou SC , Course C where Sd.StuNo=SC.StuNo and SC.CouNo=C.CouNo and StuName='甘蕾' order by WillOrder

--12、查询“00电子商务”的选修报名情况。显示：学号，姓名，课程编号，课程名称，志愿号，学号升序，志愿号升序
--select ClassName from Class where ClassName='00电子商务' 
select S.StuNo ,StuName, SC.CouNo , CouName,WillOrder from Student S , StuCou SC , Course C 
where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.ClassNo= (select ClassNo from Class where ClassName='00电子商务' ) order by StuNo,WillOrder asc

--13、查询  00电子商务 
--这个麻烦
select S.StuNo ,StuName, SC.CouNo ,CouName,WillOrder from Student S , StuCou SC , Course C 
where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.ClassNo= (select ClassNo from Class where ClassName='00电子商务' ) 
order by S.StuNo,WillOrder asc compute count (S.StuNo)  by S.StuNo

--修改之后的
select S.StuNo ,StuName, SC.CouNo ,CouName,WillOrder from Class Cl,Student S , StuCou SC , Course C 
where Cl.ClassNo=S.ClassNo and S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and  ClassName='00电子商务'  
order by S.StuNo,WillOrder asc compute count (S.StuNo)  by S.StuNo

--14、按部门统计各系最少/最多/平均/总  报名人数。汇总显示所有的报名人数，要求平均报名人数保留两位小数
--avg(grade)求得平均数  round(avg(grade),2)，对平均数四舍五入保留两位小数
select  D.DepartNo 部门编号,min(WillNum) 最少报名人数 ,max(WillNum) 最多报名人数,round(avg(WillNum) ,2)平均报名人数,sum(WillNum) 报名总人数 from Department D,Course C where D.DepartNo=C.DepartNo group by D.DepartNo

--15、查找和“陈婷”选课门数相同的同学
--select count(CouNo) 选修门数 from Student S ,StuCou SC where S.StuNo=SC.StuNo and StuName='陈婷' group by StuName 

select StuName 姓名,count(CouNo) 选修门数 from Student S ,StuCou SC where S.StuNo=SC.StuNo group by StuName having count(CouNo) =(select count(CouNo) 选修门数 from Student S ,StuCou SC where S.StuNo=SC.StuNo and StuName='陈婷' group by StuName )

--16、左连 查询没有选课的学生姓名   没选的就是CouNo为空的
select  S.StuNo 学号 ,StuName 姓名 from Student S left join StuCou SC on S.StuNo=SC.StuNo where CouNo is null

--17、用in子查询查找选课的学生姓名
--select distinct StuNo from StuCou 
select StuNo , StuName  from Student where StuNo in (select distinct StuNo from StuCou  )

--18、用exists  子查询查找选课学生姓名
--select StuNo from StuCou where StuNo=Student.StuNo
select StuNo , StuName  from Student where exists (select StuNo from StuCou where StuNo=Student.StuNo )

--19、利用合并查询except查找没有选课的学生姓名
select StuNo , StuName  from Student
except
select StuNo , StuName  from Student where StuNo in (select distinct StuNo from StuCou  )

--20 合并查询union合并16、17题结果，验证是否包括所有学生。（共180个学生，观察查询结果的列名选择）
select  S.StuNo 学号 ,StuName 姓名 from Student S left join StuCou SC on S.StuNo=SC.StuNo where CouNo is null
union
select StuNo , StuName  from Student where StuNo in (select distinct StuNo from StuCou  )