MySQL学习笔记（10）实验三源码

CREATE TABLE student(
	Num INT(10) PRIMARY KEY,
	Name VARCHAR(20) NOT NULL,
	Sex VARCHAR(4) NOT NULL,
	Birthday YEAR,
	Dept VARCHAR(20) NOT NULL,
	Address VARCHAR(50)
);

CREATE TABLE score(
	Id INT(10) PRIMARY KEY AUTO_INCREMENT,
	Cname VARCHAR(20),
	Num INT(10) NOT NULL,
	FOREIGN KEY(Num) REFERENCES student(Num),
	Grade INT(10)
);

INSERT INTO student VALUES
(901,'张军','男',1985,'计算机系','北京市海淀区'),
(902,'张超群','男',1986,'中文系','北京市昌平区'),
(903,'张美丽','女',1990,'中文系','云南省西双版纳'),
(904,'李五一','男',1990,'英语系','辽宁省阜新市'),
(905,'王芳','女',1991,'英语系','福建省厦门市'),
(906,'王桂','男',1988,'计算机系','湖南省衡阳市');

INSERT INTO score VALUES
(NULL,'计算机',901,98),
(NULL,'英语',901,80),
(NULL,'计算机',902,65),
(NULL,'中文',902,88),
(NULL,'中文',903,95),
(NULL,'计算机',904,70),
(NULL,'英语',904,92),
(NULL,'英语',905,94),
(NULL,'计算机',906,90),
(NULL,'英语',906,85);


-- 1)	查询Student表的所有记录。
SELECT * FROM student;
SELECT Num,Name,Sex,Birthday,Dept,Address FROM student;

-- 2)从student表中查询所有学生的学号、姓名和院系信息。
SELECT Num,Name,Dept FROM student;

-- 3)查询计算机系和英语系的学生信息。
SELECT * FROM student WHERE Dept IN ('计算机系','英语系');
SELECT * FROM student WHERE Dept='计算机系' OR Dept='英语系';

--4） 从Student表中查询年龄为18到22岁的学生的信息。（生日到年龄的转换：(DATE_FORMAT(NOW(), '%Y')- birthday)）
SELECT * FROM student WHERE (DATE_FORMAT(NOW(), '%Y')- birthday) BETWEEN 18 AND 22;
SELECT * FROM student WHERE (DATE_FORMAT(NOW(), '%Y')- birthday) >=18 AND (DATE_FORMAT(NOW(), '%Y')- birthday)<=22;

--5) 从Student表中查询北京市的学生
SELECT * FROM student WHERE Address  LIKE '北京市%';

--6） 从Student表中查询姓为张，姓名为三个字的学生。
SELECT * FROM Student WHERE Name LIKE'张__';

--7)  为Student表新增一列stuDesc VARCHAR(100)，允许为NULL
ALTER TABLE student ADD stuDesc VARCHAR(100);

--8) 更新学号为903学生的Desc，设置为少数民族生
UPDATE  student SET stuDesc='少数民族生' WHERE Num=903;

--9） 查询strDesc为空的学生。 
SELECT * FROM student WHERE stuDesc IS NULL;
SELECT * FROM student WHERE stuDesc <=> NULL;

--10）从student表中统计每个院系有多少人并为统计的人数取别名sum_Dept，统计用户名详情并取别名stu_Detail.
SELECT Dept,
COUNT(*) AS sum_Dept,
GROUP_CONCAT(Name) AS stu_Detail
FROM student
GROUP BY Dept;

--11) 从score表中查询每个科目的最高分。
SELECT Cname,
MAX(Grade)
FROM score 
GROUP BY Cname;

--12）计算每个考试科目的平均成绩。
SELECT Cname,
AVG(Grade)
FROM score 
GROUP BY Cname;

--13）将计算机成绩按从高到低进行排序
SELECT * FROM score WHERE Cname='计算机' ORDER BY Grade  DESC;

--14）查询student表中的第2条到第4条的记录
SELECT * FROM Student LIMIT 1,3;

--15）查询李五一的考试科目和考试成绩
SELECT a.Name,b.Cname,b.Grade 
FROM student AS a
JOIN score AS b 
ON a.Num=b.Num
WHERE a.Name='李五一';

--16）查村所有学生的信息和考试信息
SELECT * 
FROM student AS a
JOIN score AS b 
ON a.Num=b.Num;

--17）计算每个学生的总成绩（需显示学生姓名）
SELECT a.Name,
SUM(Grade)
FROM student AS a
JOIN score AS b
ON a.Num=b.Num
GROUP BY a.Num;

-- 18) 计算计算机成绩低于95分的学生的信息
SELECT a.Num,a.Name,a.Dept,a.Address,a.Birthday ,b.Grade,b.Cname
FROM student AS a
JOIN score AS b
ON a.Num=b.Num
WHERE b.Grade<95 AND b.Cname='计算机';

SELECT Num,Name,Dept,Address,Birthday FROM student 
WHERE Num IN
(SELECT Num FROM score WHERE Grade<95 AND Cname='计算机');

-- 19）查询同时参加计算机和英语考试的学生的信息
SELECT a.Num,a.Name,a.Dept,a.Address,a.Birthday 
FROM student AS a
JOIN score AS b
ON a.Num=b.Num
JOIN score AS c
ON b.Num=c.Num
WHERE (b.Cname='计算机' AND c.Cname='英语') ;


SELECT Num,Name,Dept,Address,Birthday
FROM student
WHERE Num IN
(SELECT Num FROM score WHERE Cname='计算机')
AND Num IN
(SELECT Num FROM score WHERE Cname='英语');

-- 20）从student表和score表中查询学生的学号，然后合并查询结果
SELECT Num FROM student UNION SELECT Num FROM score;

-- 21）查询姓张或姓王同学的姓名、院系、考试科目和成绩
SELECT a.Name,a.Dept,b.Cname,b.Grade
FROM student AS a
JOIN score AS b
ON a.Num=b.Num
WHERE a.name LIKE '张%' OR  a.Name LIKE '王%';

SELECT a.Name,a.Dept,b.Cname,b.Grade
FROM (SELECT Num,Name,Dept FROM student
WHERE Name LIKE '张%' OR  Name LIKE '王%') AS a
JOIN score AS b
ON a.Num=b.Num;

-- 22) 查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。
SELECT a.Address,a.Name,(DATE_FORMAT(NOW(),'%Y')-a.Birthday) AS age,a.Dept,b.Cname,b.Grade
FROM Student AS a
JOIN Score AS b
ON a.Num=b.Num
WHERE a.Address LIKE '湖南省%';

SELECT a.Address,a.Name,(DATE_FORMAT(NOW(),'%Y')-a.Birthday) AS age,a.Dept,b.Cname,b.Grade
FROM (SELECT Num,Name,Dept,Birthday,Address FROM student
WHERE Address LIKE '湖南省%') AS a
JOIN Score AS b
ON a.Num=b.Num;