SQL常用语法回忆及备忘之基本SQL语句
SQL学习已有一年时间，由于这一年基本未使用忘记大半，今日特来回忆并记录常用基本的语法，用法，并举例，以备日后快速回忆之用。
以下SQL均在Oracle10g环境下编写。表结构附在最后。
-
基本的SELECT语句
最最关键，基本，核心的一句话
SELECT [DISTINCT | UNIQUE] (*, columnname [AS alias], …)
FROM tablename
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY columnname]
- 在Oracle10g中SQL语句大小写不敏感，且一个语句可以分多行写
- Oracle10g中DISTINCT和UNIQUE的功能是同义词，功能完全相同，据说DISTINCT是标准语法，所以建议写DISTINCT而非UNIQUE。
EX.1
Display the employee name, job, and start date of employees hired between 03/1/1981 and 20/6/1981. Order the query in by start date with the earliest date first and use the exact date formats specified above.
SQL.1
SELECT ename AS "employee name", job, TO_CHAR(hiredate,'DD/MM/YYYY')
FROM emp1
WHERE hiredate BETWEEN '03/JAN/81' AND '20/JUN/81'
ORDER BY hiredate (DESC)
SUM.1
a.别名可以用AS，后面跟的是双括号; b.输出日期的时候转化格式用TO_CHAR函数（TO_CHAR函数会在后面函数中提到）c.比较两个值除了可以用'<', '>', 'AND', 'OR'这些以外，还可以用BETWEEN … AND … d.用ORDER BY 默认正序，加上DESC为倒序.
EX.2
Display the name of all employees who do not have a manager.
SQL.2
SELECT ename
FROM emp1
WHERE mgr IS NULL
EX.3
Display the name of all employees who have an O anywhere in their name.
SQL.3
SELECT ename
FROM emp1
WHERE ename LIKE (=) %O%'
SUM.3
a.字符串进行匹配的时候可以用'LIKE'也可以直接用'='，在引号中的表达式，'%'表示若干个字符，'_'代表一个字符.
EX.4
Display the name, salary, and commission for all employees who earn commissions. Sort the data in descending order of commission within salary descending.
SQL.4
SELECT ename,sal, comm
FROM emp1
WHERE comm IS NOT NULL
ORDER BY sal DESC, comm DESC
SUM.4
a.多重排序的时候，按照先后的次序来排，按需要正序或者倒序直接加到列名后面
EX.5
NULL
SQL.5
SELECT customer# || CHR(10) || lastname || ' , ' || firstname "Customer Information"
FROM customers
SUM.5
a.连接字符串可以用||，要加入字符串用'…'. b.回车用CHR(10)，数字换成其他的几位其对应的ASCII码所对应的字符c.给列取别名除了用AS以外还一直在后面加别名
附录：
表结构：
EMP1
|
Name |
Null? |
Type |
|
EMPNO |
NOT NULL |
NUMBER(4) |
|
ENAME |
|
VARCHAR2(10) |
|
JOB |
|
VARCHAR2(9) |
|
MGR |
|
NUMBER(4) |
|
HIREDATE |
|
DATE |
|
SAL |
|
NUMBER(7,2) |
|
COMM |
|
NUMBER(7,2) |
|
DEPTNO |
|
NUMBER(2) |
|
EMPTYPE |
|
VARCHAR2(2) |
DEPT1
|
Name |
Null? |
Type |
|
DEPTNO |
NOT NULL |
NUMBER(2) |
|
DNAME |
|
VARCHAR2(14) |
|
LOC |
|
VARCHAR2(13) |
BONUS1
|
Name |
Null? |
Type |
|
ENAME |
|
VARCHAR2(10) |
|
JOB |
|
VARCHAR2(9) |
|
SAL |
|
NUMBER |
|
COMM |
|
NUMBER |
SALGRADE1
|
Name |
Null? |
Type |
|
GRADE |
|
NUMBER |
|
LOSAL |
|
NUMBER |
|
HISAL |
|
NUMBER |
