共计 2910 个字符，预计需要花费 8 分钟才能阅读完成。
查问 Syntax:
SELECT
[* | DISTINCT | col_name | col_name as new_name]
FROM table
WHERE condition
GROUP BY {col_name} HAVING where_condition
ORDER BY {col_name} [ASC | DESC]
LIMIT {[offset,] row_count | row_count OFFSET offset}
查问列
。select * from ..
select from emp; 会查问所有的记录行；生产中杜绝应用（select from emp; 会做全表扫描）
select * from emp where empno=’7900′; 会取出所有的字段；生产中杜绝应用
起因 1：占用更多的内存空间（缓冲池 innodb buffer pool）
起因 2：查问条件是一般索引，就会进行回表查问（随机 io，查问性能升高）
。select col_name from ..
select ename, job from emp where empno=’7900′;
索引笼罩（using index）
。select col_name as new_col_name from ..
select sum(sal) as t_sal from emp; 取别名
。select distinct col_name from ..
select distinct job from emp; 去重
where 子句
。作用：用于条件过滤，有条件的从表中获取数据
。构造：where col_name 操作符 值
where name = ‘ergou’;
。where 子句中的操作符
| 罕用操作符 | |
|---|---|
| < , <= , > , >= , = , != | |
| between..and.. | |
| in(set) | |
| like | |
| not like | |
| is null | |
| and | |
| or | |
| not |
范畴操作符：< , <= , > , >= , between..and..，in(set)，not in(set)
select * from emp where empno > 7900;
select * from emp where empno >= 7900;
select * from emp where empno between 7900 and 9999; 查问后果蕴含两边的值
select * from emp where empno in(7900,7902);
select * from emp where empno not in(7900,7902); 在生产中杜绝应用；
等值查问：=
select * from emp where ename = ‘smith’;
注意事项：
字符串类型、日期类型的 sql 语句 where 条件中，肯定要加引号，否则将会产生隐式转换，将进行全表扫描，效率极低。
explain select * from emp where ename = ‘007’; 应用到索引，索引利用级别为 type:ref
explain select * from emp where ename = 007; 没有应用到索引，将会进行全表扫描，sql 效率非常低
不等值查问：!=
生产中不倡议应用；如果是一般索引作为查问条件，将进行全表扫描；
select * from emp where empno != 7900;
含糊查问：like，not like
select * from emp where ename like ‘s%’;
explain select * from emp where ename like ‘%n’; 生产中杜绝应用右边含糊查问
select * from emp where mgr is null;
逻辑操作符：and or not
select ename from emp where empno > 7900 and job= ‘clerk’;
select ename from emp where empno=7934 or empno=9999;
。where 子句查问案例
查问工资高于 2000 的员工
select * from emp where sal > 2000;
查问工资在 2000 到 3500 的员工状况
select * from emp where sal between 2000 and 3500;
查问员工姓名首字母为 S 的员工姓名和工资
select ename ,sal from emp where ename like ‘s%’;
查问 empno 为 7839,7902,7934 的员工状况
select * from emp where empno in(7839,7902,7934);
查问没有下级的员工状况
select * from emp where mgr is null;
查问工资高于 500 或是岗位为 MANAGER，同时还要满足他们的姓名首写字母为 J 的员工信息
select * from emp where (sal > 500 or job = ‘MANAGER’) and ename like ‘j%’;
group by .. having 子句
。作用：对查问后果进行分组统计,having 用于限度分组后果的显示
。构造：group by col_name1, col_name2 having ..
。注意事项：select @@sql_mode; ONLY_FULL_GROUP_BY
查问列必须是 group by 前面的字段或者是聚合函数，呈现其余列将报错
。group by 子句查问案例
查问每个部门的平均工资和最高工资
select deptno,avg(sal), max(sal) from emp group by deptno;
查问每个部门的每种岗位的平均工资和最低工资
select deptno, job, avg(sal),min(sal) from emp group by deptno,job;
查问部门平均工资低于 2000 的部门号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
order by 子句
。作用：用于排序
。构造：order by col_name [asc | desc];
select sal from emp order by sal ;
。注意事项：排序字段是索引，如果指定排序为降序，无奈应用索引的排序，须要从新排序
。order by 子句案例
查问工资的从低到高的程序显示员工的信息
select * from emp order by sal asc;
查问依照员工的年工资进行降序排序显示员工的姓名和年工资
select ename , sal*12 as y_sal from emp order by y_sal desc;
limit 子句
。作用：用于限度取出的记录数，也能够用于分页
select * from emp limit 2;
分页：limit (pagenow-1)*pagesize,pagesize
。分页查问案例：
按员工号升序查问员工姓名、工资，每页显示 3 条记录。请别离显示 第一页，第二页，第三页
select ename,sal from emp order by empno asc limit 0,3;
select ename,sal from emp order by empno asc limit 3,3;
select ename,sal from emp order by empno asc limit 6,3;
