irai の知识文档站

🐬MySQL

【pre】使用的数据

sql

/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.7.28-log : Database - atguigudb
*********************************************************************
*/

/*!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*/`atguigudb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `atguigudb`;

/*Table structure for table `countries` */

DROP TABLE IF EXISTS `countries`;

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;

/*Data for the table `countries` */

insert  into `countries`(`country_id`,`country_name`,`region_id`) values ('AR','Argentina',2),('AU','Australia',3),('BE','Belgium',1),('BR','Brazil',2),('CA','Canada',2),('CH','Switzerland',1),('CN','China',3),('DE','Germany',1),('DK','Denmark',1),('EG','Egypt',4),('FR','France',1),('HK','HongKong',3),('IL','Israel',4),('IN','India',3),('IT','Italy',1),('JP','Japan',3),('KW','Kuwait',4),('MX','Mexico',2),('NG','Nigeria',4),('NL','Netherlands',1),('SG','Singapore',3),('UK','United Kingdom',1),('US','United States of America',2),('ZM','Zambia',4),('ZW','Zimbabwe',4);

/*Table structure for table `departments` */

DROP TABLE IF EXISTS `departments`;

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;

/*Data for the table `departments` */

insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Administration',200,1700),(20,'Marketing',201,1800),(30,'Purchasing',114,1700),(40,'Human Resources',203,2400),(50,'Shipping',121,1500),(60,'IT',103,1400),(70,'Public Relations',204,2700),(80,'Sales',145,2500),(90,'Executive',100,1700),(100,'Finance',108,1700),(110,'Accounting',205,1700),(120,'Treasury',NULL,1700),(130,'Corporate Tax',NULL,1700),(140,'Control And Credit',NULL,1700),(150,'Shareholder Services',NULL,1700),(160,'Benefits',NULL,1700),(170,'Manufacturing',NULL,1700),(180,'Construction',NULL,1700),(190,'Contracting',NULL,1700),(200,'Operations',NULL,1700),(210,'IT Support',NULL,1700),(220,'NOC',NULL,1700),(230,'IT Helpdesk',NULL,1700),(240,'Government Sales',NULL,1700),(250,'Retail Sales',NULL,1700),(260,'Recruiting',NULL,1700),(270,'Payroll',NULL,1700);

/*Table structure for table `employees` */

DROP TABLE IF EXISTS `employees`;

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;

/*Data for the table `employees` */

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

/*Table structure for table `job_grades` */

DROP TABLE IF EXISTS `job_grades`;

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;

/*Data for the table `job_grades` */

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);

/*Table structure for table `job_history` */

DROP TABLE IF EXISTS `job_history`;

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;

/*Data for the table `job_history` */

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);

/*Table structure for table `jobs` */

DROP TABLE IF EXISTS `jobs`;

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;

/*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(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;

/*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');

/*Table structure for table `order` */

DROP TABLE IF EXISTS `order`;

CREATE TABLE `order` (
  `order_id` int(11) DEFAULT NULL,
  `order_name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `order` */

insert  into `order`(`order_id`,`order_name`) values (1,'shkstart'),(2,'tomcat'),(3,'dubbo');

/*Table structure for table `regions` */

DROP TABLE IF EXISTS `regions`;

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;

/*Data for the table `regions` */

insert  into `regions`(`region_id`,`region_name`) values (1,'Europe'),(2,'Americas'),(3,'Asia'),(4,'Middle East and Africa');

/*Table structure for table `emp_details_view` */

DROP TABLE IF EXISTS `emp_details_view`;

/*!50001 DROP VIEW IF EXISTS `emp_details_view` */;
/*!50001 DROP TABLE IF EXISTS `emp_details_view` */;

/*!50001 CREATE TABLE  `emp_details_view`(
 `employee_id` int(6) ,
 `job_id` varchar(10) ,
 `manager_id` int(6) ,
 `department_id` int(4) ,
 `location_id` int(4) ,
 `country_id` char(2) ,
 `first_name` varchar(20) ,
 `last_name` varchar(25) ,
 `salary` double(8,2) ,
 `commission_pct` double(2,2) ,
 `department_name` varchar(30) ,
 `job_title` varchar(35) ,
 `city` varchar(30) ,
 `state_province` varchar(25) ,
 `country_name` varchar(40) ,
 `region_name` varchar(25) 
)*/;

/*View structure for view emp_details_view */

/*!50001 DROP TABLE IF EXISTS `emp_details_view` */;
/*!50001 DROP VIEW IF EXISTS `emp_details_view` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 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`)) */;

/*!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 */;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227

一、引言

1.1 SQL 分类

SQL语言在功能上主要分为如下3大类：
- DDL（ DataDefinitionLanguages 、数据定义语言），这些语句定义了
  不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
  - 主要的语句关键字包括 create 、drop 、alter 等...
- DML（ DataManipulationLanguage 、数据操作语言），
  用于添加、删除、更新和查询数据库记录，并检查数据完整性。
  - 主要的语句关键字包括 insert 、delete 、update 、select 等...
- DCL（ DataControlLanguage 、数据控制语言），
  用于定义数据库、表、字段、用户的访问权限和安全级别。
  - 主要的语句关键字包括 grant 、revoke 、commit 、rollback 、savepoint 等...

因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。
还有单独将 commit 、rollback 取出来称为 TQL ( TransactionControlLanguage ，事务控制语言）

1.2 SQL 语言规则＆规范

1. 基本规则
- SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
- 每条命令以 ; 或 \g 或 \G 结束
- 关键字不能被缩写也不能分行
- 关于标点符号
  - 必须保证所有的 () 、单引号、双引号是成对结束的
  - 必须使用英文状态下的半角输入方式
  - 字符串型和日期时间类型的数据可以使用单引号（' '）表示
  - 列的别名，尽量使用双引号（" "），而且不建议省略 as
2. SQL 大小写规范（建议遵守）
- MySQL 在 Windows 环境下是大小写不敏感的
- MySQL 在 Linux 环境下是大小写敏感的
  - 数据库名、表名、表的别名、变量名是严格区分大小写的
  - 关键字、函数名、列名 (或字段名)、列的别名 (字段的别名) 是忽略大小写的。
- 推荐采用统一的书写规范：
  数据库名、表名、表别名、字段名、字段别名等都小写
  SQL 关键字、函数名、绑定变量等都大写

1.3 注释

可以使用如下格式的注释结构

sql

单行注释： # 注释文字 (MySQL特有的方式)
单行注释： -- 注释文字 (--后面必须包含一个空格。)
多行注释： /* 注释文字 */

二、基本的 select 语句

2.1.1 基本 select

语法：

sql

SELECT 标识选择哪些列
FROM 标识从哪个表中选择

无任何子句（简单计算）

会生成一个伪表 DUAL

sql

SELECT 1,9/2
from DUAL;	# 伪表 DUAL

# 上下效果一样

SELECT 1,9/2

选择全部列：

sql

SELECT * 
from departments;

选择特定的列：

sql

SELECT department_id,location_id
FROM departments;

2.1.2 列的别名

重命名一个列
便于计算
紧跟列名，也可以在列名和别名之间加入关键字 AS ，
别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
AS 可以省略 , 建议别名简短，见名知意

示例 1 :

sql

SELECT last_name AS name, commission_pct comm, employee_id "emp id"
FROM employees;

示例 2 ：

sql

SELECT last_name  as "Name", salary*12 "Annual Salary"
FROM employees;

2.1.3 去除重复行

默认的话，查询会返回所有行

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 即可，后面不需要再加其他的列名了。

2.1.4 空值参与运算

所有运算符或列值遇到 null 值，运算的结果都为 null

sql

SELECT employee_id,salary,commission_pct,
12 * salary * (1+commission_pct) as "annual_sal"
from employees;

如上效果：因为有些员工是没有 commission_pct 这个值的，所以他们的这里的数据为 null
但是我们希望正确的效果是：如果有 commission_pct 就正常按上面那样计算 annual_sal
如果 commission_pct 为 null 的话只需要计算 12 * salary 就好，或者把 null 改为 0

还有需要注意的是，在 mysql 里面，空值不等于空字符串。一个空字符串的长度为 0
而一个空值的长度是空。而且，在 mysql 里面，空值是占用空间的

2.1.5 着重号

错误的：

sql

SELECT * FROM order

刚好我们有些标识符的命名和关键字撞了，那么就需要用着重号（tab 键上面 / esc 下面）来告知 mysql

正确的：

sql

SELECT * FROM `order`

2.1.6 查询常数

SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的

取值是我们指定的，而不是从数据表中动态取出的。

你可能会问为什么我们还要对常数进行查询呢？

SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个

固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。

比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个

字段固定值为 “尚硅谷” ，可以这样写：

sql

SELECT '尚硅谷' as corporation, last_name FROM employees;

2.3 显示表结构

使用 DESCRIBE 或 DESC 命令，表示表结构。

sql

-- DESCRIBE employees
DESC employees

其中，各个字段的含义分别解释如下：

Field ：表示字段名称。
Type ：表示字段类型。
Null：表示该列是否可以存储 NULL 值。
Key ：表示该列是否已编制索引。PRI 表示该列是表主键的一部分；UNI 表示该列是 UNIQUE 索引的一
部分；MUL 表示在列中某个给定值允许出现多次。
Default ：表示该列是否有默认值，如果有，那么值是多少。
Extra ：表示可以获取的与给定列有关的附加信息，例如 AUTO_INCREMENT 等

2.4 过滤数据

背景：我们有个需求，我们现在不需要返回整张表，只想看 90号部门 的员工信息

语法：

sql

SELECT 字段1 , 字段2 .... 
FROM 表名
WHERE 过滤条件

使用 where 子句，将不满足条件的行过滤掉
where 子句紧随 from 子句后面

举例：

sql

SELECT employee_id , last_name , job_id , department_id
FROM employees
WHERE department_id = 90;

三、运算符

3.1 算数运算符

算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，
对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

注意点 ( 加减法 )：
- 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
- 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
- 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
- 在 Java 中，+ 的左右两边如果有字符串，那么表示字符串的拼接。
  但是在MySQL中 + 只表示数值相加。
  如果遇到非数值类型，先尝试转成数值，如果转失败，就按 0 计算。
  （补充：MySQL中字符串拼接要使用字符串函数 CONCAT() 实现）

注意点（乘除法）：
- 一个数乘以整数 1 和除以整数 1 后仍得原数；
- 一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
- 一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
- 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
- 乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
- 在数学运算中，0 不能用作除数，在MySQL中，一个数除以 0 为 NULL 。

3.2 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，
比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回 NULL 。
比较运算符经常被用来作为 SELECT 查询语句的条件来使用，返回符合条件的结果记录。

等号运算符
- 等号运算符（=）判断等号两边的值、字符串或表达式是否相等，
  如果相等则返回1，不相等则返回 0 。
- 在使用等号运算符时，遵循如下规则：
  - 如果等号两边的值、字符串或表达式都为字符串，则 MySQL 会按照字符串进行比较，
    其比较的是每个字符串中字符的 ANSI 编码是否相等。
  - 如果等号两边的值都是整数，则 MySQL 会按照整数来比较两个值的大小。
  - 如果等号两边的值一个是整数，另一个是字符串，则 MySQL 会将字符串转化为数字进行比较。
  - 如果等号两边的值、字符串或表达式中有一个为 NULL，则比较结果为 NULL。
- 对比：SQL 中赋值符号使用 :=

安全等于运算符
- 安全等于运算符（<=>）与等于运算符（=）的作用是相似的，
  唯一的区别是 <=> 可以用来对 NULL 进行判断。
  - 在两个操作数均为 NULL 时，其返回值为1，而不为 NULL；
  - 当一个操作数为 NULL 时，其返回值为0，而不为 NULL。

不等于运算符
- 不等于运算符（ <> 和 != ）用于判断两边的数字、字符串或者表达式的值是否不相等，
  如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。
  如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。

此外，还有非符号类型的运算符：

空运算符
- 空运算符（ IS NULL 或者 ISNULL ）判断一个值是否为 NULL，
  如果为NULL则返回 1 ，否则返回 0 。

非空运算符
- 非空运算符（IS NOT NULL）判断一个值是否不为 NULL，
  如果不为NULL则返回 1 ，否则返回 0 。

最小值运算符
- 语法格式为：LEAST(值1，值2，...，值n) 。其中，“值n” 表示参数列表中有 n 个值。
  在有两个或多个参数的情况下，返回最小值。
- 当参数是整数或者浮点数时，LEAST 将返回其中最小的值；
- 当参数为字符串时，返回字母表中顺序最靠前的字符；
- 当比较值列表中有 NULL时，不能判断大小，返回值为 NULL。

最大值运算符
- 语法格式为：GREATEST(值1，值2，...，值n) 。其中，n 表示参数列表中有n 个值。
  当有两个或多个参数时，返回值为最大值。
- 假如任意一个自变量为 NULL ，则 GREATEST() 的返回值为NULL。
- 当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；
- 当参数为字符串时，返回字母表中顺序最靠后的字符；

BETWEEN AND 运算符
- BETWEEN 运算符使用的格式通常为
  SELECT D FROM TABLE WHERE C BETWEEN A AND B，
  此时，当 C 大于或等于 A，并且 C 小于或等于 B 时，结果为 1，否则结果为 0。

IN 运算符
- IN 运算符用于判断给定的值是否是 IN 列表中的一个值，如果是则返回1，否则返回0。
- 如果给定的值为 NULL ，或者 IN 列表中存在 NULL，则结果为 NULL。

NOT IN 运算符
- NOT IN 运算符用于判断给定的值是否不是 IN 列表中的一个值，
  如果不是 IN 列表中的一个值，则返回1，否则返回0。

LIKE 运算符
- LIKE 运算符主要用来匹配字符串，通常用于模糊匹配，
  如果满足条件则返回1，否则返回0。
- 如果给定的值或者匹配条件为 NULL ，则返回结果为 NULL 。
- LIKE 运算符通常使用如下通配符：
  sql
```
“%”：匹配 0 个或 多个字符。
“_”：只能匹配一个字符。
```
  1
  2
- ESCAPE ：转义符 , 回避特殊符号

REGEXP 运算符

REGEXP 运算符用来匹配字符串，语法格式为： expr REGEXP 匹配条件 。
如果 expr 满足匹配条件，返回1；如果不满足，则返回0。
若 expr 或匹配条件任意一个为 NULL ，则结果为 NULL 。
REGEXP 运算符在进行匹配时，常用的有下面几种通配符：

sql

（1）‘^’匹配以该字符后面的字符开头的字符串。

（2）‘$’匹配以该字符前面的字符结尾的字符串。

（3）‘.’匹配任何一个单字符。

（4）“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。
	为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

（5）‘*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，
	“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。

3.3 逻辑运算符

逻辑运算符主要用来判断表达式的真假，在 MySQL 中，逻辑运算符的返回结果为1、0或者NULL。
MySQL中支持4种逻辑运算符如下：

四、排序数据

4.1 排序规则

使用 order by 子句排序
ASC (ascend) ：升序
DESC (descend) ：降序
order by 子句在 select 语句的结尾。

4.2 单列排序

查询员工薪水信息，使用 salary 来降序排序

sql

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;

查询员工信息，雇佣时间离现在越近的排前面

sql

SELECT last_name , job_id , department_id , hire_date 
FROM employees
ORDER BY hire_date DESC;

4.3 多列排序

需求：先根据部门id 由小到大排序，部门id 一样的按照薪水由大到小排序

sql

SELECT last_name , department_id , salary 
FROM employees
ORDER BY department_id , salary DESC;

可以使用不在 select 列表中的列进行排序。
在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。
如果第一列数据中所有值都是唯一的，那么将不再对第二列进行排序。

五、分页

背景：
- 背景 1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？
- 背景 2：表里有 100 条数据，我们只想要显示第 2、3 条数据怎么办呢 ?

5.1 分页规则

分页原理：所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件

Mysql 中使用 limit 进行分页

格式（语法）：

sql

LIMIT [位置偏移量,] 行数

说明：
- 第一个 “位置偏移量” 参数指示 mysql 从哪一行开始显示，是一个可选参数，
  如果不指定第一个参数，将会从表中的第一条记录开始显示
  （第一条记录的位置偏移量是 0，第二条记录的位置偏移量是 1，以此类推 ...）
- 第二个参数 “行数” 指示返回的记录条数

5.2 分页举例

显示员工表前十条数据

sql

-- 前10条记录
-- SELECT * 
-- from employees 
-- LIMIT 0,10
# ----上下一样---
SELECT * 
from employees 
LIMIT 10

显示员工表第11条数据到第20条数据

sql

-- 第11条到第20条记录
SELECT * 
from employees 
LIMIT 10,10

mysql 8.0 后新特性
需求：从第5条记录开始后面的3条记录

sql

-- mysql 8.0
SELECT * 
from employees 
LIMIT 3 OFFSET 4
# ----上下一样---
SELECT * 
from employees 
LIMIT 4,3

六、多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个

关联字段可能建立了外键，也可能没有建立外键。

比如：员工表和部门表，这两个表依靠 “部门编号” 进行关联

6.1 一个案例引发的多表连接

6.1.1 案例说明

需求：查询员工的姓名及其部门名称

通过了解上面表的设计，我们在 employees 表里通过 employee_id 只能对应到
员工的 department_id 。而且部门名称 department_name 在另一个表 departments 里面

按照需求描述，我们可能会写出以下的 sql 语句

sql

-- 笛卡尔积的错误
SELECT last_name , department_name
FROM employees , departments;

问题：
- 怎么会有 2889 条数据的啊？？？
- 怎么会一个员工对应多个部门？？？

分析错误原因：

sql

SELECT COUNT(employee_id) FROM employees;
-- 输出107行

SELECT COUNT(department_id) FROM departments;
-- 输出27行

SELECT 107*27 FROM DUAL;
--输出 2889

我们把上述多表查询中出现的问题称为：笛卡尔积的错误。

6.1.2 笛卡尔积（交叉连接）的理解

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能

组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素

个数的乘积数。

SQL92 中，笛卡尔积也称为交叉连接，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交

叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。

在 MySQL 中如下情况会出现笛卡尔积：

sql

-- 查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

6.1.3 问题解决

笛卡尔积的错误会在下面条件下产生：
- 省略多个表的连接条件（或关联条件）
- 连接条件（或关联条件）无效
- 所有表中的所有行互相连接

为了避免笛卡尔积可以在 where 中加入有效的连接条件。
在表中有相同的列时，在列名之前加上表名前缀。
语法格式：

sql

SELECT table1.column , table2.column
FROM table1 , table2
WHERE table1.department_id = table2.department_id

正确写法：

sql

SELECT last_name , department_name
FROM employees , departments
WHERE employees.department_id = departments.department_id

6.2 多表查询分类讲解

6.2.1 等值连接 v.s. 非等值连接

等值连接

补充：可以在 From 语句里写表的别名，然后在 select 语句和 where 语句中使用别名
这样可以使得多表查询的语句更简洁高效。
在某种特定的情况下：多个表中有相同列的时候，必须在列名之前加上表名前缀。
否则会报 ambiguous 的错误。（ambiguous 模棱两可的）

查询员工姓名、部门名称、部门id（两个表相同的列）：

sql

SELECT e.last_name , d.department_name ,e.department_id
FROM employees e , departments d
WHERE e.department_id = d.department_id

需要注意的是：如果我们使用了表的别名，在查询字段中、过滤条件中
就只能使用别名进行代替，不能使用原有的表名，否则会报错。

连接 n 个表需要 n-1 个连接条件

需求：查询出公司员工的 last_name , department_name , city

sql

SELECT last_name,department_name,city 
FROM employees e, departments d ,locations l 
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id

非等值连接

如上需求：查询员工姓名，薪资，薪资所在的等级

sql

SELECT e.last_name , e.salary , j.grade_level
FROM employees e , job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal

6.2.2 自连接 v.s. 非自连接

table1 和 table2 本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。
然后两个表再进行内连接，外连接等查询。

需求：查询 employees 表，返回 "XXX(worker) works for XXX(manager)"

sql

SELECT
	concat( worker.last_name, " works for ", manager.last_name ) AS msg 
FROM
	employees worker,
	employees manager 
WHERE
	worker.manager_id = manager.employee_id;

6.2.3 内连接 v.s. 外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件
的行，这种连接称为左（或右）外连接。没有匹配的行时，结果集中相应的列为空（NULL）。
如果是左外连接，则连接条件中的左边的表也称为 主表 ，右边的表称为 从表 。
如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

SQL 92 ：使用（+）创建连接

在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，（+）表示哪个从表。
(不需要学！！！了解即可) Oracal 对 SQL92 支持较好，而 MySQL则不支持SQL92的外连接！！

sql

-- 左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
-- 右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

6.3 SQL99 实现多表查询

可以见到，我另开了一个专题讲解，因为我们前面讲的都是 SQL 92 语法

6.3.1 基本语法

sql

SELECT table1.column, table2.column,table3.column
FROM table1
    JOIN table2 ON (table1 和 table2 的连接条件)
    JOIN table3 ON (table2 和 table3 的连接条件)

它的嵌套逻辑类似我们使用 FOR 循环：

python

for t1 in table1:
	for t2 in table2:
		if condition1:
			for t3 in table3:
				if condition2:
					output t1 + t2 + t3

SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，

即使再多的表进行连接也都清晰

可见。如果你采用 SQL92，可读性就会大打折扣。

语法说明：
- 可以使用 ON 子句指定额外的连接条件。
- 这个连接条件是与其它筛选 / 过滤条件分开的。
- ON 子句使得语句具有更高的可读性。
- 关键字 JOIN 、 INNER JOIN 、CROSS JOIN 的含义是一样的，都表示内连接。

6.3.2 内连接 (INNER JOIN)

语法

sql

SELECT 字段列表
FROM A表 (INNER) JOIN B表
ON 关联条件
WHERE 等其他子句;

需求：查询 employee_id , last_name, department_id , department_id, location_id

sql

SELECT
	e.employee_id,
	e.last_name,
	d.department_id,
	d.department_id,
	d.location_id 
FROM
	employees e JOIN departments d
	ON e.department_id = d.department_id

需求：查询 employee_id , city , department_name

sql

SELECT
	e.employee_id,
	l.city,
	d.department_name 
FROM
	employees e
	JOIN departments d ON e.department_id = d.department_id
	JOIN locations l ON d.location_id = l.location_id

6.3.3 外连接 (OUTER JOIN)

6.3.3.1 左外连接

语法

sql

SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

举例：查询出 last_name, department_id , department_name 并且需要包含上没有对应部门的员工

sql

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

6.3.3.2 右外连接

语法

sql

SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

举例：查询出 last_name, department_id , department_name 并且需要包含上没有员工的部门

sql

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

需要注意的是， LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，
在 SQL92 中不存在，只能用 (+) 表示。

6.3.3.3 满外连接 (FULL OUTER JOIN)

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99 是支持满外连接的。使用 FULL JOIN 或 FULL OUTER JOIN 来实现。
需要注意的是，MySQL 不支持 FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN 代替。

6.4 Union 的使用

合并查询结果 利用 UNION 关键字，可以给出多条 SELECT 语句，并将它们的结果组合成单个结果集。
合并时，两个表对应的列数和数据类型必须相同，并且相互对应。
各个 SELECT 语句之间使用 UNION 或 UNIONALL 关键字分隔。

语法格式：

sql

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION 操作符（示例图）:

UNION 操作符返回两个查询的结果集的并集，去除重复记录 。

UNION ALL 操作符（示例图）：

UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重 。

注意：执行 UNION ALL 语句时所需要的资源比 UNION 语句少。如果明确知道合并数据后的结果数据
不存在重复数据，或者不需要去除重复的数据，
则 尽量使用 UNION ALL 语句，以提高数据查询的效率 。

6.5 【重点】7种 SQL JOINS 的实现

关于案例查询 employee_id , last_name , department_name 来实现上述7个图。
经过前面对员工表和部门表的了解，我们知道他们的关系大概是下面这个图：

我们就根据这个图来设计 7 种 SQL JOINS 的查询，看看能不能出现对应数据和对应数据正确条数

中图：内连接 A ∩ B

sql

-- 中图：内连接 A ∩ B
SELECT
	employee_id,
	last_name,
	department_name 
FROM
	employees e
	JOIN departments d ON e.department_id = d.department_id

左上图：左外连接

sql

-- 左上图：左外连接
SELECT
	employee_id,
	last_name,
	department_name 
FROM
	employees e
	LEFT OUTER JOIN departments d ON e.department_id = d.department_id

右上图：右外连接

sql

-- 右上图：右外连接
SELECT
	employee_id,
	last_name,
	department_name 
FROM
	employees e
	RIGHT OUTER JOIN departments d ON e.department_id = d.department_id

左中图：A - A ∩ B

sql

-- 左中图：A - A ∩ B
SELECT
	employee_id,
	last_name,
	department_name 
FROM
	employees e
	LEFT OUTER JOIN departments d ON e.department_id = d.department_id 
WHERE
	d.department_id IS NULL

右中图：B - A ∩ B

sql

-- 右中图：B - A ∩ B
SELECT
	employee_id,
	last_name,
	department_name 
FROM
	employees e
	right OUTER JOIN departments d ON e.department_id = d.department_id 
WHERE
	e.department_id IS NULL

左下图 : 满外连接

sql

-- 左下图: 满外连接
-- 左中图 + 右上图  /  左上图 + 右中图
SELECT
	employee_id,
	last_name,
	department_name 
FROM
	employees e
	LEFT OUTER JOIN departments d ON e.department_id = d.department_id 
WHERE
	d.department_id IS NULL
UNION ALL
SELECT
	employee_id,
	last_name,
	department_name 
FROM
	employees e
	RIGHT OUTER JOIN departments d ON e.department_id = d.department_id

右下图

sql

-- 右下图
-- 左中图 + 右中图
SELECT
	employee_id,
	last_name,
	department_name 
FROM
	employees e
	LEFT OUTER JOIN departments d ON e.department_id = d.department_id 
WHERE
	d.department_id IS NULL
UNION ALL
SELECT
	employee_id,
	last_name,
	department_name 
FROM
	employees e
	right OUTER JOIN departments d ON e.department_id = d.department_id 
WHERE
	e.department_id IS NULL

6.6 SQL99 新特性

6.6.1 自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。
我们可以把自然连接理解为 SQL92 中的等值连接。
它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行等值连接。

在 SQL92 标准中：

sql

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成：

sql

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

建议不要使用：因为有时候我们其实不希望有相同字段全去连接，
连接多了，那么两个表根据这个连接条件所产生的交集就少了，这就会丢失原本需要的字段且不容易发现...

6.6.2 USING 连接

当我们进行连接的时候，SQL99 还支持使用 USING 指定数据表里的 同名字段 进行等值连接。
但是只能配合 JOIN 一起使用。比如：

sql

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING
的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。
它与下面的 SQL 查询结果是相同的：

sql

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

还是不建议使用，就像前面的一个自连接的需求，使用到的是同样的一个表
一个字段是员工id，一个字段是管理者的id，就不叫 “ 同名字段 ” 了。

【pre】使用的数据 ​

一、引言 ​

1.1 SQL 分类 ​

1.2 SQL 语言规则＆规范 ​

1.3 注释 ​

二、基本的 select 语句 ​

2.1.1 基本 select ​

2.1.2 列的别名 ​

2.1.3 去除重复行 ​

2.1.4 空值参与运算 ​

2.1.5 着重号 ​

2.1.6 查询常数 ​

2.3 显示表结构 ​

2.4 过滤数据 ​

三、运算符 ​

3.1 算数运算符 ​

3.2 比较运算符 ​

3.3 逻辑运算符 ​

四、排序数据 ​

4.1 排序规则 ​

4.2 单列排序 ​

4.3 多列排序 ​

五、分页 ​

5.1 分页规则 ​

5.2 分页举例 ​

六、多表查询 ​

6.1 一个案例引发的多表连接 ​

6.1.1 案例说明 ​

6.1.2 笛卡尔积（交叉连接）的理解 ​

6.1.3 问题解决 ​

6.2 多表查询分类讲解 ​

6.2.1 等值连接 v.s. 非等值连接 ​

6.2.2 自连接 v.s. 非自连接 ​

6.2.3 内连接 v.s. 外连接 ​

SQL 92 ：使用（+）创建连接 ​

6.3 SQL99 实现多表查询 ​

6.3.1 基本语法 ​

6.3.2 内连接 (INNER JOIN) ​

6.3.3 外连接 (OUTER JOIN) ​

6.3.3.1 左外连接 ​

6.3.3.2 右外连接 ​

6.3.3.3 满外连接 (FULL OUTER JOIN) ​

6.4 Union 的使用 ​

6.5 【重点】7种 SQL JOINS 的实现 ​

6.6 SQL99 新特性 ​

6.6.1 自然连接 ​

6.6.2 USING 连接 ​