Mysql：数据库

一、数据库概念？

 mysql是一套基于CS模式，给我们一个提供数据存取服务的网络程序。日常口语中的数据库其实是指在磁盘或内存中存储的特定组织结构的数据。
 存储数据最常见的方式就是直接用文件存储。但文件存储存在诸多缺陷：

1. 安全性问题！普通文件没有权限约制，数据可以随意被访问，存在数据安全问题。
2. 文件不利于海量数据的查询和管理。比如需要在100万数据中查找主机ip为198.1.1.0的ip地址的个数。对于文件而言需要程序员将文件打开，然后按行遍历、判断、统计等工作。非常麻烦，效率低下。 但对于数据库而言，数据库本质其实就是一套对数据内容存取的一套解决方案。程序员将相关字段和要求传给数据库即可，数据库会将结果直接反馈呈现给程序员！
3. 文件在程序中不方便控制。文件系统缺乏事务管理、并发控制等机制，难以保证数据的一致性和完整性。MySQL架构

 宏观上数据库是指：数据库客户端、服务端以及数据库文件。而口语中思的数据库指数据在磁盘上存取的一套解决方案（create databases创建出来的数据库）
  在Linux中，数据库本质就是/var/lib/mysql目录下的一个目录文件，而建表本质就是该该目录下的一些普通文件。

二、MySQL架构

 第一层主要解决连接和安全问题。第二层类似于编译器主要进行词法分析、语法分析、以及mysql语句优化。第三次为各种存储引擎，是真正和OS系统进行交换，实现功能的。
 存储引擎是：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法（show engines;查看）

三、SQL语句分类

 sql语言分为以下3类：

1. 数据定义语言（DDL）,用于维护存储数据的结构。代表指令：create、drop。
2. 数据操纵语言（DML）,用来对数据进行操作。代表指令：insert、delete、update。
3. 数据控制语言（DCL）,主要负责权限管理和事务。代表指令： grant、revoke、commit。

四、数据库操作

4.1 数据库创建

 指令：create database [if not exists] db_name [charset = xxx] [collate = xxx];

• charset （或character set）:数据库字符集，数据库存储数据的编码格式！
• collate ：数据库校验集，数据库将数据读取出来时所采用的编码格式！

4.2 数据库字符集和校验规则

作用	指令
查看系统默认字符集	show variables like 'character_set_database';
查看系统默校验规则	show variables like 'collation_database';
查看数据库支持的字符集	show charset;
查看数据库支持的校验规则	show collation;

4.3 数据库修改

 指令:alter database db_name [charset = xxx] [collate = xxx];

• 数据库的修改主要是对数据库的字符集和校验规则进行修改，但需要保证修改后的字符集和校验规则匹配，否则数据的存储或读取可能出现乱码。

4.4 数据库删除

 指令:drop database db_name [if not exists];

• 数据库的创建本质就是在/var/lib/mysql下创建的目录文件，而数据库删除的本质就是该目录文件被删除。
• 数据库删除后，对应的数据库文件夹被删除，级联删除，里面的数据表全部被删。

4.4 数据库备份和恢复

作用	指令	样例
数据库备份	mysqldump -P3306 -u root -p -B 数据库名 > 文件路径	mysqldump -P3306 -u root -p123456 -B mytest > mytest.sql
数据库恢复	source 文件路径;	source D:/mysql-5.7.22/mytest.sql;
备份表	mysqldump -u root -p 数据库名 表名1 表名2 > mytest.sql;
备份多个数据库	mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

• 如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据
  库，再使用source来还原。

其他

• 查看连接情况指令：show processlist;

五、表操作

5.1 创建表

CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

• field指明表结构的列名，datatype指明列名类型。
• 对于字符集、校验规则、存储引擎如果没有显示指定，默认采用表所在的数据库相关属性。

5.2 修改表

 修改表的结构有，比如字段名字，字段大小，字段类型，表的字符集类型，表的存储引擎等等。我以及添加字段，删除字段等等。

作用	指令	样例
添加字段	alter table tablename add [field datatype] [,field datatype]....;	在原有表结构基础上，即birthday列之后添加assets 列：alter table users add assets varchar(100) comment '图片路径' after birthday;
删除字段	alter table tablename drop (column);	删除name列： alter table users dropname ;
修改字段类型	alter table tablename modify [field datatype] [,field datatype]....;	将name属性修改为60： alter table users modify name varchar(60);
修改表名	alter table 旧命 rename to 新名
对某列进行重命名	alter table tablename xxx name xxx	alter table employee change name xingming varchar(60); --新字段需要完整定义

5.3 删除表

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

六、表的增删改查

6.1 Create(创建):数据新增

1）普通插入

insert into table (需要插入数据的类名) values (插入数据);

多行数据 + 指定列插入:

INSERT INTO students (id, sn, name) VALUES
(102, 20001, '曹孟德'),
(103, 20002, '孙仲谋');

2）插入否则更新

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败。所以可以选择性的进行同步更新操作语法：

指令：INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

实例：

// 向students表中插入数据，不存在直接插入。如果发送冲突，则执行UPDATE 之后的语句进行数据更新
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';Query OK, 2 rows affected (0.47 sec)
-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新

3） 替换方式二：replace

指令：REPLACE INTO students ...
实例：

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

6.2 Retrieve(读取)

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

1. DISTINCT关键字用于对结果去重。
2. WHERE 后接筛选条件
3. ORDER BY：按照指定列进行排序，ASC升序， DESC降序。
4. LIMIT ：输出结果显示限制

1）WHERE条件

2） 筛选分页结果

-- 起始下标为 0-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

6.3 Update(更新)

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

• 将查询到的结果进行更新。如果后面没有筛选条件，则表面对整表进行操作。

6.4 Delete（删除）

删除查到的数据： DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
截断表：TRUNCATE [TABLE] table_name;

• delete只是对数据进行删除，表结构依然存在。
• truncate截断不走事物，不会对日志进行操作（记录删除操作），所以速度更快。但由于不经过事物，所以无法归滚。
• truncate会重置AUTO_INCREMENT 项，而delete不会！

6.5 插入查询结果

INSERT INTO table_name [(column [, column ...])] SELECT ..

实例：对表进行重命名

// 创建表和新增数据
CREATE TABLE duplicate_table (id int, name varchar(20));
INSERT INTO duplicate_table VALUES (100, 'aaa'), (100, 'aaa');//创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
CREATE TABLE no_duplicate_table LIKE duplicate_table;//将 duplicate_table 的去重数据插入到 no_duplicate_table
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table// 通过重命名表，实现原子的去重操作
RENAME TABLE duplicate_table TO old_duplicate_table, no_duplicate_table TO duplicate_table;

1. 最后通过rename进行，目的在于该操作是原子的，等一切都做好后，进行统一放入、更新、生效。

七、mysql聚合函数

 聚合函数是先将数据查询到后，在进行聚合。聚合时必须保证数据是可以被聚合的。

执行顺序样例：

八、查询分类

1）多表查询

 多表查询是指对多张表进行笛卡儿积，然后通过内链接on或where筛选出我们需要的表。
 在mysql中，不仅仅物理磁盘上存在的表结构叫表，中间筛选出来的，以及最终结果也是逻辑上的表。也可以直接将其重命名后直接当作单独的表使用，即Mysql下一切皆表！

2）自连接

 自连接是指在同一张表连接查询！

 自连接的典型使用场景就是：一张表中保存公司所有人员的id以及上级id。现在需要查找某个员工的上级领导名字，那我们会使用自连接。

3） 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。子查询可以跟在where和from后面。

4）合并查询

• union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。但需要保证两个结果的字段必须一样。

实例：将工资大于2500或职位是MANAGER的人找出来

• union all: 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。