数据库实践3-SQL和QBE

数据库设计

DatabaseSolutions第三章SQL和QBE本章主题SQL的用途和重要性用“SELECT”检索数据用“INSERT”插入数据用“UPDATE”更新数据用“DELETE”删除数据用“CREATETABLE”建表关于QBE本节主题SQL的用途和重要性用“SELECT”检索数据用“INSERT”插入数据用“UPDATE”更新数据用“DELETE”删除数据用“CREATETABLE”建表关于QBESQL关系DBMS的主要语言主要特征：相对容易学习非过程化–

只要求用户指定需要什么数据，而非指明怎样获得这些数据。自由格式由标准英语单词组成能被广泛用户使用SQL的重要性首先，迄今为止，只有标准数据库语言获得了普遍的接受。来自于厂商和用户的巨大的投资联邦信息处理标准（FIPS

）被用作其它标准的基础SQL的目标理想上，数据库语言应该让用户：创建数据库和表结构。执行基本的任务，像插入、更新、删除等。执行简单和复杂的查询。必须让用户最省力的执行这些任务。必须易于学习。SQL的目标SQL是面向变换（非过程）的语言，有两个组件：DDL：定义数据库结构DML：检索和更新数据库直到SQL3为止，SQL并不包含控制流命令SQL能被交互地使用在或嵌入在上层语言中（像C/C++等）书写SQL命令SQL语句由保留字和用户自定义字组成。保留字：SQL的固定部分，并且必须按要求正确拼写，同时不能拆分成多行（只能出现在一行里）。用户自定义字：由用户构造，用于表示不同数据库对象名，诸如表名、列名、视图名。书写SQL命令除了文字字符数据外，SQL语句中大部分的组成部分是大小写不敏感的。使用扩展的BNF标记形式大写字母表示保留字小写字母表示用户自定义字“|”

表示“或”，在可选项中的选择。圆括号表示必需的元素方括号表示可选的元素“…”

表示可选的重复（0次或多次）文字量文字量是使用在SQL语句中的常量。所有的非数值文字量必须用单引号封闭起来，如‘London’。所有的数值文字量不能用引号封闭，如6500.00。本节主题SQL的用途和重要性用“SELECT”检索数据用“INSERT”插入数据用“UPDATE”更新数据用“DELETE”删除数据用“CREATETABLE”建表关于QBESelect语句SELECT[DISTINCT|ALL]{*|[columnExprn[ASnewName]][,...]}FROMTableName[alias][,...][WHEREcondition][GROUPBYcolumnList][HAVING condition][ORDERBY columnList]Select语句FROM 标明所用的表WHERE 过滤符合某种条件的记录GROUPBY 根据各记录相同的字段进行分 组HAVING 通过某一条件过滤分组中的记 录SELECT 标明输出的列ORDERBY 对输出进行排序Select语句两个注意点：１.以上子句的顺序不能更改。２.Select和From是必须的。3.1所有的列，所有的行列出所有录像完整的细节SELECTcatalogNo,title,category,dailyRental,price,directorNoFROMVideo;可以使用“＊”表示“所有的列”的含义

SELECT*FROMVideo;3.1所有的列，所有的行3.2特定的列，所有的行列出所有录像的“分类号”，“标题”和“日租金”SELECTcatalogNo,title,dailyRentalFROMVideo;3.2特定的列，所有的行3.3使用关键字“DISTINCT”列出所有录像的种类SELECTcategory

FROMVideo;3.3使用关键字“DISTINCT”使用“DISTINCT”消除冗余（副本）。SELECTDISTINCTcategoryFROMVideo;3.4计算的域列出租借三天的录像的费用SELECTcatalogNo,title,dailyRental*3FROMVideo;3.4计算的域使用AS子句来命名列SELECTcatalogNo,title,

dailyRental*3ASthreeDayRateFROMVideo;3.5“比较”搜索条件列出薪水超过$10,000的员工SELECTstaffNo,name,position,salaryFROMStaffWHEREsalary>10000;3.6“范围”搜索条件列出薪水在$45,000～$50,000之间的员工SELECTstaffNo,name,position,salaryFROMStaffWHEREsalaryBETWEEN45000AND50000;BETWEEN的范围包括端点值。3.6“范围”搜索条件3.6“范围”搜索条件还有一个否定的版本NOTBETWEENBETWEEN没有为SQL增强表达能力，同样可以写成：

SELECTstaffNo,name,position,salary FROMStaff WHEREsalary>=45000ANDsalary<=50000;

然而，BETWEEN在表达值范围的时候是很有用的。3.7集合成员关系列出动作和儿童录像SELECTcatalogNo,title,categoryFROMVideoWHEREcategoryIN(‘Action’,‘Children’);3.7集合成员关系还有一个否定版本(NOTIN)。IN没有增强SQL的表达能力，也可以写成：SELECTcatalogNo,title,categoryFROMVideoWHEREcategory=‘Action’ORcategory=‘Children’当集合包含很多元素的时候，IN是很有效的。3.8模式匹配列出人名中第一个字是Sally的员工：SELECTstaffNo,name,position,salaryFROMStaffWHEREnameLIKE‘Sally%’;3.8模式匹配SQL有两个特殊的模式匹配符号：%:零或多个字符的序列_(下划线)：任何单一字符LIKE‘Sally%’表示前５个字符必须是Sally后跟随任何字符。3.9“空”搜索条件列出尚未归还的录像的租金：必须显式地使用特殊的关键字ISNULL测试是否为NULLSELECTdateOut,memberNo,videoNoFROMRentalAgreementWHEREdateReturnISNULL;3.9“空”搜索条件否定版本ISNOTNULL能够测试非空值3.10单列排序列出按价格降序排列的所有录像：SELECT*FROMVideoORDERBYpriceDESC;3.10单列排序SELECT语句–

聚集ISOSQL定义了五个聚集函数:COUNT返回指定列的值的个数。SUM 返回指定列的值的和。AVG 返回指定列的值的平均值。MIN 返回指定列的值的最小值。MAX 返回指定列的值的最大值。SELECT语句–

聚集每个函数操作在表的单一列上并返回单一的值。

COUNT,MIN,和MAX应用于数字和非数字域,SUM和AVG仅仅用于数字域.除了COUNT(*)之外,每个函数首先消除空值并且仅仅在剩余的非空值上操作。SELECT语句–

聚集COUNT(*)计数一张表的所有行,无论空值和重复值的情况是否发生。可以在列名前使用DISTINCT消除重复。DISTINCT对MIN/MAX没有影响,但是可能对SUM/AVG有影响。SELECT语句–

聚集聚集函数只能用于Select列表和Having子句中。如果SELECT列表中包括聚集函数并且没有GROUPBY子句，那么SELECT列表不能参照用聚集函数的列。如，下例是非法的：SELECTstaffNo,COUNT(salary)FROMStaff;3.11使用COUNT和SUM列出薪水超过$40,000员工的总数以及他们薪水的总和。SELECTCOUNT(staffNo)AStotalStaff, SUM(salary)astotalSalaryFROMStaffWHEREsalary>40000;3.11使用COUNT和SUM3.12使用MIN,MAX和AVG列出员工薪水的最大值，最小值和平均值：SELECTMIN(salary)ASminSalary,MAX(salary)ASmaxSalary,AVG(salary)ASavgSalaryFROMStaff;SELECT语句–分组使用GROUPBY子句得到记录分组。SELECT和GROUPBY合用在一起：在SELECT列表中的每个项目必须是每组单值的（single-valuedpergroup

），并且SELECT子句只能包含：列名聚集函数常数上述内容组合的表达式SELECT语句–分组在SELECT列表中所有的列名必须出现在GROUPBY子句中，除非仅用于聚集函数中。WHERE首先执行，然后在满足谓词的剩余的记录中形成分组。对于GROUPBY，ISO认为两个NULL值是相等的。3.13使用GROUPBY找出每个分公司的员工数，以及他们的薪水总数。

SELECTbranchNo,COUNT(staffNo)AStotalStaff, SUM(salary)AStotalSalaryFROMStaffGROUPBYbranchNoORDERBYbranchNo;3.13使用GROUPBY受限分组–HAVING子句设计HAVING子句同GROUPBY子句一起使用，用于限制最后出现在结果表中的分组。相似于WHERE，但是WHERE过滤单独的行然而HAVING过滤分组。HAVING子句中的列名必须出现在GROUPBY列表中或者包含在聚集函数中。3.14使用HAVING对每个超过1个员工的分公司，找出每个分公司的员工数，以及他们的薪水总数。

SELECTbranchNo,COUNT(staffNo)AStotalStaff, SUM(salary)AStotalSalaryFROMStaffGROUPBYbranchNoHAVINGCOUNT(staffNo)>1ORDERBYbranchNo;3.14使用

HAVING子查询一些SQL语句能够嵌套SELECT语句。子select能够使用在外层SELECT语句的WHERE和HAVING子句中，称作子查询或者嵌套查询。子select可以出现在INSERT,UPDATE和DELETE语句中。3.15使用等式的子查询找出地处8JeffersonWay街的分公司工作的员工。

SELECTstaffNo,name,position FROMStaff WHEREbranchNo= (SELECTbranchNo FROMBranch WHEREstreet=‘8JeffersonWay’);3.15使用等式的子查询内层SELECT找出地处8JeffersonWay街的分公司号(‘B001’)。外层SELECT就检索工作在该分公司的所有员工的细节。外层SELECT等同于:

SELECTstaffNo,name,positionFROMStaffWHEREbranchNo=‘B001’;3.15使用等式的子查询3.16使用聚集的子查询列出薪水高于平均值的所有员工。SELECTstaffNo,name,positionFROMStaffWHEREsalary>

(SELECTAVG(salary)

FROMStaff);3.16使用聚集的子查询不能写成‘WHEREsalary>AVG(salary)’使用子查询找出平均的薪水(41166.67),同时使用外层SELECT找出那些薪水高于这个值的员工:SELECTstaffNo,name,positionFROMStaffWHEREsalary>41166.67;3.16使用聚集的子查询子查询规则ORDERBY子句不可以用在子查询中（尽管它可以用在最外层SELECT中）。除了使用EXISTS子句的子查询情况外，子查询SELECT列表必须由单一的列名或者表达式组成。缺省情况下,列名引用了在子查询的FROM子句中的表名；可以在FROM中使用别名指定表。当子查询作为比较运算中的一个运算数时，子查询必须出现在右手边。子查询不可以作为表达式的运算数。多表查询如果结果列来自于同一表，那么可以使用子查询。如果结果列来自于多个表，那么必须使用连接。为了能够进行连接操作，要在FROM子句中包括多个表（使用逗号分隔表名）。典型地，用WHERE指定进行连接的列。多表查询可能的话，为在FROM子句中指定的表使用别名。

别名同表名用空格分隔。

当存在歧义时，可以用别名修饰列名消除歧义。3.17简单连接

列出所有的录像及其导演：

SELECTcatalogNo,title,category,

v.directorNo,directorName

FROMVideov,Directord

WHEREv.directorNo=d.directorNo;3.17简单连接仅仅两张表的列directorNo相同的记录(v.directorNo=d.directorNo)包含在结果中。选择连接构造可选择的连接的方法:

FROMVideov

JOINDirectordONv.directorNo=d.directorNo Video用directorNo同Director连接

Video同Director进行自然连接FROM代替了原来的FROM和WHERE。然而，首先产生具有两个相等directorNo列值的表。3.18四表连接列出所有录像、该录像的导演、演员和演员角色：

SELECTv.catalogNo,title,category,directorName,actorName,character

FROMVideov,Directord,Actora,Roler

WHEREd.directorNo=v.directorNoAND

v.catalogNo=r.catalogNoAND

r.actorNo=a.actorNo;3.18四表连接本节主题SQL的用途和重要性用“SELECT”检索数据用“INSERT”插入数据用“UPDATE”更新数据用“DELETE”删除数据用“CREATETABLE”建表关于QBE插入INSERTINTOTableName[(columnList)]VALUES(dataValueList)columnList

是可选的;如果省略的话,SQL就认为columnList是以原始的CREATETABLE顺序的所有的列。

当创建表时，任何省略的列必须已经声明为NULL值或者指定为默认值。插入dataValueList

必须如下匹配columnList：每个列表中的项的数目必须相同;两个列表中项的位置必须是直接对应的;在dataValueList中每个项的数据类型必须与对应的列的数据类型相容。插入在Video中插入一行：

INSERTINTOVideoVALUES(‘207132’,‘DieAnotherDay’,‘Action’5.00,21.99,‘D1001’

);本节主题SQL的用途和重要性用“SELECT”检索数据用“INSERT”插入数据用“UPDATE”更新数据用“DELETE”删除数据用“CREATETABLE”建表关于QBE更新UPDATETableNameSETcolumnName1=dataValue1 [,columnName2=dataValue2...][WHEREsearchCondition]TableName

基表或是可更新的视图SET子句指明了要进行更新的列名更新WHERE子句是可选的:如果省略,表中所有的行会被更新;如果被指定,仅仅满足查询条件searchCondition的行被更新。

新的数据值dataValue(s)

必须同对应的列的数据类型相容。3.20更新表中的行把‘Thriller’类别的录像的日租金提高10%。 UPDATEVideo SETdailyRental=dailyRental*1.1 WHEREcategory=‘Thriller’;本节主题SQL的用途和重要性用“SELECT”检索数据用“INSERT”插入数据用“UPDATE”更新数据用“DELETE”删除数据用“CREATETABLE”建表关于QBE删除DELETEFROMTableName[WHEREsearchCondition]TableName基表或是可更新的视图searchCondition

可选。如果省略,表中所有行被删除，但不删除表。如果指定searchCondition,只有满足条件的行会被删除。3.21删除指定的行删除分类号是634817的租贷录像.

DELETEFROMVideoForRent

WHEREcatalogNo=‘634817’;本节主题SQL的用途和重要性用“SELECT”检索数据用“INSERT”插入数据用“UPDATE”更新数据用“DELETE”删除数据用“CREATETABLE”建表关于QBE数据定义两个主要的SQLDDL语句:CREATETABLE–

创建新表。CREATEVIEW–

创建新视图。CREATETABLE

语句CREATETABLETableName{(columnNamedataType[NOTNULL][UNIQUE][DEFAULTdefaultOption][,...]}[PRIMARYKEY(listOfColumns),]{[UNIQUE(listOfColumns),][…,]}{[FOREIGNKEY(listOfFKColumns)REFERENCESParentTableName[(listOfCKColumns)],[ONUPDATEreferentialAction][ONDELETEreferentialAction]][,…]}定义列columnNamedataType[NOTNULL][UNIQ