数据库chap03SQL和QBE.ppt

1、1,Chapter 3,SQL和QBE,2,Chapter 3 目标,SQL的目标 SELECT检索数据 INSERT插入数据 UPDATE更新数据 DELETE删除数据 CREATE TABLE创建新表 另一种关系数据库查询语言， QBE,3,SQL,关系型DBMS的主要语言 主要特点： 易于掌握 非过程化语言 只需指定需要什么数据，而不需要指定怎样得到这些数据 语言格式自由 命令结构由标准英语单词组成如：SELECT, INSERT, UPDATE; 可以被很多用户使用,4,SQL的目标,理想情况下，一种数据库语言应该容许用户进行以下操作 创建数据库和表结构 实现基本的数据管理工作，比如插

2、入、修改或删除表中数据 能够实现简单和复杂的查询 以用户最小的代价执行上述工作 易于学习,5,SQL的目标,SQL 是一个面向转换 （transform-oriented） 语言，包括两个主要部分： DDL 定义数据库结构和控制数据存取; DML 检索和更新数据. SQL3发布之前，SQL不包含流程控制命令,如IFTHEN，DOWHILE SQL 可以在高级语言中调用 (如：C, C#).,6,SQL语句,大部分SQL语句不区分大小写，除了字符数据的拼写 用 BNF符号扩展形式定义SQL语句 - 大写字母代表关键词 - 小写字母代表用户自定义 - | 垂直线表示在其中任选一个 - 大括号表示必

3、需的元素 - 方括号代表可选元素 - 省略号代表某一项可以重复 (0 或者多次),7,文字,文字是在SQL语句中使用的常量 所有非数字型的数据值必须包含在单引号之中 (如： London) 所有数字型的数据值必须不加单引号 (如： 650.00),8,SELECT查询,SELECT DISTINCT | ALL * | columnExprn AS newName ,. FROM TableName alias , . WHERE condition GROUP BY columnList HAVINGcondition ORDER BYcolumnList,9,组成SELECT语句的子句,F

4、ROM指定用到的一个或多个表 WHERE按照条件过滤行数据 GROUP BY按相同的列值将行分成组 HAVING按照某些条件过滤组数据 SELECT指定在输出结果中出现的列 ORDER BY 指定输出的排列顺序,10,SELECT查询,SELECT语句中各子句的顺序不能改变 SELECT 和FROM 子句是必需的,11,3.1 检索所有的行和列,列出所有录像的全部详细情况 SELECT catalogNo, title, category, dailyRental, price, directorNo FROM Video; 当希望列出一个表中的所有列时，可以用星号（*）代替列名 SELECT

5、 * FROM Video;,12,3.2 查询指定的列、所有行,列出所有录像的catalog number, title and daily rental rate SELECT catalogNo, title, dailyRental FROM Video;,13,3.3 使用DISTINCT,列出所有录像的种类 SELECT category FROM Video;,14,3.3 使用DISTINCT,使用DISTINCT 去掉重复值 SELECT DISTINCT category FROM Video;,15,3.4 计算字段,列出租借录像三天的租金 SELECT catalogN

6、o, title, dailyRental*3 FROM Video;,16,3.4 计算字段,使用 AS 子句来为列命名: SELECT catalogNo, title, dailyRental*3 AS threeDayRate FROM Video;,17,3.5 比较查询条件,列出所有年薪高于 $10,000的员工的信息 SELECT staffNo, name, position, salary FROM Staff WHERE salary 10000;,18,3.6 范围查询条件,BETWEEN 测试包含边界两端的值 列出所有年薪在 $45,000 和$50,000之间的员工信

7、息 SELECT staffNo, name, position, salary FROM Staff WHERE salary BETWEEN 45000 AND 50000;,19,3.6 范围查询条件,否定版本的范围测试（ NOT BETWEEN）. BETWEEN 没有增强SQL表达式的表达能力. 可以重写为： SELECT staffNo, name, position, salary FROM Staff WHERE salary = 45000 AND salary = 50000; 适合处于一定范围的值,20,3.7 集合成员查询,列出动作类和儿童类的所有录像 SELECT c

8、atalogNo, title, category FROM Video WHERE category IN (Action, Children);,21,3.7 集合成员查询,否定版本的集合成员查询条件 (NOT IN). IN测试也没有增强SQL表达式的表达能力。 可以重写写成： SELECT catalogNo, title, category FROM Video WHERE category =Action OR category =Children 当该集合包含多个值时，IN提供了一种更有效的表达方法,22,3.8 模式匹配查询条件,列出所有名为“Sally”的员工信息 SELEC

9、T staffNo, name, position, salary FROM Staff WHERE name LIKE Sally%;,23,3.8 模式匹配查询条件,SQL 有以下两种模式匹配符号： % (百分号)：代表0个或多个字符序列（通配符） _ (下划线)： 代表一个字符。 Sally% 代表前5个字符必须匹配Sally,24,3.9 NULL 查询条件,列出没有按期归还的录像 测试null必须用关键词 IS NULL或IS NOT NULL: SELECT dateOut, memberNo, videoNo FROM RentalAgreement WHERE dateRetu

10、rn IS NULL;,25,3.10 结果排序,列出所有录像，按价格降序排列 SELECT * FROM Video ORDER BY price DESC;,26,SELECT 查询 聚合,ISO SQL 定义了五个聚合函数： COUNT 返回指定列值的个数（行数） SUM返回指定列值的总和 AVG返回指定列值的平均值 MIN返回指定列值的最小值 MAX返回指定列值的最大值,27,SELECT查询 聚合,这些函数在表中的一个列上操作，并且返回单一值 COUNT, MIN, MAX 可以用到数值和非数值型字段上， SUM ，AVG 只能用到数值型字段上 除了 COUNT(*)以外，每个函数都

11、首先忽略NULL值，然后再对剩下的非空值进行操作 COUNT(*) 计算表中所有的行，而不管这些行是否是空值或是出现重复值 可以使用DISTINCT去掉重复值 DISTINCT对MIN/MAX没有影响, 对于SUM/AVG的结果会有所影响,28,SELECT查询 聚合,聚合函数只能在SELECT列表和HAVING子句中使用 如果SELECT列表中包含了聚合函数并且没有使用GROUP BY子句，那么SELECT 列表中的项不能包含对任何列的引用，除非该列是聚合函数的参数 以下查询是不合法的 SELECT staffNo, COUNT(salary) FROM Staff;,29,3.11 使用

12、COUNT 和 SUM,列出年薪高于 $40,000 的员工总数和他们的年薪总和 SELECT COUNT(staffNo) AS totalStaff, SUM(salary) as totalSalary FROM Staff WHERE salary 40000;,30,3.12 使用 MIN, MAX，AVG,列出员工年薪的最小值、最大值和平均值 SELECT MIN(salary) AS minSalary, MAX(salary) AS maxSalary, AVG(salary) AS avgSalary FROM Staff;,31,SELECT 查询 分组,使用GROUP B

13、Y 子句来进行小计 要求SELECT和GROUP BY 子句紧密结合在一起，当使用了GROUP BY 子句后SELECT列表中的每一项在每个分组中必须都是单值的，而且SELECT 子句只能包含： 列名 聚合函数 常量 一个包含上述各项组合的表达式 在SELECT列表中的所有列名都必须出现在GROUP BY子句中 当WHERE子句和GROUP BY子句同时使用时， WHERE被首先应用，然后在剩下的满足条件的行之上进行分组 ISO 认为在使用GROUP BY时两个空值相等,32,3.13 使用 GROUP BY,找出每个分公司的员工总数和他们的年薪总和 SELECT branchNo, COUN

14、T(staffNo) AS totalStaff, SUM(salary) AS totalSalary FROM Staff GROUP BY branchNo ORDER BY branchNo;,根据branchNo对Staff表进行分组； 针对每个分组，计算员工数目和薪水总合； 将结果按照branchNo升序排列。,33,有限制的分组 HAVING,HAVING 子句和GROUP BY子句同时作用来限制在结果表中出现的分组 类似WHERE, 但WHERE过滤进入结果表的单个行，而HAVING将过滤进入结果表的整个分组 在HAVING子句中使用的列名必须在GROUP BY列表中出现，或者

15、被包含在聚合函数中,34,3.14 使用 HAVING,对每个分公司，如果有多名员工，则找出这些分公司的员工数和工资总额 SELECT branchNo, COUNT(staffNo) AS totalStaff, SUM(salary) AS totalSalary FROM Staff GROUP BY branchNo HAVING COUNT(staffNo) 1 ORDER BY branchNo;,35,子查询,子查询的结果被用在外层的 SELECT 语句中 一个子查询可以用在外层SELECT语句的WHERE和HAVING子句中，被称为子查询或嵌套查询 子查询也可以出现在INSER

16、T, UPDATE, and DELETE 语句中,36,3.15 子查询,找出位于 8 Jefferson Way的分公司工作的员工 SELECT staffNo, name, position FROM Staff WHERE branchNo = (SELECT branchNo FROM Branch WHERE street=8 Jefferson Way);,37,3.16 包含聚合函数的子查询,列出年薪高于平均年薪的所有员工 SELECT staffNo, name, position FROM Staff WHERE salary (SELECT AVG(salary) FRO

17、M Staff);,38,子查询规则,ORDER BY不能用在子查询中 (最外层的 SELECT语句可以使用). 子查询SELECT列表必须由一列列名或是一个表达式组成，除非子查询使用关键字EXISTS 默认情况，子查询中的列名是子查询FROM子句中的表中存在的列名。通过限定列名也可以引用外层查询的FROM子句中的表 当子查询是比较运算中涉及的两个操作数中的一个时，子查询必须出现在比较操作符的右侧 子查询不能出现在表达式的左侧,39,多表查询,如果需要得到来自多个表的信息，就可以选择使用子查询或是连接操作。 如果希望在最终的结果表中得到来自不同表的列，那么必须使用连接操作join 要执行连接操

18、作join，仅需在FROM子句中加上多个表名，用逗号隔开，并在WHERE子句中指定要连接的列 可以在FROM子句中使用表的别名，别名可以跟在表名后面用一个空格隔开 在列名不明确的情况下，别名可以用来指定列名的出处,40,3.17 简单连接,列出所有录像及它们的导演 SELECT catalogNo, title, category, v.directorNo, directorName FROM Video v, Director d WHERE v.directorNo = d.directorNo;,使用两个表的主键/外键进行连接，获得查询条件，得到两个表中directorNo列值相同的行

19、,41,替代连接条件,用下面的替代方法来指定连接条件： FROM Video v JOIN Director d ON v.directorNo = d.directorNo FROM Video JOIN Director USING directorNo FROM Video NATURAL JOIN Director 用FROM子句替代原先的FROM和WHERE子句。然而，第一种替代方案产生了一张有两个相同directorNo列的表。而剩下的两种方案只包含一个directorNo列的表。,42,3.18 四表连接,列出所有录像以及它们的导演、演员名单和相应的角色 SELECT v.cat

20、alogNo, title, category, directorName, actorName, character FROM Video v, Director d, Actor a, Role r WHERE d.directorNo = v.directorNo AND v.catalogNo=r.catalogNo AND r.actorNo = a.actorNo;,43,INSERT插入,INSERT INTO TableName (columnList) VALUES (dataValueList) columnList可选，如果省略它，SQL将假定所有列按照它们在建表时定义的

21、顺序排列 （CREATE TABLE ） 如果指定了columnList，那么所有在列表中省略了的列必须在表创建时被指定为可以为空，除非在创建表时指明该列可以使用默认值DEFAULT dataValueList 必须按下列规则与 columnList 匹配 两个列表中的项数必须一样 在两个列表中，各项的位置必须直接对应 dataValueList中每一项的数据类型必须与相应的列的数据类型兼容,44,3.19 INSERT 插入,向Video表中插入一行 INSERT INTO Video VALUES (207132, Die Another Day, Action 5.00, 21.99,

22、D1001 );,45,3.19 INSERT 插入,INSERT INTO Branch(branchNo,street,city,state,zipCode) VALUES(B001,8 Jefferson Way,Portland,OR,97207), (B002,City Center Plaza,Seattle,WA,98122), (B003,14-8th Avenue,New York,NY,10012), (B004,16-14th Avenue,Seattle,WA,98128);,INSERT INTO Staff(staffNo,name,position,salary,

23、branchNo) VALUES(S1500,Tom Daniels,Manager,46000,B001), (S0003,Sally Adams,Assistant,30000,B001), (S0010,Mary Martinez,Manager,50000,B002), (S2250,Sally Stern,Manager,48000,B004), (S0415,Art Peters,Manager,41000,B003);,46,UPDATE更新,UPDATE TableName SET columnName1 = dataValue1 , columnName2 = dataVal

24、ue2. WHERE searchCondition TableName 可以是表名或是是视图 SET指明需要更新的一个列或多个列的列名 WHERE 子句是可选: 如果省略，则将修改表中指定列的所有行数据 如果指定，则只有那些满足searchCondition的行才被更新 新的dataValue(s)必须跟行的数据类型兼容,47,3.20 更新表中行,将 Thriller 类的录像的日租金提高10% UPDATE Video SET dailyRental = dailyRental*1.1 WHERE category = Thriller;,48,3.20 更新表中行,/*UPDATE B

25、ranch.mgrStaffNo*/ UPDATE Branch SET mgrStaffNo = S1500 WHERE branchNo = B001; UPDATE Branch SET mgrStaffNo = S0010 WHERE branchNo = B002; UPDATE Branch SET mgrStaffNo = S0415 WHERE branchNo = B003; UPDATE Branch SET mgrStaffNo = S2250 WHERE branchNo = B004; /*UPDATE Staff.supervisorStaffNo*/ UPDATE

26、 Staff SET supervisorStaffNo = S3250 WHERE branchNo = B002;,49,DELETE删除,DELETE FROM TableName WHERE searchCondition TableName 可以是表名或者是视图 searchCondition 是可选的；如果省略，则表中所有的行都将被删除。如果指定，那么只有符合 searchCondition的行才会被删除,50,3.21 从表中删除行,删除类别号为634817的出租录像 DELETE FROM VideoForRent WHERE catalogNo = 634817;,51,数据

27、定义,两种主要SQL DDL语句 CREATE TABLE 在数据库中创建一张新表 CREATE VIEW 从基本表创建一个新的视图,52,CREATE TABLE 语句,CREATE TABLE TableName (columnName dataType NOT NULL UNIQUE DEFAULT defaultOption,. PRIMARY KEY (listOfColumns), UNIQUE (listOfColumns), , FOREIGN KEY (listOfFKColumns) REFERENCES ParentTableName (listOfCKColumns),

28、 ON UPDATE referentialAction ON DELETE referentialAction ,53,定义列,columnName dataType NOT NULL UNIQUE DEFAULT defaultOption 支持的数据类型如下：,54,定义列,该列是否不允许为空（NOT NULL） 是否该列的每个值都是唯一的，即该列是否是一个候选键（UNIQUE） 为该列指定一个默认值，当没有为该列指定新值时，将用默认值代替（DEFAULT）,55,主键子句和实体完整性,用PRIMARY KEY子句来支持实体完整性 例如： CONSTRAINT pk PRIMARY KE

29、Y (catalogNo) CONSTRAINT pk1 PRIMARY KEY (catalogNo, actorNo),56,外键子句和参照完整性,用FOREIGN KEY子句来定义外键 SQL标准通过限制子表的INSERT和UPDATE操作实现参照完整性，这些操作试图在子表中创建和父表中的候选键不相匹配的外键值。 如果是更新或删除父表中的和子表有匹配行的候选键值的UPDATE和DELETE操作， SQL通过在FOREIGN KEY子句中使用ON UPDATE和ON DELETE子操作来实现引用操作。,57,外键子句和参照完整性,ON UPDATE或ON DELETE子句使用下列关键词：

30、CASCADE 当从父表更新或删除行时，对子表中匹配的行也要做相应的更新或删除。因为这些被更新或删除的行可能还包含候选键（这些候选键又作为另一张表的外键），另外一些表的外键规则又将被触发，所以称为级联方式。 SET NULL 当从父表更新或删除行时，将子表中的外键置为NULL。 SET DEFAULT 当从父表更新或删除行时，将子表中外键的每个成员都置为一个指定的默认值。 NO ACTION 拒绝父表中的更新或删除操作。是一种默认方式，如果没有指明ON UPDATE或ON DELETE规则就采用这种方式。,58,创建Branch表,CREATE TABLE Branch ( bruanchNo

31、 char(4) NOT NULL , street varchar(30) NOT NULL , city varchar(20) NOT NULL , state char(2) NOT NULL , zipCode char(5) NOT NULL , mgrStaffNo char(5) NOT NULL CONSTRAINT XPKBranch PRIMARY KEY (bruanchNo ASC) ),59,创建Staff表,CREATE TABLE Staff ( staffNo char(5) NOT NULL , name varchar(10) NOT NULL , pos

32、ition char(10) NOT NULL , salary numeric(5,2) NOT NULL , bruanchNo char(4) NOT NULL , CONSTRAINT XPKStaff PRIMARY KEY (staffNo ASC), ),60,创建Staff和Branch的参照完整性,ALTER TABLE Staff ADD CONSTRAINT R_1 FOREIGN KEY (bruanchNo) REFERENCES Branch(bruanchNo) ON DELETE NO ACTION ON UPDATE NO ACTION ALTER TABLE

33、 Branch ADD CONSTRAINT R_3 FOREIGN KEY (mgrStaffNo) REFERENCES Staff(staffNo) ON DELETE NO ACTION ON UPDATE CASCADE,61,创建Director表,CREATE TABLE Director ( directorNo char(5) NOT NULL , directorName varchar(30) NOT NULL , CONSTRAINT XPKDirector PRIMARY KEY (directorNo ASC) ),62,创建Video表,CREATE TABLE

34、Video ( catalogNo char(6) NOT NULL , title varchar(40) NOT NULL , category varchar(20) NOT NULL , dailyRental decimal(4,2) NOT NULL DEFAULT 5.00, price decimal(4,2) NULL , directorNo char(5) NOT NULL , CONSTRAINT XPKVideo PRIMARY KEY (catalogNo ASC), CONSTRAINT R_2 FOREIGN KEY (directorNo) REFERENCES Director(d