【雇員表】

CREATE TABLE dept( /*部門表*/

deptno MEDIUMINT??UNSIGNED?NOT NULL?DEFAULT 0,?

dname VARCHAR(20)?NOT NULL?DEFAULT "",

loc VARCHAR(13) NOT NULL DEFAULT ""

);

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');

#創(chuàng)建表EMP雇員

CREATE TABLE emp

(empno?MEDIUMINT UNSIGNED?NOT NULL?DEFAULT 0, /*編號*/

ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/

job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/

mgr MEDIUMINT UNSIGNED ,/*上級編號*/

hiredate DATE NOT NULL,/*入職時間*/

sal DECIMAL(7,2)?NOT NULL,/*薪水*/

comm DECIMAL(7,2) ,/*紅利*/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號*/

);

?

?INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20),?

(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),??

(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),??

(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),??

(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),??

(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),??

(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),??

(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),??

(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),??

(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),??

(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),??

(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),??

(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);

#工資級別表

CREATE TABLE salgrade

(

grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

losal DECIMAL(17,2)?NOT NULL,

hisal DECIMAL(17,2)?NOT NULL

);

INSERT INTO salgrade VALUES (1,700,1200);

INSERT INTO salgrade VALUES (2,1201,1400);

INSERT INTO salgrade VALUES (3,1401,2000);

INSERT INTO salgrade VALUES (4,2001,3000);

INSERT INTO salgrade VALUES (5,3001,9999);