联结

联结是将两个表或者多个表看为一个大表来使用SQL查询。

分类：

• 内联结: 在表中存在至少一个匹配时返回记录
• 外联结：返回两个表都匹配的记录和不匹配的记录。有三种类型：
  1. 左联结：以左表为主，返回左表的所有记录，右表中不匹配的记录返回NULL
  2. 右联结：与左联结相反，以右表为主，返回右表的所有记录，左表中不匹配的记录返回NULL
  3. 全联结：MySQL不支持。但可以使用左联结、右联结和UNION来实现。

语法：

SELECT `column1_name`[, `column2_name`, ...]
FROM `table1` [AS `t1`]
JOIN_TYPE `table2` [AS `t2`] [ON `t1`.`column_name` = `t2`.`column_name`]
[WHERE ...]
[ORDER BY ...]
[LIMIT ...]

外联结、左联结、右联结类型分别为：INNER JOIN、LEFT [OUTER] JOIN、RIGHT [OUTER] JOIN

比较中如果两个表具有相同的名称，可以使用USING代替ON：USING (column_name)

例子，使用内联结查询论坛数据库中某个用户最近10篇文章：

SELECT `u`.`user_id`, `u`.`user_name`, `a`.`title`, `a`.`date`, `a`.`content`
FROM  `users` AS `u`
INNER JOIN `articles` AS `a` USING(`user_id`)
WHERE `u`.`user_id` = 1
ORDER BY `a`.`date` DESC 
LIMIT 10

可以使用database.table.column跨库联结，数据库必须都在一个服务器上，执行用户需有权限访问各个表。

多联结

语法：

SELECT `column1_name`[, `column2_name`, ...]
FROM `table1` [AS `t1`]
JOIN_TYPE1 `table2` [AS `t2`] [ON ...]
JOIN_TYPE2 `table3` [AS `t3`] [ON ...]
...
[WHERE ...]
[ORDER BY ...]
[LIMIT ...]

分组函数

对列的多个行的进行操作的函数。

GROUP BY子句

GROUP BY 语句用于结合分组函数，根据一个或多个列对结果集进行分组。

例子，查询每个用户发表的文章数量：

SELECT `u`.`user_id`, `u`.`user_name`, COUNT( `a`.`user_id` ) AS `count`
FROM `articles` AS `a`
RIGHT JOIN `users` AS `u` USING (`user_id`) 
GROUP BY `user_id`
LIMIT 0 , 30

注意：GROUP BY会将NULL值分到同一组。

HAVING子句

WHERE关键字无法与分组函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。

例子，查询发表的文章数量为0的用户：

SELECT `u`.`user_id`, `u`.`user_name`, COUNT( `a`.`article_id` ) AS count
FROM `users` AS `u`
LEFT JOIN `articles` AS `a` USING (`user_id`) 
GROUP BY `user_id`
HAVING `count` = 0;

高级选择函数

CREATEST()函数：返回列表中的最大，语法：

SELECT GREATEST(`col1`, `col2`, ...) FROM `table`
SELECT GREATEST(100, 200, 300)

LEAST()函数：返回列表中的最小值，语法：

SELECT LEAST(`col1`, `col2`, ...) FROM `table`
SELECT LEAST(100, 200, 300)

COALESCE()函数：返回列表中的第一个非NULL值，语法：

SELECT COLALESCE(`col1`, `col2`, ...) FROM `table`

IF()函数：根据条件返回任意值，语法：

SELECT IF(condition, return_if_true, return_if_false) FROM `table`

condition 为真返回 return_if_true，否则返回 return_if_false

FNULL()函数，语法：

SELECT IFNULL(value, return_if_null)

value 为 NULL 返回 return_if_null

CASE()函数：类似php的switch多分支选择，语法：

SELECT CASE `col1`
WHEN value1 THEN case1
WHEN value2 THEN case2
...
END

这些函数也可以用在其他查询语句中。

ALTER语句

ALTER可以修改数据库、表、列、记录、索引。

基本语法：

ALTER TABLE table_name CLAUSE

ALTERTABLE子句

FULLTEXT查找

FULLTEXT用于多列查找，并且使用索引。而LIKE只能单列匹配。

创建FULLTEXT索引：

1. 确认表类型: SHOW TABLE STATUS\G
2. 将表修改为MyISAM类型: ALTER TABLE table ENGINE = MYISAM;
3. 为列增加FULLTEXT索引：ALTER TABLE table ADD FULLTEXT(col1, col2, …);

FULLTEXT查找语法:

SELECT * 
FROM `table`
WHERE MATCH(col1, col2, ...) AGAINST(text)

注意：MTACH中的列必须与建立的FULLTEXT索引列相同

查找规则：

• text会被分解成多个关键字（空格分割）
• 长度不足4的关键字被忽略
• 忽略停止词（stopword）
• 50%以上的记录匹配，则不返回记录
• 默认不区分大小写
• 布尔模式FULLTEXT查找：

SELECT * 
FROM `table`
WHERE MATCH(col1, col2, ...) AGAINST(text IN BOOLEAN MODE)

布尔模式运算符

布尔模式的规则：

1. 关键词没有运算符时表示这个单词可选
2. 50%以上的记录匹配时也会返回记录
3. 不会按相关性排序

查询优化

优化ALTER留下的间断：OPTIMIZE TABLE table;

优化索引：ANALYZE TABLE table;

数据库优化

• 选择最佳引擎
• 尽量为列使用最小的数据类型
• 尽可能定义NOT NULL的列
• 为主键使用整数
• 索引选择正确的类型应用到一列或多列
• 避免定义太多索引
• 如果可能的话限制索引为固定数目的字符
• 确保联结列的类型相同

解释查询

在SQL语句前面添加EXPLIN可以看到语句的解释过程

事务

数据库事务是指在单个会话期间运行的一系列查询。设置起点和终点，然后可以根据需要运行或者撤销所有的查询。

InnoDB表才能使用事务。

开始事务：START TRANSACTION;

执行事务查询：COMMIT;

撤销事务查询：ROLLBACK;

不能使用PHPMyAdmin执行事务

数据库加密

md5和sha1是不可逆加密，可逆方式可以使用AES:

加密函数：AES_ENCRYPT(str, key)

解密函数：AES_DECRYPT(str, key)