Access数据库sql.docx
- 文档编号:25942346
- 上传时间:2023-06-16
- 格式:DOCX
- 页数:26
- 大小:61.96KB
Access数据库sql.docx
《Access数据库sql.docx》由会员分享,可在线阅读,更多相关《Access数据库sql.docx(26页珍藏版)》请在冰豆网上搜索。
Access数据库sql
学生表:
createtablestudent
(snochar(8)primarykey,
snamechar(10),
ssexchar
(1),
sageint,
sdeptchar(20))
课程表:
createtablecourse
(cnochar(3)primarykey,
cnamechar(20),
cpnochar(3),
ccreditint)
学生选课表:
createtablesc
(snochar(8),
cnochar(3),
gradefloat,
primarykey(sno,cno)
);
查询仅涉及一个表:
n一、选择表中的若干列
n二、选择表中的若干元组
n三、ORDERBY子句
n四、聚集函数
n五、GROUPBY子句
n一、选择表中的若干列:
1.查询指定列:
[例1]查询全体学生的学号与姓名。
selectsno,sname
fromstudent;
[例2]查询全体学生的姓名、学号、所在系。
selectsname,sno,sdept
fromstudent;
2.选出所有属性列:
n在SELECT关键字后面列出所有列名
n将<目标列表达式>指定为“*”
[例3]查询全体学生的详细记录。
selectsno,sname,ssex,sage,sdept
fromstudent;
或者:
select*
fromstudent;
3.SELECT子句的<目标列表达式>可以为:
§算术表达式
§字符串常量
§函数
§列别名
[例4]查全体学生的姓名及其出生年份。
selectsname,2015-sage
fromstudent;
[例5]查询全体学生的姓名、出生年份和所有学院,要求用前两个字表示所有学院名。
SELECTsname,"YearofBirth:
",2015-sage,Left(sdept,2)
FROMstudent;
使用列别名改变查询结果的列标题:
SELECTsnameasname,"YearofBirth:
"asbirth,2015-sageasbirthday,Left(sdept,2)asdepartment
FROMstudent;
n二、选择表中的若干元组
1.消除取值重复的行
如果没有指定DISTINCT关键词,则缺省为ALL
[例6]查询选修了课程的学生学号。
SELECTsnoFROMsc;
相当于:
SELECTallsnoFROMsc;
指定DISTINCT关键词,去掉表中重复的行。
SELECTdistinctsno
FROMsc;
常用查询条件:
(1)比较大小
[例7]查询“卫管学院”全体学生的名单。
SELECT*
FROMstudent
wheresdept="卫管学院";
[例8]查询所有年龄在20岁以下的学生姓名及其年龄。
SELECTsname,sage
FROMstudent
wheresage<20;
[例9]查询考试成绩有不及格的学生的学号。
SELECTdistinctsno
FROMsc
wheregrade<60;
(2)确定范围
v谓词:
BETWEEN…AND…
NOTBETWEEN…AND…
[例10]查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄.
SELECTsname,sdept,sage
FROMstudent
wheresagebetween20and23;
[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECTsname,sdept,sage
FROMstudent
wheresagenotbetween20and23;
(3)确定集合
v谓词:
IN<值表>,NOTIN<值表>
[例12]查询“卫管学院”、“生工学院”和“全科教育学院”学生的姓名和性别。
SELECTsname,ssex
FROMstudent
wheresdeptin("卫管学院","生工学院",”全科教育学院”);
[例13]查询不是“卫管学院”、“生工学院”和“全科教育学院”的学生的姓名和性别。
SELECTsname,ssex
FROMstudent
wheresdeptnotin("卫管学院","生工学院","全科教育学院");
(4)字符匹配
v谓词:
[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]
1)匹配串为固定字符串
[例14]查询学号为1427002的学生的详细情况。
SELECT*
FROMstudent
wheresnolike"1427002";
等价于:
SELECT*
FROMstudent
wheresno="1427002";
2)匹配串为含通配符的字符串
[例15]查询所有姓刘学生的姓名、学号和性别。
SELECT*
FROMstudent
wheresnamelike"刘*";
[例16]查询姓"杜"且全名为三个汉字的学生的姓名。
SELECT*
FROMstudent
wheresnamelike"杜?
?
";
[例17]查询名字中第2个字为"朋"字的学生的姓名和学号。
SELECT*
FROMstudent
wheresnamelike"?
朋*";
[例18]查询所有不姓刘的学生姓名。
SELECT*
FROMstudent
wheresnamenotlike"刘*";
(5)涉及空值的查询
n谓词:
ISNULL或ISNOTNULL
n“IS”不能用“=”代替
[例19]某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。
查询缺少成绩的学生的学号和相应的课程号。
SELECTsno,cno
FROMsc
wheregradeisnull;
[例20]查所有有成绩的学生学号和课程号。
SELECTsno,cno
FROMsc
wheregradeisnotnull;
(6)多重条件查询
v逻辑运算符:
AND和OR来联结多个查询条件
•AND的优先级高于OR
•可以用括号改变优先级
v可用来实现多种其他谓词
•[NOT]IN
•[NOT]BETWEEN…AND…
[例21]查询卫管学院年龄在19岁以下的学生姓名。
SELECTsname
FROMstudent
wheresdept="卫管学院"andsage<19;
改写[例12]
[例12]查询“卫管学院”、“生工学院”和“全科教育学院”学生的姓名和性别。
SELECTsname,ssex
FROMstudent
wheresdept="卫管学院"orsdept="生工学院"orsdept="全科教育学院";
三、ORDERBY子句
vORDERBY子句
n可以按一个或多个属性列排序
n升序:
ASC;降序:
DESC;缺省值为升序
v当排序列含空值时
nASC:
排序列为空值的元组最先显示
nDESC:
排序列为空值的元组最后显示
[例22]查询选修了003号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECTsno,grade
FROMsc
wherecno="003"
orderbygradedesc;
例23]查询全体学生情况,查询结果按所在学院升序排列,同一学院中的学生按年龄降序排列。
SELECT*
FROMstudent
orderbysdept,sagedesc;
四、聚集函数
v聚集函数:
§计数
COUNT([DISTINCT|ALL]*)
COUNT([DISTINCT|ALL]<列名>)
§计算总和
SUM([DISTINCT|ALL]<列名>)
§计算平均值
AVG([DISTINCT|ALL]<列名>)
§最大最小值
MAX([DISTINCT|ALL]<列名>)
MIN([DISTINCT|ALL]<列名>)
[例24]查询学生总人数。
SELECTcount(*)
FROMstudent;
[例25]计算001号课程的学生平均成绩。
SELECTavg(grade)
FROMsc
wherecno="001";
[例26]查询选修001号课程的学生最高分数。
SELECTsname,max(grade)
FROMsc,student
wherecno="001"andstudent.sno=sc.sno;
[例27]查询学生1427001选修课程的总学分数。
SELECTsum(ccredit)
FROMsc,course
wheresno="1427001"ando=o;
五、GROUPBY子句
vGROUPBY子句分组:
细化聚集函数的作用对象
§未对查询结果分组,聚集函数将作用于整个查询结果
§对查询结果分组后,聚集函数将分别作用于每个组
§作用对象是查询的中间结果表
§按指定的一列或多列值分组,值相等的为一组
[例28]求各个课程号及相应的选课人数。
SELECTcno,count(sno)
fromsc
groupbycno;
[例29]查询选修了3门及以上课程的学生学号。
SELECTsno
fromsc
groupbysno
havingcount(*)>=3;
vHAVING短语与WHERE子句的区别:
§作用对象不同
§WHERE子句作用于基表或视图,从中选择满足条件的元组
§HAVING短语作用于组,从中选择满足条件的组。
连接查询:
一、等值与非等值连接查询
二、自身连接
三、外连接
四、复合条件连接
一、等值与非等值连接查询
v等值连接:
连接运算符为=
[例30]查询每个学生及其选修课程的情况
SELECT*
fromsc,student
wherestudent.sno=sc.sno;
v自然连接:
[例31]对[例30]用自然连接完成。
SELECTstudent.sno,sname,ssex,sage,sdept,cno,grade
fromsc,student
wherestudent.sno=sc.sno;
二、自身连接
v自身连接:
一个表与其自己进行连接
v需要给表起别名以示区别
v由于所有属性名都是同名属性,因此必须使用别名前缀
[例32]查询每一门课的间接先修课(即先修课的先修课)
selecto,second.cpno
fromcoursefirst,coursesecond
wherefirst.cpno=o;
三、外连接
v外连接与普通连接的区别
§普通连接操作只输出满足连接条件的元组
§外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出
[例33]改写[例30]
左连接:
SELECT*
fromstudentleftjoinscon(student.sno=sc.sno);
右连接:
SELECT*
fromscrightjoinstudenton(student.sno=sc.sno);
四、复合条件连接
v复合条件连接:
WHERE子句中含多个连接条件
[例34]查询选修002号课程且成绩在90分以上的所有学生。
SELECTstudent.sno,sname
fromsc,student
wherestudent.sno=sc.snoando="002"andgrade>=90;
[例35]查询每个学生的学号、姓名、选修的课程名及成绩。
SELECTstudent.sno,sname,cname,grade
fromsc,student,course
wherestudent.sno=sc.snoando=o;
v嵌套查询概述
§一个SELECT-FROM-WHERE语句称为一个查询块
§将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
3.4.3嵌套查询
一、带有IN谓词的子查询
二、带有比较运算符的子查询
三、带有ANY(SOME)或ALL谓词的子查询
四、带有EXISTS谓词的子查询
一、带有IN谓词的子查询
[例36]查询与“杜立琛”在同一个系学习的学生。
selectsno,sname,sdept
fromstudent
wheresdeptin(
selectsdept
fromstudent
wheresname="杜立琛");
用自身连接:
selects1.sno,s1.sname,s1.sdept
fromstudents1,students2
wheres1.sdept=s2.sdeptands2.sname="杜立琛";
[例40]查询选修了课程名为“计算机基础”的学生学号和姓名
SELECTSno,sname③最后在Student关系中取出Sno和Sname
FROMstudent
WHEREsnoIN
(SELECTsno②然后在SC关系中找出选修了3号课程的学生学号
FROMsc
WHEREcnoIN
(SELECTcno①首先在Course关系中找出“计算机基础”的课程号
FROMcourse
WHEREcname="计算机基础"
));
用连接查询实现[例40]
SELECTstudent.sno,sname
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoAND
SC.Cno=Course.CnoAND
Course.Cname='计算机基础'
;
二、带有比较运算符的子查询
v当能确切知道内层查询返回单值时,可用比较运
算符(>,<,=,>=,<=,!
=或<>)。
例:
假设一个学生只可能在一个系学习,并且必须属于一个系,则在[例36]可以用=代替IN:
SELECTSno,Sname,Sdept
FROMStudent
WHERESdept=
(SELECTSdept
FROMStudent
WHERESname='杜立琛');
[例41]找出每个学生超过他选修课程平均成绩的课程号。
SELECTSno,Cno
FROMSCx
WHEREGrade>=(SELECTAVG(Grade)
FROMSCy
WHEREy.Sno=x.Sno);
三、带有ANY(SOME)或ALL谓词的子查询
谓词语义
§ANY:
任意一个值
§ALL:
所有值
需要配合使用比较运算符
>ANY大于子查询结果中的某个值
>ALL大于子查询结果中的所有值
>=ANY大于等于子查询结果中的某个值 >=ALL大于等于子查询结果中的所有值 <=ANY小于等于子查询结果中的某个值 <=ALL小于等于子查询结果中的所有值 =ANY等于子查询结果中的某个值 =ALL等于子查询结果中的所有值(通常没有实际意义) ! =(或<>)ANY不等于子查询结果中的某个值 ! =(或<>)ALL不等于子查询结果中的任何一个值 [例42]查询其他系中比“护理学院”某一学生年龄小的学生姓名和年龄 SELECTSname,Sage FROMStudent WHERESage FROMStudent WHERESdept='护理学院') ANDSdept<>'护理学院'; 用聚集函数实现[例42] SELECTSname,Sage FROMStudent WHERESage< (SELECTMAX(Sage) FROMStudent WHERESdept='护理学院') ANDSdept<>'护理学院'; 四、带有EXISTS谓词的子查询 1.EXISTS谓词n 存在量词$ n带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。 Ø若内层查询结果非空,则外层的WHERE子句返回真值 Ø若内层查询结果为空,则外层的WHERE子句返回假值 n由EXISTS引出的子查询,其目标列表达式通常都用*,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义v 2.NOTEXISTS谓词Ø 若内层查询结果非空,则外层的WHERE子句返回假值 若内层查询结果为空,则外层的WHERE子句返回真值 [例44]查询所有选修了1号课程的学生姓名。 思路分析: n本查询涉及Student和SC关系 n在Student中依次取每个元组的Sno值,用此值去检查SC关系 n若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其Cno='1',则取此Student.Sname送入结果关系 n用嵌套查询 SELECTSname FROMStudent WHEREEXISTS( SELECT* FROMSC WHEREsc.Sno=Student.SnoANDCno='001'); 用连接运算 SELECTSname FROMStudent,SC WHEREStudent.Sno=SC.SnoANDSC.Cno='001'; [例45]查询没有选修1号课程的学生姓名。 SELECTSname FROMStudent WHERENOTEXISTS( SELECT* FROMSC WHERESno=Student.SnoANDCno='001'); 不同形式的查询间的替换 n一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换 n所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换 v用EXISTS/NOTEXISTS实现全称量词(难点) SQL语言中没有全称量词"(Forall) 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词: (∀x)P≡﹁(∃x(﹁P)) [例39]查询与“刘晨”在同一个系学习的学生。 可以用带EXISTS谓词的子查询替换: SELECTSno,Sname,Sdept FROMStudentS1 WHEREEXISTS( SELECT* FROMStudentS2 WHERES2.Sdept=S1.SdeptANDS2.Sname=‘刘晨’); [例46]查询选修了全部课程的学生姓名。 selectsname fromstudent wherenotexists(select* fromcourse wherenotexists(select* fromsc wheresc.sno=student.snoando=o)); 用EXISTS/NOTEXISTS实现逻辑蕴函(难点) §SQL语言中没有蕴函(Implication)逻辑运算 §可以利用谓词演算将逻辑蕴函谓词等价转换为: [例47]查询至少选修了学生200215122选修的全部课程的学生号码。 解题思路: n用逻辑蕴函表达: 查询学号为x的学生,对所有的课程y,只要200215122学生选修了课程y,则x也选修了y。 n形式化表示: 用P表示谓词“学生200215122选修了课程y” 用q表示谓词“学生x选修了课程y” 则上述查询为: (∀y)p→q 等价变换: 变换后语义: 不存在这样的课程y,学生200215122选修了y,而学生x没有选。 用NOTEXISTS谓词表示: SELECTDISTINCTSno FROMSCSCX WHERENOTEXISTS( SELECT* FROMSCSCY WHERESCY.Sno='200215122'AND NOTEXISTS( SELECT* FROMSCSCZ WHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno)); 3.4.4集合查询 v集合操作的种类: §并操作UNION §交操作INTERSECT §差操作EXCEPT v参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同 [例48]查询“卫管学院”的学生及年龄不大于19岁的学生。 方法一: SELECT* FROMStudent WHERESdept='卫管学院' UNION SELECT* FROMStudent WHERESage<=19; nUNION: 将多个查询结果合并起来时,系统自动去掉重复元组。 nUNIONALL: 将多个查询结果合并起来时,保留重复元组 方法二: SELECTDISTINCT* FROMStudent WHERESdept='CS'ORSage<=19; [例49]查询选修了课程1或者选修了课程2的学生。 SELECTSno FROMSC WHERECno='001' UNION SELECTSno FROMSC WHERECno='002'; 3.4.5SELECT语句的一般格式 SELECT[ALL|DISTINCT] <目标列表达式>as[别名][,<目标列表达式>as[别名]]… FROM<表名或视图名>as[别名] [,<表名或视图名>as[别名]]… [WHERE<条件表达式>] [GROUPBY<列名1> [HAVING<条件表达式>]] [ORDERBY<列名2>[ASC|DESC] 3.5数据更新 3.5.1插入数据 3.5.2修改数据 3.5.3删除数据 3.5.1插入数据 v两种插入数据方式 1.插入元组 2.插入子查询结果 Ø可以一次插入多个元组 一、插入元组 v语句格式 INSERT IN
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Access 数据库 sql