数据库系统原理与应用文档格式.docx
- 文档编号:19061297
- 上传时间:2023-01-03
- 格式:DOCX
- 页数:30
- 大小:40.85KB
数据库系统原理与应用文档格式.docx
《数据库系统原理与应用文档格式.docx》由会员分享,可在线阅读,更多相关《数据库系统原理与应用文档格式.docx(30页珍藏版)》请在冰豆网上搜索。
CREATECLUSTERINDEXStudentsnameOnStudent(SnameDESC);
SQL2008未能实现此功能,问题是不识别关键字cluster
3.9删除索引
DROPINDEXStudent.Stusno;
3.10查询全体学生的学号,姓名,及所在第
SELECTSno,Sname,Sdept
FROMStudent
3.11查询全体学生的所有信息
SELECT*
3.12查询全体学生的姓名及出生年分
SELECTSname,2015-Sage
FROMSTUDENT;
3.13指定一个字符常量,在年龄前面显示
SELECTSname,'
YearofBirth'
2015-Sage
FROMStudent;
3.14:
将出生年份给定一个别名
SELECTSname,2015-Sage'
3.15查询学生的学号,姓名和所在系,要求用小写字母表示所在的系
SELECTSno,Sname,LOWER(Sdept)
3.16查询所有选了课程的学生的学号,并消除重复的学号。
SELECTDISTINCTSno
FROMSC;
3.17查询计算机系的所有学生的学号和姓名
SELECTSno,Sname
WHERESdept='
Computer'
3.18查询成绩不及格的学生的学号
SELECTSno
FROMSC
WHEREGrade<
60
3.19查询年龄在~30岁之间的学生的姓名,所在第和年龄
SELECTSname,Sdept,Sage
WHERESageBETWEEN20AND30
3.20查询计算机系,数学系,和信息系的学生姓名和性别
SELECTSname,Ssex
WHEREsdeptIN('
CS'
MA'
IS'
);
/*WHEREsdept='
ORsdept='
*/
3.21查询既不是数学系也不是计算机系的学生姓名和性别
WHEREsdeptNOTIN('
基于字符串匹配的查询,
LIKE可以用来查找指定列与给定字符串常量匹配的元组。
从而实现模糊查询。
%(百分号):
代表任意长度(长度可以为)的字符串。
_(下划线):
代表任意单个字符
3.22查询信息系所有学生的情况
WHEREsdeptLIKE'
/*左侧语句等价于:
WHEREsdept='
3.23查询所有姓张的,第个字为“伟”的学生的姓名,学号和姓别
SELECTSname,Sno,Ssex
WHERESnameLIKE'
张_伟%'
;
当要查询的字符串本身就含有%或者_时,就需要用ESCAPE'
<
换码字符>
'
短语将符进行转义
3.24查询DB_Design课程的课程号及学分
SELECTCno,Ccredit
FROMCourse
WHERECnameLIKE'
DB\_Design'
ESCAPE'
\'
/*ESCAPE'
表示'
为换码字符,这样匹配串中紧跟在’\‘后的字符就不再具有通配符的含义,即将通配符_转换成普通字符*/
3.25查询没有考试成绩的学生的学号及相应课程号(基于空值的查询)
SELECTSno,Cno
WHEREGradeISNULL
3.26查询计算机系年龄在岁以下的学生的学号、姓名和姓名(基于多条件的查询)
SELECTSname,Ssex,Sage
ANDSage<
20
对查询结果排序:
如果对多个列进行排序,则先对第一列进行排序,以此类推
3.27查询选修了号课程的学生的学号及成绩,查询结果按成绩的降序排列
SELECTSno,Grade
WHERECno='
101'
ORDERBYGradeDESC;
/*ASC为升序排序*/
3.28查询全体学生情况,查询结果按所在升序排序,同一系中的学生按年龄降序排序
ORDERBYsdept,SageDESC;
聚焦函数
对表中的数据进行计算,得到统计结果。
在SQL中提供的聚焦函数主要有如下几种
1.AVG2.MIN3.MAX4.SUM5.COUNT
3.29计算等候了号课程的学生的平均分
SELECTAVG(Grade)AS'
平均成绩'
2'
3.30查询成绩中的最低分/最高分
SELECTMIN(Grade)AS'
最低成绩'
3.31统计学生的总人数
SELECTCOUNT(*)AS'
总人数'
3.32统计选修了课程的学生的总人数
SELECTCOUNT(Sno)
查询结果分组(GROUPBY)
3.33查询每门课程的课程号及相应的选课人数
SELECTCno,count(Sno)/*使用聚焦函数求得每个分组中的学生人数*/
GROUPBYCno
3.34查询至少有门以上课程成绩在分以上的学生的学号。
WHEREGrade>
=90
GROUPBYSnoHAVINGCOUNT(*)>
3
连接查询:
在数据库应用中,经常需要从多个表中提取书当然信息,雨棚俗人查询就涉及多个表。
若一个查询涉及两个或两个以上的表,称之为连接查询。
连接查询的类别:
等值连接、自然连接、非等值连接、自身连接、外连接和复合条件连接查询
3.35查询每个学生及其等候课的情况(等值连接)。
SELECTStudent.*,SC.*
FROMStudent,SC
WHEREStudent.Sno=SC.Sno
3.36对例.35用自然连接完成(自然连接)。
SELECTStudent.Sno,Sname,Ssex,Sage,SC.Sno,Sage,sdept,Cno,Grade
注意:
若连接中没有给出连接谓词,则为笛卡尔积,连接结果会产生一些没有意义的元组。
3.37查询第门课的间接先修课(自身连接)
SELECTFirst.Cno,Second.Cno
FROMCourseFirst,CourseSecond
WHEREFirst.Cno=Second.Cno
3.38将例.36改为左外连接。
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudentLEFTOUTERJOINSC
ONStudent.Sno=Sc.Sno
复合条件查询:
WHERE子句有多个连接条件,称为复合条件连接
3.39查询等候了数据库原理课程且成绩在分以上的学生的学号和姓名。
SELECTStudent.Sno,Sname
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCname='
数据库'
ANDGrade>
90
嵌套查询:
在SQL中,一个SELECT-FROM-WHERE语句称为一个查询块,.将一个查询块嵌入到另外一个查询块的WHERE子句或者HAVING短语的条件中的查询称为嵌套桑菊银翘散查询块称为外层查询或父查询,下层的查询块称为内层查询或子查询。
SQL中允许多层嵌套查询,椙子查询的SELECT语句中不能使用ORDERBY子句。
ORDERBY子句只能在最终的结果中进行排序。
嵌套查询分为不书读百遍查询和书读百遍查询。
3.40查询选修了课程的学生的姓名。
SELECTSname
WHERESnoIN(
SELECTSno
FROMSC
WHERECno='
1'
该查询也可以用连接查询实现
FROMStudent,SC
WHEREStudent.Sno=SC.SnoANDCno='
3.41查询和张明在同一个系学习的学生的学号和姓名。
WHEREsdept=
(SELECTsdept
FROMStudent
WHERE
Sname='
ZHU'
)
3.42查询其他系中比计算机系某一个学生年龄小的学生姓名的年龄
SELECTSage,Sname
WHEREsdept<
>
'
ANY(SELECTSage
FROMStudent
WHEREsdept='
)
该查询等价于:
ANY(SELECTMAX(Sage)
WHEREsdept<
3.43查询每个学生超过他所选课程平均成绩的课程号(相关子查询)
使用课本的语句
查询出正确结果的语句
FROMSCx/*重命名查询表*/
(selectAVG(Grade)
FROMSCy
WHEREx.Cno=y.Cno)
SELECTSno,Cno,Grade
FROMSCx
=
FROMSCy)
带有EXISTS谓词的子查询
EXISTS谓词表示存在量词,可以用来判断查询是否存在数据。
由EXISTS引出的嵌套子查询不返回任何任何数据,只产生逻辑真或逻辑假。
与之相对应的表示不存在的谓词为NOTEXISTS.
3.44查询所有选修了3号课程的学生的姓名。
SELECTDISTINCTSname
WHEREEXISTS
(SELECT*
WHERESno=Student.SnoANDCno='
3'
3.45查询选修了全部课程的学生姓名
SELECTSname
WHERENOTEXISTS
(SELECT*
FROMCourse
WHERESno=Student.SnoANDCno=Course.Cno)
3.46查询至少选修了9550002选修的全部课程的学生学号
FROMSCSCX
FROMSCSCY
WHERESCY.Sno='
950002'
ANDNOTEXISTS
(SELECT*
FROMSCSCZ
WHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno)
集合查询:
由于SELECT语句的查询结果是无级的集合,所以在SQL中,可以用UNION语句把两个或两个以上的查询结果合并成一个结果集
语法格式:
SELECT_STATEMENT
UNION[ALL]
合并结果结果有以下几点限制:
参加UNION运算的各查询结果的列数必须相同,且对应列的数据类型也相同。
最后结果集中的列名来自第一个SELECT语句
ORDERBY子句只能出现在最后,将对最后的结果集排序
在外挂媳妇就是甲方,浪费业务欺负从最后的结果集中删除重复的元组,除非使用ALL关键字说明要保留重复元组。
3.47查询选修了1课程和2课程的所有的学生的学号。
UNION
数据库的更新
插入数据:
INSERT语句有两种形式:
一种是向表中插入一条元组,另一种是向表中插入子查询的结果
向表中插入一条新的元组,需要注意以下几点:
INTO子句中没有出现的属性列,新元组在这些列上为空值,前提条件是该允许为空
若INTO子句没有指明属性列,则新插入的元组必须在每个属性列上有值。
INTO子句中属性列的顺序不一定与表中的相同,但必须与VALUES子句中的常量一一对应且类型一致
字符型和日期型值插入时要用单引号括起来
插入的新值不能违反完整性约束
3.48在Student表中插入一条新元组(我的设计表是添加了日期列)
INSERTINTOStudentVALUES('
980012'
张倩'
20'
1990-11-11'
3.49设数据库表中已经建立了一个与Student表结构的相同的表Student_temp.要求在Student_temp表中保存所有计算机学生情况
INSERTINTOStudent_temp
修改数据
3.50将每个学生的年龄增加1岁
UPDATEStudentSETSage=Sage+1;
3.51将所有计算机系的学生的成绩置为0
UPDATESC
SETGrade=0
WHERESnoIN
(SELECTSno
WHEREsdept='
删除数据
3.52删除所有选修了2号课程的学生选课记录。
视图:
是从一个或多个基本表(视图)导出的表,是关系数据库系统为用户提供从不同角度观察和使用数据库中数据的一种机制,与基本表不同的是,视图是一个虚表,在数据库中只存放视图的定义,而不存放视图所对应的数据,这些数据仍存放在地图工人基本表中。
基本表中数据的变化可以通过视图表现出来
创建视图:
其中<
SELECT子查询>
可以是做任意复杂的子查询,但一般不有ORDERBY子句和DISTINCT关键字。
WITHCHECKOPTION用于强制所有通过视图更新的数据必须满足<
SELECT>
子查询中指定的条件。
定义视图时注意,下列情况必须指定列名
●当目标列是聚焦函数或列表达式时
●多表连接时选出了几个同名列作为视图的字段。
●需要在视图中为某列指定一个更合适的列名
3.54建立计算机系学生的视图,并要求进行修改和插入操作时需保证视图只能对计算机系学生进行操作。
CREATEVIEWc_Student
AS
SELECTSno,Sname,Ssex,Sage
wheresdept='
WITHCHECKOPTION
3.55建立选择了2号课程且成绩在80分以上的视图
CREATEVIEWstudent_course_2(Sno,Sname,Ssex,Sage,Cno,Grade)
SELECTstudent.Sno,Sname,Ssex,Sage,Cno,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.SnoANDGrade>
=80ANDCno='
3.56建立一个反映学生出生年份的视图
CREATEVIEWstudent_birth(Sno,Sname,Sbirth)
AS
SELECTStudent.Sno,Sname,2015-Sage
删除视图
DROPVIEW<
视图名>
查询视图
3.57在计算机系的视图c_student上查询年龄在18~20岁之间的学生的学号和姓名。
SELECTSno,Sname
FROMc_Student
WHERESageBETWEEN18AND20
/*WHERESage>
=10ANDSage<
=20*/
DBMS在查询视图时,实际上是透过视图对基本表的查询,所以需要将对视图的查询转换为对基本表查询。
一般情况下有两种转换方法:
实体化视图和视图消解法。
实体化视图:
首先进行有效性检查,检查所查询的视图是否存在;
若存在,则执行视图定义,将视图临时实体化,生成临时表;
然后将查询视图转换为查询临时表,在临时表上进行查询,查询完毕后删除被实体化的视图(临时表)
视图消解法:
首先进行有效性检查,检查查询的表、视图等是否存在,如果存在,则从数据字典中取出视图的定义,把视图定义中的子查询与用户的查询结合起来,转换成等价的对基本表的查询,然后执行修正后的查询。
将3.57进行转换后的执行语句为:
ANDSageBETWEEN18AND20
3.58在3.55的学生视图上查询成绩在90分以上的好学生的学号和姓名。
FROMstudent_course_2
WHERESsex='
=90
更新视图:
通过视图进行删除、修改、插入的操作
3.59在3.54的视图中将学号为950002的学生的姓名改为“张良”
UPDATEc_Student
SETSname='
张良'
WHERESno='
注:
在本例中,由于视图c_student在定义时使用了WITHCHECKOPTION选项,所以通过视图对数据进行修改时,限定要修改的数据必须是计算机系的学生。
3.60在3.54的视图中插入一条学生的信息
INSERTINTOc_Studentvalues('
950013'
李明'
19'
同上面类似,插入的数据也受到的WITHCHECKOPTION选项的限制。
3.61在3.54的视图中删除9800013的学生信息
DELETE
9800013'
视图的作用:
1.能够简化用户的操作
2.使用户能以多种角度看待同一数据
3.对重构数据库提供了一定程度的逻辑独立性
4.能够对数据提供安全保护
SQL提供的安全性和完整性
数据库安全性概述:
是指保护数据库防止不合法的使用所造成的数据泄露,更改或破坏。
DBMS自主存取控制机制是数据库安全的一个重要保证,它确保具有数据库使用权限的用户访问数据库并进行权限范围内的操作。
DBMS自主存取控制机制由以下几个部分组成
●定义用户权限
●合法权限检查
授权:
3.62把查询Student表和修改姓名(Sname)列的权限授予用户user1
GRANTUPDATE(Sname),SELECTONStudentTOUser1
3.63把对表Student表,Course表和SC表的查询、修改、插入和删除等全部权限授予用户user1和用户user2
GRANTALLPRIVILIGESKONStudent,Course,SCTOuser1,user2
3.64把对表SC的查询权限授予所有用户
GRANTSELECTONSCTOPUBLIC
3.65把对用户SC的查询权限授予用户user3,并给用户user3授予有再授予的权限。
GRANTSELECTONSCTOuser3
WITHGRANTOPTION
3.66用户user3把查询SC表的权限授予用户user4
GRANTSELECTONSCTOuser4
收回权限:
授予的权限可以由DBA基其他授权者用REVOKE语句收回。
3.67把用户user1修改姓名的权限收回
REVOKEUPDATE(Sname)ONStudentFORMuser1
3.68把用户user3查询SC表的权限收回
REVOKESELECTONSCFROMuser3
由于在3.66中用户user3将对SC表的查询权限授予了用户user4,因此,把用户user3的查询权限
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 原理 应用