查询数据

• SELECT

• SELECT DISTINCT

去重查询

基础用法：

SELECT DISTINCT lastname FROM employees ORDER BY lastname;
列中有NULL值：只保留一个NULL值；

 在多个列上使用：使用所有列的组合来确定结果集中行的唯一性（按组合唯一去重）；

 和GROUP BY的比较：DISTINCT是GROUP BY的特殊情况，GROUP BY效率高于DISTINCT

 查询非重复项的所有列：

SELECT *,count(DISTINCT lastname) FROM employees;
可以在列上使用count(),sum()等聚合函数；

 可以搭配order by、limit使用；

过滤数据

• WHERE

使用=,!=,<>,>,<,>=,<=,between,like,in,is null等条件过滤数据

• AND

• OR

AND和OR的短路求值功能：遇到可以确定的结果时，忽略后面的表达式，例如：

SELECT 1 = 0 AND 1 / 0 ;
SELECT 1 = 1 OR 1 / 0;

• IN/NOT IN

• BETWEEN/NOT BETWEEN

between包含边界，即>=,<=，not between不包含边界，即<,>

• LIKE

通配符：%（百分号）匹配零个或多个字符，_（下划线）匹配单个字符

\（反斜杠）转义%和_

• LIMIT

• IS NULL/IS NOT NULL

  使用is null可以使查询命中索引。（普通的=条件在有空值时索引会失效）
  

数据排序

ORDER BY

• 普通列排序（略）

• 表达式排序：

SELECT a*b as total FROM table_1 ORDER BY total DESC;

• 自定义排序顺序：FIELD()函数

SELECT 
    orderNumber, statusFROM
    ordersORDER BY FIELD(status,
        'In Process',
        'On Hold',
        'Cancelled',
        'Resolved',
        'Disputed',
        'Shipped');

连接表

• AS

列别名：

SELECT [column_1 | expression] AS descriptive_nameFROM table_name;

可以在ORDER BY,GROUP BY,HAVING子句中使用别名（WHERE中不行）

表别名：一般用在连接中

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

三种连接的区别：

        left join返回包括左表中的所有记录和右表中联结字段相等的记录，右表中不存在的记录字段填null

        right join返回包括右表中的所有记录和左表中联结字段相等的记录，左表中不存在的记录字段填null

        Inner join只返回两个表中联结字段相等的行，不满足条件的行不展示

• 自连接

通过inner join或left join把一个表连接到自身（必须使用别名）

分组数据

• GROUP BY

  多个字段分组：根据多个字段的唯一组合分组

  与聚合函数组合使用：计算每一个分组的相关信息

SELECT status, COUNT(*) AS total_number FROM orders GROUP BY status;   //计算每个状态下订单数量
SELECT status, SUM(quantityOrdered * priceEach) AS amount FROM
    orders INNER JOIN
    orderdetails USING (orderNumber) GROUP BY status;                       //从别的表里获取订单详情，再求得该组订单的总金额
SELECT YEAR(orderDate) AS year,
    SUM(quantityOrdered * priceEach) AS total
    FROM orders INNER JOIN
    orderdetails USING (orderNumber)
    WHERE status = 'Shipped'GROUP BY year
    HAVING year > 2013;                                                           //和HAVING子句组合使用

可以对分组进行排序，默认升序，降序：GROUP BY status DESC

• HAVING

和GROUP BY组合使用，过滤每组数据；

和WHERE的区别：WHERE过滤每行数据

子查询，派生表，通用表达式

FROM子句中的子查询：把查询出来的结果集当做一个派生表，从派生表中查询数据；

WHERE条件中的子查询：把查询出来的结果集当做WHERE条件使用。单个结果用>,<,=等条件，多个结果用in条件

通用表达式：可重复使用，可递归

WITH cte_name AS (
    query)   //此处为查询语句
 SELECT * FROM cte_name;

UNION,UNION ALL,模拟INTERSECT

UNION,UNION ALL取两个查询结果集的并集，区别是UNION去重，UNION ALL不去重

mysql不支持INTERSECT（交集）语法，只能模拟，有以下两种办法：

1. DISTINCT + INNER JOIN

2. IN + 子查询

修改数据

• INSERT

INSERT INTO table_1SELECT c1, c2, FROM table_2;    //从table_2复制数据到table_1
INSERT INTO tasks(task_id,subject,start_date,end_date,description)
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority')
ON DUPLICATE KEY UPDATE //主键重复时插入，更新原来的值或者插入新行
task_id = task_id + 1, 
subject = 'Test ON DUPLICATE KEY UPDATE';

• INSERT IGNORE 插入时忽略错误的行

• UPDATE

• UPDATE JOIN

连表更新：

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
    WHERE condition
上面的查询等价于:
UPDATE T1, T2SET T1.c2 = T2.c2,
      T2.c3 = expr WHERE T1.c1 = T2.c1 AND conditio

• DELETE

使用LIMIT删除有限条数据。配合ORDER BY使用。

• ON DELETE CASCADE

在外键约束定义的末尾加上这个子句，父表数据删除时会自动删除子表的相关数据

• DELETE JOIN

注意inner join和left join的区别

• REPLACE

有则update，没有则insert

• PREPARE

使用占位符执行语句。

1. 避免注入

2. 提高效率

表锁

LOCK TABLES table_name [READ | WRITE];
UNLOCK TABLES;

持有READ锁的会话只能读取数据不能写入数据，其他会话在释放READ锁之前无法将数据写入表中。来自另一个会话的写操作将被放入等待状态，直到释放READ锁；

如果会话正常或异常终止，MySQL将会隐式释放所有锁；

WRITE锁同理；