SQL语句(30基础+20提高)

目录

1. 创建表

CREATE DATABASE /*!32312 IF NOT EXISTS*/`school-db` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `school-db`;

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2020                                         */
/*==============================================================*/


DROP TABLE IF EXISTS courses;

DROP TABLE IF EXISTS scores;

DROP TABLE IF EXISTS students;

DROP TABLE IF EXISTS teachers;

/*==============================================================*/
/* Table: courses                                               */
/*==============================================================*/
CREATE TABLE courses
(
   cno                  VARCHAR(5) NOT NULL,
   cname                VARCHAR(10) NOT NULL,
   tno                  VARCHAR(10) NOT NULL,
   PRIMARY KEY (cno)
);

/*==============================================================*/
/* Table: scores                                                */
/*==============================================================*/
CREATE TABLE scores
(
   sno                  VARCHAR(3) NOT NULL,
   cno                  VARCHAR(5) NOT NULL,
   degree               NUMERIC(10,1) NOT NULL,
   PRIMARY KEY (sno, cno)
);

/*==============================================================*/
/* Table: students                                              */
/*==============================================================*/
CREATE TABLE students
(
   sno                  VARCHAR(3) NOT NULL,
   sname                VARCHAR(4) NOT NULL,
   ssex                 VARCHAR(2) NOT NULL,
   sbirthday            DATETIME,
   class                VARCHAR(5),
   PRIMARY KEY (sno)
);

/*==============================================================*/
/* Table: teachers                                              */
/*==============================================================*/
CREATE TABLE teachers
(
   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),
   PRIMARY KEY (tno)
);

ALTER TABLE courses ADD CONSTRAINT FK_Reference_3 FOREIGN KEY (tno)
      REFERENCES teachers (tno) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE scores ADD CONSTRAINT FK_Reference_1 FOREIGN KEY (sno)
      REFERENCES students (sno) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE scores ADD CONSTRAINT FK_Reference_2 FOREIGN KEY (cno)
      REFERENCES courses (cno) ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陈冰','女','1977-08-14','助教','电子工程系');

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率论' ,831);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

2. 表数据

2.1 courses表

在这里插入图片描述

2.2 scores表

在这里插入图片描述

2.3 students表

在这里插入图片描述

2.4 teachers表

在这里插入图片描述

3. 基础题(30)

3.1、查询students表的所有记录
SELECT * FROM students;
3.2、查询students表中的所有记录的sname、ssex和class列
SELECT sname,ssex,class FROM students;
3.3、查询teacher表所有的单位即不重复的depart列
SELECT DISTINCT depart FROM teachers;
3.4、查询scores表中成绩在60到80之间的所有记录
SELECT * FROM scores WHERE degree BETWEEN 60 AND 80;
3.5、查询scores表中成绩为85,86或88的记录
SELECT * FROM scores WHERE degree IN(85,86,88);
3.6、查询students表中“95031”班或性别为“女”的同学记录
SELECT * FROM students WHERE class='95031' OR ssex='女';
3.7、以班级class降序查询students表的所有记录
SELECT * FROM students ORDER BY class DESC;
3.8、以cno升序、degree降序查询scores表的所有记录
SELECT * FROM scores ORDER BY cno,degree DESC;
3.9、查询“95031”班的学生人数
SELECT COUNT(*) FROM students WHERE class='95031';
3.10、查询每个班的学生人数
SELECT class,COUNT(*) FROM students GROUP BY class;
3.11、查询scores表中的最高分的学生学号和课程号
(1)
SELECT sno,cno FROM scores ORDER BY degree DESC LIMIT 1;
(2)
SELECT sno,cno FROM scores WHERE degree=(
SELECT MAX(degree) FROM scores WHERE degree);
3.12、查询‘3-105’号课程的平均分
SELECT AVG(degree) FROM scores WHERE cno='3-105';
3.13、查询最高分
SELECT MAX(degree) FROM scores WHERE degree;
3.14、查询最低分
SELECT MIN(degree) FROM scores WHERE degree;
3.15、查询最低分大于70,最高分小于90的sno列
SELECT sno FROM scores
GROUP BY sno
HAVING MIN(degree)>70 AND MAX(degree)<90;
3.16、查询95033班和95031班全体学生的记录
SELECT * FROM students WHERE class='95033' OR class='95031';
3.17、查询存在有85分以上成绩的课程cno
SELECT cno,degree FROM scores WHERE degree>85;
3.18、查询所有教师和同学的NAME、sex和birthday
SELECT tname,tsex,tbirthday FROM teachers 
UNION
SELECT sname,ssex,sbirthday FROM students;
3.19、查询所有“女”教师和“女”同学的NAME、sex和birthday
SELECT tname AS NAME,tsex AS sex,tbirthday AS birthday FROM teachers WHERE tsex='女' 
UNION
SELECT sname AS NAME,ssex AS sex,sbirthday AS birthday FROM students WHERE ssex='女';
3.20、查询所有任课教师的tname和depart
SELECT tname,depart FROM teachers WHERE tno IN(
SELECT tno FROM courses);
3.21、查询所有未讲课的教师的tname和depart
SELECT tname,depart FROM teachers WHERE tno NOT IN(
SELECT tno FROM courses);
3.22、查询至少有2名男生的班号
SELECT class,COUNT(*) FROM students WHERE ssex='男'
GROUP BY class HAVING COUNT(*)>=2;
3.23、查询students表中姓“王”的同学记录
SELECT * FROM students WHERE sname LIKE '王%';
3.24、查询students表中不姓“王”的同学记录
SELECT * FROM students WHERE sname NOT LIKE '王%';
3.25、查询students表同名的同学记录
SELECT DISTINCT sname FROM students WHERE sname IN
(SELECT sname FROM students GROUP BY sname
 HAVING COUNT(sname)>1);
3.26、查询students表中每个学生的姓名和年龄
SELECT sname,YEAR(NOW())-YEAR(sbirthday) AS sage FROM students;
3.27、查询students表中最大和最小的sbirthday日期值
SELECT MIN(sbirthday),MAX(sbirthday) FROM students;
3.28、以班号和年龄从大到小的顺序查询student表中的全部记录
SELECT * FROM students ORDER BY class DESC,sbirthday ASC;
3.29、查询“男”教师及其所上的课程
SELECT teachers.tname,courses.cname
FROM teachers INNER JOIN courses
ON(teachers.tno=courses.tno)
WHERE teachers.tsex='男';
3.30、查询课程对应的老师姓名、职称、所属系
SELECT
c.cno,c.cname,t.tno,t.tname,t.prof,t.depart
FROM(SELECT * FROM teachers) t
LEFT JOIN(SELECT * FROM courses) c
ON c.tno=t.tno;
3.31、查询每门课最高分同学的sno、cno和degree列
(1)
SELECT * FROM scores WHERE degree IN
(
	SELECT MAX(degree) FROM scores GROUP BY cno 
	HAVING MAX(degree)
);
(2)
SELECT s.sno,s.cno,s.degree
FROM
(SELECT sno,cno,degree FROM scores) s
RIGHT JOIN
(SELECT cno,MAX(degree) dmax FROM scores
GROUP BY cno) d
ON s.cno=d.cno AND s.degree=d.dmax;

4. 提高题(20)

4.1、查询scores表中至少有5名学生选修的并以3开头的课程的平均分数
SELECT cno,AVG(degree)
FROM scores
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(sno) >= 5;
4.2、查询所有学生的sname、cno和degree列
(1)
SELECT sname,cno,degree
FROM students INNER JOIN scores 
ON(students.sno=scores.sno)
ORDER BY sname;
(2)
SELECT
  stu.sname,sc.cno,sc.degree
FROM
(SELECT sno,sname FROM students) stu
LEFT JOIN
(SELECT sno,cno,degree FROM scores) sc
ON stu.sno = sc.sno
ORDER BY stu.sname;
4.3、查询所有学生的sno、cname和degree列
SELECT sno,cname,degree
FROM scores 
INNER JOIN courses ON scores.cno = courses.cno
ORDER BY sno;
4.4、查询所有学生的sname、cname和degree列
SELECT sname,cname,degree
FROM students 
INNER JOIN scores ON students.sno=scores.sno
INNER JOIN courses ON scores.cno=courses.cno
ORDER BY sname;
4.5、创建等级grade表,现查询所有同学的sno、cno和rank级别列
CREATE TABLE grade(low TINYINT,upp TINYINT,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');
SELECT sno,cno,degree,rank
FROM scores INNER JOIN grade
ON scores.degree>=grade.low AND scores.degree<=grade.upp
ORDER BY sno;
4.6、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
SELECT s1.sno,s1.degree 
FROM scores s1 LEFT JOIN scores s2
ON s1.cno=s2.cno AND s1.degree>s2.degree
WHERE s1.cno='3-105' AND s2.sno='109'
ORDER BY s1.sno;
4.7、查询scores中选学一门以上课程的同学中分数为非最高分成绩的记录
SELECT sno,degree FROM scores
GROUP BY sno
HAVING COUNT(cno)>1 AND degree!=MAX(degree);
4.8、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
SELECT s1.sno,s1.degree
FROM scores AS s1 INNER JOIN scores AS s2
ON(s1.cno=s2.cno AND s1.degree>s2.degree)
WHERE s1.cno='3-105' AND s2.sno='109'
ORDER BY s1.sno;
4.9、查询和学号为108的同学同年出生的所有学生的sno、sname和sbirthday列
SELECT s1.sno,s1.sname,s1.sbirthday
FROM students s1 INNER JOIN students s2
ON YEAR(s1.sbirthday)=YEAR(s2.sbirthday)
WHERE s2.sno='108';
4.10、查询“95033”班所选课程的平均分*
SELECT cname,AVG(degree),ROUND(AVG(degree),2)
FROM students
INNER JOIN scores ON students.sno=scores.sno
INNER JOIN courses ON scores.cno=courses.cno
WHERE class='95033'
GROUP BY courses.cno
ORDER BY cname;
4.11、查询“张旭“教师任课的学生成绩*
SELECT sno,degree
FROM scores INNER JOIN courses
ON(scores.cno=courses.cno) INNER JOIN teachers
ON(courses.tno=teachers.tno)
WHERE teachers.tname='张旭';
4.12、查询选修某课程的同学人数多于5人的教师姓名
SELECT DISTINCT tname
FROM scores INNER JOIN courses
ON(scores.cno=courses.cno) INNER JOIN teachers
ON(courses.tno=teachers.tno)
WHERE courses.cno IN(SELECT cno FROM scores GROUP BY(cno) HAVING COUNT(sno)>5);
4.13、查询出“计算机系“教师所教课程的成绩表
SELECT tname,cname,sname,degree
FROM teachers INNER JOIN courses
ON(teachers.tno=courses.tno) INNER JOIN scores
ON(courses.cno=scores.cno) INNER JOIN students
ON(scores.sno=students.sno)
WHERE teachers.depart='计算机系'
ORDER BY tname,cname,degree DESC;
4.14、查询“计算机系”与“电子工程系“不同职称的教师的tname和prof
SELECT tname,prof
FROM teachers
WHERE depart='计算机系' AND prof NOT IN(
    SELECT DISTINCT prof
    FROM teachers
    WHERE depart='电子工程系');
4.15、查询选修编号为“3-105“课程且成绩至少高于任意选修编号为“3-245”的同学的成绩的cno、sno和degree,并按degree从高到低次序排序
SELECT cno,sno,degree
FROM scores
WHERE cno='3-105' AND degree > ANY(
    SELECT degree
    FROM scores
    WHERE cno='3-245')
ORDER BY degree DESC;
4.16、查询选修编号为“3-105”且成绩高于所有选修编号为“3-245”课程的同学的cno、sno和degree
SELECT cno,sno,degree
FROM scores
WHERE cno='3-105' AND degree > ALL(
    SELECT degree
    FROM scores
    WHERE cno='3-245')
ORDER BY degree DESC;
4.17、查询成绩比该课程平均成绩低的同学的成绩表*
SELECT s1.*
FROM scores AS s1 INNER JOIN (
    SELECT cno,AVG(degree) AS adegree
    FROM scores
    GROUP BY cno) s2
ON(s1.cno=s2.cno AND s1.degree<s2.adegree);
4.18、查询和“李军”同性别的所有同学的sname
SELECT s1.sname
FROM students AS s1 INNER JOIN students AS s2
ON(s1.ssex=s2.ssex)
WHERE s2.sname='李军';
4.19、查询和“李军”同性别并同班的同学sname
SELECT s1.sname
FROM students AS s1 INNER JOIN students AS s2
ON(s1.ssex=s2.ssex AND s1.class=s2.class)
WHERE s2.sname='李军';
4.20、查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT *
FROM scores
WHERE sno IN (
    SELECT sno
    FROM students
    WHERE ssex='男') AND
cno IN (
    SELECT cno
    FROM courses
    WHERE cname='计算机导论');
  • 2
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值