SQL SERVER 数据库的查询总结

1.数据库的查询

等值连接（条件:两个表具有相同的属性）

1.1-1.7是对select语句进行操作的

1.1 自然连接

自然连接运算的结果表是在参与操作两个表的共同属性上进行等值连接后再去除重复属性后所得到的表

1.2定义列别名,当自定义的列标题中含有空格时,必须使用引号将列名括起来,不允许在 where字句中使用列别名

Select username as 用户名,password as密码 from xsb

1.3 替换查询结果中的数据

Case

Where 条件1 then表达式1

Where 条件2 then表达式2

Else 表达式

End

1.4 计算列值

Select 学分=学分X1.2 from xsb

1.5消除结果集中的重复行

Select distinct(默认值all)学业,总学分from xsb

1.6 限制结果返回行数

Select top 6 用户名，专业 from xsb

1.7 聚合函数

Avg 求组中平均值 例子:avg(成绩)

Count 求组中项数（一列对应的行数）

Max 最大值

Min 最小值

Sum 返回表达式中所有值的和

 

1.8--1.16

Where子句

1.8 表达式比较

不等于:<> ,不小于!<

1.9模式匹配（like:谓词，用于指出一个字符串是否与指定字符串相匹配）

通配符列表

通配符

说明

%

代表一个或者多个字符

_(下划线)

代表单个字符

[]

指定范围中的任何单个字符

[^]

指定不属于这个范围内的任何单个字符

举例：where 学号 like “%1_[123456]”

1.10 范围比较（用于范围比较的有两个关键字：between和in）

举例:where 出生日期not between ‘1998-10-5’ and ‘1999-10-5’

Where 专业in(“计算机”，”通信工程”，”无线电”)

等价于

Where 专业 =”计算机类” or专业=’通信工程’ or专业=”无线电”

In 关键字最主要的作用是表现子查询

1.11 空值比较（关键字:is null）

举例:where 学分 is null

1.12 contains 谓词（搜寻指定的单词、短语或者近义词等，可以使用contains谓词。可以使精确匹配，也可以是模糊匹配，还可以是加权匹配。要使用contains谓词，必须在操作的表上建立全局的索引。有两种方法可以建立索引，一是使用create index；另一 种方式是图形化界面使用索引功能）

举例:select * from xsb where contains(*,’工程’)

1.13 freetext(功能和contains类似,但是没有contains高,只能与select连用)

select * from xsb where freetext(*,’工程’)

1.14 子查询（在查询条件中，可以使用另一个查询结果作为条件的一部分）

In（not in） 子查询 用于进行一个给定值是否在子查询结果集中的判断（in和notin 子查询只能返回一列数据）

举例：select * from xsb where 学号 in (select 学号from cjb where 课程号=’206’)

先执行子查询，产生结果集，在执行查询

1.15 比较子查询（in子查询的扩展）

举例:select * from 课程号=/>/</<>（select课程号 from cjb where课程名=”离散学”）

1.16 exists 子查询（用于测试子查询的结果是否为空表，不会空返回true）

 

From 子句

1.17 select * from xsb,cjb where cjb.课程号=xsb.课程号and学号=”0102”

Select * from (select * from xsb where 总学分>50) as student where性别=”1”

 

连接 1.18--1.19

1.18 连接谓词（可以在select语句的where子句中使用比较运算符给出连接条件对表进行连接，将这种表示形式称为连接谓词表示形式）

Select xsb.*,cjb.课程号 from xsb,cjb where xsb.学号=cjb.学号（自然连接）

1.19以join关键字指定的连接（内连接，外连接，交叉连接）

1 内连接（指定了inner关键字的连接是内连接，内连接按照On所指定的连接条件合并两个表，返回满足条件的行）（还可以用于连接多个表）

举例：select * from xsb inner join cjb on xsb.学号=cjb.学号（2个表）

举例：select *from xsb join cjb join xsb join kcb on cjb.课程号=kcb.课程号on xsb.学号=cjb.学号where 课程名=”离散数学”

特殊情况：自连接（将一个表与它自身进行连接，目的：若要在一个表中查找具有相同列的行（使用自连接时需要为表指定2个别名，且堆对所有列的引用都要以别名来限定））

举例：查找不同课程成绩相同的学生的学号、课程号和成绩

Select a.学号,a.课程号,b.课程号,b.成绩from cjb a join cjb b on a.课程号!=b.课程号and a.学号=b.学号and  a.成绩=b.成绩

2 外连接（指定outer关键字的连接为外连接，外连接的结果表不但包括满足连接条件的行，而且还包括相应表中的所有行）。

外连接分为3类：左外连接，右外连接，完全外连接

2.1 左外连接（left outer join）：结果表中除了满足连接条件的行外，还包括左 表中的所有行

2.2 右外连接（right outer join）：结果表中除了满足连接条件的行外，还包括 右表中的所有行

2.3 完全外连接（left outer join）：结果表中除了满足连接条件的行外，还包括 左右两个表中的所有行

3 交叉连接（实际上是将两个表进行笛卡尔积运算，结果集是由第一个的每行与第二个表中的每行拼接后形成的表，因此结果表中的行数等于2个表行数之积）

举例：select 学号，姓名，课程号，课程名from xsb cross join kcb

·1.20 group by子句(主要用于根据字段对行分组)例如根据学生所学专业对xsb表中的所有行进行分组。sql语句实现select专业 from xsb group by专业（列名）

 使用带rollup操作符的group by子句：指定在结果集中不仅包括有group by提供的正常行，还包括汇总行

举例：在pxscj数据库上产生一个结果集，包括每个专业的男生、女生人数、总人数以及学生总人数

sql实现: select专业,性别,count(*) as人数 from xsb group by专业,性别with rollup

汇总行产生规则：按列的排列顺序的逆序进行汇总。在本例中根据专业和性别对xsb表进行分组，使用rollup之后，先对性别字段汇总（针对专业相同的行），然后对专业和性别均不同值产生了汇总行。所产生的汇总行中对应不同列值的字段将置为null

1.21 having 子句(使用group by子句和聚合函数对数据进行分组后，还可以使用having子句对分组数据进行进一步筛选)

举例：查找pxscj数据库中平均成绩在85分以上的学生（就是在cjb表上按学号分组后筛选出符合平均成绩大于85的学生）

sql实现：select学号,avg(成绩) as平均成绩 from cjb group by学号 having avg(成绩)>85

在select语句中，当where,group by,having都被使用时要注意他们的作用和执行顺序。Where用于筛选有from子句指定的数据对象，group by用于对where的结果进行分组，having则是对group by分组后的数据进行过滤

1.22 order by 子句（对查询结果进行排序输出，asc升序排列(默认)，desc降序排列）

举例：将通信工程专业的学生按出生时间的先后次序排序

Select * from xsb where 专业=”通信工程” order by出生时间 desc

Order by 子句和computer by子句一起使用时，在对结果排序的同时还将产生附加的汇总行

Sql实现：select * from xsb where专业=”通信工程” computer count(学号) by专业

 

Select 语句的其他语法

1.23--1.25

1.23 into (使用into子句可以将select查询结果保存到一个新建的表中)

举例：由xsb创建”计算机系学生”表，包括学号和姓名

sql实现：select学号，姓名 into计算机系学生表 from xsb where专业=”计算机”

1.24 union（使用union子句可以将两个或者多个select查询结果合并成一个结果集）

使用union组合2个查询的结果集的规则是：

1. 所有查询中的列数和列的顺序必须相同

2. 数据类型必须兼容

关键字all表示合并的结果集中包括所有行，不去除重复行。不使用all则在合并的结果集中取出重复行。含有union的select查询也称之为联合查询，若不指定into子句，结果将合并到第一个表中

举例：查找学号为081101和学号为081102两位同学的信息

Sql实现：select * from xsb where学号=”081101”

Union all

select * from xsb where 学号=”081102”

1.25 except和intersect(用于比较2个查询结果，返回非重复的值（查询规则和union的规则一致））

Except从except关键字左边的查询中返回右边查询未找到的所有非重复值

Inertsect返回insertsect关键字左右两边的两个查询都发挥的所有非重复值

Except和insertsect返回的结果集列名与关键字左边的查询返回的列名相同

1.26  cte(在select语句的最前边可以使用一条with子句来指定临时结果集)

Sql实现

With cte_stu(numebr,point)

As (Select 学号,成绩from cjb where 课程号= ”101”)

Select number,point

From cte_stu,xsb

Where xsb.姓名=”王林” and xsb.学号=cte_stu.number