SQL base Terms and single-line functions

1.SQL Basic Terminology

SQL language: structured Queries language (structured query Language)

Query statements

SELECT

DML Data Manipulation language

INSERT, Update,delete

DDL Data definition Statements

Create,alter,drop,rename,truncate

Transaction control Statements

Commit,rollback

Data Control Statements

Grant,revoke

Dual NULL table any data expression that contains a null value evaluates to NULL

For example:

Sql> select 3*2 from dual;

3*2

----------

6

2. Character-type single-line function

LOWER (), lowercase; UPPER (), uppercase examples are as follows :

Sql> Select LOWER (' UPPER '), UPPER (' LOWER ') from dual;

LOWER UPPER

----- -----

Upper LOWER

Sql>

Initcap (column|expression) capitalize the first letter of the string

CONCAT (column1|expression1,column2|expression2) Connect two strings (expression or column)

SUBSTR (column|expression,start,stop) intercepts the string, starting with the start bit, to the stop bit end (stop can be omitted, the default is the last) examples are as follows:

Sql> Select substr (' A sb! ', 3,5) a from dual;

A-----u are

Sql> Select substr (' A sb! ', 3) a from dual;

A------------U is an sb!

INSTR (column|expression, ' string ', Start,stop) String to search for ' string ' string, start start position, stop end position (Start,stop can not be set)

lpad| Rpad (column|expression,n, ' string ') to the left (L) right (R) fills the length of the string to n bits, filling the example with a string as follows:

Sql> Select Lpad (sal,10, ' * '), Rpad (sal,10, ' # ') from EMP where sal>4000;

Lpad (sal,10, ' * ') rpad (sal,10, ' # ')

--------------------   --------------------

5000 5000######

Sql>

TRIM (Head | tail | All, Trim_character from column|expression) Qiatouquwei A string based on Trim_character

replacle (source,search,replace) replaces search in Source with replace

3. Numeric single-line function

ROUND (column|expression,n) rounds the number at the nth bit of the number, rounding it according to the value of the n+1 bit, n>0 from the decimal point to the right, and n<0 from the decimal point to the left

TRUNC (column|expression,n) truncates the number in the nth bit of the number, n>0 from the decimal point to the right, and n<0 from the decimal point to the left

MoD (A, b) for remainder, when A>b mod (A, b) =a%b, when A<b mod (A, b) =a

Sql> Select mod (1200,500) from dual;

MOD (1200,500)

-------------

200

Sql> Select mod (200,500) from dual;

MOD (200,500)

------------

200

SQL base Terms and single-line functions