数据库学习与复习笔记--数据库的查询操作(2)

DQL:查询表中的记录

select * from 表名;

语法：

select  字段列表

from  表名列表

where  条件列表

group  by  分组字段

having  分组之后的条件

order  by  排序

limit  分页限定

为了练习，创建的表和添加的数据：

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,'男','杭州',67,68),(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 * FROM student;

一、基础查询：

多个字段的查询

select 字段名1，字段名2，... from 表名;

-- 查询姓名和年龄
SELECT NAME,age FROM student;

注意：如果查询所有字段，则可以使用*来替代字段列表。

SELECT * FROM student;

去除重复：distinct

select distinct 字段名1，字段名2，。。。 from 表名;

SELECT NAME,address FROM student;

注意：只有对应字段名的内容完全相同，才能去重

计算列：一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）

ifnull(表达式1，表达式2)：null参与的运算，计算结果都为null

SELECT NAME,math,english,math+ IFNULL(english,0) FROM student; 

表达式1：哪个字段需要判断是否为null

表达式2：如果该字段为null后的替换值

起别名：

as：as 也可以省略

如：-- 起别名
SELECT NAME,math,english,math+ IFNULL(english,0) AS 总分 FROM student; 

或者省略as：SELECT NAME,math 数学,english 英语,math+ IFNULL(english,0) 总分 FROM student; 

二、条件查询：

where子句后跟条件

运算符如下：

> 、<、 <=、 >=、 =、 <>

BETWEEN...AND

in(集合)

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;
SELECT * FROM student WHERE age <>20;
-- 查询年龄大于等于20 小于等于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);
-- 查询英语成绩为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 ：模糊查询

占位符:

_:单个任意字符

%：任意多个字符

如：-- 查询学生姓马的有哪些？ like
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%"; -- 单引号双引号 一样使用
-- 查询姓名是三个字的人
SELECT * FROM student WHERE NAME LIKE '___';
-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';

排序查询：

语法： order by 子句

     order by 排序字段1 排序方式1, 排序字段2 排序方式2...

排序方式：

ASC：升序 默认的 如数学成绩升序排列：SELECT * FROM student ORDER BY math ASC;
DESC：降序 如数学成绩降序排列：SELECT * FROM student ORDER BY math DESC;

注意：如果有多个排序条件，则当前面的条件值一样时才会判断第二条件。

如：SELECT * FROM student ORDER BY math ASC,english DESC; -- 先按数学成绩升序排序，若相同按英语成绩降序排列

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

1.count：计算个数

如：SELECT COUNT(id) FROM student;

一般选择非空的列：主键

count(*)

2.max：计算最大值

如：计算数学成绩最大值  SELECT MAX(math) FROM student;

3.min：计算最小值

如：计算数学成绩最小值 SELECT MIN(math) FROM student;

4.sum：计算和

如：计算数学成绩之和 SELECT SUM(math) FROM student;

5.avg：计算平均值

如：计算数学成绩平均分数  SELECT AVG(math) FROM student;

注意：聚合函数的计算，排除null值。

解决方案：1.选择不包含非空的列 2.IFNULL函数 如：SELECT COUNT(IFNULL(english,0)) FROM student;

分组查询：

语法：group by 分组字段;

如：-- 按照性别分组，分别查询男、女同学的平均分
SELECT sex,AVG(math) FROM student GROUP BY sex;

注意：1.分组之后查询的字段：分组字段、聚合函数 。

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

2.where和having的区别：

where在分组之前进行限定，如果不满足这个条件，则不参与分组。having在分组之后进行限定，

如果不满足结果，则不会被查询出来。

where后不可以跟聚合函数，having后可以进行聚合函数的判断。

如：-- 按照性别分组，分别查询男、女同学的平均分、人数 要求：分数低于70分的人，不参与分组
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex;
-- 按照性别分组，分别查询男、女同学的平均分、人数 要求：分数低于70分的人，不参与分组，分组之后人数要大于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 开始的索引, 每页查询的条数;

公式：开始的索引= （当前的页码-1）*每页显示的条数

limit是一个MySQL的"方言"。

例如：-- 每一页显示3条记录
SELECT * FROM student LIMIT 0,3;-- 第1页
SELECT * FROM student LIMIT 3,3;-- 第2页
-- 公式：开始的索引= （当前的页码-1）*每页显示的条数
SELECT * FROM student LIMIT 6,3;-- 第3页