db.sql

来自「spring企业级开发电子书籍.......」· SQL 代码 · 共 28 行

DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb DEFAULT CHARACTER SET utf8;
USE sampledb;
CREATE TABLE T_USER (
   USER_ID INTEGER NOT NULL AUTO_INCREMENT,
   USERNAME VARCHAR(30) NOT NULL,
   PASSWORD VARCHAR(30) DEFAULT NULL,
   STATUS TINYINT(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`USER_ID`),
   UNIQUE KEY `USERNAME` (`USERNAME`)
);
CREATE TABLE T_USER_PRIV (
  USER_ID INTEGER NOT NULL DEFAULT '0',
  PRIV_NAME VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (USER_ID, PRIV_NAME)
);

INSERT INTO T_USER (USER_ID, USERNAME, PASSWORD, STATUS) VALUES 
  (1,'tom','tom',1),
  (2,'john','john',1);

INSERT INTO T_USER_PRIV (USER_ID, PRIV_NAME) VALUES 
  (1,'PRIV_1'),
  (1,'PRIV_2'),
  (1,'PRIV_COMMON'),
  (2,'PRIV_1'),
  (2,'PRIV_COMMON');
 COMMIT;