MySQL学习笔记-子查询和连接

2018-06-17 23:45:28来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

MySQL学习笔记-子查询和连接
 
使客户端进入gbk编码方式显示:
mysql> SET NAMES gbk;

 

1.子查询
子查询的定义:
子查询(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
 
子查询的返回值:
子查询可以返回标量、一行、一列或子查询
 
2.使用比较运算符的子查询
使用比较运算符的子查询:
=、>、<、>=、<=、<>、!=、 <=>
语法结构:
operand(操作数)、comparison_operator (比较运算符)、subquery(子查询)
用ANY  、SOME 或ALL修饰的比较运算符
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
 
1.语法结构  操作数  比较运算符 any(子查询)
            操作数  比较运算符 some(子查询)
            操作数  比较运算符 all(子查询)
2.适合于子查询有多个结果
3.any 和some结果一致 all与any、some 相反
 
例,从tdb_goods表中 查询平均价格,小数点保留2位,:
mysql> SELECT ROUND(avg(goods_price),2) AS avg_price FROM tdb_goods;
+-----------+
| avg_price |
+-----------+
|   5391.30 |
+-----------+

 

查询平均价格以上的商品:
mysql> SELECT * FROM tdb_goods WHERE goods_price > 5391.30; 

 

mysql> SELECT * FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

 

 
查询超极本的列表,按价格升序排列:
mysql> SELECT * FROM tdb_goods WHERE goods_cate = '超级本' ORDER BY goods_price ASC; 

 

  • 查询比超极本的最低价格高的列表,按价格降序的方式排列:
mysql> SELECT * FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;

 

  • 查询比超极本的最高价格高的列表,按价格降序的方式排列:
mysql> SELECT * FROM tdb_goods WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;

 

 
3.使用[NOT]IN/EXISTS引发的子查询
子查询形式:
1.使用IN的子查询
2.使用[NOT]IN的子查询
  语法结构:operand comparison_operator [NOT] IN (subquery)
  =ANY 运算符与IN 等效,!=ALL或<>ALL运算符与NOT IN等效
3.使用[NOT]EXISTS的子查询(用的相对较少)
  如果子查询返回任何行,EXISTS将返回TRUE,否则为FALSE
例,查找不是超极本的商品列表:
mysql> SELECT * FROM tdb_goods WHERE goods_cate NOT IN(SELECT goods_cate FROM tdb_goods WHERE goods_cate = '超级本');

 

 
 
4.使用INSERT...SELECT插入记录
例:在tdb_goods_cates表中插入tdb_goods表中的goods_cate分类;
mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
mysql> SELECT * FROM tdb_goods_cates;
+---------+---------------------+
| cate_id | cate_name           |
+---------+---------------------+
|       1 | 台式机              |
|       2 | 平板电脑            |
|       3 | 服务器/工作站       |
|       4 | 游戏本              |
|       5 | 笔记本              |
|       6 | 笔记本配件          |
|       7 | 超级本              |
+---------+---------------------+

 

5.多表更新
多表更新:
UPDATE table_references SET col_name1={expr1|DEFAULT}
[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition]

 

其中,table_references即连接的语法结构
 
连接类型:
INNER JOIN,内连接
   在MySQL中,JOIN,CROSS JOIN和INNER JOIN是等价的
LEFT [OUTER] JOIN,左外连接
RIGHT [OUTER] JOIN,右外连接
 
连接—语法结构
table_reference
{[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

 

例,将tdb_goods中的goods_cate更新为tdb_goods_cate表中的对应cate_id。 
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;

 

 
6.多表更新之一步到位
CREATE...SELECT
创建数据表同时将查询结果写入到数据表(合并了CREATE 和 INSERT...SELECT两个操作步骤)
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

 

例:创建品牌分类数据表tdb_goods_brand,并将tdb_goods表中的brand_name写入
mysql> CREATE TABLE tdb_goods_brand(
    -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> brand_name VARCHAR(40) NOT NULL
    -> )
    -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;

 

  • 例,将tdb_goods中的brand_name更新为tdb_goods_brand表中的对应brand_id。错误写法(程序分别不出哪个brand_name属于哪个表):
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_brand ON brand_name = brand_name SET brand_name = brand_id;
ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous

 

解决方法是给表起别名:
mysql> UPDATE tdb_goods AS a INNER JOIN tdb_goods_brand AS b ON a.brand_name = b.brand_name SET a.brand_name = b.brand_id;

 

  • 查询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    |                |
| 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       |                |
+-------------+------------------------+------+-----+---------+----------------+

 

goods_cate和brand_name任然是varchar,现在我们修改字段名goods_cate为cate_id,brand_name修改为brand_id,  为了节省空间,我们修改数据类型为smallant
mysql> ALTER TABLE tdb_goods;
    -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
    -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
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.连接的语法结构
连接在MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
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子句中,
这样的子查询必须赋予别名。
 
8.内连接INNER JOIN
INNER JOIN,内连接
在MySQL中,JOIN,CROSS JOIN 和 INNER JOIN 是等价的
LEFT [OUTER] JOIN,左外连接
RIGHT [OUTER] JOIN,右外连接
连接条件:
使用ON关键字来设定连接条件,也可以使用WHERE来代替
通常使用ON关键字来设定连接条件
使用WHERE关键字进行结果集记录的过滤
 
内连接和外连接的区别
内连接 ,显示左表及右表符合连接条件的记录,即交集
例如 插入几条记录
-- 分别在tdb_goods_cates和tdb_goods_brands表插入记录
INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');

 

-- 在tdb_goods数据表写入任意记
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

 

例,查看符合内连接的记录,表中只出现商品表和品牌表都有的记录,这就是内连接(这里商品表为左表,分类表为右表):
mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods
-> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

 

 
 
9.外连接OUTER JOIN
左外连接
  • 显示左表的全部记录及右表符合连接条件的记录
  • 例,查看符合左外连接的记录,表中出现商品表的全部和2表表都有的记录,这就是左外连接(这里商品表为左表,分类表为右表)
mysql> SELECT goods_id,cate_name,goods_price FROM tdb_goods 
LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

 

 
 
  • 右外连接
 
  • 显示右表的全部记录及左表符合连接条件的记录
  • 例,查看符合右外连接的记录,表中出现品牌表的全部和2表表都有的记录,这就是右外连接(这里商品表为左表,分类表为右表)
mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods 
RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

 

 
 
10.多表连接
/**  
  多表的连接跟两张表的连接一样
  表的连接实质就是外键的逆向约束
*/
例,查看符合内连接的记录,表中只出现商品表和品牌表都有的记录,这就是内连接的多表连接(这里商品表为左表,品牌表和分类表为右表):
mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods
    -> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id
    -> INNER JOIN tdb_goods_brand ON tdb_goods.brand_id = tdb_goods_brand.brand_id;

 

 
11.无限级分类表设计
自身连接
 
  • 例,查找所有分类及其父类:
mysql> 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_id | type_name       | type_name       |
+---------+-----------------+-----------------+
|       1 | 家用电器        | NULL            |
|       2 | 电脑、办公      | NULL            |
|       3 | 大家电          | 家用电器        |
|       4 | 生活电器        | 家用电器        |
|       5 | 平板电视        | 大家电          |
|       6 | 空调            | 大家电          |
|       7 | 电风扇          | 生活电器        |
|       8 | 饮水机          | 生活电器        |
|       9 | 电脑整机        | 电脑、办公      |
|      10 | 电脑配件        | 电脑、办公      |
|      11 | 笔记本          | 电脑整机        |
|      12 | 超级本          | 电脑整机        |
|      13 | 游戏本          | 电脑整机        |
|      14 | CPU             | 电脑配件        |
|      15 | 主机            | 电脑配件        |
+---------+-----------------+-----------------+

 

  • 例,查找所有分类及其子类:
mysql> SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id; 
+---------+-----------------+--------------+
| type_id | type_name       | type_name    |
+---------+-----------------+--------------+
|       1 | 家用电器        | 大家电       |
|       1 | 家用电器        | 生活电器     |
|       2 | 电脑、办公      | 电脑整机     |
|       2 | 电脑、办公      | 电脑配件     |
|       3 | 大家电          | 平板电视     |
|       3 | 大家电          | 空调         |
|       4 | 生活电器        | 电风扇       |
|       4 | 生活电器        | 饮水机       |
|       5 | 平板电视        | NULL         |
|       6 | 空调            | NULL         |
|       7 | 电风扇          | NULL         |
|       8 | 饮水机          | NULL         |
|       9 | 电脑整机        | 笔记本       |
|       9 | 电脑整机        | 超级本       |
|       9 | 电脑整机        | 游戏本       |
|      10 | 电脑配件        | CPU          |
|      10 | 电脑配件        | 主机         |
|      11 | 笔记本          | NULL         |
|      12 | 超级本          | NULL         |
|      13 | 游戏本          | NULL         |
|      14 | CPU             | NULL         |
|      15 | 主机            | NULL         |
+---------+-----------------+--------------+

 

  • 例,查找所有分类及其子类的数目
mysql> SELECT p.type_id,p.type_name,COUNT(s.type_name) FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;
+---------+-----------------+--------------------+
| type_id | type_name       | COUNT(s.type_name) |
+---------+-----------------+--------------------+
|       1 | 家用电器        |                  2 |
|       2 | 电脑、办公      |                  2 |
|       3 | 大家电          |                  2 |
|       4 | 生活电器        |                  2 |
|       5 | 平板电视        |                  0 |
|       6 | 空调            |                  0 |
|       7 | 电风扇          |                  0 |
|       8 | 饮水机          |                  0 |
|       9 | 电脑整机        |                  3 |
|      10 | 电脑配件        |                  2 |
|      11 | 笔记本          |                  0 |
|      12 | 超级本          |                  0 |
|      13 | 游戏本          |                  0 |
|      14 | CPU             |                  0 |
|      15 | 主机            |                  0 |
+---------+-----------------+--------------------+

 

  • 为tdb_goods_types添加child_count字段
mysql> UPDATE tdb_goods_types AS t1 INNER JOIN
    -> (SELECT p.type_id,p.type_name,count(s.type_name) AS child_count FROM tdb_goods_types AS p
    -> LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id
    -> GROUP BY p.type_name
    -> ORDER BY p.type_id)AS t2
    -> ON t1.type_id = t2.type_id
    ->  SET t1.child_count = t2.child_count;
    
mysql> SELECT * FROM tdb_goods_types;
+---------+-----------------+-----------+-------------+
| type_id | type_name       | parent_id | child_count |
+---------+-----------------+-----------+-------------+
|       1 | 家用电器        |         0 |           2 |
|       2 | 电脑、办公      |         0 |           2 |
|       3 | 大家电          |         1 |           2 |
|       4 | 生活电器        |         1 |           2 |
|       5 | 平板电视        |         3 |           0 |
|       6 | 空调            |         3 |           0 |
|       7 | 电风扇          |         4 |           0 |
|       8 | 饮水机          |         4 |           0 |
|       9 | 电脑整机        |         2 |           3 |
|      10 | 电脑配件        |         2 |           2 |
|      11 | 笔记本          |         9 |           0 |
|      12 | 超级本          |         9 |           0 |
|      13 | 游戏本          |         9 |           0 |
|      14 | CPU             |        10 |           0 |
|      15 | 主机            |        10 |           0 |
+---------+-----------------+-----------+-------------+

 

11.多表删除
DELETE tbl_name[.*][,tbl_name[.*]]...
FROM table_references
[WHERE where_condition]

 

例,查找重复记录:
mysql> SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name having count(goods_name) >= 2;                
+----------+-----------------------------+
| goods_id | goods_name                  |
+----------+-----------------------------+
|       18 |  HMZ-T3W 头戴显示设备       |
|       19 | 商务双肩背包                |
+----------+-----------------------------+

 

删除重复记录
mysql> 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;

 

12.复制记录
复制编号为19,20的两条记录
mysql>  SELECT goods_id,goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);
+----------+------------------------------------+---------+----------+
| goods_id | goods_name                         | cate_id | brand_id |
+----------+------------------------------------+---------+----------+
|       19 | 商务双肩背包                       |       6 |        7 |
|       20 | X3250 M4机架式服务器 2583i14       |       3 |        1 |
+----------+------------------------------------+---------+----------+
 
mysql> INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN(19,20);

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:mysql数据库小常识

下一篇:SQLyog-12.4.2版下载,SQLyog最新版下载,SQLyog官网下载,SQLyo