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;