MySQL 常用语句

MySQL 常用语句

创建数据库

CREATE DATABASE `epoch` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

创建表

CREATE TABLE user
(
  id       INT AUTO_INCREMENT PRIMARY KEY,
  userCode VARCHAR(100) NOT  NULL COMMENT '用户登录账号',
  pwd      VARCHAR(100) NOT  NULL COMMENT '用户密码',
  nickName VARCHAR(100) NOT  NULL COMMENT '用户昵称',
  userName VARCHAR(50)  NULL COMMENT '用户真实姓名',
  age      INT(3)       NULL COMMENT '用户年龄',
  sex      INT(1)       NULL COMMENT '用户性别 0表示女 1表示男',
  birth    DATETIME     NULL COMMENT '用户生日',
  phoneNum INT          NULL COMMENT '用户电话号码',
  email    VARCHAR(100) NULL COMMENT '电子邮箱',
  school   VARCHAR(100) NULL COMMENT '毕业院校',
  job      VARCHAR(100) NULL COMMENT '工作职位',
  com      VARCHAR(100) NULL COMMENT '公司',
  pic      VARCHAR(100) NULL COMMENT '头像',
  country  VARCHAR(50)  NULL COMMENT '国家',
  province VARCHAR(50)  NULL COMMENT '省',
  city     VARCHAR(50)  NULL COMMENT '市',
  model    TEXT         NULL COMMENT '格言',
  CONSTRAINT user_id_uindex  UNIQUE (id),
  CONSTRAINT user_userCode_uindex UNIQUE (userCode)
)
  DEFAULT CHARSET = utf8
  COMMENT '系统登录人的用户信息'
  ENGINE = InnoDB;

修改密码

              update user set password=password('123456') where user='root'

              update user set host='%'' where user='root' and host='localhost'

创建用户

              CREATE USER 'abc'@'%' IDENTIFIED BY '123456';   创建用户

              GRANT ALL ON . TO 'abc'@'%';  授权

              flush privileges;   使命令生效

MariaDB For Mac 操作

sudo /usr/local/mariadb/server/bin/mariadb

CREATE USER 'zhangsan' @ '%'IDENTIFIED BY '123456';

GRANT ALL ON . TO 'zhangsan’ @ '%'; 

修改root用户的密码：SET password for 'root'@'localhost'=password('123456');

默认情况下MariaDB是自启动的
启动DB：sudo launchctl start com.mariadb.server
关闭DB：sudo launchctl stop com.mariadb.server

转载于:https://my.oschina.net/epoch/blog/1633702