1.子查询是指在另一个查询语句中的SELECT子句。
例句：
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
其中，SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement),
SELECT column1 FROM t2 称为Sub Query[子查询]。
所以，我们说子查询是嵌套在外查询内部。而事实上它有可能在子查询内部再嵌套子查询。
子查询必须出现在圆括号之间。
子查询可以包含多个关键字或条件：
如DISTINCT、GROUP BY、ORDER BY、LIMIT、等。
子查询的外层查询可以是：SELECT ,INSERT,UPDATE,SET或DO.
行级子查询
SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
行级子查询的返回结果最多为一行。子查询可以返回标量、一行、一列或子查询。
优化子查询
——————————————————————————————————————
-- 创建数据表：
root@127.0.0.1 t2>CREATE TABLE IF NOT EXISTS tdb_goods( -> goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> goods_name VARCHAR(150) NOT NULL, -> goods_cate VARCHAR(40) NOT NULL, -> brand_name VARCHAR(40) NOT NULL, -> goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0, -> is_show BOOLEAN NOT NULL DEFAULT 1, -> is_salesoff BOOLEAN NOT NULL DEFAULT 1 -> ); Query OK, 0 rows affected (0.20 sec)
——————————————————————————————————
写入数据：
root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); ERROR 1366 (HY000): Incorrect string value: 'xB4xE7xB1xCAxBCxC7...' for column 'goods_name' at row 1 root@127.0.0.1 t2>SET tdb_goods GBK; ERROR 1193 (HY000): Unknown system variable 'tdb_goods' root@127.0.0.1 t2>SET NAMES GBK; Query OK, 0 rows affected (0.00 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); Query OK, 1 row affected (0.15 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); ERROR 1054 (42S22): Unknown column 'is_saleoff' in 'field list' root@127.0.0.1 t2> INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT); Query OK, 1 row affected (0.13 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT); Query OK, 1 row affected (0.13 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT); Query OK, 1 row affected (0.13 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT); Query OK, 1 row affected (0.13 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）','平板电脑','苹果','3388',DEFAULT,DEFAULT); Query OK, 1 row affected (0.16 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）','平板电脑','苹果','2788',DEFAULT,DEFAULT); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT); Query OK, 1 row affected (0.16 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT); Query OK, 1 row affected (0.13 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT); Query OK, 1 row affected (0.13 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT); Query OK, 1 row affected (0.13 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); Query OK, 1 row affected (0.13 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT); Query OK, 1 row affected (0.16 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_salesoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); Query OK, 1 row affected (0.13 sec)
——————————————————————————————————————————————————
由比较运算符引发的子查询：
-- 求所有电脑产品的平均价格,并且保留两位小数，AVG,MAX,MIN、COUNT、SUM为聚合函数 SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods; -- 查询所有价格大于平均价格的商品，并且按价格降序排序 SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > 5845.10 ORDER BY goods_price DESC; -- 使用子查询来实现 SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods) ORDER BY goods_price DESC; -- 查询类型为“超记本”的商品价格 SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本'; -- 查询价格大于或等于"超级本"价格的商品，并且按价格降序排列 SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC; -- = ANY 或 = SOME 等价于 IN SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;
ANY 和 SOME 两个修饰符是等价的，它们两个修饰符提示之返回一个值：
注意： 1. 子查询只能返回一行记录，不然会报错。 2. 子查询还可以包含子查询。 关键字 ANY SOME ALL >、>= 最小值 最小值 最大值 <、<= 最大值 最大值 最小值 = 任意值 任意值 <>、！= 任意值
=ANY 等价于 IN !=ALL或<>ANY等价于NOT IN 使用[NOT]EXIST引发的子查询 （不常用）
使用[NOT]EXIST引发的子查询 （不常用）如果子查询返回了任何的结果，我们就返回true，否则的话我们就返回了false。
——————————————————————————————————————————————————
使用INSERT.....SELECT插入记录：
root@127.0.0.1 t2>CREATE TABLE IF NOT EXISTS tdb_goods_cates( -> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> cate_name VARCHAR(40) NOT NULL); Query OK, 0 rows affected (0.20 sec)
将分组结果写入到tdb_goods_cates数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
root@127.0.0.1 t2>INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate; Query OK, 7 rows affected (0.14 sec) Records: 7 Duplicates: 0 Warnings: 0 root@127.0.0.1 t2>SELECT * FROM tdb_goods_cates; +---------+---------------+ | cate_id | cate_name | +---------+---------------+ | 1 | 台式机 | | 2 | 平板电脑 | | 3 | 服务器/工作站 | | 4 | 游戏本 | | 5 | 笔记本 | | 6 | 笔记本配件 | | 7 | 超级本 | +---------+---------------+ 7 rows in set (0.00 sec
——————————————————————————————————
多表更新：
UPDATE table_references SET col_name1={expr1 DEFAULT} [,col_name2={expr2 DEFAULT}]... [WHERE where_condition]
通过tdb_goods_cates数据表来更新tdb_goods表
root@127.0.0.1 t2>UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name -> SET goods_cate = cate_id ; Query OK, 22 rows affected (0.14 sec) Rows matched: 22 Changed: 22 Warnings: 0 root@127.0.0.1 t2>SELECT * FROM tdb_goodsG; *************************** 1. row *************************** goods_id: 1 goods_name: R510VC 15.6英寸笔记本 goods_cate: 5 brand_name: 华硕 goods_price: 3399.000 is_show: 1 is_salesoff: 1
————————————————————————————————————————————————————————————————
多表更新一步到位：
建表、查询、写入三合一： 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;
root@127.0.0.1 t2>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, 9 rows affected (0.22 sec) Records: 9 Duplicates: 0 Warnings: 0 root@127.0.0.1 t2>SELECT * FROM tdb_goods_brands; +----------+------------+ | brand_id | brand_name | +----------+------------+ | 1 | IBM | | 2 | 华硕 | | 3 | 宏碁 | | 4 | 惠普 | | 5 | 戴尔 | | 6 | 索尼 | | 7 | 联想 | | 8 | 苹果 | | 9 | 雷神 | +----------+------------+ 9 rows in set (0.00 sec)
我们还可以使用UPDATE来进行关联：
root@127.0.0.1 t2>UPDATE tdb_goods INNER JOIN tdb_goods_brands ON tdb_goods.brand_name = tdb_goods_brands.brand_name -> SET tdb_goods.brand_name = tdb_goods_brands.brand_id; Query OK, 22 rows affected (0.15 sec) Rows matched: 22 Changed: 22 Warnings: 0
root@127.0.0.1 t2>SELECT * FROM tdb_goodsG; *************************** 1. row *************************** goods_id: 1 goods_name: R510VC 15.6英寸笔记本 goods_cate: 5 brand_name: 2 goods_price: 3399.000 is_show: 1 is_salesoff: 1
通过ALTER TABLE语句修改数据表结构 ALTER TABLE tdb_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL, CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL; Ps1：外键，不一定是物理的外键，逻辑的外键也行，当然，物理外键更能保证数据的完整性和一致性。 Ps2：注意：数字类型的字段占用的空间更小，查询的效率也更高。
root@127.0.0.1 t2>ALTER TABLE tdb_goods -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL, -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL; Query OK, 22 rows affected (0.38 sec) Records: 22 Duplicates: 0 Warnings: 0 root@127.0.0.1 t2>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 | | | 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_salesoff | tinyint(1) | NO | | 1 | | +-------------+------------------------+------+-----+---------+----------------+ 7 rows in set (0.01 sec)
-- 分别在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');
root@127.0.0.1 t2>INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡'); Query OK, 3 rows affected (0.14 sec) Records: 3 Duplicates: 0 Warnings: 0 root@127.0.0.1 t2> root@127.0.0.1 t2> INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟'); Query OK, 3 rows affected (0.18 sec) Records: 3 Duplicates: 0 Warnings: 0 root@127.0.0.1 t2>INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849'); Query OK, 1 row affected (0.14 sec) //这里面会出现一个小错误，那就是我们的tdb_goods_cates中并不存在12这个数字，但是由于我们并没有使用外键，所以我们就不会报错。
——————————————————————————————————————————————————————
链接的语法结构：
1.连接 MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。 语法结构 table reference A {[INNERCROSS] JOIN {LEFTRIGHT} [OUTER] JOIN} table_reference B ON condition_expr 2.数据表参照 table_reference tbl_name [[AS] alias] table_subquery [AS] alias 数据表可以使用tbl_name AS alias_name 或 tbl_name alias_name赋予别名。 table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。
链接类型有很多种，有左链接、右连接以及内链接三种：
-- 查询所有商品的详细信息(通过内连接实现) 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_idG; -- 查询所有商品的详细信息(通过左外连接实现) SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_idG; -- 查询所有商品的详细信息(通过右外连接实现) SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_idG; -- 无限分类的数据表设计 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; -- 查找所有分类及其子类 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; -- 查找所有分类及其子类的数目 SELECT p.type_id,p.type_name,count(s.type_name) AS children_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; -- 为tdb_goods_types添加child_count字段 ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0; -- 将刚才查询到的子类数量更新到tdb_goods_types数据表 UPDATE tdb_goods_types AS t1 INNER JOIN ( SELECT p.type_id,p.type_name,count(s.type_name) AS children_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.children_count; -- 复制编号为12,20的两条记录 SELECT * FROM tdb_goods WHERE goods_id IN (19,20); -- INSERT ... SELECT实现复制 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); -- 查找重复记录 SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 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;
内链接指的是左表和右表共同的部分
root@127.0.0.1 t2>SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id G:; *************************** 1. row *************************** goods_id: 1 goods_name: R510VC 15.6英寸笔记本 cate_name: 笔记本 *************************** 2. row *************************** goods_id: 2 goods_name: Y400N 14.0英寸笔记本电脑 cate_name: 笔记本 *************************** 3. row *************************** goods_id: 3 goods_name: G150TH 15.6英寸游戏本 cate_name: 游戏本 *************************** 4. row *************************** goods_id: 4 goods_name: X550CC 15.6英寸笔记本 cate_name: 笔记本 *************************** 5. row *************************** goods_id: 5 goods_name: X240(20ALA0EYCD) 12.5英寸超极本 cate_name: 超级本 *************************** 6. row *************************** goods_id: 6 goods_name: U330P 13.3英寸超极本 cate_name: 超级本 *************************** 7. row *************************** goods_id: 7 goods_name: SVP13226SCB 13.3英寸触控超极本 cate_name: 超级本 *************************** 8. row *************************** goods_id: 8 goods_name: iPad mini MD531CH/A 7.9英寸平板电脑 cate_name: 平板电脑 *************************** 9. row *************************** goods_id: 9 goods_name: iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） cate_name: 平板电脑 *************************** 10. row *************************** goods_id: 10 goods_name: iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） cate_name: 平板电脑 *************************** 11. row *************************** goods_id: 11 goods_name: IdeaCentre C340 20英寸一体电脑 cate_name: 台式机 *************************** 12. row *************************** goods_id: 12 goods_name: Vostro 3800-R1206 台式电脑 cate_name: 台式机 *************************** 13. row *************************** goods_id: 13 goods_name: iMac ME086CH/A 21.5英寸一体电脑 cate_name: 台式机 *************************** 14. row *************************** goods_id: 14 goods_name: AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） cate_name: 台式机 *************************** 15. row *************************** goods_id: 15 goods_name: Z220SFF F4F06PA工作站 cate_name: 服务器/工作站 *************************** 16. row *************************** goods_id: 16 goods_name: PowerEdge T110 II服务器 cate_name: 服务器/工作站 *************************** 17. row *************************** goods_id: 17 goods_name: Mac Pro MD878CH/A 专业级台式电脑 cate_name: 服务器/工作站 *************************** 18. row *************************** goods_id: 18 goods_name: HMZ-T3W 头戴显示设备 cate_name: 笔记本配件 *************************** 19. row *************************** goods_id: 19 goods_name: 商务双肩背包 cate_name: 笔记本配件 *************************** 20. row *************************** goods_id: 20 goods_name: X3250 M4机架式服务器 2583i14 cate_name: 服务器/工作站 *************************** 21. row *************************** goods_id: 21 goods_name: HMZ-T3W 头戴显示设备 cate_name: 笔记本配件 *************************** 22. row *************************** goods_id: 22 goods_name: 商务双肩背包 cate_name: 笔记本配件 22 rows in set (0.00 sec) ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':' at line 1 root@127.0.0.1 t2>SELECT * FROM tdb_goods_cates; +---------+---------------+ | cate_id | cate_name | +---------+---------------+ | 1 | 台式机 | | 2 | 平板电脑 | | 3 | 服务器/工作站 | | 4 | 游戏本 | | 5 | 笔记本 | | 6 | 笔记本配件 | | 7 | 超级本 | | 8 | 路由器 | | 9 | 交换机 | | 10 | 网卡 | +---------+---------------+ 10 rows in set (0.00 sec) root@127.0.0.1 t2>SELECT cate_id FROM goods_cates; ERROR 1146 (42S02): Table 't2.goods_cates' doesn't exist root@127.0.0.1 t2>SELECT cate_id FROM tdb_goods; +---------+ | cate_id | +---------+ | 5 | | 5 | | 4 | | 5 | | 7 | | 7 | | 7 | | 2 | | 2 | | 2 | | 1 | | 1 | | 1 | | 1 | | 3 | | 3 | | 3 | | 6 | | 6 | | 3 | | 6 | | 6 | | 12 | +---------+ 23 rows in set (0.00 sec)
root@127.0.0.1 t2>SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id G:; *************************** 1. row *************************** goods_id: 1 goods_name: R510VC 15.6英寸笔记本 cate_name: 笔记本 *************************** 2. row *************************** goods_id: 2 goods_name: Y400N 14.0英寸笔记本电脑 cate_name: 笔记本 *************************** 3. row *************************** goods_id: 3 goods_name: G150TH 15.6英寸游戏本 cate_name: 游戏本 *************************** 4. row *************************** goods_id: 4 goods_name: X550CC 15.6英寸笔记本 cate_name: 笔记本 *************************** 5. row *************************** goods_id: 5 goods_name: X240(20ALA0EYCD) 12.5英寸超极本 cate_name: 超级本 *************************** 6. row *************************** goods_id: 6 goods_name: U330P 13.3英寸超极本 cate_name: 超级本 *************************** 7. row *************************** goods_id: 7 goods_name: SVP13226SCB 13.3英寸触控超极本 cate_name: 超级本 *************************** 8. row *************************** goods_id: 8 goods_name: iPad mini MD531CH/A 7.9英寸平板电脑 cate_name: 平板电脑 *************************** 9. row *************************** goods_id: 9 goods_name: iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） cate_name: 平板电脑 *************************** 10. row *************************** goods_id: 10 goods_name: iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） cate_name: 平板电脑 *************************** 11. row *************************** goods_id: 11 goods_name: IdeaCentre C340 20英寸一体电脑 cate_name: 台式机 *************************** 12. row *************************** goods_id: 12 goods_name: Vostro 3800-R1206 台式电脑 cate_name: 台式机 *************************** 13. row *************************** goods_id: 13 goods_name: iMac ME086CH/A 21.5英寸一体电脑 cate_name: 台式机 *************************** 14. row *************************** goods_id: 14 goods_name: AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） cate_name: 台式机 *************************** 15. row *************************** goods_id: 15 goods_name: Z220SFF F4F06PA工作站 cate_name: 服务器/工作站 *************************** 16. row *************************** goods_id: 16 goods_name: PowerEdge T110 II服务器 cate_name: 服务器/工作站 *************************** 17. row *************************** goods_id: 17 goods_name: Mac Pro MD878CH/A 专业级台式电脑 cate_name: 服务器/工作站 *************************** 18. row *************************** goods_id: 18 goods_name: HMZ-T3W 头戴显示设备 cate_name: 笔记本配件 *************************** 19. row *************************** goods_id: 19 goods_name: 商务双肩背包 cate_name: 笔记本配件 *************************** 20. row *************************** goods_id: 20 goods_name: X3250 M4机架式服务器 2583i14 cate_name: 服务器/工作站 *************************** 21. row *************************** goods_id: 21 goods_name: HMZ-T3W 头戴显示设备 cate_name: 笔记本配件 *************************** 22. row *************************** goods_id: 22 goods_name: 商务双肩背包 cate_name: 笔记本配件 *************************** 23. row *************************** goods_id: 23 goods_name: LaserJet Pro P1606dn 黑白激光打印机 cate_name: NULL 23 rows in set (0.00 sec) ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':' at line 1 root@127.0.0.1 t2>SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id G:; *************************** 1. row *************************** goods_id: 11 goods_name: IdeaCentre C340 20英寸一体电脑 cate_name: 台式机 *************************** 2. row *************************** goods_id: 12 goods_name: Vostro 3800-R1206 台式电脑 cate_name: 台式机 *************************** 3. row *************************** goods_id: 13 goods_name: iMac ME086CH/A 21.5英寸一体电脑 cate_name: 台式机 *************************** 4. row *************************** goods_id: 14 goods_name: AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） cate_name: 台式机 *************************** 5. row *************************** goods_id: 8 goods_name: iPad mini MD531CH/A 7.9英寸平板电脑 cate_name: 平板电脑 *************************** 6. row *************************** goods_id: 9 goods_name: iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） cate_name: 平板电脑 *************************** 7. row *************************** goods_id: 10 goods_name: iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） cate_name: 平板电脑 *************************** 8. row *************************** goods_id: 15 goods_name: Z220SFF F4F06PA工作站 cate_name: 服务器/工作站 *************************** 9. row *************************** goods_id: 16 goods_name: PowerEdge T110 II服务器 cate_name: 服务器/工作站 *************************** 10. row *************************** goods_id: 17 goods_name: Mac Pro MD878CH/A 专业级台式电脑 cate_name: 服务器/工作站 *************************** 11. row *************************** goods_id: 20 goods_name: X3250 M4机架式服务器 2583i14 cate_name: 服务器/工作站 *************************** 12. row *************************** goods_id: 3 goods_name: G150TH 15.6英寸游戏本 cate_name: 游戏本 *************************** 13. row *************************** goods_id: 1 goods_name: R510VC 15.6英寸笔记本 cate_name: 笔记本 *************************** 14. row *************************** goods_id: 2 goods_name: Y400N 14.0英寸笔记本电脑 cate_name: 笔记本 *************************** 15. row *************************** goods_id: 4 goods_name: X550CC 15.6英寸笔记本 cate_name: 笔记本 *************************** 16. row *************************** goods_id: 18 goods_name: HMZ-T3W 头戴显示设备 cate_name: 笔记本配件 *************************** 17. row *************************** goods_id: 19 goods_name: 商务双肩背包 cate_name: 笔记本配件 *************************** 18. row *************************** goods_id: 21 goods_name: HMZ-T3W 头戴显示设备 cate_name: 笔记本配件 *************************** 19. row *************************** goods_id: 22 goods_name: 商务双肩背包 cate_name: 笔记本配件 *************************** 20. row *************************** goods_id: 5 goods_name: X240(20ALA0EYCD) 12.5英寸超极本 cate_name: 超级本 *************************** 21. row *************************** goods_id: 6 goods_name: U330P 13.3英寸超极本 cate_name: 超级本 *************************** 22. row *************************** goods_id: 7 goods_name: SVP13226SCB 13.3英寸触控超极本 cate_name: 超级本 *************************** 23. row *************************** goods_id: NULL goods_name: NULL cate_name: 路由器 *************************** 24. row *************************** goods_id: NULL goods_name: NULL cate_name: 交换机 *************************** 25. row *************************** goods_id: NULL goods_name: NULL cate_name: 网卡 25 rows in set (0.00 sec) ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':' at line 1
LEFT指的是左外链接，RIGHT指的是右外链接。
root@127.0.0.1 t2>SELECT goods_id,goods_name,cate_name 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 d ON d.brand_id = c.cate_id; +----------+------------------------------------------------------------------------+---------------+ | goods_id | goods_name | cate_name | +----------+------------------------------------------------------------------------+---------------+ | 1 | R510VC 15.6英寸笔记本 | 笔记本 | | 2 | Y400N 14.0英寸笔记本电脑 | 笔记本 | | 3 | G150TH 15.6英寸游戏本 | 游戏本 | | 4 | X550CC 15.6英寸笔记本 | 笔记本 | | 5 | X240(20ALA0EYCD) 12.5英寸超极本 | 超级本 | | 6 | U330P 13.3英寸超极本 | 超级本 | | 7 | SVP13226SCB 13.3英寸触控超极本 | 超级本 | | 8 | iPad mini MD531CH/A 7.9英寸平板电脑 | 平板电脑 | | 9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） | 平板电脑 | | 10 | iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 平板电脑 | | 11 | IdeaCentre C340 20英寸一体电脑 | 台式机 | | 12 | Vostro 3800-R1206 台式电脑 | 台式机 | | 13 | iMac ME086CH/A 21.5英寸一体电脑 | 台式机 | | 14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） | 台式机 | | 15 | Z220SFF F4F06PA工作站 | 服务器/工作站 | | 16 | PowerEdge T110 II服务器 | 服务器/工作站 | | 17 | Mac Pro MD878CH/A 专业级台式电脑 | 服务器/工作站 | | 18 | HMZ-T3W 头戴显示设备 | 笔记本配件 | | 19 | 商务双肩背包 | 笔记本配件 | | 20 | X3250 M4机架式服务器 2583i14 | 服务器/工作站 | | 21 | HMZ-T3W 头戴显示设备 | 笔记本配件 | | 22 | 商务双肩背包 | 笔记本配件 | +----------+------------------------------------------------------------------------+---------------+ 22 rows in set (0.00 sec)
这个指的是多表连接。
显示表：show clumns from 表名; 无限分类：即在同一张表中既有父类，又有子类 通过在分类表中再增加多一个字段标识其属于哪一个父类的 ID 来实现 可以通过对同一张数据表的自身连接来进行查询，需要对表标识别名 查找显示父级id对应的名称 select s.type_id ,s.type_name,p.type_name As parent_id from tdb_goods_types s left join tdb_goods_types p on s.parent_id=p.type_id; 查找子级对应的名称 select p.type_id ,p.type_name,s.type_name from tdb_goods_types p left join tdb_goods_types s on p.type_id=s.parent_id; 查找有多少子级 select p.type_id ,p.type_name,COUNT(s.type_name) from tdb_goods_types p left join tdb_goods_types s on p.type_id=s.parent_id GROUP BY p.type_name ORDER BY p.type_id;
root@127.0.0.1 t2>set names gbk; Query OK, 0 rows affected (0.00 sec) root@127.0.0.1 t2>INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT); Query OK, 1 row affected (0.03 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1); Query OK, 1 row affected (0.03 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1); Query OK, 1 row affected (0.03 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3); Query OK, 1 row affected (0.04 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3); Query OK, 1 row affected (0.03 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4); Query OK, 1 row affected (0.04 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4); Query OK, 1 row affected (0.03 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2); Query OK, 1 row affected (0.04 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2); Query OK, 1 row affected (0.03 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9); Query OK, 1 row affected (0.04 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9); Query OK, 1 row affected (0.03 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9); Query OK, 1 row affected (0.04 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10); Query OK, 1 row affected (0.03 sec) root@127.0.0.1 t2> INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10); Query OK, 1 row affected (0.03 sec) root@127.0.0.1 t2>SELECT * FROM tdb_goods_types; +---------+------------+-----------+ | type_id | type_name | parent_id | +---------+------------+-----------+ | 1 | CPU | 10 | | 2 | CPU | 10 | | 3 | 家用电器 | 0 | | 4 | 电脑、办公 | 0 | | 5 | 大家电 | 1 | | 6 | 生活电器 | 1 | | 7 | 平板电视 | 3 | | 8 | 空调 | 3 | | 9 | 电风扇 | 4 | | 10 | 饮水机 | 4 | | 11 | 电脑整机 | 2 | | 12 | 电脑配件 | 2 | | 13 | 笔记本 | 9 | | 14 | 超级本 | 9 | | 15 | 游戏本 | 9 | | 16 | CPU | 10 | | 17 | 主机 | 10 | +---------+------------+-----------+ 17 rows in set (0.00 sec)
如果我们在插入数据的时候程序报错的话，我们就需要使用set name gbk来调整。
多表删除可以删去重复的数据。
root@127.0.0.1 t2>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; Query OK, 2 rows affected (0.13 sec)