9.17

1 . create database xkw charset=utf8;

2.CREATE TABLE employee(

empid INT UNSIGNED PRIMARY KEY,

NAME VARCHAR(100),

gender TINYINT CHECK(gender=0 OR gender=1 OR gender=2),

title VARCHAR(100),

birthday Date,

depid VARCHAR(100)

);

CREATE TABLE department(

depid INT UNSIGNED PRIMARY KEY,

depname VARCHAR(100)

);

CREATE TABLE salary(

empid INT UNSIGNED PRIMARY KEY,

base_salary VARCHAR(100),

title_salary VARCHAR(100),

deduction VARCHAR(100)

);

3.ALTER TABLE department ADD 部门简介 VARCHAR(100);

4.INSERT INTO employee VALUES(1001,'张三','男','高级程师',"1975-01-01",111);

INSERT INTO employee VALUES(1002,'李四','女','助工',"1985-01-01",111);

INSERT INTO employee VALUES(1003,'王五','男','工程师',"1978-11-11",222);

INSERT INTO employee VALUES(1004,'赵六','男','工程师',"1979-01-01",222);

INSERT INTO department VALUES(111,'生产部',NULL);

INSERT INTO department VALUES(222,'销售部',NULL);

INSERT INTO department VALUES(333,'人事部',NULL);

INSERT INTO salary VALUES(1001,2200,1100,200);

INSERT INTO salary VALUES(1002,1200,200,100);

INSERT INTO salary VALUES(1003,1900,700,200);

INSERT INTO salary VALUES(1004,1950,700,150);

5.UPDATE employee,salary SET title='工程师',base_salary=5700,title_salary=600 WHERE employee.empid=1002 and salary.empid=1002;