SQL基础

MySQL官方文档中提供了一套示例数据库 Employees, MySQL官方文档中说明详见 http://dev.mysql.com/doc/employee/en/employee.html。里面详细介绍了此数据库，并提供了下载地址和导入方法。

mysql测试数据库employees一些sql语句_数据库_wepe12的博客-CSDN博客

插入

INSERT INTO table_name ( field1, field2,...fieldN )
                    VALUES
                    ( value1, value2,...valueN ), // 插入多行
                    ( value1, value2,...valueN );

删除

DELETE FROM table_name [WHERE Clause]

更新

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

单表查询

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

子句: WHERE, HAVING, GROUP BY, ORDER BY, LIMIT

按照被执行的顺序: from、where、group by、having、select、order by、limit

WHERE: 是唯一一个是直接从磁盘获取数据的时候就开始判断的条件, 从磁盘取出一条记录, 开始进行where判断:判断的结果如果成立就保存到内存中, 如果失败则直接放弃
GROUP BY: 根据一个或多个列, 对结果集进行分组, 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
- 支持的聚集函数：SUM() 统计求和, COUNT() 统计分组后每一组有多少个记录, AVG() 统计平均值
- 示例: 可以GROUP BY多个字段: SELECT user_name, SUM(order_price) FROM ORDER_TAB GROUP BY user_name, order_data
- 示例: GROUP BY的回溯统计WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计: SELECT user_name, SUM(order_price) FROM ORDER_TAB GROUP BY user_name WITH ROLLUP, 返回的数据会多一行, 该行的SUM等于GROUP返回结果再做一次求和
HAVING: 对GROUP BY的结果进行条件筛选, HAVING子句一般跟在GROUP BY子句后面。在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
- HAVING与WHERE的区别: WHERE是直接从磁盘取数据, 查询出的数据放入内存, 然后可以用聚合函数+GROUP BY分组, 分组后的数据用HAVING再筛选
- 示例: SELECT user_name, SUM(order_price) FROM ORDERS_TAB GROUP BY user_name HAVING SUM(order_price)>100
ORDER BY: 排序
- 示例: SELECT order_id, order_price FROM ORDER_TAB ORDER BY order_data desc
LIMIT：限制
- 示例: SELECT * FROM table LIMIT 10 # 取出10条
- 示例: SELECT * FROM table LIMIT 95,-1; # 检索记录行 96-last.
- 示例: SELECT * FROM table LIMIT 5,10; # 返回第6-15行数据

列去重

DISTINCT 列名: 根据该列名, 在结果中去重
- SELECT distinct(task_id), task_name from task; –带有distinct的列必须在第一个
- 但是上面的语句要id和name都相同的情况下才能虑重, 所以用下面的方式: SELECT task_id, count(DISTINCT task_name) FROM table –与其他函数使用时候，没有位置限制

WHERE的比较

WHERE查询支持的条件:

逻辑运算符与或非: AND, OR, NOT
运算符: >, <, <=, >=, =, like, between ... and, in, not in

下面是例子:

NULL:
- SELECT * FROM 表名 WHERE 字段名 IS NULL;
- SELECT * FROM 表名 WHERE 字段名 IS NOT NULL;
LIKE:
- SELECT * FROM 表名 WHERE 字段名 LIKE '%COM' # %多个字符，_单个字符
IN：
- WHERE column_name IN (value1,value2,...)
<, >, >=, <=, 可以用于比较时间：
- select count(*) from sometable where datetimecolumn>='2010-03-01 00:00:00'
- select count(*) from sometable where datetimecolumn>=UNIX_TIMESTAMP('2010-03-01 00:00:00')

多表查询

多表查询包括: 隐性连接和显性连接,
WHERE子句中使用的连接语句，在数据库语言中被称为隐性连接。
JOIN……ON子句产生的连接称为显性连接。
WHERE 和INNER JOIN产生的连接关系，没有本质区别，结果也一样。
但是隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。

隐性连接: SELECT T1.ID, T1.COLA, T2.COL2 FROM TABLE1 AS T1, TABLE2 AS T2 WHERE T1.ID=T2.ID

JOIN

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

SELECT T1.ID, T1.COLA, T2.COL2
FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2
ON T1.ID=T2.ID

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

多次JOIN:

select collum from TABLE1
LEFT JOIN TABLE2 ON condition2
LEFT JOIN TABLE3 ON condition3

Join 语句的优化 @link:: [[MySQL-01-SQL语句-JOIN原理及优化]]

UNION

UNION：操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。UNION会去掉重复的行, 但UNION ALL不会。

select columnA from table1
UNION
select collumB from table2

columnA和columnB必须是同类型

子查询

用括号()括起来的一个完整查询语句相当于一个Table, 子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句，并且如果指定了它就必须放在主 SELECT 语句的最后。

子查询作为查询条件:

如果子查询返回单个结果, 可以用例如 =，>，<等, 与子查询比较 :

SELECT customerNumber, checkNumber, amount FROM payments
WHERE amount = (SELECT MAX(amount) FROM payments);

如果子查询返回多个结果, 可以用 IN和 NOT IN:

SELECT customerName FROM customers
WHERE customerNumber NOT IN (
    SELECT DISTINCT customerNumber FROM orders
);

FROM子句 + 子查询:

SELECT MAX(items), MIN(items), FLOOR(AVG(items))
FROM (
    SELECT orderNumber, COUNT(orderNumber) AS items FROM orderdetails
    GROUP BY orderNumber
) AS lineitems;

EXISTS 和 NOT EXISTS: 当子查询与 EXISTS或 NOT EXISTS运算符一起使用时，子查询返回一个布尔值为TRUE或FALSE的值。以下查询说明了与EXISTS运算符一起使用的子查询：
SELECT * FROM tb_students_info
WHERE EXISTS (
SELECT dept_name FROM tb_departments WHERE dept_id=1
)

使用Explain分析SQL执行

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上explain

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no='10001'
AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
AND from_date='1986-06-26';

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 59      | NULL |    7 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

➤ 关注的列: possible_keys, key, type, 其中 type列表示查询类型, 改列可能的值:

system: 表中只有一条数据. 这个类型是特殊的 const 类型.
const: 针对主键或唯一索引的等值查询, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
eq_ref: 此类型通常出现在多表的查询(例如join), 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询条件通常是 =, 查询效率较高.
ref: All rows with matching index values are read from this table for each combination of rows from the previous tables.
- 1) 使用了非唯一或非主键索引, 可能匹配到多行(比较eq_ref)
- 2) 使用到了最左前缀规则的查询.
- 3) 以上两条对多表查询也适用
index_merge: 表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话
range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据. index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到(覆盖索引), 而不需要扫描数据. 当是这种情况时, Extra 字段会显示 Using index.
all: 表示全表扫描, 这个类型的查询是性能最差的查询之一

➤ 性能排序: all < index < range ~ index_merge < ref < eq_ref < const < system

➤ @ref:

SQL优化

优化select: 优化查询也即”如何高效使用索引”→ [[MySQL-03索引-高效使用索引]]
优化count: → [[../49.Course/course.MySQL实战45讲]] 14节
- count的实现: 统计非null行个数,
- 推荐使用count(*), 最差的是count(非索引列)
优化limit: limit 10000 20
- 方法1: 记录上次id select * from film where id > LAST_CURSOR limit 10
优化order by: → [[../49.Course/course.MySQL实战45讲]] 第16节
- SELECT film_id,description FROM film ORDER BY title LIMIT 50,5
- Mysql执行这段语句, 会把符合条件的列放入 order buffer, 排序后再 limit, 如果表数据很多排序会很耗时, 优化思路就是减少排序规模(延迟关联):
SELECT film.film_id,film.description
FROM film INNER JOIN (
SELECT film_id FROM film ORDER BY title LIMIT 50,5
) AS tmp USING(film_id);
优化join: 外层表减小数据规模, 内层表尽量走索引 @link:: [[MySQL-01-SQL语句-JOIN原理及优化]]
优化union:
- 原理: 创建临时表, union左右语句符合条件的行, 逐行填充到临时表, 如果用的是union而不是union all, mysql还需要用distinct做唯一过滤
- 优化: 1 尽量使用union all, 如果一定需要去重, 建议在代码里做. 2 每个union子句里尽量用where/limit减小规模
优化in:
- 原理: in可以使用索引, 优化器是转化成了n*m种组合方式来进行查询，最终将返回值合并，有点类似union但是更高效
- 优化: @todo