PPT《*》

* * 第三讲 关系数据库标准语言SQL SQL (Structured Query Language)3.

1 SQL概述3.2 学生课程数据库 3.3 数据定义3.4 数据查询3.5 数据更新3.6 视图3.8 小结 * * 3.1 SQL概述 SQL的特点1. 综合统一2. 高度非过程化3. 面向集合的操作方式4. 以同一种语法结构提供两种使用方法5. 语言简洁,易学易用 * * 5. 语言简捷,易学易用 * * SQL语言支持的关系数据库的三级模式结构 基本关系(Base Table)SQL语言支持数据库的三级模式结构 3.1.2 SQL语言的基本概念 存储文件 关系模式1 关系模式2 关系模式3 关系模式4 外模式1 外模式2 外模式3 模式 外模式 内模式 SQL 应用1-SQL 应用2-SQL 应用3-SQL * * 第三讲 关系数据库标准语言SQL 3.1 SQL概述3.2 学生课程数据库3.3 数据定义3.4 数据查询3.5 数据更新3.6 视图3.8 小结 * * 3.2 学生课程数据库-----学生 学号Sno 姓名Sname 性别Ssex 年龄Sage 所在系Sdept

200215121 李勇 男20 CS

200215122 刘晨 女19 IS

200215123 王敏 女18 MA

200215125 张立 男19 IS (a) Student * * 3.2 学生课程数据库-----课程 (b) Course 课程号 课程名 先行课 学分 Cno Cname Cpno Ccredit

1 数据库

5 4

2 数学 ?

2 3 信息系统

1 4

4 操作系统

6 3

5 数据结构

7 4

6 数据处理 ?

2 7 PASCAL语言

6 4 * * 3.2 学生课程数据库------选课 (c) SC 学号课程号成绩Sno Cno Grade

200215121 1

92 200215121

2 85

200215121 3

88 200215122

2 90

200215122 3

80 * * 第三讲 关系数据库标准语言SQL 3.1 SQL概述3.2 学生课程数据库 3.3 数据定义3.4 数据查询3.5 数据更新3.6 视图3.8 小结 * * 3.3 数据定义**3.3.1 基本表的定义、删除与修改

一、定义基本表CREATE TABLE 列名>

[ 列名>

[ 表级完整性约束条件>

] );

:所要定义的基本表的名字:组成该表的各个属性(列):涉及相应属性列的完整性约束条件:涉及一个或多个属性列的完整性约束条件 * * 定义基本关系(续) 常用完整性约束主码约束: PRIMARY KEY唯一性约束:UNIQUE非空值约束:NOT NULL默认值: DEFAULT 参照完整性约束PRIMARY KEY与UNIQUE的区别? * * 例题 [例5] 建立一个 学生 表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成.其中学号不能为空,值是唯一的,并且姓名取值也唯一. CREATE TABLE Student (Sno CHAR(12) Primary key,Sname VARCHAR2(20) UNIQUE,Ssex CHAR(3) DEFAULT ('

男'

Sage NUMBER(3)Sdept VARCHAR(20)) ;

* * 例题 [例6] 建立一个 课程 表Course. CREATE TABLE Course( Cno varchar2 (6) Primary KEY , Cname varchar2 (20), Cpno varchar2 (6), Ccredit number(2), FOREIGN KEY (Cpno) REFERENCES Course (Cno));

* * 例题 (续) [例7] 建立一个 学生选课 表SC,它由学号Sno、课程号Cno,修课成绩Grade组成,其中(Sno, Cno)为主码.CREATE TABLE SC( Sno CHAR(12)Cno varchar2(6)Grade number(3) DEFAULT (0)Primary key (Sno, Cno)FOREIGN KEY (Sno) REFERENCES Student (Sno)FOREIGN KEY (Cno) REFERENCES Course (Cno));

* *

二、数据类型 当用SQL语句定义表时,需要为表中的每一个字段设置一个数据类型,用来指定字段所存放的数据是数值、字符串、日期或是其它类型的数据.ORACLE 的数据类型有很多种,以下常用类型:char?字符型,最大长度2000B,缺省长度为1B?nchar?基于NLS国家字符集的字符型,最大长度2000B,缺省为1字符?varchar2?变长字符型,最大长度4000B?nvarchar2?基于NLS国家字符集的字符型,其余同carchar2?varchar?同varchar2? * * number(m,n)??数值型.m为总位数,n为小数位数.总长度最大为38位?date?日期型.有效表示范围公元前4712年1月1日到公元4712年12月31日?long?变长字符型,最大长度2GB,不支持对字符串内容进行搜索blob?二进制大对象类型,最大长度4GBclob?字符大对象类型,最大长度4GB? * *

三、修改基本表 ALTER TABLE [ ADD [ 完整性约束 ] ][ DROP ][ ALTER COLUMN ];

:要修改的基本表ADD子句:增加新列和新的完整性约束条件DROP子句:删除指定的完整性约束条件ALTER COLUMN子句:用于修改列名和数据类型 * * 例题 [例8] 向Student表增加 入学时间 列,其数据类型为日期型. ALTER TABLE Student ADD Sentrance DATE;

不论基本表中原来是否已有数据,新增加的列一律为空值. ALTER TABLE Student Drop (Sentrance) ? * * [例9] 将年龄的数据类型改为2位整数.ALTER TABLE Student modify Sage number(2)注:修改原有的列定义有可能会破坏已有数据 * * 例题 [例10] 增加课程名称必须取惟一值的约束条件 ALTER TABLE Course ADD UNIQUE(Cname) * *

五、删除基本表 DROP TABLE ?基本表删除时,数据、表上的索引都删除,表上的视图往往仍然保留,但无法引用 * * 例题 [例11] 删除Student表DROP TABLE Student;

* * 3.3.2 建立与删除索引 建立索引是加快查询速度的有效手段建立索引DBA或表的属主(即建立表的人)根据需要建立有些DBMS自动建立以下列上的索引 PRIMARY KEY UNIQUE维护索引 DBMS自动完成?使用索引 DBMS自动选择是否使用索引以及使用哪些索引 * *

一、建立索引 语句格式CREATE [UNIQUE] [CLUSTERED] INDEX ON ([][,[] ]…);

用指定要建索引的基本表名字索引可以建立在该表的一列或多列上,各列名之间用逗号分隔用指定索引值的排列次序,升序:ASC,降序:DESC.缺省值:ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTERED表示要建立的索引是聚簇索引 * * 例题 [例14] 为学生-课程数据库中的Student,Course二个表建立索引.其中Student表按姓名升序建索引,Course表按课程名降序建唯一索引. CREATE INDEX Stusname ON Student(Sname ASC)CREATE UNIQUE INDEX Coursename ON Course(Cname desc) * * 建立索引 (续) 唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后,插入新记录时DBMS会自动检查新记录在该列上是否取了重复值.这相当于增加了一个UNIQUE约束 * *

二、删除索引 DROP INDEX ;

删除索引时,系统会从数据字典中删去有关该索引的描述.[例15] 删除Student表的Stusname索引.DROP INDEX Stusname;

* * 3.4 查询3.4.1 单表查询3.4.2 连接查询3.4.3 嵌套查询3.4.4 集合查询3.4.5 SELECT语句的一般格式 * * 概述 语句格式SELECT [ALL|DISTINCT] 目标列表达式>

] …FROM [, ] …[ WHERE ][ GROUP BY [ HAVING ] ][ ORDER BY [ ASC|DESC ] ];

? * * 语句格式 SELECT子句:指定要显示的属性列或表达式FROM子句:指定查询对象(基本表或视图)WHERE子句:指定查询条件 GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组.通常会在每组中作用集函数.HAVING短语:筛选出只有满足指定条件的组ORDER BY子句:对查询结果表按指定列值的升序或降序排序 * * 3.4.1 单表查询 查询仅涉及一个表,是一种最简单的查询操作

一、选择表中的若干列

二、选择表中的若干元组

三、对查询结果排序

四、使用集函数

五、对查询结果分组 * *

一、选择表中的若干列 1.查询指定列2.查询全部列3. 查询经过计算的值 * * 1.查询指定列 [例1] 查询全体学生的学号与姓名.SELECT Sno,SnameFROM Student?[例2] 查询全体学生的姓名、学号、所在系.SELECT Sname,Sno,SdeptFROM Student * * 2.查询全部列 [例3] 查询全体学生的详细记录.SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student 或SELECT *FROM Student * * 3. 查询经过计算的值 SELECT子句的为表达式算术表达式字符串常量函数列别名等 * * 3. 查询经过计算的值 [例4] 查全体学生的姓名及其出生年份.SELECT Sname,2008-SageFROM Student?输出结果:Sname 2008-Sage 李勇

1988 刘晨

1989 王敏

1990 张立

1989 * * 3. 查询经过计算的值 [例5] 查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名.SELECT Sname, '

Year of Birth: '

, 2004-Sage,LOWER(Sdept)FROM Student * * 例题(续) 输出结果: Sname '

YearofBirth:'

2004-Sage ISLOWER(Sdept)李勇 Year of Birth:

1984 cs 刘晨 Year of Birth:

1985 is 王名 Year of Birth:

1986 ma 张立 Year of Birth:

1985 is * * [例5.1] 使用列别名改变查询结果的列标题 SELECT Sname NAME,'

Year of Birth: '

BIRTH,2000-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENTFROM Student;

输出结果: NAME BIRTH BIRTHDAY DEPARTMENT 李勇 Year of Birth:

1984 cs 刘晨 Year of Birth:

1985 is 王名 Year of Birth:

1986 ma 张立 Year of Birth:

1984 is * * 练习-参考

第二章习题5中的4个表 1.查询所有零件的名称、颜色和重量. 2.查询所有供应商名称、所在城市 3.查询所有工程名称和所在城市 * * SELECT Pname,Color,WeightFROM PSELECT Pname 名称,Color 颜色,Weight 重量FROM PSELECT Pname as 名称,Color as 颜色,Weight as 重量FROM P * *

二、选择表中的若干元组 1.消除取值重复的行2.查询满足条件的元组 * * 1. 消除取值重复的行 在SELECT子句中使用DISTINCT短语假设SC表中有下列数据 Sno Cno Grade

200215121 1

92 200215121

2 85

200215121 3

88 200215122

2 90

200215122 3

80 * * ALL 与DISTINCT [例6] 查询选修了课程的学生学号.(1) SELECT (ALL) Sno FROM SC?结果:Sno

200215121 200215121

200215121 200215122

200215122 * * 例题(续) (2) SELECT DISTINCT Sno FROM SC;

?结果:Sno

200215121 200215122 * * 例题(续) 注意 DISTINCT短语的作用范围是所有目标列例:查询选修课程的各种成绩错误的写法:SELECT DISTINCT Cno,DISTINCT GradeFROM SC;

正确的写法:SELECT DISTINCT Cno,Grade FROM SC;

? * * 2.查询满足条件的元组 WHERE子句常用的查询条件 * * (1) 比较大小 比较运算符包括: * * (1) 比较大小 [例7] 查询计算机科学系全体学生的名单 SELECT Sno,Sname,Ssex FROM Student WHERE Sdept = '

CS'

* * (1) 比较大小(续) [例8] 查询所有年龄在20岁以下的学生姓名及其年龄. SELECT Sname,Sage FROM Student WHERE Sage <

20 * * (1) 比较大小(续) [例9] 查询考试成绩有不及格的学生的学号SELECT Sno FROM SC WHERE Grade:用逗号分隔的一组取值[例12]查询信息系(IS)、数学系(MA)和计 算机科学系(CS)学生的姓名和性别.SELECT Sname,SsexFROM StudentWHERE Sdept IN ('

IS'

,'

MA'

,'

CS'

) * * (3) 确定集合(续) [例13]查询既不是信息系、数学系,也不是计算 机科学系的学生的姓名和性别.SELECT Sname,SsexFROM Student WHERE Sdept NOT IN ( '

IS'

,'

MA'

,'

CS'

);

* * (4) 字符串匹配 [NOT] LIKE '

'

[ESCAPE '

'

]:指定匹配模板 匹配模板:固定字符串或含通配符的字符串 当匹配模板为固定字符串时, 可以用 = 运算符取代 LIKE 谓词 用!= 或运算符取代 NOT LIKE 谓词 * * 通配符 % (百分号) 代表任意长度(长度可以为0)的字符串例:a%b表示以a开头,以b结尾的任意长度的字符串.如acb,addgb,ab 等都满足该匹配串_ (下横线) 代表任意单个字符例:a_b表示以a开头,以b结尾的长度为3的任意字符串.如acb,afb等都满足该匹配串 * * ESCAPE 短语: 当用户要查询的字符串本身就含有 % 或_时,要使用ESCAPE '

'

短语对通配符进行转义. * * 例题 1) 匹配模板为固定字符串 [例14] 查询学号为200215121的学生的详细情况. SELECT FROM Student WHERE Sno LIKE '

200215121'

;

等价于: SELECT * FROM Student WHERE Sno = '

200215121'

;

* * 例题(续) 2) 匹配模板为含通配符的字符串[例15] 查询所有姓刘学生的姓名、学号和性别. SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '

刘%'

* * 例题(续) 匹配模板为含通配符的字符串(续)[例16] 查询姓 刘 且全名为二个汉字的学生的姓名. SELECT Sname FROM Student WHERE Sname LIKE '

刘_'

* * 例题(续) 匹配模板为含通配符的字符串(续)[例17] 查询名字中第2个字为 阳 字的学生的姓名和学号. SELECT Sname,Sno FROM Student WHERE Sname LIKE '

_阳%'

* * 例题(续) 匹配模板为含通配符的字符串(续)[例18] 查询所有不姓刘的学生姓名. SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE '

刘%'

* * 例题(续) 3) 使用换码字符将通配符转义为普通字符 [例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 '

'

* * (5) 涉及空值的查询 使用谓词 IS NULL 或IS NOT NULL IS NULL 不能用 = NULL 代替[例21] 某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩.查询缺少成绩的学生的学号和相应的课程号. SELECT Sno,Cno FROM SC WHERE Grade IS NULL * ................