MySQL学习笔记（5）

子查询Subquery

出现在其他sql语句内的select子句。

子查询的外层查询可以是：SELECT,INSERT,UPDATE,SET或DO。

子查询可以返回标量，一行，一列或子查询。

①使用比较运算符的子查询

operand comparison_operator subquery （←子查询返回一条）

operand comparison_operator ANY (subquery)

operand comparison_operator SOME (subquery) （←any和some等价 符合其中一个就行）

operand comparison_operator ALL (subquery) （←符合全部）

eg1.SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

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

②[NOT] IN的子查询

operand comparison_operator [NOT] IN (subquery)

=ANY 运算符与IN等效

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

③[NOT] EXISTS的子查询（←使用较少）

---

 

多表更新（参照另外的表来更新本表）

①UPDATE table_references SET col_name1={expr1 | DEFAULT}

[,col_name2 = {expr2 | DEFAULT}] …

[WHERE where_condition]

table_references :表的参照关系

eg.UPDATE tbd_goods INNER JOIN tbd_goods_cates ON goods_cate = cate_name

SET goods_cate = cate_id;

②创建数据表同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,…)] select_statement

eg1.CREATE TABLE tbd_goods_brands(

brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

brand_name VARCHAR(40) NOT NULL)

SELECT brand_name FROM tdb_goods GROUP BY brand_name;

eg2.UPDATE tbd_goods AS g INNER JOIN tbd_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name =b.brand_id;

③修改表的数据类型和名字

ALTER TABLE tbd_goods

CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,

CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

---

 

连接：表的参照关系（事实的外键）

table_reference

{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}

table_reference

ON conditional_expr

conditional_expr （连接条件）

连接类型：

INNER JOIN 内连接：MySQL中 JOIN,INNER JOIN 和 CROSS JOIN等价

LEFT [OUTER] JOIN 左外连接

RIGHT[OUTER] JOIN 右外连接

数据表参照

table_reference

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

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

连接条件

使用ON设定连接条件，用WHERE进行结果集记录的过滤

eg.tdb_goods 结构

tdb_goods_cates结构

 

内连接INNER JOIN （显示交集）

eg.SELECT goods_id,goods_name,cate_name FROM tdb_goods AS g INNER JOIN tdb_goods_cates AS b ON g.cates_id = b.cates_id; 

左外连接 LEFT [OUTER] JOIN

在没有指定WHERE条件下，左外连接条件决定如何检索数据表B

如果A表中某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，则结果中会出现一条空记录的B行

右外连接 RIGHT [OUTER] JOIN

 

 多表连接

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;

连了三张表，输出品牌名和类型名等等

---

豆知识

使用SET NAME 编码; 来避免查询时乱码的发生