MySql基础之面试查询语句

2025-07-22 02:47:20

MySql基础之面试查询语句一、子查询准备数据代码语言：javascript代码运行次数：0运行复制CREATE DATABASE /*!212 IF OT EXISTS*/`atguigudb` /*! DEFAULT CHARACTER SET utf8 */; USE `atguigudb`; /*Table structure for table `countries

MySql基础之面试查询语句

一、子查询

准备数据

代码语言：javascript代码运行次数：0运行复制

CREATE DATABASE /*!212 IF OT EXISTS*/`atguigudb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `atguigudb`;

/*Table structure for table `countries` */

DROP TABLE IF EXISTS `countries`;

CREATE TABLE `countries` (
  `country_id` char(2) OT ULL,
  `country_name` varchar(40) DEFAULT ULL,
  `region_id` int(11) DEFAULT ULL,
  PRIMARY KEY (`country_id`),
  KEY `countr_reg_fk` (`region_id`),
  COSTRAIT `countr_reg_fk` FOREIG KEY (`region_id`) REFERECES `regi` (`region_id`)
) EGIE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `countries` */

insert  into `countries`(`country_id`,`country_name`,`region_id`) values ('AR','Argentina',2),('AU','Australia',),('BE','Belgium',1),('BR','Brazil',2),('CA','Canada',2),('CH','Switzerland',1),('C','China',),('DE','Germany',1),('DK','Denmark',1),('EG','Egypt',4),('FR','France',1),('HK','HongKong',),('IL','Israel',4),('I','India',),('IT','Italy',1),('JP','Japan',),('KW','Kuwait',4),('MX','Mexico',2),('G','igeria',4),('L','etherlands',1),('SG','Singapore',),('UK','United Kingdom',1),('US','United States of America',2),('ZM','Zambia',4),('ZW','Zimbabwe',4);

/*Table structure for table `departments` */

DROP TABLE IF EXISTS `departments`;

CREATE TABLE `departments` (
  `department_id` int(4) OT ULL DEFAULT '0',
  `department_name` varchar(0) OT ULL,
  `manager_id` int(6) DEFAULT ULL,
  `location_id` int(4) DEFAULT ULL,
  PRIMARY KEY (`department_id`),
  UIQUE KEY `dept_id_pk` (`department_id`),
  KEY `dept_loc_fk` (`location_id`),
  KEY `dept_mgr_fk` (`manager_id`),
  COSTRAIT `dept_loc_fk` FOREIG KEY (`location_id`) REFERECES `locati` (`location_id`),
  COSTRAIT `dept_mgr_fk` FOREIG KEY (`manager_id`) REFERECES `employees` (`employee_id`)
) EGIE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `departments` */

insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Administration',200,1700),(20,'Marketing',201,1800),(0,'Purchasing',114,1700),(40,'Human Resources',20,2400),(50,'Shipping',121,1500),(60,'IT',10,1400),(70,'Public Relati',204,2700),(80,'Sales',145,2500),(90,'Executive',100,1700),(100,'Finance',108,1700),(110,'Accounting',205,1700),(120,'Treasury',ULL,1700),(10,'Corporate Tax',ULL,1700),(140,'Control And Credit',ULL,1700),(150,'Shareholder Services',ULL,1700),(160,'Benefits',ULL,1700),(170,'Manufacturing',ULL,1700),(180,'Ctruction',ULL,1700),(190,'Contracting',ULL,1700),(200,'Operati',ULL,1700),(210,'IT Support',ULL,1700),(220,'OC',ULL,1700),(20,'IT Helpdesk',ULL,1700),(240,'Government Sales',ULL,1700),(250,'Retail Sales',ULL,1700),(260,'Recruiting',ULL,1700),(270,'Payroll',ULL,1700);

/*Table structure for table `employees` */

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `employee_id` int(6) OT ULL DEFAULT '0',
  `first_name` varchar(20) DEFAULT ULL,
  `last_name` varchar(25) OT ULL,
  `email` varchar(25) OT ULL,
  `phone_number` varchar(20) DEFAULT ULL,
  `hire_date` date OT ULL,
  `job_id` varchar(10) OT ULL,
  `salary` double(8,2) DEFAULT ULL,
  `commission_pct` double(2,2) DEFAULT ULL,
  `manager_id` int(6) DEFAULT ULL,
  `department_id` int(4) DEFAULT ULL,
  PRIMARY KEY (`employee_id`),
  UIQUE KEY `emp_email_uk` (`email`),
  UIQUE KEY `emp_emp_id_pk` (`employee_id`),
  KEY `emp_dept_fk` (`department_id`),
  KEY `emp_job_fk` (`job_id`),
  KEY `emp_manager_fk` (`manager_id`),
  COSTRAIT `emp_dept_fk` FOREIG KEY (`department_id`) REFERECES `departments` (`department_id`),
  COSTRAIT `emp_job_fk` FOREIG KEY (`job_id`) REFERECES `jobs` (`job_id`),
  COSTRAIT `emp_manager_fk` FOREIG KEY (`manager_id`) REFERECES `employees` (`employee_id`)
) EGIE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `employees` */

/*
* 提示：该行代码过长，系统自动注释不进行高亮。一键复制会移除系统注释 
* insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`hire_date`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`) values (100,'Steven','King','SKIG','515.12.4567','1987-06-17','AD_PRES',24000.00,ULL,ULL,90),(101,'eena','Kochhar','KOCHHAR','515.12.4568','1989-09-21','AD_VP',17000.00,ULL,100,90),(102,'Lex','De Haan','LDEHAA','515.12.4569','199-01-1','AD_VP',17000.00,ULL,100,90),(10,'Alexander','Hunold','AHUOLD','590.42.4567','1990-01-0','IT_PROG',9000.00,ULL,102,60),(104,'Bruce','Ernst','BERST','590.42.4568','1991-05-21','IT_PROG',6000.00,ULL,10,60),(105,'David','Austin','DAUSTI','590.42.4569','1997-06-25','IT_PROG',4800.00,ULL,10,60),(106,'Valli','Pataballa','VPATABAL','590.42.4560','1998-02-05','IT_PROG',4800.00,ULL,10,60),(107,'Diana','Lorentz','DLORETZ','590.42.5567','1999-02-07','IT_PROG',4200.00,ULL,10,60),(108,'ancy','Greenberg','GREEBE','515.124.4569','1994-08-17','FI_MGR',12000.00,ULL,101,100),(109,'Daniel','Faviet','DFAVIET','515.124.4169','1994-08-16','FI_ACCOUT',9000.00,ULL,108,100),(110,'John','Chen','JCHE','515.124.4269','1997-09-28','FI_ACCOUT',8200.00,ULL,108,100),(111,'Ismael','Sciarra','ISCIARRA','515.124.469','1997-09-0','FI_ACCOUT',7700.00,ULL,108,100),(112,'Jose Manuel','Urman','JMURMA','515.124.4469','1998-0-07','FI_ACCOUT',7800.00,ULL,108,100),(11,'Luis','Popp','LPOPP','515.124.4567','1999-12-07','FI_ACCOUT',6900.00,ULL,108,100),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','1994-12-07','PU_MA',11000.00,ULL,100,0),(115,'Alexander','Khoo','AKHOO','515.127.4562','1995-05-18','PU_CLERK',100.00,ULL,114,0),(116,'Shelli','Baida','SBAIDA','515.127.456','1997-12-24','PU_CLERK',2900.00,ULL,114,0),(117,'Sigal','Tobias','STOBIAS','515.127.4564','1997-07-24','PU_CLERK',2800.00,ULL,114,0),(118,'Guy','Himuro','GHIMURO','515.127.4565','1998-11-15','PU_CLERK',2600.00,ULL,114,0),(119,'Karen','Colmenares','KCOLMEA','515.127.4566','1999-08-10','PU_CLERK',2500.00,ULL,114,0),(120,'Matthew','Weiss','MWEISS','650.12.124','1996-07-18','ST_MA',8000.00,ULL,100,50),(121,'Adam','Fripp','AFRIPP','650.12.224','1997-04-10','ST_MA',8200.00,ULL,100,50),(122,'Payam','Kaufling','PKAUFLI','650.12.24','1995-05-01','ST_MA',7900.00,ULL,100,50),(12,'Shanta','Vollman','SVOLLMA','650.12.424','1997-10-10','ST_MA',6500.00,ULL,100,50),(124,'Kevin','Mourgos','KMOURGOS','650.12.524','1999-11-16','ST_MA',5800.00,ULL,100,50),(125,'Julia','ayer','JAYER','650.124.1214','1997-07-16','ST_CLERK',200.00,ULL,120,50),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','1998-09-28','ST_CLERK',2700.00,ULL,120,50),(127,'James','Landry','JLADRY','650.124.14','1999-01-14','ST_CLERK',2400.00,ULL,120,50),(128,'Steven','Markle','SMARKLE','650.124.144','2000-0-08','ST_CLERK',2200.00,ULL,120,50),(129,'Laura','Bissot','LBISSOT','650.124.524','1997-08-20','ST_CLERK',00.00,ULL,121,50),(10,'Mozhe','Atkinson','MATKISO','650.124.624','1997-10-0','ST_CLERK',2800.00,ULL,121,50),(11,'James','Marlow','JAMRLOW','650.124.724','1997-02-16','ST_CLERK',2500.00,ULL,121,50),(12,'TJ','Olson','TJOLSO','650.124.824','1999-04-10','ST_CLERK',2100.00,ULL,121,50),(1,'Jason','Mallin','JMALLI','650.127.194','1996-06-14','ST_CLERK',00.00,ULL,122,50),(14,'Michael','Rogers','MROGERS','650.127.184','1998-08-26','ST_CLERK',2900.00,ULL,122,50),(15,'Ki','Gee','KGEE','650.127.174','1999-12-12','ST_CLERK',2400.00,ULL,122,50),(16,'Hazel','Philtanker','HPHILTA','650.127.164','2000-02-06','ST_CLERK',2200.00,ULL,122,50),(17,'Renske','Ladwig','RLADWIG','650.121.124','1995-07-14','ST_CLERK',600.00,ULL,12,50),(18,'Stephen','Stiles','SSTILES','650.121.204','1997-10-26','ST_CLERK',200.00,ULL,12,50),(19,'John','Seo','JSEO','650.121.2019','1998-02-12','ST_CLERK',2700.00,ULL,12,50),(140,'Joshua','Patel','JPATEL','650.121.184','1998-04-06','ST_CLERK',2500.00,ULL,12,50),(141,'Trenna','Rajs','TRAJS','650.121.8009','1995-10-17','ST_CLERK',500.00,ULL,124,50),(142,'Curtis','Davies','CDAVIES','650.121.2994','1997-01-29','ST_CLERK',100.00,ULL,124,50),(14,'Randall','Matos','RMATOS','650.121.2874','1998-0-15','ST_CLERK',2600.00,ULL,124,50),(144,'Peter','Vargas','PVARGAS','650.121.2004','1998-07-09','ST_CLERK',2500.00,ULL,124,50),(145,'John','Russell','JRUSSEL','011.44.144.429268','1996-10-01','SA_MA',14000.00,0.40,100,80),(146,'Karen','Partners','KPARTER','011.44.144.467268','1997-01-05','SA_MA',1500.00,0.0,100,80),(147,'Alberto','Errazuriz','AERRAZUR','011.44.144.429278','1997-0-10','SA_MA',12000.00,0.0,100,80),(148,'Gerald','Cambrault','GCAMBRAU','011.44.144.619268','1999-10-15','SA_MA',11000.00,0.0,100,80),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.144.429018','2000-01-29','SA_MA',10500.00,0.20,100,80),(150,'Peter','Tucker','PTUCKER','011.44.144.129268','1997-01-0','SA_REP',10000.00,0.0,145,80),(151,'David','Bernstein','DBERSTE','011.44.144.45268','1997-0-24','SA_REP',9500.00,0.25,145,80),(152,'Peter','Hall','PHALL','011.44.144.478968','1997-08-20','SA_REP',9000.00,0.25,145,80),(15,'Christopher','Olsen','COLSE','011.44.144.498718','1998-0-0','SA_REP',8000.00,0.20,145,80),(154,'anette','Cambrault','CAMBRAU','011.44.144.987668','1998-12-09','SA_REP',7500.00,0.20,145,80),(155,'Oliver','Tuvault','OTUVAULT','011.44.144.486508','1999-11-2','SA_REP',7000.00,0.15,145,80),(156,'Janette','King','JKIG','011.44.145.429268','1996-01-0','SA_REP',10000.00,0.5,146,80),(157,'Patrick','Sully','PSULLY','011.44.145.929268','1996-0-04','SA_REP',9500.00,0.5,146,80),(158,'Allan','McEwen','AMCEWE','011.44.145.829268','1996-08-01','SA_REP',9000.00,0.5,146,80),(159,'Lindsey','Smith','LSMITH','011.44.145.729268','1997-0-10','SA_REP',8000.00,0.0,146,80),(160,'Louise','Doran','LDORA','011.44.145.629268','1997-12-15','SA_REP',7500.00,0.0,146,80),(161,'Sarath','Sewall','SSEWALL','011.44.145.529268','1998-11-0','SA_REP',7000.00,0.25,146,80),(162,'Clara','Vishney','CVISHEY','011.44.146.129268','1997-11-11','SA_REP',10500.00,0.25,147,80),(16,'Danielle','Greene','DGREEE','011.44.146.229268','1999-0-19','SA_REP',9500.00,0.15,147,80),(164,'Mattea','Marvins','MMARVIS','011.44.146.29268','2000-01-24','SA_REP',7200.00,0.10,147,80),(165,'David','Lee','DLEE','011.44.146.529268','2000-02-2','SA_REP',6800.00,0.10,147,80),(166,'Sundar','Ande','SADE','011.44.146.629268','2000-0-24','SA_REP',6400.00,0.10,147,80),(167,'Amit','Banda','ABADA','011.44.146.729268','2000-04-21','SA_REP',6200.00,0.10,147,80),(168,'Lisa','Ozer','LOZER','011.44.14.929268','1997-0-11','SA_REP',11500.00,0.25,148,80),(169,'Harrison','Bloom','HBLOOM','011.44.14.829268','1998-0-2','SA_REP',10000.00,0.20,148,80),(170,'Tayler','Fox','TFOX','011.44.14.729268','1998-01-24','SA_REP',9600.00,0.20,148,80),(171,'William','Smith','WSMITH','011.44.14.629268','1999-02-2','SA_REP',7400.00,0.15,148,80),(172,'Elizabeth','Bates','EBATES','011.44.14.529268','1999-0-24','SA_REP',700.00,0.15,148,80),(17,'Sundita','Kumar','SKUMAR','011.44.14.29268','2000-04-21','SA_REP',6100.00,0.10,148,80),(174,'Ellen','Abel','EABEL','011.44.1644.429267','1996-05-11','SA_REP',11000.00,0.0,149,80),(175,'Alyssa','Hutton','AHUTTO','011.44.1644.429266','1997-0-19','SA_REP',8800.00,0.25,149,80),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','1998-0-24','SA_REP',8600.00,0.20,149,80),(177,'Jack','Livingston','JLIVIGS','011.44.1644.429264','1998-04-2','SA_REP',8400.00,0.20,149,80),(178,'Kimberely','Grant','KGRAT','011.44.1644.42926','1999-05-24','SA_REP',7000.00,0.15,149,ULL),(179,'Charles','Johnson','CJOHSO','011.44.1644.429262','2000-01-04','SA_REP',6200.00,0.10,149,80),(180,'Winston','Taylor','WTAYLOR','650.507.9876','1998-01-24','SH_CLERK',200.00,ULL,120,50),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','1998-02-2','SH_CLERK',100.00,ULL,120,50),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','1999-06-21','SH_CLERK',2500.00,ULL,120,50),(18,'Girard','Geoni','GGEOI','650.507.9879','2000-02-0','SH_CLERK',2800.00,ULL,120,50),(184,'andita','Sarchand','SARCHA','650.509.1876','1996-01-27','SH_CLERK',4200.00,ULL,121,50),(185,'Alexis','Bull','ABULL','650.509.2876','1997-02-20','SH_CLERK',4100.00,ULL,121,50),(186,'Julia','Dellinger','JDELLIG','650.509.876','1998-06-24','SH_CLERK',400.00,ULL,121,50),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','1999-02-07','SH_CLERK',000.00,ULL,121,50),(188,'Kelly','Chung','KCHUG','650.505.1876','1997-06-14','SH_CLERK',800.00,ULL,122,50),(189,'Jennifer','Dilly','JDILLY','650.505.2876','1997-08-1','SH_CLERK',600.00,ULL,122,50),(190,'Timothy','Gates','TGATES','650.505.876','1998-07-11','SH_CLERK',2900.00,ULL,122,50),(191,'Randall','Perkins','RPERKIS','650.505.4876','1999-12-19','SH_CLERK',2500.00,ULL,122,50),(192,'Sarah','Bell','SBELL','650.501.1876','1996-02-04','SH_CLERK',4000.00,ULL,12,50),(19,'Britney','Everett','BEVERETT','650.501.2876','1997-0-0','SH_CLERK',900.00,ULL,12,50),(194,'Samuel','McCain','SMCCAI','650.501.876','1998-07-01','SH_CLERK',200.00,ULL,12,50),(195,'Vance','Jones','VJOES','650.501.4876','1999-0-17','SH_CLERK',2800.00,ULL,12,50),(196,'Alana','Walsh','AWALSH','650.507.9811','1998-04-24','SH_CLERK',100.00,ULL,124,50),(197,'Kevin','Feeney','KFEEEY','650.507.9822','1998-05-2','SH_CLERK',000.00,ULL,124,50),(198,'Donald','OConnell','DOCOEL','650.507.98','1999-06-21','SH_CLERK',2600.00,ULL,124,50),(199,'Douglas','Grant','DGRAT','650.507.9844','2000-01-1','SH_CLERK',2600.00,ULL,124,50),(200,'Jennifer','Whalen','JWHALE','515.12.4444','1987-09-17','AD_ASST',4400.00,ULL,101,10),(201,'Michael','Hartstein','MHARTSTE','515.12.5555','1996-02-17','MK_MA',1000.00,ULL,100,20),(202,'Pat','Fay','PFAY','60.12.6666','1997-08-17','MK_REP',6000.00,ULL,201,20),(20,'Susan','Mavris','SMAVRIS','515.12.7777','1994-06-07','HR_REP',6500.00,ULL,101,40),(204,'Hermann','Baer','HBAER','515.12.8888','1994-06-07','PR_REP',10000.00,ULL,101,70),(205,'Shelley','Higgins','SHIGGIS','515.12.8080','1994-06-07','AC_MGR',12000.00,ULL,101,110),(206,'William','Gietz','WGIETZ','515.12.8181','1994-06-07','AC_ACCOUT',800.00,ULL,205,110);
*/

/*Table structure for table `job_grades` */

DROP TABLE IF EXISTS `job_grades`;

CREATE TABLE `job_grades` (
  `grade_level` varchar() DEFAULT ULL,
  `lowest_sal` int(11) DEFAULT ULL,
  `highest_sal` int(11) DEFAULT ULL
) EGIE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `job_grades` */

insert  into `job_grades`(`grade_level`,`lowest_sal`,`highest_sal`) values ('A',1000,2999),('B',000,5999),('C',6000,9999),('D',10000,14999),('E',15000,24999),('F',25000,40000);

/*Table structure for table `job_history` */

DROP TABLE IF EXISTS `job_history`;

CREATE TABLE `job_history` (
  `employee_id` int(6) OT ULL,
  `start_date` date OT ULL,
  `end_date` date OT ULL,
  `job_id` varchar(10) OT ULL,
  `department_id` int(4) DEFAULT ULL,
  PRIMARY KEY (`employee_id`,`start_date`),
  UIQUE KEY `jhist_emp_id_st_date_pk` (`employee_id`,`start_date`),
  KEY `jhist_job_fk` (`job_id`),
  KEY `jhist_dept_fk` (`department_id`),
  COSTRAIT `jhist_dept_fk` FOREIG KEY (`department_id`) REFERECES `departments` (`department_id`),
  COSTRAIT `jhist_emp_fk` FOREIG KEY (`employee_id`) REFERECES `employees` (`employee_id`),
  COSTRAIT `jhist_job_fk` FOREIG KEY (`job_id`) REFERECES `jobs` (`job_id`)
) EGIE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `job_history` */

insert  into `job_history`(`employee_id`,`start_date`,`end_date`,`job_id`,`department_id`) values (101,'1989-09-21','199-10-27','AC_ACCOUT',110),(101,'199-10-28','1997-0-15','AC_MGR',110),(102,'199-01-1','1998-07-24','IT_PROG',60),(114,'1998-0-24','1999-12-1','ST_CLERK',50),(122,'1999-01-01','1999-12-1','ST_CLERK',50),(176,'1998-0-24','1998-12-1','SA_REP',80),(176,'1999-01-01','1999-12-1','SA_MA',80),(200,'1987-09-17','199-06-17','AD_ASST',90),(200,'1994-07-01','1998-12-1','AC_ACCOUT',90),(201,'1996-02-17','1999-12-19','MK_REP',20);

/*Table structure for table `jobs` */

DROP TABLE IF EXISTS `jobs`;

CREATE TABLE `jobs` (
  `job_id` varchar(10) OT ULL DEFAULT '',
  `job_title` varchar(5) OT ULL,
  `min_salary` int(6) DEFAULT ULL,
  `max_salary` int(6) DEFAULT ULL,
  PRIMARY KEY (`job_id`),
  UIQUE KEY `job_id_pk` (`job_id`)
) EGIE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `jobs` */

insert  into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,0000),('FI_ACCOUT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MA','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relati Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MA','Purchasing Manager',8000,15000),('SA_MA','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MA','Stock Manager',5500,8500);

/*Table structure for table `locati` */

DROP TABLE IF EXISTS `locati`;

CREATE TABLE `locati` (
  `location_id` int(4) OT ULL DEFAULT '0',
  `street_address` varchar(40) DEFAULT ULL,
  `postal_code` varchar(12) DEFAULT ULL,
  `city` varchar(0) OT ULL,
  `state_province` varchar(25) DEFAULT ULL,
  `country_id` char(2) DEFAULT ULL,
  PRIMARY KEY (`location_id`),
  UIQUE KEY `loc_id_pk` (`location_id`),
  KEY `loc_c_id_fk` (`country_id`),
  COSTRAIT `loc_c_id_fk` FOREIG KEY (`country_id`) REFERECES `countries` (`country_id`)
) EGIE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `locati` */

insert  into `locati`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',ULL,'IT'),(1100,'9091 Calle della Testa','1094','Venice',ULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(100,'9450 Kamiya-cho','682','Hiroshima',ULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','9926','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','ew 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',ULL,'C'),(2100,'1298 Vileparle (E)','49021','Bombay','Maharashtra','I'),(2200,'12-98 Victoria Street','2901','Sydney','ew South Wales','AU'),(200,'198 Clementi orth','540198','Singapore',ULL,'SG'),(2400,'8204 Arthur St',ULL,'London',ULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','0962985029','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 701','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 160 ','0107-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','170','Geneva','Geneve','CH'),(000,'Murtenstrasse 921','095','Bern','BE','CH'),(100,'Pieter Breughelstraat 87','029SK','Utrecht','Utrecht','L'),(200,'Mariano Escobedo 9991','1192','Mexico City','Distrito Federal,','MX');

1、查询和Zlotkey相同部门的员工姓名和工资

代码语言：javascript代码运行次数：0运行复制

SELECT last_name, salary 
FROM employees 
WHERE department_id = ( 
	SELECT department_id 
	FROM employees 
	WHERE last_name = 'Zlotkey' 
	)

2、查询工资比公司平均工资高的员工的员工号，姓名和工资

代码语言：javascript代码运行次数：0运行复制

select _id ,e.last_name ,e.salary
from employees e
where e.salary > (
	select avg(e2.salary) 
	from employees e2
	)

、选择工资大于所有JOB_ID = 'SA_MA’的员工的工资的员工的last_name, job_id, salary

方法一：

代码语言：javascript代码运行次数：0运行复制

select last_name, job_id, salary 
from employees e where e.salary > (
	select max(salary) 
	from employees e2 
	where e2.job_id = 'SA_MA'
	)

方法二：all方式

代码语言：javascript代码运行次数：0运行复制

select last_name, job_id, salary 
from employees e 
where e.salary > all (
	select e2.salary 
	from employees e2 
	where e2.job_id = 'SA_MA'
	)

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

方法一：in方式

代码语言：javascript代码运行次数：0运行复制

select _id ,e.last_name 
from employees e 
where e.department_id in (
	select e2.department_id 
	from employees e2 
	where e2.last_name like '%u%'
	)

方法二：any方法

代码语言：javascript代码运行次数：0运行复制

SELECT employee_id, last_name 
FROM employees 
WHERE department_id = AY( 
	SELECT DISTICT department_id 
	FROM employees 
	WHERE last_name 
	LIKE '%u%' 
	)

5、查询管理者是King的员工姓名和工资

方式一：自连接

代码语言：javascript代码运行次数：0运行复制

select e1.last_name ,e1.salary 
from employees e1 , employees e2
where _id = _id 
and e2.last_name = 'King'

方式二：in方式

代码语言：javascript代码运行次数：0运行复制

select e1.last_name ,e1.salary 
from employees e1 
where _id in (
	select _id 
	from employees e 
	where e.last_name = 'King'
	)

方式二：exists方式

代码语言：javascript代码运行次数：0运行复制

select e1.last_name ,e1.salary 
from employees e1 
where exists (
	select 1 
	from employees e 
	where e.last_name = 'King' 
	and _id = _id 
	)

6、查询平均工资最低的部门信息

方式一：

代码语言：javascript代码运行次数：0运行复制

# 1.先查询部门平均工资最低的工资
# 2.根据部门分组，having = 最低工资
select d.* 
from employees e2,departments d 
where e2.department_id = d.department_id 
group by e2.department_id 
having avg(e2.salary) =  (
	select min(t1.avg_salary)
	from ( 
		select avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
		) t1
	)

方式二：all方式

代码语言：javascript代码运行次数：0运行复制

select d.* 
from employees e2,departments d 
where e2.department_id = d.department_id 
group by e2.department_id 
having avg(e2.salary) <= all(
		select avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
	)

方式三：limit方式

代码语言：javascript代码运行次数：0运行复制

select d.* 
from employees e2,departments d 
where e2.department_id = d.department_id 
group by e2.department_id 
having avg(e2.salary) = (
		select avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
		order by avg_salary asc
		limit 1
	)

方式四：临时表方式

代码语言：javascript代码运行次数：0运行复制

select d.* 
from departments d ,(
		select e.department_id,avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
		order by avg_salary asc
		limit 1
	) t where d.department_id = t.department_id

7、查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

方式一：

代码语言：javascript代码运行次数：0运行复制

select d.*,(select avg(salary) from employees e2 where e2.department_id = d.department_id) 
from employees e2,departments d 
where e2.department_id = d.department_id 
group by e2.department_id 
having avg(e2.salary) =  (
	select min(t1.avg_salary)
	from ( 
		select avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
		) t1
	)

方式二：

代码语言：javascript代码运行次数：0运行复制

select d.*,t.avg_salary 
from departments d ,(
		select e.department_id,avg(e.salary) avg_salary 
		from employees e 
		group by e.department_id
		order by avg_salary asc
		limit 1
	) t where d.department_id = t.department_id

8、查询平均工资高于公司平均工资的部门有哪些

代码语言：javascript代码运行次数：0运行复制

select e.department_id  
from employees e 
where e.department_id  is not null
group by e.department_id 
having avg(e.salary) > (
	select avg(salary) from employees
	)

9、查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

代码语言：javascript代码运行次数：0运行复制

select last_name, department_id, email, salary 
from employees e 
where _id = (
	select manager_id 
	from departments d 
	where d.department_id = (
		select department_id
		from employees 
		group by department_id 
		order by avg(salary) desc
		limit 1
		)
	)

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

方式一：

代码语言：javascript代码运行次数：0运行复制

SELECT employee_id,last_name,salary 
FROM employees e1 
WHERE salary > ( 
	SELECT AVG(salary) 
	FROM employees e2 
	WHERE e2.department_id = e1.department_id
	)

方式二：

代码语言：javascript代码运行次数：0运行复制

select _id ,e.last_name ,e.salary 
from employees e,
(select department_id,avg(salary) avg_salary from employees group by department_id) t
where e.department_id = t.department_id
and e.salary > t.avg_salary

11、查询每个部门下的部门人数大于 5 的部门名称

方式一：

代码语言：javascript代码运行次数：0运行复制

select d.department_name,d.department_id 
from departments d 
where d.department_id in (
	select e.department_id 
	from employees e
	group by e.department_id 
	having count(*) > 5 
	)

方式二：

代码语言：javascript代码运行次数：0运行复制

SELECT department_name,department_id 
FROM departments d 
WHERE 5 < ( 
	SELECT COUT(*) 
	FROM employees e 
	WHERE d.department_id = e.department_id
	)

二、学生表、课程表、教师表

准备数据

代码语言：javascript代码运行次数：0运行复制

--创建学生信息表
CREATE TABLE Student(
Sid int,
Sname VARCHAR(10),
Sage int,
S VARCHAR(10)
);

--课程表
CREATE TABLE Course(
CId VARCHAR(10),
Cname VARCHAR(10),
TId VARCHAR(10)
);

--教师表
CREATE TABLE Teacher(
Tid VARCHAR(10),
Tname VARCHAR(10)
);

--成绩表
CREATE TABLE SC(
sid VARCHAR(10),
cid VARCHAR(10),
score DECIMAL(18,1)
);

insert into Student select 1,'刘一',18,'男' union all
 select 2,'钱二',19,'女' union all
 select ,'张三',17,'男' union all
 select 4,'李四',18,'女' union all
 select 5,'王五',17,'男' union all
 select 6,'赵六',19,'女';
 
 insert into Teacher select 1,'叶平' union all
 select 2,'贺高' union all
 select ,'杨艳' union all
 select 4,'周磊';
 
 insert into Course select 1,'语文',1 union all
 select 2,'数学',2 union all
 select ,'英语', union all
 select 4,'物理',4;
 
 insert into SC 
 select 1,1,56 union all 
 select 1,2,78 union all 
 select 1,,67 union all 
 select 1,4,58 union all 
 select 2,1,79 union all 
 select 2,2,81 union all 
 select 2,,92 union all 
 select 2,4,68 union all 
 select ,1,91 union all 
 select ,2,47 union all 
 select ,,88 union all 
 select ,4,56 union all 
 select 4,2,88 union all 
 select 4,,90 union all 
 select 4,4,9 union all 
 select 5,1,46 union all 
 select 5,,78 union all 
 select 5,4,5 union all 
 select 6,1,5 union all 
 select 6,2,68 union all 
 select 6,4,71;

1、查询“1”课程比“2”课程成绩高的所有学生的学号

方式一：

代码语言：javascript代码运行次数：0运行复制

select a.sid from sc a , sc b 
where a.sid = b.sid 
and a.score > b.score 
and  = '1'
and  = '2'

方式二：

代码语言：javascript代码运行次数：0运行复制

select a.sid from 
(select * from sc where cid = '1') a,
(select * from sc where cid = '2') b
where a.sid = b.sid 
and a.score > b.score

2、查询平均成绩大于60分的同学的学号和平均成绩

代码语言：javascript代码运行次数：0运行复制

select sid, avg(score) 
from sc 
group by sid 
having avg(score) > 60

、查询所有同学的学号、姓名、选课数、总成绩

代码语言：javascript代码运行次数：0运行复制

select s.Sid,s.Sname,count(),sum(sc.score) 
from student s left join sc sc
on s.Sid = sc.sid 
group by s.Sid,s.Sname

4、查询没学过“叶平”老师课的同学的学号、姓名

代码语言：javascript代码运行次数：0运行复制

# 1.先查询学过的学生的id
# 2.根据not in获取没学过的
select s.Sid,s.Sname 
from student s 
where s.Sid not in (
	select Sid 
	from sc 
	where cid 
	in (
		select c.CId 
		from teacher t ,course c 
		where t.Tid = c.TId 
		and t.Tname = '叶平'
		)
)

5、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名

方式一：

代码语言：javascript代码运行次数：0运行复制

select s.Sid,s.Sname 
from sc a,student s 
where  = '1' 
and a.sid = s.Sid 
and exists (
	select 1 
	from sc b 
	where  = '2' 
	and a.sid = b.sid 
)

方式二：

代码语言：javascript代码运行次数：0运行复制

select s.Sid ,s.Sname 
from sc a ,sc b,student s 
where a.sid = b.sid 
and a.sid = s.Sid 
and  = '1' 
and  = '2'

6、查询学过“叶平”老师所教的所有课的同学的学号、姓名

代码语言：javascript代码运行次数：0运行复制

# 1.查询叶平老师教的学生信息
# 2.根据学生分组查询每个人学过的数量
# .每个人数量 = 老师所教所有学科的数量
select s.Sid,s.Sname 
from student s 
where s.Sid in (	
	select s2.sid 
	from course c ,teacher t ,sc s2 
	where c.TId = t.Tid 
	and  = c.CId 
	and t.Tname = '叶平'
	group by s2.sid
	having count() = (
		select count(*) 
		from course c2,teacher t2 
		where c2.TId = t2.Tid 
		and t2.Tname = '叶平'
		)
	)

7、查询所有课程成绩小于60分的同学的学号、姓名

代码语言：javascript代码运行次数：0运行复制

# 1.查询所有成绩大于60的学习id
#  in就是所有成绩小于60的
select s.Sid,s.Sname 
from student s 
where s.Sid not in (
	select sid 
	from sc 
	where score > 60
	)

…未完待续

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。原始发表：2025-01-20，如有侵权请联系 cloudcommunity@tencent 删除基础面试mysqlnullselect

#感谢您对电脑配置推荐网 - 最新i3 i5 i7组装电脑配置单推荐报价格的认可，转载请说明来源于"电脑配置推荐网 - 最新i3 i5 i7组装电脑配置单推荐报价格

本文地址：http://www.dnpztj.cn/biancheng/1162492.html

本站网友板鞋	13分钟前发表
1000.00
本站网友黄石房产网	4分钟前发表
'1998-0-15'
本站网友昆明妇科检查	12分钟前发表
4); /Table structure for table `departments` / DROP TABLE IF EXISTS `departments`; CREATE TABLE `departments` ( `department_id` int(4) OT ULL DEFAULT '0'
本站网友齐河租房信息	26分钟前发表
'SH_CLERK'
本站网友舒马赫吧	22分钟前发表
'Popp'
本站网友西安现房	7分钟前发表
'Steven'
本站网友什么的雄鸡	6分钟前发表
'Geneva'
本站网友 hei	30分钟前发表
'011.44.14.829268'
本站网友中赫地产	16分钟前发表
(260
本站网友白云峰微博	22分钟前发表
'Payroll'
本站网友 kb是什么意思	23分钟前发表
`region_id` int(11) DEFAULT ULL
本站网友 450欧元	8分钟前发表
80)
本站网友通化电影院	22分钟前发表
'Ellen'
本站网友男科悍医	11分钟前发表
ULL
本站网友长沙资讯	7分钟前发表
9500.00
本站网友 shishangqiyi	0秒前发表
0.20
本站网友滨江花园房价	25分钟前发表
'Joshua'
本站网友 mp3录音机破解版	14分钟前发表
'Australia'

MySql基础之面试查询语句