A collection of Oracle's Basic grammar

--Table
CREATE TABLE test (names VARCHAR2 (12),
Dates date,
Num INT,
Dou double);
--View
Create or replace view vi_test as
SELECT * from Test;

--Synonyms
Create or replace synonym AA
For DBUSRCARD001.AA;

--Stored procedures
Create or replace produce DD (v_id in Employee.empoy_id%type)
As
Begin

End
dd

--function
Create or Replace function ee (v_id in Employee%rowtype) return varchar (15)
Is
Var_test VARCHAR2 (15);
Begin
return var_test;
Exception when others then

End

--Definition of three kinds of triggers
Create or replace trigger FF
Alter Delete
On test
For each row
Declare
Begin
Delete from test;
If Sql%rowcount < 0 or sql%rowcount is null then
Rais_replaction_err (-20004, "error")
End If
End

Create or replace trigger GG
Alter insert
On test
For each row
Declare
Begin
If:old.names =: New.names Then
Raise_replaction_err (-2003, "Code Repetition");
End If
End

Create or replace trigger HH
For update
On test
For each row
Declare
Begin
If updating then
If:old.names <>: New.names Then
Reaise_replaction_err (-2002, "keyword cannot be modified")
End If
End If
End

--Defining cursors
Declare
Cursor AA is
Select Names,num from Test;
Begin
for BB in AA
Loop
If bb.names = "ORACLE" Then

End If
End Loop;

End

--speed optimization, dozens of times times faster than the previous statement
Select Names,dates
From Test,b
where test.names = B.names (+) and
B.names is null and
B.dates > Date (' 2003-01-01 ', ' yyyy-mm-dd ')

 
Select Names,dates
from test
where names isn't in (select names
     & nbsp;                 from B
                       where dates > to_date (' 2003-01-01 ', ' yyyy-mm-dd '))
                       

--Find duplicate records
Select Names,num
From Test
where rowID!= (select Max (ROWID)
From Test b
where b.names = Test.names and
B.num = test.num)

--Find the first 10 records of the time in table test
SELECT * FROM (SELECT * from Test ORDER BY dates DESC) where RowNum < 11

--The generation of serial numbers
Create sequence row_id
MinValue 1
MaxValue 9999999999999999999999
Start with 1
Increment by 1

INSERT into test values (Row_id.nextval,....)

//

SQL Syntax Reference manual

DB2 provides query language SQL (Structured query Language) of the related database, which is a very colloquial, easy to learn and understandable syntax. This language is almost always required by every database system to represent connected operations, including the definition of data (DDL) and Data Processing (DML). SQL originally spelled SEQUEL, the language of the prototype of "System R" in the name of the IBM San Jose Laboratory completed, through the internal IBM and many other usability and efficiency testing, the results are quite satisfactory, and decided to develop the system R technology based on IBM's products. And the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) in 1987 follow an almost IBM-SQL based standard related data language definition.

Data definition DDL (Language)

Data-setting language refers to the language defined in the format and form of data, he is the first to be set up when each database is to be established, what is the form of data, the form of a field key, tables and tables of the relationship between the reference and so on, are in the beginning must be planned.

Build the form:


Grammar:
CREATE TABLE table_name (
Column1 DATATYPE [NOT NULL] [NOT NULL PRIMARY KEY],
Column2 DATATYPE [Not NULL],
... ）
Description
DATATYPE--is the format of the data, as detailed in the table.
NUT null--Can you allow the data to be empty (no data has been filled in yet).
PRIMARY Key--is the primary key for this table. Examples of building tables.


Change table

ALTER TABLE table_name
ADD COLUMN column_name DATATYPE
Note Add a field (not remove the syntax of a field)
ALTER TABLE table_name
ADD PRIMARY KEY (column_name)
Description change the definition of a table to set a field as the primary key
ALTER TABLE table_name
DROP PRIMARY KEY (column_name)
Description deletes the definition of the primary key.
Change Table Example


Establish an index

CREATE INDEX index_name on table_name (column_name)
Explains how to index a table's fields to increase the speed of a query
Example of establishing an index

Delete

DROP table_name
DROP index_name



Data Form DATATYPE

SQL data-form datatypes
smallint
16-bit integer
Interger
32-bit integer
Decimal (P,s)
P-Exact value and S-size decimal integer, exact value p
It means that all have a few numbers
(digits)
The size value s refers to several digits after the decimal point.
If not specifically specified, the system is set to p=5; S=0
。
Float
32-bit real numbers
Double
64-bit real numbers
CHAR (n)
N-length string, n cannot exceed 254
VARCHAR (n)
A string whose length is not fixed and has a maximum length of n, which cannot be
More than 4000
Graphic (n)
Same as char (n), but its unit is two characters
Double-bytes, N cannot exceed
Over 127. This form is designed to support two character lengths.
Fonts, such as Chinese
Word
Vargraphic (N)
A double character string with a variable length and a maximum length of n
No more than 2000
Date
Contains the year, month, and date.
Time
Contains the hours, minutes, and seconds.
Timestamp
Includes year, month, day, hour, minute, second, 1 per thousand
Seconds.


Data manipulation DML (data manipulation Language)

After the data is defined, the next thing is the operation of the data. The operation of the data is increased
Data (insert), check
Inquiries (query), change data (update), delete data (delete) four
mode, the following are described separately
Their grammar:

Additional Information:


INSERT into table_name (COLUMN1,COLUMN2,...)
VALUES (value1,value2, ...)
Description
1. If no column system is specified, it will be filled in the order of the fields in the form.
Material
2. The data form of the field and the information to be filled must match
3.table_name can also be a landscape view_name


INSERT into table_name (COLUMN1,COLUMN2,...)
SELECT Columnx,columny,... From another_table
Note: You can also go through a subquery (subquery) to the other forms of the funding
The material fills into

Examples of additional information

Inquiry information:


Basic query Examples
Basic Query
SELECT Column1,columns2,...
From table_name
Description: List all the specific field data of the TABLE_NAME
SELECT *
From table_name
WHERE column1 = xxx
[and Column2 &gt; yyy] [OR column3 &lt;&gt; zzz]
Description
1. ' * ' indicates that all of the fields are listed
The 2.WHERE is followed by a conditional type, listing the eligible data
SELECT Column1,column2
From table_name
Order by Column2 [DESC]
Description
Order BY is to specify a column to be sorted, [DESC] refers to from large to small
Arrangement, if
Not specified, is from small to large arrangement


Combination Query Example
Group Query
A combination query means that the source of the query is not only a single form, but
The combination of more than one table will be able to get results.
SELECT *
From Table1,table2
WHERE Table1.colum1=table2.column1
Description
1. Information on the same column1 values in two tables
2. Of course, two tables are compared to each other, and their data form must be the same
3. A complex query may be used in a number of forms


Examples of integrated queries
Consolidated queries:
SELECT COUNT (*)
From table_name
WHERE column_name = xxx
Description
Query for eligible information A total of several pens
SELECT SUM (Column1)
From table_name
Description
1. Calculates the sum, the selected field must be a number shape that can be counted
2. In addition to AVG () is calculated average, MAX (), MIN ()
An integrated query that calculates the maximum minimum value
SELECT Column1,avg (Column2)
From table_name
GROUP by Column1
Having AVG (Column2) &gt; Xxx
Description
1.GROUP by: Calculates the average of Column2 in a group of Column1
Must be used with keywords for consolidated queries such as AVG, SUM, and so on
2.HAVING: Must be used with GROUP by as a consolidation constraint


Examples of complex queries
Complex query
SELECT *
From table_name1
WHERE EXISTS (
SELECT *
From Table_name2
WHERE conditions)
Description
The 1.WHERE conditions can be another query
2.EXISTS here refers to the existence or not
SELECT *
From table_name1
WHERE Column1 in (
SELECT Column1
From Table_name2
WHERE conditions)
Description
1. In the rear is a collection that indicates that the COLUMN1 exists in the collection.
2. The data form of SELECT must conform to Column1


Examples of other queries
Other queries
SELECT *
From table_name1
WHERE column1 like ' x percent '
Description
1.LIKE must correspond to the ' percent ' of the back to indicate a string beginning with X
SELECT *
From table_name1
WHERE column1 in (' xxx ', ' yyy ',..)
Description
1. In the rear is a collection that indicates that the COLUMN1 exists in the collection.
SELECT *
From table_name1
WHERE column1 BETWEEN xx and yy
Description
1. BETWEEN indicates that the value of Column1 is between XX and yy


Change information:


UPDATE table_name
SET column1= ' xxx '
WHERE Conditoins
Description
1. Change a field to set its value to ' xxx '
2.conditions is the condition to be met, if there is no WHERE
That field in the entire table will be changed.

Examples of changing information

Delete data:


DELETE from table_name
WHERE conditions
Note: Delete eligible information

Examples of deleting data