首页 专题 文章 代码 归档
Mysql 数据库脚本导入
2020.02.27 20:29 2020.02.27 20:29

1. 系列课程SQL脚本

您可导入一下脚本,创建相关数据库与表名,以后阅读文章时即可自己同步操作:

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE = ''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 */;
/*!40101 SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES = @@SQL_NOTES, SQL_NOTES = 0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS */employees /*!40100 DEFAULT CHARACTER SET utf8 */;

USE employees;

/*Table structure for table `departments` */

DROP TABLE IF EXISTS `departments`;

CREATE TABLE `departments`
(
    `department_id`   int(4) NOT NULL AUTO_INCREMENT,
    `department_name` varchar(3) DEFAULT NULL,
    `manager_id`      int(6)     DEFAULT NULL,
    `location_id`     int(4)     DEFAULT NULL,
    PRIMARY KEY (`department_id`),
    KEY `loc_id_fk` (`location_id`),
    CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 271
  DEFAULT CHARSET = utf8;

/*Data for the table `departments` */

insert into `departments`(`department_id`, `department_name`, `manager_id`, `location_id`)
values (10, 'Adm', 200, 1700),
       (20, 'Mar', 201, 1800),
       (30, 'Pur', 114, 1700),
       (40, 'Hum', 203, 2400),
       (50, 'Shi', 121, 1500),
       (60, 'IT', 103, 1400),
       (70, 'Pub', 204, 2700),
       (80, 'Sal', 145, 2500),
       (90, 'Exe', 100, 1700),
       (100, 'Fin', 108, 1700),
       (110, 'Acc', 205, 1700),
       (120, 'Tre', NULL, 1700),
       (130, 'Cor', NULL, 1700),
       (140, 'Con', NULL, 1700),
       (150, 'Sha', NULL, 1700),
       (160, 'Ben', NULL, 1700),
       (170, 'Man', NULL, 1700),
       (180, 'Con', NULL, 1700),
       (190, 'Con', NULL, 1700),
       (200, 'Ope', NULL, 1700),
       (210, 'IT ', NULL, 1700),
       (220, 'NOC', NULL, 1700),
       (230, 'IT ', NULL, 1700),
       (240, 'Gov', NULL, 1700),
       (250, 'Ret', NULL, 1700),
       (260, 'Rec', NULL, 1700),
       (270, 'Pay', NULL, 1700);

/*Table structure for table `employees` */

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees`
(
    `employee_id`    int(6) NOT NULL AUTO_INCREMENT,
    `first_name`     varchar(20)   DEFAULT NULL,
    `last_name`      varchar(25)   DEFAULT NULL,
    `email`          varchar(25)   DEFAULT NULL,
    `phone_number`   varchar(20)   DEFAULT NULL,
    `job_id`         varchar(10)   DEFAULT NULL,
    `salary`         double(10, 2) DEFAULT NULL,
    `commission_pct` double(4, 2)  DEFAULT NULL,
    `manager_id`     int(6)        DEFAULT NULL,
    `department_id`  int(4)        DEFAULT NULL,
    `hiredate`       datetime      DEFAULT NULL,
    PRIMARY KEY (`employee_id`),
    KEY `dept_id_fk` (`department_id`),
    KEY `job_id_fk` (`job_id`),
    CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
    CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 207
  DEFAULT CHARSET = utf8;

/*Data for the table `employees` */

insert into `employees`(`employee_id`, `first_name`, `last_name`, `email`, `phone_number`, `job_id`, `salary`,
                        `commission_pct`, `manager_id`, `department_id`, `hiredate`)
values (100, 'Steven', 'K_ing', 'SKING', '515.123.4567', 'AD_PRES', 24000.00, NULL, NULL, 90, '1992-04-03 00:00:00'),
       (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', 'AD_VP', 17000.00, NULL, 100, 90, '1992-04-03 00:00:00'),
       (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', 'AD_VP', 17000.00, NULL, 100, 90, '1992-04-03 00:00:00'),
       (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', 'IT_PROG', 9000.00, NULL, 102, 60,
        '1992-04-03 00:00:00'),
       (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', 'IT_PROG', 6000.00, NULL, 103, 60, '1992-04-03 00:00:00'),
       (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', 'IT_PROG', 4800.00, NULL, 103, 60, '1998-03-03 00:00:00'),
       (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', 'IT_PROG', 4800.00, NULL, 103, 60,
        '1998-03-03 00:00:00'),
       (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', 'IT_PROG', 4200.00, NULL, 103, 60, '1998-03-03 00:00:00'),
       (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', 'FI_MGR', 12000.00, NULL, 101, 100,
        '1998-03-03 00:00:00'),
       (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', 'FI_ACCOUNT', 9000.00, NULL, 108, 100,
        '1998-03-03 00:00:00'),
       (110, 'John', 'Chen', 'JCHEN', '515.124.4269', 'FI_ACCOUNT', 8200.00, NULL, 108, 100, '2000-09-09 00:00:00'),
       (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', 'FI_ACCOUNT', 7700.00, NULL, 108, 100,
        '2000-09-09 00:00:00'),
       (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', 'FI_ACCOUNT', 7800.00, NULL, 108, 100,
        '2000-09-09 00:00:00'),
       (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', 'FI_ACCOUNT', 6900.00, NULL, 108, 100, '2000-09-09 00:00:00'),
       (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', 'PU_MAN', 11000.00, NULL, 100, 30, '2000-09-09 00:00:00'),
       (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', 'PU_CLERK', 3100.00, NULL, 114, 30, '2000-09-09 00:00:00'),
       (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', 'PU_CLERK', 2900.00, NULL, 114, 30, '2000-09-09 00:00:00'),
       (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', 'PU_CLERK', 2800.00, NULL, 114, 30, '2000-09-09 00:00:00'),
       (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', 'PU_CLERK', 2600.00, NULL, 114, 30, '2000-09-09 00:00:00'),
       (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', 'PU_CLERK', 2500.00, NULL, 114, 30,
        '2000-09-09 00:00:00'),
       (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', 'ST_MAN', 8000.00, NULL, 100, 50, '2004-02-06 00:00:00'),
       (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', 'ST_MAN', 8200.00, NULL, 100, 50, '2004-02-06 00:00:00'),
       (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', 'ST_MAN', 7900.00, NULL, 100, 50, '2004-02-06 00:00:00'),
       (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', 'ST_MAN', 6500.00, NULL, 100, 50, '2004-02-06 00:00:00'),
       (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', 'ST_MAN', 5800.00, NULL, 100, 50, '2004-02-06 00:00:00'),
       (125, 'Julia', 'Nayer', 'JNAYER', '650.124.1214', 'ST_CLERK', 3200.00, NULL, 120, 50, '2004-02-06 00:00:00'),
       (126, 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', 'ST_CLERK', 2700.00, NULL, 120, 50,
        '2004-02-06 00:00:00'),
       (127, 'James', 'Landry', 'JLANDRY', '650.124.1334', 'ST_CLERK', 2400.00, NULL, 120, 50, '2004-02-06 00:00:00'),
       (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', 'ST_CLERK', 2200.00, NULL, 120, 50, '2004-02-06 00:00:00'),
       (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', 'ST_CLERK', 3300.00, NULL, 121, 50, '2004-02-06 00:00:00'),
       (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', 'ST_CLERK', 2800.00, NULL, 121, 50,
        '2004-02-06 00:00:00'),
       (131, 'James', 'Marlow', 'JAMRLOW', '650.124.7234', 'ST_CLERK', 2500.00, NULL, 121, 50, '2004-02-06 00:00:00'),
       (132, 'TJ', 'Olson', 'TJOLSON', '650.124.8234', 'ST_CLERK', 2100.00, NULL, 121, 50, '2004-02-06 00:00:00'),
       (133, 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', 'ST_CLERK', 3300.00, NULL, 122, 50, '2004-02-06 00:00:00'),
       (134, 'Michael', 'Rogers', 'MROGERS', '650.127.1834', 'ST_CLERK', 2900.00, NULL, 122, 50, '2002-12-23 00:00:00'),
       (135, 'Ki', 'Gee', 'KGEE', '650.127.1734', 'ST_CLERK', 2400.00, NULL, 122, 50, '2002-12-23 00:00:00'),
       (136, 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', 'ST_CLERK', 2200.00, NULL, 122, 50,
        '2002-12-23 00:00:00'),
       (137, 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', 'ST_CLERK', 3600.00, NULL, 123, 50, '2002-12-23 00:00:00'),
       (138, 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', 'ST_CLERK', 3200.00, NULL, 123, 50, '2002-12-23 00:00:00'),
       (139, 'John', 'Seo', 'JSEO', '650.121.2019', 'ST_CLERK', 2700.00, NULL, 123, 50, '2002-12-23 00:00:00'),
       (140, 'Joshua', 'Patel', 'JPATEL', '650.121.1834', 'ST_CLERK', 2500.00, NULL, 123, 50, '2002-12-23 00:00:00'),
       (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', 'ST_CLERK', 3500.00, NULL, 124, 50, '2002-12-23 00:00:00'),
       (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', 'ST_CLERK', 3100.00, NULL, 124, 50, '2002-12-23 00:00:00'),
       (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', 'ST_CLERK', 2600.00, NULL, 124, 50, '2002-12-23 00:00:00'),
       (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', 'ST_CLERK', 2500.00, NULL, 124, 50, '2002-12-23 00:00:00'),
       (145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', 'SA_MAN', 14000.00, 0.40, 100, 80,
        '2002-12-23 00:00:00'),
       (146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', 'SA_MAN', 13500.00, 0.30, 100, 80,
        '2002-12-23 00:00:00'),
       (147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', 'SA_MAN', 12000.00, 0.30, 100, 80,
        '2002-12-23 00:00:00'),
       (148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', 'SA_MAN', 11000.00, 0.30, 100, 80,
        '2002-12-23 00:00:00'),
       (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', 'SA_MAN', 10500.00, 0.20, 100, 80,
        '2002-12-23 00:00:00'),
       (150, 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', 'SA_REP', 10000.00, 0.30, 145, 80,
        '2014-03-05 00:00:00'),
       (151, 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', 'SA_REP', 9500.00, 0.25, 145, 80,
        '2014-03-05 00:00:00'),
       (152, 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', 'SA_REP', 9000.00, 0.25, 145, 80, '2014-03-05 00:00:00'),
       (153, 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', 'SA_REP', 8000.00, 0.20, 145, 80,
        '2014-03-05 00:00:00'),
       (154, 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', 'SA_REP', 7500.00, 0.20, 145, 80,
        '2014-03-05 00:00:00'),
       (155, 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', 'SA_REP', 7000.00, 0.15, 145, 80,
        '2014-03-05 00:00:00'),
       (156, 'Janette', 'K_ing', 'JKING', '011.44.1345.429268', 'SA_REP', 10000.00, 0.35, 146, 80,
        '2014-03-05 00:00:00'),
       (157, 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', 'SA_REP', 9500.00, 0.35, 146, 80,
        '2014-03-05 00:00:00'),
       (158, 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', 'SA_REP', 9000.00, 0.35, 146, 80,
        '2014-03-05 00:00:00'),
       (159, 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', 'SA_REP', 8000.00, 0.30, 146, 80,
        '2014-03-05 00:00:00'),
       (160, 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', 'SA_REP', 7500.00, 0.30, 146, 80,
        '2014-03-05 00:00:00'),
       (161, 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', 'SA_REP', 7000.00, 0.25, 146, 80,
        '2014-03-05 00:00:00'),
       (162, 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', 'SA_REP', 10500.00, 0.25, 147, 80,
        '2014-03-05 00:00:00'),
       (163, 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', 'SA_REP', 9500.00, 0.15, 147, 80,
        '2014-03-05 00:00:00'),
       (164, 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', 'SA_REP', 7200.00, 0.10, 147, 80,
        '2014-03-05 00:00:00'),
       (165, 'David', 'Lee', 'DLEE', '011.44.1346.529268', 'SA_REP', 6800.00, 0.10, 147, 80, '2014-03-05 00:00:00'),
       (166, 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', 'SA_REP', 6400.00, 0.10, 147, 80, '2014-03-05 00:00:00'),
       (167, 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', 'SA_REP', 6200.00, 0.10, 147, 80, '2014-03-05 00:00:00'),
       (168, 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', 'SA_REP', 11500.00, 0.25, 148, 80, '2014-03-05 00:00:00'),
       (169, 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', 'SA_REP', 10000.00, 0.20, 148, 80,
        '2014-03-05 00:00:00'),
       (170, 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', 'SA_REP', 9600.00, 0.20, 148, 80, '2014-03-05 00:00:00'),
       (171, 'William', 'Smith', 'WSMITH', '011.44.1343.629268', 'SA_REP', 7400.00, 0.15, 148, 80,
        '2014-03-05 00:00:00'),
       (172, 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', 'SA_REP', 7300.00, 0.15, 148, 80,
        '2014-03-05 00:00:00'),
       (173, 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', 'SA_REP', 6100.00, 0.10, 148, 80,
        '2014-03-05 00:00:00'),
       (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', 'SA_REP', 11000.00, 0.30, 149, 80, '2014-03-05 00:00:00'),
       (175, 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', 'SA_REP', 8800.00, 0.25, 149, 80,
        '2014-03-05 00:00:00'),
       (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', 'SA_REP', 8600.00, 0.20, 149, 80,
        '2014-03-05 00:00:00'),
       (177, 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', 'SA_REP', 8400.00, 0.20, 149, 80,
        '2014-03-05 00:00:00'),
       (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', 'SA_REP', 7000.00, 0.15, 149, NULL,
        '2014-03-05 00:00:00'),
       (179, 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', 'SA_REP', 6200.00, 0.10, 149, 80,
        '2014-03-05 00:00:00'),
       (180, 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', 'SH_CLERK', 3200.00, NULL, 120, 50, '2014-03-05 00:00:00'),
       (181, 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', 'SH_CLERK', 3100.00, NULL, 120, 50, '2014-03-05 00:00:00'),
       (182, 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', 'SH_CLERK', 2500.00, NULL, 120, 50,
        '2014-03-05 00:00:00'),
       (183, 'Girard', 'Geoni', 'GGEONI', '650.507.9879', 'SH_CLERK', 2800.00, NULL, 120, 50, '2014-03-05 00:00:00'),
       (184, 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', 'SH_CLERK', 4200.00, NULL, 121, 50,
        '2014-03-05 00:00:00'),
       (185, 'Alexis', 'Bull', 'ABULL', '650.509.2876', 'SH_CLERK', 4100.00, NULL, 121, 50, '2014-03-05 00:00:00'),
       (186, 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', 'SH_CLERK', 3400.00, NULL, 121, 50,
        '2014-03-05 00:00:00'),
       (187, 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', 'SH_CLERK', 3000.00, NULL, 121, 50, '2014-03-05 00:00:00'),
       (188, 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', 'SH_CLERK', 3800.00, NULL, 122, 50, '2014-03-05 00:00:00'),
       (189, 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', 'SH_CLERK', 3600.00, NULL, 122, 50, '2014-03-05 00:00:00'),
       (190, 'Timothy', 'Gates', 'TGATES', '650.505.3876', 'SH_CLERK', 2900.00, NULL, 122, 50, '2014-03-05 00:00:00'),
       (191, 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', 'SH_CLERK', 2500.00, NULL, 122, 50,
        '2014-03-05 00:00:00'),
       (192, 'Sarah', 'Bell', 'SBELL', '650.501.1876', 'SH_CLERK', 4000.00, NULL, 123, 50, '2014-03-05 00:00:00'),
       (193, 'Britney', 'Everett', 'BEVERETT', '650.501.2876', 'SH_CLERK', 3900.00, NULL, 123, 50,
        '2014-03-05 00:00:00'),
       (194, 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', 'SH_CLERK', 3200.00, NULL, 123, 50, '2014-03-05 00:00:00'),
       (195, 'Vance', 'Jones', 'VJONES', '650.501.4876', 'SH_CLERK', 2800.00, NULL, 123, 50, '2014-03-05 00:00:00'),
       (196, 'Alana', 'Walsh', 'AWALSH', '650.507.9811', 'SH_CLERK', 3100.00, NULL, 124, 50, '2014-03-05 00:00:00'),
       (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', 'SH_CLERK', 3000.00, NULL, 124, 50, '2014-03-05 00:00:00'),
       (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', 'SH_CLERK', 2600.00, NULL, 124, 50,
        '2014-03-05 00:00:00'),
       (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', 'SH_CLERK', 2600.00, NULL, 124, 50, '2014-03-05 00:00:00'),
       (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', 'AD_ASST', 4400.00, NULL, 101, 10, '2016-03-03 00:00:00'),
       (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', 'MK_MAN', 13000.00, NULL, 100, 20,
        '2016-03-03 00:00:00'),
       (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', 'MK_REP', 6000.00, NULL, 201, 20, '2016-03-03 00:00:00'),
       (203, 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', 'HR_REP', 6500.00, NULL, 101, 40, '2016-03-03 00:00:00'),
       (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', 'PR_REP', 10000.00, NULL, 101, 70, '2016-03-03 00:00:00'),
       (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', 'AC_MGR', 12000.00, NULL, 101, 110,
        '2016-03-03 00:00:00'),
       (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', 'AC_ACCOUNT', 8300.00, NULL, 205, 110,
        '2016-03-03 00:00:00');

/*Table structure for table `jobs` */

DROP TABLE IF EXISTS `jobs`;

CREATE TABLE `jobs`
(
    `job_id`     varchar(10) NOT NULL,
    `job_title`  varchar(35) DEFAULT NULL,
    `min_salary` int(6)      DEFAULT NULL,
    `max_salary` int(6)      DEFAULT NULL,
    PRIMARY KEY (`job_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

/*Data for the table `jobs` */

insert into `jobs`(`job_id`, `job_title`, `min_salary`, `max_salary`)
values ('AC_ACCOUNT', 'Public Accountant', 4200, 9000),
       ('AC_MGR', 'Accounting Manager', 8200, 16000),
       ('AD_ASST', 'Administration Assistant', 3000, 6000),
       ('AD_PRES', 'President', 20000, 40000),
       ('AD_VP', 'Administration Vice President', 15000, 30000),
       ('FI_ACCOUNT', 'Accountant', 4200, 9000),
       ('FI_MGR', 'Finance Manager', 8200, 16000),
       ('HR_REP', 'Human Resources Representative', 4000, 9000),
       ('IT_PROG', 'Programmer', 4000, 10000),
       ('MK_MAN', 'Marketing Manager', 9000, 15000),
       ('MK_REP', 'Marketing Representative', 4000, 9000),
       ('PR_REP', 'Public Relations Representative', 4500, 10500),
       ('PU_CLERK', 'Purchasing Clerk', 2500, 5500),
       ('PU_MAN', 'Purchasing Manager', 8000, 15000),
       ('SA_MAN', 'Sales Manager', 10000, 20000),
       ('SA_REP', 'Sales Representative', 6000, 12000),
       ('SH_CLERK', 'Shipping Clerk', 2500, 5500),
       ('ST_CLERK', 'Stock Clerk', 2000, 5000),
       ('ST_MAN', 'Stock Manager', 5500, 8500);

/*Table structure for table `locations` */

DROP TABLE IF EXISTS `locations`;

CREATE TABLE `locations`
(
    `location_id`    int(11) NOT NULL AUTO_INCREMENT,
    `street_address` varchar(40) DEFAULT NULL,
    `postal_code`    varchar(12) DEFAULT NULL,
    `city`           varchar(30) DEFAULT NULL,
    `state_province` varchar(25) DEFAULT NULL,
    `country_id`     varchar(2)  DEFAULT NULL,
    PRIMARY KEY (`location_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 3201
  DEFAULT CHARSET = utf8;

/*Data for the table `locations` */

insert into `locations`(`location_id`, `street_address`, `postal_code`, `city`, `state_province`, `country_id`)
values (1000, '1297 Via Cola di Rie', '00989', 'Roma', NULL, 'IT'),
       (1100, '93091 Calle della Testa', '10934', 'Venice', NULL, 'IT'),
       (1200, '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP'),
       (1300, '9450 Kamiya-cho', '6823', 'Hiroshima', NULL, 'JP'),
       (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US'),
       (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US'),
       (1600, '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US'),
       (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US'),
       (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA'),
       (1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA'),
       (2000, '40-5-12 Laogianggen', '190518', 'Beijing', NULL, 'CN'),
       (2100, '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN'),
       (2200, '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU'),
       (2300, '198 Clementi North', '540198', 'Singapore', NULL, 'SG'),
       (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK'),
       (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK'),
       (2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK'),
       (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE'),
       (2800, 'Rua Frei Caneca 1360 ', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR'),
       (2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH'),
       (3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH'),
       (3100, 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL'),
       (3200, 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');

/*!40101 SET SQL_MODE = @OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES = @OLD_SQL_NOTES */;

将上面SQL语句复制到mysql的命令行界面执行即可。

包含四张表:

截图-1582771795

  • departments
  • employees
  • jobs
  • locations
本节阅读完毕! (分享
二维码图片 扫描关注我们哟