[大数据]MySQL 数据库

1、SQL、DB、DBMS分别是什么，有什么关系

• DB：数据库（DataBase），在计算机硬盘上以文件形式存在。
• DBMS：数据库管理系统（DataBase Management System）,常见的有MySQL、Oracle、DB2、SqlServer…
• SQL：结构化查询语言，是一门标准通用语言，标准的sql适用于所有的数据库产品。sql属于高级语言，SQL执行时，内部也会先进行编译，再执行SQL语句（SQL编译由DBMS完成）。

三者之间的关系：
DBMS负责执行SQL，通过执行sql语句来操作数据库（DB）中的数据
DBMS == ( 执行 ) ==> SQL == ( 操作 ) ==> DB

2、MySQL完美卸载

1. 双击安装包，点击下一步，然后点击remove，卸载
2. 手动删除Program Files中的MySQL目录
3. 手动删除ProgramData文件夹中的MySQL（ProgramData文件夹默认隐藏，需手动设置显示）

3、数据表（table）是什么？

table是数据库的基本组成单元，是一种结构化的文件，所有的数据都以表格的形式存储，目的是可读性强。
一个表包括行和列：

• 行：被称为数据（记录）
• 列：被称为字段（column）每一个字段包含的属性有：字段名、数据类型、相关约束。

4、SQL语句分类

• DQL（数据查询语言）：查询语句，凡是select语句都是DQL
• DML（数据操作语言）：对表中数据经进行增删改 【insert、delete、update】
• DDL（数据定义语言）：对表结构经进行增删改 【create、drop、alter】
• TCL（事务控制语言）：commit提交事务，rollback回滚事务。（TCL中T是Transaction）
• DCL（数据控制语言）：grant授权，revoke撤销授权等。

4、SQL脚本

以 .sql 为扩展名的文件，被称之为“sql脚本”，给文件中编写了大量SQL语句。
可以直接使用source命令执行该SQL脚本。
注意：当SQL脚本太大时，无法直接打开，请使用source命令进行数据初始化。

mysql> source 脚本文件地址

5、SQL运算符

注意：数据库中的null不是一个值，代表什么也没有，为空（空不是一个值，不能用等号衡量。需要用 is null 或 is not null ）

6、分组函数（多行处理函数）

特点：输入对行，最终输出结果为一行

注意：
1、所有的分组函数都是对一组数据进行的操作
2、分组函数常与 group by 连用，并且都会在 group by 执行完毕之后才会被执行（当一条SQL语句中无 group by 时，整张表数据会自成一组）
3、分组函数自动忽略NULL值
4、只要有NULL参与的运算结果一定是NULL
5、分组函数不能出现在 where 之后

6.1、count(*)与count(column)的区别？

1. count(*)：统计总记录行数（与所有字段无关）
2. count(column)：统计column字段中不为NULL的数据总行数

7、函数（单行处理函数）

特点：输入一行，输出结果也为一行
例如：ifnull()等函数

8、group by 与 having

group by：按照某个字段或是某些字段进行分组

--查询 每个年龄的平均薪资
select age,avg(sal) from users group by age;

having：对分组之后的数据进行再次过滤( 只能在group by后使用 )

--查询 平均薪资大于2000的年龄
select age,avg(sal) from users group by age having avg(sal)>2000;

当一条语句中出现了 group by ，那么 select 后只能跟分组字段以及分组函数

9、distinct（去重）

概念：对查询结果去重
注意：1、distinct只能在查询字段的最前方，并且以distinct后的所有字段进行联合去重

10、一条完整DQL语句的执行顺序

11、连接查询

连接查询的分类：

在表连接查询方面有一种现象被称为：笛卡尔积现象
当两张表连接查询时，在没有任何条件限制下，最终的结果条数是两张表条数的乘积
如何避免出现笛卡尔积现象？
答案：给连接查询添加条件限制
思考：添加限制条件，避免了笛卡尔积现象，但还是不会减少记录的匹配次数，只不过显示的是有效数据

11.1、内连接

图示：

1. 等值连接
   概念：条件是等量关系
   语法：

   -- inner 可省略
   ···
   A
   inner join 
   B
   on A.id=B.aId
   ···
   

2. 非等值连接
   概念：条件是非等量关系
   语法：

   -- inner 可省略
   ···
   A
   inner join 
   B
   on A.sale between B.l and B.h
   ···
   

3. 自连接
   概念：一张表当两张表，自己连接自己
   语法：

   -- inner 可省略
   ···
   A
   inner join 
   B
   on A.pid= B.id
   ···
   

11.4、外连接（左连接、右连接）

内连接：AB表进行内连接，但凡能匹配上的记录都将被查询，AB表没有主副之分。
外连接：AB表进行外连接，AB表中一张为主表，另一张为副表。主要查询主表数据，顺带查询副表数据，当副表数据与主表数据未能匹配上时，附表主动模拟出NULL与之匹配。
特点：主表数据将会无条件被全部查询出来

左连接：以左边表为主表
右连接：以右边表为主表

1. 左连接
   

   -- outer 可省略
   ···
   A
   left outer join 
   B
   on A.id= B.aId
   ···
   

2. 右连接
   

   -- outer 可省略
   ···
   A
   right outer join 
   B
   on A.id= B.aId
   ···
   

12、表复制

将user查询结果创建为一张新表

create table xxx as select * from user

13、查询结果放入表中

将user查询结果插入xxx 表中

INSERT INTO xxx SELECT u.name.u.password FROM users u;

14、删除数据

1. delete

   delete FROM users;
   

   delete删除大数据表时，效率会比较低；因为delete删除不会释放存储空间，并且可以进行数据的回滚

2. truncate

   truncate table users;
   

   delete删表时，表会被截断，会释放存储空间，数据不可以回滚，数据永久丢失。

15、约束

为表添加约束的目的：保证表中数据的合法性、完整性、有效性。
常见约束：

15.1、主键约束（primary key）

15.1.1、主键作用

主键值是这行记录在整张表中的唯一标识（人的身份证号码一样）
主键是第一范式要求的一部分，一张表只能有一个主键

15.1.2、主键分类

1. 根据主键字段数量划分：

   1、单一主键：主键只包含一个字段（常用）
   2、复合主键：主键包含多个字段（不建议使用，违背第二范式）

2. 根据主键性质划分：

   1、自然主键：主键是一个与业务无任何关系的值
   2、业务主键：主键值与业务挂钩，不推荐使用（例如：银行卡号、身份证号）

15.1.3、语法

--语法1：
--在 create table 语句中，通过 PRIMARY KEY 关键字来指定主键。
--在定义字段的同时指定主键，语法格式如下：
create table 表名(
...
  <字段名> <数据类型> primary key 
  ...
  )
  --语法2：
  --修改表结构添加主键，语法格式如下：
   alter table <表名> add primary key（字段列表,逗号隔开);

--主键删除
alter table <表名> drop primary key;

15.2、 外键约束（foreign key）

外键是表的一个字段，不是本表的主键，是对应另一个表的主键（或是具有唯一约束（unique）的列）；并且可以为NULL。
定义外键后，不允许删除另一个表中具有关联关系的行。
外键的主要作用是保持数据的一致性、完整性

删除数据，先删除子表，再删除父表
添加数据，先添加父表，再添加子表
创建表时，先创建父表，在创建子表
删除表时，先删除子表，再删除父表

--语法1：
CREATE TABLE tb (
	id INT(11) PRIMARY KEY,
	...
	...
	CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名>(<主表主键列>)
);
--语法2：
ALTER TABLE <表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<主表主键列名>);

--外键删除
alter table <表名> drop foreign key <外键约束名>;

15.3、 唯一约束

唯一约束修饰的字段具有唯一性，不可以重复，但可以为NULL

--语法1 ：
Create table <表名> (
  id int unique,//列级约束
  name varchar (20)
);
--语法2 ：
Create table <表名> (
  id int,
  name varchar (20)
  unique (name)//表级约束
);
--语法3 ：
alter table <表名> add unique(<列名列表，逗号隔开>);

16、 搜索引擎

常见搜索引擎：MyISAM、InnoDB

16.1、 MyISAM 存储引擎

MyISAM存储引擎是MySQL最常用的引擎，但其不支持事务。

• 管理表有以下特征：
  1. 格式文件 - - 存储表的结构 （xxx.frm）
  2. 数据文件 - - 存储表的内容（xxx.MYD）
  3. 索引文件 - - 存储表的索引（xxx.MYI）
• 灵活的 AUTO_INCREMENT字段处理
• 可被转换为压缩、只读表来节省空间

16.2、 InnoDB 存储引擎

InnoDB 存储引擎是MySQL的缺省引擎，其支持事务、行级锁、外键等。使数据安全得到了保障。

• 管理表有以下特征：
  1. 表的结构存储在xxx.frm文件中
  2. 表的数据存储在tablespace表空间中，无法被压缩、无法转换为只读
  3. 在MySQL崩溃后提供自动恢复
  4. 支持联级删除、联级更新

17、 事务

17.1、 事务概念：

一个事务就是一个完整的业务逻辑单元，不可再拆分。
保证多天DML语句必须同时执行成功，或是同时执行失败，不允许出现一条成功，一条失败。

17.2. 与事务有关的语句有哪些？

与事务有关的语句只有DML语句（insert、update、delete），因为这三个语句都是与数据库数据相关的操作语句。事务就是为了保证数据的完整性，安全性。

17.3. 事务执行原理

一个业务需要先执行一条insert，再执行一条update,然后再执行一条delete，才算是完成.

1. 开启事务机制
2. 执行insert语句 …
3. 执行update语句 …
4. 执行delete语句 …
5. 事务提交或回滚

开启事务后，所有执行的语句都不会直接修改磁盘上的数据，会记录在数据库操作历史中。
当事务提交后，数据才会持久化到磁盘上，并且清空操作历史。
当事务回滚后，数据不会同步持久到磁盘上，并且清空操作历史。

17.4. 事务的特性

事务包含四大特性：ACID
A、原子性：事务是最小的工作单元，不可再拆分
C、一致性：事务必须保证多条DML语句同时成功或同时失败。
I、隔离性：事务之间存在隔离
D、持久性：数据最终必须持久化到磁盘中，事务才算成功结束。

17.5. 事务之间的隔离性

事务隔离性存在隔离级别，理论上存在4种隔离级别

1. 第一级别：读未提交（read uncommitted）
   对方未提交的数据，当前事务也可以读取到数据。
读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
2. 第二级别：读已提交（read committed）
   对方提交后的数据，当前事务可以读取到。
这种隔离级别解决了：脏读现象
这种隔离级别存在的问题：不可重复读
3. 第三级别：可重复读（repeatable read）
   这种隔离级别解决了：不可重复读问题
这种隔离级别存在的问题：读到的数据是幻象
可重复读是指第一次读取的是实时的数据，往后读取的是第一次的快照。
4. 第四级别：序列化读/串行读
   这种隔离级别解决了所有问题
这种隔离级别存在的问题：效率低，需要事务排队

Oracle默认隔离级别：读已提交
MySQL默认隔离级别：可重复读

17.6. 事务隔离级别

查看全局隔离级别：

SELECT @@transaction_isolation;

设置全局隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

设置事务隔离级别，需要退出MySQL再重新登录才能看到隔离级别的变化
所以修改显示成功了,但是你在查没有变化时,需要断开连接新连接才能看到变化

17.7. 事务使用

MySQL事务默认为自动提交（执行一次DML语句就提交一次）

关闭事务自动提交：

start transaction;

事务提交：

commit;

事务保存点：

savepoint xxx

事务回滚：

--回滚全部
rollback;
--回滚至保存点
rollback xxx;

18、 索引

18.1、 索引是什么？有什么用？

索引相当于一本书的目录，通过目录可以快速访问到对应的资源。
在数据库当面，查询一条数据有两种检索方式：全表扫描、索引检索 (效率高( 缩小了扫描范围 ))
思考：索引可以提高检索效率，那索引可以任意添加吗？

不能随意添加索引，因为索引是数据库中的对象，也需要数据库不断的维护，存在维护成本
当表中索引字段的数据被修改时，索引就需要重新排序，进行维护。

18.2、什么时候需要创建索引

1. 数据量庞大
2. 该字段很少进行DML操作（修改操作，索引需要维护）
3. 该字段经常出现在where子句中（经常根据该字段进行查询）

18.3、 创建索引、删除索引

--创建索引 
--方式一
create index 索引名称 on 表名(字段名);
--方式二：
alter table 表名 ADD INDEX 索引名称(字段名);

--删除索引
drop index 索引名称 on 表名;

18.4、 索引底层原理

索引底层数据结构为：B + Tree
索引通过b+tree缩小了扫描范围，底层索引会进行分区、排序，索引会携带数据在磁盘中的 “物理地址”。通过索引获取到关联的物理地址，通过物理地址定位数据。
例：为user表添加 idCard 索引,并查询数据：

原始SQL：
select * from user where idCard=‘1234567890’：
通过索引转换为：(idCard='1234567890’的物理地址为 0x3)
select * from user where 物理地址= 0x3

图文解析：

18.5、 索引分类

1. 单一索引：给单个字段添加索引
2. 复合索引：把多个字段联合起来添加一个索引（不推荐使用，违反第二范式）
3. 主键索引：主键上会自动添加
4. 唯一索引：有unique约束的字段会自动添加

18.6、 索引什么情况下会失效

索引失效，七字口诀：模型数空运最快

1. 模：模糊查询，like的模糊查询以%开头，索引失效

   SELECT * FROM users WHERE name LIKE '%三';
   

2. 型：数据类型错误，索引也会失效。如字段类型为varchar，where条件用number

   -- height为varchar类型导致索引失效。
    SELECT * FROM users WHERE height = 180; 
   

3. 数：对索引的字段使用内部函数，索引会失效。这种情况下应该建立基于函数的索引。

   -- create_time字段设置索引，那就无法使用函数，否则索引失效。
    SELECT * FROM users  WHERE DATE(create_time) = '2020-09-03';  
   

4. 空：是Null的意思。索引不存储空值，如果不限制索引列是not null，数据库会认为索引列有可能存在空值，所以不会按照索引进行计算。

   -- 不走索引
    SELECT * FROM users WHERE address IS NULL;
   -- 走索引
    SELECT * FROM users WHERE address IS NOT NULL;
   

   建议大家这设计字段的时候，如果没有必要的要求必须为NULL，那么最好给个默认值空字符串，这可以解决很多后续的麻烦。

5. 运：是运算的意思。对索引列进行（+，-，*，/，!, !=, <>）等运算，会导致索引失效。

   SELECT * FROM users WHERE age - 1 = 20;
   

6. 最：最左原则。在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。
7. 快：全表扫描更快的意思。如果数据库预计使用全表扫描要比使用索引快，则不使用索引。

补充：查看SQL执行计划：explain
举例：explain select * from user

19、 视图

视图就是站在不同角度去看数据。

19.1、 如何创建视图、删除视图？

创建视图：

create view myview as select id,name,age from users;

删除视图：

drop view myview;

注意：
1、只有DQL语句才能以视图对象的方式创建出来
1、对视图进行crud会影响到原表数据；

19.2、 视图的作用

视图可以隐藏表的实现细节，只展现出想要被外部看到的结构及数据。
举例：保密级别较高的系统，只对程序员提供视图，程序员只能通过视图对表数据进行CRUD

20、存储过程

存储过程是一组为了完成特定功能的SQL语句集，它通过预编译后存储在数据库中，一次编译永久有效（后续调用，不在进行编译）；存储过程是数据库中的一个重要对象。

20.1、存储过程优点

1. 重复使用。存储过程可以重复使用，通过调用execute命令，执行处存储过程
2. 减少网络流量。存储过程位于服务器数据库中，调用时只需要传递存储过程名称及参数即可，降低网络数据传输量
3. 安全性。参数化的存储过程可以预防SQL注入
4. 提高数据库执行速度。存储过程只在创建时进行编译，再次调用便无需编译。一般SQL执行一次就要需要编译一次，因此使用存储过程可以提高数据库执行速度

20.2、存储过程创建

mysql服务器端窗口创建存储过程

1. 无参数存储过程

   create procedure proc1()
   as 
   begin 
   --sql语句集
   select * from users
   end
   --调用存储过程
   call proc1();
   

2. 有入参数存储过程

   create procedure proc1(in tel varchar(20),in sex varchar(20))
   as 
   begin 
   select * from users where tel=@tel and sex=@sex
   end
   --调用存储过程
   call proc1('15091774040','男');
   

3. 有出参数存储过程

   --在调用带有输出参数的存储过程之前，一定要首先定义一个变量，用来接收存储过程的输出结果
   set @res = 0;
   --注意两个关键点: 第一是变量名之前一定要加@符号，第二是变量名一定要赋初始值
   create procedure proc1(in val int,out res int)
   as 
   begin 
   select val+1 into res;
   end
   --调用存储过程
   call proc1(11,@res);
   --查询变量的值
   select @res;
   

存储过程的参数列表

• in参数模式：被该模式修饰的参数可以作为输入参数，也就是该参数需要调用的传入值
• out参数模式：被该模式修饰的参数可以作为输出，也就是该参数可以作为存储过程的返回值
• 如果存储过程没有返回值，那么会执行begin和end之间的sql语句，并显示执行结果
• 如果存储过程有返回值，那么也会执行begin和end之间的sql语句，但是不会显示执行结果，而是把执行结果存储到输出参数中

注意：
1、存储过程关键字procedure 可以缩写为proc
2、存储过程参数在as之前，as之后的是局部变量，两种变量在存储过程中都可以使用，但命名不可以冲突。
3、begin与end中的sql语句末尾添加分号

21、 触发器

触发器相当于一个触发装置，当满足了触发条件便会进行相应的操作。
触发器是一种特殊的存储过程，它不是由用户去触发，而是当发生某一事件后，由系统自动触发。
触发器是数据库的一种自动处理机制，主要作用是保证数据库数据的安全性。
触发器工作机制：
触发器是建在表上的，当这个表发生新增、修改、删除时，会自动调用此前设置好的触发器。在触发器执行过程中会创建一个临时表，这个临时表只有一行记录
当执行新增时，临时表名称为inserted
当执行删除时，临时表名称为deleted
当执行更新时，会产生两个临时表，一个是inserted（存放新的数据），一个是deleted（存放旧数据）

21.1、 触发器分类

触发器分为 ：

1. for each row：作用在每行
2. for each statement：作用在每条语句

这二者的区别的就是当一条语句使得多条记录发生改变时，前者会触发多次触发器，而后者只会触发一次。

21.2、 触发事件的分类

触发事件根据执行SQL的操作不同可以分为 INSERT ，UPDATE ， DELETE三种，即在执行插入，更新和删除操作时会激活触发器。

注意：三种触发器的触发条件如下。
INSERT：向表中插入一条记录时会激活触发器，即insert语句。
DELETE：删除表中的记录时激活触发器，即delete语句。
UPDATE：更新表中的记录时激活触发器，即update语句。

21.3、触发器执行时间

触发器可以在语句被执行之前触发也可以在语句执行之后触发，即BEFORE 和 AFTER两种。

21.4、 触发器的创建

create trigger 触发器名称 on 表名
for insert|delete|update
as 
begin
--sql语句
end;

CREATE trigger w_log  #触发器的名称
    AFTER INSERT  #当执行插入语句之后激活触发器
    ON tb_student  #作用于那个表
    FOR EACH ROW #触发器作用在每条记录上
    # 触发器需要执行的操作
    BEGIN
        set @t_name = new.sname;
        #当插入一条学生记录就向日志表插入一条记录此次操作
        INSERT INTO tb_log(operation,oper_time) VALUES (concat("insert a row record:",@t_name),NOW());
    END;

22、 数据库设计三范式

22.1、 三范式作用

按照这三个范式设计的表不会出现数据冗余。

22.1、 三范式分别是什么？

1. 第一范式
   任何一张表都应该有主键，并且每个字段原子性不可再分。

2. 第二范式
   建立在第一范式基础上，所有非主键字段完全依赖于主键，不能产生部分依赖。（复合索引可能会违反此范式）

   当出现多对多关系时，三张表，关系表两外键

   student 学生表

   sno(pk)	name
   s1	张三
   s2	李四
   s3	五四

   teacher 老师表

   sno(pk)	name
   t1	张三
   t2	李四
   t3	五四

   学生讲师关系表

   id(pk)	sno(fk)	tno(fk)
   1	s1	t3
   2	s2	t2
   3	s1	t3

3. 第三范式
   建立在第二范式基础上，所有非主键字段直接依赖主键，不能产生依赖传递

   当出现一对多关系时，两张表，多的表添加外键

   class 班级表

   cno(pk)	name
   c1	班级1
   c2	班级2
   c3	班级3

   student 学生表

   sno(pk)	name	classno(fk)
   s1	张三	c1
   s2	李四	c2
   s3	五四	c1

   提示：在开发中，以满足客户需求为主，有时会拿冗余换执行速度（空间换效率）

22.2、 一对一关系表怎么设计？

两种设计方案：

1. 主键共享
   商品表

   id(pk)	name	money
   g1	矿泉水	2$
   g2	火腿	4$
   g3	卤蛋	6$

   商品明细表

   id(pk+fk)	address	date
   g1	山西	2022-11-23
   g3	西安	2022-11-07
   g2	杭州	2022-12-16

2. 外键唯一
   商品表延续使用1
   商品明细表

   id(pk)	address	date	goodid(fk+unique)
   1	山西	2022-11-23	g1
   2	西安	2022-11-07	g3
   2	杭州	2022-12-16	g2

21、DBA命令：

1、导出数据：mysqldump 数据库名>文件地址 -u用户名 -p密码

mysqldump lean_database>c:\lean.sql -uroot -p123456

1、导入数据：sources 文件地址

sources c:\lean.sql

22、补充：

mysql数据库SQL语句不区分大小写，数据也不区分大小写
oracle数据库SQL语句不区分大小写，但数据区分大小写
oracle语法比较严格，但MySQL相对松散些

22、学习参考链接：

链接1、链接2、链接3