Lintcode SQL 教程 LEVEL 4

约束

2084 · 对课程表添加主键约束

ALTER TABLE courses ADD PRIMARY KEY (id);

2085 · 删除课程表的主键约束

ALTER TABLE courses DROP PRIMARY KEY;

2091 · 对课程表添加外键约束

ALTER TABLE courses ADD FOREIGN KEY (teacher_id) REFERENCES teachers(id);

联结

2062 · 查询指定老师教的所有课程的编号和名称

SELECT c.id, c.name AS course_name, t.name AS teacher_name
FROM courses c
INNER JOIN teachers t ON c.teacher_id = t.id
where t.name=‘Eastern Heretic’

2051 · 查询来自中国的教师名称以及所教课程名称

SELECT c.name AS course_name, t.name AS teacher_name
FROM teachers t
LEFT JOIN courses c ON c.teacher_id = t.id where country = ‘CN’;

2053 · 查询来自中国的教师名称，邮箱以及所教课程名称

SELECT c.name AS course_name, t.name AS teacher_name,t.email AS teacher_email
FROM courses c
right JOIN teachers t ON c.teacher_id = t.id where country = ‘CN’;

2055 · 查询所有课程名称以及与其相互对应的教师名称和国籍

SELECT c.name AS course_name, t.name AS teacher_name,t.country AS teacher_country
FROM courses c
LEFT JOIN teachers t ON c.teacher_id = t.id
UNION
SELECT c.name AS course_name, t.name AS teacher_name,t.country AS teacher_country
FROM courses c
RIGHT JOIN teachers t ON c.teacher_id = t.id;

参考链接