32数据定义.docx
《32数据定义.docx》由会员分享，可在线阅读，更多相关《32数据定义.docx（32页珍藏版）》请在冰豆网上搜索。
32数据定义
3.2数据定义
3.2.1定义、删除与修改基本表
一、定义基本表
格式：
CREATTABLE〈表名〉（〈列名〉〈数据类型〉[列级完整性约束条件]
[，〈列名〉〈数据类型〉[列级完整性约束条件]]…
[，〈表级完整性约束条件〉]；
例1建立一个“学生”表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。
其中学号不能为空，值是唯一的，并且姓名取值也唯一。
CREATTABLEStudent
（SnoCHAR（5）NOTNULLUNIQUE,/*列级完整性约束条件，
SnameCHAR（20）UNIQUE,Sno取值唯一，不许取空值*/
SsexCHAR
（1）,
SageINT,
SdeptCHAR（15））;
二、修改基本表
格式：
ALTERTABLE〈表名〉
[ADD〈新列名〉〈数据类型〉[完整性约束]]
[DROP]<完整性约束>]
[MODIFY<列名><数据类型>];
例2向Student表增加“入学时间”列，其数据类型为日期型。
ALTERTABLEStudentADDScomeDATE;
例3将年龄的数据类型改为半字长整数。
ALTERTABLEStudentMODIFYSageSMALLINT;
例4删除学生姓名必须取唯一值的约束。
ALTERTABLEStudentDROPUNIQUE（Sname）;
三、删除基本表
格式：
DROPTABLE<表名>
例5删除Student表
DROPTABLEStudent
3.2.2建立与删除索引
一、建立索引
格式：
CREATE[UNIQUE][CLUSTER]INDEX<索引名>
ON<表名>（<列名>[<次序>][<列名>[<次序>]]…）
例6
CREATETABLEUNIQUEINDEXStusnoONStudent（Sno）;
CREATETABLEUNIQUEINDEXCoucnoONCourse（Cno）;
CREATETABLEUNIQUEINDEXScnoONSC（SnoASC,CnoDESC）;
二、删除索引
格式：
DROPINDEX<索引名>;
例7DROPINDEXStusno
3.3查询
格式：
SELECT[ALL∣DISTINCT]<目标列表达式>[,<目标列表达式>]…
FROM<表名或视图名>[,<表名或视图名>]…
[WHERE<条件表达式>]
[GROUPBY<列名1>[HAVING<条件表达式>]]
[ORDERBY<列名2>[ASC∣DESC];
学生-课程数据库中包括三个表
学生表:
Student（Sno,Sname,Ssex,Sage,Sdpt）
课程表:
Course（Cno,Cname,Cpno,Ccredit）
选课表:
SC（Sno,Cno,Grade）
3.3.1单表查询
一、选择表中的若干列
例1查询全体学生的学号与姓名。
SELECTSno,Sname
FROMStudent;
例2查询全体学生的姓名、学号、所在系。
SELECTSname,Sno,Sdept
FROMStudent;
例3查询全体学生的详细记录。
SELECT*
FROMStudent;
例4查询全体学生的姓名及出生年份。
SELECTSname,2001-Sage,FROMStudent;
Sname2001-Sage
李勇1980
刘晨1981
王敏1982
张立1981
例5查询全体学生的姓名、出生年份、所在系（小写）。
SELECTSname,“YearofBirth:
”,2001-Sage,ISLOWER（Sdept）
FROMStudent;
Sname“YearofBirth:
”2001-SageISLOWER（Sdept）
李勇YearofBirth:
1980cs
刘晨YearofBirth:
1981is
王敏YearofBirth:
1982ma
张立YearofBirth:
1981is
SELECTSnameNAME,“YearofBirth:
”BIRTH,2001-SageBIRTHDAY,ISLOWER（Sdept）DEPARTMENT
FROMStudent;
Sname“YearofBirth:
”2001-SageISLOWER（Sdept）
李勇YearofBirth:
1980cs
刘晨YearofBirth:
1981is
王敏YearofBirth:
1982ma
张立YearofBirth:
1981is
二、选择表中的若干元组
假设SC表中有下列数据
SnoCnoGrade
95001192
95001285
95001388
95002290
95002380
例6查询选修了课程的学生学号。
SELECTSno
FROMSC;
结果为：
Sno
95001
95001
95001
95002
95002
SELECTDIATINCTSno
FROMSC;
结果为：
Sno
95001
95002
*****DISTINCT消除重复行******
例7查询计算机系全体学生的名单。
SELECTSname
FROMStudent
WHERESdept=’CS’;
例8查询所有年龄在20岁以下的学生姓名及年龄。
SELECTSname，Sage
FROMStudent
WHERESage<20;
或SELECTSname，Sage
FROMStudent
WHERENOTSage>=20;
例9查询考试成绩有不及格的学生的学号。
SELECTDISTINCTSno
FROMCourse
WHEREGrade<60;
例10查询年龄在20～23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
SELECTSname,Sdept,Sage
FROMStudent
WHERESageBETWEEN20AND23;
例11查询年龄不在20～23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
SELECTSname,Sdept,Sage
FROMStudent
WHERESageNOTBETWEEN20AND23;
例12查询信息系（IS）、数学系（MA）和计算机系（CS）学生的姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdeptIN（‘IN’,’MA’,’CS’）
例13查询不是信息系（IS）、数学系（MA）和计算机系（CS）学生的姓名和性别。
SELECTSname,Ssex
FROMStudent
WHERESdeptNOTIN（‘IN’,’MA’,’CS’）
·%代表任意长度的字符串。
·_代表任意单个字。
例14查询学号为95001的学生的详细情况。
SELECT*
FROMStudent
WHERESnoLIKE‘95001’
等价于：
SELECT*
FROMStudent
WHERESnoLIKE‘95001’
例15查询所有姓刘的学生的姓名、学号和性别。
SELECTSname,Sno,Ssex
FROMStudent
WHERESnameLIKE‘刘%’
例16查询姓“欧阳”且全名为三个汉字的学生的姓名。
SELECTSname
FROMStudent
WHERESnameLIKE‘欧阳__’;
例17查询名字中第2个字为“阳”字的学生的姓名和学号。
SELECTSname,Sno
FROMStudent
WHERESnameLIKE‘__阳%’;
例18查询所有不姓刘的学生的姓名。
SELECTSname,Sno,Ssex
FROMStudent
WHERESnameNOTLIKE‘刘%’
例19查询DB_Design课程的课程号和学分。
SELECTCno,Ccredit
FROMCourse
WHERECnameLIKE‘DB\_Design’ESCAPE‘\’
例20查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况。
SELECT*
FROMCourse
WHERECnameLIKE‘DB\_%__’ESCAPE‘\’;
例21查询缺少成绩的学生的学号和相应的课程号。
SELECLSno,Cno
FORMSC
WHEREGradeISNULL;
或SELECLSno,Cno
FORMSC
WHEREGradeISNULL;
例22查询所有有成绩的学生的学号和相应的课程号。
SELECLSno,Cno
FORMSC
WHEREGradeISNOTNULL;
例23查询计算机系年龄在20岁以下的学生姓名。
SELECTSname
FROMStudent
WHERESdept=’CS’ANDSage<20;
三、对查询结果排序
例24查询选修了3号课程的学生的学号及成绩，查询结果按分数的降序排列。
SELECTSno,Grade
FROMSC
WHERECno=’3’
ORDERBYGradeDESC;
例25查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
SELECT*
FROMStudent
ORDERBYSdept,SageDESC;
四、使用集函数
COUNT（[DISTINCT∣ALL]*）统计元组个数
COUNT（[DISTINCT∣ALL]<列名>）统计一列中值的个数
SUM（[DISTINCT∣ALL]<列名>）计算一列值的总和（数值型）
AVG（[DISTINCT∣ALL]<列名>）计算一列值的平均值（数值型）
MAX（[DISTINCT∣ALL]<列名>）求一列值的最大值（数值型）
MIN（[DISTINCT∣ALL]<列名>）求一列值的最小值（数值型）
例26查询学生总数。
SELECTCOUNT（*）
FROMStudent
例27查询选修了课程的学生人数。
SELECTCOUNT（DISTINCTSno）
FROMSC
例28计算1号课程的学生平均成绩。
SELECTAVG（Grade）
FROMSC
WHERECno=’1’;
例29查询选修1号课程的学生最高分数。
SELECTMAX（Grade）
FROMSC
WHERECno=’1’;
五、对查询结果分组
例30求各个课程号及相应的选课人数。
SELECTCno,COUNT（Sno）
FROMSC
GROUPBYCno;
CnoCOUNT（Sno）
122
234
344
433
548
例31查询选修了3门以上课程的学生学号。
SELECTSno
FROMSC
GROUPBYSno
HAVINGCOUNT（*）>3;
3.3.2连接查询
例32查询每个学生及其选修课程的情况。
SELECTstudent.*,SC.*
FROMStudent,SC
WHEREStudent.Sno=SC.Sno;
例33查询每个学生及其选修课程的情况（自然连接）。
SELECTstudent.Sno,Sname,Sage,Sdept,Cno,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.Sno;
例34查询每门课的间接先修课（即先修课的先修课）。
FIRST表（Course表）
CnoCnameCpnoCcredit
1数据库54
2数学2
3信息系统14
4操作系统63
5数据结构74
6数据处理2
7PASCAL语言64
SECOND表（Course表）
CnoCnameCpnoCcredit
1数据库54
2数学2
3信息系统14
4操作系统63
5数据结构74
6数据处理2
7PASCAL语言64
SELECLEFIRST.Cno,SECOND.Cpno
FROMCourseFIRSE,CourseSECOND
WHEREFIRSE.Cpno=SECOND.Cno;
结果为：
CnoCpno
17
35
外连接
SELECTstudent.Sno,Sname,Sage,Sdept,Cno,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.Sno（*）;
复合条件连接
例35查询选修2号课程且成绩在90分以上的所有学生。
SELECTStudent.Sno,Sname
FROMStudent,SC
WHEREStudent.Sno=SC.SnoAND
SC.Cno=’2’AND
SC.Ggrade>90;
多表连接
例36查询每个学生的学号、姓名、选修的课程名及成绩。
SELECEStudent.Sno,Sname,Cname,Grade
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno;
3.3.3嵌套查询
例37查询与“刘晨”在同一个系学习的学生。
SELECTSno,Sname,Sdept
FROMStudent
WHERESdeptIN
（SELECTSdept
FROMStudent
WHERESname=’刘晨’;
当结果为单值时可以用‘=’代替‘IN’
SELECTSno,Sname,Sdept
FROMStudent
WHERESdept=
（SELECTSdept
FROMStudent
WHERESname=’刘晨’;
用自然连接完成
SELECTS1.Sno,S1.Sname,S1.Sdept
FROMStudentS1,StudentS2
WHERES1.Sdept=S2.SdeptAND
S2.Sname=’刘晨’;
例38查询选修了课程名为“信息系统”的学生学号和姓名。
SELECTSno,Sname
FROMStudent
WHERESnoIN
（SELECTSno
FROMSC
WHERECnoIN
（SELECTCno
FROMCourse
WHERECname=’信息系统’）;
用连接查询
SELECTSno,Sname
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoAND
SC.Cno=Course.CnoAND
Course.Cname=’信息系统’;
带有ANY或ALL谓词的子查询
>ANY大于子查询结果中的某个值
>ALL大于子查询结果中的所有值
>=ANY大于等于子查询结果中的某个值
>=ALL大于等于子查询结果中的所有值
<=ANY小于等于子查询结果中的某个值
<=ALL小于等于子查询结果中的所有值
=ANY等于子查询结果中的某个值
=ALL等于子查询结果中的所有值（无意义）
!
=（或<>）ANY不等于子查询结果中的某个值
!
=（或<>）ALL不等于子查询结果中的任何一个值
例39查询其他系中比信息系某一学生年龄小的学生姓名和年龄。
SELECTSname,Sage
FROMStudent
WHERESageFROMStudent
WHERESdept=’IS’
ANDSdept<>’IS’
用集函数实现
SELECTSname.Sage
FROMStudent
WHERESage<
（SELECTMAX（Sage）
FROMStudent
WHERESdept=’IS’）
ANDSdept<>’IS’
例40查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。
SELECTSname,Sage
FROMStudent
WHERESage（SELECTSage
FROMStudent
WHERESdept=’IS’）
ANDSdept<>’IS’
用集函数实现
SELECTSname,Sage
FROMStudent
WHERESage<
（SELECTMIN（Sage）
FROMStudent
WHERESdept=’IS’）
ANDSdept<>’IS’
带有EXISTS谓词的子查询
例41查询所有选修了1号课程的学生姓名。
SELECTSname
FROMStudent
WHEREEXISTS
（SELECT*
FROMSC
WHERESno=Student.SnoANDCno=’1’）;
例42查询没有选修1号课程的学生姓名。
SELECTSname
FROMStudent
WHERENOTEXISTS
（SELECT*
FROMSC
WHERESno=Student.SnoANDCno=’1’）;
例43查询选修了全部课程的学生姓名。
SELECTSname
FROMStudent
WHERENOTEXISTS
（SELECT*
FROMCourse
WHERENOTEXISTS
（SELECT*
FROMSC
WHERESno=Student.Sno
ANDCno=Course.Cno）;
例44查询至少选修了学生95002选修的全部课程的学号号码。
SELECTDISTINCTSno
FROMSCSCX
WHERENOTEXISTS
（SELECT*
FROMSCSCY
WHERESCY.Sno=’95002’AND
NOTEXISTS
（SELECT*
FROMSCSCZ
WHERESCZ.Cno=SCY.Cno
3.3.4集合查询
例45查询计算机系的学生及年龄不大于19岁的学生。
SELECT*
FROMStudent
WHERESdept=’CS’
UNION
SELECT*
FROMStudent
WHERESage<=19;
例46查询选修了课程1或选修了课程2的学生。
****查询选修课程1的学生集合与选修课程2的学生集合的并集。
SELECTSno
FROMSC
WHERECno=’1’
UNION
SELECTSno
FROMSC
WHERECno=’2’;
例47查询计算机系的学生与年龄不大于19岁的学生的交集。
*****查询计算机系中年龄不大于19岁的学生。
SELECT*
FROMStudent
WHERESdept=’CS’AND
Sage<=19
例48查询选修课程1的学生集合与选修课程2的学生集合的交集。
*****查询既选修了课程1又选修了课程2的学生。
SELECTSno
FROMSC
WHERECno=’1’ANDSnoIN
（SELECTSno
FROMSC
WHERECno=’2’）;
例49查询计算机系的学生与年龄不大于19岁的学生的差集。
****查询计算机系中年龄大于19岁的学生。
SELECT*
FROMStudent
WHERESdept=’CS’AND
Sage>19;
3.4数据更新
SQL中数据更新包括插入数据、修改数据和删除数据三条语句。
3.4.1插入数据
格式1：
INSERT
INTO〈表名〉[（〈属性列1〉[，〈属性列2〉…]）]
VALUE（〈常量1〉[，〈常量2〉…]）
例1将一个新学生记录（学号：
95020；姓名：
陈冬；性别：
男；所在系：
IS；年龄：
18岁）插入到Student表中。
INSERT
INTOStudent
VALUE（‘95020’,‘陈冬’,‘男’,‘IS’,18）
例2插入一条选课记录（‘95020’,‘1’）。
INSERT
INTOSC（Sno,Cno）
VALUE（‘95020’,‘1’）
新插入记录在Grade列上取空值。
格式2：
INSERT
INTO〈表名〉[（〈属性列1〉[，〈属性列2〉…]）]
子查询；
例3对每个系，求学生的平均年龄，并把结果存入数据库。
CREATTABLEDeptage
（SdeptCHAR（15）
AvgageSMALINT）;
INSERT
INTODeptage（Sdept,Avgage）
SELECTSdept,AVG（Sage）
FROMStudent
GROUPBYSdept;
3.4.2修改数据
格式：
UPDATE〈表名〉
SET〈列名〉=〈表达式〉[，〈列名〉=〈表达式〉]…
[WHERE〈条件〉]；
例4将学生95001的年龄改为22岁。
UPDATEStudent
SETSage=22
WHERESno=’95001’
例5将所有学生的年龄增加1岁。
UPDATEStudent
SETSage=Sage+1;
例6将计算机系全体学生的成绩置零。
UPDATESC
SETGrade=0
WHERE‘CS’=
（SELECTSdept
FROMStudent
WHEREStudent,Sno=SC.Sno）;
3.4.3删除数据
格式：
DELETE
FROM〈表名〉
[WHERE〈条件〉]；
例7删除学号为95019的学生记录。
DELETE
FROMStudent
WHERESno=’95019’;
例8删除所有的学生选课记录。
DELETE
FROMSC
例9删除计算机系所有学生的选课记录。
DELETE
FROMSC
WHERE‘CS’=
（SELECTSdept
FROMStudent
WHEREStudent.Sno=SC.Sno）;
更新操作与数据库的一致性
删除95019学生同时删除95019学生的选课记录。
升级会员 