Mysql-练习题

数据库登录

主机地址：rm-bp188nr95fk4l9545ao.mysql.rds.aliyuncs.com
端口号：3306
用户名：fanmao65
密码：abc@fanmao65
数据库：fanmao65_hm

student表

course科目表

score成绩表

teacher老师表

建表语句；

drop TABLE if EXISTS student;
create table student(
`sno` int(5) not null,
`sname` varchar(3),
`ssex` varchar(1),`sbirthday` datetime not null,
`class` VARCHAR(10)
);
insert into student(`sno`,`sname`,`ssex`,`sbirthday`,`class`) 
values 
("108","曾华","男","1990/9/1","20033"),
("107","匡明","男","1989/8/9","20031"),
("101","王丽","女","1992/8/9","20033"),
("109","李军","男","1991/7/8","20033"),
("103","王芳","女","1993/7/9","20031"),
("105","陆君","男","1990/7/8","20031");
drop table if EXISTS course;
create table  course( 
`cno`  VARCHAR(11) not null, 
`cname` varchar(255) not null,
`tno` int(11)not null,
primary key (`cno`)
);
insert into course
(`cno`,`cname`,`tno`)
VALUES
("3-105","计算机导论","825"),
("3-245","操作系统","804"),
("6-166","数字电路","856"),
("9-888","高等数学","831");
DROP TABLE if EXISTS score;
create table score(
`sno` VARCHAR(10) not null,
`cno` varchar(20) not null,
`degree` varchar(15) not NULL
);    
INSERT INTO `score`
(`Sno`, `Cno`,`Degree`)
values
("103","3-245","86"),
("105","3-245","75"),
("109","3-245","68"),
("103","3-105","92"),
("105","3-105","88"),
("109","3-105","76"),
("101","3-105","64"),
("107","3-105","91"),
("108","3-105","78"),
("101","6-166","85"),
("107","6-166","79"),
("108","6-166","81");
DROP TABLE if EXISTS teacher;
create table teacher
(`tno` VARCHAR(11) not null,
`tname` varchar(50) not null,
`tsex` varchar(1) null,
`tbirthday` datetime(6) not null,
`prof` varchar(50) not null ,
`depart` varchar(50) not null)
;
insert into `teacher`
(`tno`,`tname`,`tsex`,`tbirthday`,`prof`,`depart`)
values 
('804','李成','男','1958/12/1','副教授','计算机系'),
('856','张翔','男','1969/3/12','讲师','电子工程系'),
('825','汪萍','女','1972/5/5','助教','计算机系'),
('831','柳冰','女','1977/8/14','助教','电子工程系');

升级版数据
fanmao65_hm.sql

1. 查询教师表Depart列去重显示； ```sql select distinct(depart) from teacher;

SELECT depart FROM teacher GROUP BY depart;

2. 查询student表中班级为`20031`班或性别为女的同学记录；
```sql
SELECT * from student
WHERE ssex="女" or class="20031";

1. 查询20031班学生人数；

   SELECT class, COUNT(*) FROM student
   WHERE class="20031"

2. 以Cno升序Degree降序查询Score表的学号，学生姓名，学科名称，成绩；

   select student.sno, sname, cname,degree FROM student
   INNER JOIN score
   ON score.sno = student.sno
   INNER JOIN course
   ON course.cno = score.cno
   ORDER BY course.cno asc, degree desc;

3. 查询Score表中单科最高分的学生学号和课程号（子查询或排序）； ```sql — 最高分 SELECT max(degree) FROM score;

— 根据最高分找人

select student.sno, sname,cname,degree from score INNER JOIN student on student.sno = score.sno INNER JOIN course ON course.cno = score.cno

WHERE degree = (SELECT max(degree) FROM score)

6. 查询`20033`班得分在80分以上或者等于 60，61，62的学生；
```sql
select student.sno, sname,cname,degree,class from score
INNER JOIN student
on student.sno = score.sno
INNER JOIN course
ON course.cno = score.cno
where class="20033" and (degree>80 or degree in (60,61,62))

1. 查询所有班级的名称，以及每个班级中人数和平均分； ```sql — 1. 算出每个人的平均分

SELECT sno,avg(degree) from score GROUP BY sno;

— 2. 跟班级

SELECT class, COUNT(sname), avg(个人平均) from student LEFT JOIN ( SELECT sno,avg(degree) 个人平均 from score GROUP BY sno) as tmp ON tmp.sno = student.sno GROUP BY class;

8. 查询总分最高的人员姓名，班级，总分；
```sql
-- 1. 每个人的总分
SELECT sno,sum(degree) from score
GROUP BY sno;
-- 
SELECT student.sno,class,sname,总分 from student
INNER JOIN (SELECT score.sno, SUM(degree) 总分 FROM score
        GROUP BY sno
        HAVING SUM(degree) = (
            SELECT sum(degree) as 总分 from score
            GROUP BY sno
            ORDER BY SUM(degree) desc
            LIMIT 1) ) as tmp 
ON tmp.sno = student.sno;

select score.sno,sname,class,sum(degree) from student
inner join score
on student.sno=score.sno
group by score.sno,sname,class
having sum(degree)=(select sum(degree) from score
  group by sno
  order BY sum(degree) desc
  limit 1
  );

1. 给你一个这样的数据库表数据 | Y | M | Amount | | —- | —- | —- | | 2020 | 1 | 101 | | 2020 | 2 | 102 | | 2020 | 3 | 103 | | 2021 | 1 | 201 | | 2021 | 2 | 202 | | 2021 | 3 | 203 |

请写一个SQL，得出如下结果：

SELECT Y,
MAX(CASE M WHEN 1 THEN Amount ELSE 0 END) as M1,
MAX(CASE M WHEN 2 THEN Amount ELSE 0 END) as M2,
MAX(CASE M WHEN 3 THEN Amount ELSE 0 END) as M3
FROM yma 
GROUP BY Y;

简答题

1. 请分别阐述inner join、left join、right join、full join四种SQL语句的结果差异

   inner join:    只显示两个表中共有的部分；
   left join:     显示以左表为主，右表中没有显示的部分为Null
   right join:    显示以右表为主，左表中没有显示的部分为Null
   full join:     左表和右表都不做限制，所有的记录都显示，两表不足的地方均为NULL

2. 一个表中的ID有多个记录，把所有这个ID的记录查出来，并显示共有多少条记录数

3. 数据库表记录的增、删、改分别用什么语法？

2021-09-27

1. 查询 学生名字，考试科目，考试成绩，班级名称，科目对应的老师姓名，系别名称； ```javascript

— 学生名字，考试科目，考试成绩，班级名称，科目对应的老师姓名，系别名称 SELECT sname, cname,degree,class,tname,depart FROM student INNER JOIN score ON score.sno = student.sno INNER JOIN course ON course.cno = score.cno INNER JOIN teacher ON teacher.tno = course.tno;

2. 查询 每个班级中年龄最大的学生，显示出 班级名，学生名，对应的年龄；
```javascript
-- 1. 统计每个学生的年龄 
SELECT sno,sname, class, YEAR(NOW())-YEAR(sbirthday) as age from student;
-- 2. 每个班最大年龄
SELECT class,  MAX(YEAR(NOW())-YEAR(sbirthday)) as age from student
GROUP BY class
-- 3. 两个临时表合并。
SELECT a.class,sname,age from (SELECT class,sname,year(now())-year(sbirthday) age from student) a
inner join (SELECT class,max(year(now())-year(sbirthday)) age1 from student
group by class) b
on a.class=b.class and a.age = b.age1;

2021-09-28

1. 统计每个班级中年龄最小的同学姓名，学号，同学所在班级，考试总分。 ```javascript

— 年龄最小的同学 select * from (SELECT a.class,sname, a.sno, age from (SELECT class,sno, sname,year(now())-year(sbirthday) age from student) a inner join (SELECT class,min(year(now())-year(sbirthday)) age1 from student group by class) b on a.class=b.class and a.age = b.age1) as tmp1

— 统计每个同学的总成绩

select * from (SELECT sno,sum(degree) from score GROUP BY sno) as tmp2

— 合并 select * from (SELECT a.class,sname, a.sno, age from (SELECT class,sno, sname,year(now())-year(sbirthday) age from student) a inner join (SELECT class,min(year(now())-year(sbirthday)) age1 from student group by class) b on a.class=b.class and a.age = b.age1) as tmp1 INNER JOIN (SELECT sno,sum(degree) from score GROUP BY sno) as tmp2

ON tmp1.sno = tmp2.sno;

---
<a name="FR00d"></a>
## 2021-09-29
1. 统计一下每个班中`张`姓 同学的个数。
```sql
select class,count(*) from student
WHERE sname like "张%"
GROUP BY class

1. 查询 参加过 汪老师的课程同学人数。

   SELECT COUNT(*) from score WHERE cno= (select cno from course
   WHERE tno = (select tno FROM teacher
   WHERE tname like "汪%"))

   SELECT count(*) from teacher,course,score
   WHERE teacher.tno = course.tno and score.cno = course.cno and tname like "汪%";

2021-10-06

1. 查询90后的男生，女生人数； ```json — 90 后

SELECT * from student WHERE YEAR(sbirthday)BETWEEN 1990 and 1999

— 分组 SELECT ssex, count(*) from student WHERE YEAR(sbirthday)BETWEEN 1990 and 1999 GROUP BY ssex;

2. 统计每个班缺考的人的姓名，班级，缺考科目。
```json
-- 总共的科目 
select COUNT(*) from course;
-- 统计 每个学生考试 少于 4门科目的学生 学号 
SELECT sno, COUNT(*) from score
GROUP BY sno
HAVING COUNT(*) < (select COUNT(*) from course)
-- 学生 信息
select * from student
WHERE sno in (
    SELECT sno from score
    GROUP BY sno
    HAVING COUNT(*) < (select COUNT(*) from course)
)
-- 缺考的学生 考试的信息 
select * from score
WHERE sno in (
SELECT sno from score
    GROUP BY sno
    HAVING COUNT(*) < (select COUNT(*) from course)
)
-- 
SELECT * from course
LEFT JOIN ( select * from score
WHERE sno in (
SELECT sno from score
    GROUP BY sno
    HAVING COUNT(*) < (select COUNT(*) from course)
) ) as tmpa 
ON tmpa.cno = course.cno
WHERE tmpa.sno =100
select sname,class,cname from (student,course) left JOIN score on course.cno = score.cno and student.sno = score.sno
where degree is null;
SELECT student.sno,sname,class,计算机,操作系统,数字电路,高等数学 from student
INNER JOIN (SELECT sno,
MAX(CASE cno WHEN "3-105" THEN degree ELSE "-" END) as "计算机",
MAX(CASE cno WHEN "3-245" THEN degree ELSE "-" END) as "操作系统",
MAX(CASE cno WHEN "6-166" THEN degree ELSE "-" END) as "数字电路",
MAX(CASE cno WHEN "9-888" THEN degree ELSE "-" END) as "高等数学"
FROM score 
GROUP BY sno)as a on student.sno=a.sno