Java知识体系

Appearance

常见查询SQL 工作记录

查询200个数据的 in 查询拼接 ​

SELECT 
  CONCAT(
    '\'',
    GROUP_CONCAT(sp.skc ORDER BY sp.id SEPARATOR '\',\''),
    '\''
  ) AS skc_list
FROM (
  SELECT skc, id 
  FROM soms_products 
  WHERE id > 1000000000000
  ORDER BY id
  LIMIT 200
) AS sp;

新增某一列 ​

其中 AFTER 的作用是来指定新添加的列在表结构中的位置的。

当你使用 ALTER TABLE 添加新列时，可以通过 AFTER 子句来指定新列应该放置在哪一列之后

ALTER TABLE `soms_loss_leader_activity_products`
ADD COLUMN `spu` varchar(100) NOT NULL DEFAULT '' COMMENT 'spu' AFTER `sku`;

常见 SQL ​

示例数据 ​

注意一下在 utf8mb4 的数据库中操作

创建示例表

CREATE TABLE Student (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    gender VARCHAR(10)
);

CREATE TABLE Score (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    subject VARCHAR(50) NOT NULL,
    score DECIMAL(5, 2),
    exam_date DATE,
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
);

示例数据

INSERT INTO Student (name, age, gender) VALUES
('张三', 20, '男'),
('李四', 19, '男'),
('王五', 21, '女'),
('赵六', 22, '女'),
('孙七', 18, '男');

INSERT INTO Score (student_id, subject, score, exam_date) VALUES
(1, '数学', 85.5, '2023-10-01'),
(1, '英语', 78.0, '2023-10-02'),
(1, '物理', 92.0, '2023-10-03'),

(2, '数学', 88.0, '2023-10-01'),
(2, '英语', 74.5, '2023-10-02'),
(2, '化学', 91.0, '2023-10-04'),

(3, '英语', 82.0, '2023-10-02'),
(3, '生物', 87.0, '2023-10-05'),
(3, '化学', 79.0, '2023-10-04'),

(4, '物理', 95.0, '2023-10-03'),
(4, '化学', 88.0, '2023-10-04'),
(4, '生物', 90.0, '2023-10-05'),

(5, '数学', 93.0, '2023-10-01'),
(5, '英语', 85.0, '2023-10-02'),
(5, '物理', 88.0, '2023-10-03'),
(5, '化学', 84.0, '2023-10-04');

查询不同科目下的学生成绩 ​

参考一下这个： https://www.cnblogs.com/kongxiaoshuang/p/17643868.html

大概的方式有几种：

  • 1、使用 case when 的方式（固定列，然后进行展示）
  • 2、使用 sum if 的方式
  • 3、使用动态SQL的方式

使用 case when 的方式

SELECT
    s.student_id,
    s.name,
    MAX(CASE WHEN sc.subject = '英语' THEN sc.score ELSE NULL END) AS 英语成绩,
    MAX(CASE WHEN sc.subject = '数学' THEN sc.score ELSE NULL END) AS 数学成绩
FROM
    Student s
LEFT JOIN
    Score sc ON s.student_id = sc.student_id
GROUP BY
    s.student_id, s.name;

使用 sum if 的方式

SELECT 
    s.student_id,
    s.name,
		SUM(if (sc.subject = '英语',sc.score,0)) as '英语成绩',
		SUM(if (sc.subject = '数学',sc.score,0)) as '数学成绩'
FROM
    Student s
LEFT JOIN
    Score sc ON s.student_id = sc.student_id
GROUP BY
    s.student_id, s.name;

动态展示并查询出来 ​

动态 SQL 的方式

SET @sql = NULL;

-- 构造SELECT语句
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN sc.subject = ''',
      subject,
      ''' THEN sc.score ELSE NULL END) AS `',
      subject, '`'
    )
  ) INTO @sql
FROM Score;

-- 完整的SQL查询语句
SET @sql = CONCAT('SELECT s.student_id, s.name, ', @sql, ' 
                   FROM Student s 
                   LEFT JOIN Score sc 
                   ON s.student_id = sc.student_id 
                   GROUP BY s.student_id, s.name');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

行转列 ​

参考： https://bbs.huaweicloud.com/blogs/400950

PIVOT函数是MySQL8.0版本中新增的函数，用于实现行转列操作。

放弃行转列，建议直接使用 CASE WHEN 的方式

（后续看一下 MySQL 相关的视频内容）

参考