1 / 103

3.1 SQL 概述 3.2 数据定义 3.3 查询 3.4* 数据更新 3.5 * 视图

3.1 SQL 概述 3.2 数据定义 3.3 查询 3.4* 数据更新 3.5 * 视图. 第 3 章 关系数据库标准语言 SQL. 3.1   SQL 概述. SQL （ S tructured Q uery L anguage) 是关系数据库的 标准语言 SQL-86, SQL-89 American National Standards Institute ( ANSI ), International Standard Organization ( ISO )

trory
Download Presentation

3.1 SQL 概述 3.2 数据定义 3.3 查询 3.4* 数据更新 3.5 * 视图

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 3.1 SQL概述 3.2数据定义 3.3 查询 3.4* 数据更新 3.5 * 视图 第 3 章关系数据库标准语言SQL

  2. 3.1   SQL概述 • SQL（Structured Query Language)是关系数据库的 标准语言 • SQL-86, SQL-89 American National Standards Institute (ANSI), International Standard Organization (ISO) • SQL2（SQL-92) • SQL3 ( 1999) : 支持“对象(object)” • SQL标准的广泛流行和巨大影响

  3. SQL的特点： • 综合统一 综合DDL、DML、DCL功能于一体 • 高度非过程化 • 面向集合的操作方式 • 以同一种语法结构提供两种使用方式 自含式(交互式) vs. 嵌入式 • 语言简洁，易学易用

  4. SQL语言的基本概念 支持关系数据库三级模式结构

  5. SQL 语言的各个部分 • 数据定义语言（Data-definition language ，DDL) • 交互式数据操纵语言（Interactive data-manipulation language ，DML) • 视图定义 • 事务控制（Transaction control） • 嵌入式SQL和动态SQL • 完整性(Integrity) • 授权(Authorization )

  6. 3.2 数据定义

  7. 数据定义语言 (DDL) • 关系的模式 • 和各属性关联的域 • 完整性约束 • 为每个关系维护一个索引集合 • 关系的安全性和授权信息 • 关系在磁盘上的物理存储结构 不但允许对一个关系集合进行规格说明，对其中的每个关系也要说明以下有关信息：

  8. SQL的主要数据类型 • char(n)固定长度字符串, n为用户指定的长度. • varchar(n)变长字符串, n为用户指定的最大长度 • int 全字长二进制整数 • smallint 半字长二进制整数 • numeric(p,d)定点数，p为用户指定精度，d为小数点 后位数 • real, double precision浮点数和双精度数 • float(n)用户指定的至少有n位精度的浮点数

  9. SQL的日期/时间类型 • date日期型 如: date ‘2001-7-27’ • time时间型 如: time ’09:00:30’ time ’09:00:30.75’ • Timestamp(时间戳):日期加时间 如 timestamp ‘2001-7-27 09:00:30.75’ • 可从date/time/timestamp中抽取出单个域的值 • 可把串类型强制转换(cast)到date/time/timestamp 类型 如 cast <string-valued-expression> as date

  10. 基本表的定义 • 使用CREATE TABLE命令建立一个基本表: CREATE TABLE <表名> (<列名> <数据类型> [列级完整性约束条件] [ ,<列名> <数据类型> [列级完整性约束条件]…] [, <表级完整性约束条件>]); • 定义被存入“数据字典” • 完整性约束条件由DBMS自动维护

  11. 例：学生表的建立 CREATE TABLE Student ( Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20) ， Ssex CHAR(1)， Sage INT， Sdept CHAR(15))；

  12. 建表的完整性约束 • not null • primary key (A1, ..., An) • check (P),P是一个谓词 • Unique(A1, ..., An) 说明： • primary key 自动保证 not null 和unique （SQL-92 之后的标准） • 若违反了完整性约束条件，更新操作将被拒绝 • Unique 要求属性值不重复，但允许有多个空值(null)

  13. 完整性约束的一个例子 例:要求branch-name为关系 branch的主码 ，资产（ assets） 值非负。 create table branch ( branch-name char(15), branch-city char(30), assets integer,primary key(branch-name)，check(assets >= 0));

  14. 一个银行数据库的SQL数据定义示例

  15. 删除和修改基本表 • DROP TABLE :从数据库中删除指定关系的所有信息DROP TABLE r • ALTER TABLE : 向已存在的关系中增加属性列 ALTER TABLE r ADDAD A是要向关系r中加入的属性，D是 A的取值域. • 关系中所有元组的新属性列的取值都为空值(null ) • ALTER TABLE 命令也可用来删除关系中的属性列ALTER TABLE r DROPA A是关系r中一属性 • 但许多数据库不支持这种功能

  16. 一般格式： • ALTER TABLE <表名>： • [ADD <新列名> <数据类型> [完整性约束] • [DROP <完整性约束名>] • [MODIFY <列名> <数据类型> ] • 例： • 将年龄的数据类型改为半字长整数。 • ALTER TABLE Student MODIFY Sage SMALLINT； • 删除学生姓名必须取唯一值的约束。 • ALTER TABLE Student DROP UNIQUE(Sname);

  17. 建立与删除索引 • 建立索引是加快查询速度的有效手段，可在基本表上建立一个或多个索引。 • 建立索引 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名> [<次序>][, <列名> [<次序>]]…); 说明： • 次序：ASC和DESC • UNIQUE: 索引的每一个索引值只对应唯一的数据记录 • CLUSTER:建立聚簇索引(索引项的顺序与表中记录的物理顺序一致)。

  18. 删除索引 DROP INDEX <索引名> 例： CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Course(Cno); CREATE UNIQUE INDEX Scno ON SC(Sno ASC,Cno DESC); DROP INDEX Stusno;

  19. 3.3   查 询 • 查询是数据库的核心操作 • 一般格式 SELECT [ ALL|DISTINCT ] < 目标列表达式> [ ,<目标列表达式>]… FROM <表名或视图名>[ ,<表名或视图名>]… [WHERE <条件表达式> ] [GROUP BY <列名1> [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC]];

  20. 学生-课程数据库 Student （Sno，Sname，Ssex，Sage，Sdept）

  21. Course (Cno, Cname, Cpno, Ccredit)

  22. SC (Sno, Cno, Grade)

  23. SQL查询 • 单表查询 • 连接查询 • 嵌套查询 • 集合查询

  24. 单表查询 1.选择表中的若干列 2.选择表中的若干元组 3.对查询结果排序 4.使用集函数 5.对查询结果分组

  25. 选择表中的若干列 例1 查询全体学生的学号与姓名 SELECT Sno，Sname FROM Student； 例2 查询全体学生的姓名、学号、所在系 SELECT Sname，Sno， Sdept FROM Student；

  26. 例3 查询全体学生的详细记录 SELECT * FROM Student; 例4 查全体学生的姓名及其出生年份 SELECT Sname, 2005-Sage FROM Student;

  27. 例5 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名 SELECT Sname, ‘Year of Birth:’, 2005-Sage , ISLOWER(Sdept) FROM Student; SELECT Sname NAME, ‘Year of Birth:’ BIRTH, 2005-Sage BIRTHDAY, ISLOWER(Sdept) DEPARTMENT FROM Student;

  28. 消除结果表中的重复行 例6 查询所有选修过课的学生的学号（学号要求不重复）。 SELECT DISTINCT Sno FROM SC;

  29. 单表查询 1.选择表中的若干列 2.选择表中的若干元组 (WHERE子句） 3.对查询结果排序 4.使用集函数 5.对查询结果分组

  30. 通过WHERE子句查询 • 常用的查询条件和谓词 • 比较 ( = ， > ，< ， .. ） • 确定范围 （BETWEEN AND,…) • 确定集合 (IN,NOT IN) • 字符匹配 (LIKE ,NOT LIKE) • 空值 (IS NULL,IS NOT NULL) • 多重条件 (AND,OR)

  31. 例7 查询计算机系全体学生的名单。 SELECT Sname FROM Student WHERE Sdept=‘CS’;

  32. 例8 查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT Sname，Sage FROM Student WHERE Sage < 20； 例9 查询考试成绩有不及格的学生的学号。 SELECT DISTINCT Sno FROM SC WHERE Grade < 60；

  33. 比较 ( = ， > ，< ， .. ） • 确定范围 （BETWEEN AND,…) • 确定集合 (IN,NOT IN) • 字符匹配 (LIKE ,NOT LIKE) • 空值 (IS NULL,IS NOT NULL) • 多重条件 (AND,OR)

  34. 例10 查询年龄在20至23岁之间的学生的姓名、系别和年龄。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23； 例11 查询年龄不在20至23岁之间的学生的姓名、系别和年龄。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOTBETWEEN 20 AND 23；

  35. 比较 ( = ， > ，< ， .. ） • 确定范围 （BETWEEN AND,…) • 确定集合 ( IN , NOT IN) • 字符匹配 (LIKE ,NOT LIKE) • 空值 (IS NULL,IS NOT NULL) • 多重条件 (AND,OR)

  36. 例12 查信息系(IS)、数学系(MA)和计算机系(CS)的学生的姓名和性别。 SELECT Sname，Ssex FROM Student WHERE Sdept IN (‘IS’,’MA’,’CS’); 例13 查既不是信息系、数学系，也不是计算机系的学生的姓名和性别。 SELECT Sname，Ssex FROM Student WHERE Sdept NOT IN (‘IS’,’MA’,’CS’);

  37. 比较 ( = ， > ，< ， .. ） • 确定范围 （BETWEEN AND,…) • 确定集合 (IN,NOT IN) • 字符匹配 (LIKE ,NOT LIKE) • 空值 (IS NULL,IS NOT NULL) • 多重条件 (AND,OR)

  38. 例14 查询学号为95001的学生的详细情况. SELECT * FROM Student WHERE Sno LIKE ‘95001’; 例15 查询所有姓刘的学生的姓名、学号和性别. 通配符，代表任意长度的字符串 SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE ‘刘%’;

  39. 例16 查姓“欧阳”且全名为3个汉字的学生姓名。 通配符，代表任意单个字符 SELECT Sname FROM Student WHERE Sname LIKE ‘欧阳_ _’; 例17 查询名字中第2个字为“阳”字的学生的姓名和学号。 SELECT Sname，Sno FROM Student WHERE Sname LIKE ‘_ _ 阳％’;

  40. 例19 查询DB_Design课程的课程号和学分。 换码字符 SELECT Cno,Ccredit FROM Course WHERE Cname LIKE ‘DB\_ Design’ ESCAPE ‘\’; 例20 查以“DB_” 开头，且倒数第3个字符为i的课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE ‘DB\_ %i_ _’ESCAPE ‘\’;

  41. 比较 ( = ， > ，< ， .. ） • 确定范围 （BETWEEN AND,…) • 确定集合 (IN,NOT IN) • 字符匹配 (LIKE ,NOT LIKE) • 空值 (IS NULL, IS NOT NULL) • 多重条件 (AND,OR)

  42. 例21查询缺少成绩的学生的学号和相应的课程号。例21查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno,Cno FROM SC WHERE Grade IS NULL;

  43. 比较 ( = ， > ，< ， .. ） • 确定范围 （BETWEEN AND,…) • 确定集合 (IN,NOT IN) • 字符匹配 (LIKE ,NOT LIKE) • 空值 (IS NULL,IS NOT NULL) • 多重条件 (AND,OR)

  44. 例23 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept =‘CS’ AND Sage < 20：

  45. 单表查询 1.选择表中的若干列 2.选择表中的若干元组 3.对查询结果排序 (ORDER BY子句） 4.使用集函数 5.对查询结果分组

  46. 例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。 SELECT Sno,Grade FROM SC WHERE Sno =‘3’ ORDER BY Grade DESC ;

  47. 例25 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept, Sage DESC

  48. 单表查询 1.选择表中的若干列 2.选择表中的若干元组 3.对查询结果排序 4.使用集函数 ( COUNT, SUM,AVG,MAX,MIN,…) 5.对查询结果分组

  49. 例26 查询学生总人数。 SELECT COUNT (*) FROM Student; 例27 查询选修了课程的学生人数。 SELECTCOUNT(DISTINCT Sno) FROM SC；

  50. 例28 计算1号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno＝’1’： 例29 查询选修1号课程的学生最高分数。 SELECT MAX(Grade) FROM SC WHERE Cno＝’1’；

More Related