zhongts 的个人博客
# 登录
mysql -uroot -p
# 登录并指定库
mysql -uroot -p 库名
# 远程登陆数据库
mysql -h192.168.33.6 -P3306 -uroot -p
# 退出
exit; 或 quit;
-- 查看所有库
SHOW DATABASES;
-- 进入库
USE 库名;
-- 查看库中表
SHOW TABLES;
-- 查看表结构
DESC 表名;
-- 查看创建表语句
SHOW CREATE TABLE 表名;
-- 创建用户
CREATE USER 'user'@'%' IDENTIFIED BY 'password';
-- 授权所有库所有权限
GRANT ALL ON *.* TO 'user'@'%';
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看当前用户
SELECT USER();
-- 查看某用户权限
SHOW GRANTS FOR 'user'@'%';
-- 查询用户（系统表）
SELECT * FROM mysql.user WHERE user='用户名'\G;
-- 查询所有用户（系统表）
SELECT * FROM mysql.user;
%：允许任何 IP 连接
localhost：只允许本机
-- 最常用
SELECT -- 查询
INSERT -- 新增
UPDATE -- 修改
DELETE -- 删除
CREATE -- 创建库/表
DROP -- 删除库/表
ALTER -- 修改表结构（加字段、改类型）
REPLICATION SLAVE -- 主从复制，从库用来连接主库、拉取 binlog 的核心权限
REPLICATION CLIENT -- 主从复制，用于查询主从状态：SHOW MASTER STATUS、SHOW SLAVE STATUS
-- 管理类
INDEX -- 创建/删除索引
GRANT OPTION -- 允许把自己的权限授权给别人
PROCESS -- 查看数据库进程
RELOAD -- 刷新权限
SHUTDOWN -- 关闭MySQL（仅root用）
-- 事务/存储过程
EXECUTE -- 执行存储过程
TRIGGER -- 触发器
# 备份单个库
mysqldump -uroot -p 库名 > backup.sql
# 备份所有库
mysqldump -uroot -p --all-databases > all.sql
# 恢复（先登录MySQL）
source /path/backup.sql;
-- 查看所有连接
SHOW PROCESSLIST;
-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 查看慢查询是否开启
SHOW VARIABLES LIKE '%slow_query_log%';
-- 查看慢查询时间阈值（默认10秒）
SHOW VARIABLES LIKE 'long_query_time';
bind-address = 0.0.0.0order by / group by / join 没优化C --> Create --> insert --> 插入
R --> Read --> select --> 查询
U --> Update -->update --> 更新
D --> Delete --> delete --> 删除
insert into 库.表 (字段1, 字段2, 字段3) values (值1, 值2,值3);
insert into 库.表 (字段1, 字段2, 字段3) values (值1, 值2,值3),(值1, 值2,值3);
insert into 库.表1 (字段1,字段2,字段3) values select 字段1,字段2,字段3 from 库.表2 where 条件;
insert into 库.表1 (字段1,字段2,字段3) values select 字段1,字段2,字段3 from 库.表2; # 不跟条件会插入全部
insert ignore into 库.表 (字段1, 字段2) values (值1, 值2);
UPDATE 必须加 WHERE！不加 WHERE 会更新整张表所有数据，生产直接事故。
执行 UPDATE 前，先用 SELECT 查一遍要改哪些行
select \* from user id = 100;
update 表 set 字段 = 值 where 条件;
update user set name = 'zhangsan' where id = 5;
update 表 set 字段1 = 值1, 字段2 = 值2, 字段3 = 值3 where 条件;
update user set name = 'shangsan', gender = 'man', age = 18 where id = 5;
update 表 set 字段 = 字段 + 1 where 条件;
update user set age = age + 1 where name = 'zhangsan';
update user set age = age * 2 where name = 'zhangsan';
update 表 set 字段 = 值 where 条件1 and 条件2;
update user set age = 18 where city = '成都' and age < 18;
select * from 表;
select 字段1,字段2 from 表;
select 字段1,字段2 from 表 where 条件;
select 字段1,字段2 from 表 where 条件1 and 条件2;
= 等于
select name from user where age = 18;
!= 或 <> 不等于
select name from user where age != 18;
>, <, >=, <=
select name from user where age < 18; # 小于18
select name from user where age <= 18; # 小等18
selcet name from user where age > 18; # 大于18
select name from user where age >= 18; # 大等18
AND / OR / NOT
select name from user where age = 18 and city = '成都';
select name from user where age = 18 or age = 20;
select name from user where not age = 18; # 等价于 age != 18
BETWEEN 10 AND 20
select name from user where age between 18 and 23 and gender = 'gail';
IN (1,2,3)
select name, age from user where age in (18,20,21,23); # IN 适合匹配离散的多个值
LIKE '%张%' 模糊匹配
select name from user where name like '%张%'; # % 代表任意长度（包括 0）的任意字符
select name from user where name like '张_'; # _ 代表单个任意字符，比如 name like '张_' 匹配 “张三”“张四”，不匹配 “张三丰”
select distinct 字段 from 表;
select distinct city from user;
select 字段 from 表 oder by 字段 desc; # desc降序
select 字段 from 表 oder by 字段 asc; # desc降序
select name,age from user oder by age desc;
select name,age from user oder by age; # 默认asc
select * from 表 limit 10; # 前10条
select * from 表 limit 5, 10; # 从第5条开始取，取10条
select * from user where limit 10;
select * from user where limit 3, 3;
select 字段名 as 别名 from 表;
select 字段名1 as 别名1, 字段名2 as 别名2 from 表;
select name as 姓名, age as 年龄 from user;
select name 姓名, age 年龄 from user; # 省略as
SELECT
COUNT(*) AS 总条数,
SUM(age) AS 年龄总和,
AVG(age) AS 平均年龄,
MAX(age) AS 最大年龄,
MIN(age) AS 最小年龄
FROM user;
select 字段 from 表 group by 字段;
select 字段 from 表 group by 字段 having 条件;
select city from user group by city;
select city from user where city != '成都' group by city;
select city as 城市, count(*) from user group by city having count(*) > 10; # 聚合条件需要先分组再过滤
select city as 城市, count(*) as 男性人数 from user where city != '成都' and gender = '男' group by city; ## 非聚合条件用where以提高性能
内连接
select * from `user` inner join `data` on `user`.id = `data`.id;
select u.name, d.grades from `user` u inner join `data` d on u.id = d.id;
select u.name, d.grades from `user` u inner join `data` d on u.id = d.id where u.city != '成都';
左连接
select * from `user` u left join `data` d on u.id = d.id; # 返回左user表的全部内容
右连接
select * from `user` u right join `data` d on u.id = d.id; # 返回右data表的全部内容
把一个 SELECT 查询的结果，作为另一个查询的条件 / 数据源，嵌套在 SQL 里。
DELETE：带条件删除，逐行记录日志，可以回滚，速度慢
TRUNCATE：清空全表，不记日志，不可回滚，速度极快
delete from 表; # 清空所有表
delete from 表 where 条件 # 常用
delete from user where id = 5; # 删除单条
delete from user where age > 18 and age < 22; # 删除多条
数值类型：
TINYINT 年龄、状态值
INT 整数、主键、年龄、用户数、订单数
BIGINT 超大数、ID
DECIMAL(M,D) 金融、金额
FLOAT/DOUBLE 浮点数、身高、重量
字符串类型
VARCHAR(n) 变长、用户名、地址
CHAR() 定长、手机号
日期类型
DATETIME 范围大、注册时间
DATE 日期、下单时间、生日
TIMESTAMP 数据更新时间
约束类型
NOT NULL 非空
UNIQUE 字段值在表中唯一
PRIMARY KEY 主键、非空且唯一
AUTO_INCREMENT 自增
DEFAULT 默认值填充
FROREIGN KEY 外键
UNSIGNED 非负数
create database 库名;
create table 表名 (结构);
// if not exists # 不存在 → 正常创建，已存在 → 不执行、不覆盖、不报错，直接跳过
// default character set utf8mb4 # 设置默认字符集，默认字符集是 utf8mb4（支持中文 + 表情）
// collate utf8mb4_general_ci # 排序规则
create database if not exists data
default character set utf8mb4
collate utf8mb4_general_ci;
// 进入data库
use data;
// comment '用户ID' # 描述
// default current_timestamp() # 默认值 获取当前系统时间并且自动填充
// engine=InnoDB default charset=utf8mb4 # 这个表使用 InnoDB 存储引擎，默认字符集是 utf8mb4）
create table if not exists user (
id int unsigned not null auto_increment comment '用户ID',
name varchar(50) not null comment '用户名',
age int unsigned comment '年龄',
create_time datetime not null default current_timestamp() comment '创建时间',
primary key (id)
) engine=InnoDB default charset=utf8mb4 comment='用户表';
drop table 表名;
drop database 库名;