第7章 高级SQL和MySQL

2018-03-03 14:48:08 《PHP与MySQL动态网站开发》笔记 阅读: 98 评论:

[TOCM]

联结

联结是将两个表或者多个表看为一个大表来使用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 JOINLEFT [OUTER] JOINRIGHT [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 ...]

分组函数

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

函数 功能
AVG 列中中所有数值的平均值
COUNT 列中所有值的个数
CROUP_CONCAT 列中所有值得联结
MAX 列中所有值的最大值
MIN 列中所有值得最小值
SUM 列中所有值的和

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子句

子句 用法 含义
ADD COLUMN ALTER TABLE t ADD COLUMN c TYPE 增加列到表尾
CHANGE COLUMN ALTER TABLE t CHANGE COLUMN c c TYPE 允许修改列类型和属性
DROP COLUM ALTER TABLE t DROP COLUM c 删除一列及这列数据
ADD INDEX ALTER TABLE t ADD INDEX i(c) 在c上设置索引
DROP INDEX ALTER TABLE t DROP INDEX i 删除索引
RENAME TO ALTER TABLE t RENAME TO new_t 修改表名

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)


功能
最新评论