- 数据库
- MySQL 中的SQL(3)
#### 数据准备
创建表
create table 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 default 0 not null,
is_show boolean default 1 not null,
is_saleoff boolean default 0 not null);
添加数据
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);
#### 分类
##### 使用比较运算符的子查询
`=、>、<、>=、<=、<>、!=、<=> ...`
语法结构
`operand comparison_operator subquery`
示例:
* 求所有电脑产品的平均价格,并且保留两位小数,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;
##### 用ANY、SOME或ALL修饰的比较运算符
`operand comparison_operator ANY(subquery)`
`operand comparison_operator SOME(subquery)`
`operand comparison_operator ALL(subquery)`
ANY、SOME、ALL关键字
![](/uploads/article/2017/05/31/20170531225955_6515.png)
* 查询价格大于或等于”超级本”价格的商品,并且按价格降序排列
`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;`
##### 使用[NOT]IN的子查询
语法结构
`operand comparison_operator [NOT]IN(subquery)`
=ANY运算符与IN等效。
!=ALL或<>ALL运算符与NOT IN等效。
示例:
* = 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;
##### 使用[NOT]EXISTS的子查询
如果子查询返回任何行,EXISTS将返回TRUE;否则为FALSE。
#### 连接
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
#### 多表更新
UPDATE table_references SET col_name1 = {expr1|DEFAULT} [, col_name2 = {expr2|DEFAULT}] ... [WHERE where_condition]
`table_references`的语法结构:
`{[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr`
##### 数据表参照
table_references
`tbl_name [[AS] alias]|table_subquery [AS] alias`
数据表可以使用`tbl_name AS alias_name`或`tbl_name alias_name`赋予别名。
`table_subquery`可以作为子查询使用在FROM子句中,这样的子查询必须为其赋予别名。
##### 连接类型
INNER JOIN,内连接。在MySQL中,JOIN,CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN,左外连接。
RIGHT [OUTER] JOIN,右外连接。
##### 示例:
通过tdb_goods_cates数据表来更新tdb_goods表
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
##### 多表删除
DELETE tbl_name [.*] [, tbl_name [.*]] ... FROM table_references [WHERE where_condition]
##### 内连接
显示左表及右表符合连接条件的记录。即仅显示符合连接条件的内容。
![](/uploads/article/2017/05/31/20170531230623_4370.png)
##### 外连接
A LEFT JOIN B join_condition.
数据表B的结果集依赖数据表A。
数据表A的结果集根据左连接条件依赖所有数据表(B表除外)。
左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)。
如果数据表A的某条记录符合WHERE条件,但是在数据表B不存在符合连接条件的记录,将生成一个所有列为空的额外的B行。
如果使用内连接查找的记录在连接数据表中不存在,并且在WHERE子句中尝试以下操作:col_name IS NULL时,如果col_name被定义为NOT NULL,MySQL将在找到符合连接条件的记录后停止搜索更多的行。
* 左外连接
显示左表的全部记录及右表符合连接条件的记录。
![左外链接](/uploads/article/2017/05/31/20170531230741_8157.png)
* 右外连接
显示右表的全部记录及左表符合连接条件的记录。
![右外连接](/uploads/article/2017/05/31/20170531230814_1012.png)
##### 连接条件
使用ON关键字来设定连接条件,也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件,使用WHERE关键字进行结果集记录的过滤。
##### 无限级分类表设计
无限分类的数据表设计
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);
##### 自身连接
同一个数据表对其自身进行连接。