mysql数据库 排序 mysql数据库排序查询

文章目录

• MySQL高级SQL语句 常用查询

• 1 按关键字排序

• 1.1 单字段排序
• 1.2 多字段排序

• 2 对结果进行分组
• 3 限制结果条目
• 4 设置别名
• 5 通配符
• 6 子查询
• 7 NULL 值

MySQL高级SQL语句 常用查询

对 MySQL 数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。

1 按关键字排序

以使用 ORDERBY 语句来完成排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段。

SELECT column1,column2,... FROM table_name ORDER BY column1,column2,...
ASC|DESC;
其中ASC代表升序、DESC代表降序

1.1 单字段排序

在数据库中新建一个数据库进行实验操作

mysql> create table info(id int(4) not null primary key auto_increment,name char(10) not null,score decimal(5,2),address varchar(50) default '未知')engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> desc info;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(4)       | NO   | PRI | NULL    | auto_increment |
| name    | char(10)     | NO   |     | NULL    |                |
| score   | decimal(5,2) | YES  |     | NULL    |                |
| address | varchar(50)  | YES  |     | 未知    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into info (name,score,address) values ('zhangsan',80,'beijing'),('lisi',90,'nanjing'),('wangwu',70,'beijing'),('zhaoliu',60,'nanjing');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from info;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 80.00 | beijing |
|  2 | lisi     | 90.00 | nanjing |
|  3 | wangwu   | 70.00 | beijing |
|  4 | zhaoliu  | 60.00 | nanjing |
+----+----------+-------+---------+
4 rows in set (0.00 sec)

mysql> select name,score from info where score>=70 order by score;    ##如果不加默认为升序，也就是ASC
+----------+-------+
| name     | score |
+----------+-------+
| wangwu   | 70.00 |
| zhangsan | 80.00 |
| lisi     | 90.00 |
+----------+-------+
3 rows in set (0.00 sec)

mysql> select name,score from info where score>=70 order by score desc;           ##降序排序
+----------+-------+
| name     | score |
+----------+-------+
| lisi     | 90.00 |
| zhangsan | 80.00 |
| wangwu   | 70.00 |
+----------+-------+
3 rows in set (0.00 sec)

1.2 多字段排序

重新插入数据
mysql> insert into info (name,score,address) values ('tianqi',80,'beijing'),('heiba',70,'nanjing');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 80.00 | beijing |
|  2 | lisi     | 90.00 | nanjing |
|  3 | wangwu   | 70.00 | beijing |
|  4 | zhaoliu  | 60.00 | nanjing |
|  5 | tianqi   | 80.00 | beijing |
|  6 | heiba    | 70.00 | nanjing |
+----+----------+-------+---------+
6 rows in set (0.00 sec)

mysql> select id,name,score from info where score>=70 order by score desc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  2 | lisi     | 90.00 |
|  1 | zhangsan | 80.00 |
|  5 | tianqi   | 80.00 |
|  3 | wangwu   | 70.00 |
|  6 | heiba    | 70.00 |
+----+----------+-------+
5 rows in set (0.00 sec)

ORDER BY 后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定

mysql> update info set score=80 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,name,score from info where score>=70 order by score desc,id desc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  5 | tianqi   | 80.00 |
|  2 | lisi     | 80.00 |
|  1 | zhangsan | 80.00 |
|  6 | heiba    | 70.00 |
|  3 | wangwu   | 70.00 |
+----+----------+-------+
5 rows in set (0.00 sec)

2 对结果进行分组

GROUPBY从字面上看， 是以BY后面的内容对查询出的数据进行分组，就是将一个“数 据集”划分成若干个“小区域”，然后针对这些个“小区域”进行数据处理。
GROUPBY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、 求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN）GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。

语法结构
SELECT column_name,aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

把分数相同的分组，统计分数相同的人数

mysql> select count(name),score from info group by score;   
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           1 | 60.00 |
|           2 | 70.00 |
|           3 | 80.00 |
+-------------+-------+
3 rows in set (0.00 sec)

分数大于70分的进行分组，并按分数降序排序

mysql> select count(name),score from info where score>=70 group by score desc; 
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           3 | 80.00 |
|           2 | 70.00 |
+-------------+-------+
2 rows in set (0.00 sec)

求平均成绩

mysql> select avg(score) from info;
+------------+
| avg(score) |
+------------+
|  73.333333 |
+------------+
1 row in set (0.00 sec)

分数在60分以上，按分数分组，按同分数的人数倒叙排序

mysql> select count(name),score from info where score>=60 group by score order by count(name) desc;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           3 | 80.00 |
|           2 | 70.00 |
|           1 | 60.00 |
+-------------+-------+
3 rows in set (0.00 sec)

3 限制结果条目

在使用 MySQLSELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句。

SELECT column1,column2,...FROM table_name LIMIT [offset,]number

LIMIT 的第一个参数是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的位置偏移量是 0，第二条是 1，以此类推。第二个参数是设置返回记录行的最大数目。

mysql> select id,name,score from info limit 2;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 80.00 |
|  2 | lisi     | 80.00 |
+----+----------+-------+
2 rows in set (0.00 sec)

mysql> select id,name,score from info limit 3;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 80.00 |
|  2 | lisi     | 80.00 |
|  3 | wangwu   | 70.00 |
+----+----------+-------+
3 rows in set (0.00 sec)

mysql> select id,name,score from info limit 1,3;
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  2 | lisi    | 80.00 |
|  3 | wangwu  | 70.00 |
|  4 | zhaoliu | 60.00 |
+----+---------+-------+
3 rows in set (0.00 sec)

mysql> select id,name,score from info limit 0,3;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 80.00 |
|  2 | lisi     | 80.00 |
|  3 | wangwu   | 70.00 |
+----+----------+-------+
3 rows in set (0.00 sec)

limit与order by连用

mysql> select * from info order by score limit 3;
+----+---------+-------+---------+
| id | name    | score | address |
+----+---------+-------+---------+
|  4 | zhaoliu | 60.00 | nanjing |
|  6 | heiba   | 70.00 | nanjing |
|  3 | wangwu  | 70.00 | beijing |
+----+---------+-------+---------+
3 rows in set (0.00 sec)

mysql> select * from info order by score desc limit 3;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 80.00 | beijing |
|  2 | lisi     | 80.00 | nanjing |
|  5 | tianqi   | 80.00 | beijing |
+----+----------+-------+---------+
3 rows in set (0.00 sec)

4 设置别名

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，设置别名使用 AS 语句。

列的别名语法结构：
SELECT column_name AS alias_name FROM table_name;

表的别名语法结构：
SELECT column_name(s) FROM table_name AS alias_name;

在使用 AS 后，可以用 alias_name 代替 table_name，其中 AS 语句是可选的。AS 之后的别名，主要是为表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名或字段名是不会被改变的。

修改列名

mysql> select count(*) from info;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) as '总数' from info;
+--------+
| 总数   |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

不用as也同样可以修改列名

mysql> select count(*)  '总数' from info;
+--------+
| 总数   |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

当表明较长的时候，可以使用 AS 给表设置别名，在查询的过程中直接使用别名
临时设置info的别名为i

mysql> select id,name from info as i;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | zhaoliu  |
|  5 | tianqi   |
|  6 | heiba    |
+----+----------+
6 rows in set (0.00 sec)

mysql> select id,name from info  i;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | zhaoliu  |
|  5 | tianqi   |
|  6 | heiba    |
+----+----------+
6 rows in set (0.00 sec)

AS还可以用作连接语句的操作符，比如创建一个表，实现用一条 SQL 语句完成在创建表 tmp 的时候将info表内的数据写入 tmp 表。

mysql> create table tmp as select * from info;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 80.00 | beijing |
|  2 | lisi     | 80.00 | nanjing |
|  3 | wangwu   | 70.00 | beijing |
|  4 | zhaoliu  | 60.00 | nanjing |
|  5 | tianqi   | 80.00 | beijing |
|  6 | heiba    | 70.00 | nanjing |
+----+----------+-------+---------+
6 rows in set (0.00 sec)

• 在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突。
• 列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在执行查询时使用。

5 通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。 通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务。

常用的通配符：

• %：百分号表示零个、一个或多个字符
• _：下划线表示单个字符

筛选除info表中name字段以z开头的

mysql> select * from info where name like 'z%';    
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 80.00 | beijing |
|  4 | zhaoliu  | 60.00 | nanjing |
+----+----------+-------+---------+
2 rows in set (0.00 sec)

查看info表中name字段zha和liu中间有任意字符的记录

mysql> select * from info where name like 'zha_liu';
+----+---------+-------+---------+
| id | name    | score | address |
+----+---------+-------+---------+
|  4 | zhaoliu | 60.00 | nanjing |
+----+---------+-------+---------+
1 row in set (0.00 sec)

查找l开头，后面有四个字符的数据

mysql> select * from info where name like 'l___';
+----+------+-------+---------+
| id | name | score | address |
+----+------+-------+---------+
|  2 | lisi | 80.00 | nanjing |
+----+------+-------+---------+
1 row in set (0.00 sec)

两种通配符配合使用的

mysql> select * from info where name like '%w_';
+----+--------+-------+---------+
| id | name   | score | address |
+----+--------+-------+---------+
|  3 | wangwu | 70.00 | beijing |
+----+--------+-------+---------+
1 row in set (0.00 sec)

6 子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一 步的查询过滤。子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。

IN 用来判断某个值是否在给定的结果集中，通常结合子查询来使用。当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回FALSE。 若启用了 NOT 关键字，则返回值相反。需要注意的是，子查询只能返回一列数据。

查询表中分数大于或等于80的

mysql> select name,score from info where id in (select id from info where score>=80);
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan | 80.00 |
| lisi     | 80.00 |
| tianqi   | 80.00 |
+----------+-------+
3 rows in set (0.00 sec)

查询表中分数大于或等于80的

mysql> select name,score from info where name in (select name from info where score>=80);
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan | 80.00 |
| lisi     | 80.00 |
| tianqi   | 80.00 |
+----------+-------+
3 rows in set (0.00 sec)

清空表

mysql> show tables;
+----------------+
| Tables_in_home |
+----------------+
| info           |
| tmp            |
| tmp1           |
+----------------+
3 rows in set (0.00 sec)

mysql> truncate table tmp;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tmp;
Empty set (0.00 sec)

子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其他的表中

将info表中的数据全部插入到tmp表中

mysql> insert into tmp select * from info where id in (select id from info);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 80.00 | beijing |
|  2 | lisi     | 80.00 | nanjing |
|  3 | wangwu   | 70.00 | beijing |
|  4 | zhaoliu  | 60.00 | nanjing |
|  5 | tianqi   | 80.00 | beijing |
|  6 | heiba    | 70.00 | nanjing |
+----+----------+-------+---------+
6 rows in set (0.00 sec)

UPDATE 语句也可以使用子查询。UPDATE 内的子查询，在 set 更新内容时，可以是单独的一列，也可以是多列。

这里使用了多表查询
mysql> update tmp set score=score -15 where id in (select id from info where score >= 60);
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from tmp;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 65.00 | beijing |
|  2 | lisi     | 65.00 | nanjing |
|  3 | wangwu   | 55.00 | beijing |
|  4 | zhaoliu  | 45.00 | nanjing |
|  5 | tianqi   | 65.00 | beijing |
|  6 | heiba    | 55.00 | nanjing |
+----+----------+-------+---------+
6 rows in set (0.00 sec)

类似 select方式的子查询，MySQL 不支持，需要多引入一层子查询

mysql> update tmp set score=score -15 where id in (select tmp.id from (select id from tmp where score >= 60)tmp);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from tmp;                                                     
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 50.00 | beijing |
|  2 | lisi     | 50.00 | nanjing |
|  3 | wangwu   | 55.00 | beijing |
|  4 | zhaoliu  | 45.00 | nanjing |
|  5 | tianqi   | 50.00 | beijing |
|  6 | heiba    | 55.00 | nanjing |
+----+----------+-------+---------+
6 rows in set (0.00 sec)

delete子查询方式，也要多一层子查询

mysql> delete from tmp where id in (select tmp.id from (select id from tmp where score=55)tmp);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tmp;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 50.00 | beijing |
|  2 | lisi     | 50.00 | nanjing |
|  4 | zhaoliu  | 45.00 | nanjing |
|  5 | tianqi   | 50.00 | beijing |
+----+----------+-------+---------+
4 rows in set (0.00 sec)

在 IN 前面还可以添加 NOT，其作用与 IN 相反，表示否 定（即不在子查询的结果集里面）。

mysql> select name,score from tmp where id not in (select id from tmp where score<50);
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan | 50.00 |
| lisi     | 50.00 |
| tianqi   | 50.00 |
+----------+-------+
3 rows in set (0.00 sec)

mysql> select name,score from tmp where id  in (select id from tmp where score<50);
+---------+-------+
| name    | score |
+---------+-------+
| zhaoliu | 45.00 |
+---------+-------+
1 row in set (0.00 sec)

EXIST 这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空， 则返回 TRUE；反之，则返回 FALSE。

子查询语句有‘zhangsan’这个数据，所以返回值为true，执行前面的聚合函数

mysql> select count(*) as '总数' from tmp where exists (select id from tmp where name='zhangsan');
+--------+
| 总数   |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

7 NULL 值

通常 NULL 被用来表示缺失的值，在表中该字段是没有值的。在创建表时可以使用 NOTNULL 关键字，限制某些字段不为空；不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。

NULL 值与数字 0 或者空白（spaces）的字段是不同的， NULL 是没有值的，空白（spaces）表示一个为空的字符串类型，0表示一个整数型的数值。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用 IS NOT NULL 可以判断不是 NULL 值。

创建一张新表，name字段设置可以为空，验证是否设置为null值

mysql> create table num (id int(4) not null primary key auto_increment,name char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> describe num;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into num values (1,'tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into num (id) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from num;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

查询name字段为null的

mysql> select * from num where name is null;
+----+------+
| id | name |
+----+------+
|  2 | NULL |
+----+------+
1 row in set (0.00 sec)

查询name字段不为null的

mysql> select * from num where name is not null;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)

null值和空值的区别：

• 空值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。
• IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是空值的。
• 空值的判断使用=’’或者<>’’来处理。
• 在通过 count()计算有多少记录数时，如果遇到 NULL 值会自动忽略掉，遇到空值会加 入到记录中进行计算