删除表
DROP TABLE IF EXISTS `sys_user_role`
创建表
CREATE TABLE `sys_user_role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
`role_id` bigint(20) DEFAULT NULL COMMENT '角色ID',
`create_time` bigint(14) DEFAULT NULL,
`update_time` bigint(14) DEFAULT NULL,
`yn` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_sys_u_r_uid` (`user_id`) USING BTREE,
KEY `idx_sys_u_r_rid` (`role_id`) USING BTREE
)
添加字段
alter table coin add COLUMN fx_price VARCHAR(50) DEFAULT ''
删除字段
alter table coin drop column age,drop column address;
修改字段
alter table coin alter column fx_price set default '';
更新数据
UPDATE coin set chinese_name="" where chinese_name is null;
删除触发器
DROP TRIGGER IF EXISTS `update`;
创建触发器
mysql> delimiter //
mysql> create trigger update_trigger
-> BEFORE UPDATE
-> on cms_news for each row begin
-> set NEW.abs = substring(NEW.content,1,100);
-> end//
查看建表语句
show create table tablename
按天统计数据量
SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as day , count(*) as count FROM group_info where create_time>='2018-10-26' and create_time<'2018-11-06' GROUP BY day