Day8：关于MySQL的数据查询——单表查询

原创于 2025-04-10 09:45:00 发布 · 1.6k 阅读

24 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

关于我在学习数据库这件事

前言：先创建一个练习的数据库和数据

1.创建数据库并创建数据表的基本结构

mysql> CREATE DATABASE practice_db;
Query OK, 1 row affected (0.01 sec)

mysql> USE practice_db;
Database changed
mysql> CREATE TABLE employees (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(50) NOT NULL,
    -> gender ENUM('Male', 'Female'),
    -> age INT,
    -> hire_date DATE,
    -> department VARCHAR(50),
    -> salary DECIMAL(10,2),
    -> email VARCHAR(100),
    -> INDEX (department)
    -> );
Query OK, 0 rows affected (0.07 sec)
mysql> DESC employees;
+------------+-----------------------+------+-----+---------+----------------+
| Field      | Type                  | Null | Key | Default | Extra          |
+------------+-----------------------+------+-----+---------+----------------+
| id         | int                   | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)           | NO   |     | NULL    |                |
| gender     | enum('Male','Female') | YES  |     | NULL    |                |
| age        | int                   | YES  |     | NULL    |                |
| hire_date  | date                  | YES  |     | NULL    |                |
| department | varchar(50)           | YES  | MUL | NULL    |                |
| salary     | decimal(10,2)         | YES  |     | NULL    |                |
| email      | varchar(100)          | YES  |     | NULL    |                |
+------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

2.导入20条练习数据

复制如下：

INSERT INTO employees (name, gender, age, hire_date, department, salary, email) VALUES
('张三', 'Male', 28, '2019-03-15', 'Technology', 15000.00, 'zhangsan@company.com'),
('李四', 'Female', 32, '2017-08-22', 'Marketing', 12000.50, 'lisi@company.com'),
('王五', 'Male', 45, '2012-11-05', 'Finance', 25000.00, 'wangwu@company.com'),
('赵六', 'Female', 26, '2020-06-18', 'Technology', 13500.00, 'zhaoliu@company.com'),
('陈七', 'Male', 38, '2015-09-12', 'HR', 18000.00, 'chenqi@company.com'),
('周八', 'Female', 29, '2018-04-30', 'Marketing', 14200.00, 'zhouba@company.com'),
('吴九', 'Male', 41, '2014-02-14', 'Finance', 26500.00, 'wujiu@company.com'),
('郑十', 'Female', 33, '2016-07-01', 'Technology', 16000.00, 'zhengshi@company.com'),
('孙十一', 'Male', 27, '2021-09-11', 'HR', 12500.00, 'sunshiyi@company.com'),
('钱十二', 'Female', 36, '2013-12-25', 'Finance', 28000.00, 'qianshier@company.com'),
('杨十三', 'Male', 31, '2019-08-07', 'Technology', 17000.00, 'yangshisan@company.com'),
('朱十四', 'Female', 24, '2022-01-15', 'Marketing', 11800.00, 'zhushisi@company.com'),
('秦十五', 'Male', 43, '2011-05-20', 'Finance', 30000.00, 'qinshiwu@company.com'),
('许十六', 'Female', 30, '2018-11-30', 'Technology', 15500.00, 'xushiliu@company.com'),
('何十七', 'Male', 35, '2016-04-12', 'HR', 19500.00, 'heshiqi@company.com'),
('吕十八', 'Female', 28, '2020-09-05', 'Marketing', 13000.00, 'lvshiba@company.com'),
('施十九', 'Male', 39, '2014-07-18', 'Finance', 27500.00, 'shishijiu@company.com'),
('张二十', 'Female', 25, '2023-02-28', 'Technology', 14000.00, 'zhangershi@company.com'),
('李二十一', 'Male', 34, '2017-03-10', 'HR', 18500.00, 'lishiyi@company.com'),
('王二十二', 'Female', 37, '2015-10-22', 'Finance', 29000.00, 'wangershier@company.com');

mysql> INSERT INTO employees (name, gender, age, hire_date, department, salary, email) VALUES
    -> ('张三', 'Male', 28, '2019-03-15', 'Technology', 15000.00, 'zhangsan@company.com'),
    -> ('李四', 'Female', 32, '2017-08-22', 'Marketing', 12000.50, 'lisi@company.com'),
    -> ('王五', 'Male', 45, '2012-11-05', 'Finance', 25000.00, 'wangwu@company.com'),
    -> ('赵六', 'Female', 26, '2020-06-18', 'Technology', 13500.00, 'zhaoliu@company.com'),
    -> ('陈七', 'Male', 38, '2015-09-12', 'HR', 18000.00, 'chenqi@company.com'),
    -> ('周八', 'Female', 29, '2018-04-30', 'Marketing', 14200.00, 'zhouba@company.com'),
    -> ('吴九', 'Male', 41, '2014-02-14', 'Finance', 26500.00, 'wujiu@company.com'),
    -> ('郑十', 'Female', 33, '2016-07-01', 'Technology', 16000.00, 'zhengshi@company.com'),
    -> ('孙十一', 'Male', 27, '2021-09-11', 'HR', 12500.00, 'sunshiyi@company.com'),
    -> ('钱十二', 'Female', 36, '2013-12-25', 'Finance', 28000.00, 'qianshier@company.com'),
    -> ('杨十三', 'Male', 31, '2019-08-07', 'Technology', 17000.00, 'yangshisan@company.com'),
    -> ('朱十四', 'Female', 24, '2022-01-15', 'Marketing', 11800.00, 'zhushisi@company.com'),
    -> ('秦十五', 'Male', 43, '2011-05-20', 'Finance', 30000.00, 'qinshiwu@company.com'),
    -> ('许十六', 'Female', 30, '2018-11-30', 'Technology', 15500.00, 'xushiliu@company.com'),
    -> ('何十七', 'Male', 35, '2016-04-12', 'HR', 19500.00, 'heshiqi@company.com'),
    -> ('吕十八', 'Female', 28, '2020-09-05', 'Marketing', 13000.00, 'lvshiba@company.com'),
    -> ('施十九', 'Male', 39, '2014-07-18', 'Finance', 27500.00, 'shishijiu@company.com'),
    -> ('张二十', 'Female', 25, '2023-02-28', 'Technology', 14000.00, 'zhangershi@company.com'),
    -> ('李二十一', 'Male', 34, '2017-03-10', 'HR', 18500.00, 'lishiyi@company.com'),
    -> ('王二十二', 'Female', 37, '2015-10-22', 'Finance', 29000.00, 'wangershier@company.com');
Query OK, 20 rows affected (0.02 sec)
Records: 20  Duplicates: 0  Warnings: 0

一、简单数据查询

格式如下：

SELECT  [ ALL | DISTINCT ] 字段名称1或表达式1 [ , 字段名称2或表达式2 … ]
FROM  表名;

1.显示字段

1.显示全部字段

格式如下：

SELECT * FROM 表名;

mysql> SELECT * FROM employees;
+----+----------+--------+------+------------+------------+----------+-------------------------+
| id | name     | gender | age  | hire_date  | department | salary   | email                   |
+----+----------+--------+------+------------+------------+----------+-------------------------+
|  1 | 张三     | Male   |   28 | 2019-03-15 | Technology | 15000.00 | zhangsan@company.com    |
|  2 | 李四     | Female |   32 | 2017-08-22 | Marketing  | 12000.50 | lisi@company.com        |
|  3 | 王五     | Male   |   45 | 2012-11-05 | Finance    | 25000.00 | wangwu@company.com      |
|  4 | 赵六     | Female |   26 | 2020-06-18 | Technology | 13500.00 | zhaoliu@company.com     |
|  5 | 陈七     | Male   |   38 | 2015-09-12 | HR         | 18000.00 | chenqi@company.com      |
|  6 | 周八     | Female |   29 | 2018-04-30 | Marketing  | 14200.00 | zhouba@company.com      |
|  7 | 吴九     | Male   |   41 | 2014-02-14 | Finance    | 26500.00 | wujiu@company.com       |
|  8 | 郑十     | Female |   33 | 2016-07-01 | Technology | 16000.00 | zhengshi@company.com    |
|  9 | 孙十一   | Male   |   27 | 2021-09-11 | HR         | 12500.00 | sunshiyi@company.com    |
| 10 | 钱十二   | Female |   36 | 2013-12-25 | Finance    | 28000.00 | qianshier@company.com   |
| 11 | 杨十三   | Male   |   31 | 2019-08-07 | Technology | 17000.00 | yangshisan@company.com  |
| 12 | 朱十四   | Female |   24 | 2022-01-15 | Marketing  | 11800.00 | zhushisi@company.com    |
| 13 | 秦十五   | Male   |   43 | 2011-05-20 | Finance    | 30000.00 | qinshiwu@company.com    |
| 14 | 许十六   | Female |   30 | 2018-11-30 | Technology | 15500.00 | xushiliu@company.com    |
| 15 | 何十七   | Male   |   35 | 2016-04-12 | HR         | 19500.00 | heshiqi@company.com     |
| 16 | 吕十八   | Female |   28 | 2020-09-05 | Marketing  | 13000.00 | lvshiba@company.com     |
| 17 | 施十九   | Male   |   39 | 2014-07-18 | Finance    | 27500.00 | shishijiu@company.com   |
| 18 | 张二十   | Female |   25 | 2023-02-28 | Technology | 14000.00 | zhangershi@company.com  |
| 19 | 李二十一 | Male   |   34 | 2017-03-10 | HR         | 18500.00 | lishiyi@company.com     |
| 20 | 王二十二 | Female |   37 | 2015-10-22 | Finance    | 29000.00 | wangershier@company.com |
+----+----------+--------+------+------------+------------+----------+-------------------------+
20 rows in set (0.00 sec)

2.显示指定字段

mysql> SELECT id,name,age
    -> FROM employees;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | 张三     |   28 |
|  2 | 李四     |   32 |
|  3 | 王五     |   45 |
|  4 | 赵六     |   26 |
|  5 | 陈七     |   38 |
|  6 | 周八     |   29 |
|  7 | 吴九     |   41 |
|  8 | 郑十     |   33 |
|  9 | 孙十一   |   27 |
| 10 | 钱十二   |   36 |
| 11 | 杨十三   |   31 |
| 12 | 朱十四   |   24 |
| 13 | 秦十五   |   43 |
| 14 | 许十六   |   30 |
| 15 | 何十七   |   35 |
| 16 | 吕十八   |   28 |
| 17 | 施十九   |   39 |
| 18 | 张二十   |   25 |
| 19 | 李二十一 |   34 |
| 20 | 王二十二 |   37 |
+----+----------+------+
20 rows in set (0.00 sec)

2.显示字段的别名

使用 AS 指定显示结果的列标题

mysql> SELECT id AS 编号,name AS 姓名,age AS 年龄
    -> FROM employees;
+------+----------+------+
| 编号 | 姓名     | 年龄 |
+------+----------+------+
|    1 | 张三     |   28 |
|    2 | 李四     |   32 |
|    3 | 王五     |   45 |
|    4 | 赵六     |   26 |
|    5 | 陈七     |   38 |
|    6 | 周八     |   29 |
|    7 | 吴九     |   41 |
|    8 | 郑十     |   33 |
|    9 | 孙十一   |   27 |
|   10 | 钱十二   |   36 |
|   11 | 杨十三   |   31 |
|   12 | 朱十四   |   24 |
|   13 | 秦十五   |   43 |
|   14 | 许十六   |   30 |
|   15 | 何十七   |   35 |
|   16 | 吕十八   |   28 |
|   17 | 施十九   |   39 |
|   18 | 张二十   |   25 |
|   19 | 李二十一 |   34 |
|   20 | 王二十二 |   37 |
+------+----------+------+
20 rows in set (0.00 sec)

3.显示计算的字段值

加—— + 减—— -

乘—— * 除—— /

黑心老板将员工的薪水减1000

mysql> USE practice_db;
Database changed
mysql> SELECT id AS 编号,name AS 姓名,age AS 年龄,salary - 1000 AS 薪水
    -> FROM employees;
+------+----------+------+----------+
| 编号 | 姓名     | 年龄 | 薪水     |
+------+----------+------+----------+
|    1 | 张三     |   28 | 14000.00 |
|    2 | 李四     |   32 | 11000.50 |
|    3 | 王五     |   45 | 24000.00 |
|    4 | 赵六     |   26 | 12500.00 |
|    5 | 陈七     |   38 | 17000.00 |
|    6 | 周八     |   29 | 13200.00 |
|    7 | 吴九     |   41 | 25500.00 |
|    8 | 郑十     |   33 | 15000.00 |
|    9 | 孙十一   |   27 | 11500.00 |
|   10 | 钱十二   |   36 | 27000.00 |
|   11 | 杨十三   |   31 | 16000.00 |
|   12 | 朱十四   |   24 | 10800.00 |
|   13 | 秦十五   |   43 | 29000.00 |
|   14 | 许十六   |   30 | 14500.00 |
|   15 | 何十七   |   35 | 18500.00 |
|   16 | 吕十八   |   28 | 12000.00 |
|   17 | 施十九   |   39 | 26500.00 |
|   18 | 张二十   |   25 | 13000.00 |
|   19 | 李二十一 |   34 | 17500.00 |
|   20 | 王二十二 |   37 | 28000.00 |
+------+----------+------+----------+
20 rows in set (0.01 sec)

4.消除查询结果中的重复数据

使用DISTINCT参数可以消除查询结果中重复数据，保证数据的唯一性

可以看到数据中有两个人的年龄都是28岁，可以采用DISTINCT查询员工都有哪些年龄

mysql> SELECT DISTINCT age FROM employees;
+------+
| age  |
+------+
|   28 |
|   32 |
|   45 |
|   26 |
|   38 |
|   29 |
|   41 |
|   33 |
|   27 |
|   36 |
|   31 |
|   24 |
|   43 |
|   30 |
|   35 |
|   39 |
|   25 |
|   34 |
|   37 |
+------+
19 rows in set (0.00 sec)

5.限制查询结果中的记录条件

使用LIMIT来限制查询中的结果中的记录条件数

1.只查询前3个记录

mysql> SELECT id,name,age,salary
    -> FROM employees
    -> LIMIT 3;
+----+------+------+----------+
| id | name | age  | salary   |
+----+------+------+----------+
|  1 | 张三 |   28 | 15000.00 |
|  2 | 李四 |   32 | 12000.50 |
|  3 | 王五 |   45 | 25000.00 |
+----+------+------+----------+
3 rows in set (0.00 sec)

2.使用偏移量和行数查询

mysql> SELECT id,name,age,salary
    -> FROM employees
    -> LIMIT 3,2;
+----+------+------+----------+
| id | name | age  | salary   |
+----+------+------+----------+
|  4 | 赵六 |   26 | 13500.00 |
|  5 | 陈七 |   38 | 18000.00 |
+----+------+------+----------+
2 rows in set (0.00 sec)

上述的3是偏移量，初始记录行的偏移量为0，故3为第四条记录，2为查询第3条记录后的2条记录即查询4和5两条记录

6.对查询结果进行排序

ORDER BY子句用于查询对查询结果进行排序

1.将单个字段的年龄升序排列

1.
SELECT id,name,age,salary
FROM employees
ORDER BY age;

2.
SELECT id,name,age,salary
FROM employees
ORDER BY 3;

+----+----------+------+----------+
| id | name     | age  | salary   |
+----+----------+------+----------+
| 12 | 朱十四   |   24 | 11800.00 |
| 18 | 张二十   |   25 | 14000.00 |
|  4 | 赵六     |   26 | 13500.00 |
|  9 | 孙十一   |   27 | 12500.00 |
|  1 | 张三     |   28 | 15000.00 |
| 16 | 吕十八   |   28 | 13000.00 |
|  6 | 周八     |   29 | 14200.00 |
| 14 | 许十六   |   30 | 15500.00 |
| 11 | 杨十三   |   31 | 17000.00 |
|  2 | 李四     |   32 | 12000.50 |
|  8 | 郑十     |   33 | 16000.00 |
| 19 | 李二十一 |   34 | 18500.00 |
| 15 | 何十七   |   35 | 19500.00 |
| 10 | 钱十二   |   36 | 28000.00 |
| 20 | 王二十二 |   37 | 29000.00 |
|  5 | 陈七     |   38 | 18000.00 |
| 17 | 施十九   |   39 | 27500.00 |
|  7 | 吴九     |   41 | 26500.00 |
| 13 | 秦十五   |   43 | 30000.00 |
|  3 | 王五     |   45 | 25000.00 |
+----+----------+------+----------+
20 rows in set (0.00 sec)

2.对多个字段进行升降序

...DESC 为降序排列 ASC为升序排列

二、条件数据查询

1.比较运算

**比较运算符**
运算符	功能	运算符	功能
=	等于	<> ,!=	不等于
>	大于	<=	小于等于
<	小于	>=	大于等于

mysql> SELECT id,name,age,salary
    -> FROM employees
    -> WHERE salary >= 15000;
+----+----------+------+----------+
| id | name     | age  | salary   |
+----+----------+------+----------+
|  1 | 张三     |   28 | 15000.00 |
|  3 | 王五     |   45 | 25000.00 |
|  5 | 陈七     |   38 | 18000.00 |
|  7 | 吴九     |   41 | 26500.00 |
|  8 | 郑十     |   33 | 16000.00 |
| 10 | 钱十二   |   36 | 28000.00 |
| 11 | 杨十三   |   31 | 17000.00 |
| 13 | 秦十五   |   43 | 30000.00 |
| 14 | 许十六   |   30 | 15500.00 |
| 15 | 何十七   |   35 | 19500.00 |
| 17 | 施十九   |   39 | 27500.00 |
| 19 | 李二十一 |   34 | 18500.00 |
| 20 | 王二十二 |   37 | 29000.00 |
+----+----------+------+----------+
13 rows in set (0.00 sec)

2.逻辑运算

运算符	功能
NOT	非运算，当表达式的值为真时，结果为假；否则结果为真
AND	与运算，当连接的两个表达式的值均为真时，结果为真；否则结果为假
OR	或运算，当连接的两个表达式的值均为假时，结果为假；否则结果为真

mysql> SELECT id,name,age,salary,gender
    -> FROM employees
    -> WHERE salary >=15000 AND gender = "Male";
+----+----------+------+----------+--------+
| id | name     | age  | salary   | gender |
+----+----------+------+----------+--------+
|  1 | 张三     |   28 | 15000.00 | Male   |
|  3 | 王五     |   45 | 25000.00 | Male   |
|  5 | 陈七     |   38 | 18000.00 | Male   |
|  7 | 吴九     |   41 | 26500.00 | Male   |
| 11 | 杨十三   |   31 | 17000.00 | Male   |
| 13 | 秦十五   |   43 | 30000.00 | Male   |
| 15 | 何十七   |   35 | 19500.00 | Male   |
| 17 | 施十九   |   39 | 27500.00 | Male   |
| 19 | 李二十一 |   34 | 18500.00 | Male   |
+----+----------+------+----------+--------+
9 rows in set (0.00 sec)

3.指定范围

运算符	功能
BETWEEN a AND b	如果在a与b之间的范围内，结果为真，否则结果为假。要求a与b类型相同，且包含a和b这两个临界值
NOT BETWEEN a AND b	如果不在a与b之间的范围内，结果为真，否则结果为假。要求a与b类型相同，且包含a和b这两个临界值
IN(列表)	如果在列表中，结果为真，否则结果为假
NOT IN(列表)	如果不在列表中，结果为真，否则结果为假

1.使用BETWEEN运算符

SELECT id,name,hire_date
FROM employees
WHERE hire_date BETWEEN "2015-01-15" AND "2020-01-15";

mysql> SELECT id,name,hire_date
    -> FROM employees
    -> WHERE hire_date BETWEEN "2015-01-15" AND "2020-01-15";
+----+----------+------------+
| id | name     | hire_date  |
+----+----------+------------+
|  1 | 张三     | 2019-03-15 |
|  2 | 李四     | 2017-08-22 |
|  5 | 陈七     | 2015-09-12 |
|  6 | 周八     | 2018-04-30 |
|  8 | 郑十     | 2016-07-01 |
| 11 | 杨十三   | 2019-08-07 |
| 14 | 许十六   | 2018-11-30 |
| 15 | 何十七   | 2016-04-12 |
| 19 | 李二十一 | 2017-03-10 |
| 20 | 王二十二 | 2015-10-22 |
+----+----------+------------+
10 rows in set (0.00 sec)

1.使用IN运算符

SELECT id,name,department
FROM employees
WHERE department IN ("Finance","HR");

mysql> SELECT id,name,department
    -> FROM employees
    -> WHERE department IN ("Finance","HR");
+----+----------+------------+
| id | name     | department |
+----+----------+------------+
|  3 | 王五     | Finance    |
|  7 | 吴九     | Finance    |
| 10 | 钱十二   | Finance    |
| 13 | 秦十五   | Finance    |
| 17 | 施十九   | Finance    |
| 20 | 王二十二 | Finance    |
|  5 | 陈七     | HR         |
|  9 | 孙十一   | HR         |
| 15 | 何十七   | HR         |
| 19 | 李二十一 | HR         |
+----+----------+------------+
10 rows in set (0.00 sec)

4.模式匹配

模式匹配运算中可以使用的通配符有“_”和“%”。

“%” 表示0个或多个字符；
“_” 表示一个字符。

运算符	功能
LIKE	如果符合指定的模式，结果为真，否则结果为假
NOT LIKE	如果不符合指定的模式，结果为真，否则结果为假

1. _ 的用法

SELECT id,name,age
FROM employees
WHERE age LIKE "2_" ;

mysql> SELECT id,name,age
    -> FROM employees
    -> WHERE age LIKE "2_" ;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   28 |
|  4 | 赵六   |   26 |
|  6 | 周八   |   29 |
|  9 | 孙十一 |   27 |
| 12 | 朱十四 |   24 |
| 16 | 吕十八 |   28 |
| 18 | 张二十 |   25 |
+----+--------+------+
7 rows in set (0.00 sec)

2. % 的用法

SELECT id,name,age
FROM employees
WHERE name LIKE "王%" ;

mysql> SELECT id,name,age
    -> FROM employees
    -> WHERE name LIKE "王%" ;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | 王五     |   45 |
| 20 | 王二十二 |   37 |
+----+----------+------+
2 rows in set (0.00 sec)

5.空值运算

运算符	功能
IS NULL	如果是空值，结果为真，否则结果为假
IS NOT NULL	如果不是空值，结果为真，否则结果为假

SELECT id,name
FROM employees
WHERE id IS NULL;

mysql> SELECT id,name
    -> FROM employees
    -> WHERE id IS NULL;
Empty set (0.00 sec)

标签

#mysql #数据库