数据库的一道面试题

[color=red]创建表：[/color]

#学生表
create table if not exists s
(
   id                        varchar(10)   primary key        not null,
   name                      varchar(20)                      not null
);

#课程表
create table if not exists c
(
   id                        varchar(10)   primary key        not null,
   name                      varchar(20)                      not null
);

#SC表
create table if not exists sc
(
	sid		varchar(10)		not null,
	cid		varchar(10)		not null
);

#插入数据
insert into s(id, name) values(1, 's1');
insert into s(id, name) values(2, 's2');
insert into s(id, name) values(3, 's3');
insert into s(id, name) values(4, 's4');
insert into s(id, name) values(5, 's5');
commit;

insert into c(id, name) values(6, 'c1');
insert into c(id, name) values(7, 'c2');
insert into c(id, name) values(8, 'c3');
insert into c(id, name) values(9, 'c4');
insert into c(id, name) values(10, 'c5');
commit;

insert into sc(sid, cid) values(1, 6);
insert into sc(sid, cid) values(1, 7);
insert into sc(sid, cid) values(1, 8);
insert into sc(sid, cid) values(1, 9);
insert into sc(sid, cid) values(1, 10);
insert into sc(sid, cid) values(2, 7);
insert into sc(sid, cid) values(2, 8);
insert into sc(sid, cid) values(2, 9);
insert into sc(sid, cid) values(2, 6);
insert into sc(sid, cid) values(3, 6);
insert into sc(sid, cid) values(3, 8);
insert into sc(sid, cid) values(4, 9);
insert into sc(sid, cid) values(5, 6);
insert into sc(sid, cid) values(5, 7);
insert into sc(sid, cid) values(5, 8);
insert into sc(sid, cid) values(5, 9);
insert into sc(sid, cid) values(5, 10);
insert into sc(sid, cid) values(5, 10);
commit;

[color=red]表结构：[/color]

select * from s;
+----+------+
| id | name |
+----+------+
| 1  | s1   |
| 2  | s2   |
| 3  | s3   |
| 4  | s4   |
| 5  | s5   |
+----+------+

select * from c;
+----+------+
| id | name |
+----+------+
| 10 | c5   |
| 6  | c1   |
| 7  | c2   |
| 8  | c3   |
| 9  | c4   |
+----+------+

mysql> select * from sc;
+-----+-----+
| sid | cid |
+-----+-----+
| 1   | 6   |
| 1   | 7   |
| 1   | 8   |
| 1   | 9   |
| 1   | 10  |
| 2   | 7   |
| 2   | 8   |
| 2   | 9   |
| 2   | 6   |
| 3   | 6   |
| 3   | 8   |
| 4   | 9   |
| 5   | 6   |
| 5   | 7   |
| 5   | 8   |
| 5   | 9   |
| 5   | 10  |
| 5   | 10  |
+-----+-----+

#更新数据：
update s set id=5 limit 1;
update s set id=5 where name = c1;

#删除数据：
#MySQL允许使用delete和truncate语句删除数据
delete from s where id = 5;
delete from s where id in (4, 5);

[color=red]问题及答案：[/color]

#求选择了所有课程的学生
select * from s where(select count(*) from sc where sc.sid = s.id) = (select count(*) from c);

#求选择了3门以上课程的学生
select * from s where(select count(*) from sc where sc.sid = s.id) > 3;

#求所有学生的所有课程
select s.name, c.name from sc join s on(s.id = sc.sid) join c on(c.id = sc.cid);
select s.name, c.name from s, c, sc where s.id = sc.sid and c.id = sc.cid;