Mysql建库,建表,创建用户,授权

建库

CREATE DATABASE `wm` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
USE `wm`;

建表

DROP TABLE IF EXISTS `admin_user_app`;
CREATE TABLE `admin_user_app` (
  `id` int(16) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_id` int(16) unsigned NOT NULL COMMENT 'user_id',
  `app_id` int(8) unsigned NOT NULL COMMENT '轻应用id',
  `o_id` int(16) unsigned NOT NULL COMMENT '操作人id',
  `c_id` int(16) unsigned NOT NULL COMMENT '创建人id',
  `status` tinyint(1) NOT NULL COMMENT '状态：0：已删除，1：正常',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `id_index` (`user_id`,`app_id`) USING BTREE COMMENT 'user_id和app_id索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='管理员可管理轻应用表';

创建用户，授权

grant select,insert,update,delete,create,drop,alter on wm.* to work@'%' identified by '1234' WITH GRANT OPTION;
# wm.* --- wm库下所有表
# work ---mysql用户名
# % ---允许外网访问
# 1234 --- 密码
FLUSH PRIVILEGES;

表增加字段，设置默认值

ALTER TABLE portal_user_info ADD unit_id int(11)  DEFAULT -100 AFTER tele_no;
# 在tele_no后面增加unit_id 【需要加数据类型】，设置默认值-100