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

posted @ 2021-12-08 14:01 肖帆咪阅读(59) 评论(0) 编辑收藏举报

刷新页面返回顶部

肖帆咪