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,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快速應用開發-讀書筆記