OracleSQL培训教程：从实践中学习OracleSQL及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,ename,sal
FROM emp
WHERE sal BETWEEN 1500 AND 2900;
SELECT hiredate FROM emp;
SELECT empno,ename,sal, hiredate
FROM emp
WHERE hiredate BETWEEN ‘01-1月-81‘ AND ‘31-5月-82‘;
SELECT empno,ename,sal, hiredate
FROM emp
WHERE hiredate NOT BETWEEN ‘01-1月-81‘ AND ‘31-5月-82‘;
SELECT empno,ename,job,sal
FROM emp
WHERE job =‘SALESMAN‘;
SELECT empno,ename,sal,job
FROM emp
WHERE job IN (‘SALESMAN‘,‘CLERK‘,‘MANAGER‘);
SELECT empno,ename,sal,job
FROM emp
WHERE job NOT IN (‘ANALYST‘,‘PRESIDENT‘);
LIKE 运算符可以使用以下两个通配符 "%"和"_"，其中：
"%"代表零个或多个字符
"_"代表一个且只能是一个字符
SELECT empno,ename,sal,job
FROM emp
WHERE job LIKE ‘SAL%‘;
SELECT empno,ename,sal,job
FROM emp
WHERE job LIKE ‘S_L_S%‘;
SELECT empno,ename,sal,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,ename,sal FROM emp WHERE sal >= 1500 ORDER BY sal; [ASC]升序默认
SELECT empno,ename,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",ename name,(500+sal)*12 "Annual Salary"
FROM emp ORDER BY (500+sal)*12 DESC;
SELECT empno AS "Employee Number",ename name,(500+sal)*12 "Annual Salary"
FROM emp ORDER BY 3 DESC;
SELECT ename,job,sal FROM emp ORDER BY job,sal DESC;
SELECT ename,job,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;

本文出自 “ZQSLZR” 博客，谢绝转载！

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