MySQL 查询排序order by语句简单应用

文章中用到的表

create table student(
	id int,
    name varchar(20),
    sex varchar(20),
    phone varchar(20),
    address varchar(20),
    primary key (id)
);
insert into student(id,name,sex,phone,address) values(1,'小A','男','10004','earth');
insert into student(id,name,sex,phone,address) values(2,'小B','男','10002','earth');
insert into student(id,name,sex,phone,address) values(3,'小C','男','10001','earth');
insert into student(id,name,sex,phone,address) values(4,'小D','男','10003','earth');
insert into student(id,name,sex,phone,address) values(5,'小F','男','10006','earth');
insert into student(id,name,sex,phone,address) values(6,'小E','男','10005','earth');

单个排序

1.查询所有的学生信息,对姓名进行升序.

select * from student order by name;

2.查询所有的学生信息,对phone进行升序.

select * from student order by phone;

多个排序

1.查询所有学生的信息,优先name升序,其次phone升序.

select * from student order by name,phone;

2.查询所有学生的信息,优先phone升序,其次name升序.

select * from student order by phone,name;

可以看到,上面两个结果是不一样的,说明order by 语句后面有多个列名时,列名的顺序是会影响结果的.越前面的列名排序的优先级越高

降序

1.查询所有学生的信息,对name降序

select * from student order by name desc;

2.查询所有学生的信息,对id升序,phone降序.

select * from student order by id,phone desc;

使用了desc就会输出降序的结果,desc关键字只会对位于他左边的第一个列名起作用.

3.查询phone最大的学生的信息.

select * from student
order by phone desc
limit 1;