Mysql3_数据操作

最新推荐文章于 2023-01-20 21:56:01 发布

ginger_mr

最新推荐文章于 2023-01-20 21:56:01 发布

阅读量363

点赞数

分类专栏： Mysql_new

本文链接：https://blog.csdn.net/qq_40332952/article/details/113885678

版权

Mysql_new 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

数据操作

数据操作

1. 查询

准备数据库

myemployees数据库
/*
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 */;

d/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.18-log : Database - girls
*********************************************************************
*/

# 创建一张表job_grades
DROP TABLE IF EXISTS job_grades;
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);

# 创建一张表sal_grade
USE  myemployees;
DROP TABLE IF EXISTS sal_grade;
CREATE TABLE sal_grade (
	id INT PRIMARY KEY AUTO_INCREMENT,
	min_salary DOUBLE ,
	max_salary DOUBLE,
	grade CHAR

);

INSERT INTO sal_grade VALUES(NULL,2000,3999,'A');
INSERT INTO sal_grade VALUES(NULL,4000,5999,'B');
INSERT INTO sal_grade VALUES(NULL,6000,9999,'C');
INSERT INTO sal_grade VALUES(NULL,10000,12999,'D');
INSERT INTO sal_grade VALUES(NULL,13000,14999,'E');
INSERT INTO sal_grade VALUES(NULL,15000,99999,'F');


girl数据库
/*!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 */;

1.基础查询

语法：
	select 查询列表 
	from 表名;

特点：
	1、查询的结果集 是一个虚拟表。
	2、select 查询列表  类似于System.out.println(打印内容);
		select后面跟的查询列表，可以有多个部分组成，中间用逗号隔开。
		例如：select 字段1,字段2,表达式 from 表;
		System.out.println()的打印内容，只能有一个。
	3、执行顺序
		1、from子句
		2、select子句
	4、查询列表可以是：字段、表达式、常量、函数等。
USE myemployees;

1.SELECT

1.查询常量

# 一、查询常量
SELECT 100;

2.查询表达式

# 二、查询表达式
SELECT 100%3;

3.查询单个字段

# 三、查询单个字段
# ``:表示的不是字符，其实是着重号，区分是不是关键字，如果有个表的字段刚好和Mysql中的关键字重名,可以使用着重号包括起 
# 来就表示这是一个字段而不是一个关键字。
SELECT `last_name` FROM `employees`;

4.查询多个字段

# 四、查询多个字段
SELECT `last_name`,`email`,`employee_id` FROM employees;

5.查询所有字段

# 五、查询所有字段
SELECT * FROM `employees`;
# F12:格式化sql
SELECT 
    `last_name`,
    `first_name`,
    `last_name`,
    `commission_pct`,
    `hiredate`,
    `salary` 
FROM
    employees;

6.查询函数

# 六、查询函数（调用函数，获取返回值）
SELECT DATABASE();
SELECT VERSION();
SELECT USER();

7.起别名

# 七、起别名
# 在Mysql中双引号和单引号没什么区别,都是表示字符。
# 方式一：使用as关键字
SELECT USER() AS 用户名;
SELECT USER() AS "用户名";
SELECT USER() AS '用户名';
SELECT last_name AS "姓 名" FROM employees;
# 方式二：使用空格
SELECT USER()   用户名;
SELECT USER()   "用户名";
SELECT USER()   '用户名';
SELECT last_name   "姓 名" FROM employees;

8.+的作用

# 八、+的作用

java中的+号：
	1、运算符，两个操作数都为数值型。
	2、连接符，只要有一个操作数为字符串。

mysql中的+号：
	仅仅只有一个功能：运算符
	select 100+100;  两个操作数值都为数值型，则做加法运算。
	select '100'+100;  两个操作数值有一个是字符型，就会将字符型转为数值型（转换成功）在做加法运算。   
	select 'jack'+100;  两个操作值有一个是字符型，就会将字符型转为数值型（转换失败）就会将字符转换为0，在做加法运算。
	select  null+10;  只要其中一方为null，则结果肯定为null。    

# 需求：查询 first_name 和last_name 拼接成的全名，最终起别名为：姓 名
# 方案1：使用+  不会报错，+只能做运算符。
mysql> SELECT first_name+last_name AS "姓 名" FROM employees;
+-------+
| 姓 名 |
+-------+
|     0 |
|     0 |
|     0 |
|     0 |
|     0 |
|     0 |
|     0 |
|     0 |
|     0 |
|     0 |

# 方案2：使用concat拼接函数
mysql> SELECT CONCAT(first_name,last_name) AS "姓 名"
FROM employees;
+------------------+
| 姓 名            |
+------------------+
| StevenK_ing      |
| NeenaKochhar     |
| LexDe Haan       |
| AlexanderHunold  |
| BruceErnst       |
| DavidAustin      |
| ValliPataballa   |
| DianaLorentz     |
| NancyGreenberg   |
| DanielFaviet     |

9.distinct的使用

# 九、distinct的使用
# 需求：查询员工涉及到的部门编号有哪些
mysql> SELECT DISTINCT department_id FROM employees;
+---------------+
| department_id |
+---------------+
| NULL          |
|            10 |
|            20 |
|            30 |
|            40 |
|            50 |
|            60 |
|            70 |
|            80 |
|            90 |
|           100 |
|           110 |
+---------------+
12 rows in set (0.08 sec)

10.查看表的结构

# 十、查看表的结构（这两种方式结果一样）
mysql> SHOW COLUMNS FROM employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| first_name     | varchar(20)  | YES  |     | NULL    |                |
| last_name      | varchar(25)  | YES  |     | NULL    |                |
| email          | varchar(25)  | YES  |     | NULL    |                |
| phone_number   | varchar(20)  | YES  |     | NULL    |                |
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |
| salary         | double(10,2) | YES  |     | NULL    |                |
| commission_pct | double(4,2)  | YES  |     | NULL    |                |
| manager_id     | int(6)       | YES  |     | NULL    |                |
| department_id  | int(4)       | YES  | MUL | NULL    |                |
| hiredate       | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.03 sec)

mysql> DESC employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| first_name     | varchar(20)  | YES  |     | NULL    |                |
| last_name      | varchar(25)  | YES  |     | NULL    |                |
| email          | varchar(25)  | YES  |     | NULL    |                |
| phone_number   | varchar(20)  | YES  |     | NULL    |                |
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |
| salary         | double(10,2) | YES  |     | NULL    |                |
| commission_pct | double(4,2)  | YES  |     | NULL    |                |
| manager_id     | int(6)       | YES  |     | NULL    |                |
| department_id  | int(4)       | YES  | MUL | NULL    |                |
| hiredate       | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.03 sec)

11.SELECT案例

# 1.下面的语句是否可以执行成功。
SELECT last_name , job_id , salary AS sal FROM employees;


# 2.下面的语句是否可以执行成功。
SELECT * FROM employees;


# 3.找出下面语句中的错误（有中文的标点符号）。
select employee_id,last_name，salary * 12 “ANNUAL SALARY” from employees;
# 改正
select employee_id,last_name,salary * 12 "ANNUAL SALARY" from employees;


# 4.显示表 departments 的结构，并查询其中的全部数据。
DESC departments;
SHOW COLUMNS FROM departments;
SELECT * FROM departments;


# 5.显示出表 employees 中的全部 job_id（不能重复）。
SELECT DISTINCT job_id FROM employees;


# 6.显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT。
SELECT CONCAT(employee_id,',',first_name,',',last_name,',',salary,',',IFNULL(commission_pct,''))  AS "OUT_PUT" 
FROM employees;
# ifnull(表达式1，表达式2)

表达式1：可能为null的字段或表达式。
表达式2：如果表达式1为null，则显示表达式2。

功能：如果表达式1为null，则显示表达式2，否则显示表达式1。
SELECT commission_pct,IFNULL(commission_pct,'空') FROM employees;

2.WHERE

语法：
	select 查询列表
	from  表名
	where 筛选条件;

执行顺序：
	1、from子句
	2、where子句
	3、select子句
	select last_name,first_name from employees where salary>20000;

特点：
	1、按关系表达式筛选
		关系运算符：>   <   >=   <=   =   <>     
			补充：也可以使用!=,但不建议。
	2、按逻辑表达式筛选
		逻辑运算符：and    or   not
	    	补充：也可以使用 && || ! ，但不建议。
	3、模糊查询
		like
		in
		between and
		is null

1.按关系表达式筛选

# 一、按关系表达式筛选
# 案例1：查询部门编号不是100的员工信息。
SELECT *
FROM employees
WHERE department_id <> 100;

# 案例2：查询工资<15000的姓名、工资。
SELECT last_name,salary
FROM employees
WHERE salary<15000;

2.按逻辑表达式筛选

# 二、按逻辑表达式筛选
# 案例1：查询部门编号不是 50-100之间员工姓名、部门编号、邮箱。
# 方式1：
SELECT last_name,department_id,email
FROM employees
WHERE department_id <50 OR department_id>100;
# 方式2：
SELECT last_name,department_id,email
FROM employees
WHERE NOT(department_id>=50 AND department_id<=100);

# 案例2：查询奖金率>0.03 或者 员工编号在60-110之间的员工信息。
SELECT *
FROM employees
WHERE commission_pct>0.03 OR (employee_id >=60 AND employee_id<=110);

3.模糊查询

1.like

# 1、like
功能：一般和通配符搭配使用，对字符型数据进行部分匹配查询。
常见的通配符：
	_：任意单个字符
	%：任意多个字符,支持0-多个
	like/not like 
# 案例1：查询姓名中包含字符a的员工信息。
SELECT *
FROM employees
WHERE last_name LIKE '%a%';

# 案例2：查询姓名中包含最后一个字符为e的员工信息。
SELECT *
FROM employees
WHERE last_name LIKE '%e';

# 案例3：查询姓名中包含第一个字符为e的员工信息。
SELECT *
FROM employees
WHERE last_name LIKE 'e%';

# 案例4：查询姓名中包含第三个字符为x的员工信息。
SELECT *
FROM employees
WHERE last_name LIKE '__x%';

# 案例5：查询姓名中包含第二个字符为_的员工信息。
# 注意：有些字符刚好和mysql语法字符重合这时候需要转译，在mysql中有两种方法转译。
# 方式1
SELECT *
FROM employees
WHERE last_name LIKE '_\_%';
# 方式2
SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';

2.in

# 2、in

功能：查询某字段的值是否属于指定的列表之内
a  in(常量值1,常量值2,常量值3,...)
a not in(常量值1,常量值2,常量值3,...)
in/not in
特点：
	1、使用in提高语句简洁度
	2、in列表的值类型必须一致或兼容
	3、in列表中不支持通配符

# 案例1：查询部门编号是30/50/90的员工名、部门编号。
# 方式1：
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);
# 方式2：
SELECT last_name,department_id
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90;

# 案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息。
# 方式1：
SELECT *
FROM employees
WHERE job_id NOT IN('SH_CLERK','IT_PROG');
# 方式2：
SELECT *
FROM employees
WHERE NOT(job_id ='SH_CLERK'
OR job_id = 'IT_PROG');

3.between and

# 3、between and
功能：判断某个字段的值是否介于xx之间
between and/not between and
特点：
	1、使用between and 可以提高语句的简洁度
	2、包含临界值
	3、两个临界值不要调换顺序

# 案例1：查询部门编号是30-90之间的部门编号、员工姓名。
# 方式1：
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;
# 方式2：
SELECT department_id,last_name
FROM employees
WHERE department_id>=30 AND department_id<=90;

# 案例2：查询年薪不是100000-200000之间的员工姓名、工资、年薪。
# 方式1：
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0))<100000 OR salary*12*(1+IFNULL(commission_pct,0))>200000;
# 方式2：
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

4.is null

# 4、is null/is not null
# 案例1：查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL;

# 案例2：查询有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;
# is不能作为=来判断一个值。
SELECT *
FROM employees
WHERE salary IS 10000;
#----------------对比------------------------------------
=			只能判断普通的内容

IS          只能判断NULL值（可读性较高）

<=>         安全等于，既能判断普通内容，又能判断NULL值（可读性较差）
SELECT *
FROM employees
WHERE salary <=> 10000;

SELECT *
FROM employees
WHERE commission_pct <=> NULL;

4.WHERE案例

# 1.查询工资大于 12000 的员工姓名和工资。
SELECT last_name,salary
FROM employees
WHERE salary>12000;

# 2.查询员工号为 176 的员工的姓名和部门号和年薪。
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE employee_id  = 176;

# 3.选择工资不在 5000 到 12000 的员工的姓名和工资。
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;
# 方式2？
SELECT last_name,salary
FROM employees
WHERE NOT(salary>= 5000 AND salary<= 12000);

# 4. 选择在 20 或 50 号部门工作的员工姓名和部门号。
SELECT last_name,department_id
FROM employees
WHERE department_id IN (20,50);
# 方式2？
SELECT last_name,department_id
FROM employees
WHERE department_id = 20 OR department_id = 50;

# 5. 选择公司中没有管理者的员工姓名及 job_id。
SELECT last_name,job_id
FROM employees 
WHERE manager_id IS NULL;

# 6. 选择公司中有奖金的员工姓名，工资和奖金率。
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

# 7. 选择员工姓名的第三个字母是 a 的员工姓名。
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

# 8. 选择姓名中有字母 a 和 e 的员工姓名。
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%'  AND  last_name LIKE '%e%';
#方式2？

# 9.显示出表 employees 表中 first_name 以 'e'结尾的员工信息。
SELECT *
FROM employees
WHERE first_name LIKE '%e';

# 10.显示出表 employees 部门编号在 80-100 之间 的姓名、职位。
SELECT last_name,job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

# 11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位。
SELECT last_name,job_id
FROM employees
WHERE manager_id IN (100,101,110);

2.排序查询

ORDER

语法：
	select 查询列表
	from 表名
	【where 筛选条件】
	order by 排序列表	

执行顺序：
	1、from子句
	2、where子句
	3、select子句
	4、order by 子句

举例：
	select last_name,salary
	from employees
	where salary>20000
	order by salary ;	
	select * from employees;

特点：
	1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
	2、升序，通过 asc，默认行为。
	   降序，通过 desc。

1.按单个字段排序

# 一、按单个字段排序
# 案例1：将员工编号>120的员工信息进行工资的升序。
SELECT * 
FROM employees 
WHERE employee_id>120 
ORDER BY salary ;
# 案例2：将员工编号>120的员工信息进行工资的降序。
SELECT * 
FROM employees 
WHERE employee_id>120 
ORDER BY salary DESC;

2.按表达式排序

# 二、按表达式排序
# 案例1：对有奖金的员工，按年薪降序。
SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

3.按别名排序

# 三、按别名排序
# 案例1：对有奖金的员工，按年薪降序。
SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;

4.按函数的结果排序

# 四、按函数的结果排序
# 案例1：按姓名的字数长度进行升序。
SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);

5.按多个字段排序

# 五、按多个字段排序
# 案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;

6.补充：按列数排序

# 六、补充选学：按列数排序
# 其实就是根据哪一列排序，1就是第一列，2就是第二列以此类推。
SELECT * FROM employees 
ORDER BY 2 DESC;

SELECT * FROM employees 
ORDER BY first_name;

7.排序查询案例

# 1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序。
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC;

# 2.选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序。
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

# 3.查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序。
SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;

3.分组查询

GROUP BY

语法：
	SELECT
		查询列表 
	FROM
		表名 
	WHERE
		筛选条件 
	GROUP BY
		分组列表 
	HAVING
		分组后筛选 
	ORDER BY
		排序列表;
	
执行顺序：
	1、from子句
	2、where子句
	3、group by 子句
	4、having子句
	5、select子句
	6、order by子句

特点：
	1、查询列表往往是  分组函数和被分组的字段。 
	2、分组查询中的筛选分为两类。
					    筛选的基表		使用的关键词		位置
		分组前筛选		原始表		    where			 group by的前面
		分组后筛选		分组后的结果集    having			 group by的后面
	3、分组可以按单个字段也可以按多个字段，多个字段组成的值一样才算是一组。
	4、在 SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。
	5、一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率。
	6、可以搭配着排序使用。

where——group by ——having
问题：分组函数做条件只可能放在having后面！！！

1.简单的分组

# 案例1：查询每个工种的员工平均工资。
mysql> SELECT job_id,avg(salary)
FROM employees
GROUP BY job_id;
+------------+--------------+
| job_id     | avg(salary)  |
+------------+--------------+
| AC_ACCOUNT |  8300.000000 |
| AC_MGR     | 12000.000000 |
| AD_ASST    |  4400.000000 |
| AD_PRES    | 24000.000000 |
| AD_VP      | 17000.000000 |
| FI_ACCOUNT |  7920.000000 |
| FI_MGR     | 12000.000000 |
| HR_REP     |  6500.000000 |
| IT_PROG    |  5760.000000 |
| MK_MAN     | 13000.000000 |

# 案例2：查询每个领导的手下人数。
mysql> SELECT manager_id,COUNT(*)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;
+------------+----------+
| manager_id | COUNT(*) |
+------------+----------+
|        100 |       14 |
|        101 |        5 |
|        102 |        1 |
|        103 |        4 |
|        108 |        5 |
|        114 |        5 |
|        120 |        8 |
|        121 |        8 |
|        122 |        8 |
|        123 |        8 |

2.分组前筛选

# 案例1：查询邮箱中包含a字符的 每个部门的最高工资。
mysql> SELECT MAX(salary) 最高工资,department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
+----------+---------------+
| 最高工资 | department_id |
+----------+---------------+
|  7000.00 | NULL          |
|  4400.00 |            10 |
| 13000.00 |            20 |
| 11000.00 |            30 |
|  6500.00 |            40 |
|  8200.00 |            50 |
|  9000.00 |            60 |
| 10000.00 |            70 |
| 13500.00 |            80 |
| 17000.00 |            90 |

# 案例2：查询每个领导手下有奖金的员工的平均工资。
mysql> SELECT AVG(salary) 平均工资,manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
+--------------+------------+
| 平均工资     | manager_id |
+--------------+------------+
| 12200.000000 |        100 |
|  8500.000000 |        145 |
|  8500.000000 |        146 |
|  7766.666667 |        147 |
|  8650.000000 |        148 |
|  8333.333333 |        149 |
+--------------+------------+
6 rows in set (0.03 sec)

3.分组后筛选

# 案例1：查询哪个部门的员工个数>5。
mysql> SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING  COUNT(*)>5;
+----------+---------------+
| 员工个数 | department_id |
+----------+---------------+
|        6 |            30 |
|       45 |            50 |
|       34 |            80 |
|        6 |           100 |
+----------+---------------+
4 rows in set (0.02 sec)

# 案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资。
mysql> SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct  IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
+--------+-------------+
| job_id | MAX(salary) |
+--------+-------------+
| SA_MAN |    14000.00 |
+--------+-------------+
1 row in set (0.02 sec)

# 案例3：领导编号>102的    每个领导手下的最低工资大于5000的最低工资。
mysql> SELECT MIN(salary) 最低工资,manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000 ;
+----------+------------+
| 最低工资 | manager_id |
+----------+------------+
|  6900.00 |        108 |
|  7000.00 |        145 |
|  7000.00 |        146 |
|  6200.00 |        147 |
|  6100.00 |        148 |
|  6200.00 |        149 |
|  6000.00 |        201 |
|  8300.00 |        205 |
+----------+------------+
8 rows in set (0.03 sec)

4.分组后排序

# 案例：查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序。
mysql> SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary) 
ORDER BY MAX(salary);
+------------+-------------+
| job_id     | MAX(salary) |
+------------+-------------+
| PU_CLERK   |     3100.00 |
| ST_CLERK   |     3600.00 |
| SH_CLERK   |     4200.00 |
| AD_ASST    |     4400.00 |
| MK_REP     |     6000.00 |
| HR_REP     |     6500.00 |
| ST_MAN     |     8200.00 |
| AC_ACCOUNT |     8300.00 |
| IT_PROG    |     9000.00 |
| FI_ACCOUNT |     9000.00 |
| PR_REP     |    10000.00 |
| PU_MAN     |    11000.00 |
| AC_MGR     |    12000.00 |
| FI_MGR     |    12000.00 |
| MK_MAN     |    13000.00 |
| AD_VP      |    17000.00 |
| AD_PRES    |    24000.00 |
+------------+-------------+
17 rows in set (0.03 sec)

5.多个字段分组

# 案例：查询每个工种每个部门的最低工资,并按最低工资降序。
# 提示：工种和部门都一样，才是一组。
mysql> SELECT MIN(salary) 最低工资,job_id,department_id
FROM employees
GROUP BY job_id,department_id;
+----------+------------+---------------+
| 最低工资 | job_id     | department_id |
+----------+------------+---------------+
|  8300.00 | AC_ACCOUNT |           110 |
| 12000.00 | AC_MGR     |           110 |
|  4400.00 | AD_ASST    |            10 |
| 24000.00 | AD_PRES    |            90 |
| 17000.00 | AD_VP      |            90 |
|  6900.00 | FI_ACCOUNT |           100 |
| 12000.00 | FI_MGR     |           100 |
|  6500.00 | HR_REP     |            40 |
|  4200.00 | IT_PROG    |            60 |
| 13000.00 | MK_MAN     |            20 |
|  6000.00 | MK_REP     |            20 |
| 10000.00 | PR_REP     |            70 |
|  2500.00 | PU_CLERK   |            30 |
| 11000.00 | PU_MAN     |            30 |
| 10500.00 | SA_MAN     |            80 |
|  7000.00 | SA_REP     | NULL          |
|  6100.00 | SA_REP     |            80 |
|  2500.00 | SH_CLERK   |            50 |
|  2100.00 | ST_CLERK   |            50 |
|  5800.00 | ST_MAN     |            50 |
+----------+------------+---------------+
20 rows in set (0.04 sec)

6.分组查询案例

# 1.查询公司员工工资的最大值，最小值，平均值，总和
mysql> SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;
+-------------+-------------+-------------+-------------+
| MAX(salary) | MIN(salary) | AVG(salary) | SUM(salary) |
+-------------+-------------+-------------+-------------+
|    24000.00 |     2100.00 | 6461.682243 |   691400.00 |
+-------------+-------------+-------------+-------------+
1 row in set (0.02 sec)

# 2. 查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）
mysql> SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM employees;
+-----------+
| DIFFRENCE |
+-----------+
|      8735 |
+-----------+
1 row in set (0.02 sec)

# 3. 查询部门编号为 90 的员工个数
mysql> SELECT COUNT(*) 员工个数
FROM employees
WHERE department_id = 90;
+----------+
| 员工个数 |
+----------+
|        3 |
+----------+
1 row in set (0.02 sec)

4.连接查询

说明：又称多表查询，当查询语句涉及到的字段来自于多个表时，就会用到连接查询。

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行。
	发生原因：没有有效的连接条件。
	如何避免：添加有效的连接条件。

分类：
	按年代分类：
	1、sql92标准:仅仅支持内连接。
		内连接：SELECT FROM 表1,表2 WHERE 连接条件
			等值连接
			非等值连接
			自连接
	2、sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接。
	
	按功能分类：
		内连接：SELECT FROM 表1 INNERT JOIN 表2 ON 连接条件 WHERE 判断条件(这个内连接层次相较于92更加分明)
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接
			右外连接
			全外连接
		交叉连接（自然连接）

1.内连接（sql92标准）

语法:
	SELECT
		查询列表 
	FROM
		表 1 别名,表 2 别名 
	WHERE
		连接条件 
		AND 筛选条件 
	GROUP BY
		分组列表 
	HAVING
		分组后筛选 
	ORDER BY
		排序列表

执行顺序：
	1、from子句
	2、where子句
	3、and子句
	4、group by子句
	5、having子句
	6、select子句
	7、order by子句

1.等值连接

1、多表等值连接的结果为多表的交集部分。
2、n表连接，至少需要n-1个连接条件。
3、多表的顺序没有要求。
4、一般需要为表起别名。
5、可以搭配前面介绍的所有子句使用，比如排序、分组、筛选。

# 案例1：查询女神名和对应的男神名。
mysql> SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
+------------+---------+
| NAME       | boyName |
+------------+---------+
| 周芷若     | 张无忌  |
| 小昭       | 张无忌  |
| 赵敏       | 张无忌  |
| 热巴       | 鹿晗    |
| Angelababy | 黄晓明  |
| 王语嫣     | 段誉    |
+------------+---------+
6 rows in set (0.02 sec)
# 案例2：查询员工名和对应的部门名。
mysql> SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;
+-------------+-----------------+
| last_name   | department_name |
+-------------+-----------------+
| Whalen      | Adm             |
| Hartstein   | Mar             |
| Fay         | Mar             |
| Raphaely    | Pur             |
| Khoo        | Pur             |
| Baida       | Pur             |
| Tobias      | Pur             |
| Himuro      | Pur             |
| Colmenares  | Pur             |
| Mavris      | Hum             |

1.表起别名

# 为表起别名。
1、提高语句的简洁度
2、区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定。
# 查询员工名、工种号、工种名。
mysql> SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.job_id=j.job_id;
+-------------+------------+---------------------------------+
| last_name   | job_id     | job_title                       |
+-------------+------------+---------------------------------+
| Gietz       | AC_ACCOUNT | Public Accountant               |
| Higgins     | AC_MGR     | Accounting Manager              |
| Whalen      | AD_ASST    | Administration Assistant        |
| K_ing       | AD_PRES    | President                       |
| Kochhar     | AD_VP      | Administration Vice President   |
| De Haan     | AD_VP      | Administration Vice President   |
| Faviet      | FI_ACCOUNT | Accountant                      |
| Chen        | FI_ACCOUNT | Accountant                      |
| Sciarra     | FI_ACCOUNT | Accountant                      |
| Urman       | FI_ACCOUNT | Accountant                      |

2.两个表顺序会否可换

# 两个表的顺序是否可以调换。
# 查询员工名、工种号、工种名。
mysql> SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees  e
WHERE e.job_id=j.job_id;
+-------------+------------+---------------------------------+
| last_name   | job_id     | job_title                       |
+-------------+------------+---------------------------------+
| Gietz       | AC_ACCOUNT | Public Accountant               |
| Higgins     | AC_MGR     | Accounting Manager              |
| Whalen      | AD_ASST    | Administration Assistant        |
| K_ing       | AD_PRES    | President                       |
| Kochhar     | AD_VP      | Administration Vice President   |
| De Haan     | AD_VP      | Administration Vice President   |
| Faviet      | FI_ACCOUNT | Accountant                      |
| Chen        | FI_ACCOUNT | Accountant                      |
| Sciarra     | FI_ACCOUNT | Accountant                      |
| Urman       | FI_ACCOUNT | Accountant                      |

3.添加筛选

# 可以加筛选
# 案例1：查询有奖金的员工名、部门名。
mysql> SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;
+------------+-----------------+----------------+
| last_name  | department_name | commission_pct |
+------------+-----------------+----------------+
| Russell    | Sal             |           0.40 |
| Partners   | Sal             |           0.30 |
| Errazuriz  | Sal             |           0.30 |
| Cambrault  | Sal             |           0.30 |
| Zlotkey    | Sal             |           0.20 |
| Tucker     | Sal             |           0.30 |
| Bernstein  | Sal             |           0.25 |
| Hall       | Sal             |           0.25 |
| Olsen      | Sal             |           0.20 |
| Cambrault  | Sal             |           0.20 |

# 案例2：查询城市名中第二个字符为o的部门名和城市名。
mysql> SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE '_o%';
+-----------------+---------------------+
| department_name | city                |
+-----------------+---------------------+
| IT              | Southlake           |
| Shi             | South San Francisco |
| Mar             | Toronto             |
| Hum             | London              |
+-----------------+---------------------+
4 rows in set (0.03 sec)

4.添加分组

# 可以加分组
# 案例1：查询每个城市的部门个数。
mysql> SELECT count(*) 个数,l.city
FROM departments d,locations l
where d.location_id=l.location_id
group by l.city;
+------+---------------------+
| 个数 | city                |
+------+---------------------+
|    1 | London              |
|    1 | Munich              |
|    1 | Oxford              |
|   21 | Seattle             |
|    1 | South San Francisco |
|    1 | Southlake           |
|    1 | Toronto             |
+------+---------------------+
7 rows in set (0.02 sec)

# 案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资。
mysql> SELECT d.department_id,d.manager_id,min(e.salary)
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.commission_pct IS NOT NULL
GROUP BY d.department_name,d.manager_id;
+---------------+------------+---------------+
| department_id | manager_id | min(e.salary) |
+---------------+------------+---------------+
|            80 |        145 |       6100.00 |
+---------------+------------+---------------+
1 row in set (0.02 sec)

5.添加排序

# 案例：查询每个工种的工种名和员工的个数，并且按员工个数降序。
mysql> SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
+---------------------------------+----------+
| job_title                       | COUNT(*) |
+---------------------------------+----------+
| Sales Representative            |       30 |
| Shipping Clerk                  |       20 |
| Stock Clerk                     |       20 |
| Purchasing Clerk                |        5 |
| Stock Manager                   |        5 |
| Accountant                      |        5 |
| Programmer                      |        5 |
| Sales Manager                   |        5 |
| Administration Vice President   |        2 |
| Marketing Manager               |        1 |
| Accounting Manager              |        1 |
| Human Resources Representative  |        1 |
| Marketing Representative        |        1 |
| Purchasing Manager              |        1 |
| Administration Assistant        |        1 |
| Public Relations Representative |        1 |
| Public Accountant               |        1 |
| President                       |        1 |
| Finance Manager                 |        1 |
+---------------------------------+----------+
19 rows in set (0.03 sec)

6.三表连接

# 案例：查询员工名、部门名和所在的城市。
mysql> SELECT e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id 
AND d.location_id =l.location_id
ORDER BY d.department_name DESC;
+-------------+-----------------+---------------------+
| last_name   | department_name | city                |
+-------------+-----------------+---------------------+
| Rogers      | Shi             | South San Francisco |
| Olson       | Shi             | South San Francisco |
| OConnell    | Shi             | South San Francisco |
| Atkinson    | Shi             | South San Francisco |
| Walsh       | Shi             | South San Francisco |
| Markle      | Shi             | South San Francisco |
| McCain      | Shi             | South San Francisco |
| Mikkilineni | Shi             | South San Francisco |
| Bell        | Shi             | South San Francisco |
| Mourgos     | Shi             | South San Francisco |

2.非等值连接

# 案例1：查询员工的工资和工资级别
mysql> SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;
+----------+-------------+
| salary   | grade_level |
+----------+-------------+
| 24000.00 | E           |
| 17000.00 | E           |
| 17000.00 | E           |
|  9000.00 | C           |
|  6000.00 | C           |
|  4800.00 | B           |
|  4800.00 | B           |
|  4200.00 | B           |
| 12000.00 | D           |
|  9000.00 | C           |
|  8200.00 | C           |
|  7700.00 | C           |

3.自连接

# 案例：查询员工名和上级的名称。
mysql> SELECT  e1.last_name,e2.last_name
FROM employees e1,employees e2
where e1.manager_id = e2.employee_id;
+-------------+-----------+
| last_name   | last_name |
+-------------+-----------+
| Kochhar     | K_ing     |
| De Haan     | K_ing     |
| Hunold      | De Haan   |
| Ernst       | Hunold    |
| Austin      | Hunold    |
| Pataballa   | Hunold    |
| Lorentz     | Hunold    |
| Greenberg   | Kochhar   |
| Faviet      | Greenberg |
| Chen        | Greenberg |

2.内连接（sql99标准）

语法：
	SELECT
	查询列表 
	FROM
		表名 1 别名
	【INNER】 JOIN 表名 2 别名 ON 连接条件 
	WHERE
		筛选条件 
	GROUP BY
		分组列表 
	HAVING
		分组后筛选 
	ORDER BY
		排序列表;

SQL92和SQL99的区别：
	SQL99，使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性！！！

1.等值连接

1.简单连接

# 案例：查询员工名和部门名。
mysql> SELECT last_name,department_name
FROM departments d 
JOIN  employees e 
ON e.department_id =d.department_id;
+-------------+-----------------+
| last_name   | department_name |
+-------------+-----------------+
| Whalen      | Adm             |
| Hartstein   | Mar             |
| Fay         | Mar             |
| Raphaely    | Pur             |
| Khoo        | Pur             |
| Baida       | Pur             |
| Tobias      | Pur             |
| Himuro      | Pur             |
| Colmenares  | Pur             |
| Mavris      | Hum             |
| Weiss       | Shi             |

2.添加筛选

# 案例1：查询部门编号>100的部门名和所在的城市名。
mysql> SELECT department_name,city
FROM departments d
JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_id>100;
+-----------------+---------+
| department_name | city    |
+-----------------+---------+
| Acc             | Seattle |
| Tre             | Seattle |
| Cor             | Seattle |
| Con             | Seattle |
| Sha             | Seattle |
| Ben             | Seattle |
| Man             | Seattle |
| Con             | Seattle |
| Con             | Seattle |
| Ope             | Seattle |
| IT              | Seattle |
| NOC             | Seattle |
| IT              | Seattle |
| Gov             | Seattle |
| Ret             | Seattle |
| Rec             | Seattle |
| Pay             | Seattle |
+-----------------+---------+
17 rows in set (0.02 sec)

3.添加分组

# 案例1：查询每个城市的部门个数。
mysql> SELECT COUNT(*) 部门个数,l.city
FROM departments d
JOIN locations l
ON d.location_id=l.location_id
GROUP BY l.city;
+----------+---------------------+
| 部门个数 | city                |
+----------+---------------------+
|        1 | London              |
|        1 | Munich              |
|        1 | Oxford              |
|       21 | Seattle             |
|        1 | South San Francisco |
|        1 | Southlake           |
|        1 | Toronto             |
+----------+---------------------+
7 rows in set (0.02 sec)

4.添加综合

# 案例：查询部门中员工个数>10的部门名，并按员工个数降序。
mysql> SELECT COUNT(*) 员工个数,d.department_name
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
GROUP BY d.department_id
HAVING 员工个数>10
ORDER BY 员工个数 DESC;
+----------+-----------------+
| 员工个数 | department_name |
+----------+-----------------+
|       45 | Shi             |
|       34 | Sal             |
+----------+-----------------+
2 rows in set (0.02 sec)

2.非等值连接

# 案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组。
mysql> SELECT COUNT(*) 个数,grade
FROM employees e
JOIN sal_grade g
ON e.salary BETWEEN g.min_salary AND g.max_salary
WHERE e.department_id BETWEEN 10 AND 90
GROUP BY g.grade;
+------+-------+
| 个数 | grade |
+------+-------+
|   42 | A     |
|    8 | B     |
|   31 | C     |
|   11 | D     |
|    3 | E     |
|    3 | F     |
+------+-------+
6 rows in set (0.02 sec)

3.自连接

# 案例：查询员工名和对应的领导名。
mysql> SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id=m.employee_id;
+-------------+-----------+
| last_name   | last_name |
+-------------+-----------+
| Kochhar     | K_ing     |
| De Haan     | K_ing     |
| Hunold      | De Haan   |
| Ernst       | Hunold    |
| Austin      | Hunold    |
| Pataballa   | Hunold    |
| Lorentz     | Hunold    |
| Greenberg   | Kochhar   |
| Faviet      | Greenberg |
| Chen        | Greenberg |

3.外连接

说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null。

应用场景：一般用于查询主表中有但从表没有的记录

特点：	
	1、外连接分主从表，两表的顺序不能任意调换。
	2、左连接的话，left join左边为主表。
	   右连接的话，right join右边为主表。   

语法：
	SELECT
		查询列表 
	FROM
		表 1 别名 LEFT | RIGHT | FULL 【OUTER】 JOIN 表 2 别名 ON 连接条件 
	WHERE
		筛选条件;
	注意：Mysql中不支持全连接查询，可以使用UNION来实现全连接查询。
	
USE girls;

1.左连接

# 左连接
# 案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null。
mysql> SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id;
+----+------------+-----+---------------------+-------------+-------+--------------+------+---------+--------+
| id | name       | sex | borndate            | phone       | photo | boyfriend_id | id   | boyName | userCP |
+----+------------+-----+---------------------+-------------+-------+--------------+------+---------+--------+
|  1 | 柳岩       | 女  | 1988-02-03 00:00:00 | 18209876577 | NULL  |            8 | NULL | NULL    | NULL   |
|  2 | 苍老师     | 女  | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 | NULL | NULL    | NULL   |
|  3 | Angelababy | 女  | 1989-02-03 00:00:00 | 18209876567 | NULL  |            3 |    3 | 黄晓明  |     50 |
|  4 | 热巴       | 女  | 1993-02-03 00:00:00 | 18209876579 | NULL  |            2 |    2 | 鹿晗    |    800 |
|  5 | 周冬雨     | 女  | 1992-02-03 00:00:00 | 18209179577 | NULL  |            9 | NULL | NULL    | NULL   |
|  6 | 周芷若     | 女  | 1988-02-03 00:00:00 | 18209876577 | NULL  |            1 |    1 | 张无忌  |    100 |
|  7 | 岳灵珊     | 女  | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 | NULL | NULL    | NULL   |
|  8 | 小昭       | 女  | 1989-02-03 00:00:00 | 18209876567 | NULL  |            1 |    1 | 张无忌  |    100 |
|  9 | 双儿       | 女  | 1993-02-03 00:00:00 | 18209876579 | NULL  |            9 | NULL | NULL    | NULL   |
| 10 | 王语嫣     | 女  | 1992-02-03 00:00:00 | 18209179577 | NULL  |            4 |    4 | 段誉    |    300 |
| 11 | 夏雪       | 女  | 1993-02-03 00:00:00 | 18209876579 | NULL  |            9 | NULL | NULL    | NULL   |
| 12 | 赵敏       | 女  | 1992-02-03 00:00:00 | 18209179577 | NULL  |            1 |    1 | 张无忌  |    100 |
+----+------------+-----+---------------------+-------------+-------+--------------+------+---------+--------+
12 rows in set (0.04 sec)

#案例2：查哪个女神没有男朋友。
mysql> SELECT b.name
FROM beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.id  IS NULL;
+--------+
| name   |
+--------+
| 柳岩   |
| 苍老师 |
| 周冬雨 |
| 岳灵珊 |
| 双儿   |
| 夏雪   |
+--------+
6 rows in set (0.04 sec)

# 案例3：查询多少个部门没有员工。
mysql> SELECT COUNT(*) 部门个数
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
+----------+
| 部门个数 |
+----------+
|       16 |
+----------+
1 row in set (0.01 sec)

2.右连接

# 右连接
# 案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null。
mysql> SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN  beauty b ON b.boyfriend_id = bo.id;
+----+------------+-----+---------------------+-------------+-------+--------------+------+---------+--------+
| id | name       | sex | borndate            | phone       | photo | boyfriend_id | id   | boyName | userCP |
+----+------------+-----+---------------------+-------------+-------+--------------+------+---------+--------+
|  1 | 柳岩       | 女  | 1988-02-03 00:00:00 | 18209876577 | NULL  |            8 | NULL | NULL    | NULL   |
|  2 | 苍老师     | 女  | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 | NULL | NULL    | NULL   |
|  3 | Angelababy | 女  | 1989-02-03 00:00:00 | 18209876567 | NULL  |            3 |    3 | 黄晓明  |     50 |
|  4 | 热巴       | 女  | 1993-02-03 00:00:00 | 18209876579 | NULL  |            2 |    2 | 鹿晗    |    800 |
|  5 | 周冬雨     | 女  | 1992-02-03 00:00:00 | 18209179577 | NULL  |            9 | NULL | NULL    | NULL   |
|  6 | 周芷若     | 女  | 1988-02-03 00:00:00 | 18209876577 | NULL  |            1 |    1 | 张无忌  |    100 |
|  7 | 岳灵珊     | 女  | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 | NULL | NULL    | NULL   |
|  8 | 小昭       | 女  | 1989-02-03 00:00:00 | 18209876567 | NULL  |            1 |    1 | 张无忌  |    100 |
|  9 | 双儿       | 女  | 1993-02-03 00:00:00 | 18209876579 | NULL  |            9 | NULL | NULL    | NULL   |
| 10 | 王语嫣     | 女  | 1992-02-03 00:00:00 | 18209179577 | NULL  |            4 |    4 | 段誉    |    300 |
| 11 | 夏雪       | 女  | 1993-02-03 00:00:00 | 18209876579 | NULL  |            9 | NULL | NULL    | NULL   |
| 12 | 赵敏       | 女  | 1992-02-03 00:00:00 | 18209179577 | NULL  |            1 |    1 | 张无忌  |    100 |
+----+------------+-----+---------------------+-------------+-------+--------------+------+---------+--------+
12 rows in set (0.04 sec)

#案例2：查哪个女神没有男朋友。
mysql> SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN  beauty b ON b.boyfriend_id = bo.id
WHERE bo.id  IS NULL;
+----+--------+-----+---------------------+-------------+-------+--------------+------+---------+--------+
| id | name   | sex | borndate            | phone       | photo | boyfriend_id | id   | boyName | userCP |
+----+--------+-----+---------------------+-------------+-------+--------------+------+---------+--------+
|  1 | 柳岩   | 女  | 1988-02-03 00:00:00 | 18209876577 | NULL  |            8 | NULL | NULL    | NULL   |
|  2 | 苍老师 | 女  | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 | NULL | NULL    | NULL   |
|  5 | 周冬雨 | 女  | 1992-02-03 00:00:00 | 18209179577 | NULL  |            9 | NULL | NULL    | NULL   |
|  7 | 岳灵珊 | 女  | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 | NULL | NULL    | NULL   |
|  9 | 双儿   | 女  | 1993-02-03 00:00:00 | 18209876579 | NULL  |            9 | NULL | NULL    | NULL   |
| 11 | 夏雪   | 女  | 1993-02-03 00:00:00 | 18209876579 | NULL  |            9 | NULL | NULL    | NULL   |
+----+--------+-----+---------------------+-------------+-------+--------------+------+---------+--------+
6 rows in set (0.07 sec)

3. 交叉连接（自然连接）

# 交叉连接（基本不使用）
# 如果没有连接条件会出现笛卡尔积，其实内连接没有连接条件也会出现笛卡尔积。
mysql> SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo; 
+----+------------+-----+---------------------+-------------+-------+--------------+----+---------+--------+
| id | name       | sex | borndate            | phone       | photo | boyfriend_id | id | boyName | userCP |
+----+------------+-----+---------------------+-------------+-------+--------------+----+---------+--------+
|  1 | 柳岩       | 女  | 1988-02-03 00:00:00 | 18209876577 | NULL  |            8 |  1 | 张无忌  |    100 |
|  1 | 柳岩       | 女  | 1988-02-03 00:00:00 | 18209876577 | NULL  |            8 |  2 | 鹿晗    |    800 |
|  1 | 柳岩       | 女  | 1988-02-03 00:00:00 | 18209876577 | NULL  |            8 |  3 | 黄晓明  |     50 |
|  1 | 柳岩       | 女  | 1988-02-03 00:00:00 | 18209876577 | NULL  |            8 |  4 | 段誉    |    300 |
|  2 | 苍老师     | 女  | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 |  1 | 张无忌  |    100 |
|  2 | 苍老师     | 女  | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 |  2 | 鹿晗    |    800 |
|  2 | 苍老师     | 女  | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 |  3 | 黄晓明  |     50 |
|  2 | 苍老师     | 女  | 1987-12-30 00:00:00 | 18219876577 | NULL  |            9 |  4 | 段誉    |    300 |
|  3 | Angelababy | 女  | 1989-02-03 00:00:00 | 18209876567 | NULL  |            3 |  1 | 张无忌  |    100 |
|  3 | Angelababy | 女  | 1989-02-03 00:00:00 | 18209876567 | NULL  |            3 |  2 | 鹿晗    |    800 |

mysql> SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo 
WHERE b.boyfriend_id=bo.id;
+----+------------+-----+---------------------+-------------+-------+--------------+----+---------+--------+
| id | name       | sex | borndate            | phone       | photo | boyfriend_id | id | boyName | userCP |
+----+------------+-----+---------------------+-------------+-------+--------------+----+---------+--------+
|  6 | 周芷若     | 女  | 1988-02-03 00:00:00 | 18209876577 | NULL  |            1 |  1 | 张无忌  |    100 |
|  8 | 小昭       | 女  | 1989-02-03 00:00:00 | 18209876567 | NULL  |            1 |  1 | 张无忌  |    100 |
| 12 | 赵敏       | 女  | 1992-02-03 00:00:00 | 18209179577 | NULL  |            1 |  1 | 张无忌  |    100 |
|  4 | 热巴       | 女  | 1993-02-03 00:00:00 | 18209876579 | NULL  |            2 |  2 | 鹿晗    |    800 |
|  3 | Angelababy | 女  | 1989-02-03 00:00:00 | 18209876567 | NULL  |            3 |  3 | 黄晓明  |     50 |
| 10 | 王语嫣     | 女  | 1992-02-03 00:00:00 | 18209179577 | NULL  |            4 |  4 | 段誉    |    300 |
+----+------------+-----+---------------------+-------------+-------+--------------+----+---------+--------+
6 rows in set (0.07 sec)

4.全连接

# 全连接
# Mysql中不支持全连接查询，可以使用UNION来实现全连接查询。
mysql>  SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;

1064 - 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 'FULL OUTER JOIN boys bo ON b.boyfriend_id = bo.id' at line 3

5.分页查询

应用场景：当页面上的数据，一页显示不全，则需要分页显示。

分页查询的sql命令请求数据库服务器——>服务器响应查询到的多条数据——>前台页面。

语法：
	select 查询列表
	from 表1 别名 join 表2 别名
	on 连接条件
	where 筛选条件
	group by 分组
	having 分组后筛选
	order by 排序列表
	limit 起始条目索引,显示的条目数

执行顺序：
	1、from子句
	2、join子句
	3、on子句
	4、where子句
	5、group by子句
	6、having子句
	7、select子句
	8、order by子句
	9、limit子句


特点：
	1、起始条目索引如果不写，默认是0。
	2、limit后面支持两个参数
	参数1：显示的起始条目索引
	参数2：条目数。

公式：
	假如要显示的页数是page，每页显示的条目数为size;

select *
from employees
limit (page-1)*size,size;

page	size=10
1           limit 0,10
2			limit 10,10
3			limit 20,10
4			limit 30,10

# 案例1：查询员工信息表的前5条。
mysql> SELECT * FROM employees LIMIT 0,5;
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email    | phone_number | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
|         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 |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
5 rows in set (0.02 sec)

# 完全等价于。
mysql> SELECT * FROM employees LIMIT 5;
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email    | phone_number | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
|         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 |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
5 rows in set (0.03 sec)

# 案例2：查询有奖金的，且工资较高的第11名到第20名。
mysql> SELECT * 
FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC
LIMIT 10,10;
+-------------+------------+------------+----------+--------------------+--------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name  | email    | phone_number       | job_id | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+------------+----------+--------------------+--------+----------+----------------+------------+---------------+---------------------+
|         150 | Peter      | Tucker     | PTUCKER  | 011.44.1344.129268 | SA_REP | 10000.00 |           0.30 |        145 |            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 |
|         151 | David      | Bernstein  | DBERNSTE | 011.44.1344.345268 | SA_REP |  9500.00 |           0.25 |        145 |            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 |
|         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 |
|         152 | Peter      | Hall       | PHALL    | 011.44.1344.478968 | SA_REP |  9000.00 |           0.25 |        145 |            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 |
+-------------+------------+------------+----------+--------------------+--------+----------+----------------+------------+---------------+---------------------+
10 rows in set (0.03 sec)

# 案例3：查询年薪最高的前10名。
mysql> SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC
LIMIT 0,10;
+-----------+----------+-----------+
| last_name | salary   | 年薪      |
+-----------+----------+-----------+
| K_ing     | 24000.00 | 288000.00 |
| Russell   | 14000.00 | 235200.00 |
| Partners  | 13500.00 | 210600.00 |
| Kochhar   | 17000.00 | 204000.00 |
| De Haan   | 17000.00 | 204000.00 |
| Errazuriz | 12000.00 | 187200.00 |
| Ozer      | 11500.00 | 172500.00 |
| Abel      | 11000.00 | 171600.00 |
| Cambrault | 11000.00 | 171600.00 |
| K_ing     | 10000.00 | 162000.00 |
+-----------+----------+-----------+
10 rows in set (0.08 sec)

6.联合查询

# 说明：当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为union查询。

语法：
	select 查询列表 from 表1  where 筛选条件  
	union
	select 查询列表 from 表2  where 筛选条件

特点：
	1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致。
	2、union实现去重查询
	   union all 实现全部查询，包含重复项。

# 案例1：查询所有国家的年龄>20岁的用户信息。
SELECT * FROM usa WHERE uage >20 
UNION
SELECT * FROM chinese WHERE age >20 ;

# 案例2：查询所有国家的用户姓名和年龄。
SELECT uname,uage FROM usa
UNION
SELECT age,name FROM chinese;

# 案例3：union自动去重/union all 可以支持重复项。
mysql> SELECT 1,'范冰冰' 
UNION ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' ;
+---+--------+
| 1 | 范冰冰 |
+---+--------+
| 1 | 范冰冰 |
| 1 | 范冰冰 |
| 1 | 范冰冰 |
| 1 | 范冰冰 |
+---+--------+
4 rows in set (0.03 sec)

mysql> SELECT 1,'范冰冰' 
UNION
SELECT 1,'范冰冰' 
UNION
SELECT 1,'范冰冰' 
UNION
SELECT 1,'范冰冰';
+---+--------+
| 1 | 范冰冰 | # 这个是字段不是数据
+---+--------+
| 1 | 范冰冰 |
+---+--------+
1 row in set (0.02 sec)

2.插入

语法：
	方式1：
		插入单行：
			insert into 表名(字段名1,字段名2 ,...) 
			values (值1，值2,...);
		插入多行：
			insert into 表名(字段名1,字段名2 ,...) 
			values (值1，值2,...),(值1，值2,...),(值1，值2,...);
	
	方式2：
		insert into 表名
		set 列名=值,列名=值,...

特点:
	1、字段和值列表一一对应，包含类型、约束等必须匹配。
	2、数值型的值，不用单引号，非数值型的值，必须使用单引号。
	3、字段顺序无要求。
	4、可以省略字段列表，默认所有字段，值列表要完整，且顺序要与字段列表一致。

# 插入方式1
# 要求字段和值列表一一对应，且遵循类型和约束的限制。
mysql> INSERT INTO stuinfo(stuid,stuname,stugender,email,age,majorid)
VALUES(1,'吴用','男','wuyong@qq.com',12,1); 
Query OK, 1 row affected (0.01 sec)

# 可以为空字段如何插入。
# 方案1：stugender字段名和值都不写。
mysql> INSERT INTO stuinfo(stuid,stuname,email,majorid)
VALUES(5,'齐鱼','qiqin@qq.com',1); 
Query OK, 1 row affected (0.00 sec)
# 方案2：字段名写上，值使用null。
mysql> INSERT INTO stuinfo(stuid,stuname,email,age,majorid)
VALUES(7,'影流之主','yingliuzhizhu@qq.com',NULL,1); 
Query OK, 1 row affected (0.00 sec)

# 默认字段如何插入
# 方案1：字段名写上，值使用default。
mysql> INSERT INTO stuinfo(stuid,stuname,email,stugender,majorid)
VALUES(8,'盲僧','mangseng@qq.com',DEFAULT,1); 
Query OK, 1 row affected (0.01 sec)
# 方案2：字段名和值都不写。
mysql> INSERT INTO stuinfo(stuid,stuname,email,majorid)
VALUES(9,'亚索','yasuo@qq.com',1); 
Query OK, 1 row affected (0.00 sec)

# 可以省略字段列表，默认所有字段
mysql> INSERT INTO stuinfo VALUES(11,'卡特','女','kate@126.com',12,1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO stuinfo VALUES(NULL,'维克托','男','weiketuo@12.com',12,1);
1048 - Column 'stuid' cannot be null

# 插入方式2
mysql> INSERT INTO beauty SET id=20,NAME='妖姬',phone='111111';
Query OK, 1 row affected (0.01 sec)

# 两种方式pk
# 1.方式一支持插入多行,方式二不支持。
mysql> INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 2.方式一支持子查询，方式二不支持。
mysql> INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1234567'
FROM boys WHERE id<3;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

3.修改

1.修改单表的记录
	语法：
		update 表名 set 列=新值,列=新值,... where 筛选条件;

2.修改多表的记录
	sql92语法:
		update 表1 别名,表2 别名 set 列=值,... where 连接条件 and 筛选条件;

	sql99语法：
		update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值,... where 筛选条件;

# 1.修改单表的记录
# 案例1：修改beauty表中姓唐的女神的电话为110
mysql> UPDATE beauty SET phone = '110'
WHERE NAME LIKE '唐%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

#案例2：修改boys表中id好为2的名称为张飞，魅力值 10
mysql> UPDATE boys SET boyname='张飞',usercp=10
WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 2.修改多表的记录
# 案例 1：修改张无忌的女朋友的手机号为114
mysql> UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

# 案例2：修改没有男朋友的女神的男朋友编号都为2号
mysql> UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;
Query OK, 11 rows affected (0.00 sec)
Rows matched: 11  Changed: 11  Warnings: 0

3.删除

方式一：delete
	语法：
		1.单表的删除
			delete from 表名 where 【筛选条件】 【limit 数量】
		2.多表的删除
			sql92语法：
				delete 表1的别名,表2的别名 from 表1 别名,表2 别名 where 连接条件 and 筛选条件;
			sql99语法：
				delete 表1的别名,表2的别名 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件;

方式二：truncate
	语法：
		truncate table 表名;

# 1.单表的删除
# 案例：删除手机号以9结尾的女神信息
mysql> DELETE FROM beauty WHERE phone LIKE '%9';
Query OK, 2 rows affected (0.01 sec)

# 2.多表的删除
# 案例：删除张无忌的女朋友的信息
mysql> DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';
Query OK, 0 rows affected (0.00 sec)
# 案例：删除黄晓明的信息以及他女朋友的信息
mysql> DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';
Query OK, 0 rows affected (0.00 sec)

# 方式二：truncate语句
TRUNCATE TABLE stuinfo ;

# delete和truncate的区别？
1.delete可以添加WHERE条件TRUNCATE不能添加WHERE条件，一次性清除所有数据。
2.truncate的效率较高
3.如果删除带自增长列的表，使用DELETE删除后，重新插入数据，记录从断点处开始
  使用TRUNCATE删除后，重新插入数据，记录从1开始
4.delete 删除数据，会返回受影响的行数
  TRUNCATE删除数据，不返回受影响的行数
5.delete删除数据，可以支持事务回滚
  TRUNCATE删除数据，不支持事务回滚

ginger_mr

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql3_数据操作

文章目录数据处理之查询1.基础查询1.查询常量2.查询表达式3.查询单个字段4.查询多个字段5.查询所有字段6.查询函数7.起别名8.+的作用9.distinct的使用10.查看表的结构2.基础查询案例3.条件查询1.按关系表达式筛选2.按逻辑表达式筛选3.模糊查询1.like2.in3.between and4.is null4.条件查询案例5.排序查询1.按单个字段排序2.按表达式排序3.按别名排序4.按函数的结果排序5.按多个字段排序6.补充：按列数排序数据处理之查询1.基础查询/* 语法：
复制链接

扫一扫