mysql 数据库考试

mysql 数据库考试

前提条件

请使用navicat 工具连接数据库
Mysql 数据库连接：
外网地址： rm-bp188nr95fk4l9545ao.mysql.rds.aliyuncs.com
外网端口： 3306 (默认端口)
用户名： fanmao55
密码： ABCfanmao55
数据库： taofei55
Mysql 数据库连接：外网地址： rm-bp188nr95fk4l9545ao.mysql.rds.aliyuncs.com 外网端口： 3306 (默认端口) 用户名： fanmao55 密码： ABCfanmao55 数据库： taofei55

基础训练（简单）😀

下面练习题都是以数据库 taofei55 — students表
2021-06 - 图1

查询所有同学的所有信息；

select * from students;

查询所有男生同学的信息；

select * from students where sex=”男”;

查询所有女生同学的体育课程信息；

select * from students where sex=”女” and course=”体育”;

查询所有数学课成绩大于60分同学的信息；

select * from students where course=”数学” and score>60;

查询年龄在25岁以下的男生同学的体育成绩；

select * from students where sex=”男” and course=”体育” and age <25;

查询年龄在18，19，23 岁的所有同学成绩；

select * from students where age in (18,19,23);

查询姓名为2个字的同学的体育成绩；

select * from students where username like “__” and course=”体育”;

查询姓名为2个字的男同学的语文成绩和数学成绩；

select * from students where username like “__” and sex=”男” or course in (“语文”,”数学”);

查询语文和数学成绩都大于70分的学员信息（要求同时都大于70分）；

— 使用子查询方式实现

— 语文，数学都大于70分

— 1. 语文大于70 的 —人

select username from students
WHERE course=”语文” and score > 70;

— 2. 武大郎林冲嫦娥大乔数学成绩大于 70 的人

SELECT username from students
WHERE username in (“武大郎”, “林冲”, “嫦娥”, “大乔”) and score > 70 and course=”数学”;

— 3 查看武大郎嫦娥大乔三人的相信信息

SELECT * from students
WHERE username in (“武大郎”, “嫦娥”, “大乔”) and not course=”体育”

—

SELECT username from students
WHERE username in (
select username from students
WHERE course=”语文” and score > 70
) and score > 70 and course=”数学”;

—
SELECT * from students
WHERE username in (
SELECT username from students
WHERE username in (
select username from students
WHERE course=”语文” and score > 70
) and score > 70 and course=”数学”
) and not course=”体育”;

— 使用分组实现
select * from students
where username in (
SELECT username from students
WHERE course in (“语文”,”数学”)
GROUP BY username
HAVING min(score) > 70);

查询语文，数学，体育分数都大于 60分的同学；

SELECT username, SUM(score), MAX(score),MIN(score) FROM students GROUP BY username HAVING MIN(score) > 60 — 最低成绩大于60

稍微复杂 😃

以下练习都在 taofei55 数据库中
表名 checkin
2021-06 - 图2

统计2021-07-01 签到的人员信息

SELECT * from checkin WHERE checkintime like “2021-07-01%”;

统计2021-07-01 共签到多少人

SELECT COUNT(DISTINCT(name)) from checkin WHERE checkintime like “2021-07-01%”;

统计签到次数最多的人的姓名

— 1. 统计姓名出现次数最多 select count(name) from checkin GROUP BY name ORDER BY COUNT(name) desc LIMIT 1 — group by 分组分组的查询条件为 having SELECT name from checkin GROUP BY name HAVING COUNT(name) = 3 — 组合在一起 SELECT name from checkin GROUP BY name HAVING COUNT(name) = ( select count(name) from checkin GROUP BY name ORDER BY COUNT(name) desc LIMIT 1 )

学生表 students
2021-06 - 图3

统计学生的姓名（去重）

select distinct(username) from students;

统计男生同学姓名

select distinct(username) from students where sex=”男”;

统计女生有多少人

SELECT count(DISTINCT(username)),sex from students GROUP BY sex;

统计数学成绩，并降序排序

select score from students where course=”数学” order by score desc;

查询年龄 18岁的成员的名字

select distinct (username) from students where age=18;

查询年龄 19 的学生个数

select count(distinct(username)) from students where age=19;

查询总分最高的学生姓名

— 1. 查询最高的总分 SELECT max(s) from ( select username, sum(score) as s from students GROUP BY username ) as tmp; — 2.根据最高分找对应的人 SELECT username, SUM(score) from students GROUP BY username HAVING sum(score) = ( SELECT max(s) from ( select username, sum(score) as s from students GROUP BY username ) as tmp )

多表查询略复杂🙃

checkin 表
2021-06 - 图4

查询张闯在 2021-07-02 的考勤记录

select * from checkin where checkintime like “2021-07-02%” and name = “张闯”;

查询2021-07-01 考勤记录的总次数

select count(checkintime) from checkin where checkintime like “2021-07-01%”;

查询每个同学的考勤次数，输出姓名，考勤次数

select COUNT(checkintime),name from checkin GROUP BY name;

考勤记录中最多考勤次数是多少？（只需要统计次数即可，统计每个人的次数然后排序）

select max(c) as 最大考勤次数 from ( SELECT name, count(name) as c from checkin GROUP BY name ) as tmp; —— SELECT DISTINCT(count(*)) as c from checkin GROUP BY name ORDER BY c DESC LIMIT 1;

考勤记录中第二多考勤次数是多少？（只需要统计次数即可）

select max(c) as 最大考勤次数 from ( SELECT name, count(name) as c from checkin GROUP BY name ) as tmp; —— SELECT DISTINCT(count(*)) as c from checkin GROUP BY name ORDER BY c DESC LIMIT 1;

orders表
2021-06 - 图5

查询购买苹果的所有订单信息

select * from orders where products=”苹果”

查询订单记录中下订单总数超过 1 次的人员信息

— 1. 先找到订单总数的人员姓名 select username from orders group by username having count()>1 — 子查询根据人员名字找所有信息 select from orders where username in ( select username from orders group by username having count(*)>1 );

查询销售额最多的商品名称

select products from orders GROUP BY products having sum(pay) = ( SELECT max(销售额)as 最大销售额 from ( SELECT products ,sum(pay) as 销售额 from orders GROUP BY products ) as tmp ) ——— — 1. 销售额查询 select sum(pay) from orders GROUP BY products ORDER BY sum(pay) DESC limit 1 — 根据销售额找商品 select products from orders GROUP BY products HAVING SUM(pay) = ( select sum(pay) from orders GROUP BY products ORDER BY sum(pay) DESC limit 1 )

统计订单表中的人员姓名（去重）

— 统计订单表中的人员姓名（去重） SELECT DISTINCT(username) from orders; select username from orders GROUP BY username;

所有订单记录中，消费最高的人员名字

select username, sum(pay) from orders GROUP BY username having sum(pay)=( SELECT sum(pay) FROM orders GROUP BY username ORDER BY sum(pay) desc limit 1 )

students表
2021-06 - 图6

查询 age 为空的人员信息；

select * from students where age is null;

查询性别为男的人员姓名，（姓名有重复需要去重）

SELECT DISTINCT(username) FROM students WHERE sex=”男”;

查询语文分数最高的同学姓名，以及对应分数

SELECT username,course,score FROM students WHERE score=( SELECT max(score) FROM students WHERE course=”语文”) and course=”语文”;

查询男生中总分最高的同学

select username, SUM(score) from students WHERE sex = “男” GROUP BY username HAVING sum(score) = ( — 男生同学的最高分数 SELECT SUM(score) FROM students WHERE sex=”男” GROUP BY username ORDER BY SUM(score) desc LIMIT 1);

查询每个同学的总分，并按从大到小排序

SELECT username, SUM(score) FROM students GROUP BY username ORDER BY SUM(score) DESC;

emps表
2021-06 - 图7
deps
2021-06 - 图8

查询姓名，工号，部门名称

select emps.emp_name, emps.emp_id,depts.dept_name from emps INNER JOIN depts on emps.dept_id=depts.dept_id;

查询客服部门的人员并按照工号降序排序

select emps.emp_name, emps.emp_id,depts.dept_name from emps INNER JOIN depts on emps.dept_id=depts.dept_id and dept_name = “客服” ORDER BY emp_id desc

统计部门名称，部门人数

select count(emps.emp_name),depts.dept_name from emps,depts where emps.dept_id = depts.dept_id GROUP BY depts.dept_name;

查询人数最多的部门，输出部门名称，部门人数

— 1. 人数最多 select DISTINCT(COUNT()) from emps GROUP BY dept_id ORDER BY COUNT() desc LIMIT 1 — 2. 查找部门人数为3 select dept_name, COUNT(emp_name) from emps INNER JOIN depts ON depts.dept_id = emps.dept_id GROUP BY dept_name HAVING COUNT(emp_name) = ( select DISTINCT(COUNT()) from emps GROUP BY dept_id ORDER BY COUNT() desc LIMIT 1 )

很复杂 😘

下面请使用 homework 数据库中相关的表
2021-06 - 图9
1. 查询student,classes 表中共有的数据，输出学号，姓名，性别，班级名称
SELECT sno,sname,sex,classname from student INNER JOIN classes ON student.classid = classes.classid;

查询student，classes 表，输出班级名称，对应班级的人数
SELECT classname, count(sname) from student INNER JOIN classes ON student.classid = classes.classid GROUP BY classname
查询出语文分数最高的同学姓名，科目，班级名称。
— 找最高分 SELECT MAX(scores.score) from scores INNER JOIN course on scores.cno=course.cno where cname=”语文” — 根据分数招人，有可能有多人同时分数一样 SELECT sname,cname,classname FROM student INNER JOIN scores on scores.sno=student.sno INNER JOIN course on course.cno=scores.cno INNER JOIN classes on classes.classid=student.classid where scores.score=( SELECT MAX(scores.score) from scores INNER JOIN course on scores.cno=course.cno where cname=”语文”) and course.cname=”语文”;
查询同学姓名，考试总分，平均成绩，并按总分排序。
SELECT sname,SUM(score),avg(score) FROM student left JOIN scores ON student.sno = scores.sno GROUP BY sname ORDER BY SUM(score) DESC
查询出每个同学的考试信息，输出姓名，班级，科目，成绩，（以姓名为主，没有数据显示null）
select sname,classname,cname,score from student left join classes on classes.classid=student.classid left join scores on scores.sno=student.sno left join course on course.cno=scores.cno;

— 时间函数相关
1. 统计checkin 表中 3天前的所有考勤数据
SELECT * from checkin WHERE datediff(curdate(),checktime)>=3 — 今天的日期 - 时间 >=3

统计 checkin， student表中， 3天前的考勤信息，输出学生姓名，考勤次数
SELECT sname,count(sname)from checkin inner JOIN student on checkin.sno=student.sno — 添加条件 WHERE DATEDIFF(curdate(),checktime) >= 3 GROUP BY sname;
统计 55期涛飞班级中所有年龄在 18-30岁之间的学生信息
SELECT sname,classname,(year(CURDATE())-year(birthday)) as age from student INNER JOIN classes on student.classid=classes.classid WHERE classname=”涛飞55期” and year(CURDATE())-year(birthday) BETWEEN 18 and 30