MySQL笔记2——查询专题

子查询（Subquery）：

• 指出现在其他SQL语句内的SELECT子句。
• 子查询嵌套在查询内部，且必须始终出现在原括号内
• 子查询可以包含多个关键字或条件（如DISTINCT,GROUP BY,OREDR BY,LIMIT,函数等）
• 子查询外层查询可以是SELECT,INSERT,UPDATE,SET或DO
• 子查询的返回结果可以是标量、某行、某列甚至是其子查询

使用比较运算符的子查询

语法：operand comparison_operator subquery

//查找商品表中的平均价格
SELECT AVG(goods_price) FROM tdb_goods;

//对于平均值得到的结果进行四舍五入
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;

//将上边的两条语句合并：
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

当然，如果子查询返回多个结果的时候可以用ANY,SOME或ALL修饰比较运算符

语法：
operand comparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL(subquery)

原则：

.	ANY	SOME	ALL
>,>=	最小值	最小值	最大值
<,<=	最大值	最大值	最小值
=	任意值	任意值
<>,!=			任意值

SELEC goods_id,goods_price,goods_name FROM tdb_goods WHERE goods_price>ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');

使用[NOT] IN/EXISTS引发的子查询

语法：
operand comparisson_operator [NOT] IN (subquery)

//=ANY运算符与IN等效。
//!=ALL或<>ALL运算符与NOT IN等效

有什么用处

之前我们讲过，INSERT…SELECT语句可以进行语句插入，其特殊之处在于：可以嵌套子查询。

INSERT [INTO] tbl_name [(col_name,...)]
SELECT...   

eg:
INSERT tdb_goods_cates(cate_name) SELECT goods_cates FROM tdb_goods GROUP BY goods_cate; 

但是我们又需要引入一个全新的概念：

多表更新

语法：
table_reference
{[INNER |CROSS] JOIN |{LEFT |RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

eg:
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods cates
SET goods_cate = cate_id;

用来参照另外的表更新本表的记录

例子中的样例并不完整，之前还有两步：
1.创建一个索引表
2.通过INSERT SELECT将信息写入新表
3.多表更新
此操作旨在将原表中大量重复的数据利用新表中的标号代替，以此达到减少存储空间的目的

连接

table_reference
{[INNER |CROSS] JOIN |{LEFT |RIGHT} [OUTER] JOIN}   //内连接，左外连接，右外连接
table_reference
ONconditional_expr

数据表参照

table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias

数据表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名

tbl_subquery可以作为子查询使用在FRON子句中，这样的子查询必须为其赋予别名

INNER JOIN：仅显示符合连接条件的记录

• mysql中，JOIN,CROSS JOIN和INNER JOIN是等价的
• 通常使用ON设定连接条件，使用WHERE进行结果集记录的过滤

SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
//查找tdb_goods表和tdb_goods_cates表的内连接中商品id,商品名及品牌名

[OUTER] LEFT JOIN:左外连接

顾名思义，结果为左表的全部且符合右表条件的。若坐左表中的某些元组不符合右表条件则对应元组的相应属性显示为NULL

[OUTER] RIGHT JOIN:右外连接

多表连接

eg:

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G; 

无限级分类表设计

eg:

CREATE TABLE tdb_goods_typos(
    type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(20) NOT NULL,
    parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

自定义函数

CREATE FUNCTION function_name
RETURNS
{STRING |INTEGER |REAL |DECIMAL}
routine_body

函数体：

• 函数体由合法的SQL语句构成
• 函数体可以实简单的SELECT或INSERT语句
• 函数体如果未复合结构则使用BEGIN…END语句
• 复合结构可以包含声明，循环，控制结构

eg:

CREATE FUNCTION F1() RETURNS VARCHAR(30)

RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点：%i分：%s秒');

若为复合结构则必须要包含在BEGIN…END之中

删除自定义函数

DROP FUNCTION [IF EXISTS] function_name