DB2 database, DML data manipulation language, and DDL Data Definition Language

 

DML (Data Manipulation Language commands) Data Manipulation Language; DDL (Data Definition Language commands) Data Definition Language; TCC (Transaction Control commands) Transaction Control Language; SCC (System Control commands) system Control Language

 

 

DB2 database usage

 

DML (Data Manipulation Language commands) Data Manipulation Language

DDL (Data Definition Language commands) Data Definition Language

TCC (Transaction Control commands) Transaction Control Language

SCC (System Control commands) System Control Language

DML data manipulation language

(1) data query commands

 

Select <query content> From <Table Name>

Where <condition>/* in, between, like % or _*/

Group by <group content>

Having <group conditions>

Order by <sort content> [asc/desc];

(2) data update command

1. Data insertion command

(1) Insert specific values into the table

Insert into <Table Name> [(column name table)]

Values <value table 1>, <value table 2>, <value table 2>...

(Note: The date and period value are enclosed in quotation marks)

(2) Insert data that meets the conditions of other tables into one table.

Insert into <Table Name> [<list Name>] <select clause>

2. data modification command

Update <Table Name> set <column name 1 >=< expression 1>, <column name 2 >=< expression 2>...

[Where <condition>];

Update <Table Name> set <column name 1 >=( <select clause>) [where <condition>];

3. data deletion command

Delete from <Table Name> [where <condition>];

2. DDL Data Definition Language

(1) Basic Data Types

1. String

A string is a byte sequence. The length of a string is the number of bytes in the sequence. If the length is zero, the value of this string is called an empty string.

CHAR (x) is a fixed-length string. (1 = <x <= 254). The default value is 1.

VARCHAR (x) Variable Length character x <= 4000, x> 254 cannot use group by, order by, distinct, or division

Except union all.

GRAPHIC (x) is a fixed graph string. (1 = <x <= 127)

A blob binary string is a byte sequence used to store non-traditional data, such as images, images, and sounds.

Audio and other data.

2 digits: All digits have symbols and precision. The precision is the number of digits or digits that are separated from the open symbol.

SMALLINT small integer, which is a 5-bit two-byte integer.

An INTEGER is a 10-digit four-byte INTEGER.

REAL Single-precision floating point number, which is a 32-bit approximate value of a REAL number.

DOUBLE.

DECIMAL (p, s) DECIMAL is a DECIMAL number. The decimal place consists of the precision (p) and decimal place (s) of the number)

OK. Precision is the total number of digits, which must be smaller than 32. Decimal place is the digit of the decimal part.

Always smaller than or equal to the precision value. If the precision and decimal places are not specified

The default precision is 5, and the default decimal place is 0.

3. datetime value: the datetime value represents the date, time, and timestamp. The datetime value can be used in some arithmetic operations and string operations and is compatible with some strings.

DATE consists of three parts (year, month, and day ).

The TIME is in the 24-hour format and consists of three parts: hour, minute, and second ).

IMESTAMP is divided into seven parts (year, month, day, hour, minute, second, And microsecond ).

4. A null value is a special value different from all non-null values. It means that the column in the row has no

Its value. All data types have null values.

(2) Data Definition

1. Create (Create)

U create table:

Create table [<mode Name>.] <table Name> (<column name 1> <type> [Null | Not null] [, <Column

Name 2> <type>...]

U create View:

Create view [<mode Name>.] <view Name> [<column name table>] as select statement

Create an alias for u:

Create alias [<mode Name>.] alias for [<mode Name>.] Table Name/view name/alias

U index creation:

Create [unique] index <index Name>/* I _ TABLE name_field name */on <Table Name> (<column name

> [Asc | desc]

U Creation Mode:

Create schema mode name authorization permission name

2. Drop (destroy)

U destroy table:

Drop table [<mode Name>.] table Name

U destroy View:

Drop view [<mode Name>.] view name

U destroy alias:

Drop alias [<mode Name>.] alias

U destroy trigger:

Drop trigger [<mode Name>.] trigger name

U:

Drop index [<mode Name>.] <index Name>

U destruction package:

Drop package [<mode Name>.] package name

3. Alter)

U add table columns:

Alter table [<mode Name>.] <table Name> add column [<column name 1> <type> [Null | Not null]...

U adds constraints:

Alter table [<mode Name>.] <table Name> add constraint column name CHECK (constraint)

U deletion constraints:

Alter table [<mode Name>.] <table Name> drop constraint name

U modify the column type:

Alter table [<mode Name>.] <table Name> alter column name set data type <type>

4. Grant)

U grants [all/select/insert/update/delete] permissions to [public/user/group] on the table:

Grant [all/select/insert/update/delete] on [<mode Name>.] Table name to [public/user/

Group];

U grants [bind/execute/] permissions to [public/user/group] on the package:

Grant [bind/execute/] on package [<mode Name>.] [package name] to [public/user/group];

U grants the [control] permission on the index to [public/user/group:

Grant control on index [<mode Name>.] index name to [public/user/Group]

5. Revoke (recycle)

U revokes the [all/select/insert/update/delete] permission on the table from public/user/group:

Revoke [all/select/insert/update/delete] on [Table name] from [public/user/group];

U revokes the [bind/execute/] permission on the package from public/user/group:

Revoke [bind/execute/] on package [<mode Name>.] [package name] from [public/user/group];