1.创建时间为当前更新时间
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
对于timestamp类型，使用UNIX_TIMESTAMP内置函数查询效率很高，几乎和int相当；直接和日期比较效率低。
对于datetime类型，使用UNIX_TIMESTAMP内置函数查询效率很低，不建议；直接和日期比较，效率还行。
对应java代码为Date 或者 timeStamp （继承date）
2.增加时间类型字段
ALTER TABLE test ADD COLUMN update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间';
3.建表语句
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(24) NOT NULL COMMENT '姓名',
`create_dt` datetime DEFAULT NULL COMMENT '订单创建时间',
`update_dt` datetime DEFAULT NULL COMMENT '订单更新时间',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='软产品订单记录表';
4.插入语句
INSERT INTO `database`.`test` (`id`, `name`, `create_dt`, `update_dt`)
VALUES ('3', 'zhangsan', '2020-10-27 15:32:51', '2020-10-27 15:32:53');
5.强制走索引
explain SELECT
p.name AS name,
p.id_number AS idNumber,
p.sap_number AS sapNumber,
p.order_qty AS orderQty,
p.create_dt AS createDt,
p.update_dt AS updateDt,
p.create_time AS createTime
FROM
order p FORCE INDEX (idx_time)
WHERE
1 = 1
AND p.create_time >= '2020-10-28 15:10:11'
ORDER BY
p.create_time DESC;
6.以 逗号 ， 分隔
select p.name,SUBSTRING_INDEX(p.name,',',1) AS longitude,SUBSTRING_INDEX(p.name,',',-1) AS latitude,p.data_status as dataStatus from test p;
substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
//1：从左数，取第1个逗号的左边，-1：从右边数，取第1个逗号的右边
例子：str=www.wikibt.com
substring_index(str,'.',1)
结果是：www
substring_index(str,'.',2)
结果是：www.wikibt
也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容
相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：
substring_index(str,'.',-2)
结果为：wikibt.com
有人会问，如果我要中间的的wikibt怎么办？
很简单的，两个方向：
从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：
substring_index(substring_index(str,'.',-2),'.',1);
7.截取字符串后几位
SELECT SUBSTRING('www.yuanrengu.com', -6)；
截取后8位： p.num = RIGHT(t.numStr, 8)
8.增加索引
alter table `test_dict` add index symbol_idx(`symbol_code`);
增加固定长度的索引：
alter table test_car add index idx_chassis(`chassis_num`(8));
alter table test_car add index idx_vin(`vin`(17));
9.增加字段、添加字段、 删除字段、删除索引
ALTER TABLE test_info ADD COLUMN vin_num varchar(16) DEFAULT NULL COMMENT '车架号';
alter table `test_info` add index vin_idx(`vin_num`);
-- 回滚
DROP INDEX vin_idx ON test_info;
ALTER TABLE test_info DROP COLUMN vin_num;
10.更新整列数据
UPDATE test_info SET vin_num = RIGHT(vin, 8);
11.修改为分区表
-- 删除主键，在删除主键的时候，这个自增会让该语句执行失败，先取消字段自增，然后执行该语句后，再加上自增
alter table test_index drop primary key;
-- 添加主键，（分区要求：分区中使用的字段必须都包含在主键当中）
alter table test_index add primary key(id,addtime);
-- 创建分区(分区要求：分区中使用的字段必须都包含在主键当中)
ALTER TABLE test_index PARTITION by RANGE(to_days(addtime))
(
PARTITION p1 VALUES LESS THAN (to_days('2021-03-01')),
PARTITION p2 VALUES LESS THAN (to_days('2021-06-01')),
PARTITION p3 VALUES LESS THAN (to_days('2021-09-01')),
PARTITION p4 VALUES LESS THAN (to_days('2021-12-01')),
PARTITION p5 VALUES LESS THAN (MAXVALUE)
-- 剩下的放在一个分区中，当需要对这部分进行再次分区的时候，需要先删除该分区，然后再添加多个分区
);
-- 删除分区
ALTER TABLE test_index DROP PARTITION p5;
-- 添加新的分区
alter table test_index add PARTITION
(
PARTITION p6 VALUES LESS THAN (to_days('2022-03-01')) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (MAXVALUE)
);
12.创建表字段，所有字段总长度长度超过65535
需要把长字段改成text类型
13.更新字段属性、类型等
-- 正常，能修改字段类型、类型长度、默认值、注释
alter table table1 modify column column1 decimal(10,1) DEFAULT NULL COMMENT '注释';
14.字符串 类型的数字 排序
order by 字段名称+0 desc/asc的形式进行排序(亲测有效)
15.case when 控制 语句实现
-- demo1
SELECT
t.NAME,
(
CASE t.sex
WHEN 1 THEN
'男'
WHEN 2 THEN
'女'
ELSE
'未知'
END
) 性别
FROM
t_customer t
-- demo2
SELECT
t.NAME,
(
CASE
WHEN t.sex=1 THEN
'男'
WHEN t.sex=2 THEN
'女'
ELSE
'未知'
END
) 性别
FROM
t_customer t
16.建库语句
create schema df_test default character set utf8 collate utf8_general_ci;
17.清空 表
truncate table table_name;
18.修改字段 编码 格式
ALTER TABLE test_car modify column `car_id` varchar(32) character set utf8 not null;
19.创建用户
create user 'test'@'%' identified by 'test123';
grant all privileges on *.* to 'test'@'%' identified by 'test123' with grant option;
20.备份表
create table test_20210630 select * from test;
21.mysql 存储 表情
-- 修改mysql 存储的格式, 指定单个字段的编码方式
ALTER TABLE test MODIFY COLUMN params text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '入参';
22.时间格式化
格式化为字符串： DATE_FORMAT(testTime,'%Y-%m-%d %H:%i:%s')
SELECT
NOW() 当前时间,
current_timestamp() ,
unix_timestamp(now()) 当前秒,
REPLACE(unix_timestamp(current_timestamp(3)),'.','') 当前毫秒;
23.导出存储过程
导出存储过程：
mysqldump -hhostname -uroot -p -ntd -R databasename > process_test.sql
或者
show create procedure proc_cars;
或者
select body from mysql.proc where specific_name = 'proc_cars';
mysql -udbuser -pdbuser123 --default-character-set=gb2312 -e "select body from mysql.proc where specific_name = 'proc_cars'" databasename > test.txt