子查询(Subquery)是指出现在其他SQL语句内的SELECT子句
例如：
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
其中SELECT * FROM t1，称为Outer Query/Outer Statement（外层查询）
SELECT col2 FROM t2，称为Subquery
子查询指嵌套在查询内部，且必须始终出现在圆括号内
子查询可以包含多个关键字和条件，如DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等
子查询的外层查询可以是：SELECT、INSERT、UPDATE、SET或DO
子查询可以返回标量、一行、一列或子查询
使用比较运算符的子查询
=、>、<、>=、<=、<>、!=、<=>
语法结构
operand comparison_operator subquery
SELECT AVG(goods_price) FROM tdb_goods;
#从名为tdb_goods的表中查询到的goods_price计算平均值
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
#对得到的平均值作四舍五入，并保留到小数点后2位
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=5636.36;
#查询哪些商品的价格大于平均价格5636.36
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);
#通过子查询，查询哪些商品的价格大于平均价格，平均价格进行四舍五入，保留2位小数
SELECT goods_price FROM tbd_goods WHERE goods_cate='超极本';
#查到分类为‘超极本’的价格
SELECT * FROM tdb_goods WHERE goods_cate = '超极本'；
#查询分类为超极本的所有记录
SELECT goods_id goods_name goods_price FROM tdb_goods WHERE goods_price > (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');
#通过子查询，查询哪些商品的价格比超极本更贵
#系统报错，提示我们子查询返回了多于一行，返回了3条记录，应该告诉系统，大于哪一个值
用ANY、SOME或ALL修饰的比较运算符
operand comparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL(subquery)
使用[NOT]IN的子查询
语法结构
operand comparison_operator [NOT]IN(subquery)
=ANY运算符与IN等效
!=ALL或<>ALL运符与NOT IN等效
使用[NOT] EXISTS的子查询
如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE
CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SAMLLINT UNISIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40) NOT NULL
);
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
#查询数据表中的所有分类
将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,...)]
SELECT ...
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
应该参照分类表去更新商品表，需要有多表更新
多表更新
UPDATA table_references
SET col_name1 = {expr1丨DEFAULT}
[,col_name2 = {expr2丨DEFAULT} ...
[WHERE where_condition]
连接类型
INNER JOIN，内连接
在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的
LEFT [OUTER] JOIN，左外连接
RIGHT [OUTER] JOIN，右外连接
语法结构
table_reference
{[INNER丨CROSS] JOIN丨{[LEFT丨RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate =cate_name
SET goods_cate =cate_id;
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement
SELECT brand_name FROM tdb_goods GROUP BY brand_name;
CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNISIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL,
)
SELECT brand_name FROM tdb_goods GROUP BY brand_name;
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name =b.brand_name;
SET g.brand_name =b.brand id;
ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SAMLLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
连接
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作
数据表参照
table_reference
tbl_name [[AS alias]丨table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name
或tbl_name alias_name赋予别名
table_subquery可以作为子查询使用在FROM子句中，
这样的子查询必须为其赋予别名
连接条件
使用ON关键字来设定连接条件，也可以使用WHERE来代替
通常使用ON关键字来设定连接条件
使用WHERE关键字进行结果集记录的过滤
内连接
仅显示符合连接条件的记录
左外连接
显示左表的全部记录及右表符合连接条件的记录
右外连接
显示右表的全部记录及左表符合连接条件的记录
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 = c.brand_idG;
外链接
A LEFT JOIN B join_condition
数据表B的结果集依赖数据表A
数据表A的结果集根据左连接条件依赖所有数据表（B表除外）
左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）
如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行
无限分类的数据表设计
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNISIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNISIGNED NOT NULL DEFAULT 0
);
INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('超极本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);
自身连接
同一个数据表对其自身进行连接
SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p
ON s.parent_id = p.type_id;
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types p LEFT JOIN
tdb_goods_types s ON s.parent_id = p.type_id;
多表删除
DELETE tbl_name [.*] [,tbl_name[.*]]...
FROM table_references
[WHERE where_condition]
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >=2;
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2) AS t2 ON t1.goods_name =t2.goods_name WHERE t1.goods_id > t2.goods_id;