mysql %u_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微秒

%pAM 或 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会一直向右匹配直到遇到范围查询(>、

比如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内容。