MySql数据库练习
题目要求
一.有这样一张学生成绩表如图,按要求完成操作.
-
根据上图格式,在数据库中创建一个学生成绩表,并添加数据.使用sql语句完成.
-
查询每个人的总成绩并按从高到低排名(要求显示字段:姓名,总成绩,学号)
-
查询每个人的平均成绩(要求显示字段: 学号,姓名,平均成绩)
-
查询各门课程的平均成绩(要求显示字段:课程,平均成绩)
-
查询学生数学成绩的排名,从高到低显示(要求显示字段:学号,姓名,成绩
-
查询学生成绩,按照如下格式展示, 小于60分为C,60-79为B,80-100为A
-
只查询每门成绩都及格的学生(要求显示字段: 学号,姓名,课程,最高成绩)
-
查询出选择课程数量大于3的学生(显示学生学号,姓名,课程总数)
-- 1.根据上图格式,在数据库中创建一个学生成绩表,并添加数据.使用sql语句完成.
CREATE TABLE stud(
stu_num INT,
stu_name VARCHAR(10),
SUBJECT CHAR(2),
stu_score INT
)
INSERT INTO stud(stu_num,stu_name,SUBJECT,stu_score)
VALUES(1,'张三','语文',70),
(1,'张三','数学',80),
(1,'张三','英语',66),
(2,'李四','语文',50),
(2,'李四','数学',75),
(2,'李四','英语',80),
(3,'王五','语文',77),
(3,'王五','数学',55),
(3,'王五','英语',88),
(3,'王五','物理',90)
-- 2.查询每个人的总成绩并按从高到低排名(要求显示字段:姓名,总成绩,学号)
SELECT stu_name,SUM(stu_score),stu_num
FROM stud
GROUP BY stu_num,stu_name
ORDER BY SUM(stu_score) DESC
-- 3.查询每个人的平均成绩(要求显示字段: 学号,姓名,平均成绩)
SELECT stu_num,stu_name,AVG(stu_score)
FROM stud
GROUP BY stu_num,stu_name
-- 4.查询各门课程的平均成绩(要求显示字段:课程,平均成绩)
SELECT SUBJECT,AVG(stu_score)
FROM stud
GROUP BY SUBJECT
-- 5.查询学生数学成绩的排名,从高到低显示(要求显示字段:学号,姓名,成绩
SELECT stu_num,stu_name,stu_score
FROM stud
WHERE SUBJECT ='数学'
ORDER BY stu_score DESC
-- 6.查询学生成绩,按照如下格式展示, 小于60分为C,60-79为B,80-100为A
SELECT stu_name,
SUBJECT,
(CASE WHEN stu_score>=80 THEN 'A'
WHEN stu_score>=60 AND stu_score<80 THEN 'B'
ELSE 'C'
END
) LEVEL # 定义查询出结果的别名
FROM stud
-- 7.只查询每门成绩都及格的学生(要求显示字段: 学号,姓名,课程,最高成绩)
SELECT stu_num,stu_name,MAX(stu_score)
FROM stud
WHERE stu_num NOT IN (SELECT stu_num
FROM stud
WHERE stu_score<60)
GROUP BY stu_num,stu_name
-- 8.查询出选择课程数量大于3的学生(显示学生学号,姓名,课程总数)
SELECT stu_num,stu_name,COUNT(*)
FROM stud
GROUP BY stu_num,stu_name
HAVING COUNT(*)>3
二,对该表实现分页数据查询,每页显示3条数据
分别写出sql语句查询第一页,第二页,第三页数据.
-- 每页显示3条数据分别写出sql语句查询第一页,第二页,第三页数据.
SELECT * FROM stud LIMIT 0,3;
SELECT * FROM stud LIMIT 3,3;
SELECT * FROM stud LIMIT 6,3;
一道SQL语句面试题,关于group by
表内容:
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
SELECT ddata,SUM(CASE WHEN ending='胜' THEN 1 ELSE 0 END ) '胜',SUM(CASE WHEN ending='负' THEN 1 ELSE 0 END ) '负'
FROM 表名
GROUP BY ddata
四.表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列。
SELECT (
CASE WHEN A>B
THEN (CASE WHEN A>C THEN A ELSE C END)
ELSE (CASE WHEN B>C THEN B ELSE C END)
END
)
FROM tableABC
五.请取出student表中日期(stu_reg_time字段)为当天的所有记录?
SELECT *
FROM student
WHERE DATEDIFF(reg_time,NOW())=0
MySql练习2
一
- 查询出每个学生最高成绩的科目(要求显示字段: 学号,姓名,课程,最高成绩)
- 查询各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,成绩)
-- 1. 查询出每个学生最高成绩的科目(要求显示字段: 学号,姓名,课程,最高成绩)
SELECT stud.stu_num,stud.stu_name,stud.subject,stu_score
FROM(SELECT stu_num,MAX(stu_score) AS maxScore
FROM stud
GROUP BY stu_num) t2
JOIN stud
ON stud.stu_num = t2.stu_num
AND stud.stu_score = t2.maxScore
-- 2. 查询各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,成绩)
SELECT stud.stu_num,stud.stu_name,stud.subject,stu_score
FROM(SELECT SUBJECT,MAX(stu_score) AS maxScore
FROM stud
GROUP BY SUBJECT) t2
JOIN stud
ON stud.SUBJECT = t2.SUBJECT
AND stud.stu_score = t2.maxScore
二.有两张表
部门表
员工表
两张表关联查询
- 查询所有员工信息,信息内容包括
员工主键,员工姓名,性别,年龄,工资,所属部门名称 - 查询出工资最高的员工,并显示员工信息 自查询 查询出最高工资
- 查询出每个部门工资最高的员工信息
- 查询所有男性员工工资总和,平均工资,最高工资,最低工资
- 查询工资大于平均工资的员工信息
- 查询所有姓王员工的姓名和性别
- 查询年龄最大的前3个员工的姓名和年龄
- 统计每个部门的工资总和,显示信息:部门名称,工资总和
首先用员工表与部门表关联.用部门分组 - 统计每个部门的总人数,显示信息:部门名称,部门人数
CREATE TABLE part(
id INT PRIMARY KEY AUTO_INCREMENT,
Dept_name VARCHAR(20),
Dept_Desc VARCHAR(20),
Dept_date DATE
)
INSERT INTO part(id,Dept_name,Dept_Desc,Dept_date)
VALUES(1,'研发部','开发软件','2014-12-14'),
(2,'财务部','发工资','2014-10-14'),
(3,'市场部','销售软件','2015-11-02')
CREATE TABLE people(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
sex CHAR(1) CHECK(sex='男' OR sex = '女'),
age INT,
money INT,
Dept_id VARCHAR(20)
)
INSERT INTO people(NAME,sex,age,money,Dept_id)
VALUES('王五','男',25,3000,1),
('李明','男',23,2500,1),
('王二小','男',23,2356,2),
('陈发','男',22,3600,2),
('小明','男',21,3100,3),
('苏奇','男',24,2800,3),
('王丽','女',19,1800,1),
('李芳','女',18,1900,3)
-- 1. 查询所有员工信息,信息内容包括员工主键,员工姓名,性别,年龄,工资,所属部门名称
SELECT *
FROM people
-- 2. 查询出工资最高的员工,并显示员工信息 自查询 查询出最高工资
SELECT *
FROM people p1,(SELECT MAX(money) maxmoney
FROM people) p2
WHERE p1.money = p2.maxmoney
-- 3. 查询出每个部门工资最高的员工信息
SELECT *
FROM people p1,(SELECT Dept_id, MAX(money) maxmoney
FROM people GROUP BY Dept_id) p2
WHERE p1.money = p2.maxmoney AND p1.Dept_id = p2.Dept_id
-- 4. 查询所有男性员工工资总和,平均工资,最高工资,最低工资
SELECT SUM(money),AVG(money),MAX(money),MIN(money)
FROM people
WHERE id NOT IN (SELECT id
FROM people
WHERE sex='女')
-- 5. 查询工资大于平均工资的员工信息
SELECT *
FROM people p1 ,(SELECT AVG(money) avgm
FROM people) p2
WHERE p1.money > p2.avgm
-- 6. 查询所有姓王员工的姓名和性别
SELECT NAME,sex
FROM people
WHERE NAME LIKE '王%'
-- 7. 查询年龄最大的前3个员工的姓名和年龄
SELECT *
FROM people
ORDER BY age DESC
LIMIT 3
-- 8. 统计每个部门的工资总和,显示信息:部门名称,工资总和
-- 首先用员工表与部门表关联.用部门分组
SELECT Dept_name,工资总和
FROM part t , (SELECT SUM(money) 工资总和,Dept_id FROM people GROUP BY Dept_id) p
WHERE t.id = p.Dept_id
-- 9.统计每个部门的总人数,显示信息:部门名称,部门人数
SELECT Dept_name,人数
FROM part t , (SELECT COUNT(id) 人数,Dept_id FROM people GROUP BY Dept_id) p
WHERE t.id = p.Dept_id