MySQL的子查询和连接知识

MySQL子查询与连接

子查询：

1.子查询是指出现在【其他SQL语句内】的SELECT子句

eg：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

其中，SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement)

SELECT column1 FROM t2 称为Sub Query[子查询]

ps:Outer Query[外查询]并不单单指SELECT等查询语句,而是所有的SQL语句,当一个SQL语句包含了子查询,则包含子查询的语句就叫外查询

2子查询的条件：

     1）始终出现在圆括号内；

     2）可以包含多个关键字或者条件；如 distinct、group by、order by、limit、函数等  （改为大写）

     3）子查询的外查询可以是:【SELECT,INSERT,UPDATE,SET或DO】。SQL语句的统称

3.子查询的返回值

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

 

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

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

operand comparision_operator subquery

当子查询返回多个结果时，可用ANY、SOME、ALL关键字来修饰子查询。

 

聚合函数：

特点 只有一个返回值

AVG(col_name) 返回col_name下数据的平均值

eg. SELECT AVG(goods_price) FROM tdb_goods;

WHERE语句中不能使用聚合函数，想使用，需要通过SELECT子查询。

ROUND(num,long) 对num保留long位小数

eg. SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;

 

使用[NOT] IN的子查询：

operand comparison_operator [NOT] IN (sunquery);

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

 

使用[NOT] EXISTS的子查询（用得少）：

如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE。

 

INSERT [INTO] tbl_name SET col_name={exprDEFAULT},...//可以使用子查询

 

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...//将查询结果写入数据表

 

Eg：INSERT INTO table_name [(column_name)] SELECT column_name2 FROM table_name2 GROUP BY column_name3;、

 

desc tablename和show columns from tablename有啥区别？

desc 表示 description 也就是 对表的描述，这个命令会用一个表格描述一个数据表，而你的数据表的特征就是数据表的列名，所以用来描述的表格里面也会显示列名。

show columns from table 就是一段最简单的直译命令，就是说显示表的所有列名，

它们代表的含义是完全不同的，但是显示结果是相同的。

 

多表更新

UPDATE table_references

SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}]...

[WHERE where_condition]

 

连接：

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

连接类型：

INNER JOIN,内连接

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

LEFT [OUTER] JOIN ,左外连接

RIGHT [OUTER] JOIN,右外连接

连接条件：

conditional_expr

例如：UPDATE 表1 INNER JOIN 表2 ON 列1 = 列2（两者属性完全相同）SET 列1 = 列3（设置值）。

举例：

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

商品表的goods_cate 与新表的cate_name 等价，把商品表的goods_cate改成新表的cate_id

 

多表更新之一步到位

 

1.CREATE SELECT

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

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

 

 CREATE TABLE tdb_goods_brands

 (

 brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

 brand_name VARCHAR(40) NOT NULL

 )

 SELECT brand_name FROM tdb_goods GOURP BY brand_name;

 

2.两个表建立连接 多表更新

根据新建的表tdb_goods_brands修改商品表tdb_goods，把商品表中的brand_name改成tdb_goods_brands中的brand_id

 

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//两个表都有brand_name,要用 AS关键词给表起别名

 

3.但是商品表tdb_goods中仍为 goods_cate VARCHAR, brand_name VARCHAR。我们要把goods_cate 改为 cate_id,把brand_name 改为 brand_id,并把数据类型改为SAMLLINT。既修改列名称又修改数据类型用CHANGE关键字

 

ALTER TABLE tdb_goods

CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,

CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

 

4.不一定要用物理的外键即FOREIGN KEY 建立联系，也可以使用这种方法 称为事实外键。实际上物理外键使用得少

 

连接

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

语法结构

table_reference(表1）

{[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER]JOIN}（链接类型）

table_reference（表2）

ON conditional_expr（连接条件）

 

数据表参照

table_reference

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

数据表可以使用tabl_name AS alias_name

或tbl_naem alias_name 赋予别名

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

 

1.内连接：

  在MySQL中JOIN,INNER JOIN,CROSS JOIN是等价的

  交集  仅显示A、B两表符合连接条件的记录。不符合连接条件的记录不显示。

 

  例如：SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN  tdb_goods_cate ON   tdb_goods.cate_id = tdb_goods_cate.cate_id;

 

2.外连接：

  LEFT JOIN左外连接；RIGHT JOIN右外连接

 

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

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

 

如果使用LEFT JOIN,左表中存在一条记录A,在右表中没有找到相应的记录,则在返回结果用会出现一条只有记录A中的相应字段内容,其他字段都为NULL在记录(RIGHT JOIN类似).

 

内连接比外链接用的多一些

3.连接条件：

  使用ON设定连接条件，也可以用WHERE代替

  一般情况下使用 ON：设定连接条件  WHERE：进行结果集记录的过滤

1、LEFT JOIN：显示左表全部和左右符合连接条件的记录

2、RIGHT JOIN：显示左右符合连接条件的记录和右表全部记录

3、如果使用LEFT JOIN,左表中存在一条记录A,在右表中没有找到相应的记录,则在返回结果用会出现一条只有记录A中的相应字段内容,其他字段都为NULL在记录(RIGHT JOIN类似).

 

多表连接

查询表达式—多表连接

SELECT col_name1,col_name2,col_name3,col_name4,col_name5 FROM tbl_name1 AS t1

INNER JOIN tbl_name2 AS t2 ON join_condition

INNER JOIN tbl_name3 AS t3 ON join_condition\G;

 

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;

显示的结果和商品表tdb_goods的记录一样，不过之前是通过单表的查询来显示，这次是通过三张表的连接来显示。

多表的连接实际上是外键的逆向约束。外键把数据分开存储，多表连接又把数据联系在一起。

 

关于连接到几点说明

 

一.外连接：

以左外连接为例：

A LEFT JOIN B join_condition

1.数据表B的结果集依赖于数据表A :数据表A中的记录在B表中显示出来，否则B表中的记录不能显示

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

3.左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)

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

二.外连接冲突：

使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试一下操作：column_name IS NULL 。如果 column_name 被指定为 NOT NULL，MySQL将在找到符合连接着条件的记录后停止搜索更多的行（查找冲突）

 

实际开发中，会对一个分类继续进行分类，这时候就需要无限分类表，

至少包含以下内容

CREATE TABLE tbl_name_type(

type_id SMALLINT UNSIGNER PRIMARY KEY AUTO_INCREMENT, //分类的编号

type_name VARCHAR(20) NOT NULL, //分类的名称

parent_type_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 //分类的父分类的编号

);

 

该表的查找通过自身连接来实现。

SELECT t1.type_name AS parent_name,t2.type_name AS child_name

FROM table_name AS t1 RIGHT JOIN table_name AS t2 ON t1.id=t2.parent_id;

要显示子类的所有内容，不含有父类时显示NULL，因此要朝向子类进行连接。

 

通过自身的连接删除重复的记录

 

（1）查找重复记录

SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;

（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;

//想象成右边有一个一样的表，起别名t1，左边的表起别名t2,t1左外链接t2,删除t1内重复的字段，小括号内是子查询

 

聚合函数的共同特点：只有一个返回值  用于数据表

AVG() - 平均值

SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;//添加AS操作是为了给临时表返回的字段添加字段名字

COUNT() - 计数

SELECT COUNT(goods_id) as counts FROM tdb_goods;

MAX() - 最大值

SELECT MAX(goods_price) as counts FROM tdb_goods;

MIN() - 最小值

SELECT MIN(goods_price) as counts FROM tdb_goods;

SUM() - 求和

SELECT SUM(goods_price) as counts FROM tdb_goods;