关系数据库查询语言SQL.docx

1、关系数据库查询语言SQL授课方式：以下所有的例子都在sql server 中进行现场调试其执行结果，或者让学生上讲台练习，让学生体会其具体的含义 第四章关系数据库查询语言SQL（8学时）第1节 关系数据库标准语言SQL 查询部分一、SQL概述 1SQL标准SQL（Structured Query Language）：结构化数据库查询语言。SQL作为一个ANSI标准，现在最新的标准是SQL99！是介于关系代数和关系演算之间的结构化查询语言，功能包括数据查询（Data Query）、数据操纵（Data Manipulation）、数据定义（Data Definition）、数据控制（Data Co

2、ntrol）2SQL的特点1）综合统一SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL功能于一体，语言风格统一。可独立完成数据库生命周期中的全部活动，包括：定义关系模式、建立数据库、插入数据、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，为数据库应用系统开发提供了良好的环境。由于关系模型中实体间连续军用关系表示，这种数据结构单一性带来了数据操作的统一，查找、插入、删除、修改等每一种操作只需要一种操作符，从而克服了非关系系统由于信息表示方式多样性带来的操作复杂性。2）高度非过程化SQL语言只要提出“做什么”，无须指明“怎么做”！用户无须了解存取路径，存取路

3、径的选择及SQL语句的操作过程由系统自动完成。减轻了用户的负担，提高了数据独立性。3）面向集合的操作方式Sql 语言采用集合操作方式，不仅操作对象、查询结果可以是元组集合，而且一次插入、删除、更新操作的对象可是元组的集合。4）以同一种语法结构提供两种使用方式既是自含式语言，又是嵌入式语言。自含式语言：能独立的用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。在SQL SERVER 2000种现场演示SQL语句的操作。嵌入式语言：SQL语句能嵌入到高级语言程序中供程序员设计时使用。5）语言简洁，易学易用功能强大，但是语言十分简捷，完成核心功能共用了9个动词。因此其使

4、用技巧上要多练习。SQL语言的动词如下：SQL功能动词数据查询SELECT数据定义CREATE，DROP，ALTER数据操纵INSERT，UPDATE，DELETE数据控制GRANT， REMOVE附：SQL语言功能表3SQL语言的基本概念 SQL语言支持关系数据库三级模式结构：外模式对应于视图（VIEW）和部分基本表（BASE Table），模式对应于基本表，内模式对应于存储文件。基本表：本身独立存在的表，在SQL中一个关系对应一个表。一个（或多个）基本表对应一个存储文件，一个表壳带若干个索引，索引也存放在存储文件中。视 图：从一个或几个基本表导出的表。本身不独立存储在数据库中，即数据库中只

5、存放视图的定义不存放视图对应的数据，这些数据仍存放在导出这个视图的基本表中，因此视图是一个虚表。视图与基本表等同，可在视图上再定义视图。SQL语言可对基本表和视图进行各种操作，二者都是关系。二、数据定义关系数据库三级模式：模式、外模式、内模式中对应的基本对象为：表，视图和索引。故数据定义功能包括定义表、定义视图、定义索引。由于视图和索引是依附于基本表的，故不提供修改操作，如需修改可删除后再建立！数据定义语句如下： 1定义、删除与修改基本表 1）定义基本表（CREATE TABLE） CTEATE TABLE (列级完整性约束条件，列级完整性约束条件 ，表级完整性约束条件 ； 是标识符，实际书写

6、时其内容必须有，但是尖括号不能有 表示其内部的项目可以有，也可没有。例：创建一个“学生”表student ，由学号sno、姓名sname、性别ssex、年龄sage、所在系sdept五个属性组成，其中学号不能为空，值是唯一的，且姓名的值也唯一。 CREATE TABLE student (sno char(5) not null uique, sname char(20) unique, ssex char(1), sage int, sdept char(15);定义表的各个属性要指明数据类型和长度。不同的数据库支持的数据类型不同。SQL SERVER中的数据类型如下：Binary：最多80

7、00个二进制字节； bit：整数0或1 ； char：定长字符串Datetime：日期和时间值，范围1/1/1753到12/31/9999Float：浮点小数，范围是-1.79e+3081.79e+308 ；image：变长位串，最大值为2312）修改基本表格式：ALTER TABLE ADD 完整性约束 /添加新列 DROP /删除完整性约束条件 MODIFY; /修改原来定义的列例：向学生表Student表中增加“入学时间”列，数据类型为日期型，将年龄的数据类型改为半字长整型，删除学生姓名必须取唯一值约束。ALTER TABLE Student ADD Scome DATE; /MODIF

8、Y Sage SMALLINT 未调试成功 / DROP UNIQUE(Sname) 思考如何实现删除一定的列？3）删除基本表（物理删除）DROP TABLE例：删除student表DROP TABLE student;4）如何通过已建立的表复制到一个新表中*只复制表的结构SELECT TOP 0 * INTO newtable FROM oldtable； *同时将数据也复制进去，去掉top 0 即可 SELECT * INTO jiameng FROM hezuo思考：能否只复制部分列及其数据2建立和删除索引索引是提高查询速度的有效手段，由数据库管理员DBA或表的属主负责完成。1）索引的建

9、立格式：CREATE UNIQUE CLUSTER INDEX ON (,次序)；说明：a）索引可以建立在一列或多个列上，各列名之间用逗号隔开；b）每个可用指定索引值排列顺序，可选ASC（升序）或DESC（降序），缺省为ASC；c）UNIQUE表明此索引每一个索引值只能对应唯一的数据记录值d）CLUSTER表示要建立的索引是聚簇索引，一个表最多只能建立一个聚簇索引。补充：索引与主键的区别主键：能够唯一的标识每行的一列或列的集合。索引：提高查询速度的方式。很多数据库自动为表的主键建立索引。索引种类：a）唯一索引：不允许两行有相同的索引。B）聚簇（群集）索引：在内部排序值得索引，索引排序结构规定了

10、行的存储结构。必须唯一。即索引项的顺序与表中记录的物理顺序是一致的索引组织。C）非群集索引：对表中的行成索引，但并不改变它们的存储顺序。不要求唯一例：为学生-课程数据库中的student ，course，sc三个表建立索引。其中student按学号升序建立唯一索引，course按课程号升序建立唯一索引，sc按学号升序和课程号降序建立唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Coucno ON Course(Cno);CREATE UNIQUE INDEX Scno ON SC(Sno ASC，Cno

11、DESC);2)删除索引格式：DROP INDEX;例：DROP INDEX Stusname;三、查询使用如下的三个表 学生表：Student(Sno,Ssex,Sage,Sdept) Sno 为主码 课程表：Course(Cno,Cname,Cpno,Ccredit) Cno为主码 课程号、课程名、选修课号、学分组成 学生选课表：SC(Sno,Cno,Grade) （Sno，Cno）为主码查询的种类（四类）：A）单表查询 b）连接查询 c）嵌套查询 d）集合查询查询的一般格式：SELECT ALL|DISTINCT, FROM, WHERE GROUP BYHAVING ORDER BYA

12、SC|DESC;（一）单表查询：仅涉及一个表的查询1选择一个表的若干列1）查询指定列例：查询全体学生的学号与姓名 SELECT Sno,sname FROM Student;练习：查询全体学生的姓名、学号、所在系SELECT sname, sno, sdept FROM student2）查询全部列将表中的所有属性列都选出来，方法有二：将各列名都列出来； 使用*代替目标列如：查询全体学生的详细记录SELECT * FROM Student ; 等价于 SELECT Sno,Ssex,Sage,Sdept FROM Student3）查询经过计算的值SELECT 子句的不仅可是表中的属性列，也可

13、是表达式。例：查询全体学生的姓名及其出生年份SELECT Sname,2006-sage FROM Student 目标列的内容：属性列，表达式，字符串常量，函数等。例：查询全体学生的姓名、出生年份和所有系，要求用小写字母标示所有系名。SELECT Sname, Year of Birth:, 2006-Sage, LOWER(Sdept) FROM Student; 可以指定别名来改变查询结果的列标题SELECT Sname NAME,Year of Birth:BIRTH,2006-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT FROM Student;2.

14、选择表中若干元组1）消除取值重复的行 DISTINCT例：SELECT DISTINCT Sno FROM SC; 缺省为ALL，即保留表中取值重复的行。2）查询满足条件的元组WHERE子句查询条件共有六大类，功能如表查询条件谓词功能比较=，,=,=,!=,!=,!,!; not +比较运算符用于比较，not用于对比较运算符取反确定范围BETWEEN AND，NOT BETWEEN AND查询属性值在或不在指定范围内的元组确定集合IN，NOT IN查找属性值属于指定集合的元组字符匹配LIKE， NOT LIKE用来进行字符串的匹配空值IS NULL，IS NOT NULL空值查询多重条件AND

15、，OR连接多个查询条件A）比较大小例：查询计算机系全体学生的名单SELECT Sname FROM Student WHERE Sdept=计算机;例：查询所有年龄在20岁以下的学生姓名及其年龄SELECT Sname,Sage FROM Student WHERE Sage20;例：查询考试成绩有不及格的学生的学号SELECT DISTINCT Sno FROM sc WHERE grade60;B)确定范围谓词BETWEEN AND 和NOT BETWEENAND可以用来查找属性值在（或不在）指定范围内的元组，其中BETWEEN后面是范围的下限，AND后面是范围的上限。例：查询年龄在20至

16、23岁之间的学生姓名、性别和年龄 SELECT Sname,Ssex,Sage FROM Student WHERE Sage BETWEEN 20 AND 23 C）确定集合谓词IN用来查找属性值属于指定集合的元组。例：查询信息系（IS）、数学系（MA）和计算机系（CS）的学生的姓名和性别SELECT Sname,Ssex FROM Student WHERE Sdept IN (信息系,数学系,计算机);D)字符匹配谓词LIKE可以用来进行字符串的匹配。格式：NOTLIKEESCAPE含义：查找指定的属性列值与相匹配的元组。可以是完整的字符串，也可含有通配符%和_.%代表任意长度（可为0）

17、的字符串。例对于a%b， 串acb，adcb，ab都匹配。_代表任意单个字符。 对于a_b ，串acb，afb都匹配。例：查询学号为95001的学生的详细情况SELECT * FROM Student WHERE Sno LIKE 95001;等价于 SELECT * FROM Student WHERE Sno=95001;如果LIKE 后面的匹配串中不含通配符，则可用=运算符替代LIKE例：查询所有姓张的学生的姓名、学号和性别SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 张% 查询名字中第二个字为“华”字的学生的姓名和学号SELEC

18、T Sname，Sno FROM Student WHERE Sname LIKE_ _阳%； /一个汉字占两个字节查询DB_Design课程的课程号和学分SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_DesignESCAPEESCAPE表示是转义字符，使得_不再是通配符，转义为普通字符“_”E)涉及空值的查询例：查询缺少成绩的学生的学号和相应的课程号SELECT Sno,Cno FROM SC WHERE Grade IS NULL;F）多重条件查询逻辑运算符AND 和OR 用来连接多个查询条件。AND的优先级高于OR，可用括号改变优先

19、级。例：查询传媒技术系年龄在22岁以下的学生姓名SELECT Sname FROM Student WHERE Sage22 AND Sdept=传媒技术系;3.对查询结果排序用ORDER BY 子句对查询结果按照一个或多个属性列的升序或降序排序，缺省为ASC例：查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECT Sno,Grade FROM SC WHERE Cno=3 ORDER BY Grade DESC4集函数的使用集函数功能如下COUNT（DISTINCT|ALL*） 统计元组个数COUNT（DISTINCT|ALL） 统计一列中值得个数SUM(DISTIN

20、CT|ALL) 统计一列中值得总合（此列必须是数值型）AVG(DISTINCT|ALL) 计算一列值得平均值（此列必须是数值型）MAX(DISTINCT|ALL) 求一列中的最大值MIN(DISTINCT|ALL) 求一列的中的最小值例：查询学生总数SELECT COUNT(*) FROM Student查询选修了课程的学生人数SELECT COUNT（DISTINCT Sno） FROM SC； /避免重复计算选课的学生人数计算1号课程的学生平均成绩SELECT AVG(Grade) FROM SC WHERE Cno=1;查询选修1号课程的学生的最高分SELECT MAX（Grade） F

21、ROM SC WHERE Cn0=1；5对查询结果分组GROUP BY 子句将查询结果表按某一列或多列值分组，值相等的为一组。例：求各个课程号及相应的选课人数SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno如果分组后还要求按一定条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。例：查询选修了3门以上课程的学生的学号SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)0; /这里是先按Sno 进行分组，然后再对每一组进行统计。WHERE和HAVING区别：在于作用对象不同：WHE

22、RE子句作用于基本表或视图，从中选择满足条件的元组。HAVING作用于组，从中选择满足条件的组。作业： 课本 第149页 习题3、4、5 第2节关系数据库标准语言查询部分（二）前面的查询都是对单表的查询，本节主要是关于两个或两个以上表的查询一、连接查询包括：等值连接、自然廉洁、非等值连接查询、自身连接查询、外连接查询和复合条件查询。 （一）等值与非等值连接查询1基本概念连接条件（连接谓词）：格式：.比较运算符主要有：=、=、=、!=连接谓词还可使用下面形式：.BETWEEN.AND.等值连接：比较运算符为= . 使用其他运算符称为非等值连接。连接字段：连接谓词中的列名 。连接条件的各连接字段类

23、型必须是可比的，但不必相同。2.例：查询每个学生及其选修课程的情况SELECT Student.*,SC.* FROM Student, SC WHERE Student.Sno=SC.SnoStudent表 和SC表数据同上一节 查询结果上机现场测试自然连接：等值连接中把目标列中重复的属性列去掉例：把上例用自然连接完成SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno;在表中唯一的属性列前面可以省略表名前缀。（二）自身连接定义：连接操作不仅可在两表间进行，也可是

24、一个表与其自己进行连接，成为自身连接。例：查询每门课的间接先修课方法： 将表Course起两个名字， 一个是FIRST，另一个是SECOND。完成查询语句：SELECT FIRST.Cno , SECOND.Cpreo FROM Course FIRST ,Course SECOND WHERE FIRST.Cpreo=SECOND.Cno（三）外连接以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，指输出其基本情况信息，其选课信息谓空值即可。使用外连接SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM

25、Student , SC WHERE Student.Sno=SC.Sno(*); 表示方法：在连接谓词的某一边加符号*。好比为符号所在边的表加一个“万能行”，这个行由空值组成。右连接：外连接符号出现在连接条件的右边。左连接：外连接符号出现在连接条件的左边。 （四）复合条件连接 WHERE子句中有多个连接条件例：查询选修2号课程且成绩在90分以上的所有学生SELECT Student.Sno ,Sname FROM Student ,SCWHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90;例：查询每个学生的学号，姓名，选修的课程名及成绩。S

26、ELECT Student.Sno ,Sname,Cname,Grade FROM Student,SC,CourseWHERE Student.Sno=SC.Sno and SC.Cno=Course.Cno;二、嵌套查询一个SELEC-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。例：SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno=2)说明：1）允许多重查询，即一个子查询中还允许有其它子查询。2）自查询的SELECT

27、语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果进行排序。3）嵌套查询求解方法是由里向外处理。即子查询的结果作为父查询的查询条件。4）嵌套查询使得可以利用简单查询完成复杂查询，增强了SQL的查询能力。（一）带有IN谓词的子查询嵌套查询中，子查询的结果往往是一个集合，故IN常使用1例：查询与“刘晨”在同一个系学习的学生。思考： 第一步查询刘晨所在的系； 第二步查找所有在IS系学习的学生实现： SELECT Sdept SELECT Sno,Sname,Sdept FROM Student FROM Student WHERE Sname= 刘晨; WHERE Sdept

28、=IS;使用嵌套：SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname=刘晨)例：查询选修了课程名为“信息系统”的学生的学号和姓名SELECT Sno,Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname=信息系统)练习改用连接查询实现上面的查询：SELECT student.Sno,Sname FROM Student,S

29、C,Course WHERE Student.Sno =SC.Sno ANDSC.Cno=Course.Cno AND Course.Cname=信息系统（二）带有比较运算符的子查询 父查询与子查询之间用比较运算符进行连接的查询为带有比较运算符的子查询。例：将上面的使用IN的例子改为=，必须是子查询的结果是唯一的值SELECT Sno,Sname,Sdept FROM Student WHERE Sdept=(SELECT Sdept FROM Student WHERE Sname=刘晨) 子查询必须跟在比较运算符之后，如改为如下是错误的，但是在sqlserver中是正确的SELECT Sno,Sname,Sdept FROM Student WHERE (SELECT Sdept FROM Student WHERE Sname=刘晨) =Sdept （三）带有ANY和ALL谓词的子查询子查询返回单值时可以用比较运算符，使用ANY或ALL谓词时必须同时使用比较运算符语义如下：谓词功能ANY大于子