[大数据]MySQL学习的正确打开方式

一、数据库和表的相关操作

1、数据库的操作

2、表的相关操作

创建表

CREATE TABLE IF NOT EXISTS `test1`(`id` INT PRIMARY KEY AUTO_INCREMENT,`name` varchar(255));

IF NOT EXISTS是判断数据库中并不存在这个表时创建
int和varchar表示数据类型
PRIMARY KEY表示主键的意思
AUTO_INCREMENT 表示自动递增，常用于主键

向表新增数据

INSERT INTO test1(id,name) values(1,"小庄");//插入单条记录
INSERT INTO test1(id,name) values(1,"张三"),(2,"李四");//插入多条记录，使用逗号隔开

修改表数据

UPDATE test1 SET id=2,name="老庄";

删除表

DROP TABLE test1;

删除记录(表内容)

//删除所有内容
DELETE FROM 表名;
//id=1指的是特定这一列
DELETE FROM 表名 WHERE id=1;

二、检索语句SELECT

1、常见的SELECT语句

查询表中所有数据

SELECT * FROM 表名;

查询表中指定列所有数据

SELECT id,name FROM 表名;

2、带有条件的检索语句（WHERE）

关键字：where 表达式

查询表中数据，并且查询成绩大于80的学生

SELECT name FROM　表名　WHERE score>=80;

表达式中，AND表示与，OR表示或，NOT表示非

关键字:BETWEEN
查询表中数据的范围，比如查询成绩在70分到80分之间的学生

SELECT name FROM　表名　WHERE score BETWEEN 70 AND 80;

关键字: IN,和上面的BETWEEN的作用一样，都是表示范围，比如要表达70到80分的学生

SELECT name FROM　表名　WHERE score IN(70,71,72,73,74,75,76,77,78,79,80);

3、带有通配符的检索语句(LIKE)

关键字：LIKE，表示模糊查询
%通配符：表示任何字符出现的次数
比如：
%三：意思是以三结尾的任意字符
三%：意思是以三开头的任意字符
%三%：意思是含有三的任意字符
%%:意思是任意字符

SELECT name FROM 表名 WHERE name LIKE '%三';

_通配符，表示匹配一个任意字符

SELECT name FROM 表名 WHERE name LIKE '_三';//这里只能匹配”某“三，也就是两个字

4、限制条数语句（LIMIT）

经常使用在分页查询中
关键字：LIMIT
具体使用

SELECT * FROM 表名 LIMIT 1,10;//查询2到11条记录，前面表示索引，后面表示条数
SELECT * FROM 表名 LIMIT 5;//查询前五条记录

5、起个别名（AS）

主要作用是在操作时防止名字相同导致出现错误。AS关键字可以省略

SELECT id AS id2 FROM 表名;
SELECT id id2 FROM 表名;//省略了AS关键字，但效果一样，使用别名id2

6、去重（DISTINCT）

当我们想要我们的数据显示只需要不重复的数据时，会用到去重，这时我们需要用到关键字DISTINCT
使用如下：

SELECT DISTINCT 字段名 FROM 表名;

三、排序和分组

1、排序（ORDER BY）

关键字：ORDER BY，默认是升序排序（ASC）
设置为降序排序使用关键字：DESC

SELECT * FROM 表名 ORDER BY id DESC,name ASC;//先按id的降序排序，再按name的升序排序，比如成绩一样，按名字排序

2、分组（GROUP BY）

作用：对相同的数据进行了分组，然后对组中的数据进行处理

SELECT * FROM 表名 GROUP BY 需要分组的列名;

通常和Order By 相结合

SELECT 列名 FROM 表名 GROUP BY 需要分组的列名 ORDER BY 列名;

过滤分组使用HAVING

HAVING和WHERE的作用一样，唯一不同点是HAVING用于对分组进行过滤
如：

SELECT 列名 FROM 表名 GROUP BY 需要分组的列名 HAVING 条件表达式 ORDER BY 列名;

四、函数

1、文本处理函数

具体使用

SELECT LENGTH(列名) FROM 表名;

2、时间相关函数

以上函数的使用方法：

SELECT Date(时间类型的列名) FROM 表名;//查询结果例：2021-10-10

UPDATE 表名 SET 时间类型的列名=Now();//修改函数，或者使用在新增操作中返回当前的时间

3、聚合函数

SELECT COUNT(*) FROM 表名;//求表的所有行数，包括NULL值
SELECT COUNT(列名) FROM　表名;//求列中的行数，不包括NULL值

SELECT AVG(列名) FROM　表名;//求这一列中的平均数
SELECT SUM(列名) FROM　表名;//求这一列中的和
SELECT MAX(列名) FROM　表名;//求这一列中最大的值
SELECT MIN(列名) FROM　表名;//求这一列中最小的值

五、子查询

1、子查询

主要作用是对子查询的数据再进行一次过滤；比如我们想对查询结果再进行处理，例如查看条数

SELECT COUNT(*) FROM (SELECT * FROM　表名　WHERE score>=80) AS 别名;

使用比较运算符

比较运算符有>，<，=，>=，<=，!=，<>

SELECT
	* 
FROM
	表名 
WHERE
	列名>( SELECT * FROM　表名　WHERE 列名 = 80 ) AS 别名;

使用IN

IN是存在的意思，作用和上面讲SELECT检索的一样,作用是将子查询的结果做为条件，然后再进行判断是否存在

SELECT
	* 
FROM
	表名 
WHERE
	列名 IN( SELECT 列名 FROM　表名　WHERE 列名 > 80 ) AS 别名;

使用EXISTS

EXISTS是存在的意思，使用它则会返回True和False
只有返回True时，外层的查询才会执行，否则不执行！

SELECT
	* 
FROM
	表名 
WHERE
	EXISTS ( SELECT * FROM　表名　WHERE 列名 = 80 ) AS 别名;

使用ANY

ANY需要和比较运算符结合才能使用，作用是满足子查询结果中返回任意一个结果做为外部查询的条件
如：子查询的结果有81,82,90等等，但是只会返回其中任意一个结果

SELECT
	* 
FROM
	表名 
WHERE
	列名 > ANY( SELECT * FROM　表名　WHERE 列名 > 80 ) AS 别名;

使用ALL

ALL需要和比较运算符结合才能使用，作用是满足子查询结果中返回的所有结果做为外部查询的条件

如：子查询的结果有81,82,90等等，外部查询需要大于所有子查询结果才会有记录

SELECT
	* 
FROM
	表名 
WHERE
	列名 > ALL( SELECT * FROM　表名　WHERE 列名 > 80 ) AS 别名;

2、表关联

简单描述：有product和consumer两张表,表结构有：
product(id(唯一标识),name(产品名),num(产品数量),price(产品价格),)

consumer(id(唯一标识),name(姓名),age(年龄),pid(外键，和产品表的id一致))

消费者和产品相关联的字段是外键pid,必须把这个字段设为关联的条件

//通过表别名，并且根据两张表关联条件进行检索，把两张的字段合并在一个表中
SELECT
	p.id,
	p.name,
	c.id 
FROM
	product AS p,
	consumer c 
WHERE
	c.pid = p.id;

3、UNION

使用UNION关键字实现多个查询

UNION规则：

1. 必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分割；
2. 每个查询必须包含相同的列、表达式或聚集函数（不过各个列的排列顺序不需要一致）;
3. 只能使用一个ORDER BY语句，并且放在最后。

比如我们想要查询成绩大于80分的同时并且把年龄大于18岁的同学的查询出来，并且以成绩排序

SELECT score,age FROM student WHERE score>80
UNION
SELECT age,score FROM student WHERE age>18
ORDER BY score;

当然，使用WHERE...OR语句能实现上面的功能，但不推荐这样使用,因为如果是很多条查询语句执行时，那样就会很大的影响性能！

SELECT score,age FROM student WHERE score>80 OR age>18
ORDER BY score;

4、视图（VIEW）

视图的内容比较少，我就放到这里了
视图是一个虚拟表
特别注意： 视图主要用于检索

视图的应用场景

1、重用SQL语句
2、简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节
3、使用表的组成部分，而不是整个表；
4、保护数据。可以给用户授予表部分的访问权限而不是整个表的访问权限；
5、更改数据个数和表示。视图可返回与底层表的表示格式不同的数据。

视图的创建、修改、使用以及删除

视图的创建：

CREATE VIEW 视图名 AS 语句 检索语句;

视图的修改：
关键字：replace

//如果视图已存在，则修改，如果没有存在则创建
CREATE OR REPLACE VIEW 视图名 AS 语句 检索语句;

视图的使用：
??和我们进行表的检索一样，把视图（虚拟表）当做一个表进行查询

SELECT * FROM 视图名;

视图的删除：

//删除多个视图
DROP VIEW 视图名1,视图名2,视图名3;

特别注意 如果检索语句外部使用ORDER BY，则视图内部的ORDER BY会被覆盖，并且视图不能检索
例如：

//这里使用列名1进行排序
CREATE VIEW 视图名 AS 语句 检索语句 ORDER BY 列名1;
//会按照视图外的列名2排序
SELECT * FROM 视图名 ORDER BY 列名2;

六、正则表达式

为什么要用正则表达式

在实际开发中，我们对一些数据需要限制存储（按照一定的规则存储，否则无法存入）。比如电话、身份证以及用户名的限定等等数据，往往需要使用正则表达式的形式存入。
很多时候需要在前端传给后端时数据是经过过滤的，但为了保证安全性，我们应该在传入数据库前再进行正则表达式的相关操作

正则表达式有哪些？

1、限定符

2、特殊字符

3、元字符

具体使用如下：
测试数据：abc、answer、book

/**注意：'/'里面内容才是我们学的正则表达式**/
//匹配到以a开头的所有数据：abc和answer
^a
//匹配到以k结尾的所有数据：book
k$	
//匹配到a或a开头的所有数据
/a*/	
//手机号正则,\d表示[0-9],[0-35-9]表示0到3和5到9的数字
^1(3[0-9]|4[01456879]|5[0-35-9]|6[2567]|7[0-8]|8[0-9]|9[0-35-9])\d{8}$
//邮政编码
/^[1-9]\d{5}$/

正则表达式测试网址：https://regex101.com/

MySQL使用正则表达式

4、具体使用

使用关键字：REGEXP
使用如下：

//查询以a开头的所有字符
SELECT 字段名或* FROM 表名 WHERE 字段名 REGEXP '^a';
//查询以k结尾的所有字符
SELECT 字段名或* FROM 表名 WHERE 字段名 REGEXP 'k$';

七、约束与事务处理

1、约束

2、事务处理的特点

??1、MySQL 事务主要用于处理操作量大，复杂度高的数据。比如我们执行多个操作构成一个事务；
??2、MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务；
??3、事务用来管理 insert,update,delete 语句；也就是对数据进行”修改“操作的管理
??4、事务保证了数据库的完整性，要么全部执行，要么全部不执行，体现了原子性。

MySQL事务的特性

1. 原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。发生错误会回滚到事务开始前的状态；
2. 一致性： 在事务开始之前和事务结束以后，数据库的完整性没有被破坏；
3. 隔离性： 事务之间井水不犯河水，互不干扰；
4. 持久性： 事务结束后，数据长期存储起来。

3、MySQL事务处理

事务处理的步骤：

BEGIN;//开启事务
SAVEPOINT 保存点名称;//设置保存点，这个可以不设置
执行相关操作
ROLLBACK;//可以选择不回滚事务或ROLLBACK TO 保存点名称;来指定回滚位置
COMMIT;//提交事务

可以通过SET来改变 MySQL 的自动提交模式,如下：

//开启自动提交，值为0时关闭自动提交
SET AUTOCOMMIT=1;

4、事务隔离级别

事务并发产生的问题：

脏读： 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读： 事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读： 系统管理员A将数据库中所有学生的成绩从具体分数改为ABCD等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改完后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。并发时会遇到

对于上面产生的问题，下面给出了四种隔离级别。

1、读未提交(read uncommitted)

顾名思义，就是能够读取还没提交（COMMIT;）的事务，当未提交的事务进行了回滚操作，那么读取到的数据就是脏数据，我们叫做脏读,读到数据可能不真实，只能保证持久性。最低隔离级别，这种隔离级别一般理论上的，大多数的数据库的隔离级别是二挡以上的

例如：在B事务进行修改操作时（未提交），A事务在进行查询，A事务读取的是B事务修改的值

//B事务
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE 表名 SET name="非常好" WHERE id=2;

//A事务
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM 同一张表名;
//上面A事务读取到的结果是B事务未提交的值，是脏数据

2、读已提交(read committed)——Oracle默认隔离级别

解决了脏读数据，只能读取事务提交后的数据，当事务A改变数据还未提交时，事务B去查询的时候数据并没有发生变化，当事务A提交事务后，事务B再去查询时，发现数据发生了改变，两次读取的数据不相同。我们叫做不可重复读，每次读取到的数据都是真实的数据。

//步骤一
//B事务进行修改数据，但并没有提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE 表名 SET name="好" WHERE id=2;


//A事务第一次读取结果为原数据，name="非常好"，因为B事务还未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM 表名;

//B事务执行了提交操作
COMMIT;
//A事务第二次读取时结果发生改变，name="好"，因为B事务已提交
SELECT * FROM 表名;
//我们发现A事务执行两次结果不一致

3、可重复读(repeatable read) ——MySQL默认的隔离级别

可重复读容易发生幻读,也就是说你可能不知道数据是被哪个事务操作的。我们还是通过例子来观察吧
具体步骤：

1. A事务开启事务，并执行了一次查询操作
2. B事务开启事务，并插入数据和提交事务，并且在B事务可以查询更改后的数据
3. A事务再次执行查询操作，并没有发现数据被更改了，然后也进行了插入数据，此时的数据是和B事务插入的数据相同的，由于id是主键，已经存在，所以报错，但是A事务并不知情，它看到的原数据并没有插入数据，按照道理不应该报这个错误。
4. 当A事务提交事务后，发现刚刚不给插入的数据竟然存在了，以上的内容就是幻读的情况。

如下图介绍：

4、串行化

最高级的隔离级别，解决了所有问题，完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。执行效率低、性能开销大，不能并发，实际开发用的少。

当我们将当前会话的隔离级别设置为serializable的时候，其他会话对该表的写操作将被挂起。这里我就不展开了。

八、表连接

1、内连接

使用关键字：JOIN ……ON 或 JOIN……WHERE

通常我们需要用到多个表的数据时，可以通过表连接同时查询多个表的数据

SELECT
	p.id,
	p.NAME,
	c.price 
FROM
	表名 1 AS p
	JOIN 表名 2 c ON c.price > p.price;
	
//或者使用下面
SELECT
	p.id,
	p.price,
	c.price 
FROM
	表名 1 AS p
	JOIN 表名 2 c 
WHERE
	c.price > p.price;

2、外连接

左外连接：除了显示匹配的数据，还会显示左表不匹配的数据

关键字：LEFT JOIN ……ON 和上面连接的功能类似

SELECT
	p.id,
	p.NAME,
	c.price 
FROM
	表名 1 AS p
	LEFT JOIN 表名 2 c ON c.price > p.price;

右外连接：除了显示匹配的数据，还会显示右表不匹配的数据

关键字：RIGHT JOIN ……ON ,和上面的功能类似

SELECT
	p.id,
	p.NAME,
	c.price 
FROM
	表名 1 AS p
	RIGHT JOIN 表名 2 c ON c.price > p.price;

3、SQL的对应关系

1、一对一关系

一个人只能有一个身份证，一个身份证只属于一个人。实际项目中使用最少的的关系
特点：
???人表可以存身份证表的主键，也可以让身份证表存人表的主键，通常要使用唯一标识UNIQUE防止重复出现

2、一对多关系

一个班级拥有多个学生，一个学生只能够属于某个班级
特点：
???”学生“那张表存储”班级“那张表的主键，用的最多

SELECT
	s.name,
	c.name
FROM
	表1 s
	JOIN 表2 c ON ( s.cid = c.id);

3、多对多关系

一个学生可以选择多个课程，一个课程可以有多个学生
特点：
???需要建立第三张表，用于存储学生表和课程表的主键

其实多对多的关系也是建立在一对多的基础上
如下：

SELECT
	s.NAME,
	c.NAME 
FROM
	表1 s
	JOIN 中间表 t ON ( t.sid = s.id )
	JOIN 表2 c ON ( t.cid = c.id );

九、索引

索引是各个数据库优化的重要手段，优化的时候优先考虑因素是索引。

1、什么是索引

索引是数据库表中字段上添加的，作用是提高查询效率而存在的一种机制，一个字段可以添加索引，也可以多个字段联合添加索引。索引相当于一本字典上的目录，能够缩小搜索访问，从而提高查询效率。

MySQL在查询方面主要是两种方式：
方式一：全表扫描
方式二：索引扫描

2、索引的创建与删除

创建索引

create index 索引名 on 表名(列名);

删除索引

drop index 索引名 on 表名;

3、添加索引的条件

1. 数据量庞大，具体根据测试得出结果
2. 该字段经常出现在WHERE 的子句,也就是说这个字段经常被扫描
3. 这个字段很少进行DML操作（增删改操作），原因是索引需要排序

在MySQL中查看sql语句是否添加索引

关键字explain+sql语句

4、索引分类

1. 单一索引
   ??单个字段添加索引;
2. 复合索引
   ??两个字段或两个以上字段添加索引;
3. 主键索引
   ??主键上添加索引;
4. 唯一索引
   ??UNIQUE约束的字段添加索引
   ??唯一性比较弱的字段添加索引用处不大，也就是大量重复内容不建议

5、索引失效

失效情况一：模糊查询中开头使用%,

//索引失效
EXPLAIN SELECT * FROM 表名 WHERE name LIKE "%庄";

失效情况二：使用OR，任意一边的字段没有使用索引，则索引会失效
??例如在test表中，name字段使用了索引，而password字段没有使用索引,当使用语句

//这时索引失效
EXPLAIN SELECT * FROM 表名 WHERE name="小庄" OR password="123";

失效情况三：在时候复合索引的时候，没有采用左侧的列进行查找。

//先创建复合索引
create index 索引名 表名(列名1,列名2);
//在查询中使用左侧的列（列名1）进行查找，索引没有失效
EXPLAIN SELECT * FROM 表名 WHERE 列名1="具体条件";
//在查询中没有使用左侧的列（列名1），直接使用列名2,这时索引失效
EXPLAIN SELECT * FROM 表名 WHERE 列名2="具体条件";

失效情况四：索引列进行了运算，索引失效

//参加运算，索引失效
EXPLAIN SELECT * FROM 表名 WHERE 索引列+=1;

失效情况五：在WHERE子句中索引列使用了函数

//WHERE子句中索引列使用了函数，索引失效
EXPLAIN SELECT * FROM 表名 WHERE Upper(name)="BARON";

注意：只有在WHERE子句中的索引列使用函数才会失效，外部使用函数则不会失效。

十、总结与优化篇

总结篇

SELECT子句顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

常用的增删改操作

1、增

//单个插入
INSERT INTO 表名 (字段名) VALUES (字段值);
//多个插入
INSERT INTO 表名(字段名) 
VALUES
	(字段值1),
	(字段值2),
	(字段值3);

2、删

//清空表
DELETE FROM 表名;
//删除单/多条数据
DELETE FROM 表名 WHERE 检索条件;

3、改

UPDATE 表名 SET 字段名=字段值;

表连接

内连接：JOIN ……ON 或 JOIN……WHERE
左外连接：LEFT JOIN ……ON
右外连接：RIGHT JOIN ……ON

事务处理的步骤

BEGIN;//开启事务
SAVEPOINT 保存点名称;//设置保存点，这个可以不设置
执行相关操作
ROLLBACK;//可以选择不回滚事务或ROLLBACK TO 保存点名称;来指定回滚位置
COMMIT;//提交事务

优化篇

1. 尽量使用数字型字段，原因是数字型字段检索的效率比字符型字段要高
2. 尽量使用varchar类型代替char类型，因为varchar类型是可变长度
3. 尽量控制类型的存储长度，减少存储空间的浪费
4. 尽量使用索引，索引能提高检索效率，在使用索引的字段在查询语句应避免使用%开头进行模糊查询，因为这样会让索引失效
5. 当索引字段是大量的重复内容，如性别字段的内容大量重复，应删除索引
6. 应尽量避免在 where 子句中使用!=或<>操作符
7. 在进行多表查询时，应尽量采用UNION实现组合查询，少用OR,原因是OR有可能造成索引失效；
8. 尽量减少使用SELECT *语句，原因是会带来额外的I/O 内存 CPU的消耗