经典MySQL练习题（一）

最新推荐文章于 2022-06-13 18:23:40 发布

TanaStudy

最新推荐文章于 2022-06-13 18:23:40 发布

阅读量1.8k

点赞数 4

分类专栏： MySQL 文章标签： sql mysql

本文链接：https://blog.csdn.net/weixin_43901865/article/details/113176994

版权

这篇博客提供了45道经典的MySQL查询题目，涵盖了从基础查询到复杂条件的筛选，包括查询学生信息、成绩范围、课程平均分、特定班级学生、教师信息等，旨在帮助读者提升SQL查询技能。

摘要由CSDN通过智能技术生成

数据准备

建表

CREATE TABLE IF NOT EXISTS students (
    sno 		VARCHAR(3) 	NOT NULL, 
    sname 		VARCHAR(4) 	NOT NULL,
    ssex 		VARCHAR(2) 	NOT NULL, 
    sbirthday 	DATETIME,
    class 		VARCHAR(5)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS courses(
    cno 	VARCHAR(5)      NOT NULL, 
    cname 	VARCHAR(10)     NOT NULL, 
    tno 	VARCHAR(10)     NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS scores (
    sno 	VARCHAR(3)      NOT NULL, 
    cno 	VARCHAR(5)      NOT NULL, 
    degree 	NUMERIC(10, 1)  NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8; 

CREATE TABLE IF NOT EXISTS 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)     NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

插入数据

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 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 ('9-888' ,'高等数学'