数据库（三）MySQL表的增删改查（进阶）

  • 开发
  • 15

目录

本节目标

  • 查询进阶：键值约束、聚合函数、分组查询、多表联查
  • 表的设计（ER图，三大范式）

1 数据库约束

键值约束，就是对标中指定字段的描述及约束

2.1 约束类型

约束名称 约束类型 说明
非空 NOT NULL 指示某列不能存储 NULL 值。
唯一键 UNIQUE 保证某列的每行必须有唯一的值。
默认值 DEFAULT 规定没有给列赋值时的默认值。
主键 PRIMARY KEY NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
外键约束 FOREIGN KEY 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。
自增属性 auto_increment 只能针对整数的主键字段进行设置，当不插入数据的时候默认从1开始自增

1.1.1 主键约束

主键约束（primary key）：约束是表中指定字段，值不能为NULL，且数据不能产生重复，主键表中中只能有一个，但是存在组合主键——以多个字段当做一个整体作为主键。
在这里插入图片描述

  1. 创建一个以id为主键的表
    修改一下表结构
    在这里插入图片描述

  2. 不能给主键 id 1）不插入值；2）不能插入null值；3）不能插入重复值；4）只能有一个主键
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

  3. 组合主键
    在这里插入图片描述
    在这里插入图片描述
    注意：组合主键是两个完全相同的不可以存在。下面的插入都是可以的
    在这里插入图片描述

1.1.2 唯一键

唯一键：unique key 约束表中的指定字段，值不能重复，一个表可以有多个唯一键，可以为null值。

  1. 创建一个以name 和 sex 为唯一键，

create table uni_tb(id int primary key,name varchar(32) unique key,age int unique,sex int ,unique key uk(sex));
在这里插入图片描述
在这里插入图片描述

  1. 插入数据 1）不能重复；2）可以为空值；3）可以设置多个唯一约束

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

在这里插入图片描述

1.1.3 非空约束

非空约束：not null ，约束表中指定字段值不能为null

  1. 创建一个name 不能为空的表

create table nn_tb(id int primary key,name varchar(32) not null);
在这里插入图片描述
在这里插入图片描述

2.插入数据不能为null

insert into nn_tb values(3,null);
在这里插入图片描述

  1. 创建一个id唯一且不为空，name 唯一且不为空的表

create table nn_tb2(id int unique key not null,name varchar(32) unique key not null);
在这里插入图片描述
在这里插入图片描述
我们发现id字段自动生成为了主键，而name字段因为已经有主键了所以没有生成主键。

注意：当表中没有指定主键，但是指定了非空且唯一字段，那么这个约束会自动升级为主键约束。

1.1.4 外键约束

foreign key … references…：限制当前指定字段的数据，必须在另一张表中字段的数据中存在。
在这里插入图片描述

  1. 创建一个班级表和学生表(学生表是以前创建的)

1)创建班级表
create table class_tb(id int primary key,info varchar(32));
在这里插入图片描述
查看表结构：
在这里插入图片描述

2)查看之前创建的学生表
在这里插入图片描述
在这里插入图片描述

发现没有id字段，我们自己加一个

alter table stu_tb add class_id int;
在这里插入图片描述
在这里插入图片描述
3）创建一个有外键约束的表stu_table
在这里插入图片描述
在设置外键约束之后，我们再往进去插入2号班级的信息就不可以了。
在这里插入图片描述

  1. 插入数据

1）给班级表插入数据
insert into class_tb values(1,‘一年级一班’);
在这里插入图片描述
在这里插入图片描述
2)给学生表插入数据
insert into stu_tb(sn,name,class_id) values(8,‘桃之夭夭’,2);
insert into stu_tb(sn,name,class_id) values(7,‘林芝桃花’,1);
查看表内容：
select *from stu_tb;
在这里插入图片描述
给其他学生插入班级id
在这里插入图片描述
此时有一个“桃之夭夭”的同学的班级id居然是2，这就不合理了，因为我们的班级表只有一个1班，所以要进行一个改进，要把两个表关联起来。

  1. 修改班级表（class_tb）把class_id字段进行修改

alter table stu_tb add constraint fk foreign key (class_id) references class_tb(id);
在这里插入图片描述
这里不让我修改，我研究了半天，发现了因为我们的stu_tb表中已经有了1班和2班，不满足要求，因为我们此时的class_id中只有1班。我就尝试把二班的那个数据删除了。之后再继续进行修改stu_tb的class_id为外键约束。最终成功了。
在这里插入图片描述
在这里插入图片描述

  1. 此时进行插入2班信息就无法插入
    在这里插入图片描述
    插入1班信息就成功
    在这里插入图片描述
    使用外键可以让表中数据更加严谨，比如，学生信息在中的班级id，必须在班级表中存在这个班级。

1.1.5 默认值

默认值：default，当没有进行主动插入某个字段数据的时候，则自动以默认值进行新增

1.创建一个有默认约束的表
create table def_tb(id int primary key,name varchar(32),sex varchar(1) default ‘男’);
在这里插入图片描述

  1. 插入数据：insert into def_tb(id, name) values(2,‘孙尚香’);
    在这里插入图片描述
  2. 这里我想修改表的sex字段非空且有默认值

alter table def_tb modify sex int not null default ‘男’;
在这里插入图片描述
发现又不可以，我觉的是因为我的def_tb表中已经有一个信息sex为空，所以我尝试把它删除之后在进行修改
在这里插入图片描述
最后才发现是我的数据类型写错了：应该是varchar(1)类型，我之前写的int
alter table def_tb modify sex varchar(1) not null default ‘男’;
在这里插入图片描述

1.1.6 自增属性

自增属性：auto_increment,只能针对整数的主键字段进行设置，当不插入数据的时候默认从1开始自增，
1.创建一个有自增属性的表

create table auto_tb(id int primary key auto_increment,name varchar(32));
在这里插入图片描述
注意：只能对主键进行自增操作
1）create table auto_tb1(id int auto_increment,name varchar(32));
在这里插入图片描述
这里就会提示，必须要是一个key才行。
2）create table auto_tb1(id int unique auto_increment,name varchar(32));
在这里插入图片描述
我们发现对unique字段进行自增约束，就直接升级为主键了

  1. 插入数据

insert into auto_tb values(null,‘虞姬’);
在这里插入图片描述
它自动给我们补了个1
在这里插入图片描述
插入很多数据，表都会自动给我们从1开始补

注意：当我们删除id为1的数据的时候，继续插入，它不会从1重新开始，而是从上次递增到的5继续向下递增，

delete from auto_tb where id=1;
在这里插入图片描述

2 数据库表的设计

E-R实体关系图和三大范式

2.1 E-R实体图

  • E-R实体图：
    以方形作为实体，以圆形作为属性，以菱形作为关系，描述表与表之间的关系。
    图形化表示实体之间的关系，通过不同的实体间的关系,决定如何设计以及关联数据库表。
  • 实体之间的关系：
  1. 一对一
    在这里插入图片描述
    在这里插入图片描述
  2. 一对多
    在这里插入图片描述

在这里插入图片描述
3. 多对多
在这里插入图片描述
在这里插入图片描述

2.2 三大范式

范式：数据库表的设计规范
数据库是存储数据的仓库，但是数据并不是仅仅存储就够了，还有查询，然而一旦数据库表的设计不够合理，则查询或者插入修改效率就大大降低了

2.2.1 第一范式

  • 第一范式：表中的所有字段必须保持原子特性
  • 举例：
    在这里插入图片描述
    假如我们要查询南京户口的人，但是我们只能进行模糊匹配。因为家庭信息里面除了地址还有人口信息，这就很不合理了。所以我们应该把这两个信息分开。
  • 当不具备原子特性时，则查询效率会大大降低，因为数据的查询需要通过模糊匹配才能实现。

2.2.2 第二范式

  • 第二范式：表中的所有字段必须与主键完全相关，而不能部分相关（主要针对组合主键的使用）
  • 举例：
    在这里插入图片描述
    所以我们应该进行拆分：
    在这里插入图片描述
  • 表中字段并没有与组合主键完全相关，从而导致表中中出现大量的数据冗余。

2.2.3 第三范式

  • 第三范式：表中字段必须与主键直接相关，不能间接相关

  • 举例
    在这里插入图片描述
    这些班主任具体信息（性别年龄），应该只和班主任相关联。这些班主任具体信息（性别年龄）和学生信息并不直接相关，即班主任信息和学生信息是间接相关的。这些班主任具体信息应该和班主任信息直接相关。这种情况下表中产生了大量的数据冗余。我们在设计的时候应该要分开设计：
    在这里插入图片描述

  • 表中字段并没有与表中主键完全相关，从而导致表中中出现大量的数据冗余。

3 查询进阶

3.1 聚合查询

3.1.1 聚合函数

  • 聚合函数：mysql数据库中内置的一些数据统计函数
函数 说明 举例
count （*） 统计结果条数 select count(*) from stu_tb where en<70;
sum(fields_name) 对指定字段数据求和 不是数字没有意义 select sum(ch) from stu_tb ;
avg(fields_name) 对指定字段求平均值 不是数字没有意义 select avg(math) from stu_tb ;
max(fields_name) 对指定字段求最大值 不是数字没有意义 select max(math) from stu_tb ;
min(fields_name) 对指定字段求最小值 不是数字没有意义 select min(en) from stu_tb ;

举例

  • count
    在这里插入图片描述

  • SUM
    在这里插入图片描述

  • avg
    在这里插入图片描述

  • MAX
    在这里插入图片描述

  • MIN
    在这里插入图片描述

3.1.2 分组查询(GROUP BY)

  1. 准备测试表及数据：职员表，有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);
在这里插入图片描述

1.分组查询

以指定字段为依据，对表中数据进行分组统计。
关键字 ：group by

  • 注意事项1：
    分组查询，以表中指定字段对表中数据进行分组统计查询，但是查询结果中只能有分组依据字段以及聚合函数~（不能出现员工名字，因为分组之后每个组有很多员工）
  1. 查询1：公司中都要哪些岗位，每个岗位的最大薪资是多少？平均薪资是多少？最小薪资是多少？

select role,max(salary),min(salary),avg(salary) from emp group by role;
在这里插入图片描述

2. HAVING
  • 注意事项2：
    分组查询，过滤条件的时候不能使用where关键字，而是使用having
  1. 查询2：我现在要找公司中平均薪资高于1W的岗位

select role from emp group by role having avg(salary)>10000;
在这里插入图片描述

常用场景：有个学生表，有学生信息，老师姓名，想查看每个老师带了多少学生。

3.2 联合查询

将多张表的数据合并在一起进行查询。
表中数据如何合并：笛卡尔积

笛卡尔积

在这里插入图片描述
当对两张表中的数据取笛卡尔积，会构造一张新表，然后再进行数据查询。比如1表中有100W数据，2表汇总有100w数据，笛卡尔积合并后，1w亿的数据量。这样会导致数据查询效率变的非常低，因此我们通常会在进行多表连接的时候进行一些合并规则设置。
例：创建一个班级表classes，在创建一个学生表stu
在这里插入图片描述
在这里插入图片描述
我们合并的时候就会设置合理的合并规则。
例如student.class_id=class.id; 设置这个合并规则后，只有符合规则的数据才会被合并到一起获取出来然后构建新表。在这种模式下，合并就有三种不同的方式：内连接、外连接、自连接。

3.2.1 内连接

  1. 内连接：对两张表进行连接，但是连接的时候只取出符合规则的数据交集
    在这里插入图片描述
  2. 语法

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

  1. 举例

select * from classes inner join stu on classes.id= stu.classes_ id;
在这里插入图片描述

3.2.2 外连接

  • 外连接：以两张表中某张表为基表，然后再另一张表中找到符合合并规则的数据，将其合并出来

（1）左连接：以左表作为基表：
在这里插入图片描述
符合条件的直接连接上，没有的就以null进行填充
（2） 右连接：以右表作为基表：
在这里插入图片描述

具体SQL操作：

join … on …

  1. 内连接：inner join … on…
    select * from classes inner join stu on classes.id= stu.classes_ id;
    在这里插入图片描述
  1. 左连接： left join … on
    select * from classes left join stu on classes.id=stu.classes_id;
    在这里插入图片描述
  1. 右连接： right join … on …
    select * from classes right join stu on classes.id=stu.classes_id;
    在这里插入图片描述

3.2.2 内连接外连接举例：

  1. 创建表
    1）创建班级表classes，id为主键

– 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
desc VARCHAR(100)
);
在这里插入图片描述

2）创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，classes_id为外键，关
联班级表id

– 创建设置学生表
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)
);
在这里插入图片描述

3）创建课程表

– 创建课程表
DROP TABLE IF EXISTS course;
CREATE TABLE course (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20)
);
在这里插入图片描述

4）创建学生课程中间表，考试成绩表

– 创建课程学生中间表：考试成绩表
DROP TABLE IF EXISTS score;
CREATE TABLE score (
id INT PRIMARY KEY auto_increment,
score DECIMAL(3, 1),
student_id int,
course_id int,
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);
在这里插入图片描述

  1. 插入数据
    1）插入classes表信息

insert into classes(name, desc) values
(‘计算机系2019级1班’, ‘学习了计算机原理、C和Java语言、数据结构和算法’),
(‘中文系2019级3班’,‘学习了中国传统文学’),
(‘自动化2019级5班’,‘学习了机械自动化’);
在这里插入图片描述

2） 插入studnent表信息

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

3） 插入course表信息

insert into course(name) values
(‘Java’),(‘中国传统文化’),(‘计算机原理’),(‘语文’),(‘高阶数学’),(‘英文’);
在这里插入图片描述

4）插入score表信息

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

  1. 查询信息
    1）查询许仙同学的成绩：

a）分解查询
select id from student where name=‘许仙’;
select score from score where student_id=4;
在这里插入图片描述
b）合并查询

select * from student
inner join score
on score.student_id =student.id
where student.name=‘许仙’;
在这里插入图片描述

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=‘许仙’;
在这里插入图片描述

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

a）分解查询

查询学生表所有同学信息
select * from student;
查询成绩表所有信息
select * from score;
在这里插入图片描述
查询所有同学总成绩：按student_id分组查询学生的总成绩
select student_id,sum(score) from score group by student_id
在这里插入图片描述
b) 组合查询
select student.*,sum(score.score) from student
inner join score
on student.id=score.student_id
group by student.id;
在这里插入图片描述

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

a）分组查询
select * from student;
select * from score;
在这里插入图片描述

b)联合查询
select student.id,student.name,score.score,score.course_id from student
inner join score
on student.id=score.student_id;
在这里插入图片描述
select student.id,student.name,score.score,course.name from student
left join score on student.id=score.student_id
inner join course on score.course_id=course.id ;
在这里插入图片描述

select student.id,student.name,score.score,course.name from student
left join score on student.id=score.student_id
left join course on score.course_id=course.id ;
在这里插入图片描述

3.2.3 自连接

  • 自连接：同一张表自己连接自己进行查询
  • 案例：
    1）显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
    将成绩表进行自连接，使用第一张成绩表中的计算机原理成绩和第二张成绩表中的Java成绩进行比较

select score1.student_id, score1.score, score2.score from score as score1
inner join score as score2 on score1.student_id=score2.student_id
inner join course as course1 on score1.course_id= course1.id
inner join course as course2 on score2.course_id = course2.id
where course1.name= ‘Java’ and course2.name= “计算机原理” and score1.score <score2.score;
在这里插入图片描述
select student.name, score1.score, score2.score from score as score1
inner join score as score2 on score1.student_id=score2.student_id
inner join course as course1 on score1.course_id= course1.id
inner join course as course2 on score2.course_id = course2.id
inner join student on score1.student_id= student.id
where course1.name= ‘Java’ and course2.name= “计算机原理” and score1.score <score2.score;
在这里插入图片描述

一个错误代码
select score1.student_id,score1.score,score2.score from score as score1
inner join score as score2 on score1.student_id=score2.student_id
inner join course as course1 on score1.course_id=course1.id
inner join course as course2 on score2.course_id=course2.id
where course1.name=‘Jave’ and course2.name=‘计算机原理’ and score1.score < score2.score;
在这里插入图片描述
我找了大半天！觉得自己没问题~想不通为啥！！最后被同学找到了！！告诫自己一定要细心细心再细心！！

3.2.4 子查询

子查询：也是多表联查的一种，但是跟连接又有所不同，它是将一条查询语句的结果，当做一张表，再次进行过滤查询。简单的说，就是一条查询语句的条件，是另一条语句的结果。

1. 单行子查询

单行子查询：返回一行记录的子查询

示例1：查询与“不想毕业” 同学的同班同学：
select classes_id from student where name=‘不想毕业’;
select * from student where classes_id = (select classes_id from studnet where name=‘不想毕业’);
在这里插入图片描述

2. 多行子查询

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

  1. [not] in 关键字；

示例1：查询语文或者英文课程的成绩信息
select id from course where name =‘语文’ or name =‘英文’；
select *from score where course_id =(select id from course where name =‘语文’ or name =‘英文’);
在这里插入图片描述
因为子语句返回结果有多条，所以此时就不能直接使用等号进行判断，这个时候要使用 in 关键字，表示只要在子查询结果中就符合条件。
select *from score where course_id in(select id from course where name =‘语文’ or name =‘英文’);
在这里插入图片描述

示例2：查询不是语文或者英文课程的成绩信息
select *from score where course_id not in(select id from course where name =‘语文’ or name =‘英文’);
在这里插入图片描述

  1. [NOT] EXISTS关键字：
    exists主要是判断子语句是否有结果，或者说是一个真或假的判断。简单理解，在score表中查询成绩信息，每取出一条信息，就会通过子查询判断是否是语文或者英文的课程信息，如果是则取出，否则则丢弃。

实例1：
1）先从score表中获取一条成绩信息
2）从course表中查询课程信息，课程必须是语文或者英文的课程
3）判断获取到的这条成绩的课程id是否与课程信息的相同
4）判断为真，则取出成绩信息。判断为假表示没有符合条件的信息。
查询从课程表中查询语文或者英文信息，但是前提是这个课程必须在成绩表中有对应的课程成绩信息。
select sco.id from course cou where (name=‘语文’ or name=‘英文’) and cou.id = sco.course_id)

select * from score sco where exists (select sco.id from course cou where (name=‘语文’ or name=‘英文’) and cou.id = sco.course_id);
查询

3. 在from子句中使用子查询
  • 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一 个临时表使用。
  • 示例：查询所有比“中文系2019级3班”平均分高的成绩信息：
阅读全部

相关推荐

  3. MySQL表的增删改查（进阶）

    2024-07-18 14:12:04       34 阅读

  8. 【数据库】MySQL表的增删改查

    2024-07-18 14:12:04       47 阅读

最近更新

  3. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-07-18 14:12:04       70 阅读

  4. Could not load dynamic library ‘cudart64_100.dll‘

    2024-07-18 14:12:04       74 阅读

  9. 在Django里面运行非项目文件

    2024-07-18 14:12:04       62 阅读

  19. Python语言-面向对象

    2024-07-18 14:12:04       72 阅读

  20. 如何分清楚常见的 Git 分支管理策略Git Flow、GitHub Flow 和 GitLab Flow

    2024-07-18 14:12:04       66 阅读

热门阅读

  1. 模乘逆元计算器

    2024-07-18 14:12:04       22 阅读

  10. 算法面试题六

    2024-07-18 14:12:04       23 阅读

  13. c++数据结构——栈

    2024-07-18 14:12:04       23 阅读

  15. 搞定前端面试题——TCP和UDP！！！

    2024-07-18 14:12:04       20 阅读

  16. vue2路由跳转是异步的

    2024-07-18 14:12:04       22 阅读

  20. 日有所增，不见其长

    2024-07-18 14:12:04       20 阅读

  21. Python面试整理-Python的数据类型，分别有哪些？

    2024-07-18 14:12:04       21 阅读

  23. WordPress与 wp-cron.php

    2024-07-18 14:12:04       18 阅读

  24. Visual Studio 快捷键大全：提升前端开发效率的必备工具

    2024-07-18 14:12:04       20 阅读

  27. LeetCode //C - 231. Power of Two

    2024-07-18 14:12:04       23 阅读

  30. Leetcode617. 两个二叉树相加

    2024-07-18 14:12:04       17 阅读