数据库Sql Server操作——查询

最新推荐文章于 2024-08-21 14:15:23 发布

770-0

最新推荐文章于 2024-08-21 14:15:23 发布

阅读量1.1k

点赞数 3

文章标签：数据库

本文链接：https://blog.csdn.net/m0_61442114/article/details/134466236

版权

9.聚合函数——多行记录返回一个值，通常用于统计分组的信息

11.HAVING——对分组之后的信息进行过滤

SQL执行顺序

12.连接查询

（1）内连接【重点】—— (inner) join ... on ...

（2）外连接 —— left/right join ... on ...

（3）完全连接 —— full join ... on ...

（4）交叉连接 —— cross join ...

二.查询

例子：test1数据库

dept表

emp表

salgrade表

1.计算列

select * from emp;
-- * 表示所有的列
-- from emp 表示从emp表查询

select empno,ename from emp;
select ename,sal from emp;

select ename,sal,sal*12 as "年薪" from emp;
select ename,sal*12 as "年薪",sal "月薪",job from emp;

as 可以省略，"年薪" 最好用双引号引用，因为单引号和直接写可能不可移植。

select 5 from emp;
select 5;

2.DISTINCT——消除重复值，去重

select deptno from emp;
--ok, 14行记录（所有记录），会产生大量重复
select distinct deptno from emp;
--ok, distinct 会过滤掉重复的 deptno值
select comm from emp;
select distinct comm from emp;
--ok, distinct 也会过滤掉重复的 NULL
select comm,deptno from emp;
select distinct comm,deptno from emp;
--ok, 把 comm 和 deptno看做一个组合，整体过滤
select deptno,distinct comm from emp;
--error, 会产生逻辑冲突

3.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;

4.IN——属于若干个孤立的值

select * 
	from emp 
	where sal in (1500,3000);
--等价于
select *
	from emp
	where sal = 1500 or sal = 3000;

select *
	from emp
	where sal not in (1500,3000);
--等价于
select *
	from emp
	where sal <> 1500 and sal <> 3000;

数据库中不等于有两种表示： <> 和 !=

5.TOP——最前面的若干个记录

select top 2 * from emp;
--把emp表 最前面的2行 记录的所有字段输出
select top 15 percent * from emp;
--把emp表 前面15% 的记录的所有字段输出，若不为整数向上取
--例如：14的15%为2.1 --> 取前3

例如：把1500到3000之间工资最高的前4个人的信息输出

select top 4 * 
	from emp
	where sal between 1500 and 3000
	order by sal desc;
--order by 排序，默认升序，desc降序

6.NULL——没有值，空值

NULL 不能参与 <> 、!= 、= 运算；

NULL 可以参与 is 、 is not 运算。

【注意】零和 NULL 是不一样，NULL表示空值、没有值，零表示一个确定的值。

例如：输出奖金非空的员工信息

select * from emp where comm <> null;--error
select * from emp where comm != null;--error

select * from emp where comm is not null;--ok

例如：输出每个员工的编号、姓名、年薪（包含奖金）、comm假设为年终奖。

select empno,ename,sal*12+comm "年薪" from emp ;
--error, 有些comm是空值，NULL参与运算的结果都为NULL
--NULL 不能参与任何数学运算
--改为
select empno,ename,sal*12+isnull(comm,0) "年薪" from emp ;

NULL参与运算的结果都为NULL

7.ORDER BY——以某个字段排序

若不指定排序的标准，则默认是升序排序。
升序用 asc 表示，默认可以不写；降序为 desc 。
为一个字段指定排序标准，不会对另一个字段产生影响。
强烈建议为每个字段都指定排序的标准。

order by a desc, b, c, d

desc 只对 a 产生影响，不会对后面的 b 、c、d 产生影响

order by a, b desc

desc 只对 b 产生影响，未对 a 产生影响

select * from emp order by sal;
--默认是升序排序
select * from emp order by deptno,sal;
--先按照 deptno 升序排序，如果它相同，则按照 sal 升序排序
select * from emp order by deptno desc,sal;
--先按 deptno 降序排序，如果 deptno 相同，再按照 sal 升序排序
select * from emp order by deptno,sal desc;
--先按 deptno 升序排序，如果 deptno 相同，再按照 sal 降序排序

8.模糊查询

格式

SELECT 字段的合集 FROM 表名

WHERE 某个字段 LIKE 匹配条件

匹配的条件通常有通配符

通配符

% 百分号：任意0个或多个字符
_ 下划线：任意单个字符
[a-f] ： a 到 f 中的任意单个字符
[a,f] ：a 或 f
[^a-c] ：不是 a 到 c 中的任意字符

例子

select * from emp where ename like '%A%';
--ename 只要含字母A就输出
select * from emp where ename like 'A%';
--ename 只要首字符是字母A就输出
select * from emp where ename like '_A';
--ename 只有两位字符，第二个字符是字母A
select * from emp where ename like '_[A-S]%';

注意

（1）匹配的条件必须用单引号括起来，不能省略，也不能改用双引号。

双引号表示对象的名称或别名；单引号表示字符串。

（2）通配符作为不同字符使用的问题

escape '/' 表示把 / 字符当作转义字符的标志

select * from student where name like '%/%%' escape'/';
--把 name 中含有 % 的输出
select * from student where name like '%/_%' escape'/';
--把 name 中含有下划线的输出

9.聚合函数——多行记录返回一个值，通常用于统计分组的信息

函数的分类

单行函数：每行返回一个值；
多行函数：多行返回一个值。聚合函数是多行函数。

select lower(ename) from emp;
--返回多行，lower()大写转换成小写
select sal from emp;
select max(sal) from emp;
--返回一行

聚合函数的分类

max() ：最大值
min() ：最小值
avg() ：平均值
count() ：求个数

select count(*) from emp;
--返回emp表所有记录的个数——14
select count(deptno) from emp;
--返回值14（一共14个记录），说明deptno重复的记录也被当做有效记录
select count(distinct deptno) from emp;
--3个，不重复
select count(comm) from emp;
--返回值是4，因为有4个非空记录

count(*) 返回表中所有记录的个数；
count(字段名) 返回字段值非空的记录的个数，重复的值也会被当做有效记录；
count(distinct 字段名) 返回字段不重复并且非空的记录的个数。

注意的问题

判断如下sql语句是否正确：

单行函数和多行函数不能混用

select max(sal), min(sal), count(*) from emp;
--ok
select max(sal), lower(ename)from emp;
--选择列表中的列 'emp.ename' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
--单行函数和多行函数不能混用

10.GROUP BY——分组

格式：group by 字段合集

功能：把表中的记录按照字段分成不同的组

--查询不同部门的平均工资
select deptno ,avg(sal) as "部门平均工资"
	from emp
	group by deptno;

注意：

理解 group by a, b, c 的用法

先按 a 分组，如果 a 相同，再按 b 分组，如果 b 相同，再按 c 分组，最终计算的是最小分组的信息。

一定要明白下列语句错误的原因：

使用了 group by 之后 select 中只能出现分组后的整体信息，不能出现组内的详细信息。

select deptno,avg(sal) as "部门平均工资",ename
	from emp
	group by deptno;
--选择列表中的列 'emp.ename' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

select deptno, ename
	from emp
	group by deptno;
--选择列表中的列 'emp.ename' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

select deptno, job, sal
	from emp
	group by deptno, job;
--选择列表中的列 'emp.sal' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

11.HAVING——对分组之后的信息进行过滤

作用类似于 WHERE

（1）HAVING 是用来对分组之后的数据进行过滤，因此使用 HAVING 时通常会先使用 GROUP BY；

（2）如果没有使用 GROUP BY 但是使用了 HAVING，则意味着 HAVING 把所有的记录当作一组来进行过滤，极少用；

select count(*) 
	from emp 
	having avg(sal)>2000;

（3）HAVING 子句出现的字段必须是分组之后的组的整体信息，HAVING 子句不允许出现组内的详细信息。

（4）尽管 SELECT 字段可以出现别名，但是 HAVING 子句中不能出现字段的别名，只能使用字段最原始的名字，因为 HAVING 的执行顺序在 SELECT 之前。

（5）HAVING 和 WHERE 的异同

相同：

都是对数据过滤，只保留有效的数据；

WHERE 和 HAVING 一样，都不允许出现字段的别名，只允许出现原始字段名。（不同的软件效果不同）

不同：

WHERE 是对原始的记录过滤，HAVING 是对分组之后的记录过滤；

WHERE 必须写在HAVING 的前面，顺序不可颠倒，否则运行出错。

select deptno, avg(sal)"平均工资", count(*)"部门人数", max(sal)"部门最高工资"
	into emp_2 --把查询结果放入表 emp_2 中
	from emp
	where sal>2000 --where对原始的记录过滤
	group by deptno
	having avg(sal)>3000 --对分组后的记录过滤

select * from emp_2;

SQL执行顺序

FROM --> JOIN --> ON --> WHERE --> GROUP BY --> AVG、SUM --> HAVING --> SELECT --> DISTINCT --> GROUP BY --> LIMIT

12.连接查询

定义：将两个或两个以上的表以一定的连接条件连接起来，从中检索出满足条件的数据。

分类：

（1）内连接【重点】—— (inner) join ... on ...

1）select ... from A, B【笛卡尔积】

产生结果：行数 = A行数 * B行数；列数 = A列数 + B列数

或者说

把 A(B)表中的每一条记录和 B(A)表的每一条记录组合在一起，形成的是一个笛卡尔积。

例如：

emp表为A表，dept表为B表。
select * from emp,dept;
--产生 70行 * 11列 （emp表：14行*8列，dept表：5行*3列）

2）select ... from A, B where ...

产生结果：对 select ... from A, B 产生的笛卡尔积用 where 中的条件进行过滤。

例如：
select * from emp,dept where empno = 7369;
判断下列语句输出的结果：
--emp表：14行*8列，dept表：5行*3列
select * 
	from emp, dept
	where emp.deptno = 10;
--输出的行数肯定是dept表行数的倍数

select * 
	from emp, dept
	where dept.deptno = 10;
--输出的行数肯定是emp表行数的倍数

3）select ... from A (inner) join B on ...

内连接为inner join ... on ... ，inner可省略。

join表示连接，on后接连接的条件，on不可省略。

例子：

--下面的两个语句输出的结果相同

select *
	from emp "E" --"E" 为 emp 的别名
	join dept "D" --"D" 为 dept 的别名
	on 1=1;	--连接条件始终为真

select *
	from emp "E",dept "D";

--判断下面语句的是否正确
select dept.deptno
	from emp "E" 
	join dept "D"
	on 1=1;	
--error 列名"deptno"不明确
--因为dept表和emp表都有 deptno 这个列，所以要写成 "E".deptno或"D".deptno

--考虑下列语句的输出结果
--14行 11列
select *
	from emp "E"
	join dept "D"
	on "E".deptno = "D".deptno;

4)SQL92标准和SQL99标准的区别

select ... from A, B where ... 是SQL92标准

select ... from A (inner) join B on ... 是SQL99标准

输出的结果相同

例子：
select * 
	from emp, dept
	where dept.deptno = 10;
--改为SQL99标准实现
select * 
	from emp
	join dept
	on 1 = 1
	where dept.deptno = 10;
推荐使用SQL99标准，原因：

a.SQL99更容易理解；

b.在SQL99标准中，on和where可以分工，on指定连接条件，where对连接之后临时表的数据进行过滤。
--SQL99标准
select "E".ename, "D".dname, "S".grade
	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 > 2000;--where不可写在前面join（因为格式要求）
--逻辑相较于SQL92更加清晰
--此例子只是3张表，具体项目可能有成千上万张

--SQL92标准
select "E".ename, "D".dname, "S".grade
	from emp "E",dept "D",salgrade "S"
	where  "E".sal > 2000 and  "E".deptno = "D".deptno
	and "E".sal between "S".losal and "S".hisal;

5）格式

select top ...
	from A
	join B
	on ...
	join C
	on ...
	where ...
	group by ...
	having by ...
	order by ...

6）习题

a.求出每个员工的姓名部门编号薪水和薪水等级

--求出每个员工的姓名 部门编号 薪水和薪水等级
select "E".ename, "E".deptno, "E".sal, "S".grade
	from emp "E"
	join salgrade "S"
	on "E".sal >= "S".losal and "E".sal <= "S".hisal

b.查找每个部门的编号该部门所有员工的平均工资平均工资的等级

--查找每个部门的编号 该部门所有员工的平均工资 平均工资的等级
select "T".deptno, "T".avg_sal "部门平均工资", "S".grade "公司等级"
	from(
		select deptno, avg(sal) as "avg_sal"
			from emp
			group by deptno
	)"T"
	join salgrade "S"
	on "T".avg_sal between "S".losal and "S".hisal
--等价于
select "T".deptno, "T".avg_sal "部门平均工资", "S".grade "公司等级"
	from salgrade "S"
	join (
		select deptno, avg(sal) as "avg_sal"
			from emp
			group by deptno
	)"T"
	on "T".avg_sal between "S".losal and "S".hisal
--等价于
select "T".deptno, "T".avg_sal "部门平均工资", "S".grade "公司等级"
	from salgrade "S", (
							select deptno, avg(sal) as "avg_sal"
								from emp
								group by deptno
						)"T"
	where "T".avg_sal between "S".losal and "S".hisal

c.查找每个部门的编号部门名称该部门所有员工的平均工资平均工资的等级

--查找每个部门的编号 部门名称 该部门所有员工的平均工资 平均工资的等级
select "T".deptno,"D".dname "部门名称", "T".avg_sal "部门平均工资", "S".grade "公司等级"
	from(
		select deptno, avg(sal) as "avg_sal"
			from emp
			group by deptno
	)"T"
	join salgrade "S"
	on "T".avg_sal between "S".losal and "S".hisal
	join dept "D"
	on "T".deptno = "D".deptno

d.求出emp表中所有领导的信息

--求出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 的组合带来的问题

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

--求出平均薪水最高的部门的编号和部门的平均工资
select top 1 deptno "部门编号", avg(sal) "平均工资"
	from emp
	group by deptno
	order by avg(sal) desc
--等价于
select "E".*
	from (
		select deptno, avg(sal) "avg_sal"
			from emp
			group by deptno
	)"E"
	where "E"."avg_sal" = (
		select max("avg_sal") 
			from (
				select deptno, avg(sal) "avg_sal" 
					from emp 
					group by deptno 
			)"T"
	)

f.把工资最低的人排除，剩下员工中工资最低的前3个人的姓名工资部门编号部门名称工资等级输出

--把工资最低的人排除，剩下员工中工资最低的前3个人的 
--姓名 工资 部门编号 部门名称 工资等级 输出
select top 3 "E".ename,  "E".sal, "E".deptno, "D".dname, "S".grade 
	from (
		select * 
			from emp "E"
			where sal > (select min(sal) from emp)
	)"E"
	join dept "D"
	on "E".deptno = "D".deptno
	join salgrade "S"
	on "E".sal between "S".losal and "S".hisal
	order by "E".sal asc

（2）外连接 —— left/right join ... on ...

定义：不但返回满足连接条件的所有记录，而且会返回部分不满足条件的记录。

分类：

1）左外连接 —— left join ... on ...

定义：不但返回满足连接条件的所有记录，而且会返回左表不满足条件的记录。

解释：

用左表第一行分别与右表的所有行进行连接，

如果有匹配的行，则一起输出，如果右表有多行匹配则结果集输出多行；

如果没有匹配的行，则结果集中是输出一行，该行左边为左表该行，右边全部输出NULL。

以此类推，直到到左表最后一行。

例子：
select *
	from emp "E"
	left join dept "D"
	on  E.deptno = D.deptno;

2）右外连接 —— right join ... on ...

定义：不但返回满足连接条件的所有记录，而且会返回右表不满足条件的记录。

解释：与左外连接类似。

例子：
select *
	from emp "E"
	right join dept "D"
	on  E.deptno = D.deptno;

实际意义：返回一个事物及该事物的相关信息，如果该事物没想相关信息，则输出NULL。

（3）完全连接 —— full join ... on ...

结果集中包含三部分内容：

a. 两个表中匹配的所有记录；

b. 左表中那些在右表中找不到匹配的行的记录，这些记录的右边全为NULL；

c. 右表中那些在左表中找不到匹配的行的记录，这些记录的左边全为NULL。

（4）交叉连接 —— cross join ...

结果：产生笛卡尔积

select * from emp cross join dept;
--等价于
select * from emp, dept;

（5）自连接【重点】

定义：一张表自己和自己连接起来查询数据。

--用聚合函数 求薪水最高的员工的信息
select * 
	from emp 
	where sal = (select max(sal) from emp);

--不使用聚合函数 求薪水最高的员工的信息
select * 
	from emp 
	where empno not in(
		select distinct "E1".empno
			from emp "E1"
			join emp "E2"
			on "E1".sal < "E2".sal);
/*
select distinct "E1".empno
			from emp "E1"
			join emp "E2"
			on "E1".sal < "E2".sal
查询有薪水比自己薪水多的记录的empno
除了薪水最高的记录外，其他的记录都可以找到比自己薪水多的记录。
*/

（6）联合

定义：表和表之间的数据以纵向的方式连接在一起。

注意：

之前的连接都是以横向的方式连接在一起的。

若干个 select 子句要联合成功的话，必须满足两个条件：

a. 这个若干个 select 子句输出的列数必须是相等的；

b. 这个若干个 select 子句输出的列的数据类型至少是兼容的。

例子：

--输出每个员工的姓名、工资和上司的姓名
select "E1".ename,"E1".sal,"E2".ename "上司的姓名"
	from emp "E1"
	join emp "E2"
	on "E1".mgr = "E2".empno
union --显示mgr为NULL的记录
select ename,sal,'已是最大老板' 
	from emp
	where mgr is null

13.分页查询

--输出工资最高的前三员工的所有信息
select top 3 * from emp order by sal desc;
--输出工资从高到低，输出工资第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 条记录，当前要显示的是第 m 页。表名是A，主键是A_id。

select top n * 
	from A
	[where A_id not in (select top n*(m-1) A_id from A order by ... )]
	[order by ...];

14.嵌套查询

770-0

关注

3
点赞
踩
11

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

数据库Sql Server操作——查询

二.查询

1.计算列

2.DISTINCT——消除重复值，去重

3.BETWEEN

4.IN——属于若干个孤立的值

5.TOP——最前面的若干个记录

6.NULL——没有值，空值

7.ORDER BY——以某个字段排序

8.模糊查询

格式

通配符

例子

注意

9.聚合函数——多行记录返回一个值，通常用于统计分组的信息

函数的分类

聚合函数的分类

注意的问题

10.GROUP BY——分组

11.HAVING——对分组之后的信息进行过滤

SQL执行顺序

12.连接查询

（1）内连接【重点】—— (inner) join ... on ...

（2）外连接 —— left/right join ... on ...

（3）完全连接 —— full join ... on ...

（4）交叉连接 —— cross join ...

（5） 自连接【重点】

（6）联合

13.分页查询

14.嵌套查询

（5）自连接【重点】