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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 【MySql】重要知识点及常见面试题的解析

一,特点与用途

  1. MySQL是一个关系型数据库管理系统,常用于存储和管理大量结构化数据。
  2. 它具有高度可靠性、稳定性和性能,被广泛应用于各种规模的应用程序,从个人使用到大型企业级应用。
  3. MySQL支持多用户并发访问,可以处理并发读写操作。
  4. 它具有灵活的存储引擎,如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结果:

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1天前
|
存储 Oracle 关系型数据库
[MySQL]知识点
[MySQL]知识点
10 0
[MySQL]知识点
|
15天前
|
SQL 关系型数据库 MySQL
MySQL - 一文解析 SQL 的执行顺序
MySQL - 一文解析 SQL 的执行顺序
|
15天前
|
存储 SQL 关系型数据库
MySQL - 深入解析MySQL索引数据结构
MySQL - 深入解析MySQL索引数据结构
|
16天前
|
SQL 监控 关系型数据库
MySQL Binlog深度解析:进阶应用与实战技巧【进阶应用】
MySQL Binlog深度解析:进阶应用与实战技巧【进阶应用】
31 0
|
16天前
|
SQL 存储 关系型数据库
解析MySQL Binlog:从零开始的入门指南【binlog入门指南】
解析MySQL Binlog:从零开始的入门指南【binlog入门指南】
84 0
|
16天前
|
SQL 监控 关系型数据库
数据库日志解析:深入了解MySQL中的各类日志
数据库日志解析:深入了解MySQL中的各类日志
42 0
|
18天前
|
存储 缓存 索引
【数据结构入门精讲 | 第十四篇】散列表知识点及考研408、企业面试练习(1)
【数据结构入门精讲 | 第十四篇】散列表知识点及考研408、企业面试练习(1)
18 0
|
16小时前
|
关系型数据库 MySQL 数据安全/隐私保护
MySQL5.7非安装版的安装过程
MySQL5.7非安装版的安装过程
6 1
|
1天前
|
关系型数据库 MySQL 数据安全/隐私保护
【极光系列】Windows安装Mysql8.0版本
【极光系列】Windows安装Mysql8.0版本
9 1
|
1天前
|
关系型数据库 MySQL 开发工具
【开发工具专题_02】windows操作系统下载与安装mysql8.0.18教程
【开发工具专题_02】windows操作系统下载与安装mysql8.0.18教程
7 0

推荐镜像

更多