Mysql知识点记录-SELECT

最新推荐文章于 2024-08-05 17:34:36 发布

ChengLang_111

最新推荐文章于 2024-08-05 17:34:36 发布

阅读量179

点赞数

分类专栏： mybatis 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_47299953/article/details/115218549

版权

mybatis 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

本文使用数据库：

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

一些基础

DISTINCT:去重(过滤重复数据)；

select ‘123’ + 90;
其中一方为字符型，试图将字符型数值转换成数值型类型，
如果转换成功，则继续做加法运算，如果转换失败，则将字符型那一方转换为0
只要一方为null 结果就为null
在这里插入图片描述

模糊查询：
like : 通配符：%任意多个，_任意单个
betwenn and ：在什么范围内；例： select * from saly where s betwenn 100 and 200; 就表示在100 到 200范围内
not betwenn and
in ： select * from saly where id in (1,2,3)
is null / is not null

排序：order by

asc 升序，desc 降序

SELECT * FROM `employees` ORDER BY salary DESC

order by 支持别名

SELECT salary AS 薪资 FROM `employees` ORDER BY 薪资 DESC

LENGTH()：返回长度
在这里插入图片描述
多个字段排序

SELECT employee_id,salary FROM `employees` ORDER BY salary DESC,employee_id ASC

常见函数：

大小写转换函数：upper() lower()

字符截取函数：substr() 索引从1开始，sql语言中索引都是从1开始

instr(str1,str2) 返回str2在str1中首次出现的位置

lpad() 用指定的字符实现左填充指定长度

concat(…); 字符串拼接；

replace() 替换

ifnull(arg1,arg2); arg1: 有可能为null 的字段，arg2 如果arg1为null 则显示arg2

round() 四舍五入

ceil() 向上取整
floor() 向下取整

日期处理

now() 返回当前系统日期+时间
curdate() 返回当前系统日期
curtime() 返回当前系统时间

在这里插入图片描述

datediff()

流程控制

if 等同于三元运算符
在这里插入图片描述
使用一：
case 相当于 java 种的 switch

case 要判断的字段或表达式
	when 表达式 then
		要显示的值或语句1
	when 表达式 then
		要显示的值或语句2
	end

在这里插入图片描述
使用二：
类似于Java种的多重if

  case 
	when 表达式 then #条件1
		要显示的值或语句1
	when 表达式 then #条件二
		要显示的值或语句2
	else 要显示的值或语句
	end

在这里插入图片描述

分组函数

sum: 求和（参数类型为 Number）
avg: 平均值（参数类型为 Number）

max: 最大值 / min：最小值（参数类型为Number 日期字符串）

count: 统计非空的值的个数（参数类型支持任何）

以上函数都支持搭配 distinct 使用例如 count(distinct salary ) from employee;

select count(*) from employess； – 统计所有列
select count(1) from employess； – 给加一列内容为’1‘的列然后统计这个列

效率：
MYISAM 存储引擎下，count(*） 效率高，是因为里面有一个内置的计数器
INNOB 存储引擎下，count(*) 和 count(1) 效率基本一致

分组查询

select 分组函数,列(要求出现在group by的后面)
	from 表
	【where 筛选条件】 
	group by 分组的列表
	# having 筛选条件
	【order by 子句】

注意：查询列表必须特殊，要求是分组函数的group by后出现的字段
如果要根据分组前的条件筛选，要使用where 要出现在group by 前面
如果要根据分组后的条件筛选，要使用having 要出现在group by 后面

在这里插入图片描述

按多个字段进行分组

连接查询（多表查询）

三、SQL92语法
1、等值连接
语法：

select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

内连接：

等值连接

# 查询员工姓名以及其对应的部门名字
SELECT e.`last_name` AS '员工姓名',d.`department_name` AS '所在部门' 
FROM `employees` e,`departments` d 
WHERE e.`department_id` = d.`department_id`

在这里插入图片描述


2、非等值连接
语法：
	select 查询列表
	from 表1 别名,表2 别名
	where 非等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

非等值连接
也就是多表查询中，连接条件不是使用=的查询

3、自连接

语法：

	select 查询列表
	from 表 别名1,表 别名2
	where 等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

自连接
自己连接自己

如下图，要查询第二条记录的manager的名字 ,先找到他的manager_id 为 100 然后继续在本个表中查询谁的id为100
在这里插入图片描述

sql199语法

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

连接类型可选：
内连接：inner （可以省略）
外连接：

左外:left【outer】
右外:right 【outer】
全外（mysql不支持):full 【outer】

交叉连接：cross ：也就是笛卡尔乘积

内连接：

等值连接：

# 查询员工名，以及其对应的部门名
SELECT e.`last_name`,d.`department_name`
FROM `employees` e INNER JOIN `departments` d  
ON e.`department_id` = d.`department_id`;

# 查询每个城市。部门个数>3的城市
SELECT l.`city`,COUNT(*) 
FROM `departments` d INNER JOIN `locations` l 
ON d.`location_id` = l.`location_id`
GROUP BY l.`city` 
HAVING COUNT(*)  > 3

# 查询出 部门员工个数>3的部门和员工个数，并且按个数降序
SELECT d.`department_name`,COUNT(*) AS total
FROM `employees` e 
INNER JOIN `departments` d ON
e.`department_id` = d.`department_id`
GROUP BY e.`department_id`
HAVING total > 3
ORDER BY total DESC

非等值连接

# 查询员工的工资级别
SELECT e.`last_name`, e.`salary` ,j.`grade_level`
FROM `employees` e 
JOIN `job_grades` j 
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`

自连接

## 查询出员工于员工领导的名字
SELECT e1.`last_name` AS '名字',e2.`last_name` AS '领导名字'
FROM `employees` e1 JOIN `employees` e2
ON e1.`manager_id` = e2.`department_id`

外连接：

左外:left【outer】
以左边的表为主表，，右边的表为从表，从表根据条件去匹配主表，如果匹配不到则以null的方式填充

## 查询没有员工的部门
SELECT d.*
FROM `departments` d  LEFT JOIN `employees` e
ON e.`department_id` = d.`department_id`
WHERE e.`employee_id` IS NULL;

## 查询部门名为SAL或IT的员工信息
SELECT * 
FROM `departments` d LEFT JOIN `employees` e
ON e.`department_id` = d.`department_id`
WHERE d.`department_name`  IN ('sal','it')

右外:right 【outer】
以右边的表为主表，，左边的表为从表，从表根据条件去匹配主表，如果匹配不到则以null的方式填充

子查询

出现在其他语句（不限于select中）中select语句，称为子查询或者内查询。

分类：

按子查询出现的位置：

select后面
from 后面
where或having后面（重点）
exists后面（相关子查询）

按结果集的行列数不同（了解即可）：

标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

where 或 having 后面

主查询的条件，用到了子查询的结果集
标量子查询（结果集只有一行一列）

# 谁的工资比Abel高
SELECT * 
FROM `employees` e 
WHERE e.`salary` > (SELECT salary FROM employees WHERE employees.`last_name`='Abel')

# 返回job_id 于141号员工相同并且salary比143号员工多的员工信息
SELECT *
FROM `employees` e
WHERE e.`job_id` = (
SELECT job_id FROM employees WHERE employee_id = 141) AND 
e.`salary` > (SELECT salary FROM employees WHERE employee_id=143)

#返回公司工资最少的员工的last_name,job_id和salary
SELECT * FROM employees WHERE salary  = (SELECT MIN(salary) FROM `employees`)

## 查询最低工资大于 50号部门最低工资 的部门id和其最低工资
SELECT MIN(salary),e.`department_id`
FROM `employees` e
GROUP BY e.`department_id`
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM `employees` e
	GROUP BY e.`department_id`
	HAVING e.`department_id` = 50
)

select后面的

# 查询每个部门的员工个数
SELECT *,(
	SELECT COUNT(*) FROM `employees` e 
	WHERE e.`department_id` = s.`department_id`) 个数
FROM `departments` s

from 后面的

将子查询结果充当一张表要求必须起别名

# 查询每个部门的平均工资的工资等级

#第一 先查询出每个部门的平均工资
SELECT AVG(salary),e.`department_id`
FROM `employees` e
GROUP BY e.`department_id`

#第二 把第一步的结果集和job_grades表连接
SELECT avg_dept.*,j.`grade_level`
FROM (
	SELECT AVG(salary) ag,e.`department_id`
	FROM `employees` e
	GROUP BY e.`department_id` 
) avg_dept JOIN `job_grades` j ON avg_dept.ag BETWEEN `lowest_sal` AND `highest_sal`

exists后面

SELECT EXISTS(SELECT * FROM employees WHERE employee_id = 100);


# 查询有员工的部门名称
SELECT d.`department_name`
FROM `departments` d
WHERE EXISTS(
	SELECT * FROM `employees` e WHERE e.`department_id` = d.`department_id`
)

# 查询和Zlotkey相同部门的员工姓名和工资
SELECT e1.`last_name`,e1.`salary`,e1.`department_id` FROM `employees` e1 
WHERE e1.`department_id` =(
	SELECT e.`department_id`
	FROM `employees` e
	WHERE e.`last_name` = 'Zlotkey'
)


# 查询工资比公司的平均工资高的员工号，姓名 工资
SELECT e.employee_id, e.last_name, e.salary FROM employees e
WHERE e.salary > (
	SELECT AVG(`salary`) 
	FROM `employees` 
)

# 查询各部门中工资比本部门平均工资高的员工号，姓名，和工资

# 做法1：
SELECT * FROM `employees` e1
WHERE e1.`salary` > (
	SELECT AVG(salary) avg_dept
	FROM `employees` e
	GROUP BY e.`department_id`
	HAVING e.department_id = e1.`department_id`
)

# 做法2：

SELECT e1.`department_id`,e1.`last_name`,e1.`salary`,avg_dept.ag
FROM `employees` e1
JOIN (
	SELECT AVG(salary) ag,e.`department_id`
	FROM `employees` e
	GROUP BY e.`department_id`
) avg_dept ON e1.`department_id` = avg_dept.`department_id`
WHERE e1.`salary` > avg_dept.ag

 
# 查询员工姓名包含u的员工在相同部门的员工号和姓名

SELECT e.employee_id,e.last_name FROM `employees` e
WHERE `department_id` IN (
	SELECT DISTINCT e.`department_id` FROM `employees` e
	WHERE e.last_name LIKE '%u%'
)

# 查询平均工资最低的部门的信息
#方式1：
SELECT 
  dd.*,AVG(salary)
FROM
  `employees` d JOIN `departments` dd ON d.`department_id` = dd.`department_id`
WHERE d.`department_id` = 
  (SELECT 
    e.`department_id` 
  FROM
    `employees` e 
  GROUP BY e.`department_id` 
  ORDER BY AVG(salary) ASC 
  LIMIT 1)
  
 #方式二：
SELECT ds.*
FROM `departments` ds 
JOIN (  SELECT AVG(salary) ag,e.`department_id`
  FROM `employees` e
  GROUP BY e.`department_id`
  ORDER BY ag ASC LIMIT 1) ag_dept ON ag_dept.department_id = ds.department_id

分页查询

多用于分页
分页公式要显示的页数 page 要显示的条数 size
limit (page -1)*size, size;

# 查询前五条 员工信息
SELECT * FROM `employees` LIMIT 0,5

联合查询

将多条查询语句的结果合成为一个结果一般用于将多个表中的查询结果连接在一起，多个表中查询的信息要一致
如果重复会自动去重不想去重加ALL

# 使用联合查询部门编号>90和邮箱包含a的员工信息
SELECT * FROM `employees` e WHERE e.`department_id` > 90
UNION 
SELECT * FROM employees WHERE email LIKE '%a%'

ChengLang_111

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql知识点记录-SELECT

DISTINCT:去重(过滤重复数据)；select ‘123’ + 90;其中一方为字符型，试图将字符型数值转换成数值型类型，如果转换成功，则继续做加法运算，如果转换失败，则将字符型那一方转换为0只要一方为null 结果就为null模糊查询：like : 通配符：%任意多个，_任意单个betwenn and ：在什么范围内；例： select * from saly where s betwenn 100 and 200; 就表示在100 到 200范围内not betwenn
复制链接

扫一扫

专栏目录