查询
先将下面内容内容复制到 zhengxinonly_db.sql 文件中
zhengxinonly_db.sql 文件内容
sql
-- MySQL dump 10.13 Distrib 8.0.12, for Win64 (x86_64)
--
-- Host: 127.0.0.1 Database: zhengxin
-- ------------------------------------------------------
-- Server version 8.0.12
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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 */;
--
-- Table structure for table `countries`
--
DROP TABLE IF EXISTS `countries`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `countries` (
`country_id` char(2) NOT NULL,
`country_name` varchar(40) DEFAULT NULL,
`region_id` int(11) DEFAULT NULL,
PRIMARY KEY (`country_id`),
KEY `countr_reg_fk` (`region_id`),
CONSTRAINT `countr_reg_fk` FOREIGN KEY (`region_id`) REFERENCES `regions` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `countries`
--
LOCK TABLES `countries` WRITE;
/*!40000 ALTER TABLE `countries` DISABLE KEYS */;
INSERT INTO `countries` (`country_id`, `country_name`, `region_id`) VALUES ('AR','阿根廷',2),('AU','澳大利亚',3),('BE','比利时',1),('BR','巴西',2),('CA','加拿大',2),('CH','瑞士',1),('CN','中国',3),('DE','德国',1),('DK','丹麦',1),('EG','埃及',4),('FR','法国',1),('HK','香港',3),('IL','以色列',4),('IN','印度',3),('IT','意大利',1),('JP','日本',3),('KW','科威特',4),('MX','墨西哥',2),('NG','尼日利亚',4),('NL','荷兰',1),('SG','新加坡',3),('UK','英国',1),('US','美国',2),('ZM','赞比亚',4),('ZW','津巴布韦',4);
/*!40000 ALTER TABLE `countries` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `departments`
--
DROP TABLE IF EXISTS `departments`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `departments` (
`department_id` int(4) NOT NULL DEFAULT '0',
`department_name` varchar(30) NOT NULL,
`manager_id` int(6) DEFAULT NULL,
`location_id` int(4) DEFAULT NULL,
PRIMARY KEY (`department_id`),
UNIQUE KEY `dept_id_pk` (`department_id`),
KEY `dept_loc_fk` (`location_id`),
KEY `dept_mgr_fk` (`manager_id`),
CONSTRAINT `dept_loc_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
CONSTRAINT `dept_mgr_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `departments`
--
LOCK TABLES `departments` WRITE;
/*!40000 ALTER TABLE `departments` DISABLE KEYS */;
INSERT INTO `departments` (`department_id`, `department_name`, `manager_id`, `location_id`) VALUES (10,'行政部',200,1700),(20,'营销部',201,1800),(30,'采购部',114,1700),(40,'人力资源部',203,2400),(50,'运输部门',121,1500),(60,'IT部',103,1400),(70,'公共关系部',204,2700),(80,'销售部门',145,2500),(90,'执行部',100,1700),(100,'财务部门',108,1700),(110,'会计部',205,1700),(120,'财政部',NULL,1700),(130,'企业税务部',NULL,1700),(140,'控制与信用部',NULL,1700),(150,'股东服务部',NULL,1700),(160,'福利服务部',NULL,1700),(170,'制造部',NULL,1700),(180,'建设部',NULL,1700),(190,'承包部',NULL,1700),(200,'经营部',NULL,1700),(210,'IT支持部门',NULL,1700),(220,'国家奥委会部门',NULL,1700),(230,'IT 服务台部门',NULL,1700),(240,'政府销售部',NULL,1700),(250,'零售销售部',NULL,1700),(260,'招聘部门',NULL,1700),(270,'薪资部',NULL,1700);
/*!40000 ALTER TABLE `departments` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Temporary view structure for view `emp_details_view`
--
DROP TABLE IF EXISTS `emp_details_view`;
/*!50001 DROP VIEW IF EXISTS `emp_details_view`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8mb4;
/*!50001 CREATE VIEW `emp_details_view` AS SELECT
1 AS `employee_id`,
1 AS `job_id`,
1 AS `manager_id`,
1 AS `department_id`,
1 AS `location_id`,
1 AS `country_id`,
1 AS `first_name`,
1 AS `last_name`,
1 AS `salary`,
1 AS `commission_pct`,
1 AS `department_name`,
1 AS `job_title`,
1 AS `city`,
1 AS `state_province`,
1 AS `country_name`,
1 AS `region_name`*/;
SET character_set_client = @saved_cs_client;
--
-- Table structure for table `employees`
--
DROP TABLE IF EXISTS `employees`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `employees` (
`employee_id` int(6) NOT NULL DEFAULT '0',
`first_name` varchar(20) DEFAULT NULL,
`last_name` varchar(25) NOT NULL,
`email` varchar(25) NOT NULL,
`phone_number` varchar(20) DEFAULT NULL,
`hire_date` date NOT NULL,
`job_id` varchar(10) NOT NULL,
`salary` double(8,2) DEFAULT NULL,
`commission_pct` double(2,2) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`department_id` int(4) DEFAULT NULL,
PRIMARY KEY (`employee_id`),
UNIQUE KEY `emp_email_uk` (`email`),
UNIQUE KEY `emp_emp_id_pk` (`employee_id`),
KEY `emp_dept_fk` (`department_id`),
KEY `emp_job_fk` (`job_id`),
KEY `emp_manager_fk` (`manager_id`),
CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
CONSTRAINT `emp_job_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`),
CONSTRAINT `emp_manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `employees`
--
LOCK TABLES `employees` WRITE;
/*!40000 ALTER TABLE `employees` DISABLE KEYS */;
INSERT INTO `employees` (`employee_id`, `first_name`, `last_name`, `email`, `phone_number`, `hire_date`, `job_id`, `salary`, `commission_pct`, `manager_id`, `department_id`) VALUES (100,'史蒂文','金','SKING','515.123.4567','1987-06-17','AD_PRES',24000.00,NULL,NULL,90),(101,'尼娜','科赫哈','NKOCHHAR','515.123.4568','1989-09-21','AD_VP',17000.00,NULL,100,90),(102,'莱克斯','德哈恩','LDEHAAN','515.123.4569','1993-01-13','AD_VP',17000.00,NULL,100,90),(103,'亚历山大','胡诺德','AHUNOLD','590.423.4567','1990-01-03','IT_PROG',9000.00,NULL,102,60),(104,'布鲁斯','恩斯特','BERNST','590.423.4568','1991-05-21','IT_PROG',6000.00,NULL,103,60),(105,'大卫','奥斯汀','DAUSTIN','590.423.4569','1997-06-25','IT_PROG',4800.00,NULL,103,60),(106,'瓦利','帕塔巴拉','VPATABAL','590.423.4560','1998-02-05','IT_PROG',4800.00,NULL,103,60),(107,'戴安娜','洛伦兹','DLORENTZ','590.423.5567','1999-02-07','IT_PROG',4200.00,NULL,103,60),(108,'南希','格林伯格','NGREENBE','515.124.4569','1994-08-17','FI_MGR',12000.00,NULL,101,100),(109,'丹尼尔','法维特','DFAVIET','515.124.4169','1994-08-16','FI_ACCOUNT',9000.00,NULL,108,100),(110,'约翰','陈','JCHEN','515.124.4269','1997-09-28','FI_ACCOUNT',8200.00,NULL,108,100),(111,'伊斯梅尔','斯西亚拉','ISCIARRA','515.124.4369','1997-09-30','FI_ACCOUNT',7700.00,NULL,108,100),(112,'何塞曼努埃尔','乌尔曼','JMURMAN','515.124.4469','1998-03-07','FI_ACCOUNT',7800.00,NULL,108,100),(113,'路易斯','波普','LPOPP','515.124.4567','1999-12-07','FI_ACCOUNT',6900.00,NULL,108,100),(114,'丹','拉斐利','DRAPHEAL','515.127.4561','1994-12-07','PU_MAN',11000.00,NULL,100,30),(115,'亚历山大','邱','AKHOO','515.127.4562','1995-05-18','PU_CLERK',3100.00,NULL,114,30),(116,'雪莉','拜达','SBAIDA','515.127.4563','1997-12-24','PU_CLERK',2900.00,NULL,114,30),(117,'西格尔','托比亚斯','STOBIAS','515.127.4564','1997-07-24','PU_CLERK',2800.00,NULL,114,30),(118,'盖伊','希穆罗','GHIMURO','515.127.4565','1998-11-15','PU_CLERK',2600.00,NULL,114,30),(119,' 凯伦','科梅纳雷斯','KCOLMENA','515.127.4566','1999-08-10','PU_CLERK',2500.00,NULL,114,30),(120,'马修','韦斯','MWEISS','650.123.1234','1996-07-18','ST_MAN',8000.00,NULL,100,50),(121,'亚当','弗里普','AFRIPP','650.123.2234','1997-04-10','ST_MAN',8200.00,NULL,100,50),(122,'帕亚姆','考夫林','PKAUFLIN','650.123.3234','1995-05-01','ST_MAN',7900.00,NULL,100,50),(123,'桑塔','沃尔曼','SVOLLMAN','650.123.4234','1997-10-10','ST_MAN',6500.00,NULL,100,50),(124,' 凯文','莫尔戈斯','KMOURGOS','650.123.5234','1999-11-16','ST_MAN',5800.00,NULL,100,50),(125,'朱莉娅','内尔','JNAYER','650.124.1214','1997-07-16','ST_CLERK',3200.00,NULL,120,50),(126,'艾琳','米基利内尼','IMIKKILI','650.124.1224','1998-09-28','ST_CLERK',2700.00,NULL,120,50),(127,'詹姆斯','兰德里','JLANDRY','650.124.1334','1999-01-14','ST_CLERK',2400.00,NULL,120,50),(128,'史蒂文','马克','SMARKLE','650.124.1434','2000-03-08','ST_CLERK',2200.00,NULL,120,50),(129,'劳拉','比索','LBISSOT','650.124.5234','1997-08-20','ST_CLERK',3300.00,NULL,121,50),(130,'莫哲','阿特金森','MATKINSO','650.124.6234','1997-10-30','ST_CLERK',2800.00,NULL,121,50),(131,'詹姆斯','马洛','JAMRLOW','650.124.7234','1997-02-16','ST_CLERK',2500.00,NULL,121,50),(132,'TJ','奥尔森','TJOLSON','650.124.8234','1999-04-10','ST_CLERK',2100.00,NULL,121,50),(133,'杰森','马林','JMALLIN','650.127.1934','1996-06-14','ST_CLERK',3300.00,NULL,122,50),(134,'迈克尔','罗杰斯','MROGERS','650.127.1834','1998-08-26','ST_CLERK',2900.00,NULL,122,50),(135,'基','哎呀','KGEE','650.127.1734','1999-12-12','ST_CLERK',2400.00,NULL,122,50),(136,'榛子','菲尔油轮','HPHILTAN','650.127.1634','2000-02-06','ST_CLERK',2200.00,NULL,122,50),(137,'伦斯克','拉德维格','RLADWIG','650.121.1234','1995-07-14','ST_CLERK',3600.00,NULL,123,50),(138,'斯蒂芬','斯泰尔斯','SSTILES','650.121.2034','1997-10-26','ST_CLERK',3200.00,NULL,123,50),(139,'约翰','徐','JSEO','650.121.2019','1998-02-12','ST_CLERK',2700.00,NULL,123,50),(140,'约书亚','帕特尔','JPATEL','650.121.1834','1998-04-06','ST_CLERK',2500.00,NULL,123,50),(141,'特伦纳','拉伊斯','TRAJS','650.121.8009','1995-10-17','ST_CLERK',3500.00,NULL,124,50),(142,'柯蒂斯','戴维斯','CDAVIES','650.121.2994','1997-01-29','ST_CLERK',3100.00,NULL,124,50),(143,'兰德尔','马托斯','RMATOS','650.121.2874','1998-03-15','ST_CLERK',2600.00,NULL,124,50),(144,'彼得','巴尔加斯','PVARGAS','650.121.2004','1998-07-09','ST_CLERK',2500.00,NULL,124,50),(145,'约翰','拉塞尔','JRUSSEL','011.44.1344.429268','1996-10-01','SA_MAN',14000.00,0.40,100,80),(146,' 凯伦','伙伴','KPARTNER','011.44.1344.467268','1997-01-05','SA_MAN',13500.00,0.30,100,80),(147,'阿尔贝托','伊拉苏','AERRAZUR','011.44.1344.429278','1997-03-10','SA_MAN',12000.00,0.30,100,80),(148,'杰拉德','坎布罗','GCAMBRAU','011.44.1344.619268','1999-10-15','SA_MAN',11000.00,0.30,100,80),(149,'埃莱妮','兹洛基','EZLOTKEY','011.44.1344.429018','2000-01-29','SA_MAN',10500.00,0.20,100,80),(150,'彼得','塔克','PTUCKER','011.44.1344.129268','1997-01-30','SA_REP',10000.00,0.30,145,80),(151,'大卫','伯恩斯坦','DBERNSTE','011.44.1344.345268','1997-03-24','SA_REP',9500.00,0.25,145,80),(152,'彼得','霍尔','PHALL','011.44.1344.478968','1997-08-20','SA_REP',9000.00,0.25,145,80),(153,'克里斯托弗','奥尔森','COLSEN','011.44.1344.498718','1998-03-30','SA_REP',8000.00,0.20,145,80),(154,'纳内特','坎布罗','NCAMBRAU','011.44.1344.987668','1998-12-09','SA_REP',7500.00,0.20,145,80),(155,'奥利弗','图沃','OTUVAULT','011.44.1344.486508','1999-11-23','SA_REP',7000.00,0.15,145,80),(156,'珍妮特','金','JKING','011.44.1345.429268','1996-01-30','SA_REP',10000.00,0.35,146,80),(157,'帕特里克','苏利','PSULLY','011.44.1345.929268','1996-03-04','SA_REP',9500.00,0.35,146,80),(158,'艾伦','麦克尤恩','AMCEWEN','011.44.1345.829268','1996-08-01','SA_REP',9000.00,0.35,146,80),(159,'林赛','史密斯','LSMITH','011.44.1345.729268','1997-03-10','SA_REP',8000.00,0.30,146,80),(160,'路易斯','多兰','LDORAN','011.44.1345.629268','1997-12-15','SA_REP',7500.00,0.30,146,80),(161,'萨拉斯','休厄尔','SSEWALL','011.44.1345.529268','1998-11-03','SA_REP',7000.00,0.25,146,80),(162,'克拉拉','维什尼','CVISHNEY','011.44.1346.129268','1997-11-11','SA_REP',10500.00,0.25,147,80),(163,'丹尼尔','格林','DGREENE','011.44.1346.229268','1999-03-19','SA_REP',9500.00,0.15,147,80),(164,'马蒂亚','马文斯','MMARVINS','011.44.1346.329268','2000-01-24','SA_REP',7200.00,0.10,147,80),(165,'大卫','李','DLEE','011.44.1346.529268','2000-02-23','SA_REP',6800.00,0.10,147,80),(166,'桑达尔','安德','SANDE','011.44.1346.629268','2000-03-24','SA_REP',6400.00,0.10,147,80),(167,'阿米特','班达','ABANDA','011.44.1346.729268','2000-04-21','SA_REP',6200.00,0.10,147,80),(168,'丽莎','奥泽尔','LOZER','011.44.1343.929268','1997-03-11','SA_REP',11500.00,0.25,148,80),(169,'哈里森','布鲁姆','HBLOOM','011.44.1343.829268','1998-03-23','SA_REP',10000.00,0.20,148,80),(170,'泰勒','福克斯','TFOX','011.44.1343.729268','1998-01-24','SA_REP',9600.00,0.20,148,80),(171,'威廉','史密斯','WSMITH','011.44.1343.629268','1999-02-23','SA_REP',7400.00,0.15,148,80),(172,'伊丽莎白','贝茨','EBATES','011.44.1343.529268','1999-03-24','SA_REP',7300.00,0.15,148,80),(173,'桑迪塔','库马尔','SKUMAR','011.44.1343.329268','2000-04-21','SA_REP',6100.00,0.10,148,80),(174,'艾伦','阿贝尔','EABEL','011.44.1644.429267','1996-05-11','SA_REP',11000.00,0.30,149,80),(175,'艾莉莎','赫顿','AHUTTON','011.44.1644.429266','1997-03-19','SA_REP',8800.00,0.25,149,80),(176,'乔纳森','泰勒','JTAYLOR','011.44.1644.429265','1998-03-24','SA_REP',8600.00,0.20,149,80),(177,'杰克','利文斯顿','JLIVINGS','011.44.1644.429264','1998-04-23','SA_REP',8400.00,0.20,149,80),(178,'金伯利','格兰特','KGRANT','011.44.1644.429263','1999-05-24','SA_REP',7000.00,0.15,149,NULL),(179,'查尔斯','约翰逊','CJOHNSON','011.44.1644.429262','2000-01-04','SA_REP',6200.00,0.10,149,80),(180,'温斯顿','泰勒','WTAYLOR','650.507.9876','1998-01-24','SH_CLERK',3200.00,NULL,120,50),(181,'吉恩','芙蓉','JFLEAUR','650.507.9877','1998-02-23','SH_CLERK',3100.00,NULL,120,50),(182,' 玛莎','沙利文','MSULLIVA','650.507.9878','1999-06-21','SH_CLERK',2500.00,NULL,120,50),(183,'吉拉德','杰奥尼','GGEONI','650.507.9879','2000-02-03','SH_CLERK',2800.00,NULL,120,50),(184,'南迪塔','萨尚德','NSARCHAN','650.509.1876','1996-01-27','SH_CLERK',4200.00,NULL,121,50),(185,'亚历克西斯','公牛','ABULL','650.509.2876','1997-02-20','SH_CLERK',4100.00,NULL,121,50),(186,'朱莉娅','德林格','JDELLING','650.509.3876','1998-06-24','SH_CLERK',3400.00,NULL,121,50),(187,'安东尼','卡布里奥','ACABRIO','650.509.4876','1999-02-07','SH_CLERK',3000.00,NULL,121,50),(188,'凯利','钟','KCHUNG','650.505.1876','1997-06-14','SH_CLERK',3800.00,NULL,122,50),(189,'詹妮弗','迪莉','JDILLY','650.505.2876','1997-08-13','SH_CLERK',3600.00,NULL,122,50),(190,'蒂莫西','盖茨','TGATES','650.505.3876','1998-07-11','SH_CLERK',2900.00,NULL,122,50),(191,'兰德尔','帕金斯','RPERKINS','650.505.4876','1999-12-19','SH_CLERK',2500.00,NULL,122,50),(192,'莎拉','贝尔','SBELL','650.501.1876','1996-02-04','SH_CLERK',4000.00,NULL,123,50),(193,'布兰妮','埃弗雷特','BEVERETT','650.501.2876','1997-03-03','SH_CLERK',3900.00,NULL,123,50),(194,'塞缪尔','麦凯恩','SMCCAIN','650.501.3876','1998-07-01','SH_CLERK',3200.00,NULL,123,50),(195,'万斯','琼斯','VJONES','650.501.4876','1999-03-17','SH_CLERK',2800.00,NULL,123,50),(196,'阿拉娜','沃尔什','AWALSH','650.507.9811','1998-04-24','SH_CLERK',3100.00,NULL,124,50),(197,' 凯文','菲尼','KFEENEY','650.507.9822','1998-05-23','SH_CLERK',3000.00,NULL,124,50),(198,'唐纳德','奥康奈尔','DOCONNEL','650.507.9833','1999-06-21','SH_CLERK',2600.00,NULL,124,50),(199,'道格拉斯','格兰特','DGRANT','650.507.9844','2000-01-13','SH_CLERK',2600.00,NULL,124,50),(200,'詹妮弗','威伦','JWHALEN','515.123.4444','1987-09-17','AD_ASST',4400.00,NULL,101,10),(201,'迈克尔','哈特斯坦','MHARTSTE','515.123.5555','1996-02-17','MK_MAN',13000.00,NULL,100,20),(202,'帕特','费伊','PFAY','603.123.6666','1997-08-17','MK_REP',6000.00,NULL,201,20),(203,'苏珊','马夫里斯','SMAVRIS','515.123.7777','1994-06-07','HR_REP',6500.00,NULL,101,40),(204,'赫尔曼','贝尔','HBAER','515.123.8888','1994-06-07','PR_REP',10000.00,NULL,101,70),(205,'雪莱','希金斯','SHIGGINS','515.123.8080','1994-06-07','AC_MGR',12000.00,NULL,101,110),(206,'威廉','吉茨','WGIETZ','515.123.8181','1994-06-07','AC_ACCOUNT',8300.00,NULL,205,110);
/*!40000 ALTER TABLE `employees` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `job_grades`
--
DROP TABLE IF EXISTS `job_grades`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `job_grades` (
`grade_level` varchar(3) DEFAULT NULL,
`lowest_sal` int(11) DEFAULT NULL,
`highest_sal` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `job_grades`
--
LOCK TABLES `job_grades` WRITE;
/*!40000 ALTER TABLE `job_grades` DISABLE KEYS */;
INSERT INTO `job_grades` (`grade_level`, `lowest_sal`, `highest_sal`) VALUES ('A',1000,2999),('B',3000,5999),('C',6000,9999),('D',10000,14999),('E',15000,24999),('F',25000,40000);
/*!40000 ALTER TABLE `job_grades` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `job_history`
--
DROP TABLE IF EXISTS `job_history`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `job_history` (
`employee_id` int(6) NOT NULL,
`start_date` date NOT NULL,
`end_date` date NOT NULL,
`job_id` varchar(10) NOT NULL,
`department_id` int(4) DEFAULT NULL,
PRIMARY KEY (`employee_id`,`start_date`),
UNIQUE KEY `jhist_emp_id_st_date_pk` (`employee_id`,`start_date`),
KEY `jhist_job_fk` (`job_id`),
KEY `jhist_dept_fk` (`department_id`),
CONSTRAINT `jhist_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
CONSTRAINT `jhist_emp_fk` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`),
CONSTRAINT `jhist_job_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `job_history`
--
LOCK TABLES `job_history` WRITE;
/*!40000 ALTER TABLE `job_history` DISABLE KEYS */;
INSERT INTO `job_history` (`employee_id`, `start_date`, `end_date`, `job_id`, `department_id`) VALUES (101,'1989-09-21','1993-10-27','AC_ACCOUNT',110),(101,'1993-10-28','1997-03-15','AC_MGR',110),(102,'1993-01-13','1998-07-24','IT_PROG',60),(114,'1998-03-24','1999-12-31','ST_CLERK',50),(122,'1999-01-01','1999-12-31','ST_CLERK',50),(176,'1998-03-24','1998-12-31','SA_REP',80),(176,'1999-01-01','1999-12-31','SA_MAN',80),(200,'1987-09-17','1993-06-17','AD_ASST',90),(200,'1994-07-01','1998-12-31','AC_ACCOUNT',90),(201,'1996-02-17','1999-12-19','MK_REP',20);
/*!40000 ALTER TABLE `job_history` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `jobs`
--
DROP TABLE IF EXISTS `jobs`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `jobs` (
`job_id` varchar(10) NOT NULL DEFAULT '',
`job_title` varchar(35) NOT NULL,
`min_salary` int(6) DEFAULT NULL,
`max_salary` int(6) DEFAULT NULL,
PRIMARY KEY (`job_id`),
UNIQUE KEY `job_id_pk` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `jobs`
--
LOCK TABLES `jobs` WRITE;
/*!40000 ALTER TABLE `jobs` DISABLE KEYS */;
INSERT INTO `jobs` (`job_id`, `job_title`, `min_salary`, `max_salary`) VALUES ('AC_ACCOUNT','公众会计',4200,9000),('AC_MGR','会计经理',8200,16000),('AD_ASST','行政助理',3000,6000),('AD_PRES','总裁',20000,40000),('AD_VP','行政副总裁',15000,30000),('FI_ACCOUNT','会计',4200,9000),('FI_MGR','财务经理',8200,16000),('HR_REP','人力资源代表',4000,9000),('IT_PROG','程序员',4000,10000),('MK_MAN','市场经理',9000,15000),('MK_REP','营销代表',4000,9000),('PR_REP','公共关系代表',4500,10500),('PU_CLERK','采购员',2500,5500),('PU_MAN','采购经理',8000,15000),('SA_MAN','销售经理',10000,20000),('SA_REP','销售代表',6000,12000),('SH_CLERK','货运员',2500,5500),('ST_CLERK','市场业务员',2000,5000),('ST_MAN','市场经理',5500,8500);
/*!40000 ALTER TABLE `jobs` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `locations`
--
DROP TABLE IF EXISTS `locations`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `locations` (
`location_id` int(4) NOT NULL DEFAULT '0',
`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) DEFAULT NULL,
PRIMARY KEY (`location_id`),
UNIQUE KEY `loc_id_pk` (`location_id`),
KEY `loc_c_id_fk` (`country_id`),
CONSTRAINT `loc_c_id_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `locations`
--
LOCK TABLES `locations` WRITE;
/*!40000 ALTER TABLE `locations` DISABLE KEYS */;
INSERT INTO `locations` (`location_id`, `street_address`, `postal_code`, `city`, `state_province`, `country_id`) VALUES (1000,'1297 Via Cola di Rie','00989','罗马',NULL,'IT'),(1100,'93091 Calle della Testa','10934','威尼斯',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','东京','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','广岛',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','南湖','Texas','US'),(1500,'2011 Interiors Blvd','99236','南旧金山','California','US'),(1600,'2007 Zagora St','50090','南不伦瑞克','New Jersey','US'),(1700,'2004 Charade Rd','98199','西雅图','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','多伦多','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','怀特霍斯','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','北京',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','孟买','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','悉尼','New South Wales','AU'),(2300,'198 Clementi North','540198','新加坡',NULL,'SG'),(2400,'8204 Arthur St',NULL,'伦敦',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','牛津','Oxford','UK'),(2600,'9702 Chester Road','09629850293','斯特雷特福德','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','慕尼黑','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','圣保罗','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','日内瓦','Geneve','CH'),(3000,'Murtenstrasse 921','3095','伯尔尼','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','乌得勒支','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','墨西哥城','Distrito Federal,','MX');
/*!40000 ALTER TABLE `locations` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `order`
--
DROP TABLE IF EXISTS `order`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `order` (
`order_id` int(11) DEFAULT NULL,
`order_name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `order`
--
LOCK TABLES `order` WRITE;
/*!40000 ALTER TABLE `order` DISABLE KEYS */;
INSERT INTO `order` (`order_id`, `order_name`) VALUES (1,'shkstart'),(2,'tomcat'),(3,'dubbo');
/*!40000 ALTER TABLE `order` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `regions`
--
DROP TABLE IF EXISTS `regions`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `regions` (
`region_id` int(11) NOT NULL,
`region_name` varchar(25) DEFAULT NULL,
PRIMARY KEY (`region_id`),
UNIQUE KEY `reg_id_pk` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `regions`
--
LOCK TABLES `regions` WRITE;
/*!40000 ALTER TABLE `regions` DISABLE KEYS */;
INSERT INTO `regions` (`region_id`, `region_name`) VALUES (1,'欧洲'),(2,'美洲'),(3,'亚洲'),(4,'中东与非洲');
/*!40000 ALTER TABLE `regions` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Final view structure for view `emp_details_view`
--
/*!50001 DROP VIEW IF EXISTS `emp_details_view`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `emp_details_view` AS select `e`.`employee_id` AS `employee_id`,`e`.`job_id` AS `job_id`,`e`.`manager_id` AS `manager_id`,`e`.`department_id` AS `department_id`,`d`.`location_id` AS `location_id`,`l`.`country_id` AS `country_id`,`e`.`first_name` AS `first_name`,`e`.`last_name` AS `last_name`,`e`.`salary` AS `salary`,`e`.`commission_pct` AS `commission_pct`,`d`.`department_name` AS `department_name`,`j`.`job_title` AS `job_title`,`l`.`city` AS `city`,`l`.`state_province` AS `state_province`,`c`.`country_name` AS `country_name`,`r`.`region_name` AS `region_name` from (((((`employees` `e` join `departments` `d`) join `jobs` `j`) join `locations` `l`) join `countries` `c`) join `regions` `r`) where ((`e`.`department_id` = `d`.`department_id`) and (`d`.`location_id` = `l`.`location_id`) and (`l`.`country_id` = `c`.`country_id`) and (`c`.`region_id` = `r`.`region_id`) and (`j`.`job_id` = `e`.`job_id`)) */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2023-07-06 21:23:06然后在命令行客户端登录 mysql，使用 source 指令导入
shell
mysql> source zhengxinonly_db.sqlshell
mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)基本的 SELECT 语句
SELECT ...
sql
SELECT 1; -- 没有任何子句
SELECT 9 / 2; -- 没有任何子句SELECT ... FROM
语法：
sqlSELECT 标识选择哪些列 FROM 标识从哪个表中选择;选择全部列：
sqlSELECT * FROM departments;一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符
'*'。 使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。 通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。在生产环境下，不推荐你直接使用SELECT *进行查询。选择特定的列：
sqlSELECT department_id, location_id FROM departments;
列的别名
- 重命名一个列
- 便于计算
- 紧跟列名，也可以在列名和别名之间加入关键字 AS ，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
- AS 可以省略
- 建议别名简短，见名知意
举例
sql
SELECT last_name AS name, commission_pct comm
FROM employees;获取姓名、年薪
sql
SELECT last_name "Name", salary * 12 "Annual Salary"
FROM employees;去除重复行
默认情况下，查询会返回全部行，包括重复行。
sql
SELECT department_id FROM employees;在 SELECT 语句中使用关键字 DISTINCT 去除重复行
sql
SELECT DISTINCT department_id FROM employees;针对于：
sql
SELECT DISTINCT department_id, salary FROM employees;这里有两点需要注意：
DISTINCT需要放到所有列名的前面，如果写成SELECT salary, DISTINCT department_id FROM employees会报错。DISTINCT其实是对后面所有列名的组合进行去重，你能看到最后的结果是 74 条，因为这 74 个部门 id 不同，都有salary这个属性值。如果你想要看都有哪些不同的部门（department_id），只需要写DISTINCT department_id即可，后面不需要再加其他的列名了。
空值参与运算
所有运算符或列值遇到 null 值，运算的结果都为 null
sqlSELECT employee_id, salary, commission_pct, 12 * salary * (1 + commission_pct) "annual_sal" FROM employees;
这里你一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。
着重号
错误的
shellmysql> SELECT * FROM ORDER; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER' at line 1正确的
shellmysql> SELECT * FROM `ORDER`; +----------+------------+ | order_id | order_name | +----------+------------+ | 1 | shkstart | | 2 | tomcat | | 3 | dubbo | +----------+------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM `order`; +----------+------------+ | order_id | order_name | +----------+------------+ | 1 | shkstart | | 2 | tomcat | | 3 | dubbo | +----------+------------+ 3 rows in set (0.00 sec)结论
我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在 SQL 语句中使用一对 ``（着重号）引起来。
查询常数
SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
你可能会问为什么我们还要对常数进行查询呢？
SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation，这个字段固定值为“正心全栈编程”，可以这样写：
sql
SELECT '正心全栈编程' as company, last_name
FROM employees;显示表结构
使用 DESCRIBE 或 DESC 命令，表示表结构。
sql
DESCRIBE employees;
-- 或
DESC employees;shell
mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)其中，各个字段的含义分别解释如下：
- Field：表示字段名称。
- Type：表示字段类型。
- Null：表示该列是否可以存储 NULL 值。
- Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分；
UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。 - Default：表示该列是否有默认值，如果有，那么值是多少。
- Extra：表示可以获取的与给定列有关的附加信息，例如
AUTO_INCREMENT等。
过滤查询
语法：
sqlSELECT 字段1,字段2 FROM 表名 WHERE 过滤条件- 使用 WHERE 子句，将不满足条件的行过滤掉
- WHERE 子句紧随 FROM 子句
举例
sqlSELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90;