查询里面最关键的是顺序()
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会过滤到重复的deptno
select distinct comm from emp;--distinct也会过滤掉重复的null
select distinct comm, deptno from emp;--把comm和deptno的组合进行过滤
select comm, distinct deptno from emp;--逻辑上有冲突
3、between
select *from emp
where sal =5000;
select *from emp
where sal >=1500 and sal <= 3000;
--等价于
select *from emp
select *from emp
where sal in (1500,3000);
--等价于
select *from emp
where sal =1500 or sal = 3000;
select *from emp
where sal not in (1500,3000);
--等价于
select *from emp
where 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 emp
where sal in (1500,3000);
--等价于
select *from emp
where sal =1500 or sal = 3000;
select *from emp
where sal not in (1500,3000);
--等价于
select *from emp
where sal !=1500 and sal != 3000;
-- 数据库中不等号用 != 或 <> 推荐用<>
5、top(最前面的若干个记录)
select top 5 * from emp;
select top 15 percent * from emp; -- 输出的是三个不是两个
--把工资在到(包括、)之间的员工中工资最高的前三个员工所有信息输出
select top 3 * from emp
where sal between 1500 and 3000
order by sal desc; --desc降序不写默认升序