零基础学MySQL--基础篇

（小白视角，纯记录学习，如有不严谨请谅解，欢迎指正。仅供学习参考）

一、MySQL概述

1、安装My
SQL：

进入mysql.com 点击下载，找到社区免费版，下载windows版本安装包即可。

2、启动/停止My
SQL：

方式①：Win+R输入services.msc，找到MySQL80，点击启动/停止。

方式②：管理员身份运行cmd，输入net start/stop mysql80 来启动/停止。

3、My
SQL客户端连接：

方式①：

输入创建的密码即可。

方式②：配置PATH环境变量，用命令行工具执行。

(1)配置PATH环境变量：

系统/系统信息/高级系统设置/环境变量/系统变量Path新建：

C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin

(2)Win+R输入cmd：输入mysql -u root -p     再输入密码即可。

4、MySQL数据库模型：

DBMS是一个软件，用来创建、操作数据库。

一个数据库可以包含多张表结构，表结构才是最终存储数据的。

关系型数据库（RDBMS）:

建立在关系模型的基础上，由多张相互连接的二维表组成的数据库。表与表之间可通过字段关联。

用表存储数据，格式统一，便于维护。

用sql语言操作，标准统一，便于使用。

二、SQL

1、SQL通用语法

（1）SQL语句可以单行、多行书写，以分号结尾。

（2）SQL语句可用空格和缩进增强可读性。

（3）MySQL的SQL语句不区分大小写，建议关键字使用大写字母。

（4）注释：

• 单行：–（注释内容）  或  #（注释内容）
• 多行：/*（注释内容）*/

2、SQL分类

3、DDL（数据定义语言）

（Data Definition Language）

（1）数据库操作 

 (方括号[ ]内部的都是可选择性填写的内容)

查询：

• 查询所有数据库：SHOW DATABASES;
• 查询当前数据库：SELECT DATABASE();

创建：

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

[IF NOT EXISTS] ：如果数据库名不存在就创建，存在就不执行。

[DEFAULT  CHARSET  字符集] ：设置默认字符集

[COLLATE 排序规则] ：设置排序规则

删除：

• DROP  DATABASE  [IF EXISTS]  数据库名；

使用：

• USE 数据库名

实践一下：记得加第三人称单数666

show databases;
create database itcast;
show databases;
create database itcast; #会报错，因为已经创建了
create database if not exists itcast; #不存在则创建
create database if not exists itheima default charset utf8mb4; #设置默认字符集utf8mb4
drop database itcast;
drop database itcast; #会报错，因为不存在了
drop database if exists itcast; #存在则删除
use itheima;
select database(); #这是函数，要加()

（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 表注释];

找个例子实践一下：

create database if not exists itcast;
use itcast;
create table tb_user(
id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
gender varchar(1) comment '性别'
)comment '用户表';

验证一下创建结果：

show tables;
desc tb_user;
show create table tb_user;

这里用到了数据类型int、varchar，它们代表什么？还有哪些数据类型？接下来进入下一部分

（3）表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：

• 数值类型
• 字符串类型
• 日期时间类型

数值类型：

整数 有 tinyint , smallint , mediumint , int(integer) , bigint 。

浮点数 有 float , double , decimal 。

字符串类型：

char和varchar相比较，char(10)固定10字符，varchar(10)不超过10字符。

用户名字段适合用varchar(50)表示，而性别之分男女，适合用char(1)表示。

日期时间类型：

案例：

根据需求创建表(设计合理的数据类型、长度)

设计一张员工信息表,要求如下:

1. 编号(纯数字)

2. 员工工号(字符串类型,长度不超过10位)

3. 员工姓名(字符串类型,长度不超过10位)

4. 性别(男/女,存储一个汉字)

5. 年龄(正常人年龄,不可能存储负数)

6. 身份证号(二代身份证号均为18位,身份证中有X这样的字符)

7. 入职时间(取值年月日即可)

create database if not exists itcast;
use itcast;
create table emp(
id int comment '编号',
work_num varchar(10) comment '员工工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
entrydate date comment '入职时间'
)comment '员工信息表';
show create table emp;

DDL-表操作-修改&删除

注意：删除表时，表中的数据也会被删除！

（1）修改：ALTER  TABLE

• 添加字段：ALTER  TABLE  表名  ADD
    字段名  类型（长度）[COMMENT  注释]  [约束];
• 修改数据类型：ALTER  TABLE  表名  MODIFY
    字段名  新数据类型（长度）;
• 修改字段名和字段类型：ALTER  TABLE  表名  CHANGE
    旧字段名  新字段名  类型（长度）[COMMENT  注释]  [约束]
• 修改表名：ALTER  TABLE  表名  RENAME  TO
    新表名;

（2）删除：  

• 删除字段：ALTER  TABLE  表名  DROP  字段名; 
• 删除表：DROP  TABLE  [IF  EXISTS]  表名;
• 删除指定表，并重新创建该表：TRUNCATE  TABLE  表名;

实操一波：

为emp表增加一个新的字段”昵称”为nickname,类型为varchar(20)

将emp表的nickname字段修改为username,类型为varchar(30)

将emp表的字段username删除

将emp表的表名修改为employee

alter table emp add nickname varchar(20) comment '昵称';
alter table emp change nickname username varchar(30);
alter table emp drop username;
alter table emp rename to employee;

DDL总结：

##阅读培养语感
show databases;
create database if not exists user;
use user;
select database();

show tables;
create table user_info(
id int coment '编号',
name varchar(20) comment '用户名',
gender char(1) comment '性别',
birthday date comment '生日'
)comment '用户信息';

desc user_info;
show create user_info;
alter table user_info add age int comment '年龄';
alter table user_info change name nickname varchar(10);
alter table user_info rename to info;
desc info;

4、DML（数据操作语言）

DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增删改操作。

• 添加数据 ( INSERT ) 
• 修改数据 ( UPDATE )
• 删除数据 ( DELETE )

DML大多都是操作表的“行”数据，可以理解成“根据条件处理向量”。

（1）添加 INSERT INTO 

给指定字段添加数据：

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

批量添加数据：

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

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

注意：

• 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小,应该在字段的规定范围内。

（2）修改 UPDATE … SET

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

注意：条件语句可有可无。如果无，则改动整张表的数据。

（3）删除 DELETE FROM

• DELETE  FROM  表名  [ WHERE  条件 ]

注意：DELETE不能删除某字段的值。实现该功能可用  UPDATE 字段名 = NULL

5、DQL（数据查询语言）

DQL（Data Query Language）是SQL中专门用于数据查询的子集，

核心命令是 SELECT … FROM … WHERE …

（1）条件

通过 WHERE 添加筛选条件，支持运算符（>, <, =, !=）和逻辑符（AND, OR）。

— 查询工资大于5000的员工
SELECT name, salary FROM employees WHERE salary > 5000;

— 查询部门为“技术部”且工资高于6000的员工
SELECT * FROM employees
WHERE department = '技术部' AND salary > 6000;

注意：字符串需用单引号包裹（'技术部'）。

（2）聚合函数

• COUNT()：统计数量
• MAX()/MIN()：最大值/最小值
• SUM()：求和
• AVG()：平均值

— 统计员工总数
SELECT COUNT(*) FROM employees;

— 计算技术部的平均工资
SELECT AVG(salary) FROM employees WHERE department = '技术部';

（3）分组 GROUP BY  注意：WHERE 在分组前过滤，HAVING 在分组后过滤。

— 统计每个部门的平均工资
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

— 查询平均工资大于7000的部门
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 7000;

（4）排序 ORDER BY 

• ASC：升序（默认）
• DESC：降序

— 按工资从高到低排序
SELECT name, salary FROM employees ORDER BY salary DESC;

— 先按部门升序，同部门按工资降序
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

（5）分页 LIMIT 用 LIMIT 起始索引, 条数 实现分页         公式：起始索引 = (页码 – 1) × 每页条数

— 每页10条数据，查看第1页
SELECT * FROM employees LIMIT 0, 10;

— 查看第2页
SELECT * FROM employees LIMIT 10, 10;

类比：书籍的页码，避免一次性加载海量数据。

DQL-案例练习

目标：综合运用DQL基础操作 假设有订单表 orders（字段：order_id, product, price, order_date）：

— 查询2023年单价超过100的商品名称及总销售额
SELECT
product,
SUM(price) AS total_sales
FROM orders
WHERE order_date >= '2023-01-01' AND price > 100
GROUP BY product
ORDER BY total_sales DESC
LIMIT 5;

DQL-执行顺序

关键：理解SQL的底层逻辑执行顺序：

SQL语句的逻辑执行顺序并非按照书写顺序从左到右执行，而是遵循一套优化后的处理流程。理解这一顺序有助于编写高效查询和排查问题。

FROM阶段

FROM子句最先执行，确定数据来源。数据库引擎从指定的表或子查询中提取原始数据集。如果是多表连接，会在此阶段生成笛卡尔积或按连接条件合并数据。

技巧：可以用 “ FROM 表名 别名 ”  的方式给表另起别名。此后的语句只能使用别名。

WHERE阶段

对FROM生成的原始数据行进行过滤。WHERE条件会逐行评估，只保留满足条件的记录。这个阶段尚未处理聚合函数或别名。关键点：

• 过滤发生在分组前，显著减少后续操作的数据量
• 不能使用SELECT中定义的列别名
• 条件表达式应尽量使用索引列

GROUP BY阶段 （分组—必—聚合）

按指定列对过滤后的数据进行分组。每组生成一个汇总行，非分组列必须使用聚合函数。

HAVING阶段

对分组后的结果进行二次过滤。与WHERE不同，HAVING可以包含聚合函数。

SELECT阶段

此时才确定最终输出的列。可以：

• 从原始表中选择列
• 使用聚合函数计算结果
• 用AS定义列别名（区别于 FROM 阶段定义表别名）
• 执行计算表达式 重要限制：前面阶段无法引用本阶段定义的别名。

ORDER BY阶段

对SELECT的输出结果排序。这是唯一可以使用SELECT阶段别名的环节

LIMIT阶段

最后应用结果集的行数限制。在排序后截取指定数量的行，这对分页查询至关重要。

顺序设计原理

该顺序反映数据处理逻辑流：

这种分阶段处理方式允许数据库引擎优化执行计划，例如在WHERE阶段就过滤掉90%数据，避免对无用数据进行分组计算。

DQL小结：

graph LR
A[SELECT] –> B[FROM]
B –> C[WHERE]
C –> D[GROUP BY]
D –> E[HAVING]
E –> F[ORDER BY]
F –> G[LIMIT]

核心能力：

典型错误警示：

• 混淆WHERE（行过滤）和HAVING（组过滤）
• JOIN时忘记关联条件导致笛卡尔积
• GROUP BY时SELECT包含非聚合列

6、DCL（数据控制语言）

创建用户：

CREATE USER … IDENTIFIED BY …

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

#实例：创建一个名为Jasper，允许从任意主机登录,密码为123456的用户

CREATE USER 'Jasper'@'%' IDENTIFIED BY '123456';

对于host， 如果是%，则表示可以从任意主机登录。如果是localhost ，则表示只能本地登录。

修改密码：

ALTER USER … IDENTIFIED BY …

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

#实例：将Jasper的密码修改为 hello_SQL

ALTER USER 'Jasper'@'%' IDENTIFIED BY 'hello_SQL';

删除用户：

DROP USER 。。。

DROP USER 'user_name'@'host';

#实例：删除用户Jasper

DROP USER 'Jasper'@'%';

授予权限：

GRANT … ON … TO …

GRANT privilege_type ON database_name.table_name TO 'username'@'host';

• privilege_type：指定权限类型，可同时授予多个权限，用逗号分隔。
• database_name.table_name：指定权限作用的数据库和表,*.* 表示所有数据库和表。

权限汇总：

• 全局权限：如ALL PRIVILEGES。
• 数据库权限：如CREATE、DROP数据库。
• 表权限：SELECT、INSERT、UPDATE、DELETE。
• 列权限：针对表中的特定列。
• 存储过程权限：如EXECUTE。

回收权限：

REVOKE … ON … FROM …

REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

#示例：收回Jasper对user数据库中所有表的INSERT权限

REVOKE INSERT ON user.* FROM 'Jasper'@'%';

角色：

创建角色：CREATE ROLE 

CREATE ROLE 'role_name';

将权限授予角色：GRANT … ON … TO … 

GRANT privilege_type ON database_name.table_name TO 'role_name';

将角色授予用户：GRANT ROLE … TO …  

GRANT ROLE 'role_name' TO 'username'@'host';

从用户中删除角色：REVOKE ROLE … FROM …  

REVOKE ROLE 'role_name' FROM 'username'@'host';

示例：

CREATE ROLE 'JOKER';
GRANT ALL PRIVILEGES ON people.game TO 'JOKER';
GRANT ROLE 'JOKER' ON 'Jasper'@'%';
REVOKE ROLE 'JOKER' ON 'Jasper'@'%';

查看权限&刷新：

查看用户权限：SHOW GRANT FOR …

SHOW GRANTS FOR 'Jasper'@'%';

刷新权限：FLUSH PRIVILEGES;

FLUSH PRIVILEGES;

权限生效：修改权限后，需执行FLUSH PRIVILEGES确保权限立即生效！！！

工程细节：

最小权限原则：仅授予用户完成任务所需要的最小权限。 定期审查权限：及时收回非必要权限，降低泄露风险。 使用角色管理：通过角色批量管理权限需求相似的用户，提高效率。

注意事项：

谨慎使用ALL PRIVILEGES，避免用户获得过高权限。 密码安全：设置强密码，并定期更换，防止密码泄露。 权限生效：修改权限后，需执行FLUSH PRIVILEGES确保权限立即生效。

三、函数

1、字符串函数

2、数值函数

— 计算折扣后价格（保留两位小数）
SELECT product, ROUND(price * 0.8, 2) AS discount_price FROM products;

— 将评分向上取整
SELECT title, CEIL(rating) AS rounded_rating FROM movies;

3、日期函数

4、流程函数

— 根据销售额标记业绩等级
SELECT
salesperson,
sales,
CASE
WHEN sales > 10000 THEN '金牌'
WHEN sales > 5000 THEN '银牌'
ELSE '铜牌'
END AS level
FROM sales_records;

四、约束

约束类型：

• 主键约束（唯一标识）
• 非空约束（字段必填）
• 唯一约束（值不重复）
• 默认约束（自动填充）
• 外键约束（关联其他表）

1、主键约束：PRIMARY KEY

用于唯一标识表中的每一条记录，具有以下特性：

• 值不可为空
• 值不可重复
• 一个表仅能有一个主键

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50)
);

2、非空约束：NOT NULL

确保字段值不允许为空 。

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE NOT NULL
);

3、唯一约束：UNIQUE

保证字段的值在表中不重复，但允许为空（与主键的区别）。

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductCode VARCHAR(20) UNIQUE
);

4、默认约束：DEFAULT …

当插入数据时，若未指定字段值，则自动填充预设的默认值。

CREATE TABLE Users (
UserID INT PRIMARY KEY,
CreateTime DATETIME DEFAULT GETDATE()
);

5、外键约束：FOREIGN KEY

用于维护表之间的关联关系，确保表之间的数据完整性。

注：

• 子表必须有外键，父表必须有主键。子表外键关联父表主键。
• 外键约束存在约束名，以便指定删除和修改。

（1）创建外键

①建表时定义：

CONSTRAINT (约束名) FOREIGN KEY (子表列名) REFERENCES 父表(父表列名)

CREATE TABLE 子表名 (
列名1 数据类型,
列名2 数据类型,
…
CONSTRAINT 约束名 FOREIGN KEY (子表列名) REFERENCES 父表名(父表列名)
);

②已有表上添加：

ADD CONSTRAINT (约束名) FOREIGN KEY (子表列名) REFERENCES 父表(父表列名)； ADD CONSTRAINT（约束名） FOREIGN KEY（子表列名） 参考文献 父表（父表列名）;

ALTER TABLE 子表名
ADD CONSTRAINT 约束名 FOREIGN KEY (子表列名) REFERENCES 父表名(父表列名);

 多列外键

可以创建基于多列的外键约束：

CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id),
CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(product_id)
);

（2）可选参数

在后方添加可选参数，定义在删除（ON DELETE
）、修改 (ON UPDATE
) 键数据时表的行为。

• RESTRICT/NO ACTION：阻止删除（默认）
• CASCADE
  ：级联删除子表相关记录。
• SET NULL
  ：将子表相关记录设为NULL
• SET DEFAULT
  ：将子表相关记录设为默认值

示例：

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
CONSTRAINT fk_customer FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE SET NULL
);

（3）删除外键约束 

ALTER TABLE （子表名） DROP CONSTRAINT （约束名）  

ALTER TABLE 子表名
DROP CONSTRAINT 约束名;

注意事项

• 外键列和引用列必须具有相同的数据类型
• 引用列必须是主键或唯一键
• MySQL的InnoDB引擎支持外键，MyISAM不支持
• 外键约束会影响性能，在高并发场景需谨慎使用

五、多表查询

表与表如何关联？

• 一对一
  （如：用户与身份证）
• 一对多
  （如：部门与员工）
• 多对多
  （如：学生与课程，需中间表）

多表查询实现方式：

• 连接查询
  （JOIN）
• 联合查询
  （UNION）
• 子查询/嵌套查询

1、连接查询 JOIN

• SELECT后的字段可以是不同表中的字段
• 连接声明 JOIN 出现在 FROM 之后
• 声明 JOIN 后紧跟条件 ON

（1）内连接
 

[ INNER ] JOIN … ON …

内连接查询的是两表的交集。

①隐式内连接：SELECT 字段列表 FROM 表1,表2 WHERE 条件 …;

②显式内连接：SELECT 字段列表 FROM 表1 [INNER] JOIN表2 ON 连接条件 … ;

— 查询员工及其部门名称
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

（2）外连接
 

LEFT/RIGHT [ OUTER ] JOIN … ON …

①左外连接：SELECT 字段列表 FROM 左表 LEFT  [OUTER]  JOIN 右表
 ON 条件;

相当于查询
左表
的所有数据,并
包含
交集部分的数据

②右外连接：SELECT 字段列表 FROM 左表 RIGHT [OUTER] JOIN 右表 ON条件;

相当于查询右
表的所有数据,并包含交集部分的数据

记忆：都保留主表全部数据

— 查询所有部门（包括无员工的部门）
SELECT d.name, e.name
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id;

（3）自连接

SELECT 字段列表 FROM 表 别名A JOIN 表 别名B ON 条件;

自连接查询,可以是内连接查询,也可以是外连接查询。

 核心：同一表内的层级关联，常用于树形结构（如：员工与上级经理）。

— 查询员工及其经理姓名
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

2、联合查询 UNION

联合查询用于合并多个SELECT语句的结果集，要求这些SELECT语句的列数相同且数据类型兼容。默认去除重复行，使用 UNION ALL 可保留重复行。

SELECT column1, column2 FROM table1
UNION [ALL]
SELECT column1, column2 FROM table2;

注意事项

• 列名以第一个SELECT语句为准。
• 数据类型必须兼容，否则需显式转换。
• 排序子句（ORDER BY）只能出现在最后一条SELECT语句后。

3、子查询/嵌套查询

select from where __ (select from where__(select from where__(……)))

根据子查询结果分类：（1）标量子查询（2）列子查询（3）行子查询（4）表子查询

根据子查询出现位置分类：（1）where之后 （2）select之后（3）from之后

（1）标量子查询：

子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询成为标量子查询。

select * from emp where dept_id = (select id from dept where name = '研发部');

（2）列子查询：

子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。

常用的操作符: IN、NOT IN、ANY、SOME、ALL （）

select * from emp where dept_id not in (select id from dept where name ='销售部'or name='市场部');

select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = ) );

select * from emp where salary > some ( select salary from emp where dept_id = (select id from dept where name ='研发部'));

（3）行子查询：

子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。

常用的操作符 : =

select * from emp where (salary, managerid) = (select salary, managerid from emp where name ='张无忌');

（4）表子查询：

子查询返回的结果是多行多列,这种子查询称为表子查询。常用or

select * from emp where (job,salary) in ( select job, salary from emp where name ='鹿杖客'or name='宋远桥');

六、事务

1、四大特性ACID

2、并发事务问题

• 脏读：读到未提交的数据
• 不可重复读：同一查询结果不同（数据被修改）
• 幻读：同一查询返回不同行数（数据被增删）

3、隔离级别（由低到高）

• READ UNCOMMITTED：可能脏读
• READ COMMITTED：避免脏读（默认）
• REPEATABLE READ：避免不可重复读
• SERIALIZABLE：避免幻读（性能最低）

4、SQL语言：

（1）设置隔离级别：

SET TRANSACTION ISOLATION LEVEL {四选一};

可在 set 后加  session / global  来表示是在本地还是全局的隔离模式。

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {四选一};

（2）查询隔离级别：

SELECT @@TRANSACTION_ISOLATION;

（3）开启事务：

START TRANSACTION;

（4）失败回滚：

ROLLBACK;

（5）提交事务：

COMMIT;

使用时的具体框架：

— 设置隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
— 执行操作…
COMMIT;