模块4：查询MySQL记录（课件）《MySQL数据库》同步教学（重庆大学版）

模块： 4 查询MySQL记录 据 库 数 MySQL 1 了解MySQL市场地位 4 建立MySQL学习兴趣 5 知 识 目 标 技 能 要 求 掌握常用聚合函数。 熟练使用like、between、in查询。 熟练掌握分组查询。 掌握多表查询常用方法。 掌握内连接、左外联、右外联等连接方法。 会根据需求进行单表查询。 会使用聚合函数进行分组。 会模糊查询和范围查询。 能用多表查询解决复杂查询问题。 会使用连接查询和子查询。 STUDB库 ERP库 0 MySQL 任务4.1 创建单表基本查询 模块4： 查询MySQL记录 数据库 MySQL 1.查询所有列 4.1.1 SELECT子句 知识储备 同步操作 SELECT | 字段列表 FROM 表名 [WHERE 条件表达式 ] [GROUP BY 字段列表 | HAVING 条件表达式 ] [ORDER BY 字段 [ASC | DESC]] [LIMIT N] [OFFSET M] SELECT子句显示字段名，列名间用逗号(,)隔开，“*”代表查询所有列。 FROM 子句指定要查询的表或视图，可以是多个，之间用(,)隔开，表示多表查询。 WHERE子句用于对查询结果进行过滤，根据具体的查询选择使用。 ORDER BY子句对查询结果进行排序。 LIMIT子句确定返回的记录数，OFFSET指定开始查询的数据偏移量，默认0。 0 MySQL 4.1.1 SELECT子句 查询所有列 同步操作 USE STUDB; SELECT * FROM STUDENTS; 操作数据库STUDB: （1）查询所有部门信息（DEPARTS表）。 （2）查询所有教师信息（TEACHERS表）。 （3）查询所有课程信息（COURSES表）。 （4）查询所有专业课程信息（MAJOR_GRADE_TERM_COURSES表）。 操作数据库ERP： （5）查询所有产品信息。 （6）查询所有类别信息。 （7）查询所有销售信息。 0 MySQL 4.1.1 SELECT子句 查询部分列 同步操作 USE STUDB; SELECT ID,SNO,SNAME,MCLASSID FROM STUDENTS; 操作数据库STUDB: （1）查询老师的工号和姓名。 （2）查询课程名称和开出院部ID。 （3）查询班级名称和所属专业ID。 操作数据库ERP： （4）查询产品编号、产品名称和入库时间。 （5）查询类别名称。 （6）查询所有销售单号、流水号、销售日期信息。 0 MySQL 4.1.1 SELECT子句 使用别名 同步操作 USE STUDB; SELECT SNO AS 学号,SNAME 姓名 FROM STUDENTS; 使用别名，中文或英文 操作数据库STUDB: （1）查询老师的工号和姓名。 （2）查询课程名称和开出院部ID。 （3）查询班级名称和所属专业ID。 操作数据库ERP： （4）查询产品编号、产品名称和入库时间。 （5）查询类别名称。 （6）查询所有销售单号、流水号、销售日期信息。 0 MySQL 4.1.2 运算符与表达式 知识储备 SELECT | 字段列表 FROM 表名 [WHERE 条件表达式 ] WHERE子句使用AND或者OR指定一个或多个条件，WHERE子句也可以运用于SQL的DELETE或者UPDATE命令。 表4.1 比较运算符 运算符 含义 = 等于 > 大于 < 小于 >= 大于等于 <= 小于等于 <> 不等于 != 不等于 表4.2 逻辑运算符 运算符 含义 AND 并且 OR 或者 NOT 取反 IS NULL 如果列值为NULL则返回TRUE，否则返回FALSE 0 MySQL 4.1.2 运算符与表达式 同步操作 SELECT * FROM COURSES WHERE CNAME=’MySQL数据库’; 操作数据库STUDB: （1）查询某老师的详细信息。 （2）查询某班的全体学生信息。 （3）查询某专业开出的所有课程信息。 操作数据库ERP： （4）查询销售价格大于800元的产品信息。 （5）查询店员“李春波”的信息。 （6）查询某日的销售记录 查询“MySQL数据库”课程的详细信息 0 MySQL 4.1.2 运算符与表达式 同步操作 SELECT * FROM MAJOR_GRADE_TERM_COURSES WHERE MAJORID=3 AND TYPEID=1 操作数据库STUDB: （1）查询大数据与计算机应用专业开设的课程。 （2）查询2020级计算机应用技术专业开设的课程。 （3）查询MAJOR_GRADE_TERM_COURSES表，本学期开出“MySQL数据库”课程的记录信息。 操作数据库ERP： （4）查询某导购员在某日的销售信息。 （5）查询男性导购员信息。 查询人工智能专业普招班的课程体系 0 MySQL 任 务 小 结 SELECT ... FROM ...语句 SELECT ... FROM ... WHERE...语句 查询条件使用关系、逻辑运算符 查询使用别名 任务4.2 使用关键字查询 模块4： 查询MySQL记录 数据库 MySQL 4.2.1 DISTINCT关键字 知识储备 SELECT DISTINCT 字段名 FROM 表名 在使用DISTINCT的过程中主要注意一下几点： 在对字段进行去重的时候，要保证DISTINCT在所有字段的最前面。 如果DISTINCT关键字后面有多个字段时，则会对多个字段进行组合去重，只有多个字段组合起来的值是相等的才会被去重。 0 MySQL 4.2.1 DISTINCT关键字 同步操作 SELECT DISTINCT DEPARTID FROM MAJORS; USE STUDB; （1）查询班级表MCLASSS中所有不同的专业编号。 （2）查询课程表COURSES中所有开出课程的部门编号。 （3）查询教师表TEACHERS中所有教师来自的部门编号。 USE ERP; （4）销售明细表中不重复的商品编号。 （5）销售明细表中不重复的销售编号。 （6）商品表中不重复的产品类型编号。 查询专业表有多少个不同二级学院ID 0 MySQL 4.2.2 LIKE关键字 知识储备 SELECT 字段列表 FROM 表名 WHERE 某字段 LIKE 条件 模糊查询条件，SQL提供了多种匹配模式： %：表示任意0个或多个字符。 _：表示任意单个字符。 0 MySQL 4.2.2 LIKE关键字 同步操作 SELECT * FROM TEACHERS WHERE TNAME LIKE ‘李%’ USE STUDB; （1）查询学生表中姓“王”的两个字的学生信息。 （2）查询学生表中名字最后一个字是“兰”的学生信息。 （3）查询含有“程序设计”的课程有哪些。 USE ERP; （4）查询店员中姓“李”且名字是三个字的店员信息。 （5）查询有哪些叫“篮球鞋”的商品。 查询所有姓“李”的老师 SELECT * FROM TEACHERS WHERE TNAME LIKE ‘李_’ 查询两个字的姓“李”的老师 0 MySQL 4.2.3 IN 与NOT IN关键字 知识储备 SELECT 字段列表 FROM 表名 WHERE 某字段 [NOT] IN (值列表) 运算符也称为“成员条件运算符”，用于判断一个值是否在一个指定的数据集合之内。 IN：表示限定在指导值列表范围内的值。 NOT IN：表示不在指导值列表范围内的值。 0 MySQL 4.2.3 IN 与NOT IN关键字 同步操作 SELECT * FROM TEACHERS WHERE DEPARTID IN(2,3,4) USE STUDB; （1）使用IN关键字查询学生表中“郑玉新”“金长顺”“李永泽”三位学生信息。 （2）使用IN关键字查询“计应2001、大数据2001、人工智能2001”班的学生信息。 （3）查询编号为3、5、7、9的教师信息。 （4）使用NOT IN关键字查询公共课部、马克思主义学院、创新创业学院开出的所有课程信息。 （5）使用NOT IN关键字查询非信息技术学院、非马克思主义学院的所有教师信息。 USE ERP; （6）使用IN关键字查询类别为4、5的商品信息。 查询DEPARTID值是2、3、4的教师信息 SELECT * FROM TEACHERS WHERE DEPARTID NOT IN(1,2,3) 查询DEPARTID值不是1、2、3的教师信息 0 MySQL 4.2.4 BETWEEN...AND关键字 知识储备 SELECT * |{字段名1，字段名2，…} FROM 表名 WHERE 字段名 [NOT] BETWEEN 值1 AND 值2 BETWEEN...AND运算符选取介于两个值之间的数据，这些值可以是数字和日期类型（取值范围包括边界值）。如果字段的值在指定范围内，则满足条件，该字段所在的记录将被查询出来，反之则不会被查询出来。 0 MySQL 4.2.4 BETWEEN...AND关键字 同步操作 SELECT * FROM MAJOR_GRADE_TERM_COURSES WHERE GRADE=2021 AND TERM='2021-2022-2' AND THOUR BETWEEN 50 AND 80; USE STUDB; （1）使用BETWEEN AND关键字查询工号在900021-900030之间的教师信息。 （2）使用BETWEEN AND关键字查询“2021-09-01 00:00:00”至“2021-12-31 23:59:59”之间更新过学生信息的学生记录。 USE ERP; （3）使用BETWEEN AND关键字查询销售价格在600-1000范围内的产品信息。 （4）使用BETWEEN AND关键字查询SALES表中销售日期从“2021-10-01”到当前的所有销售记录。 查询2021级2021-2022-2学期课程体系中开出学时在50-80之间的课程信息 0 MySQL 4.2.5 IS NULL与IS NOT NULL关键字 知识储备 SELECT * | 字段名1，字段名2，… FROM 表名 WHERE 字段名 IS [NOT] NULL 某些列的值可能存在空值（NULL），空值不同于0，也不同于空字符。使用IS NULL关键字来判断一个字段的值是否为空值，IS NOT NULL关键字判断一个字段的值非空 0 MySQL 4.2.5 IS NULL与IS NOT NULL关键字 同步操作 SELECT * FROM TEACHERS WHERE PWD IS NULL; SELECT * FROM TEACHERS WHERE PWD IS NOT NULL; USE STUDB; （1）使用UPDATE命令为STUDENTS表中部分学生添加BIRTHDAY字段值,然后查询BIRTHDAY为NULL的记录，以及不为NULL的记录。 USE ERP; （2）使用 NULL关键字查询销售人员表SALESMAN中已经填报个人联系方式（MOBILE字段）的员工信息。 （3）使用 NULL关键字查询TYPE表商品类型表中所有二级类别的名称。 查询教师表中密码为空（或不为空）的记录信息 0 MySQL 4.2.6 LIMIT关键字 知识储备 SELECT * | 字段名1，字段名2，… FROM 表名 [WHERE ...] LIMIT [offset,] rows； 参数offset和rows必须为整数，offset可以省略。 offset指定第一个返回记录行的偏移量。注意：初始记录行是0，不是1。 rows指定返回行的最大条目。 0 MySQL 4.2.6 LIMIT关键字 同步操作 SELECT * FROM TEACHERS LIMIT 10; USE STUDB; （1）查询学生STUDENTS表中从第11个开始后的5名同学信息。 （2）查询学生STUDENTS表中第11页的学生信息（每页10条）。 USE ERP; （3）查询产品表中前5个产品信息。 （4）查询产品表中从第3个开始后的5个产品信息。 查询教师表前10条记录 SELECT * FROM TEACHERS WHERE DEPARTID=1 LIMIT 10,5; 查询教师表中信息技术学院教师存储排名第11至15的教师信息 0 MySQL 4.2.7 ORDER BY关键字 知识储备 SELECT * | 字段名1，字段名2，… FROM 表名 [WHERE ...] ORDER BY 字段 [ASC | DESC]； ORDER BY这个指令来达到排序的目的。 ASC 代表结果会以由小往大的顺序列出，默认 ASC。 DESC 代表结果会以由大往小的顺序列出。 0 MySQL 4.2.7 ORDER BY关键字 同步操作 SELECT * FROM TEACHERS ORDER BY TNO DESC; USE STUDB; （1）按教师姓名排序。 （2）查询学生信息，按班级排序。 USE ERP; （3）按产品销售价格的降序排序。 （4）按产品库存数量的升序排序。 查询教师表按工号降序排列 SELECT * FROM TEACHERS ORDER BY DEPARTID,TNO DESC; 查询教师表按院部ID升序、工号降序排列 0 MySQL 任 务 小 结 DISTINCT去重关键字 LIKE模糊查询关键字 IN与NOT IN关键字 BETWEN.. AND..关键字 IS NULL与IS NOT NULL关键字 LIMIT关键字 ORDER BY 排序关键字 任务4.3 使用聚合函数和分组查询 模块4： 查询MySQL记录 数据库 MySQL 4.3.1 聚合函数 知识储备 MySQL提供了一些对数据进行统计的函数，用于实现特定功能，如统计记录个数、求某数值字段的和、求某字段的最大值或最小值等。 函数名称 函数功能 SUM 返回选取的某列值的总和 MAX 返回选取的某列的最大值 MIN 返回选取的某列的最小值 AVG 返回选取的某列的平均值 COUNT 返回选取的某列或记录的行数 0 MySQL 4.3.1 聚合函数 同步操作 SELECT SUM(THOUR) FROM MAJOR_GRADE_TERM_COURSES WHERE GRADE=2020 AND MAJORID=5 AND TYPEID=1 AND TERM=’2021-2022-2’; USE STUDB; （1）统计本班本学期开出的学时总数。 （2）统计本专业开出课程总学时数 USE ERP; （3）统计某商品销售总额。 （4）统计某销售员在某段时间内销售商品的总价值。 查询2020级人工智能专业普招班在第2021-2022-2学期开出的总学时数 SUM函数 0 MySQL 4.3.1 聚合函数 同步操作 SELECT MAX(THOUR) FROM MAJOR_GRADE_TERM_COURSES WHERE GRADE=2020 AND MAJORID=5 AND TYPEID=1 AND TERM=’2021-2022-2’; USE STUDB; （1）统计教师表TEACHERS最小工号和最大工号。 （2）统计学生表STUDENTS中最后一次更新记录的时间和第一次更改记录的时间。 USE ERP; （3）统计目前商品表GOODS中最贵商品单价和最便宜商品单价。 （4）统计SALE表中最后一次售出商品的时间。 查2020级人工智能专业普招班在第2020-2021-2学期开出学的课程最大（少）学时数 MAX/MIN函数 0 MySQL 4.3.1 聚合函数 同步操作 SELECT AVG(THOUR) FROM MAJOR_GRADE_TERM_COURSES WHERE GRADE=2020 AND MAJORID=5 AND TYPEID=1 AND TERM=’2021-2022-2’; USE STUDB; （1）统计某年级专业开出的课程平均课时数。 （2）统计某班某学期的平均周课时数。 USE ERP; （3）统计目前商品表GOODS中商品均价。 查询2020级人工智能专业普招班在第2020-2021-2学期开出的课程平均学时数 AVG函数 0 MySQL 4.3.1 聚合函数 同步操作 SELECT COUNT(*) FROM TEACHERS; USE STUDB; （1）统计某班级人数。 （2）统计某班某学期开出了多少门课。 （3）统计在校年级中某专业共有多少个班。 USE ERP; （4）统计某段时间内商品销售产生了多少笔记录。 （5）查询某价格范围内的商品有多少种。 查询教师表中共有多少人 COUNT函数 SELECT COUNT(*) FROM MCLASSS WHERE MAJORID=2; 查询2021级大数据专业共有多少个班级 0 MySQL 4.3.2 GROUP BY分组 知识储备 使用GROUP BY子句可以将数据划分到不同的组中，实现对记录的分组查询。 SELECT * | 字段名1，字段名2，… FROM 表名 [WHERE ...] GROUP BY 字段列表 [HAVING 条件表达式]； “字段列表”表示进行分组所依据的一个或多个字段的名称。 “HAVING 条件表达式”是一个逻辑表达式，用于指定分组后的筛选条件。 GROUP BY子句通常与聚合函数同时使用。 0 MySQL 4.3.2 GROUP BY分组 同步操作 SELECT TEACHERID FROM TEACHER_COURSES WHERE TERM=’2021-2022-2’ AND GROUP BY TEACHERID; USE STUDB; （1）分类统计各部门教职工人数。 （2）分类统计各专业班级数。 （3）分类统计各班级人数。 USE ERP; （4）查询产品表中售价在800元以上的产品类型编号。 （5）查询销售明细中售出产品数量大于2的产品编号。 （6）查询销售明细中售出产品数量大于2的销售编号。 查询2021-2022-2学期任课老师有哪些（查不同的老师ID） 0 MySQL 4.3.2 GROUP BY分组 同步操作 （1）统计产品表中不同产品类型的库存数，只显示库存数量大于60的结果。 （2）查询销售明细中每单出现的产品种类数，只显示种类大于2的结果。 （3）查询销售明细中不同产品的销售量，只显示销量大于5的结果。 SELECT TEACHERID,COUNT(*) NUM FROM TEACHER_COURSES WHERE TERM='2021-2022-2' GROUP BY TEACHERID HAVING NUM>=2; 查询2021-2022-2学期授课班级超过2个班的老师ID(包括2个班) 0 MySQL 任 务 小 结 聚合函数 SUM()、MAX()、MIN()、AVG()、COUNT() GROUP BY 分组查询 HAVING, WITH ROLLUP选项 任务4.4 应用系统内置函数查询 模块4： 查询MySQL记录 数据库 MySQL 4.4.1 数学函数 知识储备 统计MAJOR_GRADE_TERM_COURSES表中所有课程的平均学时、不小于平均值的整数、不大于平均值的整数 函数名称 含义 ABS(X) 返回X的绝对值 CEILING(X) 返回大于X的最小整数值 EXP(X) 返回e（自然对数的底数）的X幂 FLOOR(X) 返回小于X的最大整数值 LN(X) 返回X的自然对数 LOG(X,Y) 返回以Y为底的X的对数 MOD(X,Y) 返回X除以Y的余数（模） PI() 返回PI的值（圆周率） RAND() 返回0至1间的随机数 ROUND(X,Y) 返回X精确到小数点后Y位四舍五入的值 SIGN(X) 返回代表数字X的符号值（X小于0为-1，等于0为0，大于0为1） SQRT(X) 返回X的平方根 TRUNCATE(X,Y) 返回X精确到小数点后Y位不四舍五入的值 SELECT AVG(THOUR) 平均值, CEILING(AVG(THOUR)) 不小于值, FLOOR(AVG(THOUR)) 不大于值 FROM MAJOR_GRADE_TERM_COURSES; 同步操作 0 MySQL 4.4.1 数学函数 同步操作 USE STUDB; （1）统计MAJOR_GRADE_TERM_COURSES表中平均周课时（总课时除以周次)，精确到小数点后2位。 （2）统计MAJOR_GRADE_TERM_COURSES表中，按年级、专业分类汇总平均课时数，并使用CEILING()、FLOOR()函数求相应的值。 USE ERP; （3）针对GOODS表中STOREPRICE、SALEPRICE等数值字段，应用CEILING()、FLOOR()、ROUND()函数查询。 0 MySQL 4.4.2 字符串函数 知识储备 查询2000年出生的学生信息 SELECT * FROM STUDENTS WHERE LEFT(BIRTHDAY,4)=’2000’; 或 SELECT * FROM STUDENTS WHERE SUBSTRING(BIRTHDAY,1,4)=’2000’; 同步操作 函数名称 含义 LENGTH(str) 返回字符串str的长度 CONCAT(s1,s2) 返回一个或者多个字符串连接产生的新字符串 SUBSTRING(str,n,len) 获取从字符串str中第n个位置开始长度为len的字符串 LEFT(str,x) 返回字符串str中最左边x个字符 RIGHT(str,x) 返回字符串str中最右边x个字符 LOWER(str) 将字符串str中所有英文字母转化为小写字母 UPPER(str) 将字符串str中所有英文字母转化为大写字母 查询教师表TEACHERS，组合姓名工号输出，如：张三（900001） SELECT CONCAT(TNAME,'(',TNO,')') FROM TEACHERS; 0 MySQL 4.4.2 字符串函数 同步操作 USE STUDB; （1）学生信息表STUDENTS中有一个出生年月字段BIRTHDAY，数据格式为（YYYY-MM-DD)，查询所有在3月出生的学生信息。 （2）查询STUDENTS表中的学号（SNO）、姓名（SNAME）和创建日期（CREATETIME）的年份。 （3）针对STUDENTS表，组合姓名与学号字段输出，格式为：姓名（学号）。 USE ERP; （4）将GOODS表中商品名称GOODSNAME字段中英文字母以大写形式显示。 0 MySQL 4.4.3 日期和时间函数 知识储备 同步操作 查询学生出生日是星期几 SELECT CONCAT(BIRTHDAY,'(星期',WEEKDAY(BIRTHDAY),')') FROM STUDENTS; 函数名称 含义 CURDATE() 返回当前日期（YYYY-MM-DD） NOW() 返回当前日期时间（YYYY-MM-DD HH:MM:SS） YEAR(D) 返回日期D中的年份 MONTH(D) 返回日期D中的月份（1-12） WEEKDAY(D) 返回日期D是星期几（0表示周日） DAYOFMONTH(D) 返回日期D是本月的第几天 DATEDIFF(D1,D2) 返回日期D1-D2相隔的天数 DATE_ADD(D,INTERVAL X YEAR|MONTH|DAY) 在返回日期D基础上，加X年，或X月，或X天 0 MySQL 4.4.3 日期和时间函数 同步操作 USE STUDB; （1）将STUDENTS表创建时间CREATETIME更改为1年前。 （2）将STUDENTS表中是新时间UPTIME字段，所有ID为奇数的赋当前时间，所有ID为偶数的赋1月前的当前时间。 （3）将个人信息添加到STUDENTS表中（表添加有BIRTHDAY出生年月字段），查询所有人截至现在出生了多少天。 USE ERP; （4）查询SALES表中第一笔销售记录与最后一笔销售记录相隔多少天。 0 MySQL 4.4.4 条件判断函数 知识储备 同步操作 查询学生出生日是星期几，如：2000-10-16(星期日) SELECT SNO,SNAME,MCLASSID,CREATETIME,UPTIME, CONCAT(BIRTHDAY,'(星期', CASE WEEKDAY(BIRTHDAY) WHEN 0 THEN '日' WHEN 1 THEN '一' WHEN 2 THEN '二' WHEN 3 THEN '三' WHEN 4 THEN '四' WHEN 5 THEN '五' ELSE '六' END, ')') 出生年月 FROM STUDENTS; 函数名称 含义 IF(EXPR,V1,V2) 如果EXPR表达式为TRUE，返回V1，否则返回V2 IFNULL(V1,V2) 如果V1不为NULL，返回V1，否则返回V2 CASE EXPR WHEN V1 THEN R1 [WHEN V2 THEN R2 …] [ELSE RN] END 如果EXPR值等于V1、V2等，则返回对应位置的R1、R2的值，否则返回RN的值 0 MySQL 4.4.4 条件判断函数 同步操作 USE STUDB; （1）使用IF或IFNULL查询教师TEACHERS表，字段PHONE值为NULL的显示’’。 （2）查询STUDENTS表中出生年月（BIRTHDAY）字段对应的星期（如：星期日）。 USE ERP; （3）查询SALES表中“周一”销售总额。 0 MySQL 4.4.5 加密函数 知识储备 同步操作 查询学生表密码为‘abc’的学生信息（PWD字段已MD5加密） SELECT * FROM STUDENTS WHERE PWD=MD5(‘abc’); 函数名称 含义 PASSWORD(S) 密码加密不可逆 MD5(S) 普通加密，不可逆 ENCODE(STR,KEY) 返回一个二进制数，必须使用BLOB类型的字段来保存它,KEY为密钥 DECODE(STR,KEY) 使用KEY作为密钥解密加密字符串STR 0 MySQL 4.4.5 加密函数 同步操作 USE STUDB; （1）给STUDENTS表的密码字段PWD用PASSWORD函数进行加密。 （2）给教师信息TEACHERS表新增一个PWD密码字段，统一初始密码为“abc123”，用MD5加密。 （3）给STUDENTS表临时增加一个PWD2密码字段，用ENCODE加密，用DECODE解密。 0 MySQL 任 务 小 结 常用数学函数： RAND()、ROUND()、MOD()等 字符串函数 CONCAT()、SUBSTRING()、LOWER()、UPPER() 日期时间函数 NOW()、DATE_ADD()等 条件判断函数 IF()、IFNULL()、CASE...WHEN... THEN..等 加密函数 MD5()、PASSWORD()等 任务4.5 使用连接查询 模块4： 查询MySQL记录 数据库 MySQL 4.5.1 交叉连接查询 知识储备 同步操作 在关系型数据库中，表与表之间是有联系的，所以在实际应用中经常使用多表查询。多表查询主要有交叉连接、内连接、外连接、子查询。 交叉连接 ( CROSS JOIN ) 一般用来返回连接表的笛卡尔积。一般指两表记录间所有可能的组合。 显示信息技术学院教师（DEPARTID=1）担任计算机应用专业2021级班主任的所有可能性。 SELECT * FROM TEACHERS T1 CROSS JOIN MCLASSS T2 WHERE T1.DEPARTID=1 AND T2.MAJORID=1 AND T2.GRADE=2021; SELECT * FROM TEACHERS T1 JOIN MCLASSS T2 WHERE T1.DEPARTID=1 AND T2.MAJORID=1 AND T2.GRADE=2021; SELECT * FROM TEACHERS T1 , MCLASSS T2 WHERE T1.DEPARTID=1 AND T2.MAJORID=1 AND T2.GRADE=2021; 推荐 SELECT * FROM 左表 CROSS JOIN 右表 [WHERE ...] 0 MySQL 4.5.1 交叉连接查询 同步操作 （1）显示“计应2101”班学生与本专业课程的一对一关系。 （提示：STUDENTS表与MAJOR_GRADE_TERM_COURSES表之间的相关条件的笛卡尔积。） （2）显示马克思主义学院老师带马克思主义学院开出课程的所有可能性。 （提示：TEACHERS表与COURSES表的相关条件的笛卡尔积。） 0 MySQL 4.5.2 内连接查询 知识储备 同步操作 内连接也叫连接，它还可以被称为普通连接或自然连接。常用于有外键关系的表之间联合查询。 SELECT * | 字段名1，字段名2，… FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 [WHERE ...]； SELECT * | 字段名1，字段名2，… FROM 表1 JOIN 表2 ON 表1.外键=表2.主键 [WHERE ...]； SELECT * | 字段名1，字段名2，… FROM 表1 ，表2 WHERE 表1.外键=表2.主键； 推荐 0 MySQL 4.5.2 内连接查询 同步操作 USE STUDB; （1）查询班级及其所属专业，显示班级名称、专业名称。 （2）查询学生及其所在班级信息，显示学号、姓名、班级名称。 （3）查询学生学号、姓名、班级名称、院部名称信息。 USE ERP; （4）查询商品类型及其所属大类。（提示：TYPE表） （5）查询商品明细（SALESDETAIL）中所售商品名称、销售时间及销售人员姓名。 SELECT T1.MNAME,T2.DNAME FROM MAJORS T1,DEPARTS T2 WHERE T1.DEPARTID=T2.ID 显示所有专业名称和所属院部名称 0 MySQL 4.5.3 外连接查询 知识储备 同步操作 左连接是指将左表中的所有数据分别与右表中每条数据进行连接组合，返回的结果除内连接的数据外，还包含左表中不符合条件的数据，并在右表的相应列中添加NULL值。 SELECT * | 字段名1，字段名2，… FROM 表1 LEFT JOIN 表2 ON 表2.外键=表1.主键 [WHERE ...]； 左连接 0 MySQL 4.5.3 外连接查询 同步操作 USE STUDB; （1）查询所有老师在2021-2022-2学期的授课情况。 （提示：TEACHERS表与TEACHER_COURSES表的关系） （2）查询20级、21级各班级的教学任务安排情况。 （提示：MCLASSS表与TEACHER_COURSES表的关系） USE ERP; （3）查询所有商品的销售记录，显示商品名称、销售时间、销售员姓名。 （4）查询所有商品大类（PARENTID=NULL)的所有类别（二级类别名称）。 SELECT * FROM DEPARTS T1 LEFT JOIN TEACHERS T2 ON T2.DEPARTID=T1.ID 示例中涉及2个表，DEPARTS表中目前有“心理中心”部门还没分配员工，TEACHERS表中存在一名新到员工“张三”还没有分配部门。 查询所有部门与对应的教师信息。 0 MySQL 4.5.3 外连接查询 知识储备 同步操作 右连接是左连接的反向连接，右连接是指将右表中的所有数据分别与左表中每条数据进行连接组合，返回的结果除内连接的数据外，还包含右表中不符合条件的数据，并在左表的相应列中添加NULL。 SELECT * | 字段名1，字段名2，… FROM 表1 RIGHT JOIN 表2 ON 表2.外键=表1.主键 [WHERE ...]； 右连接 0 MySQL 4.5.3 外连接查询 同步操作 将如下左连接课堂练习用右链接实现： USE STUDB; （1）查询所有老师在2021-2022-2学期的授课情况。 （提示：TEACHERS表与TEACHER_COURSES表的关系） （2）查询20级、21级各班级的教学任务安排情况。 （提示：MCLASSS表与TEACHER_COURSES表的关系） USE ERP; （3）查询所有商品的销售记录，显示商品名称、销售时间、销售员姓名。 （4）查询所有商品大类（PARENTID=NULL)的所有类别（二级类别名称）。 SELECT * FROM DEPARTS T1 RIGHT JOIN TEACHERS T2 ON T2.DEPARTID=T1.ID; 查询所有教师对应的部门信息。 0 MySQL 4.5.3 外连接查询 知识储备 同步操作 全连接查询会显示左右表中全部数据，可以理解为在内连接基础上增加左右两边都没有显示的数据。 SELECT * | 字段名1，字段名2，… FROM 表1 LEFT JOIN 表2 ON 表2.外键=表1.主键 [WHERE ...] UNION SELECT * | 字段名1，字段名2，… FROM 表1 RIGHT JOIN 表2 ON 表2.外键=表1.主键 [WHERE ...] 全连接 0 MySQL 4.5.3 外连接查询 同步操作 （1）使用全外连接查询计算机应用技术专业的所有班级信息。 （2）使用全外连接查询类型名为“篮球鞋”的产品信息。 （3）使用全外连接查询类型名为“篮球鞋”的销售信息。 SELECT * FROM DEPARTS T1 LEFT JOIN TEACHERS T2 ON T2.DEPARTID=T1.ID UNION SELECT * FROM DEPARTS T1 RIGHT JOIN TEACHERS T2 ON T2.DEPARTID=T1.ID; 查询所有部门、所有教师对应信息。 0 MySQL 任 务 小 结 多表交叉连接查询 多表内连接查询（INNER JOIN... ON...） 多表外连接查询（左连接：LEFT JOIN...ON...） 任务4.6 创建子查询 数据库 MySQL 模块4： 查询 MySQL记录 4.6.1 子查询基础 知识储备 同步操作 子查询的实质是：一个SELECT子句的查询结果能够作为另一个子句的输入值。 编写复杂的子查询的解决思路是：逐层分解查询。即从最内层的子查询开始分解，将嵌套的SQL语句拆分为一个个独立的SQL语句。 一般情况下，联接查询可改为子查询实现，但子查询却不一定可改为联接查询实现。 方法一：（连接查询） SELECT * FROM TEACHERS T1,DEPARTS T2 WHERE T1.DEPARTID=T2.ID AND T2.DNAME=’信息技术学院’; 信息技术学院的所有老师信息。 方法二：（子查询） SELECT * FROM TEACHERS WHERE DEPARTID=(SELECT ID FROM DEPARTS WHERE DNAME=’信息技术学院’); 单行子查询 0 MySQL 4.6.1 子查询基础 同步操作 USE STUDB; （1）查询所有计算机应用技术专业的班级信息。 （2）查询马克思主义学院开出的所有课程信息。 （3）查询名为“人工智能2001”班的全体学生信息。 USE ERP; （4）使用子查询方法查询“BOMBA II 男子足球鞋”的销量。 （5）使用子查询方法查询“BOMBA II 男子足球鞋”的销售金额。 （6）使用子查询方法查询“李春波”的销量流水。 0 MySQL 4.6.1 子查询基础 同步操作 USE STUDB; （1）查询部门名称叫“信息技术学院”的所有班级信息。 （2）查询部门名称叫“信息技术学院”的所有学生信息。 USE ERP; （3）查询“BOMBA II 男子足球鞋”“FREE 5.0+ 男子跑步鞋”销售金额。 （4）查询“刘晓惠”“韩树清”的销量流水。 SELECT * FROM STUDENTS WHERE MCLASSID IN(SELECT ID FROM MCLASSS WHERE MAJORID=( SELECT ID FROM MAJORS WHERE MNAME=’人工智能技术应用’ )); 查询“人工智能技术应用”专业的所有学生信息 SELECT * FROM STUDENTS WHERE MCLASSID IN(SELECT ID FROM MCLASSS WHERE MAJORID=1); 查询专业编号为1的所有学生信息 多行子查询 0 MySQL 4.6.2 子查询进阶 知识储备 同步操作 子查询通常用于WHERE子句， 子查询可在FROM子句和SELECT子句中使用。 子查询还可作为表，进行进一步查询， 方法一：（连接查询） SELECT T1.TNO,T1.TNAME,T2.DNAME FROM TEACHERS T1,DEPARTS T2 WHERE T1.DEPARTID=T2.ID; 查询显示教师工号、姓名及院部名称。 方法三：（子查询） SELECT T1.TNO,T1.TNAME, (SELECT DNAME FROM DEPARTS WHERE ID=T1.DEPARTID) DNAME FROM TEACHERS T1; 方法二：（左接查询） SELECT T1.TNO,T1.TNAME,T2.DNAME FROM TEACHERS T1 LEFT JOIN DEPARTS T2 ON T1.DEPARTID=T2.ID; 0 MySQL 4.6.2 子查询进阶 同步操作 USE STUDB; （1）显示专业名称及其归属院部名称。 （2）显示班级名称、专业名称。 （3）显示学生学号、姓名及所在班级名称。 USE ERP; （4）查询商品信息及其归属类别。 （5）查询商品销售记录及销售员姓名。 使用子查询： 0 MySQL 4.6.2 子查询进阶 同步操作 方法一：（连接查询） SELECT T3.DNAME,T2.MNAME,T1.MNAME FROM MCLASSS T1,MAJORS T2,DEPARTS T3 WHERE T1.MAJORID=T2.ID AND T2.DEPARTID=T3.ID; 查询各院部开设的专业，以及各专业开出的班级信息，显示院部名称、专业名称、班级名称。 方法二：（含子查询） SELECT S2.CLASSNAME,S2.MNAME,S1.DNAME FROM DEPARTS S1, (SELECT T1.MNAME CLASSNAME,T2.* FROM MCLASSS T1,MAJORS T2 WHERE T1.MAJORID=T2.ID) S2 WHERE S2.DEPARTID=S1.ID; 方法三：（子查询） SELECT T1.MNAME CLASSNAME, (SELECT MNAME FROM MAJORS T2 WHERE T2.ID=T1.MAJORID), (SELECT DNAME FROM DEPARTS T3 WHERE T3.ID= (SELECT DEPARTID FROM MAJORS T2 WHERE T2.ID=T1.MAJORID)) DNAME FROM MCLASSS T1; 0 MySQL 4.6.2 子查询进阶 同步操作 USE STUDB; （1）查询所有学生的学号、姓名、所属班级名称、专业名称。 （2）查询教学任务表TEACHER_COURSES(ID,TERM,TEACHERID,MCLASSID,MGTCID),其中MGTCID是MAJOR_GRADE_TERM_COURSES表的外键，显示学期、教师工号、姓名、班级名称、课程名称。 USE ERP; （3）查询产品销售均价和已订购产品个数。 （4）显示产品子类型个数和上级产品类型个数。 使用子查询： 0 MySQL 任 务 小 结 单行、多行子查询 FROM子句中使用子查询 SELECT子句中使用子查询 UPDATE中使用子查询 DELETE中使用子查询 总 结 查询条件中的关系运算符、逻辑运算符使用 常用关键字DISTINCT、LIKE、BETWEEN...AND、LIMIT等 排序查询ORDER BY 聚合函数SUM、MAX/MIN、AVG、COUNT 分组查询GROUP BY 查询中使用数学、字符串、日期时间、判断等系统函数 连接查询、左连接 子查询 0 MySQL 谢 谢！ $$