实验代码
1.myemployees.sql文件
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.15 : Database - myemployees
*********************************************************************
*/
/*!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*/`myemployees` /*!40100 DEFAULT CHARACTER SET gb2312 */;
USE `myemployees`;
/*Table structure for table `departments` */
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`department_id` int(4) NOT NULL AUTO_INCREMENT,
`department_name` varchar(3) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`location_id` int(4) DEFAULT NULL,
PRIMARY KEY (`department_id`),
KEY `loc_id_fk` (`location_id`),
CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;
/*Data for the table `departments` */
insert into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);
/*Table structure for table `employees` */
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`employee_id` int(6) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) DEFAULT NULL,
`last_name` varchar(25) DEFAULT NULL,
`email` varchar(25) DEFAULT NULL,
`phone_number` varchar(20) DEFAULT NULL,
`job_id` varchar(10) DEFAULT NULL,
`salary` double(10,2) DEFAULT NULL,
`commission_pct` double(4,2) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`department_id` int(4) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL,
PRIMARY KEY (`employee_id`),
KEY `dept_id_fk` (`department_id`),
KEY `job_id_fk` (`job_id`),
CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;
/*Data for the table `employees` */
insert into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');
/*Table structure for table `jobs` */
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
`job_id` varchar(10) NOT NULL,
`job_title` varchar(35) DEFAULT NULL,
`min_salary` int(6) DEFAULT NULL,
`max_salary` int(6) DEFAULT NULL,
PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
/*Data for the table `jobs` */
insert into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);
/*Table structure for table `locations` */
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`location_id` int(11) NOT NULL AUTO_INCREMENT,
`street_address` varchar(40) DEFAULT NULL,
`postal_code` varchar(12) DEFAULT NULL,
`city` varchar(30) DEFAULT NULL,
`state_province` varchar(25) DEFAULT NULL,
`country_id` varchar(2) DEFAULT NULL,
PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;
/*Data for the table `locations` */
insert into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;2.girl.sql文件
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.18-log : Database - girls
*********************************************************************
*/
/*!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*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `girls`;
/*Table structure for table `admin` */
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `admin` */
insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');
/*Table structure for table `beauty` */
DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`sex` char(1) DEFAULT '女',
`borndate` datetime DEFAULT '1987-01-01 00:00:00',
`phone` varchar(11) NOT NULL,
`photo` blob,
`boyfriend_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/*Data for the table `beauty` */
insert into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);
/*Table structure for table `boys` */
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `boys` */
insert into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);
/*!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 */;关于数据库如何导入现有的文件请看这篇文章：传送门
ERD
基础查询
语法
select 查询列表 from 表名特点
1.查询列表可以是：表中的字段，常量值，表达式，函数
2.查询的结果是一个虚拟表格1.查询表中的单个字段
select last_name from employees;2.查询表中的多个字段
select last_name,salary,email from employees;3.查询表中的所有字段
select employee_id,first_name,last_name,email,phone_number,job_id,salary,commission_pct,manager_id,department_id,hiredate
from employees;也可以：
select * from employees;4.查询常量值
select 100;
select 'john';5.查询表达式
select 100*98;
select 100%98;6.查询函数
select version();7.起别名
/*
①便于理解
②如果查询字段有重名的情况，使用别名可以区分开来
*/
#方式一：
select 100%98 AS 结果;
select last_name As 姓,first_name AS 名 from employees;
#方式二：
select last_name 姓,first_name 名 from employees; 8.去重
案例：查询员工列表中涉及的所有部门的编号
select distinct department_id from employees;9.+号的作用
只有一个功能：运算符
select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select 'john'+90; 如果转换失败，则将字符型数值转换成0
select null+10; 只要其中一方为null，则结果肯定为null案例：查询员工的姓和名连接成一个字段，并显示为姓名
select concat('a','b','c') AS 结果;
select concat(last_name,first_name) AS 姓名 from employees;注意
#在查询前最好先打开指定的库
USE 库名；条件查询
语法
select
查询列表
from
表名
where
筛选条件1.条件表达式
条件运算符：
> < >= <= = != <>案例1:查询部门编号不为90的员工姓，部门编号
select last_name,department_id
from employees
where
department_id<>90;2.逻辑表达式
逻辑运算符：
and（&&）:两个条件如果同时成立，结果为true，否则为false
or(||)：两个条件只要有一个成立，结果为true，否则为false
not(!)：如果条件成立，则not后为false，否则为true案例1:查询工资大于10000且小于20000的员工姓，工资，奖金
select last_name,salary,commission_pct
from employees
where
salary>=10000
and
salary<=20000;案例2:查询部门编号小于90或大于110或工资大于15000的员工信息
select *
from employees
where
department_id<90
or
department_id>110
or
salary>150003.模糊查询
①like 关键字
特点
一般和通配符搭配使用
通配符：
% 代表任意多个字符
_ 任意单个字符案例1：查询员工姓中含字符a的员工信息
select * from employees
where last_name Like '%a%';案例2：查询员工名中第三个字符为 n ,第五个字符为 l 的员工姓和工资
select last_name,salary
from employees
where last_name like '__n_l%';案例3：查询员工名中第二个字符为_的员工姓
select last_name
from employees
where last_name like '_\_%';
# 或
select last_name
from employees
where last_name like '_$_%' escape '$';②between and 关键字
特点
包含临界值
临界值不要颠倒案例4：查询员工编号再100到120之间的员工信息
select *
from employees
where employee_id between 100 and 120;③in 关键字
特点
in 列表种类必须一致或兼容
不可使用通配符案例5：查询员工工种编号是 IT_PROG , AD_VP , AD_PRES 中一个的员工名和工种编号
select last_name,job_id
from employees
where job_id in ('IT_PROG','AD_VP','AD_PRES');④is NULL 关键字
特点
=，<>不能判断NULL值
is只能判断NULL案例6：查询没有奖金的员工名和奖金率
select last_name,commission_pct
from employees
where commission_pct is NULL案例6：查询有奖金的员工名和奖金率
select last_name,commission_pct
from employees
where commission_pct is not NULL;⑤<=>安全等于
案例1：查询没有奖金的员工名和奖金率
select last_name,commission_pct
from employees
where commission_pct <=> NULL;案例2：查询工资为12000的员工信息
select *
from employees
where salary <=> 12000;比较
is NUll: 仅仅可以判断NULL值，可读性较高，建议使用
<=>: 既可以判断NULL值，又可以判断普通的数据，可读性较低排序查询
语法
select 查询列表
from 表
where 筛选条件
order by 排序列表【asc/desc】特点
asc代表升序，desc代表降序
如果不写默认是升序
order by可以支持单个字段，多个字段，表达式，函数，别名
order by一般放在查询语句最后，limit子句除外1.降序
案例1：查询员工信息，要求工资从高到低
select *
from employees
order by salary desc;2.升序
案例1：查询员工信息，要求工资从低到高
select *
from employees
order by salary asc;案例2：查询部门编号大于等于90的员工信息，按入职时间的先后进行排序
select *
from employees
where department_id>=90
order by hiredate asc;3.按别名排序
案例3：按年薪的高低显示员工的信息和年薪
select *,salary*12*(1+ifnull(commission_pct,0)) as 年薪
from employees
order by salary*12*(1+ifnull(commission_pct,0)) desc;
#或
select *,salary*12*(1+ifnull(commission_pct,0)) as 年薪
from employees
order by 年薪 desc;4.按函数排序
案例4：按姓名的长度显示员工的姓名和工资
select length(last_name) as 字节长度,last_name,salary
from employees
order by length(last_name) desc;5.多重排序
案例5：查询员工信息，要求先按工资排序，再按员工编号排序
select *
from employees
order by salary asc,employee_id desc；常见函数
1.单行函数
①字符函数
length函数获取参数值的字节个数
select length('john'); # 返回4
select length('刘天翼')； # 返回9concat函数拼接字符串
select concat(last_name,'_',first_name) from employees;uppper,lower函数改变大小写
select upper('john'); # 返回JOHN
select lower('JOHN'); # 返回john
# 案例：将姓变大写，将名变小写然后拼接
select concat(upper(last_name),lower(first_name)) as 姓名substr函数截取字符
注意：索引从1开始
# 截取从指定索引处后面的所有字符
select substr('李莫愁爱上了陆展元',7) as output; # 陆展元
# 截取从指定索引处指定字符长度的字符
select substr('李莫愁爱上了陆展元',1,3) as output; # 李莫愁instr函数返回第一次出现的索引，如果找不到则返回0
select instr('杨不悔爱上了殷六侠','殷六侠') as output; # 返回7trim函数去除前后空格或特定字符
select trim(' 22 333 ') as output; # 返回22 333
select trim('a' from'aaa22aa333aaa') as output; # 返回22aa333lpad函数用指定字符左填充指定长度
select lpad('殷素素',10,'*') as output; # 返回 *******殷素素
select lpad('殷素素',2,'*') as output; # 返回 殷素rpad函数用指定字符右填充指定长度
select rpad('殷素素',10,'ab') as output; # 返回 殷素素abababa
select rpad('殷素素',2,'*') as output; # 返回 殷素replace函数替换
select replace('aaabbbaaa','a','c') as output; # 返回cccbbbccc②数学函数
round 四舍五入
select round(1.65); # 返回 2
select round(-1.45); # 返回 -1
select round(1.567,2)； # 小数点后精确两位，返回 1.57floor向下取整
注意：返回 <= 该参数的最大整数
select floor(1.52); # 返回 1
select floor(1.00); # 返回 1
select floor(-1.02); # 返回 -2ceil向上取整
注意：返回 >= 该参数的最小整数
select ceil(1.52); # 返回 2
select ceil(1.00); # 返回 1
select ceil(-1.02); # 返回 -1mod取余
注意：mod(a,b)的底层算法是 a - a / b*b
select mod(10,3); # 返回 1
select mod(10,-3); # 返回 1
select mod(-10,-3); # 返回 -1truncate截断
select truncate(1.699999,1)； # 返回 1.6③日期函数
now当前系统日期+时间
select now(); # 返回 2020-09-23 10:30:58current_date当前系统日期
select current_date(); # 返回 2020-09-23current_time当前系统时间
select current_time(); # 返回 10:30:58str_to_date 将字符转换成日期
| 序号 | 格式符 | 功能 |
|---|---|---|
| 1 | %Y | 四位的年份 |
| 2 | %h | 二位的年份 |
| 3 | %m | 月份(01,02…11,12） |
| 4 | %c | 月份(1,2…11,12) |
| 5 | %d | 日(01,02….) |
| 6 | %H | 24小时制 |
| 7 | %h | 12小时制 |
| 8 | %i | 分钟(00,01…59) |
| 9 | %s | 秒(00,01,59) |
# 查询入职时间为1992-4-3的员工信息
select *
from employees
where hiredate = str_to_date('4-3 1992','%c-%d %Y');date_format将日期转换成字符
select date_format(now(),'%y年%m月%d日'); # 返回：20年09月23日其他获取时间的函数
select year('2001-6-10'); # 返回：2001
select month('2001-6-10'); # 返回：6
select monthname('2001-6-10'); # 返回：June
select day('2001-6-10'); # 返回：10
select hour('2020-09-23 10:30:58'); #返回：10
select minute('2020-09-23 10:30:58'); #返回：30
select second('2020-09-23 10:30:58'); #返回：58④其他函数
select version(); # 查询版本号
select database(); #查询当前数据库
select user(); #查询当前用户⑤流程控制函数
if 处理双分支
select if(10>5,'大','小'); # 返回 大
select if(10<5,'大','小'); # 返回 小case语句 处理多分支
等值判断
/* 查询员工的工资，要求：
部门号=30，显示工资为1.1倍
部门号=40，显示工资为1.2倍
部门号=50，显示工资为1.3倍
其他部分，显示工资为原工资*/
select salary 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;区间判断
/*
查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则，显示D级别
*/
select salary,
case
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 等级
from employees;2.分组函数(统计函数，聚合函数，组函数)
特点
1、sum,avg,max,min,count忽略null值
count(*)不忽略null值
2、sum和avg一般用于处理数值型，
max、min、count可以处理任何数据类型
3、都可以搭配distinct使用，用于统计去重后的结果
4、count的参数可以支持：字段、*、常量值，一般放1sum求和
select sum(salary) from employees; # 查询所有工资之和
select sum(distinct salary) from employees; # 查询所有不重复的工资之和avg平均值
select avg(salary) from employees; # 查询所有工资平均值max最大值
select max(salary) from employees; # 查询工资最大值min最小值
select min(salary) from employees; # 查询工资最小值count计算个数
select count(salary) from employees; # 一共有多少个非空的值
select count(distinct salary) from employees; # 一共有多少个不重复非空的值
select count(*) from employees; # 计算总行数
# 也可以
select count(1) from employees; # 计算总行数分组查询
语法
select 分组函数，列(要求出现在group by后面)
from 表名
where 筛选条件（在原始表中存在）
group by 分组列表
order by 子句1.添加分组前筛选
案例1：每个工种的最高工资
select max(salary),job_id
from employees
group by job_id;案例2：每个位置上的部门个数
select count(*),location_id
from departments
group by location_id;案例3：邮箱中包含a字符的，每个部门的平均工资
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;案例4：有奖金的每个领导手下员工的最高工资
select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id;2.添加分组后筛选
案例1：哪个部门的员工个数大于2
# 查询每个部门的员工个数之后根据结果进行筛选即为所求
select count(*),department_id
from employees
group by department_id
having count(*)>2;案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;案例3：查询领导编号>102的每个领导手下的员工的最低工资>5000的领导编号是哪个，以及其最低工资
# 查询领导编号>102的每个领导手下的员工的最低工资
# 添加筛选条件：最低工资大于5000
select min(salary),manager_id
from employees
where manager_id>102
group by manager_id
having min(salary)>5000;特点
| 针对的表 | 位置 | 关键字 | |
|---|---|---|---|
| 分组前筛选 | 原始表 | group by的前面 | where |
| 分组后筛选 | 分组后的结果集 | group by的后面 | having |
分组函数做条件一定放在having子句中
能用分组前筛选的，就优先考虑使用分组前筛选①按表达式或函数分组
案例：按员工姓名的长度分组，查询每一组员工的个数，筛选员工个数>5的有哪些
select count(*),length(last_name) as len_name
from employees
group by len_name
having count(*) > 5;②按多个字段分组
案例：查询每个部门每个工种的员工的平均工资
select avg(salary),department_id,job_id
from employees
group by department_id,job_id;③分组查询添加排序
案例：查询每个部门每个工种的员工的平均工资并由高到低排序
select avg(salary),department_id,job_id
from employees
group by department_id,job_id
order by avg(salary) desc;连接查询
含义
当查询中涉及到了多个表的字段，需要使用多表连接
select 字段1，字段2
from 表1，表2;笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
如何解决：添加有效的连接条件
分类
按年代分类
sq192标准：仅仅支持内连接
sq199标准【推荐】按功能分类
内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全外连接
交叉连接1.sql92语法
①内连接
等值连接
语法：
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】案例1：查询女神和对应的男神名
select boyname,`name`
from boys,beauty
where beauty.boyfriend_id=boys.id；案例2：查询员工名和对应的部门名
select last_name,department_name
from employees,departments
where employees.department_id=departments.department_id;案例3：查询由奖金的员工名，部门名
select last_name,department_name
from employees,departments
where departments.department_id=employees.department_id
and employees.commission_pct is not null;案例4：查询城市名中第二个字符为O的部门名和城市名
select department_name,city
from departments d,locations l
where d.location_id=l.location_id
and city like "_o%";案例5：查询每个城市的部门个数
select count(*),city
from locations,departments
where departments.location_id=locations.location_id
group by city;案例6：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select department_name,departments.manager_id,min(salary)
from departments,employees
where employees.department_id=departments.department_id
and commission_pct is not null
group by department_name;案例7：查询每个工种工种名和员工的个数，并按员工个数降序
select job_title,count(*) 个数
from jobs,employees
where employees.job_id=jobs.job_id
group by job_title
order by 个数 desc;案例8：查询员工名，部门名和所在的城市
select department_name,last_name,city
from employees,departments,locations
where employees.department_id=departments.department_id
and locations.location_id=departments.location_id;特点
1. 一般为表起别名
2.多表的顺序可以调换
3.n表连接至少需要n-1个连接条件
4.等值连接的结果是多表的交集部分非等值连接
语法：
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】案例1：查询员工的工资和工资级别
需要用到
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);select salary,grade_level
from employees,job_grades
where salary between job_grades.lowest_sal and job_grades.highest_sal;自连接
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】案例：查询员工名和他上级的名称
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.manager_id = m.employee_id;2.sql99语法
①内连接
等值连接
语法：
select 查询列表
from 表1 别名
【inner】 join 表2 别名
on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;案例1：查询员工名，部门名
select last_name,department_name
from employees e
inner join departments d
on e.department_id=d.department_id;案例2：查询名字中包含e的员工名和工种名
select last_name,job_title
from employees e
inner join jobs j
on e.job_id=j.job_id
where last_name like "%e%";案例3：查询部门个数>3的城市名和部门个数
select count(*),city
from locations l
inner join departments d
on d.location_id = l.location_id
group by city
having count(*)>3；案例4：查询部门的员工个数>3的部门名和员工个数，并按个数降序
select department_name,count(*)
from departments d
inner join employees e
on e.department_id=d.department_id
group by department_name
having count(*)>3
order by count(*) desc;案例5：查询员工名，部门名，工种名，并按部门名降序
select last_name,department_name,job_title
from employees e
inner join departments d on e.department_id=d.department_id
inner join jobs j on j.job_id=e.job_id
order by department_name desc;非等值连接
案例1：查询员工的工资和工资级别
select salary,grade_level
from employees e
inner join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;案例2：查询每个工资级别的个数，并且排序
select count(*),grade_level
from employees e
inner join job_grades j
on e.salary between j.lowest_sal and j.highest_sal
group by grade_level
having count(*)>20
order by grade_level desc；自连接
案例1：查询员工名含k的员工名和他上级的名称
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e
inner join employees m
on e.manager_id = m.employee_id
where e.last_name like "%k%";②外连接
应用场景：一个表中有，一个表中没有的情景
语法：
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;特点
1.查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
2.left join 左边的就是主表，right join 右边的就是主表，full join 两边都是主表
3.一般用于查询除了交集部分的剩余的不匹配的行左外链接
案例：查询哪个部门没有员工
select d.*,e.employee_id
from departments d
left outer join employees e
on d.department_id=e.department_id
where e.employee_id is null;右外连接
案例：查询哪个部门没有员工
select d.*,e.employee_id
from employees e
right outer join departments d
on d.department_id=e.department_id
where e.employee_id is null;③交叉连接
语法
select 查询列表
from 表1 别名
cross join 表2 别名;特点
类似于笛卡尔乘积分页查询
应用场景
当要查询的条目数太多，一页显示不全语法
select 查询列表
from 表1
【join type】 join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后筛选
order by 排序字段
limit offset，size;注意
offset代表的是起始的条目索引，默认从0卡死
size代表的是显示的条目数案例1：查询前5条员工信息
select * from employees limit 0,5;
或者
select * from employees limit 5;案例2：查询第11条到第25条员工信息
select * from employees limit 10,15;案例3：查询有奖金的员工信息，并且工资较高的前10名显示出来
select * from employees
where commission_pct is not null
order by salary desc
limit 10;特点
1.limit 语句放在查询语句的最后
2.公式：
假如要显示的页数为page，每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;子查询
含义
1.嵌套在其他语句内部的select语句称为子查询或内查询
2.外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
3.外面如果为select语句，则此语句称为外查询或主查询分类
按位置分类：
1.select后面：
仅仅支持标量子查询
2.from后面：
表子查询
3.where或having后面：
标量子查询
列子查询
行子查询
4.exists后面：
标量子查询
列子查询
行子查询
表子查询按结果集的行列分类：
1.标量子查询（单行子查询）：结果集为一行一列
2.列子查询（多行子查询）：结果集为多行一列
3.行子查询：结果集为一行多列
4.表子查询：结果集为多行多列1.where或having后面的标量子查询
案例1：谁的工资比Abel高
select *
from employees
where salary>(
select salary
from employees
where last_name="Abel"
);案例2：返回 job_id 与141号员工相同，salary比143员工多的员工姓名，job_id 和工资
select last_name,job_id,salary
from employees
where job_id = (
select job_id
from employees
where employee_id = 141
) and salary > (
select salary
from employees
where employee_id = 143
);案例3：返回公司工资最少的员工的 last_name, job_id和salary
select last_name,job_id,salary
from employees
where salary = (
select min(salary)
from employees
);案例4：查询最低工资大于50号部门最低工资的部门 id和其最低工资
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(
select min(salary)
from employees
where department_id = 50
);2.where或having后面的列子查询
多行比较操作符
| 操作符 | 含义 |
|---|---|
| IN / NOT IN | 等于列表中的任意一个 |
| ANY / SOME | 和子查询返回的某一个值比较 |
| ALL | 和子查询返回的所有值比较 |
注意
Any,All的可读性不高一般用其他表示方法
eg. a > any(10,20,30)
就等价于 a > min(10,20,30)
eg. a > all(10,20,30)
就等价于 a > max(10,20,30)案例1：返回 location_id是1400或1700的部门中所有员工姓名
select last_name
from employees e
inner join departments d
on e.department_id = d.department_id
where d.location_id in (1400,1700);
#或者
select last_name
from employees
where department_id in(
select department_id
from departments
where location_id in (1400,1700)
);案例2：返回其他工种中比 job_id为‘IT_PROG’部门任一工资低的员工的工号，姓名，job_id, salary
select employee_id,last_name,job_id,salary
from employees
where salary < any(
select Distinct salary
from employees
where job_id = 'IT_PROG'
) and job_id<>'IT_PROG';
#或者
select employee_id,last_name,job_id,salary
from employees
where salary < (
select max(Distinct salary)
from employees
where job_id = 'IT_PROG'
) and job_id<>'IT_PROG';案例3：返回其他工种中比 job_id为‘IT_PROG’部门所有工资都低的员工的工号，姓名，job_id, salary
select employee_id,last_name,job_id,salary
from employees
where salary < all(
select distinct salary
from employees
where job_id = 'IT_PROG'
) and job_id<>'IT_PROG';
#或者
select employee_id,last_name,job_id,salary
from employees
where salary < (
select min(distinct salary)
from employees
where job_id = 'IT_PROG'
) and job_id<>'IT_PROG';3.where或having后面的行子查询
案例：查询员工编号最小并且工资最高的员工信息
select *
from employees
where employee_id = (
select min(employee_id)
from employees
) and salary = (
select max(salary)
from employees
);
#或者
select *
from employees
where (employee_id,salary)=(
select min(employee_id),max(salary)
from employees
);4.select后面的子查询
案例：查询每个部门的员工个数
select d.*,(
select count(*) from employees
where d.department_id = employees.department_id
) as 个数
from departments d;
#或者
select d.*,count(*) as 个数
from departments d
inner join employees e
on d.department_id=e.department_id
group by department_id;案例：查询员工号=102的部门名
select department_name
from departments d
inner join employees e
on d.department_id = e.department_id
where e.employee_id = 102;
#或者
select (
select department_name
from departments d
inner join employees e
on d.department_id = e.department_id
where e.employee_id = 102
) as 部门名;5.from后面的子查询
案例：查询每个部门的平均工资的工资等级
select ag_dep.*,g.grade_level
from (
select department_id,avg(salary) as 平均工资
from employees
group by department_id
) as ag_dep
inner join job_grades as g
on ag_dep.平均工资 between lowest_sal and highest_sal；6.exists后面的子查询
语法
select exists(
完整查询语句
)；
#结果：0或1案例1：查询有员工的部门名
select department_name
from departments
where exists(
select *
from employees
where departments.department_id=employees.department_id
)；
# 或者
select distinct d.department_name
from departments d
left outer join employees e
on d.department_id = e.department_id
where e.employee_id is not null;
# 或者
select department_name
from departments d
where d.department_id in (
select distinct department_id
from employees
)；union联合查询
案例1：查询部门编号>90或邮箱中包含a的员工信息
select * from employees
where email like "%a%"
or department_id >90;
#或者
select * from employees where email like "%a%"
union
select * from employees where department_id >90;注意
1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致
3、union 去重，union all包含重复项如果有其他问题欢迎留言或邮件提问
QQ：1269112498
Email：1269112498@qq.com
相关文章
