MySQL的基本操作及实用示例（面试真题老实用了）

2023-09-27 360

版权

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

本文涉及的产品

云原生数据库 PolarDB 分布式版，标准版 2核8GB

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

云数据库 RDS SQL Server，基础系列 2核4GB

简介： 在MySQL中，行转列是指将数据表的行转换为列，以实现数据在视图中的重组和重塑。通常，行转列用于将多行数据合并为一行，并将多个列值作为新的列。行转列可以使用多种方法实现，包括使用聚合函数、条件筛选和透视（PIVOT）表等。以下是一些常见的行转列方法：1.使用聚合函数可以使用聚合函数如等对数据进行聚合，并使用CASE语句根据条件选择和返回不同的列值。以将不同的行值转换为列值。2.使用条件筛选可以使用条件判断语句，如IF语句或CASE语句。

一、MySQL基本操作

常用语句

MySQL是一个常用的关系型数据库管理系统，可以用于存储和管理数据。下面是MySQL的一些基本操作：

1. 连接到MySQL服务器：

mysql -u username -p

其中，username是你的用户名，执行该命令后会提示输入密码。

2. 创建数据库：

CREATE DATABASE database_name;

这将创建一个名为database_name的数据库。

3. 使用数据库：

USE database_name;

进入到指定的数据库。

4. 创建数据表：

CREATE TABLE table_name (
       column1 datatype,
       column2 datatype,
       ...
   );

这将创建一个名为table_name的数据表，其中column1、column2等为列名，datatype为列的数据类型。

5. 插入数据：

INSERT INTO table_name (column1, column2, ...)
   VALUES (value1, value2, ...);

将数据插入到指定的数据表中。

6. 查询数据：

SELECT column1, column2, ...
   FROM table_name
   WHERE condition;

查询数据表中满足条件的数据，并返回指定的列。

7. 更改数据：

UPDATE table_name
   SET column1 = value1, column2 = value2, ...
   WHERE condition;

更新数据表中满足条件的数据。

8. 删除数据：

DELETE FROM table_name
   WHERE condition;

删除数据表中满足条件的数据。

9. 删除数据表：

DROP TABLE table_name;

删除指定的数据表。

常用操作

除开以上的常用语句还有一些常用的操作

连接多个表	使用`JOIN`语句连接多个数据表，进行联合查询。
排序数据	使用`ORDER BY`语句对查询结果进行排序。
分组数据	使用`GROUP BY`语句对查询结果进行分组。
过滤数据	使用`WHERE`子句根据条件过滤查询结果。
统计数据	使用聚合函数如`COUNT`、`SUM`、`AVG`等对数据进行统计。
备份和恢复数据	使用`mysqldump`命令备份数据库，使用`mysql`命令导入备份数据进行恢复。

二、示例操作

1、什么是行转列

在MySQL中，行转列是指将数据表的行转换为列，以实现数据在视图中的重组和重塑。通常，行转列用于将多行数据合并为一行，并将多个列值作为新的列。

行转列可以使用多种方法实现，包括使用聚合函数、条件筛选和透视（PIVOT）表等。以下是一些常见的行转列方法：

1. 使用聚合函数：

可以使用聚合函数如MAX、MIN、SUM等对数据进行聚合，并使用CASE语句根据条件选择和返回不同的列值。以将不同的行值转换为列值。

2. 使用条件筛选：

可以使用条件判断语句，如IF语句或CASE语句，在查询中根据指定条件筛选和重排数据。根据条件的不同，将不同的行值转换为新的列。

3. 使用透视表（PIVOT）：

MySQL中没有内置的PIVOT操作符，但可以使用条件筛选和聚合函数来模拟透视表的功能。通过将行数据作为列名，并将对应的值填充到相应的单元格中，实现行到列的转换。

行转列可以在某些情况下更直观地展示数据，提供更简洁和易读的结果。例如，在需要将多个订单的商品项转换为每个订单的商品名称和数量时，行转列可以使结果更易于理解和分析。

需要注意的是，行转列操作需要谨慎处理，因为可能会导致查询结果的多行变为一行，可能会增加数据处理的复杂性。在使用行转列时，需要根据具体的数据结构和查询需求选择合适的方法，并确保转换后的结果仍能满足业务要求。

2、案例真题

首先我们先创建四个表，以便用来演示，如果自己创建太麻烦我已经把sql文件上传了，可自行下载导入，我这里就过多的去演示。【ps】sql文件里的数据为虚拟数据

编辑

不多说我们直接上真题

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

关于这个题我们如果有自己的想法也可以根据自己的想法来写，我这里提供了两种方式，但是结果都是一样的。它们都是用到了连表查询

第一种：

SELECT
    stu.*,
    sc1.score 语文,
    sc2.score 数学 
FROM
    ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) sc1,
    ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) sc2,
    t_mysql_student stu 
WHERE
    sc1.sid = sc2.sid 
    AND stu.sid = sc1.sid 
    AND sc1.score > sc2.score

第二种：

SELECT
    s.*,
    sc1.score 语文,
    sc2.score 数学 
FROM
    t_mysql_student s
    JOIN t_mysql_score sc1 ON s.sid = sc1.sid
    JOIN t_mysql_score sc2 ON s.sid = sc2.sid 
WHERE
    sc1.cid = '01' 
    AND sc2.cid = '02' 
    AND sc1.score > sc2.score;

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

在这个里面我们不但需要用连表查询，我们还要用到行转列

-- (CASE WHEN sc1.cid='01' THEN sc1.score END)语文        如果cid=‘01’score显示的列为语文
SELECT
    s.*,
    ( CASE WHEN sc1.cid = '01' THEN sc1.score END ) 语文,
    ( CASE WHEN sc2.cid = '02' THEN sc2.score END ) 数学 
FROM
    t_mysql_student s
    JOIN t_mysql_score sc1 ON s.sid = sc1.sid
    JOIN t_mysql_score sc2 ON s.sid = sc2.sid 
WHERE
    sc1.cid = '01' 
    AND sc2.cid = '02'

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

SELECT
    sc1.*,
    sc2.score 
FROM
    ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) sc1
    JOIN ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) sc2 ON sc1.sid = sc2.sid

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

SELECT
    * 
FROM
    t_mysql_score sc 
WHERE
    sc.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) 
    AND sc.cid = '02'

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;

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

这个用到了两个方法，用的都是连表查询，一个用的是join、一个是where

-- 第一种
SELECT
  s.* 
FROM
  t_mysql_student s
  INNER JOIN t_mysql_score sc ON sc.sid = s.sid;
-- 第二种
SELECT
  s.* 
FROM
  t_mysql_student s 
WHERE
  sid IN ( SELECT sid FROM t_mysql_score )

07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

-- (没成绩的显示为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;

结果：

编辑

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

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

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

-- 第一种
SELECT
  s.* 
FROM
  t_mysql_student s,
  t_mysql_course c,
  t_mysql_teacher t,
  t_mysql_score sc 
WHERE
  s.sid = sc.sid 
  AND sc.cid = c.cid 
  AND c.tid = t.tid 
  AND t.tname = '张三';
-- 第二种
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 = '张三' ) ) 
  );

结果：

编辑

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 );

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

-- 第一种
SELECT
  * 
FROM
  t_mysql_student 
WHERE
  sid NOT IN (
SELECT
  sid 
FROM
  t_mysql_score 
WHERE
  cid NOT IN ( SELECT cid FROM t_mysql_course WHERE tid NOT IN ( SELECT tid FROM t_mysql_teacher WHERE tname = '张三' ) ) 
  );
-- 第二种
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 = '张三' 
  );

结果：

编辑

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

我们拿到平均分之后需要用ROUND取小数点后两位

-- 第一种
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;
-- 第二种
SELECT
  s.sid,
  s.sname,
  ROUND( AVG( sc.score ), 2 ) AS 平均成绩 
FROM
  t_mysql_student s,
  t_mysql_score sc 
WHERE
  s.sid = sc.sid 
  AND sc.score < 60 GROUP BY s.sid, s.sname HAVING COUNT(sc.cid ) >= 2;

结果：

编辑

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;
-- 第二种
SELECT
  s.*,
  sc.score 
FROM
  t_mysql_student s,
  t_mysql_score sc,
  t_mysql_course c 
WHERE
  s.sid = sc.sid 
  AND sc.cid = c.cid 
  AND c.cid = '01' 
  AND sc.score < 60 
ORDER BY
  sc.score DESC;

结果：

编辑

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

SELECT s.sid, s.sname, c.cid, c.cname, sc.score, AVG(sc.score) OVER (PARTITION BY s.sid) AS average_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
ORDER BY average_score DESC;

结果：

编辑

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;

结果：

编辑

MySQL的基本操作及实用示例（面试真题老实用了）

一、MySQL基本操作

常用语句

1. 连接到MySQL服务器：

2. 创建数据库：

3. 使用数据库：

4. 创建数据表：

5. 插入数据：

6. 查询数据：

7. 更改数据：

8. 删除数据：

9. 删除数据表：

常用操作

二、示例操作

1、什么是行转列

2、案例真题

关系型数据库

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像