表备份及还原
-- 备份
--同时备份表结构和表数据：
create table 新表名 as select * from 旧表名
--只备份表结构：
create table 新表名 as select * from 旧表名 where 1=2
--还原
insert into 现表 select * from 备份表;表删除
-- 删除单个表 会永久删除表中的所有数据和表结构，请务必谨慎操作
DROP TABLE [IF EXISTS] 表名;
-- 删除多个表 会永久删除表中的所有数据和表结构，请务必谨慎操作
DROP TABLE [IF EXISTS] 表名1, 表名2, ...;
-- 清空表数据 但保留表结构（速度快，不可回滚）
TRUNCATE TABLE 表名;
-- 清空表数据 但保留表结构（可以回滚）
DELETE FROM 表名;
-- 删除指定某个数据
mysql> delete from student where id=2;
--删除有级联的 会永久删除表中的所有数据和表结构，请务必谨慎操作
DROP TABLE 表名 CASCADE; 表创建
-- 创建老师表
CREATE TABLE `teacher` (
`teacher_id` INT NOT NULL COMMENT '老师id',
`teacher_name` VARCHAR ( 255 ) NULL COMMENT '老师姓名',
`teacher_age` INT NULL COMMENT '老师年龄',
`teacher_time` DATETIME NULL COMMENT '老师入校时间',
PRIMARY KEY ( `teacher_id` )
) COMMENT = '老师表';
-- 创建学生表
CREATE TABLE `student` (
`student_id` INT NOT NULL COMMENT '学生id',
`student_name` VARCHAR ( 255 ) NULL COMMENT '学生姓名',
`student_age` INT NULL COMMENT '学生年龄',
`student_time` DATETIME NULL COMMENT '学生入校时间',
`student_teacher` VARCHAR ( 255 ) NULL COMMENT '学生所属老师',
PRIMARY KEY ( `student_id` )
) COMMENT = '学生表';
表数据插入
-- 添加student表数据
INSERT INTO `student` VALUES (1, '张三', 3, '2024-09-01 00:00:00', '王老师');
INSERT INTO `student` VALUES (2, '李四', 3, '2023-09-01 00:00:00', '李老师');
INSERT INTO `student` VALUES (3, '王五', 4, '2022-09-01 00:00:00', '付老师');
INSERT INTO `student` VALUES (4, '老九', 5, '2021-09-01 00:00:00', '王老师');
INSERT INTO `student` VALUES (5, '六六', 10, NULL, '李老师');
INSERT INTO `student` (`student_id`,`student_age`) VALUES (7,4);表修改
修改表名
-- 修改表名
RENAME TABLE wjdb.student TO wjdb.students;修改表数据
update student set student_name = '张三三' where student_id = '1'; 表注释
-- 给表添加或修改注释
ALTER TABLE wjdb.student COMMENT='学生表';修改表约束
外键
- 外键（Foreign Key）是一种数据库约束，用于维护数据库中
表之间 的关系。 - 外键是某个表中的
一列或一组列 ，它们被用于引用另一个表中的主键列。 - 确定两个表的关系，通常是“
一对多 ”关系。 - 在“
多 ”的那一方表中创建外键列。
创建有外键的表
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`teacher_id` int NOT NULL COMMENT '老师id',
`teacher_name` varchar(255) NULL COMMENT '老师姓名',
PRIMARY KEY (`teacher_id`)
)COMMENT='老师表';
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`student_id` int NOT NULL COMMENT '学生id',
`student_name` varchar(255) NULL COMMENT '学生姓名',
PRIMARY KEY (`student_id`),
CONSTRAINT `学生表的外键` FOREIGN KEY (`student_id`) REFERENCES `teacher` (`teacher_id`)
)COMMENT='学生表';修改添加外键
-- 先给需要添加的新外键添加index
ALTER TABLE `wjdb`.`teacher`ADD INDEX(`teacher_name`);
-- 删除原来的外键
ALTER TABLE `wjdb`.`student` DROP FOREIGN KEY `学生表的外键`;
-- 修改外键
ALTER TABLE `wjdb`.`student`
ADD CONSTRAINT `学生表的外键` FOREIGN KEY (`student_id`) REFERENCES `wjdb`.`teacher` (`teacher_name`) ON DELETE RESTRICT ON UPDATE RESTRICT;主键约束（PRIMARY KEY）：确保某列或多列的组合在表中的唯一性。 一个表只能有一个
唯一约束（UNIQUE）：确保某列或多列的组合在表中的唯一性，但允许有空值。
非空约束（NOT NULL）：确保某列的值不能为空。
默认约束（DEFAULT）：为某列设置默认值。
外键约束（FOREIGN KEY）：确保某列或多列的值与另一个表的主键或唯一键相匹配。
检查约束（CHECK）：确保某列的值满足指定的条件。
自动递增约束（AUTO_INCREMENT）：确保某列的值自动递增。
-- 添加主键约束
alter table wjdb.xuesheng add primary key (id)
-- 删除主键约束
ALTER table wjdb.xuesheng drop primary key
-- 添加唯一约束
alter table wjdb.xuesheng add unique(name)
-- 删除唯一约束
ALTER table wjdb.xuesheng drop INDEX name
-- 添加非空约束
alter table wjdb.xuesheng modify nianji varchar(255) not null
-- 删除非空约束
alter table wjdb.xuesheng modify nianji varchar(255) null
-- 添加自动增长
alter table wjdb.xuesheng modify id int auto_increment
-- 删除自动增长
alter table wjdb.xuesheng modify id int
-- 添加默认值
alter table wjdb.xuesheng alter nianji set default '五年级'
-- 删除默认值
alter table wjdb.xuesheng alter nianji drop default
--添加外键
-- 删除外键
如果想删除表或者更新数据，但是又有外键可以临时关闭在打开
禁用外键约束
SET FOREIGN_KEY_CHECKS = 0;
删除完成后再启动外键约束
SET FOREIGN_KEY_CHECKS = 1;
这样设置只会影响当前会话， 不会影响全局
如果全局想用可以这样设置
SET GLOBAL FOREIGN_KEY_CHECKS = 0;
-- 查询是否开启了外键限制 值为1说明开启了限制
mysql> SELECT @@FOREIGN_KEY_CHECKS;
+----------------------+
| @@FOREIGN_KEY_CHECKS |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
列字段操作
添加列名
-- 添加列，必须指定列的属性
ALTER TABLE wjdb.student ADD teacher varchar(100) DEFAULT 王老师 NOT NULL COMMENT '学生所属老师';修改列名
-- 5.7版本可用 必须指定字段类型
alter table wjdb.student change column nianji banji varchar(255)
-- 8.0版本可用 age 修改为ages
ALTER TABLE wjdb.student CHANGE age ages int NOT NULL COMMENT '学生年龄';修改列注释
alter table wjdb.student modify column name VARCHAR(255) comment '学生姓名1';删除列
-- 删除列 删除school列
ALTER TABLE wjdb.student DROP COLUMN school;表查询
查询表结构及注释desc
--查询表结构及注释
-- 方式1：默认
desc wjdb.student;
-- 方式2:还会显示表具有的权限以及表是否有注释
show full columns from wjdb.student;
-- 方式2：还会显示表是否有注释
SHOW CREATE TABLE wjdb.student;
-- 方式3：查看表注释
先切换数据库
use information_schema;
完整表数据(表的创建时间，表的字符集,表注释等数据)
select * from TABLES where TABLE_SCHEMA='wjdb' and TABLE_NAME='student';
简写
select table_comment from TABLES where TABLE_SCHEMA='wjdb' and TABLE_NAME='student';单表基础查询select LIMIT
-- 查询表的所有信息
select * from student (生产不建议使用,消耗太大)
--我们从user表中查询所有用户的ID、姓名、手机号，且只显示前10条数据
-- 查询指定字段，限制返回条数（MySQL用LIMIT，Oracle用ROWNUM，SQL Server用TOP）
SELECT user_id, user_name, phone
FROM `user` -- 表名若为关键字，需用反引号包裹
LIMIT 10; -- 生产中我们要避免直接SELECT *，减少无效数据传输解析：指定字段查询是基础操作，
LIMIT用于控制结果集大小，防止全表扫描导致性能问题。
避坑：表名/字段名若与SQL关键字（如：order、user）重复，需用反引号（MySQL）、双引号（Oracle）或方括号（SQL Server）包裹。
优化：优先查询所需字段，而非SELECT *，降低IO开销。
单表带条件的查询
我们从user表中查询2023年注册、状态为“正常”的用户姓名和注册时间
-- 多条件筛选（AND连接），日期范围查询
SELECT user_name, register_time
FROM `user`
WHERE status = 'normal' -- 字符型条件需加单引号
AND register_time BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59'; -- 闭区间日期查询解析：
WHERE用于筛选数据，BETWEEN适合连续范围查询，比>=和<=简洁。
避坑：日期字段若只写'2023-01-01'，默认时间为00:00:00，可能漏查当天后期数据；字符型值必须加单引号，否则会被当作字段名。
单表排序查询
我们从order表中查询用户ID为1001的订单，按下单时间倒序、订单金额正序排列
-- 多字段排序（DESC降序，ASC升序，默认ASC）
SELECT order_id, order_amount, create_time
FROM `order`
WHERE user_id = 1001
ORDER BY create_time DESC, order_amount ASC;解析：
ORDER BY指定排序字段，优先级按字段顺序排列（先按create_time降序，同时间再按order_amount升序）。
避坑：排序字段若为NULL，MySQL中NULL默认排在最前（降序时排最后），我们需提前确认业务对NULL的排序要求。
优化：建立(user_id, create_time DESC, order_amount ASC)的联合索引，避免排序时的“文件排序”（Filesort）。
单表去重查询DISTINCT
我们从order表中查询所有下单用户的去重ID（即有过下单行为的用户）
-- DISTINCT去重，作用于所有查询字段的组合
SELECT DISTINCT user_id
FROM `order`;解析：
DISTINCT用于去除查询结果中的重复行，若查询多个字段，会对字段组合去重（而非单个字段）。
避坑：DISTINCT与LIMIT连用时，先去重再限制条数，我们需确认业务逻辑是否允许。
优化：若仅需统计去重数量，用COUNT(DISTINCT user_id)更高效；若数据量极大，我们可考虑用GROUP BY user_id替代（部分场景性能更优）。
单表聚合查询COUNT
统计user表中状态为“正常”且注册时间在2023年的用户总数
-- COUNT统计非NULL值数量，COUNT(*)统计所有行数（包括NULL）
SELECT COUNT(user_id) AS normal_user_count -- AS起别名，方便结果读取
FROM `user`
WHERE status = 'normal'
AND YEAR(register_time) = 2023;解析：
COUNT(column)统计指定字段非NULL的行数，COUNT(*)统计所有行数（无论字段是否为NULL）；YEAR()函数提取日期中的年份。
避坑：避免用COUNT(1)替代COUNT(*)（性能无差异），更不要用COUNT(字符串)（会将字符串转为0统计，逻辑错误）。
优化：若register_time有索引，register_time BETWEEN '2023-01-01' AND '2023-12-31'比YEAR(register_time)=2023更高效（可命中索引，避免函数对字段的“包装”）。
单表聚合查询（SUM/AVG）
统计order表中2024年1月的总订单金额、平均订单金额，以及订单总数。
-- 多聚合函数联用，同时计算总和、平均值、计数
SELECT SUM(order_amount) AS total_amount, -- 总和
AVG(order_amount) AS avg_amount, -- 平均值
COUNT(order_id) AS order_count -- 订单数
FROM `order`
WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';解析：
SUM计算数值字段总和，AVG计算平均值，聚合函数会自动忽略NULL值。
避坑：若order_amount存在NULL，SUM和AVG会跳过NULL（相当于不参与计算），我们需确认业务是否允许NULL（我们建议表设计时设置默认值0）。
优化：在create_time和order_amount上建立联合索引，减少聚合时的表扫描。
单表分组查询（GROUP BY）
统计order表中每个用户的订单总数和总消费金额，只显示订单数>=2的用户
-- GROUP BY分组，HAVING过滤分组结果（区别于WHERE过滤行）
SELECT user_id,
COUNT(order_id) AS order_count,
SUM(order_amount) AS total_spend
FROM `order`
GROUP BY user_id -- 分组字段必须出现在SELECT中（非聚合字段）
HAVING order_count >= 2; -- HAVING用于过滤分组后的结果解析：
GROUP BY按指定字段分组，对每组进行聚合计算；HAVING用于过滤分组结果（WHERE过滤原始数据，HAVING过滤分组后的数据）。
避坑：SELECT中的非聚合字段必须全部出现在GROUP BY中（MySQL的ONLY_FULL_GROUP_BY模式下强制要求，避免逻辑歧义）。
优化：建立(user_id, order_amount)的联合索引，实现“索引覆盖”（无需回表查询数据）。
两表内连接查询（INNER JOIN）
查询用户ID为1001的订单详情，需包含用户姓名和订单信息。
-- INNER JOIN：只返回两表中匹配条件的行（交集）
SELECT u.user_id, u.user_name, o.order_id, o.order_amount
FROM `user` u -- u是user表的别名，简化代码
INNER JOIN `order` o -- o是order表的别名
ON u.user_id = o.user_id -- 连接条件（必须写，否则会产生笛卡尔积）
WHERE u.user_id = 1001;解析：
INNER JOIN（可简写为JOIN）只返回两表中满足连接条件的行，是常用的连接方式。
避坑：如果我们忘记写ON条件会导致“笛卡尔积”（两表行数相乘，数据量暴增，严重影响性能）。
优化：确保连接字段user_id在两表中均有索引（user表的主键索引，order表的外键索引）。
两表左连接查询（LEFT JOIN）
查询所有用户的姓名及对应的订单数（包括没有下单的用户，订单数显示0）
-- LEFT JOIN：返回左表所有行，右表匹配则显示，不匹配则显示NULL
SELECT u.user_id, u.user_name,
COUNT(o.order_id) AS order_count -- 右表NULL时COUNT为0
FROM `user` u
LEFT JOIN `order` o
ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name; -- 左表字段分组解析：
LEFT JOIN以左表（user）为基准，右表（order）无匹配时返回NULL；COUNT(o.order_id)中，NULL会被忽略，因此无订单用户的计数为0。
避坑：若在WHERE中添加右表的条件（如：o.status='paid'），会将左连接转为内连接（过滤掉右表NULL的行），我们需将右表条件移至ON中。
优化：右表连接字段user_id建立索引，减少连接时的匹配时间。
两表右连接查询（RIGHT JOIN）
查询所有订单的信息及对应的用户姓名（包括用户已删除但未清理的订单，用户姓名显示NULL）
-- RIGHT JOIN：返回右表所有行，左表匹配则显示，不匹配则显示NULL
SELECT o.order_id, o.order_amount, u.user_name
FROM `user` u
RIGHT JOIN `order` o
ON u.user_id = o.user_id;解析：
RIGHT JOIN以右表（order）为基准，左表（user）无匹配时返回NULL，适合需保留右表所有数据的场景。
避坑：右连接逻辑可转换为左连接（交换表顺序），我们建议优先用左连接（更符合“基准表在前”的直观逻辑）。
优化：同左连接，我们需确保连接字段有索引。
多表连接查询
查询用户1001的订单详情，包含用户姓名、商品名称、订单金额（涉及user、order、order_item、product4张表）
-- 多表连接（先连user和order，再连order_item和product）
SELECT u.user_name, p.product_name, o.order_amount, oi.quantity
FROM `user` u
INNER JOIN `order` o ON u.user_id = o.user_id
INNER JOIN `order_item` oi ON o.order_id = oi.order_id -- 订单明细表
INNER JOIN `product` p ON oi.product_id = p.product_id -- 商品表
WHERE u.user_id = 1001;解析：多表连接需依次指定连接关系，通常按“主表→从表”的顺序（如：
user→order→order_item→product）。
避坑：多表连接时别名必须唯一，避免字段名冲突（如：两表都有create_time，需用u.create_time区分）。
优化：所有连接字段（user_id、order_id、product_id）均建立索引，减少多表匹配的开销。
模糊查询
查询所有包含字符串nignx的，可以出现在任何位置
select * from wp_links WHERE 列名 like '%nginx%' 包含mr的数据，mr开头
where 列名 like 'mr%'其它案例
--包含mr的数据，mr结尾
where 列名 like '%mr'
--包含前两个字符为mr的数据，后一个字符为任意字符
where 列名 like 'mr_'
--前面为任意字符，mr结尾
where 列名 like '_mr'
--以m或者r开头
where 列名 like '[mr]%'
--查询某列中以a-e之间的字符开头的数据，其中包a和e
where 列名 like '[a-e]%'
----查询某列中不以m或 r之间的字符开头的数据
where 列名 like '[^mr]%'
--包含mr的数据，mr结尾
where 列名 like '%mr'
--不以a-e之间开头的
where 列名 like '[^a-e]%'时间查询
时间转换
-- 时间转字符串 DATE_FORMAT 2024-09-01
SELECT student_id,student_name ,DATE_FORMAT(student_time, '%Y-%m-%d')
AS "入学时间" FROM student;
--字符串转时间 STR_TO_DATE '%Y-%m-%d %H:%i:%s'
SELECT STR_TO_DATE('2023-10-05 14:30:00', '%Y-%m-%d %H:%i:%s')
AS converted_date; 实例1：查询某个时间段的数据，默认排序方式是 （旧新） 在选择用某个字段进行分组显示 排序查询
ASC表示升序（默认），DESC表示降序
select * from file_record where create_time >= '2024-05-01'
and create_time <= '2024-05-31'
新旧 使用order by create_time desc 在选择用某个字段进行分组显示
select * from file_record where create_time >= '2024-05-01'
and create_time <= '2024-05-31' order by create_time desc案例2：查询某个时间段的数据，然后在查询某个字段在此时间的数据 用and连接即可
select * from file_record where create_time >= '2024-05-01'
and create_time <= '2024-05-31' and user_name = '小明哥'案例3:查询某个时间段的数据，然后在查询多个字段在此时间的数据 用in连接
select * from file_record where create_time >= '2024-05-01'
and create_time <= '2024-05-31'
and user_name in（'小明哥','小李哥','小张哥'）案例:排除空值用is not null，在使用子查询
select * from (select * from student s
where s.student_time is not null )student ORDER BY student_time条件查询
查询users表中年龄大于18岁的用户：
SELECT * FROM users WHERE age > 18; 查询年龄大于18岁且性别为男的用户
SELECT * FROM users WHERE age > 18 AND gender = 'male'; 聚合函数查询
COUNT：统计行数
SELECT COUNT(*) FROM 表名;SUM：求和
SELECT SUM(column1) FROM 表名; AVG：求平均值
SELECT AVG(column1) FROM 表名; MAX和MIN：求最大值和最小值
SELECT MAX(列名) FROM 表名;
SELECT MIN(列名) FROM 表名;子查询
先查询年龄大于18岁的用户，再从这些用户中查询name列：
SELECT name FROM (SELECT * FROM users WHERE age > 18) AS subquery; 表索引
索引占空间大小
use information_schema;
select concat(round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size' from tables where table_schema='wordpress';