1.在where字句中使用别名。
2.多字段合并查询
3.新增加一列,内容由其他部分计算得出
4.随机返回有限的(非全部)查询结果
5.将null值显示为其他值
6.对查询结果进行多关键字排序
7.如果某个字段不存在于另一个表中,找出他
- //错误!!!!
- //直接这样写是不行的,where字句是不认识别名的
- select sal as salary, comm as commission
- from emp
- where salary < 5000
- //正确的方法,使用一个子视图
- select
- from (
- select sal as salary, comm as commission
- from emp
- ) x
- where salary < 5000
- //将name和age这两个字段的内容合并成一个ageInfo字段显示输出
- select concat(name,' age is ', age) as ageInfo from person;
- //在查询结果中增加了一个status列
- //根据年龄的大小,分别填写'too young'或'too old'或'OK'
- select name,age,
- case when age<20 then 'too young'
- when age>30 then 'too old'
- else 'OK'
- end as status
- from person;
- //order语句负责随机,limit语句负责限制显示数目
- select * from person order by rand() limit 1;
- //coalesce函数负责将null转化为其他值显示
- select id,coalesce(name,'No Name'),age from person;
- //部门编号为主关键字,升序;
- //工资为次要关键字,降序
- select empno,deptno,sal,ename,job
- from emp
- order by deptno, sal desc
- 1 select deptno
- 2 from dept
- 3 where deptno not in (select deptno from emp )