查询1

查询里面最关键的是顺序（）

1、计算列

select * from emp;-- * 表示所有-- from emp 表示从emp表查询select empno, ename from emp;select ename, sal from emp;select ename, sal*12 from emp;select ename, sal*12 as "年薪" from emp;-- as可以省略年薪必须用双引号select ename, sal*12 "年薪" from emp;select ename, sal*12 "年薪" ,sal "月薪" from emp;

注意：
orale中的字段别名不允许用单引号括起来，但sql允许
为了兼容性别名统一用双引号括起。
2、distinct（不允许重复的）

select deptno from emp;--14条记录不是三条select distinct deptno from emp; --三条记录distinct deptno会过滤到重复的deptnoselect distinct comm from emp;--distinct也会过滤掉重复的nullselect distinct comm, deptno from emp;--把comm和deptno的组合进行过滤select comm, distinct deptno from emp;--逻辑上有冲突

3、between

select *from empwhere sal =5000;select *from empwhere sal >=1500 and sal <= 3000;--等价于select *from empselect *from empwhere sal in (1500,3000);--等价于select *from empwhere sal =1500 or sal = 3000;select *from empwhere sal not in (1500,3000);--等价于select *from empwhere sal !=1500 and sal != 3000;-- 数据库中不等号用    !=  或 <>  推荐用<> where sal between 1500 and 3000;--查找工资小于或大于的员工的所有信息select *from empwhere sal <1500 or sal > 3000;--等价于select *from empwhere sal not between 1500 and 3000;

4、in（属于若干个孤立的值）

select *from empwhere sal in (1500,3000);--等价于select *from empwhere sal =1500 or sal = 3000;select *from empwhere sal not in (1500,3000);--等价于select *from empwhere sal !=1500 and sal != 3000;-- 数据库中不等号用    !=  或 <>  推荐用<> 

5、top（最前面的若干个记录）

select top 5 * from emp;select top 15 percent * from emp; -- 输出的是三个不是两个--把工资在到（包括、）之间的员工中工资最高的前三个员工所有信息输出select top 3 * from empwhere sal between 1500 and 3000order by sal desc; --desc降序不写默认升序