MySQL练习题目

分享一个MySQL 数据库的练习题

创建一个数据库：

DROP DATABASE IF EXISTS student;
CREATE DATABASE student;
USE student;

CREATE TABLE student(
	studentno VARCHAR(10) NOT NULL PRIMARY KEY,
	studentname VARCHAR(20) NOT NULL,
	loginpwd VARCHAR(8) NOT NULL,
	sex CHAR(1) ,
	majorid INT NOT NULL REFERENCES grade(majorid),
	phone VARCHAR(11),
	email VARCHAR(20) ,
	borndate DATETIME
);

创建表

CREATE TABLE student(
	studentno VARCHAR(10) NOT NULL PRIMARY KEY,
	studentname VARCHAR(20) NOT NULL,
	loginpwd VARCHAR(8) NOT NULL,
	sex CHAR(1) ,
	majorid INT NOT NULL REFERENCES grade(majorid),
	phone VARCHAR(11),
	email VARCHAR(20) ,
	borndate DATETIME
);

CREATE TABLE major(
	majorid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	majorname VARCHAR(20) NOT  NULL

);
CREATE TABLE result(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	studentno VARCHAR(10) NOT NULL REFERENCES student(studentno),
	score DOUBLE
);

插入数据：

INSERT INTO major VALUES(NULL,'javaee');
INSERT INTO major VALUES(NULL,'html5');
INSERT INTO major VALUES(NULL,'android');


INSERT INTO student VALUES('S001','张三封','8888','男',1,'13288886666','zhangsanfeng@126.com','1966-9-1');
INSERT INTO student VALUES('S002','殷天正','8888','男',1,'13888881234','yintianzheng@qq.com','1976-9-2');
INSERT INTO student VALUES('S003','周伯通','8888','男',2,'13288886666','zhoubotong@126.com','1986-9-3');
INSERT INTO student VALUES('S004','张翠山','8888','男',1,'13288886666',NULL,'1995-9-4');
INSERT INTO student VALUES('S005','小小张','8888','女',3,'13288885678','xiaozhang@126.com','1990-9-5');

INSERT INTO student VALUES('S006','张无忌','8888','男',2,'13288886666','zhangwuji@126.com','1998-8-9');
INSERT INTO student VALUES('S007','赵敏','0000','女',1,'13288880987','zhaomin@126.com','1998-6-9');
INSERT INTO student VALUES('S008','周芷若','6666','女',1,'13288883456','zhouzhiruo@126.com','1996-7-9');
INSERT INTO student VALUES('S009','殷素素','8888','女',1,'13288886666','yinsusu@163.com','1986-1-9');
INSERT INTO student VALUES('S010','宋远桥','6666','男',3,'1328888890','songyuanqiao@qq.com','1996-2-9');


INSERT INTO student VALUES('S011','杨不悔','6666','女',2,'13288882345',NULL,'1995-9-9');
INSERT INTO student VALUES('S012','杨逍','9999','男',1,'13288885432',NULL,'1976-9-9');
INSERT INTO student VALUES('S013','纪晓芙','9999','女',3,'13288888765',NULL,'1976-9-9');
INSERT INTO student VALUES('S014','谢逊','9999','男',1,'13288882211',NULL,'1946-9-9');
INSERT INTO student VALUES('S015','宋青书','9999','男',3,'13288889900',NULL,'1976-6-8');



INSERT INTO result VALUES(NULL,'s001',100);
INSERT INTO result VALUES(NULL,'s002',90);
INSERT INTO result VALUES(NULL,'s003',80);

INSERT INTO result VALUES(NULL,'s004',70);
INSERT INTO result VALUES(NULL,'s005',60);
INSERT INTO result VALUES(NULL,'s006',50);
INSERT INTO result VALUES(NULL,'s006',40);
INSERT INTO result VALUES(NULL,'s005',95);
INSERT INTO result VALUES(NULL,'s006',88);

题目：

#一、查询每个专业的学生人数
#二、查询参加考试的学生中，每个学生的平均分、最高分
#三、查询姓张的每个学生的最低分大于60的学号、姓名
#四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称
#五、查询每个专业的男生人数和女生人数分别是多少
#六、查询专业和张翠山一样的学生的最低分
#七、查询大于60分的学生的姓名、密码、专业名
#八、按邮箱位数分组，查询每组的学生个数
#九、查询学生名、专业名、分数
#十、查询哪个专业没有学生，分别用左连接和右连接实现
#十一、查询没有成绩的学生人数

测试结果：

#一、查询每个专业的学生人数
SELECT s.majorid,count(*) ,m.majorname
FROM student s
join major m
on s.majorid = m.majorid
GROUP BY majorid

#二、查询参加考试的学生中，每个学生的平均分、最高分
SELECT studentno,avg(score),max(score) 
from result 
GROUP BY studentno 


#三、查询姓张的每个学生的最低分大于60的学号、姓名
SELECT s.studentno,s.studentname,MIN(score)
FROM student s
join result r
on s.studentno = r.studentno
where studentname LIKE '张%'
GROUP BY studentno
HAVING MIN(score)>60



#四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称
SELECT s.studentname,m.majorname,borndate
from student s
join major m
on s.majorid = m.majorid
WHERE DATEDIFF(borndate,'1988-1-1')
GROUP BY m.majorid

#五、查询每个专业的男生人数和女生人数分别是多少
SELECT m.majorid, sex ,count(*)
FROM major m
JOIN student s
on m.majorid = s.majorid
GROUP BY majorid,sex
ORDER BY majorid


#六、查询专业和张翠山一样的学生的最低分
SELECT majorid from student WHERE studentname = '张翠山'

SELECT studentno from student WHERE majorid = (
	SELECT majorid from student WHERE studentname = '张翠山'
)

SELECT min(score) FROM result WHERE studentno in(
	SELECT studentno from student WHERE majorid = (
	SELECT majorid from student WHERE studentname = '张翠山'
)
)



#七、查询大于60分的学生的姓名、密码、专业名
SELECT s.studentname,loginpwd,m.majorname,r.score
FROM student s
join major m
on s.majorid = m.majorid
JOIN result r
on s.studentno = r.studentno
WHERE score>60

#八、按邮箱位数分组，查询每组的学生个数
SELECT LENGTH(email),count(*)
FROM student
GROUP BY LENGTH(email)


#九、查询学生名、专业名、分数
SELECT s.studentname,m.majorname,r.score
FROM student s
JOIN major m
on s.majorid = m.majorid
JOIN result r
on s.studentno = r.studentno

#十、查询哪个专业没有学生，分别用左连接和右连接实现
SELECT m.* 
FROM major m
LEFT JOIN student s
on m.majorid = s.majorid
WHERE studentname is NULL


SELECT s.* ,m.*
FROM student s
RIGHT JOIN major m
on m.majorid = s.majorid
WHERE studentname is NULL


#十一、查询没有成绩的学生人数
SELECT distinct studentno FROM result

SELECT * FROM student WHERE studentno NOT IN(
	SELECT distinct studentno FROM result
)

小结：
看到这些题，开始真的懵掉了，就写不来5个。长时间不去写SQL语句，发现生疏太多太多了，原来学的的东西都忘记了，。我都怀疑这东西，老师讲的我学过吗。往后还是要坚持去看去写。多多重复，百炼成钢吧！！！！！！！