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); ##### 自身连接 同一个数据表对其自身进行连接。
联系我们

邮箱 626512443@qq.com
电话 18611320371(微信)
QQ群 235681453

Copyright © 2015-2022

备案号:京ICP备15003423号-3