MySQL 基础+高级篇- 数据库 -sql -尚硅谷 89-101章 查询篇完结yangzijiangac的博客-

查询篇终于学习完了，里面涉及了一些算法和思维，应该是mysql里面特别重要的一部分，明天有时间的话开新篇章

/* 子查询 出现在其他语句中的select语句，称为子查询或内查询 分类： 按子查询出现的位置 select 后面 from 后面表子查询 where 或 having 后面标量子查询 exists 后面（相关子查询) 标量子查询(结果只有一行一列) 行子查询(一行多列） 列子查询（一列多行） 表子查询 */ /* where 或者having后面 in,some,any,all */ SELECT * FROM `employees` WHERE salary> (SELECT      salary    FROM `employees` WHERE last_name = 'Abel') ; 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) ORDER BY salary DESC; #any/some all,in,not in 实行于子查询 SELECT `department_id` FROM `departments` WHERE `location_id` IN (1400,1700); SELECT    last_name  FROM `employees` WHERE `department_id` IN (SELECT `department_id` FROM `departments` WHERE `location_id` IN (1400, 1700)) ; SELECT DISTINCT salary FROM `employees` WHERE `job_id`='IT_PROG'; SELECT `employee_id`, `last_name`,   job_id,   salary  FROM `employees` WHERE salary <(SELECT MAX( salary) FROM `employees` WHERE `job_id`='IT_PROG') AND   job_id<>'IT_PROG'; SELECT    a.*, (SELECT COUNT(*) FROM `employees` AS b WHERE a.`department_id` = b.`department_id`) FROM `departments` AS a ; #四.exists 后面(相关子查询) /*结果为0/1查询结果是否存在*/ SELECT EXISTS(SELECT employee_id FROM `employees`); /*查询前n条信息 limit x,x+n */ SELECT * FROM `employees` LIMIT 1,5; /*联合查询 union 联合 合并：将多条查询语句的结果合并成一个结果 语法: union union 的方便: 当查询的来自于多个表的时候，并且没有相关性，我们可以用Union来合并 */ SELECT * FROM `employees` WHERE email LIKE '%a%' UNION SELECT * FROM `employees` WHERE `department_id`>90; SELECT * FROM `employees` WHERE email LIKE '%a%' OR `department_id`>90;