一、基础查询

1. 常见查询

-- 查询表中字段
SELECT username,`password` FROM user; 

-- 查询常量
SELECT 'alias';

-- 查询表达式
SELECT 1+1;

-- 查询函数
SELECT NOW();

2. 条件查询 where 子句

1. 常见运算符、函数、where值

> < >= <= != <> 
<=> -- 安全等于，可判断值是否为null

in  
like 
between...and...
is null 

and 
or 
not

any
some
all

-- 查询无数据的几种情况
0 
null 
'' 
fasle  

2. 示例

-- is null 
SELECT * FROM user_info WHERE phone IS NULL; -- 查询没记录手机号的用户
-- <=> 
SELECT * FROM user_info WHERE phone <=> NULL; -- 查询没记录手机号的用户

-- in 
SELECT * FROM `user_info` WHERE height IN (175,180); -- 查询身高为175或者180的用户
-- = or 
SELECT * FROM `user_info` WHERE height = 175 IN height = 180; -- 查询身高为175或者180的用户

-- like
SELECT * FROM user_info where `name` LIKE '%al%'; -- 查询名字中含有 al 字符的用户
SELECT * FROM user_info where `name` LIKE '_l_a%'; -- 查询第二个字符为l，第四个字符为a开头的名字

-- between and 包括边界
SELECT * FROM `user_info` WHERE height BETWEEN 175 AND 180;-- 查询身高在175和180之间的用户
-- >= <=
SELECT * FROM `user_info` WHERE height >= 175 AND height <= 180;-- 查询身高在175和180之间的用户

-- not
SELECT * FROM `user_info` WHERE NOT height >= 175 AND height <= 180; -- 查询身高不在175和180之间的用户
SELECT * FROM `user_info` WHERE height NOT BETWEEN 175 AND 180; -- 查询身高不在175和180之间的用户

-- any 和 some 用于列子查询，作用相同、只是写法不同，作用是查询符合任意一个匹配的数据
-- 查询身高小于 175,176,177 中任意一个身高的用户，即：身高小于 177 的用户
SELECT * FROM `user_info` WHERE height < ANY(
    SELECT height FROM user_info WHERE height in(175,176,177)
);
-- 查询身高小于 175,176,177 中任意一个的用户，另一种写法
SELECT * FROM `user_info` WHERE height < (
    SELECT MAX(height) FROM user_info WHERE height in(175,176,177)
);

-- all 用于列子查询，作用是查询符合所有匹配的数据
-- 查询身高小于 175,176,177 中所有身高的用户，即：身高小于 175 的用户
SELECT * FROM `user_info` WHERE height < ALL(
    SELECT height FROM user_info WHERE height in(175,176,177)
);
-- 查询身高小于 175,176,177 中所有身高的用户，另一种写法
SELECT * FROM `user_info` WHERE height < (
    SELECT MIN(height) FROM user_info WHERE height in(175,176,177)
);

-- 查询结果为空的几种情况 where 0、where null、where ''、where false
SELECT * FROM `user_info` WHERE 0 OR null OR '' OR false;

3. 排序查询 order by

-- 降序
SELECT * FROM user where 1=1 ORDER BY age DESC; 

-- 升序（默认）
SELECT * FROM user where 1=1 ORDER BY age ASC; 

4. 分页查询 limit

语法：limit pageIndex,pageSize
位置：放在查询语句的最后

-- 查询前三条用户信息
SELECT * FROM `user_info` WHERE 1 ORDER BY height LIMIT 0,3;

5. 设置别名 as

-- 字段别名
SELECT username uname,`password` pwd FROM `user_info`;

-- 表别名
SELECT u.username,u.`password` FROM `user_info` u;

-- 常量别名
SELECT 'alias' `name`;

-- 表达式别名
SELECT 1+1 sum;

-- 函数别名
SELECT NOW() `current_time`;
SELECT NOW() AS `current_time`;

6. 查询去重 distinct

SELECT DISTINCT username,`password` FROM 'user_info';

7. 字段值为null处理 ifnull(expr1,expr2)

SELECT dog_name,IFNULL(dog_age,0) dog_age from dog; -- dog_age 为null，返回 0

二、 聚合查询

聚合函数（统计函数）
注意：所有聚合函数都不处理null值

-- sum(expr)  求和
SELECT SUM(age) age_total FROM `user_info`; 

-- avg(expr)  平均值
SELECT AVG(age) age_avg FROM `user_info`;

-- max(expr) 最大值
SELECT MAX(age) age_max FROM `user_info`;

-- min(expr) 最小值
SELECT MIN(age) age_min FROM `user_info`;

-- count(expr) 计数，查询到的数据条数，不计算null值，一般用COUNT(*)统计条数效率最高
SELECT COUNT(age),COUNT(*) total FROM `user_info`;

三、分组查询 group by

group by 后可以是单个字段名、多个字段名、表达式

1. 分组查询一般和聚合查询一起使用，通过GROUP BY后面的列将表分组，再按照聚合函数修饰的列查询出对应的结果

-- 查询每个性别(GROUP BY sex)的平均年龄(SELECT AVG(age))
SELECT AVG(age),sex FROM `user_info` GROUP BY sex;
/* 结果
AVG(age)       sex
32              0
26              1 
*/

2. GROUP BY一般在from 和 where 之后，order by 之前使用

-- 查询存款大于10000（WHERE deposit>10000）的每个性别(GROUP BY sex)的最大年龄(SELECT MAX(age))的人的信息，并按平均年龄降序排列
SELECT MAX(age),sex,username,height FROM `user_info` WHERE deposit > 10000 GROUP BY sex ORDER BY MAX(age) DESC;
/* 结果
MAX(age)       sex    username    height
55              0       张大昭      162
81              1       刘海      177
*/

3. 如果需要再次对统计数据进行筛选则需要使用 having 子句

注意：分组前使用where条件子句，分组后使用having条件子句

-- 查询年龄小于50（WHERE age<60）的每个性别(GROUP BY sex)的人数(SELECT COUNT(*))，且人数小于30(HAVING COUNT(*)<30)
SELECT COUNT(*),sex FROM `user_info` WHERE age<60 GROUP BY sex HAVING COUNT(*)<30;
/* 结果
COUNT(*)       sex
22              0
*/

四、多表查询

1. 笛卡尔积（交叉查询）

对两个表进行联合查询时，返回两个表数据的乘积，多表查询是在笛卡尔积的基础上进行筛选

-- 如果user有8条数据，user_info有8条数据，那么结果是8x8=64条数据
SELECT * FROM user,user_info; 

2.内连接

查询左右两个表的交集部分数据

1. 显式内连接查询（inner join...on）

SELECT u.username, ui.age, ui.height FROM user_info ui INNER JOIN `user` u 
ON u.id = 1 AND u.id = ui.uid;

2. 隐式内连接查询（where子句）

SELECT u.username,ui.age,ui.height FROM `user` u , user_info ui
WHERE u.id = 1 AND u.id = ui.uid;

3.外连接

JOIN 左侧为左表，右侧为右表

1. 左外连接
   查询左表数据和交集数据，右表匹配不到的数据为null
   LEFT JOIN 和 LEFT OUTER JOIN 作用相同，写法不同

SELECT u.username, ui.age, ui.height FROM user_info ui LEFT JOIN `user` u 
ON u.id = 1 AND u.id = ui.uid;

2. 右外连接
   查询右表数据和交集数据，左表匹配不到的数据为null
   RIGHT JOIN 和 RIGHT OUTER JOIN 作用相同，写法不同

SELECT u.username, ui.age, ui.height FROM user_info ui RIGHT JOIN `user` u 
ON u.id = 1 AND u.id = ui.uid;

五、子查询

在select、from、where、having 子句中使用子查询

1. 在where子句中使用（最常用），把子查询的结果作为主查询的条件

-- 标量子查询（一行一列），子查询语句返回一个值
SELECT * FROM user_info WHERE uid = (
    SELECT id FROM `user` WHERE username = 'zhangsan' AND `password` = '123456'
);

-- 列子查询（一列多行），子查询语句返回一列值
SELECT * FROM user_info WHERE `uid` IN (
    SELECT id FROM `user` WHERE `password` = '123456'
);

-- 行子查询（多列多行），子查询语句返回一行或多行值
SELECT * FROM user_info WHERE (age,height) in (
    SELECT age,height FROM user_info WHERE height < 175
);

2. 在select语句中使用，把子查询的结果做为主查询的列

-- 查询用户信息和用户拥有的狗的个数（结果：在用户信息表旁加dog_num列显示用户拥有的狗的个数）
SELECT u.*,(SELECT COUNT(*) FROM dog d WHERE u.id = d.uid) dog_num FROM `user` u;

3. 在from子句中使用，把子查询的结果作为虚拟表（需要给虚拟表起别名）

-- 查询身高小于175的用户的所有信息，from子查询
SELECT * FROM `user` u,( SELECT * FROM user_info WHERE height<175) ui WHERE u.id = ui.uid;  

-- 查询身高小于175的用户的所有信息。其他写法
SELECT * FROM `user` u,user_info ui WHERE u.id = ui.uid AND ui.height<175;  

4. exists 判断子查询的结果存在，结果为 1 存在，0 不存在

exists 用于判断子查询的结果是否存在

-- 查询身高等于190的用户是否存在
SELECT EXISTS(SELECT * FROM user_info WHERE height = 190 height190); 

六、联合查询

把多次查询的结果合并起来，形成一个新的查询结果集

-- union连接的查询语句的列数必须相同
SELECT username,`password` FROM user UNION SELECT age,height FROM  user_info;

-- union会去重，如果不去重 则使用 union all
SELECT 11 'result' UNION SELECT 22 UNION ALL SELECT 22 UNION ALL SELECT 11;