• MySQL增删查改(进阶)

    前言
    所谓MySQL进阶其实就是在初阶的基础上将那些基础的增删查改进行组合使用,然后在引进一些方法来学习的,所以说大家如果没有初阶的基础的话,请看一下这篇两篇文章数据库基础 , MySQL增删查改(初阶)

    文章目录

    MySQL增删查改(进阶)

    1.数据库的约束

    1.1约束类型

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

    1.2 NULL约束

    • NOT NULL - 指示某列不能存储 NULL 值。
    create table student (id int not null,name varchar(20),score decimal(3,1)) ;

    • 1

    在这里插入图片描述
    在这里插入图片描述

    1.2 UNIQUE: 唯一约束

    • UNIQUE - 保证某列的每行必须有唯一的值。
     create table student (id int unique,name varchar(20),score decimal(3,1)) ;

    • 1

    在这里插入图片描述
    在这里插入图片描述

    1.3 DEFAULT:默认值约束

    • DEFAULT - 规定没有给列赋值时的默认值。
    create table student (id int  ,name varchar(20),score decimal(3,1) default 99) ;

    • 1

    在这里插入图片描述

    在这里插入图片描述
    在这里插入图片描述

    1.4 PRIMARY KEY：主键约束

    • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标
      识，有助于更容易更快速地找到表中的一个特定的记录。
    create table student(id int primary key auto_increment,name varchar(20),score decimal(4,1));

    • 1

    在这里插入图片描述
    对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大
    值+1。
    在这里插入图片描述
    在这里插入图片描述
    主键约束是不能为NULL的,这里因为使用了auto_increment,所以默认自增了,然后我们又直接插入了name,score并没有插入id,此时也是因为auto_increment,自增了!
    在这里插入图片描述

    1.5 FOREIGN KEY：外键约束

    • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
    foreign key (字段名) references 主表(列)

    • 1

    简单来说外键约束是用来建立表与表之间的联系的,就是说你创建外键约束的那个字段必须得参考主表中是否存在,也就是相当于一个子集关系
    比如说

    我们先创建一个学生表(id,name),在创建一个成绩表(id,score),如果没有外键约束的话,成绩表中有的id在学生表中并不存在,所以我们得搞一个外键约束住成绩表中的id,当不存在时,这不能创建!!!

    创建学生表

     create table student(id int  primary key auto_increment,name varchar(20));

    • 1

    创建成绩表

    create table score(id int primary key ,score decimal(4,1),foreign key (id) references student(id));

    • 1

    通过外键约束可知成绩表是依附学生表而存在的!!!

    在这里插入图片描述
    我创建了1,2这两个学生,当我们给他们赋值成绩是没有问题的,当我们给3号学生赋值成绩就报错了,原因就是外键约束! 因为学生表中没有3号学生,所以当我们一开始插入时是不可以先插入成绩表的,得先插入学生表!!!

    PS: 当我们的主表别其他表通过外键约束时,我们是 不可以直接删除主表的!!
    在这里插入图片描述

    1.6 CHECK 约束(了解)

    MySQL使用时不报错，但忽略该约束：

    create table user_test (
id int,
name varchar(20),
sex varchar(1),
check (sex ='男' or sex='女')
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述
    在这里插入图片描述
    我们发现它并没有检查,所以在MySQL中无用!!

    2. 表的设计

    2.1 一对一

    在这里插入图片描述

    2.2 一对多

    在这里插入图片描述

    2.3 多对多

    在这里插入图片描述

    3.查询(select)

    3.1 聚合查询

    3.1.1 聚合查询

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

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

    案例:
    在这里插入图片描述

    3.1.2 分组查询:group by

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

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

    • 1

    案例:

    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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    在这里插入图片描述
    我现在想要知道每种role中的最高工资:
    在这里插入图片描述

    3.1.3 HAVING

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

    案例:
    显示平均薪资超过1000的role
    在这里插入图片描述

    4.2 联合查询(多表查询)

    实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
    在这里插入图片描述
    注意：关联查询可以对关联表使用别名。
    测试数据:

    create table classes (id int primary key auto_increment,name varchar(20),`desc` varchar(100));

create table student (id int primary key auto_increment,sn varchar(20),name varchar(20),qq_mail varchar(20),classes_id int);

create table course (id int primary key auto_increment,name varchar(20));

create table score (score decimal(3,1),student_id int,course_id int);

insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
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),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38

    4.2.1 内连接

    语法:

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

    • 1
    • 2

    注:多表查询时,写列的时候要写成[表名].[列名]
    如:

    1. 查找名字为"许仙"的同学的各科成绩
      思路:将学生表和成绩表和学科表笛卡尔积,通过where筛选出来
     select student.name as name,course.name as course,score.score as score from student,course,score where student.name='许仙'and course.id=score.course_id and student.id=score.student_id;

    • 1

    在这里插入图片描述
    2. 查找所有同学的总成绩,以及该同学的基本信息

    select student.id,student.name, sum(score.score) from student,score where student.id = score.student_id group by student.id;

    • 1

    注: group by只能表示同种组的一条记录
    在这里插入图片描述

    4.2.2 外连接

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

    语法:

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    意思就是:

    左连接就是以左表为基准表(就是左表的所有列的数据都得显示,即使为NULL),右连接就是以右表为基准表(就是右表的所有列的数据都得显示,即使为NULL),基准没有数据也要显示

    大家可以没理解意思,那么我们用例子说明!

    先举一个子连接的:

    1. 查找所有同学的每一科的成绩,和同学的相关信息

    思路 : 需要3张表 studednt表 score表 course表
    先对3张表进行笛卡尔积. 然后根据id进行筛选

    select student.id,student.name,course.name,score.score from student,score,course where student.id = score.student_id and course.id = score.course_id;

    • 1

    在这里插入图片描述
    在看一个左连接:

    1. 查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示
    select student.id,student.name,course.name,score.score from student left join score on student.id = score.student_id left join course on score.course_id = course.id;

    • 1

    在这里插入图片描述

    我们可以看出来内连接和外连接的细微区别了吧,其实这两者写法基本一样,只不过是显示的结果不一样,所有用内外连接这种名称来区分它!

    4.2.3 自连接

    自连接是指在同一张表连接自身进行查询。
    适用于行之间的比较,通过自连接将行之间的比较转化为列之间的比较

    如;显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
    在这里插入图片描述
    力扣:删除重复的电子邮箱
    大家可以尝试一下!

    4.2.4 子连接

    子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

    • 单行子查询：子查询只有一行
      示例1: 查询与“不想毕业” 同学的同班同学：
    select name from student where classes_id = (select classes_id from student where name = '不想毕业');

    • 1

    在这里插入图片描述

    • 多行子查询：返回多行记录的子查询
      查询"语文"或者"英文"课程的成绩信息

    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 != '英文');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述
    2.[NOT] EXISTS关键字：

    -- 使用exists
select * from score where exists (select score.course_id from course where (name = '语文' or name = '英文') and course.id = score.course_id);

-- 使用not exists
select * from score where not exists (select score.course_id from course where (name != '语文' and name != '英文') and course.id = score.course_id);

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述
    exists和in的区别

    In:是把外表和内表做Hash 连接，而exists 是对外表作loop 循环，每次loop循环再对内表进行查询。
    当查询两个表的大小相当时，用In 和 exists差别不大。
    如果两个表中一个表较小,一个表较大，那么子查询表大的用exists,子查询表小的用In，效率会高的。

    也就是说 IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况，这样效率会高的

    4.2.5 合并查询

    相当于把多个查询的结果集合合并成一个集合
    可以使用集合操作符union，union all

    示例1: 查询 id < 3 或者 名字为 "英语"的课程

    • union(和or相当)
    select * from course where id < 3 union select * from course where name = '英文';

    • 1

    在这里插入图片描述

    在这里插入图片描述

    如果两个查询结果中存在相同的记录,就会只保留一个
    如果不想去重,可以使用 union all即可.

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

    • 1

    在这里插入图片描述

    4.2.6 内连 外连 集合图

    在这里插入图片描述
    在这里插入图片描述

  • 相关阅读:
    SCA Nacos 服务注册和配置中心（一）
    LeetCode每日一题——754. 到达终点数字
    【JavaScript进阶之旅 ES6篇 第十章】操作原型的方法、super、4种遍历方式、Symbol
    SQLZOO：SELECT from NOBEL Tutorial
    【MAPBOX基础功能】20、mapbox获取当前已上图的所有的图层
    物联网中的毫米波雷达：连接未来的智能设备
    死锁问题及分析
    设置圆角+阴影
    C++ Reference: Standard C++ Library reference: C Library: cstdio: FILE
    ARM按键中断控制事件
  • 原文地址：https://blog.csdn.net/m0_60565784/article/details/125443714