关系数据库标准语言SQL

1、2021-6-131 第三讲 关系数据库标准语言sql sql (structured query language) 3.1 sql概述 3.2 学生课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图 3.8 小结 2021-6-132 3.1 sql概述 psql的特点 n1. 综合统一 n2. 高度非过程化 n3. 面向集合的操作方式 n4. 以同一种语法结构提供两种使用方法 n5. 语言简洁，易学易用 2021-6-133 5. 语言简捷，易学易用 2021-6-134 sql语言支持的关系数据库的三级模式结构 基本关系（基本关系（base table） s

2、ql语言支持数据库的三级模式结构语言支持数据库的三级模式结构 3.1.2 sql语言的基本概念 存储文件 关系模式1关系模式2关系模式3关系模式4 外模式1外模式2外模式3 模式 外模式 内模式 sql 应用1-sql应用2-sql应用3-sql 2021-6-135 第三讲 关系数据库标准语言sql p3.1 sql概述 p3.2 学生课程数据库 p3.3 数据定义 p3.4 数据查询 p3.5 数据更新 p3.6 视图 p3.8 小结 2021-6-136 3.2 学生课程数据库-学生 学学 号号 sno 姓姓 名名 sname 性性 别别 ssex 年年 龄龄 sage 所所 在在 系系

3、 sdept 2002151 21 李勇李勇男男20cs 2002151 22 刘晨刘晨女女19is 2002151 23 王敏王敏女女18ma 2002151 25 张立张立男男19is (a) student 2021-6-137 3.2 学生课程数据库-课程 (b) course 课程号课程号课程名课程名先行课先行课学分学分 cnocnamecpnoccredit 1数据库数据库54 2数学数学 2 3信息系统信息系统14 4操作系统操作系统63 5数据结构数据结构74 6数据处理数据处理 2 7pascal语言语言64 2021-6-138 3.2 学生课程数据库-选课 (c) sc

4、学学 号号课课 程程 号号成成 绩绩 snocnograde 200215121192 200215121285 200215121388 200215122290 200215122380 2021-6-139 第三讲 关系数据库标准语言sql p3.1 sql概述 p3.2 学生课程数据库 p3.3 数据定义 p3.4 数据查询 p3.5 数据更新 p3.6 视图 p3.8 小结 2021-6-1310 3.3 数 据 定 义 2021-6-1311 3.3.1 基本表的定义、删除与修改 一、定义基本表一、定义基本表 create table （ ， ， ）； n：所要定义的基本表的名字

5、n：组成该表的各个属性（列） n：涉及相应属性列的完整性约束 条件 n：涉及一个或多个属性列的完整 性约束条件 2021-6-1312 定义基本关系（续） p常用完整性约束 n主码约束： primary key n唯一性约束：unique n非空值约束：not null n默认值： default n参照完整性约束 primary key与 unique的区别？ 2021-6-1313 例题 例5 建立一个“学生”表student，它由学号 sno、姓名sname、性别ssex、年龄sage、 所在系sdept五个属性组成。其中学号不能为 空，值是唯一的，并且姓名取值也唯一。 create t

6、able student (sno char(12) primary key, sname varchar2(20) unique, ssex char(3) default (男), sage number(3), sdept varchar(20) ; 2021-6-1314 例题 例6 建立一个“课程”表course。 create table course( cno varchar2 (6) primary key , cname varchar2 (20), cpno varchar2 (6), ccredit number(2）, foreign key (cpno) refere

7、nces course (cno); 2021-6-1315 例题 （续） p例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) ); 2021-6-

8、1316 二、数据类型二、数据类型 p当用sql语句定义表时，需要为表中的每一个字 段设置一个数据类型，用来指定字段所存放的数 据是数值、字符串、日期或是其它类型的数据。 poracle 的数据类型有很多种，以下常用类型： nchar 字符型，最大长度2000b，缺省长度为1b nnchar 基于nls国家字符集的字符型，最大长度 2000b，缺省为1字符 nvarchar2 变长字符型，最大长度4000b nnvarchar2 基于nls国家字符集的字符型，其余同 carchar2 varchar 同varchar2 2021-6-1317 pnumber(m,n) 数值型。m为总位数，n为

9、小数位 数。总长度最大为38位 pdate 日期型。有效表示范围公元前4712年1月1日 到公元4712年12月31日 plong 变长字符型，最大长度2gb，不支持对字符串 内容进行搜索 pblob 二进制大对象类型，最大长度4gb pclob 字符大对象类型，最大长度4gb 2021-6-1318 三、修改基本表三、修改基本表 alter table add 完整性约束 drop alter column ； n：要修改的基本表 nadd子句：增加新列和新的完整性约束条件 ndrop子句：删除指定的完整性约束条件 nalter column子句：用于修改列名和数据类型 2021-6-131

10、9 例题 例8 向student表增加“入学时间”列，其数据类型为日 期型。 alter table student add sentrance date; n不论基本表中原来是否已有数据，新增加的列一律为空值。 alter table student drop （sentrance） 2021-6-1320 例9 将年龄的数据类型改为2位整数。 alter table student modify sage number(2) n注：修改原有的列定义有可能会破坏已有数据 2021-6-1321 例题 例10 增加课程名称必须取惟一值的约束条件 alter table course add u

11、nique(cname) 2021-6-1322 五、删除基本表五、删除基本表 drop table 基本表删除时，数据、表上的索引都删除，表上的视 图往往仍然保留，但无法引用 2021-6-1323 例题 例11 删除student表 drop tablestudent; 2021-6-1324 3.3.2 建立与删除索引 p建立索引是加快查询速度的有效手段 p建立索引 ndba或表的属主（即建立表的人）根据需要建立 n有些dbms自动建立以下列上的索引 p primary key p unique p维护索引 n dbms自动完成 p使用索引 n dbms自动选择是否使用索引以及使用哪些索

12、引 2021-6-1325 一、建立索引 p语句格式 create unique clustered index on (, )； n用指定要建索引的基本表名字 n索引可以建立在该表的一列或多列上，各列名之间用逗号分 隔 n用指定索引值的排列次序，升序：asc，降序： desc。缺省值：asc nunique表明此索引的每一个索引值只对应唯一的数据记录 nclustered表示要建立的索引是聚簇索引 2021-6-1326 例题 例14 为学生-课程数据库中的student，course二个表建立 索引。其中student表按姓名升序建索引，course表按课程名 降序建唯一索引。 creat

13、e index stusname on student(sname asc) create unique index coursename on course(cname desc) 2021-6-1327 建立索引 （续） p唯一值索引 n对于已含重复值的属性列不能建unique索引 n对某个列建立unique索引后，插入新记录时 dbms会自动检查新记录在该列上是否取了重复值。 这相当于增加了一个unique约束 2021-6-1328 二、删除索引 drop index ； n删除索引时，系统会从数据字典中删去有关该索引 的描述。 例15 删除student表的stusname索引。 d

14、rop index stusname； 2021-6-1329 3.4 查 询 3.4.1 单表查询 3.4.2 连接查询 3.4.3 嵌套查询 3.4.4 集合查询 3.4.5 select语句的一般格式 2021-6-1330 概述 p语句格式 select all|distinct ， from ， where group by having order by asc|desc ； 2021-6-1331 语句格式 nselect子句：指定要显示的属性列或表达式 nfrom子句：指定查询对象(基本表或视图) nwhere子句：指定查询条件 n group by子句：对查询结果按指定列的值

15、分组，该 属性列值相等的元组为一个组。通常会在每组中作用 集函数。 nhaving短语：筛选出只有满足指定条件的组 norder by子句：对查询结果表按指定列值的升序或 降序排序 2021-6-1332 3.4.1 单表查询 查询仅涉及一个表，是一种最简单的查询操作 一、选择表中的若干列 二、选择表中的若干元组 三、对查询结果排序 四、使用集函数 五、对查询结果分组 2021-6-1333 一、选择表中的若干列 p1.查询指定列 p2.查询全部列 p3. 查询经过计算的值 2021-6-1334 1.查询指定列 例1 查询全体学生的学号与姓名。 select sno,sname from s

16、tudent 例2 查询全体学生的姓名、学号、所在系。 select sname,sno,sdept from student 2021-6-1335 2.查询全部列 例3 查询全体学生的详细记录。 select sno,sname,ssex,sage,sdept from student 或 select * from student 2021-6-1336 3. 查询经过计算的值 select子句的为表达式 n算术表达式 n字符串常量 n函数 n列别名 n等 2021-6-1337 3. 查询经过计算的值 例4 查全体学生的姓名及其出生年份。 select sname,2008-sage

17、from student 输出结果： sname 2008-sage - - 李勇 1988 刘晨 1989 王敏 1990 张立 1989 2021-6-1338 3. 查询经过计算的值 例5 查询全体学生的姓名、出生年份和所有系， 要求用小写字母表示所有系名。 select sname, year of birth: , 2004-sage, lower(sdept) from student 2021-6-1339 例题（续） 输出结果： sname yearofbirth: 2004-sage islower(sdept) - - - - 李勇 year of birth: 1984

18、cs 刘晨 year of birth: 1985 is 王名 year of birth: 1986 ma 张立 year of birth: 1985 is 2021-6-1340 例5.1 使用列别名改变查询结果的列标题 select sname name,year of birth: birth, 2000-sage birthday, lower(sdept) department from student； 输出结果： name birth birthday department - - - - 李勇 year of birth: 1984 cs 刘晨 year of birth:

19、 1985 is 王名 year of birth: 1986 ma 张立 year of birth: 1984 is 2021-6-1341 练习-参考第二章习题5中的4个表 1.查询所有零件的名称、颜色和重量。 2.查询所有供应商名称、所在城市 3.查询所有工程名称和所在城市 2021-6-1342 select pname,color,weight from p select pname 名称,color 颜色,weight 重量 from p select pname as 名称,color as 颜色,weight as 重量 from p 2021-6-1343 二、选择表中的若

20、干元组 p1.消除取值重复的行 p2.查询满足条件的元组 2021-6-1344 1. 消除取值重复的行 n在select子句中使用distinct短语 假设sc表中有下列数据 sno cno grade - - - 200215121 1 92 200215121 2 85 200215121 3 88 200215122 2 90 200215122 3 80 2021-6-1345 all 与 distinct 例6 查询选修了课程的学生学号。 (1) select (all) sno from sc 结果： sno - 200215121 200215121 200215121 200

21、215122 200215122 2021-6-1346 例题（续） (2) select distinct sno from sc; 结果： sno - 200215121 200215122 2021-6-1347 例题（续） p注意 distinct短语的作用范围是所有目标列 例：查询选修课程的各种成绩 错误的写法: select distinct cno,distinct grade from sc; 正确的写法: select distinct cno,grade from sc; 2021-6-1348 2.查询满足条件的元组 表表3.3 常常用用的的查查询询条条 件件 查查 询询

22、 条条 件件谓谓 词词 比比 较较 =，=，=，!=，!，!； not + 上上述述比比较较运运算算符符 确确定定范范围围between and，not betw een and 确确定定集集合合in，not in 字字符符匹匹配配like，not like 空空 值值is null，is not null 多多重重条条件件and，or where子句常用的查询条件子句常用的查询条件 2021-6-1349 (1) 比较大小 p比较运算符包括： =,=,=,!=,!,! 2021-6-1350 (1) 比较大小 例7 查询计算机科学系全体学生的名单 select sno,sname,ssex

23、from student where sdept = cs 2021-6-1351 (1) 比较大小（续） 例8 查询所有年龄在20岁以下的学生姓名及其年龄。 select sname,sage from student where sage 20 2021-6-1352 (1) 比较大小（续） 例9 查询考试成绩有不及格的学生的学号 select sno from sc where grade60 2021-6-1353 (2) 确定范围 p使用谓词 between and not between and 例10 查询年龄在2023岁（包括20岁和23岁）之间 的学生的姓名、系别和年龄。 s

24、elect sname,sdept,sage from student where sage between 20 and 23 2021-6-1354 例题（续） 例11 查询年龄不在2023岁之间的学生姓名、系别 和年龄。 select sname,sdept,sage from student where sage not between 20 and 23 2021-6-1355 (3) 确定集合 使用谓词 in , not in ：用逗号分隔的一组取值 例12查询信息系（is）、数学系（ma）和计 算机科学系（cs）学生的姓名和性别。 select sname,ssex from s

25、tudent where sdept in (is,ma,cs) 2021-6-1356 (3) 确定集合(续) 例13查询既不是信息系、数学系，也不是计算 机科学系的学生的姓名和性别。 select sname,ssex from student where sdept not in ( is,ma,cs ); 2021-6-1357 (4) 字符串匹配 p not like escape ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符串时， 可以用 = 运算符取代 like 谓词 用 != 或 运算符取代 not like 谓词 2021-6-1358 通配符

26、 w% (百分号) 代表任意长度（长度可以为0）的字符串 n例：a%b表示以a开头，以b结尾的任意长度的字符串。 如acb，addgb，ab 等都满足该匹配串 _ (下横线) 代表任意单个字符 n例：a_b表示以a开头，以b结尾的长度为3的任意字 符串。如acb，afb等都满足该匹配串 2021-6-1359 escape 短语： n当用户要查询的字符串本身就含有 % 或 _ 时，要使用escape 短语对 通配符进行转义。 2021-6-1360 例题 1) 匹配模板为固定字符串 例14 查询学号为200215121的学生的详细情况。 select * from student where

27、sno like 200215121； 等价于： select * from student where sno = 200215121； 2021-6-1361 例题（续） 2) 匹配模板为含通配符的字符串 例15 查询所有姓刘学生的姓名、学号和性别。 select sname,sno,ssex from student where sname like 刘% 2021-6-1362 例题（续） 匹配模板为含通配符的字符串（续） 例16 查询姓“刘”且全名为二个汉字的学生的姓名。 select sname from student where sname like 刘_ 2021-6-136

28、3 例题（续） 匹配模板为含通配符的字符串（续） 例17 查询名字中第2个字为阳字的学生的姓名和学号。 select sname,sno from student where sname like _阳% 2021-6-1364 例题（续） 匹配模板为含通配符的字符串（续） 例18 查询所有不姓刘的学生姓名。 select sname,sno,ssex from student where sname not like 刘% 2021-6-1365 例题（续） 3) 使用换码字符将通配符转义为普通字符 例19 查询db_design课程的课程号和学分。 select cno,ccredit f

29、rom course where cname like db_design escape 2021-6-1366 例题（续） 使用换码字符将通配符转义为普通字符(续) 例20 查询以db_开头，且倒数第3个字符为 i 的课程的详细情况。 select * from course where cname like db_%i_ _ escape 2021-6-1367 (5) 涉及空值的查询 n 使用谓词 is null 或 is not null n “is null” 不能用 “= null” 代替 例21 某些学生选修课程后没有参加考试，所以有选课记 录，但没有考试成绩。查询缺少成绩的学生

30、的学号和相应 的课程号。 select sno,cno from sc where grade is null 2021-6-1368 例题(续) 例22 查所有有成绩的学生学号和课程号。 select sno,cno from sc where grade is not null 2021-6-1369 (6) 多重条件查询 用逻辑运算符and和 or来联结多个查询条件 p and的优先级高于or p 可以用括号改变优先级 可用来实现多种其他谓词 p not in p not between and 2021-6-1370 例题 例23 查询计算机系年龄在20岁以下的学生姓名。 select

31、 sname from student where sdept= cs and sage=20 and sage=23 2021-6-1373 练习-参考第二章习题5中的4个表 p查询使用s1供应商所供应零件的工程号 p查询使用s1或者s2供应商所供应零件的工程号 p名称中包含“螺”字的零件编号和颜色 2021-6-1374 select distinct jno from spj where sno=s1 select distinct jno from spj where sno=s1 or sno=s2 select pno,color from p where pname like %

32、螺% 2021-6-1375 三、对查询结果排序 使用order by子句 p 可以按一个或多个属性列排序 p 升序：asc；降序：desc；缺省值为升序 当排序列含空值时 pasc：排序列为空值的元组最后显示 pdesc：排序列为空值的元组最先显示 2021-6-1376 对查询结果排序（续） 例24 查询选修了3号课程的学生的学号及其成绩， 查询结果按分数降序排列。 select sno,grade from sc where cno= 3 order by grade desc 2021-6-1377 对查询结果排序（续） 例25 查询全体学生情况，查询结果按所在系的 系号升序排列，同一

33、系中的学生按年龄降序排列。 select * from student order by sdept,sage desc 2021-6-1378 四、使用集函数 5类主要集函数 n计数 count（distinct|all *）统计元组的个数 count（distinct|all ）统计某列值的个数 n计算总和 sum（distinct|all ）求某列值的总和 n 计算平均值 avg（distinct|all ）求某列值的均值 2021-6-1379 使用集函数（续） 求最大值 nmax（distinct|all ）求某列值的最大值 求最小值 nmin（distinct|all ）求某列值的

34、最小值 distinct短语：在计算时要取消指定列中的重复值 all短语：不取消重复值 all为缺省值 2021-6-1380 使用集函数 （续） 例26 查询学生总人数。 select count(*) from student 例27 查询选修了课程的学生人数。 select count(distinct sno) from sc 注：用distinct以避免重复计算学生人数 2021-6-1381 使用集函数 （续） 例28 计算1号课程的学生平均成绩。 select avg(grade) from sc where cno= 1； 例29 查询选修1号课程的学生最高分数。 select max(grade) from sc where cno= 1 ； 2021-6-1382 使用集函数 （续） 例30 查询学生200215012选修课程的总分 select sum(grade) from sc where sno= 200215012 2021-6-1383 使用集函数 （续） 在集函数遇到空值时，除count(*)外， 都跳过空值