数据库课程组PPT课件

1、信息工程学院常州机电职业技术学院数据库课程组信息工程学院常州机电职业技术学院第05讲 查询数据表信息工程学院常州机电职业技术学院学习目标知识目标知识目标: :知道数据查询语句的基本结构了解常用数据查询子句及作用熟悉常用常见数据查询类型能力目标能力目标: :能使用查询语句进行简单查询能使用查询语句进行多表查询会使用嵌套查询会合并查询结果信息工程学院常州机电职业技术学院主要内容查询语句基本格式条件查询查询排序分组查询多表查询嵌套查询合并查询信息工程学院常州机电职业技术学院任务5.1 单表数据查询查询数据表中数据的语句基本命令格式如下：子任务5.1.1 查询语句基本格式SELECT 列名或表达式列表

2、INTO 新表FROM 表名WHERE 查询条件ORDER BY 排序条件GROUP BY 分组条件HAVING 分组后筛选条件信息工程学院常州机电职业技术学院子任务5.1.2 查询数据列和行【例5-1】查询S（学生表）中前5位同学信息。信息工程学院常州机电职业技术学院【例5-2】查询S（学生表）中SNO(学号)和SNAME(姓名)两列数据。【练一练】查询Product（产品表）中ProID(产品编号)和ProName(产品名称)两列数据。信息工程学院常州机电职业技术学院【例5-3】将SC（选课表）中SNO(学号)和CNO(课程号)两列数据合并成一列输出。【练一练】查询Product（产品表）

3、中ProID(产品编号)和ProName(产品名称)两列数据。信息工程学院常州机电职业技术学院函数用法函数用途SUM(ALL|DISTINCT 表达式)返回列或表达式的和。AVG(ALL|DISTINCT 表达式)返回列或表达式的平均值。MAX(表达式)返回列或表达式的最大值。MIN(表达式)返回列或表达式的最小值。COUNT(ALL|DISTINCT 表达式或*) 返回所有的项数。除了算术运算符外，还有聚合函数也可以用于表达式，具体用法如表5-1所示。表5-1 常用聚合函数【例5-4】统计C（课程表）中所有课程的总学时。【练一练】统计Product（产品表）中所有商品的库存总和。信息工程学院

4、常州机电职业技术学院【例5-5】统计C（课程表）中课程学时的最大值、最小值和平均值。【练一练】统计Product（产品表）中最大的商品库存。信息工程学院常州机电职业技术学院【例5-6】统计C（课程表）中开设的所有课程门数。信息工程学院常州机电职业技术学院【例5-7】统计C（课程表）中有多少种不同的学时。【练一练】统计Product（产品表）中有多少种商品。信息工程学院常州机电职业技术学院子任务5.1.3 精确条件查询要查询符合条件的数据行需要用WHERE子句。WHERE子句后写查询条件。SQL SERVER中常用的比较运算符和逻辑运算符如表5-2和5-3所示。运算符含义示例=等于WHERE S

5、CORE=100 查询成绩列的值等于100的行数据或!=不等于WHERE SCORE100 查询成绩列的值不等于100的行数据大于WHERE SCORE90 查询成绩列的值大于90的行数据小于WHERE SCORE=大于等于WHERE SCORE=90 查询成绩列的值大于或等于90的行数据=小于等于WHERE SCORE90 查询成绩列的值不大于90的行数据，即查询成绩小于等于90的行数据AND如果两个布尔表达式都为TRUE，则返回TRUEWHERE SCORE=60 AND SCORE=70查询成绩列的值大于60且小于70的行数据OR如果两个布尔表达式中的一个为TRUE，则返回TRUEWHE

6、RE SNAME=李勇 OR SNAME=刘晨查询姓名列的值为李勇或者刘晨的行数据BETWEENAND如果操作数在某个范围之内，则返回TRUE，包含边界值WHERE SCORE BETWEEN 60 AND 70效果与第二行示例相同IN如果操作数等于表达式列表中的一个，则返回TRUE。WHERE SNAME IN(李勇, 刘晨)效果与第三行示例相同信息工程学院常州机电职业技术学院【例5-8】查询C（课程表）中教师编号为T1的教师所开设的课程。【练一练】查询Sale（销售表）中产品编号为001的商品销售记录。信息工程学院常州机电职业技术学院【例5-9】查询SC（选课表）中成绩字段为空值的数据。信

7、息工程学院常州机电职业技术学院【例5-10】查询C（课程表）中学时数在60到80之间的课程。（包括60和80）。此题有两种方法可以完成。【练一练】查询Product（产品表）中库存数量大于5且小于10的的商品信息。（注意此题不包含边界值，所以不能用BETWEENAND）信息工程学院常州机电职业技术学院【例5-11】查询C（课程表）中数据库和数据结构这两门课程的学时数。此题有两种方法可以完成。【练一练】查询Product（产品表）中洗衣机和空调的库存数量。信息工程学院常州机电职业技术学院【例5-12】改写【例5-11】查询C（课程表）中除了数据库和数据结构这两门课程以外其它课程的学时数。此题有两

8、种方法可以完成。【练一练】查询Sale（销售表）中除编号001和002这两种商品外其它商品的销售数量。信息工程学院常州机电职业技术学院【例5-13】查询C（课程表）中教师T3讲授的信息系统这门课程的学时数。并将该查询结果保存到新表C_T3中。【练一练】查询Sale（销售表）中编号001的商品在2017-02-04这天的销售数量。并将该查询结果保存到新表Sale_001中。信息工程学院常州机电职业技术学院子任务5.1.4 模糊条件查询当查询条件不明确时，我们需要使用LIKE关键字进行模糊查询，模糊查询一般用于字符对象的查询。LIKE后跟带通配符的比较条件，如果条件中未通配符将进行精确比较查询。常

9、用的通配符如表5-4所示。表5-4 常用通配符通配符含义示例%匹配多个任意字符LIKE %a% 匹配包含字符a的字符串_（下划线）仅匹配一个任意字符LIKE a_ 匹配以字符a开头，之后是任意一个字符的字符串 匹配括号中的任意一个字符LIKE abc% 匹配以a、b、c中任意一个字符开头的字符串匹配不在括号中的其它字符LIKE abc% 匹配除了a、b、c之外其它字符开头的字符串信息工程学院常州机电职业技术学院【例5-14】查询S（学生表）中姓张的同学信息。【练一练】查询Product（产品表）中产品名称中含有“冰”字的产品信息。信息工程学院常州机电职业技术学院【例5-15】查询S（学生表）中

10、名字有两个字且以“敏”字结尾的同学信息。【练一练】查询Product（产品表）中产品名称以“机”字结尾的产品信息。信息工程学院常州机电职业技术学院【例5-16】查询S（学生表）中姓张和姓刘的同学信息。【练一练】查询Product（产品表）中产品名称以“冰”和“洗”开头的产品信息。信息工程学院常州机电职业技术学院【例5-17】查询S（学生表）中除姓张和姓刘的同学以外其它同学信息。此题有两种方法可以完成。【练一练】查询Product（产品表）中产品名称不以“冰”或“洗”开头的产品信息。信息工程学院常州机电职业技术学院子任务5.1.5 查询结果排序如果要对查询结果排序，则要用ORDER BY子句。关

11、键字ASC表示升序，DESC表示降序，默认为升序。【例5-18】查询S（学生表）中同学信息，结果按出生日期升序排列。【练一练】查询Sale（销售表）中产品销售信息，结果以销售日期升序排列。信息工程学院常州机电职业技术学院【例5-19】查询SC（选课表）中成绩信息，结果先按课程编号升序排，若课程编号相同再按成绩降序排。【练一练】查询Sale（销售表）中产品销售信息，结果先按销售日期升序排列，若销售日期相同，再按销售数量降序排。信息工程学院常州机电职业技术学院子任务5.1.6 分组查询统计 分组查询类似于分类汇总，即按某一特征对数据进行分类，而后汇总统计相关数据。例如，按性别分类统计人数，按课程分

12、类统计每门课程的平均分等。分组查询用GROUP BY子句加上聚合函数来进行查询。【例5-20】统计SC（选课表）中每门课程的平均成绩，结果按平均成绩升序排列。【练一练】统计Sale（销售表）中每种产品的销售数量，结果以销售数量升序排列。信息工程学院常州机电职业技术学院【例5-21】统计SC（选课表）中每位同学的选课门数，并合计总门数。【练一练】统计Sale（销售表）中每种产品的销售记录数，并合计总记录数。信息工程学院常州机电职业技术学院【例5-22】统计SC（选课表）中每位同学的选课门数，并筛选出选课门数超过5门的学生。【练一练】统计Sale（销售表）中每种产品的销售数量，并筛选出销售数量大于

13、3的记录。信息工程学院常州机电职业技术学院【例5-23】统计S（学生表）中计算机系和信息系的人数。此题有两种方法。考虑一下哪种方法查询效率更高。【练一练】分别统计Sale（销售表）中2017-02-04和2017-06-03这两天的产品销售数量。信息工程学院常州机电职业技术学院任务5.2 多表数据查询如果我们需要同时查看学生的基本信息及其选课信息就需要用到两张表S（学生表）和SC（选课表）的数据，这时就需要进行多表查询。多表查询需要将表进行连接。如果多表查询时未加表连接条件则会出现如图所示结果。该连接称为交叉连接，交叉连接的结果称为笛卡儿积。笛卡儿积结果中列数是两张表列数之和，行数是两张表行数

14、之积。信息工程学院常州机电职业技术学院笛卡儿积的结果对于我们来讲是没有意义的，我们需要对表加上连接条件。表连接分为内连接和外连接两种方式。命令也有SQL Server和ANSI标准语法两种写法，其中ANSI标准语法是通用的。SQL Server语法格式：SELECT 列名或表达式，FROM 表1，表2，WHERE 连接条件1 AND 连接条件2 ANSI语法格式：SELECT 列名或表达式，FROM 表1 JOIN 表2ON 连接条件1JOIN 表3ON连接条件2信息工程学院常州机电职业技术学院1.内连接内连接包括有等值连接、自然连接、不等连接、自连接等连接方式。1）等值连接：连接条件为两个字

15、段值相等，可以是不同字段值相等。【例5-24】查询S（学生表）中学生的基本信息及SC（选课表）中该生相关选课信息。我们分别用两种语法实现该查询。信息工程学院常州机电职业技术学院2）自然连接：自然连接时特殊的等值连接，它要求连接条件必须是公共字段相等，且结果集中没有重复列。而等值连接结果集中可以有重复列。【例5-25】用自然连接方式查询S（学生表）中学生“李勇”的基本信息及SC（选课表）中该生的选课信息。只需在【例5-23】基础上去掉重复字段SNO即可。【练一练】查询Product(产品表)中“电视机”的产品信息以及Sale（销售表）中该产品的销售信息 。信息工程学院常州机电职业技术学院【例5-

16、26】查询成绩在90分以上的学生选课信息，包括学生的学号、姓名、选修课程号、课程名及选课成绩。【练一练】查询每个部门的商品销售情况，包括部门编号，部门名称，产品编号，产品名称，销售数量，销售员工号等。信息工程学院常州机电职业技术学院3）比较连接：连接条件使用比较运算符。即连接条件不用等号。【例5-27】为每位教师查询一下非自己讲授的课程。【练一练】为每位职员查询不是自己销售的商品信息工程学院常州机电职业技术学院4）自连接：把一张表模拟成两张表，自己和自己进行连接查询。【例5-28】查询所有年龄比刘晨小的学生姓名、性别、出生日期和刘晨的出生日期。【练一练】查询价格比“空调”高的商品及空调的价格。

17、信息工程学院常州机电职业技术学院2.外连接外连接包括左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和全外连接（FULL JOIN）三种。内连接主要查询两张表中匹配的记录，外连接可以查询两张表中所有匹配和非匹配的记录。外连接只有ANSI标准语法格式。1）左外连接（LEFT JOIN）：查询JOIN关键字左边表中所有的记录及右边表中匹配的记录，如果右边表没有匹配记录，则相应字段显示为空值。2）右外连接（RIGHT JOIN）：查询JOIN关键字右边表中所有的记录及左边表中匹配的记录，如果左边表没有匹配记录，则相应字段显示为空值。3)全外连接（FULL JOIN）：显示两张表中所有

18、匹配和非匹配记录。非匹配记录的相应字段为空值。信息工程学院常州机电职业技术学院【例5-29】分别用左外连接、右外连接和全外连接的方式连接S（学生表）和SC（选课表）。信息工程学院常州机电职业技术学院信息工程学院常州机电职业技术学院【练一练】在Depart（部门表）中添加一个部门“04，管理部”，分别用左外连接、右外连接和全外连接连接查询Depart（部门表）和Product(产品表)，查看每个部门销售产品的信息，并对比查询结果。练习完成后请删除新增部门记录。信息工程学院常州机电职业技术学院任务5.3 嵌套查询把内部查询语句的结果作为比较值放在外部查询语句的WHERE或HAVING子句中，这种查

19、询方式叫嵌套查询。内部的查询语句称为子查询。【例5-30】查询T（教师表）中职称与“李力”相同的教师信息。【练一练】查询和“王丽”部门相同的其他职员信息。信息工程学院常州机电职业技术学院【例5-31】查询SC（选课表）中选修了教师 “T1”所讲授课程的学生学号及其所选课程号。信息工程学院常州机电职业技术学院【例5-32】用EXISTS改写上例5-31。 也可以用NOT EXISTS表示取反。如图5-46所示，查询除教师“T1”外，选修其他老师所授课程的学生。【练一练】用两种方法查询“家电部”所售的产品信息。信息工程学院常州机电职业技术学院【例5-33】查询SC（选课表）中成绩高于所有课程平均分的选课信息。【练一练】查询Product（商品表 ）中销售价格比所有商品平均价格低的商品信息。信息工程学院常州机