单表查询

博主：关贞雨
发布时间：2023 年 08 月 22 日
196 次浏览
暂无评论
9798字数
分类： Web框架和数据库

一、基础查询

1、SELECT语句

（1）DQL（数据查询语言）

SQL语句的构成

（2）案例概述

一对多：城市和学生，班级和学生

多对多：学生和课程（创建新的关联关系表）-----中间表

数据库DQL案例

SELECT基本语法

SELECT column1, column2, ...
FROM table_name
[JOIN … ON … ] -- 连接到其他关联表
[WHERE condition] -- 查询条件，对表中的数据进行筛选
[GROUP BY column1, column2, ...]  -- 按指定的列进行分组
[HAVING condition]  -- 对分组后的结果进行过滤
[ORDER BY column1, column2, ... [ASC | DESC]]  -- 对结果按指定的列进行排序
[LIMIT ]  -- 限制结果集的返回行数

-- 查询语句基本语法：SELECT 列名1， 列名2，...FROM 表名;
SELECT name,age,title FROM teacher;

SELECT * FROM teacher; -- 查询表的所有列，开发时一般禁止使用*

SELECT 语句的执行顺序

1、FROM：指定要检索数据的表或视图

2、WHERE：对表中的数据进行筛选，只返回满足条件的记录

3、JOIN ON: 连接到其他关联表

4、GROUP BY：按照指定的列对结果进行分组

5、HAVING：对分组后的结果进行过滤

6、SELECT：选择需要检索的列，并计算聚合函数（如SUM、AVG等）

7、ORDER BY：按照指定的列对结果进行排序

8、LIMIT：限制结果集的返回行数

2、过滤查询结果

（1）WHERE子句

-- 使用WHERE子句 过滤查询结果
-- 查询一级讲师的名字，性别，年龄，工资
SELECT name, gender, age, salary
FROM teacher
WHERE title = '一级讲师';

-- 查看除了刘苍松以外的所有讲师名字，年龄，工资
-- 推荐使用<>表示不等于
SELECT name, age, salary
FROM teacher
WHERE name <> '刘苍松';

-- 查询职位为大队长的学生的名字，性别，年龄
SELECT name, gender, age
FROM student
WHERE job = '大队长';

-- 查询年龄在30（含）岁以上的老师的名字，年龄，工资，性别
SELECT name, age, salary, gender
FROM teacher
WHERE age > 30;

（2）使用AND和OR连接多个条件

AND的优先级大于OR

-- 年龄是7岁，职位是大队长列出姓名，年龄，性别，职位
SELECT name, age, gender, job
FROM student
WHERE age = 7
  AND job = '大队长';
  
-- 查询所有一级讲师和三级讲师，名字，职称，工资
SELECT name, title, salary
FROM teacher
WHERE title = '一级讲师'
   OR title = '三级讲师';
   
-- 查看所有的大队长中队长小队长的名字,性别,年龄,职位
SELECT name, gender, age, job
FROM student
WHERE job = '大队长'
   OR job = '中队长'
   OR job = '小队长';

（3）使用（）调整优先级

因为AND的优先级高于OR，在sql语句中可以使用（）来调整优先级

-- 查询班级编号在6（含）以下的
-- 所有大队长和中队长的名字，年龄，性别和职务
SELECT name, age, gender, job, class_id
FROM student
WHERE class_id <= 6
  AND (job = '大队长'
    OR job = '中队长');

（4）IN操作符

-- 查看所有大队长，中队长和小队长的名字，职位，年龄
SELECT name, job, age
FROM student
WHERE job IN ('大队长', '中队长', '小队长');

-- 查询所有的一级讲师和二级讲师和三级讲师的名字，职称，工资，性别
SELECT name, title, salary, gender
FROM teacher
WHERE title IN ('一级讲师', '二级讲师', '三级讲师');

（5）NOT IN 操作符

-- 查看除一级讲师和二级讲师之外所有老师的名字，职称，工资
SELECT name, title, salary
FROM teacher
WHERE title NOT IN ('一级讲师', '二级讲师');

-- 查看除了大队长中队长小队长之外的学生，名字，职位，性别，年龄
SELECT name, job, gender, age
FROM student
WHERE job NOT IN ('大队长', '中队长', '小队长');

（6）BETWEEN AND 操作符

-- 查询工资在2000到5000之间的老师的名字工资职称和性别
SELECT name, salary, title, gender
FROM teacher
WHERE salary BETWEEN 2000 AND 5000;

-- 查看年龄在7到10岁的学生，名字，性别，年龄
SELECT name, gender, age
FROM student
WHERE age BETWEEN 7 AND 10;

-- 查询年龄在20到35岁之间的男老师，名字，性别，年龄，职称
SELECT name, gender, age, title
FROM teacher
WHERE age BETWEEN 20 AND 35
  AND gender = '男';

-- 查询所有在3-5层的班级，列出班级名称和所在楼
SELECT name, floor
FROM class
WHERE floor BETWEEN 3 AND 5;

（7）去除查询结果中重复的行

-- 查看老师都有哪些职位
SELECT DISTINCT title
FROM teacher;

-- 查看学生的职位有哪些
SELECT DISTINCT job
FROM student

-- 查看学生各个年龄段的职位都有哪些 两个加在一起不一样
SELECT DISTINCT age,job
FROM student

（8）LIKE模糊查询

-- 查询名字中含苍的老师都有谁
SELECT *
FROM teacher
WHERE name LIKE '%苍%';

-- 查询姓张的学生都有谁
SELECT name, age, job, gender
FROM student
WHERE name LIKE '张%';

-- 查看名字最后一个字是'晶'的老师
SELECT name, age, gender
FROM teacher
WHERE name LIKE '%晶';

-- 查询三个名字中，第二个字是"平"的学生都有谁
SELECT name
FROM student
WHERE name LIKE '_平_';

-- 查看哪些学生是课代表
SELECT name, job
FROM student
WHERE job LIKE '%课代表';

-- 查看所有2班都在几层
SELECT name, floor
FROM class
WHERE name LIKE '%2班';

（9）NULL值判断

-- 查询没有奖金的老师都有谁
SELECT name,comm
FROM teacher
WHERE comm IS NULL;

-- 查看有奖金的老师都有谁
SELECT name,comm
FROM teacher
WHERE comm IS NOT NULL;

3、对查询结果排序

（1）ORDER BY 子句

-- 查看老师的工资牌名，按最后工资降序排列
SELECT name, salary
FROM teacher
ORDER BY salary DESC;

-- 查看所有老师的奖金情况，从少到多排序
SELECT name, comm
FROM teacher
ORDER BY comm;

-- 查看学生的生日，并且生日由早到晚排序
SELECT name, birth
FROM student
ORDER BY birth;

-- 查看7-10岁的学生信息，学生排序按照从小到大排序
SELECT name, age, birth
FROM student
WHERE age BETWEEN 7 AND 10
ORDER BY birth DESC;

-- 查看老师的工资和奖金，首先按照奖金的升序，再按照工资的降序
SELECT name, salary, comm
FROM teacher
ORDER BY comm, salary DESC;

（2）LIMIT和分页查询

offset = (页号-1) * 页面行数
row_count = 页面行数

-- 查看老师奖金信息，按照降序排序后，每页显示3条，显示第五页
SELECT name,comm
FROM teacher
ORDER BY comm DESC
LIMIT 12,3;

二、函数和表达式

1、表达式和空值函数

（1）表达式生成结果集

-- 函数和表达式
-- 查看老师的工资和年薪
SELECT name, salary, comm, (IFNULL(comm, 0) + salary) * 12 as YEARS
FROM teacher;

（2）在WHERE子句中使用表达式

-- 在WHERE子句中使用表达式
-- 查看年薪小于60000的讲师都有谁
SELECT name, salary, salary * 12
FROM teacher
WHERE salary * 12 < 60000;

-- 查询奖金小于3000的讲师
SELECT name, comm
FROM teacher
WHERE IFNULL(comm, 0) < 3000;

（3）别名

-- 为查询的字段起别名
SELECT name, salary, salary * 12 AS annual_salary
FROM teacher;

（4）练习：函数和表达式练习

-- 查询所有10岁学生的生日，按生日对应的年纪从大到小
SELECT name, age, birth
FROM student
WHERE age = 10
ORDER BY birth;

-- 查询8岁同学中名字含有"苗"的学生信息
SELECT name, age, gender
FROM student
WHERE age = 8
  AND name LIKE '%苗%';

-- 查询负责课程编号1和2号且工资高于6000的老师信息
SELECT *
FROM teacher
WHERE (subject_id = 1 OR subject_id = 2)
  AND salary > 6000;

-- 查询10岁以上的语文课代表和数学课代表
SELECT *
FROM student
WHERE age > 10
  AND (job = '语文课代表'
    OR job = '数学课代表');

-- 查询不教课程编号1的老师信息，按照工资降序排序
SELECT *
FROM teacher
WHERE subject_id NOT IN (1)
ORDER BY salary DESC;

-- 查询没有奖金的老师信息
SELECT *
FROM teacher
WHERE IFNULL(comm, 0) = 0;

-- 查询所有老师的奖金，并按照奖金降序排序
SELECT name,comm
FROM teacher
ORDER BY comm DESC ;

-- 查看工资高于8000的老师负责的课程编号
SELECT name,subject_id
FROM teacher
WHERE salary >8000;

-- 查看全校年龄最小学生的第6-10名
SELECT *
FROM student
ORDER BY age
LIMIT 5,5

2、聚合函数

（1）常用的聚合函数

AVG(): 用于计算指定列的平均值
SUM(): 用于计算指定列的总和
MIN(): 用于返回指定列的最小值
MAX(): 用于返回指定列的最大值
COUNT(): 用于返回指定列中的行数

-- 查看所有老师的平均工资
SELECT AVG(salary)
FROM teacher;

-- 查看老师的最高工资，最低工资，平均工资和工资总和
SELECT MAX(salary) AS `最高工资`,MIN(salary) `最低工资`,AVG(salary) `平均工资`,SUM(salary) `工资总和`
FROM teacher;

-- 查看课程编号是1的老师的平均工资是多少
SELECT AVG(salary) `平均工资`
FROM teacher
WHERE subject_id=1;

（2）聚合函数忽略NULL值

-- 计算老师的平均奖金
SELECT AVG(IFNULL(comm, 0)) `平均工资`
FROM teacher;

-- 查看负责课程编号2的老师共多少人
SELECT COUNT(*)
FROM teacher
WHERE subject_id=2;

-- 查看全校学生生日最大的是哪一天
SELECT MAX(birth)
FROM student;

-- 查看11岁的课代表共有多少人
SELECT COUNT(*)
FROM student
WHERE age = 11
  AND job LIKE '%课代表';

三、分组查询

1、分组聚合

GROUP BY子句是仅用于配合聚合函数的

如果SELECT 中没有聚合函数，就不会使用GROUP BY
在SELECT中出现了聚合函数，那么不在聚合函数的中的其他字段都应当出现在GROUP BY子句中

（1）GROUP BY子句

-- 按照课程编号进行分组，查看教师的平均工资
SELECT AVG(salary),subject_id
FROM teacher
GROUP BY subject_id;

-- 查看每个班各多少人
SELECT COUNT(*), class_id
FROM student
GROUP BY class_id;

-- 查看学校每种职位的学生各多少人，以及最大生日和最小生日
SELECT COUNT(*), job, MAX(birth), MIN(birth)
FROM student
GROUP BY job;

（2）按照多字段分组

-- 查看每个班每种职务多少人
SELECT COUNT(*), class_id, job
FROM student
GROUP BY class_id, job;

（3）按照聚合函数排序

-- 查看每个科目老师的平均工资，并由少到多展示
SELECT AVG(salary), subject_id
FROM teacher
GROUP BY subject_id
ORDER BY AVG(salary);

2、过滤分组结果

（1）HAVING子句

-- 查看各科目老师的平均工资，但是仅查看高于6000的那些科目老师的平均工资
-- WHERE子句不能使用聚合
SELECT AVG(salary) `avg` ,subject_id
from teacher
GROUP BY subject_id
HAVING avg>6000;

-- 查看科目老师的工资总和（前提是该科老师平均奖金高于4000）
SELECT SUM(salary), avg(ifnull(comm, 0)), subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(IFNULL(comm, 0)) > 4000;

（2）HAVING子句与WHERE子句

聚合结果过滤是不能使用 WHERE子句

将聚合函数放在WHERE中进行过滤会出现上述错误：WHERE子句中不能使用聚合函数。究其原因，WHERE与HAVING的过滤时机不同：

WHERE 关键字用于过滤从表中检索出来的记录：它可以根据一个或多个条件从表中选择出符合条件的记录，然后返回这些记录
HAVING 关键字用于过滤分组后的数据：它通常与 GROUP BY 关键字一起使用，用于对分组后的数据进行筛选，仅返回符合特定条件的分组

可以简单理解为：WHERE先进行过滤查询结果，用于确定结果集；HAVING后过滤，用于确定GOURP BY分组聚合以后的数据。

四、子查询

1、DQL使用子查询

（1）子查询概述

查询中嵌套查询

（2）在DQL中使用标量子查询

标量子查询，子查询的结果只返回一行一列的数据。

-- 查看哪个老师的工资高于王克晶的工资？查询思路如下：
-- 查看王克晶的工资是多少?
SELECT salary
FROM teacher
WHERE name = '王克晶';

-- 上一个查询结果是8000，查询工资大于8000的老师
SELECT name, salary
FROM teacher
WHERE salary > 8000;

-- 整合
SELECT name,salary
FROM teacher
WHERE salary>(SELECT salary FROM teacher WHERE name = '王克晶');

-- 查看高于平均工资的那些老师的工资都是多少?
SELECT salary
FROM teacher
WHERE salary>(SELECT AVG(salary) FROM teacher);

-- 查询和李费水同一班的学生
SELECT name, age, gender, class_id
FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name = '李费水');

（3）使用多行单列子查询

\>ANY ：大于子查询列表中的其中之一，大于最小的即可
<ANY ：小于子查询列表中其中之一，小于最大的即可
\>ALL：大于子查询列表中所有的，大于最大的
<ALL：小于子查询列表中所有的，小于最小的

-- 查看与"祝雷"和"李费水"在同一个班的学生都有谁?
SELECT name,class_id
from student
where class_id in (select class_id from student where name in ('祝雷','李费水'));

-- 查看比教科目2和科目4老师工资都高的老师都有谁?
SELECT name
FROM teacher
WHERE salary > ALL (SELECT salary FROM teacher WHERE subject_id IN (2, 4));

2、DDL使用子查询

使用子查询创建表

子查询可以得到多行多列的值，这样的子查询结果可以用于建立新的表。

有需求如下：

创建一张表用于记录老师工资的统计情况(按照科目)
要记录每门课老师的最高工资，最低工资，平均工资和工资总和以及科目编号
表名为teacher_salary_info

CREATE TABLE teacher_salary_info
AS
SELECT MAX(salary) `max`,
       MIN(salary) `min`,
       AVG(salary) `avg`,
       SUM(salary) `sum`,
       subject_id
FROM teacher
GROUP BY subject_id;

五、练习

1 数据库基础查询练习

针对业务案例库，进行如下查询：

-- 查看负责课程编号(subject_id)3和4的女老师都有谁?
SELECT name, gender, subject_id
FROM teacher
WHERE gender = '女'
  AND (subject_id = 3
    OR subject_id = 4);

-- 查看工资高于5600的老女师都有谁?
SELECT name, gender, salary
FROM teacher
WHERE gender = '女'
  AND salary > 5600;

-- 查看奖金高于3000的男老师或女老师的工资？
desc teacher;
SELECT name, gender, salary
FROM teacher
WHERE comm > 3000;

-- 查看所有10岁以下学生中的小队长和语文课代表都是谁?
SELECT name, age, job
FROM student
WHERE age < 10
  AND job IN ('小队长', '语文课代表');

-- 查看奖金在1000到4000之间的老师以及具体工资?
SELECT name, salary, comm
FROM teacher
WHERE comm BETWEEN 1000 AND 4000;

-- 查看工资在4000到8000之间的老师及具体工资?
SELECT name, salary, comm
FROM teacher
WHERE salary BETWEEN 4000 AND 8000;

-- 查看男老师负责的课程编号都有什么?
SELECT DISTINCT gender, subject_id
FROM teacher
WHERE gender = '男';

-- 查看所有男老师的职称和课程编号都是什么?
SELECT DISTINCT gender, title, subject_id
FROM teacher
WHERE gender = '男';

-- 查看10-20岁的女同学的职位都有哪些?
SELECT DISTINCT age, gender, job
FROM student
WHERE age BETWEEN 10 AND 20
  AND gender = '女';

-- 查看二级讲师和三级讲师的奖金(comm)和薪酬是多少?
SELECT DISTINCT title, comm, salary
FROM teacher
WHERE title IN ('二级讲师', '三级讲师');

-- 查看除了老板、总监、一级讲师的其他老师的工资是多少?
SELECT name, title, comm, salary
FROM teacher
WHERE title NOT IN ('老板', '总监', '一级讲师');

-- 查看没有奖金的老师都有谁和薪资是多少？
SELECT name, salary, comm
FROM teacher
WHERE comm = 0 OR comm IS NULL;

2 LIKE查询练习

针对业务案例库，进行如下查询：

-- 查询名字姓"李" 和姓"张"的学生姓名
SELECT name
FROM student
WHERE name LIKE '李%'
   OR name LIKE '张%';

-- 查询名字中包含"华"的学生姓名
SELECT name
FROM student
WHERE name LIKE '%华%';

-- 查询名字以"乐"结尾的学生姓名
SELECT name
FROM student
WHERE name LIKE '%乐';

-- 查询7岁里是"课代表"的学生信息
SELECT *
FROM student
WHERE age = 7
  AND job LIKE '%课代表';

-- 查询名字第二个字是"熊"的学生信息
SELECT *
FROM student
WHERE name LIKE '_熊_';

-- 查询姓"常"的队长都是谁?
SELECT name, job
FROM student
WHERE name LIKE '常%'
  AND job LIKE '%队长'

3 排序、分组和聚合查询练习

针对业务案例库，进行如下查询：

-- 查询所有老师薪资和奖金总和大于8000的，并且按照升序输出全部信息
SELECT *
FROM teacher
where salary + comm > 8000
ORDER BY salary + comm;

-- 查询8岁同学中名字含有"苗"的学生数量
SELECT COUNT(*) `学生数量`
FROM student
WHERE age = 8
  AND name LIKE '%苗%';

-- 查询负责课程编号1和2号且年龄大于35的老师信息
SELECT *
FROM teacher
WHERE (subject_id = 1 OR subject_id = 2)
  AND age > 35;

-- 查询10岁以上的课代表总数
SELECT COUNT(*) `总数`
FROM student
WHERE age > 10
  AND job LIKE '%课代表';

-- 查询不教课程编号1的老师信息,按照年龄降序排序
SELECT *
FROM teacher
WHERE subject_id NOT IN (1)
ORDER BY age DESC;

-- 查询没有奖金的老师数量
SELECT COUNT(*) `数量`
FROM teacher
WHERE IFNULL(comm, 0) = 0;

-- 查询所有课代表年龄，并按年龄降序排序
SELECT name, age, job
FROM student
WHERE job LIKE '%课代表'
ORDER BY age DESC;

-- 查看姓李的学生每个年龄段有多少人?
SELECT COUNT(*),name,age
FROM student
WHERE name LIKE '李%'
GROUP BY age;

4 排序、分组和聚合、子查询练习

针对业务案例库，进行如下查询：

-- 统计每个城市的学生数量
SELECT COUNT(*) `学生数量`, location_id
FROM student
GROUP BY location_id;

-- 统计每个组长管理的学生平均年龄
SELECT AVG(age) `平均年龄`, team_leader
FROM student
GROUP BY team_leader;

-- 统计每个城市中年龄最小和最大的学生
SELECT MIN(age) `年龄最小`, MAX(age) `年龄最大`, name, location_id
FROM student
GROUP BY location_id;

-- 统计每个班级中，职位为"班长"的学生数
SELECT COUNT(*), class_id
FROM student
WHERE job = '班长'
GROUP BY class_id;

-- 统计每个科目的老师人数及平均年龄，按照科目编号升序排序
SELECT COUNT(*) `人数`, AVG(age) `平均年龄`, subject_id
FROM teacher
GROUP BY subject_id
ORDER BY subject_id;

-- 统计每个性别的老师人数及其平均工资和奖金总额
SELECT COUNT(*), gender, AVG(salary), SUM(salary)
FROM teacher
GROUP BY gender;

-- 统计每个上司的管理老师人数及其平均工资，按照管理老师人数降序排序
SELECT COUNT(*),subject_id,AVG(salary)
FROM teacher
WHERE title NOT IN ('总监','老板')
GROUP BY subject_id;

-- 查询乐明所在班级的所有学生信息
SELECT *
FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name = '乐明');

最后修改：2023 年 09 月 02 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

单表查询

关贞雨 • 2023 年 08 月 22 日

<h2>一、基础查询</h2><h3>1、SELECT语句</h3><h4>（1）DQL（数据查询语言）</h4><p><img src="https://s1.ax1x.com/2023/08/22/pPJN3kV.png" alt="SQL语句的构成" title="SQL语句的构成"style=""></p><h4>（2）案例概述</h4><p>一对多：城市和学生，班级和学生</p><p>多对多：学生和课程（创建新的关联关系表）-----中间表</p><p><img src="https://s1.ax1x.com/2023/08/22/pPJNlT0.png" alt="数据库DQL案例" title="数据库DQL案例"style=""></p><h5>SELECT基本语法</h5><pre><code class="lang-sql">SELECT column1, column2, ...
FROM table_name
[JOIN … ON … ] -- 连接到其他关联表
[WHERE condition] -- 查询条件，对表中的数据进行筛选
[GROUP BY column1, column2, ...]  -- 按指定的列进行分组
[HAVING condition]  -- 对分组后的结果进行过滤
[ORDER BY column1, column2, ... [ASC | DESC]]  -- 对结果按指定的列进行排序
[LIMIT ]  -- 限制结果集的返回行数</code></pre><pre><code class="lang-sql">-- 查询语句基本语法：SELECT 列名1， 列名2，...FROM 表名;
SELECT name,age,title FROM teacher;

SELECT * FROM teacher; -- 查询表的所有列，开发时一般禁止使用*</code></pre><h5>SELECT 语句的执行顺序</h5><p>1、FROM：指定要检索数据的表或视图</p><p>2、WHERE：对表中的数据进行筛选，只返回满足条件的记录</p><p>3、JOIN ON: 连接到其他关联表</p><p>4、GROUP BY：按照指定的列对结果进行分组</p><p>5、HAVING：对分组后的结果进行过滤</p><p>6、SELECT：选择需要检索的列，并计算聚合函数（如SUM、AVG等）</p><p>7、ORDER BY：按照指定的列对结果进行排序</p><p>8、LIMIT：限制结果集的返回行数</p><h3>2、过滤查询结果</h3><h4>（1）WHERE子句</h4><pre><code class="lang-sql">-- 使用WHERE子句 过滤查询结果
-- 查询一级讲师的名字，性别，年龄，工资
SELECT name, gender, age, salary
FROM teacher
WHERE title = '一级讲师';

-- 查看除了刘苍松以外的所有讲师名字，年龄，工资
-- 推荐使用&lt;&gt;表示不等于
SELECT name, age, salary
FROM teacher
WHERE name &lt;&gt; '刘苍松';

-- 查询职位为大队长的学生的名字，性别，年龄
SELECT name, gender, age
FROM student
WHERE job = '大队长';

-- 查询年龄在30（含）岁以上的老师的名字，年龄，工资，性别
SELECT name, age, salary, gender
FROM teacher
WHERE age &gt; 30;</code></pre><h4>（2）使用AND和OR连接多个条件</h4><p><strong>AND的优先级大于OR</strong></p><pre><code class="lang-sql">-- 年龄是7岁，职位是大队长列出姓名，年龄，性别，职位
SELECT name, age, gender, job
FROM student
WHERE age = 7
  AND job = '大队长';
  
-- 查询所有一级讲师和三级讲师，名字，职称，工资
SELECT name, title, salary
FROM teacher
WHERE title = '一级讲师'
   OR title = '三级讲师';
   
-- 查看所有的大队长中队长小队长的名字,性别,年龄,职位
SELECT name, gender, age, job
FROM student
WHERE job = '大队长'
   OR job = '中队长'
   OR job = '小队长';</code></pre><h4>（3）使用（）调整优先级</h4><p>因为AND的优先级高于OR，在sql语句中可以使用（）来调整优先级</p><pre><code class="lang-sql">-- 查询班级编号在6（含）以下的
-- 所有大队长和中队长的名字，年龄，性别和职务
SELECT name, age, gender, job, class_id
FROM student
WHERE class_id &lt;= 6
  AND (job = '大队长'
    OR job = '中队长');</code></pre><h4>（4）IN操作符</h4><pre><code class="lang-sql">-- 查看所有大队长，中队长和小队长的名字，职位，年龄
SELECT name, job, age
FROM student
WHERE job IN ('大队长', '中队长', '小队长');

-- 查询所有的一级讲师和二级讲师和三级讲师的名字，职称，工资，性别
SELECT name, title, salary, gender
FROM teacher
WHERE title IN ('一级讲师', '二级讲师', '三级讲师');</code></pre><h4>（5）NOT IN 操作符</h4><pre><code class="lang-sql">-- 查看除一级讲师和二级讲师之外所有老师的名字，职称，工资
SELECT name, title, salary
FROM teacher
WHERE title NOT IN ('一级讲师', '二级讲师');

-- 查看除了大队长中队长小队长之外的学生，名字，职位，性别，年龄
SELECT name, job, gender, age
FROM student
WHERE job NOT IN ('大队长', '中队长', '小队长');</code></pre><h4>（6）BETWEEN AND 操作符</h4><pre><code class="lang-sql">-- 查询工资在2000到5000之间的老师的名字工资职称和性别
SELECT name, salary, title, gender
FROM teacher
WHERE salary BETWEEN 2000 AND 5000;

-- 查看年龄在7到10岁的学生，名字，性别，年龄
SELECT name, gender, age
FROM student
WHERE age BETWEEN 7 AND 10;

-- 查询年龄在20到35岁之间的男老师，名字，性别，年龄，职称
SELECT name, gender, age, title
FROM teacher
WHERE age BETWEEN 20 AND 35
  AND gender = '男';

-- 查询所有在3-5层的班级，列出班级名称和所在楼
SELECT name, floor
FROM class
WHERE floor BETWEEN 3 AND 5;</code></pre><h4>（7）去除查询结果中重复的行</h4><pre><code class="lang-sql">-- 查看老师都有哪些职位
SELECT DISTINCT title
FROM teacher;

-- 查看学生的职位有哪些
SELECT DISTINCT job
FROM student

-- 查看学生各个年龄段的职位都有哪些 两个加在一起不一样
SELECT DISTINCT age,job
FROM student</code></pre><h4>（8）LIKE模糊查询</h4><pre><code class="lang-sql">-- 查询名字中含苍的老师都有谁
SELECT *
FROM teacher
WHERE name LIKE '%苍%';

-- 查询姓张的学生都有谁
SELECT name, age, job, gender
FROM student
WHERE name LIKE '张%';

-- 查看名字最后一个字是'晶'的老师
SELECT name, age, gender
FROM teacher
WHERE name LIKE '%晶';

-- 查询三个名字中，第二个字是&quot;平&quot;的学生都有谁
SELECT name
FROM student
WHERE name LIKE '_平_';

-- 查看哪些学生是课代表
SELECT name, job
FROM student
WHERE job LIKE '%课代表';

-- 查看所有2班都在几层
SELECT name, floor
FROM class
WHERE name LIKE '%2班';</code></pre><h4>（9）NULL值判断</h4><pre><code class="lang-sql">-- 查询没有奖金的老师都有谁
SELECT name,comm
FROM teacher
WHERE comm IS NULL;

-- 查看有奖金的老师都有谁
SELECT name,comm
FROM teacher
WHERE comm IS NOT NULL;</code></pre><h3>3、对查询结果排序</h3><h4>（1）ORDER BY 子句</h4><pre><code class="lang-sql">-- 查看老师的工资牌名，按最后工资降序排列
SELECT name, salary
FROM teacher
ORDER BY salary DESC;

-- 查看所有老师的奖金情况，从少到多排序
SELECT name, comm
FROM teacher
ORDER BY comm;

-- 查看学生的生日，并且生日由早到晚排序
SELECT name, birth
FROM student
ORDER BY birth;

-- 查看7-10岁的学生信息，学生排序按照从小到大排序
SELECT name, age, birth
FROM student
WHERE age BETWEEN 7 AND 10
ORDER BY birth DESC;

-- 查看老师的工资和奖金，首先按照奖金的升序，再按照工资的降序
SELECT name, salary, comm
FROM teacher
ORDER BY comm, salary DESC;</code></pre><h4>（2）LIMIT和分页查询</h4><ul><li>offset = (页号-1) * 页面行数</li><li>row_count = 页面行数</li></ul><pre><code class="lang-sql">-- 查看老师奖金信息，按照降序排序后，每页显示3条，显示第五页
SELECT name,comm
FROM teacher
ORDER BY comm DESC
LIMIT 12,3;</code></pre><h2>二、函数和表达式</h2><h3>1、表达式和空值函数</h3><h4>（1）表达式生成结果集</h4><pre><code class="lang-sql">-- 函数和表达式
-- 查看老师的工资和年薪
SELECT name, salary, comm, (IFNULL(comm, 0) + salary) * 12 as YEARS
FROM teacher;</code></pre><h4>（2）在WHERE子句中使用表达式</h4><pre><code class="lang-sql">-- 在WHERE子句中使用表达式
-- 查看年薪小于60000的讲师都有谁
SELECT name, salary, salary * 12
FROM teacher
WHERE salary * 12 &lt; 60000;

-- 查询奖金小于3000的讲师
SELECT name, comm
FROM teacher
WHERE IFNULL(comm, 0) &lt; 3000;</code></pre><h4>（3）别名</h4><pre><code class="lang-sql">-- 为查询的字段起别名
SELECT name, salary, salary * 12 AS annual_salary
FROM teacher;</code></pre><h4>（4）练习：函数和表达式练习</h4><pre><code class="lang-java">-- 查询所有10岁学生的生日，按生日对应的年纪从大到小
SELECT name, age, birth
FROM student
WHERE age = 10
ORDER BY birth;

-- 查询8岁同学中名字含有&quot;苗&quot;的学生信息
SELECT name, age, gender
FROM student
WHERE age = 8
  AND name LIKE '%苗%';

-- 查询负责课程编号1和2号且工资高于6000的老师信息
SELECT *
FROM teacher
WHERE (subject_id = 1 OR subject_id = 2)
  AND salary &gt; 6000;

-- 查询10岁以上的语文课代表和数学课代表
SELECT *
FROM student
WHERE age &gt; 10
  AND (job = '语文课代表'
    OR job = '数学课代表');

-- 查询不教课程编号1的老师信息，按照工资降序排序
SELECT *
FROM teacher
WHERE subject_id NOT IN (1)
ORDER BY salary DESC;

-- 查询没有奖金的老师信息
SELECT *
FROM teacher
WHERE IFNULL(comm, 0) = 0;

-- 查询所有老师的奖金，并按照奖金降序排序
SELECT name,comm
FROM teacher
ORDER BY comm DESC ;

-- 查看工资高于8000的老师负责的课程编号
SELECT name,subject_id
FROM teacher
WHERE salary &gt;8000;

-- 查看全校年龄最小学生的第6-10名
SELECT *
FROM student
ORDER BY age
LIMIT 5,5</code></pre><h3>2、聚合函数</h3><h4>（1）常用的聚合函数</h4><ul><li>AVG(): 用于计算指定列的平均值</li><li>SUM(): 用于计算指定列的总和</li><li>MIN(): 用于返回指定列的最小值</li><li>MAX(): 用于返回指定列的最大值</li><li>COUNT(): 用于返回指定列中的行数</li></ul><pre><code class="lang-sql">-- 查看所有老师的平均工资
SELECT AVG(salary)
FROM teacher;

-- 查看老师的最高工资，最低工资，平均工资和工资总和
SELECT MAX(salary) AS `最高工资`,MIN(salary) `最低工资`,AVG(salary) `平均工资`,SUM(salary) `工资总和`
FROM teacher;

-- 查看课程编号是1的老师的平均工资是多少
SELECT AVG(salary) `平均工资`
FROM teacher
WHERE subject_id=1;</code></pre><h4>（2）聚合函数忽略NULL值</h4><pre><code class="lang-sql">-- 计算老师的平均奖金
SELECT AVG(IFNULL(comm, 0)) `平均工资`
FROM teacher;

-- 查看负责课程编号2的老师共多少人
SELECT COUNT(*)
FROM teacher
WHERE subject_id=2;

-- 查看全校学生生日最大的是哪一天
SELECT MAX(birth)
FROM student;

-- 查看11岁的课代表共有多少人
SELECT COUNT(*)
FROM student
WHERE age = 11
  AND job LIKE '%课代表';</code></pre><h2>三、分组查询</h2><h3>1、分组聚合</h3><p>GROUP BY子句是仅用于配合聚合函数的</p><ul><li>如果SELECT 中没有聚合函数，就不会使用GROUP BY</li><li>在SELECT中出现了聚合函数，那么不在聚合函数的中的其他字段都应当出现在GROUP BY子句中</li></ul><h4>（1）GROUP BY子句</h4><pre><code class="lang-sql">-- 按照课程编号进行分组，查看教师的平均工资
SELECT AVG(salary),subject_id
FROM teacher
GROUP BY subject_id;

-- 查看每个班各多少人
SELECT COUNT(*), class_id
FROM student
GROUP BY class_id;

-- 查看学校每种职位的学生各多少人，以及最大生日和最小生日
SELECT COUNT(*), job, MAX(birth), MIN(birth)
FROM student
GROUP BY job;</code></pre><h4>（2）按照多字段分组</h4><pre><code class="lang-sql">-- 查看每个班每种职务多少人
SELECT COUNT(*), class_id, job
FROM student
GROUP BY class_id, job;</code></pre><h4>（3）按照聚合函数排序</h4><pre><code class="lang-sql">-- 查看每个科目老师的平均工资，并由少到多展示
SELECT AVG(salary), subject_id
FROM teacher
GROUP BY subject_id
ORDER BY AVG(salary);</code></pre><h3>2、过滤分组结果</h3><h4>（1）HAVING子句</h4><pre><code class="lang-sql">-- 查看各科目老师的平均工资，但是仅查看高于6000的那些科目老师的平均工资
-- WHERE子句不能使用聚合
SELECT AVG(salary) `avg` ,subject_id
from teacher
GROUP BY subject_id
HAVING avg&gt;6000;

-- 查看科目老师的工资总和（前提是该科老师平均奖金高于4000）
SELECT SUM(salary), avg(ifnull(comm, 0)), subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(IFNULL(comm, 0)) &gt; 4000;</code></pre><h4>（2）HAVING子句与WHERE子句</h4><p><strong>聚合结果过滤是不能使用 WHERE子句</strong></p><p>将聚合函数放在WHERE中进行过滤会出现上述错误：WHERE子句中不能使用聚合函数。究其原因，WHERE与HAVING的过滤时机不同：</p><ul><li>WHERE 关键字用于过滤从表中检索出来的记录：它可以根据一个或多个条件从表中选择出符合条件的记录，然后返回这些记录</li><li>HAVING 关键字用于过滤分组后的数据：它通常与 GROUP BY 关键字一起使用，用于对分组后的数据进行筛选，仅返回符合特定条件的分组</li></ul><p>可以简单理解为：WHERE先进行过滤查询结果，用于确定结果集；HAVING后过滤，用于确定GOURP BY分组聚合以后的数据。</p><h2>四、子查询</h2><h3>1、DQL使用子查询</h3><h4>（1）子查询概述</h4><p>查询中嵌套查询</p><h4>（2）在DQL中使用标量子查询</h4><p>标量子查询，子查询的结果只返回一行一列的数据。</p><pre><code class="lang-sql">-- 查看哪个老师的工资高于王克晶的工资？查询思路如下：
-- 查看王克晶的工资是多少?
SELECT salary
FROM teacher
WHERE name = '王克晶';

-- 上一个查询结果是8000，查询工资大于8000的老师
SELECT name, salary
FROM teacher
WHERE salary &gt; 8000;

-- 整合
SELECT name,salary
FROM teacher
WHERE salary&gt;(SELECT salary FROM teacher WHERE name = '王克晶');

-- 查看高于平均工资的那些老师的工资都是多少?
SELECT salary
FROM teacher
WHERE salary&gt;(SELECT AVG(salary) FROM teacher);

-- 查询和李费水同一班的学生
SELECT name, age, gender, class_id
FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name = '李费水');</code></pre><h4>（3）使用多行单列子查询</h4><ul><li>\&gt;ANY ：大于子查询列表中的其中之一，大于最小的即可</li><li>&lt;ANY ：小于子查询列表中其中之一，小于最大的即可</li><li>\&gt;ALL：大于子查询列表中所有的，大于最大的</li><li>&lt;ALL：小于子查询列表中所有的，小于最小的</li></ul><pre><code>-- 查看与&quot;祝雷&quot;和&quot;李费水&quot;在同一个班的学生都有谁?
SELECT name,class_id
from student
where class_id in (select class_id from student where name in ('祝雷','李费水'));

-- 查看比教科目2和科目4老师工资都高的老师都有谁?
SELECT name
FROM teacher
WHERE salary &gt; ALL (SELECT salary FROM teacher WHERE subject_id IN (2, 4));</code></pre><h3>2、DDL使用子查询</h3><h4>使用子查询创建表</h4><p>子查询可以得到多行多列的值，这样的子查询结果可以用于建立新的表。</p><p>有需求如下：</p><ul><li>创建一张表用于记录老师工资的统计情况(按照科目)</li><li>要记录每门课老师的最高工资，最低工资，平均工资和工资总和以及科目编号</li><li>表名为teacher_salary_info</li></ul><pre><code class="lang-sql">CREATE TABLE teacher_salary_info
AS
SELECT MAX(salary) `max`,
       MIN(salary) `min`,
       AVG(salary) `avg`,
       SUM(salary) `sum`,
       subject_id
FROM teacher
GROUP BY subject_id;</code></pre><h2>五、练习</h2><h3>1 数据库基础查询练习</h3><p>针对业务案例库，进行如下查询：</p><pre><code class="lang-sql">-- 查看负责课程编号(subject_id)3和4的女老师都有谁?
SELECT name, gender, subject_id
FROM teacher
WHERE gender = '女'
  AND (subject_id = 3
    OR subject_id = 4);

-- 查看工资高于5600的老女师都有谁?
SELECT name, gender, salary
FROM teacher
WHERE gender = '女'
  AND salary &gt; 5600;

-- 查看奖金高于3000的男老师或女老师的工资？
desc teacher;
SELECT name, gender, salary
FROM teacher
WHERE comm &gt; 3000;

-- 查看所有10岁以下学生中的小队长和语文课代表都是谁?
SELECT name, age, job
FROM student
WHERE age &lt; 10
  AND job IN ('小队长', '语文课代表');

-- 查看奖金在1000到4000之间的老师以及具体工资?
SELECT name, salary, comm
FROM teacher
WHERE comm BETWEEN 1000 AND 4000;

-- 查看工资在4000到8000之间的老师及具体工资?
SELECT name, salary, comm
FROM teacher
WHERE salary BETWEEN 4000 AND 8000;

-- 查看男老师负责的课程编号都有什么?
SELECT DISTINCT gender, subject_id
FROM teacher
WHERE gender = '男';

-- 查看所有男老师的职称和课程编号都是什么?
SELECT DISTINCT gender, title, subject_id
FROM teacher
WHERE gender = '男';

-- 查看10-20岁的女同学的职位都有哪些?
SELECT DISTINCT age, gender, job
FROM student
WHERE age BETWEEN 10 AND 20
  AND gender = '女';

-- 查看二级讲师和三级讲师的奖金(comm)和薪酬是多少?
SELECT DISTINCT title, comm, salary
FROM teacher
WHERE title IN ('二级讲师', '三级讲师');

-- 查看除了老板、总监、一级讲师的其他老师的工资是多少?
SELECT name, title, comm, salary
FROM teacher
WHERE title NOT IN ('老板', '总监', '一级讲师');

-- 查看没有奖金的老师都有谁和薪资是多少？
SELECT name, salary, comm
FROM teacher
WHERE comm = 0 OR comm IS NULL;</code></pre><h3>2 LIKE查询练习</h3><p>针对业务案例库，进行如下查询：</p><pre><code class="lang-sql">-- 查询名字姓&quot;李&quot; 和姓&quot;张&quot;的学生姓名
SELECT name
FROM student
WHERE name LIKE '李%'
   OR name LIKE '张%';

-- 查询名字中包含&quot;华&quot;的学生姓名
SELECT name
FROM student
WHERE name LIKE '%华%';

-- 查询名字以&quot;乐&quot;结尾的学生姓名
SELECT name
FROM student
WHERE name LIKE '%乐';

-- 查询7岁里是&quot;课代表&quot;的学生信息
SELECT *
FROM student
WHERE age = 7
  AND job LIKE '%课代表';

-- 查询名字第二个字是&quot;熊&quot;的学生信息
SELECT *
FROM student
WHERE name LIKE '_熊_';

-- 查询姓&quot;常&quot;的队长都是谁?
SELECT name, job
FROM student
WHERE name LIKE '常%'
  AND job LIKE '%队长'</code></pre><h3>3 排序、分组和聚合查询练习</h3><p>针对业务案例库，进行如下查询：</p><pre><code class="lang-sql">-- 查询所有老师薪资和奖金总和大于8000的，并且按照升序输出全部信息
SELECT *
FROM teacher
where salary + comm &gt; 8000
ORDER BY salary + comm;

-- 查询8岁同学中名字含有&quot;苗&quot;的学生数量
SELECT COUNT(*) `学生数量`
FROM student
WHERE age = 8
  AND name LIKE '%苗%';

-- 查询负责课程编号1和2号且年龄大于35的老师信息
SELECT *
FROM teacher
WHERE (subject_id = 1 OR subject_id = 2)
  AND age &gt; 35;

-- 查询10岁以上的课代表总数
SELECT COUNT(*) `总数`
FROM student
WHERE age &gt; 10
  AND job LIKE '%课代表';

-- 查询不教课程编号1的老师信息,按照年龄降序排序
SELECT *
FROM teacher
WHERE subject_id NOT IN (1)
ORDER BY age DESC;

-- 查询没有奖金的老师数量
SELECT COUNT(*) `数量`
FROM teacher
WHERE IFNULL(comm, 0) = 0;

-- 查询所有课代表年龄，并按年龄降序排序
SELECT name, age, job
FROM student
WHERE job LIKE '%课代表'
ORDER BY age DESC;

-- 查看姓李的学生每个年龄段有多少人?
SELECT COUNT(*),name,age
FROM student
WHERE name LIKE '李%'
GROUP BY age;</code></pre><h3>4 排序、分组和聚合、子查询练习</h3><p>针对业务案例库，进行如下查询：</p><pre><code class="lang-java">-- 统计每个城市的学生数量
SELECT COUNT(*) `学生数量`, location_id
FROM student
GROUP BY location_id;

-- 统计每个组长管理的学生平均年龄
SELECT AVG(age) `平均年龄`, team_leader
FROM student
GROUP BY team_leader;

-- 统计每个城市中年龄最小和最大的学生
SELECT MIN(age) `年龄最小`, MAX(age) `年龄最大`, name, location_id
FROM student
GROUP BY location_id;

-- 统计每个班级中，职位为&quot;班长&quot;的学生数
SELECT COUNT(*), class_id
FROM student
WHERE job = '班长'
GROUP BY class_id;

-- 统计每个科目的老师人数及平均年龄，按照科目编号升序排序
SELECT COUNT(*) `人数`, AVG(age) `平均年龄`, subject_id
FROM teacher
GROUP BY subject_id
ORDER BY subject_id;

-- 统计每个性别的老师人数及其平均工资和奖金总额
SELECT COUNT(*), gender, AVG(salary), SUM(salary)
FROM teacher
GROUP BY gender;

-- 统计每个上司的管理老师人数及其平均工资，按照管理老师人数降序排序
SELECT COUNT(*),subject_id,AVG(salary)
FROM teacher
WHERE title NOT IN ('总监','老板')
GROUP BY subject_id;

-- 查询乐明所在班级的所有学生信息
SELECT *
FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name = '乐明');</code></pre>

一、基础查询

1、SELECT语句

（1）DQL（数据查询语言）

（2）案例概述

SELECT基本语法

SELECT 语句的执行顺序

2、过滤查询结果

（1）WHERE子句

（2）使用AND和OR连接多个条件

（3）使用（）调整优先级

（4）IN操作符

（5）NOT IN 操作符

（6）BETWEEN AND 操作符

（7）去除查询结果中重复的行

（8）LIKE模糊查询

（9）NULL值判断

3、对查询结果排序

（1）ORDER BY 子句

（2）LIMIT和分页查询

二、函数和表达式

1、表达式和空值函数

（1）表达式生成结果集

（2）在WHERE子句中使用表达式

（3）别名

（4）练习：函数和表达式练习

2、聚合函数

（1）常用的聚合函数

（2）聚合函数忽略NULL值

三、分组查询

1、分组聚合

（1）GROUP BY子句

（2）按照多字段分组

（3）按照聚合函数排序

2、过滤分组结果

（1）HAVING子句

（2）HAVING子句与WHERE子句

四、子查询

1、DQL使用子查询

（1）子查询概述

（2）在DQL中使用标量子查询

（3）使用多行单列子查询

2、DDL使用子查询

使用子查询创建表

五、练习

1 数据库基础查询练习

2 LIKE查询练习

3 排序、分组和聚合查询练习

4 排序、分组和聚合、子查询练习

发表评论 取消回复

单表查询

发表评论取消回复