MySQL练习（一）
USE test;
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
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 写入记录
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）','平板电脑','苹果','3388',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）','平板电脑','苹果','2788',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
以上是我们的实验数据
SET NAMES utf8
在查询时可能会出现中文乱码现象，设置查询结果的编码可以解决问题。
子查询（sub query）
- 子查询是嵌套在查询的内部，且始终必须出现在圆括号内，
- 子查询可以包括多个关键字或条件 ，
- 自查询的外查询可以是;
SELECTINSERT,UPDATE,SETDO. - 子查询可以返回标量、一行、一列、或者子查询
使用比较运算符的�子查询
SELECT AVG(goods_price) FROM tdb_goods;
求tdb_goods表的goods_price 字段的平均值
+------------------+
| AVG(goods_price) |
+------------------+
| 5636.3636364 |
+------------------+
SELECT ROUND(AVG(goods_price)) FROM tdb_goods;
求tdb_goods表的goods_price 字段的平均值,再进行四舍五入
+-------------------------+
| ROUND(AVG(goods_price)) |
+-------------------------+
| 5636 |
+-------------------------+
SELECT goods_id ,goods_name FROM tdb_goods WHERE goods_price >= 5636;
求tdb_goods表中���goods_price 大于5636的��货物的goods_id和goods_name
+----------+-----------------------------------------+
| goods_id | goods_name |
+----------+-----------------------------------------+
| 3 | G150TH 15.6英寸游戏本 |
| 7 | SVP13226SCB 13.3英寸触控超极本 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 |
| 18 | HMZ-T3W 头戴显示设备 |
| 20 | X3250 M4机架式服务器 2583i14 |
| 21 | HMZ-T3W 头戴显示设备 |
+----------+-----------------------------------------+
以上两句可以合写为：
SELECT goods_id ,goods_name FROM tdb_goods WHERE goods_price >=(SELECT AVG(goods_price) FROM tdb_goods);
SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本';
查找求tdb_goods 表中 goods_cate为� 超极本货物的goods_price
+-------------+
| goods_price |
+-------------+
| 4999.000 |
| 4299.000 |
| 7999.000 |
+-------------+
在做子查询的时候肯会出现子查询语句返回多个结果比如下面的语句：
SELECT goods_id ,goods_name FROM tdb_goods WHERE goods_price >=(SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本');
查询所有价格大于超极本的笔记本电脑。此MySQL会报错：
ERROR 1242 (21000): Subquery returns more than 1 row
就是子查询返回了多行
�此时就需要以下的关键字进行�操作：
| 运算符 | ANY | SOME | ALL |
|---|---|---|---|
| >、>= | 最小值 | 最小值 | 最大值 |
| <、<= | 最大值 | 最大值 | 最小值 |
| = | 任意值 | 任意值 | —— |
| < > != | —— | —— | 任意值 |
SELECT goods_name ,goods_price FROM tdb_goods WHERE goods_price >= ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本');
这句SQL语句加入ANY关键字其意义在于：
goods_price大于子查询的结果中任意一个�值的。换句话说就是�大于子查询中的最小值
+-----------------------------------------+-------------+
| goods_name | goods_price |
+-----------------------------------------+-------------+
| Y400N 14.0英寸笔记本电脑 | 4899.000 |
| G150TH 15.6英寸游戏本 | 8499.000 |
| X240(20ALA0EYCD) 12.5英寸超极本 | 4999.000 |
| U330P 13.3英寸超极本 | 4299.000 |
| SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| PowerEdge T110 II服务器 | 5388.000 |
| Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| HMZ-T3W 头戴显示设备 | 6999.000 |
| X3250 M4机架式服务器 2583i14 | 6888.000 |
| HMZ-T3W 头戴显示设备 | 6999.000 |
+-----------------------------------------+-------------+
SELECT goods_name ,goods_price FROM tdb_goods WHERE goods_price >= SOME (SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本');
+-----------------------------------------+-------------+
| goods_name | goods_price |
+-----------------------------------------+-------------+
| Y400N 14.0英寸笔记本电脑 | 4899.000 |
| G150TH 15.6英寸游戏本 | 8499.000 |
| X240(20ALA0EYCD) 12.5英寸超极本 | 4999.000 |
| U330P 13.3英寸超极本 | 4299.000 |
| SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| PowerEdge T110 II服务器 | 5388.000 |
| Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| HMZ-T3W 头戴显示设备 | 6999.000 |
| X3250 M4机架式服务器 2583i14 | 6888.000 |
| HMZ-T3W 头戴显示设备 | 6999.000 |
+-----------------------------------------+-------------+
以下语句效果等效：
| X | X |
|---|---|
| =ALL | IN |
| !=ALL 或< > ALL | NOT IN |
等于全部换句话说就是在集合中。
不等于全部、大于或小于全部 就是不在集合中
以下两句等效：
SELECT goods_name ,goods_price FROM tdb_goods WHERE goods_price = ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本');
SELECT goods_name ,goods_price FROM tdb_goods WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本');
查询tdb_goods表中goods_cate为超级本的记录的goods_name和goods_price。
+---------------------------------------+-------------+
| goods_name | goods_price |
+---------------------------------------+-------------+
| X240(20ALA0EYCD) 12.5英寸超极本 | 4999.000 |
| U330P 13.3英寸超极本 | 4299.000 |
| SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
+---------------------------------------+-------------+
接下来将提取所有的笔记本类型创建新的表，建立外键连接
CREATE TABLE IF NOT EXISTS tdb_good_cates( cate_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , cate_name VARCHAR(40) NOT NULL );
创建新表用来存放cates字段
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
查询tdb_goods表所有goods_cate的值
+---------------------+
| goods_cate |
+---------------------+
| 台式机 |
| 平板电脑 |
| 服务器/工作站 |
| 游戏本 |
| 笔记本 |
| 笔记本配件 |
| 超级本 |
+---------------------+
DESC tdb_good_cates;
查看表的键属性
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| cate_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| cate_name | varchar(40) | NO | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
INSERT tdb_good_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
可能会出现插入错误需要修改表的字符集
ALTER TABLE tdb_good_cates CONVERT TO CHARACTER SET utf8;
show create table tablename;
输出建表语句
将tdb_goods表中所有的goods_cate记录插入在tdb_good_cates表中的cate_name字段。
SELECT *FROM tdb_good_cates;
+---------+---------------------+
| cate_id | cate_name |
+---------+---------------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 服务器/工作站 |
| 4 | 游戏本 |
| 5 | 笔记本 |
| 6 | 笔记本配件 |
| 7 | 超级本 |
+---------+---------------------+
表的连接形式：
| 关键字 | 意义 |
|---|---|
| INNER JOIN | 内连接 |
| LEFT[OUTER]JOIN | 左外连接 |
| RIGHT[OUTER]JOIN | 右外连接 |
UPDATE tdb_goods INNER JOIN tdb_good_cates ON goods_cate = cate_name SET goods_cate = cate_id;
将两张表连接为内连接形式 并将cate_id赋值给goods_cate
这时我们查看tdb_goods表
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
*************************** 3. row ***************************
goods_id: 3
goods_name: G150TH 15.6英寸游戏本
goods_cate: 4
brand_name: 雷神
goods_price: 8499.000
is_show: 1
is_saleoff: 0
*************************** 4. row ***************************
goods_id: 4
goods_name: X550CC 15.6英寸笔记本
goods_cate: 5
brand_name: 华硕
goods_price: 2799.000
is_show: 1
is_saleoff: 0
*************************** 5. row ***************************
goods_id: 5
goods_name: X240(20ALA0EYCD) 12.5英寸超极本
goods_cate: 7
brand_name: 联想
goods_price: 4999.000
is_show: 1
is_saleoff: 0
*************************** 6. row ***************************
...............
字段已经全部更改
查看表结构
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 | |
+-------------+------------------------+------+-----+---------+----------------+
但是此时的goods_cate 字段确还是varchar类型，这时因该是整形的，字符个整形所占空间差距还是很大的所以我们可以修改表的字段属性
ALTER TABLE tdb_goods CHANGE goods_cate cat_id SMALLINT UNSIGNED NOT NULL;
这就ok了
使用相同的操作对brand_name 进行修改。
+-------------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| goods_name | varchar(150) | NO | | NULL | |
| cat_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 | |
+-------------+------------------------+------+-----+---------+----------------+
