提示:以下是本篇文章正文内容,下面案例可供参考
一、基本查询
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 表名
示例:
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
or
not
示例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之间的员工信息
select * from People where PeopleSalary>=10000 and PeopleSalary<=20000
select * from People where PeopleSalary between 10000 and 20000
示例6:查询出地址在武汉或北京的员工信息
select * from People where PeopleAddress='武汉' or PeopleAddress='北京'
select * from People where PeopleAddress in('武汉','北京')
示例7:查询80后员工信息
select * from People where PeopleBirth>='1980-1-1' and PeopleBirth<='1989-12-31'
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
示例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关键字和通配符来实现 通配符含义具体如下:
%
_
[]
[^]
3.2示例
示例1:查询姓刘的人员信息
select * from People where PeopleName like '刘%'
select * from People Where SUBSTRING(PeopleName,1,1)='刘'
示例2:查询姓刘,但是名字只包含两个字的人员信息
select * from People where PeopleName like '刘_'
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表里没有,但是这些数据仍然可以查到
|