SQL语句练习02
创建一个仓库,仓库名称为test01_2106,字符集为utf8
CREATE DATABASE test01_2106 character set utf8;
进入test01_2106
创建student(学生表)
字段类型
Sno varchar(20) NOT NULL 学生编号
Sname varchar(20) NOT NULL 学生姓名
ssex varchar(20) NOT NULL 性别
sbirthday datetime 出生日期
class varchar(20) NOT NULL 班级
CREATE TABLE student
(
sno varchar(20) not null,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);
创建teacher(教师表)
字段类型
tno varchar(20) NOT NULL 教师编号
tname varchar(20) NOT NULL 教师姓名
tsex varchar(20) NOT NULL 性别
tbirthday datetime 生日
prof varchar(20)职位
depart varchar(20) NOT NULL 单位
CREATE TABLE teacher
(
tno varchar(20) not null primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null
);
创建表course(课程表)
字段类型
Cno varchar(20) NOT NULL 课程编号
Cname varchar(20) NOT NULL 课程名称
tno varchar(20) NOT NULL 教师编号
CREATE TABLE course
(
cno varchar(20) not null,
cname varchar(20) not null,
tno varchar(20) not null,
);
创建表score(成绩表)
字段类型
Sno varchar(20) NOT NULL 学生编号
cno varchar(20) NOT NULL 课程编号
degree decimal)分数
CREATE TABLE score
(
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal
);
向student表中插入数据
Sno Sname ssex sbirthday class
108 曾华男1977-09-01 95033
105 匡明男1975-10-02 95031
107 王丽女1976-01-23 95033
101 李军男1976-02-20 95033
109 王芳女1975-02-10 95031
103 陆君男1974-06-03 95031
INSERT INTO student values('108','曾华','男','1977-09-01','95033');
INSERT INTO student values('105','匡明','男','1975-10-02','95031');
INSERT INTO student values('107','王丽','女','1976-01-23','95033');
INSERT INTO student values('101','李军','男','1976-02-20','95033');
INSERT INTO student values('109','王芳','女','1975-02-10','95031');
INSERT INTO student values('103','陆君','男','1974-06-03','95031');
向teacher表中插入数据
tno tname tsex tbirthday prof depart
804 李诚男1958-12-02 副教授计算机系
856 张旭男1969-03-12 讲师电子工程系
825 王萍女1972-05-05 助教计算机系
831 刘冰女1977-08-14 助教电子工程系
INSERT INTO teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher values('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');
向course表中插入数据
cno Cname tno
3-105 计算机导论825
3-245 操作系统804
6-166 数字电路856
9-888 高等数学831
INSERT INTO course values('3-105','计算机导论','825');
INSERT INTO course values('3-245','操作系统','804');
INSERT INTO course values('6-166','数字电路','856');
INSERT INTO course values('9-888','高等数学','831');
向score表中插入数据
Sno Cno degree
103 3-245 86
105 3-245 75
109 3-245 68
103 3-105 92
105 3-105 88
109 3-105 76
103 3-105 64
105 3-105 91
109 3-105 78
103 6-166 85
105 6-166 79
109 6-166 81
INSERT INTO score values('103','3-245','86');
INSERT INTO score values('105','3-245','75');
INSERT INTO score values('109','3-245','68');
INSERT INTO score values('103','3-105','92');
INSERT INTO score values('105','3-105','88');
INSERT INTO score values('109','3-105','76');
INSERT INTO score values('103','3-105','64');
INSERT INTO score values('105','3-105','91');
INSERT INTO score values('109','3-105','78');
INSERT INTO score values('103','6-166','85');
INSERT INTO score values('105','6-166','79');
INSERT INTO score values('109','6-166','81');
查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT Sname,Ssex,Class
FROM student;
查询教师所有的单位即不重复的Depart列。
SELECT distinct Depart
FROM teacher;
查询Student表的所有记录。
查询Score表中成绩在60到80之间的所有记录。
SELECT *
FROM Score
WHERE Degree between 60 and 80;
查询Score表中成绩为85,86或88的记录。
SELECT *
FROM Score
WHERE Degree in(85,86,88);
查询Student表中“95031”班或性别为“女”的同学记录。
SELECT *
FROM Student
WHERE class='95031' or Ssex='女';
以Class降序查询Student表的所有记录。
SELECT *
FROM student
ORDER BY class desc;
以Cno升序、Degree降序查询Score表的所有记录。
SELECT *
FROM Score
ORDER BY cno asc,degree desc;
查询“95031”班的学生人数。
SELECT count(*)
FROM student
WHERE class='95031';
查询Score表中的最高分的学生学号和课程号。(子查询或者排序)
SELECT Sno,Cno
FROM Score
WHERE Degree=(SELECT max(Degree) FROM Score);
或者
SELECT Sno,Cno
FROM Score
ORDER BY Degree desc
LIMIT 0,1;
查询每门课的平均成绩。
SELECT Cno,avg(degree)
FROM Score
GROUP BY Cno;
查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT avg(Degree)
FROM score
WHERE Cno like '3%' and Cno in (SELECT Cno FROM score GROUP BY Cno having count(*)>=5);
查询分数大于70,小于90的Sno列。
SELECT Sno
FROM Score
WHERE degree>70 and degree<90;
查询所有学生的Sname、Cno和Degree列。
SELECT Sname, Cno,Degree
FROM Score , student
WHERE Score.Sno=student.Sno;
查询所有学生的Sno、Cname和Degree列。
SELECT Sno,Cname,Degree
FROM Score , Course
WHERE Score.Cno=Course.Cno;
查询所有学生的Sname、Cname和Degree列。
SELECT Sname,Cname,Degree
FROM student,course,score
WHERE student.Sno=score.Sno and course.Cno=score.Cno;
查询“95031”班学生的平均分。
SELECT avg(degree)
FROM Score
WHERE Sno in (SELECT Sno FROM Student WHERE Class='95031');
查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT *
FROM score
WHERE degree > all(SELECT degree FROM Score WHERE Sno='109' and Cno='3-105');
查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT sno,sname,sbirthday
FROM student
WHERE year(sbirthday)= (SELECT year(sbirthday) FROM student WHERE sno='108');
SELECT sno,sname,sbirthday
FROM student
WHERE year(sbirthday)= (SELECT year(sbirthday) FROM student WHERE sno='101');
查询“张旭“教师任课的学生成绩。-----
SELECT Sno,degree
FROM score,Course
WHERE score.Cno=Course.Cno and Course.Tno= (SELECT Tno FROM Teacher WHERE Tname='张旭');
SELECT degree
FROM score
WHERE Cno in (SELECT cno FROM course WHERE Tno= (SELECT Tno FROM Teacher WHERE Tname='张旭'));
查询选修某课程的同学人数多于5人的教师姓名。
SELECT tname
FROM teacher
WHERE tno in(select tno from course where cno in(select cno from score sc
GROUP BY cno
HAVING count(sc.sno)>5));
查询95033班和95031班全体学生的记录。
SELECT *
FROM student
WHERE class in ('95033','95031');
查询存在有85分以上成绩的课程Cno
SELECT Cno
FROM score
WHERE degree>85;
查询出“计算机系“教师所教课程的成绩表。
SELECT *
FROM course
WHERE cno in (SELECT cno FROM course WHERE tno in (SELECT tno FROM teacher WHERE Depart='计算机系'));
查询“计算机系”与“电子工程系“不同职称的教师Tname和Prof
SELECT Tname,Prof
FROM Teacher
WHERE Depart ='计算机系' and Prof not in( SELECT Prof FROM Teacher WHERE Depart ='电子工程系')<br>union <br>SELECT Tname,Prof FROM Teacher WHERE Depart ='电子工程系' and Prof not in( SELECT Prof FROM Teacher WHERE Depart ='计算机系')
查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
any:代表括号中任意一个成绩就可以
SELECT Cno,Sno,Degree
FROM score
WHERE cno='3-105' and degree >any(SELECT degree FROM score WHERE cno='3-245')
ORDER BY degree desc;
查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
all:代表括号中的所有成绩
SELECT Cno,Sno,Degree
FROM score
WHERE cno='3-105' and degree >all(SELECT degree FROM score WHERE cno='3-245')
ORDER BY degree desc;
查询所有教师和同学的name、sex和birthday.
SELECT tname,tsex,tbirthday
FROM Teacher union SELECT sname,ssex,sbirthday FROM Student;
查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT Tname,Tsex,Tbirthday
FROM Teacher WHERE Tsex='女' union SELECT Sname,Ssex,Sbirthday FROM Student WHERE Ssex='女';
查询成绩比该课程平均成绩低的同学的成绩表。
SELECT *
FROM score a
WHERE degree < (SELECT avg(degree) FROM score b WHERE b.cno=a.cno);
查询所有任课教师的Tname和Depart.
SELECT Tname,Depart
FROM Teacher
WHERE tno in (SELECT tno FROM course );
查询所有未讲课的教师的Tname和Depart.-
SELECT Tname,Depart
FROM Teacher
WHERE Tno not in (SELECT Tno FROM Course WHERE cno in (SELECT cno FROM score ));
查询至少有2名男生的班号。
SELECT class
FROM student
WHERE ssex='男' GROUP BY class having count(*)>1;
查询Student表中不姓“王”的同学记录。
SELECT *
FROM Student
WHERE Sname not like '王%';
查询Student表中每个学生的姓名和年龄。
SELECT Sname, year(now())-year(sbirthday)
FROM Student;
查询Student表中最大和最小的Sbirthday日期值。
SELECT Max(Sbirthday ),Min(Sbirthday )
FROM Student;
以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT *
FROM Student
ORDER BY class desc, Sbirthday asc;
查询“男”教师及其所上的课程。
SELECT Tname,Cname
FROM course,teacher
WHERE course.tno= teacher.tno and teacher.Tsex='男';
查询最高分同学的Sno、Cno和Degree列。
SELECT Sno,Cno,Degree
FROM score
WHERE degree=(SELECT max(degree) FROM score);
排序写法:
SELECT Sno,Cno,Degree
FROM score
ORDER BY degree desc
LIMIT 0,1;
查询和“李军”同性别的所有同学的Sname.
SELECT Sname
FROM Student
WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname='李军');
查询和“李军”同性别并同班的同学Sname.
SELECT Sname
FROM Student
WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname='李军') and class=( SELECT class FROM student WHERE Sname='李军');
查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT Sno,Cno,degree
FROM score
WHERE Cno=( SELECT Cno FROM course WHERE Cname='计算机导论') and Sno in (SELECT Sno FROM student WHERE Ssex='男');
作者:暄总-tester