1.SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
其中,SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement),
SELECT column1 FROM t2 称为Sub Query[子查询]。
2.子查询指嵌套在查询内部,必须始终出现在圆括号内。
3.使用比较运算符的子查询
(1)SELECT AVG(goods_price) FROM tdb_goods;
AVG位聚合函数,用来查价格平均值。
(2)SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
ROUND用来指定小数点后的精确位
(3)SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=5336;
选择 goods_id,goods_name,goods_price 从tdb_goods找出大于5336的商品
(4)子查询
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price
>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);
把子查询括在()里面
4.子查询返回多个结果的时候,返回ANY,SOME,ALL来修饰
(1)ANY:符合其中一个即可
SOME:符合其中一个即可
ALL:符合全部
=:指的是查到具体的指定值,与其他连接查询
5、使用[NOT] IN 的子查询
(1)NOT ALL 也即是不能等于子查询结果的其他结果
6.查询tdb_goods表的所有记录,并且按"类别"分组
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
7.将分组结果写入到tdb_goods_cates数据表
INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
8.多表更新
(1)先从tdb_goods中获取商品的分类放进tdb_goods_cates,再把tdb_goods_cates里面的cate_name外联
到表tdb_goods_cates中.
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name //内连接
SET goods_cate = cate_id ; //这样就能实现多表更新了。
9.多表查询省去创建表的步骤
CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
)SELECT brand_name FROM tdb_goods GROUP BY brand_name;
加上别名来实现区分。
10.改完数据表之后还得改数据表的属性,用CHANHE(列出要指定的全部属性).
11在数据表存储的时候可以用多表的数字表示,但是呈现出来的时候必须是明确
的数值,因此需要使用连接,通过连接,能呈现实际的效果,但是总体上还是进行
了数据库的优化了。
12.连接
(1)一般使用ON关键字设立连接条件
使用WHERE关键字进行结果集过滤
(2)内连接:显示左表和右表符合连接条件的记录
Coding:
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g //AS g只是写这张表的别名
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;
把两个表内连接起来。
(3)左外连接:现在左边的全部记录及右表的符合连接条件的记录
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;
(4)右外连接:现在右边的全部记录及左表的符合连接条件的记录
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;
13.无限级分类表的设计
(1)eg:type_id,type_name,parent_id
其中parent_id作为分类的入口。同一个数据表对其自身的连接。
(2)SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;
在一张表中内连接查询,必须起别名,其中parent_id 对应id
(3)查找所有分类及其子类的数目
SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM
tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY
p.type_name ORDER BY p.type_id;
14.多表删除
(1)表中有一些数据是重复的,为了删除重复的可以使用多表查询
coding:
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN(SELECT goods_id,good_name //tdb_goods的别名为t1
FROM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2) AS t2 ON //把子查询出来的值命名为t2
t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id; //条件是t1=t2的名字,且id要比它大。
其中,SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement),
SELECT column1 FROM t2 称为Sub Query[子查询]。
2.子查询指嵌套在查询内部,必须始终出现在圆括号内。
3.使用比较运算符的子查询
(1)SELECT AVG(goods_price) FROM tdb_goods;
AVG位聚合函数,用来查价格平均值。
(2)SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
ROUND用来指定小数点后的精确位
(3)SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=5336;
选择 goods_id,goods_name,goods_price 从tdb_goods找出大于5336的商品
(4)子查询
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price
>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);
把子查询括在()里面
4.子查询返回多个结果的时候,返回ANY,SOME,ALL来修饰
(1)ANY:符合其中一个即可
SOME:符合其中一个即可
ALL:符合全部
=:指的是查到具体的指定值,与其他连接查询
5、使用[NOT] IN 的子查询
(1)NOT ALL 也即是不能等于子查询结果的其他结果
6.查询tdb_goods表的所有记录,并且按"类别"分组
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
7.将分组结果写入到tdb_goods_cates数据表
INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
8.多表更新
(1)先从tdb_goods中获取商品的分类放进tdb_goods_cates,再把tdb_goods_cates里面的cate_name外联
到表tdb_goods_cates中.
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name //内连接
SET goods_cate = cate_id ; //这样就能实现多表更新了。
9.多表查询省去创建表的步骤
CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
)SELECT brand_name FROM tdb_goods GROUP BY brand_name;
加上别名来实现区分。
10.改完数据表之后还得改数据表的属性,用CHANHE(列出要指定的全部属性).
11在数据表存储的时候可以用多表的数字表示,但是呈现出来的时候必须是明确
的数值,因此需要使用连接,通过连接,能呈现实际的效果,但是总体上还是进行
了数据库的优化了。
12.连接
(1)一般使用ON关键字设立连接条件
使用WHERE关键字进行结果集过滤
(2)内连接:显示左表和右表符合连接条件的记录
Coding:
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g //AS g只是写这张表的别名
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;
把两个表内连接起来。
(3)左外连接:现在左边的全部记录及右表的符合连接条件的记录
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;
(4)右外连接:现在右边的全部记录及左表的符合连接条件的记录
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;
13.无限级分类表的设计
(1)eg:type_id,type_name,parent_id
其中parent_id作为分类的入口。同一个数据表对其自身的连接。
(2)SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;
在一张表中内连接查询,必须起别名,其中parent_id 对应id
(3)查找所有分类及其子类的数目
SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM
tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY
p.type_name ORDER BY p.type_id;
14.多表删除
(1)表中有一些数据是重复的,为了删除重复的可以使用多表查询
coding:
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN(SELECT goods_id,good_name //tdb_goods的别名为t1
FROM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2) AS t2 ON //把子查询出来的值命名为t2
t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id; //条件是t1=t2的名字,且id要比它大。