MySQL 库表操作学习笔记

SQL 语句基础

SQL 简介

SQL（结构化查询语言）是关系型数据库标准语言，可以改库结构、设置权限、增删改查数据、维护数据库。

SQL 四大分类

1. DDL 数据定义语言：操作库、表、索引对象。CREATE、DROP、ALTER、RENAME、TRUNCATE
2. DML 数据操作语言：操作表里记录。INSERT、DELETE、UPDATE、SELECT
3. DCL 数据控制语言：权限管理。GRANT、REVOKE
4. 事务控制：COMMIT提交、ROLLBACK回滚、SAVEPOINT保存点

SQL 书写规范

1. SQL 关键字不区分大小写，建议大写；字符串内容区分大小写
2. 语句以分号;结尾，可以单行 / 多行写；关键字不能换行拆分
3. 注释
   • 多行：/* */
   • 单行：--、#

数据库操作

MySQL 登录与退出

先启动服务：systemctl start mysqld，不启动会报 socket 连接错误

登录命令

mysql -u用户名 -h主机地址 -p密码 # 推荐写法（密码回车后输入更安全） mysql -uroot -p

• -h本地填localhost；-p和密码之间不要空格
• 直接命令行写密码会有安全警告

退出：exit/quit/\q

常用查看命令（登录 mysql 内部执行）

select now(); --查看当前时间 select version(); --查看mysql版本 select user(); --查看当前登录用户

查看数据库

show databases; --查看全部数据库，注意带s show databases like 's%'; --%匹配任意字符，_匹配单个字符

MySQL 自带 4 个系统库

系统库	说明
information_schema	数据字典，存库、表、列、权限元信息
performance_schema	服务器性能统计
mysql	存放账号、权限信息
sys	简化性能查询视图（5.7 新增）

创建数据库

create database 库名; show create database 库名\G; --查看建库语句，\G格式化垂直输出

• 库已存在直接创建会报错；关键字做库名要用反引号`包裹create database `database`;
• MySQL8.0 默认字符集utf8mb4，支持 emoji 表情；utf8mb4_0900_ai_ci：不区分重音、不区分大小写

切换数据库

select database(); --查看当前正在使用的库，没选库返回NULL use 库名; --切换数据库

删除数据库

drop database 库名;

数据库操作速记

create database 库名; --新建 use 库名; --切换 select database(); --看当前库 show databases; --列出所有库 drop database 库名; --删除

MySQL 字符集

1. 字符集：字符编码存储规则；字符序 (collation)：字符排序、比较规则
2. 版本差异：MySQL5.7 默认latin1；MySQL8.0 默认utf8mb4

⚠️ mysql 里面utf8实际是utf8mb3最多 3 字节，不能存 emoji，尽量不要用，统一使用 utf8mb4。

后缀含义

• ci：大小写不敏感；bin二进制；ai口音不敏感

查看命令

show character set; --查看全部字符集 show collation; --查看全部校对规则 show variables like 'character_set%'; --查看系统字符集变量

数据库对象

对象	作用
表	核心存储，行是记录，列是字段
约束	校验数据，保证完整性
数据字典	系统表，存元数据
视图	一张 / 多张表的逻辑展示
索引	提升查询速度

命名规范

1. 小写，下划线分隔；不能用保留关键字；字母开头
2. 生产规范

表类型	命名示例
普通表	t_模块_表名，例t_user_eduinfo
临时表	temp_xxx_日期
备份表	bak_xxx_日期

表的基本操作

创建表前必须use 数据库名，否则报错No database selected

常用数据类型

整型

类型	字节	有符号范围	无符号 unsigned
tinyint	1	-128~127	0‑255
smallint	2	-32768~32767	0‑65535
int	4	-21 亿～21 亿	0‑42 亿
bigint	8	超大	超大

int(10)只是显示宽度，不改变存储 4 字节，8.0.17 不推荐写法。

浮点与定点

类型	说明
float (4 字节)	存在精度丢失，存近似值
double (8 字节)	存在精度丢失，存近似值
decimal(M,D)	定点数：字符串存储，无精度丢失，财务金额必须使用 decimal

字符串

类型	特点	适用场景
char(n)	固定长度，不足补空格，最多 255	手机号、身份证
varchar(n)	可变长度，最多 255	普通短文本
text	大文本	文章、评论
blob	二进制字节	图片二进制（不推荐，文件存磁盘）
enum	枚举，只能选给定值	性别等有限选项
set	多选集合，可以存多个选项	标签多选

时间日期

类型	字节	格式
date	3	yyyy‑MM‑dd日期
time	3	HH:mm:ss时间
datetime	8	日期 + 时间
timestamp	4	时间戳日期时间，范围有限

创建表

create table 表名( 列名1 数据类型 [约束], 列名2 数据类型 [约束] );

查看表

show tables; --查看当前库所有表 show tables from 其他库名; --看别的库的表 desc 表名; --查看表字段结构 show create table 表名\G; --查看完整建表语句 show columns from 表名; --等价desc

desc 输出字段说明：Field字段名，Type类型，Null是否允许空，Key索引，Default默认值，Extra额外属性

删除表

drop table if exists 表名; --if exists防止表不存在时报错

修改表 ALTER TABLE

修改表名

alter table 旧表 rename 新表名; rename table 旧表 to 新表名;

新增列

alter table 表 add 列名 类型; alter table 表 add 列名 类型 after 某列; --加到某列后面 alter table 表 add 列名 类型 first; --放到第一列

删除列

alter table 表 drop 列名;

change：改列名 + 类型

alter table 表 change 旧列 新列 新类型;

modify：只修改列类型 / 约束，不改名字

alter table 表 modify 列名 新类型; alter table 表 modify 列名 新类型 after xx; --移动列位置

只复制表结构，不带数据

create table 新表 like 源表;

复制结构 + 全部数据

create table 新表 select * from 源表;

只复制数据到已有空表

insert into 新表 select * from 源表;

数据字典 information_schema

系统库information_schema保存全部元数据（描述数据的数据）

系统表	作用
tables	所有表信息
schemata	所有数据库信息
columns	所有字段信息

可以查询这几张系统表获取数据库运行元信息，用于运维统计。

表的约束

约束目的：保障数据完整性四类完整性：实体完整性、域完整性、引用完整性、自定义完整性。

约束分两种：

1. 列级约束：写在字段定义后面
2. 表级约束：所有字段写完之后再写约束，适合联合主键、外键

NOT NULL 非空约束

字段不能存 NULL；空字符串''和数字0不等于 NULL。

--建表时 name varchar(20) not null --后期修改 alter table 表 modify name varchar(20) not null; --删除非空约束 alter table 表 modify name varchar(20) null;

DEFAULT 默认值约束

插入不给值自动填充默认值

sex char(2) default '女' alter table 表 modify sex char(2) default '女';

COMMENT 字段注释

只做说明，不限制数据，在建表语句保存

name varchar(20) comment '用户姓名';

PRIMARY KEY 主键约束

• 特点：非空、不能重复；一张表最多 1 个主键；自动创建索引
• 复合主键：多个字段联合做主键，组合不能重复，单个可以重复

--列级 id int primary key --表级复合主键 primary key(ip,port) --后期添加主键 alter table 表 add primary key(id); --删除主键 alter table 表 drop primary key;

AUTO_INCREMENT 主键自增

• 必须是整数，必须是主键 / 唯一索引；一张表只能有一个自增列
• 不给该字段赋值自动取当前最大值 + 1；可以手动插入指定数值

id int primary key auto_increment alter table 表 modify id int auto_increment; --建表指定起始值 create table t(id int primary key auto_increment) auto_increment=100;

UNIQUE 唯一约束

字段不能重复，可以为 NULL；一张表可以多个唯一键。

主键：不能 null，只能 1 个；唯一键：允许 null，可以多个

name varchar(20) unique --删除唯一约束 alter table 表 drop index 约束名;

FOREIGN KEY 外键约束（引用完整性）

• 主表（父表）：被引用；从表（子表）：引用别人
• 外键写在从表；主表被引用字段必须是主键 / 唯一键；外键值要么是主表已存在值，要么 NULL。

foreign key(从表字段) references 主表(主表主键字段) --删除外键 alter table 表 drop foreign key 外键约束名;

CHECK 检查约束

限制字段取值范围，MySQL8.0.16 之后真正生效，之前写了不校验。

check(salary>0) alter table 表 add constraint 约束名 check(id>0); alter table 表 drop check 约束名;

约束删除速查表

约束	删除方式
NOT NULL / DEFAULT	modify 列 类型，去掉约束
PRIMARY KEY	drop primary key
UNIQUE	drop index 约束名
FOREIGN KEY	drop foreign key 约束名
CHECK	drop check 约束名

存储引擎

存储引擎是 MySQL 底层读写数据的模块。

引擎	特点
InnoDB（MySQL8 默认）	支持事务、行锁、外键；适合业务系统
MyISAM	查询插入快，不支持事务、外键
MEMORY	全部放内存，重启数据丢失，速度极快

查看引擎：

show engines\G

建表指定引擎与字符集

create table 表(...) engine=InnoDB default charset=utf8mb4;

MySQL 账户及授权

密码策略

1. MySQL8.0 默认认证插件caching_sha2_password；5.7 是mysql_native_password
2. 密码强度插件component_validate_password
   • LOW仅校验长度；MEDIUM长度 + 数字大小写特殊字符；STRONG再加字典文件
3. 修改全局密码策略属于临时生效；永久生效修改/etc/my.cnf配置文件。

用户管理（MySQL8：先创建用户，再授权）

用户格式'用户名'@'登录主机'

• localhost仅本地登录；%代表允许任意远程 IP 登录

--创建用户 create user 'test'@'localhost' identified by '123456'; --修改密码 alter user 'test'@'localhost' identified by '新密码'; --删除用户 drop user 'test'@'localhost';

用户信息保存在系统库mysql.user表。

授权 GRANT

grant 权限列表 on 库.表 to '用户'@'主机'; -- *.* 代表所有库所有表；mydb.*代表mydb库全部表

常用权限：select insert update delete create drop；all全部权限，不包含给别人授权的权限。

• with grant option：允许该用户给其它账号授权。

查看权限

show grants for 'test'@'localhost';

回收权限 REVOKE

revoke 权限列表 on 库.表 from '用户'@'主机'; revoke all on *.* from 'test'@'%'; --回收全部权限

权限最佳实践

1. root 账号禁止使用%任意远程访问
2. 账号权限最小化，业务账号尽量只给 select 查询
3. 业务账号、管理员账号分开
4. 定期清理废弃账号