~~~ create database if not exists stu default character set utf8; use stu; create table if not exists s_student( sno int unsigned key, sname varchar(250), ssex enum('男','女','保密') default '保密', sbirthday int, class varchar(50) )engine=innodb charset=utf8; create table if not exists s_course( cno text, cname varchar(250), tno int )engine=innodb; create table if not exists s_score( sno int unsigned key, cno text, degree tinyint )engine=innodb; create table if not exists s_teacher( tno int unsigned key, tname varchar(250) not null, tssex enum('男','女','保密') default '保密', tbirthday int, title varchar(250), depart varchar(250) )engine=innodb; -- 一、每张表使用SQL语句插入至少10条数据。 insert s_student(sno,sname,ssex,sbirthday,class) values(0001,'neo1','男','20010101',95033), (0002,'王neo2','女','20010102',95031), (0003,'neo3','男','20010103',95033), (0004,'王neo4','女','20010104',95033), (0005,'neo5','男','20010105',95031), (0006,'王neo6','女','20010106','4A'), (0007,'neo7','男','20010107','1A'), (0008,'neo8','女','20010108','3A'), (0009,'neo9','男','20010109',95031), (0010,'neo10','女','20010110','1A'); insert s_course(cno,cname,tno) values(201601,'电脑1',201501), (201602,'计算机导论',201503), ('3-102','计算机导论123',201503), (201604,'电脑4',201501), (201605,'计算机导论234',201501), (201606,'计算机导论345',201503), (201607,'电脑7',201503), (201608,'电脑8',201503), (201609,'电脑9',201503), (201610,'电脑10',201502); insert s_score(sno,cno,degree) values(0001,'3-102',75), (0002,'3-102',85), (0003,'201602',70), (0004,'3-105',86), (0005,'3-102',90), (0006,201602,50), (0007,'3-102',60), (0008,'3-105',70), (0009,'3-102',80), (0010,'3-102',90); insert s_teacher(tno,tname,tssex,tbirthday,title,depart) values(201501,'老师1','男',19910101,'教职员1','IT组'), (201502,'老师2','女',19910102,'教职员2','总务组'), (201503,'张旭','男',19910103,'教职员3','IT组'), (201504,'老师4','女',19910104,'教职员4','总务组'), (201505,'老师5','男',19910105,'教职员5','IT组'), (201506,'老师6','女',19910106,'教职员6','总务组'), (201507,'老师7','男',19910107,'教职员7','IT组'), (201508,'老师8','女',19910108,'教职员8','总务组'), (201509,'老师9','男',19910109,'教职员9','IT组'), (201510,'老师10','女',19910110,'教职员10','总务组'); update s_teacher set tssex='男' where tno in(201501,201503,201504,201505); update s_teacher set tssex='女' where tno in(201502,201506,201507,201508); update s_teacher set tssex='保密' where tno in(201510,201509); -- 二、完成以下查询题目： -- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。 select sname,ssex,class from s_student; -- 2、 查询教师所有的单位即不重复的Depart列。 select * from s_teacher group by depart; -- 3、 查询Student表的所有记录。 select * from s_student; -- 4、 查询Score表中成绩在60到80之间的所有记录。 select * from s_score where degree between 60 and 80; -- 5、 查询Score表中成绩为85，86或88的记录。 select * from s_score where degree in(85,86,88); -- 6、 查询Student表中“95031”班或性别为“女”的同学记录。 select * from s_student where class in(95031) or ssex in('女'); -- 7、 以Class降序查询Student表的所有记录。 select * from s_student order by class desc; -- 8、 以Cno升序、Degree降序查询Score表的所有记录。 select * from s_score order by cno asc,degree desc; -- 9、 查询“95031”班的学生人数。 select count(*) as '95031班人数' from s_student where class in(95031); -- 10、查询Score表中的最高分的学生学号和课程号。 select sno,cno,degree as '最高分' from s_score where degree>=all(select degree from s_score); -- select sno,cno,degree as '最高分' -- from s_score -- order by degree desc -- limit 0,2; -- 11、查询‘3-105’号课程的平均分。 select avg(degree) as '3-105的平均分' from s_score where cno in('3-105'); -- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 select avg(degree) as '以3开头的课程的平均分数' from s_score where cno like '3%'; -- 13、查询所有学生的Sname、Cno和Degree列。 select s_st.sname,s_sc.cno,s_sc.degree from s_student as s_st join s_score as s_sc on s_st.sno=s_sc.sno; -- 14、查询“95033”班所选课程的平均分。 select avg(degree) as '“95033”班所选课程的平均分' from s_score as sc join s_student as st on sc.sno=st.sno where class in(95033); -- 15、假设使用如下命令建立了一个grade表： create table grade(low int,upp int,rank char(1)); insert into grade values(90,100,'A'); insert into grade values(80,89,'B'); insert into grade values(70,79,'C'); insert into grade values(60,69,'D'); insert into grade values(0,59,'E'); commit; -- 现查询所有同学的Sno、Cno和rank列。 select c.sno,c.cno,g.rank from s_score as c join grade as g where c.degree between low and upp; -- 16、查询"张旭"教师任课的学生成绩。 select t.tname,s.sname,c.degree from s_score as c join s_student as s on c.sno=s.sno join s_course as r on c.cno=r.cno join s_teacher as t on t.tno=r.tno where t.tname in('张旭'); -- select r.tno,t.sname,c.degree -- from s_score as c -- join s_student as t -- on c.sno=t.sno -- join s_course as r -- on c.cno=r.cno -- where r.tno in(201503); -- 17、查询选修某课程的同学人数多于5人的教师姓名。 select t.tname as '同学人数多于5人的教师' from s_teacher as t join s_course as c on t.tno=c.tno join s_score as r on c.cno=r.cno group by c.cno having count(*)>5; -- select c.tno as '同学人数多于5人的教师' -- from s_score as r -- join s_course as c -- on r.cno=c.cno -- group by c.cno -- having count(*)>5; -- 18、查询所有教师和同学的Name、Sex和Birthday。 select sname as name,ssex as sex,sbirthday as birthday from s_student union all select tname,tssex,tbirthday from s_teacher; -- 19 查询所有未讲课的教师的Tname和Depart。 select t.tname,t.depart from s_teacher as t join s_score as c where t.tno not in(select tno from s_course) group by t.tname; -- 20、查询至少有2名男生的班号。 select class as '至少有2名男生的班号' from s_student where ssex='男' group by class having count(*)>=2; -- 21、查询Student表中不姓“王”的同学记录。 select * from s_student where sname not like '王%'; -- 22、查询所有选修“计算机导论”课程的“男”同学的成绩表。 select t.sno,t.sname,t.ssex,c.cname,r.degree from s_score as r join s_course as c on r.cno=c.cno join s_student as t on r.sno=t.sno where c.cname='计算机导论' and t.ssex='男'; ~~~ <hr/> Mysql基础练习效果图：