关于MySQL的使用总结梳理

行转列

1. 表数据格式
   
2. 创建用于循环的自增表

create table incre_table (AutoIncreID int);
insert into incre_table values (1);
insert into incre_table values (2);
insert into incre_table values (3);

或mysql内部也有现成的连续数列表可用。如mysql.help_topic： help_topic_id 共有504个数值，一般能满足于大部分需求了
或者用数据库提供的mysql

3. sql语句

select a.sid,substring_index(substring_index(a.cid,',',b.AutoIncreID),',',-1) as cid
from 
	student a
	join
	incre_table b
	on b.AutoIncreID <= (length(a.cid) - length(replace(a.cid,',',''))+1)

4. 结果
   
5. 将转换后的数据插入sc(成绩表）中

INSERT into sc(sid,cid)
select a.sid,substring_index(substring_index(a.cid,',',b.AutoIncreID),',',-1) as cid
from 
	student a
	join
	incre_table b
	on b.AutoIncreID <= (length(a.cid) - length(replace(a.cid,',',''))+1)

触发器

student表数据插入时,将插入的数据信息同步到sc表中

create trigger trig1 AFTER insert on student 
for each row 
BEGIN
	declare sid char(255);
	set sid = new.sid;
	insert into sc(sid,cid) 
	select a.sid,substring_index(substring_index(a.sname,',',b.AutoIncreID),',',-1) as cid
	from 
		student a
		join
		incre_table b
		on b.AutoIncreID <= (length(a.sname) - length(replace(a.sname,',',''))+1)
	where a.sid = sid;
END

注：监听的表和要操作的表不可为同一张表，否则报1442错误，解决方案，创建垃圾表。
例如：

create trigger wl_collect_course_insert AFTER insert on wl_collect_course1
for each row
begin 
 DECLARE courseids CHAR(255);
 DECLARE collectorids CHAR(255);
 DECLARE rid CHAR(255);
 DECLARE classids CHAR(255);
 set courseids = new.courseid;
 set collectorids = new.collectorid;
 set rid = new.row_id;
 set classids = new.classid;
 delete 
 from wl_collect_course 
 where collectorid = collectorids and courseid = courseids and row_id != rid;
 INSERT INTO `one_stop_bizdb`.`wl_collect_course` (`classid`, `collectorid`, `courseid`, `row_id`, `biz_status`) VALUES (classids, collectorids,courseids, rid, 1);
end

数据类型转换(CAST、CONVERT)

MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值

语法：
   cast(value as type)
   convert(value,type)
列如：
 将两字段转换为同一类型进行比较
 CAST(material.course_id AS char) = CAST( course.row_id  AS char)
 convert( material.course_id,char) = convert(course.row_id,char)

数据小数点处理

round（字段段名，小树点位数）

以 23.56231 为例
round(data,2) -- 23.56
round(data,3) -- 23.562

时间格式处理

• 格式转换 date_format（字段名，格式）

-- 以下以 2021-06-03 01:50:00为例
date_format(time,'%Y-%m-%d') -- 2021-06-03
date_format(time,'%y-%m-%d') -- 21-06-03
date_format(time,'%Y-%M-%D') -- 2021-June-3rd
date_format(time,'%Y%m%d') -- 20210603
date_format(time,'%Y%u') -- 202122  运用：用于时间以周进行分组(%U %u 结果一样)
date_format(time,'%Y-%m-%d %T') -- 2021-06-03 01:50:00
date_format(time,'%Y-%m-%d %H') -- 2021-06-03 01

以上为我平时用到的关于时间格式的处理，想知道更多可以查看一下网址
w3school: https://www.w3school.com.cn/sql/func_date_format.asp.

• 计算时间差 DATEDIFF（time1，time2）

	select DATEDIFF('2021-7-10','2021-7-1') -- 9

数据去重

• distinct

例如：查询出所有学生的名字，并去除重名

select distinct name from student

使用between…and…的注意事项

例如：

• 存在张数据表，数据如下：
  
• 现需要查询出制单时间(maketime )从 2021-12-15 到 2021-12-17 截止时间段中的数据。
• 理想中的sql语句

select * from jy_end_balance where maketime BETWEEN '2021-12-15' and '2021-12-17'

结果：

从结果中看出并没有将2021-12-17存入的数据查询出来，这是为什么呢？

• 正确的sql

select * from jy_end_balance where maketime BETWEEN '2021-12-15' and '2021-12-17 23:59:29'

结果：

在使用between…and…的时候，未写时分秒时默认是" 00：00：00 "，所以第一个sql查询的时间是 [2021-12-15，2021-12-17)，并未查询到2021-12-17这一天。

[注]：有不对的地方或有更好的方法，欢迎指教，谢谢！