数据库—05(SQL语句)

1、T-SQL SQL de内容内容数据定义1数据查询2数据更新3视 图4SQL de 特点特点特点一综合统一特点二高度非过程化特点三面向集合特点四多种应用方式SQL de 特点特点数据查询select数据定义createdropalter数据操纵insertupdatedelete数据控制grantrevokeSQLView1View2Base-T2Base-T3Base-T1Base-T3外模式模 式内模式Store-File1Store-File2SnoSnameSsexSageSdept200215121李勇男20CS200215122刘晨男19CS200215123王敏女18MA2002

2、15125张力男19ISCnoCnameCpno Ccredit001数据库0054002数学0054003信息系统0054004操作系统0054005数据结构0054006数据处理0054007pascal0054SnoCnoGrade200215121192200215121285200215121388200215122290200215122380Student表Course表选课表数据定义（数据定义（data definition）v模式定义v表定义v视图定义v索引定义 问：数据库与模式之间的关系？问：数据库与模式之间的关系？答：模式定义了一个命名空间，容纳相应答：模式定义了一个命名

3、空间，容纳相应的数据对象的定义。数据库的逻辑定义。的数据对象的定义。数据库的逻辑定义。 数据库是具体的数据的仓库。数据以数据库是具体的数据的仓库。数据以二维表的形式进行组织。二维表的形式进行组织。表定义（表定义（Table definition）CREATE TABLE 表名(列名1 数据类型 约束条件, 列名2 数据类型 约束条件, 列名3 数据类型 约束条件, 表级完整性约束1， 表级完整性约束2，) 格式表定义（表定义（Table definition）CREATE TABLE student(Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) UNIQUE,

4、 Sex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ) 例题一 创建学生表表定义（表定义（Table definition）CREATE TABLE course(Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(2), Ccredit SMALLINT, FOREIGN KEY Cpno REFERENCES course(Cno) ) 例题二 创建课程表表定义（表定义（Table definition）CREATE TABLE sc(Sno CHAR(9) PRIMARY KEY (Sno,Cno),

5、Cno CHAR(40), Grade SMALLINT, PRIMARY KEY (Sno,Cno) , FOREIGN KEY (Cno) REFERENCES course(Cno) , FOREIGN KEY (Sno) REFERENCES student(Sno) ,) 例题三 创建选课表数据类型（数据类型（Data Type）Sql server 2000数据类型数据类型修改基本表（修改基本表（Alter Table）格式ALTER TABLE 表名ADD 新列名 数据类型 约束条件DROP 完整性约束名ALTER COLUM 列名 数据类型修改基本表（修改基本表（Alter T

6、able）ALTER TABLE student ADD S_entrance DATE例题一 向student表添加“入学时间/S_entrance”列，数据类型为日期型。ALTER TABLE student ALTER COLUMN Sage INT例题二 将student表“Sage”列数据类型由字符型改为整形。修改基本表（修改基本表（Alter Table）ALTER TABLE student ADD UNIQUE(Cname)例题三 为课程名称列添加UNIQUE约束。ALTER TABLE student Drop column Cpno例题四 删除Cpno列删除基本表（删除基本

7、表（Drop Table）DROP TABLE 表名RESTRICT|CASECADE格式删除基本表（删除基本表（Drop Table）DROP TABLE 表名RESTRICT|CASECADE格式删除基本表（删除基本表（Drop Table）CREATE VIEW v_stuAs Select * from student Where sdept=isDrop table studentDrop table student CASCADE问题一 删除表 student删除基本表（删除基本表（Drop Table）v对于索引，删除基本表后，索引将被删除v对于视图，删除表后，sql serve

8、r 2000将保留视图的定义，但视图已经失效了v对于存储过程和函数，不会删除，会失效v对于触发器或被其他表引用的情况，不同的DBMS有不同的策略删除表的几个问题建立索引（建立索引（CREATE INDEX）CREATE UNIQUE|CLUSTER INDEX 索引名 On 表名(列名 次序)格式索引项的顺序与表的物理记录顺序一致的索引每个索引值只对应唯一的数据记录建立索引（建立索引（CREATE INDEX）Create unique index c_index on Course(Cno desc)问题一 为课程表建立升序唯一索引，索引顺序按课程号降序排列建立索引（建立索引（CREATE

9、INDEX）Drop index 索引名Drop index c_index问题一 删除索引数据查询（数据查询（data quary）Select all | distinct 目标列表达式From 表名或视图名Where 条件表达式Group by 列名 having 条件表达式Order by 列名 asc|desc格式数据查询（数据查询（data quary）Select Sno,SnameFrom studentSelect *From studentSelect top 3 Sno,SnameFrom student问题一 单表查询（选择一个表中的部分或全部列） 从学生表中查找学号和

10、姓名数据查询（数据查询（data quary）Select sname as name,Sno+Sname as idFrom student消除取值重复的行Select distinct snoFrom sc问题一 单表查询（选择一个表中的部分或全部列） 以学生的学号+姓名为id 查找学生的记录数据查询（数据查询（data quary）查询条件：比较：,=,.范围:集合:in,not in字符匹配: like,not like空值: is null,is not null多重条件:and ,or ,not,问题二 查询满足条件的记录Select *From studentWhere sno

11、like 200215121Select *From studentWhere sno like 2002%Select *From studentWhere sno like _00215121字符匹配数据查询（数据查询（data quary）Select * From sc Order by cno descOrder by 的使用查找sc中的记录，要求结果按课程号降序排列数据查询（数据查询（data quary）Select count(cno) From sc Order by cno desc聚集函数的使用Count() 求元组个数Sum() 对值求和Avg() 求元组平均值Max(

12、) 求元组最大值Min() 求元组最小值数据查询（数据查询（data quary）找出学生总共选修了多少门课程Select cno,count(cno) From sc group by cno查找选修人数超过1人的课程号Select cno,count(sno) From sc group by cnoHaving count(*)1group by 的使用连接查询（连接查询（link quary）查询选修每个学生及其选修课程的情况select student.*, sc.*from student,scwhere student.sno=sc.sno若一个查询同时涉及到两个以上的表，称之为

13、连接查询若去掉等值连接目标列中重复的属性，则为自然连接若去掉等值连接目标列中重复的属性，则为自然连接自然连接自然连接一般要求参与运算的两个关系必须有一个以上的公共属性。如果没有，则自然连接等于乘积操作。X Y Z Aa 1 a ad 2 b dc 3 c f X Y Za 1 ab 2 bc 3 cd 4 dA Ya 1d 2f 3c 7T = R S自然连接的步骤自然连接的步骤乘积乘积，求出参与运算的，求出参与运算的R和和S的的乘积乘积R R S S；选择选择，挑选出在所有公共属性，挑选出在所有公共属性上相等的值上相等的值 R.Ai=S.AiR.Ai=S.Ai；投影投影，去掉重复属性；，去掉

14、重复属性；X R.Y S.Y Z Aa 1 1 a aa 1 2 a da 1 3 a fa 1 7 a cb 2 1 b ab 2 2 b db 2 3 b fb 2 7 b cc 3 1 c ac 3 2 c dc 3 3 c fc 3 7 c cd 4 1 d ad 4 2 d dd 4 3 d fd 4 7 d c连接查询（连接查询（link quary）查询每一门课程的间接先修课程（即先修课程的先修课程）select o,second.cpnofrom course first,course secondwhere first.cpno=o注意选择的字段和用来比较的字段连接不仅可以在

15、不同的表之间进行，也可在同一个表中进行嵌套查询嵌套查询查询选修了“信息系统”课程的学生的学号和成绩select sno,grade from sc where o=(select cno from course where cname=信息系统)将一个查询块嵌套在另一个查询块的where 子句或having 子句中将某个查询作为另一个查询的中间结果SnoSnameSsexSageSdept200215121李勇男20CS200215122刘晨男19CS200215123王敏女18MA200215125张力男19ISCnoCnameCpno Ccredit001数据库0054002数学00540

16、03信息系统0054004操作系统0054005数据结构0054006数据处理0054007pascal0054SnoCnoGrade200215121192200215121285200215121388200215122290200215122380Student表Course表选课表嵌套查询嵌套查询查询各课程的课程名及选修人数？解：将任务分解为2步，1.找出各课程的选修人数；2.根据课程号连接第一步的查询结果和course表。select cno,count(cno) as cc into tmp from sc group by cnoselect ame,b.cc from cour

17、se a,tmp bwhere o=o数据查询（数据查询（data quary）查询各课程的课程名及选修人数？解：将任务分解为2步，1.找出各课程的选修人数2.根据课程号连接第一步的查询结果和course表select ame, b.cc from (select cno,count(cno) as cc from sc group by cno) as b,course as a where o=o查找选修人数超过1人的课程名及修课人数？相关数据查询（相关数据查询（data quary）找出每个学生超过他选修课程平均成绩的课程号？解：将任务分解为2步：1.通过学号找出某个学生选修的课程的平均

18、成绩；2.查询成绩优于课程平均成绩的课程号和学号select avg(grade) from scwhere sno=xxxSelect sno,cno from scWhere gradeyyy相关数据查询（相关数据查询（data quary）Select sno,cno From sc xWhere grade( select avg(grade) from sc y where y.sno=x.sno)执行步骤： 1.从外层查询的sc中取出元组，并根据元组的sno值确定课程的平均成绩 2根据平均成绩查找课程号 3.循环此过程，直到外层所有元组结束数据更新（数据更新（data update

19、）插入数据1修改数据2删除数据3插入数据1INSERT INTO （属性列1，属性列2属性列n）VALUES (表达式1，表达式2表达式n)将一个新学生元组插入到student表中(学号：2002215128；姓名：陈冬；性别：男；所在系：IS；年龄：18)Insert into Student (sno, sname, ssex, sdept, sage)Values (200215128,陈冬,男, IS, 18)插入数据1将每个系的学生的平均年龄信息放在dept_avg表中 create table dept_avg (sdept char(15), avg_age smallint)I

20、nsert into dept_avg (sdept,avg_age) select sdept,avg(sage) from student group by sdept插入数据1需要注意的问题：1.表达式可以是常量、变量，也可以是函数2.在into 句中未出现的属性应允许取空值3.插入值的类型与相应的属性列的数据类型一致根据查询建立数据表根据查询建立数据表select sdept,avg(sage) into dept_avg from student group by sdeptUPDATE SET 列名=表达式，列名=表达式WHERE 将学生200215121的年龄改为22岁updat

21、e student set sage=22where sno=200215121将所有学生的年龄增加1岁update student set sage=sage+1修改数据2DELETE FROM WHERE 删除学号为200215128的学生的记录deletefrom studentwhere sno=200215128如果不指定条件，将删除表中所有的数据删除数据3定义视图定义视图-建立视图建立视图-删除视图删除视图查询视图查询视图更新视图更新视图视图（视图（view）create view列名，列名as with check option建立信息系学生的视图create view is_s

22、tudentasselect sno，sname，sagefrom studentwhere sdept=is建立视图（建立视图（create view）建立信息系学生的视图create view is_studentasselect sno，sname，sagefrom studentwhere sdept=is视图的列名与查询结果的列名相同建立视图（建立视图（create view）建立信息系学生的视图create view is_studentasselect sno，sname，sagefrom studentwhere sdept=is视图的列名与查询结果的列名相同建立视图（建立视图（create view）行列子集视图1.从单个基本表导出2.只是去掉某些行和某些列3.保留主码建立视图（建立视图（create view）建立信息系选修了1号课程的学生的视图create view is_si(sno,sname,grade)asselect student.sno,sname,gradefrom student,scwhere sdept=is and student.sno=sc.sno and o=1建立视图（建立视图（create view）从两个表导出视图，不是行列子集视图建立反映学生出生年份的视图create view