——MySQL子查询与连接
1、set names gbk: 设置客户端的显示形式为gbk(如果之前设置是utf-8编码则显示出来文字不能识别)
2、子查询指嵌套在查询内部，必须在圆括号里使用，返回值是一个标量、一行也开始是一个数据表
3、子查询是指在另一个查询里面的select子句
4、在进行数据表的写入时，默认是不允许写入汉字的，因此需要修改一下编码格式：
show full columns from phplamp; 检查数据表所有字段的状态
alter table phplamp change address address varchar(100) character set utf8 collate utf8_unicode_ci not null; 修改为utf8编码格式
set names gbk;将客户端修改为gbk来显示数据字段
5、子查询语句：
select avg(goods_price)from tdb_goods;求取某一列的平均值
select round(avg(goods_price),2) from tdb_goods;求取某一列的平均值，保留两位小数
select goods_id,goods_name,goods_price from tdb_goods where goods_price>=5636.36;查找价格大于平均值的商品的id,name和price
select goods_id,goods_name,goods_price from tdb_goods where goods_price>=（select round(avg（goods_price）,2) from tdb_goods）;子查询方式
select goods_price from tdb_goods where goods_cate="超级本"; 选择输出超极本笔记本的价格
select * from tdb_goods where goods_cate="超级本"\G;列显示超极本笔记本的基本信息
6、一些子查询关键字any/some/all/not in/in/
select goods_id,goods_name,goods_price from tdb_goods where goods_price>=any(select goods_price from tdb_goods where goods_cate="超级本");如果子查询返回并非一列多个值时需要添加any,all，some
select goods_id,goods_name,goods_price from tdb_goods where goods_price=any/some(select goods_price from tdb_goods where goods_cate="超级本");大于等于，小于等于可以使用any和all,但是等于只可以用some/any
select goods_id,goods_name,goods_price from tdb_goods where goods_price not in(select goods_price from tdb_goods where goods_cate="超级本")\G;not in和！=all是等效的
select goods_cate from tdb_goods group by goods_cate;使用group by函数进行结果的分类统计
insert tdb_goods_cate(cate_name) select goods_cate from tdb_goods group by goods_cate; insert-select语句查询
7、多表更新函数
多步更新：
update tdb_goods inner join tdb_goods_cate on goods_cate=cate_name set goods_cate =cate_id; 内连接更新方式，多步更新—创键表-通过insert-select写入记录-表的更新
一步更新方式：crested select函数：
create table tdb_goods_band(
-> band_id smallint unsigned primary key auto_increment,
-> brand_name varchar(50)character set utf8 collate utf8_unicode_ci not null) 编码方式为utf8汉字可输入型
-> select brand_name from tdb_goods group by brand_name;
更新结束之后我们需要将数据列的名称和属性一起修改：
alter table tdb_goods
-> change goods_cate cate_id smallint unsigned not null,
-> change brand_name brand_id smallint unsigned not null;
将字符型修改为数字型，缩小占用空间
8、多表连接
内连接：只显示两个表符合连接条件的记录
select goods_id,goods_name,cate_name from tdb_goods inner join tdb_goods_cate on tdb_goods.cate_id=tdb_goods_cate.cate_id;
左外连接：显示左边全部记录以及右边符合条件的记录
select goods_id,goods_name,cate_name from tdb_goods left join tdb_goods_cate on tdb_goods.cate_id=tdb_goods_cate.cate_id;
右外连接：连接右表的全部记录以及左表符合条件的记录
select goods_id,goods_name,cate_name from tdb_goods right join tdb_goods_cate on tdb_goods.cate_id=tdb_goods_cate.cate_id;
多个表之间的连接（和两个表连接是相似的）
select goods_id,goods_name,cate_name,brand_name,goods_price from tdb_goods as g inner join tdb_goods_cate as c on g.cate_id=c.cate_id inner join tdb_goods_band as b on g.brand_id=b.band_id;
9、无线级分类表的设计（子结构连接）
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) character set utf8 collate utf8_unicode_ci not null,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
自身连接方式-相互嵌套
select s.type_id,s.type_name,count(p.type_name) from tdb_goods_types as s left join tdb_goods_types as p on s.parent_id=p.type_id;左表为子表
select p.type_id,p.type_name,count(s.type_name) 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;右表为父表
10、多表删除
（1）记录重复合并操作：
select goods_id,goods_name from tdb_goods group by goods_name; 以goods_name作为重复合并的字段
（2）多表删除-对照删除
delete t1 from tdb_goods as t1 left join (select goods_id,goods_name from tdb_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;

MySQL子查询与连接

原文：https://www.cnblogs.com/Yanjy-OnlyOne/p/12628552.html