[理学]第3章1建表+单表查询ppt课件

1、第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1 SQL概述3.2 数据定义3.3 查询3.4 数据更新3.5 视图3.6 数据控制3.7 嵌入式SQL3.8 小结qSQL语言n 1974年由Boyce和Chamberlin提出nSQL全称Structured Query Languagen原名：SEQUELStructured English QUEry Languagen前身：SQUARESpecifying QUEries as Relational Expressionsn1975年1979年IBM公司在System R原型系统上实现n是关系数据库的标准语言，是数据库领域

2、中一个主流语言3.1 SQL概述概述3.1 SQL概述续概述续qSQL标准n SQL-86n第一个SQL标准n由美国国家标准局 American National Standard Institute，简称ANSI公布n1987年国际标准化组织International Organization for Standardization，简称ISO通过n SQL-89，SQL1 n SQL-92，SQL2 n SQL-99，SQL3n SQL-20033.1 SQL概述续概述续qSQL的特点n1. 综合统一n2. 高度非过程化n3. 面向集合的操作方式n4. 以同一种语法构造提供两种使用方法n5

3、. 语言简洁，易学易用1. 综合统一综合统一qSQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体q 非关系模型的数据语言n形式数据定义语言形式DDL n外形式数据定义语言外形式DDL或子形式DDL n与数据存储有关的描绘语言DSDL n数据操纵语言DML2. 高度非过程化高度非过程化q用户只需提出“做什么，而不必指明“怎么做 q存取途径的选择以及SQL语句的操作过程由系统自动完成。大大减轻了用户负担， 而且有利于进步数据独立性。3. 面向集合的操作方式面向集合的操作方式qSQL语言采用集合操作方式n 操作对象、查找结果可以是元组的集合n 一次插入、删除、更新操作

4、的对象可以是元组的集合q非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。4.同一种语法构造提供两种使用方式同一种语法构造提供两种使用方式q自含式语言交互式n可以独立地用于联机交互的使用方式q嵌入式语言宿主式n可以嵌入到高级语言 例如C，COBOL， FORTRAN，PL/1程序中，供程序员设计程序时使用。q两种不同使用方式下，SQL语言的语法构造根本一致5. 语言简捷，易学易用语言简捷，易学易用表表 3.1 SQL 语言的动词语言的动词 SQL 功功 能能 动动 词词 数数 据据 定定 义义 CREATE，DROP，ALTER 数数 据据 查查 询询 SELECT 数数 据据 操

5、操 纵纵 INSERT，UPDATE，DELETE 数数 据据 控控 制制 GRANT，REVOKE SQL语言的根本概念语言的根本概念qSQL 语言支持关系数据库三级形式构造q用户用SQL语言对根本表和视图进展操作q根本表n 本身独立存在的表，在SQL中一个关系就对应一个表n 一个或多个根本表对应一个存储文件n 一个表可以带假设干索引，索引也存放在存储文件中q 存储文件n 存储文件的逻辑构造组成了关系数据库的内形式n 存储文件的物理构造是任意的，对用户是透明的q 视图n 从一个或几个根本表或视图导出的表n 是虚表，只存放视图的定义而不存放对应数据3.2 数数 据据 定定 义义 qSQL的数据

6、定义功能n定义表形式 n 创立表n 删除表n 修改表定义n 定义视图外形式 n 创立视图n 删除视图n 间接修改视图定义：删除+创立n 定义索引内形式 n 创立索引n 删除索引n 间接修改索引定义：删除+创立3.2 数数 据据 定定 义义续续表 3.2 SQL 的数据定义语句 操操 作作 方方 式式 操操作作 对对象象 创创 建建 删删 除除 修修 改改 表表 CREATE TABLE DROP TABLE ALTER TABLE 视视 图图 CREATE VIEW DROP VIEW 索索 引引 CREATE INDEX DROP INDEX 3.2.1 定义、删除与修改根本表定义、删除与修

7、改根本表一、定义根本表CREATE TABLE 二、修改根本表ALTER TABLE 三、删除根本表DROP TABLE一、定义根本表一、定义根本表q语句格式CREATE TABLE ， ， ；n：所要定义的根本表的名字n：组成该表的各个属性列n：涉及相应属性列的完好性约束条件n：涉及一个或多个属性列的完好性约束条件 一个简单例子一个简单例子 例1 建立一个“学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。 CREATE TABLE Student Sno CHAR5 PRIMARY KEY， Sname CHAR20 ， Sse

8、x CHAR1 ， Sage INT， Sdept CHAR15； 例题例题 续续SnoSnameSsexSageSdept 字字符符型型 字字符符型型 字字符符型型 整整数数 字字符符型型 长长度度为为5 5 长长度度为为2 20 0 长长度度为为1 1 长长度度为为1 15 5 不不能能为为空空值值数据类型数据类型qCHARnqVARCHARnqINTqSMALLINTqNUMERICp,sqFLOATnqDATEqTIME定义根本表续定义根本表续q不同的数据库系统支持的数据类型不完全相nTransact-SQL 含有以下根本数据类型：biginttinyintbitintSmallint

9、realDecimalfloatmoneysmallmoneycharNcharntextnvarcharVarcharsmalldatetimetimestampdatetimeimageVarbinarytextBinary uniqueidentifierq常用完好性约束n实体完好性约束： PRIMARY KEYn参照完好性约束： FOREIGN KEYn用户定义的完好性约束n唯一性约束：UNIQUEn非空值约束：NOT NULLn检查性约束：CHECK实体完好性定义实体完好性定义q例 建立一个“学生选课表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中Sno, Cno为

10、主码。CREATE TABLE SC Sno CHAR5 , Cno CHAR3 , Grade int, PRIMARY KEY Sno, Cno;参照完好性定义参照完好性定义CREATE TABLE SC Sno CHAR5 REFERENCES StudentSno , Cno CHAR3 REFERENCES CourseCno, Grade int, PRIMARY KEY Sno, Cno;破坏参照完好性的四种情况破坏参照完好性的四种情况q参照表回绝n插入一个元组n修改外码的值q被参照表回绝，级联操作，置空值，置缺省值n删除一个元组n修改主码的值被参照表Sno参照表StudentS

11、no, SCSno,Cno,Grade违约处理策略违约处理策略q回绝操作NO ACTIONq级联操作CASCADEq置空值SET NULLq置缺省值SET DEFAULTCREATE TABLE SC Sno CHAR5 REFERENCES StudentSno, Cno CHAR3, Grade int, PRIMARY KEY Sno, Cno, FOREIGN KEY Cno REFERENCES CourseCnoON DELETE NO ACTIONON UPDATE CASCADE ;用户定义的完好性用户定义的完好性例：CREATE TABLE Student Sno CHAR5

12、 UNIQUE， Sname CHAR20 NOT NULL， Ssex CHAR1 CHECK Ssex IN F,M ， Sage INT DEFAULT 18， Sdept CHAR15； 用户定义的完好性用户定义的完好性q例：Create Table RCDA 编号 Char8 Primary Key, 性别 Char2, 出生日期 Datetime, 工资现状Decimal10,2 Default 1000 Check 工资现状10000二、修改根本表二、修改根本表q语句格式ALTER TABLE ADD 完好性约束 DROP MODIFY ；n：要修改的根本表nADD子句：增加新列

13、和新的完好性约束条件nDROP子句：删除指定的完好性约束条件nMODIFY子句：用于修改列名和数据类型例题例题 q例2 向Student表增加“入学时间列，其数据类型为日期型。ALTER TABLE Student ADD Scome DATE；n不管根本表中原来是否已有数据，新增加的列一律为空值。 q例3 将年龄的数据类型改为半字长整数。ALTER TABLE Student MODIFY Sage SMALLINT； n修改原有的列定义有可能会破坏已有数据。q例4 删除学生姓名必须取唯一值的约束。ALTER TABLE Student DROP UNIQUESname；语句格式续语句格式续

14、q删除属性列n间接删除n把表中要保存的列及其内容复制到一个新表中n删除原表n再将新表重命名为原表名n直接删除属性列:新例：ALTER TABLE Student Drop Scome；三、删除根本表三、删除根本表 DROP TABLE ; q例5 删除Student表 DROP TABLEStudent ;根本表删除 数据、表上的索引都删除 表上的视图往往仍然保存，但无法引用3.2.2 建立与删除索引建立与删除索引 q建立索引是加快查询速度的有效手段q建立索引nDBA或表的属主即建立表的人根据需要建立n有些DBMS自动建立以以下上的索引n PRIMARY KEYn UNIQUEq维护索引n D

15、BMS自动完成 q使用索引n DBMS自动选择是否使用索引以及使用哪些索引一、建立索引一、建立索引 q语句格式CREATE UNIQUE CLUSTER INDEX ON , ；n指定要建索引的根本表名字n索引可以建立在该表的一列或多列上，各列名之间用逗号分隔n指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCnUNIQUE说明此索引的每一个索引值只对应唯一的数据记录nCLUSTER表示要建立的索引是聚簇索引例题例题 例6 为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC

16、表按学号升序和课程号降序建唯一索引。CREATE UNIQUE INDEX Stusno ON StudentSno；CREATE UNIQUE INDEX Coucno ON CourseCno；CREATE UNIQUE INDEX SCno ON SCSno ASC，Cno DESC； 建立索引建立索引 续续q唯一值索引n对于已含重复值的属性列不能建UNIQUE索引n对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束建立索引建立索引 续续q聚簇索引n建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存

17、放。n也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATE CLUSTER INDEX Stusname ON StudentSname；在Student表的Sname姓名列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放 建立索引建立索引 续续n在一个根本表上最多只能建立一个聚簇索引n在最常查询的列上建立聚簇索引，可以进步查询效率n对于经常更新的列不宜建立聚簇索引 二、删除索引二、删除索引 DROP INDEX ；n删除索引时，系统会从数据字典中删去有关该索引的描绘。例7 删除Student表的Stusname索引。DROP INDEX Stusnam

18、e；3.3 查查 询询 3.3.1 概述3.3.2 单表查询3.3.3 连接查询3.3.4 嵌套查询3.3.5 集合查询3.3.6 小结 3.3.1 概述概述 q语句格式SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ； 语句格式语句格式nSELECT子句：指定要显示的属性列nFROM子句：指定查询对象根本表或视图nWHERE子句：指定查询条件nGROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。nHAVING短语：挑选出满足指定条件的组nORDER

19、BY子句：对查询结果表按指定列值的升序或降序排序 例如数据库例如数据库 学生-课程数据库q学生表：Student#Sno，Sname，Ssex，Sage，Sdeptq课程表：Course#Cno，Cname，Cpno，Ccreditq学生选课表：SC#Sno，#Cno，Grade 3.3 查查 询询 3.3.1 概述3.3.2 单表查询3.3.3 连接查询3.3.4 嵌套查询3.3.5 集合查询3.3.6 小结 3.3.2 单表查询单表查询 查询仅涉及一个表，是一种最简单的查询操作一、选择表中的假设干列二、选择表中的假设干元组三、对查询结果排序四、使用集函数五、对查询结果分组 1.查询指定列查

20、询指定列例1 查询全体学生的学号与姓名。SELECT Sno，SnameFROM Student； 例2 查询全体学生的姓名、学号、所在系。SELECT Sname，Sno，SdeptFROM Student；2.查询全部列查询全部列q例3 查询全体学生的详细记录。SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或SELECT *FROM Student； 3. 查询经过计算的值查询经过计算的值SELECT子句的为表达式n算术表达式n字符串常量n函数n列别名n等 3. 查询经过计算的值续查询经过计算的值续例4 查全体学生的姓名及其出生年份。SELE

21、CT Sname，2007-SageFROM Student； 输出结果： Sname 2007-Sage - - 李勇 1976 刘晨 1977 王名 1978 张立 1978 3. 查询经过计算的值续查询经过计算的值续例5 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECT Sname，Year of Birth: ，2007-Sage， ISLOWERSdeptFROM Student； 例题续例题续输出结果： Sname Year of Birth: 2007-Sage ISLOWERSdept - - - - 李勇 Year of Birth: 1976

22、cs 刘晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 张立 Year of Birth: 1977 is 例例5.1 使用列使用列别名别名改变查询结果的列标题改变查询结果的列标题SELECT Sname NAME，Year of Birth: BIRTH，2007-Sage BIRTHDAY，ISLOWERSdept DEPARTMENTFROM Student；输出结果： NAME BIRTH BIRTHDAY DEPARTMENT - - - - 李勇 Year of Birth: 1976 cs 刘晨 Year of Birth:

23、1977 is 王名 Year of Birth: 1978 ma 张立 Year of Birth: 1977 is二、选择表中的假设干元组二、选择表中的假设干元组 q消除取值重复的行q查询满足条件的元组 1. 消除取值重复的行消除取值重复的行n在SELECT子句中使用DISTINCT短语假设SC表中有以下数据 Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80ALL 与与 DISTINCT 例6 查询选修了课程的学生学号。1 SELECT Sno FROM SC;或或默认默认 ALL SELE

24、CT ALL Sno FROM SC; 结果：结果： Sno - 95001 95001 95001 95002 95002 例题续例题续2 SELECT DISTINCT Sno FROM SC; 结果： Sno - 95001 95002 2.查询满足条件的元组查询满足条件的元组表表3.3 常用的查询条件常用的查询条件查查 询询 条条 件件谓谓 词词比比 较较=，=，=，!=，!，!；NOT +上述比较运算符上述比较运算符确定范围确定范围BETWEEN AND，NOT BETWEEN AND确定集合确定集合IN，NOT IN字符匹配字符匹配LIKE，NOT LIKE空空值值IS NULL，

25、IS NOT NULL多重条件多重条件AND，ORWHERE子句常用的查询条件1 比较大小比较大小q在WHERE子句的中使用比较运算符n=，=，=，!= 或 ，!，!， n逻辑运算符NOT + 比较运算符q例8 查询所有年龄在20岁以下的学生姓名及其年龄。SELECT Sname，Sage FROM Student WHERE Sage = 20； 2 确定范围确定范围q使用谓词 BETWEEN AND NOT BETWEEN AND 例10 查询年龄在2023岁包括20岁和23岁之间的学生的姓名、系别和年龄。 SELECT Sname，Sdept，SageFROM StudentWHERE

26、Sage BETWEEN 20 AND 23； 例题续例题续例11 查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECT Sname，Sdept，SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23； 3 确定集合确定集合q使用谓词 IN , NOT IN n ：用逗号分隔的一组取值q例12查询信息系IS、数学系MA和计 算机科学系CS学生的姓名和性别。SELECT Sname，SsexFROM StudentWHERE Sdept IN IS，MA，CS ;3 确定集合确定集合q例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓

27、名和性别。SELECT Sname，SsexFROM StudentWHERE Sdept NOT IN IS，MA，CS ;4 字符串匹配字符串匹配q NOT LIKE ESCAPE n搜索与指定形式匹配的字符串、日期或时间值。 n：指定匹配模板n匹配模板：固定字符串或含通配符的字符串n当匹配模板为固定字符串时，可以用 = 运算符取代 LIKE 谓词，用 != 或 运算符取代 NOT LIKE 谓词通配符通配符n% 百分号 代表任意长度长度可以为0的字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串n_ 下横线 代表任意单个字符n例：a_

28、b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串ESCAPE 短语：短语：n当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE 短语对通配符进展转义。例题例题1 匹配模板为固定字符串例14 查询学号为95001的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE 95001；等价于： SELECT * FROM Student WHERE Sno = 95001；例题续例题续2 匹配模板为含通配符的字符串例15 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM St

29、udent WHERE Sname LIKE 刘%；例题续例题续q例16 查询姓欧阳且全名为三个汉字的学生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 欧阳_；例题续例题续q例17 查询名字中第2个字为阳字的学生的姓名和学号。 SELECT Sname，Sno FROM Student WHERE Sname LIKE _阳%；例题续例题续q例18 查询所有不姓刘的学生姓名。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 刘%；例题续例题续3 使用换码字符将通配符转义为普通字符

30、例19 查询DB_Design课程的课程号和学分。 SELECT Cno，Ccredit FROM CourseWHERE Cname LIKE DB_Design ESCAPE 当转义符置于通配符之前时，该通配符就解释为普通字符。 例题续例题续q例20 查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ；5 涉及空值的查询涉及空值的查询n 使用谓词 IS NULL 或 IS NOT NULLn “IS NULL 不能用 “= NULL 代替q例21 某些学生选修课程后没有参加

31、考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECT Sno，Cno FROM SC WHERE Grade IS NULL；例题例题续续 q 例22 查所有有成绩的学生学号和课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；6 多重条件查询多重条件查询q用逻辑运算符AND和 OR来联结多个查询条件n AND的优先级高于ORn 可以用括号改变优先级q可用来实现多种其他谓词n NOT INn NOT BETWEEN AND 例题例题q例23 查询计算机系年龄在20岁以下的学生姓名。 SELECT Snam

32、e FROM Student WHERE Sdept= CS AND Sage=20 AND Sage=23；三、对查询结果排序三、对查询结果排序 q使用ORDER BY子句n 可以按一个或多个属性列排序n 升序：ASC；降序：DESC；缺省值为升序n当排序列含空值时nASC：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示 对查询结果排序续对查询结果排序续 q例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC； 查询结果查询结果 Sno Grade - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55对查询结果排序续对查询结果排序续 q例25 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept，Sage DESC； 四、使用集函数四、使用集函数 q5类主要集函数n计数nCOUNTDISTINCT|ALL *nCOUNTDIST