MySQL--基础回顾 (三) DQL详解

一、简介

MySQL 管理数据库：

1. 查看所有数据库----show database;
2. 创建数据库----create database 库名;
3. 查看数据库创建数据语句----show create database 库名;
4. 删除数据库----drop database 库名;

表的管理：

1. 查看所有表----show tables;
2. 创建表----

　　　　create table student (

　　　　　　id int,

　　　　　　name,varchar(20),

　　　　　　age int,

　　　　)

查看表结构

1. 以SQL格式返回----show create table 表名;
2. 以表格方式----desc 表名;

删除表：

1. 删除表名----drop table 表名;

管理数据：增删改查

1. 插入数据----insert  into 表名  (列名)  value  (值);
2. 修改数据----update  表名  set  列名=值   where  条件;
3. 删除数据

• 删除表中所有数据----delete  form  表名  where 条件;
• 删除所有数据----truncate  [table]  表名;

查询数据：

1. 查询所有列----select   *  from 表名;
2. 查询时指定别名---select  字段名  as  别名(可以省略);
3. 去除重复数据----select distinct  列名  from  表名;

条件查询：

1. 显示在某一区间值---10~100   between   10  and  100;
2. 多个条件中符合1个值----SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);
3. 模糊查询----SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

二、DQL查询

2.1　排序

通过 ORDER BY 子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)

　　SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
　　ASC: 升序，默认值
　　DESC: 降序

单列排序

　　只按某一个字段进行排序，单列排序

组合排序

　　同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推。

　　SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];

　　示例----查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序

　　　　　select * from student order by age desc, math asc;

2.2　聚合函数

　　之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，

它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。

五个聚合函数

　　　　max   (列名)----求这一列最大值

　　　　min    (列名)----求这一列最小值

　　　　avg    (列名)----求这一列平均值

　　　　count (列名)----统计这一列有多少条记录

　　　　sum   (列名)----对这一列求总和

使用格式：　　

　　　　SELECT 聚合函数(列名) FROM 表名;

示例：　

-- 查询年龄大于 20 的总数
select count(*) from student where age>20;
-- 查询数学成绩总分
select sum(math) 总分 from student;
-- 查询数学成绩平均分
select avg(math) 平均分 from student;
-- 查询数学成绩最高分
select max(math) 最高分 from student;
-- 查询数学成绩最低分
select min(math) 最低分 from student;

注意：　　　　　　　　

　　　　对于 NULL 的记录不会统计，如果统计个数则不要使用有可能为 null 的列，但如果需要把 NULL也统计进去？

　　　　利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏。

　　-- 查询 id 字段，如果为 null，则使用 0 代替

　　select count(ifnull(id,0)) from student;

2.3　分组查询

　　分组查询是指使用 GROUP BY 语句对查询信息进行分组，相同数据作为一组

　　SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。

分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;

注意：

　　当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的。

　　group by 后面不能出现 where，使用 having。

示例：

查询男女各多少人

　　1.查询所有数据,按性别分组。
　　2.统计每组人数
　　select sex, count(*) from student3 group by sex;

查询年龄大于 25 岁的人,按性别分组,统计每组的人数

1. 先过滤掉年龄小于 25 岁的人。
2. 再分组。
3. 最后统计每组的人数

　　　select sex, count(*) from student3 where age > 25 group by sex ;

查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据

　　-- 对分组查询的结果再进行过滤
　　SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;

having 与 where 的区别

where 子句

　　1、对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。
　　2、 where 后面不可以使用聚合函数

having 子句

　　1、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
　　2、having 后面可以使用聚合函数

2.4　limit--限制查询记录的条数

　　LIMIT 是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数。

　　SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT 子句];

LIMIT 语法格式：　

　　LIMIT offset,length;
　　offset：起始行数，从 0 开始计数，如果省略，默认就是 0
　　length： 返回的行数

示例：

　　-- 查询学生表中数据，从第 3 条开始显示，显示 6 条。
　　select * from student3 limit 2,6;

使用场景：

　　京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。

-- 如果第一个参数是 0 可以省略写：
select * from student3 limit 5;
-- 最后如果不够 5 条，有多少显示多少
select * from student3 limit 10,5;