My SQL常用优化增改删

1. explain获取对应的执行计划

题目描述
获取select * from employees对应的执行计划

explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。
作用
1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询
原文链接：http***log.csdn.net/jiadajing267/article/details/81269067

2. concat、group_concat 组合

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

select CONCAT(last_name,' ',first_name) as Name
from employees; 

解题思路：①不同字符串连接方法不一样，在Mysql中，可以利用CONCAT()函数将多个字符串连接起来，中间用逗号隔开。②空格用’ '表示。

其他：

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

select dept_no,group_concat(emp_no separator ',') as employees
from dept_emp
group by dept_no;

①连接同一列字段：group_concat( [distinct] <要连接的字段> [order by 排序字段 asc/desc ] [separator ‘分隔符’] ) 。分隔符可以选择省略，省略时默认为逗号，这里还是写出来了。另外还有一点需要注意，group_concat函数中的各个参数之间用空格隔开，不能用逗号隔开，不然会出错。
②按照dept_no进行汇总，所以要对dept_no进行分组。

3、创建一个名为xx的表：

create table actor(
actor_id smallint(5) not null,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update timestamp not null  default (datetime('now','localtime')),
PRIMARY KEY(actor_id)
);

使用默认值为系统时间：DEFAULT (datetime(‘now’,‘localtime’)));

4、批量插入数据：

解题思路：插入数据语法：insert into <表名> values (<数据1>,<数据2>…);

insert into actor values
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');

5、insert、replace

insert into:插入数据,如果主键重复，则报错
insert repalce:插入替换数据,如果存在主键或unique数据则替换数据
insert ignore:如果存在数据,则忽略。

题目描述
对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作

INSERT OR IGNORE INTO actor VALUES (3, 'ED', 'CHASE', '2006-02-15 12:34:33');

6、根据题意，本题要用两条语句完成，先用 CREATE TABLE 语句创建actor_name表，包含first_name与last_name字段，然后用 INSERT INTO … SELECT … 语句向actor_name表插入另一张表中的数据，注意分号

CREATE TABLE actor_name
(
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL
);
INSERT INTO actor_name SELECT first_name, last_name FROM actor;

7、创建索引：

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

给指定表或者视图的某列添加索引使用语句：CREATE [UNIQUE/…] INDEX indexName ON tableName(colName);

//创建唯一索引
CREATE UNIQUE INDEX uniq_idx_firstname on actor(first_name);
//创建普通索引
CREATE INDEX idx_lastname on actor(last_name);

8、creat view 创建视图

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

注意 CREATE VIEW … AS … 的 AS 是创建视图语法中的一部分

CREATE VIEW actor_name_view (fist_name_v, last_name_v) AS
SELECT first_name, last_name FROM actor 

或者：

create view actor_name_view as
select first_name as first_name_v, last_name as last_name_v
from actor

9、强制索引查询：

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

MySQL中，使用 FORCE INDEX 语句进行强制索引查询，可参考：

http://www.jb51.net/article/49807.htm

SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005

10、列表中在xx列后新加一列：

用 ALTER TABLE … ADD … 语句可以向已存在的表插入新字段，并且能够与创建表时一样，在字段名和数据类型后加入NOT NULL、DEFAULT等限定，可参考：
http://www.runoob.com/sqlite/sqlite-alter-command.html

ALTER TABLE actor ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

11、exists :

IN：确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

EXISTS：指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

使用含有关键字exists查找未分配具体部门的员工的所有信息。

用 in :

SELECT * FROM employees WHERE NOT EXISTS 
(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)

用 select :

SELECT * FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_emp)

12、limit /offset

分页查询employees表，每5行一页，返回第2页的数据

根据题意，每行5页，返回第2页的数据，即返回第6~10条记录，以下有两种方法可以解决：

方法一：LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。（当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。）

SELECT * FROM employees LIMIT 5 OFFSET 5

方法二：：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。（当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量。）

SELECT * FROM employees LIMIT 5,5

13、update

Update 语句用于修改表中的数据。
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

题目：所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

另外要注意若干列 to_date = NULL 和 from_date = ‘2001-01-01’ 之间只能用逗号连接，切勿用 AND 连接。

UPDATE titles_test SET to_date = NULL, from_date = '2001-01-01'
WHERE to_date = '9999-01-01';

例2 ：

将所有获取奖金的员工当前的薪水增加10%。

update salaries set salary=salary*1.1 where emp_no in (
    select emp_no from emp_bonus
) and to_date='9999-01-01'

14、update replace :

题目：将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

replace(字段，“需要替换的值”，“替换后的值”）

update titles_test set emp_no = replace(emp_no,10001,10005) where id=5

15、更改表名rename :

alter table tname_old rename to tname_new

将titles_test表名修改为titles_2017。

alter table titles_test（表名） rename titles_2017

16、substr 截取字符段

substr(字符串，起始位置，长度）
起始位置:截取的子串的起始位置（注意:字符串的第一个字符的索引是1）。值为正时从字符串开始位置 开始计数，值为负时从字符串结尾位置开始计数。
长度:截取子串的长度

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

SELECT first_name FROM employees ORDER BY substr(first_name,-2) 

MySQL 直接利用right函数：

SELECT first_name FROM employees ORDER BY RIGHT(first_name ,2);

17、设置触发器

构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name
FOR EACH ROW
BIGIN
trigger_stmt
END

1、trigger_name标识触发器名称，用户自行指定

2、trigger_time标识触发时机，可以为before或after

3、trigger_event标识触发事件，包括INSERT、UPDATE和DELETE

4、tbl_name标识建立触发器的表名，即在哪张表上建立触发器

5、trigger_stmt是触发器内容，触发器程序可以使用begin和end作为开始和结束，中间包含多条语句

本题SQLITE和MYSQL通用：

CREATE TRIGGER audit_log AFTER INSERT ON employees_test
FOR EACH ROW
BEGIN
INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
END

18、delete

删除emp_no重复的记录，只保留最小的id对应的记录。

本题思路如下：先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id，然后用 DELETE FROM … WHERE … NOT IN … 语句删除 “非每个分组最小id对应的所有记录”

DELETE FROM titles_test WHERE id NOT IN 
(SELECT MIN(id) FROM titles_test GROUP BY emp_no)