ORDER BY 子句—对查询结果进行排序
ORDER BY 子句——对查询结果进行排序
我们一般需要让输出以某种方式进行排序，这其实是一个很重要的问题。事实上，我们经常需要能够将找出的数据做一个系统的显示。这可能是升序(aSCending) 或是降序(descending) 。这时，我们就可以运用 ORDER BY 这个指令，以用户指定的列表格式对查询结果进行排序。
一、ORDER BY 子句的格式
语法：SELECT 列名 FROM 表名
[ WHERE 条件 ]
ORDER BY 列名 [ ASC | DESC ];
叙述：[ ] 代表WHERE 子句不是一定需要的。不过，如果WHERE 子句存在的话，它是在 ORDER BY 子句之前。
ASC 代表结果会以升序（字符 A 到 Z，数字0到9 ）列出，而 DESC 代表结果会以降序（字符 Z 到 A，数字9到0）列出。如果没有写明的话，默认为 ASC 。如果需要依照多个字段来排顺序，ORDER BY 子句的语法如下：
ORDER BY 字段1 [ ASC | DESC ], 字段2 [ ASC | DESC ], . . .
例1：我们要依照 Sales 由大往小列出 Store_Information 表里的记录：
SELECT store_name, Sales, Date FROM Store_Information ORDER BY Sales DESC;
结果:store_name Sales Date
----------------------------------------------------------
Los Angeles 1500 1999-01-05
Boston 700 1999-01-08
San FranciSCo 300 1999-01-08
San Diego 250 1999-01-07
在以上的例子中，我们用字段名来指定排列顺序的依据。除了字段名外，我们也可以用字段的顺序(依据字段在 SELECT 之后列表里的顺序)。在 SELECT 后的第一个字段为1，第二个字段为2，以此类推。在上面这个例子中，我们用以下这句SQL 可以达到完全一样的效果：
SELECT store_name, Sales, Date FROM Store_Information ORDER BY 2 DESC;
二、删除大量数据方法
第4章我们介绍了用 DELETE 语句从表里删除数据的方法，但是如果需要删除的数据量很大，该怎么办呢？这里我们介绍用 ORDER BY 子句和 LIMIT 子句结合删除大量数据的方法。
假设有一个表 syslogs 有1000万条记录，需要在业务不停止的情况下删除其中 statusid=1 的所有记录，差不多有600万条。直接执行语句：DELETE FROM syslogs WHERE statusid=1;
会发现删除失败，显示 lock wait timeout exceed 的错误，因为这条语句所涉及的记录数太多。
因此我们通过 LIMIT 参数分批删除，比如每10000条进行一次删除，那么我们可以利用这样的语句来完成：
DELETE FROM syslogs
WHERE statusid=1
ORDER BY statusid
LIMIT 10000;
然后分多次执行就可以把这些记录成功删除。
三、聚合函数
既然数据库中有许多记录都是以数字的型态存在，一个很重要的用途就是要能够对这些数字做一些运算，例如将它们总合起来，或是找出它们的平均值。SQL提供的统计函数称为聚合函数.
主要的聚合函数包括：记数函数： COUNT(列名) 计算元素的个数
求和函数： SUM(列名) 对某一列的值求和，但属性必须是整型
计算平均值：AVG(列名) 对某一列的值计算平均值
求最大值： MAX(列名) 找出某一列的最大值
求最小值： MIN(列名) 找出某一列的最小值
运用函数的语法是：SELECT 函数名(列名) FROM 表名;
例2：若我们要由 Store_Information 表里：tore_name Sales Date
----------------------------------------------------------
Los Angeles 1500 1999-01-05
San Diego 250 1999-01-07
Los Angeles 300 1999-01-08
Boston 700 1999-01-08
求出营业额的总合（营业总额），可以用以下语句:
SELECT SUM(Sales) AS Total_Sales FROM Store_Information;
结果: Total_Sales
-------------
2750
其中 2750 代表所有 Sales 的总合: 1500 + 250 + 300 + 700.
例3：找出 Store_Information 表里有几条 store_name 列不是空白的记录时，可以用:
SELECT COUNT(store_name)
FROM Store_Information
WHERE store_name is NOT NULL;
结果： Count(store_name)
---------------------------
4
COUNT 和 DISTINCT 经常被合起来使用，目的是找出表里有多少条不同的记录(至于这些记录实际上是什么并不重要)。
例4：如果我们要找出我们的表格中有多少个不同的 store_name，可以用： SELECT COUNT(DISTINCT store_name) FROM Store_Information;
结果:Count(DISTINCT store_name)
----------------------------------------
3
四、别名设置
接下来，我们讨论 alias（别名）在 SQL 上的用处。最常用到的别名有两种：字段别名和表别名。
简单地来说，使用字段别名的目的是为了让 SQL 产生的结果易读。在前面的例子中，当我们计算营业额总合时，用到了 SUM(Sales) 。如果这个查询项不是一个简单的总合，而是一个复杂的计算，那么输出结果就没有这么易懂了。所以我们用了字段别名，可以确认结果中的名称是简单易懂的。要给字段取个别名，只要在 SELECT 语句的查询项后空一格，然后再写出要用的别名就可以了。
第二种别名是表别名。要给一个数据表取一个别名，只要在 FROM 子句中的表名后空一格，然后再列出要用的表别名就可以了。这在我们要用 SQL 语句从不同的表里获取数据时是很方便的。这一点我们在后面讲到表连接（join）时会看到。
1、用空格设置别名
语法：SELECT 查询项 别名 FROM 表名 表别名;
这两种别名都是放在它们要替代的项目后面，中间由一个空格分开。
我们继续使用 Store_Information 表来做例子。
例5：Store_Information 表的内容如下所示：
store_name Sales Date
----------------------------------------------------------
Los Angeles 1500 1999-01-05
San Diego 250 1999-01-07
Los Angeles 300 1999-01-08
Boston 700 1999-01-08
算出每一间店(store_name)的营业额(Sales)总合：SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales" FROM Store_Information A1 GROUP BY A1.store_name;
结果:Store Total Sales
------------------------------------------
Los Angeles 1800
San Diego 250
Boston 700
在结果中，数据本身没有不同。不同的是字段的标题。这是运用字段别名的结果。在第二个字段上，原本我们的标题是"Sum(Sales)" ，而现在我们有一个很清楚的"Total Sales" ，能够比"Sum(Sales)" 更精确地阐述这个字段的含意。
利用字段别名可以自定义字段的标题，还可以让我们用比较简洁的名称来引用某个字段。注意：当字段名称在 SELECT 语句里别重新命名时，其名称实际上并没有被修改，这种改变只在特定的 SELECT 语句里有效。
2、用 AS 设置别名
（1）AS 字段别名
语法：SELECT 字段 AS 字段别名 FROM 表名
（2）AS 表别名
语法：SELECT 字段 FROM 表名 AS 表别名
例6：给表设置别名：SELECT s.sno, s.sname FROM student as s;
例7：查询重复出现次数最多的记录：SELECT keyword, count( * ) AS count FROM article_keyword GROUP BY keyword ORDER BY count DESC LIMIT 20;
此段查询语句返回 article_keyword 表中 keyword 重复次数（count）最多的20条记录。
SELECT DISTINCT count( * ) AS count FROM article_keyword GROUP BY keyword ORDER BY count DESCLIMIT 6;
此段查询语句返回 article_keyword 表中 keyword 的重复次数（count）排名前 6 的数值。通过添加 DISTINCT 返回唯一记录。
五、查询举例
有学生信息表 student (id, name, chinese, english, math)，包括学号、姓名、语文成绩、英语成绩、数学成绩五个字段。
完成如下查询：
1、查询表中所有学生的信息
select id,name,chinese,english,math from student;
select * from student;
2、查询表中所有学生的姓名和对应的英语成绩
select name,english from student;
3、过滤表中重复数据
select distinct english from student;
4、在所有学生的英语分数上加10分特长分
select name,english+10 from student;
5、统计每个学生的总分
select name,(english+chinese+math) from student;
6、使用别名表示学生分数
select name as 姓名,(english+chinese+math) as 总分 from student;
select name 姓名,(english+chinese+math) 总分 from student;
7、查询姓名为“王五”的学生成绩
select * from student where name='王五';
8、查询英语成绩大于90分的同学
select * from student where english>90;
9、查询总分大于200分的所有同学
select * from student where (english+chinese+math)>200;
10、查询英语分数在 80－90之间的同学
select * from student where english>80 and english<90;
select * from student where english between 80 and 90;
11、查询数学分数为89,90,91的同学
select * from student where math=80 or math=90 or math=91;
select * from student where math in(80,90,91);
12、查询所有姓李的学生成绩
select * from student where name like '李%';
13、对数学成绩排序后输出
select name,math from student order by math;
14、对总分排序后输出，然后再按从高到低的顺序输出
select name from student order by (math+english+chinese) desc;
15、对姓“李”的学生成绩排序输出
select name 姓名,(math+english+chinese) 总分 from student where name like '李%' order by (math+english+chinese) desc;
16、统计一个班级共有多少学生
select count(*) from student;
select count(name) from student;
17、统计数学成绩大于90的学生有多少个
select count(*) from student where math>90;
18、统计总分大于250的人数有多少
select count(*) from student where (math+english+chinese)>250;
19、统计一个班级数学总成绩
select sum(math) from student;
20、统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(chinese),sum(english) from student;
21、统计一个班级语文、英语、数学的成绩总和
select sum(chinese+math+english) from student;
22、统计一个班级语文成绩平均分
select sum(chinese)/count(chinese) from student;
23、求一个班级数学平均分
select avg(math) from student;
24、求一个班级总分平均分
select avg(chinese+english+math) from student;
25、求班级最高分和最低分
select max(chinese+english+math), min(chinese+english+math) from student;
【参考资料】
W3SChool 在线SQL教程 http://www.w3SChool.com.cn/sql/sql_orderby.asp SQL ORDER BY 子句
http://www.w3SChool.com.cn/sql/sql_func.asp SQL 函数
http://www.w3SChool.com.cn/sql/sql_func_avg.asp SQL AVG 函数
http://www.w3SChool.com.cn/sql/sql_func_count.asp SQL COUNT() 函数
http://www.w3SChool.com.cn/sql/sql_func_max.asp SQL MAX() 函数
http://www.w3SChool.com.cn/sql/sql_func_min.asp SQL MIN() 函数
http://www.w3SChool.com.cn/sql/sql_func_sum.asp SQL SUM() 函数
http://www.w3SChool.com.cn/sql/sql_alias.asp SQL Alias（别名）
课后练习：
1. 回答以下问题：
1）说出任何SELECT语句都需要的组成部分。
2）在WHERE子句里，任何数据都需要使用单引号吗？
3）SELECT语句属于SQL语言里的哪一类命令？
4）WHERE字句里能使用多个条件吗？
5）DISTINCT选项的作用是什么？
6）选项ALL是必须的吗
7）在基于字符字段进行排序时，数字字符是如何处理的？
2. 输入以下SELECT命令，判断其语法是否正确，如果不正确就进行必要的修改。这里使用的是表EMPLOYEE_TBL。
a.
SELECT EMP_ID , LAST_NAME, FIRST_NAME,
FROM EMPLOYEE_TBL;
b.
SELECT EMP_ID , LAST_NAME
ORDER BY EMPLOYEE_TBL
FROM EMPLOYEE_TBL;
c.
SELECT EMP_ID , LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID='213764555'
ORDER BY EMP_ID;
d.
SELECT EMP_ID SSN, LAST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID='213764555'
ORDER BY 1;
e.
SELECT EMP_ID , LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID='213764555'
ORDER BY 3,1,2;
f.
SELECT LAST_NAME , FIRST_NAME , PHONE
FROM EMPLOYEE_TBL
WHERE EMP_ID='333333333';
3.使用下面这个表 CUSTOMER_TBL，编写一条 SELECT 语句，选择住在 Indiana、Ohio、Michiga 和 Illinois 并且姓名以字母A或B开头的客户，返回它们的ID和姓名（以字母顺序）。
DESCRIBE CUSOMER_TBL;
NAME NULL？ Type
CUST_ID NOT NULL VARCHAR(10)
CUST_NAME NOT NULL VARCHAR(30)
CUST_ADDRESS NOT NULL VARCHAR(20)
CUST_CITY NOT NULL VARCHAR(12)
CUST_STATE NOT NULL VARCHAR(2)
CUST_ZIP NOT NULL VARCHAR(5)
CUST_PHONE VARCHAR(10)
CUST_FAX VARCHAR(10)
4. 编写一个 SELECT 语句，从表 PRODUCTS_TBL 返回以下信息：产品描述、产品价格、每个产品5%的销售税。产品列表按价格从高到低排列。
5. 编写一个SELECT语句，从表 PRODUCTS_TBL 返回以下信息：产品描述、产品价格、每个产品5%的销售税，加上销售税的总价。产品列表按价格从高到低排列。有两种方法可以实现。
6. 有学生表 Student(Sno, Sname, Sage, Sdept)，包括字段学号、姓名、年龄、系名；
及成绩表 SC(studentid, courseid，grade)，包括字段学生学号、课程编号、成绩。
完成以下查询：
1）查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
2）查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
3）查询全体学生的姓名、年龄，要求按照年龄降序排序。
4）查询学生总数。
5）查询选修了课程的学生人数。
6）查询1号课程的学生平均成绩。
7）查询1号课程的学生最高分和最低分。