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 )