SQL开发知识：oracle基本查询用法入门实例

SELECT *|{[DISTINCT] column|expression [alias], …}
FROM table;

–查询所有数据
select * from emp;

–查询指定列数据
select empno,ename,sal from emp;

–算数运算符(+ – * /)
select ename,sal,sal+30 from emp;

–使用括号
select ename,sal,12*(sal+30) from emp;

–定义空值
–(空值是无效的，未指定，未知的或不可预知的值，空值不是空格或是0)
select ename,job,sal,comm from emp;

–空值的数学运算
–包含空值的数学表达式的值都为空值
select ename,12*sal+comm from emp;

–列的别名
–别名使用双引号，AS可以省略
select deptno as “no”,ename as “name” from emp;

–连接符,把列与列，列与字符连接在一起
select deptno || ‘–‘ || ename from emp;

–字符串
–日期和字符只能在单引号中出现
select ‘hello ‘ || ename from emp;

–删除重复行
select distinct deptno from emp;

–显示表结构
desc[ribe] tablename;

SELECT *|{[DISTINCT] column|expression [alias], …}
FROM table
[WHERE condition(s)];

–查询指定条件数据
select deptno,ename from emp where deptno=10;

–字符串和日期包含在单引号中
–字符串大小写敏感，日期格式敏感
select ename,job,deptno from emp where ename=’King’;

–比较运算符(= > < <= >= <> !=)
select ename,sal from emp where sal<1500;

–其他比较运算符
–BETWEEN … AND … 在两个值之间包含边界
–IN(set) 等于值列表中的一个
–LIKE 模糊查询

–IS NULL 空值
select ename,sal,deptno from emp where deptno in(10,30);
select ename,sal,comm from emp where comm is null;

–逻辑运算(AND OR NOT)
select ename,sal from emp where deptno=10 and sal>1500;

ORDER BY 字段 [DESC|ASC]

select ename,sal from emp order by sal desc;
–多列排序
–先按第一列排序，如果相同，则按第二列排序，以此类推
select * from emp order by sal desc,hiredate desc;

–LOWER 转换小写
–UPPER 转换大写
–INITCAP 首字母大写
select lower(ename) from emp;
–CONCAT 接接字符串
–SUBSTR 截取字符串
–LENGTH 字符串长度
–INSTR 查找字符串
–LPAD 左边填充字符
–RPAD 右边填充字符
–TRIM([leading|trailing|both] 字符串1 from 字符串2)
–TRIM可以删除两边空格，也可删除其他字符
–REPLACE 替换字符串
select concat(‘aa’,’bb’) from emp;
select substr(‘abcdefg’, 2, 3) from emp;
select length(‘test…’) from emp;
select instr(‘hello world’, ‘w’) from emp;
select lpad(sal, ’10’, ‘0’) from emp;
select rpad(sal, ’10’, ‘*’) from emp;
select trim(‘ test ‘) from emp;
–从尾部删除字符串*号
select trim(trailing ‘*’ from ‘**1212121**’) from emp;
–把字符串中的22替换成88
select replace(‘11223344′, ’22’, ’88’) from emp;

–ROUND 四舍五入
–TRUNC 截断
–MOD 求余
select round(25.533,2) from dual;
select trunc(25.323,2) from dual;
select mod(8, 3) from dual;

–返回系统时间
select sysdate from dual;
–两个日期相减，返回日期之间相差的天数
select ename,(sysdate-hiredate) / 7 “weeks” from emp;
–MONTHS_BETWEEN 两日期相差月数
–ADD_MONTHS 指定日期加上若干月数
–NEXT_DAY 指定日期的下一个日期
–LAST_DAY 本月的最后一天
–ROUND 日期四舍五入
–TRUNC 日期截断
select months_between(sysdate,hiredate) from emp;

–TO_CHAR(date, ‘format_model’)
–把日期转换成字符串
select to_char(sysdate, ‘YYYY MM DD HH:MI:SS’) from dual;
–TO_CHAR(number, ‘format_model’)
select ename,sal,to_char(sal, ‘$99,999.00′) from emp;
–TO_NUMBER(char[,’format_model’])
–TO_DATE(char[,’format_model’])

NVL(expr1,expr2)
NVL2(expr1,expr2,expr3)
NULLIF(expr1,expr2)
COALESCE(expr1,expr2,…)

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_expr3 THEN return_expr3
ELSE else_expr]
END

DECODE(col|expression, search1, result1
[,search2, result2, …, ]
[,defautl]
)

select ename,sal,decode(round(sal/1000),
1, ‘一倍’，
2, ‘二倍’，
3, ‘三倍’,
‘不知倍数’
) from emp;

–AVG
–COUNT
–MAX
–MIN
–SUM
select sum(sal) as “total” from emp;
select max(sal) from emp;
select count(*) from emp where deptno=10;
select count(distinct deptno) from emp;

select deptno,avg(sal) from emp group by deptno;

–多列分组
select deptno,job,avg(sal) from emp group by deptno,job;

select deptno,avg(sal) from emp group by deptno having deptno in(10,20);

select max(avg(sal)) from emp group by deptno;

SELECT table1.column, table2.column
FROM table1,table2
WHERE table1.column1=table2.column2;

–等值连接
select d.dname,e.ename,e.sal from emp e, dept d where e.deptno=d.deptno;

–多连接条件和and操作符
select d.dname,e.ename,e.sal from emp e, dept d where e.deptno=d.deptno and e.deptno=10;

–不等值连接

–查询员工部门名称和工资等级
select d.dname, e.ename, e.sal, s.grade
from emp e, dept d, salgrade s
where e.deptno = d.deptno
and e.sal >= s.losal
and e.sal <= s.hisal;

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column= table2.column(+) ;

–查询部门人数
select d.dname, d.deptno, count(e.empno)
from emp e, dept d
where e.deptno(+) = d.deptno
group by d.deptno,d.dname;

–查询员工的上级
select e.ename as “员工”, e2.ename as “上级”
from emp e, emp e2
where e.empno = e2.mgr;

SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 ON(table1.column_name=table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)];

select e.ename,d.dname from emp e cross join dept d;

select e.ename,d.dname from emp e natural join dept d;

select e.ename,d.dname from emp e join dept d USING (deptno);

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

select e.ename, d.dname, e2.ename
from emp e
join dept d
on e.deptno = d.deptno
join emp e2
on e.empno = e2.mgr;

–左外连接
select e.ename, d.dname
from emp e
left outer join dept d
on e.deptno = d.deptno;

–右外连接，返回右表中不满足条件的行
select e.ename, d.dname
from emp e
right outer join dept d
on e.deptno = d.deptno;

–满外连接
select e.ename, d.dname
from emp e
full outer join dept d
on e.deptno = d.deptno;