SQL语法练习3

-- 创建主表
DROP TABLE IF EXISTS shs_class;
CREATE TABLE shs_class (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)default charset = utf8;

-- 创建从表
DROP TABLE IF EXISTS shs_students;
CREATE TABLE shs_students (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` tinyint DEFAULT '0',
  `address` varchar(255) DEFAULT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT shs_class_id  FOREIGN KEY (class_id)  REFERENCES  shs_class(id) -- 外键约束
)default charset = utf8;

-- 插入数据
INSERT INTO shs_class (`id`, `name`) VALUES ('1', '一班');
INSERT INTO shs_class (`id`, `name`) VALUES ('2', '二班');


INSERT INTO shs_students (`id`, `name`, `age`, `address`, `class_id`) VALUES ('1', 'shs1', '18', '武汉市', '1');
INSERT INTO shs_students (`id`, `name`, `age`, `address`, `class_id`) VALUES ('2', 'shs', '23', '上海市', '2');
INSERT INTO shs_students (`id`, `name`, `age`, `address`, `class_id`) VALUES ('3', '李思', '12', '孝感市', '1');
INSERT INTO shs_students (`id`, `name`, `age`, `address`, `class_id`) VALUES ('4', '刘流', '27', '武汉市', '1');

一、联表查询练习

1.交叉连接查询 (笛卡尔积)

SELECT *FROM shs_students,shs_class;

二、内连接查询

主要分为显示内连接:SELECT * FROM A INNER JOIN B ON 条件;
和隐示内连接:SELECT * FROM A,B WHERE 条件;
2.1查询第一期所有学生
2.2查询第一期和第二期所有的学生
2.3查询每个班级下的学生总数，并且学生总数升序排列
2.4查询班级总人数>2的班级，并且人数降序排列

-- 2.1查询第一期所有学生
SELECT * FROM shs_class INNER JOIN shs_students ON shs_class.id=shs_students.class_id and shs_class.id=1;

-- 2.2查询第一期和第二期所有的学生
SELECT * FROM shs_class,shs_students WHERE shs_class.id=shs_students.class_id and shs_class.id IN (1,2);

-- 2.3查询每个班级下的学生总数并且学生总数升序排列
SELECT shs_class.`name`,COUNT(*) FROM shs_class INNER JOIN shs_students ON shs_class.id=shs_students.class_id GROUP BY shs_students.class_id ORDER BY COUNT(*);

-- 2.4查询班级总人数>2的班级，并且人数降序排列
SELECT shs_class.`name`,COUNT(*) FROM shs_class INNER JOIN shs_students ON
shs_class.id=shs_students.class_id GROUP BY shs_students.class_id HAVING
COUNT(*)>2  ORDER BY COUNT(*) DESC;

三、外连接查询

外连接:左外连接、右外连接、全外连接（union）
插入两条数据：

java
INSERT INTO shs_class (`id`, `name`) VALUES ('3', '三班');
INSERT INTO shs_students (`id`, `name`, `age`, `address`, `class_id`) VALUES ('5', '刘流', '27', '武汉市', null);

3.1 查询哪些班级是有学生 哪些班级是没有学生
3.2 查询哪些学生是有班级，哪些学生是没有班级
3.3 使用union关键字实现左连接和右连接的并集 让去重复数据

-- 3.1 查询哪些班级是有学生 哪些班级是没有学生
SELECT * FROM shs_class LEFT JOIN shs_students ON shs_class.id=shs_students.class_id ;

-- 3.2 查询哪些学生是有班级，哪些学生是没有班级
SELECT * FROM shs_class RIGHT  JOIN shs_students ON shs_class.id=shs_students.class_id;

-- 3.3 使用union关键字实现左连接和右连接的并集 让去重复数据
SELECT * FROM shs_class LEFT JOIN shs_students ON shs_class.id=shs_students.class_id UNION SELECT * FROM shs_class RIGHT  JOIN shs_students ON shs_class.id=shs_students.class_id;

总结：
左连接，以左边为准，左变有该数据，就会返回，右变没有匹配上则直接返回为null；
右连接，以右边为准，右变有该数据，就会返回，左变没有匹配上则直接返回为null；
内连接左边与右边都是必须匹配才会返回。