Mysql数据库中子查询的使用

废话不多说了，直接个大家贴mysql数据库总子查询的使用。 代码如下所述：

						
废话不多说了，直接个大家贴mysql数据库总子查询的使用。
代码如下所述：

</pre><pre name="code" class="sql">1.子查询是指在另一个查询语句中的select子句。 
例句： 
select * from t1 where column1 = (select column1 from t2); 
其中，select * from t1 ...称为outer query[外查询](或者outer statement), 
select column1 from t2 称为sub query[子查询]。 
所以，我们说子查询是嵌套在外查询内部。而事实上它有可能在子查询内部再嵌套子查询。 
子查询必须出现在圆括号之间。 
行级子查询 
select * from t1 where (col1,col2) = (select col3, col4 from t2 where id = 10); 
select * from t1 where row(col1,col2) = (select col3, col4 from t2 where id = 10); 
行级子查询的返回结果最多为一行。 
优化子查询 
-- 创建数据表 
create table if not exists 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 not null default 0, 
is_show boolean not null default 1, 
is_saleoff boolean not null default 0 
); 
-- 写入记录 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('y400n 14.0英寸笔记本电脑','笔记本','联想','4899',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('x240(20ala0eycd) 12.5英寸超极本','超级本','联想','4999',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('u330p 13.3英寸超极本','超级本','联想','4299',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('svp13226scb 13.3英寸触控超极本','超级本','索尼','7999',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('ipad mini md531ch/a 7.9英寸平板电脑','平板电脑','苹果','1998',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('ipad air md788ch/a 9.7英寸平板电脑 （16g wifi版）','平板电脑','苹果','3388',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values(' ipad mini me279ch/a 配备 retina 显示屏 7.9英寸平板电脑 （16g wifi版）','平板电脑','苹果','2788',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('at7-7414lp 台式电脑 （i5-3450四核 4g 500g 2g独显 dvd 键鼠 linux ）','台式机','宏碁','3699',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('poweredge t110 ii服务器','服务器/工作站','戴尔','5388',default,default); 
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); 
insert tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) values('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',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); 
-- 求所有电脑产品的平均价格,并且保留两位小数，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; 
-- 查询类型为“超记本”的商品价格 
select goods_price from tdb_goods where goods_cate = '超级本'; 
-- 查询价格大于或等于"超级本"价格的商品，并且按价格降序排列 
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; 
-- = 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; 
-- 创建“商品分类”表 
create table if not exists tdb_goods_cates( 
cate_id smallint unsigned primary key auto_increment, 
cate_name varchar(40) 
); 
-- 查询tdb_goods表的所有记录，并且按"类别"分组 
select goods_cate from tdb_goods group by goods_cate; 
-- 将分组结果写入到tdb_goods_cates数据表 
insert tdb_goods_cates (cate_name) select goods_cate from tdb_goods group by goods_cate; 
-- 通过tdb_goods_cates数据表来更新tdb_goods表 
update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name 
set goods_cate = cate_id ; 
-- 通过create...select来创建数据表并且同时写入记录 
-- select brand_name from tdb_goods group by brand_name; 
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; 
-- 通过tdb_goods_brands数据表来更新tdb_goods数据表(错误) 
update tdb_goods inner join tdb_goods_brands on brand_name = brand_name 
set brand_name = brand_id; 
-- column 'brand_name' in field list is ambigous 
-- 正确 
update tdb_goods as g inner join tdb_goods_brands as b on g.brand_name = b.brand_name 
set g.brand_name = b.brand_id; 
-- 查看tdb_goods的数据表结构 
desc tdb_goods; 
-- 通过alter table语句修改数据表结构 
alter table tdb_goods 
change goods_cate cate_id smallint unsigned not null, 
change brand_name brand_id smallint unsigned not null; 
-- 分别在tdb_goods_cates和tdb_goods_brands表插入记录 
insert tdb_goods_cates(cate_name) values('路由器'),('交换机'),('网卡'); 
insert tdb_goods_brands(brand_name) values('海尔'),('清华同方'),('神舟'); 
-- 在tdb_goods数据表写入任意记录 
insert tdb_goods(goods_name,cate_id,brand_id,goods_price) values(' laserjet pro p1606dn 黑白激光打印机','12','4','1849'); 
-- 查询所有商品的详细信息(通过内连接实现) 
select goods_id,goods_name,cate_name,brand_name,goods_price from tdb_goods 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\g; 
-- 查询所有商品的详细信息(通过左外连接实现) 
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\g; 
-- 查询所有商品的详细信息(通过右外连接实现) 
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\g; 
-- 无限分类的数据表设计 
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); 
-- 查找所有分类及其父类 
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; 
-- 查找所有分类及其子类 
select p.type_id,p.type_name,s.type_name from tdb_goods_types as p left join tdb_goods_types as s on s.parent_id = p.type_id; 
-- 查找所有分类及其子类的数目 
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; 
-- 为tdb_goods_types添加child_count字段 
alter table tdb_goods_types add child_count mediumint unsigned not null default 0; 
-- 将刚才查询到的子类数量更新到tdb_goods_types数据表 
update tdb_goods_types as t1 inner join ( 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 ) as t2 
on t1.type_id = t2.type_id 
set t1.child_count = t2.children_count; 
-- 复制编号为12,20的两条记录 
select * from tdb_goods where goods_id in (19,20); 
-- insert ... select实现复制 
insert tdb_goods(goods_name,cate_id,brand_id) select goods_name,cate_id,brand_id from tdb_goods where goods_id in (19,20); 
-- 查找重复记录 
select goods_id,goods_name from tdb_goods group by goods_name having count(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中子查询的使用就给大家介绍这么多，希望对大家有所帮助！