MySQL表的增删改查（进阶）

最新推荐文章于 2022-11-04 21:20:16 发布
最新推荐文章于 2022-11-04 21:20:16 发布
阅读量998 收藏 4
点赞数 3
分类专栏： 笔记 文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_51405559/article/details/121725318
版权

文章目录

一、数据库约束

1.约束类型

1.NOT NULL： 指示某列不能存储NULL值

-- 创建一个学生表
mysql> create table student
    -> (id int not null,
    -> sn int unique,
    -> name varchar(20) default 'unknown',
    -> qq_mail varchar(20)
    -> );

-- 插入id为null
mysql> insert into student values(null,01,null,null);

报错：
在这里插入图片描述
前面我们对id的约束条件为not null，所以id不能为null。

2.UNIQUE： 可以是一个或者多个字段，保证任意两条数据，唯一约束的字段不能重复

-- 插入一个学生学号为01
mysql> insert into student values(null,01,null,null);
-- 插入第二个学生学号仍为01
mysql> insert into student values(2,01,null,null);

报错：
在这里插入图片描述
原因是前边规定了sn的约束条件为unique，有唯一值。

3.DEFAULT： 规定没有给列赋值时的默认值

-- 没有给name列赋值
mysql> insert into student(id,sn,qq_mail) values(3,02,null);
-- 给name列赋值为空
mysql> insert into student values(4,03,null,null);

结果：
在这里插入图片描述

插入时，不指定该字段，才能插入默认值
如果指定了，即使null，也会真实的插入null

4.PRIMARY KEY： NOT NULL和UNIQUE的结合，确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速的找到表中的一个特定的记录
应用场景：
用来标识数据库中数据

数据是在不同设备中流传的，一般来说，web开发，数据是在
1.用户浏览器
2.Java程序
3.数据库
在做修改、删除、查询时，就需要知道是哪条数据

如果主键为整型数字，可以指定为auto_increment表示从1开始自增，但是插入数据的时候，不要制定插主键字段

-- 新创建一个班级表
mysql> create table classes (
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> `desc` varchar(50)
    -> );
-- 不指定id，插入两个
mysql> insert into classes(name) values('图图');
mysql> insert into classes(name) values('小美');

显示结果：
在这里插入图片描述

5.FOREIGN KEY： 保证一个表中的数据匹配另一个表中的值的参照完整性
外键用于关联其他表的主键或唯一值，语法：

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

一对一、一对多、多对多关联时，需要使用外键约束
关联的字段名可以使用主键（最多的方式），其实也可以使用其他字段（如unique字段）
外键的值可以重复：

1.一对一关系外键的值就是唯一的
2.一对多的关系外键的值就是重复的

表的直接关系只有一对一和一对多、多对多的关系，是在中间表产生的间接关系

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

二、表的设计

三大范式

1.一对一

在这里插入图片描述

2.一对多

在这里插入图片描述
名称上虽然是一对多，但是理解上要从两方面看待：
1.一个班级关联多个学生
2.一个学生关联一个班级

-- 向classes中插入
mysql> insert into classes(name,`desc`) values
    -> ('羽毛球俱乐部',null),
    -> ('舞蹈社',null);

-- 创建一个学生表，其中包含对classes的外键
mysql> create table student (
    -> id int primary key auto_increment,
    -> sn int unique,
    -> name varchar(20),
    -> qq_mail varchar(100),
    -> classes_id int,
    -> foreign key(classes_id) references classes(id)
    -> );

mysql> insert into student(sn,name,qq_mail,classes_id) values
    -> (11,'图图',123,4),
    -> (22,'小美',234,5),
    -> (33,'壮壮',345,4),
    -> (44,'王子',456,4),
    -> (55,'小豆丁',567,5);

显示结果：
在这里插入图片描述

3.多对多

在一定的业务场景下，两张表产生了多对多的关联关系：
在这里插入图片描述
例如学生和课程之间通过考试就建立起多对多的关系
在这里插入图片描述
在每次考试发生以后，总共产生了：1x、1y、2x、2y四条数据
设计考试成绩表：
在这里插入图片描述
多对多关系，以中间表来记录关系
中间表中包含的关系：
1.id为1的一个学生，有多个课程及成绩
2.id为x的课程，有多个学生
注意：两张表，m:n关联，不直接在两张表产生关联关系。

-- 创建一个课程表
mysql> create table course(
    -> id int primary key auto_increment,
    -> name varchar(50),
    -> `desc` varchar(50)
    -> );
    
-- 插入
mysql> insert into course(name,`desc`) values
    -> ('高等数学','难'),
    -> ('大学英语','较难');
   
-- 创建一个考试表，其中有两个外键，一个学生id，一个课程id
mysql> create table exam (
    -> id int primary key auto_increment,
    -> score decimal(4,2),
    -> student_id int,
    -> foreign key (student_id) references student(id),
    -> course_id int,
    -> foreign key (course_id) references course(id)
    -> );

-- 插入
mysql> insert into exam(score,student_id,course_id) values
    -> (98,6,2),
    -> (78,6,3),
    -> (32,7,2),
    -> (45,7,3),
    -> (78,8,2),
    -> (90.8,8,3),
    -> (35,9,2),
    -> (67,9,3),
    -> (12,10,2),
    -> (99,10,3);

结果：
在这里插入图片描述

三、新增

插入查询的结果
语法：

insert into table_name [(column [,column ...])] select ...

-- 再创建一个考试表
mysql> create table exam2 (
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> score decimal(6,2)
    -> );

-- 将exam_result中的那么和计算出的总分插入到新创建的考试表中
mysql> insert into exam2(name,score) select name,chinese+math+english from exam_result;

结果：
在这里插入图片描述
注意：查询结果不一定等于表字段

四、查询

1.聚合查询

1.聚合函数
所谓的聚合，是根据结果集多条数据，计算汇总为一个数据

函数说明
count([distinct] expr)返回查询到的数据的数量
sum([distinct] expr)返回查询到的数据的总和 ，不是数字没有意义
avg([distinct] expr)返回查询到的数据的平均值 ，不是数字没有意义
max([distinct] expr)返回查询到的数据的最大值 ，不是数字没有意义
min([distinct] expr)返回查询到的数据的最小值 ，不是数字没有意义

1.count

-- 返回列表中有多少个学生
mysql> select count(*) from student;
-- 返回列表中有多少个0
mysql> select count(0) from student;

如下为列表：
在这里插入图片描述
结果：
在这里插入图片描述
为什么count(0)也是5呢？
原因是之前我们在查询列表时，如果将查询字段写完0，那么该列都会显示为0
在这里插入图片描述
所以这里的count(0)是先把列置为0，再计算0的个数，所以和count(*)一样。

注意：

1.count(*)和count(0)、count(1)的结果是一样的
2.select * from 表和select 0 from 表 ，数据和数量的结果都是一样的

2.sum

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

-- 统计语文成绩小于60的学生，没有结果，返回NULL
mysql> select id,name,chinese from exam_result where chinese<60;

运行结果：
在这里插入图片描述

3.avg

-- 统计平均总分，给其起别名为avgsum
mysql> select avg(math+chinese+english) avgsum from exam_result;

运行结果：
在这里插入图片描述

4.max

-- 返回英语最高分
mysql> select id,name,max(english) from exam_result;

在这里插入图片描述
5.min

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

运行结果：
在这里插入图片描述

2.GROUP BY语句
语法：
select 查询字段 from 表 group by 分组字段1，分组字段2…
作用：
将分组字段相同的一条或多条数据，合并为一条
注意：
1.如果使用了分组，查询字段只能是分组依据字段(分组字段，聚合函数(任意字段))，其实，可以理解为，分组前后，数据数量一样，其他字段也可以使用(如按unique字段分组，按主键字段分组)
2.如果查询字段有分组后合并的字段，mysql里边不会报错，只显示第一条(此时可能就不是我们想要的结果)
准备测试表及数据：

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

-- 查询每个角色的最高工资\最低工资和平均工资
mysql> select role,max(salary),min(salary),avg(salary) from emp group by role;

在这里插入图片描述
除了role字段,其他的其实都是合并了不存在的,只是取了第一条.

3.HAVING
GROUP BY子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用WHERE语句,而需要用HAVING
语法:
group by 分组字段 having 条件
注意:
1.分组前也可以使用条件过滤,表示先过滤,结果集再进行分组
2.having后的条件,使用的字段必须为有效的分组后的查询字段

-- 把id>3的按照角色进行分组
mysql> select role,sum(salary),avg(salary) from emp where id>3 group by role;
-- 分组后过滤出平均值>10000的
mysql> select role,sum(salary),avg(salary) from emp where id>3 group by role having avg(salary)>10000;

运行结果:
在这里插入图片描述
步骤:

1.select * from emp where id>3
2.以上结果集,按照role字段使用group by分组
3.分组后数据在使用avg聚合函数(有效的分组后查询字段)来过滤

2.联合查询

然而实际开发中往往数据来自不同的表,所以需要多表联合查询.多表查询是对多张表的数据取笛卡尔积:
在这里插入图片描述
具体要使用哪些表来关联,依赖查询字段(要获取什么字段数据,这些字段在哪些表,就关联哪些表)
语法:
(两张表关联) select * from 表1,表2 (表也可以使用别名 语法: 表名 [as] 别名)
注意:表使用了别名,查询字段也需要是表的别名,该表的字段名.

对于select * from a,b结果集来说
查询字段/结果集字段,为省略的a*,b*
数据为:
1.遍历a表的所有数据
2.a表的m条数据中,每一条数据,关联b表n条数据的每一条数据,产生m*n条件

在这里插入图片描述
关联班级表和学生表:
在这里插入图片描述
表的别名+字段别名结合使用(几乎都要使用)
在这里插入图片描述
准备测试表和数据:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
要显示该班级关联的学生,除了将学生表关联班级表作笛卡尔积外,还需要使用一对多关联条件:主外键关联(班级表主键(classes.id)关联学生表的外键(student.classes_id))
在这里插入图片描述

1.内连接
语法:
select…from 表1 别名1 join 表2 别名2 on 连接条件 and 其他条件
select…from 表1 别名1 , 表2 别名2 where 连接条件 and 其他条件
select…from表1 别名1 join 表2 别名2 on 连接条件 where 其他条件
上面一个连接就是用的内连接的第二种形式
第一种:
在这里插入图片描述
关联查询可以使用两张或者以上的表来关联:

-- 查询许仙同学的成绩,包括课程名称
mysql> select
    -> stu.id,
    -> stu.name,
    -> sco.score,
    -> sco.course_id,
    -> cou.name course_name
    -> from
    -> student stu
    -> join score sco on stu.id=sco.student_id
    -> join course cou on sco.course_id=cou.id
    -> and stu.name='许仙';

在这里插入图片描述
说明:内连接,查询结果集,必须严格保证所有的条件(包含连接条件)

2.外连接
语法:
左外连接left join 左边的表是外表(表1完全显示)
select 查询字段 from 表1 别名1 left join 表2 别名2 on 连接条件 where 其他条件
外表:表1
右外连接right join 右边的表是外表(表2完全显示)
select 查询字段 from 表1 别名1 right join 表2 别名2 on 连接条件 where 其他条件
外表:表2
注意:
如果外表有的数据,在另一个表没有关联的数据,也会显示(只是说连接条件不满足时,还会使用外表全部数据)
但是其他条件还是会生效
简单地说,就是按其他条件严格过滤后,外表数据全部显示
说明:
对外连接来说,查询结果集:
1.其他条件:完全满足
2.连接条件:(1)满足 或者(2)外表的数据

3.自连接
关联查询的两张表,是同一张表,就是自连接
使用场景:
在这里插入图片描述
在这里插入图片描述

4.子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
(1)使用在比较操作符后:需要子查询结果集返回1个字段,且只有1条数据
语法:select…from 表 where 字段1=(select 某个字段 from 表)
(2)使用在in/out后:需要子查询返回一个字段,可以有多条数据
语法:select…from 表 where 字段 in (select 某个字段 from 表)
数据库常考的一种题:根据一个或多个字段去重
在这里插入图片描述

说明:去重是delect 操作重复数据,只保留一条,这个操作比较复杂
先学习如何查询重复数据
1.group by 去重的字段
2.having count(0)>1
3.in(子查询)查询重复的数据

where exists(子查询)

mysql> select *
    -> from score sco
    -> where exists(
    -> select sco.id
    -> from course cou
    -> where (name='语文'or name='英文')
    -> and cou.id = sco.course_id);

在这里插入图片描述
(3)子查询,作为一张不存在的虚拟的临时表,可以关联真实的表来查询
语法:
select * from (select… from 表)tmp
注意:临时表,必须给别名
最简单的,一个可以执行sql,可以直接包裹起来作为临时表
(1)前面加 “select * from(”
(2)后边加 “)临时表别名”
在这里插入图片描述
临时表也可以当做一张虚拟表,来关联查询,但是注意,临时表关联的字段是结果集字段.

5.合并查询
在实际应用中,为了合并多个select的执行结果,可以试用集合操作符union,union all.使用union和union all时,前后查询的结果集中,字段需要一致
1.Union
作用:多个结果集,根据查询字段去重后,取并集
注意:是结果集合并,两个结果集,可以是不同的表数据
建议:查询字段,数量,顺序,结果集字段名,保持一致
2.Union all
作用:和union相比,不去重

dhdhdhdhg
关注
  • 3
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySql 5.7 学生信息表简单增删改查 (JDBC)
woshi4015的博客
03-01 1626
实现的功能为8个模块：1.增加 2.查询所有信息 3.通过某些字段查询 4.删除 5.修改 6.查询数据库信息总条数 7.分页 8.like查询 实现方式为 ：通过接口定义方法，创建学生类，再创建一个类去implements，最后通过测试类去实现。(选中方法右键运行JUnit) 出错的原因无非这几种：1.数据库字段与方法字段顺序有误 2.Sql语句有问题 3.导包出错。（...
Mysql表增删改查进阶—入门+实战面面俱到
元宝的博客
11-11 1500
Mysql增删改查(进阶) 1.约束类型 NOT NULL - 指示某列不能存储 NULL 值。 UNIQUE - 保证某列的每行必须有唯一的值。 DEFAULT - 规定没有给列赋值时的默认值。 PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。 FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。 CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对
转 - 数据库面试题目研究
weixin_34174422的博客
08-04 902
一:SQL tuning 类 1.      列举几种表连接方式 Answer：等连接（内连接）、非等连接、自连接、外连接（左、右、全） Or hash join/merge join/nest loop(cluster join)/index join ？？ ORACLE 8i，9i 表连接方法。   一般的相等连接： select * from a, b where a.id ...
学生表用MySQL增删改查_对学生表的增删改查操作
weixin_33548183的博客
01-29 684
package com.mm.bean;public class Student {int id;//学号String name;//姓名int age;//年龄String classes;//班级public Student(){}public int getId() {return id;}public void setId(int id) {this.id = id;}public Str...
mysql实现学生信息的增删查
m0_52180541的博客
10-18 745
表名:examstudent Student.java package com.g03.bean; public class Student { private int FlowID; private int Type; private String IDCard; private String ExamCard; private String StudentName; private String Location; private int G...
mysql常用命令
qq_25073261的博客
10-22 458
-------------------linux环境mysql安装-------------------------------------------- wget安装mysql的过程 https://www.cnblogs.com/opsprobe/p/9126864.html Ubuntu18.04下安装MySQL 提示：以下操作均在root权限下进行。 Ubuntu重启mysql sudo /etc/init.d/mysql start sudo service mysql start 查看有没有安装
【MySQL】增删改查进阶
qq_61862008的博客
11-04 2314
mysql增删改查进阶
【MySQL系列】 MySQL表的增删改查（进阶）
m0_67402774的博客
07-31 515
老规矩~~为了本篇博客的学习，先把MySQL数据库里面的表全部都删除掉~~只留下自己创建的java数据库（自带的数据库肯定是不动的了）~~
mysql增删改查进阶语句
最新发布
06-06
好的，以下是 MySQL 的增删改查进阶语句： 1. 插入数据 - 插入单行数据： ``` INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); ``` - 插入多行数据： ```...
Mysql实现增删改查
weixin_52966382的博客
10-27 645
1、创建表 /* PRIMARY KEY (`id`)设置id为主键 AUTO_INCREMENT 设置id行自增 NOT NULL 设置表格不为空 COMMENT 添加注释 DEFAULT NULL 设置默认为空 DEFAULT '123456' 设置默认值 ENGINE =INNODB 设置表类型为innodb DEFAULT CHARSET=utf8 设置默认的编码为utf8 */ USE test2; CREATE TABLE IF NOT EXISTS `cxl` ( `id...
学生表用MySQL增删改查_Java用类实现对Student数据表(Mysql)的操作（增删改查）
weixin_26704349的博客
02-05 479
Java连接mysql数据库利用了mysql-JDBC的jar包，查询操作用的是executeQuery()方法，除了查询操作外的增删改都是用executeUpdate()方法实现。代码：Student类package test;public class Student {private int id;private String name;private int age;public Stude...
VS2019 C# MySQL 学生信息增删改查（一、增删）
青哥
10-24 9292
VS2019 C# MySQL 学生信息增删改查 由于刚刚学习VS2019，入手的第一个例子是编写一个简单的增删改查。这里，我以学生信息为例，进行详细的过程描述以及代码实现。 一、 新建一个Windows窗体应用程序 1、创建新项目界面，如下图所示。 2、更改项目名称，当然不修改也可以。我这里的是“StudentInfoSystem”；项目存放位置路径根据自己需要进行更改；“将解决方案和项目放在同一目录中”前面的勾选框，选不选都可以；点击“创建”，如下图所示。 二、前期准备工作 1、将Form1属性中
MySQL约束、索引
geek_xiong的博客
09-16 591
约束 约束的作用：保证数据的一致性，有效性 约束的分类： 默认约束：default 非空约束：not null 如： 在插入记录时性别不输入，默认为 ’ nv ‘ sex enum('nan', 'nv') default 'nv', 做一个default约束，在插入时可以不插入性别，如 insert into stuinfo(id, name, score) valu...
求素数的三种方法（Java实现）
热门推荐
m0_51405559的博客
09-07 2万+
文章目录作业标题一、常规方法（最低效）思路说明：缺点：二、稍微高效的方法（x/2）思路说明：缺点：三、高效方法（根号x）思路说明： 作业标题 给定一个数字，判定一个数字是否是素数。 素数又叫质数，质数是指在大于1的自然数中，除了1和它本身以外，不能被其他自然数整除的数。最小的质数是2，它也是唯一的偶数质数，最前面的质数依次排列为：2、3、5、7、11、13、17、19、23、29、31等。 第一种方法是我自己做作业时想到的，但是随着只是不断深入，我们还是要尽可能的将每个解法做到高效。 一、常规方法（.
数据库的增删改查功能详解（初阶）
m0_51405559的博客
12-03 4370
文章目录一、SQL注意事项二、新增(Create)1.单行数据+全列插入2.多行数据+指定列输入三、查询(Retrieve)1.创建一个案例2.全列查询3.指定列查询4.查询字段为表达式5.别名6.去重：DISTINCT7.排序：ORDER BY8.条件查询：WHERE9.分页查询：LIMIT四、修改(Update)五、删除(DELETE) 一、SQL注意事项 1.在命令行工具写sql时，最后用“；”表示结束整个语句；本质是输入“；”+回车，客户换才会发送sql语句到服务端，由服务端执行这条语句 2...
详细谈谈Java中七种排序算法（带图形动画详解）
m0_51405559的博客
11-16 2793
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录对文章出现的一些名词进行解释一、插入排序1.基本思想2.直接插入排序3.希尔排序(缩小增量排序)二、选择排序1.基本思想2.直接选择排序3.堆排序总结 对文章出现的一些名词进行解释 排序： 使一串记录，按照其中的某个或某些关键字的大小，递增或者递减排列起来的操作。 稳定性： 假定在排序的记录序列中，存在多个具有相同的关键字的记录，若经过排序，这些记录的相对次序保持不变，即在原序列中，a=b，且a在b之前，而在排序后的序列中，a仍然
goto语句
m0_51405559的博客
05-09 2639
文章目录前言一、使用goto语句的一个例子二、使用步骤1.引入库2.读入数据总结 前言 goto表示“无条件跳转”，遇到goto直接跳到对应位置的代码。C语言中提供了可以随意滥用的goto语句和标记跳转的标号，从理论上goto语句是没有必要的，事件中没有goto语句也可以很容易地写出代码。但是某些场合下goto语句还是用得着的，最常见的用法就是终止程序在某些深度嵌套的结构的处理过程，例如一次跳出两层或多层循环。这种情况使用break是达不到目的的。它只能从最内层循环退出到上一层的循环。汇编语言里面主要在使
二叉树的遍历（前中后序遍历、层序遍历及相关选择题详解）以及二叉树的基本操作【数据结构】
m0_51405559的博客
11-03 2509
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录前言：简单创建一棵二叉树前言：简单创建一棵二叉树1.前中后序遍历 前言：简单创建一棵二叉树 在学习二叉树的基本操作前，需要先创建一棵二叉树，由于现在我们还是初学者，理解还不够深入，这里手动快速的创建一颗简单的二叉树，等之后二叉树的结构了解的差不多时，我们还会研究其真正的创建方式。 public class BinaryTree { public static class BTNode{ BTNode lef
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

dhdhdhdhg

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

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

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

打赏作者

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

抵扣说明：

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

余额充值