MySQL基础入门_数据库概述与查询语句详解

MySQL 基础入门：数据库概述与查询语句详解

本文涵盖 MySQL 数据库基础概念、各类查询语句及常用函数，适合入门复习与快速查阅。

---

一、数据库概述

1.1 什么是数据库

数据库（Database）是按照一定结构存储和管理数据的仓库。相比用文件存储数据，数据库具有以下优势：

• 持久化存储：数据不会因程序关闭而丢失
• 方便查询：支持复杂的条件检索
• 并发安全：多用户同时访问时保证数据一致性
• 数据完整性：通过约束保证数据正确性

1.2 关系型数据库

MySQL 是一种关系型数据库（RDBMS），核心思想是：

• 数据以**表（Table）**的形式组织，类似于 Excel 表格
• 表与表之间可以通过外键建立关系
• 使用 SQL（Structured Query Language） 进行操作

常见的关系型数据库：MySQL、Oracle、PostgreSQL、SQL Server

1.3 MySQL 基本操作

1. -- 连接数据库
2. mysql -u root -p
4. -- 创建数据库
5. CREATE DATABASE mydb;
7. -- 查看所有数据库
8. SHOW DATABASES;
10. -- 使用数据库
11. USE mydb;
13. -- 删除数据库
14. DROP DATABASE mydb;

复制代码

1.4 数据类型速查

类型	说明	示例
INT	整数	年龄、数量
BIGINT	大整数	ID
DOUBLE(M,D)	浮点数	价格、评分
DECIMAL(M,D)	精确小数	金额
CHAR(N)	定长字符串	手机号
VARCHAR(N)	变长字符串	姓名、地址
DATE	日期	生日
DATETIME	日期时间	创建时间
TIMESTAMP	时间戳	自动记录时间

1.5 表的创建与约束

1. CREATE TABLE student (
2. id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
3. name VARCHAR(50) NOT NULL COMMENT '姓名',
4. age INT COMMENT '年龄',
5. gender CHAR(1) DEFAULT '男' COMMENT '性别',
6. class_id INT COMMENT '班级ID',
7. CREATE_TIME DATETIME DEFAULT NOW() COMMENT '创建时间'
8. );

复制代码

常用约束：

约束	关键字	说明
主键约束	PRIMARY KEY	唯一标识每一行，不能为 NULL
自增	AUTO_INCREMENT	主键自动递增
非空约束	NOT NULL	该列不能为空
唯一约束	UNIQUE	该列值不能重复
默认值	DEFAULT	插入时未指定则使用默认值
外键约束	FOREIGN KEY	引用另一张表的主键
检查约束	CHECK	满足指定条件（MySQL 8.0+）

---

二、SQL 查询语句

2.1 基础查询

1. -- 查询所有列
2. SELECT * FROM student;
4. -- 查询指定列
5. SELECT name, age FROM student;
7. -- 给列起别名
8. SELECT name AS 姓名, age AS 年龄 FROM student;
10. -- 去重查询
11. SELECT DISTINCT class_id FROM student;

复制代码

2.2 条件查询（WHERE）

1. -- 比较运算符
2. SELECT * FROM student WHERE age = 20;
3. SELECT * FROM student WHERE age > 18;
4. SELECT * FROM student WHERE age >= 18;
5. SELECT * FROM student WHERE age != 20;
6. SELECT * FROM student WHERE age <> 20; -- 同 !=
8. -- 逻辑运算符
9. SELECT * FROM student WHERE age > 18 AND gender = '女';
10. SELECT * FROM student WHERE age < 16 OR age > 22;
11. SELECT * FROM student WHERE NOT gender = '男';
13. -- 范围查询
14. SELECT * FROM student WHERE age BETWEEN 18 AND 22; -- 包含边界
15. SELECT * FROM student WHERE age NOT BETWEEN 18 AND 22;
17. -- 集合查询
18. SELECT * FROM student WHERE class_id IN (1, 3, 5);
19. SELECT * FROM student WHERE class_id NOT IN (1, 3, 5);
21. -- 模糊查询
22. SELECT * FROM student WHERE name LIKE '张%'; -- 以"张"开头
23. SELECT * FROM student WHERE name LIKE '%伟'; -- 以"伟"结尾
24. SELECT * FROM student WHERE name LIKE '%小%'; -- 包含"小"
25. SELECT * FROM student WHERE name LIKE '张_'; -- "张"后跟一个字符
27. -- 空值判断
28. SELECT * FROM student WHERE class_id IS NULL;
29. SELECT * FROM student WHERE class_id IS NOT NULL;

复制代码

2.3 排序（ORDER BY）

1. -- 升序（默认）
2. SELECT * FROM student ORDER BY age ASC;
4. -- 降序
5. SELECT * FROM student ORDER BY age DESC;
7. -- 多字段排序：先按 class_id 升序，class_id 相同再按 age 降序
8. SELECT * FROM student ORDER BY class_id ASC, age DESC;

复制代码

2.4 分页（LIMIT）

1. -- 每页显示 10 条，取第 1 页
2. SELECT * FROM student LIMIT 10; -- 等价于 LIMIT 0,10
4. -- 每页显示 10 条，取第 2 页
5. SELECT * FROM student LIMIT 10, 10; -- 跳过前 10 条，取 10 条
7. -- 通用公式：第 n 页，每页 size 条
8. SELECT * FROM student LIMIT (n-1)*size, size;

复制代码

2.5 分组查询（GROUP BY）

1. -- 按 class_id 分组，统计每组人数
2. SELECT class_id, COUNT(*) AS 人数
3. FROM student
4. GROUP BY class_id;
6. -- 分组后过滤（HAVING）
7. SELECT class_id, COUNT(*) AS 人数
8. FROM student
9. GROUP BY class_id
10. HAVING COUNT(*) > 3;
12. -- 多字段分组
13. SELECT class_id, gender, COUNT(*) AS 人数
14. FROM student
15. GROUP BY class_id, gender;

复制代码

WHERE 和 HAVING 的区别：

• WHERE 在分组前过滤，作用于行
• HAVING 在分组后过滤，作用于组
• WHERE 不能用聚合函数，HAVING 可以

2.6 SQL 语句执行顺序

1. SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

复制代码

实际执行顺序：

1. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

复制代码

先确定数据来源，再逐层过滤、分组、排序、截取。

2.7 多表查询

内连接（INNER JOIN）

只返回两张表中匹配的行：

1. SELECT s.name, c.class_name
2. FROM student s
3. INNER JOIN class c ON s.class_id = c.id;

复制代码

左外连接（LEFT JOIN）

返回左表所有行，右表无匹配则补 NULL：

1. SELECT s.name, c.class_name
2. FROM student s
3. LEFT JOIN class c ON s.class_id = c.id;

复制代码

右外连接（RIGHT JOIN）

返回右表所有行，左表无匹配则补 NULL：

1. SELECT s.name, c.class_name
2. FROM student s
3. RIGHT JOIN class c ON s.class_id = c.id;

复制代码

自连接

一张表自己和自己连接：

1. -- 查询员工及其直属领导
2. SELECT e.name AS 员工, m.name AS 领导
3. FROM employee e
4. LEFT JOIN employee m ON e.manager_id = m.id;

复制代码

子查询

1. -- 标量子查询：子查询返回单个值
2. SELECT * FROM student
3. WHERE class_id = (SELECT id FROM class WHERE class_name = '一班');
5. -- 列子查询：子查询返回一列
6. SELECT * FROM student
7. WHERE class_id IN (SELECT id FROM class WHERE grade = '高一');
9. -- 行子查询：子查询返回一行
10. SELECT * FROM student
11. WHERE (class_id, age) = (SELECT class_id, age FROM student WHERE name = '张三');
13. -- EXISTS 子查询
14. SELECT * FROM class c
15. WHERE EXISTS (SELECT 1 FROM student s WHERE s.class_id = c.id);

复制代码

2.8 DML 语句

1. -- 插入数据
2. INSERT INTO student (name, age, gender) VALUES ('张三', 20, '男');
4. -- 批量插入
5. INSERT INTO student (name, age, gender) VALUES
6. ('李四', 21, '男'),
7. ('王五', 19, '女');
9. -- 修改数据
10. UPDATE student SET age = 21 WHERE name = '张三';
12. -- 删除数据
13. DELETE FROM student WHERE id = 1;
15. -- 清空表（重置自增）
16. TRUNCATE TABLE student;

复制代码

---

三、常用函数

3.1 字符串函数

函数	说明	示例	结果
CONCAT(s1, s2, ...)	拼接字符串	CONCAT('Hello', ' ', 'World')	Hello World
LENGTH(s)	字符串字节长度	LENGTH('你好')	6（UTF-8下3字节×2）
CHAR_LENGTH(s)	字符串字符数	CHAR_LENGTH('你好')	2
UPPER(s)	转大写	UPPER('hello')	HELLO
LOWER(s)	转小写	LOWER('HELLO')	hello
TRIM(s)	去掉两端空格	TRIM(' hi ')	hi
LPAD(s, len, pad)	左填充到指定长度	LPAD('5', 3, '0')	005
RPAD(s, len, pad)	右填充到指定长度	RPAD('hi', 5, '!')	hi!!!
SUBSTRING(s, pos, len)	截取子串（pos从1开始）	SUBSTRING('Hello', 1, 3)	Hel
REPLACE(s, old, new)	替换	REPLACE('abcabc', 'ab', 'x')	xcxc
LEFT(s, n)	取左边n个字符	LEFT('Hello', 3)	Hel
RIGHT(s, n)	取右边n个字符	RIGHT('Hello', 2)	lo
INSTR(s, substr)	子串首次出现位置	INSTR('hello', 'll')	3
REVERSE(s)	反转字符串	REVERSE('abc')	cba

实际应用：

1. -- 工号补零：1 → 001, 23 → 023
2. SELECT LPAD(id, 3, '0') AS 工号, name FROM employee;
4. -- 隐藏手机号中间四位
5. SELECT INSERT(phone, 4, 4, '****') AS 手机号 FROM user;
6. -- 或
7. SELECT CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS 手机号 FROM user;

复制代码

3.2 数值函数

函数	说明	示例	结果
CEIL(x)	向上取整	CEIL(1.2)	2
FLOOR(x)	向下取整	FLOOR(1.8)	1
ROUND(x, d)	四舍五入保留d位小数	ROUND(3.1415, 2)	3.14
TRUNCATE(x, d)	截断到d位小数	TRUNCATE(3.1415, 2)	3.14
MOD(m, n)	取模（求余）	MOD(10, 3)	1
ABS(x)	绝对值	ABS(-5)	5
POWER(x, n)	x的n次方	POWER(2, 3)	8
SQRT(x)	平方根	SQRT(16)	4
RAND()	0~1随机数	RAND()	如 0.3726

实际应用：

1. -- 生成 1~100 的随机整数
2. SELECT CEIL(RAND() * 100);
4. -- 保留两位小数计算单价
5. SELECT ROUND(price * 0.8, 2) AS 折后价 FROM product;
7. -- 判断奇偶
8. SELECT id, IF(MOD(id, 2) = 0, '偶数', '奇数') AS 奇偶 FROM student;

复制代码

3.3 日期和时间函数

函数	说明	示例	结果
NOW()	当前日期时间	NOW()	2026-06-05 22:30:00
CURDATE()	当前日期	CURDATE()	2026-06-05
CURTIME()	当前时间	CURTIME()	22:30:00
YEAR(d)	取年份	YEAR('2026-06-05')	2026
MONTH(d)	取月份	MONTH('2026-06-05')	6
DAY(d)	取日	DAY('2026-06-05')	5
HOUR(d)	取小时	HOUR('22:30:00')	22
MINUTE(d)	取分钟	MINUTE('22:30:00')	30
DAYNAME(d)	星期几（英文）	DAYNAME('2026-06-05')	Friday
DAYOFWEEK(d)	星期几（1=周日）	DAYOFWEEK('2026-06-05')	6
DATEDIFF(d1, d2)	日期差（d1-d2天数）	DATEDIFF('2026-06-05', '2026-01-01')	155
DATE_ADD(d, INTERVAL n UNIT)	日期加	DATE_ADD('2026-06-05', INTERVAL 30 DAY)	2026-07-05
DATE_SUB(d, INTERVAL n UNIT)	日期减	DATE_SUB('2026-06-05', INTERVAL 1 MONTH)	2026-05-05
DATE_FORMAT(d, fmt)	格式化日期	DATE_FORMAT(NOW(), '%Y年%m月%d日')	2026年06月05日
STR_TO_DATE(s, fmt)	字符串转日期	STR_TO_DATE('2026-06-05', '%Y-%m-%d')	2026-06-05

INTERVAL 单位： YEAR、MONTH、DAY、HOUR、MINUTE、SECOND

日期格式化占位符：

占位符	含义	示例
%Y	四位年份	2026
%m	两位月份	06
%d	两位日	05
%H	24制小时	22
%i	分钟	30
%s	秒	00

实际应用：

1. -- 查询最近7天内注册的用户
2. SELECT * FROM user WHERE create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);
4. -- 计算年龄
5. SELECT name, YEAR(NOW()) - YEAR(birthday) AS 年龄 FROM student;
7. -- 按月统计订单
8. SELECT DATE_FORMAT(order_time, '%Y-%m') AS 月份, COUNT(*) AS 订单数
9. FROM orders
10. GROUP BY DATE_FORMAT(order_time, '%Y-%m');
12. -- 查询入职超过5年的员工
13. SELECT * FROM employee
14. WHERE DATEDIFF(NOW(), hire_date) > 365 * 5;

复制代码

3.4 条件函数

函数	说明
IF(cond, val1, val2)	条件为真返回 val1，否则返回 val2
IFNULL(val1, val2)	val1 不为 NULL 则返回 val1，否则返回 val2
NULLIF(val1, val2)	相等返回 NULL，不等返回 val1
CASE WHEN ... THEN ... ELSE ... END	多条件分支

1. -- IF：成绩及格判断
2. SELECT name, IF(score >= 60, '及格', '不及格') AS 结果 FROM exam;
4. -- IFNULL：佣金为空时显示0
5. SELECT name, IFNULL(commission, 0) AS 佣金 FROM employee;
7. -- CASE WHEN：成绩等级
8. SELECT name,
9. CASE
10. WHEN score >= 90 THEN '优秀'
11. WHEN score >= 80 THEN '良好'
12. WHEN score >= 60 THEN '及格'
13. ELSE '不及格'
14. END AS 等级
15. FROM exam;
17. -- CASE WHEN：按字段值映射
18. SELECT name,
19. CASE gender
20. WHEN 'M' THEN '男'
21. WHEN 'F' THEN '女'
22. ELSE '未知'
23. END AS 性别
24. FROM student;

复制代码

3.5 聚合函数

聚合函数对一组值进行计算，返回单个值，常与 GROUP BY 配合使用。

函数	说明	示例
COUNT(*)	统计总行数	SELECT COUNT(*) FROM student
COUNT(列)	统计该列非 NULL 的行数	SELECT COUNT(score) FROM exam
SUM(列)	求和	SELECT SUM(salary) FROM employee
AVG(列)	求平均值	SELECT AVG(score) FROM exam
MAX(列)	求最大值	SELECT MAX(age) FROM student
MIN(列)	求最小值	SELECT MIN(age) FROM student

1. -- 统计每个班级的平均分和最高分
2. SELECT class_id,
3. COUNT(*) AS 人数,
4. ROUND(AVG(score), 2) AS 平均分,
5. MAX(score) AS 最高分,
6. MIN(score) AS 最低分
7. FROM exam
8. GROUP BY class_id
9. HAVING AVG(score) >= 70
10. ORDER BY 平均分 DESC;
12. -- 统计有佣金员工的佣金平均值
13. SELECT AVG(commission) AS 平均佣金 FROM employee WHERE commission IS NOT NULL;
14. -- 等价写法
15. SELECT AVG(IFNULL(commission, 0)) AS 平均佣金 FROM employee;
16. -- 注意：这两种结果不同！第二种把 NULL 当 0 参与了平均

复制代码

NULL 对聚合函数的影响：

• COUNT(*) 统计所有行，包括 NULL
• COUNT(列) 忽略 NULL
• SUM、AVG、MAX、MIN 自动忽略 NULL
• 对全为 NULL 的列，SUM/AVG 返回 NULL

---

四、知识总结

SQL 语句分类

分类	全称	说明	关键字
DDL	Data Definition Language	定义结构	CREATE、ALTER、DROP
DML	Data Manipulation Language	操作数据	INSERT、UPDATE、DELETE
DQL	Data Query Language	查询数据	SELECT
DCL	Data Control Language	控制权限	GRANT、REVOKE

查询语句完整语法

1. SELECT DISTINCT 列名
2. FROM 表名
3. JOIN 表名 ON 连接条件
4. WHERE 行过滤条件
5. GROUP BY 分组列
6. HAVING 组过滤条件
7. ORDER BY 排序列 ASC|DESC
8. LIMIT 偏移量, 行数;

复制代码

函数速记口诀

• 字符串：拼接 CONCAT、截取 SUBSTRING、填充 LPAD/RPAD、替换 REPLACE
• 数值：取整 CEIL/FLOOR、四舍五入 ROUND、取余 MOD、随机 RAND
• 日期：当前 NOW/CURDATE/CURTIME、提取 YEAR/MONTH/DAY、运算 DATE_ADD/DATEDIFF、格式化 DATE_FORMAT
• 条件：二选一 IF、防空 IFNULL、多分支 CASE WHEN
• 聚合：计数 COUNT、求和 SUM、平均 AVG、极值 MAX/MIN

---

本文基于 MySQL 8.0 整理，部分函数在早期版本中可能表现不同。