"Symfoware NATIVE" database operation

Symfoware Native Department

The grammatical rules of common elements
Characters: Numbers (0~9), English characters (a~z, a~z), special characters (,, (, =,%), Extended text (@, ￥, #), national characters;
Constants: String constants, national string constants, DateTime constants, time interval constants, signed constants;
Note: "--" or "/*...*/";

Ii. Types of data
String type:
CHARACTER (N)
CHAR (N)
CHARACTER VARYING (N)
CHAR VARYING (N)
VARCHAR (N)
n:1~32000, default is 1;

National language String Type:
National CHARACTER (N)
National CHAR (N)
NCHAR (N)

National CHARACTER VARYING (N)
National CHAR VARYING (N)
NCHAR VARYING (N)
n:1~32000, default is 1;

Integer:
NUMERIC (P,Q)
10 binary number;
P:1~18 q:0~p

DECIMAL (P,Q)
DEC (P,Q)
10 binary number;
P:1~18 q:0~p

INTEGER
Int
-2 of the 31-time Square of the 31-square-1 integer

SMALLINT-2 's 15-time Square-15-square-1 integer

Floating point number:
FLOAT (P)
The P-order estimate of P-sub-square of the mantissa is-2
p:1~52
P=1~23 when the real
Double PRECISION when p=24~52
REAL four-byte floating-point number
DOUBLE PRECISION 8-byte floating-point number

Date Time:
Date Month Day 10 bytes Storage
Time Hour minute seconds 8 bytes Storage
TIMESTAMP 19-byte timestamp

Blob:
BINARY LARGE
OBJECT (n units)
BLOB (n units)
Binary storage, in K, M, G
Units must not be omitted
K) N:1 ~ 2097152
M) N:1 ~ 2048
G) N:1 or 2

N: Number of digits
P: Accuracy
Q: Number of positions

Three, aggregate function
Count (*), AVG, MAX, MIN, SUM, Count, DISTINCT, all

Iv. numerical functions
Position type, extract type, length type, ABS type, ceil, floor type, round type, trunc type, span_date function,
ACOs function, ASIN function, Atan function, ATAN2 function, cos function, exp function, ln function, power function, sign function, sin function, sqrt function, tan function, ASCII function.

Five, data column value function
SUBSTRING or octet_substring (string intercept function), upper or lower (case conversion), Trim function (delete specified character), LTrim function, RTrim function, Lpad, rpad, replace, Replicate type, reverse type, Cnv_char function, Chr function,

Vii. Date-time functions
Current Date value function: Returns the present day (2014-12-11);
Current time value function: Returns the present moment (15:58:57);
Current timestamp value function: Returns the present moment (2007-04-10-15:58:59);
Round_date function: (not commonly used)
Trunc_date: Get a start time based on time and parameters
Example:
Trunc_date (DATE ' 2007-08-22 ', ' year ') → ' 2007-01-01 '
Trunc_date (DATE ' 2007-02-15 ', ' jhalf ') → ' 2006-10-01 '

Add_date function: Returns after increasing the time interval
Example:
Add_date (DATE ' 2006-08-28 ', 6, ' MONTH ') → ' 2007-02-28 '
Add_date (DATE ' 2007-08-31 ', 1, ' MONTH ') → ' 2007-09-30 '
Add_date (DATE ' 2007-08-28 ', -2, ' Day ') → ' 2007-08-26 '

Last_day function: Returns the last day of the month;
cnv_date function, cnv_date function, cnv_time function, Cnv_timestamp function

Eight, Case type
Case shorthand
1, Nullif
Example: Nullif (v1,v2) = = Case if V1=v2 then NULL ELSE V1 END

2, COALESCE
Example: COALESCE (v1,v2) = = case when V1 was not NULL and then V1 ELSE V2 END
COALESCE (V1,V2,VN) = =
Case if V1 is isn't NULL then V1 ELSE coalesce (V2,,VN) END

Case designation
Case V1 while V2 then V3
When V4 then V5
ELSE V6 END
↓ Same
Case if V1=v2 then V3
When V1=v4 then V5
ELSE V6 END

Case use Example
1. SELECT name, Nullif (nickname, name) as nickname
From user table

2. SELECT user, COUNT (case service when 1
Then service ELSE NULL END) as service 1
, COUNT (case service when 2
Then service ELSE NULL END) as service 2
, COUNT (case service when 3
Then service ELSE NULL END) as service 3
From history table GROUP by user

Nine, cast designation
Example:
1, Smallint:cast (10.13 as SMALLINT) →10;
2, Char:cast ( -99.99 as CHAR (6)) →-99.99;
3, Char:cast (INTERVAL ' 20:30 ' MINUTE to SECOND as CHAR (5)) →20:30;

Ten, the Order
1, Currval: Return the last generated value;
2, Nextval: Returns the next value.

Xi. ROWNUM (line number)
Example: Taking the first five rows of data
SELECT A,b,c
From table WHERE a <= and ROWNUM < 6

"Basic SQL"
*alter DSi: Modifying DSi
DSI name: DSI names;
USING: Specifies the split value;
ADD ALLOCATE: Database space specified.
Example:
1. Change the DSI1 split value
CREATE DSO DSO1 ... WHERE (Year Month day) between (?) and (?);
CREATE DSI DSI1 ... USING (Date ' 2005-8-1 ', Date ' 2005-8-31 '),
(Date ' 2006-8-1 ', Date ' 2006-8-31 ');
：
：
Alter DSI DSI1 ALTER USING (DATE ' 2006-8-1 ', Date ' 2006-8-31 '),
(Date ' 2007-8-1 ', Date ' 2007-8-31 ');

2. Expand DSI1 Space
ALTER DSI DSI1 ADD ALLOCATE on DBSP1 SIZE 1M;

*alter table: Modifying tables
*alter User: Modify users ' information
*apply Scope: Change the scope of application
APPLY SCOPE Tokyo SCP to YAMADA
*call: Function call
*close: Close pointer
*commit: Submit
*connect: Connect
*create Database: Creating Databases
*create DBSPACE: Creating Database Space
Example:
CREATE DBSPACE RDBS01 ALLOCATE FILE C:\RDB2\DBSP1 ATTRIBUTE SPACE (2M)

*create DSO: Create Indexdso
*create DSO: Create DSO
*create DSI: Creating Indexdsi
*create DSi: Creating DSi
Examples of the above four commands:
CREATE DSO Companydso
From Administrator.company
TYPE Sequential (PAGESIZE (4), ORDER (1));

CREATE DSI Companydsi
DSO COMPANYDSO
ALLOCATE DATA on DBSPACE1 SIZE 280K;

CREATE DSO Companynumixdso
INDEX on Administrator.company (companynum)
TYPE BTREE (PAGESIZE1 (+), PAGESIZE2 (1), realignment) by ADDRESS;

CREATE DSI Companynumixdsi
INDEX
DSO COMPANYNUMIXDSO
ALLOCATE BASE on DBSPACE1 SIZE 200K,
INDEX on DBSPACE1 SIZE 40K;

*create function: Defining functions
Example (Windows):
CREATE FUNCTION S1. FUNC1
(In Integer,in INTEGER)
RETURNS INTEGER
LANGUAGE C NAME ' user_apl1 '
LIBRARY ' D:\FORSYMFO\FUNCLIB\USERFUNC001. DLL '

*create Index: Defining indexes
CREATE TABLE S1. T1 (C1 INT not NULL,
C2 INT not NULL,
C3 INT not NULL) on DBSPACE1;
CREATE INDEX S1. T1. IXA KEY (C1) on DBSPACE2;

*create PROCEDURE: Create stored procedure
*create role: Create role
*create Schema: Create schema
*create scope: Create Scope
*create SEQUENCE: Create a sequence
*create table: Create TABLE
*create TRIGGER: Create TRIGGER
*create User: Create user
*create view: Create VIEW
*declare Cursor: Removing the pointer
*declare table: Deleting tables
*delete (Location-based):
Example: Delete from TBL1 WHERE current of CSR1 deletes the row that is currently indicated by the cursor.
*delete (Retrieval-based):
Example: DELETE from TBL1 WHERE COL2 = ' R ' and COL3 = 1

*disconnect: Disconnecting
*drop database: Deleting databases
*drop DBSPACE: Deleting database space
*drop DSi: Remove DSi
*drop DSO: Remove DSO
*drop function: Deleting functions
*drop Index: Deleting indexes
*drop PROCEDURE: Deleting stored procedures
*drop role: Deleting roles
*drop Schema: Delete mode
*drop Scope: Delete scope
*drop SEQUENCE: Deleting a sequence
*drop table: Deleting tables
*drop TRIGGER: Delete trigger
*drop User: Delete users
*drop View: Deleting views
*fetch:
Example:
1, in order to retrieve T1, define CSR1
DECLARE CSR1 CURSOR for
SELECT col1,col2,col3 from T1 WHERE COL4 > 10
ORDER by COL2 Desc,col3 ASC
The cursor is a declaration statement rather than an execution statement, and you can use open, fetch, or close.
2. Open CSR1
OPEN CSR1
3. Perform operation
FETCH CSR1 INTO:TGT1,:TGT2,:TGT3
4. Close CSR1
CLOSE CSR1

*grant: Giving permission
Example:
GRANT all privileges on table STOCKS. In the library to Yamada,tanaka

*insert: Inserting data
*open: Open cursor (cursor)
*print STATISTICS: Output the latest setup information
*release Scope: Contact range
Example: RELEASE SCOPE SCP1 from YAMADA

*release table: Delete temporary table information, release temporary tablespace
*revoke: Reclaim Permissions
*rollback: Rolling back
*set CATALOG: Changing the database name
*set CONNECTION: Change connection
Example:
CONNECT to ' SV1 ' as ' C1 ' USER ' User1/pass1 '
CONNECT to ' SV2 ' as ' C2 ' USER ' user2/pass2 '
：
SET CONNECTION ' C1 '

*set Role: Setting roles
*set Schema: Setting mode
*set SESSION AUTHORIZATION: Change user
*set STATISTICS
*set SYSTEM PARAMETER
*set TRANSACTION
*set USER PASSWORD
*single Row SELECT
*swap TABLE
*update (Location-based)
Example:
UPDATE TBL1
SET COL2 = ' A ', COL3 = NULL
WHERE Current OF CSR1
*update (based on search)
UPDATE TBL1
SET COL1 = +, COL2 = NULL
WHERE COL1 = OR COL3 = 5

This article is from the "Night" blog, be sure to keep this source http://icyore.blog.51cto.com/8486958/1596302

"Symfoware NATIVE" database operation