数据库查询操作实例

1、1.数据检索数据检索 【SELECT语句语句】 用途是从一个或多个表中检索信息。用途是从一个或多个表中检索信息。 （1） 检索单个列检索单个列 Select 读者读者ID from 读者读者 （2） 检索多个列：检索多个列： Select 读者读者ID,姓名姓名,性别性别,联系地址联系地址 from 读者读者 （3） 检索所有列检索所有列 Select * from 读者读者一单表查询一单表查询 （4） 检索不同的值检索不同的值 DISTINCT 比较：比较：Select 班级班级 from 读者读者 Select DISTINCT 班级班级 from 读者读者 （5） 限制结果限制结果 TO

2、P n Select TOP 10 读者读者ID,姓名姓名,性别性别 from 读者读者 （6）使用注释）使用注释 - - 这是一条注释这是一条注释一单表查询一单表查询 （7）使用别名）使用别名 用用“空格或空格或AS或或=”表示别名表示别名 SELECT 姓名姓名 Name FROM 读者读者 SELECT 性别性别 AS Sex FROM 读者读者 SELECT CLASS=班级班级FROM 读者读者 （8）字段组合查询）字段组合查询 + 连接字符连接字符 SELECT 读者读者ID,姓名姓名, 性别性别 +性别性别+ ,班级班级+班级班级 AS 读者信息读者信息 FROM 读者读者 SE

3、LECT 读者读者ID,姓名姓名,性别性别+ 班级班级 AS 读者信息读者信息 FROM 读者读者一单表查询一单表查询 select CAST(year(getdate() as varchar(4)+年年 +CAST(month(getdate() as varchar(2) +.（仿照完成）（仿照完成） as 日期日期 （9）字段计算查询）字段计算查询 SELECT 读者读者ID, 借阅记录号借阅记录号,借阅期限借阅期限原借阅期限原借阅期限, 借阅期限借阅期限+10 修修改后借阅期限改后借阅期限 FROM 借阅记录借阅记录 （10）无字段查询）无字段查询 Select 1第第1列列,2第第

4、2列列,3第第3列列 SELECT MONTH(2016-3-21)月月,YEAR(getdate()年年一单表查询一单表查询2.排序检索数据排序检索数据 【order by子句子句】 （1）语法结构）语法结构 SELECT 字段列表字段列表 FROM 表表 ORDER BY 排序字段排序字段 （2）按一个列排序）按一个列排序 SELECT 图书图书ID,借阅日期借阅日期,还书日期还书日期FROM 借阅记录借阅记录 ORDER BY 借阅日期借阅日期 DESC一单表查询一单表查询 （3）按多个列排序）按多个列排序 Select * from 读者读者 order by 班级班级,姓名姓名 （4

5、）按列位置排序）按列位置排序 Select * from 读者读者 order by 7,2 （5）排序方向）排序方向 Select * from 读者读者 order by 班级班级asc,姓名姓名 desc 一单表查询一单表查询3. 过滤数据过滤数据 【where 条件条件】 （1）比较运算符：）比较运算符： =、= 、= 、!= Select * from 读者读者 where 班级班级=15软件（软件（7）班）班 （2）逻辑运算符：）逻辑运算符：not 、and、or Select * from 图书图书 where 类别类别=农业科学农业科学 and 出版社出版社=海洋出版社海洋出版

6、社 优先级优先级not，and，or （3）范围运算符：）范围运算符：betweenand Select * from 图书图书 where 单价单价 between 18 and 20一单表查询一单表查询 （4）列表运算符：）列表运算符：in 、not in SELECT 图书名图书名,作者作者,类别类别,字数字数,单价单价 FROM 图书图书 WHERE 类别类别 IN(农农业科学业科学,工业技术工业技术,文学文学) （5）字符运算符）字符运算符 like、not like% 任意字符任意字符: %明明,陈陈%,%华华%_ 单个字符：单个字符：李李_,_明明,_明明_字符列表字符列表 ：包

7、括其中一个字符：包括其中一个字符字符列表字符列表 ：不包括其中任意字符：不包括其中任意字符A-M,A-M：连续字符：连续字符一单表查询一单表查询 select * from 读者读者where 姓名姓名 like %明明% select * from 读者读者 where 姓名姓名 not like 张刘张刘% 思考：思考：查询姓王、刘、张名字中不包括华、花同学信息查询姓王、刘、张名字中不包括华、花同学信息 select * from 读者读者 where 姓名姓名 like 王刘张王刘张% and 姓名姓名 not like %华花华花%一单表查询一单表查询 （6）检测字符）检测字符 is

8、null，is not null SELECT 借阅记录号借阅记录号,图书图书ID,借阅日期借阅日期 FROM 借阅记录借阅记录 WHERE 还书日期还书日期 IS NULL一单表查询一单表查询4. 分组统计查询分组统计查询 【group by】 (1) 统计函数统计函数 Count():求行数求行数 使用使用COUNT(*)对表中行的数目进行计数，含对表中行的数目进行计数，含 NULL值。值。 使用使用COUNT(column)对特定列中具有值的行进行计数。对特定列中具有值的行进行计数。 Sum()：求和：求和 AVG()：求均值：求均值 MAX()：求最大值，最大数值或日期值，忽略值为：求

9、最大值，最大数值或日期值，忽略值为NULL行行 MIN()：求最小值：求最小值 ABS() ：求绝对值：求绝对值一单表查询一单表查询 (2)聚集不同值聚集不同值 对所有行执行计算，指定对所有行执行计算，指定ALL参数或不指定参数（参数或不指定参数（ALL是默认）。是默认）。 只包含不同的值，指定只包含不同的值，指定DISTINCT参数，但只能用于列名，不用于参数，但只能用于列名，不用于*，和，和聚集函数。聚集函数。 SELECT 出版社出版社,AVG(distinct 单价单价) FROM 图书图书 GROUP BY 出版社出版社 (3) 组合聚集函数组合聚集函数 SELECT COUNT(*

10、) AS 行数行数, MIN(单价单价) AS 最低价最低价, MAX(单价单价) AS 最高价最高价, AVG(单价单价) AS 平均价平均价 FROM 图书图书一单表查询一单表查询 (4)分组统计分组统计 比较输出结果：比较输出结果： SELECT AVG(单价单价) FROM 图书图书 SELECT 出版社出版社,AVG(单价单价) FROM 图书图书 GROUP BY 出版社出版社 按出版社分组。（除统计函数外，按出版社分组。（除统计函数外，select 后面的均为分组字段，需列在后面的均为分组字段，需列在group by 之后）之后） select LEFT(姓名姓名,1) as 姓

11、姓, count(LEFT(姓名姓名,1) as 人数人数 from 读者读者 group by LEFT(姓名姓名,1) having count(LEFT(姓名姓名,1) =90 order by count(LEFT(姓名姓名,1) desc一单表查询一单表查询 (5) 过滤分组过滤分组【having子句子句】 与与group by 一起使用，对分组后的数据（含聚合字段）进行筛选。一起使用，对分组后的数据（含聚合字段）进行筛选。 SELECT 出版社出版社,AVG(单价单价) FROM 图书图书 GROUP BY 出版社出版社 HAVING AVG(单价单价)18 SELECT 出版社出

12、版社,AVG(单价单价) FROM 图书图书 GROUP BY 出版社出版社 HAVING 出版社出版社 IN (海洋出版社海洋出版社,清华大学出版社清华大学出版社) 思考：思考：查找统计上学期有查找统计上学期有2门以上不及格同学名单门以上不及格同学名单一单表查询一单表查询 注：注：order by 可用于聚集函数排序可用于聚集函数排序 SELECT 出版社出版社,AVG(单价单价) as 均价均价 FROM 图书图书 GROUP BY 出版社出版社 HAVING AVG(单价单价)18 order by 均价均价 DESC一单表查询一单表查询5. SELECT子句顺序子句顺序一单表查询一单表

13、查询子句说明是否必须使用SELECT要返回的列或表达式是FROM从中检索数据的表仅在从表选择数据时使用WHERE行级过滤否GROUP BY分组说明仅在按组计算聚集时使用HAVING组级过滤否ORDER BY输出排序顺序否1.联结联结 联结是一种机制，用来在一条联结是一种机制，用来在一条SELECT语句中关联语句中关联表，因此称为联结。使用特殊的语法，可以联结表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正多个表返回一组输出，联结在运行时关联表中正确的行。确的行。二多表查询二多表查询2.创建联结创建联结 SELECT 读者读者.读者读者ID,借阅记录借阅记录.

14、读者读者ID, 姓名姓名,班级班级,图书名图书名,借阅日期借阅日期, 借阅记录借阅记录.图书图书ID,图书图书.图书图书ID FROM 读者读者,图书图书,借阅记录借阅记录 WHERE 读者读者.读者读者ID=借阅记录借阅记录.读者读者ID AND 借阅记录借阅记录.图书图书ID=图书图书.图书图书ID 注：（注：（1）（用一个句在引用的列可能出现歧义时，必须使用完）（用一个句在引用的列可能出现歧义时，必须使用完全限定列名点分隔表名和列名）；全限定列名点分隔表名和列名）； （2）用）用where限定条件进行筛选。主键与外键连接，条件筛选。限定条件进行筛选。主键与外键连接，条件筛选。二多表查询二

15、多表查询3.为表定义别名为表定义别名 SELECT 姓名姓名,班级班级,图书名图书名,借阅日期借阅日期 FROM 读者读者 A,图书图书 B,借阅记录借阅记录 C WHERE A.读者读者ID=C.读者读者ID AND C.图书图书ID=B.图书图书ID二多表查询二多表查询4.内连接内连接【inner join】 （1）內连接有等值连接、非等值连接、自然连接）內连接有等值连接、非等值连接、自然连接三种类型，语法结构为：三种类型，语法结构为： Select 字段列表字段列表 from 表表1 inner join 表表2 On 连接条件连接条件 -主键与外键连接条件主键与外键连接条件 Where

16、 筛选条件筛选条件 -等值与非等值连接等值与非等值连接 Order by 排序字段排序字段 -字段列表中不重复为自然连接字段列表中不重复为自然连接二多表查询二多表查询（2）连接实例）连接实例 以下两种情况等效以下两种情况等效 SELECT 借阅记录借阅记录.*,读者读者.* FROM 读者读者,借阅记录借阅记录 WHERE 读者读者.读者读者ID=借阅记录借阅记录.读者读者ID Go SELECT 借阅记录借阅记录.*,读者读者.* FROM 读者读者 inner join 借借阅记录阅记录 on 读者读者.读者读者ID=借阅记录借阅记录.读者读者ID二多表查询二多表查询（3）联结多个表）联结

17、多个表 SELECT 姓名姓名,班级班级,图书名图书名,借阅日期借阅日期 FROM 借阅记录借阅记录 C inner join 读者读者 A on A.读者读者ID=C.读者读者ID inner join 图书图书 B on C.图书图书ID=B.图书图书ID二多表查询二多表查询5.自身连接自身连接 查询读者表中名字相同但班级不同的记录查询读者表中名字相同但班级不同的记录 SELECT A.读者读者ID,A.姓名姓名,A.性别性别,A.生日生日,A.班级班级 FROM 读者读者 AS A INNER JOIN 读者读者 AS B ON A.姓名姓名=B.姓名姓名 AND (A.班级班级 B.班

18、级班级) order by A.姓名姓名distinct二多表查询二多表查询6.外连接外连接 （1）左外连接）左外连接 【left outer join】 查询学生借书情况，包含读者表中所有记录，未借书查询学生借书情况，包含读者表中所有记录，未借书用用NULL select 读者读者.读者读者ID,姓名姓名,性别性别,生日生日,班级班级,借阅记录借阅记录.图书图书ID from 读者读者 left outer join 借阅记录借阅记录 on 读者读者.读者读者ID=借阅记录借阅记录.读者读者ID where 班级班级=15软件软件(5)班班二多表查询二多表查询 select 读者读者.读者读

19、者ID,姓名姓名,性别性别,生日生日,班级班级,count(借借阅记录阅记录.图书图书ID) as 借书量借书量 from 读者读者 left outer join 借阅记录借阅记录 on 读者读者.读者读者ID=借阅记录借阅记录.读者读者ID where 班级班级 like %软件软件% group by 读者读者.读者读者ID,姓名姓名,性别性别,生日生日,班级班级 order by 借书量借书量 desc二多表查询二多表查询（2）右外连接）右外连接 【right outer join】 查询学生借书情况，包含借阅记录表中所有记录，查询学生借书情况，包含借阅记录表中所有记录，无数据用无数据

20、用NULL select 读者读者.读者读者ID,姓名姓名,性别性别,生日生日,班级班级,借阅记录借阅记录.图书图书ID from 读者读者 right outer join 借阅记录借阅记录 on 读者读者.读者读者ID=借阅记录借阅记录.读者读者ID二多表查询二多表查询（3）全外链接）全外链接 【full outer join】 包含读者及借阅记录表中所有记录包含读者及借阅记录表中所有记录 select 读者读者.读者读者ID,姓名姓名,性别性别,生日生日,班级班级,借阅记录借阅记录.图书图书ID from 读者读者 full outer join 借阅记录借阅记录 on 读者读者.读者读

21、者ID=借阅记录借阅记录.读者读者ID二多表查询二多表查询7.交叉连接交叉连接【cross join】 select 读者读者.读者读者ID,姓名姓名,性别性别,生日生日,班级班级,借阅记录借阅记录.图书图书ID from 读者读者 cross join 借阅记录借阅记录 -where 读者读者.读者读者ID=借阅记录借阅记录.读者读者ID -order by 姓名姓名二多表查询二多表查询 由没有联结条件的表关系返回的结果为笛卡儿积，由没有联结条件的表关系返回的结果为笛卡儿积，也叫交叉联结（也叫交叉联结（cross join）。检索出的行的数目）。检索出的行的数目将是第一个表中的行数乘以第二个

22、表中的行数将是第一个表中的行数乘以第二个表中的行数（类推）。（类推）。与下列代码等效与下列代码等效 select 读者读者.读者读者ID,姓名姓名,性别性别,生日生日,班级班级,借阅记录借阅记录.图书图书ID from 读者读者,借阅记录借阅记录二多表查询二多表查询8. 使用带聚集函数的联结使用带聚集函数的联结 select 班级班级,COUNT(*) as 借书量借书量 from 读者读者,借阅记录借阅记录 where 读者读者.读者读者ID=借阅记录借阅记录.读者读者ID group by 班级班级 order by 借书量借书量二多表查询二多表查询1案例：查询与案例：查询与“钟强钟强”同

23、班的男同班的男学生名单（自身连接）学生名单（自身连接） SELECT 读者读者ID,姓名姓名,性别性别,生日生日,班级班级 FROM 读者读者 where 班级班级=(select 班级班级 from 读者读者 where 姓名姓名 =钟强钟强) and 性别性别=男男三三.子查询和嵌套查询子查询和嵌套查询2带带in的子查询（带嵌套）的子查询（带嵌套） SELECT 姓名姓名, 班级班级 FROM 读者读者 WHERE 读者读者ID IN (SELECT 读者读者ID FROM 借阅记录借阅记录 WHERE 图书图书ID not IN (SELECT 图书图书ID FROM 图书图书 WHER

24、E 图书名图书名 = SQL Server 2008数据库应用技术数据库应用技术)三三.子查询和嵌套查询子查询和嵌套查询 select 图书图书ID,图书名图书名,单价单价 from 图书图书 where 图书图书ID in (select 图书图书ID from 借阅记录借阅记录 where 读者读者ID= (select 读者读者ID from 读者读者 where 姓名姓名=曾仁峰曾仁峰) )二多表查询二多表查询3带带any或带或带all的子查询的子查询 （1）带）带any查询查询 select * from 图书图书 where 单价单价any(select 单价单价 from 图书图

25、书 where 出出版社版社=中国农业出版社中国农业出版社) select * from 图书图书 where 单价单价 (select min(单价单价) from 图书图书 where 出版社出版社=中国农业出版社中国农业出版社) 以上两个语句结果相同以上两个语句结果相同三三.子查询和嵌套查询子查询和嵌套查询 （2）带）带all查询查询 select * from 图书图书 where 单价单价all(select 单价单价 from 图书图书 where 出版出版社社=中国农业出版社中国农业出版社) select * from 图书图书 where 单价单价 (select max(单价

26、单价) from 图书图书 where 出版社出版社=中国农业出版社中国农业出版社) 以上两个语句结构相同以上两个语句结构相同三三.子查询和嵌套查询子查询和嵌套查询4.带有带有exists的子查询的子查询 SELECT 读者读者ID,姓名姓名,班级班级 FROM 读者读者 WHERE EXISTS (SELECT * FROM 借阅记录借阅记录 WHERE 读者读者ID=读者读者.读者读者ID AND 图书图书ID=240135) 执行：执行：按外层取按外层取1条记录，送入内层检测是否存在，条记录，送入内层检测是否存在，如存在则存入结果集，否则转入外层的下如存在则存入结果集，否则转入外层的下1

27、条记录条记录继续查询。继续查询。三三.子查询和嵌套查询子查询和嵌套查询5. 作为计算字段使用子查询作为计算字段使用子查询 select 出版社出版社,类别类别,(select COUNT(图书名图书名)from 图书图书 where 出版社出版社=A.出版社出版社 and 类别类别=A.类别类别) as 库存量库存量 from 图书图书 A 不用分组的方法查询按出版社、类别分组统计的不用分组的方法查询按出版社、类别分组统计的库存量库存量三三.子查询和嵌套查询子查询和嵌套查询1.union联合查询联合查询 （1）遵循规则：）遵循规则： UNION必须由两条或两条以上的必须由两条或两条以上的SEL

28、ECT语句组成，语句组成，语句之间用关键字语句之间用关键字UNION分隔。分隔。 UNION中的每个查询必须包含相同的列、表达式中的每个查询必须包含相同的列、表达式或聚集函数。或聚集函数。 列数据类型必须兼容：类型不必完全相同列数据类型必须兼容：类型不必完全相同四联合查询四联合查询 （2）实例：）实例： SELECT姓名姓名,班级班级 FROM 读者读者 where 姓名姓名 like %明明% union SELECT班级，姓名班级，姓名FROM 读者读者 where 姓名姓名 like %平平%四联合查询四联合查询 （3）如果使用）如果使用union all则包括重复行则包括重复行 （4）

29、排序）排序 order by 字段，写在最后，对整个结果集有效。字段，写在最后，对整个结果集有效。四联合查询四联合查询1.环境设置环境设置 exec sp_configure Ad Hoc Distributed Queries,0 reconfigure exec sp_configure show advanced options,0 reconfigure 方面方面服务器配置服务器配置adhocremotequeriesenabled=true五五.分布式查询分布式查询2.创建链接服务器创建链接服务器 exec sp_addlinkedserver srv_lnk,SQLOLEDB,远程

30、服务器名或远程服务器名或ip地址地址 exec sp_addlinkedsrvlogin srv_lnk,false,null,用用户名户名,密码密码 go五五.分布式查询分布式查询3.查询示例查询示例 select * from srv_lnk.数据库名数据库名.dbo.表名表名4.导入示例导入示例 select * into 表表 from srv_lnk.数据库名数据库名.dbo.表名表名五五.分布式查询分布式查询5.直接操作直接操作 select * into 表表 from openrowset(SQLOLEDB,sql服务器名服务器名;用户用户;密码密码,数据库名数据库名.dbo.

31、表名表名) select * into #temp1 from openrowset(SQLOLEDB,xcc;sa;123333,book.dbo.读者读者)五五.分布式查询分布式查询1.操作实例操作实例 (1)新建新建“平均单价平均单价”表，统计出版社分组求单价表，统计出版社分组求单价存入表中。存入表中。 CREATE TABLE 平均单价平均单价 (出版社出版社 CHAR(30),平均单价平均单价 FLOAT) INSERT INTO 平均单价平均单价(出版社出版社,平均单价平均单价) SELECT 出版社出版社,AVG(单价单价) FROM 图书图书 GROUP BY 出版社出版社 S

32、elect * from 平均单价平均单价六六.操作查询操作查询 (2)新建新建“图书分类图书分类”，将指定图书存入表中，将指定图书存入表中 SELECT * INTO 图书分类图书分类 FROM 图书信息图书信息 WHERE 出版社出版社=中国铁道出版社中国铁道出版社 UNION SELECT * FROM 图书信息图书信息 WHERE 出版社出版社=清华大学出版社清华大学出版社六六.操作查询操作查询2. 操作语句操作语句【insert select】 （1）语法结构）语法结构 insert into 目标表目标表(字段列表字段列表) select 字段列表字段列表 from 源表源表 wh

33、ere 条件条件 group by 字段字段 （2）要点：）要点： 目标表一定要存在；目标表一定要存在； 字段列表要一致，包括顺序、字段数目及字段类型；字段列表要一致，包括顺序、字段数目及字段类型； 插入数据符号目标表约束条件。插入数据符号目标表约束条件。六六.操作查询操作查询（3）实例）实例 建立表，插入数据建立表，插入数据 CREATE TABLE 新读者信息新读者信息(读者读者ID nchar(10) NOT NULL,姓名姓名 nchar(16) NULL,性别性别 nchar(2) NULL,班级班级 char(20) NULL) INSERT INTO 新读者信息新读者信息(读者读

34、者ID,姓名姓名,性别性别,班级班级) SELECT 读者读者ID,姓名姓名,性别性别,班级班级 FROM 读者读者六六.操作查询操作查询 建立表，插入数据，带条件筛选建立表，插入数据，带条件筛选 CREATE TABLE 新图书信息新图书信息(图书图书ID nchar(13) NOT NULL,图书名图书名 nchar(40) NOT NULL,出版社出版社 nchar(30) NOT NULL,单价单价 float NULL) INSERT INTO 新图书信息新图书信息(图书图书ID,图书名图书名,出版社出版社,单价单价) SELECT 图书图书ID,图书名图书名,出版社出版社,单价单价 FROM 图书信息图书信息 where 出版社出版社=中国铁道出版社中国铁道出版社 and 单价单价20六六.操作查询操作查询3.操作语句操作语