测试面试之数据库

测试小明

已于 2022-07-21 10:34:58 修改

阅读量638

点赞数

文章标签： mysql 数据库 sql

于 2022-07-19 20:29:28 首次发布

本文链接：https://blog.csdn.net/Dave0002/article/details/125880265

版权

本文详细介绍了MySQL数据库的相关知识，包括建表、插入、更新语句，以及一系列的SQL查询习题，涵盖联表查询、分组统计、排序、索引等。还涉及到数据库的字段添加、数据表结构查看、DDL语句、数据库引擎比较、事务隔离级别、锁的类型、ACID特性、主从同步原理等核心概念，并讨论了SQL优化策略。

摘要由CSDN通过智能技术生成

Mysql：
建表语句？
CREATE TABLE grade (
no int(11) NOT NULL,
id int(11) NOT NULL,
kemu varchar(255) DEFAULT NULL,
score int(11) DEFAULT NULL,
PRIMARY KEY (no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student (
id int(11) NOT NULL,
name varchar(255) DEFAULT NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入语句？
INSERT INTO student.grade (no, id, kemu, score) VALUES (1, 1001, ‘语文’, 85);
INSERT INTO student.grade (no, id, kemu, score) VALUES (2, 1001, ‘数学’, 86);
INSERT INTO student.grade (no, id, kemu, score) VALUES (3, 1002, ‘英语’, 98);
INSERT INTO student.grade (no, id, kemu, score) VALUES (4, 1002, ‘语文’, 94);
INSERT INTO student.grade (no, id, kemu, score) VALUES (5, 1002, ‘数学’, 98);
INSERT INTO student.grade (no, id, kemu, score) VALUES (6, 1003, ‘数学’, 56);
INSERT INTO student.grade (no, id, kemu, score) VALUES (7, 1003, ‘语文’, 69);
INSERT INTO student.grade (no, id, kemu, score) VALUES (8, 1003, ‘英语’, 68);
INSERT INTO student.grade (no, id, kemu, score) VALUES (9, 1004, ‘英语’, 99);
INSERT INTO student.grade (no, id, kemu, score) VALUES (10, 1004, ‘数学’, 97);

INSERT INTO student.student (id, name, age) VALUES (1001, ‘张三’, 18);
INSERT INTO student.student (id, name, age) VALUES (1002, ‘张四’, 19);
INSERT INTO student.student (id, name, age) VALUES (1003, ‘张五’, 20);
INSERT INTO student.student (id, name, age) VALUES (1004, ‘张六’, 19);

更新语句？
UPDATE student.student SET id=‘1001’, name=‘张三’, age=‘18’ WHERE (id=‘1001’);

#习题
#1.查询所有学生的数学成绩，显示学生姓名 name, 分数，由高到低
select t1.name,t2.score ,t2.kemu from student t1 INNER JOIN grade t2 where t1.id = t2.id and t2.kemu = “数学” ORDER BY score desc;

#2.统计每个学生的总成绩,显示字段：姓名，总成绩
select t1.name , sum(t2.score) from student t1 INNER JOIN grade t2 WHERE t1.id = t2.id group BY t1.name;

#3.统计每个学生的总成绩（由于学生可能有重复名字）,显示字段：学生 id，姓名，总成绩

select a.id , a.name ,c.sum_score from student a INNER JOIN ( select b.id,SUM(b.score) as sum_score from grade b group by id ) c where a.id = c.id ORDER BY c.sum_score desc;

#4.列出各门课程成绩最好的学生，要求显示字段: 学号，姓名,科目，成绩

select t3.id,t3.name, t2.kemu ,t2.max_score from student t3 inner join ( select t1.id , t1.kemu,max(t1.score) as max_score from grade t1 GROUP BY t1.kemu) t2 where t3.id = t2.id;

#5.列出各门课程成绩最好的 2 位学生，要求显示字段: 学号，姓名, 科目，成绩

select t1.id,t2.name,t1.kemu,t1.score from grade t1 inner JOIN student t2 where (select count(*) from grade t3 where t1.kemu = t3.kemu and t3.score > t1.score ) < 2 and t2.id = t1.id ORDER BY t1.kemu,t1.score desc;

#1.计算每个人的平均成绩，要求显示字段: 学号，姓名，平均成绩
select t1.id , t1.name, avg(t2.score) as avg_score from student t1 inner JOIN grade t2 where t1.id = t2.id GROUP BY t1.name;

#2.计算每个人的成绩，总分数，平均分，