mysql子查询连接_MySQL-子查询与连接

子查询

子查询是指出现在其他SQL语句内的SELECT子句。

例如：

select * from t1 where col1=(select col2 from t2);

其中select col2 from t2，称为子查询

子查询的外层查询可以是：select,insert,update,set或do。

使用比较运算符的子查询

=、>、=、<=、<>、!=、<=>

SELECT ROUND(AVG(goods_price),2) from tdb_goods;  #求平均值，结果四舍五入保留2位小数

SELECT * from tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) from tdb_goods);

用ANY、SOME或ALL修饰的比较运算符

SELECT * 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的子查询

如果子查询返回任何行，EXISTS将返回True;否则为False。

INSERT...SELECT

将查询结果写入数据表

insert [into] tal_name[(col_name,...)]

select...

INSERT INTO tdb_goods_cates(cate_name) SELECT goods_cate from tdb_goods GROUP BY goods_cate;

多表更新

update table_reference

set col_name1={expr1|DEFAULT}

[,col_name2={expr2|DEFAULT}]...

[where where_condition]

连接

等值连接

查询的结果为两个表匹配到的数据

方式一

select * from 表1,表2 where 表1.列=表2.列; ---笛卡尔积=两个表数据个数相乘

方式二(又称内连接)

select * from 表1 inner join 表2 on 表1.列=表2.列; ---不会出现笛卡尔积，效率高

MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。

语法结构

table_reference

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

table_reference

ON conditional_expr

连接类型

INNER JOIN，内连接

在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。

LETF [OUTER] JOIN，左外连接

RIGHT [OUTER] JOIN，右外连接

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name SET goods_cate=cate_id;

CREATE...SELECT

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

CREATE TABLE [IF NOT EXISTS] tal_name

[(create_definition,...)]

select_statement

CREATE TABLE tdb_goods_brands(

brand_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

brand_name VARCHAR(40) not NULL

)SELECT brand_name from tdb_goods GROUP BY brand_name;

UPDATE tdb_goods g INNER JOIN tdb_goods_brands b ON g.brand_name=b.brand_name SET g.brand_name=b.brand_id;

ALTER TABLE tdb_goods

CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,

CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

连接条件

使用ON关键字来设定连接条件，也可以使用WHERE来代替。

通常使用ON关键字来设定连接条件，

使用WHERE关键字进行结果集记录的过滤。

内连接：显示左表及右表符合连接条件的记录

select * from 表1 left join 表2 on 表1.列=表2.列;

左外连接：显示左表的全部记录及右表符合连接条件的记录

select * from 表1 right join 表2 on 表1.列=表2.列;

SELECT g.goods_id,g.goods_name,g.cate_id,c.cate_id from tdb_goods g LEFT JOIN tdb_goods_cates c ON g.cate_id=c.cate_id;

右外连接：显示右表的全部记录及左表符合连接条件的记录

SELECT g.goods_id,g.goods_name,g.cate_id,c.cate_id from tdb_goods g RIGHT JOIN tdb_goods_cates c ON g.cate_id=c.cate_id;

多张表连接

SELECT g.goods_id,g.cate_id,c.cate_id from tdb_goods g

INNER JOIN tdb_goods_cates c ON g.cate_id=c.cate_id

INNER JOIN tdb_goods_brands b ON g.brand_id=b.brand_id;

外连接

A LEFT JOIN B join_condition

数据表B的结果集依赖数据表A。

数据表A的结果集根据左连接条件依赖所有数据表(B表除外)。

左外连接条件决定如何检索数据表B(在没有知道where条件的情况下)。

如果数据表A的某条记录符合where条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外B行。

如果使用内连接查找的记录在连接数据表中不存在，并且在where子句中尝试以下操作：col_name IS NULL时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接条件的记录后停止搜索更多的行。

自身连接

同一个数据表对其自身进行连接。

SELECT s.type_id,s.type_name,p.type_name from tdb_goods_types s

LEFT JOIN tdb_goods_types p ON s.parent_id=p.type_id;

SELECT p.type_name,count(s.type_name) from tdb_goods_types p

LEFT JOIN tdb_goods_types s ON s.parent_id=p.type_id GROUP BY p.type_name;

查询河南省的所有区县：

select a.atitle,b.atitle,c.atitle from areas a

left join areas b on a.aid=b.pid

left join areas c on b.aid=c.pid

WHERE a.atitle="河南省";