前提条件
请使用navicat 工具连接数据库
Mysql 数据库连接：外网地址： rm-bp188nr95fk4l9545ao.mysql.rds.aliyuncs.com外网端口： 3306 (默认端口)用户名： fanmao55密码： ABCfanmao55数据库： taofei55
基础训练（简单）😀
下面练习题都是以 数据库 taofei55 — students表
查询所有同学的所有信息；
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分）； ```sql — 使用子查询方式实现
— 语文，数学 都大于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);
10. 查询语文，数学，体育分数都大于 60分的同学；
```sql
SELECT username, SUM(score), MAX(score),MIN(score) FROM students
GROUP BY username
HAVING MIN(score) > 60 -- 最低成绩大于60
稍微复杂 😃
以下练习都在 taofei55 数据库中
表名 checkin
- 统计2021-07-01 签到的人员信息 ```sql
SELECT * from checkin WHERE checkintime like “2021-07-01%”;
2. 统计2021-07-01 共签到多少人
```sql
SELECT COUNT(DISTINCT(name)) from checkin WHERE checkintime like "2021-07-01%";
- 统计签到次数最多的人的姓名 ```sql — 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<br />
1. 统计学生的姓名（去重）
```sql
select distinct(username) from students;
统计男生同学姓名
select distinct(username) from students where sex="男";统计女生有多少人 ```sql SELECT count(DISTINCT(username)),sex from students GROUP BY sex;
4. 统计数学成绩，并降序排序
```sql
select score from students where course="数学" order by score desc;
- 查询年龄 18岁的成员的名字 ```sql select distinct (username) from students where age=18;
6. 查询年龄 19 的学生个数
```sql
select count(distinct(username)) from students where age=19;
- 查询总分最高的学生姓名
```sql
— 1. 查询最高的总分
SELECT max(s) from (
) as tmp;select username, sum(score) as s from students GROUP BY username
— 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 )
<a name="xGLM4"></a>
## 多表查询 略复杂🙃
---
checkin 表<br />
1. 查询张闯在 2021-07-02 的考勤记录
```sql
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%";查询每个同学的考勤次数 ，输出 姓名，考勤次数 ```sql select COUNT(checkintime),name from checkin GROUP BY name;
4. 考勤记录中最多考勤次数是多少？（只需要统计次数即可，统计每个人的次数然后排序）
```sql
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;
- 考勤记录中第二多考勤次数是多少？（只需要统计次数即可） ```sql 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表<br />
1. 查询购买 苹果 的所有订单信息
```sql
select * from orders
where products="苹果"
- 查询 订单记录中下订单总数超过 1 次的 人员信息 ```sql — 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 );
3. 查询 销售额最多的商品名称
```sql
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
)
- 统计订单表中的人员姓名 （去重） ```sql — 统计订单表中的人员姓名 （去重） SELECT DISTINCT(username) from orders;
select username from orders GROUP BY username;
5. 所有订单记录中，消费最高的人员名字
```sql
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表
查询 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表
deps
- 查询 姓名，工号，部门名称 ```sql select emps.emp_name, emps.emp_id,depts.dept_name from emps INNER JOIN depts on emps.dept_id=depts.dept_id;
2. 查询 客服 部门的人员 并按照工号 降序排序
```sql
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;查询人数最多的部门， 输出 部门名称，部门人数 ```sql — 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 )
<a name="oF0Ny"></a>
## 很复杂 😘
---
下面请使用 homework 数据库中相关的表<br />
1. 查询student,classes 表中共有的数据， 输出学号，姓名，性别，班级名称
```sql
SELECT sno,sname,sex,classname from student
INNER JOIN classes
ON
student.classid = classes.classid;
2. 查询student，classes 表， 输出班级名称，对应班级的人数
SELECT classname, count(sname) from student
INNER JOIN classes
ON student.classid = classes.classid
GROUP BY classname
3. 查询出语文分数最高的同学 姓名，科目，班级名称。
-- 找最高分
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="语文";
4. 查询 同学姓名，考试总分，平均成绩，并按总分排序。
SELECT sname,SUM(score),avg(score) FROM student
left JOIN scores
ON student.sno = scores.sno
GROUP BY sname
ORDER BY SUM(score) DESC
5. 查询出每个同学的考试信息，输出 姓名，班级，科目，成绩， （以姓名为主，没有数据显示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
2. 统计 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;
3. 统计 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
若有收获，就点个赞吧
0 人点赞
