数据库的一道面试题
创建表：#学生表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(sidvarchar(10)not null,cidvarchar(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;
表结构：
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);
问题及答案：
#求选择了所有课程的学生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;
页:
[1]