Oracle's basic operator

! = does not equal select Empno,ename,job from scott.emp where job!= ' manager '

^= not equal to select Empno,ename,job from Scott.emp where job^= ' manager '

<> not equal to select Empno,ename,job from Scott.emp where job<> ' manager '

< less than select Sal from Scott.emp where sal<1000

> Greater than select Sal from Scott.emp where sal>1000

<= less than equals select Sal from Scott.emp where sal<=1000

>= is greater than or equal to select Sal from Scott.emp where sal>=1000

In list select Sal from Scott.emp where Sal in (1000,2000)
The query of the time can be used in such as SELECT * from student where time in (' June-March-09 ', ' August-May-09 ');

Not in is not a list select Sal from Scott.emp where Sal isn't in (1000,2000)

Between...and is between. And.. Room

Select Sal from Scott.emp where Sal between and 2000

Not Between...and is not between. And.. Between

Select Sal from scott.emp where Sal not between 2000

Like pattern matching select ename from scott.emp where ename like ' M% ' (% denotes length string of any length)
Select ename from scott.emp where ename like ' m_ ' (_ denotes an arbitrary character)

Is null whether empty select ename from scott.emp where ename is null

is not NULL for the select ename from scott.emp where ename are NOT null

Logical operators:

or (or) select ename from scott.emp where ename= ' joke ' or ename= ' Jacky '

and (with) select ename from scott.emp where ename= ' and ' or ename= ' Jacky '

Not (non) Select ename from scott.emp where not ename= ' and ' or ename= ' Jacky '

Set Operator:

Union union joins two SQL statements, two sentences of SQL statements, and a duplicate record is removed.

(select Deptno from scott.emp) union (select Deptno from Scott.dept)

UNION ALL (unions) is followed by two SQL statements, two SQL statements, and no duplicate records are removed.

(select Deptno from scott.emp) union ALL (select Deptno from Scott.dept)

Intersect (intersection) INTERSECT joins two SQL statements to fetch a common part of the two sets of queries.

(select Deptno from Scott.emp) intersect (select Deptno from Scott.dept)

Minus (complement) minus joins two SQL statements, fetching the difference of two sets of queries.
(select Deptno from Scott.emp) minus (select Deptno from Scott.dept)

Join operator: (| | ) is used to connect a single field, or to concatenate multiple strings together.

Precedence of the operator: arithmetic "connection" logic (not and OR)

Data types in Oracle

Data types mainly include: Character numeric date large object LOB Row/long row

Character type: (varchar varchar2 long)
Varchar: Length immutable, max 2000 bytes
VARCHAR2: variable length, max 4,000 bytes
Long: variable length max 2G

Numeric type: An int double float is consolidated into number type in Oracle.
Number[p,s]:p represents precision and s represents the number of decimal digits.

Date type: (date) (timestamp)
The date type stores the type of dates, including the day of the month and seconds.
The timestamp type stores date types, including more precise information, including regional information, and minutes and seconds. Milliseconds .....

The main problem with the date data type is that it does not have enough granularity to differentiate between two events which occur first. Oracle has extended the timestamp data type on the date data type, which includes information about the month and day of all date data types, and includes fractional seconds. If you want to convert the date type to a timestamp type, use the cast function.
eg. Sql> Select CAST (date1 as TIMESTAMP) "Date" from T;

Raw type: Storing binary data

Longraw Type: Stores variable-length binary data. The maximum can be 2G.

Pseudo-Columns in Oracle
(ROWID) (rownum) pseudo-column is a two column data field that is automatically added to each data when data is inserted into the data table.

Rowid The actual storage address of each record, even if the record is repeated this field does not appear duplicated.
RowNum is the field that is added to each record after the data is queried, and he does not really exist, but rather a line number that is automatically added when the view is queried. When you run the Select Rowed,rownum from table name, you can view the data.

SQL statements in Oracle. SQL statement is a common database language
SQL command includes (DDL) (DCL) (DML) (TCL)
DDL: The data definition language includes the Create statement, the drop statement, and the ALTER statement.
DCL: The Data Control language includes the GRANT statement. Revoke statements
DML: Data Control language includes adding, deleting, changing, and checking statements.
TCL: Things control language including Commit,rollback,savepoint
Functions in SQL

SQL functions include: (Single-line function) (grouping function) (analytic function)

Single-line function:

Only one data is displayed for each record queried from the table. A single-line function can appear in the SELECT clause in the WHERE clause. Single-line functions include: (character functions) (numeric functions) (date functions) (conversion functions) (other functions).

Date function: The date is calculated to display the date type or the number type.

Sysdate Current date and time
Current_date returns the current date in the current session's time zone

Current_timestamp to return the current date in the current session's time zone with the timestamp with date zone data type

Dbtimezone return time zone
Add_months Plus number of months
Months_between returns the number of months between two dates
Last_day return to the last day of the month
ROUND rounding the number of months
Next_day Specify a date for the next one weeks
TRUNC format conversions for dates
EXTRACT the part specified in the date of the lift

Character functions:
Initcap (char) capital letter.
Lower (char) turns uppercase characters to lowercase.
...
See: http://blog.sina.com.cn/s/blog_4b3c1f950100pkhv.html

numeric functions
The remainder of the Mod (m,n) m/n.
Power (M,n) m's n-th side.
Round (M,n) m is a decimal number, and n is the number of decimal places to take. Rounded
Trunc (M,n) m is a decimal number, and n is the number of decimal places to take. Not rounded

Conversion functions
To_char conversion characters.
To_date conversion date.
To_number Converting numbers

See: http://blog.sina.com.cn/s/blog_4b3c1f950100pk54.html

Other functions
NVL (expression 1, expression 2)
If expression 1 is null, return expression 2, otherwise return expression 1;
If an expression type is inconsistent, Oracle converts expression 2 to the type of expression 1
NVL2 (expression 1, expression 2, expression 3)
Returns the value of expression 3 if expression 1 is empty, otherwise returns the value of expression 2
Nullif (expression 1, expression 2)
Returns a null value if two expressions are equal, otherwise returns an expression of 1

Aggregation functions
AVG () takes an average.
Count () returns the data that was queried.
Sum () Fetch and
Max () takes maximum value
MIN () take minimum value

Group and having
SELECT p_category, MAX (itemrate) from Itemfile GROUP by P_category;

SELECT p_category, MAX (itemrate) from Itemfile GROUP by p_category
Having a p_category not in (' Accessories ');

Oracle's basic operator