数据查询语言DQL
Data Query Language概述
即"数据查询语言"，简称"DQL"。
用户通过DQL用于数据查询语言，严格意义上来讲它应该属于DML的一个子类，只不过它实在太重要了，因此我们通常把它单独拿出来说。
SELECT 配合内置函数使用
查看当前数据库的时间-NOW()
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2025-09-03 16:10:02 |
+---------------------+
1 row in set (0.00 sec)
查看当前所在的数据库-DATABASE()
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| eladmin |
+------------+
1 row in set (0.00 sec)
查看字符串拼接的函数-CONCAT()
mysql> SELECT user,host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT CONCAT(user,"@",host) AS "完整用户名" FROM mysql.user;
+----------------------------+
| 完整用户名 |
+----------------------------+
| mysql.infoschema@localhost |
| mysql.session@localhost |
| mysql.sys@localhost |
| root@localhost |
+----------------------------+
4 rows in set (0.00 sec)
查看当前数据库的版本-VERSION()
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)
查看当前登录的用户-USER()
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
查看当前的角色
SELECT CURRENT_ROLE();
SELECT语句查询数据库的参数
mysql> SELECT @@INNODB_FLUSH_LOG_AT_TRX_COMMIT;
+----------------------------------+
| @@INNODB_FLUSH_LOG_AT_TRX_COMMIT |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
# 基于关键字进行模糊查询
mysql> SHOW VARIABLES LIKE '%trx%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_api_trx_level | 0 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
2 rows in set (0.00 sec)
统计行数-COUNT()
mysql> SELECT COUNT(*) FROM mysql.user;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
对指定列去重-DISTINCT
SELECT DISTINCT(CountryCode) FROM city;
单表查询
测试数据准备
# 测试数据库下载地址: https://downloads.mysql.com/docs/world-db.tar.gz
mysql -uroot -p1qaz@WSX < world.sql
查看全表信息
SELECT * FROM city;
查看部分列信息
SELECT Name,District,Population FROM city;
分页查询应用
# 查询前10行
SELECT * FROM city limit 10;
# 从第20行的下一行开始，往后查询5行数据。
SELECT * FROM city limit 20,5;
# SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
# 起始索引从0开始，计算公式为：起始索引 = (查询页码 - 1) * 每页显示记录数。
去重
# 使用DISTINCT函数可以去重
SELECT DISTINCT(CountryCode) FROM world.city;
WHERE
我们可以使用WHERE子句来配合一些常用的比较运算符进行查询，比如: “>=",">","=","<=","<","!="("<>")等等。
# 查询属于中国的城市：
SELECT * FROM city WHERE countrycode='CHN';
# 查询属于中国的城市人数大于500万的城市
SELECT * FROM city WHERE countrycode='CHN' AND Population>5000000;
# 查询中国或美国的城市信息,我们可以使用逻辑判断的方式来过滤某个字段的信息
SELECT * FROM city WHERE CountryCode='CHN' OR CountryCode='USA';
SELECT * FROM city WHERE CountryCode IN ('CHN','USA');
# 查询中国或美国的城市信息并且人数超过800万的城市
SELECT * FROM city WHERE CountryCode IN('CHN', 'USA') AND Population > 8000000 ;
# 查询中国或美国的城市信息并且人数在500万到800万之间的城市
SELECT * FROM city WHERE CountryCode IN('CHN', 'USA') AND Population BETWEEN 5000000 AND 8000000 ;
WHERE + LIKE
LIKE子句尽量要少用，因为它要进行全表扫描。这意味着你设置的索引字段也形同虚，即查询效率极低！但基本使用还是有必要了解一下的
# 查询city中，国家代号是CH开头的城市信息
SELECT * FROM city WHERE CountryCode LIKE 'CH%';
联合查询
# 查询中国或美国的城市信息
SELECT * FROM city WHERE CountryCode='CHN' UNION ALL SELECT * FROM city WHERE CountryCode='USA';
# 将两次查询结果合并到同一张表中展示。
WHERE + GROUP BY
GROUP BY子句通常配合聚合函数使用,常用的函数如下：
- AVG: 计算平均值
- COUNT: 统计个数
- GROUP_CONCAT: 将一列数据的每个元素使用逗号(",")进行分割在一行中显示。简而言之，就是将一列数据写在一行中。
- MAX: 最大值
- MIN: 最小值
- SUM: 求和
- COUNT DISTINCT: 计算分组中唯一不重复的值的个数。
GROUP BY语句的应用场景: 需要对一张表按照不同数据特点，需要分组计算统计时，会使用:"GROUP BY + 聚合函数"。
GROUP BY语句使用核心方法:
- 根据需求，找出分组条件;
- 根据需要，使用合适的聚合函数;
# 统计每个国家城市的个数
SELECT CountryCode,COUNT(ID) FROM city GROUP BY CountryCode ;
# 统计中国每个省城市的个数
SELECT District,COUNT(ID) FROM city WHERE CountryCode='CHN' GROUP BY District;
# 统计每个国家的总人口
SELECT CountryCode,SUM(Population) FROM city GROUP BY CountryCode;
# 统计中国每个省的总人口
SELECT District,SUM(Population) FROM city WHERE CountryCode='CHN' GROUP BY District;
# 统计中国每个省的人数的个数及城市名称列表
# GROUP_CONCAT聚合函数可以将一列数据转成一行并用逗号分割。
SELECT District,SUM(Population),COUNT(ID),GROUP_CONCAT(name) FROM city WHERE CountryCode='CHN' GROUP BY District;
WHERE + GROUP BY + HAVING
# 统计中国每个省的总人数在800万以上的省份
SELECT District,SUM(Population) AS p1 FROM city WHERE CountryCode='CHN' GROUP BY District HAVING p1 > 8000000;
WHERE子句与HAVING子句的关系
HAVING和WHERE子句功能类似，都是过滤数据，只不过WHERE在GROUP BY子句之前进行过滤，而HAVING在GROUP BY子句后过滤。
这是MySQL语法的要求，WHERE子句必须在GROUP BY子句之前进行过滤，而HAVING是对GROUP BY子句之后的数据进行第二次过滤。
综上所述，HAVING子句的应用场景也很明确，即需要在"GROUP BY子句 + 聚合函数"之后再做判断时过滤。
WHERE + GROUP BY + HAVING + ORDER BY
# 统计中国每个省的总人数在800万以上的省份，并且按照总人口数从小到大排序输出
SELECT District,SUM(Population) AS p1 FROM city WHERE CountryCode='CHN' GROUP BY District HAVING p1 > 8000000 ORDER BY p1;
# 统计中国每个省的总人数在800万以上的省份，并且按照总人口数从大到小排序输出
SELECT District,SUM(Population) AS p1 FROM city WHERE CountryCode='CHN' GROUP BY District HAVING p1 > 8000000 ORDER BY p1 DESC;
WHERE + GROUP BY + HAVING + ORDER BY + LIMIT
# 统计中国每个省的总人数在800万以上的省份，并且按照总人口数从大到小排序输出，但只显示前3名。
SELECT District,SUM(Population) AS p1 FROM city WHERE CountryCode='CHN' GROUP BY District HAVING p1 > 8000000 ORDER BY p1 DESC LIMIT 0,3;
SELECT District,SUM(Population) AS p1 FROM city WHERE CountryCode='CHN' GROUP BY District HAVING p1 > 8000000 ORDER BY p1 DESC LIMIT 3 OFFSET 0;
# 统计中国每个省的总人数在800万以上的省份，并且按照总人口数从大到小排序输出，但只显示前4-5名。
SELECT District,SUM(Population) AS p1 FROM city WHERE CountryCode='CHN' GROUP BY District HAVING p1 > 8000000 ORDER BY p1 DESC LIMIT 3,2;
SELECT District,SUM(Population) AS p1 FROM city WHERE CountryCode='CHN' GROUP BY District HAVING p1 > 8000000 ORDER BY p1 DESC LIMIT 2 OFFSET 3;
# "LIMIT 3,2"表示跳过前3条数据，往下查看2条数据;
# "LIMIT 2 OFFSET 3"是跳过3行，显示2行数据，尽管写法和上面的有所不同，但都能达到相同的查询效果;
多表查询
测试数据准备
# 建表语句:
CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET = utf8mb4;
CREATE TABLE IF NOT EXISTS school.student (
id int UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学生编号',
name VARCHAR(30) NOT NULL COMMENT '学生姓名',
age tinyint UNSIGNED NOT NULL COMMENT '学生年龄',
gender enum('Male','Female') DEFAULT NULL DEFAULT 'Male' COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS school.teacher (
id smallint UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '教师编号',
name VARCHAR(30) NOT NULL COMMENT '教师姓名',
age tinyint UNSIGNED NOT NULL COMMENT '学生年龄',
gender enum('Male','Female') DEFAULT NULL DEFAULT 'Male' COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS school.course (
id tinyint UNSIGNED NOT NULL PRIMARY KEY COMMENT '课程编号',
name VARCHAR(30) NOT NULL COMMENT '课程名称',
teacher_id smallint UNSIGNED NOT NULL COMMENT '教师编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS school.student_score (
student_id int NOT NULL COMMENT '学生编号',
course_id tinyint UNSIGNED NOT NULL COMMENT '课程编号',
score smallint UNSIGNED NOT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 往表中插入数据:
# 往student表中插入数据:
INSERT INTO student
(id,name,age,gender)
VALUES
(1,'范冰冰',20,'Female'),
(2,'刘亦菲',18,'Female'),
(3,'唐嫣',21,'Female'),
(4,'李诗诗',20,'Female'),
(5,'杨幂',25,'Female'),
(6,'任贤齐',21,'Male'),
(7,'刘德华',28,'Male'),
(8,'邓超',30,'Male'),
(9,'杨紫',22,'Female'),
(10,'郑爽',20,'Female'),
(11,'霍建华',25,'Male'),
(12,'胡歌',28,'Male'),
(13,'赵丽颖',21,'FeMale'),
(14,'迪丽热巴',23,'FeMale'),
(15,'郭德纲',35,'Male');
# 往teacher表中插入数据:
INSERT INTO teacher
(id,name,age,gender)
VALUES
(201,'蒋昌建',56,'Male'),
(202,'涂磊',45,'Female'),
(203,'周星驰',59,'Female');
# 往course表中插入数据:
INSERT INTO course
(id,name,teacher_id)
VALUES
(1,'最强大脑',201),
(2,'爱情保卫战',202),
(4,'喜剧之王',203),
(8,'非你莫属',202),
(16,'功夫',203);
# 往student_score表中插入数据:
INSERT INTO student_score
(student_id,course_id,score)
VALUES
(1,1,80),
(1,16,90),
(1,4,85),
(2,1,85),
(2,8,90),
(3,2,68),
(3,4,95),
(4,16,90),
(5,1,91),
(5,2,89),
(6,1,72),
(6,4,95),
(7,1,81),
(7,16,92),
(8,1,90),
(8,8,74),
(9,1,82),
(9,2,90),
(10,4,97),
(10,8,62),
(10,16,83),
(11,1,90),
(11,16,89),
(12,8,96),
(12,16,73),
(13,1,100),
(14,2,100),
(14,4,100),
(14,16,80),
(15,8,95),
(15,1,90);
内连接
可以理解是两张表之间的交集-生产环境中应用最广泛
# 查看每个老师都有哪些课程
mysql> SELECT * FROM course;
+----+-----------------+------------+
| id | name | teacher_id |
+----+-----------------+------------+
| 1 | 最强大脑 | 201 |
| 2 | 爱情保卫战 | 202 |
| 4 | 喜剧之王 | 203 |
| 8 | 非你莫属 | 202 |
| 16 | 功夫 | 203 |
+----+-----------------+------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM teacher;
+-----+-----------+-----+--------+
| id | name | age | gender |
+-----+-----------+-----+--------+
| 201 | 蒋昌建 | 56 | Male |
| 202 | 涂磊 | 45 | Female |
| 203 | 周星驰 | 59 | Female |
+-----+-----------+-----+--------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM teacher JOIN course ON teacher.id=course.teacher_id;
+-----+-----------+-----+--------+----+-----------------+------------+
| id | name | age | gender | id | name | teacher_id |
+-----+-----------+-----+--------+----+-----------------+------------+
| 201 | 蒋昌建 | 56 | Male | 1 | 最强大脑 | 201 |
| 202 | 涂磊 | 45 | Female | 2 | 爱情保卫战 | 202 |
| 203 | 周星驰 | 59 | Female | 4 | 喜剧之王 | 203 |
| 202 | 涂磊 | 45 | Female | 8 | 非你莫属 | 202 |
| 203 | 周星驰 | 59 | Female | 16 | 功夫 | 203 |
+-----+-----------+-----+--------+----+-----------------+------------+
5 rows in set (0.00 sec)
外连接
LEFT JOIN: 左表所有数据,右表满足条件的数据.
RIGHT JOIN: 右表所有数据,左表满足条件的数据.
左外连接-可以理解是两者表的左边圆的差集
SELECT
city.name AS '城市',country.name AS '国家',city.Population AS '人口'
FROM
city
LEFT JOIN
country
ON
city.countrycode=country.code
AND
city.Population < 100
ORDER BY
city.Population
DESC;
右外连接--可以理解是两者表的右边圆的差集
SELECT
city.name AS '城市',country.name AS '国家',city.Population AS '人口'
FROM
city
RIGHT JOIN
country
ON
city.countrycode=country.code
AND
city.Population < 100;
SELECT的别名应用
列别名
列别名作用: 可以定制显示的别名，可以在"HAVING ..."或者"ORDER BY ..."子句中调用，但不能在WHERE子句中调用,这是由于SQL执行顺序WHERE子句在SELECT子句之前执行的。
SELECT
District AS 省, SUM(Population) AS 总人数
FROM
city
WHERE
CountryCode='CHN'
GROUP BY
District # 注意哈,在GROUP BY 子句中不能使用AS别名"省",这是因为GROUP BY子句在SELECT之前执行!
HAVING
总人数 > 8000000
ORDER BY
总人数 DESC
LIMIT
5 OFFSET 0;
表别名
表别名作用: 全局调用定义的别名，这是因为给表起别名是在FROM子句中定义的，从SQL的执行顺序你也知道，FROM子句是最先执行的，因此在其后的所有子句均可以访问到。
SELECT
x.id AS '学生编号',x.name AS '学生姓名', GROUP_CONCAT(z.name) AS '课程名称'
FROM
student AS x JOIN student_score AS y ON x.id=y.student_id JOIN course z ON y.course_id=z.id # 注意SQL的执行顺序: 我们给表起的别名实在WHERE之前就操作了,因此在其它子句中均可以使用。
WHERE
x.name = '胡歌'
GROUP BY
x.id,x.name;
浙公网安备 33010602011771号