AI 助理
备案控制台
开发者社区 数据库 文章 正文

半个月时间把MySQL重新巩固了一遍，梳理了一篇几万字 “超硬核” 文章！（六）

2023-02-16 120
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介： 半个月时间把MySQL重新巩固了一遍，梳理了一篇几万字 “超硬核” 文章！

2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT
  st.*,
  s.s_score AS 数学,
  s2.s_score AS 语文 
FROM
  student st
  LEFT JOIN score s ON s.s_id = st.s_id 
  AND s.c_id = '01'
  LEFT JOIN score s2 ON s2.s_id = st.s_id 
  AND s2.c_id = '02' 
WHERE
  s.s_score < s2.s_score

c81e1b5d4cb648309ef7af9188dc7e27.png

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

SELECT
  st.s_id AS '学生编号',
  st.s_name AS '学生姓名',
  AVG( s.s_score ) AS avgScore 
FROM
  student st
  LEFT JOIN score s ON st.s_id = s.s_id 
GROUP BY
  st.s_id 
HAVING
  avgScore >= 60

d40e8352558144abb70b2ed8b62d3b76.png

4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

  • (包括有成绩的和无成绩的)
SELECT
  st.s_id AS '学生编号',
  st.s_name AS '学生姓名',(
  CASE
      WHEN ROUND( AVG( sc.s_score ), 2 ) IS NULL THEN
      0 ELSE ROUND( AVG( sc.s_score ), 2 ) 
    END 
    ) 
  FROM
    student st
    LEFT JOIN score sc ON st.s_id = sc.s_id 
  GROUP BY
    st.s_id 
  HAVING
  AVG( sc.s_score )< 60 
  OR AVG( sc.s_score ) IS NULL

5ba40851785d487ea6bfc324017c083b.png

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

SELECT
  st.s_id AS '学生编号',
  st.s_name AS '学生姓名',
  COUNT( sc.c_id ) AS '选课总数',
  sum( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS '总成绩' 
FROM
  student st
  LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
  st.s_id

2be40b7b279b48beadbba9516c62b79b.png

6. 查询"流"姓老师的数量

SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '流%'

755f943200384ed7b982f18a78d23ac6.png

7. 查询学过"流浪法师"老师授课的同学的信息

SELECT
  st.* 
FROM
  student st
  LEFT JOIN score sc ON sc.s_id = st.s_id
  LEFT JOIN course cs ON cs.c_id = sc.c_id
  LEFT JOIN teacher tc ON tc.t_id = cs.t_id 
  WHERE tc.t_name = '流浪法师'

b9f30c4b1e194dccbf41a4fdbab3d094.png

8. 查询没学过"张三"老师授课的同学的信息

-- 查询流浪法师教的课
SELECT
  cs.* 
FROM
  course cs
  LEFT JOIN teacher tc ON tc.t_id = cs.t_id 
WHERE
  tc.t_name = '流浪法师'
-- 查询有流浪法师课程成绩的学生id
SELECT
  sc.s_id 
FROM
  score sc 
WHERE
  sc.c_id IN (
  SELECT
    cs.c_id 
  FROM
    course cs
    LEFT JOIN teacher tc ON tc.t_id = cs.t_id 
  WHERE
  tc.t_name = '流浪法师')
-- 取反，查询没有学过流浪法师课程的同学信息
SELECT
  st.* 
FROM
  student st 
WHERE
  st.s_id NOT IN (
  SELECT
    sc.s_id 
  FROM
    score sc 
  WHERE
  sc.c_id IN ( SELECT cs.c_id FROM course cs LEFT JOIN teacher tc ON tc.t_id = cs.t_id WHERE tc.t_name = '流浪法师' ) 
  )

bfc3df5291fe4daa9c7ffae37edffb34.png

9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

  • 方法 1
-- 查询学过编号为01课程的同学id
SELECT
  st.s_id 
FROM
  student st
  INNER JOIN score sc ON sc.s_id = st.s_id
  INNER JOIN course cs ON cs.c_id = sc.c_id 
  AND cs.c_id = '01';
-- 查询学过编号为02课程的同学id
SELECT
  st2.s_id 
FROM
  student st2
  INNER JOIN score sc2 ON sc2.s_id = st2.s_id
  INNER JOIN course cs2 ON cs2.c_id = sc2.c_id 
  AND cs2.c_id = '02';
-- 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT
  st.* 
FROM
  student st
  INNER JOIN score sc ON sc.s_id = st.s_id
  INNER JOIN course cs ON cs.c_id = sc.c_id 
  AND sc.c_id = '01' 
WHERE
  st.s_id IN (
  SELECT
    st2.s_id 
  FROM
    student st2
    INNER JOIN score sc2 ON sc2.s_id = st2.s_id
    INNER JOIN course cs2 ON cs2.c_id = sc2.c_id 
    AND cs2.c_id = '02' 
  );

b0de35338ddb41c297853e16415b799c.png

  • 方法 2
SELECT
  a.* 
FROM
  student a,
  score b,
  score c 
WHERE
  a.s_id = b.s_id 
  AND a.s_id = c.s_id 
  AND b.c_id = '01' 
  AND c.c_id = '02';

b0de35338ddb41c297853e16415b799c.png

10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT
  st.s_id 
FROM
  student st
  INNER JOIN score sc ON sc.s_id = st.s_id
  INNER JOIN course cs ON cs.c_id = sc.c_id 
  AND cs.c_id = '01' 
WHERE
  st.s_id NOT IN (
  SELECT
    st.s_id 
  FROM
    student st
    INNER JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course cs ON cs.c_id = sc.c_id 
    AND cs.c_id = '02' 
  );

1512bc3e14a148a0b150fbb0a1a10e30.png

11. 查询没有学全所有课程的同学的信息

  • 方法 1
SELECT
  * 
FROM
  student 
WHERE
  s_id NOT IN (
  SELECT
    st.s_id 
  FROM
    student st
    INNER JOIN score sc ON sc.s_id = st.s_id 
    AND sc.c_id = '01' 
  WHERE
    st.s_id IN (
    SELECT
      st.s_id 
    FROM
      student st
      INNER JOIN score sc ON sc.s_id = st.s_id 
      AND sc.c_id = '02' 
    WHERE
      st.s_id 
    ) 
    AND st.s_id IN (
    SELECT
      st.s_id 
    FROM
      student st
      INNER JOIN score sc ON sc.s_id = st.s_id 
      AND sc.c_id = '03' 
    WHERE
      st.s_id 
    ) 
  );

8041a58830fc4c408549b0ff58e83f60.png

  • 方法 2
SELECT
  a.* 
FROM
  student a
  LEFT JOIN score b ON a.s_id = b.s_id 
GROUP BY
  a.s_id 
HAVING
  COUNT( b.c_id ) != '3';

8041a58830fc4c408549b0ff58e83f60.png

12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT DISTINCT
  st.* 
FROM
  student st
  LEFT JOIN score sc ON sc.s_id = st.s_id 
WHERE
  sc.c_id IN ( SELECT sc2.c_id FROM student st2 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id WHERE st2.s_id = '01' );

93079ce0ebef4b2393d052c6e0c9ed5f.png

13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT
  st.* 
FROM
  student st
  LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
  st.s_id 
HAVING
  GROUP_CONCAT( sc.c_id )=(
  SELECT
    GROUP_CONCAT( sc2.c_id ) 
  FROM
    student st2
    LEFT JOIN score sc2 ON sc2.s_id = st2.s_id 
  WHERE
    st2.s_id = '01' 
  );

71e23426fcbb4f20815aab7d415fd310.png

14. 查询没学过"邪恶小法师"老师讲授的任一门课程的学生姓名

SELECT
  * 
FROM
  student 
WHERE
  s_id NOT IN (
  SELECT
    sc.s_id 
  FROM
    score sc
    INNER JOIN course cs ON cs.c_id = sc.c_id
  INNER JOIN teacher t ON t.t_id = cs.t_id 
  AND t.t_name = '邪恶小法师');

060ccf91bfea404cbef40c5991e5a1d3.png

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

SELECT
  st.s_id AS '学号',
  st.s_name AS '姓名',
  AVG( sc.s_score ) AS '平均成绩' 
FROM
  student st
  LEFT JOIN score sc ON sc.s_id = st.s_id 
WHERE
  sc.s_id IN (
  SELECT
    sc.s_id 
  FROM
    score sc 
  WHERE
    sc.s_score < 60 
    OR sc.s_score IS NULL 
  GROUP BY
    sc.s_id 
  HAVING
    COUNT( 1 )>= 2 
  ) 
GROUP BY
  st.s_id

393799bbd8e54effbfb4b9de125f6640.png


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

SELECT
  st.* 
FROM
  student st
  INNER JOIN score sc ON sc.s_id = st.s_id 
  AND sc.c_id = '01' 
  AND sc.s_score < '60' 
ORDER BY
  sc.s_score DESC;
SELECT
  st.* 
FROM
  student st
  LEFT JOIN score sc ON sc.s_id = st.s_id 
WHERE
  sc.c_id = '01' 
  AND sc.s_score < '60' 
ORDER BY
  sc.s_score DESC;

83da236ada9540b9bb0d9c0237f2366e.png

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

  • 方法 1
SELECT
  st.*,
  AVG( sc4.s_score ) AS '平均分',
  sc.s_score AS '语文',
  sc2.s_score AS '数学',
  sc3.s_score AS '英语' 
FROM
  student st
  LEFT JOIN score sc ON sc.s_id = st.s_id 
  AND sc.c_id = '01'
  LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
  AND sc2.c_id = '02'
  LEFT JOIN score sc3 ON sc3.s_id = st.s_id 
  AND sc3.c_id = '03'
  LEFT JOIN score sc4 ON sc4.s_id = st.s_id 
GROUP BY
  st.s_id 
ORDER BY
  AVG( sc4.s_score ) DESC;

0b8be12869ba43d984ca7a379135bf93.png

  • 方法 2
SELECT
  st.*,
  ( CASE WHEN AVG( sc4.s_score ) IS NULL THEN 0 ELSE AVG( sc4.s_score ) END ) AS '平均分',
  ( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS '语文',
  ( CASE WHEN sc2.s_score IS NULL THEN 0 ELSE sc2.s_score END ) AS '数学',
  ( CASE WHEN sc3.s_score IS NULL THEN 0 ELSE sc3.s_score END ) AS '英语' 
FROM
  student st
  LEFT JOIN score sc ON sc.s_id = st.s_id 
  AND sc.c_id = '01'
  LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
  AND sc2.c_id = '02'
  LEFT JOIN score sc3 ON sc3.s_id = st.s_id 
  AND sc3.c_id = '03'
  LEFT JOIN score sc4 ON sc4.s_id = st.s_id 
GROUP BY
  st.s_id 
ORDER BY
  AVG( sc4.s_score ) DESC;


5688db7698f14d8d93486bb9ada52d95.png

18. 查询各科成绩最高分、最低分和平均分：

  • 以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
  • 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT
  cs.c_id,
  cs.c_name,
  MAX( sc1.s_score ) AS '最高分',
  MIN( sc2.s_score ) AS '最低分',
  AVG( sc3.s_score ) AS '平均分',
  ((
    SELECT
      COUNT( s_id ) 
    FROM
      score 
    WHERE
      s_score >= 60 
      AND c_id = cs.c_id 
      )/(
    SELECT
      COUNT( s_id ) 
    FROM
      score 
    WHERE
      c_id = cs.c_id 
    )) AS '及格率',
  ((
    SELECT
      COUNT( s_id ) 
    FROM
      score 
    WHERE
      s_score >= 70 
      AND s_score < 80 
      AND c_id = cs.c_id 
      )/(
    SELECT
      COUNT( s_id ) 
    FROM
      score 
    WHERE
      c_id = cs.c_id 
    )) AS '中等率',
  ((
    SELECT
      COUNT( s_id ) 
    FROM
      score 
    WHERE
      s_score >= 80 
      AND s_score < 90 
      AND c_id = cs.c_id 
      )/(
    SELECT
      COUNT( s_id ) 
    FROM
      score 
    WHERE
      c_id = cs.c_id 
    )) AS '优良率',
  ((
    SELECT
      COUNT( s_id ) 
    FROM
      score 
    WHERE
      s_score >= 90 
      AND c_id = cs.c_id 
      )/(
    SELECT
      COUNT( s_id ) 
    FROM
      score 
    WHERE
      c_id = cs.c_id 
    )) AS '优秀率' 
FROM
  course cs
  LEFT JOIN score sc1 ON sc1.c_id = cs.c_id
  LEFT JOIN score sc2 ON sc2.c_id = cs.c_id
  LEFT JOIN score sc3 ON sc3.c_id = cs.c_id 
GROUP BY
  cs.c_id;

23838babb2cf47489e6e855843eac53d.png

19. 按各科成绩进行排序，并显示排名(实现不完全)

  • mysql没有rank函数
  • 加@score是为了防止用union all 后打乱了顺序
SELECT
  c1.s_id,
  c1.c_id,
  c1.c_name,
  @score := c1.s_score,
  @i := @i + 1 
FROM
  (
  SELECT
    c.c_name,
    sc.* 
  FROM
    course c
    LEFT JOIN score sc ON sc.c_id = c.c_id 
  WHERE
    c.c_id = "01" 
  ORDER BY
    sc.s_score DESC 
  ) c1,
  ( SELECT @i := 0 ) a UNION ALL
SELECT
  c2.s_id,
  c2.c_id,
  c2.c_name,
  c2.s_score,
  @ii := @ii + 1 
FROM
  (
  SELECT
    c.c_name,
    sc.* 
  FROM
    course c
    LEFT JOIN score sc ON sc.c_id = c.c_id 
  WHERE
    c.c_id = "02" 
  ORDER BY
    sc.s_score DESC 
  ) c2,
  ( SELECT @ii := 0 ) aa UNION ALL
SELECT
  c3.s_id,
  c3.c_id,
  c3.c_name,
  c3.s_score,
  @iii := @iii + 1 
FROM
  (
  SELECT
    c.c_name,
    sc.* 
  FROM
    course c
    LEFT JOIN score sc ON sc.c_id = c.c_id 
  WHERE
    c.c_id = "03" 
  ORDER BY
    sc.s_score DESC 
  ) c3;
SET @iii = 0;

1144b16c68764a99a1703ea2dccd7a3f.png

20. 查询学生的总成绩并进行排名

SELECT
  st.s_id,
  st.s_name,
  ( CASE WHEN sum( sc.s_score ) IS NULL THEN 0 ELSE SUM( sc.s_score ) END ) 
FROM
  student st
  LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
  st.s_id 
ORDER BY
  SUM( sc.s_score ) DESC

f7b92b0829ce4d0cab3ab9e6fa3b8d76.png

21. 查询不同老师所教不同课程平均分从高到低显示

SELECT
  t.t_id,
  t.t_name,
  AVG( sc.s_score ) 
FROM
  teacher t
  LEFT JOIN course c ON c.t_id = t.t_id
  LEFT JOIN score sc ON sc.c_id = c.c_id 
GROUP BY
  t.t_id 
ORDER BY
  AVG( sc.s_score ) DESC


6c36531204174ab79005321cb453a53d.png


22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT
  a.* 
FROM
  (
  SELECT
    st.s_id,
    st.s_name,
    c.c_id,
    c.c_name,
    sc.s_score 
  FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON sc.c_id = c.c_id 
    AND c.c_id = '01' 
  ORDER BY
    sc.s_score DESC 
    LIMIT 1,
    2 
  ) a UNION ALL
SELECT
  b.* 
FROM
  (
  SELECT
    st.s_id,
    st.s_name,
    c.c_id,
    c.c_name,
    sc.s_score 
  FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON c.c_id = sc.c_id 
    AND c.c_id = '02' 
  ORDER BY
    sc.s_score DESC 
    LIMIT 1,
    2 
  ) b UNION ALL
SELECT
  c.* 
FROM
  (
  SELECT
    st.s_id,
    st.s_name,
    c.c_id,
    c.c_name,
    sc.s_score 
  FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON c.c_id = sc.c_id 
    AND c.c_id = '03' 
  ORDER BY
    sc.s_score DESC 
    LIMIT 1,
    2 
  ) c;


c8e471e4980842388c0b8a9daa316f41.png


23. 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT
  c.c_id,
  c.c_name,
  (
  SELECT
    COUNT( 1 ) 
  FROM
    score sc 
  WHERE
    sc.c_id = c.c_id 
    AND sc.s_score <= 100 AND sc.s_score > 80 
    )/(
  SELECT
    COUNT( 1 ) 
  FROM
    score sc 
  WHERE
    sc.c_id = c.c_id 
  ) AS '100-85',
  ((
    SELECT
      COUNT( 1 ) 
    FROM
      score sc 
    WHERE
      sc.c_id = c.c_id 
      AND sc.s_score <= 85 AND sc.s_score > 70 
      )/(
    SELECT
      COUNT( 1 ) 
    FROM
      score sc 
    WHERE
      sc.c_id = c.c_id 
    )) AS '85-70',
  ((
    SELECT
      COUNT( 1 ) 
    FROM
      score sc 
    WHERE
      sc.c_id = c.c_id 
      AND sc.s_score <= 70 AND sc.s_score > 60 
      )/(
    SELECT
      COUNT( 1 ) 
    FROM
      score sc 
    WHERE
      sc.c_id = c.c_id 
    )) AS '70-60',
  ((
    SELECT
      COUNT( 1 ) 
    FROM
      score sc 
    WHERE
      sc.c_id = c.c_id 
      AND sc.s_score <= 60 AND sc.s_score >= 0 
      )/(
    SELECT
      COUNT( 1 ) 
    FROM
      score sc 
    WHERE
      sc.c_id = c.c_id 
    )) AS '85-70' 
FROM
  course c 
ORDER BY
  c.c_id


1a3891a301414e599518b497f00fe812.png

24. 查询学生平均成绩及其名次

SET @i = 0;
SELECT
  a.*,
  @i := @i + 1 
FROM
  (
  SELECT
    st.s_id,
    st.s_name,
    round( CASE WHEN AVG( sc.s_score ) IS NULL THEN 0 ELSE AVG( sc.s_score ) END, 2 ) AS agvScore 
  FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
  GROUP BY
    st.s_id 
  ORDER BY
    agvScore DESC 
  ) a


3b4a8d71fad841ff8d3c61a2218124c5.png

25. 查询各科成绩前三名的记录

SELECT
  a.* 
FROM
  (
  SELECT
    st.s_id,
    st.s_name,
    c.c_id,
    c.c_name,
    sc.s_score 
  FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON c.c_id = sc.c_id 
    AND c.c_id = '01' 
  ORDER BY
    sc.s_score DESC 
    LIMIT 0,
    3 
  ) a UNION ALL
SELECT
  b.* 
FROM
  (
  SELECT
    st.s_id,
    st.s_name,
    c.c_id,
    c.c_name,
    sc.s_score 
  FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON c.c_id = sc.c_id 
    AND c.c_id = '02' 
  ORDER BY
    sc.s_score DESC 
    LIMIT 0,
    3 
  ) b UNION ALL
SELECT
  c.* 
FROM
  (
  SELECT
    st.s_id,
    st.s_name,
    c.c_id,
    c.c_name,
    sc.s_score 
  FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON c.c_id = sc.c_id 
    AND c.c_id = '03' 
  ORDER BY
    sc.s_score DESC 
    LIMIT 0,
    3 
  ) c

077fa80313d644949d94c7f056da8213.png

26. 查询每门课程被选修的学生数



SELECT
  c.c_id,
  c.c_name,
  COUNT( 1 ) 
FROM
  course c
  LEFT JOIN score sc ON sc.c_id = c.c_id
  INNER JOIN student st ON st.s_id = c.c_id 
GROUP BY
  c.c_id


72b5920a123c4cadb5f5d80a9466796e.png


文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
关键词：
云数据库 RDS MySQL 版文章
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
sot57do6zp33o
目录
相关文章
平凡@之路
|
存储 SQL 关系型数据库
MySQL阅读网上MySQL文章有感的杂记
MySQL阅读网上MySQL文章有感的杂记
平凡@之路
66 0
JamieChyi
|
关系型数据库 MySQL Linux
让安装变简单：Linux下安装Mysql一篇文章搞定
让安装变简单：Linux下安装Mysql一篇文章搞定
JamieChyi
162 0
T-OPEN
|
7月前
|
SQL 关系型数据库 MySQL
一篇文章解析mysql的 行转列（7种方法） 和 列转行
一篇文章解析mysql的 行转列（7种方法） 和 列转行
T-OPEN
2249 0
程序员小海绵
|
3月前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表，从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移？从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
程序员小海绵
529 15
一篇文章搞懂MySQL的分库分表，从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
程序员小海绵
|
3月前
|
存储 缓存 关系型数据库
【MySQL调优】如何进行MySQL调优？一篇文章就够了！
MySQL调优主要分为三个步骤：监控报警、排查慢SQL、MySQL调优。 排查慢SQL：开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优： 基础优化：缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表； 表设计优化：数据类型优化、冷热数据分表等。 索引优化：考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
程序员小海绵
54 4
【MySQL调优】如何进行MySQL调优？一篇文章就够了！
默不会开发
|
7月前
|
SQL 关系型数据库 MySQL
这篇文章带你了解：如何一次性将Centos中Mysql的数据快速导出！！！
这篇文章带你了解：如何一次性将Centos中Mysql的数据快速导出！！！
默不会开发
70 1
张思睿ZGQ
|
7月前
|
存储 SQL 关系型数据库
【MySQL 数据库】6、一篇文章学习【索引知识】，提高大数据量的查询效率【文末送书】
【MySQL 数据库】6、一篇文章学习【索引知识】，提高大数据量的查询效率【文末送书】
张思睿ZGQ
102 0
才云
|
7月前
|
Cloud Native 关系型数据库 MySQL
AnalyticDB MySQL文章汇总
AnalyticDB MySQL文章汇总
才云
86 0
木子斤欠木同
|
SQL 存储 关系型数据库
MySQL的第一篇文章——了解数据库、简单的SQL语句
MySQL的第一篇文章——了解数据库、简单的SQL语句
木子斤欠木同
158 0
ha_lydms
|
SQL 关系型数据库 MySQL
mybatis-plus代码生成器(+24篇MySql/MyBatis-Plus文章)
mybatis-plus代码生成器(+24篇MySql/MyBatis-Plus文章)
ha_lydms
111 0

热门文章

最新文章

  • 1
    MySQL数据库下载安装教程（Windows&Linux）
  • 2
    MySQL常用函数：IF、SUM等用法
  • 3
    Python处理数据库：MySQL与SQLite详解 | python小知识
  • 4
    从新手到高手：彻底掌握MySQL表死锁
  • 5
    MySQL事务隔离级别及默认隔离级别的设置
  • 6
    Liunx部署java项目Tomcat、Redis、Mysql教程
  • 7
    Mysql索引：深入理解InnoDb聚集索引与MyisAm非聚集索引
  • 8
    MySQL导入.sql文件后数据库乱码问题
  • 9
    MySQL的插件式认证到底是干什么的？
  • 10
    数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
  • 1
    【MYSQL】 ——索引（B树B+树）、设计栈
    9
  • 2
    【MySQL】——数据查询_进阶操作（超详细）！！
    10
  • 3
    MySQL（条件约束）
    9
  • 4
    带你认识MySQL（前置介绍）
    16
  • 5
    【MySQL进阶篇】存储引擎（MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案）
    32
  • 6
    【MySQL基础篇】事务（事务操作、事务四大特性、并发事务问题、事务隔离级别）
    53
  • 7
    MySQL/SqlServer跨服务器增删改查（CRUD）的一种方法
    26
  • 8
    【MySQL基础篇】MySQL约束语法
    34
  • 9
    【MySQL基础篇】盘点MySQL常用四大类函数
    13
  • 10
    【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
    13

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析
  • AnalyticDB MySQL游戏行业数据分析实践
  • 函数计算X RDS PostgreSQL，基于LLM大语言模型构建AI知识库
  • AnalyticDB MySQL海量数据秒级分析体验

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    手把手教你白嫖阿里云服务器(免费领服务器)