mysql基本操作及相关内容

MySQL相关内容：https://www.runoob.com/mysql/mysql-tutorial.html

SQL(Structure Query Language)结构化查询语言是数据库的核心语言，是高级的非过程化编程语言。它功能强大，效率高，简单易学易维护。SQL语言基本上独立于数据库本身、使用的机器、网络、操作系统，基于SQL的DBMS产品可以运行在从个人机、工作站到基于局域网、小型机和大型机的各种计算机系统上，具有良好的可移植性。

SQL结构化查询语言包含6个部分:

(1)数据定义语言（DDL）
常用的有CREATE和DROP，用于在数据库中创建新表或删除表，以及为表加入索引等。
(2)数据操纵语言（DML：Data Manipulation Language）
主要用来对数据库的数据进行一些操作，常用的就是INSERT、UPDATE、DELETE。

语法：
    INSERT INTO <表名>(列1,列2,...) VALUES (值1,值2,...);
    UPDATE <表名> SET <列名>=新值 WHERE <列名>=某值;
    DELETE FROM <表名> WHERE <列名>=某值;

(3)数据查询语言（DQL: Data Query Language）
数据检索语句，用于从表中获取数据。通常最常用的为保留字SELECT,并且常与FROM子句、WHERE子句组成查询SQL查询语句。

语法：
    SELECT <字段名> FROM <表或视图名> WHERE <查询条件>;

(4)事务处理语言（DPL）
事务处理语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION、COMMIT和ROLLBACK。
(5)数据控制语言（DCL）
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。通过GRANT和REVOKE，确定单个用户或用户组对数据库对象的访问权限。
(6)指针控制语言（CCL）
它的语句，想DECLARE CURSOR、FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

数据库设计三大范式
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
而通常我们用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF）。
①第一范式（1NF）： 要求数据库表的每一列都是不可分割的原子数据项。
②第二范式（2NF）： 在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
最好的解决方案：出id（唯一标识），每一列直接与id关联。
③第三范式（3NF）： 在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
例子参考：https://www.cnblogs.com/xietianjiao/p/10972285.html

mysql基本操作
(1)启动数据库： mysql -u root -p
(2)创建/使用/查看/删除数据库
创建数据库：create database 数据库名;
查看所有数据库：show databases;
查看创建数据库的定义信息：show create database 数据库名称；
使用数据库： use 数据库名;
查看数据库中所有的表：show tables;
删除数据库：drop database 数据库名;
(3)创建/查看/删除表
创建表: create table tb_user(id int, name varchar(225), pwd varchar(225));
create table tb_money(id int primary key auto_increment, name int not null, pwd int default 100);
UNSIGNED------无符号，没有负数，从0开始
ZEROFILL-------零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
NOT NULL------非空约束，也就是插入值的时候这个字段必须要给值,值不能为空
DEFAULT------默认值，如果插入记录的时候没有给字段赋值，则使用默认值
PRIMARY KEY------主键（当作索引保存），标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
AUTO_INCREMENT------自增，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
UNIQUE KEY------唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是NULL值除外
FOREIGN KEY------外键约束
查看创建的表的详细信息：show create table 表名；
查看表的结构：desc 表名；
删除表：drop table 表名；
(4)修改表
修改表的字符集：alter table 表名character set 编码方式;
修改表名：rename table 旧表名 to 新表名;
添加列：alter table 表名 add（ 列名，属性）;
修改列的属性：alter table 表名 modify 列名 属性;
修改列的名称: alter table 表名change column 旧列名 新列名 varchar(20);
删除列: alter table 表名 drop 列名属性;
(5)对表中数据的操作
插入数据: insert into 表名(列名,列名…) value/values(对应的数据);
例：insert into tb_user(id, account, pwd)values(3, ‘zcy’, 123456);
（在插入单行时，使用values比较快；插入多行时，用value比较快。）
删除数据:
删除表中所有记录: delete from表名; 按行删除表中的所有记录，但会保留表，适合删除数据量不大的数据，可按条件删除
按条件删除表中的数据：delete from 表名 where 条件表达式;
删除表本身： drop table表名；
改正数据： update 表名 set 字段 = 值 条件；
例：update tb_user set name = ‘aaa’, pwd = ‘123’ where id = 9;
查询数据: select [列名 别名] from 表名 别名 where 条件；
例：select name as ‘姓名’， pwd as ‘密码’ from 表名 as a；
查询所有列： select * from 表名； select * from 表名 where…；
查询需要的列：select 列名 from 表名；
distinct过滤重复的数据 ： select distinct * from 表名;

(6)运算符：
比较运算符： between …and…显示在某一区间的值
in(a,b,c…)显示在in列表中的值 not in(a,b,c…)显示不在in列表中的值
is null/is not null 判断是否位空
逻辑运算符： and && 多个条件同时成立
or || 多条件任一成立
not !不成立
模糊查询 ： like ‘%a%’ （%代表零个或多个字符 a表示一个字符）

（7）函数 Mysql中像其他的语言工具一样，提供了对于数据库用户，更方便管理数据库的一套工具，用户可以通过使用这些工具来处理一些相对复杂一点的数据。
这些工具其中一部分，就是函数。这些函数和我们认知到的所有面向过程语言中的函数没有区别，也是有返回值和参数的。
举例：uuid（）： 可以连续调用不会重复的函数，得到一个32位16进制的数字。

replace（）：替换

concat（）：连接

（8）聚合函数: 是我们在使用数据库查询过程当中，针对查询出来的结果，进行二次操作包装，使用到的一些特殊的函数。
select 聚合函数 from 表名 where 条件

举例：
max （）：求最大值 select max(被求者) from 表名 （where 条件）;
min（）：求最小值 select min(被求者) from 表名（where 条件）;
当max()和min()函数位于日期类型时，分别取得最近日期和最早日期。
count（）：聚合 数数 统计有多少个内容，不统计NULL值 select count(被求者) from 表名（where 条件）;
avg（）： 求平值 select avg(被求者) from 表名（where 条件）;
group by：分组 select … from 表名 group by(被分组者）；
类过滤器：having 条件表达式;
where和having区别：
where主要用于行过滤器；
having主要用于类别过滤器，通常有having就一定出现group by,但有group by的地方，不一定出现having。

order by: 排序 (desc放在前面是描述 ,放在后面是降序)

MySQL 索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件，过多的使用索引将会造成滥用。
索引原理 ->B+树
索引分类：这个分类和引擎有关系，我们讨论的索引是在InnoDB引擎中讨论，InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。
①主键索引：不能重复，不能是空。
②唯一索引（unique）：不能重复，可以是空。
③普通索引（index）：可以重复，可以是空。
④联合索引（union）：普通索引的联合，必须符合最左原则，从最左端开始必须进入索引。
⑤全文索引
关于索引：
https://www.cnblogs.com/little-orangeaaa/p/9707850.html
Mysql 的存储引擎MYISAM和InnoDB的区别。
答：①MyISAM 是非事务的存储引擎，适合用于频繁查询的应用。表锁，不会出现死锁，适合小数据，小并发。
②InnoDB是支持事务的存储引擎，适合用于插入和更新操作比较多的应用，设计合理的话是行锁（最大区别就在锁的级别上），适合大数据，大并发。

最左匹配原则：
最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

最左匹配原则的原理：
最左匹配原则都是针对联合索引来说的，所以我们有必要了解一下联合索引的原理。
我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的键值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。
例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。
同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
视图内部如果给单表添加内容是可以的，如果给多表添加内容会出现问题。


存储过程是将SQL的批量执行过程整合到BEGIN和END中间，存储过程使用命令CALL执行。

调用存储过程：call foo();
MySql 的命令行客户机的语句分隔符默认为分号 ; ，而实用程序也是用 ; 作为分隔符，这会使得存储过程的 SQL 出现语法错误，使用 DELIMITER $$告诉命令行实用程序将$$ 作为新的语句结束分隔符，最后再使用 DELIMITER 。

带参数的存储过程:
MySql 支持 IN (传递给存储过程) , OUT (从存储过程传出) 和 INOUT (对存储过程传入和传出) 类型的参数 , 存储过程的代码位于 BEGIN 和 END 语句内 , 它们是一系列 SQL 语句 , 用来检索值 , 然后保存到相应的变量 (通过指定INTO关键字)。
IN 获得外界传进来的值，但存储过程执行完成之后并不改变外界变量中的值 ；
OUT 为了改变传进来的变量的值 ，变量传进来之前的值并不关心；
INOUT既要获得传进来的值，又要改变最终的值。

触发器（TRIGGER）触发器是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作( insert，delete， update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器只能增删改

select不能加触发器，因为select是很多次的查询，涉及到多个表，而触发器只对单表设置。

触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。例如:您可以根据客户当前的帐户状态，控制是否允许插入新订单。

触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。

触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。

触发器的优点
触发器可通过数据库中的相关表实现级联更改，不过，通过级联引用完整性约束可以更有效地执行这些更改。触发器可以强制比用CHECK约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。
慎用触发器
触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作…… 同时规则、约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。

before after是时机选择
insert update delete是业务选择
table name是范围选择

如何进行SQL优化？
答：（1）选择正确的存储引擎
以 MySQL为例，包括有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。但是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

（2）优化字段的数据类型

记住一个原则，越小的列会越快。如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。当然，你也需要留够足够的扩展空间。

（3）为搜索字段添加索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么最好是为其建立索引，除非你要搜索的字段是大的文本字段，那应该建立全文索引。

(4)避免使用Select 从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。即使你要查询数据表的所有字段，也尽量不要用通配符，善用内置提供的字段排除定义也许能给带来更多的便利。

(5)使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。例如，性别、民族、部门和状态之类的这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

(6)尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。 NULL其实需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

(7)固定长度的表会更快

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。