MYSQL使用基础进阶分享

　　MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品，是最流行的关系型数据库管理系统之一。
　　端口是3306。 规范不区分大小写 以 ;  结尾，支持多行输入通过tab和空格提高可读性 注释：单行注释： --  多行注释：/* .... */  数据库创建数据库以默认的编码方式创建：    CREATE    DATABASE   < 数据库名称  >;创建时，指定编码方式：    CREATE    DATABASE   < 数据库名称  > CHARACTER  SET  <编码方式 >;不存在时才创建：    CREATE    DATABASE   IF  NOT    EXISTS   < 数据库名称  >;存在时不会报错，有warning 使用：SHOW WARNINGS；  查看。查看数据库查看所有数据库：    SHOW   DATABASES; 查看当前数据库：    SELECT    DATABASE  (); 为NULL时，说明未使用数据库可查看编码    SHOW    CREATE    DATABASE   < 数据库名称  >;使用数据库即进入或切换到某个数据库中：  USE <  数据库名称  >;修改数据库修改编码方式    ALTER    DATABASE   < 数据库名称  > CHARACTER  SET  <编码方式 >;修改名称 修改起来比较麻烦
　　MySQL 5.1.23之前的旧版本 使用： RENAME    DATABASE   < 旧名称  > TO  <新名称 >;都可以用的方法：
　　此方法实际上将所有表从一个数据库移动到另一个数据库。
　　如：把＂user＂改为＂data＂-- 1 创建新的数据库＂data＂ CREATE DATABASE data; -- 2 使用RENAME TABLE命令修改表名，将表移动到新的库里 RENAME TABLE user.table1 TO data.table1; -- 假如有多个表的话，应该重复使用RENAME命令 -- 3 完成后删除旧库＂user＂ DROP DATABASE user;
　　表很多时，使用linux脚本，需要根据需要修改一下： #!/bin/bash  mysql -uroot -p<密码> -e ＂create database if not exists <新数据库名称>;＂  list_table=$(mysql -uroot -p<密码> -Nse ＂select table_name from information_schema.TABLES where TABLE_SCHEMA=＂<旧数据库名称>＂＂)   for table in $list_table  do  mysql -uroot -p<密码> -e ＂rename table <旧数据库名称>.$table to <新数据库名称>.$table＂  done    # mysql 参数：  # -e, --execute=name        # 执行mysql的sql语句  # -N, --skip-column-names   # 不显示列信息  # -s, --silent              # 一行一行输出，中间有tab分隔删除数据库DROP DATABASE <数据库名称>;
　　和创建一样，可以加上 if exists  表数据类型
　　可两篇文章：
　　Mysql学习笔记之常用数据类型
　　MySQL常用数据类型约束条件NOT NULL   - 指示某列不能存储 NULL 值。UNIQUE   - 保证某列的每行必须有唯一的值。PRIMARY KEY   - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。FOREIGN KEY   - 保证一个表中的数据匹配另一个表中的值的参照完整性。CHECK   - 保证列中的值符合指定的条件。DEFAULT   - 规定没有给列赋值时的默认值。其他字段   AUTO_INCREMENT  - 默认AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。修改默认值ALTER TABLE 表名 AUTO_INCREMENT=100  创建表CREATE TABLE <表名称>( <字段名> <类型> [约束条件], <字段名> <类型> [约束条件], <字段名> <类型> [约束条件], );
　　如： CREATE TABLE Persons (     Id_P int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Address varchar(255),     City varchar(255),     PRIMARY KEY (Id_P)  //PRIMARY KEY约束 ) CREATE TABLE Persons (     Id_P int NOT NULL PRIMARY KEY,   //PRIMARY KEY约束     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Address varchar(255),     City varchar(255) )查看表信息查看表结构    DESC   < 表名称  >; -- 或者  SHOW  COLUMNS FROM  <表名称 >;查看当前数据库的所有表    SHOW   TABLES; 查看建表语句    SHOW    CREATE    TABLE   < 表名称  >;修改列
　　用于在已有的表中添加、删除或修改列。
　　添加 ADD  ALTER TABLE <表名称> ADD <字段名> <类型> [约束条件];
　　或  ALTER TABLE <表名称> ADD <字段名> <类型> [约束条件],  ADD <字段名> <类型> [约束条件]，  ADD <字段名> <类型> [约束条件]，  ADD <字段名> <类型> [约束条件]；
　　默认是添加到最后，但可以指定位置。  FIRST  ：添加最前
　　AFTER <字段名>  ：添加指定字段之后
　　例子：  ALTER TABLE data ADD username VARCHAR(20) AFTER id;  -- 在data中往id后面添加一个username字段  -- FIRST的使用方法类似
　　删除 DROP  ALTER TABLE <表名称> DROP <字段名>;  -- 和添加一样可以多行DROP
　　修改 MODIFY 主要修改原列的类型或约束条件 同样可以用 FIRST  和 AFTER <字段名>  ，代表的是修改到哪里。  ALTER TABLE <表名称> MODIFY <字段名> <类型> [约束条件];  -- 和添加一样可以多行MODIFY
　　修改字段名 CHANGE  ALTER TABLE <表名称> CHANGE <旧字段名> <新字段名> <类型> [约束条件];  -- 和添加一样可以多行CHANGE删除表DROP TABLE <表名称>复制表CREATE TABLE <表1名称> (SELECT * FROM <表2名称>);
　　可以把表2的数据复制到表1中，但 不能复制约束性条件 。 记录增加
　　单行 INSERT INTO <表名称>(行1, 行2...) VALUES(值1, 值2...);
　　多行，注意 只有一个VALUES ： INSERT INTO <表名称>(行1, 行2...) VALUES 			(值1, 值2...), 			(值1, 值2...), 			(值1, 值2...);
　　不写 (行1, 行2...)  这一部分的话，默认一一对应
　　除了以上方法外，还可以用SET为每一行附上相应的值。 INSERT INTO <表名称> SET 行1=值1, 行2=值2...;修改
　　假如没有筛选的话，就给全部都修改了。可以用 WHERE  筛选。-- 修改一个或多个 UPDATE <表名称> SET 行1=值1, ...; -- 加上WHERE UPDATE <表名称> SET 行1=值1, ... WHERE ...		删除
　　假如 没有筛选的话，就给全部删除了 。相当于清空。 DELETE FROM <表名称>;
　　清空 TRUNCATE TABLE <表名称>;
　　先把表删除，然后再建一个。与 DELETE FROM  相比，TRUNCATE  的效率更快，因为DELETE FROM  是把记录逐条删除的。查询（单表）
　　查询执行的顺序
　　FROM --> WHERE --> SELECT --> GROUP BY --> HAVING --> ORDER BY --> LIMIT SELECT SELECT 语句用于从数据库中选取数据。结果被存储在一个结果表中，称为结果集。 *  ：SELECT * FROM <表名称>  把表中所有的信息都取出来DISTINCT  ：SELECT DISTINCT <字段名> FROM <表名称>  去重AS  ：SELECT <字段名> AS <别名> FROM <表名称>  别名 如：SELECT data+10 AS ＂数据＂ FROM <表名称>  WHERE WHERE子句用于过滤记录。
　　运算符
　　描述
　　例子
　　=
　　等于
　　SELECT * FROM data WHERE id = 1;
　　<>
　　不等于。某些 SQL中，可写成 !=
　　参考  =  的
　　>
　　大于
　　参考 =  的
　　<
　　小于
　　参考  =  的
　　>=
　　大于等于
　　参考 =  的
　　<=
　　小于等于
　　参考  =  的
　　BETWEEN
　　在某个范围内
　　SELECT * FROM data WHERE id BETWEEN 2 AND 4; -- 2到4，两边都是闭区间
　　LIKE
　　使用通配符搜索
　　%  ：0 个或多个字符，_  ：一个字符，[charlist]  ：字符列中的任何单一字符，[^charlist]  或[!charlist]  ：不在字符列中的任何单一字符；如：WHERE 字段名 LIKE ＂a_%_%＂  以＂a＂开头且长度至少为3个字符的值
　　IN
　　指定针对某个列的多个可能值
　　SELECT * FROM data WHERE id IN (1,3,6);
　　AND
　　OR
　　NOT
　　IS
　　IS NUll  或IS NOT NULL  ORDER BY 以某一字段名进行排序。
　　如： SELECT * FROM data ORDER BY name;
　　可以指定升序(ASC  )、降序(DESC  )，默认升序。 SELECT * FROM data ORDER BY name DESC;  GROUP BY 行名： SELECT name FROM data GROUP BY name;  数字：指的是SQL语句中出现的字段顺序，如： SELECT sum(id), name FROM data GROUP BY 2;  2代表额是第二个字段name  与聚合函数结合，如上面例子中的sum。  注：如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的 。报错：＂this is incompatible with sql_mode=only_full_group_by＂ HAVING 分组后的过滤。 用WHERE的地方可以用HAVING，HAVING可以使用聚合函数，而WHERE不可以使用聚合函数。 SELECT name FROM data GROUP BY name HAVING SUM(id) > 5;聚合函数COUNT(字段名)：统计行数SUM(字段名)：求和AVG(字段名)：求平均数MAX(字段名)：求最大值MIN(字段名)：求最小值LENGTH(字段名)：返回长度LIMIT与OFFSET 可用于分页，只取一部分。 LIMIT：只有一个数时，表示取几条数据；两个数(a, b)时，表示取 [a+1, a+b]  这个区间的数据 OFFSET：值跳过多少条数据SELECT * FROM Customer LIMIT 10;	--检索前10行数据，显示1-10条数据 SELECT * FROM Customer LIMIT 1,10;	--检索从第2行开始，累加10条id记录，共显示id为2....11 SELECT * FROM Customer LIMIT 10 OFFSET 1; --检索从第2行开始，累加10条id记录，共显示id为2....11 SELECT * FROM Customer LIMIT 5,10;	--检索从第6行开始向前加10条数据，共显示id为6,7....15
　　注意
　　当数据很大，上百万的时候，使用LIMIT ... OFFSET ..的方式进行分页十分浪费资源且耗时长。最好是结合WHERE使用，如： -- LIMIT  ... OFFSET .. SELECT * FROM data LIMIT 10 OFFSET 80000001; -- 10 rows in set (12.80 sec)  -- WHERE SELECT * FROM data WHERE id > 80000000 LIMIT 10; -- 10 rows in set (0.01 sec)
　　REGEXP 使用正则表达进行匹配。 查询时，需要搭配WHERE或HAVING使用 。 SELECT name FROM data GROUP BY name HAVING name REGEXP ＂f#39;; SELECT name FROM data WHERE name REGEXP ＂f#39; GROUP BY name;
　　多表查询内连接查询
　　两个表之间有交集且要用到两个表的数据时，可以使用内连接查询。
　　user表： +----+--------+ | id | name   | +----+--------+ |  1 | 小明   | |  2 | 小红   | |  3 | 小林   | +----+--------+ log表： +----+-------------+------+ | id | longinCount | uid  | +----+-------------+------+ |  1 |           2 |    3 | |  2 |          19 |    1 | |  3 |           5 |    2 | +----+-------------+------+-- 使用INNER JOIN ... ON ... -- 格式：SELECT * FROM <表1名> INNER JOIN <表2名> ON <表1名>.id = <表2名>.uid; -- 注：表1、表2的顺序可变 SELECT * FROM user INNER JOIN log ON user.id=log.uid;  -- 也可以直接查询 -- 格式：SELECT * FROM <表1名>, <表2名> WHERE <表1名>.id = <表2名>.uid; SELECT * FROM user, log WHERE user.id=log.uid;  /* +----+--------+----+-------------+------+ | id | name   | id | longinCount | uid  | +----+--------+----+-------------+------+ |  3 | 小林   |  1 |           2 |    3 | |  1 | 小明   |  2 |          19 |    1 | |  2 | 小红   |  3 |           5 |    2 | +----+--------+----+-------------+------+ */    -- 排序并使用别名 SELECT user.id AS UID, user.name AS Name, log.longinCount FROM user, log WHERE user.id=log.uid ORDER BY user.id;  /* +-----+--------+-------------+ | UID | Name   | longinCount | +-----+--------+-------------+ |   1 | 小明   |          19 | |   2 | 小红   |           5 | |   3 | 小林   |           2 | +-----+--------+-------------+ */外链接查询LEFT JOIN
　　LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
　　name1: +----+--------+------------+ | id | name   | loginCount | +----+--------+------------+ |  1 | 小明   |         23 | |  2 | 小红   |         12 | |  3 | 小刚   |         53 | |  4 | 小吕   |        123 | +----+--------+------------+  name2: +----+--------+-----------+ | id | name   | comment   | +----+--------+-----------+ |  1 | 小刚   | 直男       | |  2 | 小红   | 白富美     | |  3 | 小明   | 学霸       | +----+--------+-----------+
　　用法： /* SELECT * FROM <表1名> LEFT JOIN <表2名> ON <表1名>.<某字段> = <表2名>.<某字段>; */ -- 例子 SELECT * FROM name1 LEFT JOIN name2 ON name1.name=name2.name;  /* +----+--------+------------+------+--------+-----------+ | id | name   | loginCount | id   | name   | comment   | +----+--------+------------+------+--------+-----------+ |  3 | 小刚   |         53 |    1 | 小刚   | 直男        | |  2 | 小红   |         12 |    2 | 小红   | 白富美      | |  1 | 小明   |         23 |    3 | 小明   | 学霸        | |  4 | 小吕   |        123 | NULL | NULL   | NULL       | +----+--------+------------+------+--------+-----------+ */  -- 没有匹配到，就为NULL -- 美化一下 SELECT name1.id, name1.name, name2.comment FROM name1 LEFT JOiN name2 ON name1.name=name2.name ORDER BY name1.id;  /* +----+--------+-----------+ | id | name   | comment   | +----+--------+-----------+ |  1 | 小明   | 学霸       | |  2 | 小红   | 白富美     | |  3 | 小刚   | 直男       | |  4 | 小吕   | NULL      | +----+--------+-----------+ */ RIGHT JOIN
　　RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。 把LEFT JOIN的表1、表2调换顺序，就是REGHT JOIN 。
　　SELECT * FROM name2 right JOIN name1 ON name1.name=name2.name;  /* +------+--------+-----------+----+--------+------------+ | id   | name   | comment   | id | name   | loginCount | +------+--------+-----------+----+--------+------------+ |    1 | 小刚   | 直男      |  3 | 小刚   |         53 | |    2 | 小红   | 白富美    |  2 | 小红   |         12 | |    3 | 小明   | 学霸      |  1 | 小明   |         23 | | NULL | NULL   | NULL      |  4 | 小吕   |        123 | +------+--------+-----------+----+--------+------------+ */ FULL OUTER JOIN
　　FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行. 相当于结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
　　但 MySQL中不支持 FULL OUTER JOIN 。 SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;子查询
　　即SELECT嵌套。
　　IN 一个查询结果作为另一个查询的条件。 如： SELECT * FROM data WHERE name IN (SELECT name FROM user);
　　EXISTS 用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。True时执行。 如：  SELECT * FROM data WHERE EXISTS (SELECT name FROM user);索引
　　索引的本质是一种排好序的数据结构。利用索引可以提高查询速度。
　　常见的索引有： hash索引 Hash 索引是比较常见的一种索引，他的单条记录查询的效率很高，时间复杂度为1。但 Mysql Innodb引擎不支持 。
　　适合精确查找，不适合范围查找 ：存储引擎为每一行计算一个hash码让后放在hash索引中存储，相邻的数据hash值相差可能很大。 二叉树 二叉树的时间复杂度为 O(n)
　　一个节点只能有两个子节点。且左子节点 < 本节点 < 右子节点
　　数据量越多，遍历次数越多，IO次数就越多，就越慢。 B树 每个节点中不仅包含数据的 key 值，还有 data 值。
　　和二叉树一样，数据多时，导致B树很深，影响查询效率。 B+树  最常用的索引的数据结构。
　　B+树比较复杂，可以看此文：MySQL索引原理，一篇从头到尾讲清楚 添加索引创建时添加主键  CREATE TABLE test(id TINYINT PRIMARY KEY, name VARCHAR(20));  -- 或  CREATE TABLE test(id TINYINT, name VARCHAR(20), PRIMARY KEY (id));    /*  mysql> SHOW CREATE TABLE test;  +-------+------------------------------------------------------------------------------------------------------------------------------------------------+  | Table | Create Table                                                                                                                                   |  +-------+------------------------------------------------------------------------------------------------------------------------------------------------+  | test  | CREATE TABLE `test` (    `id` tinyint(4) NOT NULL,    `name` varchar(20) DEFAULT NULL,    PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |  +-------+------------------------------------------------------------------------------------------------------------------------------------------------+  1 row in set (0.00 sec)  */非主键 普通索引 -- 格式：  -- CREATE TABLE <表名> ( <字段> <类型>等 ... , INDEX|KEY [索引名] (<字段名> [长度] [ASC|GESC]));  -- | 表示或  CREATE TABLE test (name VARCHAR(20), INDEX n (name));   /*  mysql> SHOW CREATE TABLE test;  +-------+------------------------------------------------------------------------------------------------------------------+  | Table | Create Table                                                                                                     |  +-------+------------------------------------------------------------------------------------------------------------------+  | test  | CREATE TABLE `test` (    `name` varchar(20) DEFAULT NULL,    KEY `n` (`name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |  +-------+------------------------------------------------------------------------------------------------------------------+  1 row in set (0.00 sec)   */UNIQUE、FULLTEXT、SPATIAL索引 这三个分别对应唯一索引、全文索引、多列索引。 -- CREATE TABLE <表名> ( <字段> <类型>等 ... ,  -- UNIQUE | FULLTEXT | SPATIAL [ INDEX | KEY] [索引名] [索引类型] (<字段名>,…)   CREATE TABLE test(name VARCHAR(20), UNIQUE INDEX(name));   /*  mysql> show create table test;  +-------+----------------------------------------------------------------------------------------------------------------------------+  | Table | Create Table                                                                                                               |  +-------+----------------------------------------------------------------------------------------------------------------------------+  | test  | CREATE TABLE `test` (    `name` varchar(20) DEFAULT NULL,    UNIQUE KEY `name` (`name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |  +-------+----------------------------------------------------------------------------------------------------------------------------+  1 row in set (0.00 sec)  */创建后追加/* 原表 mysql> desc test; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id    | tinyint(4)  | YES  |     | NULL    |       | | name  | varchar(20) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+  */主键 ALTER TABLE test ADD PRIMARY KEY (id);  /* mysql> desc test; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id    | tinyint(4)  | NO   | PRI | NULL    |       | | name  | varchar(20) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+  */非主键索引普通索引 -- 格式和上面的一样 ALTER TABLE test ADD INDEX(name);UNIQUE、FULLTEXT、SPATIAL索引 -- 格式和上面的一样 ALTER TABLE test ADD UNIQUE INDEX(name);删除索引非主键索引 -- 格式： -- 		DROP INDEX <索引名> ON <表名>;   DROP INDEX name ON test;主键 -- 格式： -- 		ALTER TABLE <表名> DROP PRIMARY KEY;  ALTER TABLE test DROP PRIMARY KEY; 外键
　　MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 外键的使用条件： 两个表必须是InnoDB表 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引 外键关系的两个表的列必须是数据类型相似，如int和tinyint可以，但int和char则不可以
　　外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。 外键创建-- 表1字表，表2主表  CREATE TABLE <表1名称> ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), -- 主键也可在字段后面创建 FOREIGN KEY (P_Id) REFERENCES <表2名称>(<表2的ID>) )；-- user表 CREATE TABLE user( id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) );   -- data表 CREATE TABLE data( id TINYINT PRIMARY KEY AUTO_INCREMENT, loginCount TINYINT, uid TINYINT, FOREIGN KEY (uid) REFERENCES user(id) ); 增加
　　对已有的两个表增加外键 比如：主表为A，子表为B，外键为aid，外键约束名字为a_fk_b
　　为子表添加一个字段，当做外键 ALTER TABLE B ADD aid TINYINT;
　　为子表添加外键约束条件 -- 注意对应上 ALTER TABLE B ADD CONSTRAINT a_fk_b FOREIGN KEY(aid) REFERENCES A(id);删除删除外键约束条件  ALTER TABLE <表名> DROP FOREIGN KEY <外键约束的名字>;    ALTER TABLE data DROP FOREIGN KEY data_ibfk_1;外键约束的名字需要通过SHOW CREATE TABLE  命令查看外键 先删除外键约束条件，再删外键字段 第一步在上面
　　第二步使用命令：ALTER TABLE <表名> DROP <外键字段名>;  删除记录
　　假如删除记录报错： [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...)
　　这是因为MySQL中设置了foreign key关联，造成无法更新或删除数据。可以通过设置 FOREIGN_KEY_CHECKS  变量来避免这种情况。 第一步：禁用外键约束，我们可以使用：SETFOREIGN_KEY_CHECKS=0;   第二步：删除数据 第三步：启动外键约束，我们可以使用：SETFOREIGN_KEY_CHECKS=1;   查看当前FOREIGN_KEY_CHECKS的值，可用如下命令：SELECT @@FOREIGN_KEY_CHECKS;
　　组合查询#
　　即 UNION
　　使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。
　　每个查询必须包含相同的列、表达式和聚集函数。
　　默认会去除相同行，如果需要保留相同行，使用UNION ALL 。
　　只能包含一个 ORDER BY  子句，并且必须位于语句的最后。SELECT colFROM mytableWHERE col = 1UNIONSELECT colFROM mytableWHERE col =2; 内置函数#
　　内置函数很多, 见: MySQL 函数 事务#
　　我们一般使用 START TRANSACTION 或BEGIN 开启事务, COMMIT 提交事务中的命令, SAVEPOINT : 相当于设置一个还原点, ROLLBACK TO : 回滚到某个还原点下
　　一般的使用格式如下:START TRANSACTION-- ...SAVEPOINT delete1-- ...ROLLBACK TO delete1-- ...COMMIT锁#
　　开启事务时, 默认加锁
　　根据类型可分为共享锁（SHARED LOCK）和排他锁（EXCLUSIVE LOCK）或者叫读锁（READ LOCK）和写锁（WRITE LOCK）。
　　读锁（共享锁）：针对同一块数据，多个读操作可以同时进行而不会互相影响。由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获取排它锁，也就是说只能读不能写。
　　写锁（排它锁）：当当前写操作没有完成之前，它会阻断其他写锁和读锁。由写表操作加上的锁，加锁后其他用户不能获取该表或行的任何锁。
　　根据粒度划分又分表锁和行锁。表锁由数据库服务器实现，行锁由存储引擎实现。
　　除此之外,我们可以显示加锁 SELECT .... LOCK IN SHARE MODE; -- 加共享锁 SELECT .....FOR UPDATE; -- 加排他锁
　　加锁时,  如果没有索引，会锁表，如果加了索引，就会锁行 死锁#
　　InnoDB默认支持行锁，获取锁是分步的，并不是一次性获取所有的锁，因此在锁竞争的时候就会出现死锁的情况
　　解决方法: 首先要解决死锁问题，在程序的设计上，当发现程序有高并发的访问某一个表时，尽量对该表的执行操作串行化，或者锁升级，一次性获取所有的锁资源。 然后也可以设置参数 innodb_lock_wait_timeout ，超时时间，并且将参数innodb_deadlock_detect 打开，当发现死锁的时候，自动回滚其中的某一个事务。事务的基本特性#
　　即ACID特性: 原子性: 一个事务中的操作要么全部成功, 要么全部失败 一致性: 数据库总是从一个一致性的状态转换到另一个一致性状态, 比如A转账个B100块, 假如转账期间系统崩溃, A也不会损失100块, 因为事务还未提交, 不会保存到数据库中 隔离性: 一个事务在提交前, 对其他事务是不可见的 持久性: 一旦事务提交, 所做的修改就会永久保存到数据库中 事务并发引发的问题#丢失更新
　　事务A提交或撤销时, 事务B把数据覆盖了 脏读
　　事务A执行过程中, 修改了数据, 事务B读取了, 但事务A后来回滚了, 则事务B读取的数据为脏数据
　　即:  读取了另一个事务未提交数据的现象为脏读 不可重复读
　　事务B读取了两次数据, 但其间事务A修改了数据, 导致事务B两次读取数据不一致 幻读
　　事务B两次读取同一范围的数据, 但其间事务A新增了数据, 导致事务B后面读取的数据与前面读取的数据行数不一致
　　幻读强调集合增减, 不可重复读强调数据修改
　　隔离级别#
　　由于并发事务会引发上面这些问题, 我们可以设置事务的隔离级别解决上面的问题.
　　MySQL的默认隔离级别（可重复读）
　　查看当前会话隔离级别
　　方式1 SHOW VARIABLES LIKE ＂transaction_isolation＂; +-----------------------+--------------+| Variable_name  | Value |+-----------------------+--------------+| transaction_isolation | SERIALIZABLE |+-----------------------+--------------+
　　方式2 SELECT @@transaction_isolation;  +-------------------------+| @@transaction_isolation |+-------------------------+| SERIALIZABLE            |+-------------------------+
　　设置隔离级别 SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
　　GLOBAL 时, 只对执行完该语句之后产生的会话起作用, 当前已经存在的会话无效
　　SESSION 时, 对当前会话的所有后续的事务有效, 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务 如果在事务之间执行，则对后续的事务有效。
　　不指定时, 只对当前会话中下一个即将开启的事务有效, 下一个事务执行完后，后续事务将恢复到之前的隔离级别, 该语句不能在已经开启的事务中间执行，会报错的
　　level的四个值: REPEATABLE READ  READ COMMITTED  READ UNCOMMITTED  SERIALIZABLE 主从集群与读写分离#
　　主从集群的示意图如下:
　　主要涉及三个线程： binlog  线程、I/O  线程和 SQL  线程。binlog dump 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。 I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。 SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。
　　同步流程: MySQL变更数据时, 会记录到 Binlog 上主节点 binlog dump 线程监听, Binlog 是否改变, 假如改变MySQL将主节点的Binlog同步给从节点从节点的 I/O  线程接收Binlog 数据, 并写入到relay log 文件从节点的 SQL  线程读取replay log 文件, 并更新数据, 完成主从之间数据同步
　　由于MySQL主从集群只会从主节点同步到从节点, 不会反过来同步, 所以需要读写分离
　　读写分离需要在业务层面实现 , 写数据只能在主节点上完成, 而读数据可以在主节点或从节点上完成
　　除此之外, 还可以将Binlog数据同步给Redis Kafaka等中间件, 同步数据 索引#
　　索引是帮助MySQL高效获取数据的排好序的数据结构
　　MySQL的索引有 主键索引
　　建立在 PRIMARY KEY 上字段的索引称为主键索引, 值非空且唯一唯一索引
　　建立有 UNIQUE 的字段上的索引, 可以有多个唯一索引, 值可以为空且多个空值不会冲突普通索引
　　我们一般建立的 INDEX 前缀索引
　　对于一些字符或字节类型的数据, 可以取前几个字符或字节建立索引, 但是 不能 ORDER BY 和GROUP BY B树与B+树#
　　推荐两个在线工具: B树可视化工具: B-Trees B+树可视化工具: B+ Trees
　　简单来说, B树是在红黑树(一个平衡二叉树)的基础上将一个节点存放多个值, 实现的, 降低了树的高度, 每个节点都存放索引及对应数据指针, 同一层的节点是递增的
　　而B+树在B树的基础上进行优化, 非叶子节点存放 子节点的开始的索引, 叶子节点存放索引和数据的指针, 且叶子节点之间有双向的指针
　　如下示意图:
　　不同的引擎, 主键索引存放的数据也不一样, 比如常见的 MyISAM 和InnoDB
　　MyISAM 的B+树叶子节点存放表数据的指针, InnoDB 的B+树叶子节点存放处主键外的数据
　　其他的: InnoDB 支持事务, MyISAM 不支持事务InnoDB 支持外键, MyISAM 不支持InnoDB 支持聚集索引, MyISAM , (即上面说的)InnoDB 不保存表的行数, select count(*) ... 时, 全表扫描, 而MyISAM 保存了整个表的行数, 可以直接读取联合索引及最左前缀原则#
　　即多个列组成一个索引, 语法:  create index indexName on  tableName(column1,column2,...,columnN)
　　由于联合索引的B+树的结构, 根据列建立, 所以我们的查找条件也要根据索引列的顺序( where column1=x, column2=y,columnN... ), 否则会全表扫描索引失效的情况及原因#使用 != 或者<> 导致索引失效SELECT * FROM `user` WHERE `name` != ＂lczmx＂;我们给name字段建立了索引，但是如果!=  或者<> 这种都会导致索引失效，进行全表扫描，所以如果数据量大的话，谨慎使用类型不一致导致的索引失效
　　这涉及MySQL的类型转换,索引为整形, 而比较值为字符串时, 会将字符串转化为数字, 除了是数字字符串外, 其他的都为0 (＂1＂=>1, ＂a＂=>0)索引为字符串, 比较值为整形时, 会将索引装换为数字, 会破坏二叉树且不一定合法, 所以会走全表扫描函数导致的索引失效SELECT * FROM `user` WHERE DATE(create_time) = ＂2022-03-03＂;如果你的索引字段使用了索引，不走索引 实际上, 一般只要是对索引操作的, 都不会走索引 运算符导致的索引失效 SELECT * FROM `user` WHERE age - 1 = 20;
　　如果你对列进行了 （+，-，*，/，!） , 那么都将不会走索引。
　　实际上, 一般只要是对索引操作的, 都不会走索引
　　OR 引起的索引失效SELECT * FROM `user` WHERE `name` = ＂张三＂ OR height = ＂175＂;
　　OR 导致索引是在特定情况下的，并不是所有的OR 都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。模糊搜索导致的索引失效 SELECT * FROM `user` WHERE `name` LIKE ＂%mx＂;
　　这个我相信大家都明白，模糊搜索如果你前缀也进行模糊搜索，那么不会走索引。 NOT IN  NOT EXISTS 导致索引失效SELECT s.* FROM `user` s WHERE NOT EXISTS (SELECT * FROM `user` u WHERE u.name = s.`name` AND u.`name` = ＂lczmx＂)   SELECT * FROM `user` WHERE `name` NOT IN (＂lczmx＂);
　　这两种用法，也将使索引失效。另 IN 会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描, 见: MySQL中使用IN会不会走索引IS NULL 不走索引，IS NOT NULL 走索引SELECT * FROM `user` WHERE address IS NULL
　　不走索引。 SELECT * FROM `user` WHERE address IS NOT NULL;
　　走索引。
　　所以设计表的时候, 建议不可为空, 而是将默认值设置为 ＂＂ ( NOT NULL DEFAULT ＂＂ )
　　原文地址：https://www.cnblogs.com/lczmx/p/15997452.html