SQL的数据定义语句.ppt

第七章 SQL的数据定义与操纵,7.1 SQL的数据定义功能 7.2 SQL的数据定义语句 7.3 SQL Server2000的数据定义语句 7.4 SQL的数据操纵功能 7.5 SQL的数据操纵语句 7.6 SQL中的视图语句 7.7 SQL Server2000中的数据操纵及视图功能 本章小结 习题,7.1 SQL的数据定义功能,主要为应用系统定义数据库上的整体结构模式，包括三个层次： 上层-模式层 关系数据库管理系统可以定义多个模式 每个模式对应一个应用系统 模式由若干个表、视图及索引组成,7.1 SQL的数据定义功能,中层-表结构层 基表 创建表 修改表 删除表 视图 视图是建立在基表上的虚表 创建视图 删除视图 索引 创建索引 删除索引,7.1 SQL的数据定义功能,底层-列定义层 表中属性的定义 如列名的定义 列数据类型的定义 列的完整性约束定义 如列是否是主键或外键 列值是否允许为空 列间的约束表达式,7.2 SQL的数据定义语句,1 SQL的基本数据类型,7.2 SQL的数据定义语句,2 SQL的模式定义语句 模式定义 定义语句： create schema authorization 例如，学生数据库的模式可定义如下： create schema student authorization lin;,7.2 SQL的数据定义语句,模式删除 语句为 Drop schema , 删除方式有两种： Cascade,表示级联方式 Restrict,表示限制方式 删除学生数据库模式： Drop schema student cascade;,7.2 SQL的数据定义语句,3 SQL的表定义语句 表的定义 语句 Create table ()其它参数 其中列定义形式如下： ,7.2 SQL的数据定义语句,例：前面所创建的student数据库中有s,c,sc三张表，试定义它们。 create table s (sno char(4), sname varchar(8), sage smallint, sex char(2), sdept char(2);,7.2 SQL的数据定义语句,create table c (cno char(2), cname varchar(20), credit numeric); create table sc (sno char(4), cno char(2), grade smallint);,7.2 SQL的数据定义语句,表的更改 增加列 Alter table add 例，在s中增加一个新的列sex Alter table s add sex char(2); 删除列 Alter table drop 例，在s中将sdept删除 Alter table s drop sdept;,7.2 SQL的数据定义语句,表的删除 语句 drop table 例，删除s表 drop table s; 删除表后，表中的数据、在此表上建立的视图、索引都将删除,7.2 SQL的数据定义语句,4 SQL的索引定义语句 建立索引 语句 create uniquecluster index on (顺序.)其它参数 Unique:唯一性索引，不允许两个元组在给定索引中有相同的值。 Cluster:聚簇索引，索引项的顺序与表中记录的物理顺序一致。 顺序：ASC, DESC,2 SQL的数据定义语句,例：在表s的sno上建立一个按升序排列的唯一性索引xsno。 create unique index xsno on s (sno) asc; 删除索引 语句 drop index 删除名为xsno的索引 drop index xsno;,7.3 SQL Server2000中的数据定义语句,建立数据库 Create database student1; 删除数据库 Drop database student1;,7.4 SQL的数据操纵功能,数据查询功能 select 增、删、改功能 Insert, delete, update 其它功能 赋值 分类 运算,7.5 SQL的数据操纵语句,关系代数中的查询语句 求选修了c1号课程的学生姓名 sname(cno=c1(S SC),7.5 SQL的数据操纵语句,1 SQL的查询语句 Select 列名 From 表名 Where 选择条件,投影,选择,连接,7.5 SQL的数据操纵语句,示例数据： S(sno,sname,sage,sex,sdept) C(cno,cname,credit) SC(sno,cno,grade),7.5 SQL的数据操纵语句,SQL的基本查询语句 单表简单查询 例1：查询所有学生的情况 select * from s; *代表所有的列 例2：查询所有学生的姓名 select sname from s;,7.5 SQL的数据操纵语句,例3：查询学号为s01的学生学号与姓名 Select sno,sname From s Where sno=s01; Where后面可以使用比较符，包括=、=、!=,7.5 SQL的数据操纵语句,例4：查询年龄大于20的学生的学号和姓名 Select sno,sname From s Where sage20;,7.5 SQL的数据操纵语句,常用谓词 between, like, null用于where子句中 Distinct ,as 用于select子句中 例5：查询所有选修了课程的学生学号 Select distinct sno From sc; Select后的distinct表示在结果中去掉重复的sno。,7.5 SQL的数据操纵语句,例：查询每个学生的出生年份 select sno,2008-sage as birthyear from s; Select之后可以是简单运算 as 用于select 之后表示为某一属性取别名,7.5 SQL的数据操纵语句,例6：查询年龄 在18-21岁的学生的姓名和年龄 Select sname,sage From s; where sage between 18 and 21;,（不）,(not),7.5 SQL的数据操纵语句,例7：查询姓“李”的学生的姓名及所在系 Select sname,sdept From s Where sname like 李%； Like一般情况下与通配符% ,_一起使用 %表示任意长度的字符串，例a%b _表示任意单个字符，例a_b,7.5 SQL的数据操纵语句,例8：查询名字的倒数第二字为“丽”的学生的姓名及所在系 Select sname,sdept From s Where sname like %丽_；,7.5 SQL的数据操纵语句,例9：查询无课程分数的学生的学号和课程号 select sno,cno from sc where grade is null; Null是测试属性值是否为空的谓词，其一般形式是： is not null,7.5 SQL的数据操纵语句,布尔表达式 not, and ,or 例10：查询计算机系年龄小于20的学生姓名 Select sname From s Where sdept=cs and sage20; 例11：查询非计算机系或年龄不为20的学生姓名 Select sname From s Where not sdept=cs or not sage=20;,7.5 SQL的数据操纵语句,单连接 表间等值连接 在where子句中设置两表相同属性间的相等关系 例12：查询修读课程号为c1的所有学生的姓名 select sname from s,sc where s.sno=sc.sno and o=c1;,7.5 SQL的数据操纵语句,例13：查询修读课程名为”数据库”的所有学生姓名 select sname from s,sc,c where s.sno=sc.sno and o=o and cname=数据库;,7.5 SQL的数据操纵语句,自连接 例14：查询至少选修了s5所修读的一门课的学生学号 select first.sno from sc first,sc second where o=o and second.sno=s5;,7.5 SQL的数据操纵语句,练习： 求每一门课程的间接先行课 select o, second.preno from c as first, c as second where first.preno=o;,7.5 SQL的数据操纵语句,结果排序 order by ASC/DESC 例15：查询计算机系所有学生名单并按学号顺序升序显示。 select sno,sname from s where sdept=cs order by sno asc;,7.5 SQL的数据操纵语句,例16：查询全体学生情况，结果按学生年龄降序排列 select * from s order by sage desc;,7.5 SQL的数据操纵语句,分层结构查询与集合谓词的使用 谓词in的使用 表示元素与集合之间的属于关系 例17：查询计算机系、数学系及物理系学生的姓名 select sname from s where sdept in (ma,cs,py);,7.5 SQL的数据操纵语句,例18：查询修读课程号为c1的所有学生姓名 select sname from s where sno in (select sno from sc where cno=c1);,7.5 SQL的数据操纵语句,例19：查询修读课程名为java的学生姓名 select sname from s where sno in (select sno from sc where cno in (select cno from c where cname=java);,7.5 SQL的数据操纵语句,限定比较谓词的使用 例20：查询成绩大于所有选修课程号c1的学生成绩的学生学号 select sno from sc where grade all (select grade from sc where cno=c1);,7.5 SQL的数据操纵语句,查询间的运算语句 例21：查询计算机系的学生以及年龄小于20的学生 (select * from s where sdept=cs) union (select * from s where sage20 );,7.5 SQL的数据操纵语句,2 SQL的更新语句 SQL的删除功能 SQL的插入功能 SQL的修改功能,7.5 SQL的数据操纵语句,SQL的删除功能 格式 delete from where 例22：删除学生“王林”的信息 delete from s where sname=王林;,7.5 SQL的数据操纵语句,例23：删除计算机系学生的选课记录 delete from sc where cs= (select sdept from s where s.sno=sc.sno); 注： 删除操作可能会破坏参照完整性,7.5 SQL的数据操纵语句,SQL的插入功能 格式： 插入常量 insert into , values (,) 注： （1）只有当插入的元组中包含所有属性的值并且值的顺序 与属性的顺序一致时，表名后的属性列表可以省略 （2）当只插入部分属性的值时，没有插入值的属性默认取 空值。,7.5 SQL的数据操纵语句,例24：插入一条选课记录（s1,c1,85） insert into sc values (s1,c1,85); 例25: 插入一条选课记录（s2,c2） insert into sc (sno,cno) values (s2,c2);,7.5 SQL的数据操纵语句,插入子查询结果 insert into , ,7.5 SQL的数据操纵语句,例26：将sc中成绩合格的记录插入到sci中。 insert into sci select * from sc where grade60;,7.5 SQL的数据操纵语句,SQL的修改功能 格式 update set =表达式，=表达式 where 指定对哪些列进行更新，以及更新后的值是什么,7.5 SQL的数据操纵语句,例27：将学号为s16的学生的系别改成cs。 update s set sdept=cs where sno=s16; 例28：将数学系学生的年龄增加1岁。 update s set sage=sage+1 where sdept=ma;,7.5 SQL的数据操纵语句,例29：将计算机系的学生成绩全部置零 update sc set grade=0 where cs= (select sdept from s where s.sno=sc.sno); 注：更新操作可能会破坏参照完整性。,7.5 SQL的数据操纵语句,3 SQL的统计、计算及分类语句 统计功能 count: 计数 sum：求某一列的总和（该列为数值型） avg: 求某一列的平均值（该列为数值型） max: 求某一列的最大值（该列类型可比） min: 求某一列的最小值（该列类型可比）,7.5 SQL的数据操纵语句,例30：给出全体学生数 select count(*) from s; count(列名)与count(*)的区别: count(列名) 对一列中的值计算个数 count(*) 计算元组的个数,7.5 SQL的数据操纵语句,例31：求选修课程的学生人数 select count ( distinct sno ) from sc; 例32：求学生选课记录数 select count(*) from sc;,7.5 SQL的数据操纵语句,例33：给出学生s7所修课程的平均成绩 select avg(grade) from sc where sno=s7;,7.5 SQL的数据操纵语句,计算功能 例34：给出计算机系学生的出生日期 select sno,sname,2008-sage from s where sdept=cs;,7.5 SQL的数据操纵语句,分组功能 group by:对元组进行分组 having ：设置逻辑条件，选择满足条件的分组 例35：给出每个学生的平均成绩 select sno,avg(grade) from sc group by sno; 练习：给出每门课程的最高成绩,7.5 SQL的数据操纵语句,例36：给出修读人数超过5人的课程号以及修读该课程的学生人数。 select cno,count(sno) from sc group by cno having count(*)5;,7.6 SQL中的视图语句,视图定义 格式 create view ( ,) as with check option 视图的属性名缺省为子查询结果中的属性名，也可以显式指明 with check option指明当对视图进行insert，update时，要检查是否满足视图定义中的条件,7.6 SQL中的视图语句,例37：定义一个计算机系学生的视图 create view css(sno,sname,sage,sex,sdept) as (select * from s where sdept=cs) 删除视图 drop view css;,7.6 SQL中的视图语句,视图操作 例38：用已定义的视图，查询计算机系年龄大于20的学生。 select * from css where sage20; 对视图的查询，在实际操作中转换成对基表的查询 select * from s where sdept=cs and sage20;,7.6 SQL中的视图语句,例39：对于表T(tno,tname,age,zc,sal),创建教师工资的视图. create view T_SAL as (select tno, tname,sal from T ) ; 对视图的更新操作将转换为对基表的更新操作,7.6 SQL中的视图语句,insert into T_SAL values ( T08 , 张立 , 1500 ) ;,insert into T values ( T08 , 张立 , null , null , 1500 ) ;,转换为,7.6 SQL中的视图语句,视图更新约束 select子句中的目标列不能包含聚集函数 select子句中不能使用unique或distinct关键字 不能包括group by子句 不能包括经算术表达式计算出来的列 对于行列子集视图可以更新（视图是从单个基本表使用选择、投影操作导出的，并且包含了基本表的主码）,7.7 SQL Server2000中的数据操纵及视图功能,Sql Server 2000中的数据操作功能与SQL92符合度较高，两者基本可以兼容 创建视图 删除视图 修改视图,本章小结,基本功能 数据定义 模式定义层 表结构定义层 列定义层 数据操纵 查询 增、删、改 统计、分类、赋值,习题,设有供应商S、零件P、工程项目J三个关系模式以及反映它们之间联系（某工程使用了某供应商提供的某零件多少个）的关系模式SPJ。 S (SNO, SNAME, STATUS, CITY) P (PNO, PNAME, COLOR, WEIGHT) J (JNO, JNAME, CITY) SPJ(SNO, PNO, JNO, QTY),习题,求向工程J1提供零件的供应商的编号。 求向工程J1提供零件P1的供应商的编号。 求向工程J1提供红色零件的供应商的编号。 求没有使用天津供应商提供的红色零件的工程号。 统计各个供应商供应的零件总数。 求至少使用了3种以上零件的工程号。 把所有红色零件的颜色改为粉红色。 将向工程J1提供零件P1的供应商S1改为S2提供。 删除红色零件的供应-使用记录。,自连接,SECOND,FIRST,自连接,SECOND,FIRST,IN查询,first:在sc表中查询课程号 为C1的学生学号,second:在s表中查询其学 号在第一步结果表中的元 组，取其sname属性。,IN查询,s,sc,c,比较谓词,sc,删除操作,分组操作,列出每个学生的平均成绩,列出每门课程的平均成绩,group by sno,group by cno,92,85,90,92,92,90,