【MySql】重要知识点及常见面试题的解析-阿里云开发者社区

【MySql】重要知识点及常见面试题的解析

2023-11-14 163

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS PostgreSQL，集群系列 2核4GB

简介： 【MySql】重要知识点及常见面试题的解析

一，特点与用途

MySQL是一个关系型数据库管理系统，常用于存储和管理大量结构化数据。

它具有高度可靠性、稳定性和性能，被广泛应用于各种规模的应用程序，从个人使用到大型企业级应用。

MySQL支持多用户并发访问，可以处理并发读写操作。

它具有灵活的存储引擎，如InnoDB、MyISAM等，可根据需求选择适当的存储引擎。

二，如何进行MySql中的操作语法(增删改查)

2.1数据库管理

2.1.1创建数据库：通过CREATE DATABASE 数据库名语句创建新的数据库。

create  database  Userxiaoyang

2.1.2 删除数据库：通过DROP DATABASE 数据库名语句删除现有数据库。

DROP DATABASE Userxiaoyang

2.1.3备份和恢复：可使用命令行工具（mysqldump）进行数据库备份和恢复操作。

备份数据库：

mysqldump -u 用户名 -p 密码 数据库名 > 备份文件名.sql

替换"用户名"、“密码”、"数据库名"和"备份文件名"为相应的值。执行此命令将整个数据库导出为一个SQL脚本文件，保存为指定的备份文件

恢复数据库：

mysql -u 用户名 -p 密码 数据库名 < 备份文件名.sql

替换"用户名"、“密码”、"数据库名"和"备份文件名"为相应的值。执行此命令将指定的备份文件还原到指定的数据库中

2.2 表管理：

2.1 创建表使用CREATE TABLE语句创建新的数据表，定义字段、数据类型、约束

CREATE TABLE 创建的表名 (
  列1 数据类型 约束,
  列2 数据类型 约束,
  列3 数据类型 约束,
  ...
);

实例：

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(255) UNIQUE,
  password VARCHAR(255),
  email VARCHAR(255) NOT NULL
);

上面实例包含id、username、password和email四个列。id列定义为int类型，并指定为主键。username列定义为VARCHAR(255)类型，UNIQUE并指定为唯一约束。password列定义为VARCHAR(255)类型，没有其他约束。email列定义为VARCHAR(255)类型，并指定为NOT NULL可以为空约束。

2.3 修改表的结构使用ALTER TABLE语句添加修改或删除表的列约束

2.3.1添加列

ALTER TABLE 表名 ADD 列名 数据类型 约束;

实列：

ALTER TABLE users ADD age INT;

在users表中添加age列名并为int类型

2.3.2修改列的数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;

2.3.3修改列名

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 约束;

2.3.4 删除列

ALTER TABLE 表名 DROP COLUMN 列名;

注意：这些语句将直接影响数据库结构和数据。在执行这些操作之前，请确保已经备份了重要的数据

三，聚合函数的各含义加案例

聚合函数是用于对数据进行计算和统计的函数，它们可以对一组数据进行聚合操作，返回单个值作为结果

3.1 SUM: 计算指定列的总和

示例：计算销售表中的销售总额

SELECT SUM(amount) FROM sales;

3.2 AVG: 计算指定列的平均值

示例：计算学生成绩表中的平均分数

SELECT AVG(score) FROM grades;

3.3 COUNT：计算指定列的行数

示例：计算订单表中的订单数量

SELECT COUNT(order_id) FROM orders;

3.4 MAX：获取指定列的最大值

示例：获取产品表中的最高价格

SELECT MAX(price) FROM products;

3.5 MIN：获取指定列的最小值

示例：获取库存表中的最低库存量

SELECT MIN(quantity) FROM inventory;

3.6 GROUP_CONCAT：将指定列的值以逗号分隔的形式连接起来

示例：获取订购者表中每个订购者的所有关联产品

SELECT customer_id, GROUP_CONCAT(product_name) FROM orders GROUP BY customer_id;

3.7 DISTINCT：去除重复的值，用于计算某个列的唯一值数量

示例：计算产品表中有多少个独特的产品类别

SELECT COUNT(DISTINCT category) FROM products;

四，行转列

行转列（行透视或Pivot）是一种数据转换操作，用于将以行形式存储的数据重新组织为以列形式存储的数据。在行转列操作中，作为标识行的一列（或多列）被提取出来，用作新表的列头，而原始数据行中的其他列的值则被填充到相应的新表的单元格中。常用于数据透视表、数据报表、统计分析等场景。

SELECT
  姓名,
  MAX(CASE WHEN 科目 = '语文' THEN 成绩 ELSE NULL END) AS 语文,
  MAX(CASE WHEN 科目 = '数学' THEN 成绩 ELSE NULL END) AS 数学
FROM 表名
GROUP BY 姓名;

结果：

╔══════╦══════╦══════╗
║ 姓名  ║ 语文 ║ 数学 ║
╠══════╬══════╬══════╣
║ 张三 ║ 80   ║ 90   ║
║ 李四 ║ 75   ║ 85   ║
║ 王五 ║ 90   ║ 95   ║
╚══════╩══════╩══════╝

五，面试题(15道)

模拟数据(四个表)：

-- 1.学生表-t_mysql_student -- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别

-- 2.教师表-t_mysql_teacher -- tid 教师编号,tname 教师名称

-- 3.课程表-t_mysql_course -- cid 课程编号,cname 课程名称,tid 教师名称

-- 4.成绩表-t_mysql_score -- sid 学生编号,cid 课程编号,score 成

学生表
insert into t_mysql_student values('01' , '赵雷' , '1990-01-01' , '男');
insert into t_mysql_student values('02' , '钱电' , '1990-12-21' , '男');
insert into t_mysql_student values('03' , '孙风' , '1990-12-20' , '男');
insert into t_mysql_student values('04' , '李云' , '1990-12-06' , '男');
insert into t_mysql_student values('05' , '周梅' , '1991-12-01' , '女');
insert into t_mysql_student values('06' , '吴兰' , '1992-01-01' , '女');
insert into t_mysql_student values('07' , '郑竹' , '1989-01-01' , '女');
insert into t_mysql_student values('09' , '张三' , '2017-12-20' , '女');
insert into t_mysql_student values('10' , '李四' , '2017-12-25' , '女');
insert into t_mysql_student values('11' , '李四' , '2012-06-06' , '女');
insert into t_mysql_student values('12' , '赵六' , '2013-06-13' , '女');
insert into t_mysql_student values('13' , '孙七' , '2014-06-01' , '女');
-- 教师表
insert into t_mysql_teacher values('01' , '张三');
insert into t_mysql_teacher values('02' , '李四');
insert into t_mysql_teacher values('03' , '王五');
-- 课程表
insert into t_mysql_course values('01' , '语文' , '02');
insert into t_mysql_course values('02' , '数学' , '01');
insert into t_mysql_course values('03' , '英语' , '03');
-- 成绩表
insert into t_mysql_score values('01' , '01' , 80);
insert into t_mysql_score values('01' , '02' , 90);
insert into t_mysql_score values('01' , '03' , 99);
insert into t_mysql_score values('02' , '01' , 70);
insert into t_mysql_score values('02' , '02' , 60);
insert into t_mysql_score values('02' , '03' , 80);
insert into t_mysql_score values('03' , '01' , 80);
insert into t_mysql_score values('03' , '02' , 80);
insert into t_mysql_score values('03' , '03' , 80);
insert into t_mysql_score values('04' , '01' , 50);
insert into t_mysql_score values('04' , '02' , 30);
insert into t_mysql_score values('04' , '03' , 20);
insert into t_mysql_score values('05' , '01' , 76);
insert into t_mysql_score values('05' , '02' , 87);
insert into t_mysql_score values('06' , '01' , 31);
insert into t_mysql_score values('06' , '03' , 34);
insert into t_mysql_score values('07' , '02' , 89);
insert into t_mysql_score values('07' , '03' , 98);

01)查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

分析：
    t_mysql_score表与t_mysql_student表
    1.先查询01课程的分数
    2.再查询02课程的分数
    3.进行比较成绩
    4.然后查询学生的信息和分数
SELECT
  t3.*,
  t1.score 01语文,
  t2.score 02数学 
FROM
  ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1,
  ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2,
  t_mysql_student t3 
WHERE
  t1.sid = t2.sid 
  AND t1.sid = t3.sid 
  AND t1.score > t2.score

crud结果：

02）查询同时存在" 01 "课程和" 02 "课程的情况

SELECT
    t3.*,
    t1.score 01语文,
    t2.score 02数学
FROM
    ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1,
    ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2,
    t_mysql_student t3 
WHERE
    t1.sid = t2.sid 
    AND t1.sid = t3.sid

cure结果：

03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

SELECT
  t1.* ,t2.score from ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1
  LEFT JOIN ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2 ON t1.sid = t2.sid

curd结果：

04）查询不存在" 01 "课程但存在" 02 "课程的情况

select * from t_mysql_score sc  where sc.sid not in 
( SELECT sc.sid FROM t_mysql_score sc WHERE sc.cid = '01' ) and sc.cid='02'

curd结果

05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT
    s.sid,
    s.sname,
    AVG( sc.score ) AS 平均成绩 
FROM
    t_mysql_student s
    JOIN t_mysql_score sc ON s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname 
HAVING
    AVG( sc.score ) >= 60;

curd结果：

06）查询在t_mysql_score表存在成绩的学生信息

SELECT
    s.* 
FROM
    t_mysql_student s 
WHERE
    sid IN ( SELECT sid FROM t_mysql_score )

curd结果：

07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )-- (没成绩的显示为null)

SELECT
    s.sid,
    s.sname,
    COUNT( sc.cid ) AS 选课总数,
    SUM( sc.score ) AS 所有课程的总成绩 
FROM
    t_mysql_student s
    LEFT JOIN t_mysql_score sc ON sc.sid = s.sid 
GROUP BY
    s.sid,
    s.sname;

curd结果：

08）查询「李」姓老师的数量

SELECT
    COUNT( * ) 数量 
FROM
    t_mysql_teacher 
WHERE
    tname LIKE '李%';

curd结果：

09）查询学过「张三」老师授课的同学的信息

SELECT
    * 
FROM
    t_mysql_student 
WHERE
    sid IN (
SELECT
    sid 
FROM
    t_mysql_score 
WHERE
    cid IN ( SELECT cid FROM t_mysql_course WHERE tid IN ( SELECT tid FROM t_mysql_teacher WHERE tname = '张三' ) ) 
    );

curd结果：

10）查询没有学全所有课程的同学的信息

SELECT
    s.* 
FROM
    t_mysql_student s
    LEFT JOIN t_mysql_score sc ON s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname,
    s.sage,
    s.ssex 
HAVING
    COUNT( DISTINCT sc.cid ) < ( SELECT COUNT( * ) FROM t_mysql_course );

curd结果：

11）查询没学过"张三"老师讲授的任一门课程的学生姓名

-- 第二种
SELECT
    s.* 
FROM
    t_mysql_student s 
WHERE
    s.sid NOT IN (
SELECT
    sc.sid 
FROM
    t_mysql_score sc
    JOIN t_mysql_course c ON sc.cid = c.cid
    JOIN t_mysql_teacher t ON c.tid = t.tid 
    AND t.tname = '张三' 
    );

curd结果：

12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
    s.sid,
    s.sname,
    ROUND( AVG( sc.score ), 2 ) AS 平均成绩 
FROM
    t_mysql_student s
    JOIN t_mysql_score sc ON s.sid = sc.sid 
WHERE
    sc.score < 60 GROUP BY s.sid, s.sname HAVING COUNT(sc.cid ) >= 2;

curd结果：

13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息

SELECT
    s.*,
    sc.score 
FROM
    t_mysql_student s
    JOIN t_mysql_score sc ON s.sid = sc.sid
    JOIN t_mysql_course c ON sc.cid = c.cid 
WHERE
    c.cid = '01' 
    AND sc.score < 60 
ORDER BY
    sc.score DESC;

curd结果：

14)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT
    s.sid,
    s.sname,
IF
    (
    max( CASE WHEN sc.cid = '01' THEN sc.score END ) > 0,
    max( CASE WHEN sc.cid = '01' THEN sc.score END ),
    0 
    ) 语文,
IF
    (
    max( CASE WHEN sc.cid = '02' THEN sc.score END ) > 0,
    max( CASE WHEN sc.cid = '02' THEN sc.score END ),
    0 
    ) 数学,
IF
    (
    max( CASE WHEN sc.cid = '03' THEN sc.score END ) > 0,
    max( CASE WHEN sc.cid = '03' THEN sc.score END ),
    0 
    ) 英语,
    ROUND( AVG( sc.score ), 2 ) 平均成绩 
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname

curd结果：

15）查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
    c.cid AS 课程ID,
    c.cname AS 课程名称,
    MAX(sc.score) AS 最高分,
    MIN(sc.score) AS 最低分,
    AVG(sc.score) AS 平均分,
    SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS 及格率,
    SUM(CASE WHEN sc.score >= 70 AND sc.score <= 80 THEN 1 ELSE 0 END) / COUNT(*) AS 中等率,
    SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END) / COUNT(*) AS 优良率,
    SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS 优秀率,
    COUNT(*) AS 选修人数
FROM t_mysql_course c
JOIN t_mysql_score sc ON c.cid = sc.cid
GROUP BY c.cid, c.cname
ORDER BY 选修人数 DESC, c.cid ASC;

curd结果：

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

【MySql】重要知识点及常见面试题的解析

一，特点与用途

二，如何进行MySql中的操作语法(增删改查)

2.1数据库管理

2.1.1创建数据库：通过CREATE DATABASE 数据库名 语句创建新的数据库。

2.1.2 删除数据库：通过DROP DATABASE 数据库名 语句删除现有数据库。

2.1.3备份和恢复：可使用命令行工具（mysqldump）进行数据库备份和恢复操作。

2.2 表管理：

2.1 创建表 使用CREATE TABLE语句创建新的数据表，定义字段、数据类型、约束

2.3 修改表的结构 使用ALTER TABLE语句添加 修改或删除表的列 约束

2.3.1添加列

2.3.2修改列的数据类型

2.3.3修改列名

2.3.4 删除列

三，聚合函数的各含义加案例

3.1 SUM: 计算指定列的总和

3.2 AVG: 计算指定列的平均值

3.3 COUNT：计算指定列的行数

3.4 MAX：获取指定列的最大值

3.5 MIN：获取指定列的最小值

3.6 GROUP_CONCAT：将指定列的值以逗号分隔的形式连接起来

3.7 DISTINCT：去除重复的值，用于计算某个列的唯一值数量

四，行转列

五，面试题(15道)

01)查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

02）查询同时存在" 01 "课程和" 02 "课程的情况

03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

04）查询不存在" 01 "课程但存在" 02 "课程的情况

05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

06）查询在t_mysql_score表存在成绩的学生信息

07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )-- (没成绩的显示为null)

08）查询「李」姓老师的数量

09）查询学过「张三」老师授课的同学的信息

10）查询没有学全所有课程的同学的信息

11）查询没学过"张三"老师讲授的任一门课程的学生姓名

12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息

14)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

15）查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

2.1.1创建数据库：通过CREATE DATABASE 数据库名语句创建新的数据库。

2.1.2 删除数据库：通过DROP DATABASE 数据库名语句删除现有数据库。

2.1 创建表使用CREATE TABLE语句创建新的数据表，定义字段、数据类型、约束

2.3 修改表的结构使用ALTER TABLE语句添加修改或删除表的列约束