数据库，后端开发者必学，而且现在以MySQL居多。家下来我们将系统化MySQL一些高级用法。打算先讲很多人关注的使用方式（增删改查以及其优化），然后就讲数据库和表的操作（很多我们学习忽略的地方），接着就是引擎还有更高级的查询等等
首先我们用Navicat链接到我们的数据库，新建一个数据库，选中我们的数据库找到查询下的新建查询，创建两个数据表
CREATE TABLE `score` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `change_type` VARCHAR(255) NULL DEFAULT NULL, `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `score` INT(11) NULL DEFAULT NULL, `user_id` BIGINT(20) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci'; CREATE TABLE `gag` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `gag_time` DATE NULL DEFAULT NULL, `user_id` BIGINT(20) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci';有了这两张表 我们就可以来操作了
INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), …]
INSERT INTO score (change_type,score,user_id) VALUES ('吃饭',10,1);注意： 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。 可同时插入多条数据记录！
例子
INSERT INTO score (change_type,score,user_id) VALUES ('吃饭',10,1),('喝茶',10,1),('喝茶',10,1);（2）将进程/线程数控制在2倍于CPU数目相对合适 （3）采用顺序主键策略（例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键）
例子
DELETE FROM gag; 删除全部 DELETE FROM gag WHERE id=1;UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件] 例子：
UPDATE score SET change_type='洗澡' WHERE id=2;优化： 更新多条记录
Update score SET change_type = CASE id WHEN 1 THEN 'value1' WHEN 2 THEN 'value2' WHEN 3 THEN 'value3' END WHERE id IN (1,2,3)更新多条记录的多个值
Update score SET change_type = CASE id WHEN 1 THEN 'value1' WHEN 2 THEN 'value2' WHEN 3 THEN 'value3' END , score = CASE id WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 END WHERE id IN (1,2,3)(1). 尽量不要修改主键字段。 (2). 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。 (3). 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。 (4). 避免UPDATE将要复制到其他数据库的列。 (5). 避免UPDATE建有很多索引的列。 (6). 避免UPDATE在WHERE子句条件中的列。
（1）查看所有数据库以及使用数据库：
show databases; use table;（2）查看当前数据库
select database();查找优化-1mysql数据库引擎
本文档使用 看云 构建
数据库，后端开发者必学，而且现在以MySQL居多。家下来我们将系统化MySQL一些高级用法。打算先讲很多人关注的使用方式（增删改查以及其优化），然后就讲数据库和表的操作（很多我们学习忽略的地方），接着就是引擎还有更高级的查询等等
通常情况下，当访问某张表的时候，读取者首先必须获取该表的锁，如果有写入操作到达，那么写入者一直等待读取者完成操作（查询开始之后就不能中断，因此不允许读取者完成之前进行写操作）。当读取者完成对表的操作的时候，锁就会被解除。如果写入者正在等待的时候，另一个读取操作到达了，该读取操作也会被阻塞（block），因为默认的调度策略是写入者优先于读取者。当第一个读取者完成操作并解放锁后，写入者开始操作，并且直到该写入者完成操作，第二个读取者才开始操作。
通过LOCK TABLES和UNLOCK TABLES语句可以显式地获取或释放锁，但是在通常情况下，服务器的锁管理器会自动地在需要的时候获取锁，在不再需要的时候释放锁。获取的锁的类型依赖于客户端是写入还是读取操作。
对某张表进行写入操作的客户端必须拥有独占的（排他的）访问权的锁。操作在进行的过程中，该数据表处于不一致的（inconsistent）状态，因为数据记录在删除、添加或修改的时候，数据表上的索引也可能需要更新以相互匹配。允许两个客户端同时写入一张数据表是不利的，因为这样的操作会很快使数据表中的信息成为一堆无用的垃圾。同时允许客户端读取变化之中的数据表也不正确，因为正在读取的位置中的数据可能正在变化（修改），读取的结果可能并不是真实的。因此对某张表执行读取操作的客户端也必须获取一个锁，防止在读取的过程中，其它的客户端写入或改变表。但是这个锁不需要独占的访问权。因为读取操作不会改变数据，因此没有理由让某个读取者阻止其它的读取者访问这张表。所以读取锁可允许其它的客户端在同一时刻读取这张表。 虽然通过锁机制，可以实现多线程同时对某个表进行操作，但当某个线程作更新操作时，首先要获得独占的访问权。在更新的过程中，所有其它想要访问这个表的线程必须要等到其更新完成为止。此时就会导致锁竞争的问题，从而导致用户等待时间的延长。
因此：要提高MySQL的更新/插入效率，应首先考虑降低锁的竞争，减少写操作的等待时间。 （在后面才讨论表设计的优化）
INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), …]
注意： 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。 可同时插入多条数据记录！
例子：
INSERT INTO score (change_type,score,user_id) VALUES ('吃饭',10,1);优化： （1）当我们需要批量插入数据的时候，这样的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，然后才能够到达存储引擎做真的插入工作。上述所说的同时插入多条就是一种优化。（经测试，大概10条同时插入是最高效的）
例子：
INSERT INTO score (change_type,score,user_id) VALUES ('吃饭',10,1),('喝茶',10,1),('喝茶',10,1);（2）将进程/线程数控制在2倍于CPU数目相对合适 （3）采用顺序主键策略（例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键）
DELETE语句： DELETE FROM 表名[ 删除条件子句] 没有条件子句，则会删除全部（慎重）
例子：
DELETE FROM gag; 删除全部 DELETE FROM gag WHERE id=1;补充： Mysql中truncate table和delete语句都可以删除表里面所有数据，但是在一些情况下有些不同！
例子：truncate table gag;
（1）truncate table速度要更快一些，但truncate删除后不记录mysql日志，不可以恢复数据。
（2）如果没有外键关联，innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。
（3）表有外键关联，truncate table删除表数据为逐行删除， 如果外键指定级联删除(delete cascade)，关联的子表也会会被删除所有表数据。 如果外键未指定级联(cascde),truncate table逐行删除数据， 如果是父行关联子表行数据，将会报错。 （4）auto_increment计数器在truncate table后会重置为0.与是否有外键关联没有关系。
注意： 一个大的 DELETE 或 INSERT 操作，要非常小心，因为这两个操作是会锁表的，表一锁住，其他操作就进不来了。因此，我们要交给DBA去拆分，重整数据库策略，比如限制处理1000条。 MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以在超大型数据库中，删除时处理好索引关系非常重要。
推荐的折中方法： 在删除数据之前删除这几个索引，然后删除其中无用数据，删除完成后重新创建索引。
UPDATE语句UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件] 例子：
UPDATE score SET change_type='洗澡' WHERE id=2;优化： 更新多条记录
Update score SET change_type = CASE id WHEN 1 THEN 'value1' WHEN 2 THEN 'value2' WHEN 3 THEN 'value3' END WHERE id IN (1,2,3)更新多条记录的多个值
Update score SET change_type = CASE id WHEN 1 THEN 'value1' WHEN 2 THEN 'value2' WHEN 3 THEN 'value3' END , score = CASE id WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 END WHERE id IN (1,2,3)(1). 尽量不要修改主键字段。 (2). 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。 (3). 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。 (4). 避免UPDATE将要复制到其他数据库的列。 (5). 避免UPDATE建有很多索引的列。 (6). 避免UPDATE在WHERE子句条件中的列。
（1）查看所有数据库以及使用数据库：
show databases; use table;（2）查看当前数据库
select database();（3）显示当前时间、用户名、数据库版本
select now(), user(), version();（4）创建库
create database[ if not exists] 数据库名 数据库选项 数据库选项： CHARACTER SET charset_name COLLATE collation_name（5）查看当前库信息
show create database 数据库名（6）修改库的选项信息
alter database 库名 选项信息（7）删除库
本文档使用 看云 构建
数据库，后端开发者必学，而且现在以MySQL居多。家下来我们将系统化MySQL一些高级用法。打算先讲很多人关注的使用方式（增删改查以及其优化），然后就讲数据库和表的操作（很多我们学习忽略的地方），接着就是引擎还有更高级的查询等等
通常情况下，当访问某张表的时候，读取者首先必须获取该表的锁，如果有写入操作到达，那么写入者一直等待读取者完成操作（查询开始之后就不能中断，因此不允许读取者完成之前进行写操作）。当读取者完成对表的操作的时候，锁就会被解除。如果写入者正在等待的时候，另一个读取操作到达了，该读取操作也会被阻塞（block），因为默认的调度策略是写入者优先于读取者。当第一个读取者完成操作并解放锁后，写入者开始操作，并且直到该写入者完成操作，第二个读取者才开始操作。
通过LOCK TABLES和UNLOCK TABLES语句可以显式地获取或释放锁，但是在通常情况下，服务器的锁管理器会自动地在需要的时候获取锁，在不再需要的时候释放锁。获取的锁的类型依赖于客户端是写入还是读取操作。
对某张表进行写入操作的客户端必须拥有独占的（排他的）访问权的锁。操作在进行的过程中，该数据表处于不一致的（inconsistent）状态，因为数据记录在删除、添加或修改的时候，数据表上的索引也可能需要更新以相互匹配。允许两个客户端同时写入一张数据表是不利的，因为这样的操作会很快使数据表中的信息成为一堆无用的垃圾。同时允许客户端读取变化之中的数据表也不正确，因为正在读取的位置中的数据可能正在变化（修改），读取的结果可能并不是真实的。因此对某张表执行读取操作的客户端也必须获取一个锁，防止在读取的过程中，其它的客户端写入或改变表。但是这个锁不需要独占的访问权。因为读取操作不会改变数据，因此没有理由让某个读取者阻止其它的读取者访问这张表。所以读取锁可允许其它的客户端在同一时刻读取这张表。 虽然通过锁机制，可以实现多线程同时对某个表进行操作，但当某个线程作更新操作时，首先要获得独占的访问权。在更新的过程中，所有其它想要访问这个表的线程必须要等到其更新完成为止。此时就会导致锁竞争的问题，从而导致用户等待时间的延长。
因此：要提高MySQL的更新/插入效率，应首先考虑降低锁的竞争，减少写操作的等待时间。 （在后面才讨论表设计的优化）
INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), …]
注意： 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。 可同时插入多条数据记录！
例子：
INSERT INTO score (change_type,score,user_id) VALUES ('吃饭',10,1);优化： （1）当我们需要批量插入数据的时候，这样的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，然后才能够到达存储引擎做真的插入工作。上述所说的同时插入多条就是一种优化。（经测试，大概10条同时插入是最高效的）
例子：
INSERT INTO score (change_type,score,user_id) VALUES ('吃饭',10,1),('喝茶',10,1),('喝茶',10,1);（2）将进程/线程数控制在2倍于CPU数目相对合适 （3）采用顺序主键策略（例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键）
DELETE语句： DELETE FROM 表名[ 删除条件子句] 没有条件子句，则会删除全部（慎重）
例子：
DELETE FROM gag; 删除全部 DELETE FROM gag WHERE id=1;补充： Mysql中truncate table和delete语句都可以删除表里面所有数据，但是在一些情况下有些不同！
例子：truncate table gag;
（1）truncate table速度要更快一些，但truncate删除后不记录mysql日志，不可以恢复数据。
（2）如果没有外键关联，innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。
（3）表有外键关联，truncate table删除表数据为逐行删除， 如果外键指定级联删除(delete cascade)，关联的子表也会会被删除所有表数据。 如果外键未指定级联(cascde),truncate table逐行删除数据， 如果是父行关联子表行数据，将会报错。 （4）auto_increment计数器在truncate table后会重置为0.与是否有外键关联没有关系。
注意： 一个大的 DELETE 或 INSERT 操作，要非常小心，因为这两个操作是会锁表的，表一锁住，其他操作就进不来了。因此，我们要交给DBA去拆分，重整数据库策略，比如限制处理1000条。 MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以在超大型数据库中，删除时处理好索引关系非常重要。
推荐的折中方法： 在删除数据之前删除这几个索引，然后删除其中无用数据，删除完成后重新创建索引。
UPDATE语句UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件] 例子：
UPDATE score SET change_type='洗澡' WHERE id=2;优化： 更新多条记录
Update score SET change_type = CASE id WHEN 1 THEN 'value1' WHEN 2 THEN 'value2' WHEN 3 THEN 'value3' END WHERE id IN (1,2,3)更新多条记录的多个值
Update score SET change_type = CASE id WHEN 1 THEN 'value1' WHEN 2 THEN 'value2' WHEN 3 THEN 'value3' END , score = CASE id WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 END WHERE id IN (1,2,3)(1). 尽量不要修改主键字段。 (2). 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。 (3). 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。 (4). 避免UPDATE将要复制到其他数据库的列。 (5). 避免UPDATE建有很多索引的列。 (6). 避免UPDATE在WHERE子句条件中的列。
（1）查看所有数据库以及使用数据库：
show databases; use table;（2）查看当前数据库
select database();（3）显示当前时间、用户名、数据库版本
select now(), user(), version();（4）创建库
create database[ if not exists] 数据库名 数据库选项 数据库选项： CHARACTER SET charset_name COLLATE collation_name（5）查看当前库信息
show create database 数据库名（6）修改库的选项信息
alter database 库名 选项信息（7）删除库
drop database[ if exists] 数据库名 同时删除该数据库相关的目录及其目录内容
