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

╰+攻爆jí腚メ 2024-03-17 16:03 12阅读 0赞

## 一、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;

> 删除指定的数据表。

### 常用操作 ###

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

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td>连接多个表</td> 
 <td>使用<code>JOIN</code>语句连接多个数据表，进行联合查询。</td> 
 </tr> 
 <tr> 
 <td>排序数据</td> 
 <td>使用<code>ORDER BY</code>语句对查询结果进行排序。</td> 
 </tr> 
 <tr> 
 <td>分组数据</td> 
 <td>使用<code>GROUP BY</code>语句对查询结果进行分组。</td> 
 </tr> 
 <tr> 
 <td>过滤数据</td> 
 <td>使用<code>WHERE</code>子句根据条件过滤查询结果。</td> 
 </tr> 
 <tr> 
 <td>统计数据</td> 
 <td>使用聚合函数如<code>COUNT</code>、<code>SUM</code>、<code>AVG</code>等对数据进行统计。</td> 
 </tr> 
 <tr> 
 <td>备份和恢复数据</td> 
 <td>使用<code>mysqldump</code>命令备份数据库，使用<code>mysql</code>命令导入备份数据进行恢复。</td> 
 </tr> 
 </tbody> 
</table>

## 二、示例操作 ##

### 1、什么是行转列 ###

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

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

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

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

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

> 行转列可以在某些情况下更直观地展示数据，提供更简洁和易读的结果。例如，在需要将多个订单的商品项转换为每个订单的商品名称和数量时，行转列可以使结果更易于理解和分析。  
> 
> 
> 需要注意的是，行转列操作需要谨慎处理，因为可能会导致查询结果的多行变为一行，可能会增加数据处理的复杂性。在使用行转列时，需要根据具体的数据结构和查询需求选择合适的方法，并确保转换后的结果仍能满足业务要求。

### 2、案例真题 ###

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

![ae7ebc4979a343579cad2f5263f065d9.png][]

> 不多说我们直接上真题

**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;
> 
> 结果：
> 
> ![8907228ca274490d9bdee7428dd73c58.png][]

**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 = '张三' ) ) 
>     	);
> 
> 结果：
> 
> ![db8e349424814229a7b8701941f285bd.png][]

**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 = '张三' 
>     	);
> 
> 结果：
> 
> ![3f3998ab41e44974a8923fe0f5869c32.png][]

**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;
> 
> 结果：
> 
> ![aec0c3fe7f3a4f3d860db6e02905f80a.png][]

**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;
> 
> 结果：
> 
> ![cc6ef04e2fba4f2dba6edb58d79ca09a.png][]

**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;
> 
> 结果：
> 
> ![f5f8552d0efa4909a1bd5694d67894c7.png][]

**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;
> 
> 结果：
> 
> ![0f88c8ddcf8c438881a7529008aa2bcb.png][]

[ae7ebc4979a343579cad2f5263f065d9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/6a83f266e39d498abbb1c0862d7d709b.png
[8907228ca274490d9bdee7428dd73c58.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/d704994e62f748b69f88028ea7c4397e.png
[db8e349424814229a7b8701941f285bd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b5318b31118a496d98fdeeb76f443089.png
[3f3998ab41e44974a8923fe0f5869c32.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/986e3a0582d14937907040c4b8ac5796.png
[aec0c3fe7f3a4f3d860db6e02905f80a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/58d94e5b4121425d971cdc3ae5b3d761.png
[cc6ef04e2fba4f2dba6edb58d79ca09a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/529b7ffbfa2a47b5b629baa4c0509ab2.png
[f5f8552d0efa4909a1bd5694d67894c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/066e17b11a924b87b512eda8f49e96cd.png
[0f88c8ddcf8c438881a7529008aa2bcb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/4951175dc8d345c3aa5d421b341830d3.png