表结构:
CREATE TABLE STUDENT(SNO VARCHAR(3) NOT NULL, SNAME VARCHAR(4) NOT NULL,SSEX VARCHAR(2) NOT NULL, SBIRTHDAY DATETIME NOT NULL,CLASS VARCHAR(5));
CREATE TABLE COURSE(CNO VARCHAR(5) NOT NULL, CNAME VARCHAR(10) NOT NULL, TNO VARCHAR(10) NOT NULL);
CREATE TABLE SCORE (SNO VARCHAR(3) NOT NULL, CNO VARCHAR(5) NOT NULL, DEGREE NUMERIC(10, 1) NOT NULL);
CREATE TABLE TEACHER (TNO VARCHAR(3) NOT NULL, TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL, TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6), DEPART VARCHAR(10) NOT NULL);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');
以下分别为student、teacher、score、course的表格
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
2、 查询教师所有的单位即不重复的Depart列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85,86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。*/
set sql_safe_updates=0;/*更新数据库可操作*/
select sname,ssex,class from student;
select * from course;
select distinct depart from teacher;
delete from teacher where TNO=831;
select * from score where degree between 60 and 80;
select * from score where degree in(85,86,88);
select * from student where class=95031 or ssex='女';
select * from student order by class desc;
select * from score order by cno asc,degree desc;
select count(*) from student where class=95031;
/*10、查询score表中最高分的学生学号和课程---最高分为定语(得到最高分需要先查询,使用where子句)*/
select sno,cno from score where degree=(select max(degree) from score);
select sno,cno from score order by degree asc limit 1;
/*11、查询‘3-105’号课程的平均分--3-105为定语 where子句*/
select avg(degree) from score where cno='3-105';
/*12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。--按课程分组,每个课程至少有5个学生的课程成绩
使用having子句的原因:where关键字无法与合计函数一起使用count(),max(),min()*/
select avg(degree),cno from score where cno group by cno having count(sno)>=5;
/*13、查询最低分大于70,最高分小于90的Sno列。*/
select sno from score group by sno having max(degree)<90 and min(degree)>70;
/*14、查询所有学生的Sname、Cno和Degree列。--将student表与score表全连接 就可得到两表所有变量*/
select a.sname,b.cno,b.degree from student as a join score as b on a.sno=b.sno;
/*15、查询所有学生的Sno、Cname和Degree列。*/
select b.sno,a.cname,b.degree from course as a join score as b on a.cno=b.cno;
/*16、查询所有学生的Sname、Cname和Degree列。--三表全连接*/
select a.sname,b.cname,c.degree from student as a join (course b,score c) on a.sno=c.sno and b.cno=c.cno;
/*17、查询“95033”班所选课程的平均分。---宾语直接放在select后*/
select avg(b.degree) from student as a join score as b on a.sno=b.sno where a.class='95033';
/*18、假设使用如下命令建立了一个grade表:*/
create table grade(low numeric(3),upp numeric(3),rank char(1));
insert into grade value(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 数据主要是执行的sql提交到数据库*/
/*现查询所有同学的Sno、Cno和rank列。score中degree在low与upp*/
select a.sno,a.cno,b.rank from score a,grade b where a.degree between b.low and b.upp;
/*19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录(学号)。
技巧:选出3-105选修课学号109的同学成绩作为子句,all代表所有高于前者成绩的学生,
*/
select a.sno,a.degree from score a where a.cno='3-105'and a.degree>=all(select b.degree from score b where b.sno='109' and b.cno='3-105');
/*20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
技巧:1、一门以上:group by sno having count(sno)>1---分组后计算每组个数
2、最高分子句:select max(degree) from score;select degree from score order by degree asc limit 1;
错误:where degree<max(degree)-1 因为where子句不能和算术函数一起使用) 正确:having where degree<max(degree)-1*/
select * from score where degree<(select max(degree) from score ) group by sno having count(sno)>1 order by degree;
/*21、查询成绩高于学号(sno)为“109”、课程号(cno)为“3-105”的成绩的所有记录。*/
select * from score where degree>all(select degree from score where sno='109' and cno='2-105') order by degree;
/*22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
技巧:datetime型 有year(),month()等函数*/
select sno,sname,sbirthday from student where year(sbirthday)=all(select year(sbirthday) from student where sno='108');
/*23、查询“张旭“教师任课的学生成绩。teacher表中tname 与 score中degree 没有相关关键字相等联系,故需要course这第三张表连接
三表全连接(找出相等关键字)*/
select c,sno,c.degree from course a join (teacher b,score c) on a.tno=b.tno and a.cno=c.cno where b.tname='张旭';
SELECT A.SNO,A.DEGREE FROM SCORE A JOIN (TEACHER B,COURSE C)ON A.CNO=C.CNO AND B.TNO=C.TNO WHERE B.TNAME='张旭';
/*24、查询选修某课程的同学人数多于5人的教师姓名。
常用用法from-(join--on—)group b--having-分组后利用having算术函数*/
select t.tname from course c join (teacher t,score s) on c.tno=t.tno and c.cno=s.cno group by s.sno having count(s.sno)>5;
select tname from teacher where tno in(select c.tno from course c join score s on c.cno=s.cno group by s.sno having count(s.sno)>5);
/* 查询教授课程数量>5的老师姓名*/
select count(tname) from teacher where tno in(select c.tno from course c,score s where c.cno=s.cno group by c.tno having count(c.tno)>5);
/*25、查询95033班和95031班全体学生的记录。inner join 和join一样*/
select * from student;
select * from student where class='95031' or calss='95031';
SELECT * FROM STUDENT S INNER JOIN SCORE SC ON S.SNO=SC.SNO INNER JOIN COURSE C ON SC.CNO=C.CNO INNER JOIN TEACHER T ON T.TNO=C.TNO WHERE S.CLASS='95033'OR S.CLASS='95031';
/*26、查询存在有85分以上成绩的课程Cno.--主要找出max(degree)>85*/
select distinct cno from score where degree>85;
select distinct cno from score where degree in (select degree from score where degree>85);
SELECT CNO FROM SCORE GROUP BY CNO HAVING MAX(DEGREE)>85;
/*27、查询出“计算机系“(t.prof)教师所教课程的成绩表。
技巧1、三表全连接 2、将三表全连接拆解 where in( ) 当多连接的时候语句2的性能会下降*/
select s.* from course c join (score s,teacher t) on c.cno=s.cno and c.tno=t.tno where depart='计算机系' ;
select * from score where cno in (select c.cno from course c join teacher t on c.tno=t.tno where depart='计算机系');
/*28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof*/
select depart,tname,prof from teacher where depart='计算机系' and depart='电子工程系';
/*29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
技巧:同一个表需要使用两次,就把同一张表定义为不同别名*/
select* from score as a,score as b where a.cno='3-105' and b.cno='3-245'and a.sno=b=sno and a.degree>b.degree order by a.degree desc;
/*31、查询所有教师和同学的name、sex和birthday.
技巧;union联结 相同变量名*/
SELECT SNAME AS NAME, SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT UNION
SELECT TNAME AS NAME, TSEX AS SEX, TBIRTHDAY AS BIRTHDAY FROM TEACHER;
/*32、查询所有“女”教师和“女”同学的name、sex和birthday.*/
select sname as name,ssex as sex,sbirthday as birthday from student where ssex='女'union
select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex='女';
/*33、查询成绩比该课程平均成绩低的同学的成绩表。
技巧:题目可知同一表的同一变量(成绩)被使用了两次;故需要设定两个别名;(select avg(b.degree) from score b where a.cno=b.cno)保证了求分组平均*/
select a.* from score a where a.degree<(select avg(b.degree) from score b where a.cno=b.cno);
/*34、查询所有任课教师的Tname和Depart.*/
select distinct tname,depart from teacher where tno in(select tno from course);
select distinct t.tname,t.depart from teacher t join course c on t.tno=c.tno;
/*35 查询所有未讲课的教师的Tname和Depart.
1:找出两表不匹配可使用left join或者right join(全项和join的位置一致)
2、not in 效率较差,尽量不使用*/
select tname,depart from teacher a left join course b using (tno) where isnull(b.tno);
select tname,depart from teacher a where not exists(select * from course b where a.tno=b.tno);
select distinct tname,depart from teacher where tno not in(select tno from course);
/*36、查询至少有2名男生的班号。
技巧:按班级分组后 再ssex筛选*/
select * from student where ssex='男' group by class having count(ssex)>1;
/*37、查询Student表中不姓“王”的同学记录。*/
select * from student where sname not like '王%';
/*38、查询Student表中每个学生的姓名和年龄。*/
select sname,(year(now())-year(sbirthday)) as age from student;
/*39、查询Student表中最大和最小的Sbirthday日期值。*/
select max(sbirthday) as sb from student union select min(sbirthday) as sb from student;
select sname,sbirthday as THEMAX from student where sbirthday =(select min(SBIRTHDAY) from student)union
select sname,sbirthday as THEMIN from student where sbirthday =(select max(SBIRTHDAY) from student);
/*40、以班号和年龄从小到大的顺序查询Student表中的全部记录。*/
select class,year(now())-year(sbirthday) as age from student order by class desc,age desc;
/*41、查询“男”教师及其所上的课程。*/
select distinct t.tname,c.cname from teacher t join course c on c.tno=t.tno where t.tsex='男';
select distinct t.tname,c.cname from course c left join teacher t using(tno) where t.tsex='男';
/*42、查询最高分同学的Sno、Cno和Degree列。
技巧:在独立的子句中不需要别名*/
select * from score s1 where s1.degree=(select max(s2.degree) from score s2);
select * from score where degree=(select max(degree) from score);
/*43、查询和“李军”同性别的所有同学的Sname.*/
select sname from student where ssex=(select ssex from student where sname='李军');
/*44、查询和“李军”同性别并同班的同学Sname.*/
select sname from student where (ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军'));(mysql中出现错误)
/*45、查询所有选修“计算机导论”课程的“男”同学的成绩表*
技巧:如果join…using(sno,cno) sno,cno必为共同*/
select sc.* from student s join (course c,score sc) on s.sno=sc.sno and c.cno=sc.cno where c.cname='计算机导论' and s.ssex='男';
SELECT A.* FROM SCORE A JOIN (STUDENT B,COURSE C) USING(sno,CNO) WHERE B.SSEX='男'AND C.CNAME='计算机导论';