五
SQL语言由：数据定义语言（DDL），数据操作语言（DML）和数据控制语言（DCL）组成。
1）数据定义语言（DDL）
create table,alter table和drop table。用于创建表，修改期属性和删除表。
2)数据操作语言（DML）
用于查询和操作数据。select,insert,update,delete。
3)数据控制语言（DCL）
用于控制对象库对象操作的权限。grant和revoke
二 聚合函数
在查询中还会经常碰到的要求是取某些列的最大值,最小值,平均值等信息,有时候还需要计算出究竟查询到多少行数据项.这个时候,查询的’统计数据’是大家比较关心的,sql server提供了’聚合函数’,聚合函数能够基于列进行计算,并返回单个值.
Sql server提供了下面几个聚合函数
1 sum
sum返回表达式中所有数值的总和,sum只能用于数字类型的列,不能够汇总字符,日期等其他数据类型,例如查询表中所有人员的月薪总和.
例句: select sum (rmb) from user1
也可以查询经理们的月薪总和: select sum (rmb) from user1 where posts='经理'
这种查询只返回一个数字,不能够直接与可能返回多行的列一起使用进行查询.
如: select truename,sum (rmb) from user1 where posts='经理'
这个因为有truename字段，而符合post='经理'的值有多行，但sum(rmb)只返回一个数字，产生冲突，故出错。
但是在一个查询中可以同时使用多个聚合函数.
2 avg
avg 函数返回表达式中所有数值的平均值,avg函数也只能用于数字类型的列,例如查询经理人员的平均月薪.
例句: select avg(rmb) from user1 where posts='经理'
3 max 和min
max返回表达式中的最大值, min返回表达式中的最小值,它们都可以用于数字型,字符型以及日期/时间类型的列.
查询经理人员中最高月薪,最低月薪还有平均月薪.
例句: select max(rmb) as 最高月薪,min(rmb) as 最低月薪,avg(rmb) as 平均月薪from user1 where posts='经理'
4 count
Count返回提供的表达式中非空值的计算,count可以用于数字和字符类型的列.
另外,也可以使用星号(*)作为count的表达式,使用星号可以不必指定特定的列尔计算所有的行数.
例如查询月薪在3000以上的人员
例句: select count(*) from user1 where rmb > 3000
三，其它相关
1.select
1)distinct 消除重复值
select distinct 出版社 from book1
2)使用top n(percent)仅返回n行
use book
go
select top 5* from book1
go
显示前5条
use book
go
select top 5 percent * from book1
go
显示前百分之五条记录
3)修改查询结果中列的标题（别名）
(1)使用=号，显示别名=元组
use book
go
select '图书编号'=编号,'图书书号'=ISBN号 from book1
go
(2)别名加单引号，空格与元组分开
use book
go
select 编号 '图书编号',ISBN号 '图书书号' from book1
go
(3)别名与元组用AS分开
use book
go
select 编号 as '图书编号',ISBN号 as '图书书号' from book1
go
4)在查询结果中附加字符串
要增加的字符串用单引号括起，后接列名字.
use book
go
select 书名, '图书定价为：',定价
from book1
go
5)使用where子句给出查询的条件，使用
(1)比较运算符：=,<>,!=,>,>=,!>,<,<=,!<.
(2)逻辑运算符：and,or,not.用来连接表达表.
eg, where 定价>50 and 出版社='中山大学' 图书定价在50元以上，并出版社为中山大学。
6)使用ORDER BY子句重新排列查询结果
说明：默认(即省略ASC,DESC时)为“升序”，ASC（升序），DESC（降序）
(1)要求selcct出来的内容，按“定价”的降排序
use book
go
select 书名,定价 from book1 order by 定价 desc
(2)要求查询结果首先按“书名”降序排序，“书名”相同时，则按“编号”升序排序
use book
go
select 书名,编号,出版社 from book1 order by 书名 desc,编号 asc(这个ase可以省的，因为默认是
升序）
go
7)使用IN关键字，可简化查询语句
(1)查询编号为'XH5468','YBZT0001','YBZT0024'的书名称。
方法一：使用逻辑运算式or
use book
go
select 书名
from book1
where 编号='XH5468' OR 编号='YBZT0001' OR 编号='YBZT0024'
GO
方法二：使用IN
use book
go
select 书名
from book1
where 编号 IN('XH5468','YBZT0001','YBZT0024')
GO
(2)查询编号不为'XH5468','YBZT0001','YBZT0024'的书名称。
方法一：使用逻辑运算式or
use book
go
select 书名
from book1
where 编号<>'XH5468' and 编号<>'YBZT0001' and 编号='YBZT0024'
GO
方法二：使用IN
use book
go
select 书名
from book1
where 编号 NOT IN('XH5468','YBZT0001','YBZT0024')
GO
8)使用LIKE关键字进行模糊查询，
SQL的4个通配符为：
(1)%百分比：匹配包含0个或多个字符的字符串。
like '中%'：匹配以“中”字开头的字符串。
(2)_下划线：匹配任何单个的字符。
like '_人'：匹配第二个为“人”的字符串。
(3)[]排列通配符，匹配任何在范围或集合之内的单个字符。如[M-P]匹配的是m,n,o,p单个字符。
like '[ck]ars[eo]n':表示carsen,carson或karsen,karson
(4)[^]不在范围之内的字符，匹配任何不在范围或集合之内的单个字任。
like 'n[^c]%'：匹配所有以字母n开始，并且第二个字母不是c的所有字符串。
要查找通配符本身时，需将它们用方括号括起来。
like'[[]'：表示要匹配"['
like '5[%]':表示要匹配“5%".
注：通配符和字符串必须括在“单引号”中。
例子:
(1)
use book
select *
from book1
where 书名 like '中%'
go
(2)查询第二个字为“人”的所有图书
use book
select *
from book1
where 书名 like '_人%'
go
(3)查询第一个字不为“半”的所有图书
use book
select *
from book1
where 书名 like '[^半]%'
go
(4)使用NOT LIKE查询
use book
select *
from book1
where 书名 not like '半%'
go
9)使用IS NULL关键字查询没有赋值的行
(1)查询book1表中没有出版社的所有记录
use book
go
select *
from book1
where 出版社 is null
go
10)查询某一范围内的信息
表达式的where中使用“比较运算符”: =,<,<>,!>,!=,>=,<=,!>,!<,between,not between
"逻辑运算符“：not,and,or
"范围运算符“：between,not between.
(1)定价大于1000并且小于2000的记录
use book
go
select *
from book1
where 定价>1000 and 定价<2000
go
(2)定价大于1000并且小于2000的记录
use book
go
select *
from book1
where 定价 between 1000 and 2000
go
(3)定价大于2000并且小于5的记录，并按降序排列
use book
go
select *
from book1
where 定价 not between 5 and 2000 order by 定价 desc
go
11)使用compute子句
用于计算总计或进行分组小计。
(1)查询“出版社”为“中国长安”的记录，并计算“定价”的平均值
use book
go
select *
from book1
where 出版社='中国长安'
compute avg(定价)
go
12)使用compute by子句
它对by后面给出的列进行分组显示，并计算该列的分组小计。使用compute by子句必须按照order by和
COMPUTE BY中的by指定列进行排序。
(1)显示book1的记录，并计算每个出版社的平均定价
use book
go
select *
from book1
order by 出版社
compute avg(定价) by 出版社
go
13)使用group by 子句
将查询结果按照group by后指定的列进行分组。该子句写在where子句的后面。当在select子句中包含聚
合函数时，最适合使用GROUP BY子句。SELECT子句中的选项列表中出现的列。包含在聚合函数中或者包
含在GROUPB BY子句中。否则，SQL SERVER将返回如下错误提示消息：
“表名列名在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在GROUP BY 子句中”。
(1)按出版社分类统计每个出版社出书的本数.
use book
go
select 出版社,count(出版社) as '本数'
from book1
group by 出版社
go
解释:select 出版社<这个要与group by 后面的列对应，如group by后面只有“出版社”，这里也只有
能有"出版社“>,count(出版社<这个为聚合函数，当中的字段为任何一个存在的列字段都可以>) as '本
数<这个为显示的别名，可随意取>' from book1 group by 出版社<这个要与select后面的列相对应>
group by后面跟的字段，表明按这个字段来分类。
例句: select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb
按posts与rmb来分类汇总，求个数。
14)HAVING子句
HAVING子句用于限定组或聚合函数的查询条件。
HAVING 只能与 SELECT 语句一起使用。HAVING 通常在 GROUP BY 子句中使用。如果不使用 GROUP BY 子句，则 HAVING 的行为与 WHERE 子句一样。
Having子句是对分组后的条件的筛选,比如这个时候我们需要查看具有相同工资的人员,
例句: select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb having count(*)<>1
上例句的意思是：
在“select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb“得出来的结果的基础之上，查筛出“人数”不为1的数据。
Having和where子句可以在同一个select语句中使用,使用的顺序是首先where然后group by 最后having
15)使用嵌套查询
一个select-from-where语句称为一个查询块。有时一个查询块无法完成查询任务，需要一个子查询的
结果作为主查询语句的条件。
注：(1)子查询在上一级查询处理之前求解。
(2)order by子句只能对最终查询结果排序。
(1)查询图书定价大于平均定价的图书记录
use book
go
select *
from book1
where 定价>(select avg(定价) from book1)
go
16)使用UNION运算符
用于将两个或多个查询结果合并成一个结果，当使用UNION运算符时，需要遵循以下两个规则：
(1)所有查询中列数和列的顺序必须要相同。
(2)所有查询中按顺序对应列的数据类型必须兼容。
(1)book1图书名，book2图书编号，进行union运算合并这两个查询结果.
use book
go
select 书名 from book1
union
select 编号 from book2
go
17)对多个表进行查询
1>笛卡儿积
use book
go
select *
from book1,teacher
go
结果为：144478行,11列;
boo1表列数(6)+teacher表列数(5)=11列；
book1表的行数2413 x teacher表的行数6=14478行
这种结果达不到我们需要的结果，而且会造成非常大的运算，特别是表大时，可以使计算机死机，慎用
。
2>连接条件
为了避免产生笛卡儿积，并且得到所需的查询结果。必须使用where子句给出连接条件。一般来说，对
N个表（或视图）的查询要有N-1个连接条件.
(1)查询编号为'XH5468"的书名，定价（在book1表中），作得姓名和作者的职称（在teacher表中），但
双方的编号一致。
use book
go
select book1.编号,书名,定价,作者姓名,职称
from book1,teacher
where book1.编号=teacher.编号 and book1.编号='XH5468'
GO
连接的类型：内连接（自然连接，相等连接），外连接（左外连接，右外连接，全外连接），交叉连接
。
在SQL SERVER中，可以使用两个连接语法形式：
1)ANSI连接语法形式。
2)使用SQL SERVER连接语法形式。
3>相等连接
相等连接是将要连接的列作相等比较后所作的连接，相等连接总会产生冗余，因为连接的列要显示两次
。‘
(1)查询所有图书和所有作者的信息
use book
go
select *
from book1,teacher
where book1.编号=teacher.编号
go
过程，先计逄两个表的“笛卡儿积”，然后再消除不满足连接条件（ book1.编号=teacher.编号)的记录
. 但在查询的结果中有完全相同的两列“编号”。
使用ANSI连接语法的select语句如下：
use book
go
select *
from boo1 inner join teacher
on book1.编号=teacher.编号
go
显示结果同上。
4>自然连接
自然连接是将要连接的列作相等比较的连接。但是连接的列只显示一次，自然连接消除了相等连接产生
的冗余。
(1)查询所有图书和所有作者的信息，要求连接的列只显示一列
use book
go
select book1.*,作者姓名,职称
from book1,teacher
where book1.编号=teacher.编号
go
5>带有选择条件的连接
在对多表查询时，在指定的连接条件之外也可以包括其他的选择条件。
use book
go
select book1.书名,book1.定价,teacher.作者姓名,book1.出版社
from book1,teacher
where book1.编号=teacher.编号 and book1.定价>=50
go
6>不等值连接
在连接条件中使用等于运算符以外的其他比较运算符（>,>=,<=,<,!>,!<,<>)来比较被连接的列值。
(1)查询book1表和book2表中凡是编号不相等的所有书的书名和编号信息。
use book
go
select book1.书名，book1.编号,book2.书名,book2.编号
from book1,book2
where book1.编号<>book2.编号
go
7>自连接
如果所连接的两个表为同一个表，那么这种连接又称为“自连接”。自连接能把一个表中的行和该表中
的另外一些行联系起来。
(1)在book1表查询与编号为'YBZT0005'图书的出版社相同的书名，定价和出版日期。
分析：将book1表定义别名为a,b
use book
go
select a.编号,a.书名,a.定价,a.出版日期
from book1 a, book1 b
where a.出版社=b.出版社 and a.编号<>'YBZT0005' AND b.编号='YBZT0005'
go
8>左外连接
use book
go
select book1.书名,book1.出版社,ISBN号,teacher.作者姓名
from teacher LEFT JOIN book1
on book1.编号=teacher.编号
go
9>右外连接
右外连接与左外连接相对应，右外连接首先计算两个表的自然连接，再取出右表中与左表任一数据行都
不匹配的那些行，用空值填充所有来自左表的那些列，再把增加的这些行增加到自然连接的结果集中。
(1)使用右外连接查询book1表中凡是有作者信息的所有图书的信息
use book
go
select book1.书名,book1.出版社,ISBN号,teacher.作者姓名
from teacher RIGHT JOIN book1
ON book1.编号=teacher.编号
go
10>全外连接
为了包含两个表中都不匹配的数据行，可以使用全外连接，它完成左外连接和右外连接的操作，包括了
左表和右表所有不满足条件的行。
use book
go
select book1.书名,book1.出版社,ISBN号,teacher.作者姓名
from teacher FULL JOIN book1
ON book1.编号=teacher.编号
go
11>交叉连接
交叉连接就是将连接的两个表的所有行进行组合，形成一个结果集，该结果的列数等于两个表的列数和
，行数等于两个表的行数积。
(1)计算book1表和teacher表的交叉连接
use book
go
select *
from teacher,book1
go
12>使用表别名
可以给表定义别名，以方便查询时对列的引用和简化连接条件的书写。
别名的定义方法：在FROM子句中将要定义的别名的表用空格间隔，然后紧随所定义的别名。
如：from book1 b,将b定义为book1表的别名。
use book
go
select b.书名,b.定价,t.作者姓名
from teacher t,book1 b
where t.编号=b.编号
go
13>使用EXISTS关键字
EXISTS子句用于测试跟随的子查询中的行是否存在，如果存在则返回TURE。
use book
go
select 书名,定价
from book1
where exists (select 编号 from teacher)
go
=====================附他人的说明================
一 模糊查询
模糊查询提取的数据不一定是确切的,查询者对查询条件也是模糊的,大概的,不特别明确的.比如我们
经常用到的搜索引擎google,baidu等,都是典型的模糊查询,我们也不知道确切的答案,打开网页搜索之后
就在那些搜索出来的结果当中进行排除,找到我们需要的答案.模糊查询可以使用我们前面已经介绍过的
like,通配符来进行,上节课学习的is null从严格意义上来说也是模糊查询.
1 使用like进行模糊查询
前面的课程中我们已经介绍过使用like来编写约束,like运算符用于匹配字符串或字符串的一部分.该运
算符只用于字符串.在数据更新,删除或者查询的时候,都可以用like关键字来进行匹配查找.
例句: select * from user1 where companyname like '北京%' order by id
2 使用between在某个范围内进行查询
使用关键字between可以查找那些介于两个已经知道数值之间的一组未知值,要实现这种查找,首先
要知道开始查找的初始值和最终值.这个最大值和最小值用单词and分开,例句: select * from user1
where rmb between 1000 and 2000
Between在查询日期范围的时候使用得比较多.例如,查询生日在1980到1990之间的
例句: select * from user1 where birthday between '1980/1/1' and '1990/12/31'
3 使用in在列举值内进行查询
查询的值是指定的某些值之一,可以使用带列举值的in关键字来进行查询.将列举值放在圆括号里,用逗
号分开,例如查询职位是经理的姓名
例句: select truename as 经理人员表from user1 where posts in ('经理')
二 聚合函数
在查询中还会经常碰到的要求是取某些列的最大值,最小值,平均值等信息,有时候还需要计算出究竟查
询到多少行数据项.这个时候,查询的’统计数据’是大家比较关心的,sql server提供了’聚合函数’,
聚合函数能够基于列进行计算,并返回单个值.
Sql server提供了下面几个聚合函数
1 sum
sum返回表达式中所有数值的总和,sum只能用于数字类型的列,不能够汇总字符,日期等其他数据类型,
例如查询表中所有人员的月薪总和.
例句: select sum (rmb) from user1
也可以查询经理们的月薪总和: select sum (rmb) from user1 where posts='经理'
这种查询只返回一个数字,不能够直接与可能返回多行的列一起使用进行查询.
例句: select truename,sum (rmb) from user1 where posts='经理'
但是在一个查询中可以同时使用多个聚合函数.
2 avg
avg 函数返回表达式中所有数值的平均值,avg函数也只能用于数字类型的列,例如查询经理人员的平均月
薪.
例句: select avg(rmb) from user1 where posts='经理'
3 max 和min
max返回表达式中的最大值, min返回表达式中的最小值,它们都可以用于数字型,字符型以及日期/时间类
型的列.
查询经理人员中最高月薪,最低月薪还有平均月薪.
例句: select max(rmb) as 最高月薪,min(rmb) as 最低月薪,avg(rmb) as 平均月薪from user1 where
posts='经理'
4 count
Count返回提供的表达式中非空值的计算,count可以用于数字和字符类型的列.
另外,也可以使用星号(*)作为count的表达式,使用星号可以不必指定特定的列尔计算所有的行数.
例如查询月薪在3000以上的人员
例句: select count(*) from user1 where rmb > 3000
三 分组查询
我们这个表中存储了来自不同地域,不同公司,不同职位的人员工资,我们现在要算一下在同一职位的平
均月薪,也就是说要把他们的职位来进行分组,分组以后再进行聚合计算,得到累计信息.再比如一个餐馆
营业一天之后他们需要计算出今天卖出的酒水有多少钱,主食有多少钱,炒菜有多少钱.这种类型的查询,
在sql server中叫做分组查询.
1 使用group by 来进行分组查询
注意: 指定 GROUP BY 时，选择列表中任意非聚合表达式内的所有列都应包含在 GROUP BY 列表中
，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。
例句: select posts as 职位,avg(rmb) as 平均月薪from user1 group by posts
分组查询有时候还需要按照多个列来进行分组,比如我们要查看他们具体的工资情况,我们还需要把它们
的月薪进行分组.
例句: select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb
2 使用having 子句进行分组筛选
HAVING 只能与 SELECT 语句一起使用。HAVING 通常在 GROUP BY 子句中使用。如果不使用 GROUP
BY 子句，则 HAVING 的行为与 WHERE 子句一样。
Having子句是对分组后的条件的筛选,比如这个时候我们需要查看具有相同工资的人员,
例句: select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb
having count(*)<>1
上例句的意思是：
在“select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb“得
出来的结果的基础之上，查筛出“人数”不为1的数据。
Having和where子句可以在同一个select语句中使用,使用的顺序是首先where然后group by 最后
having
四 多表连接查询
前面我们学习的所有查询都是基于单个数据表的查询,下面我们将介绍牵涉到多个表的数据查询,多表
查询实际上是通过各个表之间共同列的关联性来查询数据的,它是关系型数据库查询最重要的特征.
1 内连接查询
内连接是最典型,最常用的连接查询,它根据表中共同的列来进行匹配,特别是两个表存在主外键关系时通
常会使用到内连接查询.
内连接查询可以通过两种方法实现
1.1在where子句中指定连接条件
例句:select students.name,courses.coursename,scores.score
from students,courses,scores
where students.studentid=scores.studentid and courses.courseid=scores.courseid
这种形式的查询,相当于from后面紧跟了两个或者多个表名,然后字段列表中用’表名.列名’来
区分列,再在where条件子句中加以判断,要求学员编号还有课程编号相等.
1.2在from子句中使用join...on
我们呢就可以把上面的语句通过join...on语句来实现
例句:select students.name,courses.coursename,scores.score from scores
inner join students on (students.studentid=scores.studentid)
inner join courses on (courses.courseid=scores.courseid)
2 外连接查询
内连接的结果是从两个或者两个以上的表的组合中挑选出符合链接条件的数据,如果数据无法满足连接
条件则将其丢弃.与内部链接相对的方法称为外部链接,在外部链接中参与链接的表有主从之分,以主表的
每行数据去匹配从表的数据列,符合链接条件的数据将直接返回到结果集中,对那些不符合链接条件的列,
将被填上null值后再返回到结果集中.
1.1 左外连接查询
语法:From 左表 Left [outer] join 右表 on 连接条件
左外链接的结果集包括left join子句中指定的左表的所有行,而不仅仅是链接列所匹配的行,如
果左表的某行在右表中没有匹配行,则在相关联的结果集右表的行中所有选择列均为空值
例如要统计所有学生的考试成绩,就算没有考试的也要显示出来,这时候,学生信息表为左表,也就是主表.
例句:select students.name,courses.coursename,scores.score from students
left join scores on scores.studentid= students.studentid
left join courses on courses.courseid=scores.courseid
1.2 右外连接查询
右外链接查询与左外链接查询类似,只不过要包含右表中所有匹配的行.如果右表中有的项在左
表中没有对应的项,则以null值来填充.例如,在我们课程表中有的课程没有考试,我们成绩表中也自然没
有他们的成绩
语法: from 左表 right [outer] join 右表 on 连接条件
例句: select students.name,courses.coursename,scores.score from students
right join scores on scores.studentid= students.studentid
right join courses on courses.courseid=scores.courseid
1.3 全外链接
全外链接对两个表或者多个表都不加限制,所有表中的行都会包括在结果集中.
语法:from 左表 full [outer] join 右表 on 连接条件
例句: select students.name,courses.coursename,scores.score from students
full join scores on scores.studentid= students.studentid
full join courses on courses.courseid=scores.courseid
五 案例分析
一家银行发行了一批信用卡,刚开始做的还可以,等到后来就出现了好多的废卡,好多用户的钱不到2块
钱,而且长时间的不用,银行就把这些卡的信息备份了下然后从数据库中删除了,删除之后就出现问题了,
那些客户发现卡不能用就来银行投诉了,这时候银行只好再把数据库恢复了.
主表users中以帐号cardid列作为主键.
子表account中以countid为主键,cardid列引用users表中的cardid列为外键,rmb存储用户的余额.
现在需要编写sql语句把users表中有的cardid列插入到account中没有的cardid列中.插入的rmb数值
是2.
分析:1.首先这个是插入语句,需要用到insert语句,这种插入方式是从其他表中筛选数据然后
在插入,要使用到我们前面学习过的insert into select结构.
2.插入的数据项中是子表中没有,而主表中存在的,我们可以用上面学习的外连接,以
users作为主表来连接我们的子表.
例句: select users.cardid,account.cardid from users
left join account on (users.cardid=account.cardid)
3.最后,子表中不存在的数据项,我们用where条件来筛选account.cardid is null 判断
我们所要插入的cardid.
例句: insert into account (account.cardid,account.rmb)
select users.cardid,2 from users
left join account on account.cardid=users.cardid
where account.cardid is null
SQL语言由：数据定义语言（DDL），数据操作语言（DML）和数据控制语言（DCL）组成。
1）数据定义语言（DDL）
create table,alter table和drop table。用于创建表，修改期属性和删除表。
2)数据操作语言（DML）
用于查询和操作数据。select,insert,update,delete。
3)数据控制语言（DCL）
用于控制对象库对象操作的权限。grant和revoke
二 聚合函数
在查询中还会经常碰到的要求是取某些列的最大值,最小值,平均值等信息,有时候还需要计算出究竟查询到多少行数据项.这个时候,查询的’统计数据’是大家比较关心的,sql server提供了’聚合函数’,聚合函数能够基于列进行计算,并返回单个值.
Sql server提供了下面几个聚合函数
1 sum
sum返回表达式中所有数值的总和,sum只能用于数字类型的列,不能够汇总字符,日期等其他数据类型,例如查询表中所有人员的月薪总和.
例句: select sum (rmb) from user1
也可以查询经理们的月薪总和: select sum (rmb) from user1 where posts='经理'
这种查询只返回一个数字,不能够直接与可能返回多行的列一起使用进行查询.
如: select truename,sum (rmb) from user1 where posts='经理'
这个因为有truename字段，而符合post='经理'的值有多行，但sum(rmb)只返回一个数字，产生冲突，故出错。
但是在一个查询中可以同时使用多个聚合函数.
2 avg
avg 函数返回表达式中所有数值的平均值,avg函数也只能用于数字类型的列,例如查询经理人员的平均月薪.
例句: select avg(rmb) from user1 where posts='经理'
3 max 和min
max返回表达式中的最大值, min返回表达式中的最小值,它们都可以用于数字型,字符型以及日期/时间类型的列.
查询经理人员中最高月薪,最低月薪还有平均月薪.
例句: select max(rmb) as 最高月薪,min(rmb) as 最低月薪,avg(rmb) as 平均月薪from user1 where posts='经理'
4 count
Count返回提供的表达式中非空值的计算,count可以用于数字和字符类型的列.
另外,也可以使用星号(*)作为count的表达式,使用星号可以不必指定特定的列尔计算所有的行数.
例如查询月薪在3000以上的人员
例句: select count(*) from user1 where rmb > 3000
三，其它相关
1.select
1)distinct 消除重复值
select distinct 出版社 from book1
2)使用top n(percent)仅返回n行
use book
go
select top 5* from book1
go
显示前5条
use book
go
select top 5 percent * from book1
go
显示前百分之五条记录
3)修改查询结果中列的标题（别名）
(1)使用=号，显示别名=元组
use book
go
select '图书编号'=编号,'图书书号'=ISBN号 from book1
go
(2)别名加单引号，空格与元组分开
use book
go
select 编号 '图书编号',ISBN号 '图书书号' from book1
go
(3)别名与元组用AS分开
use book
go
select 编号 as '图书编号',ISBN号 as '图书书号' from book1
go
4)在查询结果中附加字符串
要增加的字符串用单引号括起，后接列名字.
use book
go
select 书名, '图书定价为：',定价
from book1
go
5)使用where子句给出查询的条件，使用
(1)比较运算符：=,<>,!=,>,>=,!>,<,<=,!<.
(2)逻辑运算符：and,or,not.用来连接表达表.
eg, where 定价>50 and 出版社='中山大学' 图书定价在50元以上，并出版社为中山大学。
6)使用ORDER BY子句重新排列查询结果
说明：默认(即省略ASC,DESC时)为“升序”，ASC（升序），DESC（降序）
(1)要求selcct出来的内容，按“定价”的降排序
use book
go
select 书名,定价 from book1 order by 定价 desc
(2)要求查询结果首先按“书名”降序排序，“书名”相同时，则按“编号”升序排序
use book
go
select 书名,编号,出版社 from book1 order by 书名 desc,编号 asc(这个ase可以省的，因为默认是
升序）
go
7)使用IN关键字，可简化查询语句
(1)查询编号为'XH5468','YBZT0001','YBZT0024'的书名称。
方法一：使用逻辑运算式or
use book
go
select 书名
from book1
where 编号='XH5468' OR 编号='YBZT0001' OR 编号='YBZT0024'
GO
方法二：使用IN
use book
go
select 书名
from book1
where 编号 IN('XH5468','YBZT0001','YBZT0024')
GO
(2)查询编号不为'XH5468','YBZT0001','YBZT0024'的书名称。
方法一：使用逻辑运算式or
use book
go
select 书名
from book1
where 编号<>'XH5468' and 编号<>'YBZT0001' and 编号='YBZT0024'
GO
方法二：使用IN
use book
go
select 书名
from book1
where 编号 NOT IN('XH5468','YBZT0001','YBZT0024')
GO
8)使用LIKE关键字进行模糊查询，
SQL的4个通配符为：
(1)%百分比：匹配包含0个或多个字符的字符串。
like '中%'：匹配以“中”字开头的字符串。
(2)_下划线：匹配任何单个的字符。
like '_人'：匹配第二个为“人”的字符串。
(3)[]排列通配符，匹配任何在范围或集合之内的单个字符。如[M-P]匹配的是m,n,o,p单个字符。
like '[ck]ars[eo]n':表示carsen,carson或karsen,karson
(4)[^]不在范围之内的字符，匹配任何不在范围或集合之内的单个字任。
like 'n[^c]%'：匹配所有以字母n开始，并且第二个字母不是c的所有字符串。
要查找通配符本身时，需将它们用方括号括起来。
like'[[]'：表示要匹配"['
like '5[%]':表示要匹配“5%".
注：通配符和字符串必须括在“单引号”中。
例子:
(1)
use book
select *
from book1
where 书名 like '中%'
go
(2)查询第二个字为“人”的所有图书
use book
select *
from book1
where 书名 like '_人%'
go
(3)查询第一个字不为“半”的所有图书
use book
select *
from book1
where 书名 like '[^半]%'
go
(4)使用NOT LIKE查询
use book
select *
from book1
where 书名 not like '半%'
go
9)使用IS NULL关键字查询没有赋值的行
(1)查询book1表中没有出版社的所有记录
use book
go
select *
from book1
where 出版社 is null
go
10)查询某一范围内的信息
表达式的where中使用“比较运算符”: =,<,<>,!>,!=,>=,<=,!>,!<,between,not between
"逻辑运算符“：not,and,or
"范围运算符“：between,not between.
(1)定价大于1000并且小于2000的记录
use book
go
select *
from book1
where 定价>1000 and 定价<2000
go
(2)定价大于1000并且小于2000的记录
use book
go
select *
from book1
where 定价 between 1000 and 2000
go
(3)定价大于2000并且小于5的记录，并按降序排列
use book
go
select *
from book1
where 定价 not between 5 and 2000 order by 定价 desc
go
11)使用compute子句
用于计算总计或进行分组小计。
(1)查询“出版社”为“中国长安”的记录，并计算“定价”的平均值
use book
go
select *
from book1
where 出版社='中国长安'
compute avg(定价)
go
12)使用compute by子句
它对by后面给出的列进行分组显示，并计算该列的分组小计。使用compute by子句必须按照order by和
COMPUTE BY中的by指定列进行排序。
(1)显示book1的记录，并计算每个出版社的平均定价
use book
go
select *
from book1
order by 出版社
compute avg(定价) by 出版社
go
13)使用group by 子句
将查询结果按照group by后指定的列进行分组。该子句写在where子句的后面。当在select子句中包含聚
合函数时，最适合使用GROUP BY子句。SELECT子句中的选项列表中出现的列。包含在聚合函数中或者包
含在GROUPB BY子句中。否则，SQL SERVER将返回如下错误提示消息：
“表名列名在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在GROUP BY 子句中”。
(1)按出版社分类统计每个出版社出书的本数.
use book
go
select 出版社,count(出版社) as '本数'
from book1
group by 出版社
go
解释:select 出版社<这个要与group by 后面的列对应，如group by后面只有“出版社”，这里也只有
能有"出版社“>,count(出版社<这个为聚合函数，当中的字段为任何一个存在的列字段都可以>) as '本
数<这个为显示的别名，可随意取>' from book1 group by 出版社<这个要与select后面的列相对应>
group by后面跟的字段，表明按这个字段来分类。
例句: select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb
按posts与rmb来分类汇总，求个数。
14)HAVING子句
HAVING子句用于限定组或聚合函数的查询条件。
HAVING 只能与 SELECT 语句一起使用。HAVING 通常在 GROUP BY 子句中使用。如果不使用 GROUP BY 子句，则 HAVING 的行为与 WHERE 子句一样。
Having子句是对分组后的条件的筛选,比如这个时候我们需要查看具有相同工资的人员,
例句: select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb having count(*)<>1
上例句的意思是：
在“select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb“得出来的结果的基础之上，查筛出“人数”不为1的数据。
Having和where子句可以在同一个select语句中使用,使用的顺序是首先where然后group by 最后having
15)使用嵌套查询
一个select-from-where语句称为一个查询块。有时一个查询块无法完成查询任务，需要一个子查询的
结果作为主查询语句的条件。
注：(1)子查询在上一级查询处理之前求解。
(2)order by子句只能对最终查询结果排序。
(1)查询图书定价大于平均定价的图书记录
use book
go
select *
from book1
where 定价>(select avg(定价) from book1)
go
16)使用UNION运算符
用于将两个或多个查询结果合并成一个结果，当使用UNION运算符时，需要遵循以下两个规则：
(1)所有查询中列数和列的顺序必须要相同。
(2)所有查询中按顺序对应列的数据类型必须兼容。
(1)book1图书名，book2图书编号，进行union运算合并这两个查询结果.
use book
go
select 书名 from book1
union
select 编号 from book2
go
17)对多个表进行查询
1>笛卡儿积
use book
go
select *
from book1,teacher
go
结果为：144478行,11列;
boo1表列数(6)+teacher表列数(5)=11列；
book1表的行数2413 x teacher表的行数6=14478行
这种结果达不到我们需要的结果，而且会造成非常大的运算，特别是表大时，可以使计算机死机，慎用
。
2>连接条件
为了避免产生笛卡儿积，并且得到所需的查询结果。必须使用where子句给出连接条件。一般来说，对
N个表（或视图）的查询要有N-1个连接条件.
(1)查询编号为'XH5468"的书名，定价（在book1表中），作得姓名和作者的职称（在teacher表中），但
双方的编号一致。
use book
go
select book1.编号,书名,定价,作者姓名,职称
from book1,teacher
where book1.编号=teacher.编号 and book1.编号='XH5468'
GO
连接的类型：内连接（自然连接，相等连接），外连接（左外连接，右外连接，全外连接），交叉连接
。
在SQL SERVER中，可以使用两个连接语法形式：
1)ANSI连接语法形式。
2)使用SQL SERVER连接语法形式。
3>相等连接
相等连接是将要连接的列作相等比较后所作的连接，相等连接总会产生冗余，因为连接的列要显示两次
。‘
(1)查询所有图书和所有作者的信息
use book
go
select *
from book1,teacher
where book1.编号=teacher.编号
go
过程，先计逄两个表的“笛卡儿积”，然后再消除不满足连接条件（ book1.编号=teacher.编号)的记录
. 但在查询的结果中有完全相同的两列“编号”。
使用ANSI连接语法的select语句如下：
use book
go
select *
from boo1 inner join teacher
on book1.编号=teacher.编号
go
显示结果同上。
4>自然连接
自然连接是将要连接的列作相等比较的连接。但是连接的列只显示一次，自然连接消除了相等连接产生
的冗余。
(1)查询所有图书和所有作者的信息，要求连接的列只显示一列
use book
go
select book1.*,作者姓名,职称
from book1,teacher
where book1.编号=teacher.编号
go
5>带有选择条件的连接
在对多表查询时，在指定的连接条件之外也可以包括其他的选择条件。
use book
go
select book1.书名,book1.定价,teacher.作者姓名,book1.出版社
from book1,teacher
where book1.编号=teacher.编号 and book1.定价>=50
go
6>不等值连接
在连接条件中使用等于运算符以外的其他比较运算符（>,>=,<=,<,!>,!<,<>)来比较被连接的列值。
(1)查询book1表和book2表中凡是编号不相等的所有书的书名和编号信息。
use book
go
select book1.书名，book1.编号,book2.书名,book2.编号
from book1,book2
where book1.编号<>book2.编号
go
7>自连接
如果所连接的两个表为同一个表，那么这种连接又称为“自连接”。自连接能把一个表中的行和该表中
的另外一些行联系起来。
(1)在book1表查询与编号为'YBZT0005'图书的出版社相同的书名，定价和出版日期。
分析：将book1表定义别名为a,b
use book
go
select a.编号,a.书名,a.定价,a.出版日期
from book1 a, book1 b
where a.出版社=b.出版社 and a.编号<>'YBZT0005' AND b.编号='YBZT0005'
go
8>左外连接
use book
go
select book1.书名,book1.出版社,ISBN号,teacher.作者姓名
from teacher LEFT JOIN book1
on book1.编号=teacher.编号
go
9>右外连接
右外连接与左外连接相对应，右外连接首先计算两个表的自然连接，再取出右表中与左表任一数据行都
不匹配的那些行，用空值填充所有来自左表的那些列，再把增加的这些行增加到自然连接的结果集中。
(1)使用右外连接查询book1表中凡是有作者信息的所有图书的信息
use book
go
select book1.书名,book1.出版社,ISBN号,teacher.作者姓名
from teacher RIGHT JOIN book1
ON book1.编号=teacher.编号
go
10>全外连接
为了包含两个表中都不匹配的数据行，可以使用全外连接，它完成左外连接和右外连接的操作，包括了
左表和右表所有不满足条件的行。
use book
go
select book1.书名,book1.出版社,ISBN号,teacher.作者姓名
from teacher FULL JOIN book1
ON book1.编号=teacher.编号
go
11>交叉连接
交叉连接就是将连接的两个表的所有行进行组合，形成一个结果集，该结果的列数等于两个表的列数和
，行数等于两个表的行数积。
(1)计算book1表和teacher表的交叉连接
use book
go
select *
from teacher,book1
go
12>使用表别名
可以给表定义别名，以方便查询时对列的引用和简化连接条件的书写。
别名的定义方法：在FROM子句中将要定义的别名的表用空格间隔，然后紧随所定义的别名。
如：from book1 b,将b定义为book1表的别名。
use book
go
select b.书名,b.定价,t.作者姓名
from teacher t,book1 b
where t.编号=b.编号
go
13>使用EXISTS关键字
EXISTS子句用于测试跟随的子查询中的行是否存在，如果存在则返回TURE。
use book
go
select 书名,定价
from book1
where exists (select 编号 from teacher)
go
=====================附他人的说明================
一 模糊查询
模糊查询提取的数据不一定是确切的,查询者对查询条件也是模糊的,大概的,不特别明确的.比如我们
经常用到的搜索引擎google,baidu等,都是典型的模糊查询,我们也不知道确切的答案,打开网页搜索之后
就在那些搜索出来的结果当中进行排除,找到我们需要的答案.模糊查询可以使用我们前面已经介绍过的
like,通配符来进行,上节课学习的is null从严格意义上来说也是模糊查询.
1 使用like进行模糊查询
前面的课程中我们已经介绍过使用like来编写约束,like运算符用于匹配字符串或字符串的一部分.该运
算符只用于字符串.在数据更新,删除或者查询的时候,都可以用like关键字来进行匹配查找.
例句: select * from user1 where companyname like '北京%' order by id
2 使用between在某个范围内进行查询
使用关键字between可以查找那些介于两个已经知道数值之间的一组未知值,要实现这种查找,首先
要知道开始查找的初始值和最终值.这个最大值和最小值用单词and分开,例句: select * from user1
where rmb between 1000 and 2000
Between在查询日期范围的时候使用得比较多.例如,查询生日在1980到1990之间的
例句: select * from user1 where birthday between '1980/1/1' and '1990/12/31'
3 使用in在列举值内进行查询
查询的值是指定的某些值之一,可以使用带列举值的in关键字来进行查询.将列举值放在圆括号里,用逗
号分开,例如查询职位是经理的姓名
例句: select truename as 经理人员表from user1 where posts in ('经理')
二 聚合函数
在查询中还会经常碰到的要求是取某些列的最大值,最小值,平均值等信息,有时候还需要计算出究竟查
询到多少行数据项.这个时候,查询的’统计数据’是大家比较关心的,sql server提供了’聚合函数’,
聚合函数能够基于列进行计算,并返回单个值.
Sql server提供了下面几个聚合函数
1 sum
sum返回表达式中所有数值的总和,sum只能用于数字类型的列,不能够汇总字符,日期等其他数据类型,
例如查询表中所有人员的月薪总和.
例句: select sum (rmb) from user1
也可以查询经理们的月薪总和: select sum (rmb) from user1 where posts='经理'
这种查询只返回一个数字,不能够直接与可能返回多行的列一起使用进行查询.
例句: select truename,sum (rmb) from user1 where posts='经理'
但是在一个查询中可以同时使用多个聚合函数.
2 avg
avg 函数返回表达式中所有数值的平均值,avg函数也只能用于数字类型的列,例如查询经理人员的平均月
薪.
例句: select avg(rmb) from user1 where posts='经理'
3 max 和min
max返回表达式中的最大值, min返回表达式中的最小值,它们都可以用于数字型,字符型以及日期/时间类
型的列.
查询经理人员中最高月薪,最低月薪还有平均月薪.
例句: select max(rmb) as 最高月薪,min(rmb) as 最低月薪,avg(rmb) as 平均月薪from user1 where
posts='经理'
4 count
Count返回提供的表达式中非空值的计算,count可以用于数字和字符类型的列.
另外,也可以使用星号(*)作为count的表达式,使用星号可以不必指定特定的列尔计算所有的行数.
例如查询月薪在3000以上的人员
例句: select count(*) from user1 where rmb > 3000
三 分组查询
我们这个表中存储了来自不同地域,不同公司,不同职位的人员工资,我们现在要算一下在同一职位的平
均月薪,也就是说要把他们的职位来进行分组,分组以后再进行聚合计算,得到累计信息.再比如一个餐馆
营业一天之后他们需要计算出今天卖出的酒水有多少钱,主食有多少钱,炒菜有多少钱.这种类型的查询,
在sql server中叫做分组查询.
1 使用group by 来进行分组查询
注意: 指定 GROUP BY 时，选择列表中任意非聚合表达式内的所有列都应包含在 GROUP BY 列表中
，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。
例句: select posts as 职位,avg(rmb) as 平均月薪from user1 group by posts
分组查询有时候还需要按照多个列来进行分组,比如我们要查看他们具体的工资情况,我们还需要把它们
的月薪进行分组.
例句: select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb
2 使用having 子句进行分组筛选
HAVING 只能与 SELECT 语句一起使用。HAVING 通常在 GROUP BY 子句中使用。如果不使用 GROUP
BY 子句，则 HAVING 的行为与 WHERE 子句一样。
Having子句是对分组后的条件的筛选,比如这个时候我们需要查看具有相同工资的人员,
例句: select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb
having count(*)<>1
上例句的意思是：
在“select posts as 职位,count(posts) as 人数,rmb as 月薪from user1 group by posts,rmb“得
出来的结果的基础之上，查筛出“人数”不为1的数据。
Having和where子句可以在同一个select语句中使用,使用的顺序是首先where然后group by 最后
having
四 多表连接查询
前面我们学习的所有查询都是基于单个数据表的查询,下面我们将介绍牵涉到多个表的数据查询,多表
查询实际上是通过各个表之间共同列的关联性来查询数据的,它是关系型数据库查询最重要的特征.
1 内连接查询
内连接是最典型,最常用的连接查询,它根据表中共同的列来进行匹配,特别是两个表存在主外键关系时通
常会使用到内连接查询.
内连接查询可以通过两种方法实现
1.1在where子句中指定连接条件
例句:select students.name,courses.coursename,scores.score
from students,courses,scores
where students.studentid=scores.studentid and courses.courseid=scores.courseid
这种形式的查询,相当于from后面紧跟了两个或者多个表名,然后字段列表中用’表名.列名’来
区分列,再在where条件子句中加以判断,要求学员编号还有课程编号相等.
1.2在from子句中使用join...on
我们呢就可以把上面的语句通过join...on语句来实现
例句:select students.name,courses.coursename,scores.score from scores
inner join students on (students.studentid=scores.studentid)
inner join courses on (courses.courseid=scores.courseid)
2 外连接查询
内连接的结果是从两个或者两个以上的表的组合中挑选出符合链接条件的数据,如果数据无法满足连接
条件则将其丢弃.与内部链接相对的方法称为外部链接,在外部链接中参与链接的表有主从之分,以主表的
每行数据去匹配从表的数据列,符合链接条件的数据将直接返回到结果集中,对那些不符合链接条件的列,
将被填上null值后再返回到结果集中.
1.1 左外连接查询
语法:From 左表 Left [outer] join 右表 on 连接条件
左外链接的结果集包括left join子句中指定的左表的所有行,而不仅仅是链接列所匹配的行,如
果左表的某行在右表中没有匹配行,则在相关联的结果集右表的行中所有选择列均为空值
例如要统计所有学生的考试成绩,就算没有考试的也要显示出来,这时候,学生信息表为左表,也就是主表.
例句:select students.name,courses.coursename,scores.score from students
left join scores on scores.studentid= students.studentid
left join courses on courses.courseid=scores.courseid
1.2 右外连接查询
右外链接查询与左外链接查询类似,只不过要包含右表中所有匹配的行.如果右表中有的项在左
表中没有对应的项,则以null值来填充.例如,在我们课程表中有的课程没有考试,我们成绩表中也自然没
有他们的成绩
语法: from 左表 right [outer] join 右表 on 连接条件
例句: select students.name,courses.coursename,scores.score from students
right join scores on scores.studentid= students.studentid
right join courses on courses.courseid=scores.courseid
1.3 全外链接
全外链接对两个表或者多个表都不加限制,所有表中的行都会包括在结果集中.
语法:from 左表 full [outer] join 右表 on 连接条件
例句: select students.name,courses.coursename,scores.score from students
full join scores on scores.studentid= students.studentid
full join courses on courses.courseid=scores.courseid
五 案例分析
一家银行发行了一批信用卡,刚开始做的还可以,等到后来就出现了好多的废卡,好多用户的钱不到2块
钱,而且长时间的不用,银行就把这些卡的信息备份了下然后从数据库中删除了,删除之后就出现问题了,
那些客户发现卡不能用就来银行投诉了,这时候银行只好再把数据库恢复了.
主表users中以帐号cardid列作为主键.
子表account中以countid为主键,cardid列引用users表中的cardid列为外键,rmb存储用户的余额.
现在需要编写sql语句把users表中有的cardid列插入到account中没有的cardid列中.插入的rmb数值
是2.
分析:1.首先这个是插入语句,需要用到insert语句,这种插入方式是从其他表中筛选数据然后
在插入,要使用到我们前面学习过的insert into select结构.
2.插入的数据项中是子表中没有,而主表中存在的,我们可以用上面学习的外连接,以
users作为主表来连接我们的子表.
例句: select users.cardid,account.cardid from users
left join account on (users.cardid=account.cardid)
3.最后,子表中不存在的数据项,我们用where条件来筛选account.cardid is null 判断
我们所要插入的cardid.
例句: insert into account (account.cardid,account.rmb)
select users.cardid,2 from users
left join account on account.cardid=users.cardid
where account.cardid is null
