mysql之DQL

DQL(数据查询语言)

• 语法：

  select 列名(多列间逗号，全部列用*) from 表名 [where 过滤条件 group by 分组查询 having 分组后的过滤 order by 排序 limit 分页];

1.简单的条件条件查询：

• 查询所有数据

select * from student(表名)

• 带条件查询

可以使用的条件：< , <= , = , > , >= , != , <> , in , between…and… , and , or ,not , is null , like 。

-- 查询编号为1004的学生的姓名和生日  
    select name,birthday from student where id=1004;
-- 查询年龄大于18的学生信息  
    select * from student where age>18;
-- 查询小明的成绩  
	select name,score from student where name='小明';
-- 查询年龄在18到20之间的所有学生  
    select * from student where age>18 and age<20;
    select * from student where age between 18 and 20; 
-- 查询除了1003外的所有学生信息  
    select * from student where id != 1003;
    select * from student where id <> 1003;
-- 查询编号为1005或者年龄为18的学生信息  
    select * from student where id=1005 or age=18;
-- 查询编号为1003和编号为1005的学生信息  
    select * from student where id=1003 and id=1005;
    select * from student where id in(1003,1005);
-- 查询编号不是1003和1005的学生信息  
    select * from student where id not in(1003,1005);
-- 查询所有姓张的学生信息, 可以使用like关键字进行模糊查询  
   --like关键字需要配合通配符使用  
   --通配符有两个: %(任意个任意字符), _(任意一个字符)  
    select * from student where name like '张%';
-- 查询名字中带国的学生信息  
    select * from student where name like '%国%';
-- 查询名字第二个字符为明的学生信息  
    select * from student where name like '_明%';
-- 查询缺考学生的信息, score没有值, 不是为0  
    select * from student where score is null;
-- 查询没有缺考的学生信息  
    select * from student where score is not null;
    select * from student where not score is null;

2.select子句

select子句的作用是选择要查询的列，还包括以下功能：

• 可以在select子句中进行运算；
• 可以用distinct过滤重复的行数据；
• 可以给列起别名。

-- 运算  
-- null不参与运算, 结果都是null  
   select name,score,scoer+10 from student;
-- 可以给列定义别名, 使用as关键字, 可以省略  
-- 别名最好不要出现特殊字符, 中文也不建议使用  
   select name, score, score+10 as new_score from student;  
   select name, score, score+10 new_score from student;  
   select name, score, score+10 '成绩' from student; 
-- ifnull函数，类似于java中的三目运算符，用于处理空值null，如果某个字段为null，则使用给定的值进行替换
   select name, score, IFNULL(score,0)+10 new_score from student;  
-- 查询所有学生的年龄  
   select distinct age from student;

3.排序

使用order by子句可以进行结果的排序，需要配合两个关键字一起使用：

​ ASC ，升序 ，ascending （ 默认值 ）

​ DESC ，降序 , descending 。

--查询所有学生信息，按照id进行升序排序
  select * from student order by id asc;
--查看年龄为20的学生信息，按照id降序排序
  select * from student order by id desc;
--可以按照别名进行排序
--查询所有学生的新成绩，并按照新成绩排名
  select name,score+10 new_score from student order by new_score desc;
--order by 后可以写数字，代表第几列(一般不用) 
  select * from student order by 6;
--rand（）函数，生成随机数，范围是[0，1）
--配合rand函数可以进行数据的随机排序，打乱数据,洗牌.
  select * from student order by rand();
--查询所有学生的信息，按照年龄降序排序，如果年龄相同，再按照id降序排序
  select * from student order by age desc,id desc;

4.分组函数

分组函数用于对查询结果进行统计，共有5个，返回结果是一个值。count() ，sum()，max()，min()，avg()。

--分组函数使用时，null值不进行统计
--统计所有学生的人数
  select count(*) from student;
  select count(id) from student;
--查询总分，最低分数和最高分数及平均分数
  select sum(score),min(score),max(score),avg(score) from student;

5.分组查询

分组查询使用group by子句实现，分组查询后需要注意以下几点：

​ 1.select子句只能查询分组字段和分组函数。

​ 2.可以使用where在分组前条件过滤。

​ 3.可以使用having在分组后进行条件过滤。

-- 按照性别进行分组, 分别统计男女同学的人数和平均成绩
   select gender,count(*) cnt,avg(score) avg_score from student group by gender;
-- 按照性别进行分组，分别统计男女同学的人数和平均成绩，排除成绩低于70的学生信息
   select gender,count(*) cnt,avg(score) avg_score from student where score>=70 group by gender; 
-- 按照年龄进行分组，分别统计不同年龄同学的人数和平均成绩，排除人数小于2的分组信息
-- where和having的区别在于where中无法使用分组函数，而having中可以使用
   select age, count(*) cnt, avg(score) avg_score from student group by age having count(*)>=2;
   select age, count(*) cnt, avg(score) avg_score from student group by age having cnt>=2;

6.分页查询

MySQL中，可以通过limit关键词实现分页查询，limit是MySQL的特有语法，limit后面需要跟两个参数，第一个参数是查询的起始索引（从0开始），第二个参数是查询的记录数，limit必须出现在SQL的末尾。

--每页显示两条学生信息，完成分页显示
--page：第几页
--size：每页显示几条数据
  select * from student order by id limit (page-1) * size, size;
--第一页
  select * from student order by id limit 0,2;
--第二页
  select * from student order by id limit 2,2;
--第三页
  select * from student order by id limit 4,2;