目录
10.2 SELECT的WHERE或HAVING中嵌套子查询
第10章 子查询
10.1 SELECT的SELECT中嵌套子查询
/*
子查询:嵌套在另一个SQL语句中的查询。
SELECT语句可以嵌套在另一个SELECT中,UPDATE,DELETE,INSERT,CREATE语句等。
(1)SELECT的SELECT中嵌套子查询
*/
#(1)在“t_employee”表中查询每个人薪资和公司平均薪资的差值,
#并显示员工薪资和公司平均薪资相差5000元以上的记录。
SELECT ename AS "姓名",
salary AS "薪资",
ROUND((SELECT AVG(salary) FROM t_employee),2) AS "全公司平均薪资",
ROUND(salary-(SELECT AVG(salary) FROM t_employee),2) AS "差值"
FROM t_employee
WHERE ABS(ROUND(salary-(SELECT AVG(salary) FROM t_employee),2))>5000;
#(2)在“t_employee”表中查询每个部门平均薪资和公司平均薪资的差值。
SELECT did,AVG(salary),
AVG(salary)-(SELECT AVG(salary) FROM t_employee)
FROM t_employee
GROUP BY did;
10.2 SELECT的WHERE或HAVING中嵌套子查询
当子查询结果作为外层另一个SQL的过滤条件,通常把子查询嵌入到WHERE或HAVING中。根据子查询结果的情况,分为如下三种情况。
-
当子查询的结果是单列单个值,那么可以直接使用比较运算符,如“<”、“<=”、“>”、“>=”、“=”、“!=”等与子查询结果进行比较。
-
当子查询的结果是单列多个值,那么可以使用比较运算符IN或NOT IN进行比较。
-
当子查询的结果是单列多个值,还可以使用比较运算符, 如“<”、“<=”、“>”、“>=”、“=”、“!=”等搭配ANY、SOME、ALL等关键字与查询结果进行比较。
/*
子查询嵌套在where后面。
在where或having后面的子查询结果是:
(1)单个值,那么可以用=,>,<,>=,<=,!=这样的运算符和子查询的结果做比较
(2)多个值,那么需要用in,not in, >all,>any....形式做比较
如“<”、“<=”、“>”、“>=”、“=”、“!=”等搭配ANY、SOME、ALL等关键字与查询结果进行比较
*/
#(1)在“t_employee”表中查询薪资最高的员工姓名(ename)和薪资(salary)。
#SELECT ename,MAX(salary) FROM t_employee;#错误
#取表中第一行员工的姓名和全公司最高的薪资值一起显示。
SELECT ename,salary
FROM t_employee
WHERE salary = (SELECT MAX(salary) FROM t_employee);
#(2)在“t_employee”表中查询比全公司平均薪资高的男员工姓名和薪资。
SELECT ename,salary
FROM t_employee
WHERE salary > (SELECT AVG(salary) FROM t_employee) AND gender = '男';
#(3)在“t_employee”表中查询和“张三”,“李四”同一部门的员工姓名和电话。
SELECT ename,tel,did
FROM t_employee
WHERE did IN(SELECT did FROM t_employee WHERE ename='张三' || ename='李四');
SELECT ename,tel,did
FROM t_employee
WHERE did =ANY(SELECT did FROM t_employee WHERE ename='张三' || ename='李四');
#(4)在“t_employee”表中查询薪资比“张三”,“李四”,“王五”三个人的薪资都要高的员工姓名和薪资。
SELECT ename,salary
FROM t_employee
WHERE salary >ALL(SELECT salary FROM t_employee WHERE ename IN('张三','李四','王五'));
#(5)查询“t_employee”和“t_department”表,按部门统计平均工资,
#显示部门平均工资比全公司的总平均工资高的部门编号、部门名称、部门平均薪资,
#并按照部门平均薪资升序排列。
SELECT t_department.did,dname,AVG(salary)
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
GROUP BY t_department.did
HAVING AVG(salary) >(SELECT AVG(salary) FROM t_employee)
ORDER BY AVG(salary);
10.3 SELECT中的EXISTS型子查询
EXISTS型子查询也是存在外层SELECT的WHERE子句中,不过它和上面的WHERE型子查询的工作模式不相同,所以这里单独讨论它。
如果EXISTS关键字后面的参数是一个任意的子查询,系统将对子查询进行运算以判断它是否返回行,如果至少返回一行,那么EXISTS的结果为true,此时外层查询语句将进行查询;如果子查询没有返回任何行,那么EXISTS的结果为false,此时外层查询语句不进行查询。EXISTS和NOT EXISTS的结果只取决于是否返回行,而不取决于这些行的内容,所以这个子查询输入列表通常是无关紧要的。
如果EXISTS关键字后面的参数是一个关联子查询,即子查询的WHERE条件中包含与外层查询表的关联条件,那么此时将对外层查询表做循环,即在筛选外层查询表的每一条记录时,都看这条记录是否满足子查询的条件,如果满足就再用外层查询的其他WHERE条件对该记录进行筛选,否则就丢弃这行记录。
#exist型子查询
/*
(1)exists()中的子查询和外面的查询没有联合的情况下,
如果exists()中的子查询没有返回任何行,那么外面的子查询就不查了。
(2)exists()中的子查询与外面的查询有联合工作的情况下,
循环进行把外面查询表的每一行记录的值,代入()中子查询,如果可以查到结果,
就留下外面查询的这条记录,否则就舍去。
*/
#(1)查询“t_employee”表中是否存在部门编号为NULL的员工,
#如果存在,查询“t_department”表的部门编号、部门名称。
SELECT * FROM t_department
WHERE EXISTS(SELECT * FROM t_employee WHERE did IS NULL);
#(2)查询“t_department”表是否存在与“t_employee”表相同部门编号的记录,
#如果存在,查询这些部门的编号和名称。
SELECT * FROM t_department
WHERE EXISTS(SELECT * FROM t_employee WHERE t_employee.did = t_department.did);
#查询结果等价于下面的sql
SELECT DISTINCT t_department.*
FROM t_department INNER JOIN t_employee
ON t_department.did = t_employee.did;
10.4 SELECT的FROM中嵌套子查询
当子查询结果是多列的结果时,通常将子查询放到FROM后面,然后采用给子查询结果取别名的方式,把子查询结果当成一张“动态生成的临时表”使用。
#子查询嵌套在from后面
/*
当一个查询要基于另一个查询结果来筛选的时候,
另一个查询还是多行多列的结果,那么就可以把这个查询结果当成一张临时表,
放在from后面进行再次筛选。
*/
#(1)在“t_employee”表中,查询每个部门的平均薪资,
#然后与“t_department”表联合查询
#所有部门的部门编号、部门名称、部门平均薪资。
SELECT did,AVG(salary) FROM t_employee GROUP BY did;
+------+-------------+
| did | AVG(salary) |
+------+-------------+
| 1 | 11479.3125 |
| 2 | 13978 |
| 3 | 37858.25 |
| 4 | 12332 |
| 5 | 11725 |
+------+-------------+
5 ROWS IN SET (0.00 sec)
#用上面的查询结果,当成一张临时表,与t_department部门表做联合查询
#要给这样的子查询取别名的方式来当临时表用,不取别名是不可以的。
#而且此时的别名不能加""
#字段的别名可以加"",表的别名不能加""
SELECT t_department.did ,dname,AVG(salary)
FROM t_department LEFT JOIN (SELECT did,AVG(salary) FROM t_employee GROUP BY did) temp
ON t_department.did = temp.did;
#错误,from后面的t_department和temp表都没有salary字段,
#SELECT t_department.did ,dname,AVG(salary)出现AVG(salary)是错误的
SELECT t_department.did ,dname,pingjun
FROM t_department LEFT JOIN (SELECT did,AVG(salary) AS pingjun FROM t_employee GROUP BY did) temp
ON t_department.did = temp.did;
#(2)在“t_employee”表中查询每个部门中薪资排名前2的员工姓名、部门编号和薪资。
SELECT * FROM (
SELECT ename,did,salary,
DENSE_RANK() over (PARTITION BY did ORDER BY salary DESC) AS paiming
FROM t_employee) temp
WHERE temp.paiming <=2;
第11章 MySQL支持的数据类型
11.1 数值类型:包括整数和小数
数值类型主要用来存储数字,不同的数值类型提供不同的取值范围,可以存储的值范围越大,所需要的存储空间也越大。MySQL支持所有标准SQL中的数值类型,其中包括严格数据类型(INTEGER、SMALLINT、DECIMAL、NUMERIC)和近似数值类型(FLOAT、REAL、DOUBLE PRECISION)。MySQL还扩展了TINYINT、MEDIUMINT和BIGINT等3种不同长度的整数类型,并增加了BIT类型,用来存储位数据。
对于MySQL中的数值类型,还要做如下说明:
-
关键字INT是INTEGER的同义词。
-
关键字DEC和FIXED是DECIMAL的同义词。
-
NUMERIC和DECIMAL类型被视为相同的数据类型。
-
DOUBLE视为DOUBLE PRECISION的同义词,并在REAL_AS_FLOAT SQL模式未启用的情况下,将REAL也视为DOUBLE PRECISION的同义词。
1、整数类型
说明:
对于整数类型,MySQL还支持在类型名称后面加小括号(M),而小括号中的M表示显示宽度,M的取值范围是(0, 255)。int(M)这个M在字段的属性中指定了unsigned(无符号)和zerofill(零填充)的情况下才有意义。表示当整数值不够M位时,用0填充。如果整数值超过M位但是没有超过当前数据类型的范围时,就按照实际位数存储。当M宽度超过当前数据类型可存储数值范围的最大宽度时,也是以实际存储范围为准。
MySQL8之前,int没有指定(M),默认显示(11)。最多能存储和显示11位整数。从MySQL 8.0.17开始,整数数据类型不推荐使用显示宽度属性,默认显示int。
#演示整数类型
#创建一个表格,表格的名称“t_int”,
#包含两个字段i1和i2,分别是int和int(2)类型
#create table t_int(i1 int,i2 int(2));
create table t_int(
i1 int,
i2 int(2) #没有unsigned zerofill,(2)没有意义
);
#查看当前数据库的所有表格
show tables;
show tables from 数据库名;
#查看表结构
desc 表名称;
desc t_int;
mysql> desc t_int;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| i1 | int | YES | | NULL | |
| i2 | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#创建一个表格,表格的名称“t_int2”,
#包含两个字段i1和i2,分别是int和int(2)类型
create table t_int2(
i1 int,
i2 int(2) unsigned zerofill
);
mysql> desc t_int2;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| i1 | int | YES | | NULL | |
| i2 | int(2) unsigned zerofill | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#添加数据到表格中
insert into 表名称 values(值列表);
insert into t_int values(1234,1234);
insert into t_int2 values(1234,1234);
#查询数据
select * from 表名称;
select * from t_int;
select * from t_int2;
#添加数据到表格中
insert into 表名称 values(值列表);
insert into t_int values(1,1);
insert into t_int2 values(1,1);
insert into t_int values(12222228854225548778455,12222228854225548778455);
mysql> insert into t_int values(12222228854225548778455,12222228854225548778455);
ERROR 1264 (22003): Out of range value for column 'i1' at row
2、bit类型
bit类型,如果没有指定(M),默认是1位。这个1位,那么表示只能存1位的二进制值。这里(M)是表示二进制的位数。M范围从1到64。
对于位类型字段,之前版本直接使用SELECT语句将不会看到结果,而在MySQL8版本中默认以“0X”开头的十六进制形式显示,可以通过BIN()函数显示为二进制格式。
#演示bit类型,存储二进制,只有0和1
#创建一个表格
create table t_bit(
b1 bit, #没有指定(M),默认是1位二进制
b2 bit(4) #能够存储4位二进制0000~1111
);
#查看表结构
desc t_bit;
mysql> desc t_bit;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| b1 | bit(1) | YES | | NULL | |
| b2 | bit(4) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#添加记录
insert into t_bit values(1,1);
#查看数据
select * from t_bit;
mysql> select * from t_bit;
+------------+------------+
| b1 | b2 |
+------------+------------+
| 0x01 | 0x01 | #0x开头表示十六进制
+------------+------------+
1 row in set (0.00 sec)
#显示二进制值,需要使用bin函数
select bin(b1),bin(b2) from t_bit;
mysql> select bin(b1),bin(b2) from t_bit;
+---------+---------+
| bin(b1) | bin(b2) |
+---------+---------+
| 1 | 1 |
+---------+---------+
1 row in set (0.00 sec)
#添加记录
insert into t_bit values(2,2);
mysql> insert into t_bit values(2,2);
#values()中是十进制值,需要转为二进制存储,2对应10,超过1位,b1存不下
ERROR 1406 (22001): Data too long for column 'b1' at r