SQL语句的强化
数据库数据
girls数据库
1 /* 2 SQLyog Ultimate v10.00 Beta1 3 MySQL - 5.7.18-log : Database - girls 4 ********************************************************************* 5 */ 6 7 8 /*!40101 SET NAMES utf8 */; 9 10 /*!40101 SET SQL_MODE=''*/; 11 12 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 13 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 14 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 15 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 16 CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */; 17 18 USE `girls`; 19 20 /*Table structure for table `admin` */ 21 22 DROP TABLE IF EXISTS `admin`; 23 24 CREATE TABLE `admin` ( 25 `id` int(11) NOT NULL AUTO_INCREMENT, 26 `username` varchar(10) NOT NULL, 27 `password` varchar(10) NOT NULL, 28 PRIMARY KEY (`id`) 29 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; 30 31 /*Data for the table `admin` */ 32 33 insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666'); 34 35 /*Table structure for table `beauty` */ 36 37 DROP TABLE IF EXISTS `beauty`; 38 39 CREATE TABLE `beauty` ( 40 `id` int(11) NOT NULL AUTO_INCREMENT, 41 `name` varchar(50) NOT NULL, 42 `sex` char(1) DEFAULT '女', 43 `borndate` datetime DEFAULT '1987-01-01 00:00:00', 44 `phone` varchar(11) NOT NULL, 45 `photo` blob, 46 `boyfriend_id` int(11) DEFAULT NULL, 47 PRIMARY KEY (`id`) 48 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; 49 50 /*Data for the table `beauty` */ 51 52 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); 53 54 /*Table structure for table `boys` */ 55 56 DROP TABLE IF EXISTS `boys`; 57 58 CREATE TABLE `boys` ( 59 `id` int(11) NOT NULL AUTO_INCREMENT, 60 `boyName` varchar(20) DEFAULT NULL, 61 `userCP` int(11) DEFAULT NULL, 62 PRIMARY KEY (`id`) 63 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 64 65 /*Data for the table `boys` */ 66 67 insert into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300); 68 69 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 70 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 71 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 72 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
myemployees数据库
1 /* 2 SQLyog Ultimate v10.00 Beta1 3 MySQL - 5.5.15 : Database - myemployees 4 ********************************************************************* 5 */ 6 7 8 /*!40101 SET NAMES utf8 */; 9 10 /*!40101 SET SQL_MODE=''*/; 11 12 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 13 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 14 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 15 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 16 CREATE DATABASE /*!32312 IF NOT EXISTS*/`myemployees` /*!40100 DEFAULT CHARACTER SET gb2312 */; 17 18 USE `myemployees`; 19 20 /*Table structure for table `departments` */ 21 22 DROP TABLE IF EXISTS `departments`; 23 24 CREATE TABLE `departments` ( 25 `department_id` int(4) NOT NULL AUTO_INCREMENT, 26 `department_name` varchar(3) DEFAULT NULL, 27 `manager_id` int(6) DEFAULT NULL, 28 `location_id` int(4) DEFAULT NULL, 29 PRIMARY KEY (`department_id`), 30 KEY `loc_id_fk` (`location_id`), 31 CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) 32 ) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312; 33 34 /*Data for the table `departments` */ 35 36 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); 37 38 /*Table structure for table `employees` */ 39 40 DROP TABLE IF EXISTS `employees`; 41 42 CREATE TABLE `employees` ( 43 `employee_id` int(6) NOT NULL AUTO_INCREMENT, 44 `first_name` varchar(20) DEFAULT NULL, 45 `last_name` varchar(25) DEFAULT NULL, 46 `email` varchar(25) DEFAULT NULL, 47 `phone_number` varchar(20) DEFAULT NULL, 48 `job_id` varchar(10) DEFAULT NULL, 49 `salary` double(10,2) DEFAULT NULL, 50 `commission_pct` double(4,2) DEFAULT NULL, 51 `manager_id` int(6) DEFAULT NULL, 52 `department_id` int(4) DEFAULT NULL, 53 `hiredate` datetime DEFAULT NULL, 54 PRIMARY KEY (`employee_id`), 55 KEY `dept_id_fk` (`department_id`), 56 KEY `job_id_fk` (`job_id`), 57 CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`), 58 CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`) 59 ) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312; 60 61 /*Data for the table `employees` */ 62 63 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'); 64 65 /*Table structure for table `jobs` */ 66 67 DROP TABLE IF EXISTS `jobs`; 68 69 CREATE TABLE `jobs` ( 70 `job_id` varchar(10) NOT NULL, 71 `job_title` varchar(35) DEFAULT NULL, 72 `min_salary` int(6) DEFAULT NULL, 73 `max_salary` int(6) DEFAULT NULL, 74 PRIMARY KEY (`job_id`) 75 ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; 76 77 /*Data for the table `jobs` */ 78 79 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); 80 81 /*Table structure for table `locations` */ 82 83 DROP TABLE IF EXISTS `locations`; 84 85 CREATE TABLE `locations` ( 86 `location_id` int(11) NOT NULL AUTO_INCREMENT, 87 `street_address` varchar(40) DEFAULT NULL, 88 `postal_code` varchar(12) DEFAULT NULL, 89 `city` varchar(30) DEFAULT NULL, 90 `state_province` varchar(25) DEFAULT NULL, 91 `country_id` varchar(2) DEFAULT NULL, 92 PRIMARY KEY (`location_id`) 93 ) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312; 94 95 /*Data for the table `locations` */ 96 97 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'); 98 99 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 100 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 101 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 102 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
基础查询
1 # 基础查询DQL data query language 2 /* 3 特点: 4 1.查询列表可以是：表中的字段、常量值、表达式、函数 5 2.查询的结果是一张虚拟表 6 */ 7 8 USE myemployees; 9 # 1.查询单个字段 10 SELECT last_name FROM employees; 11 # 2.查询表中多个字段 12 SELECT last_name, email FROM employees; 13 # 3.查询表中所有字段 14 SELECT * FROM employees; 15 # 4.查询表中所有字段方式2 着重号区分字段与关键字 16 SELECT 17 `first_name`, 18 `last_name`, 19 `email`, 20 `salary` 21 FROM 22 employees; 23 24 # 5.查询常量值 25 SELECT 100; 26 27 # 6.查询表达式 28 SELECT 100*98 29 30 # 7.查询函数 31 SELECT VERSION(); 32 33 34 35 # 8. 起别名 36 # 方式1 37 SELECT 100*98 AS 结果; 38 SELECT last_name AS 姓, first_name AS 名 FROM employees; 39 40 # 方式2 41 SELECT last_name 姓,first_name 名 FROM employees; 42 43 # 方式3 区分与关键字发生冲突 加双引号 44 SELECT last_name "OUT put" FROM employees 45 46 47 # 9.去重 48 SELECT DISTINCT department_id FROM employees; 49 50 # 10.连接符 51 SELECT CONCAT(first_name, " ", last_name) AS 姓名 FROM employees; 52 53 54 # 11.IFNULL(expr1,expr2)的使用 55 SELECT CONCAT(last_name, first_name, IFNULL(commission_pct, 0)) FROM employees;
条件查询
1 # 一. 按照条件表达式查询 2 3 # 1.查询出工资大于12000的员工信息 4 5 SELECT 6 * 7 FROM 8 employees 9 WHERE 10 salary > 12000; 11 12 # 2.查询出员工编号不等于90的员工名字与部门编号 13 SELECT last_name, department_id FROM employees WHERE department_id != 90; 14 15 16 # 二.按照逻辑表达式查询 17 # 1. 查询工资在10000-20000的员工名，工资，奖金 18 SELECT last_name, salary, commission_pct FROM employees WHERE salary>10000 AND salary<20000; 19 20 21 # 三、模糊查询
常见函数
1 # 1.流程控制函数 IF(expr1,expr2,expr3)函数 2 # 2.流程控制函数 case函数 后面跟字段或表达式 或者都不跟 单行函数 3 SELECT 4 salary 原工资, 5 department_id, 6 CASE 7 department_id 8 WHEN 30 THEN 9 salary * 1.1 10 WHEN 40 THEN 11 salary * 1.2 12 WHEN 50 THEN 13 salary * 1.3 ELSE salary 14 END AS 新工资 15 FROM 16 employees; 17 18 19 SELECT 20 salary, 21 CASE 22 23 WHEN salary > 20000 THEN 24 'A' 25 WHEN salary > 15000 THEN 26 'B' 27 WHEN salary > 10000 THEN 28 'C' ELSE 'D' 29 END AS 工资级别 30 FROM 31 employees;
分组查询
1 # GROUP BY 2 -- 查询每个工种的最高工资 3 -- 语法: 分组函数 列 表 分组 字段 4 SELECT 5 MAX( salary ), 6 job_id 7 FROM 8 employees 9 GROUP BY 10 job_id; 11 12 13 -- 查询每个位置上的部门个数 14 SELECT COUNT(*), location_id FROM departments GROUP BY location_id; 15 16 17 -- 查询邮箱中包含a字符的，每个部门的平均工资 18 SELECT 19 AVG( salary ), 20 department_id, 21 email 22 FROM 23 employees 24 WHERE 25 email LIKE '%a%' 26 GROUP BY 27 department_id; 28 29 -- 查询有奖金的每个领导员工的最高工资 30 SELECT MAX(salary), manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id; 31 32 -- 查询哪个部门的员工个数大于2 添加分组后的刷选 33 # 1.首先查出每个部门员工的个数 34 SELECT COUNT(*), department_id FROM employees GROUP BY department_id; 35 # 2.再在新临时表后加筛选 36 SELECT COUNT(*), department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 2; 37 38 39 -- 查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资； 40 -- 1.查询每个工种有奖金的员工的最高工资 41 SELECT MAX(salary), job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id; 42 -- 2.在1之后的结果再加条件 43 SELECT 44 MAX( salary ) AS 最高工资, 45 job_id 46 FROM 47 employees 48 WHERE 49 commission_pct IS NOT NULL 50 GROUP BY 51 job_id 52 HAVING 53 最高工资 > 12000; 54 55 56 -- 查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号是哪个 以及最低工资 57 58 SELECT 59 MIN( salary ), 60 manager_id 61 FROM 62 employees 63 WHERE 64 manager_id > 102 65 GROUP BY 66 manager_id 67 HAVING 68 MIN( salary )> 5000;
分组函数
1 # 分组函数 统计作用 2 -- SUM AVG处理数字型 3 -- MIN MAX COUNT可以处理任何类型 日期 字符都行 4 5 SELECT SUM(salary) 和, ROUND(AVG(salary), 2) 平均, MIN(salary) 最小, MAX(salary) 最大, COUNT(salary) 个数 FROM employees; 6 7 -- 和distinct搭配使用 8 SELECT SUM(DISTINCT(salary)), SUM(salary) FROM employees; 9 10 -- count的详细使用 11 12 SELECT COUNT(salary) FROM employees; 13 14 -- 统计行数 效率稍微比上边高点 15 SELECT COUNT(1) FROM employees; 16 SELECT COUNT(*) FROM employees; # 这种情况用的比较多
连接查询
1 # SQL92 2 # 连接查询 多张表查询 3 USE girls; 4 -- 笛卡尔积乘积现象 5 SELECT NAME, boyname FROM beauty, boys; #没有条件去约束 6 7 8 # 1.等值连接 9 SELECT NAME 10 , 11 boyname 12 FROM 13 beauty, 14 boys 15 WHERE 16 beauty.boyfriend_id = boys.id; 17 18 -- 查询员工对应的部门 19 USE myemployees; 20 SELECT 21 last_name, 22 department_name 23 FROM 24 employees, 25 departments 26 WHERE 27 employees.department_id = departments.department_id; 28 29 30 -- 查询员工名 工种号 工种名 31 SELECT 32 last_name, 33 employees.job_id, 34 job_title 35 FROM 36 employees, 37 jobs 38 WHERE 39 employees.job_id = jobs.job_id; 40 41 SELECT 42 last_name, 43 e.job_id, 44 job_title 45 FROM 46 employees e, 47 jobs j 48 WHERE 49 e.job_id = j.job_id; 50 51 52 -- 可以加筛选 分组group by 分组后筛选having 排序order by 53 -- 查询城市名第二个字符为o的部门名以及城市名 54 SELECT 55 department_name, 56 city 57 FROM 58 departments, 59 locations 60 WHERE 61 locations.location_id = departments.location_id 62 AND city LIKE '_o%'; 63 64 65 -- 非等值连接 与等值连接一样可以加分组 排序等条件 66 CREATE TABLE job_grades( 67 grade_level VARCHAR(3), 68 lowest_sal INT, 69 highest_sal INT 70 ); 71 72 INSERT INTO job_grades(grade_level, lowest_sal, highest_sal) VALUES 73 ("A", 1000, 2999), 74 ("B", 3000, 5999), 75 ("C", 6000, 9999), 76 ("D", 10000, 14999), 77 ("E", 15000, 24999), 78 ("F", 25000, 40000); 79 80 # 查询员工的工资以及对应的级别 81 SELECT 82 salary, 83 grade_level 84 FROM 85 employees e, 86 job_grades g 87 WHERE 88 salary BETWEEN g.lowest_sal 89 AND g.highest_sal; 90 91 -- 自连接 92 -- 查询员工名与上级名 93 SELECT last_name, manager_id, employee_id FROM employees; 94 SELECT 95 e.employee_id, 96 e.last_name, 97 m.last_name, 98 m.employee_id 99 FROM 100 employees e, 101 employees m 102 WHERE 103 e.manager_id = m.employee_id; 104
SQL99语法连接
1 # SQL99语法 2 /* 3 -- 格式: 4 SELECT 查询列表 5 FROM 表1 别名 连接类型 6 JOIN 表2 别名 7 ON 连接条件 8 WHERE 筛选条件 9 GROUP BY 分组 10 HAVING 筛选条件 11 ORDER BY 排序列表 12 13 14 分类 15 内连接： INNER JOIN 等值 非等值 自连接 16 外连接 17 左外 LEFT OUTER 18 右外 RIGHT OUTER 19 全外 FULL OUTER 20 交叉连接 cross 21 22 内连接 23 语法: 24 SELECT 查询列表 25 FROM 表1 别名 26 INNER JOIN 表2 别名 27 ON 连接条件 28 */ 29 30 #1. 等值连接 31 # 案例1: 查询员工名、部门名 32 SELECT 33 last_name, 34 department_name 35 FROM 36 employees e 37 INNER JOIN departments d ON e.department_id = d.department_id; 38 39 # 案例2 查询名字中包含e的员工名和工种名 40 SELECT 41 last_name, 42 job_title 43 FROM 44 employees e 45 INNER JOIN jobs j 46 ON e.job_id = j.job_id 47 WHERE e.last_name LIKE '%e%'; 48 49 # 案例3 查询部门个数大于3的城市名与部门个数 50 # 案例4 查询哪个部门的员工个数>3的部门名和员工个数，并按照个数排序（降序） 51 SELECT department_name, COUNT(*) 个数 FROM employees e INNER JOIN departments d ON e.department_id = d.department_id 52 GROUP BY department_name 53 HAVING COUNT(*) > 3 54 ORDER BY COUNT(*) DESC; 55 56 # 案例5查询员工名 部门名 工种名 并按照部门名降序 57 SELECT 58 last_name, 59 department_name, 60 job_title 61 FROM 62 employees e 63 INNER JOIN departments d ON e.employee_id = d.department_id 64 INNER JOIN jobs j ON e.job_id = j.job_id 65 ORDER BY 66 department_name DESC; 67 68 69 # 2.非等值连接 70 # 查询员工的工资级别 71 SELECT 72 salary, 73 grade_level 74 FROM 75 employees e 76 JOIN job_grades j ON e.salary BETWEEN j.lowest_sal 77 AND j.highest_sal; 78 79 # 查询员工的工资级别个数大于20的个数并且已工资级别降序 80 SELECT 81 COUNT(*), 82 grade_level 83 FROM 84 employees e 85 JOIN job_grades j ON e.salary BETWEEN j.lowest_sal 86 AND j.highest_sal 87 GROUP BY 88 grade_level 89 HAVING 90 COUNT(*) > 20 91 ORDER BY 92 grade_level DESC; 93 94 # 3 自连接 95 # 查询姓名中包含字符K的员工的名字 上级的名字 其中inner可以省掉 96 SELECT e.last_name, m.last_name 97 FROM employees e 98 INNER JOIN employees m 99 ON e.manager_id = m.employee_id 100 WHERE e.last_name LIKE '%k%'; 101 102 103 -- 二 外连接 104 # 外连接应用场景就是一张表关联另外一张表里边没有的数据 105 # 左外连接 left JOIN左边的是主表 106 # 右外连接 right JOIN 右边的是主表 107 # 左外 与 右外 交换两张表的顺序 最终的结果是一样的 108 # 查询男朋友不在男神表中的女神名字 109 USE girls; 110 # 左外连接 111 SELECT 112 b.NAME, 113 bo.* 114 FROM 115 beauty b 116 LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id 117 WHERE 118 bo.id IS NULL; 119 # 右外连接 120 SELECT 121 b.NAME, 122 bo.* 123 FROM 124 boys bo 125 RIGHT OUTER JOIN beauty b ON b.boyfriend_id = bo.id 126 WHERE 127 bo.id IS NULL; 128 129 # 全外连接 130 # 全外连接的结果就是相当于内连接的结果+表1中有的但表2中没有+表2中有的但表1中没有 131 -- FULL OUTER JOIN 在mysql中是不支持全外连接的 132 SELECT 133 b.NAME, 134 bo.* 135 FROM 136 boys bo 137 FULL OUTER JOIN beauty b ON b.boyfriend_id = bo.id 138 WHERE 139 bo.id IS NULL; 140 141 # 交叉连接 142 -- CROSS JOIN 143 SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo; 144 145
