mysql数据库练习sql语句用到的emp与dept表结构和数据

DROP TABLE IF EXISTS dept;

CREATE TABLE dept (

DEPTNO int(2) NOT NULL,

DNAME varchar(14) DEFAULT NULL,

LOC varchar(13) DEFAULT NULL,

PRIMARY KEY (DEPTNO)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

-- Records of dept

---

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');

---

-- Table structure for emp

---

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (

EMPNO int(4) NOT NULL,

ENAME varchar(10) DEFAULT NULL,

JOB varchar(9) DEFAULT NULL,

MGR int(4) DEFAULT NULL,

HIREDATE date DEFAULT NULL,

SAL int(7) DEFAULT NULL,

COMM int(7) DEFAULT NULL,

DEPTNO int(2) DEFAULT NULL,

PRIMARY KEY (EMPNO),

KEY FK_DEPTNO (DEPTNO),

CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES dept (DEPTNO)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

-- Records of emp

---

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-04-19', '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-05-23', '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');