SQL练习题集合

第一题：on后边跟and和where的区别

表结构如下：

CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `a` VALUES (1, 'a');
INSERT INTO `a` VALUES (2, 'b');
INSERT INTO `a` VALUES (3, 'c');

CREATE TABLE `b` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `b` VALUES (1, '甲');
INSERT INTO `b` VALUES (2, '乙');

SQL1：SELECT a.*,b.* FROM a LEFT JOIN b on a.id = b.id; 执行结果为：

+----+------+------+------+
| id | name | id   | name |
+----+------+------+------+
|  1 | a    |    1 | 甲   |
|  2 | b    |    2 | 乙   |
|  3 | c    | NULL | NULL |
+----+------+------+------+

SQL2：SELECT a.*,b.* FROM a LEFT JOIN b ON a.id = b.id AND b.id=2;执行结果为：

+----+------+------+------+
| id | name | id   | name |
+----+------+------+------+
|  1 | a    | NULL | NULL |
|  2 | b    |    2 | 乙   |
|  3 | c    | NULL | NULL |
+----+------+------+------+

SQL3：SELECT a.*,b.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id=2;执行结果为：

+----+------+------+------+
| id | name | id   | name |
+----+------+------+------+
|  2 | b    |    2 | 乙   |
+----+------+------+------+

小结：
1.on后面条件用and，不管条件是否成立都会把左表的数据全部展示。
2.on后面条件用where，在left join 生成的表基础上在做筛选，这时会把where中不成立的筛选掉。

第二题：查询出每门课都大于80分的学员姓名

表名：t_score

-- 方式1：
SELECT name FROM t_score GROUP BY name HAVING MIN(score) >= 80;

-- 方式2：
SELECT DISTINCT name 
FROM t_score 
WHERE name NOT IN 
(	SELECT name 
	FROM t_score 
	WHERE score < 80
);