当前位置: 首页>后端>正文

sql server 查询有多少个人在连接 sql查询总共多少条数据

查询

对表的操作—顺序

计算列
  • 查询重点(顺序):理顺SQL语句执行顺序。到底先执行哪个后执行哪个?先执行from后执行select…先执行from是把整个表的每一条数据拉出来,然后用select选择判断到底对哪个值进行输出,如果是字段就把字段列输出;如果是字段列有别名,就把别名输出;如果是多个字段,就多个字段列输出;如果是常量值,就把无意义的值输出,但可看出表内一共有多少行
-- select选择
-- * 表示所有
-- from emp:从emp中查询
select * from emp;

select empno,ename from emp;

select ename,sal from emp;

select ename,sal * 12 as "年薪" from emp; 
			-- as可以省略,记住“年薪”不要写成'年薪',也不要写成 年薪 
			
select 5111111 from emp;
			-- 输出的行数是emp表的行数,每行只有一个字段,值是5
			
select ename,sal * 12 as "年薪", sal as "月薪",job from emp; 
			--  逗号分隔(输出规则:emp -> ename -> sal -> sal*12 -> sal -> 月薪 -> job)
distinct

[不允许重复的]-- distinct用法

select deptno from emp;
		-- 访问emp,找到deptno,找到一个输出一个
select distinct deptno from emp;
		-- 过滤重复值
		
select distinct comm from emp;
		-- 有空值
		-- distinct也可以过滤掉重复的null
		
select distinct comm,deptno from emp;
		-- 整体过滤(过滤comm,deptno同时重复的,不单过滤)
		
-- select deptno,distinct comm from emp; 
		-- erro 逻辑冲突
between
-- between (比特翁)
-- 查找工资在1500到3000之间(包括1500和3000)的所有员工信息
select *from emp
	where sal >= 1500 and sal <= 3000			-- 挨个遍历
	
-- 等价于
select *from emp
	where sal between 1500 and  3000

-- 查找工资在小于等于1500或大于等于3000之间的所有员工信息
select *from emp
	where sal <= 1500 or sal >= 3000
	
-- 等价于
select *from emp
	where sal not between 1500 and  3000
in [属于若干个孤立的值]
-- in
select *from emp where sal in (1500,3000,5000);
-- 等价于
select *from emp
	where sal = 1500 or sal = 3000 or sal = 5000

select *from emp where sal not in (1500,3000,5000);
		-- 找sal既不是1500,也不是3000,也不是5000
-- 等价于
select *from emp
	where sal <> 1500 and sal <> 3000 and sal <> 5000
	-- 数据库中不等于有两种表示: !=  <>(推荐使用)
	-- 对“或”取反是“且”,反之也成立
top
-- top用法
-- 分页查熊也会用到top,比如一页显示10个,显示若干页(网页搜索)
select * from emp;

select top 2 * from emp;

select top 15 percent * from emp;
		-- 前15%,小数点上取整
test : 把工资在1500 到3000之间(包括)的工资最高的前四个人降序输出
-- 执行顺序 from -> where -> order by -> top 4*
select top 4*
	from emp
	where sal between 1500 and 3000 
	order by sal desc		-- 降序输出
null

靠!

出毛病了!一点执行就弹出该窗口

奥,这玩意其实就是显示方式,原来是网格,现在是文件。只需要点查询按钮–>将结果保存

-- null
-- 输出奖金非空的人
select *from emp where comm <> null;	-- 不行
-- 为啥?
-- 因为null 不能参与 <> 、!= 运算

-- 1、null可以参与is、not is运算
-- 0和null 不一样
select * from emp where comm is null;

select * from emp where comm is not null;

-- 输出每个员工的姓名、年薪(包含奖金)
select *from emp;
select ename, sal * 12+comm as "年薪" from emp;	-- 一个不是值的东西和一个值做数学运算,最后还不是一个值
-- 2、null不能参与任何数学运算,应该怎么做?
-- 自己试试(用in,不大行)

select *from emp 
	 select sal * 12+comm as "年薪" from emp where comm is not null;
select ename, sal * 12+comm as "年薪" from emp;	-- 一个不是值的东西和一个值做数学运算,最后还不是一个值
-- 2、null不能参与任何数学运算,应该怎么做?

-- isnull (comm,0) : 如果comm是null,就返回0,否则返回具体的值
select ename ,sal * 12 + ISNULL (comm,0) as "年薪" from emp;	-- 这样就行了
order by
-- order by 【以某个字段排序】
select *from emp order by sal;		-- 默认是升序排序
select *from emp order by deptno,sal;	-- order by deptno,sal:按组合排序吗?NO,是先按deptno排,如果deptno相同了,再排sal(优先级问题)

select *from emp order by deptno desc,sal;-- 有两种可能,一种是前面降序后面也降序,另一种是前面降序,后面还是按默认的升序
-- 结果:对后面的没有影响
-- 结论:order by a desc,b,c,d; desc只对a有影响

select *from emp order by deptno,sal desc;	
---- 也不会
-- 先按deptno升序,如果相同,再按sal降序
-- 还是讲究优先级问题

-- 如果不指定排序标准,默认升序(asc),默认可以不写
-- 为某一个字段指定排序,都不会影响其他字段的排序,并且优先级还是先来后到
模糊查询
-- 模糊查询
-- 搜索关键字
-- 格式:
	-- select 字段的集合(某个或几个字段) from 表名 where 某个字段的名字 like 匹配的条件
	-- 匹配的条件通常有通配符:
	
-- 1、% :任意0个或多个字符
select * from emp 
	where ename like '%S%'		-- 只要含有S就输出
select * from emp 
	where ename like 'A%'			-- 首字符只要有A就输出
select * from emp 
	where ename like '%A'			-- 尾字符只要有A就输出
		
-- 2、 _【下划线,不是减号】
select * from emp where ename like '_A%';  -- 第二个字母是A就输出
-- _A:表示第二个字母是A且到字母A后,该单词就结束了

-- 3、[a-f]
-- a到f中的任意单个字符,只能是a,b,c,d,e,f
select * from emp where ename like '_[A-F]%'; -- 把第二个字母是A-K的输出

-- 4、[a,f]
-- a或f
select * from emp where ename like '_[A,f]%'

-- 5、[^a-c]	^:(异或)取反,不是a也不是f
select * from emp where ename like '_[^A-F]%';
-- 把ename中第二个字母不是A,B,C,D,E,F的输出
	--注意:
	-- 1、匹配的条件必须得用单引号,双引号表示对象名称,单引号表示字符串 
	-- 3、通配符和要查找的字符冲突问题(比如我要查找%、_)
select * from emp where ename like '%\%%' escape '\';		-- escape:把\当成转义字符看待,所以在\后再添加%,就代表是一个普通的%
															-- 当然这个'\',可以换成任何字符,比如’m‘ : escape 'm',一般用\
															-- '%\%%':第一个%表示任意0个或多个字符;\后第一个%就是个普通的%,最后一个%表示后面任意0个或多个字符
聚合函数
-- 聚合函数的分类
-- max()
-- min()
-- avg()
-- count()	求个数
select count(*) from emp;	-- 返回emp表所有记录的个数
select count(deptno) from emp;   -- 返回值是14,说明deptno中重复的记录也被当做有效记录

select count(distinct deptno) from emp;	-- 返回字段不重复个数且非空的记录
select COUNT(comm) from emp;		-- 返回值是4,null值不计数

-- 总结
-- 返回字段非空值的个数,重复记录也会被当做有效记录

-- 判断如下语句是否正确
select max (sal) "最高",MIN (sal) "最低",COUNT(*) "人数" from emp;    -- 正确
select MAX (sal) ,LOWER(ename) from emp;			-- 错误(单行函数与多行函数不能通用)
group by
  • 我想看看部门的编号、部门的人数、部门的平均工资,看看哪个高
    格式:
group by  字段组合

功能:

把表中的记录按照字段分成不同组。

实例:

-- group by 分组
use scott
select deptno from emp;

-- 输出每个部门的编号和该部门的平均工资
select deptno,avg(sal) as "部门平均工资" from emp			-- 3、deptno,avg(sal)这俩是不是必须具有相关性	--必然是
	group by deptno;										
	
select deptno,avg(sal) as "部门平均工资",ename from emp		-- 错误:加上ename不行
	group by deptno;										-- 已经分好组了,1、依据什么分的组2、直接过滤重复的?--内部实现好了
	-- 总结:
	-- group by deptno之后,select 中只能出现分组后的整体信息,不能出现组内的详细信息


select deptno,job 
	from emp
	group by deptno,job; -- 先按deptno分组,遇到相同的再看job,最后得到一个小组


select deptno,job,AVG(sal) 
	from emp
	group by deptno,job	-- 部门是deptno的,从事job的人,这些人的平均工资是avg(sal)
	order by deptno;
	
select deptno,job,AVG(sal) "平均工资",COUNT(*) as "个数",SUM(sal) as "总和"		-- COUNT(*)是整体信息
	from emp												
	group by deptno,job	
	order by deptno desc;
-- NULL也可以成一组
select comm ,COUNT(*)from emp
	group by comm;

注意:

group by a,b,c的用法:

		--先按a分组,a相同再按b分组,b相同再按c分组

		--最终统计的是最小分组的信息
--使用 group by 之后,select中只能出现分组后的整体信息,不能出现组内的详细信息
--只有经过 group by分组的整体信息,才能被select
having

先分组再过滤,对分组之后的信息进行过滤

-- having
-- 先分组再过滤,对分组之后的信息进行过滤

-- 输出部门平均工资大于2000的部门的部门编号	部门的平均工资
-- 首先你得把部门分类,再找到平均工资大于2000的部门

select deptno,avg(sal) as "平均工资" from emp
	group by deptno
	having AVG(sal) > 2000		-- 对分组后的信息再过滤

select deptno,avg(sal) as "平均工资" from emp
	group by deptno
	having deptno > 20		-- 对分组后的信息再过滤
	
select deptno,avg(sal) as "平均工资" from emp
	group by deptno
	having COUNT(*) > 3		-- 对分组后,有组内个数>3的组吗? 不对!
							-- 它是统计总个数吧
-- having 与 where
-- 把姓名不包含A的所有员工按部门编号分组;
-- 思路 :先把带A的排除,然后分组,然后求平均工资,过滤两次:1、不包含A   2、平均工资大于2000
-- 统计输出部门平均工资大于2000的部门的部门编号	部门的平均工资
select  deptno,AVG(sal) "平均工资"
	from emp
	where ename not like '%A%'
	group by deptno
	having avg(sal) > 2000
-- 把工资>2000所有员工按部门编号分组;
-- 统计输出部门平均工资大于3000的部门的部门编号	部门的平均工资
select deptno ,avg(sal) as "平均工资"
	from emp
	where sal > 2000			-- 对原始记录过滤
	group by deptno
	having AVG(sal) > 3000		-- 对分组后的记录过滤

– 所有参数的顺序是不允许变化的

总结:

  • having子句用来对分组后数据进行过滤,常与group by 连用
  • 如果没有使用group by,但使用了having,则意味着having把所有记录当做一组来进行过滤

连接查询

定义:

分类:

内连接

– 连接查询
– 定义
– 将两个表或两个以上的表以一定的连接条件连接起来,从中检索满足条件的数据

– 内连接(重难点)
– 让你把员工姓名和部门名称输出

-- 1、select ... from A表 ,B表的用法
-- emp 14行8列,dept 5行3列(笛卡尔积)
-- 结果70行,11列(行数是乘积,列数是之和)
-- A表的每条记录和B表的第条记录拼接
-- A表的每条记录和B表的每条记录连接,B表的每条记录和A表的每条记录连接,
select *from emp,dept
-- 2、select ... from A,B where ... 的用法
-- 产生的笛卡尔积用where中的条件进行过滤
select * 
	from emp,dept
	where EMPNO = 7369
		-- 输出5行
-- A 撞 B 昂
-- 3、select ... from A join B on ... 的用法
-- from emp join dept on 用法一般会给表起别名,别名的话用"",不用也行
-- 输出11列70行
select E.ename as "员工姓名",D.dname as "部门名称"	-- select得加个具体的名字
	from emp E
	join dept D   -- join表连接
	on 1 = 1	  -- on 是连接条件,on不能省,有join就有on

-- 输出11列14行
select E.ename as "员工姓名",D.dname as "部门名称"	-- select得加个具体的名字
	from emp E
	join dept D   -- join表连接
	on E.deptno = D.deptno	  -- on 是连接条件,on不能省,有join就有on

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pHj3y5Bu-1641436524648)(…/image_t/image-20211206194543148-16389635964811.png)]

-- 4、select ... from A,B where ...
-- 与
--	  select ... from A join B on ...
select E.ename,D.dname
	from emp E,dept D
	where E.deptno = D.deptno
-- 等价于
select E.ename,D.dname
	from emp E join dept D
	on E.deptno = D.deptno
5、select ... from A,B where ...   SQL 92	
	-- 等价于
	select ... from A join B on ... SQL 99
	-- 推荐使用99

– 把工资大于2000的员工的姓名和部门和工资的等级输出
– SQL99标准

如何把三个表连接在一起?

-- 1、首先emp表的deptno和dept的deptno连接,成一张表了,那如何用**这个表**去连接**第三个表**?

-- 用emp表的sal连接第三个表的lowsal;再用sal连接第三个表的highsal。

-- SQL99标准

-- 三个表连接,350行14列
select *
	from emp E join dept 
	on 1 = 1		-- 两张表合成一张表了
	join SALGRADE S
	on 1 = 1		-- 三张表合成一张表了
	
-- SQL99标准
select E.ename,D.dname,S.GRADE
	from emp E 
	join dept D 
	on E.deptno = D.deptno		-- 两张表合成一张表了(为啥二表合体的东西也没个名字,就能和新表join)
	join SALGRADE S
	on E.sal >= S.LOSAL and E.sal <= S.HISAL		-- 三张表合成一张表了
	where E.sal > 2000
	
-- SQL92标准
select *
	from emp E,dept D,SALGRADE S
	where E.deptno = D.deptno and E.sal >= S.LOSAL and E.sal <= S.HISAL and E.sal > 2000

第一题:

-- 求出员工的姓名、部门编号、薪水和薪水等级

select E.ename "姓名",E.deptno "部门编号",E.sal "薪水",S.GRADE "薪水等级"
	from emp E 
	join SALGRADE S
	on sal >= S.LOSAL and sal <= S.HISAL

第二题:

-- 查找出每个部门的编号、该部门所有员工的平均工资、平均工资的等级
-- 先连接工资表,这时候会出现5列,我只需要输出1/2/3列

-- 每个部门的平均工资
select T.部门编号 "部门编号",T.平均工资 "平均工资",S.GRADE "工资等级"
	from SALGRADE S join(		-- 虚表要用()
		select deptno "部门编号", AVG(sal) "平均工资"
		from emp E
		group by deptno
	) T
	on T.平均工资 between S.LOSAL and S.HISAL
	

-- select E.deptno "部门编号",AVG(E.sal) "平均工资",S.GRADE "平均工资等级"
-- from emp E
-- join SALGRADE S
-- on AVG(E.sal) > S.LOSAL and AVG(E.sal) < S.HISAL

第三题:

-- 三、查找出每个部门的编号、部门名称、该部门所有员工的平均工资、平均工资的等级
select T.部门编号 "部门编号",D."dname",T.平均工资 "平均工资",S.GRADE "工资等级"
	from SALGRADE S join(		-- 虚表要用()
		select deptno "部门编号", AVG(sal) "平均工资"
		from emp E
		group by deptno
	) T on T.平均工资 between S.LOSAL and S.HISAL
	join dept D 
	on D.deptno = T.部门编号

第四题:

-- 四、输出emp表所有领导的信息
select *
	from emp 
	where EMPNO in (select mgr from emp)
	
-- 输出emp表所有非领导的信息
select *
	from emp
	where EMPNO not in (select mgr from emp)
-- in 与null的组合带来的问题

select *
	from emp
	where EMPNO not in (
	select mgr from emp where mgr is not null)

第五题:

-- 求出平均薪水最高的部门的编号和部门的平均工资

select top 1 D.deptno "部门编号",T.avg_sal "平均工资"
	from dept D join(
	select AVG(sal) "avg_sal",E.deptno "部门编号"
		from emp E
		group by deptno
		) T on D.deptno = T.部门编号
	
-- 等价于
select top 1 AVG(sal) "avg_sal",E.deptno "部门编号"
		from emp E
		group by deptno
		order by avg(sal) desc

查询顺序

select top ...
	from A
	inner join B
	on ....
	join C
	on ....
	where .....
	group by ...
	having .....
	order by ...
  • on 中既可以写连接条件,也可以写过滤条件,但不推荐,应该分开写,on中只写连接条件,where中写过滤条件
-- 把工资大于1500的所有的员工按部门分组   把部门的平均工资大于2000的最高的前2个,按升序输出部门编号,部门名称,部门平均工资等级

select T.*,S.GRADE,D.dname
	from dept D
	join (
		select top 2 E.deptno,AVG(E.sal) avg_sal
		from emp E
		join dept D
		on E.deptno = D.deptno
		join SALGRADE S 
		on E.sal between S.LOSAL and S.HISAL
		where E.sal > 1500
		-- 问:按某个表的某个字段分组后,已连接的其他表的字段都无法使用?加上D.dname,E.sal就能用了	
		group by E.deptno	-- 按照部门分组了,没有dename
		having AVG(E.sal) > 2000
		order by AVG(E.sal) desc
	) T 
	on D.deptno = T.deptno
	join SALGRADE S
	on T.avg_sal between S.LOSAL and S.HISAL

外连接

-- 不仅返回满足连接条件的部分,还返回部分不满足连接条件的元素
-- 内连接:如果前面的后面的不匹配,则不输出
-- 左外连接:以左表为中心,如果左边的某个记录在右边找不到匹配的行,也会有一行输出,输出的东西是空,左边输出左表第一行内容,右边全部输出NULL
-- 左表只要有信息,肯定输出,如果对应的右表没信息,输出空
select * 
	from emp E
	left join dept D
	on E.deptno = D.deptno
完全连接
-- 完全连接
-- 输出的结果包含三部分:
-- 1.两个表中匹配的所有行记录
-- 2.左表中那些在右表中找不到匹配的行的记录,这些记录的右边全为空
-- 3.右表中那些在左表中找不到匹配的行的记录,这些记录的左边全为空

交叉连接

生成没有过滤的笛卡尔积。

自连接

– 自连接(自交)
– 定义:一张表自己和自己连接起来查询数据
– 例子:不准用聚合函数 求薪水最高的员工的信息

– 能用聚合函数
select *
from emp
where sal = (select max(sal) from emp)

– 自连接

-- 自连接(自交)
	-- 定义:一张表自己和自己连接起来查询数据
	-- 例子:不准用聚合函数  求薪水最高的员工的信息

-- 能用聚合函数
select *
	from emp 
	where sal = (select max(sal) from emp)
-- 自连接
select * 
	from emp 
		where empno not in 
		(
			select distinct E1.EMPNO 
			from emp E1
			inner join emp E2	
			on E1.sal < E2.sal	-- 把E1所有的元素依次与E2进行连接,最后只剩下最高的没连接								  (因为E2没有比它大的)
		)

SQL例题 :

联合

-- 用联合
-- 表和表之间的数据以纵向的方式连接
-- 以前讲的都是横向的
select E1.ename "员工姓名",E1.sal "工资", E2.ename "上司姓名"
	from emp E1
	join emp E2
	on E1.mgr = E2.EMPNO
union
select ename,sal,'已是最大boss' from emp where mgr is NULL
-- 注意:若干个slect 语句要联合成功的话,必须满足的两个条件
	-- 1、这若干个select子句输出的字段数必须是相等的
	-- 2、这若干个select子句输出字段的类型至少是兼容的

分页查询

-- 分页查询
-- 输出工资最高的前三个员工的所有信息
-- 工资从高到低排序,输出工资第4-6的员工信息
-- 工资从高到低排序,输出工资第7-9的员工信息
--  ...
select top 3 * from emp order by sal;	-- 先执行order by,后执行top

-- 工资从高到低排序,输出工资第4-6的员工信息
select top 3 *
	from emp
	where EMPNO not in (select top 3 empno from emp order by sal desc)
	order by sal desc
	
-- 工资从高到低排序,输出工资第7-9的员工信息
select top 3 *
	from emp
	where EMPNO not in (select top 6 empno from emp order by sal desc)
	order by sal desc
	
-- 总结
-- 假设每页显示n条记录,当前要显示的是第n页
select top n *
	from A
	where A_id not in (select top (n - 1) * A_id from emp)

ame,sal,‘已是最大boss’ from emp where mgr is NULL
– 注意:若干个slect 语句要联合成功的话,必须满足的两个条件
– 1、这若干个select子句输出的字段数必须是相等的
– 2、这若干个select子句输出字段的类型至少是兼容的

**分页查询**

```sql
-- 分页查询
-- 输出工资最高的前三个员工的所有信息
-- 工资从高到低排序,输出工资第4-6的员工信息
-- 工资从高到低排序,输出工资第7-9的员工信息
--  ...
select top 3 * from emp order by sal;	-- 先执行order by,后执行top

-- 工资从高到低排序,输出工资第4-6的员工信息
select top 3 *
	from emp
	where EMPNO not in (select top 3 empno from emp order by sal desc)
	order by sal desc
	
-- 工资从高到低排序,输出工资第7-9的员工信息
select top 3 *
	from emp
	where EMPNO not in (select top 6 empno from emp order by sal desc)
	order by sal desc
	
-- 总结
-- 假设每页显示n条记录,当前要显示的是第n页
select top n *
	from A
	where A_id not in (select top (n - 1) * A_id from emp)



https://www.xamrdz.com/backend/3b21939830.html

相关文章: