MySQL 基础学习

SQL（Structured Query Language，结构化查询语言）是一种用来定义、查询和管理关系型数据库的语言。它的核心目标是：用接近自然语言的方式，对表格数据进行“取、增、改、删”和结构管理。

一、 基础概述

• 关系型数据库（RDBMS）： 建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
• MySQL的架构层次：
  1. 客户端 连接到 **MySQL数据库管理系统 (DBMS)**。
  2. 一个 DBMS 中可以维护管理多个 **数据库 (Database)**。
  3. 一个数据库中可以包含多张 **表 (Table)**。
  4. 一张表中包含多行 记录 (Row/Record) 和 多列 **字段 (Column/Field)**。

---

二、 SQL 通用语法及分类

1. 通用语法规范

• SQL 语句可以单行或多行书写，以分号 ; 结尾。
• SQL 语句可以使用空格/缩进来增强语句的可读性。
• MySQL 数据库的 SQL 语句不区分大小写（但建议关键字用大写，自定义名称用小写）。
• 注释方式：
  • 单行注释：-- 注释内容 或 # 注释内容
  • 多行注释：/* 注释内容 */

2. SQL 语言分类

SQL 语言按功能主要分为四大类：

• DDL (Data Definition Language): 数据定义语言，用来定义数据库对象（数据库、表、字段）。
• DML (Data Manipulation Language): 数据操作语言，用来对表中的数据进行增删改。
• DQL (Data Query Language): 数据查询语言，用来查询表中的记录。
• DCL (Data Control Language): 数据控制语言，用来创建数据库用户、控制数据库的访问权限。

---

三、数据库数据类型

MySQL 中的数据类型主要分为三大类：数值类型、字符串类型、日期时间类型。

1.数值类型

主要用于存储整数和小数。在定义整数时，经常会用到一个关键字 UNSIGNED（无符号），表示该数值不能为负数，从而使正数的取值范围扩大一倍。

类型	大小(字节)	有符号范围 (Signed)	无符号范围 (Unsigned)	常见使用场景
TINYINT	1 byte	-128 ~ 127	0 ~ 255	年龄(age)、状态码(status 0/1)
SMALLINT	2 bytes	-32768 ~ 32767	0 ~ 65535	较小的数值
MEDIUMINT	3 bytes	-8388608 ~ 8388607	0 ~ 16777215	中等数值
INT / INTEGER	4 bytes	-21亿 ~ 21亿	0 ~ 42亿	绝大多数的整数、ID主键
BIGINT	8 bytes	很大…	很大…	超大数据的ID
FLOAT	4 bytes	-	-	单精度浮点数 (易产生精度丢失)
DOUBLE	8 bytes	-	-	双精度浮点数
DECIMAL	依赖精度	-	-	涉及金额、价格等需要高精度的数字

2.字符串类型

用于存储文本数据，最常用的是 CHAR 和 VARCHAR。

类型	大小限制	描述	常见使用场景
CHAR(n)	0 ~ 255 bytes	定长字符串。指定长度后，即使存的数据不够长，也会用空格补齐。	长度固定的数据：性别(男/女)、手机号(11位)、身份证号(18位)
VARCHAR(n)	0 ~ 65535 bytes	变长字符串。根据实际存储的数据长度动态分配空间。	长度不固定的数据：用户名(username)、密码、家庭住址
TINYTEXT	0 ~ 255 bytes	短文本字符串	简短的备注信息
TEXT	0 ~ 65535 bytes	长文本数据	文章内容、商品详情描述
LONGTEXT	0 ~ 4GB	超大文本数据	极长的文本

3.日期时间类型

用于存储时间和日期信息。

类型	大小(字节)	格式	描述	常见使用场景
DATE	3	YYYY-MM-DD	日期值（只有年月日）	员工入职日期、生日
TIME	3	HH:MM:SS	时间值或持续时间	打卡时间（时分秒）
YEAR	1	YYYY	年份值	出生年份
DATETIME	8	YYYY-MM-DD HH:MM:SS	日期和时间（混合）	订单创建时间、文章发布时间
TIMESTAMP	4	YYYY-MM-DD HH:MM:SS	时间戳（受时区影响，最大到2038年）	记录修改时间

四、数据库和数据表的基本操作

1.数据库的基本操作

• 查询

  • 查询所有数据库

    SHOW DATABASES;

  • 查询当前数据库

    SELECT DATABASE();

• 创建

  CREATE DATABASE[IF NOT EXISTS ]数据库名[DEFAULT CHARSET 字符集]

• 删除

  DROP DATABASE[IF EXISTS ]数据库名	

• 使用

  USE 数据库名

2.数据表的基本操作

2.1 创建数据表

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

假设创建一张员工表（employee），包含编号、姓名、性别、入职时间。

CREATE TABLE employee (    
    id INT COMMENT '编号',    
    work_no VARCHAR(10) COMMENT '工号',    
    name VARCHAR(50) COMMENT '姓名',    
    gender CHAR(1) COMMENT '性别',    
    age TINYINT UNSIGNED COMMENT '年龄',    
    entry_date DATE COMMENT '入职时间'
) COMMENT '员工表';

2.2 查询数据表

• 查询当前数据库所有表

  SHOW TABLES;

• 查询表结构

  DESC 表名;

• 查询指定表的建表语句

  SHOW CREATE TABLE 表名;

2.3 修改数据表

• 添加字段

  ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释];

• 修改

  • 修改数据类型

    ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;

  • 修改字段名和数据类型

    ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度）[COMMENT 注释];

  • 修改表名

    ALTER TABLE 表名 RENAME TO 新表名;

2.4 删除数据表

• 删除字段

  ALTER TABLE 表名 DROP 字段名;

• 删除表

  DROP TABLE [IF EXISTS] 表名;

• 删除指定表，并重新创建该表

  TRUNCATE TABLE 表名

五、插入数据

1. 给指定字段添加数据

INSERT INTO 表名 （字段1，字段2，...） VALUES（值1，值2，...）;

2. 给全部字段添加数据

INSERT INTO 表名 VALUES （值1，值2，...）;

3. 批量添加数据

INSERT INTO 表名（字段1，字段2，...） VALUES （值1，值2，...）（值1，值2，...）（...）;
INSERT INTO 表名 VALUES （值1，值2，...）（值1，值2，...）

• 插入数据，保证字段与值的顺序一一对应
• 字符串与日期数据要包含在引号内

六、修改数据

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

• 如果没有 WHERE 条件，修改的是整张表的数据

七、删除数据

DELETE FROM 表名 [WHERE 条件]

• 如果没有 WHERE 条件，删除的是整张表的所有数据
• 不能删除某一个字段的值，可以使用 UPDATE 设置为 NULL

八、查询数据

1. 基本查询

1.1 查询指定字段

SELECT 字段1，字段2，... FROM 表名;

1.2 查询所有字段

SELECT * FROM 表名;

1.3 设置别名

SELECT 字段1[AS 别名1],字段2[AS 别名2],... FROM 表名;

• AS 可省略

1.4 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

2. 条件查询

2.1 语法

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

2.2 条件

逻辑运算符	功能
AND 或 &&	并且（多条件同时成立）
OR 或 ||	或者 （多条件任意一个成立）
NOT 或 ！	非

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN … AND …	在某个范围之内
IN(…)	在in之后的列表中的值多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL	是 NULL

3. 聚合函数

一列数据为整体进行纵向计算

3.1 语法

SELECT 聚合函数（字段列表） FROM 表名;

3.2 常见聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

• null 值不参与聚合函数运算

4. 分组查询

4.1 语法

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

• 执行顺序：where > 聚合函数 > having
• 分组后，查询的字段一般为聚合函数和分组字段

5. 排序查询

5.1 语法

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

5.2 排序方式

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

6. 分页查询

6.1 语法

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

• 起始索引从 0 开始，起始索引=（查询页码-1） 每页显示记录数*
• 如果查询的是第一页，起始索引可以省略

九、管理

1.管理用户

1.1查询用户

USE mysql;
SELECT * FROM user;

1.2 创建用户

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

1.3 修改用户密码

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

1.4 删除用户

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

2. 权限控制

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	更新数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据表/库/视图
CREATE	创建数据表/视图

2.1 查询权限

SHOW GRANTS FOR '用户名'@'主机名';

2.2 授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

2.3 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

十、函数

1. 字符串函数

常见函数：

函数	功能
CONCAT（s1,s2…）	字符串拼接
LOWER（str）	将字符串全部转为小写
UPPER（str）	将字符串全部转为大写
LPAD（str,n,pad）	左填充，用字符串pad对str左侧进行填充达到n个字符串长度
RPAD（str,n,pad）	右填充，用字符串pad对str右侧进行填充达到n个字符串长度
TRIM（str）	去掉字符串头部和尾部的空格
SUBSTRING（str,start,len）	取字符串str从start开始位置起的第len个长度的字符串

2. 数值函数

常见函数：

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	取模运算(余数) x/y
RAND()	返回0-1间的随机数
ROUND(x,y)	返回x四舍五入的值保留y位小数

3. 日期函数

常见函数：

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date,INTERVAL exper type)	返回一个date加上一个时间间隔exper类型为type的时间值
DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2间的天数（前者减后者）

4. 流程函数

常见函数：

函数	功能
IF(value,t,f)	如果value为true，返回t，否则返回f
IFNULL(value1,value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] … ELSE [default] END	如果val1为true，返回res1，否则返回default默认值
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END	如果val1的返回值等于expr，返回res1，否则返回default默认值

十一、约束

1. 概述

作用于表中字段的一种规则，限制存储在表中的数据

2. 分类

约束	关键字	描述
非空约束	NOT NULL	限制该字段的数据不能为null
唯一约束	UNIQUE	保证该字段的唯一性
主键约束	PRIMARY KEY	主键是一行数据的唯一标识，非空且唯一
默认约束	DEFAULT	在保存数据时，未指定，则采用默认值
检查约束	CHECK	保证字段值满足于某个条件
外键约束	FOREIGN KEY	让两张表数据之间建立链接，保证数据一致性和完整性

3. 外键约束

3.1 添加外键

1.
CREATE TABLE 表名 (
	字段名 数据类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主列表名)
);

2.
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主列表名);

3.2 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3.3 删除/更新 行为

行为	说明
NO ACTION	当在父表中删除或更新对应记录时，先检查记录是否有对应外键，如果有则不允许
RESTRICT	当在父表中删除或更新对应记录时，先检查记录是否有对应外键，如果有则不允许
CASCADE	当在父表中删除或更新对应记录时，先检查记录是否有对应外键，如果有则也删除或更新外键在子表中的记录
SET NULL	在父表中删除对应记录时，先检查记录是否有对应外键，如果有则设置子表中该外键值为null
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值

ALTER TABLE 表名 ADD CONSTRINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表名字段) ON UPDATE 行为 ON DELETE 行为

十二、多表查询

将多张表的数据组合在一起提取出来

如果没有连接条件，两张表的数据会全排列组合（表A有M行，表B有N行，结果有 M*N 行）。

注意：一定要通过 WHERE 或 ON 消除无效的笛卡尔积。

1. 表与表之间的三种基本关系

1.1 一对多

• 概念：表A中的一条记录可以对应表B中的多条记录，但表B中的一条记录只能对应表A中的一条记录。
• 举例：部门（Department）与员工（Employee）。一个部门有多个员工，但一个员工只属于一个部门。
• 实现方式：在“多”的一方（员工表）建立外键，指向“一”的一方（部门表）的主键。

1.2 多对多

• 概念：表A中的一条记录可以对应表B中的多条记录，同时表B中的一条记录也可以对应表A中的多条记录。
• 举例：学生（Student）与课程（Course）。一个学生可以选修多门课程，一门课程也可以被多个学生选修。
• 实现方式：必须引入第三张表（中间表/关联表）。中间表至少包含两个外键，分别指向另外两张表的主键。

1.3 一对一

• 概念：表A中的一条记录只能对应表B中的一条记录，反之亦然。
• 举例：用户基本信息表（User）与用户详情表（User_Profile）。
• 实现方式：
  • 方式一：在任意一方加入外键指向另一方的主键，并将该外键设置为唯一约束（UNIQUE）。
  • 方式二：让两张表共享相同的主键（表B的主键既是主键，也是指向表A的外键）。

2. 内连接

返回两张表中完全匹配的记录（相当于集合的交集）

• 隐式内连接：

  SELECT * FROM 表1,表2 WHERE 条件...;

• 显式内连接：

  SELECT * FROM 表1 [INNER] JOIN 表2 ON 连接条件; 

3. 外连接

外连接会保留某一张表的全部记录，即使另一张表没有匹配项 （包含交集部分数据）

• 左外连接：

  SELECT * FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

• 右外连接：

  SELECT * FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

4. 自连接

同一张表自己与自己连接（可以内连接也可以外连接）

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

举例：查询员工及其直属领导的名字（都在employee表中，manager_id指向id）。

5. 联合查询

将多次查询（多条 SELECT 语句）的结果集合并成一个结果集

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

• UNION：合并结果并去除重复行。

• UNION ALL：合并结果，保留重复行

• 合并的各个查询必须具有相同的列数，且对应列的数据类型应兼容

6. 子查询

一个查询语句嵌套在另一个查询语句内部。

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

• 外部语句可以是 INSERT/UPDATE/DELETE/SELECT 任意一个

6.1 标量子查询

子查询返回的结果是单个值（数字，字符串，日期等）

常用操作符： = > < >= <=

6.2 列子查询

子查询返回的结果是一列（可以是多行）

常用操作符：

操作符	描述
IN	在指定的集合范围之内多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	子查询返回列表中，有任意一个满足即可
ALL	子查询返回列表的所有值都必须满足

6.3 行子查询

子查询返回的结果是一行（可以是多列）

常用操作符：=、 <>、 IN、 NOT IN

6.4 表子查询

子查询返回的结果是多行多列

常用操作符：IN

练习：

create database practice;
use practice;

-- 部门表
CREATE TABLE dept (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL COMMENT '部门名称',
    location VARCHAR(50) COMMENT '工作地点'
);
-- 员工表
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    job VARCHAR(50) COMMENT '职位',
    salary DECIMAL(10,2) COMMENT '薪资',
    entrydate DATE COMMENT '入职日期',
    manager_id INT COMMENT '直属领导ID (自关联)',
    dept_id INT COMMENT '所属部门ID'
);


-- 插入部门
INSERT INTO dept (id, name, location) VALUES
(1, '研发部', '北京'), (2, '销售部', '上海'), (3, '财务部', '广州'), (4, '后勤部', '北京');
-- 注意：后勤部目前没有员工
-- 插入员工 (注意：张三丰没有领导，周芷若没有部门)
INSERT INTO emp (id, name, age, job, salary, entrydate, manager_id, dept_id) VALUES
(1, '张三丰', 65, '总裁', 50000, '2020-01-01', NULL, 1),
(2, '张无忌', 28, '研发总监', 30000, '2020-05-15', 1, 1),
(3, '杨过', 26, '开发工程师', 18000, '2021-03-10', 2, 1),
(4, '小龙女', 25, '测试工程师', 15000, '2021-04-12', 2, 1),
(5, '赵敏', 27, '销售总监', 28000, '2020-06-01', 1, 2),
(6, '周芷若', 26, '实习生', 5000, '2023-09-01', 5, NULL),
(7, '黄蓉', 30, '财务总监', 25000, '2020-07-01', 1, 3);


# 阶段一：基础连接查询（内连接、外连接）
# 【内连接】 查询所有有分配部门的员工姓名、职位以及对应的部门名称。
    select e.name,e.job,d.name from emp e,dept d where e.dept_id=d.id;
# 【左外连接】 查询所有员工的姓名、薪资和所属部门名称（要求：即使该员工没有分配部门，也要查出来，如实习生周芷若）。
    select e.name,e.salary,d.name from emp e left join dept d on e.dept_id = d.id;
# 【右外连接】 查询所有部门的名称以及该部门下的员工姓名（要求：即使该部门没有任何员工，也要把部门查出来，如后勤部）。
    select d.name,e.name from emp e right join dept d on e.dept_id = d.id;
# 阶段二：高级连接（重点：自连接、联合查询）
# 【自连接 - 核心】 查询每个员工的姓名，以及他们直属领导的姓名。
    select a.name,b.name from emp a join emp b on a.manager_id=b.id;
# 【自连接 + 外连接】 查询所有员工的姓名及其直属领导的姓名（要求：即使像“张三丰”这样没有领导的总裁，也要显示出来，领导名显示为NULL）。
    select e1.name,e2.name from emp e1 left join emp e2 on e1.manager_id=e2.id;
# 【联合查询 UNION】 财务部需要一份名单，要求列出薪资大于 25000 的员工，或者年龄大于 30 岁的员工（使用 UNION 实现，去掉重复的人员）。
    select name from emp where salary>25000
    union
    select name from emp where age>30;
# 阶段三：子查询大集合（标量、列、行、表子查询）
# 【标量子查询】 查询和“杨过”在同一个部门的其他员工姓名。
    -- 1.select dept_id from emp where name='杨过' ;
    -- 2.select name from emp where dept_id=1;
    select name from emp where dept_id=(select dept_id from emp where name='杨过') ;

# 【标量子查询】 查询薪资高于公司平均薪资的员工姓名及薪资。
    -- select avg(emp.salary) from emp;
    select name,salary from emp where salary>(select avg(emp.salary) from emp);

# 【列子查询】 查询在“北京”或“上海”工作的员工姓名和职位。
    -- select id from dept where location in('北京','上海');
    select name,job from emp where dept_id in (select id from dept where location in('北京','上海'));

# 【列子查询】 查询比“研发部”所有员工薪资都低的员工姓名和薪资。
    -- select salary from emp where dept_id=(select id from dept where name='研发部');
    select name,salary from emp where salary<all(select salary from emp where dept_id=(select id from dept where name='研发部'));

# 【行子查询】 查询与“杨过”的直属领导相同，并且职位也相同的员工姓名。
    select name from emp where(manager_id,job)=(select manager_id,job from emp where name='杨过') and name!='杨过';
# 【表子查询】 查询入职日期在 '2021-01-01' 之后的员工信息，并将这些员工信息作为一张临时表，再与部门表进行内连接，查出他们的姓名和部门名称。
    -- select name from emp where entrydate>'2021-01-01';
    select tmp.name,dept.name from (select * from emp where entrydate>'2021-01-01') tmp join dept on tmp.dept_id=dept.id;