MySQL 基础入门指南

2.1 库的操作

1. 创建数据库

• 语法： create database db_name;
• 功能： 本质就是在 /var/lib/mysql 下创建一个目录。
• 安全创建： create database if not exists db_name;（如果创建的数据库存在，会抛出错误警告，不会导致已存在的数据库被覆盖）。
• 指定字符集： create database d1 charset=utf8mb4 collate utf8mb4_general_ci;

字符集（Character Set）：决定了数据库可以存储哪些字符。校验集（Collation）：定义了字符的比较和排序规则。

2. 查看数据库

• 查看所有库： show databases;
• 查看建表语句： show create database db_name;
• 查看当前连接： show processlist;

3. 删除数据库

• 语法： drop database db_name;
• 功能： 本质就是在 /var/lib/mysql 下删除一个目录，级联删除，里面的数据表全部被删。

4. 修改数据库

• 语法： alter database d1 charset=gbk collate gbk_chinese_ci;
• 功能： 修改数据库 d1 的字符集和校验集。

5. 切换当前工作数据库

• 语法： use db_name;
• 补充： select database(); 查看当前工作数据库。

6. 数据库的备份与还原

• 备份指令： mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径
• 示例： mysqldump -P3306 -uroot -p -B database1 > mytest.sql
• 还原语句： source 路径
• 示例： source /root/MySQL/mytest.sql

2.2 表的操作

1. 创建表

• 语法：

create table table_name (
    field1 datatype,
    field2 datatype,
    field3 datatype
) character set=字符集 collate=校验规则 engine=存储引擎;

• 示例：

create table users(
    id int,
    name varchar(20) comment '用户名',
    password char(32) comment '密码',
    birthday date comment '生日'
) character set=utf8mb4 collate=utf8mb4_general_ci;

2. 查看表结构

• 查看当前库中的表： show tables;
• 查看详细表结构： desc 表名;
• 查看建表语句： show create table 表名 \G;

3. 修改表

• 新增字段： alter table 表名 add 字段 字段类型 comment '备注' after 位于哪个字段后面;
• 修改字段类型： alter table 表名 modify 字段 要修改的字段类型 comment '要修改的备注';
• 删除字段： alter table 表名 drop 要删除的字段;
• 修改表名： alter table 表名 rename to 新表名;
• 重命名字段： alter table 表名 change 旧字段名 新字段名 字段类型 [其他属性];

4. 删除表

• 语法： drop table 表名;
• 功能： 删除指定的表，table 后跟 if exists 可以避免删除表不存在的报错。

2.3 表数据的插入

1. 插入数据

• 基本插入： insert into 表名 values(值 1), (值 2);
• 插入否则更新： insert into 表名 (字段,...) values (数据,...) on duplicate key update 字段=更新数据,...;
• 替换插入： replace into stu (字段,...) values (字段,...);

2. 插入查询结果

• 语法： insert into [要插入的表名] select [查询语句];
• 案例： 表去重操作。先创建空表结构和原表一样，将去重数据插入到新表，然后重命名表实现原子去重。

2.4 基本查询

1. Select

• 全列查询： select * from 表名;
• 指定列查询： select 字段,... from 表名;
• 表达式查询： select 字段,... 表达式 as 重命名 from 表名;
• 去重： select distinct 字段,... from 表名;

2. Where 条件子句

• 比较运算符： =, <>, >, >=, <, <=
• 逻辑运算符： AND, OR, NOT
• 范围查询： BETWEEN ... AND ...
• 集合查询： IN (...)
• 模糊查询： LIKE '孙%' (匹配多个), LIKE '孙_' (严格匹配一个)
• NULL 判断： IS NULL, IS NOT NULL (不能用 = null)

3. Order By 结果排序

• 语法： select 字段,... from 表名 order by 要排序的字段 [asc/desc];
• 说明： asc 为升序（从小到大），desc 为降序（从大到小），默认不写为 asc。

4. Limit 筛选分页结果

• 语法： limit n (取前 n 条), limit s, n (从第 s+1 条开始取 n 条), limit n offset s (语义更清晰)。

2.5 表数据的更新

1. Update 数据更新

• 语法： update [要更新的表名] set [更新的字段] = [新数据] (where/order/limit 查询结果);
• 注意： 若不跟 where 等条件子句，会默认对整张表的指定字段执行全表更新。

2.6 表数据删除

1. Delete 删除数据

• 语法： delete from 表名 (where/order/limit 查询语句);
• 注意： 不跟查询子句默认就是删除整张表的数据。

2. Truncate 截断表

• 语法： truncate 表名;
• 特点： 只能对整表操作，比 delete 更快，但无法回滚，会重置 auto_increment 项。

2.7 聚合函数

• Count: count(*), count(字段)
• Sum: sum(字段)
• Avg: avg(字段)
• Max/Min: max(字段), min(字段)

三、多种查询方法

1. 分组查询 Group By

• 概念： 对查询结果按指定字段进行分组聚合，必须配合聚合函数使用。
• Having vs Where： where 是对具体的任意列做条件筛选，having 是对分组聚合之后的结果进行条件筛选。where 必须在 group by 之前，having 必须在 group by 之后。

2. 单表查询练习

• 多条件组合： (sal>500 or job='manager') and ename like 'j%'
• 多字段排序： order by deptno asc, sal desc
• 子查询： select ename, job from emp where sal = (select max(sal) from emp);

3. 多表查询

（1）笛卡尔积

• 直接查两张表会出现穷举组合。行数 = 表 A 总行数 × 表 B 总行数。
• 区分不同表字段：emp.ename。

（2）多表查询案例

• 内连接： select emp.ename, dept.dname from emp, dept where emp.deptno = dept.deptno;

4. 自连接

• 同一张表连接查询，需重命名自身表。
• 示例： 查找员工 ford 的上级领导编号和姓名。

5. 子查询

• 概念： 将一个 SELECT 查询嵌套在另一个 SQL 语句的 WHERE/FROM/HAVING 等子句中。
• 单行子查询： 返回 1 行结果，可用 =。
• 多行子查询： 返回≥2 行结果，需用 IN/ALL/ANY。
• 多列子查询： 返回多个列数据。
• FROM 子句中使用： 把子查询当作临时表使用。

6. 合并查询

• Union： 取得两个结果集的并集，自动去掉重复行。
• Union All： 取得两个结果集的并集，不去掉重复行。

7. 表的内连和外连

（1）内连接

• 只保留多张表中匹配关联条件的记录。
• 语法： select 字段 from 表 1 inner join 表 2 on 连接条件;

（2）外连接

• 左外连接 (Left Join)： 保留左表所有记录，右表不匹配的填充 NULL。
• 右外连接 (Right Join)： 保留右表所有记录，左表不匹配的填充 NULL。

四、理解数据库

1. 为什么要有数据库？

文件保存数据的缺点：安全性问题、不利于查询和管理、不利于海量数据存储、程序控制不方便。数据库能更有效地管理数据。

2. 服务器、数据库、表之间的关系

数据库服务器管理多个数据库，每个应用通常创建一个数据库，数据库中创建多个表保存实体数据。

3. 数据类型

（1）数值类型

• bit(m): 位类型。
• tinyint/smallint/int/bigint: 整数类型，分有符号和无符号。
• float/double: 浮点数。
• decimal: 定点数，精确存储。

（2）文本、二进制类型

• char/varchar: 字符串。
• blob/text: 二进制/大文本。

（3）时间日期

• date/datetime/timestamp: 日期和时间类型。

（4）String 类型

• enum/set: 枚举和集合类型。

4. 表的约束

约束的最终目标：保证数据的完整性和可预期性。

（1）空属性

• null/not null: 约束该字段能否为空。

（2）默认值

• default: 指定默认值。

（3）列描述

• comment: 字段的备注说明。

（4）zerofill

• 数值左侧补 0，自动添加 unsigned 属性。

（5）主键

• primary key: 唯一且不能为空，自带唯一索引。
• 复合主键: 多个字段联合组成主键。

（6）自增长

• auto_increment: 字段值自动递增，通常和主键搭配。

（7）唯一键

• unique key: 保证字段值唯一，一张表可以有多个，可以为空。

（8）外键

• foreign key: 定义主表和从表之间的关系，要求外键列数据必须在主表的主键列存在或为 null。

5. 内置函数

（1）日期函数

• current_date(), current_time(), date_add(), datediff()。

（2）字符串函数

• concat(), left(), right()。

（3）数学函数

• abs(), ceiling(), floor(), rand()。

（4）其他函数

• user(), md5(), database(), sha2(), ifnull()。

6. 索引

6.1 概念和原理

• 作用： 提升检索性能，空间换时间。
• 底层结构： B + 树。
• InnoDB 聚簇索引： 叶子节点存完整行数据。
• 辅助索引： 叶子节点存索引列值 + 主键值，查询需回表。

6.2 索引操作

• 创建： create index, add primary key, add unique, add index。
• 查询： show keys from 表名, show index from 表名, desc 表名。
• 删除： drop index, drop primary key。

6.3 索引创建原则

• 频繁作为查询条件的字段应该创建索引。
• 唯一性太差的字段不适合单独创建索引。
• 更新非常频繁的字段不适合作创建索引。
• 不会出现在 where 子句中的字段不该创建索引。

7. 事务

7.1 事务的概念

• ACID 特性： 原子性 (Atomicity)、一致性 (Consistency)、隔离性 (Isolation)、持久性 (Durability)。

7.2 事务的版本支持

• 只有使用了 InnoDB 数据库引擎的数据库或表才支持事务，MyISAM 不支持。

7.3 事务的提交方式

• 自动提交： 每条 DML 语句自动构成独立事务并即时提交。
• 手动提交： 需显式执行 commit 才会持久化事务修改。

7.4 事务的基本操作

• 开启： begin 或 start transaction。
• 保存点： savepoint 保存点名。
• 回滚： rollback to 保存点 或 rollback。
• 提交： commit。

7.5 事务隔离级别

• 读未提交 (Read Uncommitted)
• 读提交 (Read Committed)
• 可重复读 (Repeatable Read)：MySQL 默认隔离级别。
• 串行化 (Serializable)

8. 视图

• 概念： 虚拟表，内容由查询定义。
• 创建： create view 视图名 as select 语句;
• 删除： drop view 视图名;

9. 用户管理

9.1 用户信息

• 存储在系统数据库 mysql 的 user 表中。

9.2 创建用户

• 语法： create user '用户名'@'登陆主机/ip' identified by '密码';

9.3 删除用户

• 语法： drop user '用户名'@'主机名';

9.4 修改用户密码

• 语法： set password for '用户名'@'主机名' = '新密码'; 或 alter user '用户名'@'主机名' identified by '新密码';

9.5 给用户授权

• 语法： grant 权限列表 on 库。对象名 to '用户名'@'登陆位置';
• 回收权限： revoke 权限列表 on 库。对象名 from '用户名'@'登陆位置';