7 SQL的数据定义语句

第七章SQL的数据定义与操纵7.1SQL的数据定义功能7.2SQL的数据定义语句7.3SQLServer2000的数据定义语句7.4SQL的数据操纵功能7.5SQL的数据操纵语句7.6SQL中的视图语句7.7SQLServer2000中的数据操纵及视图功能本章小结习题7.1SQL的数据定义功能主要为应用系统定义数据库上的整体结构模式，包括三个层次：上层---模式层关系数据库管理系统可以定义多个模式每个模式对应一个应用系统模式由若干个表、视图及索引组成7.1SQL的数据定义功能中层---表结构层基表创建表修改表删除表视图视图是建立在基表上的虚表创建视图删除视图索引创建索引删除索引7.1SQL的数据定义功能底层---列定义层表中属性的定义如列名的定义列数据类型的定义列的完整性约束定义如列是否是主键或外键列值是否允许为空列间的约束表达式7.2SQL的数据定义语句1SQL的基本数据类型7.2SQL的数据定义语句2SQL的模式定义语句模式定义定义语句：createschema模式名authorization用户名例如，学生数据库的模式可定义如下：createschemastudentauthorizationlin;7.2SQL的数据定义语句模式删除语句为Dropschema模式名,删除方式删除方式有两种：Cascade,表示级联方式Restrict,表示限制方式删除学生数据库模式：Dropschemastudentcascade;7.2SQL的数据定义语句3SQL的表定义语句表的定义语句Createtable表名(列定义[列定义]…)[其它参数]其中列定义形式如下：列名数据类型7.2SQL的数据定义语句例：前面所创建的student数据库中有s,c,sc三张表，试定义它们。createtables(snochar(4),snamevarchar(8),sagesmallint,sexchar(2),sdeptchar(2));7.2SQL的数据定义语句createtablec(cnochar(2),cnamevarchar(20),creditnumeric);createtablesc(snochar(4),cnochar(2),gradesmallint);7.2SQL的数据定义语句表的更改增加列Altertable表名add列名数据类型例，在s中增加一个新的列sexAltertablesaddsexchar(2);删除列Altertable表名drop列名例，在s中将sdept删除Altertablesdropsdept;7.2SQL的数据定义语句表的删除语句droptable表名例，删除s表droptables;删除表后，表中的数据、在此表上建立的视图、索引都将删除7.2SQL的数据定义语句4SQL的索引定义语句建立索引语句create[unique][cluster]index索引名on表名(列名[顺序]...)[其它参数]Unique:唯一性索引，不允许两个元组在给定索引中有相同的值。Cluster:聚簇索引，索引项的顺序与表中记录的物理顺序一致。顺序：ASC,DESC2SQL的数据定义语句例：在表s的sno上建立一个按升序排列的唯一性索引xsno。createuniqueindexxsnoons(sno)asc;删除索引语句dropindex索引名删除名为xsno的索引dropindexxsno;7.3SQLServer2000中的数据定义语句建立数据库Createdatabasestudent1;删除数据库Dropdatabasestudent1;7.4SQL的数据操纵功能数据查询功能select增、删、改功能Insert,delete,update其它功能赋值分类运算7.5SQL的数据操纵语句关系代数中的查询语句求选修了c1号课程的学生姓名∏sname(cno=c1(SSC))7.5SQL的数据操纵语句1SQL的查询语句Select列名From表名Where选择条件投影选择连接7.5SQL的数据操纵语句示例数据：S(sno,sname,sage,sex,sdept)C(cno,cname,credit)SC(sno,cno,grade)7.5SQL的数据操纵语句SQL的基本查询语句单表简单查询例1：查询所有学生的情况select*froms;*代表所有的列例2：查询所有学生的姓名selectsnamefroms;7.5SQL的数据操纵语句例3：查询学号为s01的学生学号与姓名Selectsno,snameFromsWheresno=‘s01’;Where后面可以使用比较符，包括=、、、=、=、、!=7.5SQL的数据操纵语句例4：查询年龄大于20的学生的学号和姓名Selectsno,snameFromsWheresage20;7.5SQL的数据操纵语句常用谓词between,like,null用于where子句中Distinct,as用于select子句中例5：查询所有选修了课程的学生学号SelectdistinctsnoFromsc;Select后的distinct表示在结果中去掉重复的sno。7.5SQL的数据操纵语句例：查询每个学生的出生年份selectsno,2008-sageasbirthyearfroms;Select之后可以是简单运算as用于select之后表示为某一属性取别名7.5SQL的数据操纵语句例6：查询年龄在18-21岁的学生的姓名和年龄Selectsname,sageFroms;wheresagebetween18and21;（不）(not)7.5SQL的数据操纵语句例7：查询姓“李”的学生的姓名及所在系Selectsname,sdeptFromsWheresnamelike‘李%’；Like一般情况下与通配符%,_一起使用%表示任意长度的字符串，例a%b_表示任意单个字符，例a_b7.5SQL的数据操纵语句例8：查询名字的倒数第二字为“丽”的学生的姓名及所在系Selectsname,sdeptFromsWheresnamelike‘%丽_’；7.5SQL的数据操纵语句例9：查询无课程分数的学生的学号和课程号selectsno,cnofromscwheregradeisnull;Null是测试属性值是否为空的谓词，其一般形式是：列名is[not]null7.5SQL的数据操纵语句布尔表达式not,and,or例10：查询计算机系年龄小于20的学生姓名SelectsnameFromsWheresdept=‘cs’andsage20;例11：查询非计算机系或年龄不为20的学生姓名SelectsnameFromsWherenotsdept=‘cs’ornotsage=20;7.5SQL的数据操纵语句单连接表间等值连接在where子句中设置两表相同属性间的相等关系例12：查询修读课程号为c1的所有学生的姓名selectsnamefroms,scwheres.sno=sc.snoandsc.cno=‘c1’;7.5SQL的数据操纵语句例13：查询修读课程名为”数据库”的所有学生姓名selectsnamefroms,sc,cwheres.sno=sc.snoandsc.cno=c.cnoandcname=‘数据库’;7.5SQL的数据操纵语句自连接例14：查询至少选修了s5所修读的一门课的学生学号selectfirst.snofromscfirst,scsecondwherefirst.cno=second.cnoandsecond.sno=‘s5’;7.5SQL的数据操纵语句练习：求每一门课程的间接先行课selectfirst.cno,second.prenofromcasfirst,cassecondwherefirst.preno=second.cno;7.5SQL的数据操纵语句结果排序orderby列名[ASC/DESC]例15：查询计算机系所有学生名单并按学号顺序升序显示。selectsno,snamefromswheresdept=‘cs’orderbysnoasc;7.5SQL的数据操纵语句例16：查询全体学生情况，结果按学生年龄降序排列select*fromsorderbysagedesc;7.5SQL的数据操纵语句分层结构查询与集合谓词的使用谓词in的使用表示元素与集合之间的属于关系例17：查询计算机系、数学系及物理系学生的姓名selectsnamefromswheresdeptin(‘ma’,’cs’,’py’);7.5SQL的数据操纵语句例18：查询修读课程号为c1的所有学生姓名selectsnamefromswheresnoin(selectsnofromscwherecno=‘c1’);7.5SQL的数据操纵语句例19：查询修读课程名为java的学生姓名selectsnamefromswheresnoin(selectsnofromscwherecnoin(selectcnofromcwherecname=‘java’));7.5SQL的数据操纵语句限定比较谓词的使用例20：查询成绩大于所有选修课程号c1的学生成绩的学生学号selectsnofromscwheregradeall(selectgradefromscwherecno=‘c1’);7.5SQL的数据操纵语句查询间的运算语句例21：查询计算机系的学生以及年龄小于20的学生(select*fromswheresdept=‘cs’)union(select*fromswheresage20);7.5SQL的数据操纵语句2SQL的更新语句SQL的删除功能SQL的插入功能SQL的修改功能7.5SQL的数据操纵语句SQL的删除功能格式deletefrom基表where逻辑条件例22：删除学生“王林”的信息deletefromswheresname=‘王林’;7.5SQL的数据操纵语句例23：删除计算机系学生的选课记录deletefromscwhere‘cs’=(selectsdeptfromswheres.sno=sc.sno);注：删除操作可能会破坏参照完整性7.5SQL的数据操纵语句SQL的插入功能格式：插入常量insertinto表名[列名[,列名]…]values(常量[,常量]…)注：（1）只有当插入的元组中包含所有属性的值并且值的顺序与属性的顺序一致时，表名后的属性列表可以省略（2）当只插入部分属性的值时，没有插入值的属性默认取空值。7.5SQL的数据操纵语句例24：插入一条选课记录（s1,c1,85）insertintoscvalues(‘s1’,’c1’,85);例25:插入一条选课记录（s2,c2）insertintosc(sno,cno)values(‘s2’,’c2’);7.5SQL的数据操纵语句插入子查询结果insertinto表名[列名[,列名]…]子查询语句7.5SQL的数据操纵语句例26：将sc中成绩合格的记录插入到sci中。insertintosciselect*fromscwheregrade60;7.5SQL的数据操纵语句SQL的修改功能格式update表名set列名=表达式[，列名=表达式]…where逻辑条件指定对哪些列进行更新，以及更新后的值是什么7.5SQL的数据操纵语句例27：将学号为s16的学生的系别改成cs。updatessetsdept=‘cs’wheresno=‘s16’;例28：将数学系学生的年龄增加1岁。u