结构化查询语言

1、清华大学出版社2013年8月1日2 2第5章 结构化查询语言SQL 5.1 SQL语言概述5.2 SQL数据定义5.3 SQL数据更新5.4 SQL数据查询 5.4.1 查询的语法 5.4.2 基本的查询 5.4.3 连接查询 5.4.4 子查询 5.4.5 合并查询本章小结3 35.1 SQL语言概述pSQLSQL是是Structured Query LanguageStructured Query Language的缩写，即结构化的缩写，即结构化查询语查询语言。是一种通用的且功能强大的关系数据库语言，言。是一种通用的且功能强大的关系数据库语言，也是也是关系关系数据库的标准数据库的标准语言语

2、言。它它既可以用于大型数据库管理系统，也既可以用于大型数据库管理系统，也可以用于微型机可以用于微型机数据库管理系统。数据库管理系统。p具有具有数据定义数据定义、数据更新、数据查询、数据更新、数据查询、数据控制等功能。数据控制等功能。4 45.1 SQL语言概述p SQLSQL的主要特点：的主要特点：n 高度集成化n 集数据的定义、操纵、查询和控制功能于一体。n 高度非过程化n 用SQL语言进行数据操作，不必告诉计算机怎么做，只要提出做什么，系统就可以自动完成全部工作。n 面向集合的操作方式n 采用集合操作方式，操作对象和操作结果都是记录集。n 简洁易学n 完成核心功能只用了9个命令动词，且接近

3、英语的自然语法。n 用法灵活n 既可以独立使用，也可以嵌入到高级语言中使用。5 5在Access中使用SQL语句n 使用方法使用方法n 打开查询设计视图。n 执行“查询工具/设计”“查询类型”“数据定义”命令，打开“数据定义”查询窗口，输入SQL语句。n 注意：在“数据定义”查询窗口中一次只能输入一条SQL语句。n 单击功能区的“运行”按钮，执行SQL语句。n 根据需要，将SQL语句保存为一个查询对象，或直接关闭查询窗口。6 65.2 SQL数据定义n使用SQL的CREATE、ALTER和DROP命令可以实现数据定义功能，包括表、索引等的创建、修改和删除。nCREATE TABLEnCREAT

4、E INDEXnALTER TABLEnDROP TABLE nDROP INDEX7 75.2 SQL数据定义n1.1.定义表定义表n使用使用 CREATE TABLE CREATE TABLE 命令定义命令定义表表n示例：nCreate Table Create Table empemp( (员工号员工号 Text(3)Text(3), ,姓名姓名 Text(4),Text(4),性别性别 Text(1),Text(1),婚否婚否 Logical,Logical,聘用日期聘用日期 Date,Date,照片照片 OLEObject,OLEObject,简历简历 MemoMemo) )表名字段

5、名数据类型及大小8 85.2 SQL数据定义n进一步进一步：nCreate Create Table empTable emp( (员工号员工号 Text(3Text(3) ) Primary KeyPrimary Key, ,姓名姓名 Text(4Text(4) ) Not NullNot Null, ,性别性别 Text(1),Text(1),婚否婚否 Logical,Logical,聘用日期聘用日期 Date,Date,照片照片 OLEObject,OLEObject,简历简历 MemoMemo) )定义主键指定字段不允许为空值UNIQUE 定义唯一键9 95.2 SQL数据定义nCRE

6、ATE TABLE ( () NOT NULL PRIMARY KEY | UNIQUE , () NOT NULL PRIMARY KEY | UNIQUE , )n 定义表时，必须指定表名表名、各个字段名字段名及相应的数据类型数据类型和字段大小字段大小（由系统自动确定的字段大小省略），并且各个字段之间用英文的逗号分隔。n 字段的数据类型必须用字符表示数据类型必须用字符表示。n primary key primary key 定义主键主键，unique unique 定义惟一键定义惟一键。n 命令关键词和语句中的其他关键词不区分大小写。1010定义表示例【例5-1】使用SQL语句定义一个名为

7、emp的表，结构如下： 员工号（Text，3字符）、姓名（Text，4字符）、性别（Text，1字符）、婚否（Logical）、聘用日期（Date）、照片（OLEObject）、简历（Memo），员工号为主键，姓名不允许为空值。 1111定义表示例1212定义表示例n【例5-2】在“销售管理”数据库中，使用SQL语句定义item表和sale表。Item表结构：商品号（Text，6字符）、品牌（Text，10字符）、类别（Text，6字符）、型号（Text，15字符）、价格（Single），商品号为主键。Sale表结构：序号（Counter）、员工号（Text，3字符）、商品号（Text，6字符

8、）、销售量（Integer）、销售日期（Date），序号为主键1313定义表示例nCreate Table itemCreate Table item( (商品号商品号 Text(6) Primary Text(6) Primary key,key,品牌品牌 Text(10) ,Text(10) ,类别类别 Text(6),Text(6),型号型号 Text(15),Text(15),价格价格 SingleSingle) )nCreate Table saleCreate Table sale( (序号序号 Counter Primary key,Counter Primary key,员工号

9、员工号 Text(3) Not Null,Text(3) Not Null,商品号商品号 Text(6) Not Null,Text(6) Not Null,销售量销售量 Integer Not Null,Integer Not Null,销售日期销售日期 DateDate) )1414定义表示例索引名表名索引字段指定唯一索引降序n进一步： create unique index uni_model on item(型号)nCreate index idx_price on item(价格 desc) n 2. 建立索引建立索引n 使用 CREATE INDEX 命令建立索引n示例： crea

10、te index idx_date on emp (聘用日期)1515建立索引nCREATE CREATE UNIQUEUNIQUE INDEX INDEX ON ON ( (1ASC|DESCASC|DESC,ASC|DESC,)2ASC|DESC,) WITH WITH PRIMARYPRIMARY n UNIQUE 指定唯一索引，WITH PRIMARY 指定主索引。nASC和DESC指定索引值得排序方式，ASC表示升序，DESC表示降序，默认为升序 。【例5-3】使用SQL语句建立索引，在item表的“型号”字段上建立惟一索引，索引名称为uni_model；在“类别”和“价格”2个字段

11、上分别按升序和降序建立组合索引，索引名称为idx_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) 1616建立表间关系n3.3.建立表间关系建立表间关系 n使用使用 Create Table Create Table 命令，在命令，在定义表的同时建立表间关系定义表的同时建立表间关系【

12、例5-4】使用SQL语句定义sale2表，并通过“员工号”字段与emp表建立关系，通过“商品号”字段与item表建立关系。references 表示参照引表示参照引用，即用，即sale2(子表子表)中的商品号中的商品号为外键，为外键，item(主表主表)中的中的商品号为主键商品号为主键1717修改表n4.4.修改表修改表 n使用 Alter Table 命令，可以修改表的结构，包括修改、添加个删除字段等。n （1）修改字段 ALTER TABLE ALTER column ()n 使用该命令不能修改字段名使用该命令不能修改字段名。n （2）添加字段 ALTER TABLE ADD column

13、 ()n （3）删除字段 ALTER TABLE DROP column 1818修改表例如，在emp表中增加一个“电话号码”字段（Integer），然后将该字段修改为Text型（11字符），最后删除该字段。 alter table emp add column 电话号码 integer alter table emp alter 电话号码 text(11) alter table emp drop 电话号码1919删除表和索引n5.删除索引或表删除索引或表 n使用 DROP 命令 ，可以删除表上的索引或删除表。n 删除索引 DROP INDEX ON n 删除表 DROP TABLE n 删

14、除表后，在表上定义的索引也一起被删除。例如，使用SQL语句删除item表中的uni_model索引项。 drop index uni_model on item 使用SQL语句删除sale表。 drop table sale20205.3 SQL数据更新n使用SQL的INSERT、UPDATE和DELETE命令可以实现数据更新功能，包括插入记录、更新记录和删除记录。nINSERT命令nUPDATE命令nDELETE命令21215.3 SQL数据更新n1.插入记录n使用 Insert Into 命令n示例：ninsert into emp values (a01, 马立, 男, yes, #20

15、11-5-15#, null, null)ninsert into emp(员工号, 姓名, 性别, 聘用日期) values (b01, 陈慧娟, 女, #2011-7-10#)22225.3 SQL数据更新nINSERT INTO (,，) VALUES(,)n 如果缺省字段名，则必须为新记录中的每个字段都赋值，且如果缺省字段名，则必须为新记录中的每个字段都赋值，且数据类型和顺序要与表中定义的字段一一对应。数据类型和顺序要与表中定义的字段一一对应。【例5-5】使用SQL语句在emp表中插入2条员工记录。 insert into emp values (a01, 马立, 男, yes, #2

16、011-5-15#, null, null) insert into emp(员工号, 姓名, 性别, 聘用日期) values (b01, 陈慧娟, 女, #2011-7-10#)2323SQL数据更新n2.更新记录 n使用 Update Set 命令更新记录 UPDATEUPDATE SETSET = ,=, WHEREWHERE 例如，使用SQL语句将emp表中所有男员工的“婚否”字段改为“否”。 update emp set 婚否=no where 性别=男n3.删除记录 n使用 Delete From 命令删除记录 DELETE FROM WHERE 例如，使用SQL语句删除emp表

17、中的所有记录。 delete from emp24245.4 SQL数据查询n数据查询是数据库的核心操作。nSQL语言的SELETCT 命令可以实现数据查询功能。n包括：选择查询、投影查询、连接查询、子查询、合并查询等。25255.4.1 查询的语法nSELECT命令是SQL的核心语句，具有灵活的使用方式和丰富的功能。n其语法： SELECTSELECT ALL|DISTINCT TOP PERCENT AS FROMFROM AS ,AS WHEREWHERE AND GROUP BY GROUP BY HAVING ORDER BY ORDER BY ASC | DESC 26265.4.

18、1 查询的语法从功能区的“视图”列表中选择“SQL视图”命令或“设计视图”命令，可以在SQL命令窗口和查询设计视图之间切换。27275.4.2 基本的查询n基本的查询包括投影投影、选择选择、排序排序、分组分组等操作 。n1.投影投影n 从数据源中选择若干从数据源中选择若干列，相当于关系代数中的投影运算。列，相当于关系代数中的投影运算。 SELECTSELECT ,， FROMFROM n 目标列可以是数据源中已有的字段，也可以是一个计算列也可以是一个计算列。n 若要选择数据源中的所有字段所有字段，目标列可表示为“* *”。n 在字段名前加上DISTINCTDISTINCT关键字，可消除重复的记

19、录。 28285.4.2 基本的查询【例5-6】查询“员工”表中所有员工的员工编号、姓名、性别和聘用日期。 select select 员工编号员工编号, , 姓名姓名, , 性别性别, , 聘用日期聘用日期 from from 员工员工若要查询所有员工的所有记录，则： select select * * from from 员工员工 【例5-7】查询“员工”表中所有员工的员工编号、姓名和聘用年数。 select 员工编号, 姓名, year(date()-year(year(date()-year(聘用日期聘用日期) as ) as 聘用聘用年数年数 from 员工AS关键字改变输出列的列标

20、题29295.4.2 基本的查询【例5-8】在“销售”表中查询每件商品的商品编号 select distinctdistinct 商品编号 from 销售 消除查询结果中的重复记录3030基本的查询n 2.2.选择选择n 从表中选出满足条件的记录，相当于关系代数中的选择运算。 SELECT SELECT FROM FROM WHERE WHERE n 是一个逻辑表达式，由多个关系表达式通过逻辑运算符连接而成。【例5-9】查询“员工”表中所有已婚的女员工的员工编号、姓名和聘用日期。 select 员工编号, 姓名, 聘用日期 from 员工 where 性别=女 and 婚否=yes【例5-10

21、】查询“商品”表中商品编号以“D”打头的所有商品记录。 select * from 商品 where 商品编号 like D* 3131基本的查询n 3.排序排序n 在SELECT语句中使用ORDER BY子句可以对查询结果按照一个或多个列的升序（ASC）或降序（DESC）排列，默认是升序默认是升序。 ORDER BY ORDER BY ASC|DESC ASC|DESCn 可以是字段名，或目标列的序号，如1，2，。n 在目标列前加上TOPTOP短语短语，可以从查询结果中选择排在前面的若干条记录（用数值或百分比指定）。 SELECT TOP PERCENT ORDER BY ASC|DESC3

22、232基本的查询【例5-11】查询“商品”表中销售价在900-1200之间（包括900和1200）的商品记录，并按销售价降序排列。 select * from 商品 where 价格 between 900 and 1200 order by order by 价格价格 descdesc【例5-12】查询“销售”表中员工编号为“001”、“003”和“005”的员工的销售记录，并按“员工编号”排序，对同一员工再按“销售日期”降序排列。 select * from 销售 where 员工编号 in(001,003,005) order by order by 员工编号员工编号, , 销售日期销售

23、日期 descdesc【例5-13】查询“商品”表中销售价最低的3种商品的商品编号、品牌和销售价。 select top 3top 3 商品编号,品牌,销售价 from 商品 order by order by 销售价销售价3333基本的查询n 4.分组分组n在SELECT语句中使用使用GROUP BY子句可以按照某一列的值分组。 GROUP BY GROUP BY HAVING HAVING n 使用HAVING短语可以对分组后的结果进行筛选。n 注意：HAVING短语必须和GROUP BY子句同时使用。n 分组查询通常与分组查询通常与SQLSQL聚合函数一起使用聚合函数一起使用，先按指定的

24、数据项分组，再对各组进行总计，如计数、求和、求平均值等。如果未分组，则聚合函数作用于整个表。n 常用SQL聚合函数：COUNT、AVG、SUM、MIN、MAX3434基本的查询【例5-14】统计“商品”表中的商品数 Select count(*) as 商品数 from 商品【例5-15】统计“商品”表中各类别商品的数量select 类别, count(*) as 各类别数量 from 商品 group by 类别【补充例】按“商品编号”统计每件商品的销售总量。SELECT 商品编号, Sum(销售量) AS 销售总量FROM 销售GROUP BY 商品编号3535基本的查询【例5-16】在“

25、销售”表中查询有3次及3次以上销售记录的员工编号。select 员工编号 from 销售 group by 员工编号 havinghaving count(*)=3n本例的执行过程是：先用GROUP BY子句中指定的员工编号对“销售”表进行分组，将员工编号相同的记录分为一组，然后用HAVING短语中的COUNT函数对每一组计数（即统计每个员工的小销售次数），并将计数结果大于等于3的员工编号选出来作为查询的最终结果。3636基本的查询n【例5-17】在“销售”表中查询2009年有2次及2次以上销售记录的员工编号select 员工编号 from 销售 where year(销售日期)=2009 g

26、roup by 员工编号 havinghaving count(*)=2n当WHERE子句、GROUP BY子句和HAVING短语同时出现在一个查询语句中时，先执行WHERE子句，从表中选取满足条件的记录，然后执行GROUP BY子句对选取的记录进行分组，再执行HAVING短语从分组结果中选取满足条件的组。37375.4.3 连接查询n 连接查询同时涉及2个或多个表的数据。n 连接查询需要指定2个表的连接条件，有2种格式：n 在 Where 子句中指定连接条件 SELECT SELECT FROM FROM , 2 WHERE WHERE . = = .2n 在 FROM 子句中指定连接条件S

27、ELECT SELECT FROM FROM INNER JOIN | LEFT JOIN | RIGHT JOIN INNER JOIN | LEFT JOIN | RIGHT JOIN 2 ON ON . = = .2n INNER JOIN 内部连接,LEFT JOIN 左外部连接,RIGHT JOIN 右外部连接 n 连接条件中的连接字段通常为2个表中的公共字段或语义相同的字段。3838连接查询【例5-18】查询每个员工的商品销售记录，要求查询结果中包含员工编号、姓名、商品编号、销售量和销售日期。 select 员工.员工编号, 姓名, 商品编号, 销售量, 销售日期 from 员工,

28、 销售 where 员工.员工编号 = 销售.员工编号 n为简化输入，可以使用表的别名代替表名，别名在FROM子句中指定 select yg.员工编号, 姓名, 商品编号, 销售量, 销售日期 from 员工 as yg, 销售 as xs where yg.员工编号 = xs.员工编号n 在FROM子句中指定连接条件： select yg.员工编号, 姓名, 商品编号, 销售量, 销售日期 from 员工 as yg inner join 销售 as xs on yg.员工编号 = xs.员工编号3939连接查询n【例5-19】查询20102010年年每个员工的商品销售记录，要求查询结果中包含员工编号、姓名、商品编号、销售量和销售日期。 select 员工.员工编号, 姓名, 商品编号, 销售量, 销售日期 from 员工, 销售 where 员工.员工编号 = 销售.员工编号 and year(销售日期)=201040405.4.4 子查询n 子查询(或称嵌套查询)n 指在一个SELECT语句的WHEREWHERE子句中包含另一个子句中包含另一个SELECTSELECT语句语句n 子查询必须用括号“( )”括起来。n 子查询（内层查询）的结果用作建立其父查询（外层查询）的条件，因此，子查询的结果必须有确定的值。n 利用子查询可以将几个简单