MySQL--CRUD和面试题讲解-阿里云开发者社区

MySQL--CRUD和面试题讲解

2023-10-24 21

版权

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

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

云数据库 RDS MySQL Serverless，价值2615元额度，1个月

简介： MySQL--CRUD和面试题讲解

1.CRUD:

什么是CRUD：

CRUD是一个常用的术语，用于描述对于数据库或持久化存储系统的基本操作。CRUD 是指增加(Create)、读取(Retrieve)、更新(Update)和删除(Delete)这四个操作。

CRUD是对数据库操作的基本操作集合，它是为了描述常见的数据操作需求而产生的。通过使用这四个操作，可以实现对数据库中的数据进行增加、查询、修改和删除等操作。

创建（Create）：向数据库中插入新的数据记录。这通常通过执行 INSERT 语句来实现。
读取（Retrieve）：从数据库中获取数据记录。这通常通过执行 SELECT 语句来实现。
更新（Update）：修改数据库中已有的数据记录。这通常通过执行 UPDATE 语句来实现。
删除（Delete）：从数据库中删除数据记录。这通常通过执行 DELETE 语句来实现。

2.链表查询及聚合函数的使用：

连表查询：

连表查询（Join）是指在关系型数据库中，通过将两个或多个表按照某个条件进行关联，从中获取更丰富、更有价值的数据信息。

在SQL语言中，使用关键字"JOIN"进行连表查询，常见的连表查询类型包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全外连接（FULL OUTER JOIN）等。

以下是几个常见的连表查询示例：

内连接（INNER JOIN）：

SELECT
  t1.column1,
  t2.column2
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON
  t1.common_column = t2.common_column;

2.左连接（LEFT JOIN）：

SELECT
  t1.column1,
  t2.column2
FROM
  table1 AS t1
LEFT JOIN
  table2 AS t2
ON
  t1.common_column = t2.common_column;

3.右连接（RIGHT JOIN）：

SELECT
  t1.column1,
  t2.column2
FROM
  table1 AS t1
RIGHT JOIN
  table2 AS t2
ON
  t1.common_column = t2.common_column;

4.全外连接（FULL OUTER JOIN）：

SELECT
  t1.column1,
  t2.column2
FROM
  table1 AS t1
FULL OUTER JOIN
  table2 AS t2
ON
  t1.common_column = t2.common_column;

聚合函数：

聚合函数是一种用于计算和返回多行数据的单个值的函数，常用于对数据库中的数据进行汇总、统计和计算。SQL语言中常见的聚合函数包括：

COUNT：用于计算指定列或表中的行数。

SELECT COUNT(*) FROM table_name;

SUM：用于计算指定列的总和。

SELECT SUM(column_name) FROM table_name;

AVG：用于计算指定列的平均值。

SELECT AVG(column_name) FROM table_name;

MAX：用于找出指定列的最大值。

SELECT MAX(column_name) FROM table_name;

MIN：用于找出指定列的最小值。

SELECT MIN(column_name) FROM table_name;

GROUP_CONCAT：用于将指定列的值连接成一个字符串。

SELECT GROUP_CONCAT(column_name) FROM table_name;

3.面试题讲解：

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

SELECT
    t3.*,
    t1.score 语文,
    t2.score 数学 
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.*,
(CASE WHEN t1.cid='01' THEN    t1.score END ) 语文,
(CASE WHEN t2.cid='02' THEN    t2.score END ) 数学
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

crud输出结果：

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

CRUD输出结果：

-- 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'

crud输出结果：

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

SELECT 
s.sid,s.sname,ROUND(AVG(sc.score),2)
from
t_mysql_score sc,
t_mysql_student s
where 
sc.sid =s.sid
GROUP BY s.sid,s.sname
HAVING AVG((sc.score)>=60)

crud输出结果：

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

SELECT * from 
t_mysql_student where sid in
(SELECT sc.sid from t_mysql_score sc GROUP BY sc.sid)

crud输出结果：

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

SELECT
    s.sid,
    s.sname,
    count( sc.cid ),
    sum( sc.score ) 
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname

crud输出结果：

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

SELECT count(*) FROM t_mysql_teacher where tname like '李%'

crud输出结果：

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

SELECT
    * 
FROM
    t_mysql_student 
WHERE
    sid IN (
SELECT
    sc.sid 
FROM
    t_mysql_teacher t,
    t_mysql_score sc,
    t_mysql_course c 
WHERE
    t.tid = c.tid 
    AND sc.cid = c.cid 
    AND t.tname = "张三" 
GROUP BY
    sc.sid 
    )

crud输出结果：

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

SELECT
    s.*,
    count( sc.score ) '课程数量'
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname 
HAVING
    count( sc.score ) < ( SELECT count( 1 ) FROM t_mysql_course )

crud输出结果：

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

SELECT
    sname 
FROM
    t_mysql_student 
WHERE
    sid NOT IN (
SELECT
    sc.sid 
FROM
    t_mysql_score sc,
    t_mysql_course c,
    t_mysql_teacher t 
WHERE
    t.tid = c.tid 
    AND sc.cid = c.cid 
    AND t.tname = "张三" 
    )

crud输出结果：

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

SELECT
    s.sid,
    s.sname,
    ROUND( AVG( sc.score ), 2 ) 平均成绩 
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    sc.sid = s.sid 
    AND sc.score < 60 GROUP BY s.sid, s.sname HAVING count( sc.score ) >= 2

crud输出结果：

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

SELECT
    s.*,
    sc.score 
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid 
    AND sc.cid = "01" 
    AND sc.score < 60 
ORDER BY
    sc.score DESC

crud输出结果：

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

crud输出结果：

-- 15）查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 NAME，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为 >= 60，中等为：70-80，优良为：80-90，优秀为： >= 90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
    c.cid,
    c.cname,
    max( sc.score ) '最高分',
    min( sc.score ) '最低分',
    ROUND( AVG( sc.score ), 2 ) '平均分',
    CONCAT(ROUND(sum( IF ( sc.score >= 60, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 及格率,
    CONCAT(ROUND(sum( IF ( sc.score >= 70 AND sc.score < 80, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 中等率,
    CONCAT(ROUND(sum( IF ( sc.score >= 80 AND sc.score < 90, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 优良率,
    CONCAT(ROUND(sum( IF ( sc.score >= 90, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 优秀率,
    COUNT( sc.score ) 选修人数 
FROM
    t_mysql_score sc,
    t_mysql_course c 
WHERE
    sc.cid = c.cid 
GROUP BY
    sc.cid 
ORDER BY
    选修人数 DESC,
    c.cid ASC;

crud输出结果：

关注我的人都会找到好工作的，谢谢大家！！！

MySQL--CRUD和面试题讲解

1.CRUD:

什么是CRUD：

2.链表查询及聚合函数的使用：

连表查询：

聚合函数：

3.面试题讲解：

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像