2020-03-19

【灵光一现】学而时习之-SQL查询新姿势

本文用于记录做题中遇到的比较有意思的sql查询函数或知识点

1.每种数据库的字符串拼接有不同的用法比如sqlite用||，mysql用concat

2.在插入数据时，若该数据主键在表中已存在，可选择忽略本条数据的插入而不是替换

比如：

1 2	insert ignore into actor values(3,'ED','CHASE','2006-02-15 12:34:33');

3.查询条件区间使用between……and……

例如

SELECT  * FROM XXX_log WHERE (`ctime` BETWEEN '2017-09-11 09:34:13'  AND '2017-10-11 09:34:13')
and     id > 27851
AND column1 = 'xxx'
AND (column2 = 'null' OR LENGTH(column2) > 91)
ORDER BY  id DESC LIMIT 0, 30

4.强制索引：force index（）

mysql查询新姿势，其作用就是在查询过程中指定要走的索引，强制要走那个索引，提高查询效率
效果很明显，是个很不错的sql优化方案
据说在一个100w条数据实际生产中由查询时间3s优化到了0.2s
案例由3的代码改为：

SELECT  * FROM XXX_log force index(ctime) WHERE (`ctime` BETWEEN '2017-09-11 09:34:13'  AND '2017-10-11 09:34:13')
and     id > 27851
AND column1 = 'xxx'
AND (column2 = 'null' OR LENGTH(column2) > 91)
ORDER BY  id DESC LIMIT 0, 30

5.mysql的触发器

a.用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
b.触发器执行的内容写出 BEGIN与END 之间
c.可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录
案例：

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

6.substr()的使用，可以是 substr(X,Y,Z) 或 substr(X,Y)

X是要截取的字符串。Y是字符串的起始位置，Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。
举个例子：获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

解答：

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

7.group_concat(X,Y)的使用（查询新姿势）

用于求同一分组下对应字段的拼接（omoxinoyi~）
其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。
案例：按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

解答：