mysql准备dept、emp表和数据-马育民老师

# 创建 company 数据库

```
CREATE DATABASE `company` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
```

# 创建表

### 创建 dept 表

```
CREATE TABLE `dept` (
	`deptno` INT(4) NOT NULL,
	`dname` VARCHAR(14) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`loc` VARCHAR(13) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`deptno`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
```

### 创建 emp 表

```
CREATE TABLE `emp` (
	`empno` INT(4) NOT NULL,
	`ename` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`job` VARCHAR(9) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`mgr` INT(4) NULL DEFAULT NULL,
	`hiredate` DATE NULL DEFAULT NULL,
	`sal` DECIMAL(7,2) NULL DEFAULT NULL,
	`comm` DECIMAL(7,2) NULL DEFAULT NULL,
	`deptno` INT(2) NULL DEFAULT NULL,
	PRIMARY KEY (`empno`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
```

# 导入数据

### 导入 dept 表数据

```
INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (3, '市场部', '北京');
INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (40, 'OPERATIONS', 'BOSTON');

```

### 导入 emp 表数据

```
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);

```

原文出处：http://malaoshi.top/show_1IX1OSTp4Hbe.html