oracle基本查询语言

--1.简单的数据查询语句
--查询所有的员工的信息
select * from emp;
--查询员工的姓名和工作职位
select ename,job from emp;
--姓名和工作以中文的形式显示出来
select ename "姓名",job "工作职位" from emp;
select ename as "姓名",job as "工作职位" from emp;
--查询每个员工的职位
select job from emp;
--查询每个员工的职位(去掉重复的行记录)
select distinct job from emp;
--查询所有员工的基本信息，并且显示效果是：编号是 7369，姓名是SMITH,工作职位是CLERK
select '编号是' || empno || '姓名是' || ename || '工作职位是' || job 个人信息 from emp;
--查询所有员工的姓名和年薪
select ename,sal*12 年薪 from emp;
select ename, (sal+30)*12+sal 年薪 from emp;
--2.条件查询
--查询职工编号是7788的员工信息
select * from emp where empno = 7788;
--查询名字是SMITH的员工信息
select * from emp where ename = 'SMITH';
--查询工资高于1500的员工信息
select * from emp where sal > 1500;
--查询职位是办事员或者是销售人员的全部信息,并且要求这些员工的工资大于1200
select * from emp where (job = 'CLERK' or job = 'SALESMAN') and sal > 1200;
--查询员工编号不是7788的员工信息
select * from emp where empno <> 7788;
select * from emp where empno != 7788;
select * from emp where Not empno = 7788;
--查询姓名中包含A的员工信息(模糊查询)
--查询以A开头的员工信息
select * from emp where ename like 'A%';
--查询名字的第二个字符是A的员工信息
select * from emp where ename like '_A%';
--查询名字中只要有A的员工信息
select * from emp where ename like '%A%';
--查询所有员工信息
select * from emp where ename like '%%';
--查询名字中只要没有A的员工信息
select * from emp where ename Not like '%A%';
--3.范围查询
--查询工资在1500到3000之间所有员工信息
select * from emp where sal > 1500 and sal < 3000;
select * from emp where sal BETWEEN 1500 and 3000;
--[BETWEEN ... and ... ] 是一个闭区间
select * from emp where sal >= 1500 and sal <= 3000;
--查询81年入职的所有员工信息
select * from emp where hiredate between '1-1月-81' and '31-12月-81';
--表示日期格式是需要用单引号括起来
--查询职工编号是:7788,7499,7521的员工信息。
select * from emp where empno = 7788 or empno = 7499 or empno = 7521;
select * from emp where empno In (7788,7499,7521);
--查询SMITH,FORD ,KING的信息
select * from emp where ename = 'SMITH' or ename = 'FORD' or ename = 'KING';
select * from emp where ename in ('SMITH','FORD','KING');
--查询职工编号不是:7788,7499,7521的员工信息
select * from emp where empno not in (7788,7499,7521);
--如果使用了IN操作符,查询的范围之中有null,不影响查询
select * from emp where empno in (7788,7499,null);
--如果使用NOT IN操作符,查询范围之中有null,不会又任何结果返回
select * from emp where empno not in (7788,7499,null);
--4.判断是否为空 IS (NOT)NULL
--查询出所有有奖金的员工姓名与工作职位
select ename, job from emp where comm is not null;
--查询出没有资金的员工姓名和工作职位以及入职日期
select ename,job,hiredate from emp where comm is null;
--查询工资大于1500,并且有奖金的员工
select * from emp where sal > 1500 And comm is Not null;
--查询工资大于2000,或者有奖金员工
select * from emp where sal > 2000 or comm is not null;
--查询工资大于1500,并且有奖金的以外的员工
select * from emp where not (sal > 1500 and comm is not null);
--5.排序查询
--查询所有员工姓名,入职日期,工资。结果按入职升序显示
select ename,hiredate,sal from emp order by hiredate ASC;
--查询所有员工姓名,入职日期,工资。结果按入职升序显示,工资降序
select ename,hiredate,sal from emp order by hiredate ASC,sal Desc;

 

 

DQL语言查询

 

 1 select * from t_hq_ryxx;
 2 
 3 select nianl, xingm from t_hq_ryxx;
 4 
 5 select nianl as 年龄, xingm as 姓名 from t_hq_ryxx t;
 6 
 7 select nianl 年龄 from t_hq_ryxx;
 8 
 9 select nianl || xingm as 年龄和姓名 from t_hq_ryxx;
10 
11 select nianl as hhh,t.* from t_hq_ryxx t order by nianl desc ;--排序
12 
13 select nianl as hhh,t.* from t_hq_ryxx t order by xingb desc ,bum desc;
14 
15 select nianl,xingm,bum,xingb from t_hq_ryxx t order by bum desc nulls last,xingb;
16 
17 select * from t_hq_ryxx order by 3;
18 
19 select nianl,gongz, (nianl+gongz)as g from t_hq_ryxx order by (gongz||nianl);
20 
21 --去重复
22 select distinct bum from t_hq_ryxx;
23 
24 select distinct bum,xingb from t_hq_ryxx;
25 
26 select * from t_hq_ryxx;
27 
28 
29 select * from t_hq_ryxx where xingb = '1 'and bum = '102';
30 
31 select * from t_hq_ryxx where (bum = '101' or bum = '102') and xingb = '2'and nianl >'40' ;
32 
33 select * from t_hq_ryxx where bum is not null;
34 
35 --模糊查询 %表示通配符
36 
37 select * from t_hq_ryxx where xingm like '三%'
38 
39 select * from t_hq_ryxx where xingm like '%儿'
40 
41 select * from t_hq_ryxx where xingm like '%三%'
42 
43 select * from t_hq_ryxx where gongz <any (select pingjgz from t_hq_bm);
44 
45 --值得范围
46 select * from t_hq_ryxx where nianl in ('33','58');
47 
48 select * from t_hq_ryxx where nianl ='33' or nianl = '58';
49 
50 --区间范围
51 
52 select * from t_hq_ryxx where gongz between 40 and 100;
53 
54 select * from t_hq_ryxx where gongz >=40 and gongz <= 100;
55 
56 --子查询
57 select * from t_hq_ryxx where bum in (select bumbm from t_hq_bm where lianxdh = '10086');
58 
59 --大于最小值，小于最大值
60 
61 select * from t_hq_ryxx where gongz >any (select pingjgz from t_hq_bm);
62 
63 --大于最大值，小于最小值
64 
65 
66 select * from t_hq_ryxx where gongz >all (select pingjgz from t_hq_bm);
67 
68 --分组 多种形式
69 select bum from t_hq_ryxx group by bum;
70 
71 select bum,count(1) as 数量 from t_hq_ryxx group by bum;
72 
73 select bum,count(1) as 数量,sum (gongz) as 合计 from t_hq_ryxx group by bum;
74 
75 select bum,count(1) as 数量,avg(gongz) as 平均值 from t_hq_ryxx group by bum;
76 
77 select nianl, bum,count(1) as 数量,avg(gongz) as 平均值 from t_hq_ryxx group by nianl, bum;
78 
79 select bum,count(1) as 数量,avg(gongz) as 平均值 from t_hq_ryxx group by bum having avg(gongz)>60;