列控制

查询列内容:
select 列名称 from 表名称

模板(也既执行顺序):

select
from
where
group by
having
order by

tips:
添加distinct描述符, 可以筛选重复值, 去除重复值
任何东西跟NULL相加为NULL
字符串相加为0

• 例子:

select ename, sal from em;
select distinct ename, sal from em;取出重复值
select *, sal+ifnull(comm, 0) from em;如果字段值为空则视为0
select concat(ename, job) from em;连接字符串
select ename 姓名 from em; 别名

image.png

条件控制(带条件查询)

• 例子

select * from em where sal>20000; 薪资大于20000
select * from em where comm is not null; 查询奖金不为空的
select * from em where deptno=20;20部门员工
select * from em where sal between 20000 and 30000;工资两万3万之间
select * from em where job in('经理', '分析师');是经理或者分析师的
select * from em where job in('保洁员', '经理', '文员');

模糊查询

• 例子

select * from em where ename like '张_';查询姓张, 且两个字名字的员工
select * from em where ename like '张__';查询姓张, 且三个字名字的员工
select * from em where ename like '___';查询所有三字字名字的员工
select * from em where ename like '%刚%';查询所有含刚字的员工
select * from em where ename like '赵%';查询所有姓赵的员工

排序

• 例子

select * from em ORDER BY sal;按工资排序, 不指定排序方式默认为升序
select * from em ORDER BY sal desc;按工资降序
select * from em order by sal ASC, comm DESC, empno ASC;先按工资升序, 工资相同, 则按奖金降序, 奖金相同则按empno升序

练习题: image.png

• 练习题答案:

select * from em where deptno=30;

select ename, empno, deptno from em where job='销售员';

select * from em where comm>sal;

select * from em where (deptno=10 and job='经理') or (deptno=20 and job='销售员');

select * from em where (deptno=10 and job='经理') or (deptno=20 and job='销售员') or (job NOT in (' 经理',  '销售员') and sal>=20000);

select * from em where (comm is null) or (comm<=1000);

select * from em where ename like '___';

select * from em where hiredate like '2000-%';

select * from em order by empno asc;

select * from em order by sal desc, hiredate asc;

select deptno, AVG(sal) from em group by deptno;

select deptno, COUNT(*) from em group by deptno;

select job, MAX(sal) 最高工资, MIN(sal) 最低工资, COUNT(*) 人数 from em group by job;