【MySQL】表的增删改查（进阶）

一、数据库约束

1.1 约束类型

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

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

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

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

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

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

1.2 null约束

创建表时，可以指定某列不为空：
create table student (
id int not null,
name varcahr(20)
);

1.3 unique：唯一约束

指定id列为唯一的、不重复的：
create table student (
id int unique,
name varchar(20)
);

1.4 default：默认值约束

指定插入数据时，id列为空，默认值unkown：
 create table student (
 id int unique,
 name varchar(20)
 );

1.5 primary key：主键约束

指定id列为主键：
create table student (
id int primary key,
name varchar(20)
);

主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
create table student (
id int primary key auto_increment,
name varchar(20)
);

1.6 foreign key：外键约束

外键用于关联其他表的主键或唯一键

创建班级表classes，id为主键：
create table classes (
id int primary key,
name varchar(20)
);

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

二、表的设计

在这里插入图片描述

创建课程表：
create table course (
id int primary key,
name varchar(20)
);

创建考试成绩表（学生和成绩的中间表）：
create table score (id int primary key,
score decimal(3,1),
student_id int,course_id int,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id))；

三、新增

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

create table user (name varchar(20),
email varchar(20),
sex varchar(1)
);

insert into user (name) select name from student;

四、查询

4.1 聚合查询

4.1.1 聚合函数

在这里插入图片描述

统计班级共有多少同学
select count(*) from student;

统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
select count(qq_mail) from student;

统计数学成绩总分
select sum(math) from exam_result;

不及格 < 60 的总分，没有结果，返回 NULL
select sum(math) from exam_result where math < 60;

统计平均总分
select avg(chinese + math + english) 平均总分 from exam_result;

返回英语最高分
select max(english) from exam_result;

返回 > 70 分以上的数学最低分
select min(math) from exam_result where math > 70;

4.1.2 group by 子句

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

准备测试表及数据：
职员表，有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

显示平均工资低于1500的角色和它的平均工资
select role,avg(salary) from emp group by role having avg(salary)<1500;

4.2 联合查询

在这里插入图片描述
初始化测试数据：

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 其他条件;

查询“许仙”同学的 成绩：
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='许仙';

查询所有同学的总成绩，及同学的个人信息：
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;

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

查询出来的都是有成绩的同学，“老外学中文”同学 没有显示
select * from student stu join score sco on stu.id=sco.student_id;

学生表、成绩表、课程表3张表关联查询
select
	 stu.id,
	 stu.sn,
	 stu.NAME,
	 stu.qq_mail,
	 sco.score,
	 sco.course_id,
	 cou.NAME 
from
	 student stu
	 JOIN score sco ON stu.id = sco.student_id
	 JOIN course cou ON sco.course_id = cou.id 
order by
	stu.id;

4.2.2 外连接

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

语法：

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

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

查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示

-- “老外学中文”同学 没有考试成绩，也显示出来了
select * from student stu left join score sco on stu.id=sco.student_id;

-- 对应的右外连接为：
select * from score sco right join student stu on stu.id=sco.student_id;

-- 学生表、成绩表、课程表3张表关联查询
select
	 stu.id,
	 stu.sn,
	 stu.NAME,
	 stu.qq_mail,
	 sco.score,
	 sco.course_id,
	 cou.NAME 
from
	 student stu
	 LEFT JOIN score sco ON stu.id = sco.student_id
	 LEFT JOIN course cou ON sco.course_id = cou.id 
order by
	 stu.id;

4.2.3 自连接

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

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

先查询“计算机原理”和“Java”课程的id
select id,name from course where name='Java' or name='计算机原理';
再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息
select
	 s1.* 
from
	 score s1,
	 score s2 
where
	 s1.student_id = s2.student_id 
	 AND s1.score < s2.score 
	 AND s1.course_id = 1 
	 AND s2.course_id = 3;
 
也可以使用join on 语句来进行自连接查询
select
	 s1.* 
from
	 score s1
	 join score s2 on 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='不想毕业');

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

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

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

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

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

查询所有比“中文系2019级3班”平均分高的成绩信息：
select
	 avg( sco.score ) score 
from
	 score sco
	 join student stu on sco.student_id = stu.id
	 join classes cls on stu.classes_id = cls.id 
where
	 cls.name = '中文系2019级3班';

4.2.5 合并查询

🚒union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

🚒union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/151374.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

阿里云2核2G3M带宽服务器，新老用户同价99元/年！续费不涨价！

阿里云2核2G3M带宽服务器，新老用户同价99元/年！续费不涨价！

作为双11服务器中备受用户关注的一款&#xff0c;轻量服务器2核2G3M带宽优惠价87元一年的价格令人惊喜。不仅价格实惠&#xff0c;而且配置也十分出色。2核2G的配置足够应对一般网站和轻量级应用的需求&#xff0c;同时3M的带宽也能够保障数据的快速传输。对于个人网站、小型企…
阅读更多...
如何设计短域名系统

如何设计短域名系统

输入可能是 一个冗长的域名&#xff0c;过期时间和自定义的别名输出 自定义别名或者随机生成的短域名&#xff0c;在过期时间到来之前访问都可以被重定向到冗长的域名上约束条件 1.过期后就失效 2.短域名是唯一的 3.自定义短域名长度在7个字符&#xff08;不包含域名长度&am…
阅读更多...
代码随想录算法训练营第五十五天丨 动态规划part16

代码随想录算法训练营第五十五天丨 动态规划part16

583. 两个字符串的删除操作 思路 #动态规划一 本题和动态规划&#xff1a;115.不同的子序列 (opens new window)相比&#xff0c;其实就是两个字符串都可以删除了&#xff0c;情况虽说复杂一些&#xff0c;但整体思路是不变的。 这次是两个字符串可以相互删了&#xff0c;这…
阅读更多...
中国又一家手机企业赶超苹果，逼得苹果降价抢占3000元市场

中国又一家手机企业赶超苹果，逼得苹果降价抢占3000元市场

今年第44周的数据显示&#xff0c;苹果再次失去了中国手机市场第一名&#xff0c;这对于苹果希望iPhone15热销带动销量的目标受挫&#xff0c;难怪苹果在双十一竭尽全力降价抢占市场了。 苹果的iPhone15上市确实带动了一波销售&#xff0c;不过仅仅维持了两周&#xff0c;随后1…
阅读更多...
“具有分布式能源资源的多个智能家庭的能源管理的联邦强化学习”文章学习二

“具有分布式能源资源的多个智能家庭的能源管理的联邦强化学习”文章学习二

一、准备工作 本篇文章所使用的缩写总结如下表。 Markov决策过程&#xff08;MDP&#xff09;被定义为元组&#xff08;S&#xff0c;A&#xff0c;P&#xff0c;R&#xff0c;T&#xff09;&#xff0c;其中S和A是有限的有效状态集和所有有效动作的有限集。函数P : SA→ P(S)是…
阅读更多...
Java排序算法之归并排序

Java排序算法之归并排序

图解 归并排序是一种效率比较高的分治排序算法&#xff0c;主要分为两个步骤&#xff0c;分别为“分”和“并”。 分&#xff1a;将序列不断二分&#xff0c;直到每个子序列只有一个元素为止。 并&#xff1a;将相邻两个子序列进行合并&#xff0c;合并时比较两个子序列的元素…
阅读更多...
BUUCTF 被劫持的神秘礼物 1

BUUCTF 被劫持的神秘礼物 1

BUUCTF:https://buuoj.cn/challenges 题目描述&#xff1a; 某天小明收到了一件很特别的礼物&#xff0c;有奇怪的后缀&#xff0c;奇怪的名字和格式。小明找到了知心姐姐度娘&#xff0c;度娘好像知道这是啥&#xff0c;但是度娘也不知道里面是啥。。。你帮帮小明&#xff1…
阅读更多...
工作中积累的对K8s的就绪和存活探针的一些认识

工作中积累的对K8s的就绪和存活探针的一些认识

首先&#xff0c;我的项目是基于 Spring Boot 2.3.5 的&#xff0c;并依赖 spring-boot-starter-actuator 提供的 endpoints 来实现就绪和存活探针&#xff0c;POM 文件如下图&#xff1a; 下面&#xff0c;再让我们来看下与该项目对应的Deployment的YAML文件&#xff0c;如下…
阅读更多...
2023最新最全【虚幻4引擎】下载安装零基础教程

2023最新最全【虚幻4引擎】下载安装零基础教程

1、创建Epic Games账户 我们先打开浏览器&#xff0c;输入以下网址&#xff1a;unrealengine.com 随后点击【立即开始】 选择许可证类型&#xff0c;此处提供三种选项&#xff0c;分别是【游戏】、【非游戏】以及【私人定制】 第一类许可证适用于游戏和商业互动产品&#xff…
阅读更多...
Java代码实现贪吃蛇游戏

Java代码实现贪吃蛇游戏

一、创建新项目 创建一个新的项目&#xff0c;并命名。创建一个名为images的文件夹用来存放游戏相关图片。然后再在项目的src文件下创建一个com.xxx.view的包用来存放所有的图形界面类&#xff0c;创建一个com.xxx.controller的包用来存放启动的入口类(控制类)。如下所示&…
阅读更多...
msvcp140.dll文件的丢失原因以及五个解决办法

msvcp140.dll文件的丢失原因以及五个解决办法

在计算机使用过程中&#xff0c;我们常常会遇到一些错误提示&#xff0c;其中之一就是“msvcp140.dll丢失”。这个错误通常会导致某些应用程序无法正常运行。为了解决这个问题&#xff0c;我们需要采取一些措施来修复丢失的msvcp140.dll文件。本文将介绍五个处理办法&#xff0…
阅读更多...
【C++】深拷贝与浅拷贝

【C++】深拷贝与浅拷贝

1、深拷贝与浅拷贝 当我们对复杂类型(结构体或者类)的对象进行初始化时&#xff0c;如果将同类型的对象A赋值给同类型的对象B&#xff0c;此时就涉及深拷贝和浅拷贝的问题。 浅拷贝&#xff1a;简单的赋值拷贝操作。把类/结构体的对象的属性原封不动的赋值给另一个同类型的对…
阅读更多...
这可能测试全网最详细的Pytest教程

这可能测试全网最详细的Pytest教程

前言 关于自动化测试&#xff0c;这些年经历了太多的坑&#xff0c;有被动的坑&#xff0c;也有自己主动挖的坑&#xff0c;在这里做了一些总结。 主要思考总结下这些年来自动化测试过程中的一些基本的东西&#xff0c;例如何时进行自动化、如何自动化、或是怎么自动化我们的…
阅读更多...
论文绘图-机器学习100张模型图

论文绘图-机器学习100张模型图

在现代学术研究和技术展示中&#xff0c;高质量的图表和模型结构图是至关重要的。这尤其在机器学习领域更为显著&#xff0c;一个领域以其复杂的算法和复杂的数据结构而闻名。机器学习是一种使用统计技术使计算机系统能够从数据中学习和改进其任务执行的方法&#xff0c;而有效…
阅读更多...
cmake简单使用

cmake简单使用

简介 理论上&#xff0c;任意一个C程序都可以用g来编译。 但当程序规模越来越大时&#xff0c;一个工程可能有许多个文件夹和源文件&#xff0c;这时输入的编译命令将越来越长。通常&#xff0c;一个小型C项目可能含有十几个类&#xff0c;各类间还存在着复杂的依赖关系。其中…
阅读更多...
Python数据容器通用操作

Python数据容器通用操作

通用操作 1.数据容器可以从以下视角进行简单的分类2.数据容器特点对比3.数据容器的通用操作4.功能总览5.字符串大小比较 1.数据容器可以从以下视角进行简单的分类 是否支持下标索引 支持&#xff1a;列表、元组、字符串 --序列类型不支持&#xff1a;集合、字典 --非序列类型 …
阅读更多...
【C++干货铺】解密vector底层逻辑

【C++干货铺】解密vector底层逻辑

个人主页点击直达&#xff1a;小白不是程序媛 C系列专栏&#xff1a;C干货铺 代码仓库&#xff1a;Gitee 目录 vector介绍 vector的使用 vector的定义和使用 vector的空间增长问题 vector的增删查改 解密vector及模拟实现 成员变量 成员函数 构造函数 拷贝构造函数…
阅读更多...
分类预测 | Matlab实现PSO-LSTM-Attention粒子群算法优化长短期记忆神经网络融合注意力机制多特征分类预测

分类预测 | Matlab实现PSO-LSTM-Attention粒子群算法优化长短期记忆神经网络融合注意力机制多特征分类预测

分类预测 | Matlab实现PSO-LSTM-Attention粒子群算法优化长短期记忆神经网络融合注意力机制多特征分类预测 目录 分类预测 | Matlab实现PSO-LSTM-Attention粒子群算法优化长短期记忆神经网络融合注意力机制多特征分类预测分类效果基本描述程序设计参考资料 分类效果 基本描述 1…
阅读更多...
飞书开发学习笔记(七)-添加机器人及发送webhook消息

飞书开发学习笔记(七)-添加机器人及发送webhook消息

飞书开发学习笔记(七)-添加机器人及发送webhook消息 一.添加飞书机器人 1.1 添加飞书机器人过程 在群的右上角点击折叠按键…选择 设置 群机器人中选择 添加机器人 选择自定义机器人&#xff0c;通过webhook发送消息 弹出的信息中有webhook地址&#xff0c;选择复制。 安…
阅读更多...
【Linux专题】SFTP 用户配置 ChrootDirectory

【Linux专题】SFTP 用户配置 ChrootDirectory

【赠送】IT技术视频教程&#xff0c;白拿不谢&#xff01;思科、华为、红帽、数据库、云计算等等https://xmws-it.blog.csdn.net/article/details/117297837?spm1001.2014.3001.5502 红帽认证 认证课程介绍&#xff1a;红帽RHCE9.0学什么内容&#xff0c;新版有什么变化-CSDN…
阅读更多...
最新文章

Copyright @ 2022~2023