创建数据库
创建数据表
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);
写入数据
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff
)
VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
查看列
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
SET NAMES utf8;设置编码格式为utf8，不乱码！
查看数据
mysql> select * from tdb_goods;
+----------+-----------------------+------------+------------+-------------+---------+------------+
| goods_id | goods_name | goods_cate | brand_name | goods_price | is_show | is_saleoff |
+----------+-----------------------+------------+------------+-------------+---------+------------+
| 1 | R510VC 15.6英寸笔记本 | 笔记本 | 华硕 | 3399.000 | 1 | 0 |
+----------+-----------------------+------------+------------+-------------+---------+------------+
1 row in set子查询（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。
子查询可以返回标量、一行、一列或子查询。
使用比较运算符的子查询
=、>、<、>=、<=、<>、！=、<=>
语法结构
operand comparison_operator subquery
mysql> SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods; // 查询商品价格的平均值（保留两位小数）
+----------------------------+
| ROUND(AVG(goods_price), 2) |
+----------------------------+
| 3399.00 |
+----------------------------+
1 row in set
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 |
+----------+----------------------------------+-------------+
| 5 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 8 | HMZ-T3W 头戴显示设备 | 69999.000 |
+----------+----------------------------------+-------------+
2 rows in set
用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 |
+-------------+
| 7999.000 |
+-------------+
1 row in set
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 | G150TH 15.6英寸游戏本 | 8499.000 |
| 3 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 4 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 5 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 8 | HMZ-T3W 头戴显示设备 | 69999.000 |
+----------+----------------------------------+-------------+
5 rows in set使用 [NOT] IN的子查询
语法结构
operand comparison_operator [NOT]IN(subquery)
=ANY 运算符与 IN 等效。
！=ALL 或 < >ALL 运符与 NOT IN 等效。
使用[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查看tdb_goods中的goods_cate都有哪些？
mysql> SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
+------------+
| goods_cate |
+------------+
| 一体机 |
| 台式机 |
| 头戴设备 |
| 显示器 |
| 服务器 |
| 笔记本 |
| 超级本 |
+------------+
7 rows in set
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 setmysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate; # 查询结果写入数据表
Query OK, 7 rows affected
Records: 7 Duplicates: 0 Warnings: 0-> SELECT * FROM tdb_goods_cates;
+---------+-----------+
| cate_id | cate_name |
+---------+-----------+
| 1 | 一体机 |
| 2 | 台式机 |
| 3 | 头戴设备 |
| 4 | 显示器 |
| 5 | 服务器 |
| 6 | 笔记本 |
| 7 | 超级本 |
+---------+-----------+
7 rows in set
多表更新
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, 8 rows affected
Rows matched: 8 Changed: 8 Warnings: 0mysql> SELECT * FROM tdb_goods;
+----------+----------------------------------+------------+------------+-------------+---------+------------+
| goods_id | goods_name | goods_cate | brand_name | goods_price | is_show | is_saleoff |
+----------+----------------------------------+------------+------------+-------------+---------+------------+
| 1 | R510VC 15.6英寸笔记本 | 6 | 华硕 | 3399.000 | 1 | 0 |
| 2 | G150TH 15.6英寸游戏本 | 6 | 华硕 | 8499.000 | 1 | 0 |
| 3 | SVP13226SCB 13.3英寸触控超极本 | 7 | 华硕 | 7999.000 | 1 | 0 |
| 4 | iMac ME086CH/A 21.5英寸一体电脑 | 1 | 华硕 | 9188.000 | 1 | 0 |
| 5 | Mac Pro MD878CH/A 专业级台式电脑 | 2 | 华硕 | 28888.000 | 1 | 0 |
| 6 | HMZ-T3W 头戴显示设备 | 3 | 华硕 | 6999.000 | 1 | 0 |
| 7 | X3250 M4机架式服务器 2583i14 | 5 | 华硕 | 6888.000 | 1 | 0 |
| 8 | HMZ-T3W 头戴显示设备 | 4 | 华硕 | 69999.000 | 1 | 0 |
+----------+----------------------------------+------------+------------+-------------+---------+------------+
8 rows in set
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 |
+------------+
| 华硕 |
| 宏碁 |
| 惠普 |
| 戴尔 |
| 神州 |
| 索尼 |
| 联想 |
| 苹果 |
+------------+
8 rows in setmysql> 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, 8 rows affected
Records: 8 Duplicates: 0 Warnings: 0mysql> SELECT * FROM tdb_goods_brands;
+----------+------------+
| brand_id | brand_name |
+----------+------------+
| 1 | 华硕 |
| 2 | 宏碁 |
| 3 | 惠普 |
| 4 | 戴尔 |
| 5 | 神州 |
| 6 | 索尼 |
| 7 | 联想 |
| 8 | 苹果 |
+----------+------------+
8 rows in setmysql> SELECT * FROM tdb_goods;
+----------+----------------------------------+------------+------------+-------------+---------+------------+
| goods_id | goods_name | goods_cate | brand_name | goods_price | is_show | is_saleoff |
+----------+----------------------------------+------------+------------+-------------+---------+------------+
| 1 | R510VC 15.6英寸笔记本 | 6 | 华硕 | 3399.000 | 1 | 0 |
| 2 | G150TH 15.6英寸游戏本 | 6 | 联想 | 8499.000 | 1 | 0 |
| 3 | SVP13226SCB 13.3英寸触控超极本 | 7 | 宏碁 | 7999.000 | 1 | 0 |
| 4 | iMac ME086CH/A 21.5英寸一体电脑 | 1 | 惠普 | 9188.000 | 1 | 0 |
| 5 | Mac Pro MD878CH/A 专业级台式电脑 | 2 | 戴尔 | 28888.000 | 1 | 0 |
| 6 | HMZ-T3W 头戴显示设备 | 3 | 神州 | 6999.000 | 1 | 0 |
| 7 | X3250 M4机架式服务器 2583i14 | 5 | 索尼 | 6888.000 | 1 | 0 |
| 8 | HMZ-T3W 头戴显示设备 | 4 | 苹果 | 69999.000 | 1 | 0 |
+----------+----------------------------------+------------+------------+-------------+---------+------------+
8 rows in set上面多表更新并没有修改表的结构
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
修改表结构
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: 0mysql> 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
