尚硅谷之MySQL基础

CREATE TABLE t_stu(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(100) NOT NULL,

gender CHAR NOT NULL DEFAULT ‘男’,

card_id CHAR(18) NOT NULL UNIQUE,

birthday DATE,

address VARCHAR(200)

);

INSERT INTO t_stu VALUES(1,’张三’,DEFAULT,’123456789012345678′,’1989-09-09′,NULL);

INSERT INTO t_stu VALUES(2,’李四’,’女’,’123456789012345677′,’1988-09-09′,’尚硅谷’);

INSERT INTO t_stu VALUES(0,’王五’,’男’,’123456789012345676′,’1987-09-09′,’尚硅谷’);

INSERT INTO t_stu VALUES(NULL,’赵六’,’男’,’123456789012345675′,’1987-09-09′,’尚硅谷’);

INSERT INTO t_stu VALUES

(NULL,’冰冰’,’女’,’123456789012345674′,’1988-09-09′,’尚硅谷’),

(NULL,’小丽’,’女’,’123456789012345673′,’1988-09-09′,’尚硅谷’);

INSERT INTO t_stu (sname,card_id,birthday)

VALUES(‘小薇’,’123456199012045672′,STR_TO_DATE(SUBSTRING(card_id,7,8),’%Y%m%d’));

INSERT INTO t_stu (sname,card_id,birthday)VALUES

(‘小红’,’123456789012345671′,’1990-09-09′),

(‘小紫’,’123456789012345670′,’1990-09-09′);

CREATE TABLE t_department(                                         

did INT PRIMARY KEY AUTO_INCREMENT,

dname VARCHAR(100) NOT NULL,

description VARCHAR(200),

manager_id INT

);

INSERT INTO t_department(dname,description)

VALUES(‘教学部’,’技术培训’),

(‘咨询部’,’课程咨询服务’);

CREATE TABLE `t_job` (

  `job_id` INT(11) PRIMARY KEY AUTO_INCREMENT,

  `job_name` VARCHAR(100) DEFAULT NULL,

  `description` VARCHAR(200) DEFAULT NULL

);

INSERT INTO t_job VALUES

(NULL,’JavaSE讲师’,’Java基础’),

(NULL,’Web讲师’,’Web基础’),

(NULL,’JavaEE框架’,’框架讲解’),

(NULL,’课程顾问’,’课程咨询’);

CREATE TABLE t_employee(

eid INT PRIMARY KEY AUTO_INCREMENT,

ename VARCHAR(100) NOT NULL,

gender CHAR NOT NULL DEFAULT ‘男’,

card_id CHAR(18) UNIQUE,

tel CHAR(11),

job_id INT,

`mid` INT,

birthday DATE,

hiredate DATE,

address VARCHAR(100),

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES t_department(did),

FOREIGN KEY (job_id) REFERENCES t_job(job_id)

);

INSERT  INTO `t_employee`(`eid`,`ename`,`gender`,`card_id`,`tel`,`job_id`,`mid`,`birthday`,`hiredate`,`address`,`dept_id`)

 VALUES (1,’孙红雷’,’男’,’123456789012345678′,’12345678901′,1,NULL,’1990-01-01′,’2015-01-01′,’白庙’,1),

 (2,’张亮’,’男’,’123456789012345677′,’12345678902′,2,NULL,’1990-01-02′,’2015-01-02′,’天通苑北’,1),

 (3,’鹿晗’,’男’,’123456789012345676′,’12345678903′,3,NULL,’1990-01-03′,’2015-01-03′,’北苑’,1),

 (4,’邓超’,’男’,’123456789012345675′,’12345678904′,2,NULL,’1990-01-04′,’2015-01-04′,’和谐家园’,1),

 (5,’孙俪’,’女’,’123456789012345674′,’12345678905′,3,NULL,’1990-01-05′,’2015-01-05′,’霍营’,1),

 (6,’Angelababy’,’女’,’123456789012345673′,’12345678906′,4,NULL,’1990-01-06′,’2015-01-06′,’回龙观’,2);

CREATE TABLE t_salary(

eid INT PRIMARY KEY,

basic_salary DECIMAL(10,2),

performance_salary DECIMAL(10,2),

commission_pct DECIMAL(10,2),

deduct_wages DECIMAL(10,2),

FOREIGN KEY (eid) REFERENCES t_employee(eid)

);

INSERT  INTO `t_salary`(`eid`,`basic_salary`,`performance_salary`,`commission_pct`,`deduct_wages`)

VALUES (1,’12000.00′,’6000.00′,’0.40′,’0.00′),

(2,’9000.00′,’5000.00′,’0.20′,NULL),

(3,’11000.00′,’8000.00′,NULL,NULL),

(4,’13000.00′,’5000.00′,NULL,NULL),

(5,’8000.00′,’8000.00′,’0.30′,NULL),

(6,’15000.00′,’6000.00′,NULL,NULL);