云扬のBlog

与你共享美好生活

【MySQL】必备SQL语句大全：从基础到实战

1.1 库与表的创建方法

1.1.1 库表字段的核心构成

数据库表的字段是数据存储的基本单元，通常包含三要素：字段名（标识数据含义）、字段类型（限制数据格式，如int、varchar等）、约束条件（如主键、默认值、非空等）。合理设计字段是保证数据完整性的基础。

1.1.2 SQL的三大分类

  • DDL（Data Definition Language，数据定义语言）：用于创建、修改、删除数据库对象（如库、表、索引），常见命令有create、alter、drop。
  • DML（Data Manipulation Language，数据操纵语言）：用于对表中数据进行增删改查，常见命令有insert、delete、update、select。
  • DCL（Data Control Language，数据控制语言）：用于管理数据库权限，常见命令有grant、revoke。

1.1.3 数据库的创建与使用

  • 创建数据库：create database [库名];
    示例：create database school_info;（创建名为school_info的数据库）
  • 查看所有数据库：show databases;（列出当前MySQL服务器中所有数据库）
  • 切换数据库：use [库名];
    示例：use school_info;（切换到school_info数据库，后续操作默认在此库中执行）

1.1.4 表的创建与结构查看

表的创建语法

create table [表名](
  [字段名1] [字段类型] [约束条件] comment [字段注释],
  [字段名2] [字段类型] [约束条件] comment [字段注释],
  ...
) engine=[存储引擎] charset=[字符集];
  • engine=innodb：指定存储引擎为InnoDB（支持事务、外键，MySQL默认引擎）。
  • charset=utf8mb4：指定字符集为utf8mb4（支持所有Unicode字符，包括emoji）。

示例：创建学生成绩表

create table score_info(
  id int primary key auto_increment comment '主键（自增）',
  name varchar(11) default null comment '学生姓名',
  score int default null comment '学生分数'
) engine=innodb charset=utf8mb4;

表结构相关操作

  • 查看当前库中所有表：show tables;
  • 查看表详细结构：
    show create table [表名];（显示建表完整语句）
    或 desc [表名];（简洁展示字段信息）
    示例：show create table score_info;

1.2 字段的增删改操作

1.2.1 新增字段

  • 末尾新增字段（默认添加到表最后）：
    alter table [表名] add column [字段名] [字段类型] comment [字段注释];
    示例：alter table score_info add column class varchar(10) default null comment '班级';
  • 指定位置新增（在某字段后）：
    alter table score_info add column course varchar(10) default null comment '课程' after name;（在name字段后添加course）
  • 首部新增（添加到表最前面）：
    alter table score_info add column stu_id int default null comment '学号' first;
  • 批量新增字段：
    alter table score_info add column sex varchar(10) default null comment '性别', add column age int default null comment '年龄';

1.2.2 删除字段

  • 单字段删除：alter table [表名] drop column [字段名];
    示例：alter table score_info drop column stu_id;
  • 多字段删除：
    alter table score_info drop column sex, drop column age;（同时删除sex和age）

1.2.3 修改字段类型

语法：alter table [表名] modify column [字段名] [新类型] default [默认值] comment [注释];

  • 示例1：将class字段从varchar改为int：
    alter table score_info modify column class int default null comment '班级';
  • 示例2：调整name字段长度为20：
    alter table score_info modify column name varchar(20) default null comment '学生姓名';

1.2.4 修改字段名称

语法：alter table [表名] change [原字段名] [新字段名] [新类型] default [默认值] comment [注释];
示例：将name改为stu_name并调整长度：
alter table score_info change name stu_name varchar(20) default null comment '学生姓名';

1.3 增删查改（CRUD）基本功

1.3.1 插入数据（Insert）

  • 全字段插入（需按表字段顺序）：
    insert into score_info values (1, 'a', 88);（假设表字段为id, stu_name, score）
  • 多行长插入：
    insert into score_info values (2, 'b', 90), (3, 'c', 86);（同时插入2行数据）
  • 部分字段插入（指定字段名）：
    insert into score_info(stu_name, score) values ('d', 60);（只插入stu_name和score）

1.3.2 数据查询（Select）

  • 全表查询：select [字段名] from [表名];
    示例：select * from score_info;（*表示查询所有字段）
  • 条件查询：select * from score_info where id=2;（查询id=2的记录）
  • 指定字段查询：select stu_name from score_info where id=2;（只查询id=2的学生姓名）

1.3.3 数据修改（Update）

  • 条件修改：update [表名] set [字段名=值] where [条件];
    示例：update score_info set stu_name='aa' where id=1;（将id=1的姓名改为aa）
  • 全表修改（谨慎使用！）：
    update score_info set stu_name='aa';（将表中所有记录的stu_name改为aa）

1.3.4 数据删除（Delete/Truncate）

  • 条件删除：delete from [表名] where [条件];
    示例：delete from score_info where id=4;（删除id=4的记录）
  • 全表数据删除：
  • 方式1：delete from score_info;（逐行删除，支持事务回滚）
  • 方式2：truncate score_info;（清空表并重置自增主键，效率更高但不支持回滚）

1.4 数据过滤：精准提取所需记录

比较运算符

  • 等值匹配：select * from score_info where stu_name='a';
  • 不等于：select * from score_info where stu_name<>'a'; 或 stu_name!='a';
  • 范围匹配：
    score<80（小于80）、score>80（大于80）、score<=86（小于等于86）、score>=88（大于等于88）。

逻辑与范围

  • 区间匹配：select * from score_info where score between 80 and 90;（查询80-90分的记录，包含边界）
  • 多条件且：select * from score_info where score>=88 and stu_name='a';（分数≥88且姓名为a）
  • 多条件或：select * from score_info where score>88 or score<=60;（分数>88或≤60）

集合匹配

  • 匹配集合内值：select * from score_info where stu_name in ('a', 'b');（姓名为a或b）
  • 排除集合内值：select * from score_info where stu_name not in ('a', 'b');（姓名不是a且不是b）

1.5 子查询：嵌套查询的灵活应用

1.5.1 实验环境准备

创建测试表

-- 学生信息表
create table student_info ( 
  id int primary key auto_increment comment '主键',
  stu_id int(11) default null comment '学生ID',
  stu_name varchar(30) default null comment '学生姓名',
  class varchar(30) default null comment '班级'
) engine=innodb default charset=utf8mb4;

-- 成绩表
create table score_info(
  id int primary key auto_increment comment '主键',
  stu_id int(11) default null comment '学生ID',
  score int default null comment '分数'
) engine=innodb default charset=utf8mb4;

插入测试数据

insert into student_info(stu_id, stu_name, class) values 
(1, 'a', '一班'), (2, 'b', '一班'), (3, 'c', '二班'), (4, 'd', '二班'), (5, 'e', '二班');

insert into score_info(stu_id, score) values 
(1, 88), (2, 90), (3, 82), (4, 92), (6, 85); -- 注意：stu_id=6在student_info中无对应

1.5.2 子查询类型及示例

  • 单行子查询（返回单值，用=匹配）：
    select * from score_info where stu_id = (select stu_id from student_info where stu_name = 'a');
    （查询姓名为a的学生的成绩，子查询返回stu_id=1）
  • 多行子查询（返回多值，用in匹配）：
    select * from score_info where stu_id in (select stu_id from student_info where class = '一班');
    （查询一班所有学生的成绩，子查询返回stu_id=1,2）
  • FROM子句子查询（将子查询结果作为临时表）：
    select stu_id, score from (select stu_id, score-1 as score from score_info) as score_tmp where score >=90;
    （查询“分数减1后≥90”的记录，子查询生成临时表score_tmp）

1.6 关联查询：多表数据联动分析

内连接（Inner Join）

只返回两表中匹配条件的记录。
语法：select [字段] from 表1 a inner join 表2 b on a.关联字段 = b.关联字段;

示例1：查询学生信息与对应成绩（全字段）：
select * from student_info a inner join score_info b on a.stu_id = b.stu_id;

示例2：查询指定字段+条件（只查一班学生的姓名和分数）：
select a.stu_name, b.score from student_info a inner join score_info b on a.stu_id = b.stu_id where a.class = '一班';

左连接（Left Join）

返回左表所有记录，右表无匹配时补null。
示例：select * from student_info a left join score_info b on a.stu_id = b.stu_id;
（即使学生无成绩，也会显示其基本信息）

右连接（Right Join）

返回右表所有记录，左表无匹配时补null。
示例：select * from student_info a right join score_info b on a.stu_id = b.stu_id;
（即使成绩对应的学生信息不存在，也会显示成绩记录）

1.7 聚集函数与分组查询：数据聚合分析

1.7.1 实验表准备

create table student_score(
  id int primary key auto_increment comment '主键',
  stu_id int default null comment '学生ID',
  class varchar(30) default null comment '班级',
  score int default null comment '分数'
) engine=innodb default charset=utf8mb4;

insert into student_score(stu_id, class, score) values 
(1, '一班', 88), (2, '一班', 90), (3, '二班', 82), (4, '二班', 92), (5, '二班', 85);

1.7.2 常用聚集函数

  • 计数：select count(*) from student_score;（统计总记录数）
  • 平均值：select avg(score) from student_score;（所有学生的平均分）
  • 求和：select sum(score) from student_score;（所有分数总和）
  • 最大值：select max(score) from student_score;（最高分数）
  • 最小值：select min(score) from student_score;（最低分数）

带条件的聚合：select avg(score) from student_score where class='一班';（一班的平均分）

1.7.3 分组查询（Group By）

按指定字段分组，对每组应用聚合函数。

  • 按班级查平均分：
    select class, avg(score) as avg_score from student_score group by class;
  • 按班级查最高分：
    select class, max(score) as max_score from student_score group by class;
  • 按班级查人数：
    select class, count(*) as stu_count from student_score group by class;
  • 按班级合并学生ID：
    select class, group_concat(stu_id) as stu_ids from student_score group by class;
    （结果示例：一班 | 1,2；二班 | 3,4,5）

1.7.4 过滤分组（Having）

对分组后的结果进一步筛选（where用于分组前过滤，having用于分组后）。

示例：查询人数大于2的班级及其人数：
select class, count(*) as stu_count from student_score group by class having stu_count > 2;
（二班人数为3，会被筛选出来）

1.8 模糊查询：关键词匹配

1.8.1 测试表准备

create table student_name ( 
  id int primary key auto_increment comment '主键',
  stu_id int default null comment '学生ID',
  stu_name varchar(30) default null comment '姓名'
) engine=innodb default charset=utf8mb4;

insert into student_name(stu_id, stu_name) values (1, 'abc'), (2, 'def'), (3, 'ghi');

1.8.2 Like匹配

  • %：匹配任意长度字符（包括0个）
  • 以a开头：select * from student_name where stu_name like 'a%';（匹配abc）
  • 以c结尾：select * from student_name where stu_name like '%c';（匹配abc）
  • 包含b：select * from student_name where stu_name like '%b%';（匹配abc）
  • _：匹配单个字符
    示例：select * from student_name where stu_name like 'ab_';（匹配abc，_对应c）
  • 排除匹配：select * from student_name where stu_name not like 'a%';（排除以a开头的记录）

1.8.3 正则匹配（Regexp）

更灵活的模式匹配，支持正则表达式。

  • 包含a或d：
    select * from student_name where stu_name regexp '[ad]'; 或 regexp 'a|d';（匹配abc、def）
  • 包含小写字母：select * from student_name where stu_name regexp '[a-z]';
  • 学生ID包含数字：select * from student_name where stu_id regexp '[0-9]';

1.9 结果排序：Order By

  • 升序（默认）：select * from student_score order by score;（按分数从低到高）
  • 降序：select * from student_score order by score desc;（按分数从高到低）
  • 多字段排序（先按班级升序，再按分数降序）：
    select * from student_score order by class, score desc;

1.10 结果分页：Limit

用于限制返回结果的数量，常用于分页展示。

  • 取前N行：select * from student_score limit 2;（返回前2条记录）
  • 分页查询（从第3行开始，取2条）：
    select * from student_score limit 2, 2;（limit 偏移量, 条数，偏移量从0开始，即从第3行取2条）
  • 结合排序：select * from student_score order by score desc limit 3;（取分数最高的3条）
  • 带Offset语法：select * from student_score limit 2 offset 1;（跳过1行，取2条，等同于limit 1,2）

1.11 组合查询：Union

用于合并多个select的结果集，要求字段数和类型一致。

  • Union（自动去重）：
    示例：合并“二班学生”和“分数≥90”的记录（去重）：
    select * from student_score where class='二班' union select * from student_score where score >=90;
  • Union All（保留所有记录，包括重复）：
    示例：保留所有符合条件的记录（含重复）：
    select * from student_score where class='二班' union all select * from student_score where score >=90;

1.12 综合案例：学生成绩分析

1.12.1 环境准备

创建数据库与表

-- 创建学生库
create database student;
use student;

-- 学生信息表
create table student_info(
  id int primary key auto_increment comment '主键',
  stu_num int default null comment '学号',
  stu_name varchar(10) default null comment '姓名',
  stu_class varchar(10) default null comment '班级'
) engine=innodb charset=utf8mb4;

-- 成绩表
create table student_score(
  id int primary key auto_increment comment '主键',
  stu_num int default null comment '学号',
  stu_course varchar(10) default null comment '学科',
  stu_score int default null comment '分数'
) engine=innodb charset=utf8mb4;

插入测试数据

-- 学生信息
insert into student_info(stu_num, stu_name, stu_class) values 
(1101, '周星星', '高三一班'), (1102, '仙蒂', '高三一班'), 
(1201, '黄小龟', '高三二班'), (1202, '阿敏', '高三二班');

-- 成绩数据
insert into student_score(stu_num, stu_course, stu_score) values 
(1101, '语文', 88), (1101, '数学', 90), (1102, '语文', 91), (1102, '数学', 87),
(1201, '语文', 89), (1201, '数学', 80), (1202, '语文', 92), (1202, '数学', 89);

1.12.2 典型场景查询

  1. 查询周星星的学号：
    select stu_num from student_info where stu_name='周星星';
  2. 查询姓黄的学生：
    select * from student_info where stu_name like '黄%';
  3. 统计高三一班的人数：
    select count(*) as 一班人数 from student_info where stu_class='高三一班';
  4. 查询周星星的所有成绩（两种方式）：
-- 子查询方式
select * from student_score where stu_num = (select stu_num from student_info where stu_name='周星星');

-- 关联查询方式
select * from student_score a inner join student_info b on a.stu_num = b.stu_num where b.stu_name='周星星';
  1. 查询分数>90的学生姓名和学科：
    select b.stu_name, a.stu_course, a.stu_score from student_score a inner join student_info b on a.stu_num = b.stu_num where a.stu_score>90;
  2. 查询每个学生的总分：
    select stu_num, sum(stu_score) as 总分 from student_score group by stu_num;
  3. 查询总分前两名的学生姓名和分数：
    select b.stu_name, sum(a.stu_score) as 总分 from student_score a inner join student_info b on a.stu_num = b.stu_num group by a.stu_num order by 总分 desc limit 2;
  4. 查询各学科的平均分：
    select stu_course, avg(stu_score) as 平均分 from student_score group by stu_course;

1.13 SQL模式配置（sql_mode）

sql_mode用于控制MySQL的语法校验和数据校验规则，建议配置为严格模式：

-- 查看当前模式
show global variables like 'sql_mode';

-- 设置严格模式（需重启连接生效）
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

1.14 本章总结

知识点需要掌握的内容
库表创建创建和查看 database、创建表、查看表结构
字段管理增删字段、改字段类型、改字段名
增删查改写入单行和多行数据、全表查询和按条件查询、按条件更新和删除数据
数据过滤等值匹配、范围查询、多个值匹配
子查询单行、多行子查询、FROM 子查询
关联查询内连接、左连接、右连接
聚集函数和分组函数count()、avg()、sum()、max()、min()、group by
模糊查询like、regexp
排序正序、倒序、多个字段排序
分页查询limit、offset
组合查询union、union all
案例演示复习了常用的一些 SQL 语句

本文涵盖了日常工作中必备的SQL技能，从库表创建、字段操作到数据增删查改，再到高级查询（子查询、关联查询、分组聚合等），通过实例演示了各类场景的实现方式。掌握这些基础后，可进一步学习索引优化、事务管理等进阶内容，提升数据库操作效率与稳定性。

Tags:
Previous Article
Next Article

发表回复

Your email address will not be published. Required fields are marked *.

*
*

近期文章

站点统计

  • 0
  • 22
  • 57
  • 0
  • 59

归档

分类

Java (4) Linux (2) MySQL (44) 网站建设 (5) 读书笔记 (4)