mysql 常用
1、索引
-- 查看某表的所有索引字段
SHOW INDEX FROM table_name;
-- 创建某表的某索引字段
CREATE INDEX sku_id ON table_name (sku_id);
-- 删除某表的某索引字段
DROP INDEX sku_id ON table_name;
2、修改表结构
-- 新增字段
ALTER TABLE `hshr_mall_cashcoupon_activity`
ADD COLUMN `send_number` int(11) DEFAULT '0' COMMENT '已发放数量' AFTER `number` ;
-- 改变字段名
ALTER TABLE `hshr_mall_cashcoupon_activity`
CHANGE COLUMN `receive_number` `send_number` int(11) NULL DEFAULT 0 COMMENT '已发放数量' AFTER `number`;
-- 修改字段信息
ALTER TABLE `hshr_mall_cashcoupon`
MODIFY COLUMN `price` int(11) NULL DEFAULT 0 COMMENT '代金券面值金额' AFTER `id`;
-- 删除字段
ALTER TABLE `hserp_finance_payment`
DROP COLUMN `cyberbank_operate_status`;
-- 修改表名
RENAME TABLE user11 TO user10;
或
ALTER TABLE 表名 RENAME [TO|AS] 新表名 ==> ALTER TABLE user10 RENAME TO user11;
或修改表备注
alter table student comment '学生信息';
3、自增序列表
CREATE TABLE `sequence` (
`name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '序列的名字',
`current_value` int(11) NOT NULL COMMENT '序列的当前值',
`increment` int(11) NOT NULL DEFAULT '1' COMMENT '序列的自增值',
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- FUNCTION `currval`(`seq_name` VARCHAR(50))
BEGIN
DECLARE value VARCHAR(30);
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN LPAD(value, 11 ,0);
END
-- FUNCTION `nextval`(`seq_name` VARCHAR(50))
BEGIN
UPDATE sequence
SET current_value = current_value + increment
WHERE name = seq_name;
IF ROW_COUNT() = 0 THEN
INSERT INTO sequence (name,current_value,increment) VALUES (seq_name, 1, 1);
END IF ;
RETURN currval(seq_name);
END
4、查询表字段备注名
SELECT
'名称','类型','备注'
UNION ALL
SELECT
column_name,column_type,column_comment
FROM
information_schema.columns t
WHERE
table_schema ='qmct3.0'
AND
table_name = 'qmct_member_integral_grade';
5、mysql函数
-- ROUND(number,要保留的小数位)，四舍五入
SELECT ROUND(100/6,2);
-- TRUNCATE(xnumber,要保留的小数位)，按小数位截取保留
SELECT TRUNCATE(100/6,2);
-- ceil(X)函数，返回大于X的最小整数值(向上取整)
SELECT CEIL(100.31*10)/10; -- 向上保留1位小数
-- floor(X)函数，返回小于X的最大整数值(向下取整)
SELECT FLOOR(100.3811*10)/10; -- 向下保留1位小数
-- TIMESTAMPDIFF(INTERVAL,DATETIME_EXPR1,DATETIME_EXPR2)函数，时间差函数
SELECT TIMESTAMPDIFF(DAY,'2019-03-04 14:00:00',NOW()) >= 7; -- 时间小的放在前面，时间大的放在后面，返回整数差
/*INTEGER取值可是：
FRAC_SECOND 毫秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 星期
MONTH 月
QUARTER 季度
YEAR 年
*/
-- TIMESTAMPADD(INTERVAL,INT_EXPR,DATETIME_EXPR)函数，时间扩大函数
SELECT TIMESTAMPADD(HOUR,1,NOW()); -- 前面是数字，后面是时间或日期时间表达式，返回日期
-- 日期函数
-- NOW()，当前语句的执行时间，格式如：2019-03-04 14:40:02
-- CURDATE()，当前语句的执行时间的年月日，格式如：2019-03-04
-- CURTIME()，当前语句的执行时间的时分秒，格式如：14:40:02
-- UNIX_TIMESTAMP，当前语句的执行时间的秒数差时间戳，如：1551681602
-- SYSDATE()，当前系统实时时间，格式如：2019-03-04 14:40:02
SELECT NOW(),SYSDATE(),CURDATE(),CURTIME(),UNIX_TIMESTAMP();
SELECT NOW(),SLEEP(2),NOW(); -- 2019-03-04 14:37:43,0,2019-03-04 14:37:43
SELECT SYSDATE(),SLEEP(2),SYSDATE(); -- 2019-03-04 14:37:43,0,2019-03-04 14:37:45
-- 时间戳函数
-- 获取当前时间戳:SELECT UNIX_TIMESTAMP(); SELECT UNIX_TIMESTAMP(NOW());
-- 时间转时间戳：SELECT UNIX_TIMESTAMP('2019-03-04 14:47:38');
-- 时间戳转时间：SELECT FROM_UNIXTIME(1551682058);
-- 时间戳格式化：SELECT FROM_UNIXTIME(1551682058, '%Y-%m-%d %H:%i:%S');
-- 时间格式化：SELECT DATE_FORMAT('2019-03-04 14:47:38', '%Y-%m-%d');
/*
%Y 年，4 位
%y 年，2 位
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%m 月，数值 (01-12)
%c 月，数值 (1-12)
%M 月，英文名 (January February March April May June July August September October November December)
%b 月，缩写英文名 (Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec)
%a 缩写星期名 (Mon Tues Wed Thu Fri Sat Sun)
%W 星期名 (Monday Tuesday Wednesday Thursday Friday Saturday Sunday)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%w 天，周的天 （0=星期日, 6=星期六）
%D 天，月的天，带有英文后缀(1th-31th)
%d 天，月的天，数值(01-31)
%e 天，月的天，数值(1-31)
%j 天，年的天 (001-366)
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%k 小时 (0-23)
%l 小时 (1-12)
%i 分钟，数值(00-59)
%S 秒(00-59)
%s 秒(00-59)
%f 微秒
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%T 时间, 24-小时 (hh:mm:ss)
*/
-- 日期修改
-- CONCAT(str1,str2)，字符串拼接
-- DATE(DATETIME_EXPR)，返回日期或日期表达式的年月日部分
-- DATE_ADD(date,INTERVAL expr type)，向日期添加指定的时间间隔
SELECT CONCAT(date(DATE_ADD(SYSDATE(),INTERVAL 1 DAY)),' 23:59:59');
-- STR_TO_DATE()，字符串转日期
SELECT STR_TO_DATE('2019-03-04','%Y-%m-%d');
-- 字段为空判断
-- IF(BOOLEAA_EXPR,VALUE1,VALUE2)，条件为真时返回value1，否则返回value2
-- IFNULL(VALUE1,VALUE2)，当value1为空时返回value2，否则返回value1
-- ISNULL(VALUE)，判断VALUE是否为空，是返回1，否则返回0
SELECT IF(0 = 1,'right','wrong'); -- wrong
SELECT IFNULL(NULL,0); -- 0
SELECT ISNULL(NULL); -- 1
-- FIND_IN_SET(str,strlist)，返回str在strlist中的位置
SELECT FIND_IN_SET('b','a,b,c,d'); -- 2
6、随机取多条数据
- 速度快，数据量少时返回条数不固定
SELECT
*
FROM
table_user
WHERE
id >= (
(SELECT MAX(id) FROM table_user)
- (SELECT MIN(id) FROM table_user)
) * RAND()
+ (SELECT MIN(id) FROM table_user)
LIMIT 1;
-- 速度慢，数据量少时可以使用
SELECT
*
FROM
table_user
ORDER BY
RAND()
LIMIT 1;
7、排序(针对于逗号拼接的字段)
-- FIELD(field1,field2,...)，使用函数按固定值排序
ORDER BY FIELD(id,22131,14117,14116,21911,22601,23487,22016,21158);
-- 使用GROUP_CONCAT排序
SELECT
GROUP_CONCAT(id ORDER BY rownum),
GROUP_CONCAT(sku_title ORDER BY rownum)
FROM
(SELECT
id,
sku_title,
(@rowNum:=@rowNum + 1) as rownum
FROM hshr_mall_goods_sku
WHERE
id in(22131,14117,14116,21911,22601,23487,22016,21158)
ORDER BY FIELD(id,22131,14117,14116,21911,22601,23487,22016,21158)) a;
-- 或
SELECT
GROUP_CONCAT(sku_title ORDER BY FIELD(id,22131,14117,14116,21911,22601,23487,22016,21158))
FROM
hshr_mall_goods_sku
WHERE
id in(22131,14117,14116,21911,22601,23487,22016,21158)
ORDER BY FIELD(id,22131,14117,14116,21911,22601,23487,22016,21158);
8、是否存在 插入或更新
INSERT ... ON DUPLICATE KEY UPDATE last_update_date = sysdate() 语句，一条语句就搞定了查询是否存在和插入或者更新这几个步骤
9、拆分逗号字段
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num
FROM
mysql.help_topic
WHERE
help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1;
-------------------------------
select
distinct substring_index(substring_index(a.value,',',b.help_topic_id+1),',',-1)
from
table a
join
mysql.help_topic b
on
b.help_topic_id < (length(a.value) - length(replace(a.value,',',''))+1);
---------------------
substring_index（被截取字段，关键字，关键字出现的次数）（注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束）
distinct：去重复操作
a.value：表别名.需要拆分的字段名
table a：表名 别名
help_topic：自增长表
length：返回字符串str的长度，以字节为单位。一个多字节字符算作多字节。这意味着，对于包含五个两字节字符的字符串，LENGTH() 返回10
replace(object, search,replace) 把object中出现search的全部替换为replace。
-- eg. select replace('www.163.com','w','Ww')--->WwW wWw.163.com
10、调优
多条新增尽量使用批量操作；
多条数据更新使用如下格式：
update table set name = (case id when 1 then '张三' when 2 ... end) where id in (1,2,3...);
使用Replace代替insert+delete:
REPLACE INTO score (id,change_type,score,user_id) VALUES (1,'吃饭',10,1);
使用Replace()来做更新:
UPDATE score SET change_type=REPLACE(change_type,'做任务','bb');
根据缓存命中率和缓存写入率来判断是否需要开启数据库缓存；
只需要返回一条数据时使用limit1;
查询尽量不要select *;
查询时使用between and 或 exists 来代替 in；
尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
设计表时多使用数值型字段；
合理使用索引(btree、hash-直接定址法、平方取中法、折叠法、除数取余法、随机数法)；
1-must）定义有外键的数据列一定要建立索引。
2-should）较频繁作为查询条件的字段才去创建索引
3-should）尽量的扩展索引，不要新建索引。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
4-should） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，
如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
5-not can）更新频繁字段不适合创建索引
6-not can）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
7-no）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8-no）对于定义为text、image和bit的数据类型的列不要建立索引。
删除百万级数据可以先删除索引、再删除数据、再重建索引；
char是固定长度，所以它的处理速度比varchar快得多，但缺点是浪费存储空间，不能在行尾保存空格。
在MySQL中，MyISAM建议使用固定长度代替可变长度列；
InnoDB建议使用varchar类型，因为在InnoDB中，内部行存储格式没有区分固定长度和可变长度；
text与blob区别：blob保存二进制数据；text保存字符数据，有字符集。text和blob不能有默认值。
实际场景：text与blob主要区别是text用来保存字符数据（如文章，日记等），blob用来保存二进制数据（如照片等）。
blob与text在执行了大量删除操作时候，有性能问题（产生大量的“空洞“），为提高性能建议定期optimize table 对这类表进行碎片整理。
尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半，且日期类型中只有它能够和实际时区相对应。
对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。
优先使用：DATE>TIMESTAMP(4个字节)>DATETIME(8个字节)
垂直拆分:
1）把不常用的字段单独放在一张表;
2）把text，blob等大字段拆分出来放在附表中;
3）经常组合查询的列放在一张表中;
缺点也很明显，需要使用冗余字段，而且需要join操作。
水平分割:
以该表主键的某个值为界线，将该表的记录水平分割为两个表;
增量法(不会改变的数据)：
1.创建一张日充值表,记录每天充值总额
2.每天用定时器对当前充值记录进行结算
3.创建每月充值表,每月最后一天用定时器计算总额
4.则要查询总额,则从月报表中汇总，再从日报表查询当天之前的数据汇总，再加上今天的使用当天流水表记录今天的流水，三张表加起来,汇总。
这样子效率是极好的！
读写分离；
通过在服务层引入队列和缓存，让最底层的数据库高枕无忧。
mycat的负载均衡实现mysql集群（如LVS+keepalived组合、haproxy+keepalived组合）
11.存储过程与存储函数
存储过程
create procedure 存储过程名字()
(
[in|out|inout] 参数 datatype
)
begin
MySQL 语句;
end;
MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。
用call关键字调用存储过程：call pr_add(10, null);
优点：
能实现较快的执行速度（存储过程是预编译的）；
允许标准组件是编程；
可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算；
可被作为一种安全机制来充分利用；
能减少网络流量；
存储过程与存储函数区别：
1.存储过程没有返回值，存储函数有且只有一个返回值；
2.存储过程可以有多个in,out,inout参数，存储函数只能有输入参数，而且不能带in；
3.存储过程可以调用存储函数。但函数不能调用存储过程；
4.存储函数中不能在使用insert,update,delete,create等语句；
5.存储函数只完成查询的工作；
存储方法：
create function 函数([函数参数[,….]])
Returns 返回类型
Begin
If
Return (返回的数据)
Else
Return (返回的数据)
end if;
end;
存储方法与存储过程的区别：
1，存储方法的参数列表只允许IN类型的参数，而且没必要也不允许指定IN关键字
2，存储方法返回一个单一的值，值的类型在存储方法的头部定义
3，存储方法可以在SQL语句内部调用
4，存储方法不能返回结果集
12.触发器
触发器在INSERT、UPDATE或DELETE等DML语句修改数据库表时触发
触发器的典型应用场景是重要的业务逻辑、提高性能、监控表的修改等
触发器可以在DML语句执行前或后触发
13、视图
视图是基于 SQL 语句的结果集的可视化的表；
create or replace view v_test as select * from user;
14、主从复制
是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；
主数据库一般是准实时的业务数据库
原理（重中之重，面试必问）：
1.数据库有个bin-log二进制文件，记录了所有sql语句。
2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。
3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。
4.具体需要三个线程来操作：
binlog输出线程：主库发送binlog内容到从库；
从库I/O线程：从库读取主库发过来的内容保存到本地；
从库的SQL线程：从库执行binlog内容。
