MySQL增删查改(进阶)

已于 2022-08-18 22:46:49 修改
阅读量259 收藏 4
点赞数 6
分类专栏： 数据库 文章标签： mysql 数据库 sql
于 2022-06-24 22:05:32 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_60565784/article/details/125443714
版权

前言
所谓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.2 UNIQUE: 唯一约束

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

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

1.3 DEFAULT:默认值约束

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

在这里插入图片描述

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

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

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

1.5 FOREIGN KEY：外键约束

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

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

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

创建学生表

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

创建成绩表

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

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

在这里插入图片描述
我创建了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='女')
);

在这里插入图片描述
在这里插入图片描述
我们发现它并没有检查,所以在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 子句可以对指定列进行分组查询。

我们发现下面的sql的group by有多个字段,正常情况我们使用一个就行了,除非有特殊要求
解释一下:先会按第一个字段分表,在刚刚分的表中按第二个字段继续分表…最后展现出每个小表的数据组合起来就形成最新的表展现

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

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

select name,class,max(sroce) from sroce group class ,name;

在这里插入图片描述

案例:


drop table if exists emp;
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),

在这里插入图片描述

我现在想要知道每种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);

4.2.1 内连接

语法:

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

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

  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;

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

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

注: 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 连接条件;

意思就是:

左连接就是以左表为基准表(就是左表的所有列的数据都得显示,即使为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. 查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示
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;

在这里插入图片描述

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

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

在这里插入图片描述
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);

在这里插入图片描述
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 = '英文';

在这里插入图片描述

在这里插入图片描述

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

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

在这里插入图片描述

4.2.6 内连 外连 集合图

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

相知-
关注
  • 6
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 4
    评论
专栏目录
如何用Mysql对表进行增删改查
chj156_的博客
08-15 1685
Mysql创建数据库和表 第一步：打开Mysql软件 第二步：输入自己的数据库登录密码********   第三步：输入"create database  day1;"   按回车键，创建一个day1的数据库，注意分号，Query  OK显示已经创建好了 第四步：输入"use day1;"，进去day1数据库，可以开始建表操作 第五步：在这里创建一个mine表： crea...
C#Windows窗体开发的图书管理系统，可远程，有文档，供学习参考使用，主要功能：涵盖数据库增删查改，登录注册，上传图片
05-25
1. **数据库操作**：系统与数据库紧密结合，实现了对图书数据的增删查改。这通常涉及到ADO.NET技术，通过连接字符串、SQL语句和数据适配器等组件来操作数据库，如SQLite、MySQL或SQL Server等。 2. **登录注册**：...
MySQL的增删改查（进阶）
question_mark的博客
10-24 370
约束 1.NULL约束 mysql> create table student( -> sn int not null, -> name varchar(20) -> ); Query OK, 0 rows affected (0.36 sec) mysql> insert into student values(null,'s1'); ER...
MySQL表的增删改查(进阶)
qq_45914985的博客
05-04 1055
数据库的约束 1.1 NULL 约束 创建表时, 可以指定某列不为空 例如: 1.2 UNIQUE 唯一约束 指定某列是唯一的, 不重复的 例如: 1.3 DEFAULT 默认值约束 例如: 指定插入数据时, name 列为空, 默认值为 ‘unknown’ 1.4 PRIMARY KEY 主键约束 相当于 NOT NULL 和 UNIQUE 的结合 对于整数类型的主键, 常搭配自增长 a...
MySQL4-增删改查进阶
kikajack的博客
05-03 536
1. 主键冲突 新增数据时，如果主键对应的数据已经存在，则会报错： ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' ON DUPLICATE KEY UPDATE 语法 通过 ON DUPLICATE KEY UPDATE 语法，可以在 INSERT 失败时自动转为执行 UPDATE 操作： INSERT INT...
MySQL增删查改进阶
2301_80026123的博客
05-26 1042
使用group by 指定某一个列，就会把列值相同的行回到一个组中，分完组后还可以针对每个组进行聚合查询。子表中的class引用自父表class中的class列class表中的数据制约啦stu表中的class。创建stu表，指定name这一列被UNIQUE修饰，表中插入两个‘张三’，所以报错，插入失败。SQL中进行条件查询，是针对列于列之间比较的，不能把比较行，如果把一个表自己与自己链接。插入：在子表中插入数据会触发对父表的查询，若没有对应的数据会报错。一个查询可以包括分组前的条件和分组后的过滤条件。
MySQL 增删查改进阶版
sjp151的博客
04-14 1277
增删查改进阶版数据库的约束NOT NULL 约束UNIQUE 约束DEFAULT 约束PRIMARY KEY 主键约束主键自增FOREIGN KEY 约束高阶查询数据库设计二级目录三级目录 数据库的约束 数据库的约束就是数据库在使用的时候，对于里面的数据能够提出要求和限制。可以借助约束来完成更好的体验。这里的约束都是针对列来操作的。 NOT NULL 约束 NOT NULL 就是某列不能插入空值。如果插入空值，就会报错。代码如下： create table student (id int not null
MySQL表的增删查改（进阶）
fengxia110的博客
03-02 421
数据库的约束，表之间的关系，新增，聚合查询，联合查询。
MySQL增删查改进阶版
weixin_64808654的博客
07-17 82
需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。select 字段 from 表1 别名1，表2，别名2 where 连接条件 and 其他条件；实际开发中，数据来自不同的表，所以需要多边联合查询，多表查询是对多张表的数据取笛卡尔积。当使用该操作符时，不会去掉结果集中的重复行.
Mysql表增删查改进阶
m0_51765966的博客
01-03 721
Mysql增删查改进阶一.数据库约束关系1.not null2.unique3.default4.primary key5.foreign key6.check二.新增三.查询 一.数据库约束关系 约束类型 作用 not null 某列不能存储null值 unique 某列的值唯一，该列每一行必须不同 default 没有给列赋值时的默认值 primary key 主键，唯一标识某列(或者多列的结合),便于快速找到表中的一条特定的记录.该约束
数据库增删查改进阶
yanweiding的博客
11-12 167
数据库约束 null约束 创建表时，可以指定某列不为空： id int not null, unique：唯一约束 指定sn列为唯一的、不重复的： sn int unique, default：默认值约束 指定插入数据时，name列为空，默认值unkown： name varchar(20) default ‘unkown’, primary key：主键约束 指定id列为主键： id int not null primary key, – 主键是 not null 和 unique的结合，可以不用 not
基于C++和mysql实现的学生成绩管理系统源码(含增删查改功能)+详细注释.zip
08-29
基于C++和mysql实现的学生成绩管理系统源码(含增删查改功能)+详细注释.zip基于C++和mysql实现的学生成绩管理系统源码(含增删查改功能)+详细注释.zip基于C++和mysql实现的学生成绩管理系统源码(含增删查改功能)+详细...
jsp+servlet+bootstrap+mysql实现的人员信息的增删查改管理系统.zip
06-14
2、本项目适合计算机相关专业(如计科、人工智能、通信工程、自动化、电子信息等)的在校学生、老师或者企业员工下载学习，也适合小白学习进阶，当然也可作为毕设项目、课程设计、作业、项目初期立项演示等。...
.NET实验大作业——网上书店
08-19
3. 增删查改（CRUD）操作：这是所有数据库驱动的应用程序的基础。创建（Create）新书籍，读取（Read）书籍详情，更新（Update）书籍信息，删除（Delete）不再售卖的书籍，这些功能在系统中都应得到实现。 4. 用户...
MySQL：SELECT 语句
Zachyy的博客
07-20 405
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
测试开发面试题---MySQL
m0_53302824的博客
07-22 946
索引是数据库管理系统中用于提高数据检索速度的数据结构，它可以帮助快速定位数据不需要扫描全表。提高查询速度；唯一性约束，确保数据不会重复；辅助排序和分组；减少磁盘IO。
mysql中ONLY_FULL_GROUP_BY报错
zzzzzucc的博客
07-21 322
禁用会话级别的 ONLY_FULL_GROUP_BY SET SESSION sql_mode = ‘’;– 或者禁用全局级别的 ONLY_FULL_GROUP_BY SET GLOBAL sql_mode = ‘’;在 MySQL 中，sql_mode是一个服务器变量，用于定义 SQL 语句的运行模式。是sql_mode中的一个选项，它影响 SQL 查询中GROUP BY子句的行为。
【MySQL】根据binlog日志获取回滚sql的一个开发思路
weixin_45385457的博客
07-19 527
不同客户端之间会交替追加在 binlog 中，需要通过 👆的 binlog 匹配流程来控制匹配。行，需要再次寻找 thread_id 相同的行，匹配到后执行上一个流程。在 binlog 中的线程 ID 记录为 thread_id。一个线程执行多个 sql 回滚到同一个文件可能带来的问题。对应 binlog 日志中的 thread_id=指定 mysql 客户端 开始时间和结束时间。打开 mysql 客户端 开始时间。关闭 mysql 客户端 结束时间。先匹配 thread_id 相同的。
MySQL中的CREATE EVENT 语句详解
最新发布
u011565038的博客
07-24 432
CREATE EVENT语句在 MySQL 中用于创建一个事件调度器（Event Scheduler）的事件。事件调度器允许你安排数据库任务（如查询、数据更新等）在将来的某个时间点自动执行，或者根据特定的时间间隔重复执行。这对于需要定期执行的任务特别有用，比如数据清理、报表生成等。
MySQL增删查改语句
04-01
MySQL是一种关系型数据库管理系统，它支持各种增删查改操作。下面是一些常见的MySQL增删查改语句： 1. 插入数据 插入数据的语法：INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value...
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

相知-

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值