数据库查询语句1

数据库查询语句一

一无条件查询

1 查询表的全部信息

select * from emp;

2 查询某一列

select ename from emp;

3查询部分列信息

select ename,job,hiredate from emp;

4 distinct 标识

避免相同的行出现师使用distinct标识

select distinct deptno from emp;

二条件查询

select * from emp where deptno = 20;

select * from ename,sal from emp where comm is not null;

select sal,job from emp where ename='SMITH';

select * from emp where hiedate>'23-Sep-89';

用order by 排序

select * from emp order by ename;

select * from emp order by ename desc;

select ename,deptno,sal from emp order by deptno ,ename;

三运算符与谓词

1 算术运算符 + - * / ()

select ename,sal,comm,sal*12 from emp where comm>0.05*sal order by comm/sal desc;

2 not and or

3 比较运算符

= != > >= < <=

4 谓词

谓词是一种条件，以产生真假未知的结果值

谓词有以下几种

1 in(not in)

表示在(或者不在)一个数值的范围内即等于列表中的任意一个值

select ename,hiredate,job,sal from emp where deptno in (20,30,50);

以上语句等价于

select ename,hiredate,job,sal from emp where deptno = 20 or deptno = 30 or deptno = 50 ;

2 between and

3 not between and

从小到大的一个范围或 not between and排除一个范围

select ename,job,sal from emp where sal between 2000 and 3000;

以上语句等价于

select ename,job,sal from emp where sal >=2000 and sal <=3000;

4 like (not) like

模式匹配（或者不匹配）

% 表示任意字符串

- 表示一个字符串

select ename,job,sal,empno from emp where ename like 'S%';

select ename,job,sal,empno from emp where ename like 'S_M%';

select ename,job,sal,empno from emp where ename like 'JAM_%';

5 空值(NULL)

空值匹配方法为 IS NULL (IS NOT NULL) 空值表示未知，但不是零

select ename,job,sal,empno from emp where comm is null;

四多条件查询

select ename,job from emp where job !='CLERK' and deptno =20;

select ename,job from emp where job !='CLERK' and deptno =20 or sal>2500

五列名的别名(Alias)

select ename employee from emp;

select ename 姓名 from emp;

select sal,com/sal "CS/Rate" from emp where comm>0.05*sal order by comm/sal desc;

数据库查询语句1

相关推荐