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关键字

• 查询价格大于或等于”超级本”价格的商品，并且按价格降序排列 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]

内连接

显示左表及右表符合连接条件的记录。即仅显示符合连接条件的内容。

外连接

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将在找到符合连接条件的记录后停止搜索更多的行。

• 左外连接 显示左表的全部记录及右表符合连接条件的记录。
• 右外连接 显示右表的全部记录及左表符合连接条件的记录。

连接条件

使用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);

自身连接

同一个数据表对其自身进行连接。