sql sever 查询知识点汇总

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、基本查询

1.1查询所有行所有列

语法：

select * from 表名

示例1：查询部门表中所有数据

select * from Department 

示例2：查询职级表中所有数据

select * from [Rank]

1.2指定列查询（姓名、性别、月薪、电话）

语法：

select 列名1,列名2...from 表名

示例：

select PeopleName,PeopleSex,PeopleSalary,PeoplePhone from People

如果你想把列名起个别名，语法：

select 列名1 as 别名1,列名2 as 别名2... from 表名
--as可省略

示例：

select PeopleName as 姓名,PeopleSex as 性别,PeopleSalary as 月薪,PeoplePhone as 电话 from People

select PeopleName 姓名,PeopleSex 性别,PeopleSalary 月薪,PeoplePhone 电话 from People
--两种写法效果一样

1.3查询公司员工所在城市（去重）

语法：

select distinct(列名) from 表名

示例：

select distinct(PeopleAddress) from People

1.4假设工资普调10%，查询原始工资和调整后工资

select PeopleName,PeopleSalary as 加薪前,PeopleSalary*1.1 as 加薪后 from People 

二、条件查询

2.1语法及常见运算符：

语法

select * from 表名 where 条件
--如果只是对个别列查询，你就把*换成对应的列即可

运算符

= --比较，等于
!= --比较，不等于
> --比较，大于
< --比较，小于
>= --比较，大于等于
<= --比较，小于等于
IS NULL --比较，为空
IS NOT NULL --比较，不为空
in --比较，是否在其中
like --模糊查询
BETWEEN...AND... --比较是否在两者之间
and --逻辑与，类似c语言&&
or --逻辑或，类似c语言||
not --逻辑非，类似c语言！

示例1：查询员工表中性别为女的员工信息

select * from People where PeopleSex='女'

示例2：查询员工表中工资大于10000元的员工姓名和工资

select PeopleName,PeopleSalary from People where PeopleSalary>10000

示例3：查询员工表中工资大于9000元的女员工的全部信息

select * from People where PeopleSalary>9000 and PeopleSex='女'

示例4：查询月薪大于10000的员工，或者月薪大于等于8000的女员工

select * from People where PeopleSalary>=10000 or (PeopleSalary>=8000 and PeopleSex='女')

示例5：查询月薪在10000-20000之间的员工信息

--写法1
select * from People where PeopleSalary>=10000 and PeopleSalary<=20000
--写法2
select * from People where PeopleSalary between 10000 and 20000

示例6：查询出地址在武汉或北京的员工信息

--写法1
select * from People where PeopleAddress='武汉' or PeopleAddress='北京'
--写法2
select * from People where PeopleAddress in('武汉','北京')

示例7：查询80后员工信息

--写法1
select * from People where PeopleBirth>='1980-1-1' and PeopleBirth<='1989-12-31'
--写法2
select * from People where year(PeopleBirth) between 1980 and 1989

示例8：查询工资比张三高的员工信息

select * from People where PeopleSalary>(select PeopleSalary from People where PeopleName='张三')

2.2排序及相关示例

语法：

select * from 表名 order by 列名 desc/asc
--desc是降序
--asc是升序

示例1：查询所有员工信息，根据工资，降序排序

select * from People order by PeopleSalary desc

示例2：查询所有员工信息，根据名字长度，升序排序

select * from People order by len(PeopleName) asc

示例3：查询员工表中，工资最高的5个人信息(降序)

select top 5 * from People order by PeopleSalary desc

示例4：查询员工表中，工资前10%的个人信息(降序)

select top 10 percent * from People order by PeopleSalary desc

示例5：查询员工表中，地址没有填的员工信息

select * from People where PeopleAddress is null

注意：
查询null是不能用等号的，
比如你写PeopleAddress = null就什么也查不出来
需要使用is null

示例6：查询员工表中，地址已填的员工信息

select * from People where PeopleAddress is not null

三、模糊查询

3.1like关键字及通配符

模糊查询使用like关键字和通配符来实现
通配符含义具体如下：

%  --匹配任意个字符（>=0）
_  --匹配1个字符
[]  --匹配范围内的
[^] --匹配不在范围内的

3.2示例

示例1：查询姓刘的人员信息

--写法1
select * from People where PeopleName like '刘%'
--写法2
select * from People Where SUBSTRING(PeopleName,1,1)='刘'
--SUBSTRING（PeopleName,1,1）表示从PeopleName第1个位置开始取，取1个字符

示例2：查询姓刘，但是名字只包含两个字的人员信息

--写法1
select * from People where PeopleName like '刘_'
--写法2
select * from People Where SUBSTRING(PeopleName,1,1)='刘' and len(PeopleName)=2

示例3：查询姓名中含有“小”这个字的员工信息

select * from People where PeopleName like '%小%'

示例4：查询电话号码是138开头的

select * from People where PeoplePhone like '138%'

示例5：查询电话号码是138开头的，第四位是7或8的，最后一位是5的

select * from People where PeoplePhone like '138[7,8]%5'

示例6：查询电话号码是138开头，最后一位不是5和1的

select * from People where PeoplePhone like '138%[^5,1]'

注：如果你想查2~5之间的数，
[ ]里面可以写[2,3,4,5],也可以写[2-5]
[^]也是同理

四、聚合函数

4.1主要的聚合函数

sql sever中聚合函数主要有

count --求数量
max --求最大值
min --求最小值
sum --求和
avg --求平均值

4.2示例

示例1：求员工总人数

select count(*) 人数 from People

你在count后面加上“人数”，那么结果显示出来的列名就是“人数”

示例2：求最大值，求最高工资

select max(PeopleSalary) 最高工资 from People

示例3：求最小值，求最低工资

select min(PeopleSalary) 最低工资 from people

示例4：求和，求所有员工一个月的工资总和

select sum(PeopleSalary) 一个月工资总和 from People

示例5：求平均值，求所有员工平均工资

select avg(PeopleSalary) 平均工资 from People

示例6：求员工表中 人员数量、最大工资、最小工资、总和、平均值，在一行显示

select count(*)总人数 , max(PeopleSalary) 最高工资 ,min(PeopleSalary) 最低工资 , sum(PeopleSalary) 一个月工资总和,avg(PeopleSalary) 平均工资 from People

五、分组查询

分组查询也经常和聚合函数一起使用

举个例子：我现在要查人员表中员工平均工资

select  avg(PeopleSalary) as 平均工资 from People

那也会有可能出现客户需要查询“男性平均工资”和“女性平均工资”，也就是出现分组的情况，这样怎么查询呢？

可能有同学会说，你分别查男性和女性的平均工资，然后用union联合起来，代码如下

select avg(PeopleSalary) as 平均工资 from People where PeopleSex='男'
union
select avg(PeopleSalary) as 平均工资 from People where PeopleSex='女'

这样写不算你错，但是如果分组比较多呢？假如哪天我让你按地区分组查，你不仅要知道表里面有哪些地区，你还要写这么多select就很麻烦了

我们可以用group by 进行分组查询

select PeopleSex 性别, avg(PeopleSalary) as 平均工资 from People group by PeopleSex

group by也可以搭配where来实现条件筛选
比如我现在筛选工资2w以下的男女平均工资

select PeopleSex 性别, avg(PeopleSalary) as 平均工资 from People where PeopleSalary<20000 group by PeopleSex 

六、多表查询

6.1笛卡尔积

比如我现在有一张部门表，共3个部门；一张人员表，共19人
我们直接多表查询，结果如下

select * from People,Department

共57条记录，也就是57=3*19
笛卡尔积=表一元素数量 * 表二元素数量

但是仔细看这个表其实会发现有问题，因为我们人员表和部门表其实是有关系的，但是实际笛卡尔积出来的实则是每个都排了一次。

比如刘小九是某一个部门的，并且他的信息应该只出现一次，但是笛卡尔积后把他和所有部门都组合了一遍，所以出现了很多没有用的信息。

多表查询中，笛卡尔积是没有考虑我们表之间元素关系的，我们应该要加where条件的

需要注意的是，由于我们两个都是DepartmentId，自己不能等于自己啊，你要在DepartmentId前加表名.

select * from People Department where People.DepartmentId=Department.DepartmentId

这样就可以看到，最后只查出19个人，是和预期一样的。

6.2简单多表查询

示例1：查询员工信息，显示职级名称(Rank表)

select * from People,[Rank] where People.RankId=[Rank].RankId

示例2：查询员工信息，显示部门名称，显示职级名称

select * from People,Department,[Rank]
where People.DepartmentId=Department.DepartmentId
and People.RankId=[Rank].RankId

6.3内连接

我们前面的简单的多表查询是直接简单的select * from 多张表，然后加几个条件

那我们内连接是怎么查询的呢？
select * from 第一张表 inner join 第二张表 on 关系
on关系，关系比如People.DepartmentId=Department.DepartmentId

示例1：查询员工信息，显示部门名称

select * from People 
inner join Department on People.DepartmentId=Department.DepartmentId

示例2：查询员工信息，显示职级名称

select * from People 
inner join[Rank] on People.RankId=[Rank].RankId 

示例3：查询员工信息，显示部门名称，显示职级名称

select * from People
inner join Department on People.DepartmentId=Department.DepartmentId
inner join [Rank] on People.RankId=[Rank].RankId 

简单多表查询和内连接共同特点：不符合主外键的数据不会被查询出来

这句话是什么意思？原先查询到的数据都是有主外键的，而我现在把主外键删掉，新增一个员工信息
比如说我现在的部门表里面就三个部门，部门编号为1，2，3，而新插入的员工数据我给他部门编号为4，但是4这个编号原先部门表没有啊，你再查询，是查询不到新增这个员工信息的

6.4外连接

外连接分三种：左外连、右外连、全外连

6.4.1左外连

以左表为主表进行数据显示，主外键关系找不到的数据用null替代

示例1：查询员工信息，显示部门名称

select * from People
left join Department on People.DepartmentId=Department.DepartmentId

示例2：查询员工信息，显示职级名称

select * from People 
left join [Rank] on People.RankId=[Rank].RankId

那左外连和内连接有什么区别呢？
我们知道内连接查询到的数据是要符合主外键的
那我们这个左连接，比如People left join Department
我们这里People表里有个人，他的部门名称在Depatment表里面没有，或者说他没有填部门是哪个
但是我们用左连接查，依然可以查到这个人的信息

6.4.2右外连

以右表为主表进行数据显示，主外键关系找不到的数据用null替代
右外连就是和左外连相反
A left join B = B right join A

示例：查询员工信息，显示部门名称

--左外连写法
select * from People
left join Department on People.DepartmentId=Department.DepartmentId

--右外连写法
select * from Department
right join People on People.DepartmentId=Department.DepartmentId

右外连和内连接区别和前面说的左外连接和内连接区别不大
我们这里Department表里有个部门，这个部门名在People表里面没有用到，
但是我们用右连接查，依然可以查到这个部门的信息
如下图，我们人员表中没有哪个人是人事部的，但也会显示人事部

6.4.3全外连

两张表的数据，无论是否符合关系，全部显示

select * from People
full join Department on People.DepartmentId=Department.DepartmentId

全外连就是，People表里有些人部门在部门表没有，或者部门表里有些部门在People表里没有，但是这些数据仍然可以查到