用来创建表、插入数据
SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS dept_;-- 部门表 DROP TABLE IF EXISTS emp_;-- 部门表 SET FOREIGN_KEY_CHECKS = 1; SELECT @@FOREIGN_KEY_CHECKS; CREATE TABLE dept_ ( DEPTNO INT PRIMARY KEY, -- 部门编号 DNAME VARCHAR ( 14 ), -- 部门名称 LOC VARCHAR ( 13 ) -- 部门地址 ); INSERT INTO dept_ VALUES ( 10, 'ACCOUNTING', 'NEW YORK' ); INSERT INTO dept_ VALUES ( 20, 'RESEARCH', 'DALLAS' ); INSERT INTO dept_ VALUES ( 30, 'SALES', 'CHICAGO' ); INSERT INTO dept_ VALUES ( 40, 'OPERATIONS', 'BOSTON' ); CREATE TABLE emp_ ( emp_NO INT PRIMARY KEY,-- 员工编号 ENAME VARCHAR ( 10 ),-- 员工名称 JOB VARCHAR ( 9 ),-- 工作 MGR DOUBLE,-- 直属领导编号 HIREDATE DATE,-- 入职时间 SAL DOUBLE,-- 工资 COMM DOUBLE,-- 奖金 DEPTNO INT,-- 部门号 FOREIGN KEY ( DEPTNO ) REFERENCES dept_ ( DEPTNO ) ); INSERT INTO emp_ VALUES ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20 ); INSERT INTO emp_ VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30 ); INSERT INTO emp_ VALUES ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30 ); INSERT INTO emp_ VALUES ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20 ); INSERT INTO emp_ VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30 ); INSERT INTO emp_ VALUES ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30 ); INSERT INTO emp_ VALUES ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10 ); INSERT INTO emp_ VALUES ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000, NULL, 20 ); INSERT INTO emp_ VALUES ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10 ); INSERT INTO emp_ VALUES ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30 ); INSERT INTO emp_ VALUES ( 7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100, NULL, 20 ); INSERT INTO emp_ VALUES ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30 ); INSERT INTO emp_ VALUES ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20 ); INSERT INTO emp_ VALUES ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10 );