MySQL的子查询和连接知识点

最新推荐文章于 2024-08-20 17:31:09 发布

大数据_clown

最新推荐文章于 2024-08-20 17:31:09 发布

阅读量243

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/weixin_44210386/article/details/97390644

子查询

子查询是指出现在其他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。
查询不是指的是查找，而是所有SQL命令的统称。

子查询返回值
可以返回标量、一行、一列或子查询。
在这里插入图片描述
这里的AVG和ROUND函数是聚合函数，ROUND函数后面传入的参数是四舍五入后保留的小数的位数

在这里插入图片描述

使用[NOT] IN 的子查询

语法结构
operand comparison_operator [NOT]IN (subquery)
=ANY 运算符与IN 等效
！= ALL或<>ALL运算符与 NOT IN 等效

多表更新

表的参照关系：一张表通过JOIN
table_reference
{[INNER| CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}

mysql> SELECT goods_id, goods_name,cate_name,brand_name,goods_price
    -> FROM tdb_goods AS g
    -> INNER JOIN tdb_goods_cate AS c ON g.cate_id = c.cate_id
    -> INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

实质：通过外键进行多张表进行存储，再通过多表连接将各个表进行连接

连接类型
INNER JOIN
记住UPDATE一定要大写！！！

CREATE…SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,…)]
select_statement

在这里插入图片描述

在上面的代码当中实现了进行多表更新并且使用了子表查询的方式

修改了表的字段之后，也要修改表的结构
通过ALTER…CHANGE语句进行修改

连接
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
这里的连接指的是连接各个操作表

语法结构：
table_reference
{[INNER | CROSS] JOIN | {LEFT |RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

ON后面跟着的是连接的条件

内链接（相当于取并集）

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

通常使用ON关键字来设定连接条件，
使用WHERE关键字进行结果搜集记录的过滤。

外连接

左外连接
A LEFT JOIN B join_condition
数据表B的结果依赖数据表A
数据表A的结果集根据左连接条件依赖所有数据表（B除外）
左连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）。
如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行

无限级分类表的设计

例子：在图书馆的图书当中，图书可以分为四大名著，四大名著又可以进行无限的分类
所以可以通过自身连接：同一个数据表对其自身进行连接

  CREATE TABLE tdb_goods_types(
     type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     type_name VARCHAR(20) NOT NULL,
     parent_id SMALLINT UNSIGNED 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;

第一种自身连接方式，就是将自己的表看作父类，然后右边另一个表看作是子类，然后形成的临时表的第三列其实就是父类的type_name
在这里插入图片描述

 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;

第二种方式是把自己左边的表看作是子类的表，然后右边的表看作是父类的表，然后形成的临时表的最后一列就是子类的type_name，查询父类的子类

多表删除

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

1、首先先查询有多个重复记录的记录通过HAVING
2、通过DELETE语句进行删除

 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;

这里需要注意的是，使用到了子查询“（）”里就是进行子查询，值得一提的是
对于left join 不管on 后面跟什么条件，左表的数据全部查出来，因此想过滤把条件放到where后面去。