admin 管理员组文章数量: 887021
2024年1月16日发(作者:excel hlookup函数用法)
oracle查询语法大全(Oracle query syntax Daquan)
1。SELECT * FROM emp;
2。选择编号、姓名,工作从EMP;
三.选择empno编号,ename姓名,从工作工作EMP;
4。选择工作从EMP;
5。选择不同的工作,从EMP;
6。选择不同的编号、工作从EMP;
说明:因为雇员编号不重复,所以此时证明所有的列没有重复,所以不能消除掉重复的列。
7。查询出雇员的编号,姓名,工作,但是显示的格式:编号是:7369的雇员,姓名是:史密斯,工作是:清晰
选择“编号是:“| | EMPNO | |”的雇员,姓名是:“| | ename |
| ',工作是:“| |工作从EMP;
8。求出每个雇员的姓名及年薪
选择ename,萨尔* 12收入从EMP;
9。1500的所有雇员信息求出工资大于
SELECT * FROM EMP在萨尔> 1500;
10。查询每月可以得到奖金的雇员信息
SELECT * FROM EMP,奖金不为空;
11。查询没有奖金的雇员信息
SELECT * FROM EMP在公司是空的;
12。1500同时可以领取奖金的雇员信息查询出基本工资大于
SELECT * FROM EMP在萨尔> 1500和奖金不为空;
13。1500或者可以领取奖金的雇员信息查询出基本工资大于
SELECT * FROM EMP在萨尔> 1500或通信不为空;
14。1500或者不可以领取奖金的雇员信息查询出基本工资不大于
SELECT * FROM emp不(Sal > 1500和奖金不为空);
15。查询基本工资大于1500,但是小于3000的全部雇员信息
SELECT * FROM EMP在萨尔> 1500和萨尔<3000;
16。查询基本工资大于等于1500,但是小于等于3000的全部雇员信息
SELECT * FROM EMP在萨尔> = 1500和萨尔< = 3000;
SELECT * FROM EMP在萨尔1500和3000之间;
17。查询出在1981年雇佣的全部雇员信息(1981年1月1日到1981
年12月31日之间的雇佣的雇员)
SELECT * FROM EMP在姓名和雇佣之间的1-1月- 81”和“81”月汔;
18。要求查询出姓名是史密斯的雇员信息
SELECT * FROM EMP在ename =史密斯;
19。7369, 7499, 7521的雇员的具体信息要求查询出雇员是
SELECT * FROM EMP在EMPNO = 7369 = 7499 = 7521或编号编号;
SELECT * FROM EMP empno在哪里(7369, 7499, 7521);
20。7369, 7499, 7521的雇员的具体信息要求查询出雇员不是
SELECT * FROM EMP在EMPNO不(7369, 7499, 7521);
21。
The query name is Smith, Allen, king of the employee information
Select * from EMP where ename in ('SMITH','ALLEN','KING');
22. check out the names of all employees in the second letters
containing "M" employee information
Select * from EMP where ename like'_M%';
23. a query containing the letter M in the name of the employee
information employee
Select * from EMP where ename like'%M%';
24. check out the employees information in 1981
Select * from EMP where hiredate like'%81%';
Contains 5 employee information query wages 25.
Select * from EMP where Sal like'%5%';
26. query employee number not 7369 employee information
Select * from EMP where empno! = 7369;
Select * from EMP where empno < > 7369;
27. in accordance with the requirements of wages from low to
high ranking
Select * frm EMP order by sal;
Select * from EMP order by sal asc;
28. in accordance with the requirements of wages in order from
high to low
Select * from EMP order by sal desc;
29. check out all the employee information query information
of the 20 sector, in accordance with the wage from high to low
ranking, if wages are equal, in accordance with the date of
employment from early to late order.
Select * from EMP where deptno order by Sal desc = 20, hiredate
asc;
30. lowercase letters to uppercase
Select upper ('hello') from dual;
31. capital letters to lowercase
Select lower ('HELLO WORLD') from dual;
32. query name is an employee of Smith information
Select * from EMP where ename = upper ('smith');
33. using initcap () function to capitalize the first letter
of the words
Select initcap ('Hello world') from dual;
34. the employee name table into capital letters at the
beginning
Select initcap (ename) from emp;
35. the string "hello" and "world" series
Select concat ('Hello ''world') from dual;
Common character function processing 36. string operation
Select substr ('hello', 1, 3) ('hello') length string, the
string length, replace ('hello','l','x') from dual string
replacement;
Select substr ('hello', 0, 3) ('hello') length string, the
string length, replace ('hello','l','x') from dual string
replacement;
Three character 37. displays the name and the names of all the
employees
Select ename (ename, substr, length (ename) -2) from emp;
Select ename, substr (ename, -3, 3) from emp;
38. the use of numerical function performs four to five homes
in operation
Select round (789.536) from dual;
39. the 789.536 number two decimal places
Select round (789.536,
2) from dual;
40. requirements will be 789.536 integer in numerical ten four
to five homes in the carry
Select round (789.536, -2) from dual;
41. using TRUNC () function does not retain any decimal and
decimal point will perform four to five homes in the operation
Select TRUNC (789.536) from dual;
The TRUNC (42.) can also specify retained digit decimal point
Select TRUNC (789.536, 2) from dual;
43. digit negative numbers
Select TRUNC (789.536, -2) from dual;
The use of mod (44.) to take over the operation function
Select mod (10, 3) from dual;
45. shows 10 employees into the company the number of weeks
(current date - hire date = 7 = the number of days / week)
Select empno, ename, round ((sysdate - hiredate) / 7) from EMP
where deptno = 10;
46. date function
(months_between): for a given date range for the number of
months
(add_months): in the months of the date specified on the
specified date, calculated after
(next_day): Specifies the date the next date
(last_day): for a given date the date is the last day
Forty-seven
Select empno, ename, months_between (sysdate, hiredate) from
emp;
Select empno, ename, round (months_between (sysdate, hiredate))
from emp;
48. select sysdate, add_months from dual (sysdate, 4);
49. select next_day (sysdate, 'Monday') from dual;
50. select last_day (sysdate) from dual;
51. transfer function
(to_char): String
(to_number), converted to digital
(to_date): conversion to date
52. check all employees employee number, name, date of
employment
Select empno,
Ename,
To_char (hiredate,'yyyy') year,
To_char (hiredate,'mm') months,
To_char (hiredate,'dd') day
From emp;
Select empno, ename, to_char (hiredate,'yyyy-mm-dd') from emp;
Select empno, ename, to_char (hiredate,'fmyyyy-mm-dd') from
emp;
53. query all employee number, name and salary
Select empno, ename Sal, from emp;
Select empno, ename, to_char (SAL,'99999') from emp;
Select empno, ename, to_char (SAL,'L99999') from emp;
Select empno, ename, to_char (SAL,'$99999') from emp;
54. select to_number ('123') + to_number ('123') from dual;
55. convert a string into a date type
Select to_date ('2009-01-01','yyyy-mm-dd') from dual;
56. for each employee's annual salary (requirements plus bonus)
Select empno, ename, Sal, comm, (SAL + comm) * 12 from emp;
Select empno,
ename,萨尔,通讯,NVL(通讯,0),(SAL + NVL(通讯,0))×12收入从EMP;
57。decode()函数类似于elsif其他语句如果„„
选择解码(1, 1,“内容是1”,2,“内容是2’,3,’内容是3)从双;
58。查询出雇员的编号,姓名,雇佣日期及工作,要求将雇员的工作替换成以下信息:
选择empno雇员编号,
雇员姓名ename,
雇佣日期姓名和雇佣,
解码(工作,
“职员”,“业务员”,
“推销员”,“销售人员”,
“经理”、“经理”,
“分析师”、“分析员”,
'总统','总裁”
)职位
从EMP;
59。笛卡尔积(交差连接)
SELECT * FROM EMP,部;
SELECT * FROM EMP交叉连接部;
60。内连接
SELECT * FROM EMP E,D, = 系;
SELECT * FROM EMP E内连接部D = ;
SELECT * FROM EMP E加入系D = ;
61。自然连接
SELECT * FROM emp自然连接部;
SELECT * FROM EMP E加入部门使用(deptno);
62。要求查询出雇员的编号,姓名,部门的编号,名称,地址
选择,,,及其所在,从EMP E ,系里 = ;
63。要求查询出雇员的姓名,工作,雇员的直接上级领导姓名
查询每个部门,,从 EMP E,M, = EMP;
64。要求查询出雇员的姓名,工作,雇员的直接上级领导姓名以及部门名称
查询每个部门,,及其所在,从EMP,电磁脉冲,系里 = 和 = ;
65。要求查询出每个雇员的姓名,工资,部门名称,工资在公司的等级(salgrade),及其领导的姓名及工资所在公司的等级
查询每个部门,,及其所在,,,,
从EMP E系D,salgrade,EMP m,salgrade MS
在 =
和 和之间
和 =
和 和之间;
查询每个部门,
,
及其所在,
(解码,1,“第五等级',2,'第四等级',3,'第三等级',4,'第二等级',5,'第一等级”),
,
,
(解码,1,“第五等级',2,'第四等级',3,'第三等级',4,'第二等级',5,'第一等级”)
从EMP E系D,salgrade,EMP m,salgrade MS
在E.
表和和和 = 之间
和 和之间;
66。选择编号、姓名,、名称、LOC从EMP E,D,
= 系;
选择编号、姓名,、名称、LOC从EMP E内连接部D
= ;
67。左外连接
选择编号、姓名,、名称、LOC从EMP E,D,系 =
(+);
选择编号、姓名,、名称、LOC从EMP E左外连接部D
= ;
选择编号、姓名,、名称、LOC从EMP E左连接部D
= (+);
68。右外连接
选择编号、姓名,、名称、LOC从EMP,系在 D(+)= ;
选择编号、姓名,、名称、LOC从EMP E右外部联接部D
= ;
选择编号、姓名,、名称、LOC从EMP E右连接部D
= ;
69。选择,,,从 EMP E,M,
= EMP;
70。选择,,,从EMP E ,EMP M, = (+);
71。
SELECT * FROM EMP E,D,系 = 和 = 30;
SELECT * FROM EMP E内连接部D = 哪里
= 30;
SELECT * FROM EMP E加入系D = 哪里
= 30;
SELECT * FROM EMP E自然连接部D在表30;
SELECT * FROM EMP E加入部门使用(deptno)在表30;
72。
查询每个部门,,及其所在,从EMP E右外部联接部D
= ;
查询每个部门,,及其所在,从EMP对加入系D =
;
查询每个部门,,及其所在,从EMP E ,系在
D(+)= ;
73。select count(ename)从EMP;
74。选择最小(SAL)从EMP;
75。选择最大(SAL)从EMP;
76。选择的总和(SAL)从EMP;
77。选择AVG(SAL)从EMP;
78。
选择的总和(SAL)从EMP在表20;
79。选择AVG(SAL)从EMP在表20;
80。求出每个部门的雇员数量
选择多种计数(deptno)的雇员工资;
选择多种计数(EMPNO)的雇员工资;
81。求出每个部门的平均工资
选择DEPTNO,AVG(SAL)的雇员工资;
82。按部门分组,并显示部门的名称,及每个部门的员工数
选择及其所在,计数()从EMP E系D
在 =
组及其所在;
选择,及其所在,温度C
从(SELECT DEPTNO,计数()C从EMP E组的)温度,系D
在 = ;
83。2000的部门编号和平均工资要求显示出平均工资大于
选择DEPTNO,AVG(SAL)的雇员平均工资(SAL)> 2000;
84。显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于5000,输出结果按月工资的合计升序排序。
选择工作,和(SAL)苏从EMP工作<>“推销员”的各种工作的总和(SAL)> 5000阶苏;
选择,总和(温度。Sal)的
从(选择工作,萨尔从EMP E工作<>“售货员”)温度
集团通过
具有总和(温度。萨尔)> 5000
顺序的;
85。求出平均工资最高的部门工资
选择最大(AVG(SAL))的雇员工资;
86。7654工资高的所有雇员信息要求查询出比雇员编号为
SELECT * FROM EMP在萨尔>(选择萨尔从EMP在EMPNO = 7654);
87。要求查询出工资比7654高,同时与7788从事相同工作的全部雇员信息
SELECT * FROM emp
在萨尔>(选择萨尔从EMP在EMPNO = 7654)
工作=(选择工作从EMP在EMPNO = 7788);
88。要求查询出工资最低的雇员姓名,工作,工资
选择不工作,萨尔从EMP在萨尔=(选择min(SAL)从EMP);
89。要求查询出:部门名称,部门的员工数,部门的平均工资,部门的最低收入雇员的姓名
选择及其所在,温度,温度,
从部门D,
(选择多种计数(EMPNO)C,AVG(SAL),min(SAL)的工资从EMP M组)温度,
EMP E
在 = 和 =温度。M;
选择,
, temp.c, temp.a, ,
From
SELECT COUNT ( , (c), AVG (), min (m )
From EMP e, Dept d
Where =
Group by ) temp.
EMP e,
Dept d
Where temp.m =
And = ;
90. 求出每个部门的最低工资的雇员的信息
SELECT * from EMP where salt in (Select min (sal) from EMP Group
by deptno);
SELECT * from EMP where Salt = any (Select min (sal) from EMP
Group by deptno);
SELECT * from
(Select min (sal) from EMP Group by deptno) temp.
EMP and
Where = temp.m;
91. 范例 90 中, 比子查询条件中最低 (小) 的工资要大的雇员信息
SELECT * from EMP where Salt > any (Select min (sal) from EMP
Group by deptno);
SELECT * from EMP where Salt > (Select min (min (sal) from EMP
Group by deptno);
92. 范例 90 中, 比子查询条件中最高 (大) 的工资要小的雇员信息
SELECT * from EMP where Salt < any (Select min (sal) from EMP
Group by deptno);
SELECT * from EMP where Salt < (Select Max (min (sal) from EMP
Group by deptno);
93. 范例 90 中, 比子查询条件中最高 (大) 的工资要大的雇员信息
SELECT * from EMP where Salt > All (Select min (sal) from EMP
Group by deptno);
SELECT * from EMP where Salt > (Select Max (min (sal) from EMP
Group by deptno);
94. 范例 90 中, 比子查询条件中最低 (小) 的工资要小的雇员信息
SELECT * from EMP where Salt < all (Select min (sal) from EMP
Group by deptno);
SELECT * from EMP where Salt < (Select min (min (sal) from EMP
Group by deptno);
95. 查找出 20 部门中没有奖金的雇员信息
SELECT * from EMP where (Salt, NVL (COMM, - 1) in (Select NVL
(sal, COMM, - 1) from EMP where deptno = 20);
SELECT * from EMP where deptno = 20 and Comm is null;
96. 操作符返回两个查询选定的所有不重复的行 Union
Select deptno from EMP Select Union deptno from Dept;
97. Union All 操作符合并两个查询选定的所有行, 包括重复的行
Union all select deptno from EMP Select deptno from Dept;
98. 操作符只返回两个查询都有的行 Intersect
From EMP Select deptno Intersect Select deptno from Dept;
99.
The minus operator returns only selected by the first query but
was second query selected row is ruled out in second in the query
results in a query results
Select deptno from dept minus select deptno from emp;
query plus automatic growth column query and index (two
methods)
Select (row_number) over (order by codevalue) as num, codevalue
from ibpsodt0303
Select rownum, CODECODE, CODECATALOG, CODEVALUE from
ibpsodt0303 where codecatalog='MsgId'
版权声明:本文标题:oracle查询语法大全(OraclequerysyntaxDaquan) 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.freenas.com.cn/jishu/1705353193h482270.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论