创建表
DROP TABLE IF EXISTS student; CREATE TABLE student( sno int auto_increment primary key, sname varchar(8), ssex varchar(3), sage int , sclass varchar(6) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO student(sname,ssex,sage,sclass) VALUES('李勇','男',20,'y01'); INSERT INTO student(sname,ssex,sage,sclass) VALUES('刘晨','男',21,'y02'); INSERT INTO student(sname,ssex,sage,sclass) VALUES('王敏','女',19,'y02'); INSERT INTO student(sname,ssex,sage,sclass) VALUES('张力','男',25,'y05'); DROP TABLE IF EXISTS course; CREATE TABLE course( cno int auto_increment primary key, cname varchar(20), ccredit int ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO course(cname,ccredit) VALUES('C语言',5); INSERT INTO course(cname,ccredit) VALUES('数据库',5); INSERT INTO course(cname,ccredit) VALUES('开发模式_VB',5); DROP TABLE IF EXISTS sc; CREATE TABLE sc( sno int references student(sno) on delete cascade, cno int references course(cno) on delete cascade, grade int ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO sc VALUES(1,1,90); INSERT INTO sc VALUES(1,2,95); INSERT INTO sc VALUES(2,1,55); INSERT INTO sc VALUES(4,3,null);
1、查出全体同学的学号、姓名 select sno,sname from student; 2、查询全体同学的姓名学号班级（按顺序输出） select sname,sno,sclass from student; 3、查询全体同学的记录（显示所有行） select * from student; 4、查询全体同学的姓名及出生年份 select sname,(2010-sage) as '出生年份' from student ; 5、查询全体同学姓名出生年份班级（班级要用小写字母lower函数） select sname,2010-sage as '出生年份',lower(sclass) from student; 6、查询全体同学的姓名/出生年份/所在班级列为YearOfBirth select sname,2010-sage YearOfBirth,sclass from student; 7、查询选中学员的所在班级并且去掉重复行用distinct select distinct sclass from student; 8、查询Y02班全体同学名单 select * from student where sclass='y02'; 9、查询所有年龄在20岁一下的同学姓名及年龄 select sname,sage from student where sage<20; 10、查询考试不及格的同学的姓名及成绩 select s.sname,sc.grade from student s,sc where s.sno = sc.sno and sc.grade <60; 11、查询年龄在19－20岁（包括19、20）之间的同学姓名、班级、年龄 select sname,sclass,sage from student where sage in (19,20);（19、20 比较特殊选项少可以使用 in） select sname,sclass,sage from student where sage between 19 and 20; select sname,sclass,sage from student where sage>=19 and sage<=20; 12、查询年龄不在19－20岁之间的同学姓名、班级、年龄 select sname,sclass,sage from student where sage not in (19,20); select sname,sclass,sage from student where sage not between 19 and 20; select sname,sclass,sage from student where sage<19 or sage>20; 13、查询y02班级和y05班的同学的姓名、性别 select sname,sclass from student where sclass in ('y02','y05'); select sname,sclass from student where sclass='y02' or sclass='y05'; 14、查询不是y02或者y05班的同学的姓名、性别 select sname,sage from student where sclass not in('y02','y05'); select sname,sage from student where sclass!='y02' and sclass!='y05'; select sname,sage from student where not sclass='y02' and not sclass='y05'; 15、查所有姓刘的同学的姓名、学号、性别( " % "表示一个或者多个，" _ "表示只占一个字符) select sname,sno,ssex from student where sname like'刘%'; 16、查所有姓张且全名只有2个汉子的同学的所有信息 select * from student where sname like'张_'; 17、某些学生未考试查缺成绩的同学的学号和课程号 select sno,cno,grade from sc where grade is null; 18、查询所有成绩的同学的学号、课程号和成绩 select sno,cno,grade from sc where grade is not null; 19、查y02班年龄在20岁一下的姓名和年龄 select sname,sage from student where sclass='y02' and sage<20; 20、查选修1号课程的同学的学号和成绩，按成绩降序排序 select sno,grade from sc where cno=1 order by grade desc; 21、查全体同学信息查询结果按所在班级的班级名称按降序排列，同班同学按年龄升序排列 select * from student order by sclass desc,sage asc; 22、查询学员的总人数 select count(sno) from student; 23、查选修课程学院人数 select count(*) from sc; 24、统计1号课的学院平均成绩 select avg(grade) 平均成绩 from sc where cno=1; 25、查选修1号课和同学最高成绩 select max(grade) from sc where cno=1; 26、求各个课程号及相应选课人数 select cno,count(*) 选课人数 from sc group by cno; 27、查选取1门以上课程的同学学号和课程个数 select sno,count(cno) from sc group by sno having count(cno)>1; 28、查每个学员及其选修课程情况 select sno,cno from sc; 29、查每个学员及其选修课程情况对没有选课的也要输出其姓名、学号、性别、班级(注意：是作外连接student是主表) select st.sname,st.sno,st.ssex,st.sclass,sc.cno,sc.grade from student st left join sc on st.sno = sc.sno order by st.sname; 30、查选取2号课程且成绩在90分以上的同学 select * from sc where cno=2 and grade>90; 31、查询每个同学学号姓名，选课程名称及其成绩 select stu.sno,stu.sname,c.cname,sc.grade from student stu join sc on stu.sno = sc.sno join course c on c.cno = sc.cno; 或者 select stu.sno,stu.sname,c.cname,sc.grade from student stu,course c,sc where stu.sno=sc.sno and c.cno= sc.cno order by sc.cno desc; 32、查与刘晨在一个班的同学 select * from student where sclass=(select sclass from student where sname='刘晨'); 33、选取C语言的同学学号和姓名 select s.sno,s.sname from student s join sc on s.sno=sc.sno join course c on sc.cno=c.cno where c.cname='C语言'; 或者 select sno,sname from student where sno in (select sno from sc where cno in (select cno from course where cname='C语言')); 34、查其他班级中比y02班某一同学大的同学姓名和年龄 select sname,sage from student where sclass<>'y02' and sage > (select min(sage) from student where sclass='y02'); 或者（加入 any 关键字） select sname,sage from student where sclass<>'y02' and sage >any (select sage from student where sclass='y02'); 35、查其他班中比y02班同学全部都大的同学姓名和年龄 select sname,sage from student where sclass!='y02' and sage > (select max(sage) from student where sclass='y02'); 或者（加入 all 关键字） select sname,sage from student where sclass!='y02' and sage >all (select sage from student where sclass='y02'); 36、查选取1号课程的学员的姓名 （在与查询的集合的包含关系时，最好使用 in 、any、all） select s.sname from student s,sc where s.sno = sc.sno and sc.cno=1; 或者（子查询） select sname from student where sno in (select sno from sc where cno=1); 37、查没有选取1号课程的学员的姓名(注意：要过滤已经选取过1号的同学，因为可能某同学多选修课程) 说明：如果是过滤集合，就只能用 in/all/any select sname from student where sno not in (select sno from sc where cno=1); 38、查y02班同学及年龄不大于19岁的学员（union） select * from student where sclass='y02' union select * from student where sage<=19; 解析：y02班级的同学与全年纪年龄不大于19岁的学生，而（sage<=19 and sclass='y02'）表示为 y02班级同学“且”年龄不大于19岁的同学，与题意不符。所以下面的语句是错误的，因为范围没有确定好 select * from student where sclass='y02' and sage<=19; 说明：union 用来两个集合求并集（合并两个集合，并且除去重复的记录） 39、查询选取1号课程或者2号课程的同学学号 select distinct sno from sc where cno=1 or cno=2; 或者 select distinct sno from sc where cno in (1,2); 40、将4号学员的年龄改为23岁 update student set sage=23 where sno=4; 41、将所有同学的年龄增加1岁 update student set sage=sage+1; 42、y02班的同学的成绩改为100 update sc set grade=100 where sno in (select sno from student where sclass='y02'); 43、删除学号为1的同学记录 delete from student where sno=1;
