mysql---多表查询

判断数据表中的分数是否及格：

mysql> select sum(if(num>=60,1,0)) 及格,sum(if(num<60,1,0)) 不及格 from t5;
+------+--------+
| 及格 | 不及格 |
+------+--------+
|    5 |      3 |
+------+--------+

多表联合查询---左连接

mysql> select t4.name  ban, if(count(t3.class),count(t3.class),'无') name from t4 left join t3 on t3.class=t4.id group by t4.id;
+------+------+
| ban  | name |
+------+------+
| 一班 | 2    |
| 二班 | 2    |
| 三班 | 3    |
| 四班 | 无   |
| 五班 | 无   |
+------+------+

统计个数

mysql> select *,count(*) num from t3 group by class;
+----+------+-------+-----+
| id | name | class | num |
+----+------+-------+-----+
|  1 | a    | 1     |   2 |
|  2 | b    | 2     |   2 |
|  3 | c    | 3     |   3 |
+----+------+-------+-----+

嵌套查询：

select * from user where id in( select max(id) from user)

多表查询：

select user.name,class.id from user,class where user.class_id=class.id