《SQL的数据定义语句》由会员分享，可在线阅读，更多相关《SQL的数据定义语句（70页珍藏版）》请在金锄头文库上搜索。
1、第七章 SQL的数据定义与操纵7.1 SQL7.1 SQL的数据定义功能的数据定义功能7.2 SQL7.2 SQL的数据定义语句的数据定义语句7.3 SQL Server20007.3 SQL Server2000的数据定义语句的数据定义语句7.4 SQL7.4 SQL的数据操纵功能的数据操纵功能7.5 SQL7.5 SQL的数据操纵语句的数据操纵语句7.6 SQL7.6 SQL中的视图语句中的视图语句7.7 SQL Server20007.7 SQL Server2000中的数据操纵及视图功能中的数据操纵及视图功能v 本章小结本章小结v 习题习题7.1 SQL的数据定义功能v主要为应用系统定
2、义数据库上的整体结构主要为应用系统定义数据库上的整体结构模式，包括三个层次：模式，包括三个层次：上层上层-模式层模式层关系数据库管理系统可以定义多个模式关系数据库管理系统可以定义多个模式每个模式对应一个应用系统每个模式对应一个应用系统模式由若干个表、视图及索引组成模式由若干个表、视图及索引组成7.1 SQL的数据定义功能中层中层-表结构层表结构层基表基表创建表创建表修改表修改表删除表删除表视图视图视图是建立在基表上的虚表视图是建立在基表上的虚表创建视图创建视图删除视图删除视图索引索引创建索引创建索引删除索引删除索引7.1 SQL的数据定义功能底层底层-列定义层列定义层表中属性的定义表中属性的定
3、义如列名的定义如列名的定义列数据类型的定义列数据类型的定义列的完整性约束定义列的完整性约束定义如列是否是主键或外键如列是否是主键或外键列值是否允许为空列值是否允许为空列间的约束表达式列间的约束表达式7.2 SQL的数据定义语句1 SQL的基本数据类型的基本数据类型7.2 SQL的数据定义语句2 SQL的模式定义语句的模式定义语句模式定义模式定义定义语句：定义语句： create schema authorization例如，学生数据库的模式可定义如下：例如，学生数据库的模式可定义如下： create schema student authorization lin;7.2 SQL的数据定义语句
4、模式删除模式删除语句为语句为 Drop schema ,删除方式有两种：删除方式有两种：Cascade,表示级联方式：删除与模式关联的模式表示级联方式：删除与模式关联的模式元素元素Restrict,表示限制方式：模式中无任何关联的元素表示限制方式：模式中无任何关联的元素删除学生数据库模式：删除学生数据库模式： Drop schema student cascade;7.2 SQL的数据定义语句3 SQL的表定义语句的表定义语句表的定义表的定义语句语句 Create table ()其它其它参数参数其中列定义形式如下：其中列定义形式如下： 7.2 SQL的数据定义语句例：前面所创建的例：前面所创
5、建的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 nomeric); create table sc (sno char(4), cno char(2), grade smallint);7.2 SQL的数据定义语句表的更改表的更改增加列增
6、加列 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的索引定义语句的索引定义语句建立索引建
7、立索引语句语句create uniquecluster index on (顺序顺序.)其它参数其它参数Unique:唯一性索引，不允许两个元组在给定索引中唯一性索引，不允许两个元组在给定索引中有相同的值。有相同的值。Cluster:聚簇索引，索引项的顺序与表中记录的物理聚簇索引，索引项的顺序与表中记录的物理顺序一致。顺序一致。顺序：顺序：ASC, DESC2 SQL的数据定义语句例：在表例：在表s的的sno上建立一个按升序排列的唯一性索引上建立一个按升序排列的唯一性索引xsno。 create unique index xsno on s (sno) asc; 删除索引删除索引语句语句 dr
8、op index 删除名为删除名为xsno的索引的索引 drop index xsno;7.3 SQL Server2000中的数据定义语句v建立数据库建立数据库Create database student1;v删除数据库删除数据库Drop database student1;7.4 SQL的数据操纵功能v数据查询功能数据查询功能selectv增、删、改功能增、删、改功能Insert, delete, updatev其它功能其它功能赋值赋值分类分类运算运算7.5 SQL的数据操纵语句v关系代数中的查询语句关系代数中的查询语句求选修了求选修了c1号课程的学生姓名号课程的学生姓名 sname(
9、cnocno=c1(S SC)=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的数据操纵语句vSQL的基本查询语句的基本查询语句单表简单查询单表简单查询例例1：查询所有学生的情况：查询所有学生的情况 select * from s;*代表所有的列代表所有的列例例2：查询所有学生的姓名：
10、查询所有学生的姓名 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子句中子句中D
11、istinct ,as 用于用于select子句中子句中例例5：查询所有选修了课程的学生学号：查询所有选修了课程的学生学号 Select distinct sno From sc;Select后的后的distinct表示在结果中去掉重复的表示在结果中去掉重复的sno。7.5 SQL的数据操纵语句例：查询每个学生的出生年份例：查询每个学生的出生年份 select sno,2012-sage as birthyear from s;Select之后可以是简单运算之后可以是简单运算as 用于用于select 之后表示为某一属性取别名之后表示为某一属性取别名7.5 SQL的数据操纵语句例例6：查询年龄
12、：查询年龄 在在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_b7.5 SQL的数据操纵语句例
13、例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 null7.5 SQL的数据操纵语句布尔表达式布尔表达式not, and ,or 例例10：查询计算
14、机系年龄小于：查询计算机系年龄小于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
15、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 fi
16、rst.sno from sc first,sc second where o=o and second.sno=s5;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 d
17、esc;7.5 SQL的数据操纵语句v分层结构查询与集合谓词的使用分层结构查询与集合谓词的使用谓词谓词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);
18、7.5 SQL的数据操纵语句限定比较谓词的使用限定比较谓词的使用例例19：查询成绩大于所有选修课程号：查询成绩大于所有选修课程号c1的学的学生成绩的学生学号生成绩的学生学号 select sno from sc where grade all (select grade from sc where cno=c1); 7.5 SQL的数据操纵语句v查询间的运算语句查询间的运算语句例例20：查询计算机系的学生以及年龄小于：查询计算机系的学生以及年龄小于20的学的学生生 (select * from s where sdept=cs) union (select * from s where sag
19、e20 );7.5 SQL的数据操纵语句2 SQL的更新语句的更新语句vSQL的删除功能的删除功能vSQL的插入功能的插入功能vSQL的修改功能的修改功能7.5 SQL的数据操纵语句vSQL的删除功能的删除功能格式格式deletefrom where 例例21：删除学生：删除学生“王林王林”的信息的信息 delete from s where sname=王林王林;7.5 SQL的数据操纵语句例例22：删除计算机系学生的选课记录：删除计算机系学生的选课记录 delete from sc where cs= (select sdept from s where s.sno=sc.sno);注：注
20、： 删除操作可能会破坏参照完整性删除操作可能会破坏参照完整性7.5 SQL的数据操纵语句vSQL的插入功能的插入功能格式：格式：插入常量插入常量 insert into , values (,) 注：注： （1）只有当插入的元组中包含所有属性的值并且值的）只有当插入的元组中包含所有属性的值并且值的顺序顺序 与属性的顺序一致时，表名后的属性列表可以省略与属性的顺序一致时，表名后的属性列表可以省略 （2）当只插入部分属性的值时，没有插入值的属性默）当只插入部分属性的值时，没有插入值的属性默认取认取 空值。空值。7.5 SQL的数据操纵语句例例23：插入一条选课记录（：插入一条选课记录（s1,c1,
21、85） insert into sc values (s1,c1,85);例例24: 插入一条选课记录（插入一条选课记录（s2,c2） insert into sc (sno,cno) values (s2,c2); 7.5 SQL的数据操纵语句插入子查询结果插入子查询结果 insert into , 7.5 SQL的数据操纵语句例例25：将：将sc中成绩合格的记录插入到中成绩合格的记录插入到sci中。中。 insert into sci select * from sc where grade60;7.5 SQL的数据操纵语句vSQL的修改功能的修改功能格式格式 update set =表达
22、式表达式，=表达式表达式 where 指定对哪些列进行更新，以及更新后的值是指定对哪些列进行更新，以及更新后的值是什么什么7.5 SQL的数据操纵语句例例26：将学号为：将学号为s16的学生的系别改成的学生的系别改成cs。 update s set sdept=cs where sno=s16;例例27：将数学系学生的年龄增加：将数学系学生的年龄增加1岁。岁。 update s set sage=sage+1 where sdept=ma;7.5 SQL的数据操纵语句例例28：将计算机系的学生成绩全部置零：将计算机系的学生成绩全部置零 update sc set grade=0 where c
23、s= (select sdept from s where s.sno=sc.sno); 注：更新操作可能会破坏参照完整性。注：更新操作可能会破坏参照完整性。7.5 SQL的数据操纵语句3 SQL的统计、计算及分类语句的统计、计算及分类语句v统计功能统计功能count: 计数计数sum：求某一列的总和（该列为数值型）：求某一列的总和（该列为数值型）avg: 求某一列的平均值（该列为数值型）求某一列的平均值（该列为数值型）max: 求某一列的最大值（该列类型可比）求某一列的最大值（该列类型可比）min: 求某一列的最小值（该列类型可比）求某一列的最小值（该列类型可比）7.5 SQL的数据操纵语句
24、例例29：给出全体学生数：给出全体学生数 select count(*) from s;count(列名列名)与与count(*)的区别的区别: count(列名列名) 对一列中的值计算个数对一列中的值计算个数 count(*) 计算元组的个数计算元组的个数7.5 SQL的数据操纵语句例例30：求选修课程的学生人数：求选修课程的学生人数 select count ( distinct sno ) from sc;例例31：求学生选课记录数：求学生选课记录数 select count(*) from sc;7.5 SQL的数据操纵语句例例32：给出学生：给出学生s7所修课程的平均成绩所修课程的平
25、均成绩 select avg(grade) from sc where sno=s7;7.5 SQL的数据操纵语句v计算功能计算功能例例33：给出计算机系学生的出生日期：给出计算机系学生的出生日期 select sno,sname,2012-sage from s where sdept=cs;7.5 SQL的数据操纵语句v分组功能分组功能group by:对元组进行分组对元组进行分组 having ：设置逻辑条件，选择满足条件的分：设置逻辑条件，选择满足条件的分组组例例34：给出每个学生的平均成绩：给出每个学生的平均成绩 select sno,avg(grade) from sc group
26、 by sno;练习：给出每门课程的最高成绩练习：给出每门课程的最高成绩7.5 SQL的数据操纵语句例例35：给出修读人数超过：给出修读人数超过5人的课程号。人的课程号。 select cno,count(sno) from sc group by cno having count(*)5;7.6 SQL中的视图语句v视图定义视图定义格式格式 create view ( ,) as with check option视图的属性名缺省为子查询结果中的属性名，也视图的属性名缺省为子查询结果中的属性名，也可以显式指明可以显式指明with check option指明当对视图进行指明当对视图进行ins
27、ert，update时，要检查是否满足视图定义中的条件时，要检查是否满足视图定义中的条件7.6 SQL中的视图语句例例36：定义一个计算机系学生的视图：定义一个计算机系学生的视图 create view css(sno,sname,sage,sex,sdept) as (select * from s where sdept=cs)删除视图删除视图 drop view css;7.6 SQL中的视图语句v视图操作视图操作例例37：用已定义的视图，查询计算机系年龄大于：用已定义的视图，查询计算机系年龄大于20的学生。的学生。 select * from css where sage20;对视图的
28、查询，在实际操作中转换成对基表的查询对视图的查询，在实际操作中转换成对基表的查询 select * from s where sdept=cs and sage20;7.6 SQL中的视图语句例例38：对于表：对于表T(tno,tname,age,zc,sal),创建创建教师工资的视图教师工资的视图. create view T_SAL as (select tno, tname,sal from T ) ;对视图的更新操作将转换为对基表的更对视图的更新操作将转换为对基表的更新操作新操作7.6 SQL中的视图语句转转换换为为7.6 SQL中的视图语句v视图更新约束视图更新约束select子句中
29、的目标列不能包含聚集函数子句中的目标列不能包含聚集函数select子句中不能使用子句中不能使用unique或或distinct关键字关键字不能包括不能包括group by子句子句不能包括经算术表达式计算出来的列不能包括经算术表达式计算出来的列对于行列子集视图可以更新（视图是从单个基本对于行列子集视图可以更新（视图是从单个基本表使用选择、投影操作导出的，并且包含了基本表使用选择、投影操作导出的，并且包含了基本表的主码）表的主码）7.7 SQL Server2000中的数据操纵及视图功能vSql Server 2000中的数据操作功能与中的数据操作功能与SQL92符合度较高，两者基本可以兼容符合度
30、较高，两者基本可以兼容v创建视图创建视图v删除视图删除视图v修改视图修改视图本章小结本章小结v基本功能基本功能数据定义数据定义模式定义层模式定义层表结构定义层表结构定义层列定义层列定义层数据操纵数据操纵查询查询增、删、改增、删、改统计、分类、赋值统计、分类、赋值习题习题v设有供应商设有供应商S、零件、零件P、工程项目、工程项目J三个关系三个关系模式以及反映它们之间联系（某工程使用了模式以及反映它们之间联系（某工程使用了某供应商提供的某零件多少个）的关系模式某供应商提供的某零件多少个）的关系模式SPJ。 S (SNO, SNAME, STATUS, CITY) P (PNO, PNAME, CO
31、LOR, WEIGHT) J (JNO, JNAME, CITY) SPJ(SNO, PNO, JNO, QTY)习题习题1.求向工程求向工程J1提供零件的供应商的编号。提供零件的供应商的编号。2.求向工程求向工程J1提供零件提供零件P1的供应商的编号。的供应商的编号。3.求向工程求向工程J1提供红色零件的供应商的编号。提供红色零件的供应商的编号。4.求没有使用天津供应商提供的红色零件的工程号。求没有使用天津供应商提供的红色零件的工程号。5.统计各个供应商供应的零件总数。统计各个供应商供应的零件总数。6.求至少使用了求至少使用了3种以上零件的工程号。种以上零件的工程号。7.把所有红色零件的颜色
32、改为粉红色。把所有红色零件的颜色改为粉红色。8.将向工程将向工程J1提供零件提供零件P1的供应商的供应商S1改为改为S2提供。提供。9.删除红色零件的供应删除红色零件的供应-使用记录使用记录。自连接snoCno gS1C185S2C280S5C280snoCno gS1C185S2C280S5C280snoCno gS5C280SECONDFIRSTIN查询snosname sageS1刘敏刘敏18S2王天成王天成19S5李舒李舒20snoCno gS1C185S2C180S5C280Sname刘敏刘敏王天成王天成first:在在sc表中查询课程号表中查询课程号为为C1的学生学号的学生学号se
33、cond:在在s表中查询其学表中查询其学号在第一步结果表中的元号在第一步结果表中的元组，取其组，取其sname属性。属性。SnoS1S2比较谓词比较谓词sno Cno gS1C185S2C180S3C390scg8580删除操作snosname sdeptS1刘敏刘敏csS2王天成王天成csS5李舒李舒pysnoCno gS1C185S2C180S5C280分组操作sno cnogs1c184s1c290s1c396s2c180s2c290s3c296s3c388snocnogs1c184s1c290s1c396s2c180s2c290s3c296s3c388列出每个学生的平均成绩列出每个学生的平均成绩列出每门课程的平均成绩列出每门课程的平均成绩group by snogroup by cno928590929290
