sinEagle

bottlecode

0%

SQL Server --查询

发表于 更新于
本文字数： 16k 阅读时长 ≈ 15 分钟

查询

计算列

1
2
3
4
select * from emp 
select empno, ename from emp
select ename, sal*12 as"年薪" from emp --as可以省略
select 5 from emp

distinct

过滤掉重复的值

1
2
3
4
5
select deptno from emp
select distinct deptno from emp
select distinct comm from emp
select distinct comm, deptno from emp --整体过滤,把comm和deptno组合过滤
--select  comm, distinct deptno from emp --ERROR

between

1
2
3
select * from emp where sal  >= 1500 and  sal <= 3000
select * from emp where sal between 1500 and 3000
select * from emp where sal not between 1500 and 3000

in

1
2
3
4
select * from emp where sal in (1500, 3000, 5000)
select * from emp where sal not in (1500, 3000, 5000)
select * from emp where sal <>1500 and sal<>3000 and sal<>5000 --不等于 != 或 <>

top

最前面的若干记录

1
2
3
4
select * from emp
select top 2 * from emp
select top 15 percent * from emp

工资1500到3000的员工工资前4个人信息输出

1
2
select top 5 * from emp where sal between 1500 and 3000
    order by sal desc

null

没有值，空值
任何类型都可以为null

1
2
3
4
5
6
select * from emp where comm <> null --error
select * from emp where comm != null --error
-- null 不能参与 <> != 运算
--null 可以参与 is 或 is not
select * from emp where comm is null
select * from emp where comm is not null

输出每个员工的姓名，年薪（包含了奖金）comm是一年的奖金

1
2
3
select ename, sal*12 + comm from emp --error

select ename, sal*12+isnull(comm,0) from emp
1
isnull(comm, 0) --如果comm 是空值，返回0， 否则返回comm的值

order by

以某个字段排序

1
2
3
select * from emp order by sal --默认按升序排序
select * from emp order by deptno, sal; --按组合排序，先按deptno排序,如果deptno相等,再按sal排序
select * from emp order by deptno desc, sal;

模糊查询

select 字段的集合 from 表名 where 字段的名字 like 匹配的条件
匹配的条件通常含有通配符
通配符：

% :任意0个或多个字符 

1
2
select * from emp where ename like '%A%' --只要含有字母A就输出
select * from emp where ename like 'A%' --只要首字符为A的就输出

_ :任意单个字符

1
select * from emp where ename like '_A%' --只要第二个字母是A就输出

[a-f] : a到f的任意单个字符

1
2
select * from emp where ename like '_[A-F]%'

[a,f] : a或f的任意单个字符
[~a-c] 不是(a-f)

1
select * from emp where ename like '%\%%' escape '\' --把名字包含%的输出

聚合函数

多行记录返回一个值, 通常用于统计分组的信息
函数的分类
单行函数: 每一行返回一个值
多行函数: 多行返回一个值, 聚合函数是多行函数
例子：

1
2
select lower(ename) from emp --lower()是单行函数
select max(sal) from emp  --返回一行, max是多行函数

聚合函数的分类：
max()
min()
avg()
count(): 求个数
count(*) : 返回表中所有记录的个数
count(字段名) : 返回字段值非空的的记录的个数，重复的记录也会被当做有效的记录
count(distinct 字段名) 

1
2
3
4
5
6
7
select count(*) from emp --返回emp表中所有记录的个数
select count(deptno) from emp; --返回值是14，说明deptno重复的记录也被当作有效值
select count(distinct deptno) from emp --返回值是3,去重后

select comm from emp;
select count(comm) from emp;

注意的问题：

1
2
select max(sal),min(sal),count(*) from emp --correct
select max(sal),lower(sal) from emp --单行函数和多行函数不能混用

group by

格式：
group by 字段的集合
功能:
把表中的记录按照字段分为不同的组

分组后只能出现分组后的整体信息，不能出现组内详细信息

1
2
3
select deptno, avg(sal) as "部门平均工资" from emp group by deptno; --
select deptno, avg(sal) as "部门平均工资", ename
    from emp group by deptno; --error

group by a, b
先按a分组,再按b分组，最终统计的是最小分组的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
select deptno, job, avg(sal) as '平均'
    from emp group by deptno, job --最小的分组信息


select deptno, job, avg(sal) as '平均'
    from emp
    group by deptno, job
    order by deptno;

select deptno, job, avg(sal) as '平均', count(*) "部门人数", sum(sal) "部门的工资", min(sal) "部门的最低工资"
    from emp
    group by deptno, job
    order by deptno;

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

having

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

1
2
3
4
5
--输出部门平均工资大于2000的 部门编号 部门平均工资
select deptno, avg(sal) 
    from emp
    group by deptno 
    having avg(sal) > 2000

判断下列sql语句是否正确

1
2
3
4
5
6
7
8
9
select deptno, avg(sal) as "平均工资", count(*) as "部门人数"
    from emp
    group by deptno 
    having count(*) > 5 --correct

select deptno as 部门编号, avg(sal) as "平均工资", count(*) as "部门人数"
    from emp
    group by deptno 
    having 平均工资 > 1500; --ERROR

having 和 where 的异同

where对原始数据进行过滤
having对分组之后的数据进行过滤
所有select 参数的位置顺序不允许变化，否则编译出错

1
2
3
4
5
6
7
8
9
10
11
12
13
--把姓名不包含A的所有员工按部门编号分组,统计输出部门平均工资大于2000的部门的 部门编号 部门的平均工资
select deptno, avg(sal)
    from emp 
    where ename not like '%A%'
    group by deptno
    having avg(sal) > 2000

----把工资大于2000所有员工按部门编号分组,统计输出部门平均工资大于2000的部门的 部门编号 部门的平均工资
select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "最高工资"
    from emp
    where sal > 2000
    group by deptno
    having avg(sal) > 3000
  1. having 子句是用来对分组以后的数据进行过滤，因此使用having时通常先使用group by
  2. 如果没使用 group by 但使用了having，意味着having把所有的记录当作一组来进行过滤
    1
    2
    3
    select count(*) 
        from emp
        having avg(sal) > 1000
  3. having 子句出现的字段必须是分组之后的组的整体信息，不允许出现组内详细信息
  4. having 子句不能出现别名

连接查询

定义

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

内连接

1
2
3
4
select  "E".ename "员工姓名", "D".dname "部门名称"
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno

分类

select … from A, B

row = rowA * rowB
col = colA + colB
把A表的每一条记录和B表的每一条记录组合在一起，形成的是笛卡尔积

1
select * from emp, dept
select … from A, B where
1
select * from emp, dept where empno = 7369 --5行
select … from A join B on …
1
2
3
4
5
6
7
8
select  "E".ename "员工姓名", "D".dname "部门名称"
    from emp "E"
    join dept "D" --join是连接
    on 1 = 1 --on是连接条件,不能省略
select  *
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno
select … from A, B where … 与 select … from A join B on … 的区别
1
2
3
4
5
6
select * from emp, dept
    where emp.deptno = dept.deptno  --sql92标准
--等价于
select * from emp
    join dept 
    on emp.deptno = dept.deptno --sql99标准
1
2
3
4
5
6
7
8
select *
    from emp "E", dept "D"
    where "E".sal > 2000 and "E".deptno = "D".deptno
select * 
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno
    where "E".sal > 2000

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

1
2
3
4
5
6
7
select "E".ename, "D".dname, "S".grade
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno
    join salgrade "S"
    on "E".sal >= S.losal and "E".sal <= S.hisal
    where "E".sal > 2000

sql92标准

1
2
3
4
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 >= S.losal and E.sal <= S.hisal)

select from where join on group by order top having 混合使用
1
2
3
4
5
6
7
8
--输出工资最高的前三名每个员工的姓名,工资，工资等级，部门名称
select top 3 "E".ename, "E".sal, "S".grade, "D".dname
    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 desc
1
2
3
4
5
6
7
8
9
--输出部门名称不包含A的所有员工中工资最高的前三名每个员工的姓名,工资，工资等级，部门名称
select top 3 "E".ename, "E".sal, "S".grade, "D".dname
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno
    join salgrade "S"
    on "E".sal between "S".losal and "S".hisal
    where "D".dname not like '%A%'
    order by "E".sal desc
习题
  1. 求出每个员工的姓名 部门编号 薪水 薪水等级
    1
    2
    3
    4
    select "E".ename, "E".deptno, "S".grade
        from emp "E"
        join salgrade "S"
        on "E".sal between "S".losal and "S".hisal
  2. 查找每个部门的编号 该部门所有员工的平均工资 平均工资的等级
    1
    2
    3
    4
    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
  3. 求出emp表中所有领导的姓名
    1
    2
    3
    select * 
        from emp
        where empno in (select mgr from emp)
    求出所有非领导的姓名
    1
    2
    3
    select * 
        from emp
        where empno not in (select mgr from emp) --error mgr 中含有null
  4. 求出平均薪水最高的部门编号和部门的平均工资
    1
    2
    3
    4
    select top 1 deptno "部门的编号", avg(sal) "部门的平均工资"
        from emp 
        group by deptno
        order by avg(sal) desc
  5. 把工资大于所有员工中工资最低的前3个人的姓名 工资 部门编号 工资等级输出
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    --第一种写法
    select top 3 "E".ename, "E".sal, "E".deptno, "D".dname, "S".grade
        from emp "E"
        join dept "D"
        on D.deptno = E.deptno
        join salgrade "S"
        on "E".sal between "S".losal and "S".hisal
        where sal > (select min(sal) from emp)
        order by sal 
    --第二种写法
    select top 3 "T".ename, "T".sal,  "T".deptno, "D".dname, "S".grade
        from (
            select * from emp "E" where sal > (select min(sal) from emp)
        ) "T"
        join dept "D"
        on "T".deptno = "D".deptno 
        join salgrade "S"
        on "T".sal between "S".losal and "S".hisal
        order by "T".sal

总结：

把工资大于1500的所有员工按部门分组, 把部门平均工资大于2000的最高前两个的部门编号，平均薪资,部门名称,平均工资对应的薪资等级输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
--把工资大于1500的所有员工按部门分组, 把部门平均工资大于2000的最高前两个的部门编号，平均薪资,部门名称,平均工资对应的薪资等级输出
select top 2 "E".deptno, avg(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 
    group by "E".deptno
    having avg("E".sal) > 2000
    order by avg("E".sal) desc 

select "T".*, "D".dname, "S".grade
    from dept "D"
    join (
        select top 2 "E".deptno, avg(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 
        group by "E".deptno
        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



外连接

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

  • 左外连接：不但返回满足条件的所有记录，而且还会返回部分左表不满足条件的记录

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

    1
    2
    3
    4
    5
    6
    7
    select * from emp "E"
        left join dept "D"
        on "E".deptno = "D".deptno --14行

    select * from dept "D"
        left join emp "E"
        on "E".deptno = "D".deptno --16行

完全连接

1
2
3
4
select * from dept "D"
full join emp "E"
on "E".deptno = "D".deptno --16行

交叉连接

产生笛卡尔积

1
select * from emp cross join dept

自连接

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

1
2
3
4
5
6
7
8
9
10
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子句要联合成功的话必须要满足两个条件：

  1. 这若干个select子句输出的列数必须是相等的
  2. 这若干个select子句输出的数据类型至少是兼容的
1
2
3
4
5
6
7
--输出每个员工的姓名，工资,上司的姓名
select "E1".ename, "E1".sal, "E2".ename "上司的姓名"
    from emp "E1"
    join emp "E2"
    on "E1".mgr = "E2".empno
union 
select ename, sal, 'big_boss' from emp where mgr is null

嵌套查询

分页查询

***

*week10_1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
select getdate()

select datepart(mm, getdate())

--select datediff (yy, birthday, getdate()) from student

select charindex('am', 'I am a student')
select LEFT (stu_email, charindex('@', stu_email)-1) from student
select right(stu_email, len(stu_email)-charindex('@', stu_email)) from student

select len('  123456    ')
select substring(stu_email, 1, charindex('@', stu_email)- 1) from student
select substring(stu_email, charindex('@', stu_email)+1, len(stu_email)-charindex('@', stu_email) ) from student

select rtrim(ltrim('    12345 6    '))

select stuff('123456', 2, 3, '99999')

select replace('abcdsfgabc sfsfs abc', 'abc', '123') --

select replace('ajbcjdajefgajiojk00of', 'o', '0') --ajbcjdajefgaji0jk000f
select replace(replace('ajbcjdajefgajiojk00of', 'o', '0'), 'j', 's') --asbcsdasefgasi0sk000f

select reverse( replace(replace('ajbcjdajefgajiojk00of', 'o', '#'), 'j', 's')) --f#00ks#isagfesadscbsa
select charindex('sa', reverse( replace(replace('ajbcjdajefgajiojk00of', 'o', '#'), 'j', 's'))) --9
select reverse(left(reverse( replace(replace('ajbcjdajefgajiojk00of', 'o', '#'), 'j', 's')), charindex('sa', reverse( replace(replace('ajbcjadajefgajiojk00of', 'o', '#'), 'j', 's')))-1))

--使用right
select right(replace(replace('ajbcjdajefgajiojk00of', 'o', '0'), 'j', 's'), len('ajbcjdajefgajiojk00of')-charindex('sa', reverse( replace(replace('ajbcjdajefgajiojk00of', 'o', '#'), 'j', 's'))))

week10_2

stuff

在字符串1中在指定的开始位置删除指定长度的字符，并在指定的开始位置处插入字符串2。返回新字符串

1
select stuff('abcdef', 2, 3, '1234')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
select convert(varchar, getdate(), 126)

select 1 + convert(int, '123')

select '1' + convert(varchar, '123')


create table a(
    a1 varchar(20), 
)
insert a 
select '11-3' union
select '11-2' union
select '14-5' union
select '3-8' union
select '2-9' union
select '13-2' union
select '132-1' union
select '16-4' union
select '34-3' union
select '83-1' union
select '83-2' union
select '83-111' union
select '11-23'

select * from a

select left(a1, charindex('-', a1)-1) from a 

select * from a order by convert (int, left(a1, charindex('-', a1)-1) ), cast (right(a1, len(a1)-charindex('-',a1))as int )
select * from a order by convert (int, left(a1, charindex('-', a1)-1) ), cast (stuff(a1, 1, charindex('-', a1), '') as int)
select * from a order by convert (int, left(a1, charindex('-', a1)-1) ), cast (stuff(a1, 1, charindex('-', a1), '') as int)

select left(reverse(a1), charindex('-', reverse(a1)) -1) from a 

select reverse(left(reverse(a1), charindex('-', reverse(a1)) -1) ) from a

select * from a order by convert (int, left(a1, charindex('-', a1)-1) ),  convert(int, reverse(left(reverse(a1), charindex('-', reverse(a1)) -1) )  )


--stuff(str, 1, 3, '')
--cast (stuff(a1, 1, charindex('-', a1), '') as int
delete from a

select stuff('abcdef', 2, 3, '1234')

week11

1
2
3
4
5
6
7
8
select count(studentid) from student group by gradeid

select subjectid,AVG(score) from Score group by subjectid
select gender, count(studentid) from student group by gender
select gradeid, gender, count(studentid) from student group by gradeid, gender order by gradeid

select gradeid,gender, count(studentid) from student group by gradeid,gender having count(studentid) > 10 order by gradeid

week11_2

week12_1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
--考过试的学生姓名
select distinct(name) from student inner join Score on student.studentID = score.StudentID

select * from Score
select * from student
select name from student where StudentID in(select StudentID from score)

--select name, subjectname, score, testtime from student join Score on 

select scoreid,StudentID, SubjectId, testtime from score
select scoreid,(select name from student where student.studentID = score.StudentID), 
(select subjectname from Subject where subject.SubjectID = score.SubjectId),score testtime from score

--查询和金蝶同一月生日的同学姓名
select name, Birthday from student where month(birthday) = (select month(birthday) from student where name = '金蝶')

--查询和凌洋同一月天日的同学姓名
select name, Birthday from student where month(birthday) = (select month(birthday) from student where name = '凌洋')
                                       and day(birthday) = (select day(birthday) from student where name = '凌洋')
--成绩最高的同学姓名
--step1: 找最高分
select top 1 score from score order by Score desc
select max(Score) from score
--step2: 找学号
select studentid from Score where Score = (select max(Score) from score)
select studentid, Score from score order by Score desc --验证

select name from student where studentID in (select studentid from Score where Score = (select max(Score) from score))

--查询平均分高于金蝶的学生姓名
select avg(score) from Score where StudentID = (select StudentID from student where name = '金蝶')
select studentID, avg(score) from score group by studentid

select name from student where studentID in(select studentid from score group by studentid 
                                                            having avg(score) > (87))
--select AVG(score) from Score group by studentid order by avg(score) desc

-- in 等价于 = any

--内外查询无关
select name from student where exists(select * from score)
select name from student where exists(select * from score where StudentID = '1')

--考过试的学生姓名
select name from student where exists (select * from score where student.studentID = score.StudentID)


week_12_2

1
2
3
4
5
6
7
8
9
10
11

select top 10 * from student where studentid not in 
    (select top 10 studentid from student)

select row_number() over(order by studentid) as num, * from student --加列名
--select row_number() over(order by studentid) as num, * from student where num <= 10 and num >= 1 --error
select * from (select row_number() over(order by studentid) as num, * from student) as b where num <= 10 and num >= 1
select * from (select row_number() over(order by studentid) as num, * from student) 
 as b where num  between ((2-1)*10+1) and 2*10

select 姓名 from (select StudentID 学号, name 姓名 from student) b
求大佬赏个饭