文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >专栏 >MySQL表的增删改查（进阶）

MySQL表的增删改查（进阶）

作者头像
趙卋傑
发布于 2026-01-12 14:34:20
发布于 2026-01-12 14:34:20
810
举报

1.数据库约束

  • NOT NULL - 指示某列不能存储 NULL 值。
  • UNIQUE - 保证某列的每行必须有唯一的值。
  • DEFAULT - 规定没有给列赋值时的默认值。
  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标 识，有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句。

NOT NULL 非空约束

代码语言：javascript
复制
create table student (id int not null,name varchar(20));
insert into student (1,null);
insert into student values (null,null);

UNIQUE 唯一约束

代码语言：javascript
复制
create table student (id int unique,name varchar(20));
insert into student values (1,'张三');
insert into student values (2,'张三');
insert into student values (2,'张三');

DEFAULT 默认值约束

代码语言：javascript
复制
create table student (id int,name varchar(20) default 'unknown');
desc student;

PRIMARY KEY：主键约束 约定了这个表里的某一列为"身份标识"：非空和唯一。

代码语言：javascript
复制
--对于整数类型的主键，常配搭自增长auto_increment来使用。
--插入数据对应字段不给值时，使用最大值+1。

create table student (id int primary key auto_increment,name varchar(20));
insert into student (name) values ('张三'),('李四')('王五');
select * from student;

注意：不允许一个表中存在多个主键（多个身份标识）

但是数据库允许把多个列共同作为一个主键(联合主键)

FOREIGN KEY：外键约束 外键用于关联其他表的主键或唯一键 基本语句：foreign key (字段名) references 主表(列)

代码语言：javascript
复制
--  `desc`    创建班级表class，Id为主键：
create table class (id int primary key auto_increment,className varchar(20));

--创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，
--class为外键，关联班级表id
create table student(studentId int primary key,name varchar(20),classId int,foreign key(classId) references class(id));

再次往student中插入数据,就务必要确保插入的数据的classld的值,得在class表中存在.

代码语言：javascript
复制
--成功添加
insert into student values(11,'张三',1);
--失败，class表中没有id = 4
insert into student values(12,'张三',4);

如果父表中的某个记录,被子表引用了，此时就不能删除/修改父表中的对应记录了.

确保先删除子表,再删除父表.

2.表的设计

一对一的关系 一个学生，只能有一个账号.一个账号，只能被一个学生持有. 通过创建两个表，account表和student表，使用id将两个表联系起来

一对多关系 一个学生只能属于一个班级—个班级可以包含多个学生 通过创建两个表，student表和class表，通过班级id联系起来

多对多关系 一个学生可以选择多门课程，一门课程也可以被多个学生选中 此时就需要创建一个中间表，使用联合主键来引用课程表和学生表

代码语言：javascript
复制
--学生表
create table student(student_id int primary key,name varchar(20) not null);
--课程表
create table course(course_id int primary key,course_name varchar(20));
--中间表
create table connect_student_course(student_id int,course_id int,selection_date datetime,primary key(student_id,course_id),foreign key(student_id) references student(student_id),foreign key(course_id) references course(course_id));

说明：在这个例子中，connect_student_course 表是一个中间表，它使用两个外键（student_id 和 course_id）来引用 student 和 course 表。通过使用联合主键（student_id 和 course_id），我们可以确保每个学生和课程的组合在表中只出现一次。如果一个学生注册了多门课程，那么该学生的 student_id 将在 connect_student_course 表中多次出现，但每次都与不同的 course_id 关联。同样，如果一门课程被多个学生注册，那么该课程的 course_id 也将在 connect_student_course 表中多次出现，但每次都与不同的 student_id 关联。

3.新增

插入查询结果，将查询和新增结合起来 得到的结果集合, 例数/类型/顺序要和insert into后面的表相匹配. 列的名字是不要求相同的.

代码语言：javascript
复制
--表1
create table student1(student_id int,name varchar(20));
insert into student1 values(1,'张三'),(2,'李四'),(5,'王五');
--表二
create table student2(student_id int,name varchar(20));
insert into student2 select * from student1 where student_id > 3;

4.聚合查询

（1）聚合函数

COUNT([DISTINCT] expr) 返回查询到的数据的数量 注： count和()之间没有空格 如果是select列名的方式,遇到空值,不会进行计数

代码语言：javascript
复制
select count(*) from student1;
select count(student_id) from student1;

SUM([DISTINCT] expr) 返回查询到的数据的总和，不是数字没有意义 注： sum遇到null会直接跳过,不参与运算

代码语言：javascript
复制
select sum(math) from student1;

AVG([DISTINCT] expr) 返回查询到的数据的 平均值，不是数字没有意义

代码语言：javascript
复制
select avg(math) as avg_math from student1;

MAX([DISTINCT] expr) 返回查询到的数据的 最大值，不是数字没有意义 MIN([DISTINCT] expr) 返回查询到的数据的 最小值，不是数字没有意义

代码语言：javascript
复制
select max(math) as avg_math from student1;
select min(math) as avg_math from student1;

（2）GROUP BY子句

使用group by,指定一个列. 就会把列的值相同的行,归到一组中. 分完组之后，还可以针对每个组,分别进行聚合查询

代码语言：javascript
复制
 select * from student1 group by student_id;

搭配使用 order by ,where在此处也适用

分组之前的条件：

代码语言：javascript
复制
--查询每个岗位的平均薪资，但是除去张三
select student_id,name,avg(math) from student1 where name != '张三' group by student_id;

（3）HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING

分组之后的条件：

代码语言：javascript
复制
select student_id,name,avg(math) from student1 group by student_id having avg(math) > 85;

同时包含分组前的条件和分组后的条件：

代码语言：javascript
复制
--查询每个id的数学平均成绩，并且排除张三，也排除平均成绩小于85的
select student_id,name,avg(math) from student1 where name != '张三' group by student_id having avg(math) > 85;

（4）联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

代码语言：javascript
复制
select * from student,class;

相当于将两个表的数据进行了排列组合

代码语言：javascript
复制
select * from student,class where student.student_id = class.id;

注意：这里的比较要将表名带上（student.），避免二义性

学生表

代码语言：javascript
复制
--创建学生表
create table student(id int primary key auto_increment,sn varchar(20),name varchar(20),qq_email varchar(20),classes_id int);
--添加数据
insert into student values(2,9346,'mack','mack@qq.com',2),(3,9456,'cici','cici@qq.com',2),(4,8128,'lisa',null,1);

课程表

代码语言：javascript
复制
--创建课程表
create table course (id int primary key auto_increment,name varchar(20));
--添加数据
insert into course values(1,'Java'),(2,'中国传统文化'),(3,'计算机原理'),(4,'语文'),(5,'高数'),(6,'英语')

分数表

代码语言：javascript
复制
--创建分数表
create table score (score decimal(3,1),student_id int,course_id int);
--添加数据
insert into score(score, student_id, course_id) values (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),(60, 2, 1),(59.5, 2, 5),(33, 3, 1),(68, 3, 3),(99, 3, 5),(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6);

此时我们就构成了学生-班级一对多的关系和学生课程多对多的关系

内连接
代码语言：javascript
复制
--基本语法
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

查询lily的实际成绩

第一步：将学生表和分数表进行笛卡尔积

代码语言：javascript
复制
select * from student,score;

第二步：指定连接条件，筛选无用数据

代码语言：javascript
复制
select * from student,score where student.id = score.student_id;

第三步：根据需要再筛选

代码语言：javascript
复制
select * from student,score where student.id = score.student_id and name = 'lily';

第四步：再精简信息

代码语言：javascript
复制
select student.name,score.score from student,score where student.id = score.student_id and name = 'lily';

多表查询还可以通过join on 的写法来完成

代码语言：javascript
复制
select * from student join score;
select * from student join score on student.id = score.student_id;
select * from student join score on student. id = score. student_id and student. name = 'lily';
select name,score from student join score on student. id = score.student_id and student. name = 'lily';

查询所有同学的总成绩及个人信息

代码语言：javascript
复制
select student.id,student.name,score.score,sum(score.score) from student,score where student.id = score.student_id group by student.id;
--join on写法
select student.id,student.name, stm(score.score)from student join score on, student. id = score.student_id group by student.id;

查询所有同学的成绩，及同学的名字，课程名字和分数

代码语言：javascript
复制
--三个表进行笛卡尔积
select * from student,course,score;
--指定连接条件
select * from student,course,score where student.id = score.student_id and course.id = score.course_id;
--把列进行精简
select student.name as studentName,course.name as courseName,score.score from student,course,score where student.id = score.student_id and course.id = score.course_id;
外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完 全显示我们就说是右外连接。 外连接不支持from多个表

代码语言：javascript
复制
-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

此时两个表的数据不是一一对应的，相应的外连接内连接就会产生不同的效果

内连接：

左外连接：

说明：

左外连接就是以左表为基准能够确保左表中的每个记录都出现在最终结果里. 如果左表中的记录,在右表中没有对应的记录,此时就会把右表中的相关字段,填成 NULL

右外连接：

说明：

右外连接,是以右表为基准，然后确保右表中的每个记录都出现在最终结果里如果右表中的某个记录在左表里没有对应的相关字段,填成NULL

自连接

自连接是指在同一张表连接自身进行查询，也就是自己和自己进行笛卡尔积

显示所有“计算机原理”成绩比“Java”成绩高的成绩信息：

代码语言：javascript
复制
--自连接   需要指定别名
select * from score as s1, score as s2;
--筛选
select * from score as s1, score as s2 where s1.student_id = s2.student_id;
--精简
select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 1 and s2.course_id = 3 and s1.score < s2.score;
子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询 如果当前发现,要查询的条件,是针对两行,而不是两列就可以考虑使用自连接,进行转换.

单行子查询：返回一行记录的子查询

查询与“lily” 同学的同班同学：

代码语言：javascript
复制
select * from student where classes_id=(select classes_id from student where name = 'lily');

多行子查询：返回多行记录的子查询

查询“Java”或“英文”课程的成绩信息:

代码语言：javascript
复制
--in查询
select * from student where classes_id in (select id from course where name = 'Java' or name = '英语');
--exist查询
SELECT s.*  
FROM student s  
WHERE EXISTS (  
    SELECT 1  
    FROM course c  
    WHERE c.id = s.classes_id  
    AND (c.name = 'Java' OR c.name = '英语')  
);
合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION 和UNION ALL时，前后查询的结果集中，字段需要一致。

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。 案例：查询id小于3，或者名字为“英文”的课程：

代码语言：javascript
复制
select * from course where id<3 
union
select * from course where name='英语';
-- 或者使用or来实现
select * from course where id<3 or name='英语';

union all 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。 案例：查询id小于3，或者名字为“Java”的课程

代码语言：javascript
复制
-- 可以看到结果集中出现重复数据Java
select * from course where id<3 
union all
select * from course where name='英语';
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2024-05-24，如有侵权请联系 cloudcommunity@tencent.com 删除
数据库
mysql
select
连接
数据

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

数据库
mysql
select
连接
数据
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
目录
  • 1.数据库约束
  • 2.表的设计
  • 3.新增
  • 4.聚合查询
    • （1）聚合函数
    • （2）GROUP BY子句
    • （3）HAVING
    • （4）联合查询
      • 内连接
      • 外连接
      • 自连接
      • 子查询
      • 合并查询
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2026 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论