select students.sname, subjects.stitle, scores.score
from scores
inner join students on scores.stuid = students.id
inner join subjects on scores.subid = subjects.id;
### 如下
+-------+--------+-------+
| sname | stitle | score |
+-------+--------+-------+
| Gage | 语文 | 90.00 |
+-------+--------+-------+
查询每个学生的平均成绩
select students.sname, avg(scores.score)
from scores
left join students on scores.stuid = students.id
group by students.sname;
返回右表中的所有数据,即使左表中没有匹配的数据
对于省市区表,我们可以采用一对多的形式建立三张表,但这样会带来不必要开销,分析后发现 省份无上级,我们建立三个字段,id为省份id, pid为市区id,这样只要让pid=id就可以使省市区关联起来
定义数据表
查询山东省下的所以市区
查询省市区
对于复杂的一些查询,每次书写查询语句相当麻烦。我们可以定义视图
mysql> select sname as 姓名,
-> (select scores.score from scores inner join subjects on subjects.id=scores.subid where subjects.stitle="语文" and students.id=scores.stuid) as 语文,
-> (select scores.score from scores inner join subjects on subjects.id=scores.subid where subjects.stitle="数学" and students.id=scores.stuid) as 数学
-> from students;
+--------+--------+--------+
| 姓名 | 语文 | 数学 |
+--------+--------+--------+
| Gage | 90.00 | 54.00 |
| sss | NULL | NULL |
| kksk | NULL | NULL |
+--------+--------+--------+
select sname as 姓名,
(select scores.score from scores inner join subjects on subjects.id=scores.subid where subjects.stitle="数学" and students.id=scores.stuid and score>85) as 数学
from students;
+--------+--------+
| 姓名 | 数学 |
+--------+--------+
| Gage | 98.00 |
| sss | 86.00 |
| kksk | NULL |
+--------+--------+
使用in子查询:表示该操作数(字段值)等于该子查询的任意一个值就满足条件
返回所有以小开头的商品id
select pid from product where pid in
(select pid from product where pname like '小%');
返回以小开头的商品
select * from product where pid=any
(select pid from product where pname like '小%');
表示该操作数的值必须跟列子查询的所有值都满足给定的比较运算,才算满足了条件
返回商品最高的哪个商品
如果该子查询有结果数据(无论什么数据,只要大于等于1行),则为true,否则为false
select * from product where exists
(select pid from product where pname like 'p%');