Oracle查询语法笔记

摘要:Oracle查询语法笔记

Sql是Structured Query Language的简称

结构化查询语言分类

数据查询语言(DQL:Data Query Language)语句主要包括SELECT,用于从表中检索数据。
数据操作语言(DML:Data Mainpulation Language)语句主要包括INSERT,UPDATE和DELETE,用于添加,修改和删除表横纵的行数据。
事务处理语言(TPL:Transaction Process Language)语句主要包括COMMIT和ROLLBACK,用于提交和回滚。
数据控制语言(DCL:Data Control Language)语句主要包括GRANT和REVOKE,用于进行授权和收回权限。
数据定义语言(DDL:Data Definition Language)语句主要包括create,drop,alter,用于定义,销毁,修改数据库对象。

ed是修改
(R或者/)是执行
SELECT 字段,字段,…
FROM 表名/结合列表
WHERE 条件;
设置页面的宽度
SET linesize 整数;
设置每页的行数
SET pagesize 行数;
NVL(可能产生空值的字段,替换值)
SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp;

别名

在oracle里面只有起别名的时候用双引号””
SELECT 字段 as 别名,字段 别名。。。
SELECT ename as em FROM emp;
SELECT ename "姓名" FROM emp;

连接操作符||


SELECT ename||job FROM emp;
SELECT '姓名'||ename FROM emp;

去掉重复行distinct


SELECT distinct job FROM emp;
SELECT distinct job,deptno FROM emp;
临时表 dual
当前时间 sysdate
SELECT sysdate FROM dual;

导入


@+路径
@d:\sql.sql

查看表结构


DESC 表名 descride;
DESC emp descride;
特殊指明<>为不等于

特殊比较运算符

运算符含义
BETWEEN … AND …判断要比较的值是否在某个范围内
IN(集合列表)判断要比较的值是和集合列表中的任何一个值相等
LIKE判断要比较的值是否满足部分匹配,%代表零或任意更多的字符,_代表一个字符
IS NULL判断要比较的值是否为空值NULL

SELECT * FROM emp WHERE sal BETWEEN 800 and 1500; SELECT * FROM emp WHERE sal>=800 and sal<=1500;
SELECT * FROM emp WHERE sal=800 or sal=1500; SELECT * FROM emp WHERE sal IN(800,1500);
SELECT * FROM emp WHERE ename like '%A%'; SELECT ename FROM emp WHERE ename LIKE '_A%';
SELECT * FROM emp WHERE comm is NULL;

排序

ASC(升序,可以不写)/DESC(降序)
SELECT 字段1,字段2…
FOM 表名
WHERE 条件
ORDER by 排序字段1 asc/desc…;

大小写转换UPPER/ LOWER


SELECT UPPER('hello') FROM dual; SELECT LOWER('HELLO') FROM dual; SELECT INITCAP(‘welcome to dalian’) FROM dual;

连接CONCAT


SELECT CONCAT(ename,CONCAT(job,sal)) FROM emp;

截取SUBSTR

,sql的角标从1开始,0和1相同
SELECT SUBSTR(‘hello,world’,开始,个数) FROM emp; SELECT SUBSTR(‘hello,world’,1,3) FROM emp;

求长度LENGTH


SELECT LENGTH(‘hello’) FROM dual;

判断某个字符第一次出现的位置INSTR


SELECT INSTR(‘hello,world’,’e’) FROM dual;

左右填充补位LPAD/ RPAD


SELECT LPAD(100,长度,内容) FROM dual;
SELECT RPAD(100,长度,内容) FROM dual;
SELECT LPAD(‘你好’,8,’我好’) FROM dual;

去掉左右两边某个字符TRIM


SELECT TRIM(‘去掉的字符’ FROM ‘字段’) FROM dual;
SELECT TRIM(‘H’ FROM ‘HELLO.WORLD’) FROM dual; SELECT TRIM(‘ HELLO.WORLD ’) FROM dual; SELECT LTRIM(‘ HELLO.WORLD ’) FROM dual; SELECT RTRIM(‘ HELLO.WORLD ’) FROM dual;

替换REPLACE


SELECT REPLACE(字段,’前的内容’,’后的内容’) FROM emp;
SELECT REPLACE(ename,’A’,’a’) FROM emp;

四舍五入ROUND


SELECT ROUND(字段,位数) FROM dual;
SELECT ROUND(123.456789,-2) FROM dual;

截断TRUNC


SELECT TRUNC(字段,位数) FROM dual;
SELECT TRUNC(123.4567,2) FROM dual;

截断时间


SELECT TRUNC(SYSDATE,’Y’) FROM dual;

取余


SELECT MOD(除数,被除数) FROM dual;
SELECT MOD(10,3) FROM dual;

日期运算


SELECT SYSDATE+2 FROM dual;

返回天数


SELECT SYSDATE- Hiredate FROM emp;

返回月数,小数点后是小时


SELECT MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;

加月份可跨年


SELECT ADD_MONTHS(SYSDATE,15) FROM dual;

返回某个日期的下一个指定日期


SELECT NEXT_DAY(某个日期,’指定日期’) FROM dual;
SELECT NEXT_DAY(SYSDATE,’星期一’) FROM dual;

返回指定日期当月最后一天


SELECT LAST_DAY(指定日期) FROM dual;
SELECT LAST_DAY(SYSDATE) FROM dual;

转换函数


SELECT TO_CHAR(指定日期,’fmt格式’) FROM dual;
SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD Hh44:MI/SS’) FROM dual;

NULLIF判断两个表达式是否相等,如果不相等返回原值,否则返回null值


SELECT ename,sal, NULLIF(sal,950) FROM emp;

CASE和DECODE


SELECT ename,deptno, (CASE deptno WHEN 10 THEN ‘销售部’ WHEN 20 THEN ‘管理部’ WHEN 30 THEN ‘人事部’ END) FROM emp;
SELECT ename,deptno, (CASE deptno WHEN 10 THEN ‘销售部’ WHEN 20 THEN ‘管理部’ else ‘人事部’ END) FROM emp;
SELECT ename,deptno,DECODE (deptno,10,'销售部',20,'管理部',30,'人事部') FROM emp; SELECT ename,deptno,DECODE (deptno,10,'销售部',20,'管理部','人事部') FROM emp;


多表查询


SELECT 字段,字段,表名.字段…….
FROM 表名,表名
WHERE 多表之间的关联条件 AND 限制条件;
SELECT ename,empno,emp.deptno,loc FROM emp,dept WHERE emp.deptno=dept.deptno;

数据补全


SELECT ename,emp.deptno,dname FROM emp,dept WHERE emp.deptno(+)=dept.deptno;—-右连接
SELECT ename,emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno(+);—-左连接
SELECT e.ename,m.ename FROM emp e,emp m WHERE e.mgr=m.empno;—-自连
1998版本语法—-了解的知识,这个就先不写了


COUNT 统计


SELECT COUNT(empno) FROM emp;
MAX/MIN/SUM/AVG/ 求大/小/和/平均值
SELECT MAX/MIN(SAL) FROM emp

分组


GROUP BY 分组条件
SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno;
注意:使用分组函数的时候,不能出现分组函数和分组条件以外的字段

Having 条件


WHERE 搜索条件在进行分组操作之前应用;而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似,但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项

子查询


SELECT…
FROM…(查询函数)…
WHERE…(查询函数)…
GROUP BY…
HAVING…(查询函数)…
ORDER BY…

ROLLUP 和CUBE操作符 结果集 放在GROUP BY 后面


SELECT deptno,job,AVG(sal),MAX(sal) FROM emp GROUP BY ROLLUP(deptno,job);
SELECT deptno,job,AVG(sal),MAX(sal) FROM emp GROUP BY CUBE(deptno,job);
IN 在什么什么中
SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno);
ANY
=any:与IN操作符的功能是一样。
>any:比最小的还大
<any:比最大的还小
ALL
>all:比最大的还大
<all:比最小的还小


合并查询


UNION 并集 自动去掉重复行


SELECT ename,job,sal FROM emp WHERE sal>2500 UNION SELECT ename,job,sal FROM emp WHERE job=’MANAGER’;

UNION ALL 并集不去掉重复行


INTERSECT 交集,只会显示交集里的内容,并且以第一列进行排序


MINUS 差集。当使用这个操作符的时候,只会显示在第一结果集中存在的,第二个给果集不显示

扫一扫,打赏我
Fanyun Fanyun
Copyright©1993 - Now. 瞎塔码搞