mysql数据库的操作（一）

如需用我的练习表，发消息qq:2383241998@qq.com

对应库myemployees

建库的sql文件
Navicat Premium Data Transfer

Source Server : mylink
Source Server Type : MySQL
Source Server Version : 80013
Source Host : localhost:3306
Source Schema : myemployees

Target Server Type : MySQL
Target Server Version : 80013
File Encoding : 65001

Date: 31/03/2019 16:49:43
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for departments

---

DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
department_id int(4) NOT NULL AUTO_INCREMENT,
department_name varchar(3) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
manager_id int(6) NULL DEFAULT NULL,
location_id int(4) NULL DEFAULT NULL,
PRIMARY KEY (department_id) USING BTREE,
INDEX loc_id_fk(location_id) USING BTREE,
CONSTRAINT loc_id_fk FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 271 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Dynamic;

---

– Records of departments

---

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

---

– Table structure for employees

---

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
employee_id int(6) NOT NULL AUTO_INCREMENT,
first_name varchar(20) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
last_name varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
email varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
phone_number varchar(20) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
job_id varchar(10) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
salary double(10, 2) NULL DEFAULT NULL,
commission_pct double(4, 2) NULL DEFAULT NULL,
manager_id int(6) NULL DEFAULT NULL,
department_id int(4) NULL DEFAULT NULL,
hiredate datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (employee_id) USING BTREE,
INDEX dept_id_fk(department_id) USING BTREE,
INDEX job_id_fk(job_id) USING BTREE,
CONSTRAINT dept_id_fk FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT job_id_fk FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 207 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Dynamic;

---

– Records of employees

---

INSERT INTO employees VALUES (100, ‘Steven’, ‘K_ing’, ‘SKING’, ‘515.123.4567’, ‘AD_PRES’, 24000.00, NULL, NULL, 90, ‘1992-04-03 00:00:00’);
INSERT INTO employees VALUES (101, ‘Neena’, ‘Kochhar’, ‘NKOCHHAR’, ‘515.123.4568’, ‘AD_VP’, 17000.00, NULL, 100, 90, ‘1992-04-03 00:00:00’);
INSERT INTO employees VALUES (102, ‘Lex’, ‘De Haan’, ‘LDEHAAN’, ‘515.123.4569’, ‘AD_VP’, 17000.00, NULL, 100, 90, ‘1992-04-03 00:00:00’);
INSERT INTO employees VALUES (103, ‘Alexander’, ‘Hunold’, ‘AHUNOLD’, ‘590.423.4567’, ‘IT_PROG’, 9000.00, NULL, 102, 60, ‘1992-04-03 00:00:00’);
INSERT INTO employees VALUES (104, ‘Bruce’, ‘Ernst’, ‘BERNST’, ‘590.423.4568’, ‘IT_PROG’, 6000.00, NULL, 103, 60, ‘1992-04-03 00:00:00’);
INSERT INTO employees VALUES (105, ‘David’, ‘Austin’, ‘DAUSTIN’, ‘590.423.4569’, ‘IT_PROG’, 4800.00, NULL, 103, 60, ‘1998-03-03 00:00:00’);
INSERT INTO employees VALUES (106, ‘Valli’, ‘P