MySQL | DQL 查询语句

MySQL | DQL 查询语句

DQL,Data Query Language,即数据查询语句,用于查询表中的记录

基本语法

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组之后的条件
order by
	排序
limit
	分页限定

先准备一些供练习用的数据,从这里开始,使用客户端图形化工具:SQLYog

CREATE DATABASE IF NOT EXISTS db; -- 建库
USE db; -- 使用库
CREATE TABLE student(
	 id INT, -- 编号
	NAME VARCHAR(20), -- 姓名
	age INT, -- 年龄
	sex VARCHAR(5), -- 性别
	address VARCHAR(100), -- 地址
	math INT, -- 数学成绩
	english INT -- 英语成绩
); -- 建表,添加字段

INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES 
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65); -- 添加数据(添加多条数据的简化写法)

基础查询

select field1,field2... from tbl_name; -- 查询指定字段

select * from tbl_name; -- 查询所有字段

select distinct field1 from tbl_name; -- 去除重复的结果集

select field1 + field2 form tbl_name; -- 使用四则运算计算一些字段的值(一般只会进行数值型的计算)

select field1 + ifnull(field2,var) from tbl_name; -- null参与的运算,计算结果都为null,使用ifnull() 函数,如果field2字段值为null则改用var进行计算

select field1 as other_name1,field as other_name2,field1 + ifnull(field2,var) as other_name3 fromtbl_name; -- 给查询的字段起别名,as 也可省略

条件查询

  1. 使用 where 子句后跟条件
  2. 条件语句中可以使用的运算符
    • > 、< 、<= 、>= 、= 、<>
    • BETWEEN...AND...
    • IN(集合)
    • LIKE:模糊查询
      • 占位符:
        • _:单个任意字符
        • %:多个任意字符
    • IS NULL
    • and 或 &&
    • or 或 ||
    • not 或 !
-- 查询年龄大于20岁
SELECT * FROM student WHERE age > 20;

-- 查询年龄大于等于20岁
SELECT * FROM student WHERE age >= 20;

-- 查询年龄等于20岁
SELECT * FROM student WHERE age = 20;

-- 查询年龄不等于20岁
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;


-- 查询年龄大于等于 20 小于等于30		
SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

-- 查询年龄22岁,18岁,25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN (22,18,25);
-- 查询地址是湖南、南京的信息
SELECT * FROM student WHERE address IN ('湖南','南京');

-- 查询英语成绩为 null
-- SELECT * FROM student WHERE english = NULL; -- 错误写法,null值不可以用 = 或 != 判断
SELECT * FROM student WHERE english IS NULL;

-- 查询英语成绩不为null
SELECT * FROM student WHERE english IS NOT NULL;

-- 查询姓马的有哪些? 使用模糊搜索 like
SELECT * FROM student WHERE NAME LIKE "马%";

-- 查询姓名第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";

-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE "___"; -- 三个下划线

-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE "%德%";

排序查询

排序查询使用 order by 子句

-- 按照指定字段进行指定排序方式进行查询
-- 先按math字段的值降序排,如果值相同,再按english字段的值升序排
SELECT * FROM student ORDER BY math DESC,english ASC;
  • 排序方式:
    • ASC:升序,默认的
    • DESC:降序
  • 如果有多个排序条件,当前边的条件值一样时,才会判断第二条件

聚合函数

聚合函数:将一列数据作为一个整体,进行纵向的计算

-- 计算个数,一般选择非空的字段:例如主键
SELECT COUNT(id) FROM student; -- 结果 count(id):8

-- 计算最大值
SELECT MAX(math) FROM student;

-- 计算最小值
SELECT MIN(english) FROM student;

-- 计算总和
SELECT SUM(math + english) FROM student;

-- 计算平均值
SELECT AVG(math + IFNULL(english,0)) AS 平均值 FROM student;

  • 注意:聚合函数的计算,会排除 null 值。所以,尽量选择不包含 null 的字段进行计算或者使用 IFNULL 函数

分组查询

  • 分组查询使用 group by 把字段进行分组,一般配合聚合函数使用
  • 注意
    1. 使用 group by 的查询语句中 select 列表中的字段只能是 group by 子句中指定的字段或者聚合函数
    2. where 和 having 的区别
      1. where 在分组之前进行限定,如果不满足条件,则不参与分组。having 在分组之后进行限定,如果不满足结果,则不会被查询出来
      2. where 后不可以跟聚合函数,having可以进行聚合函数的判断。(也就是说where 子句中的字段只能是表中原有的字段)
-- 按照性别分组。分别查询男、女同学的数学平均分
SELECT sex,AVG(math) FROM student GROUP BY sex;

-- 按照性别分组。分别查询男、女同学的英语平均分和人数
SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;

--  按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

-- 按照性别分组。分别查询男、女同学的平均分,人数 
-- 要求:1. 分数低于70分的人,不参与分组
-- 2. 分组之后,人数要大于 2 的组才会被查询出来
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

-- 给要查询的字段(包括聚合函数)起别名
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2; 

分页查询

  • 实际开发中,查询到的数据无法或不需在一页展示(例如百度搜索的结果),可以使用分页查询,使用 limit 关键字
  • 语法:limit 开始的索引,每页查询的条数;
    • 公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
  • limit 是一个 MySQL 特有的语法,其他数据库有各自的实现
-- 分页查询,每页显示 3 条记录  
SELECT * FROM student LIMIT 0,3; -- 第1页

SELECT * FROM student LIMIT 3,3; -- 第2页

SELECT * FROM student LIMIT 6,3; -- 第3页

参考

黑马 JavaWeb
数据库复习笔记