MySQL五类查询语法
基本查询
条件查询(where)
分组查询(group by)
排序查询(order by)
分页查询(limit)
初始化数据
-- 删除stu表
drop table if exists stu;
-- 创建stu表
CREATE TABLE stu (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math double(5,2), -- 数学成绩
english double(5,2), -- 英语成绩
hire_date date -- 入学时间
);
-- 添加数据
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date)
VALUES
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');
-- 查看验证数据
SELECT * FROM stu;
基础查询
/*
查询全部记录
标准语法:
SELECT * FROM 表名;
不建议使用select *
1. 冗余字段会被查出来,后期添加的字段也会被查出来
2. select * 的性能低于 select 字段名列表
*/
-- 基础查询 ================
-- 查询所有人的姓名和年龄
SELECT `name`, age from stu;
-- 查询stu表中所有列的所有记录
SELECT * FROM stu;
SELECT id, `name`, age, sex, address, math, english, hire_date FROM stu;
-- 查询所有人的地址
SELECT address FROM stu;
-- 查询所有人的地址并去除重复记录
SELECT DISTINCT address FROM stu;
-- 查询所有人的姓名 、数学成绩、英语成绩,并全部起别名
SELECT `name` , math, english FROM stu;
SELECT `name` AS '姓名', math '数学成绩', english '英语成绩' FROM stu;
-- 查询有人的姓名 、数学成绩、英语成绩,部分(英语成绩)起别名
SELECT `name`, math, english AS '英语成绩' FROM stu;
条件查询
/*
标准语法:
SELECT 列名列表 FROM 表名 WHERE 条件;
在SQL中,null是一个占位符,而非数据,所以null <> null.
要进行null或者非null的判断,就使用IS NULL 或者 IS NOT NULL
*/
-- 条件查询 =====================
-- 1.查询年龄大于20岁的学员信息
SELECT * FROM stu WHERE age >20;
-- 2.查询年龄大于等于20岁的学员信息
SELECT * FROM stu WHERE age >= 20;
-- 3.查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
SELECT * FROM stu WHERE age >=20 AND age<=30;
SELECT * FROM stu WHERE age >=20 && age<=30;
SELECT * from stu WHERE age BETWEEN 20 AND 30;
-- 4.查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息
SELECT * FROM stu WHERE hire_date BETWEEN '1998-09-01' AND '1999-09-01';
SELECT * FROM stu WHERE hire_date >= '1998-09-01' AND hire_date <= '1999-09-01';
-- 5. 查询年龄等于18岁的学员信息
SELECT * FROM stu WHERE age = 18;
-- 6. 查询年龄不等于18岁的学员信息
SELECT * FROM stu WHERE age != 18;
SELECT * FROM stu WHERE age <> 18;
-- 7. 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
SELECT * FROM stu WHERE age = 18 OR age = 20 OR age = 22;
SELECT * FROM stu WHERE age IN (18,20,22);
-- 8. 查询英语成绩为 NULL的学员信息
-- 注意: SQL中 null不等于null,所以不能使用=和!=进行和NULL的等值判断。
SELECT * FROM stu WHERE english = null; -- 差不到数据
SELECT * FROM stu WHERE english IS NULL;
SELECT * FROM stu WHERE english IS NOT NULL;
-- 条件查询进阶 模糊查询 LIKE =====================
/*
通配符:
(1)_:代表单个任意字符(1个)
(2)%:代表任意个数字符(≥0个)
*/
-- 1. 查询姓'马'的学员信息
SELECT * FROM stu WHERE name LIKE '马%';
-- 2. 查询第二个字是'花'的学员信息
SELECT * FROM stu WHERE `name` LIKE '_花%';
-- 3. 查询名字中包含 '德' 的学员信息
SELECT * FROM stu WHERE `name` LIKE '%德%';
-- 4. 查询名字为两个字的所有学员信息
SELECT * FROM stu WHERE `name` LIKE '__';
排序查询
/*
标准语法:
SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;
排序方式:
ASC:升序排列(默认值)
DESC:降序排列
*/
-- 1.查询学生信息,按照年龄升序排列
SELECT * FROM stu ORDER BY age ASC;
-- 2.查询学生信息,按照数学成绩降序排列
SELECT * FROM stu ORDER BY math DESC;
-- 3.查询学生信息,按照数学成绩降序排列,如果数学成绩一样,再按照英语成绩升序排列
SELECT * FROM stu ORDER BY math DESC , english ASC; -- 当主要条件无法排出顺序的时候,才考虑次要条件
/*
排序查询进阶版 聚合查询
标准语法:
SELECT 聚合函数名(列名) FROM 表名 [WHERE 条件];
聚合函数:
count 统计数量
max 求最大值
min 求最小值
sum 求和
avg 求平均值
*/
-- 查询stu表中所有记录
SELECT * FROM stu;
-- 1. 统计班级一共有多少个同学
SELECT count(id) FROM stu;
SELECT count(*) FROM stu;
-- 2. 查询数学成绩的最高分
SELECT max(math) FROM stu;
-- 3. 查询数学成绩的最低分
SELECT min(math) FROM stu;
-- 4. 查询统计数学成绩的总分
SELECT sum(math) FROM stu;
-- 5. 查询统计数学成绩的平均分
SELECT AVG(math) FROM stu;
-- 6. 查询英语成绩的最低分
SELECT MIN(english) FROM stu;
SELECT min(IFNULL(english,0)) from stu;
分组查询
/*
标准语法:
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤]…;
分组查询一般会配合聚合函数一同出现
书写技巧:
1. 先写分组的条件,不写查询的列
2. 按照需求添加要查询的列
*/
-- 1. 查询男同学和女同学各自的数学平均分
SELECT
sex, avg(math)
FROM
stu
GROUP BY
sex;
-- MySQL5.6及以后版本中要求,分组查询时,要查询的列中只能包含分组列和聚合函数,否则会报语法错误
-- MySQL5.5及以前版本中,分组查询时,查询的目标字段列表可以包含非聚合函数和分组字段,但是查询其他字段无任何意义
SELECT
name, sex, avg(math)
FROM
stu
GROUP BY
sex;
-- 2. 查询男同学和女同学各自的数学平均分,以及各自人数
SELECT
sex, avg(math), count(*)
FROM
stu
GROUP BY
sex;
-- 3. 查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组
SELECT
sex, AVG(math),COUNT(*)
FROM
stu
WHERE
math >=70
GROUP BY
sex;
-- 4. 查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组,分组之后人数大于2个的。
SELECT
sex '性别', AVG(math) '数学平均分',COUNT(*) AS "人数"
FROM
stu
WHERE
math >=70
GROUP BY
sex
HAVING
人数 > 2;
-- WHERE字句中的查询条件只能使用表字段,不能使用只在结果集中出现的字段或者表字段的聚合函数;因为是在分组前对原始数据进行过滤
SELECT
sex '性别', AVG(math) '数学平均分',COUNT(*) AS "人数"
FROM
stu
WHERE
math >=70 AND 人数 > 2
GROUP BY
sex;
-- HAVING子句中的查询条件只能使用查询出来的结果集字段;因为是在分组后对查询出来的结果集进行再次过滤
SELECT
sex '性别', AVG(math) '数学平均分',COUNT(*) AS "人数"
FROM
stu
WHERE
math >=70
GROUP BY
sex
HAVING
id > 2;
-- HAVING 不能使用目标字段列表外的字段作为条件
SELECT sex, AVG(math),COUNT(*) FROM stu WHERE math > 70 GROUP BY sex HAVING english >10;
-- WHERE 不能使用表字段外的字段进行判断(包括表字段的聚合函数)
SELECT sex, AVG(math),COUNT(*) FROM stu WHERE AVG(math) >10 GROUP BY sex HAVING COUNT(*) > 2;
分页查询
/*
基础语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询记录个数
起始索引:最小起始索引为0,起始索引 = (当前页码 - 1) * 每页显示的记录数
注意:
不同数据库实现分页的方言不同,常见数据库对应方言如下:
MySQL:LIMIT
Oracle:rownumber
SQL Server:top
*/
-- 查询stu表中所有记录
SELECT * FROM stu;
-- 1. 从0开始查询,查询3条数据
SELECT * FROM stu LIMIT 0, 3;
-- 2. 每页显示3条数据,查询第1页数据
SELECT * FROM stu LIMIT 0, 3;
-- 3. 每页显示3条数据,查询第2页数据
SELECT * FROM stu LIMIT 3, 3;
-- 4. 每页显示3条数据,查询第3页数据
SELECT * FROM stu LIMIT 4, 3;
-- 起始索引 = (当前页码 - 1) * 每页显示的条数