SQL 向：在 mysql 中创建《SQL 经典实例》中的两个主要在用的表 emp 和 dept 供大家使用

本文介绍如何使用SQL创建员工(emp)和部门(dept)表，并插入初始数据。此外，还展示了如何创建数据透视表T1、T10、T100和T500，以及使用存储过程填充数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建 emp 表

CREATE TABLE emp
(
    EMPNO int NOT NULL,
    ENAME VARCHAR (20) NOT NULL,
    JOB VARCHAR (20) NOT NULL,
    MGR int NULL,
    HIREDATE VARCHAR (20) NOT NULL,
    SAL int NOT NULL,
    COMM int NULL,
    DEPTNO int NOT NULL
    -- specify more columns here
);

INSERT INTO emp
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES
( 
 7369, "SMITH", "CLERK", 7902, "17-DEC-1980", 800, NULL, 20
),
( 
 7499, "ALLEN", "SALESMAN", 7698, "20-FEB-1981", 1600, 300, 30
),
( 
 7521, "WARD", "SALESMAN", 7698, "22-FEB-1981", 1250, 500, 30
),
( 
 7566, "JONES", "MANAGER", 7839, "02-APR-1981", 2975, NULL, 20
),
( 
 7654, "MARTIN", "SALESMAN", 7698, "28-SEP-1981", 1250, 1400, 30
),
( 
 7698, "BLAKE", "MANAGER", 7839, "01-MAY-1981", 2850, NULL, 30
),
( 
 7782, "CLARK", "MANAGER", 7839, "09-JUN-1981", 2450, NULL, 10
),
( 
 7788, "SCOTT", "ANALYST", 7566, "09-DEC-1982", 3000, NULL, 20
),
( 
 7839, "KING", "PRESIDENT", null, "17-NOV-1981", 5000, NULL, 10
),
( 
 7844, "TURNER", "SALESMAN", 7698, "08-SEP-1981", 1500, 0, 30
),
( 
 7876, "ADAMS", "CLERK", 7788, "12-JAN-1983", 1100, NULL, 20
),
( 
 7900, "JAMES", "CLERK", 7698, "03-DEC-1981", 950, NULL, 30
),
( 
 7902, "FORD", "ANALYST", 7566, "03-DEC-1981", 3000, NULL, 20
),
( 
 7934, "MILLER", "CLERK", 7782, "23-JAN-1982", 1300, NULL, 10
)

创建 dept 表

CREATE TABLE dept
(
    DEPTNO int NOT NULL,
    DNAME VARCHAR (20) NOT NULL,
    LOC VARCHAR (20) NOT NULL
    -- specify more columns here
);

INSERT INTO dept
( -- columns to insert data into
 DEPTNO, DNAME, LOC
)
VALUES   
(10, "ACCOUNTING", "NEW YORK"),
(20, "RESEARCH", "DALLAS"),
(30, "SALES", "CHICAGO"),
(40, "OPERATIONS", "BOSTON")

创建T1、T10、T100 和 T500数据透视表。

CREATE TABLE t1
(
    ID int(11) NOT NULL PRIMARY KEY auto_increment 
    -- specify more columns here
);

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 10; -- 创建t10
  WHILE v1 > 0 DO
    INSERT t1 VALUES (NULL);
    SET v1 = v1 - 1;
  END WHILE;
END;;

CALL dowhile();
SELECT * FROM t1;

Note：其中，删除 PROCEDURE 的方法如下所示，