1.as(取别名)
select empno as '职工号', sal as '工资' from emp where sal >1000;
empno:职工号,sal:工资,emp:职工表
执行结果:
2.or and
select ename,sal from emp where sal>1000 or sal<500;
select ename,sal from emp where sal>1000 and empno<5;
执行结果:
or:满足其中一个条件即可
and:两个条件必须同时满足
3. like
select ename,sal from emp where ename like '_熊%';
like:模糊查询,用在条件表达式中
其中的_代替一个字符,%代替0个或者多个字符
执行结果:
4. between A and B , not between A and B(between 包括 A,B)
select ename,empno from emp where empno between 2 and 8;
select ename,empno from emp where empno not between 2 and 8;
between A and B :在A和B之间,包括A和B
not between A and B:在A和B这个范围之外,不包括A和B
执行结果:
5. in(,) not in(,)
select ename,sal,mgr from emp where mgr in(5,3);
in(a,b,c):搜索满足条件a or b or c的
not in(a,b,c)就是不满足条件 a or b or c的
执行结果:
6.limit n,m
select ename,sal,empno from emp limit 0,2;
limit n,m:分页,每页m条数据,当前页为第n条信息所在页
执行结果:
7.distinct
select distinct sal from emp;
distinct:去重复
执行结果:
9.count , group by
select mgr,count(*) 人数 from emp group by mgr;
这里的mgr是管理人员
count:是累加行数(我是这么理解的,每一行加1)
group by:分组,根据一定的条件,对搜索的结果进行分组
执行结果:
10.sum
select mgr,sum(sal) 薪资 from emp group by mgr;
sum:累加,把某一类的值加起来
执行结果:
11.order by
select ename,sal from emp order by sal;
select ename,sal from emp order by sal desc;
order by:根据 某个条件或者属性来排列,默认下时升序
order by 条件,属性 desc:在最后加上desc就是降序
执行结果: