第3章 结构化查询语言SQL

1、数据库系统应用数据库系统应用第第3章章 关系数据关系数据库标准语言库标准语言SQL数据库系统应用数据库系统应用vSQL语言即结构化查询语言（Structured Query Language），在各种关系型数据库中有着广泛的应用。 SQL语言由若干语句组成，每个语句都遵守特定的语法和约定。 SQL查询就是利用SQL语句创建的查询。数据库系统应用数据库系统应用 1、基本表：存放系统中各种数据的场所。、基本表：存放系统中各种数据的场所。 2、视图：从一个或多个基本表中导出的虚、视图：从一个或多个基本表中导出的虚表，视图中存放的是视图的定义，而非实际的表，视图中存放的是视图的定义，而非实际的数据。数

2、据。 3、索引：从基本表中选出的属性或属性组，、索引：从基本表中选出的属性或属性组，其目的是为了提高基本表的查询速度。其目的是为了提高基本表的查询速度。3.1 SQL基本概念基本概念一一 、SQL语言的基本操作对象语言的基本操作对象数据库系统应用数据库系统应用二、二、 SQL语言和数据库的三级模式语言和数据库的三级模式基本表基本表概念模式概念模式视视 图图外模式外模式存储文件存储文件内模式内模式数据库系统应用数据库系统应用三三 、SQL的功能的功能 1、数据定义功能、数据定义功能 包括创建和删除基本表、视图、索引，修改基本表的结构。数据库逻辑结构的建立主要由数据定义功能完成。 2、数据查询功能

3、、数据查询功能 查询的对象可以是基本表，也可以是视图。通过Select语句来实现。 数据库系统应用数据库系统应用3、数据操纵功能、数据操纵功能 对数据库中的基本表和视图中的内容进行修改、插入和删除。 4、数据控制功能、数据控制功能 主要包括对基本表和视图的存取权限控制、对基本表的完整性规则的描述和事务控制等功能。数据库系统应用数据库系统应用 1、功能强大，简便易学。、功能强大，简便易学。 2、操作的对象和结果均为元组的集合。、操作的对象和结果均为元组的集合。 3、高度的非过程化语言。、高度的非过程化语言。四、四、 SQL语言的特点语言的特点数据库系统应用数据库系统应用 1、关键语句：、关键语句

4、： CREATE TABLE 2、语句格式：、语句格式： CREATE TABLE ( NOT NULL , NOT NULL ); 3.2 数据定义数据定义一一 、基本表的定义、基本表的定义数据库系统应用数据库系统应用例例1 Customer属性列名属性列名含义含义数据类型数据类型完整性约束完整性约束Cno客户代号客户代号长度为长度为4的字符串的字符串非空，值唯一非空，值唯一Name姓名姓名长度为长度为8的字符串的字符串Sex性别性别长度为长度为2的字符串的字符串男男,女女Age年龄年龄短整型短整型City客户所在客户所在城市城市长度为长度为10的字符串的字符串主码：主码：Cno应用举例：应

5、用举例：数据库系统应用数据库系统应用Create Table Customer( Cno Char(4) Not Null, Name Char(8), Sex Char(2), Age SmallInt, City Char(10), Primary Key (Cno);数据库系统应用数据库系统应用1、关键语句： ALTER TABLE2、语句格式：ALTER TABLE ADD ,DROP ,ALTER 二、二、 基本表结构的修改基本表结构的修改数据库系统应用数据库系统应用3、应用举例、应用举例Customer（Cno，Name，Sex，Age，City） （1）在）在Customer表中

6、增加属性表中增加属性Company（长度为（长度为20的字符串）的字符串）ALTER TABLE CustomerADD Company Char(20) )数据库系统应用数据库系统应用 ALTER TABLE Customer DROP Company（3）删除）删除Company属性属性 （2）将）将Customer表中表中Company属性改为长属性改为长度为度为30的字符串的字符串ALTER TABLE Customer ALTER Company Char(30)数据库系统应用数据库系统应用 1、关键语句：、关键语句： DROP TABLE 2、语句格式：、语句格式： DROP TA

7、BLE 3、应用举例、应用举例 删除表删除表Customer DROP TABLE Customer 三、基本表的删除三、基本表的删除数据库系统应用数据库系统应用 1、关键语句： CREATE INDEX 2、语句格式： CREATE UNIQUE INDEX ON （ASC|DESC ,ASC|DESC） 四、索引的建立四、索引的建立数据库系统应用数据库系统应用 （1）为表）为表Customer按客户代号的升序建立按客户代号的升序建立名为名为Con_idx的索引；的索引； CREATE INDEX Con_name ON Customer(name ASC) （2）为表）为表Customer

8、按城市升序和客户代号按城市升序和客户代号降序建立名为降序建立名为City_Cno_idx的索引；的索引； CREATE INDEX City_Cno_idx ON Customer (City ASC, Cno DESC)3、应用举例、应用举例数据库系统应用数据库系统应用 （1）创建索引时，默认的排序为升序；）创建索引时，默认的排序为升序； （2）建立唯一性索引时，应在）建立唯一性索引时，应在INDEX语句语句前加上前加上UNIQUE短语短语 （3）创建索引可以加快对索引列的数据查）创建索引可以加快对索引列的数据查询，但也使基本表数据的插入、删除和修改询，但也使基本表数据的插入、删除和修改操作

9、变得更复杂和费时。操作变得更复杂和费时。4、注意、注意数据库系统应用数据库系统应用 1、语句格式： DROP INDEX ON 2、应用举例 删除表Customer的City_Cno_idx索引 DROP INDEX City_Cno_idx on Customer五、索引的删除五、索引的删除数据库系统应用数据库系统应用3.3 数据查询数据查询 关键语句：关键语句： SELECTFROMWHERE 语句格式：语句格式： SELECT ALL|DISTINCT */选择列表选择列表 FROM ,表名或视图名表名或视图名 WHERE GROUP BY , HAVING ORDER BY ASC|D

10、ESC , ASC|DESC 数据库系统应用数据库系统应用（1）选择表中的若干属性（投影）选择表中的若干属性（投影） SELECT 所需属性的序列所需属性的序列 FROM 一、单表查询一、单表查询 1、单表查询：查询所涉及的内容均来自、单表查询：查询所涉及的内容均来自一个基本表。一个基本表。2、各类单表查询、各类单表查询数据库系统应用数据库系统应用SnoNameScore0101张三张三850102李四李四900103王五王五95例：例：StudentSELECT Name, ScoreFROM Student NameScore张三张三85李四李四90王五王五95结果：结果：数据库系统应用数

11、据库系统应用注意注意： （a）SELECT语句后属性列的顺序即语句后属性列的顺序即为输出结果的顺序，它可以和基本表中属为输出结果的顺序，它可以和基本表中属性的先后序列不相同。性的先后序列不相同。 （b）SELECT后用后用“*”表示基本表中表示基本表中所有的属性列。所有的属性列。数据库系统应用数据库系统应用SELECT 所需属性的序列所需属性的序列FROM 基本表基本表WHERE 条件条件 （2）选择表中符合条件的元组）选择表中符合条件的元组数据库系统应用数据库系统应用SnoNameScore0101张三张三850102李四李四900103王五王五95例：例：StudentSELECT *FR

12、OM Student WHERE Name= “李四李四”结果：结果：SnoNameScore0102李四李四90数据库系统应用数据库系统应用常用的查询运算符号常用的查询运算符号查询条件查询条件运算符（或谓词）运算符（或谓词）比较比较=, , =, =, 确定范围确定范围BETWEEN AND, NOT BETWEEN AND确定集合确定集合IN, NOT IN字符匹配字符匹配LIKE, NOT LIKE空值空值IS NULL, IS NOT NULL多重条件多重条件AND, OR, NOT数据库系统应用数据库系统应用 例例1 在价格表在价格表RoomPrice(Tno,Type,Price)

13、中中查询单价小于查询单价小于400元的客房类型名称元的客房类型名称 查询运算符的应用查询运算符的应用（a）比较大小）比较大小Select TypeFrom RoomPriceWhere Price=400数据库系统应用数据库系统应用例例1 从学生表从学生表Student（Sno，Name，Score）中）中找出成绩在找出成绩在70至至80分的所有学生的姓名和学号分的所有学生的姓名和学号 Select Name, Sno From Student Where Score Between 70 And 80 说明：说明：Between 后的数据表示下限，后的数据表示下限，And后后的数据表示上限；

14、最终的查询结果为上下限之的数据表示上限；最终的查询结果为上下限之间且包括上下限的所有数据。间且包括上下限的所有数据。 （b）确定范围）确定范围 数据库系统应用数据库系统应用Select Name, Sex, AgeFrom CustomerWhere City In (“杭州杭州”, “苏州苏州”, “上上海海”) （c）确定集合）确定集合 查询来自杭州、苏州和上海的客户名称、性查询来自杭州、苏州和上海的客户名称、性别和年龄等数据别和年龄等数据 Customer(Cno,Name,Sex,Age,City)数据库系统应用数据库系统应用（d）字符匹配）字符匹配格式：格式： NOTLIKE “”

15、通配符通配符“*”：代表：代表0个或多个字符；个或多个字符； 通配符通配符“?”：代表：代表1个字符；个字符；数据库系统应用数据库系统应用 Select * From Student Where Name Like “王王*” 例例1 查询表查询表Student（Sno，Name，Score）中所有姓王的学生中所有姓王的学生。数据库系统应用数据库系统应用 Select * From Student Where Name Like “王？王？” 例例2： 查询表查询表Student（Sno，Name，Score）中所有姓王且是双名的学生。中所有姓王且是双名的学生。数据库系统应用数据库系统应用Cu

16、stomer(Cno,Name,Sex,Age,Company,City)在表中，查询公司名称以在表中，查询公司名称以“well”结尾的客户姓结尾的客户姓名和公司名称。名和公司名称。Select Name,Company, From CustomerWhere Company Like “*well”数据库系统应用数据库系统应用 例例1 查询表查询表Student（Sno，Name，Score）中没）中没有成绩的学生名字。有成绩的学生名字。 （e）涉及空值的查询）涉及空值的查询Select Name From StudentWhere Score Is Null数据库系统应用数据库系统应用Se

17、lect Name, Sex, Age From Customer Where City=”上海上海” AND Name Like “张张*” （f）多重条件查询）多重条件查询例例1：在表：在表Customer（Cno,Name,Sex, Age, City, Company）中，查询来自上海且姓张的客户名称、性）中，查询来自上海且姓张的客户名称、性别和年龄。别和年龄。运算符优先级：运算符优先级： “NOT” 最高，最高，“AND”次之，次之，“OR”最低。最低。数据库系统应用数据库系统应用 例例2 查询表查询表Student（Sno，Name，Score）中成绩在）中成绩在6070或成绩在或

18、成绩在90100之间的学生。之间的学生。 Select * From Student Where Score =60 And Score=90 And Score=100或或 Select * From Student Where Score Between 60 And 70 Or Score Between 90 And 100数据库系统应用数据库系统应用二、多表查询二、多表查询 1、连接查询：、连接查询： 对多个表同时进行查询以组成一个综合对多个表同时进行查询以组成一个综合性的结果集，这样的查询称为连接查询性的结果集，这样的查询称为连接查询数据库系统应用数据库系统应用 2、仅涉及一个连接

19、条件的连接查询（简单、仅涉及一个连接条件的连接查询（简单条件连接查询）条件连接查询） 例例1 :查询教师的姓名、婚姻状况、职称及所查询教师的姓名、婚姻状况、职称及所在系名在系名SELECT 教师教师.教师姓名教师姓名, 教师教师.已婚已婚, 教师教师.职职称称, 系系.系名系名FROM 系系,教师教师 where 系系.系编号系编号 = 教师教师.系编系编号号;数据库系统应用数据库系统应用注意注意1：当属性名不会引起混淆时，可以省略表名直当属性名不会引起混淆时，可以省略表名直接使用属性名。接使用属性名。注意注意2：在进行表连接时必须在：在进行表连接时必须在WHERE字句中指明字句中指明连接条件

20、，否则就成为广义的笛卡尔积，其连接结连接条件，否则就成为广义的笛卡尔积，其连接结果一般无意义。果一般无意义。形式二形式二 SELECT 教师姓名教师姓名, 已婚已婚, 职称职称, 系名系名FROM 系系,教师教师 WHERE 系系.系编号系编号 = 教师教师.系编号系编号;数据库系统应用数据库系统应用SELECT 教师姓名教师姓名, 已婚已婚, 职称职称, 系名系名FROM 系系,教师教师 where 系系.系编号系编号 = 教教师师.系编号系编号 and 系系.系名系名=“经管学院经管学院;3、复合条件连接查询、复合条件连接查询例例2:查询经管学院的教师情况查询经管学院的教师情况数据库系统应

21、用数据库系统应用 例例3 查询经管学院学生所有课程成绩单查询经管学院学生所有课程成绩单 SELECT 学生姓名学生姓名, 课程名称课程名称, 学分学分,成绩成绩FROM 课程课程,学生学生,选课选课 where 学生学生.学生编学生编号号 = 选课选课.学生编号学生编号 and 课程课程.课程编号课程编号 = 选课选课.课程编号课程编号 and 系编号系编号=D001;数据库系统应用数据库系统应用三、子查询三、子查询 1、嵌套查询是指将一个、嵌套查询是指将一个SELECTFROMWHERE查询块嵌套在另一个查询查询块嵌套在另一个查询块的块的WHERE或或HAVING短语的条件中的查短语的条件中

22、的查询。询。数据库系统应用数据库系统应用 例例1 查询与李娜在一个班上的所有女生查询与李娜在一个班上的所有女生SELECT 学生编号学生编号, 学生姓名学生姓名, 性别性别, 班级班级FROM 学生学生 WHERE 班级班级=(SELECT 班级班级 FROM 学生学生 WHERE 学生姓名学生姓名=李娜李娜);数据库系统应用数据库系统应用SELECT 课程名称课程名称, 学分学分FROM 课程课程,授课授课 where 课程课程.课程编号课程编号 = 授课授课.课课程编号程编号 and 授课授课.教师编号教师编号 in(SELECT 教师编号教师编号 FROM 教师教师 WHERE 系编系编

23、号号=D001);2、带有、带有In谓词的子查询谓词的子查询 当子查询的结果包含多个值时，常用当子查询的结果包含多个值时，常用IN来连接来连接子查询和父查询子查询和父查询例例2 查询经管学院教师所教授的课程名单查询经管学院教师所教授的课程名单数据库系统应用数据库系统应用例例3 查询没有学生选修的课程查询没有学生选修的课程3、带有、带有NOTEXISTS谓词的子查询谓词的子查询语法：语法： WHERE NOT EXISTSSELECT 课程编号课程编号, 课程名称课程名称, 学分学分FROM 课程课程 WHERE NOT EXISTS(SELECT * FROM 选课选课 where 课程课程.

24、课程编号课程编号=选课选课.课程编号课程编号);数据库系统应用数据库系统应用 （1）带）带EXISTS谓词的子查询返回的是逻辑谓词的子查询返回的是逻辑值值“真真”或或“假假”，其他子查询返回的是某一，其他子查询返回的是某一列的值（单值或多值）；列的值（单值或多值）； （2）带）带EXISTS谓词的子查询中的谓词的子查询中的Where条条件的值与父查询中所涉及的某些属性列的值有件的值与父查询中所涉及的某些属性列的值有关，而其他子查询中的关，而其他子查询中的Where条件的值与父查条件的值与父查询无关。询无关。EXISTS查询与其他子查询的区别：查询与其他子查询的区别：数据库系统应用数据库系统应用

25、集函数集函数功能功能说明说明COUNT(DISTINCT|ALL*)统计元组个数统计元组个数COUNT(DISTINCT|ALL） 统计列中值的个数统计列中值的个数SUM(DISTINCT|ALL)统计列值的总和统计列值的总和仅用于数值列仅用于数值列AVG(DISTINCT|ALL)统计列值平均值统计列值平均值仅用于数值列仅用于数值列MAX(DISTINCT|ALL)计算一列的最大值计算一列的最大值MIN(DISTINCT|ALL)计算一列的最小值计算一列的最小值五、汇总查询五、汇总查询数据库系统应用数据库系统应用 例例 查询某班学生人数查询某班学生人数SELECT Count(学生编号学生编

26、号) AS 学生编号之计数学生编号之计数FROM 学生学生WHERE (班级班级=.0510401.);计算某个（或某些）属性列的汇总值计算某个（或某些）属性列的汇总值数据库系统应用数据库系统应用 例例 查询某课程平均分查询某课程平均分SELECT Avg(成绩成绩) AS 成绩之平均值成绩之平均值FROM 选课选课WHERE 课程编号课程编号=C001;数据库系统应用数据库系统应用 针对某个（或某些）属性列的值来计算另针对某个（或某些）属性列的值来计算另一个（或另一些）属性列的汇总值。一个（或另一些）属性列的汇总值。 例例1 在基本表学生表中，查询每班学生人数在基本表学生表中，查询每班学生人

27、数 SELECT 班级班级, Count(学生编号学生编号) AS 学生编学生编号之计数号之计数FROM 学生学生GROUP BY 班级班级;数据库系统应用数据库系统应用 例例2 在基本表在基本表学生表中，查询学生在基本表在基本表学生表中，查询学生人数在人数在21人以上的班级人以上的班级 SELECT 班级班级FROM 学生学生GROUP BY 班级班级 HAVING Count(学生编号学生编号)21;数据库系统应用数据库系统应用 Order by 在默认情况下为升序（ASC） 例例1 在基本表选课中，将学生按成绩从高在基本表选课中，将学生按成绩从高到低排列，相同成绩的学生按学号顺序排列。到

28、低排列，相同成绩的学生按学号顺序排列。SELECT 学生编号学生编号, 课程编号课程编号, 成绩成绩FROM 选课选课ORDER BY 成绩成绩,学生编号学生编号 DESC;六、查询结果排序六、查询结果排序 （Order by）数据库系统应用数据库系统应用七、使用七、使用UNION的查询的查询语法：语法：query1 UNION ALL query2 注意：集合运算符进行运算的前提条件是查注意：集合运算符进行运算的前提条件是查询的结果具有相同的属性集。询的结果具有相同的属性集。数据库系统应用数据库系统应用例例1 在学生表中查询在学生表中查询 (Select * From 学生学生 Where

29、班级班级=.0510401.) Union (Select * From 学生学生 Where 性别性别=男男)数据库系统应用数据库系统应用3.4 数据更新数据更新一、数据插入一、数据插入 格式1 ： INSERT INTO (, ) VALUES ( , ) 数据更新主要包括数据插入、修改和删除数据更新主要包括数据插入、修改和删除等操作。等操作。数据库系统应用数据库系统应用格式格式2： INSERT INTO ( ,) SELECT 子查询子查询 注意：注意： 当新插入的元组在每列上都有值，且当新插入的元组在每列上都有值，且顺序和表中各列顺序一致时，顺序和表中各列顺序一致时，INTO子句后可

30、子句后可以不说明列名。以不说明列名。数据库系统应用数据库系统应用应用举例应用举例 例例1 将一个新的客户元组（将一个新的客户元组（Cno：“C010”； Company：“联合利华联合利华”； Contact_person：“李立李立”；Title：“销售代销售代表表”；City：”上海上海”；Tel：“025”）插入到）插入到表表Customer中。中。 InsertInto CustomerValues（“C010”, “联合利华联合利华” ,“李立李立”,“销销售代表售代表”，“上海上海”，“025”）;数据库系统应用数据库系统应用 例例2 在表在表Product（Pno，Pname，T

31、no，Price，Stock）中，插入一条新的元组（）中，插入一条新的元组（Pno： “B004”，Pname：“看图识字卡看图识字卡”，Price：“3”） InsertInto Product（Pno, Pname, Price)Values (“B004”, “看图识字卡看图识字卡”，3） 数据库系统应用数据库系统应用 例例3 将将Customer表中上海客户的信息插表中上海客户的信息插入到另一个关系入到另一个关系CustomerSh中。中。 InsertInto CustomerShSelect *From CustomerWhere City=“上海上海”数据库系统应用数据库系统应用

32、二、数据修改二、数据修改格式： UPDATE SET = ,= WHERE 数据库系统应用数据库系统应用应用举例：应用举例： 例例1 将将“商务房商务房”的单价改为的单价改为1000元元 RoomPrice(Tno,Type,Price) Update RoomPriceSet Price=1000Where Type=“商务房商务房”数据库系统应用数据库系统应用 例例2 将所有客房的价格提高将所有客房的价格提高50元。元。 RoomPrice(Tno,Type,Price)Update RoomPriceSet Price=Price+50 数据库系统应用数据库系统应用 例例3 将表将表Pr

33、oduct（Pno，Pname，Tno，Price，Stock）中，所有）中，所有“文具文具”类产品的单类产品的单价降低价降低2元。表元。表Ptype（Tno，Tname） Update ProductSet Price=Price-2Where Tno= ( Select Tno From Ptype Where Tname=“文具文具” )数据库系统应用数据库系统应用三、数据删除三、数据删除格式：格式： DELETE FROM WHERE 数据库系统应用数据库系统应用应用举例：应用举例： 例例1 在表在表Order_item(Ono, Pno,Quantity)中，删除中，删除“O005”

34、号订单的信息。号订单的信息。 Delete From Order_item Where Ono=“O005”数据库系统应用数据库系统应用四、更新操作注意事项四、更新操作注意事项 （1）更新操作一次只能对一个表进行。）更新操作一次只能对一个表进行。 （2）在进行表的更新操作时必须考虑数据）在进行表的更新操作时必须考虑数据的完整性。的完整性。数据库系统应用数据库系统应用 例：在表例：在表Order（Ono，Cno，Odate，Freight）中，删除中，删除“O006”号订单。号订单。 Delete From Order Where Ono “O006” 当进行完删除操作时，在表当进行完删除操作时

35、，在表Order_item(Ono,Pno,Quantity)中仍然保留着中仍然保留着“O006”号订单的详细信息，这违背了参照完整性号订单的详细信息，这违背了参照完整性规则。所以正确的操作应该将规则。所以正确的操作应该将Order_item中有关中有关“O006”的订单项也删除掉。的订单项也删除掉。数据库系统应用数据库系统应用第一步：第一步： Delete From Order_item Where Ono=“O006”第二步：第二步： Delete From Order Where Ono=“Ono”数据库系统应用数据库系统应用3.5 视图视图 视图是从一个或多个基本表中导出的虚表，视图是

36、从一个或多个基本表中导出的虚表，其中存放的仅仅是视图的定义。其中存放的仅仅是视图的定义。一、视图的创建一、视图的创建 1、语句格式、语句格式 CREATE VIEW (,) AS WITH CHECK OPTION数据库系统应用数据库系统应用说明：说明： （1）WITH CHECK OPTION 表示在更表示在更新视图时必须保证被更新的元组满足定义视图新视图时必须保证被更新的元组满足定义视图数据的子查询中的条件。数据的子查询中的条件。 （2）当子查询中属性列与视图中属性列）当子查询中属性列与视图中属性列完全相同时，可以省略视图名后的属性列名。完全相同时，可以省略视图名后的属性列名。 数据库系统

37、应用数据库系统应用 应用举例应用举例 例例1 建立建立“上海上海”客户的视图，并取名为客户的视图，并取名为Customer_shCreate View Customer_sh (Cno,Company,Contact_person,Title,Tel) As Select Cno,Company,Contact_person,Title,Tel From Customer Where City=“上海上海” With Check Option 数据库系统应用数据库系统应用也可以表示为：也可以表示为：Create View Customer_sh As Select Cno,Company,Co

38、ntact_person,Title,Tel From Customer Where City=“上海上海” With Check Option 数据库系统应用数据库系统应用 例例2 建立一个名为建立一个名为Avg_price的视图，其中包的视图，其中包含产品类别代号和平均价格两相内容。含产品类别代号和平均价格两相内容。 Product（Pno，Pname，Tno，Price，Stock） Create View Avg_price (Tno, AvgPrice) As Select Tno, AVG(Price) From Product Group by Tno 注意：当视图中包含有非直接

39、的表列时，必须要在视图注意：当视图中包含有非直接的表列时，必须要在视图名后给出视图中的列。名后给出视图中的列。数据库系统应用数据库系统应用 例例1 建立一个名为建立一个名为Order_Sh的包含所有的包含所有上海客户订单信息的视图，要求在该视图中上海客户订单信息的视图，要求在该视图中包括各客户公司的名称，订单代号和订购日包括各客户公司的名称，订单代号和订购日期等属性。期等属性。 从多个基本表中导出的视图从多个基本表中导出的视图CnoCompanyContact_personTitleCityTelOnoCnoOdateFreight数据库系统应用数据库系统应用Create View Order_Sh (Company,Ono,Odate)AS Select Company, O