前言、
在mysql 中,可以使用SELECT 语句来查询数据,查询数据是指从数据库中根据需求,使用不同的查询方式来获取不同的数据,是使用频率最高,最重要的操作。
今天给大家带来查询语句的高级语法
一、按关键字排序
1、使用order by语句来实现排序
2、排序可针对一个或多个字段
3、ASC:升序,默认排序方式
4、DESC:降序
5、order by的语法结构
select 字段1,字段2 from 表明 order by 字段1 desc|asc,字段2 desc|asc;
6、按单子段排序
按照成绩降序排序
+----------+----------+---------+
| id | name | chengji |
+----------+----------+---------+
| 20201001 | lili | 80 |
| 20201002 | zhangsan | 85 |
| 20201003 | mazi | 90 |
+----------+----------+---------+
mysql> mysql> select id,name,chengji from chengji where chengji>50 ordhengji desc;
+----------+----------+---------+
| id | name | chengji |
+----------+----------+---------+
| 20201003 | mazi | 90 |
| 20201002 | zhangsan | 85 |
| 20201001 | lili | 80 |
+----------+----------+---------+
3 rows in set (0.01 sec)
按照chengji升序排列 且>50
mysql> select id,name,chengji from chengji where chengji>50 order by chengji asc;
+----------+----------+---------+
| id | name | chengji |
+----------+----------+---------+
| 20201001 | lili | 80 |
| 20201002 | zhangsan | 85 |
| 20201003 | mazi | 90 |
+----------+----------+---------+
3 rows in set (0.00 sec)
多字段排序
主参考字段在前面,按照成绩降序,辅助参考字段写在后面,按照ID升序 先比较主参考字段,如果相同再按照辅助字段排序
mysql> select id,name,chengji from chengji where chengji>70 order by chengji desc,id asc;
+----------+----------+---------+
| id | name | chengji |
+----------+----------+---------+
| 20201003 | mazi | 90 |
| 20201002 | zhangsan | 85 |
| 20201004 | sisi | 85 |
| 20201001 | lili | 80 |
+----------+----------+---------+
4 rows in set (0.00 sec)
二、对结果进行分组
1、使用group by 语句来实现分组
2、通常结合聚合函数一起使用
3、可以按照一个或多个字段对结果进行分组
4、group by 的语法结构
按照成绩进行分组,并进行计数。统计成绩相同的那么数量
mysql> select count(name),chengji from chengji where chengji>79 group by chengji;
+-------------+---------+
| count(name) | chengji |
+-------------+---------+
| 1 | 80 |
| 2 | 85 |
| 1 | 90 |
+-------------+---------+
3 rows in set (0.00 sec)
按照上面的排序 在按照降序排列
mysql> select count(name),chengji from chengji where chengji>79 group by chengji desc;
+-------------+---------+
| count(name) | chengji |
+-------------+---------+
| 1 | 90 |
| 2 | 85 |
| 1 | 80 |
+-------------+---------+
3 rows in set (0.00 sec)
三、限制结果条目
1、只返回select查询结果的第一行或前几行
2、使用limit 语句限制条目
3、limit 语法结构
select 字段1,字段2 from 表名 limit [offset,] number;
offset: 位置偏移量,从0开始
number:返回记录行的最大数目
mysql> select * from chengji;
+----------+----------+---------+
| id | name | chengji |
+----------+----------+---------+
| 20201001 | lili | 80 |
| 20201002 | zhangsan | 85 |
| 20201003 | mazi | 90 |
| 20201004 | sisi | 85 |
+----------+----------+---------+
4 rows in set (0.00 sec)
只显示前两行
mysql> select id,name,chengji from chengji limit 2;
+----------+----------+---------+
| id | name | chengji |
+----------+----------+---------+
| 20201001 | lili | 80 |
| 20201002 | zhangsan | 85 |
+----------+----------+---------+
2 rows in set (0.00 sec)
mysql> select * from chengji;
+----------+----------+---------+
| id | name | chengji |
+----------+----------+---------+
| 20201001 | lili | 80 |
| 20201002 | zhangsan | 85 |
| 20201003 | mazi | 90 |
| 20201004 | sisi | 85 |
+----------+----------+---------+
4 rows in set (0.00 sec)
限制显示结果,从第2行开始,显示两行
mysql> select id,name,chengji from chengji limit 2,2;
+----------+------+---------+
| id | name | chengji |
+----------+------+---------+
| 20201003 | mazi | 90 |
| 20201004 | sisi | 85 |
+----------+------+---------+
2 rows in set (0.00 sec)
显示前三行
mysql> select id,name,chengji from chengji limit 3;
+----------+----------+---------+
| id | name | chengji |
+----------+----------+---------+
| 20201001 | lili | 80 |
| 20201002 | zhangsan | 85 |
| 20201003 | mazi | 90 |
+----------+----------+---------+
3 rows in set (0.00 sec)
四、设置别名
1、使用as语句设置别名,关键字as可shenglue
2、设置别名时,保证不能从库中其他表或字段名称冲突
3、别名的语法结构
字段别名:
select 字段 from 表名 as 别名
设置字段的别名、
、mysql> select count(*) as 总行数 from chengji;
+-----------+
| 总行数 |
+-----------+
| 4 |
+-----------+
1 row in set (0.00 sec)
五、通配符
1、用于替换字符串中的部分字符
2、通常配合like一起使用,并协同where完成查询
3、常用的通配符
%:表示零个,一个或多个即任意字符
_: 表示单个字符
name字段中以z开头的任意记录
mysql> select id,name from chengji where name like 'z%';
+----------+----------+</