MySQL的基础部分（基础部分完结）

MySQL的基础部分（基础部分完结）

知识小回顾

小案例部分

1.-- 如何查找1992.1.1号入职的员工

SELECT * FROM emp

WHERE hiredate>'1992-01-01';-- 这个格式要与我们的我们自己表中的数据进行比较

– 显示首字符为S的员工的姓名和工资

SELECT ename,sal FROM emp -- 这个一定要注意的是我们如何进行具体的操作处理
WHERE ename LIKE 'S%';

3.-- 如何显示第三个字符为大写O的所有员工的姓名和工资

这里说明一下就是我们之前前面是不加__这个的 但是这次题目要求的是从第三位开始是O的才开始进行计算处理

SELECT ename,sal FROM emp -- 前面的_是代表我们的前面几个是可以是任意的数据
WHERE ename LIKE '__O%';

4.-- 如何显示没有上级的员工的信息 – mgr代表上级编号

我们借助了 这个mgr列中 数据为null的我们就进行显示 我们不能写成

mgr=null 这样是不可以的

SELECT * FROM emp
WHERE mgr IS NULL; -- 我们要是查看某个是不是为空 我们要采用is null 而不是等于进行操作处理

5.-- 查询表的结构

DESC emp;

6.order by 语句的复习

1.-- 如何按照工资从低到高(我们默认就是升序)，显示员工信息

SELECT * FROM emp
ORDER BY sal ASC;   -- 我们asc是升序的意思  这里我们要是默认什么都不写的话，就会是升序排列

2.按照部门号升序，而雇员的工资进行降序排序，显示雇员信息

说一下，当我们遇到多个排序的处理的时候，我们应当排序的时候按照我们自己写的顺序来进行。

SELECT * FROM emp   
ORDER BY deptno ASC  -- 如果有多个排序的需求 我们排序的顺序是按照我们写的排序顺序来进行
, sal DESC;

分页查询

讲讲为什么分页查询会出现 是因为我们的数据量如果是很大的话，我们没法将数据直接显示到我们的整个页面上边去了，

这个时候我们就要将我们的数据进行所谓的分页查询

1.任务驱动

– 按雇员的id号进行升序取出，每页显示3条记录，请分别显示第一页，第二页

2.基本语法

-- 基本语法 select.... limit start ,rows
-- 表示从start+1行开始取，取出rows行 start从0开始取
-- 第一页

代码展示

SELECT * FROM emp
ORDER BY empno
LIMIT 0,3

-- 第二页
SELECT * FROM emp
ORDER BY empno
LIMIT 3,3

-- 第三页
SELECT * FROM emp
ORDER BY empno
LIMIT 6,3

技巧

SELECT * FROM emp
ORDER BY empno
LIMIT (每页显示的记录数)*（第几页-1），每页显示的记录数

也就是说我们的limit这里 我们要书写的是上边的公式就可以帮助我们进行查询

分组查询增强版本group by

1，任务驱动

– 显示各种岗位的雇员总数，平均工资

这里用到我们的count关键字

讲到count我们就说一下，他的注意事项

首先count里面传递的参数，其实是由我们的数据决定的

要是我们传入的是*号的话，我们就会选中所有的行 不管是不是空，但是我们要是在count（）里面传递的参数是null

count这个函数就不会帮助我们进行计数操作，我们可以利用这个特性解决下面问题

1.-- 显示雇员总数，以及获得补助的雇员数(这里充分利用了，count的概念)

-- 思路： 获得补助的雇员数 就是comm列非null
SELECT COUNT(*),COUNT(comm)-- 空就不会统计
FROM emp;

2.-- 统计没有获得补助的员工数

我们就可以借助count 这个不会统计null的这个特性 帮助我们进行筛选不必要的

SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL))-- 如果为空 我们就返回1返回1不就是可以进行计算数据了
//第二个写法就是，我们采用相减的形式进行计算
SELECT COUNT(*),COUNT(*)-COUNT(comm) -- 采用减法也是可以的

3.显示管理员的总人数

 -- disytinct 是一个去重的操作
 SELECT COUNT(DISTINCT mgr)
 FROM emp;

4.显示雇员工资的最大差值

这个非常简单，我们用两个数学函数就欧克了

SELECT MAX(sal)-MIN(sal)
FROM emp;

总结多子句查询

遵循下述规则

先是分组

需求判断

排序

分页

当然以上也是根据实际情况看看是不是真的需要这么多语句

任务驱动

– 请统计 每个部门(group by) 的 平均工资(avg) ，并且是大于1000的(having)，
– 并且按照工资从高到低进行排序处理(order by)
– 提取出前两行记录处理(limit)

代码

SELECT deptno,AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno -- 分组
HAVING avg_sal>1000 -- 需求
ORDER BY avg_sal DESC -- 排序
LIMIT 0,2 -- 显示页数

简单总结多语句查询

select 后面跟上你要显示的数据

from 写出你要进行查询的位置

group决定你要根据什么进行分组查询

having决定你的需求

order by决定你要怎么排序 （desc）是降序

limit 决定你要一页显示多少数据

多表查询（重点，难点）

就是实际开发过程中 一张表的数据 可能根本就不够你去具体使用，所有会用到多表查询的技术去支持

1.认识笛卡尔表

就是我们将两个表进行合并成笛卡尔表，这个表的来源就是将第一个表的行数乘以第二个表的行数

就可以得到我们的这个表的总数之和了，

但是直接进行查询两个表是不能得到我们想要的数据的，因为这个笛卡尔表的实现是由第一个表的第一行乘以第二个表的所有行，得到的结果，这样就会导致一些数据的不匹配，从而使得我们的数据必须经过筛选才能进一步的进行操作处理，

那么我们来看一下如何进行具体的操作实现。

1.小案例1

– 显示部门号为10的部门名，员工名，和工资

SELECT ename,sal,dname FROM 
emp,dept
WHERE emp.`deptno`=dept.`deptno` AND emp.`deptno`=10;-- 继续进行过滤处理

2.小案例2

– 显示各个员工的姓名，工资，及其工资的级别

SELECT ename,sal,grade
FROM emp,salgrade
WHERE emp.`sal`>salgrade.`losal` AND emp.`sal`<salgrade.`hisal`

------

-- 采取between和 and都可以的
SELECT ename,sal,grade
FROM emp,salgrade
WHERE sal BETWEEN losal AND hisal;-- 就是这个来进行筛选的

3.小技巧

我们进行多表查询的时候需要注意的就是我们要进行的筛选的条件 不能少于我们要查询表的数量

比如说 我们要查询两个表 但是那，我们必须给出一个限制条件出来才行，因为我们不能直接进行处理，因为我们直接进行处理的话，就会导致我们会查询到很多没有用的消息出来，

比方说

下面这个奖金表

这里面存在的是我们最高和最低的奖 但是我们进行多表查询的时侯，我们会将两个表同时进行查询处理，

就会导致一个奖金 可能不是1等级 但是他也会进行匹配处理 那么这个时候就需要我们进行判断处理，就是确定哪些是需要进行保留的，哪些不需要进行保留。

SELECT ename,sal,grade
FROM emp,salgrade
WHERE emp.`sal`>salgrade.`losal` AND emp.`sal`<salgrade.`hisal`

这个语句就很好的进行了筛选，你可以看到的是，是什么呢，我们显示的等级是你的薪水满足两侧的钱才进行筛选的

并不是盲目的进行筛选

自连接

自连接就是将同一张表看做一张表进行连接查询

1.任务驱动

-- 显示公司员工和他的上级的名字

首先 我们要明确的是

这样迫使我们采用两张表进行查询

来看一下我们这个自连接的特点

-- 1.把同一张表当作两张表进行使用 2.需要给表取别名表  alias（别名）
-- 列名要是不明确 可以指定列名

代码展示

SELECT  worker.ename AS '职员名字',boss.ename AS '上级名字'-- 为了区分ename是哪一个 所以我们采取的是用worker和boss进行操作的
FROM emp worker ,emp boss -- 13*13 = 169-- 我们在这里进行了去了一个别名 
WHERE worker.mgr=boss.empno;-- 我的工人的上级编号就等于我们老板的编号

多行子查询

我们利用我们一个查询语句 返回几个数据 然后利用这几个数据 进行继续查询

1.任务驱动

– 如何显示和Smith同一部门的所有员工？

首先 我们不知道Smith是在哪个部门的

1.-- 分析 1.先查询到和Smith的部门的门牌号

SELECT deptno 
FROM emp
WHERE ename='SMITH';

2.-- 2.根据这个门牌号进行查询

-- 上面这个返回的是部门号 我们就是把上面这个当作一个子语句 放到我们的查询中去
-- 我们第一步先查到史密斯在哪个部门可以将这个返回的结果带入到里面去
-- 
SELECT ename
FROM emp
WHERE deptno=(SELECT deptno 
FROM emp
WHERE ename='SMITH');

2.例题2

– 小练习 如何查询和部门10的工作相同的雇员的
– 名字 岗位 工资 部门号 但是不含10自己的雇员

分析

1.首先查询一下部门10里面有哪些岗位

SELECT DISTINCT job-- 加distinct 是为了防止重复
FROM emp
WHERE deptno =10;

2.-- 2. 把上面查询的结果当作子查询使用

SELECT ename,job,sal,deptno
FROM emp
WHERE job IN (SELECT DISTINCT job -- 为什么要采用 in是因为in代表里面有不止一条数据等着我们进行查询，所以说
-- 所以说 我们要采用in
FROM emp
WHERE deptno =10) AND deptno !=10;

小结

本质就是一个语句嵌套一个语句的实现

就是利用 一个语句查询出来的数据 放到我们将要查询的操作当中去

子查询临时表

这个的本质和子行 或者多行的本质差不多 ，这个是先进行查询一次 借助我们查询出来的表 作为一个临时表

这个临时表可以作为我们继续查询的一个表进而继续使用

 -- 子查询临时表
 SELECT  cat_id , MAX(shop_price)
 FROM ecs_goods
 GROUP BY cat_id;

就是我们整个大表当中分离出来一个小表出来 作为我们第二次进行查询的表

-- 查询eshop里面的各个类别中，价格最高的商品

-- 查询 商品表
 -- 先得到 各个类别中，价格最高的

 -- 临时表
 -- 我们将我们查询出来的数据先放一下 作为一张表参与我们的查询工作中去
 SELECT goods_id,tmp.cat_id,goods_name,shop_price
 FROM ( SELECT  cat_id , MAX(shop_price) AS max_price
 FROM ecs_goods
 GROUP BY cat_id) tmp ,ecs_goods
 WHERE tmp.cat_id =ecs_goods.cat_id
 AND tmp.max_price=ecs_goods.shop_price

any/all关键字的使用

1.任务驱动

– 任务 显示工资比部门30的所有员工的工资高的员工的姓名
– 工资 和部门号

1.先找出部门30里面的数据

(SELECT sal 
FROM emp 
WHERE deptno=30

2.然后进行语句的嵌套，嵌套之后进行查询处理

SELECT ename,sal,deptno
FROM emp
WHERE sal>ALL(SELECT sal 
FROM emp 
WHERE deptno=30)

2.写法2

我们可以这么理解 大于所有 不就是大于全部吗

-- 第二种写法 查询到最大值
SELECT ename,sal,deptno
FROM emp
WHERE sal>(SELECT MAX(sal)
 FROM emp
WHERE deptno=30 )

2.任务驱动

-- 如何显示工资比部门30的其中一个员工的工资高的员工的姓名

其中一个 就是任意一个 any

SELECT ename,sal,deptno
FROM emp
WHERE sal> ANY (SELECT sal 
FROM emp 
WHERE deptno=30)

或者是大于最小的那个就行

-- 第二种写法 比它的最低的高就行
SELECT ename,sal,deptno
FROM emp
WHERE sal>  (SELECT MIN (sal) 
FROM emp 
WHERE deptno=30)

mysql多列子查询

1.任务驱动

– 如何查询与Smith的部门和岗位完全相同的所有的雇员（不包含它自己）

2.分析

-- 1.得到smith的岗位和职业

SELECT deptno,job
FROM emp
WHERE ename='SMITH'

2.把这个得到的查询结果当作子查询来使用，并且采取多列子查询进行匹配，需要注意书写的格式

-- 2.把这个查询当作子查询使用 并且使用多列子查询进行匹配 就是注意书写的格式

SELECT * 
FROM emp
WHERE (deptno,job)=(SELECT deptno,job  -- 这里需要我们进行模仿 就是要进行几次的匹配
	FROM emp
	WHERE ename='SMITH'
	) AND ename !='SMITH'

任务驱动2

– 1.查询到宋江同学的数学 英语 语文成绩都相同的同学

分析

1.查到宋江的各科成绩

SELECT chinese, english ,math
FROM student11
WHERE `name`='宋江'

2.把查到的记录当作多个条件进行查询处理

-- 
SELECT * FROM student11
WHERE (chinese,english,math)=(SELECT chinese, english ,math
     FROM student11
WHERE `name`='宋江'
)

总结

就是多列查询就是在单列查询上边多加了几列，实际上也是匹配的一个过程而已。

不过是从单匹配到了多匹配而已

mysql子表查询

练习题目

1.任务驱动

查找每个部门工资高于本部门平局工资的人资料

2.分析题目

2.1-- 1.先得到每个部门的平局工资

SELECT deptno,AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno

2.2查找每个部门的工资高于本部门的平均工资的

关键在于如何建立联系，两张表直接如何建立了联系

SELECT ename,sal,tmp.avg_sal,tmp.deptno
FROM emp ,(SELECT deptno,AVG(sal) AS avg_sal
	FROM emp
GROUP BY deptno) tmp 
WHERE emp.`deptno`= tmp.`deptno` AND emp.`sal`>tmp.avg_sal

2.任务驱动

查找每个部门工资 最高的人的详情资料

1.分析

我们先找到每个部门的最高工资,然后把这个表作为临时表

SELECT MAX(sal),deptno
FROM emp
GROUP BY deptno

2.然后我们找到一个可以进行对应的数据，这两个表可以建立联系的点

-- 
SELECT *
FROM emp,(SELECT MAX(sal) AS max_sal,deptno
FROM emp
GROUP BY deptno)tmp
WHERE emp.`deptno`=tmp.`deptno` AND emp.`sal`=tmp.max_sal

3.任务驱动

-- 查询每个部门的信息（包括：部门名，编号，地址）和人员数量

分析

– 1.部门名 部门编号 地址 来自dept
– 2.各个部门的人员数量（我们可以构建临时表的）

先构建临时表

-- 1.构建临时表
SELECT COUNT(*),deptno
FROM emp
GROUP BY deptno

找到可以与临时表进行处理的点

-- 2.找到连接点 就是部门号是一样的
SELECT dept.`deptno`,dept.`dname`,dept.`loc`,tmp.num -- tmp.* 表示将该表所有列都显示处理，多表查询中
FROM dept,
(SELECT COUNT(*)AS num,deptno
FROM emp
GROUP BY deptno) tmp
WHERE dept.`deptno`=tmp.`deptno`

表的复制

这个复制的原因是因为我们可能在日后的工作中需要大量的数据出来

1.第一种复制的操作

就是借助之前存在的表 进行复制的操作处理

CREATE TABLE my_tab01(
	id INT,
	`name`VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT)
	-- 我们创建完一张表之后 我们就去以这个表的结构作为模板进行进一步的操作处理
	
INSERT INTO my_tab01
 (id,`name`,sal,job,deptno)
 SELECT empno,ename,sal,job,deptno FROM emp 
 -- 我们用这个查询之后的数据放到我们的新建的表1中去
 就是利用查询出来的记录放到我们的新建的表中去

2.自我复制

	
-- 自我赋值
INSERT INTO my_tab01
SELECT * FROM my_tab01

3.面试真题

1描述

-- 如何删除一张表中的重复记录

我们这样考虑 我们先创建一张和我们的原表的类型一样的表，然后把这张表以不去重的形式导入进去

然后我们把原来的表删除 把目前的表的名字进行修改一下就可以了

-- 1.先创建一个临时表my_tmp,该表的结构和my_tab02一样
CREATE TABLE my_tmp LIKE my_tab02;
-- 2.把my_tmp的记录 通过distinct关键字 处理后 把记录复制到my_tmp中去
INSERT INTO my_tmp
SELECT DISTINCT * FROM my_tab02;
===================
-- 这样得到的就是没有重复的数据了
-- 然后这个表中的数据就是我们的想要的数据了 
-- 我们可以直接将这个表进行改名就行 然后把我们的那个表删除掉
DROP TABLE my_tab02;
-- 改名
ALTER TABLE my_tmp RENAME TO my_tab02

4.合并查询

介绍.

union 和union all

就是当我们具有多条语句的时候，

我们可以通过这个进行合并

1.先看union all

这个是不去重的

2. union

这个是会帮助我们进行去重操作的

MySQL的外连接

1.之前我们的多表查询中，是利用where子句对两个表或多张表，形成的笛卡尔表集进行筛选，根据关联条件，显示出我们想要的记录，匹配不上的，不显示。

先看一下下面这个图片

这个图片 我们看上去发现明显的存在一个问题 就是 我们肯定是没法显示40号部门的信息，因为我们左边的表不能和右边的表建立起来联系，所以就会导致我们目前的状况出现。

我们规定

左侧的表完全显示 叫做左外连接

右侧的表完全显示就是右外连接

1.我们现在看下面两张表

我们对照一下 会发现 我们要是想把这两种表放到一起是不可能的，如果我们按照多表查询的话，我们就会发现一个问题

我们这两个表没法一一对应，因为我们的id对应不全，我们要是按照id去对应，必然会有一些记录的缺失，

这样我们产生了左右连接 ，什么叫做左右连接？ 其实所谓左右连接的意思就是 我们就是无论你右边（左边）有没有跟我对应的，我都要显示出来，没有我就以null进行补全处理。

1.先看一下左连接

SELECT stu.`id`,`name`,grade
FROM stu LEFT JOIN  exam  -- 把where改为no 左外连接就是完全显示就算
-- 和右侧表没有匹配 但是依然会显示，
-- 不过就是那个位置设置为null就行了 在左边的那个表就是左表 表2就是右表
ON stu.`id`=exam.`id`

我们会发现 我们将所有的id都显示出来了，尽管 我们的成绩没法对应，但是我们达到了，左连接的效果，左连接就是将左侧的信息完全显示出来。

2.右连接

就是将右侧全部显示出来 不管左侧会不会真的对应，能对应就对应不能对应就直接显示出来

-- 使用右外连接 没有名字就显示为null 什么意思就是说 我们无论怎么样也要将我们的右表的数据显示出来  反正成绩必有 不管有没有人对应 无所谓 

SELECT  stu.`id`,`name`,grade
FROM stu RIGHT JOIN exam
ON stu.`id`=exam.`id`

小练习

– 列出部门名称和这些部门的员工信息（名字和工作），
– 同时列出那些没有员工的部门名

代码

-- 使用外连接就行
SELECT dname,ename,job
FROM emp RIGHT JOIN dept
ON emp.`deptno`=dept.`deptno`

小结

我们使用最多的应该是使用的是我们前面学过的那个类连接，找出我们的相关联的位置继续查询处理

约束

主键：

定义：用于唯一的标示表行的数据，当定义主键约束后，该列不能重复。

创建 1.直接进行创建主键

CREATE TABLE primary_key(
	id INT PRIMARY KEY,-- 表示是主键 主键列的值是不能重复的
	`name` VARCHAR(32),
	email VARCHAR(32)) 

2.我们在末尾进行创建主键

CREATE TABLE primary_key(
	id INT ,
	`name` VARCHAR(32),
	email VARCHAR(32)
    primary key(id));-- 在最后的位置指定那些是主键

3.进行数据的插入

	INSERT INTO primary_key VALUES(1,'jack','123.com');
	
	INSERT INTO primary_key VALUES(2,'tom','234.com'); -- 这个是我们将我们的值进行插入
	我们的id编号是不同的 因为我们是指定了 id的编号 只要是不同的就可以进行数据的插入

4.复合键

	CREATE TABLE primary_key(
	id INT ,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY(id,`name`));-- 这里我们将主键的定义放到了 最后面去了，其实是为了说明我们要将id和
	-- name进行复合处理 

符合键的意思就是说 我们通过两个键符合对我们输入的数据进行约束，就是这一次 你不单单是写两个

	INSERT INTO primary_key VALUES(1,'jack','123.com');
	INSERT INTO primary_key VALUES(1,'tom','123.com');
	这次你进行插入数据的话 就可以成功 因为你的约束条件扩大了 必须id和name都是一样的才不能插入

5.主键的细节

– 主键的细节讨论 主键不能重复而且不能为null（虽然我们没写 但是我们的id就是不能写成null）
– 多个主键的拼接，复合主键 注意这个是复合主键 不是两个主键

6.unique的约束

当我们的字段被unique修饰的话，我们这一列就不能有重复的，但是可以有null值，前提是你自己没有指定哪个字段不能为null但是你要是指定不能为null的话 那么你就获得了和key具有同样效果的unique

7.外键

看一下这个图片，

我们有两张表，一张学生表，一张班级表，我们要是想通过班级表对学生表进行约束的话，就可以借助外键

就是你一旦绑定上了，外键的话，你就必须按照规定去写，比方说 你先300就不行了，上面根本就没有300

外键如何绑定？

1.先创建主表

CREATE TABLE my_class(
	id INT PRIMARY KEY, -- 学生编号
	`name` VARCHAR(32) NOT NULL DEFAULT ' ');

2.创建从表

	CREATE TABLE my_stu(
        id INT PRIMARY KEY,-- 学生编号
        `name` VARCHAR(32) NOT NULL DEFAULT ' ',
        class_id INT, -- 学生所在的班级的编号
        -- 下面指定外键的关系
        FOREIGN KEY (class_id) REFERENCES my_class(id));-- 这句话就是说明 我们的class_id 受限制
        于我们的my_class这个表的id约束处理
        
-- 我们会把外键放到我们的从表当中去，也就是被约束的那张表 我们指定clas_id和my_class(id)进行匹配 我们要插入的数据必须是和这个外键进行匹配

3.如何删除

我们必须先动外键所在的表，比方说 我要删除id=100的表，但是我必须将从键中的和100有关系的数据全部删除掉才能删除主键中的数据，这是为什么？ 这是因为，你要是直接删除主表中的数据 就会把从表中的数据丢下，就是找不到对对应关系了，这样是不行的。

-- 一旦主外键的关系形成了就不能随意删除 要是想删除 必须先删除和主表有关系的数据 也就是先动从表才行	

check

这个关键字就是也是约束的一种，但是这个大家稍作了解就行

在Oracle和sql service里面生效

小练习：

CREATE TABLE goods(
	goos_id INT PRIMARY KEY ,
	goods_name VARCHAR(32),
	unitprice INT CHECK (unitprice BETWEEN 1.0 AND 9999.99)),
	category VARCHAR(32),
	provider VARCHAR(32))
	
	CREATE TABLE customer (
		costumer_id INT PRIMARY KEY ,
		`name` VARCHAR(32) NOT NULL,
		address VARCHAR(32) UNIQUE,
		sex ENUM('男','女') NOT NULL, -- 这句话是生效的 
		-- sex varchar(10) check (sex in ('man','woman'))
		card_id INT)
		
		CREATE TABLE purchase(
		order_id INT PRIMARY KEY,
		customer_id INT ,
		goods_id INT,
		nums DOUBLE ,
		FOREIGN KEY (customer_id) REFERENCES costomer(costumer_id),
		FOREIGN KEY (goods_id) REFERENCES goods(goos_id));
		-- 进行外键说明 

注意一下就是 check由于不生效 所以我们可以使用在我们的enum进行局限的确定处理

自增长

1.在某张表中，存在一个数据，我们在进行添加数据的时候我们这个数据从1开始进行增长，怎么处理？

我们采用 AUTO_INCREMENT配合主键使用

当我们采用这个写法的时候，我们就可以直接把我们的那个默认值赋值为null就行。

CREATE TABLE t(
	id INT PRIMARY KEY AUTO_INCREMENT,-- 直接写AUTO_INCREMENT就行
	`name` VARCHAR(32) NOT NULL DEFAULT '');
	INSERT INTO t VALUES(NULL,'yt');-- 第一种插入方式就是需要指定我们要插入的位置
	INSERT INTO t (`name`) VALUES('hh');-- 这个是插入的值为hh
	SELECT * FROM t;

注意细节

索引

提高数据库的性能，索引是最物美价廉的东西，不需要加内存，不用改程序，不用调sql，查询速度可以提高数千倍

先看一下索引的过人之处

-- 这个表有8000000万条记录

SELECT * FROM emp WHERE empno=1234567;-- 执行5.104s才返回的 这就太慢了 524m

-- 我们使用索引进行优化处理 ,体验一下索引的厉害之处   655m 索引本身也是有开销的

CREATE INDEX empno_index ON emp (empno) -- 第一个是索引名称 后面on 表示我们的emp表的 empno这里进行索引

-- 创建索引后
SELECT * FROM emp WHERE empno=1234590; -- 0.010s 这个就厉害了  速度直接是上千倍 

通过看上述的描述 我们可以得到 创建索引之后 我们的数据变得查找起来非常的快速，原因是因为我们的索引其实是创建了新的数据类型比如说是

二叉树索引 扫描的速度可以大大的提升我们扫描的速度
使用索引会形成一个数据结构 比如二叉树 但是还有其他的方案
但是建立索引之后 会占领内存 就相当于说是 用内存来换取速度
但是你要是对索引之后的数据进行删除处理的话就会导致我们的数据发生大大的改变其实是 如果对表进行dml语句的操作 就会导致磁盘占用 对dml语句的效率影响

一句话就是用内存来换取我们的速度，而且你创建完索引之后的话，我们的数据就会变得不是那么的容易被改变，因为二叉树，你要是想对其修改的难度还是大的。

但是 我们实际开发过程中 使用较多的还是进行查询处理的操作实现的比较多，进行修改的成分就其实没有那么多了，所以我们自己要进行平衡处理。

主键索引

  1.主键索引-- primary key 
  这个其实只要你在创建的时候加上了主键 就是主键索引，不用在另加了
  但是你要是在一开始创建的时候没有加的话，你就要加上所谓的索引了。
  ALTER TABLE tmp01 ADD PRIMARY KEY (id); -- 这个也是可以进行添加一个主键索引的
  这个就是添加主键索引的方法

唯一索引

也就是unique 这个索引和主键索引比较的像，我们可以在我们创建之处就加上，或者是在表创建之后再加上

  CREATE TABLE tmp01(
	id INT unique ,
	`name` VARCHAR(32));
	下面这个是在创建之后进行的添加处理
	-- 添加唯一索引 
CREATE UNIQUE INDEX id_index ON tmp01 (id);

普通索引

-- 添加普通索引的方式
ALTER TABLE tmp01 ADD INDEX id_index (id);-- 这个也是添加索引的一个方法
-------- 这个也是添加普通索引的一个方式。
CREATE INDEX id_index ON tmp01 (id);-- 这个就是普通索引的操作处理

普通索引VS唯一索引

说一下 唯一索引和普通索引什么时候用哪个

这个就取决于 我们用户的需求了，为什么那，因为如果我们用户想要的是一个唯一的，不想要重复的，那么还想查询的速度快，那么你就选择unique索引。

删除索引

DROP INDEX id_index ON tmp01;-- 这个删除索引的 就是我们要先知道索引的名字才行
--- 第一种更加简短 而且好理解
----- 第二种删除方式
ALTER TABLE tmp01 drop index id_index (id)

显示索引

SHOW INDEX FROM tmp01;

修改索引

就是先删除 然后在添加新的索引

普通索引如何创建

	CREATE INDEX id_index ON order01 (id);
	CREATE INDEX  (书写你要创建索引的名字) ON (你要创建表的名字) (要对那个字段进行创建索引)

创建索引需要注意的事项

1.较为频繁的作为查询条件的字段应该建立索引，

2.唯一性太差的字段不适合单独创建索引，就比如说 人的性别，要么是男，要么是女，太过于频繁了，导致查询的意义就不大

3.更新非常频繁的字段不适合索引，为什么？

因为如果你的更新过于频繁，就会导致我们会对我们的索引进行修改，这个是很浪费时间，最后可能根本达不到我们想要缩短时间的目的，反而使得我们查询速度更慢。

事务

我们先看到的是 我们成功的话，当然是很好的，tom成功的将100块钱转给了，king，但是万一你要是没有完全成功那，

这就出现了问题，没有完全成功，那就是说，tom少了100块钱，但是king没有收到，那就麻烦了，所以我们需要一个整体的语句，就是这个语句要是生效就必须全部生效，不能你成了，我没有成功。

这里我们将多个dml语句，当做一个整体，要么全部成功，要么全部失败，

这里我们就使用事务来解决.

mysql操控事务的几个比较重要的操作语句

简单看一下我们的事务操作流程图

简单说一下什么意思，就是我们首先开启一个事务，然后这个事务里面，我们可以做到的就是进行一系列的dml的操作，

然后在你想要保存的时候进行保存处理，就相当于我们在玩这个游戏的时候我们进行存档处理。 要是我们发现这一关没玩好，还可以回档，就是这么一个流程。

事务管理

1.如何开启事务

-- 1.创建一张测试表
CREATE TABLE t001(
	id INT,
	`name` VARCHAR(32));

-- 2.开启一个事务
START TRANSACTION ;
-- 开启事务的第二个形式 （不常用 但是看见要知道是干嘛的）
-- set autocomm=off;

2.进行一些dml操作

-- 执行dim操作
INSERT INTO t001 VALUES(1,'yt');
INSERT INTO t001 VALUES(3,'tom');
INSERT INTO t001 VALUES(2,'white');

3.设置保存点（savepoint）

这一步就是我们常说的设置保存点的意思，我们通过不断的创建这个savepoint就可以将我们的数据能够回退到这个保存点的位置

SAVEPOINT a;

设置完成之后 我们就可以继续进行我们的dml语句的操作处理。

4.rollback （回退处理）

出现回退 是因为我们在写着写着的时候可能不小心书写错了某些语句，那么我们就会想要回退到某个点，那么我们就可以

进行回退

ROLLBACK TO b;

这个就是回退到我们的b点进行不断的处理实现我们的具体操作。

5.commit

这个其实是和START TRANSACTION 一起使用才有意义的，为什么那，因为其实我们要是不打开事务，其实我们每一次dml语句都会是直接就是commit的，就不存在返回某个存档点一说，这也是为什么我们之前一直没有用事务的原因。因为我们进行一次dml语句就会提交

来看一下 提交的注意事项

– 5.innodb存储引擎是支持事务的

我们可以打开设计表进行看一下我们的引擎是不是innodb

隔离级别（***）

先放一张图片 让大家理解一下有哪些隔离级别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vUT2eQMg-1643869280046)(数据库进阶版本约束.assets
)]

说一下 这些隔离级别的不同

1.读未提交

这个就是指的 我们两个连接的数据库，用户 a b a创建一个事务进行修改 ，而b可以直接看到所有的修改的实时信息

2.读已提交

这个和刚刚差不多，就是这个不能看到实时信息，但是只要a提交了，b还是可以看到所有的信息。

3.可重复读

这个是我们最常用的 也是我们mysql默认的，这个可重复的的操作就是说 你既不能实时观看，并且就算别人把事务commit之后 我们还是不能查看。

4.可串行化

这个和可重复读的效果基本一样，也是不能查看，最后就算commit也是和最初一样。

就是b要是想看 会被加锁 就是没法执行指令。

事务隔离的几个小操作

1.查看隔离级别

SELECT @@tx_isolation; -- 这个语句是查看隔离级别的
-- 2.查看当前系统隔离级别
SELECT @@global.tx_isolation;

2.设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

这个是设置为我们想要的级别

3开启事务

你要是不开启事务 ，那么所有的操作对于

START TRANSACTION;

4.修改默认隔离等级

-- 5.我们一般设置默认为可重复读 你要是想要修改 就去配置文件那里去修改就行了
-- 修改之后重启就行了 我们应该是先关掉 然后再重启就行了

我们需要进行的操作就是先开启事务，再进行下面的操作，不然不开启事务谈隔离 没有意义

ACID

mysql表类型和存储引擎

mysql表类型和存储引擎的特点

如何选择存贮引擎

如何修改一个表的存储引擎

ALTER TABLE t003 ENGINE=INNODB;
-- 这样就可以进行修改处理了。
table后面加上表名就OK啦

视图（view）

视图的原理

描述一下为什么会出现视图

比如说我们的员工表

里面的数据有很多 但是我只想要显示出一些数据 ，比如说 我只想显示（empno，ename，job，和deptno）信息。

那么我们就需要建立一张视图

1.创建视图

CREATE VIEW emp_view01 -- 第一句交代我们视图的名字
AS -- 固定格式
SELECT empno,ename,job,deptno FROM emp;-- 这个代表的是我们要显示出那些数据出来

2.查看创建视图的时候使用的指令

SHOW CREATE VIEW emp_view01;-- 查看你当时创建视图的时候的指令

3.删除视图

DROP VIEW emp_view01;-- 这个就是删除视图的操作

4.修改视图也会影响到基表

-- 修改视图会影响到基表 反过来也会影响到视图
UPDATE emp_view01
SET job='MANAGER'
WHERE empnp=7369;
-- 视图中还是可以使用视图的(从emp_view01中重新选择两列作为新的视图)(但是他们的本质还是我们的基表)

5.我们可以以视图为基表 再次创建视图

但是本质还是以emp为基表

CREATE VIEW emp_view02
AS
SELECT empno,job FROM emp_view01;

视图的实践

1.安全

2.性能

3.灵活

练习

– 视图课堂练习
– 针对emp，dept，和salgrade三张表，创建一个视图emp_view03,
– 可以显示雇员编号，雇员名称，雇员部门和薪水级别【即分别使用三张表，创建一个视图】

1.我们根据三张表多表查询 先找到对应关系，创建出符合要求的表

SELECT empno,ename,dname,grade
FROM emp,dept,salgrade
WHERE emp.`deptno`=dept.`deptno` AND (emp.`sal` BETWEEN losal AND hisal)-- 两个查询条件才能获得到我们想要的条件

2.把得到的结果构建成视图

CREATE VIEW emp_view03
AS
(SELECT empno,ename,dname,grade
FROM emp,dept,salgrade
WHERE emp.`deptno`=dept.`deptno` AND (emp.`sal` BETWEEN losal AND hisal))

3.说明

一个视图的基表可能来自很多的位置 不一定只是一个表

mysql权限管理

出现的原因

当我们做项目开发的时候，可以根据不同的开发人员，赋给他们相应的mysql操作权限
– （比如说你就是进行简单的增删改查，就没有必要给你很高的权限）

所以 数据库管理人员（root）根据需要，创建不同的用户，然后赋予相应的权限，供开发人员使用。

1.创建一个新的user

CREATE USER 'yt' @'localhost' IDENTIFIED BY 'yt';-- 用户名 @ 可以登陆的ip 最后一个是密码但是存放的密码是加密的存放，

解释一下 user后面的就是我们的用户名@后面的就是我们的登陆地址 这个告诉我们这个用户可以从哪里进行登陆处理

然后进行修改我们的密码。

2.查看加密后的密码

SELECT PASSWORD('yt'); -- 这个可以查询加密后的密码是什么

3.删除用户

-- 删除用户
DROP USER 'yt'@'localhost';

权限的授予

1.对密码的修改

我们的root拥有最高的权限 ，它可以将我们用户的密码进行修改

 SET PASSWORD FOR 'yt'@'localhost'=PASSWORD('yt');

2.小案例

1.-- 创建用户 密码

-- 创建用户 密码

CREATE USER 'yt1105'@'localhost' IDENTIFIED BY '123';

2.-- 使用root用户 创建testdb 表news

-- 使用root用户 创建testdb 表news
CREATE DATABASE testdb;

CREATE TABLE news(
	id INT,
	content VARCHAR(32));

3.分配权限

GRANT SELECT,INSERT ON testdb.`news` TO 'yt1105'@'localhost';
-- grant 代表授予的意思
-- 后面紧跟着的是 我们赋予的权限是什么 把我们的权限赋值到哪个数据库的哪个表中去，然后把这个权限给到哪个用户用to进行连接

4.测试

这个直接去我们的另一个用户去查看就行，你会发现多了数据库而且具有了查看和添加的权限了

5.修改yt1105的密码为123

-- 修改yt1105的密码为123
SET PASSWORD FOR 'yt1105'@'localhost'=PASSWORD('abc');-- 这样操作就ok了 我们只需要正常的操作处理就行

7.删除用户

DROP USER 'yt1105'@'localhost'; 

8.回收权限

-- 演示如何回收权限()

REVOKE SELECT ,UPDATE,INSERT ON
testdb.`news` FROM 'yt1105'@'localhost';
-- 第二种写法就是下面这个 回收所有的权限

REVOKE ALL ON testdb.`news` FROM 'yt1105'@'localhost'

用户管理的一些细节

1. 在创建用户的时候，如果不指定host的话，则为%，%表示所有的ip都具有连接权限

-- 我们先创建一个用户
CREATE USER tom;

我们查看一下是不是默认为%

SELECT `host`,`user` FROM mysql.user -- 截图

2.模糊的访问

CREATE USER 'jack' @'192.168.1.%';
-- 上面的这个就是说我们的数据其实是从这个ip段上的就都可以进行参与

3.删除细节

由于 我们第一个用户tom是用默认的访问 所以我们删除的时候 直接删除就行

不用加@…

-- 删除的时候，要是你的host不是% 需要明确指定
DROP USER tom -- 默认就是 DROP USER 'tom'@'%'

但是 Jack就不行 他是指定了的连接 所以要是想要删除Jack 我们必须 写清楚到底是什么样的

DROP USER 'jack' @'192.168.1.%';-- 这个时候必须写清楚不然 系统会理解错的

结语

本次的内容差不多就是这些 后面会继续更新jdbc