首页 / MYSQL / MySQL 查询进阶篇

MySQL 查询进阶篇

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 查询进阶篇，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5764字，纯文字阅读大概需要9分钟。

内容图文

  • group by的用法
  • 集合函数的使用
  • order by的用法
  • having对组的管理
  • limit的用法
  • 查重distinct的用法

1.1、group by的用法

create table student
(
    sno int primary key,
    name char(10) not null,
    age int,
    sex char(5) not null,
    city char(5) not null,
    score int not null
);

insert into student(sno,name,age,sex,city,score)
value(1001,"刘军",18,"男","武汉",91),
(1002,"刘军",18,"男","杭州",90),
(1003,"李俊杰",20,"男","北京",21),
(1004,"王凡",17,"男","上海",76),
(1005,"伊利",18,"男","天津",61),
(1006,"蒙牛",19,"男","重庆",99),
(1007,"蓝莓",17,"女","西安",98),
(1008,"缇米",19,"女","南京",87),
(1009,"赛亚",17,"女","沈阳",66),
(1010,"李磊",19,"男","银川",43),
(1011,"比利",16,"男","西藏",21);

select * from student group by sex;

//如果我对性别进行分组，那么它只会显示第一个为男的行和第一个为女的行。

select * from student group by age;

//如果我对年龄进行分组，那么它只会显示第一个为18，19,20岁的个人信息
//分组的意义在于，把它关联的列合并为一组，可以查看相同列有多少条数据，常常配合集合函数使用。上述只做了解，实际不会出现这种写法

 

2.1、集合函数的使用

函数名 count（） sum() avg() max（） min()
作用 统计记录的条数 计算字段值的总和 计算字段的值的平均值 查询值的最大值 查询字段的最小值

 

 

create table student
(
    sno int primary key,
    name char(10) not null,
    age int,
    sex char(5) not null,
    city char(5) not null,
    score int not null
);

insert into student(sno,name,age,sex,city,score)
value(1001,"刘军",18,"男","武汉",91),
(1002,"刘军",18,"男","杭州",90),
(1003,"李俊杰",20,"男","北京",21),
(1004,"王凡",17,"男","上海",76),
(1005,"伊利",18,"男","天津",61),
(1006,"蒙牛",19,"男","重庆",99),
(1007,"蓝莓",17,"女","西安",98),
(1008,"缇米",19,"女","南京",87),
(1009,"赛亚",17,"女","沈阳",66),
(1010,"李磊",19,"男","银川",43),
(1011,"比利",16,"男","西藏",21);


select sex ,count(*)  from  student  group by sex;
//查询性别中，男女各有多少人

select count(*) from student group by score>60;
//大于60的分一组，小于60的分一组，可以看到多少人及格了，多少人没及格

select  sum(score)  score from student;
//直接使用sum对score进行累加，算出一个班级的总分数

select sex ,sum(score)  score from student group by sex;
//计算男生总分数，女生总分数

select sex,avg(score) score from student group by sex;
//计算男生平均分，女生平均分

select avg(score) from student;
//计算整个班的平均分数

select age ,avg(score) from student group by age;
//查看各个年龄的平均成绩

select max(score) from student;
//查询最高成绩

select max(age) from student;
//查询年龄最大值


--前方有坑

//查询最大年龄的所有信息

select sno, name,sex,city,max(age) from student;

//如果你这么写，恭喜你答错了，这里除了max(age)会打印最大年龄外，其他信息均是第一条学生的信息。

//正确的写法

select * from student where age in(select max(age) from student);

//当然这只是其中一种写法

select * from student where age in(select max(age) from student);

//查询学生成绩最少的个人信息

3.1、order by的用法

create table student
(
    sno int primary key,
    name char(10) not null,
    age int,
    sex char(5) not null,
    city char(5) not null,
    score int not null
);

insert into student(sno,name,age,sex,city,score)
value(1001,"刘军",18,"男","武汉",91),
(1002,"刘军",18,"男","杭州",90),
(1003,"李俊杰",20,"男","北京",21),
(1004,"王凡",17,"男","上海",76),
(1005,"伊利",18,"男","天津",61),
(1006,"蒙牛",19,"男","重庆",99),
(1007,"蓝莓",17,"女","西安",98),
(1008,"缇米",19,"女","南京",87),
(1009,"赛亚",17,"女","沈阳",66),
(1010,"李磊",19,"男","银川",43),
(1011,"比利",16,"男","西藏",21);

//order by通常配合group by使用，也会有单独使用的情况
//asc  升序   desc  降序

select * from student order by age asc;
//升序打印学生年龄

select * from student order by score desc;
//降序查看学生成绩

4.1、having对分组的管理

create table student
(
    sno int primary key,
    name char(10) not null,
    age int,
    sex char(5) not null,
    city char(5) not null,
    score int not null
);

insert into student(sno,name,age,sex,city,score)
value(1001,"刘军",18,"男","武汉",91),
(1002,"刘军",18,"男","杭州",90),
(1003,"李俊杰",20,"男","北京",21),
(1004,"王凡",17,"男","上海",76),
(1005,"伊利",18,"男","天津",61),
(1006,"蒙牛",19,"男","重庆",99),
(1007,"蓝莓",17,"女","西安",98),
(1008,"缇米",19,"女","南京",87),
(1009,"赛亚",17,"女","沈阳",66),
(1010,"李磊",19,"男","银川",43),
(1011,"比利",16,"男","西藏",21);

//having必须是配合分组函数group by使用，它是对分组的一种管理，通常配合集合函数一起使用

select * from student group by age having count(*) > 2;
//这里对年龄进行分组，然后筛选出相同年龄大于2的年龄。

select * from student group by sno having score > 60;
//查询分数大于60的学生信息

 

where 和 having区别：https://blog.csdn.net/yaoyuanbo/article/details/81295981

 

5.1、limit的用法

create table sss
(
    id int
);

insert into sss(id)
value(1),(2),(3),(4),(5),(6);


select * from sss limit 5;
//只显示到第六行，如果没有则显示到最后一行

select * from sss limit 2,3;
//这里2表示第三位数，向后拿取3行

select * from sss limit 5,9999;
//第二参数给出上限，直接默认为从第6行打印到结束

select * from sss limit 5,-1;
//从第六行打印到最后，我用的Navicat Premium 12版本可能低了点，竟然不能使用。

6.1、查重distinct的用法

create table number
(
    id int,
    num int
);

insert into number(id,number)
value(1,2),(2,3),(3,4),(4,5),(5,6),(1,7),(2,8);


//语法格式，distinct只能放在select后面

select id,distinct num from number;

//上面这样子的写法会报错

select distinct id from number;

//这里是获取id不重复的值

select distinct id,num from number;

//这里是获取id，num组合起来不重复的值，因为这里没有一条是重复的，所以全部打印

select count(distinct id) from number;

//这里计算id不重复的列只有5条

select * ,count(distinct num) from number  group  by id;

题外

distinct和group by都有去重的作用，如有需要可以了解下

转载：https://blog.csdn.net/ljl890705/article/details/70602442

MySQL 查询进阶篇

标签：tin   max   ali   class   ima   sum   group   order by   大于   

本文系统来源：https://www.cnblogs.com/cheneyboon/p/11369049.html

内容总结

以上是互联网集市为您收集整理的MySQL 查询进阶篇全部内容，希望文章能够帮你解决MySQL 查询进阶篇所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/511183.html

来源：【匿名】