语法:
SELECT 字段列表 FROM 表名字段 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后的条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
查询多个字段:
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;
设置别名:
SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;
去除重复记录:
SELECT DISTINCT 字段列表 FROM 表名;
转义:
SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'
/ 之后的_不作为通配符
语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件:
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN ... AND ... | 在某个范围内(含最小、最大值) |
IN(...) | 在in之后的列表中的值,多选一 |
LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能 |
---|---|
AND 或 && | 并且(多个条件同时成立) |
OR 或 || | 或者(多个条件任意一个成立) |
NOT 或 ! | 非,不是 |
例子:
-- 年龄等于30 select * from employee where age = 30; -- 年龄小于30 select * from employee where age < 30; -- 小于等于 select * from employee where age <= 30; -- 没有身份证 select * from employee where idcard is null or idcard = ''; -- 有身份证 select * from employee where idcard; select * from employee where idcard is not null; -- 不等于 select * from employee where age != 30; -- 年龄在20到30之间 select * from employee where age between 20 and 30; select * from employee where age >= 20 and age <= 30; -- 下面语句不报错,但查不到任何信息 select * from employee where age between 30 and 20; -- 性别为女且年龄小于30 select * from employee where age < 30 and gender = '女'; -- 年龄等于25或30或35 select * from employee where age = 25 or age = 30 or age = 35; select * from employee where age in (25, 30, 35); -- 姓名为两个字 select * from employee where name like '__'; -- 身份证最后为X select * from employee where idcard like '%X';
常见聚合函数:
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
语法:
SELECT 聚合函数(字段列表) FROM 表名;
例:
SELECT count(id) from employee where workaddress = "广东省";
语法:
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];
where 和 having 的区别:
例子:
-- 根据性别分组,统计男性和女性数量(只显示分组数量,不显示哪个是男哪个是女) select count(*) from employee group by gender; -- 根据性别分组,统计男性和女性数量 select gender, count(*) from employee group by gender; -- 根据性别分组,统计男性和女性的平均年龄 select gender, avg(age) from employee group by gender; -- 年龄小于45,并根据工作地址分组 select workaddress, count(*) from employee where age < 45 group by workaddress; -- 年龄小于45,并根据工作地址分组,获取员工数量大于等于3的工作地址 select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3;
语法:
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式:
例子:
-- 根据年龄升序排序 SELECT * FROM employee ORDER BY age ASC; SELECT * FROM employee ORDER BY age; -- 两字段排序,根据年龄升序排序,入职时间降序排序 SELECT * FROM employee ORDER BY age ASC, entrydate DESC;
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
例子:
-- 查询第一页数据,展示10条 SELECT * FROM employee LIMIT 0, 10; -- 查询第二页 SELECT * FROM employee LIMIT 10, 10;
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
-- DQL(数据查询语言)
-- 基础查询
-- 查询多个字段
SELECT age FROM test;
-- 查询全部
SELECT * FROM test;
-- 设置别名
SELECT age as a ,name as n from test;
-- 别名简写
SELECT name n ,habby h FROM test;
-- 去重
SELECT DISTINCT habby FROM test;
-- 转义
SELECT * FROM test WHERE name LIKE '/_%' ESCAPE '/';
USE test2;
-- 条件查询
SELECT * FROM test WHERE age = 20;
SELECT * FROM test WHERE age < 100;
SELECT * FROM test WHERE age <= 39;
SELECT * FROM test WHERE age >= 29;
-- 不等于
SELECT * FROM test WHERE age != 20;
SELECT * FROM test WHERE age <> 20;
-- 不为空或者为 20 OR 是或者 AND 是和
SELECT * FROM test WHERE age is NULL OR age = 20;
-- 在某个范围
SELECT * FROM test WHERE age BETWEEN 29 AND 69;
-- 是固定几个值
SELECT * FROM test WHERE age IN(20,19,28);
-- 模糊匹配 LIKE % _ 匹配符 % 匹配任意 _ 匹配一个
SELECT * FROM test WHERE age LIKE '1_';
SELECT * FROM test WHERE age LIKE '%';
-- 聚合查询
-- count 统计数量
SELECT COUNT(age) FROM test WHERE age like '1_';
-- max min avg sum 求最大值、最小值、平均值、和
SELECT MAX(age) FROM test WHERE age < 30;
SELECT MIN(age) FROM test;
SELECT AVG(age) FROM test;
SELECT SUM(age) FROM test;
-- 分组查询 可以根据 habby 分成几组 count 统计每组数量
SELECT habby,COUNT() FROM test GROUP BY habby;
-- 分组查询 根据 habby 分组 avg 统计每组的平均年龄
SELECT habby,AVG(age) FROM test GROUP BY habby;
-- 年龄小于 30 分组查询
SELECT habby,COUNT() FROM test WHERE age < 30 GROUP BY habby;
-- 年龄小于45,并根据爱好,获取人数大于等于2的爱好
SELECT habby,COUNT(*) test_count FROM test WHERE age <= 45 GROUP BY habby HAVING test_count >= 2;
-- 执行顺序 WHERE > 聚合函数 >HAVING
-- 排序查询 ORDER BY
-- ASC 升序 DESC 降序
-- 根据年龄升序 年龄相同时根据 id 降序
SELECT * FROM test ORDER BY age ASC ,id DESC;
-- 分页查询 LIMIT id 大于 1 再分页 起始索引为 0 的前两个
SELECT * FROM test WHERE id > 1 LIMIT 2,2;
-- DQL 执行语句顺序 FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT