第4章 查询和视图
SELECT语句可以从一个或多个表中选取特定的行和列。因为查询和检索数据是数据库管理中最重要的功能，所以SELECT语句在SQL中是工作量最大的部分。
SELECT语句的结果通常是生成另外一个表。在执行过程中系统根据用户的标准从数据库中选出匹配的行和列，并将结果放到临时的表中。在直接SQL中，它将结果显示在终端的显示屏上，或者将结果送到打印机或文件中。也可以结合其他SQL语句来将结果放到一个已知名称的表中。
SELECT语句功能强大。它用来完成关系代数运算“选择”，也可以完成其他两种关系运算 “投影”和“连接”，还可以完成聚合计算并对数据进行排序。
1 SELECT语句的语法
SELECT 筛选条件
FROM 表名称
Where 搜索子句
Groupby 分组子句
Having 分组统计条件
Order by 排序子句 ASC/DESC
2 结果筛选条件
(1) select * from users
(2) select username,ownname from users
(3) 消除结果中的重复行distinct子句
Select distinct * from users
(4) select username as 用户名, 真名=ownname from users
注意：as和＝的用途是一致的，但是要注意顺序
(5) 计算列值
在计算列值的时候，可以使用的算术运算符有：+ - * / %
select username as 用户名,真名=ownname + '123' from users (这里的+是连接字符串的功能)
例子:
select product_name as 产品名称,库存总额=product_price*product_store
from product
(6) 限制结果集返回行数 top / percent
select top 5 username, ownname from users
使用top限制其返回的行数，如果使用percent关键字，则返回结果集的n％行，如：select top 5 percent username from users
(7) 替换查询结果中的数据
例子:/*替换结果中的数据*/
select stu_name as 姓名,stu_major as 专业,性别=
case
when stu_sex='1' then '男'
when stu_sex='0' then '女'
end
,等级=
case
when stu_total_credit is null then '尚未选课'
else str(stu_total_credit) /*要注意两个结果的数据类型保持一致*/
end
from xs
where stu_major='计算机'
3 搜索子句
3.1 运算符
| 比较运算符 |
逻辑运算符 |
| = |
And |
| <
|
Or |
| >
|
Not |
| <= |
|
| >= |
|
| != |
|
| <>
|
|
例子：
1) select * from employees where last_name = 'jones'
2) select * from employees where salary > 50000
3) select * from employees where last_name = 'jones' and first_name = 'davy'
4) select * from employees where last_name = 'jones' or last_name = 'smith'
5) select * from employees where not(branch_office = 'boston')
6) select * from employees where (last_name = 'jones'and first_name = 'indiana')or (last_name = 'smith' and first_name = 'bessie')
3.2 NULL和三值逻辑
首先，在进行NULL判断时需要特殊的语法。例如，如果用户需要显示所有年薪未知的职员的全部信息，用户可以使用如下SELECT语句：
select * from employees where salary is null
相反，如果用户需要所有已知年薪数据的职员的信息，你可以使用以下语句：
select * from employees where salary is not null
NULL表示未知的值而不是说明是一个空值。对于SQL来说意味着这个值是未知的，而只要这个值为未知，就不能将其与其他值比较。所以SQL允许除了在true 和false之外还有第三种类型的真值，称之为“非确定”值。
3.3 Like模式匹配
Like谓词的运算对象是字符型/文本型/日期型数据,返回逻辑值true/false。通常使用like谓词进行模糊查询。
| 通配符 |
意义 |
| % |
任意长度的字符 0个或多个 |
| _ |
任何单个字符 |
| [] |
某个范围内的单个字符 |
| ^ |
指定不属于某范围内的任何单个字符 |
注意：在SQL语句中，字符串使用’’括起来的。
例子：
select * from xs where stu_major like '计算机'
select * from xs where stu_name like '王%'
select * from xs where stu_id like '00__[2-3]%'
如果要做通配符的字符包括 % _ [] ^，可使用关键字 escape 。
select * from xs where stu_name like '王/_%' escape '/' ，其中/字符是可以任意指定的。
或者用中括号[]也可以，如：'王[_ ]%'
又如，包含5％怎么表示：%5[%]% 或 %5/%% escape’/’
注意：如何将-用于查找字符串，必须放在[]首位，如：[ - abc]
[ à [[ ]
] à ]
3.4 范围比较
select * from users where ip in('202.202.037.088','211.083.215.241')
注意：in后面还可以接子查询
select * from users where logincount between 50 and 400
3.5 Contains 和Freetext谓词
若需要在表中搜索指定的单词、短语或近义词等，可以使用Contains谓词。该谓词用于在表中搜索指定的字符串，可以是精确匹配、模糊匹配，也可以是加权匹配。
该谓词的使用，首先要给这个表建立全文索引。可以通过命令方式或企业管理器给表建立全文索引，但需要注意的是，每当表中数据发生变化时，都要启动增量填充或全部填充。
Contains谓词的语法格式：
Contains ({ columns | *}, <contains_search_condition>)
contains_search_condition::=
{<simple_term : word | “phrase” >|<prefix_term >|<generation_term>|<proximity_term>|<weighted_term>} and |and not|or <contains_search_condition>
1． simple_term
例子：在book表中查询字符串包含“语”的记录。
select * from book where contains(*,’语’)
注意：contains(*,’语’)中的*表示表中参与到全文索引中的所有列！
例子：在book表中查询含：“operation system”的书的情况：
Select * from book where contains(*,’ ” operation system” ‘)
2． Prefix_term
给出了要搜索单词或短语必须匹配的前缀，{“word*” | “phase*”}，当查询的是串是短语时，需要双引号定界。
例子：查询作者姓王的情况
Select * from book where contains(author,’王*’)
例子：查询书名中包含“SQL Server”的情况
Select * from book where contains(title,’” SQL Server”’)
3． Generation_term
搜索包含原词的派生词，如单复数，时态等，格式为：
Formsof(inflecational,<simple_term>[…n])
例子：查询含operate或其派生词的情况
Select * from book where contains（*,’ormsof(inflecational,operate)’）
4． Proximity_term
搜索包含near或～运算符左右两边的词或短语，格式为：
{<simple_term> | <prefix_term>}{{near | ~ }{<simple_term> | <prefix_term>}}[….]
例子：查询在“程序”附近有“设计”字样的书
Select * from book where contains (*,’程序 near 设计’)
5．Weight_term
查询的数据与给定的权重进行加权匹配，格式为：
Isabout({{<simple_term>|<prefix_term }|<generation_term>|<proximity_term>|<weighted_term>} weight（weight_value）})
例子：
Select * from book where contains(*,isabout(operate weight(.4),system weight(.8)))
Freetext的查询精度没有contains高。该谓词对所查询的串也没有写法要求。
select * from book where freetext (*,’程序 设计’)
本节示例表book表生成T-SQL语句：
create table book
(title char(30) not null
,author char(10) not null
,abstract ntext null
)
insert into book values ('计算机程序设计','王键','计算机程序设计基础教程')
insert into book values ('汇编语言','王不顺','IBMPC汇编程序设计')
insert into book values ('C++语言','黎明','C++程序语言设计')
3.6 子查询
子查询可以用在select语句中，也可以用在insert update delete 语句中。
子查询通常与in 、exist 或比较运算符结合使用：
select * from exam_user_log where xsbh in (select distinct xsbh from exam_score)
select * from exam_user_log where xsbh not in (select distinct xsbh from exam_score)
注意：in 子查询只能返回1列值，如果是较复杂的查询，可以使用嵌套的子查询。
例子：查询未选修离散数学的学生的情况:
Select * from xs
Where stu_id not in
( select stu_id from xs_kc
Where kc_id in
(
Select kc_id from kc
Where kc_name=’离散数学’
)
)
格式：expession{<|<=|=|>|>=|!=|<>|!<|>!} {all|some|any} (子查询)
select *
from exam_user_log
where xsbh<>all(select distinct xsbh from exam_score)
例子：查找比所有计算机系的学生年龄都大的学生
Select * from xs
Where stu_birth<all
(select stu_birth from xs
Where stu_major=’计算机’
)
例子：查找课程号为206且成绩不低于课程号为101的最低成绩的学生的学号。
Select stu_id from xs_kc
Where ks_id=’
(
Select kc_score from xs_kc
Where ks_id=’
)
select *
from exam_user_log
where not exists (select distinct xsbh from exam_score where xsbh=exam_user_log.xsbh)
例子：查询选修了206号课程的所有学生姓名
Select stu_name from xs
Where exists
(select * from xs_kc
Where stu_id=xs.stu_id and kc_id=’
)
分析这种相关子查询的工作过程！
例子：查找选修了全部课程的同学的姓名
select name from xs
where not exists
(select * from kc
where not exists
(select * from xs_kc
where stuid=xs.stuid and subid=kc.subid
)
)
4 from
from子句中可以查询的内容有：表，视图，行集函数，XML文档，子查询，连接表。
4.1 table_name
42 view
注意：可以使用as选项为表或视图指定别名。
4.3 rowset_function
Rowset_function是一个行集函数，通常返回一个表或视图。主要的行集函数有：
4.4 derived_table
子查询必须使用一个别名
Select * from (select * from xs where stu_birth<'20070731') as m (学号,姓名,专业,性别,出生日期,学分,备注)
4.5 joined_table
5 连接
连接查询有两大类表示形式，一个是符合SQL标准连接谓词表示形式，一个是SQL扩展的使用关键字JOIN的表示形式。
5.1 连接谓词
例子：查询每个学生的情况及其选课的情况
select xs.*,xs_kc.* from xs,xs_kc where xs.stu_id=xs_kc.stu_id
例子：
/*查询选修了课程号为101，且成绩在60分以上的学生姓名和成绩。*/
select stu_name,score from xs,xs_kc
where xs.stu_id=xs_kc.stu_id and xs_kc.kc_id='101' and xs_kc.score>=60
例子：多表连接
/*查找选修了计算机基础课程并且成绩在60上的学生学号 姓名 课程名 及成绩*/
select xs.stu_id as 学号,xs.stu_name as 姓名 ,kc.kc_name as 课程名称, xs_kc.score as 成绩
from xs,kc,xs_kc
where xs.stu_id=xs_kc.stu_id and kc.kc_id=xs_kc.kc_id and kc.kc_name='计算机基础' and xs_kc.score>=60
注意：
1） 两个字段必须是可比的；
2） 比较符不一定是＝，也可以是其他的，如 > < 等；但是当用＝时，就是等值连接。若在目标列中去除相同的字段名，就是自然连接。
3） 连接和子查询的区别：连接可以合并两个或多个表中的数据，而带子查询的select语句的结果只能来自一个表。
4） 有的查询既可以使用连接，也可以使用子查询来表达。通常使用子查询表示可以将一个复杂的查询分解为一系列的逻辑步骤，条理清晰，而使用连接表示有执行速度快的优点。
5.2 以join关键字指定的连接
例子:
/*查询每个学生的情况及其选课的情况*/
select * from xs inner join xs_kc on xs.stuid=xs_kc.stu_id
inner可以省略，因为SQL中默认的就是内连接
例子: 两表内连接
/*查询选修了课程号为101，且成绩在60分以上的学生姓名和成绩。*/
select xs.stu_name,xs_kc.score from xs inner join xs_kc on xs.stu_id=xs_kc.stu_id
where xs_kc.kc_id='101' and xs_kc.score>=60
例子:多表内连接
/*查找选修了计算机基础课程并且成绩在60上的学生学号 姓名 课程名 及成绩*/
select xs.stu_id as 学号,xs.stu_name as 姓名,kc.kc_name as 课程名称,xs_kc.score as 成绩
from xs inner join xs_kc join kc on kc.kc_id=xs_kc.kc_id on xs.stu_id=xs_kc.stu_id
where kc.kc_name='计算机基础' and xs_kc.score>=60
注意:若将on kc.kc_id=xs_kc.kc_id on xs.stu_id=xs_kc.stu_id两个连接条件写反,则报错!
这种多表连接的时候,通常是从内到外进行连接.一定要注意!
例子:自连接
/*查找不同课程成绩相同的学号,课程号 成绩*/
select a.stu_id,a.kc_id,a.score
from xs_kc a inner join xs_kc b on a.stu_id=b.stu_id and a.score =b.score and a.kc_id !=b.kc_id
5.2.2 外连接
注意:外连接只能对两个表进行。
1） 左外连接 left join
例子：/*查找所有学生的选修课程号，若没有选修课程，也要包括其情况*/
select xs.*,xs_kc.kc_id
from xs left outer join xs_kc on xs.stu_id=xs_kc.stu_id
2） 右外连接 right join
例子：/*查找被选修了的课程的选修情况和所有开设的课程*/
select kc.* ,xs_kc.stu_id
from xs_kc right join kc on kc.kc_id=xs_kc.kc_id
备注：本质上 左右外连接并没有区别，只是在使用的时候注意顺序就可以了。
3） 完全外连接 full join
交叉连接实际上是将两个表进行笛卡尔积运算。注意：交叉连接不能有条件，且不能带where子句。
例子：列出学生所有可能的选课情况
select 学号，姓名，课程号，课程名 from xs cross join ks
6 排序
如果你希望以字母表顺序将结果列出又该怎么做呢？只要使用ORDER BY子句就可以按照升序或降序来排列结果：
select distinct branch_office from employees order by branch_office asc
ORDER BY子句只将临时表中的结果进行排序；并不影响原来的表。
7 数据汇总
7.1 聚合函数
表 常用聚合函数表
| 函数 |
返回值 |
| Sum(col_name) |
返回一系列值的总和 |
| Avg(col_name) |
返回一系列值的平均值 |
| COUNT(*) Count_big 返回bigint型！ |
其功能是检索表中满足给定条件的记录数 |
| Max(col_name) |
返回一系列值中的最大值 |
| Min(col_name) |
返回一系列值中的最小值 |
例子：select count(distinct stu_id) as '已选课学生总数' from xs_kc
例子：select count(*) as '已选课总人次数' from xs_kc
例子：/*101课程的平均成绩*/
select avg(score) as '101课程的平均成绩' from xs_kc
where xs_kc.kc_id='101'
4.2 groupby子句
例子：/*将各专业及其学生总数输出*/
select stu_major as '专业名',count(stu_major) as '该专业学生总数'
from xs
group by stu_major
例子：/*求被选修的各门课程的平均成绩和选修该课程的人数*/
select kc_id,avg(score) as '平均成绩',count(stu_id) as '选修人数'
from xs_kc
group by kc_id
例子：查询出每个专业的男女生总人数和总学生数
select stu_major as '专业名',stu_sex,count(stu_major) as '该专业学生总数'
from xs
group by stu_major,stu_sex
with rollup
注意：多于1列的分组是按照group by列的逆序排列。
备注：with cube 除了进行rollup的汇总外，还会对一切可能的组合进行统计汇总。
4.3 having子句
使用group by 子句和聚合函数对数据进行分组后，还可以使用having字句对分组的数据进一步筛选。
例子：/*查找选修课程超过两们且成绩都在80分以上的学生的学号*/
select stu_id from xs_kc
where score>=80
group by stu_id
having count(kc_id)>=2
注意：groupby子句是对where的结果进行分组，而having则是对groupby以后的分组数据进行过滤。
4.4 compute子句
compute子句用于分类汇总。Compute将产生额外的汇总行，其格式是：
compute 聚合函数名称(expression)
例子：/*查询通信专业学生的学号、姓名、出生时间，并产生一个学生总人数的行*/
select stu_id,stu_name,stu_birth from xs
where stu_major like '%通信%'
compute count(stu_id)
8 其他子句
8.1 into子句
使用into子句可以将查询结果保存到一个新建的表中。
select *
into temp1
from exam_user_log
8.2 union子句
UNION 运算符使您得以将两个或多个 SELECT 语句的结果组合成一个结果集。使用 UNION 组合的结果集都必须具有相同的结构。而且它们的列数必须相同，并且相应的结果集列的数据类型必须兼容。
UNION 的指定方式如下：
select_statement UNION [ALL] select_statement
例如，Table1 和 Table2 具有相同的两列结构。
| Table1 |
|
Table2 |
|
| ColumnA |
ColumnB |
ColumnC |
ColumnD |
| char(4) |
int |
char(4) |
int |
| abc |
1 |
ghi |
3 |
| def |
2 |
jkl |
4 |
| ghi |
3 |
mno |
5 |
下面的查询在这两个表之间创建 UNION 运算：
SELECT * FROM Table1
UNION
SELECT * FROM Table2
下面是结果集：
ColumnA ColumnB
------- --------
abc 1
def 2
ghi 3
jkl 4
mno 5
默认情况下，UNION 运算符从结果集中删除重复的行。如果使用 ALL 关键字，那么结果中将包含所有行并且将不删除重复的行。
9 视图
9.1 视图的优点
1. 为用户集中数据，简化用户的数据查询和处理；
2. 屏蔽数据库的复杂性；
3. 简化用户权限的管理；
4. 便于数据共享；
5. 可以重新组织数据以便输出到其他应用程序中。
使用视图时，要注意：
1. 不能把规则、默认值、触发器与视图相关联；
2. 不能在视图上建立任何索引，包括全文索引。
9.2 使用企业管理器或向导创建视图
9.3 使用命令创建视图
Create view view_name
As
Select sententce
Go
例子：
Create view view_name
As
Select * from xs where stu_major=’计算机’
Go
注意：创建视图语句必须是批查询中的第一条语句。
Create view 语句的完整格式：
Create view database_name.owner_name.view_name(column_name1,column_name2,….)
With <view_attribute>[1…n]
As select statement
With check option /*指出在视图上所进行的修改都要符合select statement */
其中，view_attribute可以取以下值：
（1）encryption 系统在系统表syscomments中存储create view语句时进行加密；
（2）schemabinding 说明视图与其所依赖的表或视图结构相关联。
（3）view_metadata 指定为引用视图的查询请求浏览模式的元数据时，向DBLIB、ODBC或OLEDB API返回有关视图的元数据信息，而不是返回给基表或其他表。
例子：
/*创建cs_kc视图，包括计算机系各学生的信息及其选课的课程号和成绩*/
create view cs_kc
as
select xs.*,xs_kc.kc_id,xs_kc.score
from xs,xs_kc
where xs.stu_id=xs_kc.stu_id and xs.stu_major='计算机'
with check option
或
create view cs_kc
as
select xs.*,xs_kc.kc_id,xs_kc.score
from xs left join xs_kc on xs.stu_id=xs_kc.stu_id
where xs.stu_major='计算机'
with check option
例子：
/*创建计算机专业学生的平均成绩，包含学号和平均成绩*/
create view cs_kc_avg(num,score_avg)
as
select stu_id,avg(score)
from cs_kc
group by stu_id
9.4 查询视图
9.5 更新视图
只有是可更新的视图才能被更新，可更新的视图通常包括：
（1）所有的数据至少包含从一个基本表得到的原数据（没有用聚合函数、top、percent、distinct等语句）
（2）可更新的分区视图
（3）通过instead of 触发器创建的可更新视图
插入数据可以使用 insert into view_name values(…..)语句来完成。但是其中的限制比较多，如：
该视图的数据只能来自于1个基本表，或是可更新的分区视图；
删除数据可以使用delete from view_name where ….,其中的限制如同插入。
该语句可以通过视图修改基本表的数据。若一个视图（非分区视图）依赖多个表，则一次修改该视图只能变动一个基本表的数据。
9.6 修改视图的定义
Alter view view_name
As
Select statement
….
9.7 删除视图
Drop view view_name