mysql练习题-燕十八老师

-- mysql练习表（goods表+category表）
drop table goods ;
create table goods(
goods_id int not null auto_increment primary key,
goods_name varchar(32) ,
cat_id int,
brand_id int,
goods_sn varchar(55),
goods_number int,
goods_price decimal(8,2),
click_count int
)engine innodb charset utf8 ;
insert into goods values(null,'KD876 ',4,8,'ECS000000',10,1388.00,5);
-- 下面这些插入是用php处理文件生成的
insert into goods values(' 4 ',' 诺基亚N85原装充电器 ',' 8 ',' 1 ',' ECS000004 ',' 17 ',' 58.00 ',' 0 '),(' 3 ',' 诺基亚原装5800耳机 ',' 8 ',' 1 ',' ECS000002 ',' 24 ',' 68.00 ',' 3 '),(' 5 ',' 索爱原装M2卡读卡器 ',' 11 ',' 7 ',' ECS000005 ',' 8 ',' 20.00 ',' 3 '),(' 6 ',' 胜创KINGMAX内存卡 ',' 11 ',' 0 ',' ECS000006 ',' 15 ',' 42.00 ',' 0 '),(' 7 ',' 诺基亚N85原装立体声耳机HS-82 ',' 8 ',' 1 ',' ECS000007 ',' 20 ',' 100.00 ',' 0 '),(' 8 ',' 飞利浦9@9v ',' 3 ',' 4 ',' ECS000008 ',' 17 ',' 399.00 ',' 9 '),(' 9 ',' 诺基亚E66 ',' 3 ',' 1 ',' ECS000009 ',' 13 ',' 2298.00 ',' 20 '),(' 10 ',' 索爱C702c ',' 3 ',' 7 ',' ECS000010 ',' 7 ',' 1328.00 ',' 11 '),(' 11 ',' 索爱C702c ',' 3 ',' 7 ',' ECS000011 ',' 1 ',' 1300.00 ',' 0 '),(' 12 ',' 摩托罗拉A810 ',' 3 ',' 2 ',' ECS000012 ',' 8 ',' 983.00 ',' 14 '),(' 13 ',' 诺基亚5320 XpressMusic ',' 3 ',' 1 ',' ECS000013 ',' 8 ',' 1311.00 ',' 13 '),(' 14 ',' 诺基亚5800XM ',' 4 ',' 1 ',' ECS000014 ',' 4 ',' 2625.00 ',' 6 '),(' 15 ',' 摩托罗拉A810 ',' 3 ',' 2 ',' ECS000015 ',' 3 ',' 788.00 ',' 8 '),(' 16 ',' 恒基伟业G101 ',' 2 ',' 11 ',' ECS000016 ',' 0 ',' 823.33 ',' 3 '),(' 17 ',' 夏新N7 ',' 3 ',' 5 ',' ECS000017 ',' 1 ',' 2300.00 ',' 2 '),(' 18 ',' 夏新T5 ',' 4 ',' 5 ',' ECS000018 ',' 1 ',' 2878.00 ',' 0 '),(' 19 ',' 三星SGH-F258 ',' 3 ',' 6 ',' ECS000019 ',' 0 ',' 858.00 ',' 7 '),(' 20 ',' 三星BC01 ',' 3 ',' 6 ',' ECS000020 ',' 13 ',' 280.00 ',' 14 '),(' 21 ',' 金立 A30 ',' 3 ',' 10 ',' ECS000021 ',' 40 ',' 2000.00 ',' 4 '),(' 22 ',' 多普达Touch HD ',' 3 ',' 3 ',' ECS000022 ',' 0 ',' 5999.00 ',' 15 '),(' 23 ',' 诺基亚N96 ',' 5 ',' 1 ',' ECS000023 ',' 8 ',' 3700.00 ',' 17 '),(' 24 ',' P806 ',' 3 ',' 9 ',' ECS000024 ',' 148 ',' 2000.00 ',' 36 '),(' 25 ',' 小灵通/固话50元充值卡 ',' 13 ',' 0 ',' ECS000025 ',' 2 ',' 48.00 ',' 0 '),(' 26 ',' 小灵通/固话20元充值卡 ',' 13 ',' 0 ',' ECS000026 ',' 2 ',' 19.00 ',' 0 '),(' 27 ',' 联通100元充值卡 ',' 15 ',' 0 ',' ECS000027 ',' 2 ',' 95.00 ',' 0 '),(' 28 ',' 联通50元充值卡 ',' 15 ',' 0 ',' ECS000028 ',' 0 ',' 45.00 ',' 0 '),(' 29 ',' 移动100元充值卡 ',' 14 ',' 0 ',' ECS000029 ',' 0 ',' 90.00 ',' 0 '),(' 30 ',' 移动20元充值卡 ',' 14 ',' 0 ',' ECS000030 ',' 9 ',' 18.00 ',' 1 '),(' 31 ',' 摩托罗拉E8 ',' 3 ',' 2 ',' ECS000031 ',' 1 ',' 1337.00 ',' 5 '),(' 32 ',' 诺基亚N85 ',' 3 ',' 1 ',' ECS000032 ',' 1 ',' 3010.00 ',' 9 ');
update goods set goods_name = replace(goods_name,' ','');


-- where 条件查询
select * from goods where cat_id=4 or cat_id=11;
select * from goods where cat_id in (4,11);


select * from goods where goods_price >=100 and goods_price<=500;
select * from goods where goods_price between 100 and 500;
-- 取出不属于第3栏目 且 不属于第11栏目的商品(and,或not in分别实现) 
select * from goods where cat_id!=3 and cat_id!=11;
select * from goods where cat_id not in (3,11);
-- not in== and in == or
-- 取出价格大于100且小于300,或者大于4000且小于5000的商品()
select * from goods where goods_price >100 && goods_price<300 || goods_price >4000 && goods_price<5000;
-- 取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品 
select * from goods where cat_id=3 and (goods_price<1000 or goods_price>3000) and click_count >5;


-- 取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有) :1栏目是顶级，取出它下面的，也就是要取出它下级分类下面的所有商品
select * from goods where cat_id in(2,3,4);
select * from goods where goods_name like '诺基亚%';
select * from goods where goods_name like '诺基亚N__'


-- 取出名字不以”诺基亚”开头的商品 
select * from goods where goods_name not like '诺基亚%';
select * from goods where cat_id=3 and goods_price>1000 and goods_price <3000 and click_count>5 and goods_name like '诺基亚%';


-- 把good表中商品名为’诺基亚xxxx’的商品,改为’HTCxxxx’, 
-- 提示:大胆的


-- 把列看成变量,参与运算,甚至调用函数来处理 . 
-- substring(),concat()
-- 查询每个商品所积压的货款(提示:库存*单价) 
select goods_price*goods_number from goods ;
select goods_price*goods_number as jy from goods where jy >5000;
select cat_id,sum(goods_price*goods_number) s  from goods group by cat_id having s>2000;
select cat_id,sum(goods_price*goods_number) s from goods group by cat_id having sum(goods_price*goods_number)>1000 ;
-- having 用于在字段进行了运算取了别名或是用了函数，对结果进行的条件查询
select sum(goods_price*goods_number) from goods ;


update goods set goods_name = replace('诺基亚','HTC',goods_name) where goods_name like '诺基亚____';
--:接栏目由低到高排序,栏目内部按价格由高到低排序 
select * from goods order by cat_id asc ,goods_price desc;
-- 取出价格最高的前三名商品 
select * from goods order by goods_price desc limit 3;
-- :取出点击量前三名到前5名的商品
select * from goods order by click_count desc limit 2,3;


select goods_name,cat_name,goods_price from goods g left join category c on g.cat_id=c.cat_id where g.cat_id=4;
select goods_name,cat_name,goods_price from goods g left join category c on g.cat_id=c.cat_id having g.cat_id=4;
-- where==having：但字段取别名后，不能用在where中，只能用在having中。


-- 两个表关联，连接一次可以，连接多次也可以,如下
create table matchs(
id int not null auto_increment primary key,
hid int comment '关联teams表id',
gid int comment'关联teams表id',
mresult varchar(5),
mdate date
)comment '赛事表'; 
insert into matchs values('1 ',' 1 ',' 2 ','2:0',' 2006-05-21 '),('2 ',' 2 ',' 3 ','1:2',' 2006-06-21 '),('3 ',' 3 ',' 1 ','2:5',' 2006-06-25 '),('4 ',' 2 ',' 1 ','3:2',' 2006-07-21');
select * from matchs m left join teams t on m.hid=t.id ;
select m.id,t1.tname,m.mresult,t2.tname,m.mdate from matchs m left join teams t1 on m.hid=t1.id left join teams t2 on m.gid=t2.id;
 -- 查询出编号为19的商品的栏目名称(用左连接查询和子查询分别) 
select * from goods g left join category c on g.cat_id=c.cat_id where goods_id=19 ;
select g.goods_name,g.goods_id,(select cat_name from category c where g.cat_id=c.cat_id) as cat_name from goods g where goods_id=19 ;
-- 小结：要查询另一张表数据，除了表连接查询语句以外，还可以用子查询
-- 用where型子查询把goods表中的每个栏目下面最新的商品取出来 
select max(goods_id) as maxid from goods group by cat_id;  
select * from goods where goods_id in (select max(goods_id) as maxid from goods group by cat_id) ; 
-- 用from型子查询把goods表中的每个栏目下面最新的商品取出来 
select * from (select * from goods order by goods_id desc) as tmp group by cat_id;
create table teams(
id int not null auto_increment primary key,
tname varchar(15)
)comment '组队';
insert into teams values(null,'国安'),(null,'申花'),(null,'花牛');
-- 查询赛事结果
select * from matchs;


select goods_name gn from goods where gn like '%d8%';
create table category(
cat_id int not null primary key,
cat_name varchar(10),
parent_id int
)engine innodb charset utf8 ;


-- where having group 综合运用：查询挂科两门及以上的学生的平均分
create table cj(
id int not null auto_increment,
name varchar(12),
subject varchar(10),
score int(5) zerofill,
primary key(id) 
);
insert into cj values (null,'xim','math',56);
insert into cj values (null,'xim','yuwen',45);
insert into cj values (null,'xim','lishi',66);
insert into cj values (null,'xim','english',59);
insert into cj values (null,'xiaohong','math',65);
insert into cj values (null,'xiaohong','yuwen',45);
insert into cj values (null,'xiaohong','lishi',77);
insert into cj values (null,'lilie','english',88);
insert into cj values (null,'lilie','math',45);
insert into cj values (null,'lilie','yuwen',89);
insert into cj values (null,'lilie','lishi',55);
insert into cj values (null,'lilie','english',92);
select score<60 from cj; 


select sum(score<60),name from cj ;


select sum(score<60),group_concat(distinct name) from cj ;


select sum(score<60),name from cj group by name;
-- 最后查询
select name,sum(score<60) gk,avg(score) from cj group by name having gk >=2;