数据库教程-SQL示例数据库

跨零代码为大家提供高品质的解决方案,请大家多多来访,跨零不胜感激,在此谢过。

在本教程中,我们将向您介绍在整个教程中使用的SQL示例数据库。以下数据库关系图 – HR 示例数据库:

SQL示例数据库

在这个HR 示例数据库有7个表:

  • employees表存储员工的数据信息。
  • jobs表存储工作数据信息,包括职位和工资范围。
  • departments表存储部门数据信息。
  • dependents表存储员工的家属信息。
  • locations表存储公司各部门的所在位置信息。
  • countries表存储公司开展业务的国家/地区的数据。
  • regions表存储亚洲,欧洲,美洲,中东和非洲等地区的数据。 这些国家分为不同的地区。

下图显示了表名称及其记录数据数量。

编号 表名称 行数
1 employees 40
2 dependents 30
3 departments 11
4 jobs 19
5 locations 7
6 countries 25
7 regions 4

通常,要使用SQL,需要安装关系数据库管理系统(RDBMS)。 如果您使用过MySQL,PostgreSQL,Oracle数据库,SQL Server或SQLite等关系数据库管理系统(RDBMS),则可以使用以下对应脚本创建示例数据库。

1. MySQL

以下SQL脚本是用于在MySQL中创建HR 示例数据库。

CREATE TABLE regions (     region_id INT (11) AUTO_INCREMENT PRIMARY KEY,     region_name VARCHAR (25) DEFAULT NULL );  CREATE TABLE countries (     country_id CHAR (2) PRIMARY KEY,     country_name VARCHAR (40) DEFAULT NULL,     region_id INT (11) NOT NULL,     FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE );  CREATE TABLE locations (     location_id INT (11) AUTO_INCREMENT PRIMARY KEY,     street_address VARCHAR (40) DEFAULT NULL,     postal_code VARCHAR (12) DEFAULT NULL,     city VARCHAR (30) NOT NULL,     state_province VARCHAR (25) DEFAULT NULL,     country_id CHAR (2) NOT NULL,     FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE );  CREATE TABLE jobs (     job_id INT (11) AUTO_INCREMENT PRIMARY KEY,     job_title VARCHAR (35) NOT NULL,     min_salary DECIMAL (8, 2) DEFAULT NULL,     max_salary DECIMAL (8, 2) DEFAULT NULL );  CREATE TABLE departments (     department_id INT (11) AUTO_INCREMENT PRIMARY KEY,     department_name VARCHAR (30) NOT NULL,     location_id INT (11) DEFAULT NULL,     FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE );  CREATE TABLE employees (     employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR (20) DEFAULT NULL,     last_name VARCHAR (25) NOT NULL,     email VARCHAR (100) NOT NULL,     phone_number VARCHAR (20) DEFAULT NULL,     hire_date DATE NOT NULL,     job_id INT (11) NOT NULL,     salary DECIMAL (8, 2) NOT NULL,     manager_id INT (11) DEFAULT NULL,     department_id INT (11) DEFAULT NULL,     FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,     FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,     FOREIGN KEY (manager_id) REFERENCES employees (employee_id) );  CREATE TABLE dependents (     dependent_id INT (11) AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR (50) NOT NULL,     last_name VARCHAR (50) NOT NULL,     relationship VARCHAR (25) NOT NULL,     employee_id INT (11) NOT NULL,     FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE ); 

2. PostgreSQL

以下脚本用于在PostgreSQL中创建HR 示例数据库结构。

CREATE TABLE regions (     region_id SERIAL PRIMARY KEY,     region_name CHARACTER VARYING (25) );  CREATE TABLE countries (     country_id CHARACTER (2) PRIMARY KEY,     country_name CHARACTER VARYING (40),     region_id INTEGER NOT NULL,     FOREIGN KEY (region_id) REFERENCES regions (region_id) ON UPDATE CASCADE ON DELETE CASCADE );  CREATE TABLE locations (     location_id SERIAL PRIMARY KEY,     street_address CHARACTER VARYING (40),     postal_code CHARACTER VARYING (12),     city CHARACTER VARYING (30) NOT NULL,     state_province CHARACTER VARYING (25),     country_id CHARACTER (2) NOT NULL,     FOREIGN KEY (country_id) REFERENCES countries (country_id) ON UPDATE CASCADE ON DELETE CASCADE );  CREATE TABLE departments (     department_id SERIAL PRIMARY KEY,     department_name CHARACTER VARYING (30) NOT NULL,     location_id INTEGER,     FOREIGN KEY (location_id) REFERENCES locations (location_id) ON UPDATE CASCADE ON DELETE CASCADE );  CREATE TABLE jobs (     job_id SERIAL PRIMARY KEY,     job_title CHARACTER VARYING (35) NOT NULL,     min_salary NUMERIC (8, 2),     max_salary NUMERIC (8, 2) );  CREATE TABLE employees (     employee_id SERIAL PRIMARY KEY,     first_name CHARACTER VARYING (20),     last_name CHARACTER VARYING (25) NOT NULL,     email CHARACTER VARYING (100) NOT NULL,     phone_number CHARACTER VARYING (20),     hire_date DATE NOT NULL,     job_id INTEGER NOT NULL,     salary NUMERIC (8, 2) NOT NULL,     manager_id INTEGER,     department_id INTEGER,     FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON UPDATE CASCADE ON DELETE CASCADE,     FOREIGN KEY (department_id) REFERENCES departments (department_id) ON UPDATE CASCADE ON DELETE CASCADE,     FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ON UPDATE CASCADE ON DELETE CASCADE );  CREATE TABLE dependents (     dependent_id SERIAL PRIMARY KEY,     first_name CHARACTER VARYING (50) NOT NULL,     last_name CHARACTER VARYING (50) NOT NULL,     relationship CHARACTER VARYING (25) NOT NULL,     employee_id INTEGER NOT NULL,     FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE ); 

3. Microsoft SQL Server

以下脚本用于在Microsoft SQL Server中创建HR 示例数据库结构。

CREATE TABLE regions (     region_id INT IDENTITY(1,1) PRIMARY KEY,     region_name VARCHAR (25) DEFAULT NULL );  CREATE TABLE countries (     country_id CHAR (2) PRIMARY KEY,     country_name VARCHAR (40) DEFAULT NULL,     region_id INT NOT NULL,     FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE );  CREATE TABLE locations (     location_id INT IDENTITY(1,1) PRIMARY KEY,     street_address VARCHAR (40) DEFAULT NULL,     postal_code VARCHAR (12) DEFAULT NULL,     city VARCHAR (30) NOT NULL,     state_province VARCHAR (25) DEFAULT NULL,     country_id CHAR (2) NOT NULL,     FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE );  CREATE TABLE jobs (     job_id INT IDENTITY(1,1) PRIMARY KEY,     job_title VARCHAR (35) NOT NULL,     min_salary DECIMAL (8, 2) DEFAULT NULL,     max_salary DECIMAL (8, 2) DEFAULT NULL );  CREATE TABLE departments (     department_id INT IDENTITY(1,1) PRIMARY KEY,     department_name VARCHAR (30) NOT NULL,     location_id INT DEFAULT NULL,     FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE );  CREATE TABLE employees (     employee_id INT IDENTITY(1,1) PRIMARY KEY,     first_name VARCHAR (20) DEFAULT NULL,     last_name VARCHAR (25) NOT NULL,     email VARCHAR (100) NOT NULL,     phone_number VARCHAR (20) DEFAULT NULL,     hire_date DATE NOT NULL,     job_id INT NOT NULL,     salary DECIMAL (8, 2) NOT NULL,     manager_id INT DEFAULT NULL,     department_id INT DEFAULT NULL,     FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,     FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,     FOREIGN KEY (manager_id) REFERENCES employees (employee_id) );  CREATE TABLE dependents (     dependent_id INT IDENTITY(1,1) PRIMARY KEY,     first_name VARCHAR (50) NOT NULL,     last_name VARCHAR (50) NOT NULL,     relationship VARCHAR (25) NOT NULL,     employee_id INT NOT NULL,     FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE ); 

4. Oracle数据库(> 12c)

以下脚本用于在Oracle Database 12c中创建HR 示例数据库结构。

CREATE TABLE regions (     region_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,     region_name VARCHAR2 (25) DEFAULT NULL );  CREATE TABLE countries (     country_id CHAR (2) PRIMARY KEY,     country_name VARCHAR2 (40) DEFAULT NULL,     region_id NUMBER NOT NULL,     FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE );  CREATE TABLE locations (     location_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,     street_address VARCHAR2 (40) DEFAULT NULL,     postal_code VARCHAR2 (12) DEFAULT NULL,     city VARCHAR2 (30) NOT NULL,     state_province VARCHAR2 (25) DEFAULT NULL,     country_id CHAR (2) NOT NULL,     FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE );  CREATE TABLE jobs (     job_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,     job_title VARCHAR2 (35) NOT NULL,     min_salary NUMBER (8, 2) DEFAULT NULL,     max_salary NUMBER (8, 2) DEFAULT NULL );  CREATE TABLE departments (     department_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,     department_name VARCHAR2 (30) NOT NULL,     location_id NUMBER DEFAULT NULL,     FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE );  CREATE TABLE employees (     employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,     first_name VARCHAR2 (20) DEFAULT NULL,     last_name VARCHAR2 (25) NOT NULL,     email VARCHAR2 (100) NOT NULL,     phone_number VARCHAR2 (20) DEFAULT NULL,     hire_date DATE NOT NULL,     job_id NUMBER NOT NULL,     salary NUMBER (8, 2) NOT NULL,     manager_id NUMBER DEFAULT NULL,     department_id NUMBER DEFAULT NULL,     FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE,     FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE,     FOREIGN KEY (manager_id) REFERENCES employees (employee_id) );  CREATE TABLE dependents (     dependent_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,     first_name VARCHAR2 (50) NOT NULL,     last_name VARCHAR2 (50) NOT NULL,     relationship VARCHAR2 (25) NOT NULL,     employee_id NUMBER NOT NULL,     FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ); 

5. SQLite

以下脚本用于在SQLite中创建HR 示例数据库结构。

CREATE TABLE regions (     region_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,     region_name text NOT NULL );  CREATE TABLE countries (     country_id text NOT NULL,     country_name text NOT NULL,     region_id INTEGER NOT NULL,     PRIMARY KEY (country_id ASC),     FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE );  CREATE TABLE locations (     location_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,     street_address text,     postal_code text,     city text NOT NULL,     state_province text,     country_id INTEGER NOT NULL,     FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE );  CREATE TABLE departments (     department_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,     department_name text NOT NULL,     location_id INTEGER NOT NULL,     FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE );  CREATE TABLE jobs (     job_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,     job_title text NOT NULL,     min_salary double NOT NULL,     max_salary double NOT NULL );  CREATE TABLE employees (     employee_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,     first_name text,     last_name text NOT NULL,     email text NOT NULL,     phone_number text,     hire_date text NOT NULL,     job_id INTEGER NOT NULL,     salary double NOT NULL,     manager_id INTEGER,     department_id INTEGER NOT NULL,     FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,     FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,     FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE );  CREATE TABLE dependents (     dependent_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,     first_name text NOT NULL,     last_name text NOT NULL,     relationship text NOT NULL,     employee_id INTEGER NOT NULL,     FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE ); 

6. 导入数据

以下脚本用于将数据加载到上面HR示例数据库创建的表中。

/*Data for the table regions */  INSERT INTO regions(region_id,region_name) VALUES (1,'欧洲'); INSERT INTO regions(region_id,region_name) VALUES (2,'美洲'); INSERT INTO regions(region_id,region_name) VALUES (3,'亚洲'); INSERT INTO regions(region_id,region_name) VALUES (4,'中东和非洲');  /*Data for the table countries */ INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','阿根廷',2); INSERT INTO countries(country_id,country_name,region_id) VALUES ('AU','澳大利亚',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('BE','比利时',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('BR','巴西',2); INSERT INTO countries(country_id,country_name,region_id) VALUES ('CA','加拿大',2); INSERT INTO countries(country_id,country_name,region_id) VALUES ('CH','瑞士',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('CN','中国',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','德国',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('DK','丹麦',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('EG','埃及',4); INSERT INTO countries(country_id,country_name,region_id) VALUES ('FR','法国',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('HK','香港',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('IL','以色列',4); INSERT INTO countries(country_id,country_name,region_id) VALUES ('IN','印度',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('IT','意大利',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('JP','日本',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('KW','科威特',4); INSERT INTO countries(country_id,country_name,region_id) VALUES ('MX','墨西哥',2); INSERT INTO countries(country_id,country_name,region_id) VALUES ('NG','尼日利亚',4); INSERT INTO countries(country_id,country_name,region_id) VALUES ('NL','荷兰',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('SG','新加坡',3); INSERT INTO countries(country_id,country_name,region_id) VALUES ('UK','英国',1); INSERT INTO countries(country_id,country_name,region_id) VALUES ('US','美国',2); INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZM','赞比亚',4); INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZW','津巴布韦',4);    /*Data for the table locations */ INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'); INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'); INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1700,'2004 Charade Rd','98199','Seattle','Washington','US'); INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'); INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2400,'8204 Arthur St',NULL,'London',NULL,'UK'); INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'); INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');    /*Data for the table jobs */  INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (1,'会计师',4200.00,9000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (2,'会计经理',8200.00,16000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (3,'行政助理',3000.00,6000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (4,'主席',20000.00,40000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (5,'行政副主席',15000.00,30000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (6,'会计',4200.00,9000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (7,'财务经理',8200.00,16000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (8,'人力资源代表',4000.00,9000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (9,'程序员',4000.00,10000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (10,'市场经理',9000.00,15000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (11,'市场代表',4000.00,9000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (12,'公关代表',4500.00,10500.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (13,'采购职员',2500.00,5500.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (14,'采购经理',8000.00,15000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (15,'销售经理',10000.00,20000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (16,'销售代表',6000.00,12000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (17,'运输职员',2500.00,5500.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (18,'库存职员',2000.00,5000.00); INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (19,'库存管理',5500.00,8500.00);    /*Data for the table departments */  INSERT INTO departments(department_id,department_name,location_id) VALUES (1,'管理',1700); INSERT INTO departments(department_id,department_name,location_id) VALUES (2,'市场营销',1800); INSERT INTO departments(department_id,department_name,location_id) VALUES (3,'采购',1700); INSERT INTO departments(department_id,department_name,location_id) VALUES (4,'人力资源',2400); INSERT INTO departments(department_id,department_name,location_id) VALUES (5,'运输',1500); INSERT INTO departments(department_id,department_name,location_id) VALUES (6,'IT',1400); INSERT INTO departments(department_id,department_name,location_id) VALUES (7,'公共关系',2700); INSERT INTO departments(department_id,department_name,location_id) VALUES (8,'销售',2500); INSERT INTO departments(department_id,department_name,location_id) VALUES (9,'行政人员',1700); INSERT INTO departments(department_id,department_name,location_id) VALUES (10,'财务',1700); INSERT INTO departments(department_id,department_name,location_id) VALUES (11,'会计',1700);    /*Data for the table employees */ INSERT INTO `employees` VALUES ('100', 'Steven', 'Lee', 'steven.lee@yiibai.com', '0532-86011111', '1987-06-17', '4', '24000.00', null, '9'); INSERT INTO `employees` VALUES ('101', 'Neena', 'Wong', 'neena.wong@kaops.com', '0551-4243311', '1989-09-21', '5', '17000.00', '100', '9'); INSERT INTO `employees` VALUES ('102', 'Lex', 'Liang', 'lex.liang@kaops.com', '0571-87622362', '1993-01-13', '5', '17000.00', '100', '9'); INSERT INTO `employees` VALUES ('103', 'Alexander', 'Lee', 'alexander.lee@kaops.com', '020-95105105', '1990-01-03', '9', '9000.00', '102', '6'); INSERT INTO `employees` VALUES ('104', 'Bruce', 'Wong', 'bruce.wong@yiibai.com', '0371-68356666', '1991-05-21', '9', '6000.00', '103', '6'); INSERT INTO `employees` VALUES ('105', 'David', 'Liang', 'david.liang@kaops.com', '0512-67513131', '1997-06-25', '9', '4800.00', '103', '6'); INSERT INTO `employees` VALUES ('106', 'Valli', 'Chen', 'valli.chen@yiibai.com', '0535-95105175', '1998-02-05', '9', '4800.00', '103', '6'); INSERT INTO `employees` VALUES ('107', 'Diana', 'Chen', 'diana.chen@yiibai.com', '025-95105105', '1999-02-07', '9', '4200.00', '103', '6'); INSERT INTO `employees` VALUES ('108', 'Nancy', 'Chen', 'nancy.chen@yiibai.com', '0531-86012520', '1994-08-17', '7', '12000.00', '101', '10'); INSERT INTO `employees` VALUES ('109', 'Daniel', 'Chen', 'daniel.chen@yiibai.com', '021-8008207890', '1994-08-16', '6', '9000.00', '108', '10'); INSERT INTO `employees` VALUES ('110', 'John', 'Chen', 'john.chen@yiibai.com', '0592-2088888', '1997-09-28', '6', '8200.00', '108', '10'); INSERT INTO `employees` VALUES ('111', 'Ismael', 'Su', 'ismael.su@yiibai.com', '029-95105688', '1997-09-30', '6', '7700.00', '108', '10'); INSERT INTO `employees` VALUES ('112', 'Max', 'Su', 'max.su@yiibai.com', '021-95105105', '1998-03-07', '6', '7800.00', '108', '10'); INSERT INTO `employees` VALUES ('113', 'Min', 'Su', 'min.su@yiibai.com', '027-88068888', '1999-12-07', '6', '6900.00', '108', '10'); INSERT INTO `employees` VALUES ('114', 'Avg', 'Su', 'avg.su@yiibai.com', '0755-82328647', '1994-12-07', '14', '11000.00', '100', '3'); INSERT INTO `employees` VALUES ('115', 'Alexander', 'Su', 'alexander.su@yiibai.com', '0431-86122222', '1995-05-18', '13', '3100.00', '114', '3'); INSERT INTO `employees` VALUES ('116', 'Shelli', 'Zhang', 'shelli.zhang@kaops.com', '0771-2222222', '1997-12-24', '13', '2900.00', '114', '3'); INSERT INTO `employees` VALUES ('117', 'Sigal', 'Zhang', 'sigal.zhang@yiibai.com', '0791-6101074', '1997-07-24', '13', '2800.00', '114', '3'); INSERT INTO `employees` VALUES ('118', 'Guy', 'Zhang', 'guy.zhang@kaops.com', '0411-82603331', '1998-11-15', '13', '2600.00', '114', '3'); INSERT INTO `employees` VALUES ('119', 'Karen', 'Zhang', 'karen.zhang@yiibai.com', '010-51019999', '1999-08-10', '13', '2500.00', '114', '3'); INSERT INTO `employees` VALUES ('120', 'Matthew', 'Han', 'matthew.Han@yiibai.com', '0574-56163111', '1996-07-18', '19', '8000.00', '100', '5'); INSERT INTO `employees` VALUES ('121', 'Max', 'Han', 'Max.han@yiibai.com', '0731-2637122', '1997-04-10', '19', '8200.00', '100', '5'); INSERT INTO `employees` VALUES ('122', 'Min', 'Liu', 'Min.liu@yiibai.com', '023-63862607', '1995-05-01', '19', '7900.00', '100', '5'); INSERT INTO `employees` VALUES ('123', 'Shanta', 'Liu', 'shanta.liu@yiibai.com', '311-87600111', '1997-10-10', '19', '6500.00', '100', '5'); INSERT INTO `employees` VALUES ('126', 'Irene', 'Liu', 'irene.liu@kaops.com', '0752-95105688', '1998-09-28', '18', '2700.00', '120', '5'); INSERT INTO `employees` VALUES ('145', 'John', 'Liu', 'john.liu@yiibai.com', null, '1996-10-01', '15', '14000.00', '100', '8'); INSERT INTO `employees` VALUES ('146', 'Karen', 'Liu', 'karen.liu@yiibai.com', null, '1997-01-05', '15', '13500.00', '100', '8'); INSERT INTO `employees` VALUES ('176', 'Jonathon', 'Yang', 'jonathon.yang@yiibai.com', null, '1998-03-24', '16', '8600.00', '100', '8'); INSERT INTO `employees` VALUES ('177', 'Jack', 'Yang', 'jack.yang@yiibai.com', null, '1998-04-23', '16', '8400.00', '100', '8'); INSERT INTO `employees` VALUES ('178', 'Kimberely', 'Yang', 'kimberely.yang@yiibai.com', null, '1999-05-24', '16', '7000.00', '100', '8'); INSERT INTO `employees` VALUES ('179', 'Charles', 'Yang', 'charles.yang@yiibai.com', null, '2000-01-04', '16', '6200.00', '100', '8'); INSERT INTO `employees` VALUES ('192', 'Sarah', 'Yang', 'sarah.yang@kaops.com', '0351-2233611', '1996-02-04', '17', '4000.00', '123', '5'); INSERT INTO `employees` VALUES ('193', 'Britney', 'Zhao', 'britney.zhao@yiibai.com', '0351-2233611', '1997-03-03', '17', '3900.00', '123', '5'); INSERT INTO `employees` VALUES ('200', 'Jennifer', 'Zhao', 'jennifer.zhao@yiibai.com', '021-66050000', '1987-09-17', '3', '4400.00', '101', '1'); INSERT INTO `employees` VALUES ('201', 'Michael', 'Zhou', 'michael.zhou@yiibai.com', '010-67237328', '1996-02-17', '10', '13000.00', '100', '2'); INSERT INTO `employees` VALUES ('202', 'Pat', 'Zhou', 'pat.zhou@yiibai.com', '0755-28114518', '1997-08-17', '11', '6000.00', '201', '2'); INSERT INTO `employees` VALUES ('203', 'Susan', 'Zhou', 'susan.zhou@yiibai.com', '0755-83587526', '1994-06-07', '8', '6500.00', '101', '4'); INSERT INTO `employees` VALUES ('204', 'Hermann', 'Wu', 'hermann.wu@yiibai.com', '0513-83512816', '1994-06-07', '12', '10000.00', '101', '7'); INSERT INTO `employees` VALUES ('205', 'Shelley', 'Wu', 'shelley.wu@yiibai.com', '0898-31686222', '1994-06-07', '2', '12000.00', '101', '11'); INSERT INTO `employees` VALUES ('206', 'William', 'Wu', 'william.wu@yiibai.com', '022-26144822', '1994-06-07', '1', '8300.00', '205', '11');    /*Data for the table dependents */  INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (1,'Penelope','Wu','Child',206); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (2,'Nick','Wu','Child',205); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (3,'Ed','Zhao','Child',200); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (4,'Jennifer','Lee','Child',100); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (5,'Johnny','Wong','Child',101); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (6,'Bette','Liang','Child',102); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (7,'Grace','Chen','Child',109); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (8,'Matthew','Chen','Child',110); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (9,'Joe','Su','Child',111); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (10,'Christian','Su','Child',112); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (11,'Zero','Su','Child',113); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (12,'Karl','Chen','Child',108); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (13,'Uma','Zhou','Child',203); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (14,'Vivien','Lee','Child',103); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (15,'Cuba','Wong','Child',104); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (16,'Fred','Liang','Child',105); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (17,'Helen','Chen','Child',106); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (18,'Dan','Chen','Child',107); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (19,'Bob','Zhou','Child',201); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (20,'Lucille','Zhou','Child',202); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (21,'Kirsten','Wu','Child',204); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (22,'Elvis','Su','Child',115); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (23,'Sandra','Zhang','Child',116); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (24,'Cameron','Zhang','Child',117); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (25,'Kevin','Zhang','Child',118); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (26,'Rip','Zhang','Child',119); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (27,'Julia','Su','Child',114); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (28,'Woody','Liu','Child',145); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (29,'Alec','Liu','Child',146); INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (30,'Sandra','Yang','Child',176); 

7. 删除表

以下是用于刷新示例数据库时,删除所有表的脚本。

DROP TABLE employees; DROP TABLE dependents; DROP TABLE departments; DROP TABLE locations; DROP TABLE countries; DROP TABLE regions; DROP TABLE jobs; 

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 sql教程 只为你绽放。

本文固定链接: http://kua0.com/2019/01/25/数据库教程-sql示例数据库/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注