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

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

结果：