SQL Server操作语句

摘要：分组的目的是为了细化聚合函数的作用对象。使用子句时，如果在的查询列表中包含聚合函数，则是针对每个组计算出来的一个汇总的值，从而实现对查询结果的分组统计。外连接操作中可以使用子句，子句等

数据查询功能：

Select <目标列名序列（ 需要的列 ）> from <数据源（ 表格 ）>
[where<检索条件表达式>]
[Group by <分组依据列>]
[having <组提取条件>]
[order by <排序依据列>]

学生表（student）

课程表（course）

成绩表（sc）

1.查询指定的列

select Sno ,Sname,sdept From Student

2.查询全部列

select * from Student

、

3.根据已知参数查询

select Sname ,"生日",2018 - Sage from Student

4.指定列的别名

列名|表达式 [AS] 列别名 或者  列别名=列名|表达式

select Sname ,"生日" as 常量列 ,2018 - Sage as 年份 from Student

5.去除重复列

select distinct Sno from sc

6.查询满足条件的元组

关键字（WHERE）

select Sname from Student Where Sdept ="计算机系"

select Sname,Sage from Student Where Sage <20

select Sname,Sage from Student Where not Sage >=20

常用的where的查询条件

7.确定范围between ....and和not between....and

格式：列名|表达式 [NOT] between 下限值 and 上限值（包括边界值）
        select Sname,Sage,sdept from Student where Sage between 20 and 23
(where Sage <20 or Sage>23 ) 

创建图书表：

create table book(
  Number char(8) primary key,
  BookName char(20) not null,
  price real not null,
  data datetime not null
  )
  go

    
  select *from book
  where date between "2009/6/1" and "2009/6/30"

日期类型的常量要用单引号引起来，而且年，月，日之间通常用分隔符隔开，常用的分隔符有“/”和“-”

8.确定集合
关键字IN

 select Sname ,Ssexn from Student where Sdept（NOT） IN("信息系","数学系","计算机系")
 

9.字符串匹配
LIKE关键字

LIKE的通用形式：
列名 [NOT] LIKE <匹配串>

通配符：
_:匹配任意一个字符；
%：匹配0个或多个字符；
[]:匹配[]当中的任意一个字符
[^]:不匹配[]中的任意一个字符

例如查询姓“张”的学生详细信息

select * from Student Where Sname Like "张%"
select * from Student Where Sname Like "[张李刘]%"
select * from Student Where Sname Like "张%" OR Sname Like "李%"

查询所有不姓‘王’也不姓‘张’的学生的姓名。

select * from Student Where Sname NOT Like "[王张]%"
                        where Sanme Like "[^王张]%"
                        where Sname NOT LIKE "王%" AND Sname NOT LIKE "张%"
 

查询姓“王”且名字是2个字的学生的姓名。

select Sname from Student Where Sname like "王_"

查询姓“王”且名字是3个字的学生的姓名。

   select Sname from Student Where Sname like "王_ _"
  
  

如果希望系统在比较时能够去掉尾随空格的干扰可以使用sql去掉尾随空格的函数：rtrim（列名）
因为系统在存储“王敏”时固定的char为10个空间所以还要在其后面添加6个空格也就是6个字符

改变后的格式：
select Sname From Student Where rtrim (Sname) LIKE "王__"

查询学号的最后一位不是2,3，5的学生的信息

select * from Student where Sno like "%[^235]"

如果要查找的字符串正好含有通配符，就需要使用一个特殊得转义字符

escape("a")表示位于a字符后面的字符不是转义字符

`where fieldl like "%30!%%" Escape "!"`  查询含有30%的字段
`where fieldl like "%!_%" Escape "!"`    包含_的字段

10.涉及NULL的查询

判断某个值是否为null值，不能使用普通的比较运算符（=，！= 等）
判断列值为空：

列名 IS NULL

判断列值为非空：

列名 IS NOT NULL

查询所有非空的考试成绩学生的学号，课程号，和成绩:

select Sno,Cno, From ,Grade from Sc WHERE Grade IS Not NULL

11.多重条件查询
关键字and和or(AND的优先级大于OR)

select Sname FROM Student WHERE Sdept ="计算机系" AND Sage<20

这里因为AND的优先级大于OR要用括号进行区分

select Sname ,Sdept,Sage from Student where (Sdept ="计算机系" OR Sdept="信息系") AND Sage>=20 

也可以用IN语句避免优先级关系

select Sname ,Sdept,Sage From Student WHERE Sdept IN("计算机系","信息系") AND Sage >=20

12.对查询的结果进行排序

ORDER BY <列名> [ASC|DESC] [,....n]（默认的排序方式为升序排列）

全体学生年龄按升序排列：

select *from From order by sage ASC     

选修c02这门课的学生按降序排列：

select Sno ,Grade from SC where cno="c02" order by grade DESC     

查询结果让所在系的系名升序排列，同一系的学生按年龄降序排列：（这个地方的使用注意体会！）

select * from Student order by Sdept ASC,Sage DESC

13.使用聚合函数汇总数据

count(*):统计表中元组的个数。
count（[distinct]<列名>）:统计本列非空列值个数，distinct表示不包括列的重复值。
sum（<列名>）：计算列值总和（必须是数值类型）
avg(<列名>)：计算列值平均值（必须是数值整形）
max(<列名>)：求列值的最大值
min（<列名>）:求列值的最小值

上面的函数中除了count（*）外，其他函数在计算过程中均忽略NULL再计算（count计算时如果这一行都为null也算一行）

统计学生总人数：select count（*） from Student

统计了选修课程的学生人数：select count （distinct Sno）from SC（注意一个学生可以选择多门课）

统计"9512101"学生的选课门数和考试总成绩：select count（*） as 选课门数，Sum（Grade）as 总成绩 from SC WHERE Sno ="9512101"

注意：聚合函数不能出现在where字句当中。
例如：select Sname FROM Student where Sage = MAX(Sage)是错误的写法。

14.对查询结果进行分组的统计
前面所列举的通过聚合函数的例子都是对全表数据进行统计的，有时我们希望计算的精度更高一些，比如统计每个学生的考试平均成绩，而不是全体学生的考试平均成绩，这时就要用到查询语句的分组功能。
关键字：GROUP BY

group by 可以将统计控制在组一级。 分组的目的是为了细化聚合函数的作用对象。

在一个查询语句中，可以使用多个列进行分组，需要注意的是如果使用了分组子句，则查询列表的每一个列必须要么是分组依据列（group by后面的列），要么是聚合函数。

使用group by子句时，如果在select的查询列表中包含聚合函数，则是针对每个组计算出来的一个汇总的值，从而实现对查询结果的分组统计。

分组子句跟在where子句的后边，一般形式：

GROUP BY <> [,....]
[HAVING <组选择条件>]

注意：分组依据列不能是text，ntext，image，bit类型的列。

使用group by子句

统计每门课程的选课人数，列出课程和选课的人数。

select Cno as 课程号，Count (Sno) as 选课人数
     from SC group by Cno

该语句首先对sc表的数据按Cno的值进行分组，所有具有相同Cno值的元组归为一组，然后再对每一组的元组使用count进行计算，求得每一组的学生的人数。

2.统计每个学生的选课门数和平均成绩

select Sno 学号，count(*) 选课门数,average (Grade) 平均成绩
from sc gropu by Sno

3.统计每个系的学生人数和平均年龄

select Sno 学号,Count(*) 选课门数 ,avg(Grade) 平均年龄 
from Sc group by Sno 

总结： 统计哪一列的各种参数就将哪一列进行分组

4.用带where子句的分组，统计每个系的女生人数和最大年龄。（按列进行分组然后再按组里面的值有选择的进行运算，而不是直接整组运算可以用where进行修饰）

select Sdept ,count(*) 女生人数 from Student 
 where Ssex ="女"
  group by Sdept        

5.按多列进行分组，统计每个系的男生人数和女生人数，以及男生的最大年龄和女生的的最大年龄，结果按系命的升序排列。

select Sdept ,Ssexn ,Count (*) 人数,max(Sage) 最大年龄
  from Student  Group by Sdept ,Ssexn
  Order by Sdept 

6.使用having子句

having子句用于对分组后的结果在进行筛选，它的功能有点像where子句，但他用于组而不是单个记录。

在having子句中可以使用统计函数，但在where子句中则不能。

having通常与group by子句一起使用。

例：查询选修了3门以上学生学号和选课的门数

select Sno ,Count(*) 选课的门数 from sc
 group BY Sno
 HAVING COUNT(*)>3    

先执行group by对Sno进行分组，然后再用统计函数count对每一组的元组个数进行统计，最后找出大于3的那一组。

例：查询选课门数大于等于4门的学生的平均成绩和选课门数。

select Sno,Avg(Grade) 平均成绩,count(*) 选课门数 from sc 
Group by Sno
having count (*)>=4

总结：

where子句用来筛选from子句中指定的数据元源所产生的行数据。

group by 子句用来对经where子句筛选后的结果数据进行分组。 (前面限定where，后面限定having)

having子句用来对分组后的结果数据再进行筛选

建议将所有应该分组之前进行的搜索条件放在where子句而不是having子句中，这样写的效率更高一些。

查询计算机系和信息系的学生人数
第一种

select Sdept ,Count (*) from Student GROUP BY Sdept 
having Sdept IN("计算机系","信息系")

第二种

select Sdept ,Count(*) from Student where Sdept in("计算机系"，"信息系")
group by Sdept

很显然第二种的效率更高一些。

但是要注意一点有时候只能用where限定
例：查询每个系年龄小于等于20岁的学生的人数。

select Sdept ,count(*) from Student where Sage <=20  group by Sdept

注意该语句不可以写成

  select Sdept ,count(*) from Student group by Sdept having Sage <=20

这里是因为having是在分组统计之后再进行这是只包含分组依据列（系别和人数）所以没有Sage元素。

15.多表连接查询
1.内连接
内连接是一种常用的连接类型，如果两个表的字段满足连接条件，则可以从这两个表里面提取数据并组和成新的记录。

内连接的语法格式：

from 表1 [inner] join 表2 ON <连接条件> 

连接条件的一般格式：

[<表名 1.>] [<列名 1>]<比较运算符> [<表名 2.>][<列名 2>]

当比较运算符为等号（=）是时，称为等值连接，其他的称为非等值连接。

例：
查询每个学生及其选课的详细信息。

select * from Student Inner JOIN sc on Student.Sno=sc.Sno

在写多连接表时有必要将这些重复的列给去掉，方法是在select 子句中直接列出所需要的列名，而不是"*"，另外由于多表连接后，在连接生成的表中可能存在列名相同的列，因此为了明确到底需要哪个列，可以在列名的前面添加表名前缀限制，其格式：表名.列名

去掉上图中的重复列

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student [Inner] JOIN sc on Student.Sno=sc.Sno

例：查询计算机系的学生的修课情况，要求列出学生的名字，所选择课程号和成绩

select Sname ,Cno ,Grade  FROM Student JOIN sc ON Sudent .Sno =sc.Sno  where Sdept ="计算机系"

可以为表提供别名，其格式如下：
from <源表名> [as] <表别名>

为表指定别名可以简化表的书写，而且在有些连接查询中要求必须指定别名。

select Sname ,Cno,Grade From Student S join sc

这里要注意的是当你为表指定了别名在查询语句的其他地方，所有用到表民的地方都要使用别名，而不能在使用原表名。

例：查询信息系选修了计算机文化学课程的学生的信息，要求列出学生的姓名，课程名和成绩。

select Sname ,Cname,Grade
  FROM Student s jOIN sc ON s.Sno=sc.Sno 
  join course c ON c.Cno=SC.Cno
  where Sdept ="信息系"
  AND Cname="计算机文化学"

查询所有选修了VB课程的学生情况，列出学生的姓名和所在的系。

select Sname,Sdept  From Student S
  JOIN sc ON S.Sno=sc.Sno
  JOIN course c ON c.Cno=sc.Cno
  where Sdept ="VB"
  

注意：因为本来所需要的数据与sc表无关但是Student和Course没有可以进行连接的元素，所以这是需要引入sc表进行连接。

2.自连接

自连接是一种特殊的内连接，相互连接的表在物理表上为同一张表，但在逻辑上将其看成两张表。
from 表1 T1---在内存中生成表名为T1的表
join 表1 T2---在内存中生成表名为T2的表
因此在自连接时一定要为表取别名。

查询与刘晨在一个系学习的所有学生的姓名和所在系

select S2.Sname ,S2.Sdept from Student S1 join Student S2 on S1.Sdept=S2.Sdept
where S1.Sname="刘晨" AND S2.Sname!="刘晨"

----->

分析：
先在s1中找出刘晨这一项数据然后在s2中找出除刘晨外的计算机系所有学生数据最后S1,S2查询结果通过计算机系进行连接

3.外连接
有时候我们也希望看到那些不满足元组条件的元组信息，比如查看全部学生的选课的情况既想看到那些课程被选了，哪些课程没有被选择。因为内连接实现首先要有满足连接条件，sc.Cno=course.Cno,如果在sc表中没有人选择课程但是在course中却有，由于不满足上述条件所以查找不出来。

外连接只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件。

From 表1 left | right [outer] join 表2 ON <连接条件>

如果是左外连接那么就是限制表2 中的数据必须满足连接条件，而不用管表1中的数据是否满足连接条件，均输出表一中的内容。右连接亦同。

例：查询学生的选课情况，包括选择了课程的学生和没有选择课程的学生，列出学号，姓名，课程号和成绩。

左连接： 

select Student.Sno,Sname,Cno,Grade from Student left outer join sc on Student.Sno =sc.Sno
        
 
右连接： 
select Student.Sno,Sname ,Cno,Grade From sc right outer join Student ON Student.Sno=SC.Sno

例：查询哪些课程没有人选，列出其课程名。

select Cname FROM Course C left join sc on c.cno=sc.cno where sc.cno is null.

外连接操作中可以使用where 子句 ，group by子句等

查询计算机系没有选课的学生，列出学生的姓名和性别。

select Sname,Sdept,Cno,grade from Student S left join sc on S.sno=sc.Sno
where Sdept ="计算机系" and Sc.Sno is null.

假设我们让连接的两个表全部打印：
------>
------->

分析：首先进行外连接以student的sno进行连接从student里面拿一个学号与sc里面的学号进行比较相等则连接当student找不到时则连接空值行
连接后的表再进行where筛选。注意此时还是要当成两个表去看待比如选择一个计算机系且没有选课的（Sno为null）的行就要写成：

where Sdept ="计算机系"（sdept唯一只属于student的不用标识） and Sc.Sno（这个需要标识两者都有） is null.

例：统计计算机系每个学生的选课门数，包括没有选课的学生，结果按选课门数递减排列。

Select s.sno as 学号,count(sc.cno) as 选课门数
    from student s left join sc on s.sno=sc.sno
    where sdept ="计算机系"
    group by s.sno
    order by count (sc.sno) desc
    

首先通过sno进行连接

然后通过计算机系进行过滤

最后通过学号进行分组（需要指定因为学号不唯一）统计
并打印指定的参数列

16.使用TOP限制结果集
在使用select语句时我们希望只显示这些结果集中的前几行结果，就可以使用top语句。
格式：TOP n [percent] [with ties]
其中：

n为非负数

TOP n：表示查询结果的前n%行的数据；

TOP n percent:表示取查询结果的前n%行 的数据；

WITH TIES：表示包括并列的结果。

TOP谓词写在select单词的后边（如果有distinct的话，则在distinct 单词之后），查询列表的前边。