MySQL表的增删改查（进阶）

核動力牛馬

已于 2024-08-13 15:50:19 修改

阅读量655

点赞数 15

分类专栏： Mysql 文章标签： mysql 数据库

于 2024-08-13 15:42:02 首次发布

本文链接：https://blog.csdn.net/m0_63272315/article/details/140999396

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1、数据库约束

1.1 约束类型

NOT NULL - 指示某列不能存储 NULL 值。

创建表时，可以指定某列不为空：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

UNIQUE - 保证某列的每行必须有唯一的值。

指定sn列为唯一的、不重复的：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

DEFAULT - 规定没有给列赋值时的默认值。

指定插入数据时，name列为空，默认值unkown：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT '无名氏',
   qq_mail VARCHAR(20)
);

如果插入name的字段为null会怎么样？

执行代码：

insert into student(id,name) values (1,null);

结论：用户指定的优先级要高于默认约束

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

主键约束的列非空且唯一

指定id列为主键：

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL PRIMARY KEY,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1

auto_increment的优点：

让数据库帮我们去维护主键的增长

在插入时最先找到最大值然后加1

生成一个新的值，作为新一个数据行的主键(id列）的值

create table if not exists student(
    id bigint primary key  auto_increment,
    sn varchar(20),
    name varchar(20)
);

可以指定主键的值只要确保主键的值不重复即可

在最大值的基础上加1 那么主键的值可能不连续

执行代码：

insert into student values (1,10001,'A');
insert into  student values (100,10002,'B');
insert into  student values (null,10003,'C');

一个主键同时可以包含多个列（符合主键）

create  table  stul(
    id bigint,
    name varchar(20),
    mail varchar(50),
    primary key (id,name)
);

insert into stul values (1,'A','aa@qq.com');
insert into stul values (1,'B','bb@qq.com');

结论：虽然编号相同，但姓名不同，可以插入成功！

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

外键用于关联其他表的主键或唯一键，语法：

foreign key (字段名) references 主表(列)

创建课表class

create table if not exists  class(
    id bigint primary key  auto_increment,
    name varchar(20)
);

创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键， classes_id为外键，关联班级表id

create table student(
    id bigint primary key  auto_increment,
    name varchar(20) not null ,
    class_id bigint,
    foreign key (class_id) references class(id)
);

class_id 关联 id

写入正确的数据可插入成功！

insert into student(id,name,class_id) values(1,'A',1),(2,'B',2),(3,'C',3);

CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句。

drop table if exists test_user;
create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
);

2、表的设计

三大范式：

2.1 一对一 (1:1)

一对一 (1:1) : 一对一关系是指两个表之间的一种关系，其中一个表的记录只与另一个表中的一个记录关联。例如，一个用户可能有一个唯一的地址。

实现方式：

共享主键：在两个表中使用相同的主键字段。
外键：在一个表中添加另一个表的主键作为外键。

示例：

假设我们有两个表 users 和 addresses。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE addresses (
    user_id INT PRIMARY KEY,
    address_line VARCHAR(100),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

2.2 一对多 (1:N)

一对多 (1:N):

一对多关系是指一个表的一条记录可以与另一个表中的多条记录相关联。例如，一个作者可以写多本书。

实现方式：

在“多”的那一边的表中添加一个外键，指向“一”的那张表的主键。

示例：

假设我们有两个表 authors 和 books。

CREATE TABLE authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

2.3 多对多 (N:M)

多对多 (N:M):

多对多关系是指两个表中的记录可以相互关联多个对方表中的记录。例如，学生可以选修多门课程，一门课程也可以被多名学生选修。

实现方式：

创建第三个关联表（也称为连接表或交叉引用表），该表至少包含两个外键，分别指向两个参与多对多关系的表的主键。

示例：

假设我们有两个表 students 和 courses。

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

3、新增

语法：

INSERT INTO table_name [(column [,column ...])] SELECT ...

案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
   id INT primary key auto_increment,
   name VARCHAR(20) comment '姓名',
   age INT comment '年龄',
   email VARCHAR(20) comment '邮箱',
 sex varchar(1) comment '性别',
 mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;

4、查询

4.1 聚合查询

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

案例：

COUNT

-- 统计班级共有多少同学
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;
-- 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
SELECT COUNT(qq_mail) FROM student;
-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math)

SUM

-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

AVG

-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

MAX

-- 返回英语最高分
SELECT MAX(english) FROM exam_result;

MIN

-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

4.1.2 GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

select column1, sum(column2), .. from table group by column1,column3;

案例：

准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）

create table emp(
 id int primary key auto_increment,
 name varchar(20) not null,
 role varchar(20) not null,
 salary numeric(11,2)
);
insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);

查询每个角色的最高工资、最低工资和平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role;

4.1.3 HAVING

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

显示平均工资低于1500的角色和它的平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<1500;

4.2 联合查询

在MySQL中，当两个表进行连接操作时，并且没有指定任何连接条件（即没有ON或USING子句），就会产生笛卡尔积（Cartesian product）。简单来说，如果一个表有m行，另一个表有n行，那么结果集将会有m * n行。

示例

假设我们有两个简单的表：Employees 和 Departments。

Employees 表:

id	name
1	Alice
2	Bob

Departments 表:

id	department_name
1	Sales
2	IT

如果我们执行一个没有连接条件的查询，比如：

SELECT *
FROM Employees, Departments;

这将会产生以下结果：

Employees.id	name	Departments.id	department_name
1	Alice	1	Sales
1	Alice	2	IT
2	Bob	1	Sales
2	Bob	2	IT

因为Employees表有2行记录，而Departments表也有2行记录，所以结果集中共有2 * 2 = 4行记录。每一条Employees表中的记录都与Departments表中的每一行记录配对了一次，形成了笛卡尔积。

准备数据：

CREATE TABLE `class`  (
  `class_id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '计算机系2019级1班');
INSERT INTO `class` VALUES (2, '中文系2019级3班');
INSERT INTO `class` VALUES (3, '自动化2019级5班');

-- ----------------------------
-- Table structure for course
-- ----------------------------

CREATE TABLE `course`  (
  `course_id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, 'Java');
INSERT INTO `course` VALUES (2, '中国传统文化');
INSERT INTO `course` VALUES (3, '计算机原理');
INSERT INTO `course` VALUES (4, '语文');
INSERT INTO `course` VALUES (5, '高阶数学');
INSERT INTO `course` VALUES (6, '英文');

-- ----------------------------
-- Table structure for student
-- ----------------------------

CREATE TABLE `student`  (
  `student_id` bigint NOT NULL AUTO_INCREMENT,
  `sn` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `mail` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `class_id` bigint NULL DEFAULT NULL,
  PRIMARY KEY (`student_id`) USING BTREE,
  UNIQUE INDEX `sn`(`sn` ASC) USING BTREE,
  INDEX `class_id`(`class_id` ASC) USING BTREE,
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '09982', '黑旋风李逵', 'xuanfeng@qq.com', 1);
INSERT INTO `student` VALUES (2, '00835', '菩提老祖', NULL, 1);
INSERT INTO `student` VALUES (3, '00391', '白素贞', NULL, 1);
INSERT INTO `student` VALUES (4, '00031', '许仙', 'xuxian@qq.com', 1);
INSERT INTO `student` VALUES (5, '00054', '不想毕业', NULL, 1);
INSERT INTO `student` VALUES (6, '51234', '好好说话', 'say@qq.com', 2);
INSERT INTO `student` VALUES (7, '83223', 'tellme', NULL, 2);
INSERT INTO `student` VALUES (8, '09527', '老外学中文', 'foreigner@qq.com', 2);

-- ----------------------------
-- Table structure for score
-- ----------------------------

CREATE TABLE `score`  (
  `score_id` bigint NOT NULL AUTO_INCREMENT,
  `student_id` bigint NULL DEFAULT NULL,
  `course_id` bigint NULL DEFAULT NULL,
  `score` decimal(5, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`score_id`) USING BTREE,
  INDEX `student_id`(`student_id` ASC) USING BTREE,
  INDEX `course_id`(`course_id` ASC) USING BTREE,
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 70.50);
INSERT INTO `score` VALUES (2, 1, 3, 98.50);
INSERT INTO `score` VALUES (3, 1, 5, 33.00);
INSERT INTO `score` VALUES (4, 1, 6, 98.00);
INSERT INTO `score` VALUES (5, 2, 1, 60.00);
INSERT INTO `score` VALUES (6, 2, 5, 59.50);
INSERT INTO `score` VALUES (7, 3, 1, 33.00);
INSERT INTO `score` VALUES (8, 3, 3, 68.00);
INSERT INTO `score` VALUES (9, 3, 5, 99.00);
INSERT INTO `score` VALUES (10, 4, 1, 67.00);
INSERT INTO `score` VALUES (11, 4, 3, 23.00);
INSERT INTO `score` VALUES (12, 4, 5, 56.00);
INSERT INTO `score` VALUES (13, 4, 6, 72.00);
INSERT INTO `score` VALUES (14, 5, 1, 81.00);
INSERT INTO `score` VALUES (15, 5, 5, 37.00);
INSERT INTO `score` VALUES (16, 6, 2, 56.00);
INSERT INTO `score` VALUES (17, 6, 4, 43.00);
INSERT INTO `score` VALUES (18, 6, 6, 79.00);
INSERT INTO `score` VALUES (19, 7, 2, 80.00);
INSERT INTO `score` VALUES (20, 7, 6, 92.00);

4.2.1 内连接

语法：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

案例：

（1）查询“许仙”同学的成绩

select sco.score from student stu inner join score sco on stu.id=sco.student_id
and stu.name='许仙';
-- 或者
select sco.score from student stu, score sco where stu.id=sco.student_id and
stu.name='许仙';

（2）查询所有同学的总成绩，及同学的个人信息：

-- 成绩表对学生表是多对1关系，查询总成绩是根据成绩表的同学id来进行分组的
SELECT
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sum( sco.score ) 
FROM
 student stu
 JOIN score sco ON stu.id = sco.student_id
GROUP BY
 sco.student_id;

4.2.2 外连接

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

语法：

-- 左外连接，表1完全显示 
select 字段名  from 表名1 left join 表名2 on 连接条件; 
-- 右外连接，表2完全显示 
select 字段 from 表名1 right join 表名2 on 连接条件;

案例：查询哪位同学没有考试成绩

 select * from student;

select * from class;

select * from student as stu left join  score as sco on stu.student_id = sco.student_id;

以左表（student)为基准：

4.2.3 自连接

自连接是指在同一张表连接自身进行查询。

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

select s1.*
from score as s1,
     score as s2
where s1.student_id = s2.student_id
  and s1.score < s2.score
  and s1.course_id = 1
  and s2.course_id = 3;

4.2.4 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询单行子查询：返回一行记录的子查询查询与“不想毕业” 同学的同班同学：

select * from student where classes_id=(select classes_id from student where
name='不想毕业');

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

案例：查询“语文”或“英文”课程的成绩信息

1. [NOT] IN关键字：

-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');

可以使用多列包含：

统计重复的字段数:

select student_id, course_id, score ,count(*)
from score
group by student_id, course_id, score;

查询重复数大于1条的记录：

select student_id, course_id, score
from score
group by student_id, course_id, score
having count(*) > 1;

添加外层查询：

select *
from score
where (student_id, course_id, score) in (select student_id, course_id, score
                                         from score
                                         group by student_id, course_id, score
                                         having count(*) > 1);

外层查询中的条件字段，与内层查询中的结果，一一做比较，如果相等则满足条件，其中只要有一个不相等则不满足条件。

2. [NOT] EXISTS关键字：
语法：

select * from 表名 where exists(select * from 表名);

exists后面括号中的查询语句，如果有结果返回，则执行外层的查询如果返回的是一个空结果集，则不执行外层的查询。

例子1：

select * from  student where  student_id=1;

select * from  student where  exists(select * from  student where  student_id=1);

exist()相当于 if语句的判断条件有结果返回true 否则返回 flase；

内层查询返回空结果集外层也返回空结果集，也可以说外层查询没有执行

例子2：

返回的结果非空

在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。

查询所有比“中文系2019级3班”平均分高的成绩信息：

1、先算出中文系2019级3班的平均分

select avg(score)
from student as stu,
     score as sco,
     class as cla
where sco.student_id = stu.student_id
  and cla.class_id = stu.class_id
  and cla.name = '中文系2019级3班';

2、再用表中学生的真实成绩和以上平均成绩作比较

select *
from score sc,
     (select avg(sc.score) as score
      from score sc,
           student st,
           class c
      where c.class_id = st.class_id
        and st.student_id = sc.student_id
        and c.name = '中文系2019级3班') as temp
where sc.score > temp.score;

4.2.5 合并查询

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

union

作用：合并多个查询结果到一个结果集中

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

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”的课程

-- 可以看到结果集中出现重复数据Java
select * from course where id<3
union all
select * from course where name='英文';

核動力牛馬

关注

15
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
MySQL表的增删改查（进阶）

需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。简单来说，如果一个表有。exists后面括号中的查询语句，如果有结果返回，则执行外层的查询如果返回的是一个空结果集，则不执行外层的查询。外层查询中的条件字段，与内层查询中的结果，一一做比较，如果相等则满足条件，其中只要有一个不相等则不满足条件。
复制链接

扫一扫