32数据定义.docx

1、32数据定义3.2数据定义3.2.1 定义、删除与修改基本表一、 定义基本表格式：CREAT TABLE 表名（列名数据类型列级完整性约束条件 ，列名数据类型列级完整性约束条件 ，表级完整性约束条件；例1 建立一个“学生”表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREAT TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, /*列级完整性约束条件，Sname CHAR(20) UNIQUE, Sno取值唯一，不许取空值*/Ssex CHAR(

2、1),Sage INT,Sdept CHAR(15);二、 修改基本表格式：ALTER TABLE 表名ADD新列名数据类型完整性约束DROPMODIFY ;例2 向Student表增加“入学时间”列，其数据类型为日期型。ALTER TABLE Student ADD Scome DATE;例3 将年龄的数据类型改为半字长整数。ALTER TABLE Student MODIFY Sage SMALLINT;例4 删除学生姓名必须取唯一值的约束。ALTER TABLE Student DROP UNIQUE(Sname);三、 删除基本表格式： DROP TABLE 例5 删除Student表

3、 DROP TABLE Student3.2.2 建立与删除索引一、 建立索引格式：CREATE UNIQUE CLUSTER INDEX ON ( )例6 CREATE TABLE UNIQUE INDEX Stusno ON Student (Sno); CREATE TABLE UNIQUE INDEX Coucno ON Course(Cno);CREATE TABLE UNIQUE INDEX Scno ON SC(Sno ASC,Cno DESC);二、 删除索引格式：DROP INDEX ;例7 DROP INDEX Stusno3.3 查询格式：SELECT ALLDISTIN

4、CT ,FROM ,WHEREGROUP BY HAVINGORDER BYASCDESC;学生-课程数据库中包括三个表学生表: Student(Sno,Sname,Ssex,Sage,Sdpt)课程表: Course(Cno,Cname,Cpno,Ccredit)选课表: SC(Sno,Cno,Grade)3.3.1单表查询一、 选择表中的若干列例1 查询全体学生的学号与姓名。SELECT Sno,SnameFROM Student;例2 查询全体学生的姓名、学号、所在系。SELECT Sname,Sno,SdeptFROM Student;例3 查询全体学生的详细记录。SELECT *FR

5、OM Student;例4 查询全体学生的姓名及出生年份。SELECT Sname,2001-Sage,FROM Student;Sname 2001-Sage李勇 1980刘晨 1981王敏 1982张立 1981例5 查询全体学生的姓名、出生年份、所在系（小写）。SELECT Sname, “Year of Birth:”, 2001-Sage, ISLOWER(Sdept)FROM Student; Sname “Year of Birth:” 2001-Sage ISLOWER(Sdept)李勇 Year of Birth: 1980 cs刘晨 Year of Birth: 1981

6、is王敏 Year of Birth: 1982 ma张立 Year of Birth: 1981 isSELECT Sname NAME, “Year of Birth:” BIRTH, 2001-Sage BIRTHDAY, ISLOWER(Sdept) DEPARTMENTFROM Student; Sname “Year of Birth:” 2001-Sage ISLOWER(Sdept)李勇 Year of Birth: 1980 cs刘晨 Year of Birth: 1981 is王敏 Year of Birth: 1982 ma张立 Year of Birth: 1981 i

7、s二、 选择表中的若干元组假设SC表中有下列数据Sno Cno Grade95001 1 9295001 2 8595001 3 8895002 2 9095002 3 80例6 查询选修了课程的学生学号。SELECT Sno FROM SC;结果为：Sno 95001 95001 95001 95002 95002 SELECT DIATINCT Sno FROM SC;结果为：Sno 95001 95002 * DISTINCT消除重复行*例7 查询计算机系全体学生的名单。SELECT SnameFROM StudentWHERE Sdept=CS;例8 查询所有年龄在20岁以下的学生姓名

8、及年龄。SELECT Sname，SageFROM StudentWHERE Sage=20;例9 查询考试成绩有不及格的学生的学号。SELECT DISTINCT Sno FROM CourseWHERE Grade60;例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sage BETWEEN 20 AND23;例11 查询年龄不在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sag

9、e NOT BETWEEN 20 AND23;例12 查询信息系（IS）、数学系（MA）和计算机系（CS）学生的姓名和性别。SELECT Sname,SsexFROM StudentWHERE Sdept IN (IN,MA,CS)例13 查询不是信息系（IS）、数学系（MA）和计算机系（CS）学生的姓名和性别。SELECT Sname,SsexFROM StudentWHERE Sdept NOT IN (IN,MA,CS)%代表任意长度的字符串。_代表任意单个字。例14 查询学号为95001的学生的详细情况。SELECT *FROM Student WHERE Sno LIKE 95001

10、等价于：SELECT *FROM Student WHERE Sno LIKE 95001例15 查询所有姓刘的学生的姓名、学号和性别。SELECT Sname,Sno,SsexFROM StudentWHERE Sname LIKE 刘%例16 查询姓“欧阳”且全名为三个汉字的学生的姓名。SELECT SnameFROM StudentWHERE Sname LIKE 欧阳_ _;例17 查询名字中第2个字为“阳”字的学生的姓名和学号。SELECT Sname,Sno FROM Student WHERE Sname LIKE _ _阳%;例18 查询所有不姓刘的学生的姓名。SELECT S

11、name,Sno,SsexFROM StudentWHERE Sname NOT LIKE 刘%例19 查询DB_Design课程的课程号和学分。SELECT Cno,CcreditFROM CourseWHERE Cname LIKE DB_Design ESCAPE 例20 查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况。SELECT *FROM CourseWHERE Cname LIKE DB _ %_ _ ESCAPE ;例21 查询缺少成绩的学生的学号和相应的课程号。SELECL Sno, CnoFORM SCWHERE Grade IS NULL;或 SELECL S

12、no, CnoFORM SCWHERE Grade IS NULL;例22 查询所有有成绩的学生的学号和相应的课程号。SELECL Sno, CnoFORM SCWHERE Grade IS NOT NULL;例23 查询计算机系年龄在20岁以下的学生姓名。SELECT SnameFROM Student WHERE Sdept=CS AND Sage20;三、 对查询结果排序例24 查询选修了3号课程的学生的学号及成绩，查询结果按分数的降序排列。SELECT Sno,GradeFROM SCWHERE Cno=3ORDER BY Grade DESC;例25 查询全体学生情况，查询结果按所在

13、系的系号升序排列，同一系中的学生按年龄降序排列。SELECT *FROM Student ORDER BY Sdept,Sage DESC;四、 使用集函数COUNT (DISTINCTALL*) 统计元组个数COUNT(DISTINCTALL) 统计一列中值的个数SUM(DISTINCTALL) 计算一列值的总和(数值型)AVG(DISTINCTALL) 计算一列值的平均值(数值型)MAX(DISTINCTALL) 求一列值的最大值(数值型)MIN(DISTINCTALL) 求一列值的最小值(数值型)例26 查询学生总数。SELECT COUNT（*）FROM Student例27 查询选修

14、了课程的学生人数。SELECT COUNT（DISTINCT Sno）FROM SC例28 计算1号课程的学生平均成绩。SELECT AVG（Grade）FROM SCWHERE Cno=1;例29 查询选修1号课程的学生最高分数。SELECT MAX（Grade）FROM SCWHERE Cno=1;五、 对查询结果分组例30 求各个课程号及相应的选课人数。SELECT Cno,COUNT(Sno)FROM SCGROUP BY Cno;Cno COUNT(Sno)1 222 343 444 335 48例31 查询选修了3门以上课程的学生学号。SELECT Sno FROM SCGROUP

15、 BY SnoHAVING COUNT(*)3;3.3.2 连接查询例32 查询每个学生及其选修课程的情况。SELECT student.*,SC.*FROM Student,SCWHERE Student.Sno=SC.Sno;例33 查询每个学生及其选修课程的情况(自然连接)。SELECT student.Sno,Sname,Sage,Sdept,Cno,GradeFROM Student,SCWHERE Student.Sno=SC.Sno;例34 查询每门课的间接先修课（即先修课的先修课）。FIRST表（Course表）Cno Cname Cpno Ccredit 1 数据库 5 42

16、 数学 23 信息系统 1 44 操作系统 6 35 数据结构 7 46 数据处理 27 PASCAL语言 6 4SECOND表（Course表）Cno Cname Cpno Ccredit 1 数据库 5 42 数学 23 信息系统 1 44 操作系统 6 35 数据结构 7 46 数据处理 27 PASCAL语言 6 4SELECLE FIRST.Cno,SECOND.CpnoFROM Course FIRSE, Course SECONDWHERE FIRSE.Cpno=SECOND.Cno;结果为： Cno Cpno1 73 5外连接SELECT student.Sno,Sname,S

17、age,Sdept,Cno,GradeFROM Student,SCWHERE Student.Sno=SC.Sno(*);复合条件连接例35 查询选修2号课程且成绩在90分以上的所有学生。SELECT Student.Sno, SnameFROM Student, SCWHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Ggrade90;多表连接例36 查询每个学生的学号、姓名、选修的课程名及成绩。SELECE Student.Sno,Sname,Cname,GradeFROM Student,SC,CourseWHERE Student.Sno=SC.

18、Sno and SC.Cno=Course.Cno;3.3.3嵌套查询例37 查询与“刘晨”在同一个系学习的学生。SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname=刘晨;当结果为单值时可以用=代替INSELECT Sno,Sname,SdeptFROM StudentWHERE Sdept = (SELECT Sdept FROM Student WHERE Sname=刘晨;用自然连接完成SELECT S1.Sno,S1.Sname,S1.SdeptFROM Stud

19、ent S1,Student S2WHERE S1.Sdept=S2.Sdept AND S2.Sname=刘晨;例38 查询选修了课程名为“信息系统”的学生学号和姓名。SELECT Sno,SnameFROM StudentWHERE Sno IN(SELECT SnoFROM SCWHERE Cno IN (SELECT Cno FROM Course WHERE Cname=信息系统);用连接查询SELECT Sno,SnameFROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.C

20、name=信息系统;带有ANY或ALL谓词的子查询 ANY 大于子查询结果中的某个值ALL 大于子查询结果中的所有值ANY 小于子查询结果中的某个值=ANY 大于等于子查询结果中的某个值=ALL 大于等于子查询结果中的所有值=ANY 小于等于子查询结果中的某个值=ALL 小于等于子查询结果中的所有值=ANY 等于子查询结果中的某个值=ALL 等于子查询结果中的所有值（无意义）!=(或)ANY 不等于子查询结果中的某个值!=(或)ALL 不等于子查询结果中的任何一个值例39 查询其他系中比信息系某一学生年龄小的学生姓名和年龄。SELECT Sname, Sage FROM Student WHE

21、RE SageANY(SELECT Sage FROM Student WHERE Sdept=IS AND SdeptIS用集函数实现SELECT Sname.SageFROM StudentWHERE Sage (SELECT MAX(Sage) FROM Student WHERE Sdept=IS) AND SdeptIS例40 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。SELECT Sname, Sage FROM StudentWHERE SageALL(SELECT Sage FROM Student WHERE Sdept=IS)AND SdeptIS用集函数实现S

22、ELECT Sname, Sage FROM StudentWHERE Sage(SELECT MIN(Sage) FROM Student WHERE Sdept=IS)AND SdeptIS带有EXISTS谓词的子查询例41 查询所有选修了1号课程的学生姓名。SELECT SnameFROM StudentWHERE EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1);例42 查询没有选修1号课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT * FROM SC WHE

23、RE Sno=Student.Sno AND Cno=1);例43 查询选修了全部课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.SnoAND Cno=Course.Cno);例44 查询至少选修了学生95002选修的全部课程的学号号码。SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sn

24、o=95002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Cno=SCY.Cno3.3.4 集合查询例45 查询计算机系的学生及年龄不大于19岁的学生。SELECT *FROM StudentWHERE Sdept=CSUNIONSELECT *FROM StudentWHERE Sage=19;例46 查询选修了课程1或选修了课程2的学生。*查询选修课程1的学生集合与选修课程2的学生集合的并集。SELECT SnoFROM SCWHERE Cno=1UNIONSELECT SnoFROM SCWHERE Cno=2;例47 查询计算机系的学

25、生与年龄不大于19岁的学生的交集。*查询计算机系中年龄不大于19岁的学生。SELECT *FROM StudentWHERE Sdept=CS AND Sage19;3.4 数据更新SQL中数据更新包括插入数据、修改数据和删除数据三条语句。3.4.1 插入数据格式1： INSERTINTO表名（属性列1，属性列2）VALUE (常量1，常量2)例1 将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERTINTO StudentVALUE (95020, 陈冬, 男, IS, 18)例2 插入一条选课记录（95020, 1）。

26、INSERTINTO SC（Sno,Cno）VALUE (95020, 1)新插入记录在Grade列上取空值。格式2： INSERTINTO表名（属性列1，属性列2） 子查询；例3 对每个系，求学生的平均年龄，并把结果存入数据库。CREAT TABLE Deptage (Sdept CHAR(15) Avgage SMALINT);INSERTINTO Deptage(Sdept,Avgage)SELECT Sdept, AVG(Sage)FROM StudentGROUP BY Sdept;3.4.2 修改数据格式：UPDATE 表名SET列名=表达式，列名=表达式WHERE 条件；例4 将

27、学生95001的年龄改为22岁。UPDATE Student SET Sage=22WHERE Sno=95001例5 将所有学生的年龄增加1岁。UPDATE Student SET Sage=Sage+1;例6 将计算机系全体学生的成绩置零。UPDATE SCSET Grade=0WHERE CS= (SELECT Sdept FROM Student WHERE Student,Sno=SC.Sno);3.4.3 删除数据格式：DELETEFROM 表名WHERE条件；例7 删除学号为95019的学生记录。DELETEFROM StudentWHERE Sno=95019;例8 删除所有的学生选课记录。DELETE FROM SC例9 删除计算机系所有学生的选课记录。DELETEFROM SCWHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno);更新操作与数据库的一致性删除95019学生同时删除95019学生的选课记录。