子查询与连接

子查询(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_id\G;

 

外链接

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;