Oracle Study Notes (1)

I. Basic query statements, Special symbol |. Specifies the column alias AS, which uniquely identifies distinct.

1. Character connector "|" and "+"

Oracle: select column name | '123' from Table Name
SQL: select column name + '000000' from table name T

2 "AS" symbol
Oracle: select column name as new column name from Table Name (space, cannot have as) New table name
SQL: select column name as new column name from table name T as (as can be unavailable) New table name

2. Data Sorting and data filtering query are the same as SQL statements.

In, like, is null, between... and..., and, or

Order by COLUMN name asc/desc Ascending by default

Iii. Single Row Functions(Single-row functions)
Character function, Number function, Date function, Conversion Function, General Function

1. Character functions:
Case character processing functions include: LOWER, UPPER, and INITCAP.
Lower ('SQL')-> SQL converts uppercase letters to lowercase letters.
Upper ('SQL')-> SQL converts lowercase letters to uppercase letters.
Initcap ('SQL course')-> upper-case SQL course

Character operation functions

Character concatenation
Concat ('hello', 'World')-> HelloWorld

Character Truncation
5 characters from the first one on the left
Substr ('helloworld', 1, 5)-> Hello

Use the left () function in SQL

1. left () LEFT (<character_expression>, <integer_expression>) Returns character_expression, which starts from integer_expression at the left. Column: left ('helloworld', 5)-> hello

5 characters from the fifth number on the right
Substr ('helloworld',-5, 5)-> World

Use the right () function in SQL

1. right () RIGHT (<character_expression>, <integer_expression>) Returns character_expression, which starts from integer_expression at the left. Column: right ('helloworld', 5)-> world

 

Length
Length ('helloworld')-> 10

Find the position of w
Instr ('helloworld', 'w')-> 6

Used in SQLCharindex ()Function

2. charindex ()
Returns the starting position of a specified substring in a string.
CHARINDEX (<'substring _ expression' >,< expression>)
Here, substring _ expression is the character expression to be searched, and expression can be a string or a column name expression. If no substring is found, the return value is 0.
This function cannot be used for TEXT and IMAGE data types.

The 10 characters are not enough to be filled with the character "*" on the left.
Lpad ('hello', 10, '*')-> ***** hello

10 characters are not enough to fill in the right side of the character "*"
Rpad ('hello' 10, '*')-> hello *****

Delete the first letter from the character
Trim ('H' from 'helloworld')-> elloWorld

2. Numeric functions: round, trunc, and mod
Round (45.926, 2)-> 45.923

Trunc (45.926, 2)-> 45.92

Mod ()-> 1 calculate the remainder

3. Date Functions

Obtain the current system time
SQL: select getdate ();
Oracle: select sysdate from dual;

Calculation date difference:
Months_between (sysdate, to_date ('2017-11-29 ', 'yyyy-MM-dd '))

Add a month
Add_months (sysdate, 1)

The number of digits next Friday (executed in plsql)
Select next_day (to_date ('1970-11-1 ', 'yyyy-mm-dd'), 'Friday') from dual-> 2011

The last day of March January:
Last_day (to_date ('1970-11-1 ', 'yyyy-mm-dd')-> 2011-11-30

4. conversion functions

Varchar2 or char-> number

Varchar2 or char-> date

Number-> varchar2

Date-> varchar2

Get the current day of the week
Oracle: select to_char (sysdate, 'day') from dual
SQL: select datename (weekday, getdate ())

To_char (Date Field, 'fmdd Month yyyy ')
To_char (number, 'format ')
To_number (character field)
To_date ('1-11-2011 ', 'dd-MM-yyyy ')

SQL: Use CAST and CONVERT

Explicitly converts a data type expression to another data type. CAST and CONVERT provide similar functions.

Use CAST:
CAST (expression AS data_type)

Use CONVERT:
CONVERT (data_type [(length)], expression [, style])

Function nested select length (substr ('helloworld ))

 

5. General Functions
Nvl (parm1, parm2) If parm1 is null, parm2 is returned.

Nvl2 (parm1, parm2, parm3) is not null, and parm2 is returned; otherwise, parm3

If the two expressions are not equal, NULLIF returns the value of the first expression1.
If the two expressions are equal, NULLIF returns NULL.
Nullif (expression1, expression2)
Coalesce (...) One-time judgment

4. Multi-Table query and table alias
SQL:
1. Inner join (a typical join operation that uses comparison operators such as = or <> ). Including equal join and natural join.
The inner join uses the comparison operator to match rows in two tables based on the values of the columns in each table. For example, retrieve all rows with the same student ID in the students and courses tables.

2. Outer Join. Outer Join can be left Outer Join, right outer join, or complete external join.
When an external join is specified in the FROM clause, it can be specified by one of the following sets of keywords:

1) left join or LEFT OUTER JOIN
The result set of the left outer Join includes all rows in the LEFT table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all selection list columns in the right table in the row of the associated result set are null.

2) right join or RIGHT OUTER JOIN
The right outer join is the reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.
3) full join or FULL OUTER JOIN
The Complete External Join Operation returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.

3. Cross join
Returns all rows in the left table. Each row in the left table is combined with all rows in the right table. Cross join is also called Cartesian product.

Tables or views in the FROM clause can be specified in any order through internal join or complete external join. However, when you use left or right outer join to specify a table or view, the order of tables or views is very important. For more information about using Left or Right outer join to arrange tables, see using outer join.

Example:

-------------------------------------------------
Table a id name table B id job parent_id
1 piece 3 1 23 1
2 Li Si 2 34 2
3 Wang Wu 3 34 4
A. id is related to parent_id

--------------------------------------------------
1) Internal Connection
Select a. *, B. * from a inner join B on a. id = B. parent_id
The result is
1 piece 3 1 23 1
2 Li Si 2 34 2

2) left join
Select a. *, B. * from a left join B on a. id = B. parent_id
The result is
1 piece 3 1 23 1
2 Li Si 2 34 2
3 Wang Wu null

3) Right join
Select a. *, B. * from a right join B on a. id = B. parent_id
The result is
1 piece 3 1 23 1
2 Li Si 2 34 2
Null 3 34 4

4) full connection
Select a. *, B. * from a full join B on a. id = B. parent_id

The result is
1 piece 3 1 23 1
2 Li Si 2 34 2
Null 3 34 4
3 Wang Wu null