前言

本文是对黑马程序员的《Mysql从入门到精通》系列视频的基础部分的笔记。

1.SQL语句

1.1 SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾。
2.SQL语句可以使用空格/缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4.注释：
单行注释：--注释内容或#注释内容(MySQL特有)
多行注释：/*注释内容*/

1.2 SQL分类

1.3 DDL语句的使用

1.3.1 数据库操作

查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

创建

CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集 ] [ COLLATE 排序规则 ];

删除

DROP DATABASE [ IF EXISTS ] 数据库名;

使用

USE 数据库名;

1.3.2 数据表单操作

查询当前数据库所有表

SHOW TABLES;

查询表结构

DESC 表名;

查询指定表的建表语句

SHOW CREATE TABLE 表名；

创建表

CREATE TABLE表名(
字段1 字段1类型 [COMMENT 字段1注释]，
字段2 字段2类型 [COMMENT 字段2注释]，
字段3 字段3类型 [COMMENT 字段3注释]，
...
字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT表注释]；

往表中增加内容

ALTER TABLE 表名 ADD 字段名类型(长度) [COMMENT注释] [约束];

修改表中字段的数据类型

ALTER TABLE 表明 MODIFY 字段名 新数据类型(长度) [COMMENT注释] [约束];

修改表中数据的字段名和数据类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型(长度) [COMMENT注释] [约束];

删除字段

ALTER TABLE 表名 DROP 字段名;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

删除表

DROP TABLE [IF EXISTS] 表名;

删除并重新创建表：一般用于清空表中数据

TRUNCATE TABLE 表名;

1.3.3 Mysql字段数据类型梳理

1. 数值类型

2. 字符串类型

3. 日期与时间类型

✅ 总结使用场景：

• 主键 ID：INT / BIGINT
• 金额：DECIMAL(10,2)
• 文章内容：TEXT
• 上传的文件/图片：路径存 VARCHAR，文件本身建议存在对象存储（而不是 BLOB）
• 时间记录：DATETIME（推荐）或 TIMESTAMP（带时区）
• 状态/枚举值：TINYINT 或 ENUM

1.4 DML语句的使用

DML英文全称是DataManipulationLanguage(数据操作语言），用来对数据库中表的数据记录进行增删改操作。

1.4.1 插入数据

给表中部分字段插入数据

INSERT INTO 表名(字段名1,字段名2,..) VALUES(值1，值2,...);

给表中的所有字段插入数据

INSERT INTO 表名 VALUES(值1，值2,...);

给表中的部分字段批量添加多条数据

INSERT INTO 表名(字段名1,字段名2,..) VALUES(值1,值2,..),(值1,值2,...),(值1,值2,..);

给表中的所有字段批量插入数据

INSERT INTO 表名 VALUES(值1,值2,..),(值1,值2,...),(值1,值2,..);

1.4.2 修改数据

UPDATE 表名 SET 字段名1=值1,字段名2=值2... [WHERE 条件];

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

1.4.3 删除数据

DELETE FROM 表名 [WHERE 条件]

注意：
DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
DELETE语句不能删除某一个字段的值(可以使用UPDATE)。

那么不加WHERE语句的DELETE操作和上面提到的TRUNCATE操作有啥区别呢？一句话总结：

• TRUNCATE TABLE 速度快(直接全部删除)，危险大(通常无法回滚)，触发器不响应，自增主键会重置；
• DELETE（无条件）速度慢(一条一条删除)，安全（可以回滚)，触发器响应，自增主键不重置。

1.5 DQL语句的使用

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数

1.5.1 基本查询

1.查询多个字段

SELECT 字段1,字段2,字段3... FROM 表名;
SELECT * FROM 表名;

2.设置别名

SELECT 字段1 [AS 别名1]，字段2 [AS 别名2]... FROM 表名；

3.去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

1.5.2 条件查询

1.基本语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2.条件

1.5.3 聚合函数

1.介绍
将一列数据作为一个整体，进行纵向计算。

2.常见聚合函数

3.语法

SELECT 聚合函数() FROM 表名;

注意：所有的null值不参与聚合函数运算。

1.5.4 分组查询

1.语法

SELECT 字段列表 FROM 表名 [WHERE条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

2.where与having区别
(1)执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
(2)判断条件不同：where不能对聚合函数进行判断，而having可以。

例子：
1.根据性别分组，统计男性员工和女性员工的数量

select gender,count(*) from emp group by gender;

2.根据性别分组，统计男性员工和女性员工的平均年龄

select gender,avg(age) from emp group by gender;

3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

select workaddress, count(*) address_count from emp where age < 45 group by workeddress having address_count >= 3;

注意:
执行顺序:where>聚合函数>having。
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

1.5.5 排序查询

1.语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

2.排序方式

ASC:升序(默认值)

DESC:降序

3.例子：

排序查询
(1)根据年龄对公司的员工进行升序排序

select * from emp order by age asc; -- 升序
select * from emp order by age desc; -- 降序
select * from emp order by age; -- 默认升序

(2)根据入职时间，对员工进行降序排序

select * from emp order by entrydate desc;

(3)根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序

select * from emp order by age asc, entrydate desc;

1.5.6 分页查询

1.语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注意:

1. 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。
2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
3. 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

2.例子
1.查询第1页员工数据，每页展示10条记录

select * from emp limit 0,10;
select * from emp limit 10;

2．查询第2页员工数据，每页展示10条记录

select * from emp limit 10,10;

1.5.7 案例总结

按照需求完成如下DQL语句编写
1.查询年龄为20,21,22,23岁的女性员工信息。

select * from emp where gender = '女' and age in(20,21,22,23);

2.查询性别为男，并且年龄在20-40岁(含)以内的姓名为三个字的员工。

select * from emp where gender = '男' and (age between 20 and 40) and name like '___';

3.统计员工表中，年龄小于60岁的，男性员工和女性员工的人数。

select gender,count(*) from emp where age < 60 group by gender;

4.查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。

select name,age from emp where age<=35 order by age asc,entrydate desc;

5.查询性别为男，且年龄在20-40岁（含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

select * from emp where gender = '男' and (age between 20 and 40) order by age asc,entrydate asc limit 5;

1.5.8 执行顺序

从上到下执行。

FROM
    表名列表
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后条件列表
SELECT
    字段列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

例子：
查询年龄大于15的员工的姓名、年龄，并根据年龄进行升序排序

select e.name ename,e.age eage from emp e where eage > 15 order by age asc;

此处报错。因为select在where之后执行，where这里用不到sel命名的别名。

1.5.9 DQL语句总结

如下图所示。

1.6 DCL语句的使用

1.6.1 用户管理

DCL-介绍
DCL英文全称是DataControlLanguage（数据控制语言），用来管理数据库用户、控制数据库的访问权限。

DCL-管理用户

1.查询用户

USE mysql;
SELECT * FROM user;

2.创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'；

3.修改用户密码

ALTER USER'用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

4.删除用户

DROP USER '用户名'@'主机名';

用例

创建用户itcast，只能够在当前主机localhost访间，密码123456;

create user 'itcast'@'localhost' identified by '123456';

创建用户heima，可以在任意主机访问该数据库，密码123456；

create user 'heima'@'%' identified by '123456';

修改用户heima的访问密码为1234

alter user 'heima'@'%' identified with mysql_native_password by '1234';

删除itcast@localhost用户

drop user 'itcast'@'localhost';

这部分语句主要是运维在使用，开发人员了解即可，无需重点掌握。

1.6.2 权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

1.查询权限

SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'heima'@'%'; -- example

2.授予权限

GRANT 权限列表 ON 数据库名:表名 TO '用户名'@'主机名';
grant all on itcast.* to 'heima'@'%';

3.撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
revoke all on itcast.* from 'heima'@'%';

注意：

1. 多个权限之间，使用逗号分隔
2. 授权时，数据库名和表名可以使用*进行通配，代表所有。

1.6.3 小结

1.7 函数

1.7.1 字符串函数

常用的Mysql字符串函数如下：

用例：

1.concat

select concat('Hello','MySQL');

2.lower

select lower('Hello');

3.upper

select upper('Hello');

4.lpad

select lpad('01',5,'-');

5.rpad

select rpad('01',5,'-');

6.trim

select trim(' Hello Mysql! ');

7.substring

select substring('Hello Mysql',1,5) -- 索引1based

场景题：

由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001。

update emp set workno = lpad(workno,5,'0');

1.7.2 数值函数

Mysql常见的数值函数如下：

用例：

1.ceil

select ceil(1.4); -- 2

2.floor

select floor(1.9) -- 1

3.mod

select mod(6,4); -- 2

4.rand

select rand();

5.round

select round(5.123411,2) -- 5.12 四舍五入

场景题：

通过数据库的函数，生成一个六位数的随机验证码。

select round(rand()*1000000,0);

1.7.3 日期函数

常见的日期函数如下：

示例：

1.curdate

select curdate(); -- 2025-10-21

2.curtime

select curtime(); -- 20:00:00

3.now

select now();

4.year month day

select YEAR(now()); -- 2025
select MONTH(now()); -- 10
select DAY(now()); -- 21

5.date_add

select date_add(now(),INTERVAL 70 DAY); -- 2025-12-30

6.datediff

select datediff('2025-09-01','2025-10-21');
-- 50 是第一个时间减去第二个时间

场景题：

查询所有员工的入职天数，并根据入职天数倒序排序。

select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;

1.7.4 流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

示例：

1.if

select if(true,'ok','Error'); -- ok

2.ifnull

select ifnull('ok','Default'); -- ok
select ifnull('','Default'); -- 返回空串
select ifnull(null,'Default') -- Default

3.case when then else end

需求：查询emp表的员工姓名和工作地址（北京/上海---->一线城市，其他---->二线城市）

select
    name,
    (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

场景题:

案例：统计班级各个学员的成绩，展示的规则如下：

>=85，展示优秀；
>=60，展示及格；
否则，展示不及格。

select id,
    (case when math>=85 then '优秀' when math >= 60 then '及格' else '不及格' end) '数学',
    (case when english>=85 then '优秀' when english >= 60 then '及格' else '不及格' end) '英语',
    (case when chinese>=85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) '语文'
    from score;