1.mysql排序
1.1.从大到小排序,不加desc默认情况下是升序
MariaDB [fei1]> select * from student order by age desc;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangshan | 20 |
| 11 | qiuxiaotian | 20 |
| 10 | qiuyi | 15 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
+----+-------------+------+
11 rows in set (0.001 sec)
1.2.根据表中column_name进行降序并只取前4个结果
MariaDB [fei1]> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
+----+-----------+------+
4 rows in set (0.000 sec)
1.3.根据表中column_name进行升序,并跳过前2个结果取后面3个结果
MariaDB [fei1]> select * from student order by age limit 2,3;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 10 | qiuyi | 15 |
| 1 | tom | 20 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
3 rows in set (0.000 sec)
2.使用group by 对取出表中的内容进行分组
MariaDB [fei1]> select name,min(age) as age from student group by name;
+-------------+------+
| name | age |
+-------------+------+
| chenshuo | 10 |
| jerry | 23 |
| lisi | 50 |
| qiuxiaotian | 20 |
| qiuyi | 15 |
| sean | 28 |
| tom | 20 |
| wangqing | 25 |
| wangwu | 100 |
| zhangshan | 26 |
+-------------+------+
10 rows in set (0.010 sec)
3,多表查询,使用左连接,右连接,内连接
3.0.多表查询语法
select 字段1,字段2… from 表1,表2… [where 条件]
3.1.内连接: 内连接查询 (只显示符合条件的数据)
注:内连接查询与多表联合查询的效果是一样的
#查询人员和部门所有信息
select * from person inner join dept on person.did =dept.did;
3.2.左连接:左外连接查询 (左边表中的数据优先全部显示)
#查询人员和部门所有信息
select * from person left join dept on person.did =dept.did;
3.2.右连接:右外连接查询 (右边表中的数据优先全部显示)
#查询人员和部门所有信息
select * from person right join dept on person.did =dept.did;