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的删除功能格式deletefrom 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_SALvalues(T08,张立,1500);,insert into Tvalues(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,