1. 两表的数据和结构
员工表： 部门表：
2. 新建数据库test
DROP DATABASE IF EXISTS test ; -- 建数据库之前先删除数据库CREATE DATABASE test; -- 创建数据库test
USE test; -- 选择数据库
3. 创建部门表dept并插入数据
DROP TABLE IF EXISTS dept;
CREATE TABLE DEPT(
deptno smallint(5) not null primary key comment '部门编号',
dname varchar(20) comment '部门名称',
loc varchar(20) comment '部门所在城市'
)ENGINE=INNODB,CHARSET=UTF8;
-- 插入数据
INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES(20,'PESEARCT','DALLAS');
INSERT INTO dept VALUES(30,'SALES','CHICAGO');
INSERT INTO dept VALUES(40,'OPERATIONS','BOSTON');
4. 创建部门表emp并插入数据
DROP TABLE IF EXISTS emp; -- 删除emp
CREATE TABLE emp(
empno samllint(5) not null null primary key comment '员工编号',
ename varchar(20) comment '员工姓名',
job varchar(20) comment '工作岗位',
mgr int comment '上级领导编号',
hiredate date comment '入职时间',
sal int comment '薪资',
comm int comment '提成',
deptno int comment '部门编号',
FOREIGN KEY (deptno) REFERENCES dept (deptno)
)ENGINE=INNODB,CHARSET=UTF8;
-- 插入数据
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-03',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,'1981-01-23',1300,NULL,10);
标签：comment,INSERT,20,数据库,练习,VALUES,emp,MySQL,INTO From： https://www.cnblogs.com/chf333/p/16812898.html