sql语句笔记

1: 基础查询 where的练习:

1.1:主键为32的商品

select goods_id,goods_name,shop_price 
     from ecs_goods

     where goods_id=32;

1.2:不属第3栏目的所有商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where cat_id!=3;

1.3:本店价格高于3000元的商品

 select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where shop_price >3000;

1.4:本店价格低于或等于100元的商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods where shop_price <=100;

1.5:取出第4栏目或第11栏目的商品(不许用or)

select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where cat_id in (4,11);

1.6:取出100<=价格<=500的商品(不许用and)

select goods_id,cat_id,goods_name,shop_price  from ecs_goods
     where shop_price between 100 and 500;

1.7:取出名字以"诺基亚"开头的商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where goods_name like '诺基亚%';

1.8:取出名字为"诺基亚Nxx"的手机

select goods_id,cat_id,goods_name,shop_price  from ecs_goods  
   where goods_name like '诺基亚N__';

1.9:取出不在第3栏目和不在第11栏目的商品(and,或not in分别实现)

select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where cat_id!=3 and cat_id!=11;


select goods_id,cat_id,goods_name,shop_price  from ecs_goods     where cat_id not in (3,11);

1.10:取出名字不以"诺基亚"开头的商品

select goods_id,cat_id,goods_name,shop_price from ecs_goos
     where goods_name not like '诺基亚%';

1.11:取出价格大于100且小于300,或者大于4000且小于5000的商品()

select goods_id,cat_id,goods_name,shop_price  from ecs_goods where shop_price>100 and shop_price <300 or shop_price >4000 and shop_price <5000;

1.12:取出第3个栏目下面价格在1000到3000之间,并且点击量>5 "诺基亚"开头的系列商品

select goods_id,cat_id,goods_name,shop_price  from ecs_goods where 
cat_id=3 and shop_price>1000 and shop_price <3000 and click_count>5 and goods_name like '诺基亚%';


select goods_id,cat_id,goods_name,shop_price  from ecs_goods where 
shop_price between 1000 and 3000 and cat_id=3  and click_count>5 and goods_name like '诺基亚%';

1.13:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品

select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where
cat_id=3 and (shop_price <1000 or shop_price>3000) and click_count>5;

2:分组查询group:

2.1:查出最贵的商品的价格

select max(shop_price) from ecs_goods;

2.2:查出最大(最新)的商品编号

select max(goods_id) from ecs_goods;

2.3:查出最便宜的商品的价格

select min(shop_price) from ecs_goods;

2.4:查出最旧(最小)的商品编号

select min(goods_id) from ecs_goods;

2.5:查询该店所有商品的库存总量

select sum(goods_number) from ecs_goods;

2.6:查询所有商品的平均价

 select avg(shop_price) from ecs_goods;

2.7:查询该店一共有多少种商品

 select count(*) from ecs_goods;

2.8:查询每个栏目下面
最贵商品价格
最低商品价格
商品平均价格
商品库存量
商品种类
提示:(5个聚合函数,sum,avg,max,min,count与group综合运用)

select cat_id,max(shop_price) from ecs_goods  group by cat_id;

3 having与group综合运用查询:

3.1:查询该店的商品比市场价所节省的价格

select goods_id,goods_name,market_price-shop_price as j 
     from ecs_goods ;


3.2:查询每个商品所积压的货款(提示:库存*单价)

select goods_id,goods_name,goods_number*shop_price  from ecs_goods

3.3:查询该店积压的总货款

select sum(goods_number*shop_price) from ecs_goods;


3.4:查询该店每个栏目下面积压的货款.

select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;


3.5:查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)

select goods_id,goods_name,market_price-shop_price  as k from ecs_goods
where market_price-shop_price >200;


select goods_id,goods_name,market_price-shop_price  as k from ecs_goods
having k >200;


3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款

select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id
having k>20000

3.7:where-having-group综合练习题

有如下表及数据
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学    |    90 |
| 张三 | 语文    |    50 |
| 张三 | 地理    |    40 |
| 李四 | 语文    |    55 |
| 李四 | 政治    |    45 |
| 王五 | 政治    |    30 |
+------+---------+-------+


要求:查询出2门及2门以上不及格者的平均成绩

#正确思路,先查看每个人的平均成绩
mysql> select name,avg(score) from stu group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三 |    60.0000 |
| 李四 |    50.0000 |
| 王五 |    30.0000 |
| 赵六 |    99.0000 |
+------+------------+
4 rows in set (0.00 sec)


mysql> # 看每个人挂科情况
mysql> select name,score < 60 from stu;
+------+------------+
| name | score < 60 |
+------+------------+
| 张三 |          0 |
| 张三 |          1 |
| 张三 |          1 |
| 李四 |          1 |
| 李四 |          1 |
| 王五 |          1 |
| 赵六 |          0 |
| 赵六 |          0 |
| 赵六 |          0 |
+------+------------+
9 rows in set (0.00 sec)


mysql> #计算每个人的挂科科目
mysql> select name,sum(score < 60) from stu group by name;
+------+-----------------+
| name | sum(score < 60) |
+------+-----------------+
| 张三 |               2 |
| 李四 |               2 |
| 王五 |               1 |
| 赵六 |               0 |
+------+-----------------+
4 rows in set (0.00 sec)


#同时计算每人的平均分
mysql> select name,sum(score < 60),avg(score) as pj from stu group by name;
+------+-----------------+---------+
| name | sum(score < 60) | pj      |
+------+-----------------+---------+
| 张三 |               2 | 60.0000 |
| 李四 |               2 | 50.0000 |
| 王五 |               1 | 30.0000 |
| 赵六 |               0 | 99.0000 |
+------+-----------------+---------+
4 rows in set (0.00 sec)


#利用having筛选挂科2门以上的.
mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2; 
+------+------+---------+
| name | gk   | pj      |
+------+------+---------+
| 张三 |    2 | 60.0000 |
| 李四 |    2 | 50.0000 |
+------+------+---------+
2 rows in set (0.00 sec)

4: order by 与 limit查询

4.1:按价格由高到低排序

select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;


4.2:按发布时间由早到晚排序

select goods_id,goods_name,add_time from ecs_goods order by add_time;


4.3:接栏目由低到高排序,栏目内部按价格由高到低排序

select goods_id,cat_id,goods_name,shop_price from ecs_goods
     order by cat_id ,shop_price desc;


4.4:取出价格最高的前三名商品

select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;


4.5:取出点击量前三名到前5名的商品

select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;

5连接查询


5.1:取出所有商品的商品名,栏目名,价格


select goods_name,cat_name,shop_price from 
ecs_goods left join ecs_category
on ecs_goods.cat_id=ecs_category.cat_id;


5.2:取出第4个栏目下的商品的商品名,栏目名,价格


select goods_name,cat_name,shop_price from 
ecs_goods left join ecs_category
on ecs_goods.cat_id=ecs_category.cat_id
where ecs_goods.cat_id = 4;


5.3:取出第4个栏目下的商品的商品名,栏目名,与品牌名


select goods_name,cat_name,brand_name from 
ecs_goods left join ecs_category
on ecs_goods.cat_id=ecs_category.cat_id
left join ecs_brand 
on ecs_goods.brand_id=ecs_brand.brand_id
where ecs_goods.cat_id = 4;