MySQL-查询增强

# 查询增强
SELECT * FROM dept;
SELECT * FROM emp;
SELECT * FROM salgrade;

SELECT * FROM emp
	WHERE hiredate > '1992-01-01'

-- 模糊查询like，%表示多个任意字符，_表示单个任意字符	
SELECT ename,sal FROM emp
	WHERE ename LIKE 'S%'
SELECT ename,sal FROM emp
	WHERE ename LIKE '__O%'

-- 判断值为空不能用＝，用is null，不为空为is not null
SELECT * FROM emp
	WHERE mgr IS NOT NULL;

-- 查询表结构
DESC emp

-- order by,asc升序默认，desc降序
SELECT * FROM emp
	ORDER BY sal ASC;
SELECT * FROM emp
	ORDER BY sal ASC,deptno DESC;

# 分页查询
SELECT * FROM dept;
SELECT * FROM emp;
SELECT * FROM salgrade;

-- 第一页
SELECT * FROM emp
	ORDER BY empno 
	LIMIT 0,3 -- limit start，row 从start+1取row行数据
-- 第二页
SELECT * FROM emp
	ORDER BY empno 
	LIMIT 3,3 -- limit start，row 从start+1取row行数据

-- 增强group by
-- 显示每种岗位的雇员总数、平均工资
SELECT COUNT(*) ,AVG(sal),job
	FROM emp
	GROUP BY job;

-- 显示雇员总数,以及获得补助的雇员数
SELECT COUNT(*),COUNT(comm)
	FROM emp
-- 统计没有获得补助的雇员数
SELECT COUNT(IF(comm IS NULL,1,NULL))
	FROM emp;

-- 显示管理者的总人数
SELECT COUNT(DISTINCT mgr) 
	FROM emp
	
-- 显示雇员工资的最大差额
SELECT MAX(sal) - MIN(sal)
	FROM emp;

-- 各函数先后顺序不能错	
SELECT AVG(sal),deptno FROM emp
	GROUP BY deptno
	HAVING AVG(sal) > 1000
	ORDER BY AVG(sal) DESC
	LIMIT 0,2