第五章结构化查询语言SQL

1、2 2第第5 5章章 结构化查询语言结构化查询语言SQL SQL 5.1 5.1 SQLSQL语言概述语言概述5.2 5.2 SQLSQL数据定义数据定义5.3 5.3 SQLSQL数据更新数据更新5.4 SQL5.4 SQL数据查询数据查询 5.4.1 5.4.1 查询的语法查询的语法 5.4.2 5.4.2 基本的查询基本的查询 5.4.3 5.4.3 连接查询连接查询 5.4.4 5.4.4 子查询子查询 5.4.5 5.4.5 合并查询合并查询本章小结本章小结3 35.1 SQL5.1 SQL语言概述语言概述 SQLSQL语言是数据库的标准语言。语言是数据库的标准语言。 高度集成化高度

2、集成化 集数据的定义、操纵、查询和控制功能于一体。集数据的定义、操纵、查询和控制功能于一体。 高度非过程化高度非过程化 用用SQLSQL语言进行数据操作，不必告诉计算机怎么做，只要提出语言进行数据操作，不必告诉计算机怎么做，只要提出做什么，系统就可以自动完成全部工作。做什么，系统就可以自动完成全部工作。 面向集合的操作方式面向集合的操作方式 采用集合操作方式，操作对象和操作结果都是记录集。采用集合操作方式，操作对象和操作结果都是记录集。 简洁易学简洁易学 完成核心功能只用了完成核心功能只用了9 9个命令动词，且接近英语的自然语法。个命令动词，且接近英语的自然语法。 用法灵活用法灵活 既可以独立

3、使用，也可以嵌入到高级语言中使用。既可以独立使用，也可以嵌入到高级语言中使用。4 4在在AccessAccess中使用中使用SQLSQL语句语句 使用方法使用方法 打开查询设计视图。打开查询设计视图。 执行执行“查询查询|SQL|SQL特定查询特定查询| |数据定义数据定义”命令，打开命令，打开“数据定义查询数据定义查询”窗口，输入窗口，输入SQLSQL语句。语句。 注意：在注意：在“数据定义查询数据定义查询”窗口中一次只能输入一条窗口中一次只能输入一条SQLSQL语句。语句。 单击工具栏的单击工具栏的“运行运行”按钮，执行按钮，执行SQLSQL语句。语句。 根据需要，将根据需要，将SQLSQ

4、L语句保存为一个查询对象，或直接语句保存为一个查询对象，或直接关闭查询窗口。关闭查询窗口。5 55.2 SQL5.2 SQL数据定义数据定义 使用使用 CREATE TABLE CREATE TABLE 命令定义表命令定义表 CREATE TABLE CREATE TABLE ( ( () NOT NULL ) NOT NULL PRIMARY KEY | UNIQUE PRIMARY KEY | UNIQUE , , () NOT NULL ) NOT NULL PRIMARY KEY | UNIQUE PRIMARY KEY | UNIQUE , ) , ) 定义表时，必须指定表名、各个字

5、段名及相应的数据类型和字段定义表时，必须指定表名、各个字段名及相应的数据类型和字段大小（由系统自动确定的字段大小省略），并且各个字段之间用大小（由系统自动确定的字段大小省略），并且各个字段之间用英文的逗号分隔。英文的逗号分隔。 字段的数据类型必须用字符表示。字段的数据类型必须用字符表示。 primary key primary key 定义主键，定义主键，unique unique 定义惟一键。定义惟一键。 命令关键词和语句中的其他关键词不区分大小写。命令关键词和语句中的其他关键词不区分大小写。表示可选项表示必选项 | 表示多选1 6 6定义表示例定义表示例【例例5-15-1】使用使用SQLS

6、QL语句定义一个名为语句定义一个名为empemp的表，结构如下：的表，结构如下： 员工号（员工号（TextText，3 3字符）、姓名（字符）、姓名（TextText，4 4字符）、性别字符）、性别（TextText，1 1字符）、婚否（字符）、婚否（LogicalLogical）、聘用日期（）、聘用日期（DateDate）、）、照片（照片（OLEObjectOLEObject）、）、简历（简历（MemoMemo），员工号为主键，姓名不允许为空值。），员工号为主键，姓名不允许为空值。【例例5-25-2】【】【例例5-35-3】7 7建立索引建立索引 使用使用 CREATE INDEX CREA

7、TE INDEX 命令建立索引命令建立索引 CREATE UNIQUE INDEX CREATE UNIQUE INDEX ON ON ( (ASC|DESC,ASC|DESC,ASC|DESC,)2ASC|DESC,) WITH PRIMARY WITH PRIMARY UNIQUE UNIQUE 指定唯一索引，指定唯一索引，WITH PRIMARY WITH PRIMARY 指定主索引。指定主索引。建立索引时关闭表建立索引时关闭表 【例例5-35-3】使用使用SQLSQL语句建立索引，在语句建立索引，在itemitem表的表的“型号型号”字段上建立惟一字段上建立惟一索引，索引名称为索引，索

8、引名称为uni_modeluni_model；在；在“类别类别”和和“价格价格”2 2个字段上分别个字段上分别按升序和降序建立组合索引，索引名称为按升序和降序建立组合索引，索引名称为idx_tpriceidx_tprice。 create unique index uni_model on item(create unique index uni_model on item(型号型号) ) create index idx_tprice on item( create index idx_tprice on item(类别类别, , 价格价格 desc) desc) 8 8建立表间关系建立表间

9、关系 使用使用 Create Table Create Table 命令，在定义表的同命令，在定义表的同时建立表间关系时建立表间关系【例例5-45-4】使用使用SQLSQL语句定义语句定义sale2sale2表，并通过表，并通过“员工号员工号”字字段与段与empemp表建立关系，通过表建立关系，通过“商品号商品号”字段与字段与itemitem表建立表建立关系。关系。references 表示参照引用，即sale2(子表)中的商品号为外键，item(主表)中的商品号为主键9 9修改表修改表 使用使用 Alter Table Alter Table 命令命令 修改字段修改字段 ALTER TABL

10、E ALTER TABLE ALTER ALTER () 使用该命令不能修改字段名。使用该命令不能修改字段名。 添加字段添加字段 ALTER TABLE ALTER TABLE ADD ADD () 删除字段删除字段 ALTER TABLE ALTER TABLE DROP DROP 例如，在例如，在empemp表中增加一个表中增加一个“电话号码电话号码”字段（字段（IntegerInteger），然后将该字），然后将该字段修改为段修改为TextText型（型（1111字符），最后删除该字段。字符），最后删除该字段。 alter table emp add alter table emp ad

11、d 电话号码电话号码 integerinteger alter table emp alter alter table emp alter 电话号码电话号码 text(11)text(11) alter table emp drop alter table emp drop 电话号码电话号码1010删除表和索引删除表和索引 使用使用 DROP DROP 命令命令 删除索引删除索引 DROP INDEX DROP INDEX ON ON 删除表删除表 DROP TABLE DROP TABLE 删除表后，在表上定义的索引也一起被删除。删除表后，在表上定义的索引也一起被删除。例如，使用例如，使用S

12、QLSQL语句删除语句删除itemitem表中的表中的uni_modeluni_model索引项。索引项。 drop index uni_model on itemdrop index uni_model on item 使用使用SQLSQL语句删除语句删除salesale表。表。 drop table saledrop table sale11115.3 SQL5.3 SQL数据更新数据更新 使用使用 Insert Into Insert Into 命令插入记录命令插入记录 INSERT INTO INSERT INTO ( (,2，) VALUES( VALUES(,)2,) 如果缺省字段

13、名，则必须为新记录中的每个字段都赋值，且数据如果缺省字段名，则必须为新记录中的每个字段都赋值，且数据类型和顺序要与表中定义的字段一一对应。类型和顺序要与表中定义的字段一一对应。【例例5-55-5】使用使用SQLSQL语句在语句在empemp表中插入表中插入2 2条员工记录。条员工记录。 insert into emp valuesinsert into emp values (a01, (a01, 马立马立, , 男男, yes, #2011-5-15#, null, null), yes, #2011-5-15#, null, null) insert into emp( insert in

14、to emp(员工号员工号, , 姓名姓名, , 性别性别, , 聘用日期聘用日期) values) values (b01, (b01, 陈慧娟陈慧娟, , 女女, #2011-7-10#), #2011-7-10#)1212SQLSQL数据更新数据更新 使用使用 Update Set Update Set 命令更新记录命令更新记录 UPDATA UPDATA SET SET =1 , ,=,2, WHERE WHERE 例如，使用例如，使用SQLSQL语句将语句将empemp表中所有男员工的表中所有男员工的“婚否婚否”字段改为字段改为“否否”。 update emp set update

15、emp set 婚否婚否=no where =no where 性别性别=男男 使用使用 Delete From Delete From 命令删除记录命令删除记录 DELETE FROM DELETE FROM WHERE WHERE 例如，使用例如，使用SQLSQL语句删除语句删除empemp表中的所有记录。表中的所有记录。 delete from empdelete from emp13135.4.1 5.4.1 查询的语法查询的语法 使用使用 SELECT SELECT 命令实现数据查询功能命令实现数据查询功能 SELECT ALL|DISTINCT TOP SELECT ALL|DIS

16、TINCT TOP PERCENT PERCENT AS AS FROM FROM AS AS ,ASAS2 WHERE WHERE AND AND GROUP BY GROUP BY HAVING HAVING ORDER BY ORDER BY ASC | DESC ASC | DESC 打开查询设计器，从工具栏的视图列表中选择“SQL视图”选项或“设计视图”选项，可以在SELECT命令和查询设计器之间切换。14145.4.2 5.4.2 基本的查询基本的查询 投影投影 从数据源中选择若干列从数据源中选择若干列 SELECT SELECT ,2， FROM FROM 目标列可以是数据源中已

17、有的字段，也可以是一个计算列。目标列可以是数据源中已有的字段，也可以是一个计算列。 若要选择数据源中的所有字段，目标列可表示为若要选择数据源中的所有字段，目标列可表示为“* *”。 在字段名前加上在字段名前加上DISTINCTDISTINCT关键字，可消除重复的记录。关键字，可消除重复的记录。 例如，例如， select select 员工编号员工编号, , 姓名姓名, , 性别性别, , 聘用日期聘用日期 from from 员工员工 select select * * from from 员工员工 select select 员工编号员工编号, , 姓名姓名, , year(date()-

18、year(year(date()-year(聘用日期聘用日期) as ) as 聘用年聘用年数数 from from 员工员工 select select distinctdistinct 商品编号商品编号 from from 销售销售 1515基本的查询基本的查询 选择选择 从表中选出满足条件的记录从表中选出满足条件的记录 SELECT SELECT FROM FROM WHERE WHERE 是一个逻辑表达式，由多个关系表达式通过逻辑是一个逻辑表达式，由多个关系表达式通过逻辑运算符连接而成。运算符连接而成。例如，例如， select select 员工编号员工编号, , 姓名姓名, , 聘

19、用日期聘用日期 from from 员工员工 where where 性别性别=女女 and and 婚否婚否=yes=yes select select * * from from 商品商品 where where 商品编号商品编号 like Dlike D* * 1616基本的查询基本的查询 排序排序 使用使用ORDER BYORDER BY子句可以对查询结果按照一个或多个列的升序子句可以对查询结果按照一个或多个列的升序（ASCASC）或降序（）或降序（DESCDESC）排列，默认是升序。）排列，默认是升序。 ORDER BY ORDER BY ASC|DESC ASC|DESC 可以是字

20、段名，或目标列的序号，如可以是字段名，或目标列的序号，如1 1，2 2，。 在目标列前加上在目标列前加上TOPTOP短语短语，可以从查询结果中选择排在前面的若，可以从查询结果中选择排在前面的若干条记录（用数值或百分比指定）。干条记录（用数值或百分比指定）。 SELECT TOP SELECT TOP PERCENT ORDER BY PERCENT ORDER BY ASC|DESC ASC|DESC例如，例如， select select * * from from 商品商品 where where 价格价格 between 900 and 1200between 900 and 1200

21、order by order by 价格价格 descdesc select select * * from from 销售销售 where where 员工编号员工编号 in(001,003,005)in(001,003,005) order by order by 员工编号员工编号, , 销售日期销售日期 descdesc select select top 3 top 3 * * from from 商品商品 order by order by 价格价格 1717基本的查询基本的查询 分组分组 使用使用GROUP BYGROUP BY子句可以按照某一列的值分组。子句可以按照某一列的值分组

22、。 GROUP BY GROUP BY HAVING HAVING 使用使用HAVINGHAVING短语可以对分组后的结果进行筛选。短语可以对分组后的结果进行筛选。 注意：注意：HAVINGHAVING短语必须和短语必须和GROUP BYGROUP BY子句同时使用。子句同时使用。 分组查询通常与分组查询通常与SQLSQL聚合函数一起使用，先按指定的数据项分组，再对聚合函数一起使用，先按指定的数据项分组，再对各组进行总计，如计数、求和、求平均值等。如果未分组，则聚合函数各组进行总计，如计数、求和、求平均值等。如果未分组，则聚合函数作用于整个表。作用于整个表。 常用常用SQLSQL聚合函数：聚合

23、函数：COUNTCOUNT、AVGAVG、SUMSUM、MINMIN、MAXMAX1818基本的查询基本的查询【例例5-155-15】统计商品表中各类别商品的数量统计商品表中各类别商品的数量 select select 类别类别, count(, count(* *) as ) as 各类别数量各类别数量 from from 商品商品 group by group by 类别类别Select Select 出版周期出版周期 ，avgavg（ 订阅年价订阅年价 ）as as 平均定价平均定价 from from 报纸报纸 group group by by 出版周期出版周期【例例5-165-16

24、】在销售表中查询有在销售表中查询有3 3次或次或3 3次以上销售记录的员工编号次以上销售记录的员工编号 select select 员工编号员工编号 from from 销售销售 group by group by 员工编号员工编号 having count(having count(* *)=3)=3【例例5-175-17】在销售表中查询在销售表中查询20092009年有年有2 2次或次或2 2次以上销售记录的员工编号次以上销售记录的员工编号 SELECT SELECT 员工编号员工编号 FROM FROM 销售销售WHERE year(WHERE year(销售日期销售日期)=2009)=

25、2009GROUP BY GROUP BY 员工编号员工编号 HAVING count( HAVING count(* *)=2;)=2;19195.4.3 5.4.3 连接查询连接查询 连接查询同时涉及连接查询同时涉及2 2个或多个表的数据。个或多个表的数据。 连接查询需要指定连接查询需要指定2 2个表的连接条件，有个表的连接条件，有2 2种格式：种格式： 在在 Where Where 子句中指定连接条件子句中指定连接条件 SELECT SELECT FROM FROM , 2 WHERE WHERE . = = .2 在在 FROM FROM 子句中指定连接条件子句中指定连接条件SELEC

26、T SELECT FROM FROM INNER JOIN | LEFT JOIN | RIGHT JOIN INNER JOIN | LEFT JOIN | RIGHT JOIN 2 ON ON . = = .2 INNER JOIN INNER JOIN 内部连接内部连接,LEFT JOIN ,LEFT JOIN 左外部连接左外部连接,RIGHT JOIN ,RIGHT JOIN 右外部右外部连接连接 连接条件中的连接字段通常为连接条件中的连接字段通常为2 2个表中的公共字段或语义相同的个表中的公共字段或语义相同的字段。字段。2020连接查询示例连接查询示例【例例5-185-18】查询每个

27、员工的商品销售记录，要求查询结果中包含员工编查询每个员工的商品销售记录，要求查询结果中包含员工编号、姓名、商品编号、销售量和销售日期。号、姓名、商品编号、销售量和销售日期。 select select 员工员工. .员工编号员工编号, , 姓名姓名, , 商品编号商品编号, , 销售量销售量, , 销售日期销售日期 from from 员工员工, , 销售销售 where where 员工员工. .员工编号员工编号 = = 销售销售. .员工编号员工编号 为简化输入，可以使用表的为简化输入，可以使用表的别名别名代替表名，别名在代替表名，别名在FROMFROM子句中指定子句中指定 select

28、select yg.yg.员工编号员工编号, , 姓名姓名, , 商品编号商品编号, , 销售量销售量, , 销售日期销售日期 from from 员工员工 as ygas yg, , 销售销售 as xsas xs where where yg.yg.员工编号员工编号 = = xs.xs.员工编号员工编号 select yg.select yg.员工编号员工编号, , 姓名姓名, , 商品编号商品编号, , 销售量销售量, , 销售日期销售日期 from from 员工员工 as yg inner join as yg inner join 销售销售 as xsas xs on yg. on

29、 yg.员工编号员工编号 = xs.= xs.员工编号员工编号 【例例5-195-19】查询查询20102010年每个员工的商品销售记录，要求查年每个员工的商品销售记录，要求查询结果中包含员工编号、姓名、商品编号、销售量和销售询结果中包含员工编号、姓名、商品编号、销售量和销售日期日期SELECT SELECT 员工员工. .员工编号员工编号, , 姓名姓名, , 商品编号商品编号, , 销售量销售量, , 销售日期销售日期FROM FROM 员工员工, , 销售销售WHERE WHERE 员工员工. .员工编号员工编号= =销售销售. .员工编号员工编号 And year( And year(

30、销售日期销售日期)=2010;)=2010;212122225.4.4 5.4.4 子查询子查询 子查询子查询( (或称嵌套查询或称嵌套查询) ) 指在一个指在一个SELECTSELECT语句的语句的WHEREWHERE子句中包含另一个子句中包含另一个SELECTSELECT语句语句 子查询必须用括号子查询必须用括号“( )”( )”括起来。括起来。 子查询（内层查询）的结果用作建立其父查询（外层查询）子查询（内层查询）的结果用作建立其父查询（外层查询）的条件，因此，子查询的结果必须有确定的值。的条件，因此，子查询的结果必须有确定的值。 利用子查询可以将几个简单查询构成一个复杂查询，从而增利用

31、子查询可以将几个简单查询构成一个复杂查询，从而增强强SQLSQL的查询能力。的查询能力。 2323子查询示例子查询示例【例例5-205-20】查询与员工编号为查询与员工编号为“005”005”的员工同一时间聘用的员工信息，的员工同一时间聘用的员工信息，包括员工编号、姓名、性别和聘用日期。包括员工编号、姓名、性别和聘用日期。 select select 员工编号员工编号, ,姓名姓名, ,性别性别, ,聘用日期聘用日期 from from 员工员工 where where 聘用日期聘用日期 = = (select (select 聘用日期聘用日期 from from 员工员工 where whe

32、re 员工编号员工编号 = 005)= 005)2424子查询示例子查询示例 【例例5-215-21】查找未被销售的商品记录，包括商品编号、品牌、类别、查找未被销售的商品记录，包括商品编号、品牌、类别、型号和销售价型号和销售价 Select Select * * from from 商品商品where where 商品编号商品编号 not in (select distinct not in (select distinct 商品编号商品编号 from from 销售销售);); 使用设计视图设计使用设计视图设计 Select Select 邮发代号，名称邮发代号，名称 from from 报纸报纸 where where 邮发代号邮发代号 not in not in （select distinct select distinct 邮发代号邮发