1.数学函数
有如下表和数组
把num值处于[20,29]之间,改为20
num值处于[30,39]之间的,改为30
UPDATE mian SET num = FLOOR(num/10)*10 WHERE num BETWEEN 20 AND 39;
2.字符串
把good表中商品名为'诺基亚xxxx'的商品,改为'HTCxxxx',
提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .
substring(),concat()
SELECT goods_id,goods_name,CONCAT('htc',SUBSTRING(goods_name,4)) FROM goods WHERE goods_name LIKE'诺基亚%';
3.where-having-group综合练习题
要求:查询出2门及2门以上不及格者的平均成绩
SELECT * FROMresult;SELECT *,AVG(score) AS pjf FROM result GROUP BY NAME; --显示所有人的平均分
SELECT *,score < 60 FROM result; --比较运算
SELECT NAME,SUM(score < 60) AS gk,AVG(score) AS pj FROM result GROUP BY NAME; --统计每个人的挂科数
--最终的查询语句
SELECT NAME,SUM(score < 60) AS gk,AVG(score) AS pj FROM result GROUP BY NAME HAVING gk>=2;
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 | 公益联队 |
SELECT m.mid,t1.`tname` AS hname,m.`mres`,t2.`tname` AS gname, m.`matime` FROM m INNER JOIN t AS t1 ON m.`hid` =t1.`tid`INNER JOIN t AS t2 ON m.`gid` = t2.`tid` WHERE m.`matime` BETWEEN '2006-06-01' AND '2006-07-01';
5.子查询和合并
A表:
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
+------+------+
B表:
+------+------+
| id | num |
+------+------+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+------+------+
要求查询出以下效果:
+------+----------+
| id | num |
+------+----------+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
+------+----------+
SELECT id,SUM(num) FROM (SELECT * FROM a UNION ALL SELECT * FROM b) AS temp GROUP BY id;