sql order by 语句的使用方法

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照升序对记录进行排序。

如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

实例

SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

注意：在以上的结果中有两个相等的公司名称 (www.111cn.net)。只有这一次，在第一列中有相同的值时，第二列是以升序排列的。如果第一列中有些值为 nulls 时，情况也是这样的。

实例

mysql教程> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->              values (1,'Jason',    'Martin', '19960725', '20060725', 1234.56, 'Toronto', 'Programmer');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(2,'Alison',   'Mathews', '19760321', '19860221', 6661.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(3,'James',    'Smith',    '19781212', '19900315', 6544.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78, 'Vancouver','Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(6,'Linda',    'Green',    '19870730', '19960104', 4322.78,'New York', 'Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(7,'David',    'Larry',    '19901231', '19980212', 7897.78,'New York', 'Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(8,'James',    'Cat',     '19960917', '20020415', 1232.78,'Vancouver', 'Tester');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> SELECT * FROM employee ORDER BY city;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager     |
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.02 sec)

order by还可以对日期进行排序，
实例

mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->              values (1,'Jason',    'Martin', '19960725', '20060725', 1234.56, 'Toronto', 'Programmer');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(2,'Alison',   'Mathews', '19760321', '19860221', 6661.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(3,'James',    'Smith',    '19781212', '19900315', 6544.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78, 'Vancouver','Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(6,'Linda',    'Green',    '19870730', '19960104', 4322.78,'New York', 'Tester');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(7,'David',    'Larry',    '19901231', '19980212', 7897.78,'New York', 'Manager');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary, City,       Description)
    ->               values(8,'James',    'Cat',     '19960917', '20020415', 1232.78,'Vancouver', 'Tester');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> SELECT first_name, start_date FROM employee ORDER BY start_date;
+------------+------------+
| first_name | start_date |
+------------+------------+
| Alison     | 1976-03-21 |
| James      | 1978-12-12 |
| Celia      | 1982-10-24 |
| Robert     | 1984-01-15 |
| Linda      | 1987-07-30 |
| David      | 1990-12-31 |
| Jason      | 1996-07-25 |
| James      | 1996-09-17 |
+------------+------------+
8 rows in set (0.00 sec)

这样在我们数据库教程中以date形式存放数据也可以方便快捷进行排序操作了。

时间： 2024-11-09 03:55:20

sql order by 语句的使用方法的相关文章

sql insert select语句的使用方法

sql insert select语句的使用方法 INSERT 语句中的 SELECT 子查询可用于将一个或多个其它的表或视图的值添加到表中.使用 SELECT 子查询可同时插入多行. 下面的 INSERT 语句将 titles 中数据的 type 是 modern cooking 的所有行的数据插入到一个单独的表中: USE pubsINSERT INTO MyBooks SELECT title_id, title, type FROM titles WHERE type = '

SQL ORDER BY 语句用法

ORDER BY 语句用于对结果集进行排序. ORDER BY 语句 ORDER BY 语句用于根据指定的列对结果集进行排序. ORDER BY 语句默认按照升序对记录进行排序. 如果您希望按照降序对记录进行排序,可以使用 DESC 关键字. 原始的表 (用在例子中的): Orders 表: Company OrderNumber IBM 3532 W3School 2356 Apple 4698 W3School 6953 实例 1 以字母顺序显示公司名称: SELECT Company, O

sql union 查询语句的使用方法

UNION运算符可以将两个或两个以上上SELECT语句的查询结果集合合并成一个结果集合显示,即执行联合查询.UNION的语法格式为 SELECT <fields> FROM <table> WHERE <condition> UNION SELECT <fields> FROM <table> WHERE <condition> 联合查询时,查询结果的列标题为第一个查询语句的列标题.因此,要定义列标题必须在第一个查询语句中定义

使用SQL语句取随机数的方法

SQL随机查询语句,[数据库使用技巧]SQL语句生成随机数,如何随机显示sql查询语句查询的数据记录集中的20条,随机取出若干条记录的SQL语句,使用SQL语句取随机数的方法方法如下: SQL Server:Select TOP N * From TABLE order By NewID() Access:dim rrandomizer = rndsql = "select top 10 * from table order by rnd(" & r & "

mysql数据库的sql语句常用优化方法

mysql数据库的sql语句常用优化方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where

Sql in语句的使用方法教程

Sql in语句的使用方法教程在in的在In允许您指定多个值在WHERE子句. SQL的语法 SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...) 来看个实例吧.下面是数据 _Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pet

orale 创建视图sql语句与使用方法

SQL CREATE VIEW 语句什么是视图? 在 SQL 中,视图是基于 SQL 语句的结果集的可视化的表. 视图包含行和列,就像一个真实的表.视图中的字段就是来自一个或多个数据库教程中的真实的表中的字段.我们可以向视图添加 SQL 函数.WHERE 以及 JOIN 语句,我们也可以提交数据,就像这些来自于某个单一的表. 注释:数据库的设计和结构不会受到视图中的函数.where 或 join 语句的影响. SQL CREATE VIEW 语法 CREATE VIEW view_name A

关于sql server查询语句的写法。

问题描述关于sql server查询语句的写法. 怎样写一个查询语句select distinct ID from TrainTime order by ID select Station from TrainTime where S_No='1'order by ID select Stationfrom TrainTime where D_Time='-' order by ID 怎样把这3个查询语句写为一句啊,让查询查来的结果为这3列数据. 因为我想建一个表,为3列,列名为:列车车次.起

Mysql中order by语句的优化详解

在某些情况中,MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序.where条件和order by使用相同的索引,并且order by的顺序和索引顺序相同,并且order by的字段都是升序或者都是降序. 一.建议使用一个索引来满足Order By子句. 在条件允许的情况下,笔者建议最好使用一个索引来满足Order By子句.如此的话,就可以避免额外的排序工作.这里笔者需要强调的一点是及时Order By子句不确切匹配索引,但是只要Where子句中所有未使用的索引部分和所有