MySQL 完整语法总结（基础：增删改查+进阶：条件多判断、聚合函数等）

MySQL 完整语法总结基础增删改查进阶条件多判断、聚合函数等一、MySQL整体结构层级从大到小三层MySQL服务实例 → 数据库(database/schema) → 数据表(table) → 字段(列column) → 行数据(row)服务本地localhost:3306客户端先连接服务数据库独立项目库如student_db、shop_db数据表库内存储结构化数据如user、goods字段表的列id、name、age定义数据类型记录表的一行完整数据二、【增】连接、建库、建表、插入数据1. 连接MySQL命令行登录语法mysql -h主机 -P端口 -u用户名 -p密码示例# 本地连接回车后输入密码 mysql -hlocalhost -P3306 -uroot -p2. 创建数据库 create database语法CREATE DATABASE [IF NOT EXISTS] 库名 [DEFAULT CHARSETutf8mb4];示例# 不存在则创建学生库指定utf8完整字符集 CREATE DATABASE IF NOT EXISTS student_db DEFAULT CHARSETutf8mb4;3. 使用数据库 use操作表前必须选中库语法USE 库名;示例USE student_db;4. 创建数据表 create table语法CREATE TABLE [IF NOT EXISTS] 表名( 字段1 类型 约束, 字段2 类型 约束, ... ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;示例学生表CREATE TABLE IF NOT EXISTS student( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键自增id, name VARCHAR(20) NOT NULL COMMENT 学生姓名, age TINYINT COMMENT 年龄, score FLOAT COMMENT 分数 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;5. 新增表数据 insert语法1指定字段插入INSERT INTO 表名(字段1,字段2...) VALUES(值1,值2...);示例INSERT INTO student(name,age,score) VALUES(张三,18,92.5);语法2全字段批量插入INSERT INTO 表名 VALUES(全部字段值),(第二条值);示例INSERT INTO student VALUES(2,李四,19,88),(3,王五,18,95);三、【删】删数据、删表、删数据库1. 删除表数据 delete删除行保留表结构语法DELETE FROM 表名 [WHERE 条件];示例删除id3的学生DELETE FROM student WHERE id3;不加WHERE会清空整张表慎用2. 清空表 truncate重置自增速度更快语法TRUNCATE TABLE 表名;示例TRUNCATE TABLE student;3. 删除数据表 drop table语法DROP TABLE [IF EXISTS] 表名;示例DROP TABLE IF EXISTS student;4. 删除数据库 drop database语法DROP DATABASE [IF EXISTS] 库名;示例DROP DATABASE IF EXISTS student_db;四、【改】更新数据、修改表结构1. 更新表数据 update 修改行内容语法UPDATE 表名 SET 字段1新值,字段2新值 [WHERE 条件];示例把张三分数改成96UPDATE student SET score96 WHERE name张三;不加WHERE会修改全表所有数据严禁2. 修改表结构 alter table常用三种① 添加字段 ADD语法ALTER TABLE 表名 ADD 字段名 类型 约束;示例给学生表加性别字段ALTER TABLE student ADD sex CHAR(1) COMMENT 性别;② 修改字段类型/名称 MODIFY / CHANGE语法修改类型ALTER TABLE 表名 MODIFY 字段 新类型;示例age改为smallintALTER TABLE student MODIFY age SMALLINT;③ 删除字段 DROP COLUMN语法ALTER TABLE 表名 DROP COLUMN 字段名;示例删除sex字段ALTER TABLE student DROP COLUMN sex;④ 修改表名 RENAMEALTER TABLE student RENAME TO stu;五、【查】基础查询、去重、过滤、排序前置表数据下面查询统一用这张表INSERT INTO student(name,age,score) VALUES (张三,18,92), (张三,18,92), (李四,19,88), (赵六,17,76), (钱七,20,98);1. 基础SELECT 查询语法1查询全部列 *SELECT * FROM 表名;示例SELECT * FROM student;语法2查询指定列SELECT 字段1,字段2 FROM 表名;示例SELECT name,score FROM student;2. 去重 DISTINCT 去除重复行语法SELECT DISTINCT 字段 FROM 表名;示例去除重复姓名SELECT DISTINCT name FROM student;3. 条件过滤 WHERE 筛选数据支持运算符 ! and or like语法SELECT 字段 FROM 表名 WHERE 筛选条件;示例1分数大于90的学生SELECT name,score FROM student WHERE score 90;示例2年龄18且分数90SELECT * FROM student WHERE age18 AND score90;示例3模糊匹配姓名带“张”SELECT * FROM student WHERE name LIKE %张%;4. 排序 ORDER BY asc/descASC升序默认DESC降序语法SELECT 字段 FROM 表名 [WHERE 条件] ORDER BY 排序字段 [DESC];示例1分数从高到低排序SELECT name,score FROM student ORDER BY score DESC;示例2先年龄升序同年龄分数降序SELECT * FROM student ORDER BY age ASC,score DESC;5. 分页 LIMIT补充高频查询语法LIMIT 偏移量,条数示例取前3条数据SELECT * FROM student LIMIT 3;示例跳过2条取2条SELECT * FROM student LIMIT 2,2;六、基础总结增mysql连接、CREATE DATABASE、CREATE TABLE、INSERT删DELETE、TRUNCATE、DROP TABLE、DROP DATABASE改UPDATE改数据、ALTER TABLE改表结构查SELECT、DISTINCT去重、WHERE过滤、ORDER BY排序、LIMIT分页需要我把所有语法整理成一页可直接复制背诵的精简速查表吗MySQL 进阶语法完整详细总结下面分模块条件多判断、聚合函数、分组、子查询、多表联查、事务、索引、视图、存储过程、流程控制、常用函数、分页优化、表复制、关联删除更新沿用上面student、新增class、score测试表。前置测试表统一示例数据源先执行-- 班级表 CREATE TABLE class( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(20) NOT NULL )ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO class(cname) VALUES(高一1班),(高一2班); -- 学生表增加班级外键cid DROP TABLE IF EXISTS student; CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 学生主键, name VARCHAR(20) NOT NULL COMMENT 姓名, age TINYINT COMMENT 年龄, score FLOAT COMMENT 总分, cid INT COMMENT 所属班级id )ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO student(name,age,score,cid) VALUES (张三,18,92,1), (张三,18,92,1), (李四,19,88,2), (赵六,17,76,1), (钱七,20,98,2), (周八,18,59,1), (吴九,19,77,2), (郑十,18,NULL,1); -- 分数为空一、WHERE 进阶筛选语法基础查询升级1. IN / NOT IN 多值匹配语法字段 IN (值1,值2,值3)-- 查询id为1,3,5的学生 SELECT * FROM student WHERE id IN(1,3,5); -- 查询不在1班、2班以外无数据 SELECT * FROM student WHERE cid NOT IN(1,2);2. BETWEEN AND 区间范围包含两端-- 分数70~95之间 SELECT name,score FROM student WHERE score BETWEEN 70 AND 95; -- 不在区间 SELECT name,score FROM student WHERE score NOT BETWEEN 70 AND 95;3. IS NULL / IS NOT NULL 判断空值不能匹配NULL必须用IS NULL-- 查询成绩为空的学生 SELECT * FROM student WHERE score IS NULL; -- 成绩有分数的学生 SELECT * FROM student WHERE score IS NOT NULL;4. LIKE 模糊查询进阶%任意多个字符_单个字符-- 名字以张开头 SELECT * FROM student WHERE name LIKE 张%; -- 名字带七 SELECT * FROM student WHERE name LIKE %七%; -- 两个字名字单个下划线匹配一位 SELECT * FROM student WHERE name LIKE __; -- 不含三的名字 SELECT * FROM student WHERE name NOT LIKE %三%;5. AND / OR 优先级 括号分组AND 优先级高于OR复杂条件用括号-- 1班 且 分数大于90 或者 2班分数大于95 SELECT * FROM student WHERE (cid1 AND score90) OR (cid2 AND score95);二、聚合函数统计类语法必学进阶聚合函数会把多行合并成一行结果共5大核心COUNT()计数、SUM()求和、AVG()平均值、MAX()最大值、MIN()最小值语法通用SELECT 聚合函数(字段) FROM 表名 [WHERE 条件];示例-- 1. 统计学生总人数*统计所有行含null SELECT COUNT(*) AS total_stu FROM student; -- 2. 统计有成绩的学生忽略score为NULL的数据 SELECT COUNT(score) AS have_score FROM student; -- 3. 去重计数统计不重复姓名人数 SELECT COUNT(DISTINCT name) AS diff_name FROM student; -- 4. 全班总分、平均分、最高分、最低分 SELECT SUM(score) AS total_score, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM student; -- 带条件只统计1班的平均分 SELECT AVG(score) FROM student WHERE cid1;三、GROUP BY 分组统计聚合配套核心语法按某一列分组每组单独统计分组后过滤必须用HAVING不能用WHERE1. 基础分组-- 按班级分组统计每个班人数、平均分 SELECT cid, COUNT(id) AS stu_count, AVG(score) AS class_avg FROM student GROUP BY cid;2. HAVING 分组后过滤WHERE分组前过滤原始数据HAVING分组完成后过滤分组结果-- 只保留平均分数大于80的班级 SELECT cid,AVG(score) avg_score FROM student GROUP BY cid HAVING avg_score 80;3. 多字段分组先按班级再按年龄分组统计SELECT cid,age,COUNT(id) count FROM student GROUP BY cid,age;四、子查询查询嵌套查询分为标量子查询、列子查询、表子查询、相关子查询1. WHERE 后标量子查询返回单个值-- 查询分数大于平均分的学生 SELECT name,score FROM student WHERE score (SELECT AVG(score) FROM student);2. IN 子查询返回一列多值-- 查询高一1班的所有学生先查班级表拿cid SELECT * FROM student WHERE cid IN (SELECT cid FROM class WHERE cname高一1班);3. FROM 后表子查询派生表必须起别名-- 先过滤分数60再统计人数 SELECT COUNT(*) pass_num FROM ( SELECT * FROM student WHERE score 60 ) t; -- t是派生表别名不可省略4. EXISTS 存在性子查询效率高于IN判断子查询是否有数据有则保留外层记录-- 查询存在对应班级的学生 SELECT * FROM student s WHERE EXISTS (SELECT 1 FROM class c WHERE c.cid s.cid);五、多表联查进阶重中之重4种连接1. 内连接 INNER JOIN只查两边匹配数据语法表1 INNER JOIN 表2 ON 关联条件-- 查询学生所属班级名称只显示有班级的学生 SELECT s.id,s.name,c.cname FROM student s INNER JOIN class c ON s.cid c.cid;2. 左连接 LEFT JOIN左表全部保留右表无匹配显示NULL常用场景查询所有学生哪怕没有分配班级SELECT s.id,s.name,c.cname FROM student s LEFT JOIN class c ON s.cid c.cid;3. 右连接 RIGHT JOIN右表全部保留SELECT s.id,s.name,c.cname FROM student s RIGHT JOIN class c ON s.cid c.cid;4. 交叉连接 CROSS JOIN 笛卡尔积极少用两张表所有数据两两组合无ON条件SELECT * FROM student CROSS JOIN class;5. 多表级联连接学生 班级多表关联SELECT s.name,c.cname FROM student s LEFT JOIN class c ON s.cid c.cid WHERE s.age 18;6. 关联更新 / 关联删除进阶改删关联更新-- 将高一1班所有学生分数5分 UPDATE student s JOIN class c ON s.cid c.cid SET s.score s.score 5 WHERE c.cname 高一1班;关联删除-- 删除高一2班分数低于60的学生 DELETE s FROM student s JOIN class c ON s.cidc.cid WHERE c.cname高一2班 AND s.score60;六、UNION / UNION ALL 结果集合并UNION合并后自动去重排序效率低UNION ALL直接拼接不去重速度快推荐要求前后查询列数、字段类型一致-- 合并1班、2班分数90以上学生不去重 SELECT name,score FROM student WHERE cid1 AND score90 UNION ALL SELECT name,score FROM student WHERE cid2 AND score90;七、CASE 条件判断查询内多分支逻辑相当于SQL里的if else简单CASESELECT name,score, CASE WHEN score 90 THEN 优秀 WHEN score 70 THEN 良好 WHEN score 60 THEN 及格 ELSE 不及格 END AS level FROM student;八、事务 Transaction保证数据一致性仅InnoDB引擎支持ACID特性关键字BEGIN/START TRANSACTION、COMMIT提交、ROLLBACK回滚完整示例-- 开启事务 START TRANSACTION; -- 两条修改操作 UPDATE student SET score100 WHERE id1; UPDATE student SET score99 WHERE id2; -- 出现错误执行回滚放弃所有修改 -- ROLLBACK; -- 无异常提交永久生效 COMMIT;九、索引 Index查询优化进阶1. 创建普通索引CREATE INDEX idx_stu_name ON student(name);2. 创建唯一索引值不能重复CREATE UNIQUE INDEX idx_stu_id ON student(id);3. 联合复合索引CREATE INDEX idx_cid_age ON student(cid,age);4. 查看、删除索引-- 查看表索引 SHOW INDEX FROM student; -- 删除索引 DROP INDEX idx_stu_name ON student;十、视图 VIEW封装常用查询简化SQL视图是虚拟表不存真实数据创建视图CREATE VIEW v_stu_class AS SELECT s.id,s.name,s.score,c.cname FROM student s LEFT JOIN class c ON s.cidc.cid;使用视图像查表一样SELECT * FROM v_stu_class WHERE score80;修改/删除视图ALTER VIEW v_stu_class AS SELECT name,score FROM student; DROP VIEW IF EXISTS v_stu_class;十一、存储过程 PROCEDURE批量SQL封装创建无参存储过程DELIMITER // -- 修改语句结束符为// CREATE PROCEDURE query_high_score() BEGIN SELECT * FROM student WHERE score 90; END // DELIMITER ; -- 恢复分号结束 -- 调用 CALL query_high_score(); -- 删除存储过程 DROP PROCEDURE IF EXISTS query_high_score;带入参存储过程DELIMITER // CREATE PROCEDURE get_stu_by_cid(IN param_cid INT) BEGIN SELECT * FROM student WHERE cid param_cid; END // DELIMITER ; CALL get_stu_by_cid(1); -- 查询1班学生十二、常用内置函数进阶1. 字符串函数SELECT CONCAT(name,-,age) AS info FROM student; -- 拼接 SELECT SUBSTR(name,1,1) FROM student; -- 截取首字符 SELECT LENGTH(name) FROM student; -- 字符串长度2. 数字函数SELECT ROUND(score,1) FROM student; -- 保留1位小数 SELECT CEIL(score),FLOOR(score) FROM student; -- 向上/向下取整3. 日期函数举例SELECT NOW(),CURDATE(),YEAR(NOW());4. 控制函数 IF()SELECT name,IF(score60,及格,不及格) pass FROM student;十三、表结构进阶操作ALTER 补充修改表字符集ALTER TABLE student CHARACTER SET utf8mb4;添加外键约束ALTER TABLE student ADD CONSTRAINT fk_stu_class FOREIGN KEY(cid) REFERENCES class(cid);删除外键ALTER TABLE student DROP FOREIGN KEY fk_stu_class;复制表结构数据CREATE TABLE student_copy AS SELECT * FROM student;只复制表结构不带数据CREATE TABLE student_copy2 LIKE student;十四、分页 LIMIT 进阶优化基础LIMIT offset,size-- 第1页每页3条 SELECT * FROM student LIMIT 0,3; -- 第2页 SELECT * FROM student LIMIT 3,3;大数据偏移优化offset很大时慢用主键过滤-- 替代 LIMIT 10000,10 SELECT * FROM student WHERE id10000 LIMIT 10;十五、权限管理DBA运维进阶语法创建用户CREATE USER user1localhost IDENTIFIED BY 123456;授权库表权限GRANT SELECT,INSERT,UPDATE ON student_db.* TO user1localhost;刷新权限FLUSH PRIVILEGES;删除用户DROP USER user1localhost;十六、进阶总结基础查询SELECT → FROM → WHERE分组统计GROUP BY → HAVING排序分页ORDER BY → LIMIT多表JOIN ON、UNION逻辑CASE、IF、EXISTS、IN高级对象视图VIEW、存储过程PROCEDURE、索引INDEX事务TRANSACTION、COMMIT、ROLLBACKDDL库表CREATE/ALTER/DROP、外键、复制表DCL权限CREATE USER、GRANT、FLUSH PRIVILEGES