Oracle SQL培训教程:从实践中学习Oracle SQL及Web快速应用开发-读书笔记

前端之家收集整理的这篇文章主要介绍了Oracle SQL培训教程:从实践中学习Oracle SQL及Web快速应用开发-读书笔记前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

字符集的不同使日期型的显示有问题解决

alter session set NLS_DATE_LANGUAGE='AMERICAN';


解锁scott账户

alter user scott identified by tiger account unlock;


DISTINCT去掉重复的行


基本查询语句的格式

SELECT *|{[DISTINCT] 列表,...}

FROM 表名;

其中 列表: 列名|表达式 [别名]


SELECT * FROM tab;
SELECT * FROM emp;
SELECT empno,ename,sal
FROM emp
WHERE sal >= 1500;
SELECT empno,sal
FROM emp
WHERE sal BETWEEN 1500 AND 2900;
SELECT hiredate FROM emp;
SELECT empno,sal,hiredate
FROM emp
WHERE hiredate BETWEEN '01-1月-81' AND '31-5月-82';
SELECT empno,hiredate
FROM emp
WHERE hiredate NOT BETWEEN '01-1月-81' AND '31-5月-82';
SELECT empno,job,sal
FROM emp
WHERE job ='SALESMAN';
SELECT empno,job
FROM emp
WHERE job IN ('SALESMAN','CLERK','MANAGER');
SELECT empno,job
FROM emp
WHERE job NOT IN ('ANALYST','PRESIDENT');
LIKE 运算符可以使用以下两个通配符 "%"和"_",其中:
"%"代表零个或多个字符
"_"代表一个且只能是一个字符
SELECT empno,job
FROM emp
WHERE job LIKE 'SAL%';
SELECT empno,job
FROM emp
WHERE job LIKE 'S_L_S%';
SELECT empno,hiredate
FROM emp
WHERE hiredate LIKE '%81';

SELECT * FROM tab;
SELECT * FROM dept_temp;
CREATE TABLE dept_temp AS SELECT * FROM dept;
INSERT INTO dept_temp VALUES (88,'IT_PRESEARCH','BEIJING');
SELECT * FROM dept_temp WHERE dname LIKE 'IT\_%' escape '\';
SELECT * FROM dept_temp WHERE dname LIKE 'IT~_%' escape '~';
ORDER BY 子句一定是sql语句的最后一个子句
SELECT empno,sal FROM emp WHERE sal >= 1500 ORDER BY sal; [ASC]升序默认
SELECT empno,sal FROM emp WHERE sal >= 1500 ORDER BY sal DESC;
SELECT empno AS "Employee Number",ename name,(500+sal)*12 "Annual Salary"
FROM emp ORDER BY "Annual Salary" DESC;
SELECT empno AS "Employee Number",(500+sal)*12 "Annual Salary"
FROM emp ORDER BY (500+sal)*12 DESC;
SELECT empno AS "Employee Number",(500+sal)*12 "Annual Salary"
FROM emp ORDER BY 3 DESC;
SELECT ename,sal FROM emp ORDER BY job,sal DESC;
SELECT ename,sal FROM emp ORDER BY empno;

修改默认显示宽度

SET LINE[SIZE] {80|n}

SET line 100


L 显示刚刚输入的缓冲区中的sql语句

n text 修改出错的部分

'/' (run)命令

SAVE

GET

@或START 命令时把指定脚本文件内容装入sql缓冲区中运行

SPOOL命令

SELECT * FROM emp;SELECT * FROM tab;DESC emp;DESC dept;

猜你在找的Oracle相关文章