首页 > > 网络编程 > Mysql >

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

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签