Mysql常用命令参考

人们所获得一切知识来源于社会生产实践、科学实验实践、改造社会的实践。

1、创建表：

USE ccsoftexchangedb;  # 指定数据库
CREATE TABLE `t_vn_bill_sms_realtime` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',  # 注意列名不是单引号
    `id_code` VARCHAR(36) NOT NULL DEFAULT '' COMMENT '随机生成id',
    `sms_id` VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'sms唯一id',
    `sms_result` VARCHAR(30)  NULL DEFAULT NULL COMMENT 'sms_result',
    `sender` VARCHAR(32) NOT NULL  DEFAULT '' COMMENT '发送者加密号码',
    `receiver` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '接受者加密号码',
    `sender_show` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'sender_show',
    `receiver_show` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'receiver_show',
    `transfer_time` DATETIME NULL DEFAULT NULL COMMENT '发送时间',
    `bind_id` VARCHAR(64) NULL DEFAULT NULL COMMENT '企业可携带自定义信息',
    `DataChange_LastTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近更新时间',
    PRIMARY KEY(`id`),  # 设置主键
    INDEX `ix_id_code` (`id_code`),  # 设置索引
    INDEX `ix_DataChange_LastTime` (`DataChange_LastTime`)
)
COMMENT = '小号服务短信计费明细'  #表名
ENGINE = InnoDB
COLLATE = 'utf8_general_ci' 
;

2、insert插入一条记录：

insert into table_name values ('value1', 'value2', 'value3',...)  # 所有字段插入

insert into table_name (column1,column2,column3,...) values ('value1', 'value2', 'value3',...)   # 指定字段插入

3、循环插入语句：

delimiter $$  # 定义结束符为##
drop procedure if exists wk;
create procedure wk()
begin
declare i int;
set i = 1;
while i < 11 do
insert into midnum_pool(tel_x, business_id, duration, bind_time) values ('a', 'b', 'c', 'd');
set i = i+1;
end while;
end $$        #结束定义语句

delimiter ;
call wk();

4、添加字段：

use ccsoftexchangedb;   # 指定数据库
alter table `t_vn_bill_sms_realtime12` add column `sms_type` varchar(30) not null default '' after `sms_result`;  # 新加字段在“sms_result”之后

5、修改字段类型：

use ccsoftexchangedb;
alter table `t_vn_bill_xtotalfee` modify column `bill_time` varchar(10) null  # 把bill_time字段类型改为长度为10的String

6、使用union联合查询：

select tel_x, business_id, call_duration from A
union all
select tel_x, business_id, 0 as call_duration from B

*查询结果为A, B表中tel_x，business_id和call_duration字段的汇总；

*如果使用union而不是union all，则A和B中若某条记录重复则只显示一次；

*union要求两个select语句的查询字段完全一致，而B表中没有call_duration字段，所以使用了“0 as call_duration”。

7、把表A的数据复制到表B：

insert into A select * from B

8、某一列的数据分情况进行“count”：

select bu_id, sum(case when call_result = '1' then 1 else 0 end) as successCount,
              sum(case when call_result <> '1' then 1 else 0 end) as failCount 
from t_vn_record group by bu_id

9、excel文件导入mysql数据库的方法

10、取当前时间前一天的数据：

select * from gatewaycdr_opensbc where disconnect_time between date_sub(curdate(),interval 1 day) and date_sub(curdate(),interval 0 day)

解释：
date_sub(‘2017-08-01’,interval 1 day) 表示 2017-07-31
date_sub(‘2017-08-01’,interval 0 day) 表示 2017-08-31

11、内联、左联、右联、全联查询

12、对某个字段值相同的不同记录进行去重：

select * from t where id in (select max(id) from t group by number)

其中id是主键，对“number”字段的值相同的记录进行去重

13、两个表查询结果的差集
方法一：

select id from usertable where id not in (select id from blog)

缺点：大数据量时效率极低

方法二：

select id FROM usertable LEFT JOIN
(select id as i from blog) as t1
ON usertable.id=t1.i where t1.i IS NULL

14、去除重复，并取日期最新的一条数据
方法一

select t1.* from ipt_reg_log t1 LEFT JOIN ipt_reg_log t2
ON (t1.reg_name = t2.reg_name and t1.log_time <t2.log_time)
where t2.reg_name is NULL;

图1中reg_name有重复数据，需要取出log_time最新的每一条reg_name，结果如图2：


方法二（速度更快，方法一可能有准确性问题）：

select t1.*from ipt_reg_log t1 inner join
(select reg_name, max(log_time) 'maxtime' from ipt_reg_log group by reg_name) t2
on t1.reg_name=t2.reg_name and t1.log_time=t2.maxtime;