查询数据
-
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（交集）语法，只能模拟，有以下两种办法：
-
DISTINCT + INNER JOIN
-
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
使用占位符执行语句。
-
避免注入
-
提高效率
表锁
LOCK TABLES table_name [READ | WRITE];
UNLOCK TABLES;
持有READ锁的会话只能读取数据不能写入数据，其他会话在释放READ锁之前无法将数据写入表中。来自另一个会话的写操作将被放入等待状态，直到释放READ锁；
如果会话正常或异常终止，MySQL将会隐式释放所有锁；
WRITE锁同理；
网友评论