MySQL基础之SQL练习

蝌蚪成长记 07-27 78

前言：

目前大家对“mysql转义字符escape”都比较重视，看官们都想要了解一些“mysql转义字符escape”的相关资讯。那么小编在网摘上汇集了一些有关“mysql转义字符escape””的相关内容，希望同学们能喜欢，兄弟们快快来了解一下吧！

1.数据脚本导入

/*SQLyog Ultimate v10.00 Beta1MySQL - 5.7.24 : Database - myemployees**********************************************************************//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;CREATE DATABASE /*!32312 IF NOT EXISTS*/`myemployees` /*!40100 DEFAULT CHARACTER SET gb2312 */;USE `myemployees`;/*Table structure for table `departments` */DROP TABLE IF EXISTS `departments`;CREATE TABLE `departments` (  `department_id` int(4) NOT NULL AUTO_INCREMENT,  `department_name` varchar(3) DEFAULT NULL,  `manager_id` int(6) DEFAULT NULL,  `location_id` int(4) DEFAULT NULL,  PRIMARY KEY (`department_id`),  KEY `loc_id_fk` (`location_id`),  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;/*Data for the table `departments` */insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);/*Table structure for table `employees` */DROP TABLE IF EXISTS `employees`;CREATE TABLE `employees` (  `employee_id` int(6) NOT NULL AUTO_INCREMENT,  `first_name` varchar(20) DEFAULT NULL,  `last_name` varchar(25) DEFAULT NULL,  `email` varchar(25) DEFAULT NULL,  `phone_number` varchar(20) DEFAULT NULL,  `job_id` varchar(10) DEFAULT NULL,  `salary` double(10,2) DEFAULT NULL,  `commission_pct` double(4,2) DEFAULT NULL,  `manager_id` int(6) DEFAULT NULL,  `department_id` int(4) DEFAULT NULL,  `hiredate` datetime DEFAULT NULL,  PRIMARY KEY (`employee_id`),  KEY `dept_id_fk` (`department_id`),  KEY `job_id_fk` (`job_id`),  CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),  CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;/*Data for the table `employees` */insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');/*Table structure for table `jobs` */DROP TABLE IF EXISTS `jobs`;CREATE TABLE `jobs` (  `job_id` varchar(10) NOT NULL,  `job_title` varchar(35) DEFAULT NULL,  `min_salary` int(6) DEFAULT NULL,  `max_salary` int(6) DEFAULT NULL,  PRIMARY KEY (`job_id`)) ENGINE=InnoDB DEFAULT CHARSET=gb2312;/*Data for the table `jobs` */insert  into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);/*Table structure for table `locations` */DROP TABLE IF EXISTS `locations`;CREATE TABLE `locations` (  `location_id` int(11) NOT NULL AUTO_INCREMENT,  `street_address` varchar(40) DEFAULT NULL,  `postal_code` varchar(12) DEFAULT NULL,  `city` varchar(30) DEFAULT NULL,  `state_province` varchar(25) DEFAULT NULL,  `country_id` varchar(2) DEFAULT NULL,  PRIMARY KEY (`location_id`)) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;/*Data for the table `locations` */insert  into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

2.SQL练习

#MySQL练习

数据存放到表中，然后表再放到库中

一个库中可以有多张表，每张表具有唯一的表名用来标识自己

mysql注解：

#单行注解

-- 单行注解(注意有空格)

多行注解

-- 注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要

-- 0.查询当前数据库的版本

SELECT VERSION();

-- 1.查看当前所有数据库

SHOW DATABASES;

-- 2.使用指定的数据库(myemployees是数据库)

USE myemployees;

-- 3.查看当前库下的所有表

SHOW TABLES;

-- 4.查看指定的表结构(employees/departments是表)

DESC employees;

DESC departments;

#5.查询单个字段

SELECT last_name FROM employees;

-- 6.查询多个字段(多个字段之间使用逗号隔开)

SELECT last_name,salary,email FROM employees;

-- 7.查询所有字段(*)

SELECT * FROM employees;

-- 8.查询所有last_name 并起：姓名

SELECT last_name AS 姓名 FROM employees;

SELECT last_name 姓名 FROM employees;

-- 9.查询所有last_name 并起：Name

SELECT last_name "Name" FROM employees;

-- 10.查询员工表中涉及到的所有的部门编号(去重distinct)

SELECT DISTINCT department_id '部门' FROM employees;

-- 11.显示"我的工资是7200" ;CONCAT()连接符

SELECT CONCAT('我的工资是',salary) FROM employees;

-- 12.显示公司员工的姓名，奖金率，若奖金率为空的显示0 ;IFNULL()

SELECT last_name AS 姓名 ,IFNULL(commission_pct,0) AS 奖金率 FROM employees;

-- 13.显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT

SELECT CONCAT(last_name,',',email,',',salary,',',hiredate) AS 'OUT_PUT' FROM employees;

-- 14.查询工资>12000的员工信息

SELECT * FROM employees WHERE salary>12000;

-- 15.查询部门编号不等于90号的员工名和部门编号

SELECT last_name,department_id FROM employees WHERE department_id <>90;

SELECT last_name,department_id FROM employees WHERE department_id !=90;

SELECT last_name,department_id FROM employees WHERE department_id NOT IN(90);

-- 16.查询工资在10000到20000之间的员工名、工资及奖金

SELECT last_name,salary,commission_pct FROM employees

WHERE salary BETWEEN 10000 AND 20000;

-- 17.查询员工名中包含字符a的员工信息

SELECT last_name FROM employees WHERE last_name LIKE '%a%';

-- 18.查询员工中第三个字符为n,第五个字符为l的员工名和工资

SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';

-- 19.查询员工名中第二个字符为_(下划线)的员工名

-- 转义关键字ESCAPE,建议使用$,其中使用\报错

SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

-- 20.查询1992年入职的员工姓名和入职时间

SELECT last_name,hiredate FROM employees WHERE hiredate LIKE '1992%';

-- 21.查询员工的姓名及部门编号，其中部门编号为1开头的

SELECT last_name,department_id FROM employees WHERE department_id LIKE '1%'

-- 22.查询奖金率不是空的并且工资小于18000员工工资，员工名

SELECT salary,last_name FROM employees WHERE commission_pct IS NOT NULL AND salary<18000;

-- 22.查询job_id不在'IT_PROG'这个或者工资大于12000的信息

SELECT * FROM employees WHERE job_id NOT IN('IT_PROG') OR salary>12000;

-- 23.查询员工号为176的员工姓名和部门号及年薪(commission_pct有可能为null,与null运算都为空)

SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) FROM employees

WHERE employee_id = 176;

-- 24.选择工资不在 5000 到 12000 的员工的姓名和工资

SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000;

-- 25.选择在 20 或 50 号部门工作的员工姓名和部门号

SELECT last_name,department_id FROM employees WHERE department_id IN(20,50);

-- 26.选择公司中没有管理者的员工姓名及 job_id

SELECT last_name,job_id FROM employees WHERE manager_id IS NULL;

-- 27.选择姓名中有字母 a 和 e 的员工姓名

SELECT last_name FROM employees

WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

-- 28.显示出表 employees 表中 first_name 以 'e'结尾的员工信息

SELECT * FROM employees WHERE first_name LIKE '%e';

-- 29.查询员工的信息并按照工资的从高到低排序(desc降序;asc升序)

SELECT * FROM employees ORDER BY salary DESC;

-- 30.按照年薪的高低显示员工的信息和年薪 [表达式排序]

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) '年薪' FROM employees

ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

-- 31.选择工资不在8000到17000的员工的姓名和工资，按工资降序

SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000

ORDER BY salary DESC;

-- 32.查询员工的姓名和部门号和年薪，按年薪降序按姓名升序

SELECT last_name AS 姓名,department_id AS 部门,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪

FROM employees ORDER BY 年薪 DESC,姓名 ASC;

-- 33.选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序

SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000

ORDER BY salary DESC;

-- 34.查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT email, LENGTH(email),department_id FROM employees WHERE email LIKE '%e%'

ORDER BY LENGTH(email) DESC ,department_id ASC;

-- 35.显示当前日期+时间s;其中DUAL是伪表

SELECT NOW();

SELECT NOW() FROM DUAL;

-- 36.显示当前时间

SELECT CURTIME();

-- 37.显示当前日期

SELECT CURDATE();

-- 38.显示2016-02-11到现在相差天数

SELECT DATEDIFF(NOW(),'2016-02-11');

-- 39.用英文显示当前的月份

SELECT MONTHNAME('2018-02-04');

-- 40.显示员工的名字字节数长度

SELECT LENGTH(last_nam) 长度 FROM employees;

-- 41.把员工的姓变成大写名变成小写连接显示姓名

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

-- 42.从"我是中国运动员"中截取中国显示出来

SELECT SUBSTR('我是中国运动员',3,2);

SELECT SUBSTRING('我是中国运动员',3,2);

-- 43.从"我是中国运动员我爱中国这个国家"中 "中国"首次出现的位置？

SELECT INSTR('我是中国运动员我爱中国这个国家','中国') ;

-- 44.从" 我是运动员 "中去除空格(TRIM只能去除前后空格)

SELECT TRIM(' 我是运动员 ') ;

-- 45.员工的工资用10位数显示，不够的用空格补齐

SELECT LPAD(salary,10,'*') FROM employees;

SELECT RPAD(salary,10,'*') FROM employees;

-- 46.从"我是美国运动员我爱美国这个国家"中，把美国替换成中国

SELECT REPLACE('我是美国运动员我爱美国这个国家','美国','中国');

-- 47.从" 我是运动员 *"中去除空格

SELECT REPLACE(' 我是运动员 *',' ','') AS ji;

-- 48.将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT last_name,LENGTH(last_name) '长度' FROM employees

ORDER BY last_name ASC;

-- 49.查询员工工资在10000到15000的员工姓名、工资，且工资保留两位小数(四舍五入),并且按工资降序排列

SELECT last_name,ROUND(salary,2) FROM employees

WHERE salary BETWEEN 10000 AND 15000

ORDER BY salary DESC;

SELECT TRUNCATE(100.85,1);

-- 50.查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE

FROM employees;

51.使用 case-when，按照下面的条件：

job grade

AD_PRES A

ST_MAN B

IT_PROG C

SA_REP D

ST_CLERK E

产生以下结果

Last_name Job_id Grade

king AD_PRES A

....

SELECT last_name 'Last_name',job_id 'Job_id',

CASE job_id WHEN 'AD_PRES' THEN 'A'

WHEN 'ST_MAN' THEN 'B'

WHEN 'IT_PROG' THEN 'C'

WHEN 'SA_REP' THEN 'D'

WHEN 'ST_CLERK' THEN 'E'

END AS 'grade'

FROM employees;

-- 52.查询每个工种的员工平均工资

SELECT AVG(salary),job_id

FROM employees

GROUP BY job_id;

-- 53.查询邮箱中包含a字符的每个部门的最高工资

SELECT MAX(salary),department_id

FROM employees

WHERE email LIKE '%a%'

GROUP BY department_id;

-- 54.查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary),manager_id

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY manager_id;

-- 55.每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING MAX(salary)>12000;

-- 56.每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

SELECT job_id,MAX(salary) m

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING m>6000

ORDER BY m ;

-- 57.查询每个工种每个部门的最低工资,并按最低工资降序

SELECT MIN(salary),job_id,department_id

FROM employees

GROUP BY department_id,job_id

ORDER BY MIN(salary) DESC;

-- 58.姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM employees;

-- 59.查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期

FROM employees

WHERE commission_pct IS NOT NULL;

/*60.

查询员工的工资，要求

部门号=30，显示的工资为1.1倍

部门号=40，显示的工资为1.2倍

部门号=50，显示的工资为1.3倍

其他部门，显示的工资为原工资

SELECT salary 原始工资,department_id,

CASE department_id

WHEN 30 THEN salary*1.1

WHEN 40 THEN salary*1.2

WHEN 50 THEN salary*1.3

ELSE salary

END AS 新工资

FROM employees;

/*61.

查询员工的工资的情况

如果工资>20000,显示A级别

如果工资>15000,显示B级别

如果工资>10000，显示C级别

否则，显示D级别

SELECT salary,

CASE

WHEN salary>20000 THEN 'A'

WHEN salary>15000 THEN 'B'

WHEN salary>10000 THEN 'C'

ELSE 'D'

END AS 工资级别

FROM employees;

-- 62.查询每个部门的员工个数

SELECT COUNT(*) FROM employees WHERE department_id=90;

-- 63.查询每个工种的员工平均工资

SELECT AVG(salary),job_id

FROM employees

GROUP BY job_id;

-- 64.查询每个位置的部门个数

SELECT COUNT(*),location_id

FROM departments

GROUP BY location_id;

-- 65.查询邮箱中包含a字符的每个部门的最高工资

SELECT MAX(salary),department_id

FROM employees

WHERE email LIKE '%a%'

GROUP BY department_id;

-- 66.每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING MAX(salary)>12000;

-- 67.领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

SELECT manager_id,MIN(salary)

FROM employees

GROUP BY manager_id

HAVING MIN(salary)>5000;

-- 68.每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

SELECT job_id,MAX(salary) m

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING m>6000

ORDER BY m ;

-- 69.查询每个工种每个部门的最低工资,并按最低工资降序

SELECT MIN(salary),job_id,department_id

FROM employees

GROUP BY department_id,job_id

ORDER BY MIN(salary) DESC;

-- 70查询员工名和对应的部门名

SELECT last_name,department_name FROM employees,departments

WHERE employees.department_id = departments.department_id;

-- 71.查询员工名、工种号、工种名

SELECT last_name,j.job_id,job_title

FROM employees e , jobs j

WHERE e.job_id = j.job_id;

-- 72.查询有奖金的员工名、部门名

SELECT last_name,department_name,commission_pct

FROM employees e,departments d

WHERE e.department_id = d.department_id AND commission_pct IS NOT NULL;

-- 73.查询城市名中第二个字符为0的部门名和城市名

SELECT department_name,city

FROM departments d,locations l

WHERE d.location_id = l.location_id AND city LIKE '_o%' ;

-- 74.查询每个城市部门的个数

SELECT COUNT(*),city FROM departments d,locations l

WHERE d.location_id = l.location_id

GROUP BY city;

-- 75.查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name,d.manager_id,MIN(salary)

FROM employees e ,departments d

WHERE e.department_id = d.department_id

AND commission_pct IS NOT NULL

GROUP BY department_name,d.manager_id;

-- 76.查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*)

FROM employees e,jobs j

WHERE e.job_id = j.job_id

GROUP BY job_title

ORDER BY COUNT(*) DESC;

-- 77.查询员工名、部门名和所在的城市

SELECT last_name,department_name,city

FROM employees e,departments d ,locations l

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id;

-- 78.查询员工的工资和工资级别,并且工资级别为'D'

SELECT salary,grade_level

FROM employees e,job_grades j

WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`

AND j.grade_level='D';

-- 79.查询工资级别的个数>20的个数，并且按工资级别降序

SELECT grade_level,COUNT(*)

FROM employees e,job_grades j

WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`

GROUP BY grade_level

HAVING COUNT(*)>20

ORDER BY grade_level DESC;

-- 80.查询员工名和上级的名称

SELECT e.last_name,m.last_name

FROM employees e,employees m

WHERE e.manager_id = m.`employee_id`;

-- 81.显示所有员工的姓名，部门号和部门名称。

SELECT last_name,d.department_id,department_name

FROM employees e,departments d

WHERE e.`department_id` = d.`department_id`;

-- 82.查询90号部门员工的job_id和90号部门的location_id

SELECT job_id,location_id

FROM employees e,departments d

WHERE e.`department_id`=d.`department_id`

AND e.`department_id`=90;

-- 83.选择所有有奖金的员工的last_name , department_name , location_id , city

SELECT last_name , department_name , l.location_id , city

FROM employees e,departments d,locations l

WHERE e.department_id = d.department_id

AND d.location_id=l.location_id

AND e.commission_pct IS NOT NULL;

-- 84.选择city在Toronto工作的员工的last_name , job_id , department_id , department_name

SELECT last_name , job_id , d.department_id , department_name

FROM employees e,departments d ,locations l

WHERE e.department_id = d.department_id

AND d.location_id=l.location_id

AND city = 'Toronto';

-- 85.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT department_name,job_title,MIN(salary) 最低工资

FROM employees e,departments d,jobs j

WHERE e.`department_id`=d.`department_id`

AND e.`job_id`=j.`job_id`

GROUP BY department_name,job_title;

-- 86.查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) 部门个数

FROM departments d,locations l

WHERE d.`location_id`=l.`location_id`

GROUP BY country_id

HAVING 部门个数>2;

/*87.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees Emp# manager Mgr#

kochhar 101 king 100

SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"

FROM employees e,employees m

WHERE e.manager_id = m.employee_id

AND e.last_name='kochhar';

-- 88.查询名字中包含e的员工名和工种名（添加筛选）

SELECT last_name,job_title

FROM employees e

INNER JOIN jobs j

ON e.`job_id`= j.`job_id`

WHERE e.`last_name` LIKE '%e%';

-- 89.查询部门个数>3的城市名和部门个数

SELECT city,COUNT(*) 部门个数

FROM departments d

INNER JOIN locations l

ON d.`location_id`=l.`location_id`

GROUP BY city

HAVING COUNT(*)>3;

-- 90.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序

SELECT COUNT(*) 个数,department_name

FROM employees e

INNER JOIN departments d

ON e.`department_id`=d.`department_id`

GROUP BY department_name

HAVING COUNT(*)>3

ORDER BY COUNT(*) DESC;

-- 91.查询员工名、部门名、工种名，并按部门名降序

SELECT last_name,department_name,job_title

FROM employees e

INNER JOIN departments d ON e.`department_id`=d.`department_id`

INNER JOIN jobs j ON e.`job_id` = j.`job_id`

ORDER BY department_name DESC;

-- 92.查询员工的工资级别

SELECT salary,grade_level

FROM employees e INNER JOIN job_grades j

ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

-- 93.查询每个工资级别的个数>2的个数，并且按工资级别降序

SELECT salary, grade_level,COUNT(*) 个数

FROM employees e JOIN job_grades g

ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`

GROUP BY salary, grade_level

HAVING COUNT(*)>2

ORDER BY grade_level DESC ;

-- 94.查询姓名中包含字符k员工的名字、上级的名字

SELECT e.last_name,m.last_name

FROM employees e JOIN employees m

ON e.`manager_id` = m.`employee_id`

WHERE e.`last_name` LIKE '%k%';

-- 95.谁的工资比Abel高？

SELECT last_name,salary

FROM employees

WHERE salary>(

SELECT salary

FROM employees

WHERE last_name = LOWER('abel')

);

-- 96.返回job_id 与141 号员工相同，salary 比143 号员工多的员工姓名，job_id 和工资

SELECT last_name,job_id,salary

FROM employees WHERE job_id=

(SELECT job_id FROM employees WHERE employee_id=141)

AND salary>(SELECT salary FROM employees WHERE employee_id = 143);

-- 97.返回公司工资最少的员工的last_name,job_id 和salary

SELECT last_name,job_id,salary

FROM employees WHERE salary =

(SELECT MIN(salary) FROM employees);

-- 98.查询最低工资大于50 号部门最低工资的部门id 和其最低工资

SELECT department_id,MIN(salary)

FROM employees

GROUP BY department_id

HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=50);

-- 99.返回location_id 是1400 或1700 的部门中的所有员工姓名

SELECT last_name

FROM employees

WHERE department_id IN (SELECT department_id FROM departments WHERE location_id IN(1400,1700));

/*100.

返回其它工种中比job_id 为‘IT_PROG’ 工种任一工资低的员工的员

工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary

FROM employees WHERE salary < ANY (SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG' )

AND job_id !='IT_PROG';

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary < ANY

(SELECT salary

FROM employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

/*101.

返回其它工种中比job_id 为‘IT_PROG’ 工种所有工资都低的员工

的员工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary

FROM employees WHERE salary< ALL(SELECT salary FROM employees WHERE job_id='IT_PROG')

AND job_id <> 'IT_PROG';

-- 102.查询每个部门的员工的个数

SELECT d.department_id,

(SELECT COUNT(*)

FROM employees e WHERE e.department_id = d.`department_id`) 个数

FROM departments d;

-- 103.查询和Zlotkey相同部门的员工姓名和工资

SELECT last_name,salary

FROM employees

WHERE department_id = (SELECT department_id

FROM employees WHERE last_name='Zlotkey');

-- 104.查询工资比公司平均工资高的员工的员工号，姓名和工资。

SELECT employee_id,last_name,salary

FROM employees WHERE salary > (

SELECT AVG(salary) FROM employees

);

-- 105查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

SELECT employee_id,last_name,e.salary

FROM employees e JOIN

(SELECT department_id,AVG(salary) ag

FROM employees

GROUP BY department_id) dg ON

e.`department_id` = dg.department_id

WHERE e.`salary`>dg.ag;

-- 106.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT last_name,employee_id

FROM employees

WHERE department_id IN(

SELECT DISTINCT department_id

FROM employees

WHERE last_name LIKE '%u%'

);

SELECT employee_id

FROM employees

WHERE department_id =ANY(

SELECT DISTINCT department_id

FROM departments

WHERE location_id = 1700

);

点赞收藏不迷路，更多精彩请关注#蝌蚪成长记#，敬请期待！

本文地址：http://www.longkongtuishu.com/ca8d7BARsBFcEDVZc.html

标签： #mysql转义字符escape #mysql取值范围199