Summary of important Oracle database SQL syntax

[SQL statement classification] DQL-Data Query Language (SELECT) DML-data operation language (INSERTUPDATEDELETEMERGE) DDL-Data Definition Language (CREATEALTERDROPTRUNCATE) DCL-Data Control Language (GRANTREVOKE) TCL-transaction control statement (COMMITROLLBACKSAVEPOINT) [query the base

[SQL statement classification] DQL-Data Query Language (SELECT) DML-data operation language (INSERT/UPDATE/DELETE/MERGE) DDL-Data Definition Language (CREATE/ALTER/DROP/TRUNCATE) DCL-Data Control Language (GRANT/REVOKE) TCL-transaction control statement (COMMIT/ROLLBACK/SAVEPOINT) [query Base

[SQL statement category]

DQL-Data Query Language (SELECT)

DML-data operation language (INSERT/UPDATE/DELETE/MERGE)

DDL-Data Definition Language (CREATE/ALTER/DROP/TRUNCATE)

DCL-Data Control Language (GRANT/REVOKE)

TCL-transaction control statement (COMMIT/ROLLBACK/SAVEPOINT)

[Query Basics]

1. Connect the property value with a connector: select ename | ''s job is '| job as job from emp;

[Note: Pay attention to the two single quotes in front of s. If you use one, a problem may occur]

2. Cancel duplicate (distinct): select distinct job from emp;

3. Alias: select ename as "name" from emp;

4. query the table structure: desc emp;

5. Conditional query where + operator

(1) between... and...

(2) in (*,*,*,....)

(3) like, used to match strings ('%' // match any number of characters; '_' // match one character)

(4) is null

(5) AND, OR, not in/between/like/, is not null

6. display query results with conditions

(1) order by: desc (descending); asc (Ascending BY default) [null ascending at the end]

[Function] 1. Character Function

(1) LOWER (column | expression); UPPER (); INITCAP () // uppercase

(2) CONCAT connects two values, which are equivalent to |

CONCAT (column1 | expression1, column2 | expression2 );

(3) SUBSTR ('string', 1, 3) = 'str' // obtain the substring starting from the first position, len = 3

[Note: oracle strings start from 1st locations by default]

(4) LENGTH ('string') = 6

(5) INSTR (s1, s2, [n1], [n2]) // returns the position where the substring s2 appears for the second time starting from n1, default Value: n1, n2 = 1; INSTR ('string', 'R') = 3

(6) LPAD (sal, 10, '*') = ****** 50000; RPAD (); [left-aligned, right-aligned]

(7) TRIM ('s 'FROM 'ssmith') = MITH // remove the character 's' at the beginning and end of the string'

Usage parameter: TRIM (LEADING | TAILING | BOTH's 'FROM 'ssmith ')

LEADING: removes header characters; TAILING: removes tail characters; BOTH: removes BOTH head and end characters

(8) REPLACE ('abc'. 'B', 'D') = 'ads' // REPLACE

(9) To_char (str, [fm]) converts an attribute into a string, or you can add a conversion format by yourself (the specific format depends on the specific type of the attribute)

Select ename, to_char (hiredate, 'yyyy-mm-dd') as d from emp;

2. Numeric Functions

(1) ROUND (column name | expression, n), rounding the value to the nth digit after the decimal point

(2) TRUNC (column name | expression, n), truncated to the nth digit after the decimal point (not rounded up)

(3) MOD (column name | expression, n), expression returns n

3. Date Functions

(1) SYSDATE // returns the current time

Select to_char (SYSDATE, 'yyyy-MM-DD HH24: MI: ss') as time from DUAL;

(2) MONTHS_BETWEEN // returns the number of months between two dates

Select ename, hiredate,

Round (months_between (to_date ('01-10-3050', 'dd-mm-yyyy '), hiredate) as months from emp;

(3) ADD_MONTHS (date, n) // Add the corresponding number of months based on the specified date

(4) NEXT_DAY (date, num)

Returns the next specified date of a date, and returns the date of the day of the week after the date. num can be a valid number representing the day of the week, and 1 is Sunday.

You can also enter the following data: NEXT_DAY (date, 'monday ')

(5) LAST_DAY (date) // returns the last day of the month in which the date is located.

(6) ROUND (date, ['cc' | 'yy' | 'mm' | 'dd' | 'hh24' | 'mi' | 'ss']) the default format is DD.

(7) TRUNC (date [, 'fmt']) // TRUNC. The default format is DD, And the date is truncated to the last day.

(8) EXTRACT: return date type, which indicates dating, month, and day.

EXTRACT (YEAR | MONTH | DAY from hiredate)

4. conversion functions

(1) TO_CHAR (date | number [, 'fmt']) converts the date type and number type to the character type

Common date format description:

YYYY: a four-digit number indicates the year.

YY: two digits indicate the year, but there is no century Conversion

RR: a two-digit number indicates the Year, which has a century conversion.

Current year 0-49: specify year 0-49 (current century), specify year 50-99 (previous actual)

Current year 50-99: specify year 0-49 (next century), specify year 59-99 (current century)

YEAR: English spelling of the YEAR

MM: two digits indicate the month.

MONTH: English spelling of the MONTH

DY: the first three letters of the week

DAY: English spelling of the week

D: number indicates the day of a week, Sunday = 1

DD: number indicates the day of January 1, January.

DDD: number indicates the day of the year

AM/PM: indicates the upper afternoon

HH/HH12/HH24: hour

MI: minute

SS: seconds,

TH: displays the ordinal number of numbers.

SP: display the spelling of numbers

SPTH: display the spelling of ordinal words represented by numbers

Number Format

9: one digit

0: one digit or leading 0

$: Displayed as a dollar sign

L: displays the local currency symbols set by region

.: Decimal point

,: Thousands of delimiters

[Note: when converting the numeric type to the character type, the width in the format must be greater than the actual column width. Otherwise, ### is displayed. If the width is smaller than the actual width after the decimal point, rounding is performed.]

Select ename, to_char (sal, '$99,999.00') as salary from emp;

(2) convert TO_DATE () to the Time Format

Select to_date ('1970-10-10 ', 'yyyy-mm-dd') + 15 from DUAL;

5. General Functions

(1) NVL (expression1, expression2). If expression1 is null, expression2 is displayed.

[Note: The data format can be date, character, or number. The data type must match]

(2) NVL2 (expression1, expression2, expression3 ),

If expression1 is not empty, expression2 is displayed. If it is empty, expression3 is displayed.

(3) NULLIF (expression1, expression2). If the two parameters are not equal, the first parameter is returned. If the two parameters are equal, null is returned.

(4) COALESCE (expression1, expression2 ,....., Expression n), returns the first non-null parameter.

(5) use CASE

Selectename, deptno,

(CaseDeptno

When 10Then'Department 10'

When20Then'Department 20'

When 30Then'Department 30'

Else'None'

End) Dept

From emp;

You can also use case to determine the value range.

Selectename, sal,

(Case

When sal <1000 then 'shao'

When sal> 1000 then 'duo'

Else 'right'

End) as level1

From emp;

Multi-Table query: 1. equijoin

Select ename, job from dept, emp where dept. deptno = emp. deptno;

Alias for table

Select ename, job from dept D, emp E where D. deptno = E. deptno;

[Note: If you have defined a table alias, you can only use the alias. You cannot use the original name]

2. External Connection

Left Outer Join: select ename, dname from emp, dept where emp. deptno = dept. deptno (+ );

Right outer join: select ename, dname from emp, dept where emp. deptno (+) = dept. deptno;

[Note: For tables without +, the content is displayed completely]

3. cross join (generate Cartesian Product)

Select ename, dname from emp cross join dept;

4. Naturally JOIN NATURAL JOIN

[Join condition: the two tables have the same value and data type in the same column. If the column name is of the same data type, an error is returned]

5. USING clause

USING (column_name)

Usage condition: Two or more identical columns exist in the two tables (if there is only one column, you can connect them naturally)

[Note: Do not repeat natural join with NATURAL connections ,]

Select ename, deptno from emp join dept using (deptno); -- Do not forget to add parentheses and keyword join

6. ON clause

Select ename, dname from emp join dept on emp. deptno = dept. deptno;

7. left outer join: LEFT OUTER JOIN

Select ename, dname from emp left outer join dept on emp. deptno = dept. deptno;

8. right outer join: RIGHT OUTER JOIN
9. full join: FULL OUTER JOIN

Select ename, dname from emp full outer join dept on emp. deptno = dept. deptno;

[Set operation] 1. UNION Operation

Removes duplicate rows and sorts by default.

Select ename, sal, deptno from emp

Where sal> 1500

UNION

Select ename, sal, deptno from emp

Where deptno in (10, 20 );

2. UNION ALL

Do not duplicate, not sort by default

[Grouping function] 1. MIN/MAX

It can be a number or a character

Selectmin (sal), max (sal) from emp;

Select min (ename), max (ename) from emp;

2. SUM/AVG

Select sum (sal), avg (sal) from emp;

3. COUNT

Select count (*) from emp;

4. Duplicate DISTINCT Cancellation

Select count (distinct deptno) from emp;

5. Handling null values

Select avg (comm) from emp; // null values are not included

Select avg (nvl (comm, 0) from emp; // calculate the null value as 0

6. GROUP

Select deptno, sum (sal) from emp group by deptno;

Select job, mgr, avg (sal) from emp group by job, mgr order by job;

Note: The group by clause cannot appear in the select clause. The function column of the select clause must appear in the group by clause]

7. HAVING clause (filter and judge group functions)

Select job, avg (sal) from emp where avg> 1000 group by avg;

(Error, where cannot be used here)

Select job, avg (sal) from emp group by job having avg (sal)> 1000;

(Use having instead. aliases cannot be used in having)

[Select execution process: FROM--WHERE--group by--HAVING -- SELECT--order]

Subquery]

1. Single Row subquery: The where operator can appear in having.

2. multi-row subquery, multi-row operators: IN, ANY, ALL

3. The subquery has a null value.

Example: select ename, sal from emp

Where empno not in (select mgr from emp where mgr is not null );

Result: No row is selected.

Cause: the returned values IN the subquery contain null values (the not in operator does NOT ignore null values)

4. EXISTS, NOT EXISTS

If a record is found in the EXISTS subquery, The subquery statement does not continue. true is returned.

Example: select ename from emp e

Where exists (select '1' from emp where manager_id = e. empno)

Here, '1' is only for placeholder use and has no practical significance. [as is not available for table alias]

Not exists operator operation method is different from not in. Only true or false is returned, and no null value is returned. Therefore, the null value IN subquery is NOT considered.

[Data operation DML] 1. INSERT

Insert into dept values (60, 'qqq', 'xxx ');

Insert into dept (deptno, loc) values (70, 'ss ');

Insert into hhh select * from eee where XXX (condition)

2. UPDATE

Update emp set deptno = 10 where ename = 'Scott ';

3. DELETE

Delete from e-mapreduce where e-mapreduce = 7788;

Delete cannot delete data referenced by other tables (data that violates integrity constraints)

4. MERGE data

Insert or update an object based on the specified conditions.

If the conditions are met, perform the update operation. Otherwise, perform the insert operation.

Merge into T T1

Using (select '000000' as a, 2 as B from fual) T2

On (T1.a = T2.a)

When matched then

Update set T1. B = T2. B

When not matched then

Insert (a, B) values (T2.a, T2. B );

5. ROWID

Is a virtual column in the table, containing the physical location information of the row of data

Select rowid, dname from dept;

// AAAMgxAAEAAAAAMAAA qqq

Quick Positioning

6. Features of transactions: ACID

Atomicity: an independent unit of work, either all successful or all failed

Consistency: Once the transaction is completed, the entire system is in a unified state of operation rules, no matter whether the transaction succeeds or fails, that is, the data will not be damaged.

Isolation: it is not affected by other transactions, so transactions should be isolated.

Persistent Durability: Once a transaction is committed, changes to data in the database are permanent.

7. Transaction Control

Display Control: commit and rollback)

Implicit control: Implicit commit (execute DDL or DCL statements, EXIT from SQL * PLUS using EXIT or QUIT command), implicit rollback (Force EXIT from SQL * PLUS, abnormal client-to-server interruption and system crash)

8. transactions:

Show commit: commit (rollback makes no sense after commit)

Show rollback: rollback

Set up a save point: savepoint // (return save point: rollback)

For example:

Insert into dept values (60, 'xxx', 'xxx ');

Savepoint insert_a;

Insert into dept values (80, 'xxx', 'xxx ');

Rollback to insert_a;

Cancel insert into dept values (80, 'xxx', 'xxx ');

[Tables and constraints] 1. Name:

(1) it must start with a letter and be 1-30 characters in length

(2) name can only contain A-Z, a-z, 0-9, _ (underline), $ ,#

(3) Reserved Words cannot be used.

(4) The name is case-insensitive.

2. Create a table

Create table dossier (

Id number (4 ),

Cname varchar2 (20 ),

Birthday date,

Stature number (3 ),

Weight number (5, 2 ),

Country_code char (2) default '01 ');

Create table user_table (

User_id number (9) primary key,

User_name varchar2 (50 ),

User_passwd varchar2 (50) not null,

User_state varchar2 (1) not null );

(Create a table using subquery)

Create table dept10

As

Select empno, ename, sal + 1000 as newsal

From emp

Where deptno = 10;

3. Add Columns

You cannot specify the position to add to the last column.

AlterTable dossierAdd(Sex char (1 ));

Change column attributes

AlterTable dossierModify(Sex char (2 ));

Default Value

AlterTable dossierModify(Sex default 'male ');

Delete column

1,AlterTable dossierDrop columnSex;

2,AlterTable dossierDrop (country_code );

3. delete a table

DropTable table_nale

Rename a table

RenameDossierToHaha;

4. Constraints

Not null non-empty

UNIQUE value

Primary key master code

Reference of columns in the foreign key external table

CHECK specifies a condition that must be true.

5. Related Data Dictionary

Select table_name from user_tables;

Query the data dictionary structure

Desc user_tables;

6. Data Description supplement: char, varchar, and varchar2

(1) The difference between varchar and varchar2 is that the latter occupies two bytes of all characters, and the former occupies only two bytes of Chinese characters and full-angle characters.

(2) The difference between nvarchar and nvarchar2 is the same as the above. The difference is the type defined according to the Unicode standard. It is usually used to support the definition of similar systems in multiple languages.

1. the length of char is fixed. For example, if you define char (20), even if you insert abc, less than 20 bytes, the database will also automatically add 17 spaces after abc to Supplement 20 bytes. char is differentiated between Chinese and English, and Chinese occupies two bytes in char, while English occupies one, so char (20) You can only store 20 letters or 10 Chinese characters. Char is applicable when the length is relatively fixed and generally does not contain Chinese characters.

2. varchar/varchar2 varchar is of an unfixed length. For example, you have defined varchar (20). When you insert abc, only three bytes are occupied in the database. Varchar also distinguishes Chinese and English, which is the same as char. Varchar2 is basically the same as varchar. It is a non-industrial standard varchar defined by oracle,The difference is that varchar2 uses null to replace the null String Of varchar.Varchar/varchar2 is applicable when the length is not fixed and generally does not contain Chinese characters.

3. nvarchar/nvarchar2 nvarchar and nvarchar2 are unfixed lengths.Nvarchar is not distinguished between Chinese and English,For example, if you define nvarchar (20), you can store 20 English letters, Chinese characters, or Chinese/English combinations. This 20 defines the number of characters rather than the number of bytes. nvarchar2 is basically the same as nvarchar, the difference is that the English letters in nvarchar2 also occupy two bytes.Nvarchar/nvarchar2 for storing Chinese Characters