MYSQL强化练习51题

创建数据表

CREATE TABLE 学生表(
	学号 CHAR(10) NOT NULL PRIMARY KEY,
	姓名 VARCHAR(30) NOT NULL,
	性别 CHAR(2) NOT NULL,
	年龄 INT NOT NULL,
	所在院系 VARCHAR(10) NOT NULL,
	班级名称 VARCHAR(20) NOT NULL,
	入学年份 DATETIME NOT NULL
);

CREATE TABLE 课程表(
	课程号 CHAR(10) NOT NULL PRIMARY KEY,
	课程名 VARCHAR(20) NOT NULL,
	选修课 CHAR(10) NULL
);

CREATE TABLE 成绩表(
	学号 CHAR(10) NOT NULL,
	课程号 CHAR(10) NOT NULL,
	成绩 INT NOT NULL,
	FOREIGN KEY(学号) REFERENCES 学生表(学号), # 外键
	FOREIGN KEY(课程号) REFERENCES 课程表(课程号) # 外键
);

CREATE TABLE 授课表(
	编号 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	教师名 VARCHAR(30) NOT NULL,
	课程号 CHAR(10) NOT NULL,
	学时数 INT NOT NULL,
	班级名 VARCHAR(20) NOT NULL
	FOREIGN KEY(课程号) REFERENCES 课程表(课程号)
);

USE 学生课程;

插入数据

INSERT INTO 学生表 VALUES
('200009001','葛文卿','女','22','国际贸易','国贸2班','2000-8-29'),
('200104019','郑秀莉','女','21','会计学','会计2班','2001-2-9'),
('200203001','刘成锴','男','18','计算机','软件2班','2002-8-27'),
('200206001','李涛','男','19','电子学','电子1班','2002-8-27'),
('200203002','沈香娜','女','22','计算机','软件2班','2002-8-27'),
('200206002','李涛','男','23','电子学','电子1班','2002-8-27'),
('200203003','肖一竹','女','19','计算机','软件2班','2002-8-27');

SELECT * FROM 学生表;

INSERT INTO 课程表 VALUES
('C801','高等数学',''),
('C807','离散数学',''),
('C802','C++语言','C807'),
('C803','数据结构','C802'),
('C804','数据库原理','C803'),
('C805','操作系统','C807'),
('C806','编译原理','C803')
;

INSERT INTO 成绩表 VALUES
('200203001','C801','98'),
('200203002','C804','70'),
('200206001','C801','85'),
('200203001','C802','99'),
('200206002','C803','82')
;

INSERT INTO 授课表 VALUES
(0,'苏亚步','C801','72','软件2班'),
(0,'王立山','C802','64','软件2班'),
(0,'何珊','C803','72','软件2班'),
(0,'王立山','C804','64','软件2班'),
(0,'苏亚步','C801','72','软件1班');

#1.查询学生表中信息

SELECT * FROM 学生表;

#2.查询成绩表中的信息

SELECT * FROM 成绩表;

#3.查询全部学生的所有信息,且姓名输出在左边(第一列)

SELECT 姓名,学号,性别,年龄,所在院系,班级名称,入学年份 FROM 学生表

#4.查询学生当前的年龄是多少岁

SELECT 年龄 FROM 学生表;

#5.要求计算教师授课程的学分(设学分=学时数/16)

SELECT 学时数/16 AS 学分 FROM 授课表;

#6.要求列出学生所在的所有院系名称

SELECT DISTINCT 所在院系 FROM 学生表;

#7.查询姓’李’的学生情况

SELECT * FROM 学上表 WHERE 姓名 LIKE ‘李%’;

#8.查询成绩不在85 到95 之间的信息

SELECT * FROM 成绩表 WHERE 成绩 <=95 OR 成绩 >=85 ;

#9.列出所有非软件2班的班级名称

SELECT 班级名称 FROM 学生表 WHERE 班级名称<>'软件2班';

#10.列出课程号在’C802’与’C806’之间的所有课程信息

SELECT * FROM 课程表 WHERE 课程号 REGEXP 'C80[2-6]'  #正则
SELECT * FROM 课程表 LIMIT 1,5; #limit函数它会自动排序

#11.列出无选修课的所有课程的课程名

SELECT 课程名 FROM 课程表 WHERE 选修课<>’’;

#12.列出’高等数学’,‘操作系统’,'编译原理’的全部信息

SELECT DISTINCT 课程表.*,成绩表.*,授课表.* FROM 课程表,成绩表,授课表 WHERE 课程表.`课程号`=成绩表.`课程号` AND 课程表.`课程号`=授课表.`课程号` AND (课程名='高等数学' OR
课程名='操作系统' OR 课程名='编译原理');

#13.要求查询所有2002年元旦前入学的学生名单并按年龄排序

SELECT * FROM 学生表 WHERE YEAR(入学年份)<2002 ORDER BY 年龄;

#14.要求查询年龄在19岁以下或者是女生的学生姓名,年龄,性别

SELECT 姓名,年龄,性别 FROM 学生表 WHERE 年龄<19 OR 性别='女';

#15.同时按学号(从小到大)和课程号(从大到小)排列出所有学生课程成绩

SELECT * FROM 成绩表 ORDER BY 学号 ASC,课程号 DESC;

#16.从学生表中统计每个班级名称的人数

SELECT COUNT('姓名') FROM 学生表;

#17.统计19岁以上女生的个数

SELECT COUNT(*) FROM 学生表 WHERE 年龄>19 AND 性别='女';

#18.查询所有年龄的总和

SELECT SUM(年龄) AS 年龄总和 FROM 学生表;

#19.从成绩表查询最高分,最低分

SELECT MAX(成绩) AS 最高分,MIN(成绩) AS 最低分 FROM 成绩表;

#20.要求查询’C801’课程的平均分

SELECT AVG(成绩) FROM 成绩表 WHERE 课程号='C801';

#21.统计选修了课程的学生人数(用成绩表)

SELECT COUNT(*) FROM 成绩表 WHERE 课程号 <>"";

#22.根据性别统计学生平均年龄

SELECT 性别,AVG(年龄) FROM 学生表 GROUP BY 性别;

#23.根据学生表统计入学年份

SELECT 入学年份,COUNT(入学年份) FROM 学生表 GROUP BY 入学年份;

#24.找出入学人数大于3人的年份及人数

SELECT 入学年份,COUNT(入学年份) FROM 学生表 GROUP BY 入学年份 HAVING COUNT(入学年份)>3;

#25.求相同月份入学的人数(不考虑年份)

SELECT MONTH(入学年份) FROM 学生表 ;
SELECT 入学月份,COUNT(*) AS 人数 FROM (SELECT MONTH(入学年份) AS 入学月份 FROM 学生表) AS a GROUP BY 入学月份 HAVING 人数>0;

#26.筛选出平均成绩在80分以上的课程及平均成绩

SELECT 课程号,AVG(成绩)AS 平均成绩 FROM 成绩表 GROUP BY 课程号;
SELECT 课程号,平均成绩 FROM (SELECT 课程号,AVG(成绩)AS 平均成绩 FROM 成绩表 GROUP BY 课程号) AS a WHERE 平均成绩>80;

#27.查询学生 姓名,课程号,成绩

SELECT 学生表.`姓名`,成绩表.`成绩`,成绩表.`课程号` FROM 学生表,成绩表 WHERE 学生表.`学号`=成绩表.`学号`;

#28.列出所有课程的选修课程名称

SELECT 选修课 FROM 课程表 WHERE 选修课<>"";
SELECT 课程名 FROM 课程表 WHERE 课程号 IN(SELECT 选修课 FROM 课程表 WHERE 选修课<>"");

#29.查询课程号为’C801’并且成绩大于90分的学生姓名,学号和成绩

SELECT 姓名,学生表.学号,成绩表.成绩 FROM 学生表,成绩表 WHERE 学生表.`学号`= 成绩表.`学号`AND 成绩>90;

#30.查询学生姓名,学号,课程名,成绩

SELECT 学生表.`姓名`,学生表.`学号`,课程表.`课程名`,成绩表.`成绩` FROM 成绩表 JOIN 学生表 ON 成绩表.`学号`=学生表.`学号` JOIN  课程表 ON 成绩表.`课程号`=课程表.`课程号`;
SELECT 姓名,学生表.学号,课程表.课程名,成绩 FROM 学生表,课程表,成绩表 WHERE 学生表.学号=成绩表.学号 AND 课程表.课程号=成绩表.课程号;

– 31.要求列出选修了高等数学的学生学号,姓名,成绩,课程名

SELECT 学生表.`姓名`,学生表.`学号`,课程表.`课程名`,成绩表.`成绩` FROM 成绩表 JOIN 学生表 ON 成绩表.`学号`=学生表.`学号` JOIN  课程表 ON 成绩表.`课程号`=课程表.`课程号`
WHERE 课程表.`选修课`='C801';

– 32.查询与’沈香娜’ 同院系的学生信息

SELECT 所在院系 FROM 学生表 WHERE 姓名='沈香娜';
SELECT * FROM 学生表 WHERE 所在院系 IN(SELECT 所在院系 FROM 学生表 WHERE 姓名='沈香娜') AND 姓名<>'沈香娜';

– 33.查询选修了’C801’与’C804’ 这2门课程的学生姓名,课程名和成绩

SELECT 学生表.`姓名`,课程表.`课程名`,成绩表.`成绩` FROM 学生表,课程表,成绩表 WHERE 学生表.`学号`=成绩表.`学号` AND 成绩表.`课程号`=课程表.`课程号`AND (课程表.`选修课`='C801' OR 课程表.`选修课`='C804');

– 34.查询所有与计算机系学生年龄不相同的学生信息

SELECT 年龄 FROM 学生表 WHERE 所在院系='计算机';
SELECT * FROM 学生表 WHERE 年龄 NOT IN(SELECT 年龄 FROM 学生表 WHERE 所在院系='计算机');

– 35.查询所有年龄大于计算机系的平均年龄的非计算机系学生的学生信息

SELECT AVG(年龄) FROM 学生表 WHERE 所在院系='计算机';
SELECT * FROM 学生表 WHERE 年龄>(SELECT AVG(年龄) FROM 学生表 WHERE 所在院系='计算机') AND 所在院系<>'计算机';

– 36.查询所有年龄大于计算机系的最小年龄的非计算机系学生的学生信息

SELECT MIN(年龄) FROM 学生表 WHERE 所在院系='计算机';
SELECT * FROM 学生表 WHERE 年龄>(SELECT MIN(年龄) FROM 学生表 WHERE 所在院系='计算机') AND 所在院系!='计算机';

– 37.查询所有年龄小于计算机系的最小年龄的非计算机系学生的学生信息

SELECT MIN(年龄) FROM 学生表 WHERE 所在院系='计算机';
SELECT * FROM 学生表 WHERE 年龄<(SELECT MIN(年龄) FROM 学生表 WHERE 所在院系='计算机') AND 所在院系!='计算机';

– 38.查询’何珊’所教班级的所有任课教师的姓名,所教班级名

SELECT 班级名 FROM 授课表 WHERE 教师名='何珊';
SELECT 教师名,班级名 FROM 授课表 WHERE 班级名=(SELECT 班级名 FROM 授课表 WHERE 教师名='何珊');

– 39.按年龄降序列出所有超过平均年龄的学生姓名和年龄

SELECT AVG(年龄) FROM 学生表;
SELECT 姓名,年龄 FROM 学生表 WHERE 年龄>(SELECT AVG(年龄) FROM 学生表) ORDER BY 年龄 DESC;

– 40.检索学号不是[200108011]且入学月份为8月的学生信息

SELECT * FROM 学生表 WHERE 学号!='200108011' AND MONTH(入学年份)='8';

– 41.列出年龄在18~20岁之间且选修了高等数学的男同学的详细信息

SELECT 学生表.* FROM 学生表,成绩表,课程表 WHERE (学生表.`学号`=成绩表.`学号`) AND (成绩表.`课程号`=课程表.`课程号`) AND (课程表.`选修课`='C801') AND (学生表.`性别`='男') AND (学生表.`年龄` BETWEEN 18 AND 20);
SELECT * FROM 学生表 WHERE 年龄>=18 AND 年龄<=23 AND 性别=N'男' AND 学号 IN(SELECT 学号 FROM 成绩表 WHERE 课程号 IN(SELECT 课程号 FROM 课程表 WHERE 选修课='C802'));

– 42.列出所有不是姓’刘’的学生信息

SELECT * FROM 学生表 WHERE 姓名 NOT LIKE '刘%';

– 43.统计学生表中一共有多少名20岁以上的女同学

SELECT COUNT(*) FROM 学生表 WHERE 年龄>20 AND 性别='女';

– 44.查找年龄最大和最小的男学生年龄

SELECT MAX(年龄),MIN(年龄) FROM 学生表 WHERE 性别='男';

– 45.计算选修课门数在1门以上的学生号,姓名, 选课门数

SELECT 学号,COUNT(课程号)AS 选课门数 FROM 成绩表  GROUP BY 学号 HAVING COUNT(课程号)>1;
SELECT 学生表.`学号`,学生表.`姓名` FROM 学生表 WHERE 学号 IN(SELECT 学号 FROM 成绩表  GROUP BY 学号 HAVING COUNT(课程号)>1);

SELECT 学生表.学号,姓名 FROM 学生表 WHERE 学号 IN( SELECT 学号 FROM 成绩表 GROUP BY 学号 HAVING COUNT(学号)>1);

– 46.计算每个班学生的平均年龄和最小年龄

SELECT 班级名称,AVG(年龄) AS 平均年龄,MIN(年龄) AS 最小年龄 FROM 学生表  GROUP BY 班级名称;

– 47.列出所有成绩大于学号为200206002的学生平均成绩的学生姓名,课程名和成绩

SELECT AVG(成绩) FROM 成绩表 WHERE 学号='200206002';
SELECT 学生表.`姓名`,课程表.`课程名`,成绩表.`成绩` FROM 学生表,成绩表,课程表 WHERE 学生表.`学号`=成绩表.`学号` AND 成绩表.`课程号`=课程表.`课程号` AND 成绩表.`成绩`>(SELECT AVG(成绩) FROM 成绩表 WHERE 学号='200206002');

– 48.创建一个[简单学生表]要求包含4个字段"学号,姓名,性别和所在院系",
– --然后将[学生表]中"国际贸易"或者男学生的记录全部插入到简单学生表中,最后显示插入的记录

CREATE TABLE 简单学生表(
	学号 CHAR(10) NOT NULL PRIMARY KEY,
	姓名 VARCHAR(30) NOT NULL,
	性别 CHAR(2) NOT NULL,
	所在院系 VARCHAR(10) NOT NULL
);

INSERT INTO 简单学生表 SELECT 学号,姓名,性别,所在院系 FROM 学生表 WHERE 性别='男' OR 所在院系='国际贸易'; # 插入的数据要和新创建的表字段相同，否则报错
DELETE FROM 简单学生表;
SELECT * FROM 简单学生表;

– 49.列出所有没有选修课程’C804’的学生清单

SELECT 课程号 FROM 课程表 WHERE 选修课='C804';
SELECT 学号 FROM 成绩表 WHERE 课程号 IN (SELECT 课程号 FROM 课程表 WHERE 选修课='C804');
SELECT * FROM 学生表 WHERE 学号 IN(SELECT 学号 FROM 成绩表 WHERE 课程号 IN (SELECT 课程号 FROM 课程表 WHERE 选修课='C804'));

– 50.计算每个学生的选修课门数,要求显示学生学号和选修课门数

SELECT 课程号,COUNT(选修课) AS 选修课门数 FROM 课程表 GROUP BY 选修课;

SELECT DISTINCT 学号,选修课门数 FROM(SELECT 课程号,COUNT(选修课) AS 选修课门数 FROM 课程表 GROUP BY 选修课) AS a,成绩表 WHERE  成绩表.`课程号`= a.课程号;

– 51.将计算级系的学生成绩均加5分

SELECT 学号 FROM 学生表 WHERE 所在院系='计算机';

SELECT 学号 FROM 成绩表 WHERE 学号 IN (SELECT 学号 FROM 学生表 WHERE 所在院系='计算机');

UPDATE 成绩表 SET 成绩=成绩+5 WHERE 学号 IN (SELECT 学号 FROM 学生表 WHERE 所在院系='计算机');

SELECT * FROM 成绩表;