MySQL之进化篇

MySQL之实用篇 MySQL之牛刀小试

子查询是指出现在其他SQL语句内的SELECT子句. 例如: SELECT * FROM t1 WHERE column1 = (SELECT column2 FROM t2) 其中 SELECT * FRIN t1 称为outerQuery SELECT column2 FROM t2 称为subQuery 注意:子查询指嵌套在查询内部,且必须始终出现在圆括号内. 子查询可以包含多个关键字或者条件,如 DISTINCT , GROUP BY, ORDER BY,函数等. 子查询的外层查询可以是SELECT , INSERT, UPDATE,SET 或DO. 子查询可以返回标量,一行,一列或子查询.

###下面是我创建的goods表结构以及添加的一些数据

###子查询 ######查询商品价格的平均值

SELECT AVG(goods_price) FROM goods
复制代码

######查询商品价格的平均值并对值进行四舍五入取两位小数 SELECT ROUND(AVG(goods_price),2) FROM goods

######子查询 查询出价格大于平均值得商品 SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > ANY (SELECT goods_price FROM goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price NOT IN (SELECT goods_price FROM goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC

• AVG,MAX,MIN、COUNT、SUM为聚合函数(平均值,最大值,最小值,行数,求和)
• ROUND 是四舍五入 2代表保留两位小数
• 用ANY,SOME或ALL修饰比较运算符 (ANY,SOME 代表的满足任意一个就可以,ALL代表必须全部满足)
• [NOT] IN ,[NOT] EXISTS

多表插入

INSERT [INTO] table_name[(column_name,....)] SELECT ...

再创建一个新的表

CREATE TABLE IF NOT EXISTS goods_cates(cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, cate_name TEXT NOT NULL)
复制代码

######查询商品的分类 SELECT goods_cate FROM goods GROUP BY goods_cate ######插入所有商品的分类到goods_cate分类的表中 INSERT INTO goods_cates(cate_name) SELECT goods_cate FROM goods GROUP BY goods_cate ###多表更新 ######UPDATE table_references SET column_name1 = {expr1 | DEFAULT} [, column_name2 = {expr2 | DEFAULT}] ...... [WHERE where_condition]

for example:

UPDATE goods INNER JOIN goods_cates ON goods_cate = cate_name SET goods_cate = cate_id
复制代码

CREATE ...SELECT

######创建数据表的同时将查询结果写入到数据表 CREATE TABLE [IF NOT EXISTS] table_name [(create_definition,....)] select_statement for example:

CREATE TABLE goods_brands (brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, brand_name TEXT NOT NULL) SELECT brand_name FROM goods GROUP BY brand_name
复制代码

######再进行刚刚的多表更新 UPDATE goods INNER JOIN goods_brands ON goods.brand_name = goods_brands.brand_name SET goods.brand_name = goods_brands.brand_id ###连接 ######MySQL在SELECT 语句,多表更新,多表删除语句中支持JOIN 操作 #####语法结构 table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON conditional_expr

A表 + 连接类型{INNER JOIN | LEFT JOIN | RIGHT JOIN} + B表 ON + 连接的条件

#####数据表参照 table_reference

table_name [[AS] alias] | table_subquery [AS] alias
复制代码

• 数据表可以使用table_name AS alias_name 或 table_name alias_name 赋予别名
• table_subquery可以作为子查询使用在FROM子句中,这样的子查询必须为其赋予别名 #####连接类型
• INNER JOIN (内连接) 在MySQL中,JOIN , CROSS JOIN 和 INNER JOIN 是等价的
• LEFT [OUTER] JOIN (左外连接)
• RIGHT [OUTER] JOIN (右外连接) ######注意:使用ON关键字来设定连接条件,也可以使用WHERE来代替通常使用ON关键字来设定连接条件,使用WHERE关键字进行结果集记录的过滤 #####内连接

for example:

SELECT goods_id, goods_name, cate_name FROM goods INNER JOIN goods_cates ON goods_cate = cate_id
复制代码

###外链接

• 如果使用内连接查找的记录在连接数据表中不存在,并且在WHERE子句中尝试一下操作: column_name IS NULL 时,如果column_name被定义为NOT NULL, MySQL将在找到符合连接条件的记录后停止搜索更多的行
• A LEFT JOIN B join_condition
• 数据表B的结果集依赖数据表A
• 数据表A的结果集根据左连接条件依赖所有数据表(B表除外)
• 左外连接条件决定如何缩减数据表B(在没有指定WHERE条件的情况下)
• 如果数据表A的魔调记录符合WHERE条件,但是在数据表B不存在连接条件的记录,将会生成一个所有列为空的额外的B行 #####左外连接

for example:

SELECT goods_id, goods_name, cate_name FROM goods LEFT JOIN goods_cates ON goods_cate = cate_id
复制代码

#####右外连接

for example:

SELECT goods_id, goods_name, cate_name FROM goods RIGHT
复制代码

######多表连接 ######我们先把goods表的两个字段名称以及类型修改一下 ALTER TABLE goods CHANGE brand_name brand_id SMALLINT NOT NULL UNSIGNED ALTER TABLE goods CHANGE goods_cate cate_id SMALLINT NOT NULL UNSIGNED

for example :

SELECT goods_id,goods_name, goods_price, cate_name ,brand_name FROM goods AS good 
INNER JOIN goods_cates AS cate ON good.cate_id = cate.cate_id 
INNER JOIN goods_brands AS brand ON good.brand_id = brand.brand_id
复制代码

###无限分类的数据表设计 CREATE TABLE goods_types ( type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMEN type_name TEXT NOT NULL, parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 )

• INSERT goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);

• INSERT goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);

• INSERT goods_types(type_name,parent_id) VALUES('大家电',1);

• INSERT goods_types(type_name,parent_id) VALUES('生活电器',1);

• INSERT goods_types(type_name,parent_id) VALUES('平板电视',3);

• INSERT goods_types(type_name,parent_id) VALUES('空调',3);

• INSERT goods_types(type_name,parent_id) VALUES('电风扇',4);

• INSERT goods_types(type_name,parent_id) VALUES('饮水机',4);

• INSERT goods_types(type_name,parent_id) VALUES('电脑整机',2);

• INSERT goods_types(type_name,parent_id) VALUES('电脑配件',2);

• INSERT goods_types(type_name,parent_id) VALUES('笔记本',9);

• INSERT goods_types(type_name,parent_id) VALUES('超级本',9);

• INSERT goods_types(type_name,parent_id) VALUES('游戏本',9);

• INSERT goods_types(type_name,parent_id) VALUES('CPU',10);

• INSERT goods_types(type_name,parent_id) VALUES('主机',10);

######查询父类下边的子类 SELECT s.type_id,s.type_name,p.type_name FROM goods_types AS s LEFT JOIN goods_types AS p ON s.parent_id = p.type_id ######查父类下的父类的子类 SELECT p.type_id,p.type_name,s.type_name FROM goods_types AS p LEFT JOIN goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id

SELECT p.type_id,p.type_name,s.type_name child_count FROM goods_types AS p LEFT JOIN 
goods_types AS s ON s.parent_id = p.type_id
复制代码

###多表删除 DELETE table_name [.] [table_name[.]]..... FROM table_references [WHERE where_condition]

for example:

SELECT goods_id,goods_name FROM goods GROUP BY goods_name HAVING count(goods_name) >= 2;

DELETE t1 FROM goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM goods GROUP BY goods_name HAVING count(goods_name) >= 2) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;
复制代码