SQL语句总结

不同的数据库，sql的支持与要求就会不一样，本次就牛客的sql测试，总结遇到的一些不同。

一、HAVING的使用

由于where后面不能跟sql内置的函数，所以当需要的时候，就得使用having。

例子：

查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

select emp_no,COUNT(*) as t FROM `salaries` GROUP BY emp_no HAVING t>15

二、嵌套查询

当需要的条件简历在另一个已经查询的条件的结果集基础之上，就需要嵌套查询（嵌套的子查询会将结果保存在一个临时表里，占用内存）

例子：

获取所有非manager的员工emp_no
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
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`));

select employees.emp_no from employees
where employees.emp_no not in(
    select emp_no from dept_manager
)

三、COUNT(DISTINCT xxx)

这句可查询一个字段里不同数据的数量

例子：

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

select title, count(distinct emp_no) as t
from titles
group by title
having t >= 2

四、字段的拼接 

将两个字段字符串拼接成一个字符串，例子：

将employees表的所有员工的last_name和first_name拼接起来作为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`));

注意：以mysql为例子，mysql支持concat（str1，str2，。。。），concat_ws('分隔符'，str1，str2，。。。)

由于有些语言的sql会不一样。所以会有不一样的写法

SELECT CONCAT_WS(' ',employees.last_name,employees.first_name) AS Name FROM employees

 

五、创建默认时间字段

CREATE TABLE ssss(`now_date` TIMESTAMP not null DEFAULT now() )

六、忽略不替换插入

批量插入数据,如果数据已经存在，请忽略，不使用replace操作

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

七、创建索引 

例子：

针对如下表actor结构创建索引：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

1.

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

2.

ALTER TABLE actor ADD INDEX uniq_idx_firstname (first_name)

ALTER TABLE actor ADD UNIQUE idx_lastname (last_list)

删除索引

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

查看索引

show index from tblname;

show keys from tblname;

 八、创建视图

视图的定义和作用可以参考这篇博客

例子：

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

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

九、强制索引查询

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

例子：

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
create index idx_emp_no on salaries(emp_no);

SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005

 十、触发器

这篇文章说的非常好：https://blog.csdn.net/qq_36396104/article/details/80469997

十一、修改表名

例子：

将titles_test表名修改为titles_2017。

alter table titles_test rename to titles_2017

 十二、创建外键

简单概述一下外键吧：

外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。（acid的重要的一部分）

外键约束用于当有多个表存在级联关系的情况下，修改一张表的键值即可完成所有级联表的相关键的约束的操作，

们在更新与删除时遇到的外键约束解决方案分别对应设置Update rule与Delete rule。有如下四个选项：

1.CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行。 
2.SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。 
3.RESTRICT：拒绝对父表的删除或更新操作。 
4.NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同。

补充
在阿里的Java开发手册时，他们对于外键与级联是这样描述的：

【强制】不得使用外键与级联,一切外键概念必须在应用层解决。

说明:以学生和成绩的关系为例,学生表中的 student _ id 是主键,那么成绩表中的 student _ id则为外键。如果更新学生表中的 student _ id ,同时触发成绩表中的 student _ id 更新,即为级联更新。外键与级联更新适用于单机低并发,不适合分布式、高并发集群 ; 级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。

外键约束、级联更新与删除对于开发者是非常有用的，它确保了数据删除与更新的完整性。至于阿里所说的影响性能，适用于高并发、多用户的场景下，所以开发时应仔细斟酌。

例子：

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID)
);

 

十三、explain 查询执行计划

首先，先看一下例子：

上面我explain一个嵌套查询查看执行计划，出现了途中下方的结果图，接下来一个字段一个字段地看。

首先 id ，它有下面两个规则：

1.id相同，语句执行顺序由上到下

2.id不同（说明有子查询），id谁大先执行谁

select_types的操作方式，有如下几种类型

(1) SIMPLE(简单SELECT，不使用UNION或子查询等)

(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table操作的是哪张表

type

比较：system>const>eq_ref>ref>range>index>all（从左到右性能依次递减）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值（多条记录符合条件）

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件（一条记录符合条件）

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

Key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好 

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

 估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集进行排序、分组，常见于排序和分组查询，常见 group by ; order by

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”（mysql自己内部进行排序）

Using index

调优：

sql符合最佳左前缀法则

推荐：索引列上少计算，范围之后的索引全失效，经量不要用*，like时range查询，尽量右边写%，‘%字符串%’会覆盖索引

group by基本上都要进行排序，产生临时表

实例

两张表

1)资产&利润

2)市值

开始嵌套查询

要求：1）与2）inner join，条件：证券代码=股票代码；会计期间=截止日期

SELECT * FROM `资产&利润` INNER JOIN `市值` ON `资产&利润`.`证券代码'`=`市值`.`股票代码'` AND `资产&利润`.`会计期间'`=`市值`.`截止日期'`

SELECT a.`证券代码'`,a.`会计期间'`,a.`流动资产合计'`,a.`资产总计'`,a.`流动负债合计'`,a.`负债合计'`,a.`盈余公积'`,a.`未分配利润'`,a.`营业总收入'`,a.`营业收入'`,a.`利息支出'`,a.`所得税费用'`,a.`净利润'`,b.`股票代码'`,b.`截止日期'`,b.`市值A'`
FROM `资产&利润` as a INNER JOIN `市值` as b ON a.`证券代码'`=b.`股票代码'` AND a.`会计期间'`=b.`截止日期'`

explain分析

添加索引：

CREATE INDEX a_a on `资产&利润`(`证券代码'`,`会计期间'`);
CREATE INDEX b_b on `市值`(`股票代码'`,`截止日期'`);

show INDEX from `资产&利润`;
show INDEX from `市值`;

查询结果

1）select *

2)非select *

3）EXPLAIN分析