2018-05-01-5章_子查询与连接

mhh4399

于 2019-03-17 11:48:00 发布

阅读量143

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/github_38970218/article/details/88616517

版权

mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

5章子查询与连接

5-1数据准备

source filename.sql
执行sql文件来生成数据

mysql> source /root/create.sql
Query OK, 0 rows affected (0.00 sec)

5-2 MySQL 子查询简介

子查询

子查询(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。

子查询返回值

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

5-3 由比较运算符引发的子查询

使用比较运算符的子查询

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

语法结构

operand comparison_operator subquery

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

operand comparison_operator ANY (subquery)

operand comparison_operator SOME(subquery)

operand comparison_operator ALL(subquery)

ANY和SMOE是等价的，符合其中的一个就行，
ALL是符合全部

查询商品价格平均值

mysql> select avg(goods_price) from tdb_goods;
+------------------+
| avg(goods_price) |
+------------------+
|     5391.3043478 |
+------------------+
1 row in set (0.00 sec)

查询商品价格平均值四舍五入

mysql> select round(avg(goods_price)) from tdb_goods;
+-------------------------+
| round(avg(goods_price)) |
+-------------------------+
|                    5391 |
+-------------------------+
1 row in set (0.00 sec)

查询商品价格平均值四舍五入保留两位小数

mysql> select round(avg(goods_price),2) from tdb_goods;
+---------------------------+
| round(avg(goods_price),2) |
+---------------------------+
|                   5391.30 |
+---------------------------+
1 row in set (0.00 sec)

查询价格大于等于平均值的商品

mysql> select goods_id,goods_name,goods_price from tdb_goods
    -> where goods_price >=5391.30;
+----------+------------------------------+-------------+
| goods_id | goods_name                   | goods_price |
+----------+------------------------------+-------------+
|        3 | G150TH 15.6????????          |    8499.000 |
|        7 | SVP13226SCB 13.3??????????   |    7999.000 |
|       13 | iMac ME086CH/A 21.5????????? |    9188.000 |
|       17 | Mac Pro MD878CH/A ?????????  |   28888.000 |
|       18 |  HMZ-T3W ????????            |    6999.000 |
|       20 | X3250 M4??????????? 2583i14  |    6888.000 |
|       22 |  HMZ-T3W ????????            |    6999.000 |
+----------+------------------------------+-------------+
7 rows in set (0.00 sec)

查询价格大于等于平均值的商品，使用子查询

mysql> select goods_id,goods_name,goods_price 
    -> from tdb_goods 
    -> where goods_price >=(select round(avg(goods_price),2)
    -> from tdb_goods);
+----------+-----------------------------------------+-------------+
| goods_id | goods_name                              | goods_price |
+----------+-----------------------------------------+-------------+
|       26 | G150TH 15.6英寸游戏本                   |    8499.000 |
|       30 | SVP13226SCB 13.3英寸触控超极本          |    7999.000 |
|       36 | iMac ME086CH/A 21.5英寸一体电脑         |    9188.000 |
|       40 | Mac Pro MD878CH/A 专业级台式电脑        |   28888.000 |
|       41 |  HMZ-T3W 头戴显示设备                   |    6999.000 |
|       43 | X3250 M4机架式服务器 2583i14            |    6888.000 |
|       44 | 玄龙精英版 笔记本散热器                 |    6777.000 |
|       45 |  HMZ-T3W 头戴显示设备                   |    6999.000 |
+----------+-----------------------------------------+-------------+
8 rows in set (0.00 sec)

5-4 由[NOT] IN/EXISTS引发的子查询

使用[NOT] IN 的子查询

语法结构

operand comparison_operator [NOT] IN (subquery)

= ANY 运算符与 IN 等效

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

5-5 使用INSERT…SELECT插入记录

INERT … SELECT

将查询结果写入数据表

INSERT [INTO] tbl_name [(col_name,…)]

SELECT …

5-6 多表更新

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

--注意这里更新,在 update set 之间查寻
mysql> update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name
    -> set goods_cate=cate_id;
Query OK, 23 rows affected (0.00 sec)
Rows matched: 23  Changed: 23  Warnings: 0

5-7 多表更新之一步到位

CREATE…SELECT

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

CREATE TABLE [IF NOT EXISTS ] tbl_name

[(create_definition,…)]

select_statement

--创建并写入数据
mysql> 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 group by brand_name;
Query OK, 10 rows affected (0.07 sec)
Records: 10  Duplicates: 0  Warnings: 0
--查看tdb_goods_brands表
mysql> select * from `tdb_goods_brands`;
+----------+--------------+
| brand_id | brand_name   |
+----------+--------------+
|        1 | IBM          |
|        2 | 九州风神     |
|        3 | 华硕         |
|        4 | 宏碁         |
|        5 | 惠普         |
|        6 | 戴尔         |
|        7 | 索尼         |
|        8 | 联想         |
|        9 | 苹果         |
|       10 | 雷神         |
+----------+--------------+
10 rows in set (0.00 sec)

--修改tdb_goods的品牌,中文替换成tdb_goods_brans的brand_id
mysql> update tdb_goods as a inner join tdb_goods_brands as b 
	->on a.brand_name=b.brand_name
	->set a.brand_name=b.brand_id;
Query OK, 23 rows affected (0.00 sec)
Rows matched: 23  Changed: 23  Warnings: 0
--查看tdb_goods的表结构
--发现goods_cate和brand_name字段类型仍然是varchar
--瘦身还不够完美，所以修改字段类型为smallint
--用到的change
mysql> show columns from tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
| Field       | Type                   | Null | Key | Default | Extra          |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment |
| goods_name  | varchar(150)           | NO   |     | NULL    |                |
| goods_cate  | varchar(40)            | NO   |     | NULL    |                |
| brand_name  | varchar(40)            | NO   |     | NULL    |                |
| goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                |
| is_show     | tinyint(1)             | NO   |     | 1       |                |
| is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
+-------------+------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
--使用change更改字段类型
mysql> alter table tdb_goods
    -> change goods_cate cate_id smallint unsigned not null,
    -> change brand_name brand_id smallint unsigned not null;
Query OK, 23 rows affected (0.05 sec)
Records: 23  Duplicates: 0  Warnings: 0
--再次查看tdb_goods的数据结构
mysql> desc tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
| Field       | Type                   | Null | Key | Default | Extra          |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment |
| goods_name  | varchar(150)           | NO   |     | NULL    |                |
| cate_id     | smallint(5) unsigned   | NO   |     | NULL    |                |
| brand_id    | smallint(5) unsigned   | NO   |     | NULL    |                |
| goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                |
| is_show     | tinyint(1)             | NO   |     | 1       |                |
| is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
+-------------+------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

连接

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

5-8 连接的语法结构

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

数据表参照

table_reference

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

数据表可以使用tbl_name AS alias_name

或tbl_name alias_name 赋予别名。

table_subquery 可以作为子查询使用在FROM自句中,

这样的子查询必须为其赋予别名。

5-9 内连接INNER JOIN

连接类型

INNER JOIN,内连接

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

LEFT [OUTER] JOIN ,左外连接

RIGHT [OUTER] JOIN ,右外连接

连接条件

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

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

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

内连接只显示符合条件的记录

5-10 外连接OUTER JOIN

5-11 多表连接

--通过多张表的连接实现的
--其实多表的连接就是外键的逆向操作
--外键是将多张表分开
--多表连接又是将多张表连接
mysql> select goods_id,goods_name,cate_name,brand_name,goods_price 
	-> from tdb_goods as a  inner join tdb_goods_cates as b 
	-> on a.cate_id=b.cate_id 
	-> inner join tdb_goods_brands as c 
	-> on a.brand_id=c.brand_id\G;

5-12 关于连接的几点说明

5-13 无限级分类表设计

--创建一张表tdb_goods_types
mysql> 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
    ->   ); 
Query OK, 0 rows affected (0.02 sec)
--插入数据
--查看表
mysql> select * from `tdb_goods_types`;
+---------+-----------------+-----------+
| type_id | type_name       | parent_id |
+---------+-----------------+-----------+
|       1 | 家用电器        |         0 |
|       2 | 电脑、办公      |         0 |
|       3 | 大家电          |         1 |
|       4 | 生活电器        |         1 |
|       5 | 平板电视        |         3 |
|       6 | 空调            |         3 |
|       7 | 电风扇          |         4 |
|       8 | 饮水机          |         4 |
|       9 | 电脑整机        |         2 |
|      10 | 电脑配件        |         2 |
|      11 | 笔记本          |         9 |
|      12 | 超级本          |         9 |
|      13 | 游戏本          |         9 |
|      14 | CPU             |        10 |
|      15 | 主机            |        10 |
+---------+-----------------+-----------+
15 rows in set (0.00 sec)
--自关联查询,查询相对父级name和相对子级name
mysql> select p.type_id,p.type_name,c.type_name 
	-> from tdb_goods_types as p 
	-> inner join tdb_goods_types as c 
	-> on p.type_id = c.parent_id;
+---------+-----------------+--------------+
| type_id | type_name       | type_name    |
+---------+-----------------+--------------+
|       1 | 家用电器        | 大家电       |
|       1 | 家用电器        | 生活电器     |
|       3 | 大家电          | 平板电视     |
|       3 | 大家电          | 空调         |
|       4 | 生活电器        | 电风扇       |
|       4 | 生活电器        | 饮水机       |
|       2 | 电脑、办公      | 电脑整机     |
|       2 | 电脑、办公      | 电脑配件     |
|       9 | 电脑整机        | 笔记本       |
|       9 | 电脑整机        | 超级本       |
|       9 | 电脑整机        | 游戏本       |
|      10 | 电脑配件        | CPU          |
|      10 | 电脑配件        | 主机         |
+---------+-----------------+--------------+
13 rows in set (0.00 sec)
--查询并分组
mysql> select p.type_id,p.type_name,c.type_name 
	-> from tdb_goods_types as p 
	-> inner join tdb_goods_types as c 
	-> on p.type_id = c.parent_id group by p.type_name;
+---------+-----------------+--------------+
| type_id | type_name       | type_name    |
+---------+-----------------+--------------+
|       3 | 大家电          | 平板电视     |
|       1 | 家用电器        | 大家电       |
|       4 | 生活电器        | 电风扇       |
|       2 | 电脑、办公      | 电脑整机     |
|       9 | 电脑整机        | 笔记本       |
|      10 | 电脑配件        | CPU          |
+---------+-----------------+--------------+
6 rows in set (0.00 sec)
--查询并分组并排序
mysql> select p.type_id,p.type_name,c.type_name 
	-> from tdb_goods_types as p 
	-> inner join tdb_goods_types as c 
	-> on p.type_id = c.parent_id 
	-> group by p.type_name
	-> order by p.type_id;
+---------+-----------------+--------------+
| type_id | type_name       | type_name    |
+---------+-----------------+--------------+
|       1 | 家用电器        | 大家电       |
|       2 | 电脑、办公      | 电脑整机     |
|       3 | 大家电          | 平板电视     |
|       4 | 生活电器        | 电风扇       |
|       9 | 电脑整机        | 笔记本       |
|      10 | 电脑配件        | CPU          |
+---------+-----------------+--------------+
6 rows in set (0.00 sec)
--计算相对子级name并起别名 child_count
mysql> select p.type_id,p.type_name,count(c.type_name) as child_count
	-> from tdb_goods_types as p 
	-> inner join tdb_goods_types as c 
	-> on p.type_id = c.parent_id 
	-> group by p.type_name
	-> order by p.type_id;
+---------+-----------------+-------------+
| type_id | type_name       | child_count |
+---------+-----------------+-------------+
|       1 | 家用电器        |           2 |
|       2 | 电脑、办公      |           2 |
|       3 | 大家电          |           2 |
|       4 | 生活电器        |           2 |
|       9 | 电脑整机        |           3 |
|      10 | 电脑配件        |           2 |
+---------+-----------------+-------------+
6 rows in set (0.00 sec)

###5-14补充:

通过设置CONSTRAINT 这种方法来创建表并添加外键约束

mysql> CREATE TABLE stu(
    -> id int primary key auto_increment,
    -> name VARCHAR(30),
    -> GRADES int,
    -> c_id int,
    -> CONSTRAINT fk_grade FOREIGN KEY(c_id)
    -> REFERENCES py_class(id));
Query OK, 0 rows affected (0.02 sec)

SELECT CASE grades WHEN 60 THEN "A" WHEN 90 THEN "B" ELSE "不及格" END FROM stu;
SELECT CASE WHEN grades>60 THEN "及格" ELSE "不及格" END AS "级别" FROM stu;
SELECT CASE WHEN grades >= 90 THEN "A" WHEN grades<90 AND grades>60 THEN "B" ELSE "C" END AS 啦啦 FROM stu;

mhh4399

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2018-05-01-5章_子查询与连接

5章子查询与连接5-1数据准备source filename.sql执行sql文件来生成数据mysql&gt; source /root/create.sqlQuery OK, 0 rows affected (0.00 sec)5-2 MySQL 子查询简介子查询子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。例如:SELECT * FROM ...
复制链接

扫一扫