mysql之学习秘籍(六)
ategory
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;
5.4: 用友面试题
根据给出的表结构按要求写出SQL语句。
Match 赛程表
| 字段名称 | 字段类型 | 描述 |
| matchID | int | 主键 |
| hostTeamID | int | 主队的ID |
| guestTeamID | int | 客队的ID |
| matchResult | varchar(20) | 比赛结果，如（2:0） |
| matchTime | date | 比赛开始时间 |
Team 参赛队伍表
| 字段名称 | 字段类型 | 描述 |
| teamID | int | 主键 |
| teamName | varchar(20) | 队伍名称 |
Match的hostTeamID与guestTeamID都与Team中的teamID关联
查出 2006-6-1 到2006-7-1之间举行的所有比赛，并且用以下形式列出：
拜仁 2：0 不来梅 2006-6-21
mysql> select * from m;
+-----+------+------+------+------------+
| mid | hid | gid | mres | matime |
+-----+------+------+------+------------+
| 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 |
+-----+------+------+------+------------+
4 rows in set (0.00 sec)
mysql> select * from t;
+------+----------+
| tid | tname |
+------+----------+
| 1 | 国安 |
| 2 | 申花 |
| 3 | 传智联队 |
+------+----------+
3 rows in set (0.00 sec)
mysql> select hid,t1.tname as hname ,mres,gid,t2.tname as gname,matime
-> from
-> m left join t as t1
-> on m.hid = t1.tid
-> left join t as t2
-> on m.gid = t2.tid;
+------+----------+------+------+----------+------------+
| hid | hname | mres | gid | gname | matime |
+------+----------+------+------+----------+------------+
| 1 | 国安 | 2:0 | 2 | 申花 | 2006-05-21 |
| 2 | 申花 | 1:2 | 3 | 传智联队 | 2006-06-21 |
| 3 | 传智联队 | 2:5 | 1 | 国安 | 2006-06-25 |
| 2 | 申花 | 3:2 | 1 | 国安 | 2006-07-21 |
+------+----------+------+------+----------+------------+
4 rows in set (0.00 sec)
6 union查询
6.1:把ecs_comment,ecs_feedback两个表中的数据,各取出4列,并把结果集union成一个结果集.
6.2:3期学员碰到的一道面试题
A表:
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
+------+------+
B表:
+------+------+
| id | num |
+------+------+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+------+------+
mysql> # 合并 ,注意all的作用
mysql> select * from ta
-> union all
-> select * from tb;
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+------+------+
要求查询出以下效果:
+------+----------+
| id | sum(num) |
+------+----------+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
+------+----------+
参考答案:
mysql> # sum,group求和
mysql> selec