- 创建数据表
mysql> 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_saleoff BOOLEAN NOT NULL DEFAULT 0
);
- 写入数据
mysql> INSERT tdb_goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
mysql> ...
- 查看列
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.01 sec)
- SET NAMES gbk; 客户端显示数据的编码方式为gbk,并不影响数据存储数据的编码。
- 查看数据
mysql> select * from tdb_goods\G;
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
goods_cate: 笔记本
brand_name: 华硕
goods_price: 3399.000
is_show: 1
is_saleoff: 0
*************************** 2. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
.......
子查询(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。
-
子查询可以返回标量、一行、一列或子查询。
1、使用比较运算符的子查询
=、>、<、>=、<=、<>、!=、<=>
- 语法结构
operand comparison_operator subquery
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 >= (SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods); # >=后面的括号为子查询
+----------+----------------------------------+-------------+
| 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)
- 用ANY、SOME或ALL修饰的比较运算符
operand comparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL(subquery)
mysql> SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本';
+-------------+
| goods_price |
+-------------+
| 4999.000 |
| 4299.000 |
| 7999.000 |
+-------------+
3 rows in set (0.00 sec)
mysql> SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price >= ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本'); # >= ANY 为子查询结果的最小值
+----------+----------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+----------------------------------+-------------+
| 2 | Y400N 14.0英寸笔记本电脑 | 4899.000 |
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 5 | X240(20ALA0EYCD) 12.5英寸超极本 | 4999.000 |
| 6 | U330P 13.3英寸超极本 | 4299.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 16 | PowerEdge T110 II服务器 | 5388.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 |
| 22 | HMZ-T3W 头戴显示设备 | 6999.000 |
+----------+----------------------------------+-------------+
11 rows in set (0.01 sec)
2、使用 [NOT] IN的子查询
-
语法结构
operand comparison_operator [NOT]IN(subquery)
=ANY 运算符与 IN 等效。
!=ALL 或 < >ALL 运符与 NOT IN 等效。
3、使用[NOT]EXISTS的子查询
- 如果子查询返回任何行,EXISTS将返回TRUE;否则为FALSE。
上表中品牌栏中都是固定的几个词条中的一个,能否用数字存储,对应关系用另一张表加以存储,以减少存储量?
- 创建tdb_goods_cates表 存储品牌信息
mysql> 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.02 sec)
- 查看tdb_goods中的goods_cate都有哪些?
mysql> SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
+---------------+
| goods_cate |
+---------------+
| 台式机 |
| 平板电脑 |
| 服务器/工作站 |
| 游戏本 |
| 笔记本 |
| 笔记本配件 |
| 超级本 |
+---------------+
7 rows in set (0.00 sec)
4、INSERT…SELECT
- 将查询结果写入数据表
INSERT [INTO] tbl_ name [ (col_name…) ]
SELECT……
mysql> DESC tdb_goods_cates;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| cate_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| cate_name | varchar(40) | NO | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate; # 查询结果写入数据表
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tdb_goods_cates;
+---------+---------------+
| cate_id | cate_name |
+---------+---------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 服务器/工作站 |
| 4 | 游戏本 |
| 5 | 笔记本 |
| 6 | 笔记本配件 |
| 7 | 超级本 |
+---------+---------------+
7 rows in set (0.00 sec)
5、多表更新
UPDATE table_ references
SET col_name1 = { expr1 DEFAULT }
[, col_name2 = { expr2 | DEFAULT } ]……
[ WHERE where_condition ]
- 用 tdb_goods 去更新 tdb_goods_cates 的内容:
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.01 sec)
Rows matched: 23 Changed: 23 Warnings: 0
mysql> SELECT * FROM tdb_goods\G;
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
goods_cate: 5
brand_name: 华硕
goods_price: 3399.000
is_show: 1
is_saleoff: 0
*************************** 2. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
goods_cate: 5
brand_name: 联想
goods_price: 4899.000
is_show: 1
is_saleoff: 0
......
6、CREATE……SELECT 创建时写入
- 创建数据表同时将查询结果写入到数据表
CREATE TABLE [ IF NOT EXISTS ] tbl_name
[ (create_definition, …) ]
select_statement
mysql> SELECT brand_name FROM tdb_goods GROUP BY brand_name;
+------------+
| brand_name |
+------------+
| IBM |
| 九州风神 |
| 华硕 |
| 宏碁 |
| 惠普 |
| 戴尔 |
| 索尼 |
| 联想 |
| 苹果 |
| 雷神 |
+------------+
10 rows in set (0.00 sec)
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.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
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)
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; # 更新 tdb_goods_brands 的 brand_name 到 tdb_goods中
Query OK, 23 rows affected (0.01 sec)
Rows matched: 23 Changed: 23 Warnings: 0
mysql> SELECT * FROM tdb_goods\G;
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
goods_cate: 5
brand_name: 3
goods_price: 3399.000
is_show: 1
is_saleoff: 0
*************************** 2. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
goods_cate: 5
brand_name: 8
goods_price: 4899.000
is_show: 1
is_saleoff: 0
.....
- 上面多表更新并没有修改表的结构
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.01 sec)
- 修改表结构
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.04 sec)
Records: 23 Duplicates: 0 Warnings: 0
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 | |
| 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.02 sec)