MySQL练习04

#创建数据库db_mydatabase3
CREATE DATABASE IF NOT EXISTS `db_mydatabase3`;

#使用数据库db_mydatabase3
USE `db_mydatabase3`;

#创建表tb_grade
CREATE TABLE IF NOT EXISTS `tb_grade`(
`gradeid` INT(11) NOT NULL,
`gradename` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`gradeid`)
);
#创建表tb_student
CREATE TABLE IF NOT EXISTS `tb_student`  (
`studentno` VARCHAR(50) NOT NULL,
`studentname` VARCHAR(50) NOT NULL,
`loginpassword` VARCHAR(50) NOT NULL,
`sex` VARCHAR(3) NOT NULL,
`phone` VARCHAR(50) NOT NULL,
`address` VARCHAR(50) NOT NULL,
`born` DATE NULL DEFAULT NULL,
`email` VARCHAR(200) NULL DEFAULT NULL,
`gradeid` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`studentno`) USING BTREE,
CONSTRAINT `fk_student_grade` FOREIGN KEY (`gradeid`) REFERENCES `tb_grade` (`gradeid`)
);

#创建表tb_subjects
CREATE TABLE IF NOT EXISTS `tb_subjects`  (
`subjectid` INT(11) NOT NULL,
`subjectname` VARCHAR(200) NOT NULL,
`classhour` INT(11) NULL DEFAULT NULL,
`gradeid` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`subjectid`),
CONSTRAINT `fk_subjects_grade` FOREIGN KEY (`gradeid`) REFERENCES `tb_grade` (`gradeid`)
)

#创建表tb_mark
CREATE TABLE IF NOT EXISTS `tb_mark`  (
`markid` INT(11) NOT NULL,
`studentno` VARCHAR(50) NULL DEFAULT NULL,
`subjectid` INT(11) NULL DEFAULT NULL,
`studentscore` INT(11) NULL DEFAULT NULL,
`examdate` DATE NULL DEFAULT NULL,
PRIMARY KEY (`markid`),
CONSTRAINT `fk_mark_student` FOREIGN KEY (`studentno`) REFERENCES `tb_student` (`studentno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_mark_subjects` FOREIGN KEY (`subjectid`) REFERENCES `tb_subjects` (`subjectid`) ON DELETE RESTRICT ON UPDATE RESTRICT
)

#向tb_grade表中插入数据
INSERT INTO `tb_grade` VALUES (1, '一年级');
INSERT INTO `tb_grade` VALUES (2, '二年级');
INSERT INTO `tb_grade` VALUES (3, '三年级');
INSERT INTO `tb_grade` VALUES (4, '四年级');

#向tb_student表中插入数据
INSERT INTO `tb_student` VALUES ('s1001', '张三', 'zhangsan', '男', '13022225555', '宿舍', '1994-01-01', '', 1);
INSERT INTO `tb_student` VALUES ('s1002', '李四', 'l1s1', '男', '13266669999', '宿舍', '1991-07-07', NULL, 1);
INSERT INTO `tb_student` VALUES ('s1003', '张丽', '123456', '女', '13099999999', '宿舍', '1992-05-06', NULL, 1);
INSERT INTO `tb_student` VALUES ('s1004', '王磊', '123456', '男', '15066668888', '西安', '1991-06-07', NULL, 1);
INSERT INTO `tb_student` VALUES ('s1005', '张丹', 'zhangsan', '女', '15036998888', '宿舍', '1992-06-07', NULL, 1);
INSERT INTO `tb_student` VALUES ('s1006', '李亮', '123456', '男', '15022226669', '西安市雁塔区', '1993-12-01', 'liliang@126.com', 1);
INSERT INTO `tb_student` VALUES ('s1007', '李丹', '123456', '女', '15036699965', '宿舍', '1992-11-11', '20161201141947@126com', 1);
INSERT INTO `tb_student` VALUES ('s1008', '王亮', '123456', '男', '15022223333', '西安科技二路', '1993-12-02', NULL, 2);
INSERT INTO `tb_student` VALUES ('s1009', '赵龙', '123456', '男', '13022229999', '西安科技二路', '1992-06-07', NULL, 2);
INSERT INTO `tb_student` VALUES ('s1010', '徐丹', '123456', '女', '15899996666', '宿舍', '1993-05-06', NULL, 2);

#向tb_subjects表中插入数据
INSERT INTO `tb_subjects` VALUES (1, 'MySQL深入', 65, 1);
INSERT INTO `tb_subjects` VALUES (2, 'java基础', 60, 2);
INSERT INTO `tb_subjects` VALUES (3, '计算机基本原理', 70, 1);
INSERT INTO `tb_subjects` VALUES (4, '毛邓概论', 61, 1);
INSERT INTO `tb_subjects` VALUES (5, '英语', 55, 1);
INSERT INTO `tb_subjects` VALUES (6, 'jsp', 40, 2);
INSERT INTO `tb_subjects` VALUES (7, '数据结构', 60, 2);
INSERT INTO `tb_subjects` VALUES (8, 'oracle', 65, 2);

#向tb_mark表中插入数据
INSERT INTO `tb_mark` VALUES (1, 's1001', 1, 80, '2015-07-01');
INSERT INTO `tb_mark` VALUES (2, 's1002', 1, 40, '2015-07-01');
INSERT INTO `tb_mark` VALUES (3, 's1001', 2, 15, '2015-07-01');
INSERT INTO `tb_mark` VALUES (4, 's1002', 2, 20, '2015-07-01');
INSERT INTO `tb_mark` VALUES (5, 's1003', 1, 60, '2015-07-01');
INSERT INTO `tb_mark` VALUES (6, 's1001', 3, 82, '2015-07-03');
INSERT INTO `tb_mark` VALUES (7, 's1001', 4, 90, '2015-07-03');
INSERT INTO `tb_mark` VALUES (8, 's1001', 5, 75, '2015-07-01');
INSERT INTO `tb_mark` VALUES (9, 's1002', 3, 65, '2015-07-03');
INSERT INTO `tb_mark` VALUES (10, 's1002', 4, 35, '2015-07-03');
INSERT INTO `tb_mark` VALUES (11, 's1002', 5, 87, '2015-07-01');
INSERT INTO `tb_mark` VALUES (12, 's1003', 2, 65, '2015-07-01');
INSERT INTO `tb_mark` VALUES (13, 's1003', 3, 45, '2015-07-03');
INSERT INTO `tb_mark` VALUES (14, 's1003', 4, 92, '2015-07-03');
INSERT INTO `tb_mark` VALUES (15, 's1003', 5, 55, '2015-07-01');
INSERT INTO `tb_mark` VALUES (16, 's1004', 1, 65, '2015-07-01');
INSERT INTO `tb_mark` VALUES (17, 's1004', 2, 0, '2015-07-01');
INSERT INTO `tb_mark` VALUES (18, 's1004', 3, 60, '2015-10-10');

#1、 查询全部一年级的学生信息。
SELECT * 
FROM tb_student
WHERE gradeid=(SELECT gradeid FROM tb_grade WHERE gradename='一年级');

#2、 查询全部二年级的学生的姓名和电话。
SELECT studentname,phone FROM tb_student WHERE gradeid=(SELECT gradeid FROM tb_grade WHERE gradename='二年级');

#3、 查询全部一年级女同学的信息。
SELECT * 
FROM tb_student 
WHERE sex = '女' AND gradeid=(SELECT gradeid FROM tb_grade WHERE gradename='一年级');

#4、 查询课时超过60的科目信息。
SELECT * 
FROM tb_subjects 
WHERE classhour > 60;

#5、 查询二年级的科目名称
SELECT * 
FROM tb_subjects 
WHERE gradeid=(SELECT gradeid FROM tb_grade WHERE gradename='二年级');

#6、 查询二年级男同学的姓名和住址。
SELECT studentname,address 
FROM tb_student 
WHERE sex = '男' 
AND gradeid=(SELECT gradeid FROM tb_grade WHERE   gradename='二年级');

#7、 查询无电子邮件的学生姓名和年级信息。
SELECT s.studentname, g.gradename
FROM tb_student s
JOIN tb_grade g ON s.gradeid = g.gradeid
WHERE s.email IS NULL;

#8、 查询出生日期在1992年之后的男学生姓名和年级信息。
SELECT s.studentname, g.gradename
FROM tb_student s
JOIN tb_grade g ON s.gradeid = g.gradeid
WHERE s.sex = '男' AND s.born > '1992-01-01';

#9、 参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
SELECT s.studentname, m.studentscore
FROM tb_student s
JOIN tb_mark m ON s.studentno = m.studentno
JOIN tb_subjects sj ON m.subjectid = sj.subjectid
WHERE sj.subjectname = '计算机基本原理' AND m.examdate = '2015-07-03';

#10、 按照出生日期查询一年级的学生信息。
SELECT * 
FROM tb_student 
WHERE gradeid = (SELECT gradeid FROM tb_grade WHERE gradename='一年级') ORDER BY born;

#11、 按成绩由高到低的次序查询参加编号为1的科目考试信息。
SELECT * 
FROM tb_mark
WHERE subjectid= 1
ORDER BY studentscore DESC;

#12、 查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
SELECT s.studentname, m.studentscore
FROM tb_student s
INNER JOIN tb_mark m ON s.studentno = m.studentno
INNER JOIN tb_subjects sj ON m.subjectid = sj.subjectid
WHERE sj.subjectname = 'MySQL深入' AND m.examdate = '2015-07-01'
ORDER BY m.studentscore DESC
LIMIT 2;

#13、 查询课时最多的科目名称及课时。
SELECT * 
FROM tb_subjects 
WHERE classhour=(SELECT MAX(classhour) FROM tb_subjects);

#14、 查询年龄最小的学生所在的年级及姓名。
SELECT s.studentname,g.gradename
FROM tb_student s,tb_grade g
WHERE s.gradeid=g.gradeid
AND born=(SELECT MAX(born) FROM tb_student);

#15、 查询考试的最低分出现在哪个科目
SELECT subjectname,studentscore
FROM tb_subjects sub,tb_mark m
WHERE sub.subjectid = m.subjectid 
AND studentscore = (SELECT MIN(studentscore)FROM tb_mark WHERE studentscore != 0);
#16、 查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
SELECT *
FROM tb_subjects sub,tb_mark m
WHERE m.subjectid = sub.subjectid
AND m.studentno = 's1001' ORDER BY examdate DESC;
#17、 查询年龄超过25周岁的学生信息
SELECT *
FROM tb_student 
WHERE TIMESTAMPDIFF(YEAR,born,CURDATE())>25;

#18、 查询1月份过生日的学生信息
SELECT *
FROM tb_student 
WHERE MONTH(born) = 1;

#19、 查询今天过生日的学生姓名及所在年级。
SELECT s.studentname, g.gradename
FROM tb_student s
INNER JOIN tb_grade g ON s.gradeid = g.gradeid
WHERE DAY(s.born) = DAY(CURDATE()) AND MONTH(s.born) = MONTH(CURDATE());

#20、 新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com
UPDATE tb_student
SET email = CONCAT('S1', DATE_FORMAT(CURDATE(),'%Y%m%d'), '@bd.com')
WHERE studentno = 's1001';

#21、 查询住址为“雁塔区”的学生姓名、电话、住址
SELECT * 
FROM tb_student 
WHERE address LIKE '%雁塔区%';

#22、 查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
SELECT * 
FROM tb_subjects sub,tb_grade g
WHERE sub.gradeid=g.gradeid AND subjectname LIKE '%计算机%' 
ORDER BY sub.gradeid DESC;

#23、 查询电话中含有以“130”开头的学生姓名，住址和电话。
SELECT studentname,address,phone 
FROM tb_student 
WHERE phone LIKE '130%' ;
 
#24、 查询姓“赵”的学号、姓名和住址。
SELECT studentno,studentname,address 
FROM tb_student 
WHERE studentname LIKE '赵%';

#25、 统计一年级女生的总人数。
SELECT COUNT(1) '一年级女生的总人数'
FROM tb_student 
WHERE sex='女' 
AND gradeid = (SELECT gradeid FROM tb_grade WHERE gradename='一年级');

#26、 查询李四总成绩
SELECT studentno,SUM(studentscore) '李四总成绩'
FROM tb_mark 
WHERE studentno IN (SELECT studentno FROM tb_student WHERE studentname ='李四' )
GROUP BY studentno;

#27、 学号为s1003的学生所有课总成绩
SELECT SUM(studentscore) '总成绩'
FROM tb_mark 
WHERE studentno ='s1003';

#28、 学号为s1003的学生考试的平均分。
SELECT AVG(studentscore) '平均分'
FROM tb_mark 
WHERE studentno='s1003';

#29、 查询一年级的科目“Mysql”的最高分、最低分、平均分。：
select MAX(studentscore) AS 最高分,MIN(studentscore) AS 最低分,AVG(studentscore) AS 平均分
from tb_mark m 
where subjectid in (select subjectid from tb_subjects where subjectname ='MySQL深入' 
and gradeid=(select gradeid from tb_grade where gradename='一年级'))
group by subjectid;

#30、 查询每个年级的总学时数，并按照升序排列。
SELECT g.gradeid, g.gradename ,SUM(classhour )
FROM tb_subjects sub
JOIN tb_grade g ON g.gradeid=sub.gradeid
GROUP BY sub.gradeid 
ORDER BY SUM(classhour);

#31、 查询每个参加考试的学员的平均分。（Group by 学号）
SELECT s.studentno,s.studentname,AVG(studentscore) 
FROM tb_mark m
JOIN tb_student s ON m.studentno=s.studentno
GROUP BY studentno; 

#32、 查询每门课程的平均分，并按照降序排列。（group by 课程）
SELECT subjectid,AVG(studentscore) '平均分'
FROM tb_mark 
GROUP BY subjectid 
ORDER BY AVG(studentscore) DESC;

#33、 查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
SELECT studentno,SUM(studentscore) '总分'
FROM tb_mark 
GROUP BY studentno 
ORDER BY SUM(studentscore) DESC;

#34、 查询一年级的平均年龄。
SELECT AVG(DATEDIFF(NOW(), tb_student.born) / 365) AS 平均年龄
FROM tb_student 
WHERE gradeid = (SELECT gradeid FROM tb_grade WHERE gradename = '一年级');

#35、 查询每个年级西安地区的学生人数。
SELECT gradeid '年级',COUNT(1) '学生人数'
FROM tb_student 
WHERE address LIKE '%西安%' 
GROUP BY gradeid;

#36、 查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
SELECT studentno,AVG(studentscore) '平均成绩'
FROM tb_mark 
GROUP BY studentno 
HAVING AVG(studentscore)>=60 
ORDER BY AVG(studentscore) DESC;

#37、 查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
SELECT s.studentno,s.studentname,COUNT(*) '次数'
FROM tb_mark m
JOIN tb_student s ON s.studentno=m.studentno
WHERE studentscore < 60 
GROUP BY studentno 
HAVING COUNT(*)>=2;

#38、 查询学生姓名、所属年级名称及联系电话。
SELECT s.studentname, g.gradename ,s.phone 
FROM tb_student s
JOIN tb_grade g ON g.gradeid=s.gradeid;

#39、 查询年级编号为1的科目名称、年级名称及学时。
SELECT s.subjectname, g.gradename, s.classhour 
FROM tb_subjects s
JOIN tb_grade g ON g.gradeid=s.gradeid
WHERE s.gradeid=1;

#40、 查询参加科目编号为1的考试的学生姓名、分数、考试日期。
SELECT * 
FROM tb_mark 
WHERE subjectid=1;

#41、 查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
SELECT s.subjectname,r.studentscore,r.examdate 
FROM tb_mark r 
JOIN tb_subjects s ON  s.subjectid=r.subjectid 
WHERE studentno='s1001';

#42、 查询所有科目的参考信息（某些科目可能还没有被考试过）
SELECT * 
FROM tb_subjects sub
LEFT JOIN tb_mark m ON sub.subjectid=m.subjectid;

#43、 查询没有被考过的科目信息。
SELECT * 
FROM tb_subjects 
WHERE subjectid 
NOT IN (SELECT DISTINCT subjectid FROM tb_mark);