脚本宝典收集整理的这篇文章主要介绍了MySQL56--SQL语句练习02，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

SQL语句练习02

创建一个仓库，仓库名称为test01_2106,字符集为utf8

CREATE DATABASE test01_2106 character set utf8;

进入test01_2106

USE 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表的所有记录。

SELECT *
FROM 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='男');

脚本宝典总结

以上是脚本宝典为你收集整理的MySQL56--SQL语句练习02全部内容，希望文章能够帮你解决MySQL56--SQL语句练习02所遇到的问题。

如果觉得脚本宝典网站内容还不错，欢迎将脚本宝典推荐好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。