Oracle Foundation One

1. String
It is case-sensitive and should be quoted when used.
When specifying aliases, quotation marks can be added or not added.
When you have spaces or special characters, you must enclose them in quotation marks.
When you do not enclose the quotation marks, the display is capitalized, and after the quotation marks, it is displayed as written.
Use single quotation marks when using strings.
Use double quotation marks when writing aliases.

2. Filter empty function: NVL (expression, the value to use when the expression is empty)
Select Empno, ename, Sal, Sal * As annual salary, NVL (comm, 0), total revenue (SAL * + NVL (comm,0))
From EMP

3. Remove duplicate rows
– Acts on a column
Sql> SELECT distinct job from EMP;
– Work on multiple columns, and repeat records for all column values together
Sql> SELECT DISTINCT job, deptno from EMP;

4. If you query only one expression and no data is used for any table, you must also write from ...
Can be written from dual
Dual is a virtual table that itself exists and can be used directly.
Such as:
Select 3+2
Select ' Hello ' | | "World" from dual;

5. String connector
Sql> Select Ename | | ' The salary is ' | | Sal from EMP;
ename| | ' The salary is ' | | SAL
———————————————————-
Smith's salary is 800.
Allen's salary is 1600.
Ward's salary is 1250.
Jones's salary is 2975.
Martin's salary is 1250.
Blake's salary is 2850.
Clark's salary is 2450.
Scott's salary is 3000.
King's salary is 5000.
Turner's salary is 1500.
Adams's salary is 1100.
James's salary is 950.
Ford's salary is 3000.
Miller's salary is 1300.

You can also use the function concat ():
Sql> Select Concat (' Hello ', ' world ') from dual;

6.like
When you use like, you can use% and _ to represent any number of any character or any character, respectively.
To express% or _ itself, you need to use an escape character, for example:
Sql> SELECT * from emp where ename like ' ki\%% ' escape ' \ ';

7.BETWEEN
Consists of two boundaries.
Must be a small value to write to the front, the large value is written to the back, otherwise there is no result.

8.IN
Where: In (..,..,..,...) If it contains null, there is no effect.
Example: Query all employees who are managers
Sql> SELECT * from emp where empno in (select Mgr from EMP);
Where: Not in (...) if it contains null, no result is returned.
Example: Query all employees who are not managers
Sql> SELECT * from emp where empno not in (select Mgr from EMP where Mgr are NOT NULL);

Date type in 9.MySQL: Day, Time, datetime
Only date in Oracle

Handling of the date
1, check the employee information after the appointed date
Sql> SELECT * from emp where hiredate> ' 3 January-December-81 ';
2, modify the format of the date
Sql> select * from V$nls_parameters;
Sql> alter session set nls_date_format= ' YYYY-MM-DD '; Valid only for the current session
3, how to use the Date function:
Sql> SELECT * from emp where hiredate>to_date (' 1981-12-31 ', ' yyyy-mm-dd ');
Or
Sql> SELECT * from emp where TO_CHAR (hiredate, ' yyyy-mm-dd ') > ' 1981-12-31 ';

语法：TO_CHAR(date, ‘format_model‘)语法：TO_DATE(str, ‘format_model‘)格式字符串不区分大小写：获取当前时间：SQL> select to_char(sysdate,‘YYYY-MM-DD‘) from dual;TO_CHAR(SY----------2012-03-07SQL> select to_char(sysdate,‘yyyy-mm-dd‘) from dual;TO_CHAR(SY----------2012-03-07SQL> select to_char(sysdate,‘yyyy-mm-dd hh:mi:ss‘) from dual;TO_CHAR(SYSDATE,‘YY-------------------2012-03-07 02:33:41

10. Sort:
Order BY column name, ...
Can function in: number, date, string.
You can use column names, expressions, aliases, ordinal numbers (which represent the first column in select)

当order by所在的列中有null，会：升序时，null的在下面。降序时，null的在上面。我们希望，不管升序还是降序，null值的始终在下面方式一：SQL> select * from emp order by comm desc nulls last;方式二：    select empno, ename, job, hiredate, sal, nvl(comm, 0)     from emp     order by 6 desc

11. Group functions
Select
Max (SAL) Wages,
Min (sal) minimum wage,
AVG (SAL) Average salary,
SUM (SAL) The wages of all employees and,
Number of employees with Count (SAL) payroll
from EMP;
12. Handling of NULL for group functions
example, check the average bonus for all employees (the bonus is null for someone)
Select SUM (COMM)/count (*) average bonus from EMP
The group function automatically filters out null values.
Be aware of handling null values when using AVG ():
Select AVG (NVL (comm, 0)) from EMP;
Functions can be nested using

13. Grouping
Group by, written after from, if there is a where, just behind where.
The columns of the query must be:
Columns that appear in group by (not necessarily all in select)
or use Group functions
GROUP by a column
Group BY multiple columns
There is a different group of multiple columns that participate in grouping.

14. Grouping results filtering
Having, after splitting the group and then filtering, shows only the results that match the conditions.
Aliases cannot be used in both group by and have.
The difference from where
Having is the separation of the group after the filter.
Where is the first filter, then the grouping operation.
If possible, write where conditions, do not write having.

Select
...
From
...
Where
...
Group by
...
Having
...
Order by
...

================================================
15. Sub-query:
Subqueries can be used when one step is not solved.
Divided into:
Single-line subquery
Multi-row subqueries

可以在主查询的select, from, where, having 都可以放子查询    不可以在主查询的group by 放子查询单行操作符对应单行子查询，多行操作符对应多行子查询在select中放子查询时，要求只能是单行子查询。

In:

Any:
is less than any of the values in a collection, which is less than the maximum value in the collection.
Greater than any one of the values in a set is greater than the minimum value.

All:
is less than the minimum value for all values in a collection.
is greater than the maximum value for all values in a collection.

16.rownum is a pseudo-column that represents the line number recorded in the result set.
1,rownum once generated, it does not change (the rownum is generated in the default order when no sorting is performed, and then the sort is executed).
2, for rownum, can only use < and <=, can not use > and >= and =.

Oracle Foundation One