一、基础查询
1. 常见查询
-- 查询表中字段
SELECT username,`password` FROM user;
-- 查询常量
SELECT 'alias';
-- 查询表达式
SELECT 1+1;
-- 查询函数
SELECT NOW();
2. 条件查询 where 子句
- 常见运算符、函数、where值
> < >= <= != <>
<=> -- 安全等于，可判断值是否为null
in
like
between...and...
is null
and
or
not
any
some
all
-- 查询无数据的几种情况
0
null
''
fasle
- 示例
-- 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.内连接
查询左右两个表的交集部分数据
- 显式内连接查询（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;
- 隐式内连接查询（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 左侧为左表，右侧为右表
- 左外连接
查询左表数据和交集数据，右表匹配不到的数据为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;
- 右外连接
查询右表数据和交集数据，左表匹配不到的数据为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;
网友评论