第四课 排序查询

• 语法：

SELECT 列名 FROM 表名 [WHERE 查询条件] ORDER BY 排序列名 DESC | ASC

• 特点：
  • DESC降序，ASC升序，默认情况下是升序排列
  • ORDER BY子句中可以支持单个字段、多个字段、表达式、函数、别名
  • ORDER BY子句通常放在查询语句的最后，LIMIT子句除外

# 1 查询员工信息，按工资从高到低排序
SELECT
	*
FROM
	employees
ORDER BY
	salary DESC;

# 2 按年薪的高低显示员工信息和年薪【表达式】
SELECT
	emp.employee_id,
	(
		emp.salary * 12 * (1 + IFNULL(commission_pct, 0))
	) AS yearSalary
FROM
	employees emp
ORDER BY
	emp.salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC;

# 3 按年薪的高低显示员工信息和年薪【别名】
SELECT
	emp.employee_id,
	(
		emp.salary * 12 * (1 + IFNULL(commission_pct, 0))
	) AS yearSalary
FROM
	employees emp
ORDER BY
	yearSalary DESC;

# 4 按员工姓名长度及员工工资【函数，多个字段排序】
SELECT
	CONCAT(
		emp.first_name,
		emp.last_name
	) AS "name",
	LENGTH(
		CONCAT(
			emp.first_name,
			emp.last_name
		)
	) AS "length",
emp.salary
FROM
	employees emp
ORDER BY
	LENGTH(`name`) DESC,emp.salary ASC