Mysql的查询

　　出现在select语句中的select语句，称为子查询或内查询。
　　外部的select查询语句，称为主查询或外查询。子查询分类按结果集的行列数不同分为4种标量子查询（结果集只有一行一列）列子查询（结果集只有一列多行）行的查询（结果集有一行多列）表子查询（结果集一般为多行多列）按子查询出现在主查询中的不同位置分select后面：仅仅支持标量子查询。from后面：支持表子查询。where或having后面：支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行）exists后面（即相关子查询）：表子查询（多行、多列）准备测试数据
　　测试数据比较多，放在我的个人博客上了。
　　浏览器中打开链接：http：www。itsoku。comarticle196
　　mysql中执行里面的javacode2018employees库部分的脚本。
　　成功创建javacode2018employees库及5张表，如下：
　　表名
　　描述
　　departments
　　部门表
　　employees
　　员工信息表
　　jobs
　　职位信息表
　　locations
　　位置表（部门表中会用到）
　　jobgrades
　　薪资等级表select后面的子查询
　　子查询位于select后面的，仅仅支持标量子查询。示例1
　　查询每个部门员工的个数SELECTa。，（SELECTcount（）FROMemployeesbWHEREb。departmentida。departmentid）AS员工个数FROM示例2
　　查询员工号102个部门名称SELECT（SELECTa。departmentnameFROMdepartmentsa，employeesbWHEREa。departmentidb。departmentidANDb。employeeid102）AS部门名；from后面的子查询将自己查询的结果集充当一张表，要求必须起别名，否则这个表找不到。
　　然后将真实地表格和自己查询结果表进行连接查询。示例1
　　查询每个部门平均工资的工资等级查询每个部门平均工资SELECTdepartmentid，avg（a。salary）FROMemployeesaGROUPBYa。薪资等级表SELECTFROM将上面2个结果连接查询，筛选条件：平均工资SELECTt1。departmentid，saAS平均工资，t2。gradelevelFROM（SELECTdepartmentid，avg（a。salary）saFROMemployeesaGROUPBYa。departmentid）t1，jobgradest2WHEREt1。saBETWEENt2。lowestsalANDt2。
　　运行最后一条结果如下：mysqlSELECTt1。departmentid，saAS平均工资，t2。gradelevelFROM（SELECTdepartmentid，avg（a。salary）saFROMemployeesaGROUPBYa。departmentid）t1，jobgradest2WHEREt1。saBETWEENt2。lowestsalANDt2。departmentid平均工资gradelevelNULL7000。000000C104400。000000B209500。000000C304150。000000B406500。000000C503475。555556B605760。000000B7010000。000000D808955。882353C9019333。333333E1008600。000000C11010150。000000D12rowsinset（0。00sec）where和having后面的子查询where或having后面，可以使用标量子查询（单行单列子查询）列子查询（单列多行子查询）行的查询（一行多列）特点子查询放在小括号内。子查询一般放在条件的右侧。标量子查询，一般搭配着单行单列操作符使用、、、、、、！列子查询，一般搭配着多行操作符使用in（notin）：列表中的任意一个any或者some：和子查询返回的某一个值比较，比如asome（10，20，30），a大于子查询中任意一个即可，a大于子查询中最小值即可，等同于amin（10，20，30）。all：和子查询返回的所有值比较，比如aall（10，20，30），a大于子查询中所有值，换句话说，a大于子查询中最大值即可满足查询条件，等同于amax（10，20，30）；子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。mysql中的in、any、some、all
　　in，any，some，all分别是子查询的关键词之一。
　　in：in常用于where表达式中，其作用是查询某个范围内的数据
　　any和some一样：可以与、、、、、结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。
　　all：可以与、、、、、结合使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
　　下文中会经常用到这些关键字。标量子查询一般标量子查询，示例
　　查询谁的工资比Abel的高？查询abel的工资【改查询是标量子查询】SELECTsalaryFROMemployeesWHERElastnameA查询员工信息，满足salary的结果SELECTFROMemployeesaWHEREa。salary（SELECTsalaryFROMemployeesWHERElastnameAbel）；多个标量子查询，示例
　　返回jobid与141号员工相同，salary比143号员工多的员工、姓名、jobid和工资返回jobid与141号员工相同，salary比143号员工多的员工、姓名、jobid和工资查询141号员工的jobidSELECTjobidFROMemployeesWHEREemployeeid141；查询143好员工的salarySELECTsalaryFROMemployeesWHEREemployeeid143；查询员工的姓名、jobid、工资，要求jobidandsalarySELECTa。lastname姓名，a。jobid，a。salary工资FROMemployeesaWHEREa。jobid（SELECTjobidFROMemployeesWHEREemployeeid141）ANDa。salary（SELECTsalaryFROMemployeesWHEREemployeeid143）；子查询分组函数，示例
　　查询最低工资大于50号部门最低工资的部门id和其最低工资【having】查询最低工资大于50号部门最低工资的部门id和其最低工资【having】查询50号部门的最低工资SELECTmin（salary）FROMemployeesWHEREdepartmentid50；查询每个部门的最低工资SELECTmin（salary），departmentidFROMemployeesGROUPBY在的基础上筛选，满足min（salary）SELECTmin（a。salary）minsalary，departmentidFROMemployeesaGROUPBYa。departmentidHAVINGmin（a。salary）（SELECTmin（salary）FROMemployeesWHEREdepartmentid50）；错误的标量子查询，示例
　　将上面的示例中子查询语句中的min（salary）改为salary，执行效果如下：mysqlSELECTmin（a。salary）minsalary，departmentidFROMemployeesaGROUPBYa。departmentidHAVINGmin（a。salary）（SELECTsalaryFROMemployeesWHEREdepartmentid500000）；ERROR1242（21000）：Subqueryreturnsmorethan1row错误提示：自己查询返回的结果超过了1行记录。
　　说明：上面的子查询只支持最多一列一行记录。列子查询（子查询结果集一列多行）列子查询需要搭配多行操作符使用：in（notin）、anysome、all。
　　为了提升效率，最好去重复一下distinct关键字。示例1
　　返回locationid是1400或1700的部门中的所有员工姓名返回locationid是1400或1700的部门中的所有员工姓名方式1查询locationid是1400或1700的部门编号SELECTDISTINCTdepartmentidFROMdepartmentsWHERElocationidIN（1400，1700）；查询员工姓名，要求部门是列表中的某一个SELECTa。lastnameFROMemployeesaWHEREa。departmentidIN（SELECTDISTINCTdepartmentidFROMdepartmentsWHERElocationidIN（1400，1700））；方式2：使用any实现SELECTa。lastnameFROMemployeesaWHEREa。departmentidANY（SELECTDISTINCTdepartmentidFROMdepartmentsWHERElocationidIN（1400，1700））；拓展，下面与notin等价SELECTa。lastnameFROMemployeesaWHEREa。departmentidALL（SELECTDISTINCTdepartmentidFROMdepartmentsWHERElocationidIN（1400，1700））；示例2
　　返回其他工种中比jobid为’ITPROG’工种任意工资低的员工的员工号、姓名、jobid、salary返回其他工种中比jobid为ITPROG工种任一工资低的员工的员工号、姓名、jobid、salary查询jobid为ITPROG部门任工资SELECTDISTINCTsalaryFROMemployeesWHEREjobidITPROG；查询员工号、姓名、jobid、salary，slary的任意一个SELECTlastname，employeeid，jobid，salaryFROMemployeesWHEREsalaryANY（SELECTDISTINCTsalaryFROMemployeesWHEREjobidITPROG）ANDjobid！ITPROG；或者SELECTlastname，employeeid，jobid，salaryFROMemployeesWHEREsalary（SELECTmax（salary）FROMemployeesWHEREjobidITPROG）ANDjobid！ITPROG；示例3
　　返回其他工种中比jobid为’ITPROG’部门所有工资低的员工的员工号、姓名、jobid、salary返回其他工种中比jobid为ITPROG部门所有工资低的员工的员工号、姓名、jobid、salarySELECTlastname，employeeid，jobid，salaryFROMemployeesWHEREsalaryALL（SELECTDISTINCTsalaryFROMemployeesWHEREjobidITPROG）ANDjobid！ITPROG；或者SELECTlastname，employeeid，jobid，salaryFROMemployeesWHEREsalary（SELECTmin（salary）FROMemployeesWHEREjobidITPROG）ANDjobid！ITPROG；行子查询（子查询结果集一行多列）示例
　　查询员工编号最小并且工资最高的员工信息，3种方式。查询员工编号最小并且工资最高的员工信息查询最小的员工编号SELECTmin（employeeid）FROM查询最高工资SELECTmax（salary）FROM方式1：查询员工信息SELECTFROMemployeesaWHEREa。employeeid（SELECTmin（employeeid）FROMemployees）ANDsalary（SELECTmax（salary）FROMemployees）；方式2SELECTFROMemployeesaWHERE（a。employeeid，a。salary）（SELECTmin（employeeid），max（salary）FROMemployees）；方式3SELECTFROMemployeesaWHERE（a。employeeid，a。salary）in（SELECTmin（employeeid），max（salary）FROMemployees）；
　　方式1比较常见，方式2、3更简洁。exists后面（也叫做相关子查询）语法：exists（完整的查询语句）。exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。一般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。和前面的查询不同，这先执行主查询，然后主查询查询的结果，在根据子查询进行过滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。示例1
　　简单示例mysqlSELECTexists（SELECTemployeeidFROMemployeesWHEREsalary300000）ASexists返回1或者0；exists返回1或者001rowinset（0。00sec）示例2
　　查询所有员工的部门名称exists入门案例SELECTexists（SELECTemployeeidFROMemployeesWHEREsalary300000）ASexists返回1或者0；查询所有员工部门名SELECTdepartmentnameFROMdepartmentsaWHEREexists（SELECT1FROMemployeesbWHEREa。departmentidb。departmentid）；使用in实现SELECTdepartmentnameFROMdepartmentsaWHEREa。departmentidIN（SELECTdepartmentidFROMemployees）；示例3
　　查询没有员工的部门查询没有员工的部门exists实现SELECTFROMdepartmentsaWHERENOTexists（SELECT1FROMemployeesbWHEREa。departmentidb。departmentidANDb。departmentidISNOTNULL）；in的方式SELECTFROMdepartmentsaWHEREa。departmentidNOTIN（SELECTdepartmentidFROMemployeesbWHEREb。departmentidISNOTNULL）；
　　上面脚本中有b。departmentidISNOTNULL，为什么，有大坑，向下看。NULL的大坑示例1
　　使用notin的方式查询没有员工的部门，如下：SELECTFROMdepartmentsaWHEREa。departmentidNOTIN（SELECTdepartmentidFROMemployeesb）；
　　运行结果：mysqlSELECTFROMdepartmentsaWHEREa。departmentidNOTIN（SELECTdepartmentidFROMemployeesb）；Emptyset（0。00sec）
　　notin的情况下，子查询中列的值为NULL的时候，外查询的结果为空。
　　建议：建表是，列不允许为空。