[大数据]MySQL增删查改(进阶)

前言
所谓MySQL进阶其实就是在初阶的基础上将那些基础的增删查改进行组合使用,然后在引进一些方法来学习的,所以说大家如果没有初阶的基础的话,请看一下这篇两篇文章数据库基础 , MySQL增删查改(初阶)

MySQL增删查改(进阶)

1.数据库的约束

1.1约束类型

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

1.2 NULL约束

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

create table student (id int not null,name varchar(20),score decimal(3,1)) ;

1.2 UNIQUE: 唯一约束

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

 create table student (id int unique,name varchar(20),score decimal(3,1)) ;

1.3 DEFAULT:默认值约束

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

create table student (id int  ,name varchar(20),score decimal(3,1) default 99) ;

1.4 PRIMARY KEY：主键约束

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

create table student(id int primary key auto_increment,name varchar(20),score decimal(4,1));

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大
值+1。


主键约束是不能为NULL的,这里因为使用了auto_increment,所以默认自增了,然后我们又直接插入了name,score并没有插入id,此时也是因为auto_increment,自增了!

1.5 FOREIGN KEY：外键约束

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

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

简单来说外键约束是用来建立表与表之间的联系的,就是说你创建外键约束的那个字段必须得参考主表中是否存在,也就是相当于一个子集关系
比如说

我们先创建一个学生表(id,name),在创建一个成绩表(id,score),如果没有外键约束的话,成绩表中有的id在学生表中并不存在,所以我们得搞一个外键约束住成绩表中的id,当不存在时,这不能创建!!!

创建学生表

 create table student(id int  primary key auto_increment,name varchar(20));

创建成绩表

create table score(id int primary key ,score decimal(4,1),foreign key (id) references student(id));

通过外键约束可知成绩表是依附学生表而存在的!!!

我创建了1,2这两个学生,当我们给他们赋值成绩是没有问题的,当我们给3号学生赋值成绩就报错了,原因就是外键约束! 因为学生表中没有3号学生,所以当我们一开始插入时是不可以先插入成绩表的,得先插入学生表!!!

PS: 当我们的主表别其他表通过外键约束时,我们是 不可以直接删除主表的!!

1.6 CHECK 约束(了解)

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

create table user_test (
id int,
name varchar(20),
sex varchar(1),
check (sex ='男' or sex='女')
);

我们发现它并没有检查,所以在MySQL中无用!!

2. 表的设计

2.1 一对一

2.2 一对多

2.3 多对多

3.查询(select)

3.1 聚合查询

3.1.1 聚合查询

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

案例:

3.1.2 分组查询:group by

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

select column1, sum(column2), .. from table group by column1,column3;

案例:

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

我现在想要知道每种role中的最高工资:

3.1.3 HAVING

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

案例:
显示平均薪资超过1000的role

4.2 联合查询(多表查询)

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

注意：关联查询可以对关联表使用别名。
测试数据:

create table classes (id int primary key auto_increment,name varchar(20),`desc` varchar(100));

create table student (id int primary key auto_increment,sn varchar(20),name varchar(20),qq_mail varchar(20),classes_id int);

create table course (id int primary key auto_increment,name varchar(20));

create table score (score decimal(3,1),student_id int,course_id int);

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. 查找名字为"许仙"的同学的各科成绩
   思路:将学生表和成绩表和学科表笛卡尔积,通过where筛选出来

 select student.name as name,course.name as course,score.score as score from student,course,score where student.name='许仙'and course.id=score.course_id and student.id=score.student_id;

2. 查找所有同学的总成绩,以及该同学的基本信息

select student.id,student.name, sum(score.score) from student,score where student.id = score.student_id group by student.id;

注: group by只能表示同种组的一条记录

4.2.2 外连接

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

语法:

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

意思就是:

左连接就是以左表为基准表(就是左表的所有列的数据都得显示,即使为NULL),右连接就是以右表为基准表(就是右表的所有列的数据都得显示,即使为NULL),基准没有数据也要显示

大家可以没理解意思,那么我们用例子说明!

先举一个子连接的:

1. 查找所有同学的每一科的成绩,和同学的相关信息

思路 : 需要3张表 studednt表 score表 course表
先对3张表进行笛卡尔积. 然后根据id进行筛选

select student.id,student.name,course.name,score.score from student,score,course where student.id = score.student_id and course.id = score.course_id;

在看一个左连接:

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

select student.id,student.name,course.name,score.score from student left join score on student.id = score.student_id left join course on score.course_id = course.id;

我们可以看出来内连接和外连接的细微区别了吧,其实这两者写法基本一样,只不过是显示的结果不一样,所有用内外连接这种名称来区分它!

4.2.3 自连接

自连接是指在同一张表连接自身进行查询。
适用于行之间的比较,通过自连接将行之间的比较转化为列之间的比较

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

力扣:删除重复的电子邮箱
大家可以尝试一下!

4.2.4 子连接

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

• 单行子查询：子查询只有一行
  示例1: 查询与“不想毕业” 同学的同班同学：

select name 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 where exists (select score.course_id from course where (name = '语文' or name = '英文') and course.id = score.course_id);

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

exists和in的区别

In:是把外表和内表做Hash 连接，而exists 是对外表作loop 循环，每次loop循环再对内表进行查询。
当查询两个表的大小相当时，用In 和 exists差别不大。
如果两个表中一个表较小,一个表较大，那么子查询表大的用exists,子查询表小的用In，效率会高的。

也就是说 IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况，这样效率会高的

4.2.5 合并查询

相当于把多个查询的结果集合合并成一个集合
可以使用集合操作符union，union all

示例1: 查询 id < 3 或者 名字为 "英语"的课程

• union(和or相当)

select * from course where id < 3 union select * from course where name = '英文';

如果两个查询结果中存在相同的记录,就会只保留一个
如果不想去重,可以使用 union all即可.

• union all
  该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行
  案例：查询id小于3，或者名字为“Java”的课程

select * from course where id<3 union all select * from course where name='Java';

4.2.6 内连 外连 集合图