MySQL面试题

用户10175992

发布于 2023-10-17 10:36:37

2020

发布于 2023-10-17 10:36:37

文章被收录于专栏：辰远辰远

MYSQL题目

MySQL的存储引擎InnoDB和MYISAM的差异点

MyISAM存储引擎：只读型数据引擎，访问速度快

InnoDB存储引擎：事务性数据引擎，支持事务处理

SQL语言包括哪四个部分

DML数据操作语言，负责对数据访问工作的指令集，例如inser，update，delete语句

DDL数据定义语言，包括结构定义，操作方法定义等，例如create，drop，alter语句

DQL数据查询语言，主要是select语句

DCL数据控制语言，用来设置或更改数据库用户或角色权限的语句，例如grant，revoke语句

char和varchar的区别

char为固定长度字符串，例如在char(10)类型中存放字符串’hello’，那么会在’hello’之后补上5个空格保证’hello ’长度为10

varchar为可变长度字符串，例如在varchar(10)类型中存放字符串’hello’，那么实际值长度为5

请讲解数据库设计中的三大范式理论

1NF，原子性，确保每列都是不可再分的最小数据单元

2NF，每个表只做一件事，确保表中的列与主键相关

3NF，减少冗余，确保每个列与主键直接相关

MySQL是什么类型的数据库

关系型数据库，是建立在关系模型基础上的数据库，可以描述现实世界中的实体与实体之间的关系

什么是E-R图，E-R图如何绘制

实体关系模型图，矩形表示实体，椭圆表示属性，菱形表示关系

例如：描述学生与学生成绩之间的关系

数据库实体映射基数有多少种，分别是什么

映射表示了表与表之间的联系，一般通过外键来体现这种映射关系

一对一：例如一个人对应一张身份证，一张身份证对应一个人，两者可以放同一张表中

一对多：例如一个班级对应多个学生

多对一：与一对多相反，多个学生是同一个班级的

多对多：例如每个学生要学习多门课程，每门课程有多个学生进行学习

一般多对多的关系需要转换成1对多关系，那么就需要创建中间表来转换，在中间表中存放学生表中的主键，和课程表里面的主键，记录表（中间表）与学生表是1对多关系，与课程同样是1对多关系

TRUNCATE TABLE，DROP TABLE，DELETE语句的区别

Truncate Table与不带Where条件的Delete语句相同，可以删除表中所有行，但是truncate table 比delete速度快，且使用的系统和事务日志资源很少。truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。detele语句用于删除表中的行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行回滚操作。

trunctate table：删除内容、释放空间但不删除定义

delete ：删除内容不删除定义，不释放空间

drop table：删除内容和定义，释放空间

事务的四个特性

ACID，原子性Atomicity、一致性Consistency、隔离性Isolation、还有持久性Durability

原子性：事务是一个完整的操作，事务的各元素是不可分的，事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务失败

一致性：在事务开始之前和事务结束之后，数据库中的数据处于一致状态。

隔离性：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖或影响其他事务。

持久性：事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。

视图的作用

视图是另一种查看数据库中一个或多个表中数据的方法，视图是一种虚拟表，可以包含所有的数据，但并不是数据库中存储数据值的集合，它的行列来自查询中引用的表。视图可以隐藏部分数据，保护数据安全；可以简化复杂的sql操作。

视图可以修改数据吗

视图中的数据和结构是建立在表查询的基础上的，所以它可以像物理表一样使用，对其更新，即对实际数据库中的原始数据表进行更新，但是视图主要用于查看数据，不建议使用视图作更新，特别是视图数据来自多个表时，不允许添加和删除数据。

什么是索引

索引与书籍中的目录类似，可以协助快速查询。

为表设置索引是需要付出代价的，一是增加了数据库的存储空间，二是在修改数据的时候要花费时间更新索引

根据索引的存储类型，可以分为B-树索引，哈希索引，其中InnoDB和MyISAM存储引擎支持B-树索引

索引的分类

在数据库中创建索引通常有以下六类

唯一索引：不允许有两行具有相同索引值的索引

主键索引：在数据库关系图中为定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型，要求主键的每个值是非空的，唯一的。

聚集索引：表中行的物理顺序与键值的逻辑顺序相同，一个表只能包含一个聚集索引

非聚集索引：表中的物理顺序与键值的逻辑顺序无关，一个表可以有多个非聚集索引

如何优化查询速度（索引的角度）

a）查询时减少使用*返回全部列，不要返回不需要的列

b）索引应该尽量小，在字节数小的列上建立索引

c）Where子句中有多个表达式时，包含索引的表达式应置于其他条件表达式之前

d）避免在ordery by子句中使用表达式

e）根据业务数据发送频率，定期重新生成或重新组织索引，进行碎片整理

查询时减少使用*返回全部列，不要返回不需要的列

a）频繁搜索的列

b）经常用作查询的列

c）经常排序，分组的列

d）经常用作连接的列（主键/外键）

请不要用以下列创建索引

e）仅包含几个不同值的列

f）表中只有几行

使用SQL创建一个表Teacher表，包含两个字段，ID（编号）和Name（姓名），其中ID是主键和自增列，姓名不允许为空

    create tableTeacher(
              ID int auto_increment primary key,
              Name varchar(20) not null
    );

假设每页数据显示5行，要显示上述Teacher表中第2页的数据，请写出SQL

    select * fromTeacher
    limit 5,5

综合练习题

创建数据库和数据表（使用脚本创建，创建前写Drop语句先删除对应数据库和数据表）（20分）

学生表Student结构如下：

列名	类型	长度	是否为空	说明
sid	int		否	主键，自增，学生编号
sname	varchar	20	否	学生姓名
sage	int		否	年龄
ssex	char	2	否	性别
speciality	varchar	30	否	专业

老师表Teacher结构如下：

列名	类型	长度	是否为空	说明
tid	int		否	主键，自增，老师编号
tname	varchar	20	否	老师姓名

课程表Course结构如下：

列名	类型	长度	是否为空	说明
cid	int		否	主键，自增，课程编号
cname	varchar	50	否	课程名称
tid	int		否	外键，授课老师编号

成绩表Score结构如下：

列名	类型	是否为空	说明
id	int	否	主键，自增，成绩编号
sid	int	否	外键，学生编号
cid	int	否	外键，课程编号
score	int	否	成绩

（主键与外键可以直接在创建时添加，也可以后面通过修改表结构添加）

DROP DATABASE MyDB;
CREATE DATABASE MyDB;
USE myDb;
CREATE TABLE Student(
        sidINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        snameVARCHAR(20) NOT NULL,
        sageINT NOT NULL,
        ssexCHAR(2) NOT NULL,
        specialityVARCHAR(30) NOT NULL  #专业
);
CREATE TABLE Teacher(
        tidINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        tnameVARCHAR(20) NOT NULL
);
CREATE TABLE Course(
        cidINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        cnameVARCHAR(50) NOT NULL,
        tidINT NOT NULL,
        FOREIGNKEY(tid) REFERENCES Teacher(tid)
);
CREATE TABLE Score(
        idINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        sidINT NOT NULL,
        cidINT NOT NULL,
        scoreINT NOT NULL,
        FOREIGNKEY(sid) REFERENCES Student(sid),
        FOREIGNKEY(cid) REFERENCES Course(cid)
);

使用sql添加测试数据（有测试数据即可，无需完全相同）

Student：

Teacher：

Course：

INSERT INTO Student(sname,sage,ssex,speciality)
VALUES ('张三',20,'男','计算机软件');
INSERT INTO Student(sname,sage,ssex,speciality)
VALUES ('王芳',19,'女','计算机网络');
INSERT INTO Student(sname,sage,ssex,speciality)
VALUES ('李四',21,'男','软件工程');
INSERT INTO Student(sname,sage,ssex,speciality)
VALUES ('王五',19,'男','计算机软件');
INSERT INTO Student(sname,sage,ssex,speciality)
VALUES ('杨兰',21,'女','软件工程');

INSERT INTO Teacher(tname) 
VALUES('张老师'),('陈老师'),('李老师');

INSERT INTO Course(cname,tid)
VALUES('Java语言编程',1),
('MySQL数据库编程',2),
('数据结构',2),
('Html&CSS',3);

INSERT INTO score(sid,cid,score)
VALUES(1,1,80),(1,2,78),(1,3,67),(1,4,58),
(2,1,78),(2,2,60),(2,3,84),
(3,1,91),(3,3,88),(3,4,78),
(4,2,70),(4,3,58),(4,4,45),
(5,1,68),(5,2,65),(5,4,71);

使用sql查询

查询学生的学习情况（要求显示学生姓名，年龄，课程名称，成绩，任课老师按学生编号排序）

SELECT Sname,sage,cname,score,tname
FROM student s
JOIN score sc 
ON s.`sid` = sc.`sid`
JOIN course c
ON c.`cid` = sc.`cid`
JOIN teacher t
ON t.`tid` = c.`tid`
ORDER BY s.`sid`

创建视图，内容为题1的显示结果，并且使用视图筛选出Java语言编程的前三名同学信息

CREATE VIEW stu_info                        --创建视图create view 删除drop view 
AS
    SELECT Sname,sage,cname,score,tname
    FROM student s
    JOIN score sc 
    ON s.`sid` = sc.`sid`
    JOIN course c
    ON c.`cid` = sc.`cid`
    JOIN teacher t
    ON t.`tid` = c.`tid`
    ORDER BY s.`sid`;

SELECT * FROM stu_info                     --使用视图 select from 视图名 where
WHERE cname = 'Java语言编程'
ORDER BY score DESC
LIMIT 3;

查询平均成绩大于60分的同学的学员信息和平均成绩

SELECT Sname,sage,`avg`
FROM student
JOIN (
    SELECT sid,AVG(score) AS `avg`
    FROM Score
    GROUP BY sid                  --from表后作子查询
)temp ON temp.sid=Student.`sid`
WHERE student.sid IN(
    SELECT sid
    FROM score
    GROUP BY sid
    HAVING AVG(Score)>60
)
或
SELECT Sname,sage,(
    SELECT AVG(score)
    FROM Score
    WHERE score.`sid`=Student.`sid`   --select列作子查询
) 'avg'
FROM student
WHERE student.sid IN(
    SELECT sid
    FROM score
    GROUP BY sid
    HAVING AVG(Score)>60
)
--子查询可以用在不同的位置，select子句from子句where子句都可以
--from子句后面作子查询主要起别名！

查询没学过李老师课的同学的学号、姓名

SELECT * FROM Student
WHERE sid NOT IN(              --子查询返回多个结果时使用in连接，不在此范围可使用
    SELECT sid                   not关键字
    FROM score
    WHERE cid =
    (    
        SELECT cid FROM course
        JOIN Teacher ON
        course.`tid` = teacher.`tid`
        WHERE tname='李老师'
    )
)

查询学过Java语言编程并且也学过MySQL数据库编程课程的同学的学号、所选课程

SELECT cid FROM course               --分步骤先查询出科目的编号
WHERE cname='Java语言编程'

SELECT cid FROM course
WHERE cname='MySQL数据库编程'

SELECT Sname,cname 
FROM student
JOIN score
ON student.`sid`=score.`sid`
JOIN course
ON course.`cid` = score.`cid`
WHERE student.sid IN (
    SELECT sid
    FROM Score 
    WHERE sid IN(
        SELECT sid
        FROM Score 
        WHERE cid=(
            SELECT cid FROM course
            WHERE cname='MySQL数据库编程'
        )
    )AND cid=(
        SELECT cid FROM course
        WHERE cname='Java语言编程'
    )
)ORDER BY student.`sid`,course.`cid`

查询学过"陈老师"所教的所有课的同学的姓名，课程，任课老师

SELECT sname,cname,tname
FROM student
JOIN score
ON student.`sid`=score.`sid`
JOIN course
ON course.`cid` = score.`cid`
JOIN teacher 
ON teacher.`tid`=course.`tid`
WHERE student.sid IN (
    SELECT sid
    FROM score
    WHERE cid IN(

        SELECT cid FROM course
        JOIN teacher 
        ON course.`tid`=teacher.`tid`
        WHERE tname='陈老师'

    )
    GROUP BY sid                          --使用group by 注意select中只能是group 
    HAVING COUNT(*)=(                       by 的分组字段和聚合函数计算字段
        SELECT COUNT(*) FROM course        --使用having 筛选的不是原始数据，是聚合
        JOIN teacher                         数据
        ON course.`tid`=teacher.`tid`
        WHERE tname='陈老师'
    )
)ORDER BY student.`sid`,course.`cid`

计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

SELECT student.Sid,sName,temp.s `Max`,cname
FROM student JOIN
(
    SELECT score.Sid,MAX(Score) s
    FROM Score 
    GROUP BY score.sid               --group by 分组 
) temp
ON student.`sid`=temp.sid
JOIN score
ON score.`sid` = student.`sid`
JOIN course
ON course.`cid` = score.`cid`
WHERE score.`score`=temp.s

查询Java语言编程课程比MySQL数据库编程课程成绩高的所有学生（可以直接用课程编号）

SELECT sname,score,cid
FROM Student
JOIN score
ON student.`sid`=score.`sid` 
WHERE student.sid IN(
    SELECT a.Sid FROM
    (SELECT Sid,Score FROM SCore WHERE Cid=1) a,       --from 后子查询
    (SELECT Sid,Score FROM SCore WHERE Cid=2) b
    WHERE a.Sid=b.Sid AND a.Score>b.Score
)AND cid IN(1,2)

查询学生成绩并显示成下面表结构

--行列转置
select Sname,sum(CASE cname WHEN 'Java语言编程' THEN score END) AS 'Java语言编程',
    sum(CASE cname WHEN 'MySQL数据库编程' THEN score END) AS 'MySQL数据库编程',
    sum(CASE cname WHEN '数据结构' THEN score END) AS '数据结构',
    sum(CASE cname WHEN 'Html&CSS' THEN score END) AS 'Html&CSS'
FROM student s
JOIN score sc 
ON s.`sid` = sc.`sid`
JOIN course c
ON c.`cid` = sc.`cid`
group by s.sname

--sum可以换成max 分别作为统计总分和查询最高分的函数
-- CASE cname WHEN 'Java语言编程' THEN score END 可以替换成if(cname='Java语言编程',score,0) ——> 那么结构中的null值会变成0

在上面结果中筛选出Java成绩80分以上的同学，并且排序

select * from(
    select Sname,sum(CASE cname WHEN 'Java语言编程' THEN score END) AS 'Java语言编程',
        sum(CASE cname WHEN 'MySQL数据库编程' THEN score END) AS 'MySQL数据库编程',
        sum(CASE cname WHEN '数据结构' THEN score END) AS '数据结构',
        sum(CASE cname WHEN 'Html&CSS' THEN score END) AS 'Html&CSS'
    FROM student s
    JOIN score sc 
    ON s.`sid` = sc.`sid`
    JOIN course c
    ON c.`cid` = sc.`cid`
    group by s.sname
) as temp where `Java语言编程`>=80 
order by `Java语言编程` desc
-- from后面跟有子查询时，必须要对该查询结果起别名