MySQL表的增删改查（进阶）

1. 数据库约束

1.1 约束类型  

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

1.2 NULL约束 

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

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

1.3 UNIQUE：唯一约束 

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

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

1.4 DEFAULT：默认值约束  

该约束是：规定没有给列赋值时的默认值。如：指定插入数据时，name列为空，默认值unkown：

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

1.5 PRIMARY KEY：主键约束  

指定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 。

-- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL

id INT PRIMARY KEY auto_increment,

1.6 FOREIGN KEY：外键约束 

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

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

案例：

  • 创建班级表classes，id为主键：
-- 创建班级表，有使用 MySQL 关键字作为字段时，需要使用 `` 来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR ( 20 ),
   `desc` VARCHAR ( 100 )
);  
  • 创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，classes_id为外键，关联班级表id：
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
    id INT PRIMARY KEY auto_increment,
    sn INT UNIQUE,
    name VARCHAR ( 20 ) DEFAULT 'unkown' ,
    qq_mail VARCHAR ( 20 ),
    classes_id int ,
FOREIGN KEY (classes_id) REFERENCES classes(id)
);

1.7 CHECK约束（了解） 

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

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 一对一  

 

2.2 一对多   

2.3 多对多     

 

 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 聚合查询  

4.1.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;
  • 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（薪水）
  •  查询每个角色的最高工资、最低工资和平均工资（对角色进行分组）
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 联合查询  

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

 

注意：关联查询可以对关联表使用别名。 

 初始化测试数据：

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 ）查询 “ 许仙 ” 同学的 成绩
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 ;
（ 3 ）查询所有同学的成绩，及同学的个人信息：
-- 查询出来的都是有成绩的同学， “ 老外学中文 ” 同学 没有显示
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= ' 不想毕业 ' );
  • 多行子查询：返回多行记录的子查询 

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

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

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

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

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

相关文章

Go并发编程（上）

Go并发编程（上）

目录 一、go语言当中的协程 二、MPG模型介绍 三、Goroutine 的使用 3.1 协程的开启 3.2 优雅地等待子协程结束 四、捕获子协程的panic 五、管道Channel 5.1、认识管道 5.2、Channel的遍历和关闭 5.3 、用管道实现生产者消费者模型 5.4、Channel一些使用细节和注意事…
阅读更多...
交叉编译中常见错误解决方法

交叉编译中常见错误解决方法

目录 程序运行基础知识 编译程序时去哪找头文件&#xff1f; 链接时去哪找库文件&#xff1f; 运行时去哪找库文件&#xff1f; 运行时不需要头文件&#xff0c;所以头文件不用放到板子上 常见错误的解决方法 头文件问题 库文件问题 运行问题 交叉编译程序的万能命令 …
阅读更多...
开源论道 源聚一堂@COSCon

开源论道 源聚一堂@COSCon

自2015年以来&#xff0c;开源高峰论坛一直是中国开源年会中的传统亮点项目。本次在COSCon23 大会期间的高峰圆桌会&#xff0c;于2023年10月29日在成都高新区的菁蓉汇召开。 本次高峰圆桌上&#xff0c;我们特别邀请了20 位来自企业&#xff0c;基金会和社区的专家和领袖参加讨…
阅读更多...
基于单片机设计的智能风扇(红外线无线控制开关调速定时)

基于单片机设计的智能风扇(红外线无线控制开关调速定时)

一、项目介绍 在炎热的夏季&#xff0c;风扇成为人们室内生活中必不可少的电器产品。然而&#xff0c;传统的风扇控制方式存在一些不便之处&#xff0c;比如需要手动操作开关、无法远程控制和调速&#xff0c;以及缺乏定时功能等。为了解决这些问题&#xff0c;设计了一款基于…
阅读更多...
Python实验项目6 ：文件操作与模块化

Python实验项目6 ：文件操作与模块化

1、使用random库&#xff0c;产生10个100到200之间的随机数&#xff0c;并求其最大值、平均值、标准差和中位数。 # 1、使用random库&#xff0c;产生10个100到200之间的随机数&#xff0c;并求其最大值、平均值、标准差和中位数。 import random # 定义一个列表 list[] for i …
阅读更多...
关于Android Studio中开发Flutter配置

关于Android Studio中开发Flutter配置

配置系统环境变量&#xff1a;path下 &#xff0c;flutter的bin目录下 File->Settings->Languages&Frameworks->FlutterFile->Settings->Languages&Frameworks->DartFile->Settings->Languages&Frameworks->Android SDK 确认是…
阅读更多...
QMetaType和QVariant使用

QMetaType和QVariant使用

描述 QMetaType和QVariant可以结合使用&#xff0c;用于在运行时确定数据类型。 QMetaType是Qt提供的用于管理各种数据类型的类&#xff0c;它可以帮助我们在运行时动态地创建、销毁、复制和比较数据类型。我们可以使用QMetaType来注册我们自己的数据类型&#xff0c;并为其提…
阅读更多...
ChromeDriver谷歌浏览器驱动下载安装与使用最新版118/119/120

ChromeDriver谷歌浏览器驱动下载安装与使用最新版118/119/120

ChromeDriver谷歌浏览器驱动下载安装与使用最新版118/119/120 1. 确定Chrome版本 我们首先确定自己的Chrome版本 Chrome设置->关于Chrome 可以看到&#xff0c;当前chrome是最新版本&#xff1a;119.0.6045.124&#xff08;正式版本&#xff09; &#xff08;64 位&#…
阅读更多...
前端图片压缩上传，减少等待时间！优化用户体检

前端图片压缩上传，减少等待时间！优化用户体检

添加图片注释&#xff0c;不超过 140 字&#xff08;可选&#xff09; 这里有两张图片&#xff0c;它们表面看上去是一模一样的&#xff0c;但实际上各自所占用的内存大小相差了180倍。 添加图片注释&#xff0c;不超过 140 字&#xff08;可选&#xff09; 添加图片注释&…
阅读更多...
RxJava/RxAndroid的基本使用方法（一）

RxJava/RxAndroid的基本使用方法（一）

文章目录 一、什么是RxJava二、使用前的准备1、导入相关依赖2、字段含意3、Upstream/Downstream——上/下游4、BackPressure5、BackPressure策略6、“热” and “冷” Observables7、 基类8、事件调度器9、操作符是什么&#xff1f;10 、Consumer和 Observer的区别 三、RxJava的…
阅读更多...
已解决：Python Error: IndentationError: expected an indented block 问题

已解决：Python Error: IndentationError: expected an indented block 问题

&#x1f337;&#x1f341; 博主猫头虎&#xff08;&#x1f405;&#x1f43e;&#xff09;带您 Go to New World✨&#x1f341; &#x1f984; 博客首页: &#x1f405;&#x1f43e;猫头虎的博客&#x1f390;《面试题大全专栏》 &#x1f995; 文章图文并茂&#x1f996…
阅读更多...
C# OpenCvSharp 玉米粒计数

C# OpenCvSharp 玉米粒计数

效果 项目 代码 using OpenCvSharp; using System; using System.Drawing; using System.Text; using System.Windows.Forms;namespace OpenCvSharp_Demo {public partial class frmMain : Form{public frmMain(){InitializeComponent();}string fileFilter "*.*|*.bmp;…
阅读更多...
OpenGL_Learn08（坐标系统与3D空间）

OpenGL_Learn08（坐标系统与3D空间）

目录 1. 概述 2. 局部空间 3. 世界空间 4. 观察空间 5. 剪裁空间 6. 初入3D 7. 3D旋转 8. 多个正方体 9. 观察视角 1. 概述 OpenGL希望在每次顶点着色器运行后&#xff0c;我们可见的所有顶点都为标准化设备坐标(Normalized Device Coordinate, NDC)。也就是说&#x…
阅读更多...
C++跨模块传递CRT引发问题

C++跨模块传递CRT引发问题

SDK新增加了一个接口&#xff0c;参数使用std::vector<Class>&&#xff0c;传给dll函数中填充数值&#xff0c;然后应用层拿到这个vector出现了崩溃 越界等问题&#xff0c;调了很久&#xff0c;之前知道这个问题&#xff0c;没有想起来&#xff0c;耽误了许多时间。…
阅读更多...
用Python的requests库来模拟爬取地图商铺信息

用Python的requests库来模拟爬取地图商铺信息

由于谷歌地图抓取商铺信息涉及到API使用和反爬虫策略&#xff0c;直接爬取可能会遇到限制。但是&#xff0c;我们可以使用Python的requests库来模拟爬取某个网页&#xff0c;然后通过正则表达式或其他文本处理方法来提取商铺信息。以下是一个简单的示例&#xff1a; # 导入requ…
阅读更多...
uniapp+uview2.0+vuex实现自定义tabbar组件

uniapp+uview2.0+vuex实现自定义tabbar组件

效果图 1.在components文件夹中新建MyTabbar组件 2.组件代码 <template><view class"myTabbarBox" :style"{ backgroundColor: backgroundColor }"><u-tabbar :placeholder"true" zIndex"0" :value"MyTabbarS…
阅读更多...
网页分析和xml.etree库

网页分析和xml.etree库

源代码&#xff1a; Lib/xml/etree/ElementTree.py 该xml.etree.ElementTree模块实现了一个简单高效的 API&#xff0c;用于解析和创建 XML 数据。 一、说明 这是一个简短的使用教程xml.etree.ElementTree&#xff08;ET简而言之&#xff09;。目标是演示该模块的一些构建块和基…
阅读更多...
基于FPGA的模板匹配红外目标跟踪算法设计

基于FPGA的模板匹配红外目标跟踪算法设计

为什么要写这篇文章 我写这篇文章的原因是一天在B站看到了一个大神发的视频是关于跟踪一个无人机的&#xff0c;看到作者跟网友的回复说是用的图像匹配算法&#xff0c;我就在网上搜索相关资料&#xff0c;最终找到一篇文献。文献中对该算法的评价很高&#xff0c;满足制导系统…
阅读更多...
为你摘星辰

为你摘星辰

欢迎来到程序小院 为你摘星辰 玩法&#xff1a;鼠标控制人物方向&#xff0c;点击鼠标键上升人物&#xff0c;经过⭐️⭐️吃掉获得分数&#xff0c;共三次生命&#xff0c;碰到红色障碍物减去一次生命&#xff0c; 人物掉落底部游戏结束&#xff0c;看你获得多少分^^。开始游…
阅读更多...
排序算法的空间复杂度和时间复杂度

排序算法的空间复杂度和时间复杂度

一、排序算法的时间复杂度和空间复杂度 排序算法 平均时间复杂度 最坏时间复杂度 最好时间复杂度 空间复杂度 稳定性 冒泡排序 O(n) O(n) O(n) O(1) 稳定 直接选择排序 O(n) O(n) O(n) O(1) 不稳定 直接插入排序 O(n) O(n) O(n) O(1) 稳定 快速排序 O(n…
阅读更多...
最新文章

Copyright @ 2022~2023