学好MySQL增删查改，争取不做CURD程序员【下篇(六个小时肝MySQL万字大总结)】

✨✨hello，愿意点进来的小伙伴们，你们好呐！
🐻🐻系列专栏：【MySQL初阶】
🐲🐲本篇内容：一套打通MySQL基础操作.
🐯🐯作者简介:一名现大二的三非编程小白，日复一日，仍需努力。

这篇文章我将对MySQL的使用操作进行一次大总结,没有很明确的顺序,可能内容顺序有点乱,大家见谅

分组查询:

在上篇文中,我讲解了数据库的查询的基础操作,但是那些对于实际应用中还远远不够.比如:我们在实际上有时候查询会根据某些字段来分类查询

例如:在员工表中,不同员工有不同的部门,所以我们有可能会根据部门的分类来查询,比如说:查询每个部门的平均工资:那么在这个时候之前用的查询方法就不够用了,现在将引入一个 order by 子句,所以该语句就可以很好的解决我刚刚所提的需求

演示:

我们将使用group by 子句在emp表中去查询每个部门的平均工资:

select deptno,  avg(sal) from emp group by deptno;

使用该查询时,MySQL会先将数据分组,然后再进行平均数的计算

然后如何我们分组后想要过滤条件,其实是不可以使用where过滤条件的,只能使用having,当然是在分组后的条件过滤,要是在分组前的条件过滤还是可以使用where的

接下来来个案例演示:

我们将使用group by 子句在emp表中去查询部门的平均工资高于1000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal) > 1000;

group by 与 having 经常是成对出现的,值得我们注意:

分页查询:

在我们实际上的查询中,有时候数据量会比较大,然后这时候要是将数据全部显示出来就会让数据的查询者看的眼花缭乱,我们想到可以解决该问题的方法就是将数据按照一定的数量一页一页的显示出来,就如在百度等搜索浏览器查询数据的时候,就会显示多页的信息,可能它的底层用的也就是分页查询吧这时候就用到了分页子句: limit start ,rows 表示从start+1行开始,取出rows行,start从0开始算起.

接下来来看个案例演示一遍分页查询

演示:

我们想要在emp表中按照员工的工资进行降序排列,取出工资最高的一位,(即分页,一页一个人,取第一页)

select ename,sal from emp order by sal desc limit 0,1;

数据分组的总结:

数据分组的语句包含 : group by,having,limit,order by
所以对于这些语句同时在select中的情况下,就会有顺序之分,我们要遵循:
group by,having,order by,limit该顺序来使用

多表查询:

在实际上的应用中,对于单个表的查询可以不会满足你的需求,大多数情况下都是两张或者以上的表来进行查询.这就是多表查询

笛卡尔集:

笛卡尔集是对于多表查询操作中肯定会遇到的一种情况,多表查询就是基于笛卡尔集的基础上来进行过滤的,接下来我们来看看这种情况是怎么一回事,

在数据库中存在两张表 ---- emp , dept
接下来我们来对这两张表进行多表查询操作,展示笛卡尔集:

 select * from emp,dept;

我们会发现对于查询后的结果,从两个表的3 , 4 行变为了12行,其实就是emp表的每一行和dept表的每一行组合一下,排列组合,所以就会有 4 * 3 = 12行

那么对于该笛卡尔集,我们想要转为我们需要的查询结果,就要对笛卡尔集进行过滤操作,那么首先我们就要先找到要过滤的条件

通过观察该表,我们会发现emp 的 deptno 与 dept 的 deptno是一致的,那么就可以以这个条件为过滤条件,进行过滤

select * from emp,dept where emp.deptno = dept.deptno;

这时候我们就将笛卡尔集中的重复部分给过滤掉 ,留下我们要使用的一个表

自连接:

自连接:顾名思义就是自己与自己的连接,就是将一个表来当成两个表来使用,在同一个表上的查询,自连接可以很好解决对于一个表中行与行之间的关系查询

演示:

我们想显示公式员工和他上级的名字

 select workers.ename as '员工名' ,superior.ename as '上级名' 
  from emp as workers,emp as superior where  workers.mgr = superior.empno;

在自连接中,一个表会出现两次,所以我们应该对每个表进行别民操作,避免MySQL无法识别你要查询的表而报错

子查询:

子查询就是在一个查询中嵌套多个查询语句

单行子查询

对于子查询语句只返回一行的查询,我们称为单行子查询.

演示:

我们要查询与SMITH同一个部门的员工

首先我们要先查询出SMITH是在哪个部门,然后再对整表进行查询,找到与SMITH同一个部门的员工

1.

select deptno from emp where  ename = 'SMITH';1

2.

select * from emp where deptno = (select deptno from emp where  ename = 'SMITH');1

多行子查询:

对于子查询语句返回多行的查询,我们称为多行子查询.

演示:

如何查询与部门30里面的员工的工作相同的员工,不包括部门30的员工

1.找出部门30的员工的所有工作

select job from emp where deptno = 30;

2.使用该表进行子查询,

select * from emp 
where job in (select job from emp where deptno = 30) and deptno != 30;

all 与 any 多行子查询的使用:

显示工资比部门30所有员工工资高的员工信息

select * from emp where sal > all(select sal from emp where deptno = 30);

显示工资比部门30某一员工工资高的员工信息

select * from emp where sal > any(select sal from emp where deptno = 30);

多列子查询:

多列子查询指的是查询返回多个列的子查询语句

如何查询与SMITH部门与岗位完全相同的所有雇员

1.查询smith的工作与部门

select deptno , job from emp where ename = 'SMITH';

2.使用多列子查询的语句查询

select * from emp  
where(deptno,job) = (select deptno , job from emp where ename = 'SMITH');

子查询临时表:

我们可以把子查询表也可以当中一个临时表来进行查询.

演示:

我们想再每个部门查找工资高于本部门的平均工资的人

1.先查询每个部门平均工资

select deptno, avg(sal) from emp group by deptno;

2.将该表当成临时表进行使用

select * from 
emp ,(select deptno,avg(sal) as sal_avg  from emp group by deptno) temp 
where emp.deptno = temp.deptno and emp.sal > temp.sal_avg;

查询每个部门的信息(包括：部门名,编号,地址)和人员数量

1.先找出每个部门的人数:

select deptno , count(1)  from emp group by deptno;

2.然后使用该表当做临时表来查询

select dept.* , temp.count_ from 
dept , ( select deptno , count(1) count_  from emp group by deptno) temp  
where dept.deptno = temp.deptno;

合并查询:

在实际应用上,为了合并多个select语句的结果,可以使用union操作符来操作
union可以将两个表给联合起来到一个表,条件是:两个表的列需要一致.

演示:

1. 查询sal大于2500与job = 'MANAGER’的员工

select * from emp where sal > 2500 union select * from emp where  job = 'MANAGER';

union 与 union all 都可以做到将两个表给合并起来,但是union会自动去重,将查询到的重复行给去掉,union all 则将查询到的所有行给显示出来

表外连接:

我们先用一个案例来看看外连接的实际上的应用及好处

 select * from student_lhj;
  select * from scores;1

在上述两张表中我们想查询学生id,姓名与对应的分数,在没有使用外连接的知识前,我们会用以下操作:

 select student_lhj.* , score from student_lhj,scores where student_lhj.id = scores.id;

查询到的结果为:id为1与3的学生的信息查询出来,但是id为2的学生的信息并没有查询出来,这个是因为id为2的学生的成绩为null,那么这样子的画就不会查询到该学生的信息,
但是对于实际上来说其实是不合理的的,难道学生没有成绩就查不到它的信息吗,要是有可能是该学生没有去考试呢

对于上面这种情况我们就可以使用外连接来优化查询.

select student_lhj.* , score from
 student_lhj left join  scores on student_lhj.id = scores.id;

上述使用了左外连接,将左表的所有消息都一一不漏的显示出来,on是过滤条件,
当然有左外连接就会有右外连接,其实是一个道理,就将left改为right

演示:

列出部门名称和这些部门的员工消息,同时列出没有员工的部门

select dept.dname,emp.* from emp right join dept on emp.deptno = dept.deptno;

约束:

在数据库中约束确保了数据库满足特定的商业规则,在MySQL中约束包括:
not null , unique , primart key , forreign key ,check
not null 是不为null约束,在前面有介绍过了,我主要介绍后面四种约束:

primary key (主键)

该约束使字段不可以重复且不能为null,用于标识表中唯一字段

演示:

1.我们创建一个表,然后将该表的id字段设置为主键.

create table test_1 (id int primary key ,name varchar(32));

2.往表中插入一个数据,接下来我们再来插入一个id为1的数据看看有上面后果.

3.如图,错误提示说,id为主键无法插入

 insert into test_1 values(1,'罗基');

细节:

1. primary key 不可以重复且不能为null
insert into test_1 values(null,'罗基');

2. 一张表中只能有一个主键,但是可以为复合主键

创建复合主键只能在定义表的末尾创建,同时指定两个字段为主键字段

create table test_2(id int,name varchar(32),primary key(id,name) ) ;

在查询表构造中,我们可以看的key列上标志了主键字段

现在表中有一个数据行,接下来我们来检验复合主键是否有效

往表中存进一个id相同,name不同的值依旧可以存进去,这个是怎么回事呢,难道主键无效吗?

当我们往表中插入id与name同时存在的元素时,将无法插入,会报为主键无法重复的语句

小结: 在表中插入重复主键,那么当复合主键的两个字段同时满足重复的条件时候将无法插入

在实际上开发中,每个表往往都会设计一个主键,所以主键的知识需要我们掌握

unique(唯一)

unique是唯一约束,约束后,该列的值是不可以重复的,与主键好像很相似,但是该约束是可以为null的主键约束是不为null,且该约束在一个表中可以有多个存在

演示:

一个约束的表与主键约束几乎无差异,我们创建一个拥有两个唯一约束的表,

create table test_3 (id int unique ,name varchar(32) unique );

该表中插入一个数据,id与name皆为`唯一约束

然后继续往表里存入数据,你会发现无论是id或者name其中任何一个字段重复都无法插入

primary key 与 unique 与 not null 的关系:

我们会发现unique与primary key 就相差个是否可以为null,但是在约束中也会有not null ,他们三者之间会有什么关系吗,接下来我们来探究一下

我创建一个表.id字段的约束为 unique与not null.
任何看表的构造,我们会发现,表中的id字段的约束为primary
好像就是not null + unique = primary key

 create table test_4(id int unique not null,name varchar(32));

foreign key(外键):

用于定义主表与从表之间的关系:外键约束要定义在从表上,主表必须具有主键或者唯一约束,当定义外键后,要求外键列数据必须在主表的主键列存在或者为null

接下来我们来设计一个学生-班级表来体会一下外键的用处

1.创建一个class表,class_id为主键

 create table class_test (class_id int primary key);

2. 创建student表,将学生表中的class_id与class表中的class_id联系起来

 create table student_test 
 (id int ,class_id int,foreign key(class_id) references class_test(class_id));

3.先往教室表中插入id为1 与 2 的教室,然后再往学生表中插入学生消息,class_id将为教室表中存在的教室id,插入成功


4.接下来我往学生表中插入学生信息,且学生教室id不在class表中存在,这时候会报错—外键错误.

insert into student_test values(1008,3);

现在突然有一天id为1的班级要取消的掉,但是id为1的班级有外键的约束,那可以直接在表中删除id为1的班级吗

答案是不行的

 delect form class_test where class_id = 1;

因为主键有外键的影响下无法随意删除,那么我们可以把该班级的置为一个知道是不存在的逻辑上的删除

细节:

1. 外键指向的表的字段,必须为主键或者唯一约束
2.表的引擎必须是innodb,这样子才支持外键
3.外键的字段类型与主键的字段类型要一致(若是有长度的类型,长度可以不相同)
4. 外键字段的值必须在主键字段出现过,或者为null,一旦建立外键的关系后,就不可以随意删除主键了

check:

该约束是用于强制行数据必须满的条件

演示:

强制表中的sal数据必须在1000,2000之间,不然就会报错

CREATE TABLE t23 (
id INT PRIMARY KEY, `name` VARCHAR(32) ,
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
);

oracle与sql server 均支持check,但是mysql中5.7版本目前还不支持check,只能做语法校验,但是不会生效

自增长:

在某张表中,存在一个1id列(整数的),我们如果希望在添加记录的时候,该列从1开始,且自动的增长,该怎么处理呢? 这就用到了自增长语法
自增长是与primary key来配合使用的或者与unique配合

演示:

1.创建一个id自增长的表,接下来我们往表中插入数据,观察增长的趋势

create table test_5(id int primary key auto_increment , name varchar(32));

2.往自增长的字段存入null,MySQL就会自动给我们增长,从1开始

insert into test_5 values(null,'lhj');

3.继续往表中存入数据,观察到增长的幅度是1.

4.然后也可以指定id插入

insert into test_5 values(100,'lhj');

5.如果`这个时候继续往表中存入数据使其增长,那么会从哪个数字开始增长呢?
我们会发现会从100 + 1来开始增长,那么就可以得出一个结论,增长的起始数若表中有数据的情况下是找到表中增长字段最大的那个数开始增长

细节:

==1. 自增长的字段一般为整型,小数也会有但是很少见
2. 自增长默认从1开始,但是我们也可以改变增长的默认起始值,
alter table 表名 auto_increment = 新的开始

索引:

数据库的查询有时候,会是很多数据量一起查询,那么提高数据库的性能就必不可少,对于数据库性能来说,索引是一个物美价廉的的东西噢,加了索引可以比不加索引的查询速度提升百倍千倍.下面我们来举个例子来体会一下索引的好处

使用索引体会

1.我们来创建一个海量表
创建该表所需要的时间会很久,如果去尝试创建表达小伙伴请耐心等待,

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $$

#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 declare return_str varchar(255) default '';
 declare i int default 0; 
 while i < n do
    # concat 函数 : 连接函数mysql函数
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end $$


 #这里我们又自定了一个函数,返回一个随机的部门号
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$

 #创建一个存储过程， 可以添加雇员
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
 #autocommit = 0 含义: 不要自动提交
 set autocommit = 0; #默认不提交sql语句
 repeat
 set i = i + 1;
 #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
 #commit整体提交所有sql语句，提高效率
   commit;
 end $$

 #添加8000000数据
call insert_emp(100001,8000000)$$

#命令结束符，再重新设置为;
delimiter ;

该表中有80w行数据

SELECT COUNT(*) FROM emp;

现在我们对该表进行查询,在没有创建索引前执行的时间为3.459秒,要知道对于计算机来说一般都是以毫秒为单位的,以秒为单位实在是一个很长的时间了.

SELECT * FROM emp WHERE empno = 1234567;

接下来我们来以empno该字段创建索引,然后重新执行查询操作,我们会发现查询的时间居然从3秒变为2毫秒,增长倍数数万倍.

CREATE INDEX empno_index ON emp (empno)

然后加了索引后,表的空间由52wkb变为了63wkb增长10wkb,这就是典型的以空间换时间,那么在索引的背后又会是什么支持他有这么快的查询速度的呢?

索引中的数据结构:

为什么加入索会导致查询速度的提升呢?索引背后究竟是什么数据结构呢?

在索引的背后其实是B+数的支撑,由于有这种可以说专门为数据库索引的数据结构,所以在数据库查询中索引才会速度那么快.

索引的类型:

索引的类型有4种:

1.主键索引,在我们创建主键字段的时候,主键就会自动添加主索引.
2. 唯一索引.
3. 普通索引:就是我们自己创建的索引.
4.全文索引:

索引增删改:

1.添加索引:
在给test_5表中的id创建一个索引有两种方式:

create index id_index on test_5(id);

alter table test_4 add index index_id(id);

2.添加主键索引:

alter table test_3 add primary key(id);

3.删除索引:

drop index index_id on test_4;

 alter table test_4 drop index index_id;

4.删除主键索引:

 alter table  test_3 drop primary key;

5.查询索引:

show index from test_4;

show keys from test_4;

desc test_4;

索引创建规范:

1. 较频繁出现的作为查询条件字段应该创建索引
2.唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
3.更新非常频繁的字段不适合创建索引
4,不会出现在where子句中的字段不\应该创建索引

事务:

在实际应用中有时候会多条语句同时执行的情况,多条语句必须同时成功执行或者执行失败;例如转账,在转账中总会有转出与转入的一方,这个时候就不允许出现一方成功一方失败的原因,不然全世界都乱套了.那么这个时候要是我们可以把转出与转入的语句给绑定在一起,就可以很好的解决这个问题.

事务就可以很好的解决该问题,接下来我们来了解一下事务的情况吧

事务用于保证数据的一致性,由一组dml命令组成,改组命令要么全部成功要么全部失败.

下面这个简图简略说明了事务的作用与操作

事务的基础操作:

开启一个事务

start transaction;

往表中插入数据.

 insert into test_6 values(1,'lhj'),(2,'lhj2');

** 设置保存点a**

savepoint a;

继续插入数据

设置保留点b

savepoint b;

回退事务到保存点a,我们来观察一下表的变化

rollback to a;

然后我们现在发现事务回滚成功了,

我们来回滚全部事务

rollback;

提交事务,所有操作就生效,无法继续回退

commit;

在开启事务操作的时候,执行dml语句,MySQL会在表中加锁,防止其他用户修改表的数据

事务细节:

1. 如果不开启事务,默认情况下,dml操作是自动提交的,不可以回滚
2. 如果开始一个事务,你没有创建保存点,只能执行rollback语句,回滚到事务开头
3.可以在没有提交的事务里创建多个保存点
4.你可以在事务还没有提交前,选择回退到哪个保存点
5.MySQL的事务只能在innodb存储引擎中使用

事务的隔离级别:

在多个连接开启各自事务操作数据库中数据时,数据库系统要复杂隔离操作,以保证各个连接在获取数据的时候的准确性

事务的隔离级别如下:
脏读: 当一个事务读取另一个事务还没有提交的改变;
不可重复读:同以查询在个事务多次进行,由于其他提交事务所做的修改和删除,每次返回的结果集,此时发生不可重复读
幻读:同一查询在同一事务中多次进行,由于其他的提交事务所做的插入操作,每次返回的结果不同的集,此时发生幻读.
MySQL默认情况下隔离级别为可重复读,一般情况下不需要修改

MySQL隔离级别定义了事务与事务之间的隔离程度

设置事务隔离级别:

查询当前会话事务级别

select @@tx_isolation;

查询当前系统事务级别

 select @@global.tx_isolation;

设置当前事务隔离级别

set session transaction isolation level repeatable read;

设置当前系统隔离级别

 set global transaction isolation level repeatable read;

事务的特性:

1.原子性:事务中是一个不可分割的单位,要么都发生,要么不发生
2. 一致性:事务必须使数据库从一个一致性状态变为另一个一致性状态
3. 隔离性:多个事务并发访问数据库时,多个事务之间会相互隔离
4. 持久性:一个事务一旦被提交,就是永久性的数据修改