MySql常用操作总结（实时更新）

MySql常用操作总结（实时更新）

开始顺序可能会有点乱，我会慢慢分类显示，如果有不准确的欢迎指出！

• 删除指定字符

 从左侧依次开始匹配字符并删除，匹配失败则停止匹配
select trim(leading 'a' from 'a123456a');
执行结果： 123456a
leading 表示只从左边开始匹配

从右侧依次开始匹配字符并删除，匹配失败则停止匹配
select trim(trailing 'a' from 'a123456a');
执行结果： a123456

trailing 表示只从右边开始匹配

分别从两侧依次开始匹配字符并删除，匹配失败则依次停止匹配
select trim(both 'a' from 'a123456aa');
执行结果： 123456

both 表示两边同时匹配

• 日期格式化 参考mysql日期格式化
  DATE_FORMA T(date, format) 根据格式串format 格式化日期或日期和时间值date，返回结果串。

select date_format(日期字段,’%Y-%m-%d’) as ‘日期’ from test

• mysql str_to_date 字符串转换为日期

1.mysql日期和字符相互转换方法 
date_format(date,’%Y-%m-%d’) ————–>oracle中的to_char(); 
str_to_date(date,’%Y-%m-%d’) ————–>oracle中的to_date();

%Y：代表4位的年份 
%y：代表2为的年份

%m：代表月, 格式为(01……12) 
%c：代表月, 格式为(1……12)

%d：代表月份中的天数,格式为(00……31) 
%e：代表月份中的天数, 格式为(0……31)

%H：代表小时,格式为(00……23) 
%k：代表 小时,格式为(0……23) 
%h： 代表小时,格式为(01……12) 
%I： 代表小时,格式为(01……12) 
%l ：代表小时,格式为(1……12)

%i： 代表分钟, 格式为(00……59) 【只有这一个代表分钟，大写的I 不代表分钟代表小时】

%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M) 
%T：代表 时间,格式为24 小时(hh:mm:ss)

%S：代表 秒,格式为(00……59) 
%s：代表 秒,格式为(00……59)

2.例子： 
select str_to_date(‘09/01/2009’,’%m/%d/%Y’)

select str_to_date(‘20140422154706’,’%Y%m%d%H%i%s’)

select str_to_date(‘2014-04-22 15:47:06’,’%Y-%m-%d %H:%i:%s’)

• mysql 日期加减操作

1. MySQL 为日期增加一个时间间隔：date_add()

set @dt = now();

select date_add(@dt, interval 1 day);        -- add 1 day
select date_add(@dt, interval 1 hour);       -- add 1 hour
select date_add(@dt, interval 1 minute);     -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day);       -- sub 1 day

MySQL adddate(), addtime()函数，可以用 date_add() 来替代。下面是 date_add() 实现 addtime() 功能示例：

2. MySQL 为日期减去一个时间间隔：date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+
| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |
+----------------------------------------------------------------+
| 1997-12-30 22:58:59                                            |
+----------------------------------------------------------------+

MySQL date_sub() 日期时间函数 和 date_add() 用法一致，不再赘述。另外，MySQL 中还有两个函数 subdate(), subtime()，建议，用 date_sub() 来替代。

 

4. MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。

select datediff('2008-08-08', '2008-08-01');  -- 7
select datediff('2008-08-01', '2008-08-08');  -- -7

MySQL timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00');                       -- 08:08:08

注意：timediff(time1,time2) 函数的两个参数类型必须相同。

• MySQL添加字段和修改字段

1添加表字段

alter table table1 add transactor varchar(10) not Null;

alter table   table1 add id int unsigned not Null auto_increment primary key

2.修改某个表的字段类型及指定为空或非空
alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

3.修改某个表的字段名称及指定为空或非空
alter table 表名称 change 字段原名称 字段新名称 字段类型 [是否允许非空

4如果要删除某一字段，可用命令：ALTER TABLE mytable DROP 字段名;

• MySQL用命令行复制表的方法

1.只复制表结构到新表
 CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2;
或

CREATE TABLE 新表 LIKE 旧表 ;
2.复制表结构及数据到新表
 CREATE TABLE 新表 SELECT * FROM 旧表
 
3.复制旧表的数据到新表(假设两个表结构一样) 
 INSERT INTO 新表 SELECT * FROM 旧表
 
4.复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表

• mysql数据库查询包含大写字母的字段

SELECT * FROM `v9_category_attribute` WHERE title_cn REGEXP BINARY  '[A-Z]';

BINARY  强制区分大小写

REGEXP 正则表达式

• 用SQL命令查看Mysql数据库大小

要想知道每个数据库的大小的话，步骤如下：

1、进入information_schema 数据库（存放了其他的数据库的信息）

use information_schema;

 

2、查询所有数据的大小：

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
 

3、查看指定数据库的大小：

比如查看数据库home的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';
 

4、查看指定数据库的某个表的大小

比如查看数据库home中 members 表的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';

• MySQL查看当前运行的事务和执行的账户

-- 查看当前运行的事务，这点在变更表结构之前必须要查看
select * from information_schema.innodb_trx;

-- 查看当前运行的事务的账户和事务开始的时间,及其事务语句
select 
    a.id,a.user,a.host,b.trx_started,b.trx_query 
from information_schema.processlist a right outer join information_schema.innodb_trx b
on a.id = b.trx_mysql_thread_id;

• mysql终止当前正在执行的sql语句

show processlist;
找出你要停止的语句
然后找出你要终止的语句的id
在系统下mysqladmin -uroot -ppassword kill 要杀的ID

• Mysql update多表联合更新
• mysql group_concat合并多条记录某一字段