[03]MySQL表的增删改查【进阶】

news/2024/4/25 13:32:15/文章来源:https://blog.csdn.net/qq_33866936/article/details/130344152

目录

1.数据库约束

约束类型

null约束

unique:唯一约束

default:默认值约束

primary key:主键约束

foreign key:外键约束

语法

示例

注意

补充

2.表的设计

关系模型

①没有关系

②一对一关系

③一对多关系

④多对多关系

3.新增

语法

4.查询

聚合查询

聚合函数

group by

having关键字

联合查询

问题引入

内连接

外连接

自连接

子查询

合并查询

1.数据库约束

约束类型

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

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

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

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

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

null约束

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student(id INT NOT NULL,   # 指定id列不可为nullsn INT,  name VARCHAR(20),  qq_mail VARCHAR(20)
);

unique:唯一约束

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

create table student(id bigint not null unique,    # 指定id列不为空,且各个数据不能一样name varchar(255)
);

default:默认值约束

当没写数据时,填充默认值

create table student(id bigint,name varchar(255) default '无名氏'
);

primary key:主键约束

指定列为主键，是NOT NULL 和 UNIQUE 的结合

一个表里只能有一个主键,但可以用多个列合并成一个主键(复合主键),意味着多个列组成的组合不能重复

create table student(id bigint primary key auto_increment,  # 把当前列设置为主键,自增(且非空和唯一)name varchar(255)
)

foreign key:外键约束

外键用于关联其他表的主键

语法

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

示例

先创建班级表,并新建一些班级,例如id=1,name='java78'

# 班级表
create table classes()id bigint primary key auto_increment,name varchar(255)
);

创建学生表

create table student(id bigint primary key auto_increment,name varchar(255),classesId bigint not null,foreign key (classesId) references classes(id)    # 创建主外键关系
);

当新创建学生作为java78班的学生

insert into student (name,classesId) values ('张三',1);
# 后面的1是java78班的id,以后班级改名的名字,id也不会变,不影响这个学生

当新建学生的班级id不存在,则会报违反外键约束

insert into student (name,classesId) values ('李四',100);
# 提示违反外键约束

此时删除id为1的班级

delete from classes where id=1;
# 报错,因为有别的学生跟他关联了,除非先把所有有关联的学生全删了,才能删这个班级

注意

删除主表数据时，有外键约束会删除失败

补充

①多个约束可以写在一起

②auto_increment表示自增(以当前列最大值开始增,中间可以人为跳过某些值)

③对于每个表建议都设置主键,且是bigint类型和自增

2.表的设计

和设计类思路一样,是一个抽象的过程

根据需求文档或现实世界中的实体,转换成具体的一张张表

表对应Java的类,字段对应Java的类属性

关系模型

设计数据库时有一些固定的关系模型:

①没有关系

②一对一关系

# 用一张表把所有的字段全部包含里面,例如:
user(id,name,account_name,password)# 按业务把相关的字段拆分出两个表,再为它们建立主外键关系,例如:
user(id,name,account_id)# 和下表建立主外键关系
account(id,account_name,password)

③一对多关系

# 对于多的一方,可以和主表建立主外键关系
student(id,name,classes_id)    # classes_id和下表建立主外键关系
classes(id,name)

④多对多关系

student(id,name)    # 存学生信息
course(id,name)    # 存课程信息
score(id,score,student_id,course_id)    # 记录成绩,并匹配学生和课程

3.新增

插入查询结果

可以把一个查询结果插入到另一个表里

语法

insert into 表名 (各种列名) select (对应的列名) from 另一个表名;

前提是表必须存在

4.查询

聚合查询

聚合函数

①count(列名):统计该列有几个数据,其中null会被忽略

-- 统计班级共有多少同学
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;-- 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
SELECT COUNT(qq_mail) FROM student;

可以后面加where限定条件

②sum(列名):求该列所有数据的总和

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

③avg(各个列名):求各个列的平均数

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

可以用as别名

④max(列名)

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

⑤min(列名)

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

⑥注意

可以后面加where限定条件

可以用as别名

group by

语法

select 要分组的字段,聚合函数(需要操作的列名),... from table [where] group by 要分组的字段,列名3;

问题引入

# 不同角色工资没有可比性
insert into emp(name, role, salary) values('马云','服务员', 1000.20),('马化腾','游戏陪玩', 2000.99),('孙悟空','游戏角色', 999.11),('猪无能','游戏角色', 333.5),('沙和尚','游戏角色', 700.33),('隔壁老王','董事长', 25000.66);# 可以按相同角色,来查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) 
from emp 
group by role;# 还可以再进行排序
select role,max(salary),min(salary),avg(salary) 
from emp 
group by role 
order by salary DESC;# 过滤条件:不包含角色为游戏角色的
select role,max(salary),min(salary),avg(salary) 
from emp 
where role!='游戏角色' 
group by role 
order by salary DESC;

having关键字

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

# 按相同角色,来查询每个角色的平均工资,
# 其中不算上游戏陪玩,然后对于计算出来的平均值大于20000的不要
select role,avg(salary) 
from emp 
where role!='游戏陪玩' 
group by role 
having avg(salary)<20000;

where跟在from 表名后面

having跟在having后面

联合查询

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

问题引入

# 有两个表
student(id,name,class_id)1  张三  12  李四  2classes(id,name)1  java782  java23

 联合查询就是做两个表的笛卡尔积

# 联合查询
select * from student,classes;# 结果是笛卡尔积
(id,name,class_id) (id, name)1  张三    1       1  java78    √有效数据1  张三    1       2  java23    ×无效数据2  李四    2       1  java78    ×无效数据2  李四    2       2  java23    √有效数据

主外键对应的值相等时,数据才有效,则要用where匹配student.class_id=classes_id

select * 
from student,classes 
where student.class_id=classes_id

里面的student.class_id意思是student里面的id,当有名字相同的列时候,可以这样区分

同时还可以用别名(别名可以省略as关键字)

内连接

语法

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

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

示例①

查询许仙学生成绩

①确定表

②做笛卡尔积

③确认连接条件:student.id=score.student_id

select * from student stu,score sco where stu.id=sco.student_id;

④加入查询条件

select * from student stu,score sco where stu.id=sco.student_id and [stu.]name='许仙';

⑤精简查询结果

select stu.id,stu.name,sco.course_id,sco.score

from student stu,score sco

where stu.id=sco.student_id and [stu.]name='许仙';

示例②

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

①确定表

②做笛卡尔积

③确认连接条件:stu.id=sco.student_id

select * from student stu,score sco where stu.id=sco.student_id;

④加入条件查询

⑤精简查询结果

select stu.id,stu.name,stu.sn,stu.email, sum(sco.score) as 总分    # 精简查询结果

from student stu,score sco         # 确定表做笛卡尔积

where stu.id=sco.student_id        # 确认连接关系

group by stu.id;                # 分组依据

外连接

外连接分为左外连接和右外连接。

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

语法

-- 左外连接，表1完全显示,右表若没匹配到数据,则以null填充

select 字段名  from 表名1 left join 表名2 on 连接条件;

-- 右外连接，表2完全显示

select 字段 from 表名1 right join 表名2 on 连接条件;

自连接

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

案例

显示所有“计算机原理”成绩比“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_idAND s1.score < s2.scoreAND s1.course_id = 1AND s2.course_id = 3;-- 也可以使用join on 语句来进行自连接查询
SELECT s1.*
FROM score s1
JOIN score s2
ON s1.student_id = s2.student_idAND s1.score < s2.scoreAND s1.course_id = 1AND s2.course_id = 3;

子查询

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

单行子查询

返回一行记录的子查询

示例

查询与“不想毕业” 同学的同班同学：

select * from student
where classes_id = (select classes_id from student where name='不想毕业');
# 子句查询"不想毕业"同学的班级id,再查询同id的班级
# 查询结果包含"不想毕业"同学

多行子查询

返回多行记录的子查询

示例

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

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

合并查询

        在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 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

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

跟union完全一样,唯一区别不去重

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

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

相关文章

Handbook of MusicPsychology 音乐心理学手册 ( 多纳德·霍杰斯 Donald.A.Hodges） 笔记

Handbook of MusicPsychology 音乐心理学手册 ( 多纳德·霍杰斯 Donald.A.Hodges） 笔记

由两个以上的音组成的结合音&#xff0c;除了该声波的波形&#xff0c;人耳会另外脑补出不存在的波形 频率相距较远的一些音与频率相距较近的一些音&#xff0c;前者累加的响度比后者要大 除了泛音部分&#xff0c;音的起声部分也是音色辨别的关键 音高、响度、音色、时值&a…
阅读更多...
LINUX的系统管理与维护命令

LINUX的系统管理与维护命令

文章目录 一、LINUX的系统管理与维护命令总结 一、LINUX的系统管理与维护命令 - Linux ls命令:显示指定工作目录下的内容 Linux pwd命令:显示当前工作目录 Linux cd命令:切换工作目录 Linux date命令:显示或设置系统时间 Linux su命令:切换用户 Linux clear命令:清除屏幕 Li…
阅读更多...
Java编程设计语言-集合类

Java编程设计语言-集合类

API(application programming interface)是JDK的重要组成部分&#xff0c;API提供了Java程序与运行它的系统软件&#xff08;Java虚拟机&#xff09;之间的接口&#xff0c;可以帮助开发者方便、快捷地开发Java程序 集合在程序设计中是一种重要的是数据结构&#xff0c;Java中提…
阅读更多...
Semantic Kernel 知多少 | 开启面向 AI 编程新篇章

Semantic Kernel 知多少 | 开启面向 AI 编程新篇章

在 ChatGPT 火热的当下, 即使没有上手亲自体验&#xff0c;想必也对 ChatGPT 的强大略有耳闻。当一些人在对 ChatGPT 犹犹豫豫之时&#xff0c;一些敏锐的企业主和开发者们已经急不可耐地开展基于 ChatGPT 模型 AI 应用的落地探索。 因此&#xff0c;可以明确预见的是&#xf…
阅读更多...
Java+Angular开发的医院信息管理系统源码，系统部署于云端，支持多租户

Java+Angular开发的医院信息管理系统源码，系统部署于云端，支持多租户

云HIS系统源码&#xff0c;采用云端SaaS服务的方式提供 基于云计算技术的B/S架构的云HIS系统源码&#xff0c;采用云端SaaS服务的方式提供&#xff0c;使用用户通过浏览器即能访问&#xff0c;无需关注系统的部署、维护、升级等问题&#xff0c;系统充分考虑了模板化、配置化、…
阅读更多...
系统分析师之软件工程（十二）

系统分析师之软件工程（十二）

目录 一、 软件开发生命周期 1.1 开发阶段工作细分 二、软件开发模型 2.1 瀑布模型 2.2 原型模型 2.3 增量模型与螺旋模型 2.4 V模型 2.5 喷泉模型 2.6 快速应用开发模型RAD 2.7 构件主装模型 2.8 统一过程 2.9 敏捷方法 三、逆向工程 四、净室软件工程 一、 软件…
阅读更多...
斯坦福| ChatGPT用于生成式搜索引擎的可行性

斯坦福| ChatGPT用于生成式搜索引擎的可行性

文&#xff5c;智商掉了一地 随着 ChatGPT 在文本生成领域迈出了重要一步&#xff0c;Bing 浏览器也接入了聊天机器人功能&#xff0c;因此如何保证 Bing Chat 等搜索引擎结果的精确率和真实性也成为了搜索领域的热门话题之一。 当我们使用搜索引擎时&#xff0c;往往希望搜索结…
阅读更多...
电子阅读器市场角力，AI成为关键变量

电子阅读器市场角力，AI成为关键变量

配图来自Canva可画 近年来&#xff0c;随着国家“书香型社会”建设政策的出台&#xff0c;公众的阅读需求正在逐年增加&#xff0c;各类读书产品和读书活动&#xff0c;也如同雨后春笋般涌现&#xff0c;人们的阅读体验日益得到丰富。比如&#xff0c;昨天世界读书日举行的“不…
阅读更多...
更简单的存取Bean方式-@Bean方法注解

更简单的存取Bean方式-@Bean方法注解

1.Bean方法存储 类注解是添加在某个类上的,那么方法注解是添加在某个方法前的 public class UserBeans {Beanpublic User user1(){User user new User();user.setUid(001);user.setUname("zhangsan");user.setAge(19);user.setPassword("123123");retur…
阅读更多...
【分布式搜索引擎ES01】

【分布式搜索引擎ES01】

分布式搜索引擎ES 分布式搜索引擎ES1.elasticsearch概念1.1.ES起源1.2.倒排索引1.2.1.正向索引1.2.2.倒排索引 1.3.es的一些概念1.3.1.文档和字段1.3.2.索引和映射1.3.3.mysql与elasticsearch 1.4.1安装es、kibana、IK分词器1.4.2扩展词词典与停用词词典 2.索引库操作2.1.mappi…
阅读更多...
Springcloud连接nacos集群，nacos地址配置为nginx，报错：requst nacos server failed

Springcloud连接nacos集群，nacos地址配置为nginx，报错：requst nacos server failed

先说下版本&#xff1a; Spring cloud&#xff1a; Hoxton.SR12 spring.cloud.alibaba&#xff1a; 2.2.9.RELEASE spring.boot&#xff1a; 2.3.12.RELEASE Linux Centos7 nacos-server&#xff1a;2.1.0 nginx&#xff1a; 1.20.2 环境说明&#xff1a; nacos正常搭建三个集…
阅读更多...
supervisor安装

supervisor安装

说明 Supervisor翻译过来是监管人&#xff0c;在Linux中Supervisor是一个进程管理工具&#xff0c;当进程中断的时候Supervisor能自动重新启动它。可以运行在各种类Linux/unix的机器上&#xff0c;supervisor就是用Python开发的一套通用的进程管理程序&#xff0c;能将一个普通…
阅读更多...
【虚幻引擎】UE4/UE5科大讯飞文字合成语音

【虚幻引擎】UE4/UE5科大讯飞文字合成语音

一、链接地址 链接&#xff1a;https://pan.baidu.com/s/15Qoc48x3DLpw4eW1qHXInQ 提取码&#xff1a;jqpx B站视频链接&#xff1a;https://space.bilibili.com/449549424?spm_id_from333.1007.0.0 二、案例介绍 第一步&#xff1a;首先进入讯飞开放平台注册一个账号&…
阅读更多...
ThreadPoolExecutor源码阅读流程图

ThreadPoolExecutor源码阅读流程图

1.创建线程池 public ThreadPoolExecutor(int corePoolSize,int maximumPoolSize,long keepAliveTime,TimeUnit unit,BlockingQueue<Runnable> workQueue) {this(corePoolSize, maximumPoolSize, keepAliveTime, unit, workQueue,Executors.defaultThreadFactory(), def…
阅读更多...
Automa函数学习(三)

Automa函数学习(三)

从变量中获取数据 当我们想要用automa获取文本标签获取到网页的文本内容后,想要将获取到的文本内容当做参数往后面的标签里进行传递时就需要用到automa提供的传参格式 {{ variables.自定义参数名}} 举例: 先建立打开百度首页工作流 前面自定义的变量名为text,所以这里参数拼接…
阅读更多...
开放式耳机有什么好处，盘点几款性能不错的开放式耳机

开放式耳机有什么好处，盘点几款性能不错的开放式耳机

随着人们对生活质量要求的提高&#xff0c;大家在运动的时候都喜欢戴上耳机&#xff0c;享受运动的乐趣。但是传统耳机戴久了之后就会出现耳朵酸痛的情况&#xff0c;这是因为传统耳机佩戴方式是通过空气振动来传递声音&#xff0c;而人在运动时就会伴随着大量的汗水&#xff0…
阅读更多...
深入学习RabbitMQ五种模式(一)

深入学习RabbitMQ五种模式(一)

1.安装erlang 下载otp_win64_25.3.exe https://www.erlang.org/downloads erlang安装完成&#xff0c;需要配置erlang环境变量 ERLANG_HOMEE:\software\Erlang OTPPATH%PATH%;%ERLANG_HOME%\bin; 2.安装RabbitMQ 下载rabbitmq-server-3.11.13.exe https://www.rabbitmq.com/dow…
阅读更多...
【Python 协程详解】

【Python 协程详解】

0.前言 前面讲了线程和进程&#xff0c;其实python还有一个特殊的线程就是协程。 协程不是计算机提供的&#xff0c;计算机只提供&#xff1a;进程、线程。协程是人工创造的一种用户态切换的微进程&#xff0c;使用一个线程去来回切换多个进程。 为什么需要协程&#xff1f; …
阅读更多...
IntelliJ IDEA 接入ChatGPT （免费，无需注册）生产力被干爆了！

IntelliJ IDEA 接入ChatGPT （免费，无需注册）生产力被干爆了！

IntelliJ IDEA 接入ChatGPT 前言 : 今天给大家介绍一款好用的 IntelliJ IDEA ChatGPT 插件 可以帮助我们写代码&#xff0c;以及语言上的处理工作&#xff0c;以及解释代码。让我们的生产力大大提高&#xff01; 一. ChatGPT-Plus 功能介绍 支持最新idea版本AI询问功能,写好…
阅读更多...
Adobe Photoshop 软件下载

Adobe Photoshop 软件下载

Adobe Photoshop&#xff0c;简称“PS”&#xff0c;是由Adobe Systems开发和发行的图像处理软件。Photoshop主要处理以像素所构成的数字图像。 时至今日&#xff0c;Adobe Photoshop 已经成为当今世界上最流行、应用最广泛的图像处理软件。不但设计专业的学生要系统的学习这个…
阅读更多...
推荐文章
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站