mysql常见SQL语句

红太狼 2024-03-30 14:14 115阅读 0赞

## 1、需求 ##

统计某个`子串`在`字符串`中`出现的次数`，`子串`在`字符串`中是否出现过。

## 2、案例 ##

### 2.1 统计某个子串是否在字符串中出现过 ###

mysql> select find_in_set('02','01,02,03,04,05');
    +------------------------------------+
    | find_in_set('02','01,02,03,04,05') |
    +------------------------------------+
    |                                  2 |
    +------------------------------------+
    1 row in set (0.00 sec)

`find_in_set：` 返回字符串列表中字符串的位置，位置从`1`开始。被查找的数据需要以`,`分隔。

### 2.2 统计某个子串在字符串中出现的次数 ###

1.  `length：`统计的是字符串所占的`字节数`
2.  `char_length：`统计的是字符串所占的`字符数`
3.  `子串出现的次数=` (原始字符串字符数 - (原始字符串字符数 - 替换子串后的字符串字符数))/替换子串后的字符串字符数

mysql> select '01,02,03,中文,05' '原始字符串',
        ->        length('01,02,03,中文,05') 'code字段的值所占的字节数',
        ->        char_length('01,02,03,中文,05') 'code字段的值所占的字符数',
        ->        charset('01,02,03,中文,05') 'code字段的字符编码,一个中文utf8mb4占3个字节',
        ->        (char_length('01,02,03,中文,05') - char_length(replace('01,02,03,中文,05','02','')))/char_length('02') '02出现的次数';
    +-----------------------------------+------------------------------------+------------------------------------+------------------------------------------------------------+-------------------+
    | 01,02,03,中文,05                  | code字段的值所占的字节数           | code字段的值所占的字符数           | code字段的字符编码,一个中文utf8mb4占3个字节                | 02出现的次数      |
    +-----------------------------------+------------------------------------+------------------------------------+------------------------------------------------------------+-------------------+
    | 01,02,03,中文,05原始字符串        |                                 18 |                                 14 | utf8mb4                                                    |            1.0000 |
    +-----------------------------------+------------------------------------+------------------------------------+------------------------------------------------------------+-------------------+
    1 row in set (0.00 sec)

![统计某个子串在字符串中出现的次数][093b1ec570dd45c6a985effa1f9065c6.png]

### 2.3、将一个数组字段转换成多行 ###

通过`json_table`来实现。

select
        org_no,
        code,
        substr(code, instr(code,':') + 1, instr(code,';') - instr(code,':') - 1) as '变电站名称',
        substr(code, instr(code,'线路名称:') + 5, instr(code,'台区名称:') - instr(code,'线路名称:') - 6) as '线路名称',
        substr(code, instr(code,'台区名称:') + 5) as '台区名称',
        concat('["', replace(substr(code, instr(code,'台区名称:') + 5), '、','","'), '"]')  as '台区名称str',
        cast(concat('["', replace(substr(code, instr(code,'台区名称:') + 5), '、','","'), '"]')  as json) as '台区名称arr',
        json_extract(cast(concat('["', replace(substr(code, instr(code,'台区名称:') + 5), '、','","'), '"]')  as json), '$[0]') as 'first台区',
        tq.tq_name
    from test_001 t
        cross join json_table(
            cast(concat('["', replace(substr(code, instr(code,'台区名称:') + 5), '、','","'), '"]')  as json),
            '$[*]' columns (tq_name varchar(50) path '$')
        ) tq
    where t.org_no like '42%';

![将一个数组字段转换成多行][41785fa13e834d4793f309403f9b2e1a.png]

### 2.4、交叉连接补全数据 ###

**需求：** 存在如下3个表接口，`course`课程表,`student`学生表和 `score`表。其中，`course`表中存在3门课程，`student`表中存在2个学生，`score`表中，一个学生考了一门课程，另外一个学生考了2门课程。 `希望统计出每个学生在所有的课程下的得分`，如果没有参加者们课程的考试，则得0分。

#### 1 表结构 ####

CREATE TABLE `course` (
      `course_id` int NOT NULL AUTO_INCREMENT COMMENT '课程id',
      `course_name` varchar(20) DEFAULT NULL COMMENT '课程名称',
      PRIMARY KEY (`course_id`)
    ) ENGINE=InnoDB COMMENT='课程表';
    
    CREATE TABLE `score` (
      `score_id` int NOT NULL AUTO_INCREMENT COMMENT '分数id',
      `student_id` int DEFAULT NULL COMMENT '学生id',
      `course_id` int DEFAULT NULL COMMENT '课程id',
      `score` int DEFAULT NULL COMMENT '分数',
      PRIMARY KEY (`score_id`),
      UNIQUE KEY `score_score_id_uindex` (`score_id`)
    ) ENGINE=InnoDB COMMENT='分数表';
    
    CREATE TABLE `student` (
      `student_id` int NOT NULL AUTO_INCREMENT COMMENT '学生id',
      `student_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
      PRIMARY KEY (`student_id`)
    ) ENGINE=InnoDB COMMENT='学生表';

#### 2 插入数据 ####

-- 添加课程
    insert into course values (1,'语文');
    insert into course values (2,'数学');
    insert into course values (3,'化学');
    -- 添加2个学生
    insert into student values (1,'张三');
    insert into student values (2,'李四');
    -- 添加考试分数
    -- 张三只考了语文
    insert into score(student_id, course_id, score) values (1,1,80);
    -- 李四考了 语文和数学
    insert into score(student_id, course_id, score) values (2,1,85);
    insert into score(student_id, course_id, score) values (2,2,90);

#### 3 统计出每个学生在所有的课程下的得分 ####

select a.student_id,a.student_name,a.course_id,a.course_name,b.score_id,b.score from (
        select
            s.student_id,
            s.student_name,
            c.course_id,
            c.course_name
        -- 此处交叉查询了
        from course c cross join student s
        order by s.student_id,c.course_id
    ) a left join score b 
    -- 学生一样 并且分数也一样
    on a.student_id = b.student_id and a.course_id = b.course_id
    order by a.student_id,a.course_id;

**思路：**

1.  先 `交叉查询` 出每个学生需要考试多少学科
2.  然后关联`score`表，注意 `on`后面的 `and` 条件

`另外一种写法`

select a.student_id,a.student_name,a.course_id,a.course_name,sc.score_id,ifnull(sc.score,0) score from (select c2.course_id, c2.course_name, s2.student_id, s2.student_name
       from (select -1 as link,
                    c1.course_id,
                    course_name
             from course c1) c2
                left join
            (select -1 as link, s1.student_id, student_name
             from student s1) s2 on c2.link = s2.link
       ) a left join score sc on a.student_id = sc.student_id and a.course_id = sc.course_id
    order by a.student_id,a.course_id

#### 4 运行结果 ####

![运行结果][60735d4ba25946d18b94fe37a40d8b41.png]

[093b1ec570dd45c6a985effa1f9065c6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/5423381a5874408f9bd9b302249c55ee.png
[41785fa13e834d4793f309403f9b2e1a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/a0d5058c022a4698818438fba0e25a7f.png
[60735d4ba25946d18b94fe37a40d8b41.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/40cff7a83a034821a833bf31b6c844d2.png