(课件)-sql概述数据定义查询更新视图数据控制嵌入式sql

1/9/2018,数据库原理,1,SQL概述 数据定义 查询 更新 视图 数据控制 嵌入式SQL,1/9/2018,数据库原理,2, SQL的发展及现状,1974年，由Boyce和Chamberlin提出。 19751979，IBM公司San Jose Research Lab研制了 关系数据库管理系统原型System R并实现了这种语言。 1986年美国国家标准局(ANSI)的数据库委员会X3H2批准 SQL作为关系数据库语言的美国标准（SQL-86） 。 ANSI 不断修改和完善SQL标准，公布了SQL-89、 SQL-92(SQL2)、SQL-99(SQL3) 大部分DBMS产品都支持SQL，成为操作数据库的标准语言,1/9/2018,数据库原理,3,3.1 SQL概述,3.1.1 SQL的特点,综合统一 集DDL、DML、DCL的功能于一体，可以独立完成数据库生命周期中的全部活动。 高度非过程化 无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。 面向集合的操作方式 操作对象、查找结果、插入、删除、更新操作的对象可以是元组集合。 以同一种语法结构提供两种使用方式 可独立的用于联机交互的使用方式，用户可在终端键入SQL命令对数据库进行操作；SQL语句可嵌入到高级语言程序中，供程序员设计程序时使用。 语言简单，易学易用 核心功能只有9个动词。,1/9/2018,数据库原理,4,数据查询(Data Query),SQL语言,数据操纵(Data Manipulation),数据定义(Data Definition),数据控制(Data Control),1/9/2018,数据库原理,5,SQL,用 户,基本表1,视图1,视图2,基本表2,基本表3,基本表4,存储文件1,存储文件2,外模式,模 式,内模式,图3.1 SQL对关系数据库模型的支持,3.1.2 SQL语言的基本概念,1/9/2018,数据库原理,6,基本概念： 用户可以用SQL语言对视图(View)和基本表(Base Table)进行查询等操作，在用户观点里，视图和表一样，都是关系。 视图是从一个或多个基本表中导出的表，本身不存储在数据库中，只有其定义，可以将其理解为一个虚表。 基本表是本身独立存在的表，每个基本表对应一个存储文件，一个表可以带若干索引，存储文件及索引组成了关系数据库的内模式。,1/9/2018,数据库原理,7,3.2 数据定义,1/9/2018,数据库原理,8,3.2.1 定义、删除与修改基本表,定义基本表,格式：CREATE TABLE ( 列级完整性约束条件, 列级完整性约束条件 ,)；,建立一个新表，表中无记录,1/9/2018,数据库原理,9,CREATE TABLE Student ( Sno CHAR(5) NOT NULL UNIQUE, /* 列级完整性 约束条件 Sname CHAR(20) UNIQUE, Ssex CHAR(1), Sage INT, Sdept CHAR(15)；,例1 ：建立学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性构成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。,1/9/2018,数据库原理,10,CREATE TABLE S ( S# CHAR(6) NOT NULL, /* 学号 SNAME CHAR(8) NOT NULL, /* 姓名 SAGE SMALLINT, /* 年龄 SD CHAR(10), /* 系名 SEX CHAR(2) DEFAULT 男 /* 性别 CHECK(SEX=男) OR (SEX=女), PRIMARY KEY (S#)；,例2 ：建立学生S、课程C、选课SC三个表,S表：,1/9/2018,数据库原理,11,CREATE TABLE C (C# CHAR(6) NOT NULL, /* 课程号 CNAME CHAR(30) NOT NULL, /* 课程名 TNAME CHAR(8), /* 教师姓名 PC# CHAR(6), /* 先行课 PRIMARY KEY (C#)；,例2 ：建立学生S、课程C、选课SC三个表,C表：,1/9/2018,数据库原理,12,CREATE TABLE SC (S# CHAR(6) NOT NULL, /* 学号C# CHAR(6) NOT NULL, /* 课程号 GR SMALLINT DEFAULT NULL, /* 成绩PRIMARY KEY (S#,C#),FOREIGN KEY (S#) REFERENCES S(S#),FOREIGN KEY (C#) REFERENCES C(C#),CHECK (GR IS NULL)OR (GR BETWEEN O AND 100) ；,例2 ：建立学生S、课程C、选课SC三个表,SC表：,1/9/2018,数据库原理,13,例3 ：设关系S_G(S#,AVG_G),把平均成绩大于80的男生的学号及平均成绩存入S_G中。,CREATE TABLE S_G (S# CHAR(6) NOT NULL, AVG_G SMALLINT DEFAULT NULL)AS (SELECT S#， AVG(ALL GRADE) FROM SC WHERE S# IN (SELECT S# FROM S WHERE SEX=男) GROUP BY S# HAVING AVG(ALL GRADE)80),1/9/2018,数据库原理,14,修改基本表,改变表名 增加列 改变列的数据类型 删除列的约束 删除列 改变列名,基本表的修改操作：,1/9/2018,数据库原理,15,格式：ALTER TABLE ADD 完整性约束 DROP MODIFY RENAME TO ;,增加新列和新的完整性约束条件 删除指定的完整性约束条件 修改列的定义（列名和数据类型） 改变表名,1/9/2018,数据库原理,16,SQL没有提供删除属性列的语句，可通过先将要保留的列及其内容复制到一个新表中，然后删除原表，再将新表重新命名为原表名。,1/9/2018,数据库原理,17,例4：在Student表中增加“入学时间”列，数据类型为日期型。 ALETR TABLE Student ADD Scome DATE；,例5：把年龄的数据类型改为半字长整数. ALETR TABLE Student MODIFY Sage SMALLINT；,例6：删除学生姓名必须取唯一值的约束。 ALETR TABLE Student DROP UNIQUE(Sname)；,1/9/2018,数据库原理,18,删除基本表,格式： DROP TABLE ；,例7： 删除Student表。 DROP TABLE Student;,1/9/2018,数据库原理,19,3.2.2 建立与删除索引,建立索引是加快查询速度的有效手段，一个基本表上可建立一个或多个索引，以提供多种存取路径，加快查找速度。 建立与删除索引由DBA或表的属主负责完成，用户不必也不能选择索引。,1/9/2018,数据库原理,20,建立索引,格式：CREATE UNIQUE CLUSTER INDEX ON (, )；,UNIQUE 表示索引值唯一。 CLUSTER 表示索引是聚簇索引,指索引项的顺序与表中 记录的物理顺序一致。 索引一旦建立，交由系统使用和维护。,1/9/2018,数据库原理,21,例8： 为学生课程数据库中的S, C, SC三个表建立索引。其中S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。,CREATE UNIQUE INDEX SIDX ON S(S#);CREATE UNIQUE INDEX CIDX ON C(C#);CREATE UNIQUE INDEX SCIDX ON S(S# ASC, C# DESC);,1/9/2018,数据库原理,22,例9：为学生Student表建立聚簇索引（按学生姓名升序建索引）。,CREATE CLUSTER INDEX SNAMEIDX ON Student(Sname);,1/9/2018,数据库原理,23,删除索引,格式： DROP INDEX ；,例10： 删除Student表的SNAMEIDX索引。 DROP INDEX SNAMEIDX ；,1/9/2018,数据库原理,24,3.3 查 询,基本结构Select A1, A2, ., AnFrom R1, R2, ., RmWhere P,Select,Where,From,A1, A2, ., An(p(R1R2.Rm),数据库原理,25,格式 SELECT ALL|DISTINCT , FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC；,1/9/2018,数据库原理,26,SELECTFROM常用语句执行过程 SELECT 投影 FROM TABLE内存 WHERE 选取元组 GROUP 分组 HAVING 选择分组 UNION| 查徇结果的集合运算 SELECT ORDER BY 排序输出,1/9/2018,数据库原理,27,3.3.1 单表查询,选择表中的若干列 选择表中的若干元组 对查询结果排序 使用集函数 对查询结果分组,1/9/2018,数据库原理,28,1.查询指定列,例1. 查询全体学生的学号及姓名。,学生表：Student(Sno, Sname, Ssex, Sage, Sdept),查询各列的先后顺序可以与表中的顺序不一致。,例2. 查询全体学生的姓名、学号、所在系。,SELECT Sno, SnameFROM Student；,SELECT Sname, Sno, Sdept FROM Student；,1/9/2018,数据库原理,29,1/9/2018,数据库原理,30,2.查询全部列,学生表：Student(Sno, Sname, Ssex, Sage, Sdept),例3. 查询全体学生的全部信息。,SELECT Sno, Sname, Ssex, Sage, Sdept FROM Student； 等价于SELECT * FROM Student;,1/9/2018,数据库原理,31,1/9/2018,数据库原理,32,3.查询经过计算的值,学生表：Student(Sno, Sname, Ssex, Sage, Sdept),例4. 查询全体学生的姓名及其出生年份。,SELECT Sname, 2006- Sage FROM Student；,1/9/2018,数据库原理,33,例5. 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。,SELECT Sname, Year of Birth:, 2006- Sage, LOWER(Sdept)FROM Student；,SELECT Sname NAME, Year of Birth: BIRTH, 2006- Sage BIRTHDAY, ISLOWER(Sdept) DEPARTMENTFROM Student；,1/9/2018,数据库原理,34,1/9/2018,数据库原理,35,1.消除取值重复的行,例6. 查询选修了课程的学生学号。,学生课程表：SC(Sno, Cno, Grade),消除重复的行,SELECT SnoFROM SC；,SELECT DISTINCT Sno FROM SC；,1/9/2018,数据库原理,36,1/9/2018,数据库原理,37,2. 查询满足条件的元组,Where 子句运算符,比较：、=、=、!=、!、! not+上述比较运算符确定范围：Between A and B、 Not Between A and B确定集合：IN、NOT IN字符匹配：LIKE、NOT LIKE空值： IS NULL、IS NOT NULL多重条件：AND、OR,1/9/2018,数据库原理,38,例7. 查询计算机系全体学生的名单。,(1) 比较大小,SELECT SnameFROM Student WHERE Sdept = CS；,1/9/2018,数据库原理,39,1/9/2018,数据库原理,40,例8. 查询所有年龄在20岁以下的学生姓名及其年龄。,SELECT Sname, Sage FROM Student WHERE Sage = 20；,或,1/9/2018,数据库原理,41,1/9/2018,数据库原理,42,例9. 查询考试成绩有不及格的学生的学号。,SELECT DISTINCT Sno FROM SC WHERE Grade = 20 AND Sage = 23；,1/9/2018,数据库原理,45,1/9/2018,数据库原理,46,SELECT Sname, Sdept, SageFROM Student WHERE Sage NOT BETWEEN 20 AND 23；,例11. 查询年龄不在2023岁之间的学生姓名、系别 和年龄。,SELECT Sname, Sdept, SageFROM Student WHERE Sage 23；,1/9/2018,数据库原理,47,1/9/2018,数据库原理,48,例12. 查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。,(3) 确定集合,SELECT Sname, SsexFROM Student WHERE Sdept IN (IS, MA, CS)；,SELECT Sname, SsexFROM Student WHERE Sdept = IS OR Sdept = MA OR Sdept = CS；,1/9/2018,数据库原理,49,1/9/2018,数据库原理,50,例13.查询既不是信息系(IS)、数学系(MA)也不是计算机 科学系(CS)学生的姓名和性别。,SELECT Sname, SsexFROM Student WHERE Sdept NOT IN (IS, MA, CS)；,SELECT Sname, SsexFROM Student WHERE Sdept != IS AND Sdept != MA AND Sdept != CS；,1/9/2018,数据库原理,51,1/9/2018,数据库原理,52,(4) 字符匹配,Where 子句Like,格式: NOT LIKE ESCAPE % : 表示任意长度(长度0）的字符串。_ : 表示单个的任意字符ESCAPE : 表示为换码字符，匹配串中紧跟在后面的字符%或_，被定义为普通字符(不作通配符用),1/9/2018,数据库原理,53,例14. 查询学号为95001的学生的详细情况。,如果LIKE后面的匹配串中不含通配符，则可以用运算符取代LIKE谓词；用!=或取代NOT LIKE谓词。,SELECT *FROM Student WHERE Sno LIKE 95001；,SELECT *FROM Student WHERE Sno = 95001；,等价于,1/9/2018,数据库原理,54,1/9/2018,数据库原理,55,例15. 查询所有姓王的学生的姓名、学号和性别。,SELECT Sname, Sno, SsexFROM Student WHERE Sname LIKE 王；,1/9/2018,数据库原理,56,1/9/2018,数据库原理,57,例16. 查询姓李，且姓名为两个汉字的学生姓名。,一个汉字占两个字符的位置,SELECT SnameFROM Student WHERE Sname LIKE 李_ ；,1/9/2018,数据库原理,58,1/9/2018,数据库原理,59,例17. 查询名字中第二个字为“晨”字的学生姓名 和学号。,SELECT Sname, SnoFROM Student WHERE Sname LIKE _晨% ；,1/9/2018,数据库原理,60,1/9/2018,数据库原理,61,例18. 查询所有不姓刘的学生姓名。,SELECT SnameFROM Student WHERE Sname NOT LIKE 刘% ；,1/9/2018,数据库原理,62,1/9/2018,数据库原理,63,例19. 查询DB_Design课程的课程号和学分。,SELECT Cno, CcreditFROM Course WHERE Cname LIKE DB_Design ESCAPE ；,1/9/2018,数据库原理,64,例20. 查询以“DB_”开头，且倒数第三个字符为i的课程 的详细情况。,SELECT *FROM Course WHERE Cname LIKE DB_%i_ ESCAPE；,1/9/2018,数据库原理,65,(5) 涉及空值的查询,例21. 某些学生选修课后没有参加考试，所以有选课录， 但没有成绩。查询缺少成绩的学生的学号和相应 课程号。,SELECT Sno, CnoFROM SC WHERE Grade IS NULL ；,1/9/2018,数据库原理,66,1/9/2018,数据库原理,67,例22. 查询所有有成绩的学生学号和课程号。,SELECT Sno, CnoFROM SC WHERE Grade IS NOT NULL ；,1/9/2018,数据库原理,68,1/9/2018,数据库原理,69,(6) 多重条件查询,例23. 查询计算机系年龄在20岁以下的学生姓名。,SELECT SnameFROM Student WHERE Sdept = CS AND Sage = 3 ;,HAVING用于分组后按一定条件对这些分组进行筛选。 WHERE子句作用于基本表或视图，从中选择满足条件的元组 HAVING短句作用于组，从中选择满足条件的组,1/9/2018,数据库原理,83,1/9/2018,数据库原理,84,3.3.2 连接查询,等值与非等值连接查询 自身连接 外连接 复合条件连接,1/9/2018,数据库原理,85,1. 等值与非等值连接查询,连接条件一：. .连接条件二：. BETWEEN . AND .,比较运算符主要有：、=、= 90 ;,1/9/2018,数据库原理,94,例37. 查询每个学生的学号、姓名、选修课程名及成绩。,多表连接,SELECT Student.Sno, Sname, Cname, GradeFROM Student, SC, CourseWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;,1/9/2018,数据库原理,95,1/9/2018,数据库原理,96,3.3.3 嵌套查询,在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。例如：,SELECT SnameFROM StudentWHERE Sno IN SELECT Sno FROM SC WHERE Cno = 2 ;,外层查询,内层查询,不能使用ORDER BY子句,1/9/2018,数据库原理,97,带有IN谓词的子查询 带有比较运算符的子查询 带有ANY或ALL谓词的子查询 带有EXISTS谓词的子查询,3.3.3 嵌套查询,1/9/2018,数据库原理,98,例38. 查询与“刘晨”在同一个系学习的学生。SELECT Sno, Sname, SdeptFROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘晨);,(1)首先在Student关系中找出刘晨所在的系，结果为IS 。,(2)其次在Student关系中找出IS系的学生,1.带有IN谓词的子查询,或 SELECT S1.Sno, S1.Sname, S1.Sdept FROM Student S1, Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 刘晨；,1/9/2018,数据库原理,99,1/9/2018,数据库原理,100,例39. 查询选修了课程名为“信息系统”的学生学号和姓名。SELECT Sno, SnameFROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = 信息系统);,(1)首先在Course关系中找出“信息系统的课程号”，结果为3。,(2)其次在SC关系中找出选修3号课程的学生,(3)最后在Student关系中取出学号和姓名。,或 SELECT Sno, Sname FROM Student, SC, Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname = 信息系统 ；,1/9/2018,数据库原理,101,1/9/2018,数据库原理,102,2.带有比较运算符的子查询,例40. 查询与“刘晨”在同一个系学习的学生。,内存查询返回的是单值时，可以用比较运算符；子查询要跟在比较符之后。,SELECT Sno, Sname, SdeptFROM StudentWHERE Sdept (SELECT Sdept FROM Student WHERE Sname = 刘晨);,1/9/2018,数据库原理,103,1/9/2018,数据库原理,104,3.带有ANY或ALL谓词的子查询,子查询返回单值时可以用比较运算符，而使用ANY或ALL谓词时必须同时使用比较运算符。其语义为：(=、=)ANY 大于（大于等于、等于）子查询结果中的某个值(=、=)ALL 大于（大于等于、等于）子查询结果中的所有值)ANY 不等于子查询结果中的某个值!=(或)ALL 不等于子查询结果中的任何一个值,1/9/2018,数据库原理,105,例41. 查询其他系中比信息系某一学生年龄小的学生姓名和年龄。SELECT Sname, SageFROM Student WHERE Sage IS ;,或 SELECT Sname, SageFROM Student WHERE Sage IS ;,1/9/2018,数据库原理,106,1/9/2018,数据库原理,107,例42. 查询其他系中比信息系所有学生年龄小的学生姓名和年龄。SELECT Sname, SageFROM Student WHERE Sage IS;,或 SELECT Sname, Sage FROM Student WHERE Sage IS;,1/9/2018,数据库原理,108,表3-4 ANY, ALL谓词与集函数及IN谓词的等价转换关系,1/9/2018,数据库原理,109,4.带有EXISTS谓词的子查询,不相关子查询： 子查询的查询条件不依赖于父查询的子查询。 相关子查询(Correlated Subquery)： 子查询的查询条件依赖于外层父查询的某个属性值的子查询。 带EXISTS 的子查询就是相关子查询 EXISTS表示存在量词 带有EXISTS的子查询不返回任何记录的数据，只返回逻辑值 True 或 False,1/9/2018,数据库原理,110,例43-1. 查询所有选修了1号课程的学生姓名。 SELECT Sname FROM Student WHERE Sno IN (SELECT SNO FROM SC WHERE SC.Cno= 1);,不相关子查询,1/9/2018,数据库原理,111,例43-2. 查询所有选修了1号课程的学生姓名。SELECT SnameFROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1);,执行过程：,先在外层查询中取Student表的第一个元组，用该元组的相关的属性值（在内层WHERE子句中给定的）处理内层查询，若外层的WHERE子句返回TRUE值，则此元组送入结果的表中。然后再取下一个元组；重复上述过程直到外层表的元组全部遍历一次为止。,相关子查询,1/9/2018,数据库原理,112,1/9/2018,数据库原理,113,说明：,不关心子查询的具体内容，因此用 SELECT * Exists + 子查询用来判断该子查询是否返回元组 当子查询的结果集非空时，Exists 为 True 当子查询的结果集为空时，Exists 为 False NOT EXISTS ：若子查询结果为空，返回TRUE值， 否则返回 FALSE,1/9/2018,数据库原理,114,例44. 查询没有选修1号课程的学生姓名。SELECT SnameFROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1); 或 SELECT Sname FROM Student WHERE Sno NOT IN (SELECT SNO FROM SC WHERE SC.Cno= 1);,不相关子查询,相关子查询,1/9/2018,数据库原理,115,1/9/2018,数据库原理,116,例45. 查询与刘晨在同一个系学习的学生。SELECT Sno, Sname, SdeptFROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname= 刘晨); 或 SELECT Sno, Sname, SdeptFROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘晨);,1/9/2018,数据库原理,117,1/9/2018,数据库原理,118,例46. 查询选修了全部课程的学生姓名。SELECT SnameFROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno);,这门课他没选,这样的课是不存在的,1/9/2018,数据库原理,119,1/9/2018,数据库原理,120,例47. 查询至少选修了学生95002选修的全部课程的学生号。,SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno= 95002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；,1/9/2018,数据库原理,121,3.3.4 集合查询,例48. 查询计算机系的学生或者年龄不大于19岁的学生。,SELECT *FROM StudentWHERE Sdept = CSUNIONSELECT *FROM StudentWHERE Sage = 19,1/9/2018,数据库原理,122,1/9/2018,数据库原理,123,例49. 查询选修了课程1或选修课程2的学生。,SELECT SnoFROM SCWHERE Cno = 1UNIONSELECT SnoFROM SCWHERE Cno = 2;,1/9/2018,数据库原理,124,1/9/2018,数据库原理,125,例50. 查询计算机系的学生与年龄不大于19岁的学生的交集。,SELECT *FROM StudentWHERE Sdept = CS AND Sage 19;,例53. 查询选修课程1但没有选修课程2的学生。,SELECT SnoFROM SCWHERE Cno= 1 AND Sno NOT IN (SELECT Sno FROM SC WHERE Cno= 2);,1/9/2018,数据库原理,127,3.3.5 SELECT语句的一般格式,SELECT ALL|DISTINCT别名,别名 FROM 别名,别名 WHERE GROUP BYHAVING ORDER BYASC|DESC;,1/9/2018,数据库原理,128,3.4 数据更新,插入操作 INSERT 修改操作 UPDATE 删除操作 DELETE,1/9/2018,数据库原理,129,3.4.1 插入数据,1.插入单个元组,格式：INSERT INTO (,) VALUES (,)；,插入一已知元组的全部列的常量 插入一已知元组的部分列的常量 表定义时说明了NOT NULL的属性列不能取空值 如果INTO子句没有指明任何列名，则新插入的记录必须在 每个属性列上均有值。,1/9/2018,数据库原理,130,插入一已知元组的全部列常量,INSERT INTO Student VALUES(95005, 陈冬, 男,18, CS)；,例1：将一个新生记录插入学生表。,INSERT INTO SC(Sno，Cno) VALUES (95005, 1)；,插入一已知元组的部分列常量,例2：新增一条选课记录,1/9/2018,数据库原理,131,1/9/2018,数据库原理,132,2.插入子查询结果,格式：INSERT INTO (,) 子查询；,CREATE TABLE Deptage (Sdept CHAR(15) Avgage SMALLINT);INSERT INTO Deptage(Sdept, Avgage) SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept;,例3：对每个系，求学生的平均年龄，并把结果存入数据