《数据库管理与应用》课件-项目二数据查询与操作

数据库原理与应用1.数据查询基础学号姓名总学分98001陈辰2298002李四30………………学号课程号成绩98001kc0015998001kc0028598001kc00377………………课程号课程名学分kc001c语言3kc009数据库项目开发9………………课程表选课表学生表比速度游戏：陈辰的c语言的成绩是多少？倒计时10秒！学号姓名总学分98001陈辰2298002李四30………………学号课程号成绩98001kc0015998001kc0028598001kc00377………………课程号课程名学分kc001c语言3kc009数据库项目开发9………………课程表选课表学生表比速度游戏：陈辰的c语言的成绩是多少？成绩多表查询比速度游戏：李冰冰的年龄是多少？单表查询数据是怎么查到的？（1）确定数据源

（2）找到所在行

（3）找到所需列1.语法格式

select<目标列>

from<数据源>

where<选择行的条件>select基本语法格式：（3）找到所需列（2）找到所在行（1）确定数据源2.查询语句的本质本质：把人工查询数据的行为转为执行查询语句（select）。数据库原理与应用2.执行查询语句1.回顾（3）列出所需列（2）找到所在行（1）确定数据源

select<目标列>

from<数据源>

where<选择行的条件>select基本语法格式：（3）列出所需列（2）找到所在行（1）确定数据源2.案例分析2.案例分析select学号,姓名from

学生where

性别='女'（3）列出所需列（2）找到所在行（1）确定数据源3.执行查询语句视频：执行查询语句4.小结（2）语句书写区（5）查询结果显示区（1）当前连接的数据库（4）执行代码（3）分析语法数据库原理与应用3.单表查询1.回顾（3）列出所需列（2）找到所在行（1）确定数据源select学号,姓名from

学生where

性别='女'2.单表查询学生（学号，姓名，年龄，所在系）课程（课程号，课程名，先行课）选课（学号，课程号，成绩）2.单表查询例1：求数学系学生的学号和姓名select学号,姓名from学生where所在系='数学'selectdistinct

学号from选课2.单表查询例2：求选修了课程的学生学号select学号from选课去除重复行：distinct例3：查询计算机系全体学生的基本信息select*from学生where所在系='计算机'表中所有列：*2.单表查询例4：查询全体学生的学号、姓名以及出生年份2.单表查询例4：查询全体学生的学号、姓名以及出生年份select学号,姓名,2017-年龄as出生年份from学生select学号,姓名,2017-年龄from学生出生年份=2017-年龄产生新列名：as或=2.单表查询例5：查询考试成绩有不及格的学生的学号selectdistinct学号from选课where成绩<602.单表查询例6：查询计算机系，数学系，物理系三个系的全体学生的学号，姓名，所在系select学号,姓名,所在系from学生where所在系='数学‘or所在系='计算机'or所在系='物理'select学号,姓名,所在系from学生where所在系in('数学','计算机','物理')或者列in（值1，值2，值3，……）2.单表查询通配符：%（任意字符串），_（任意一个字符）例7：查询所有姓李的学生学号，姓名select学号,姓名from学生where姓名like'李%'例8：查询所有姓李的且为单名的学生学号，姓名select学号,姓名from学生where姓名like'李_'2.单表查询例9：查询课程名以“100%”开头的课程情况select*from课程where课程名like‘100\%%'escape'\'2.单表查询转义：escape‘特殊字符’\,&，￥，#，@等值(不)为空：is（not）null2.单表查询例10：求缺少成绩的学生学号、课程号、成绩select学号,课程号from选课where成绩isnull2.单表查询例11：求选修了课程号为“cr001”且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出select学号,成绩*0.8from选课where课程号=‘cr001’and成绩>=80and成绩<=90select学号,成绩*0.8from选课where课程号='cr001'and

成绩between80and90列>Aand列<B等价于列betweenAandB（1）去除重复行：distinct3.小结（2）表中所有列：*（3）产生新列名：as或=（4）列in（值1，值2，……）等价于列=值1or列=值2or……（5）通配符：%（任意字符串），_（任意一个字符）（6）转义：escape‘特殊字符’（7）列>Aand列<B等价于列betweenAandB（8）值(不)为空：is（not）null思考：查询数学系或计算机系姓“陈”的学生的信息。倒计时3分钟！4.应用例12：检索数学系或计算机系姓“陈”的学生的信息select*

from学生

where所在系in('数学','计算机')and

姓名like'陈select*from学生where所在系in('数学','计算机')and姓名like'陈%'（1）数据源是？——学生（2）选择行的条件是?1）所在系为数学或计算机——列in（值1，值2，……）2）姓陈——使用通配符

（3）所需的列有哪些？——学生的信息（*）

数据库原理与应用4.连接查询1.连接查询学生（学号，姓名，年龄，所在系）课程（课程号，课程名，先行课）选课（学号，课程号，成绩）1.连接查询例1：查询学生的学号，姓名及所选修的课程号、成绩select学生.学号,姓名,课程号,成绩

from学生,选课select学生.学号,姓名,课程号,成绩

from学生,选课where学生.学号=选课.学号

自然连接笛卡尔积select学生.学号,姓名,课程名,成绩from学生,选课，课程where学生.学号=选课.学号and课程.课程号=选课.课程号多个连接条件用and连接1.连接查询例2：查询学生的学号，姓名及所选修的课程名、成绩例3：查询考试成绩有不及格的学生的学号、姓名1.连接查询selectdistinct学生.学号,姓名from学生,选课where成绩<60and

学生.学号=选课.学号连接条件和查询条件用and连接例4：查询每一门课的间接先行课（即先行课的先行课）连接操作不只在两个表之间进行，一个表内也可以进行自身连接课程号课程名先行课c1 计算机导论NULLc2 C语言 c1c3 数据结构c2c4 数据库原理c3c5 软件工程c4c6 高等数学NULLc7 DB_Designc3A课程号课程名先行课c1 计算机导论NULLc2 C语言 c1c3 数据结构c2c4 数据库原理c3c5 软件工程c4c6 高等数学NULLc7 DB_Designc3B2.自连接例4：查询每一门课的间接先行课（即先行课的先行课）selectA.课程号,A.课程名,B.先行课from课程A,课程BwhereA.先行课=B.课程号AB2.自连接内连接：结果集中只保留了符合连接条件的元组，而排除了两个表中没有匹配的元组情况，前面所举的例子均属内连接。左外部连接：结果集中保留连接表达式左表中的非匹配记录。”＊=”右外部连接：结果集中保留连接表达式右表中的非匹配记录。“=＊”注：外部连接中不匹配的分量用NULL表示3.外连接例5：所有学生的选课情况（包括没有选课的学生）select学生.学号,课程号,成绩from学生,选课where学生.学号*=选课.学号

3.外连接（1）连接查询——内连接4.小结（2）自连接（3）外连接（*=，=*）数据库原理与应用5.嵌套查询学生（学号，姓名，年龄，所在系）课程（课程号，课程名，先行课）选课（学号，课程号，成绩）1.嵌套查询问题：查询“陈辰”所在系的全体学生的学号，姓名头脑风暴：（1）自连接？（2）内连接？（3）人工查询

第一步：根据姓名（陈辰）找到所在系第二步：根据所在系找到该系学生信息第一步：根据姓名（陈辰）找到所在系第二步：根据所在系找到该系学生信息select所在系from学生where姓名='陈辰'select学号,姓名from学生where所在系=‘计算机’

可否合并？1.嵌套查询select学号,姓名from学生where所在系=‘计算机’

问题：查询“陈辰”所在系的全体学生的学号，姓名select所在系from学生where姓名='陈辰'select学号,姓名from学生where所在系=（select所在系from学生where姓名='陈辰'）1.嵌套查询（1）一个查询语句嵌套在另一个查询语句的where或having条件中的查询叫做嵌套（子）查询。（2）外层的查询块叫外层查询或父（主）查询或查询。（3）sql语句允许多层嵌套查询.select学号,姓名from学生where所在系=（select所在系from学生where姓名='陈辰'）问题：查询“陈辰”所在系的全体学生的学号，姓名1.嵌套查询（1）带in谓词的子查询（2）带比较运算符的子查询（3）带any或all的子查询（4）带exists谓词的子查询2.嵌套查询例1：查询“张小红”所在系的全体学生的学号，姓名select学号,姓名from学生where所在系in(select所在系from学生where姓名=‘张小红')3.案例——IN谓词内层查询可能返回多个值例2：求选修了“计算机导论”的学生学号select学号from选课where课程号in(select课程号from课程where课程名='计算机导论')连接查询：select学号from选课,课程where选课.课程号=课程.课程号and课程名='计算机导论'3.案例——IN谓词讨论1：select学号from选课

where课程号notin(select课程号from课程where课程名='计算机导论')例3：求没有选修“计算机导论”的学生学号select学号from学生where学号notin(select学号from选课where课程号in(select课程号from课程where课程名='计算机导论'))讨论2：select学号from选课,课程where选课.课程号=课程.课程号and课程名<>'计算机导论'3.案例——IN谓词3.案例——比较运算符（1）带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。（2）常用的比较运算符：>、<、=、>=、<=、!=、3.案例——比较运算符例4：查询cr001课程成绩比陈辰cr001课程成绩高的学生学号和成绩select学号,成绩from选课where课程号=‘cr001‘and成绩>(select成绩

from选课where课程号='cr001'and学号=(select学号from学生where姓名=‘陈辰'))3.案例——any，all谓词any：任意一个all：全部、所有>any大于子查询结果中的某个值<any小于子查询结果中的某个值>all大于子查询结果中的所有值<all小于子查询结果中的所有值大于最小小于最大大于最大小于最小例5：求其他系中比计算机系某一学生年龄小的学生的信息。

（即求年龄小于计算机系年龄最大者的学生）select*from学生where所在系<>'计算机‘and年龄<any(select年龄from学生where所在系='计算机')

select*from学生where所在系<>'计算机'and

年龄<(selectmax(年龄)from学生where所在系=‘计算机’)

3.案例——any，all谓词用max()实现例6：求其他系中比计算机系学生年龄都小的学生的信息。

（即求年龄小于计算机系年龄最小者的学生）select*from学生where所在系<>'计算机'

and年龄<all(select年龄from学生where所在系='计算机')3.案例——any，all谓词3.案例——exists谓词例7判断是否有“98001”学生，存在返回“y”，否则返回“n”。ifexists(select*from学生where学号=‘98001’）select‘y’’--显示字符串yelseselect‘n’--显示字符串nexists存在谓词一般和if语句配合使用（1）in谓词4.小结（2）比较运算符（3）any,all谓词（4）exists存在谓词思考：统计课程选修情况，统计学生选课情况数据库原理与应用6.排序课程选修情况学生选课情况已有基础：单表查询，连接查询。学习目标：统计课程选修情况，统计学生选课情况。1.提出问题少量简单数据，通过排序可以看出数据分布情况。2.排序对查询结果排序——使用orderby

子句

语法格式：orderby属性asc|desc2.排序例1：将选课表中数据按照成绩由小到大顺序排序select学号，课程号，成绩

from选课

orderby33.案例select*from选课

orderby成绩可以用select子句中列的序号来表示列名例2：查询所有选课信息。

要求：首先按照课程号的升序排列，

同一个课程号按照成绩的降序排列。3.案例select*from选课

orderby课程号asc,成绩descnull最小4.小结缺点：（1）只能查看少量数据的简单分布情况

（2）不能统计数据4.小结（1）null最小（2）列名可以用序号代替数据统计基础——聚合函数

（1）max

（2）min

（3）avg

（4）sum

（5）count5.思考？数据库原理与应用7.聚合函数课程选修情况学生选课情况目标：统计课程选修情况，统计学生选课情况。问题（1）count(列名)：求不为null的行数

count(*)：求行数

count(distinct列名)：不计重复求行数

（2）sum(列名)：求总和

（3）avg(列名)：求平均值

（4）max(列名)：求最大值

（5）min(列名)：求最小值注意：除count(*)外，列中的null值不计算。1.聚合函数例1：统计选课人次数、选了课的人数、成绩不为null的个数。2.案例selectcount(*)as选课人次数,

count(distinct学号)as选课人数,

count(成绩)as成绩不为null数量from选课例2：求“98001“学生选课门数，平均分。2.案例selectcount(课程号)as选课门数,

avg(成绩)as平均分from选课where学号='98001'例3：求“cr001“这门课选课人数，平均分。2.案例selectcount(学号)as选课门数,

avg(成绩)as平均分from选课where课程号=‘cr001'已解决问题：（1）某位同学（98001）的选课情况——（选课门数，最高分，最低分，平均分）

？（2）某门课（cr001）的选修情况——（选课人数，最高分，最低分，平均分）3.小结目标：（1）每位同学的选课情况3.小结（2）每门课的选修情况预习：分组数据库原理与应用8.分组查询游戏：分类已解决问题：（1）某位同学（98001）的选课情况——（选课门数，最高分，最低分，平均分）（2）某门课（cr001）的选修情况——（选课人数，最高分，最低分，平均分）1.回顾目标：（1）每位同学的选课情况1.回顾（2）每门课的选修情况分组：groupby（列名）select性别,count(学号)as人数from学生groupby性别例1：统计男女学生各有多少。２.案例select所在系,count(学号)as人数from学生groupby所在系例2：统计各专业的学生人数。２.案例例3：查询每一门课的选课情况（课程号，选课人数，平均分）。２.案例select课程号,count(学号)as选课人数,avg(成绩)as平均分from选课groupby课程号２.案例例4：查询每一门课的选课情况（课程号，课程名，选课人数，平均分）。groupby的原则：select后面的所有列中,没有使用聚合函数的列,必须出现在groupby后面。２.案例例4：查询每一门课的选课情况（课程号，课程名，选课人数，平均分）。练习１:

查询每个学生的选课情况（学号,选课门数,平均分）倒计时3分钟！练习1:

查询每个学生的选课情况（学号,选课门数,平均分）。２.案例select学号,count(课程号)as选课门数,avg(成绩)as平均分from选课groupby学号思考：查询选课数量超过5门课程的学生。深入：选择符合条件的组（

having

）

３.having短语select所在系,count(学号)as人数from学生groupby所在系havingcount(学号)>=3例5：查找学生人数>=3人的专业。４.案例where和having的区别：

where子句作用于数据源，从中选择满足条件的元组。

having短语作用于结果集的分组中,选择满足条件的组，所以，having短语必须与groupby子句合用。练习２：求选课数量超过5的学生学号及选课数量。倒计时3分钟！练习2:求选课数量超过5的学生学号数量。４.案例select学号,count(课程号)as选课门数from选课groupby学号havingcount(课程号)>=5（1）分组查询的意义５.小结（3）having短语（4）where和having的区别（2）groupby的使用原则思考：学霸是谁？６.思考回答１：选课数量最多的学生。回答２：平均分最高的学生。数据库原理与应用9.综合查询问题：学霸是谁？回答１：选课数量最多的学生。回答２：平均分最高的学生。从分组数据中找出符合条件的语法：topn或topnpercent

select学号,avg(成绩)as平均分from选课groupby学号orderby平均分desc１.top短语例1：查询平均分排名在前30%的学生学号和平均分方法：按照平均分降序排列，再选择前30%。top30percent

不具有通用性１.top短语例2:

查询选课门数最多的同学的学号及其选课门数。select学号,count(课程号)as选课门数from选课groupby学号orderby选课门数desc不具有通用性top1

2.嵌套查询例2:

查询选课门数最多的同学的学号及其选课门数。select学号,count(课程号)as选课门数from选课groupby学号havingcount(课程号)>=all（selectcount(课程号)from选课groupby学号）带any,all谓词的嵌套查询3.练习问题：最受欢迎的课程是？回答１：选课人数最多的课程。回答２：平均成绩最高的课程。倒计时3分钟！例3:

查询选课人数最多的课程的课程号及其选课人数。select课程号,count(学号)as选课人数from选课groupby课程号orderby选课人数desc

3.思考top1

不具有通用性例3:

查询选课人数最多的课程的课程号及其选课人数。select课程号,count(学号)as选课人数from选课groupby课程号havingcount(学号)>=all（selectcount(学号)from选课groupby课程号）3.思考3.小