MySql：基本操作语法

1、Create user from Mysql

//创建lmengi用户
CREATE USER 'lmengi'@'localhost' IDENTIFIED BY 'lmengi';
//insert into mysql.user(Host,User,Password) values("localhost","lmengi",password("lmengi"));
//赋值所有权限给当前lmengi用户
GRANT ALL PRIVILEGES ON *.* TO 'lmengi'@'localhost' IDENTIFIED BY 'lmengi' WITH GRANT OPTION;
//刷新系统权限表
flush privileges;

2、Create database from MySql

//创建数据库
drop database if exists icom;
create database icom DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
//先赋值本地权限
grant select,insert,update,delete,create,drop on icom.* to iusr@localhost 
Identified by "iusr123"; 
//后赋值所有权限
grant all privileges on icom.* to iusr@"%" Identified by "iusr123" WITH GRANT OPTION; 
use icom;
FLUSH  PRIVILEGES; 
注: icom数据库，iusr 用户名，iusr123密码

3、Create table from Mysql

drop table if exists log ;
CREATE TABLE log (
logId int NOT NULL AUTO_INCREMENT ,
productId int NULL COMMENT '商品ID' ,
UserId int NULL COMMENT '审核管理员ID' ,
createTime datetime NULL COMMENT '审核时间' ,
message varchar(255) NULL COMMENT '审核的理由' ,
statusId int NULL COMMENT '审核状态： 1，通过； 2，不通过' ,
PRIMARY KEY (logId)
) ENGINE=MyISAM DEFAULT CHARACTER SET=utf8 COMMENT='审核日志表';

注意：MyISAM和InnoDB的区别

4、Add column and drop column from table tlog

alter table tlog drop column log_size;
alter table tlog add log_size varchar(32) comment '日志大小';

5、Insert into table from Mysql

insert into log (message) VALUES ("rensheng");//注意空格

--单表插入多条数据
INSERT INTO table(APPROVE_NAME,CORP_ID,IS_SHOWTIME) 
VALUES("请假单",2,1),("出差申请单",2,1),("用车申请单",2,0),("采购申请单",2,0),
("报销申请单",2,0),("外出申请单",2,1),("调休申请单",2,1),("加班申请单",2,1),
("补休申请单",2,1),("开会申请单",2,1);

6、Update column from table

use lmengidb;
ALTER TABLE `tlog`
CHANGE COLUMN `isshow` `isShow`  int(11) NULL DEFAULT 0 COMMENT '0,不显示；1，显示' AFTER `log_size `;

ALTER TABLE `tlog` 
ADD `prior` BIGINT(20) NOT NULL DEFAULT '0' COMMENT '权重' AFTER `isshow`;

7、Mysql 查询今天、昨天、7天、近30天、本月、上一月 数据

今天：select * from 表名 where to_days(时间字段名) = to_days(now());
昨天：SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
7天：SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
本周：SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) 
= YEARWEEK(now());
近30天：SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月：SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上一月：SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , 
date_format( 时间字段名, '%Y%m' ) ) =1

8、重复语句操作

//查询重复记录
select * from tlog
where userId in (select userId from tloggroup by userId having count(userId) > 1)
//删除重复记录
delete from `tlog` where id not in ( select * from (select MAX(id) as id from `tlog` 
group by user_id) b where `tlog`.id=b.id )