MySQL数据库学习之排序查询和分组查询

第二章

第一节 排序查询 

	案例：按薪水降序排序
		mysql> select ename,sal from emp order by sal desc;
		+--------+---------+
		| ename  | sal     |
		+--------+---------+
		| Andrew | 6000.00 |
		| jenny  | 5500.00 |
		| White  | 4500.00 |
		| Jack   | 4000.00 |
		| cathy  | 4000.00 |
		| merry  | 3500.00 |
		| Andy   | 3500.00 |
		| Angus  | 3000.00 |
		| lucy   | 2800.00 |
		| Hellen | 2700.00 |
		| ford   | 2700.00 |
		| Jacob  | 2700.00 |
		+--------+---------+

	案例：按薪水升序排序(asc可以省略)
		mysql> select ename,sal from emp order by sal asc;
		+--------+---------+
		| ename  | sal     |
		+--------+---------+
		| Hellen | 2700.00 |
		| ford   | 2700.00 |
		| Jacob  | 2700.00 |
		| lucy   | 2800.00 |
		| Angus  | 3000.00 |
		| merry  | 3500.00 |
		| Andy   | 3500.00 |
		| Jack   | 4000.00 |
		| cathy  | 4000.00 |
		| White  | 4500.00 |
		| jenny  | 5500.00 |
		| Andrew | 6000.00 |
		+--------+---------+
	
	案例：按薪水升序，姓名降序排序(asc可以省略)
		mysql> select ename,sal from emp order by sal asc,ename desc;

第二节 数据处理函数/单行处理函数

	lower、upper、substr、length、trim
		案例：把名字大写显示,字段重命名
		mysql> select upper(ename) as name from emp;
		+--------+
		| name   |
		+--------+
		| JACK   |
		| WHITE  |
		| CATHY  |
		| MERRY  |
		| LUCY   |
		| HELLEN |
		| JENNY  |
		| ANDREW |
		| FORD   |
		| ANGUS  |
		| ANDY   |
		| JACOB  |
		+--------+
	
		案例：把名字一部分显示出来
		mysql> select substr(ename,1,3) as ename from emp;
		+-------+
		| ename |
		+-------+
		| Jac   |
		| Whi   |
		| cat   |
		| mer   |
		| luc   |
		| Hel   |
		| jen   |
		| And   |
		| for   |
		| Ang   |
		| And   |
		| Jac   |
		+-------+

第三节 分组查询

		分组函数/聚合函数/多行处理函数
		--count、sum、avg、max、min
		--group by 【表示通过哪个或者哪些字段进行分组】
		--having【having和where功能相同，where在group by之前完成过滤，having在group by之后完成过滤】
		案例：按照工作进行分组，求每种工作的最大薪水
		mysql> select job,max(sal) from emp group by job;
		+-----------+----------+
		| job       | max(sal) |
		+-----------+----------+
		| salesman  |  5500.00 |
		| Manager   |  6000.00 |
		| Secretary |  4000.00 |
		| Analyst   |  3500.00 |
		| Assistant |  3000.00 |
		| Typist    |  2700.00 |
		+-----------+----------+
		案例：求不同部门不同工作的最大薪水
		mysql> select deptno,job,max(sal) from emp group by deptno,job;
		+--------+-----------+----------+
		| deptno | job       | max(sal) |
		+--------+-----------+----------+
		|      3 | salesman  |  5500.00 |
		|      2 | Manager   |  4500.00 |
		|      1 | Secretary |  4000.00 |
		|      4 | Analyst   |  3500.00 |
		|      5 | Assistant |  2800.00 |
		|      6 | Typist    |  2700.00 |
		|      1 | Manager   |  6000.00 |
		|      7 | salesman  |  3500.00 |
		|      3 | Assistant |  3000.00 |
		|      2 | Secretary |  2700.00 |
		+--------+-----------+----------+
		案例：找出每种工作岗位的平均薪水，要求显示平均薪水大于1500
		mysql> select job,avg(sal) from emp group by job having avg(sal)>1500;
		+-----------+-------------+
		| job       | avg(sal)    |
		+-----------+-------------+
		| salesman  | 3925.000000 |
		| Manager   | 5250.000000 |
		| Secretary | 3350.000000 |
		| Analyst   | 3500.000000 |
		| Assistant | 2900.000000 |
		| Typist    | 2700.000000 |
		+-----------+-------------+