Mysql数据库中子查询的使用
程序员文章站
2023-12-16 11:06:40
废话不多说了,直接个大家贴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中子查询的使用就给大家介绍这么多,希望对大家有所帮助!