mysql常见SQL语句-蒲公英云

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 统计某个子串在字符串中出现的次数

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

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)

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

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

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

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;

思路：

先 交叉查询 出每个学生需要考试多少学科
然后关联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