CONTENT
- CHANGES
Szukaj
#top Zapytania SQL¶
- Zapytania SQL
- users
- USER CREATE
- USER ALTER
- USER SET PASSWORD
- USER DROP
- permissions
- GRANT
- REVOKE
- SHOW GRANTS
- databases
- DATABASE CREATE
- DATABASE ALTER
- DATABASE DROP
- schemas
- SCHEMA CREATE
- SCHEMA ALTER
- SCHEMA DROP
- tablespaces
- TABLESPACE CREATE
- TABLESPACE ALTER
- TABLESPACE DROP
- tables
- TABLE CREATE
- TABLE CREATE LIKE
- TABLE CREATE AS
- TABLE CREATE with PRIMARY KEY
- TABLE CREATE with auto_increment
- TABLE CREATE with UNIQUE KEY (singlecolumn)
- TABLE CREATE with UNIQUE KEY (multicolumn)
- TABLE CREATE with FULLTEXT KEY
- TABLE CREATE with FOREIGN KEY
- TABLE CREATE with INDEX
- TABLE CREATE with partitioned
- TABLE CREATE with COLLATE without UNIQUE
- TABLE CREATE with COLLATE with UNIQUE
- TABLE ALTER
- TABLE ALTER with PRIMARY KEY
- TABLE ALTER with auto increment
- TABLE ALTER with UNIQUE KEY (singlecolumn)
- TABLE ALTER with UNIQUE KEY (multicolumn)
- TABLE ALTER with FULLTEXT KEY
- TABLE ALTER with FOREIGN KEY
- TABLE ALTER with INDEX
- TABLE ALTER with partitioned
- TABLE ALTER with COLLATE without UNIQUE
- TABLE ALTER with COLLATE with UNIQUE
- TABLE RENAME
- TABLE TRUNCATE
- TABLE DROP
- sequences
- SEQUENCE CREATE
- SEQUENCE ALTER
- SEQUENCE DROP
- indexes
- INDEX CREATE
- INDEX ALTER
- INDEX DROP
- functions
- Functions and Operators
- DATA Manipulations
- SELECT with LIMIT and OFFSET
- WHERE IN subselect with multiple columns
- Deleting duplicates rows
- REPLACE INTO
- GROUP with CONCAT
#top users¶
#top USER CREATE¶
Zobacz także USER CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0050615.html
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0002172.html
Składnia:
>>-CREATE ROLE--role-name--------------------------------------><
Example:
-- comment
#top USER ALTER¶
Zobacz także USER ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0002166.html
Składnia:
>>-ALTER USER MAPPING FOR--+-authorization-name-+--------------->
+-USER---------------+
'-PUBLIC-------------'
>--SERVER--server-name------------------------------------------>
.-,----------------------------------------------.
V .-ADD-. |
>--OPTIONS -(----+-+-----+--user-option-name--string-constant-+-+--)-><
| '-SET-' |
'-DROP--user-option-name---------------------'
Example:
-- comment
#top USER SET PASSWORD¶
Zobacz także USER SET PASSWORD dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
Składnia:
comment
Example:
ALTER USER davide WITH PASSWORD 'hu8jmn3';
#top USER DROP¶
Zobacz także USER DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000945.html
Składnia:
>>-DROP---------------------------------------------------------> >--+-| alias-designator |------------------------------------------+->< +-AUDIT POLICY--policy-name-------------------------------------+ +-BUFFERPOOL--bufferpool-name-----------------------------------+ +-DATABASE PARTITION GROUP--db-partition-group-name-------------+ +-EVENT MONITOR--event-monitor-name-----------------------------+ +-| function-designator |--+----------+-------------------------+ | '-RESTRICT-' | +-FUNCTION MAPPING--function-mapping-name-----------------------+ +-HISTOGRAM TEMPLATE--template-name-----------------------------+ | (1) | +-INDEX--index-name---------------------------------------------+ +-INDEX EXTENSION--index-extension-name--RESTRICT---------------+ +-MASK--mask-name-----------------------------------------------+ +-| method-designator |--+----------+---------------------------+ | '-RESTRICT-' | +-MODULE--module-name-------------------------------------------+ +-NICKNAME--nickname--------------------------------------------+ +-PACKAGE--package-name--+-------------------------+------------+ | | .-VERSION-. | | | '-+---------+--version-id-' | +-PERMISSION--permission-name-----------------------------------+ +-| procedure-designator |--+----------+------------------------+ | '-RESTRICT-' | +-ROLE--role-name-----------------------------------------------+ +-SCHEMA--schema-name--RESTRICT---------------------------------+ | .-RESTRICT-. | +-SECURITY LABEL--security-label-name--+----------+-------------+ | .-RESTRICT-. | +-SECURITY LABEL COMPONENT--sec-label-comp-name--+----------+---+ | .-RESTRICT-. | +-SECURITY POLICY--security-policy-name--+----------+-----------+ +-SEQUENCE--sequence-name--+----------+-------------------------+ | '-RESTRICT-' | +-SERVER--server-name-------------------------------------------+ | .-RESTRICT-. | +-| service-class-designator |--+----------+--------------------+ | .-RESTRICT-. | +-STOGROUP--storagegroup-name--+----------+---------------------+ +-TABLE--table-name---------------------------------------------+ +-TABLE HIERARCHY--root-table-name------------------------------+ | .-,---------------. | | V | | +-+-TABLESPACE--+----tablespace-name-+--------------------------+ | '-TABLESPACES-' | +-+-TRANSFORM--+--+-ALL--------+--FOR--type-name----------------+ | '-TRANSFORMS-' '-group-name-' | +-THRESHOLD--threshold-name-------------------------------------+ +-TRIGGER--trigger-name-----------------------------------------+ +-TRUSTED CONTEXT--context-name---------------------------------+ +-TYPE--type-name--+----------+---------------------------------+ | '-RESTRICT-' | +-TYPE MAPPING--type-mapping-name-------------------------------+ +-USAGE LIST--usage-list-name-----------------------------------+ +-USER MAPPING FOR--+-authorization-name-+--SERVER--server-name-+ | '-USER---------------' | +-VARIABLE--variable-name--+----------+-------------------------+ | '-RESTRICT-' | +-VIEW--view-name-----------------------------------------------+ +-VIEW HIERARCHY--root-view-name--------------------------------+ +-WORK ACTION SET--work-action-set-name-------------------------+ +-WORK CLASS SET--work-class-set-name---------------------------+ +-WORKLOAD--workload-name---------------------------------------+ +-WRAPPER--wrapper-name-----------------------------------------+ '-XSROBJECT--xsrobject-name-------------------------------------'
Example:
-- comment
#top permissions¶
#top GRANT¶
Zobacz także GRANT dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0050616.html
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000966.html
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0050618.html
Składnia:
.-,---------.
.-ROLE-. V |
>>-GRANT--+------+----role-name-+------------------------------->
.-,---------------------------------.
V |
>--TO----+-+-------+--authorization-name-+-+-------------------->
| +-USER--+ |
| +-GROUP-+ |
| '-ROLE--' |
'-PUBLIC------------------------'
>--+-------------------+---------------------------------------><
'-WITH ADMIN OPTION-'
Example:
-- comment
#top REVOKE¶
Zobacz także REVOKE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0050617.html
Składnia:
.-,---------.
.-ROLE-. V |
>>-REVOKE--+------------------+--+------+----role-name-+-------->
'-ADMIN OPTION FOR-'
.-,---------------------------------.
V | .-BY ALL-.
>--FROM----+-+-------+--authorization-name-+-+--+--------+-----><
| +-USER--+ |
| +-GROUP-+ |
| '-ROLE--' |
'-PUBLIC------------------------'
Example:
-- comment
#top SHOW GRANTS¶
Zobacz także SHOW GRANTS dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
Składnia:
comment
Example:
-- comment
#top databases¶
#top DATABASE CREATE¶
Zobacz także DATABASE CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
Składnia:
comment
Example:
-- comment
#top DATABASE ALTER¶
Zobacz także DATABASE ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
Składnia:
comment
Example:
-- comment
#top DATABASE DROP¶
Zobacz także DATABASE DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000945.html
Składnia:
>>-DROP---------------------------------------------------------> >--+-| alias-designator |------------------------------------------+->< +-AUDIT POLICY--policy-name-------------------------------------+ +-BUFFERPOOL--bufferpool-name-----------------------------------+ +-DATABASE PARTITION GROUP--db-partition-group-name-------------+ +-EVENT MONITOR--event-monitor-name-----------------------------+ +-| function-designator |--+----------+-------------------------+ | '-RESTRICT-' | +-FUNCTION MAPPING--function-mapping-name-----------------------+ +-HISTOGRAM TEMPLATE--template-name-----------------------------+ | (1) | +-INDEX--index-name---------------------------------------------+ +-INDEX EXTENSION--index-extension-name--RESTRICT---------------+ +-MASK--mask-name-----------------------------------------------+ +-| method-designator |--+----------+---------------------------+ | '-RESTRICT-' | +-MODULE--module-name-------------------------------------------+ +-NICKNAME--nickname--------------------------------------------+ +-PACKAGE--package-name--+-------------------------+------------+ | | .-VERSION-. | | | '-+---------+--version-id-' | +-PERMISSION--permission-name-----------------------------------+ +-| procedure-designator |--+----------+------------------------+ | '-RESTRICT-' | +-ROLE--role-name-----------------------------------------------+ +-SCHEMA--schema-name--RESTRICT---------------------------------+ | .-RESTRICT-. | +-SECURITY LABEL--security-label-name--+----------+-------------+ | .-RESTRICT-. | +-SECURITY LABEL COMPONENT--sec-label-comp-name--+----------+---+ | .-RESTRICT-. | +-SECURITY POLICY--security-policy-name--+----------+-----------+ +-SEQUENCE--sequence-name--+----------+-------------------------+ | '-RESTRICT-' | +-SERVER--server-name-------------------------------------------+ | .-RESTRICT-. | +-| service-class-designator |--+----------+--------------------+ | .-RESTRICT-. | +-STOGROUP--storagegroup-name--+----------+---------------------+ +-TABLE--table-name---------------------------------------------+ +-TABLE HIERARCHY--root-table-name------------------------------+ | .-,---------------. | | V | | +-+-TABLESPACE--+----tablespace-name-+--------------------------+ | '-TABLESPACES-' | +-+-TRANSFORM--+--+-ALL--------+--FOR--type-name----------------+ | '-TRANSFORMS-' '-group-name-' | +-THRESHOLD--threshold-name-------------------------------------+ +-TRIGGER--trigger-name-----------------------------------------+ +-TRUSTED CONTEXT--context-name---------------------------------+ +-TYPE--type-name--+----------+---------------------------------+ | '-RESTRICT-' | +-TYPE MAPPING--type-mapping-name-------------------------------+ +-USAGE LIST--usage-list-name-----------------------------------+ +-USER MAPPING FOR--+-authorization-name-+--SERVER--server-name-+ | '-USER---------------' | +-VARIABLE--variable-name--+----------+-------------------------+ | '-RESTRICT-' | +-VIEW--view-name-----------------------------------------------+ +-VIEW HIERARCHY--root-view-name--------------------------------+ +-WORK ACTION SET--work-action-set-name-------------------------+ +-WORK CLASS SET--work-class-set-name---------------------------+ +-WORKLOAD--workload-name---------------------------------------+ +-WRAPPER--wrapper-name-----------------------------------------+ '-XSROBJECT--xsrobject-name-------------------------------------'
Example:
-- comment
#top schemas¶
#top SCHEMA CREATE¶
Zobacz także SCHEMA CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000925.html
Składnia:
>>-CREATE SCHEMA------------------------------------------------>
>--+-schema-name------------------------------------+----------->
+-AUTHORIZATION--authorization-name--------------+
'-schema-name--AUTHORIZATION--authorization-name-'
>--+---------------------------+--+--------------------------+-><
'-DATA CAPTURE--+-NONE----+-' | .----------------------. |
'-CHANGES-' | V | |
'---schema-SQL-statement-+-'
Example:
-- comment
#top SCHEMA ALTER¶
Zobacz także SCHEMA ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0058662.html
Składnia:
>>-ALTER SCHEMA--schema-name--DATA CAPTURE--+-NONE----+--------><
'-CHANGES-'
Example:
-- comment
#top SCHEMA DROP¶
Zobacz także SCHEMA DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000945.html
Składnia:
>>-DROP---------------------------------------------------------> >--+-| alias-designator |------------------------------------------+->< +-AUDIT POLICY--policy-name-------------------------------------+ +-BUFFERPOOL--bufferpool-name-----------------------------------+ +-DATABASE PARTITION GROUP--db-partition-group-name-------------+ +-EVENT MONITOR--event-monitor-name-----------------------------+ +-| function-designator |--+----------+-------------------------+ | '-RESTRICT-' | +-FUNCTION MAPPING--function-mapping-name-----------------------+ +-HISTOGRAM TEMPLATE--template-name-----------------------------+ | (1) | +-INDEX--index-name---------------------------------------------+ +-INDEX EXTENSION--index-extension-name--RESTRICT---------------+ +-MASK--mask-name-----------------------------------------------+ +-| method-designator |--+----------+---------------------------+ | '-RESTRICT-' | +-MODULE--module-name-------------------------------------------+ +-NICKNAME--nickname--------------------------------------------+ +-PACKAGE--package-name--+-------------------------+------------+ | | .-VERSION-. | | | '-+---------+--version-id-' | +-PERMISSION--permission-name-----------------------------------+ +-| procedure-designator |--+----------+------------------------+ | '-RESTRICT-' | +-ROLE--role-name-----------------------------------------------+ +-SCHEMA--schema-name--RESTRICT---------------------------------+ | .-RESTRICT-. | +-SECURITY LABEL--security-label-name--+----------+-------------+ | .-RESTRICT-. | +-SECURITY LABEL COMPONENT--sec-label-comp-name--+----------+---+ | .-RESTRICT-. | +-SECURITY POLICY--security-policy-name--+----------+-----------+ +-SEQUENCE--sequence-name--+----------+-------------------------+ | '-RESTRICT-' | +-SERVER--server-name-------------------------------------------+ | .-RESTRICT-. | +-| service-class-designator |--+----------+--------------------+ | .-RESTRICT-. | +-STOGROUP--storagegroup-name--+----------+---------------------+ +-TABLE--table-name---------------------------------------------+ +-TABLE HIERARCHY--root-table-name------------------------------+ | .-,---------------. | | V | | +-+-TABLESPACE--+----tablespace-name-+--------------------------+ | '-TABLESPACES-' | +-+-TRANSFORM--+--+-ALL--------+--FOR--type-name----------------+ | '-TRANSFORMS-' '-group-name-' | +-THRESHOLD--threshold-name-------------------------------------+ +-TRIGGER--trigger-name-----------------------------------------+ +-TRUSTED CONTEXT--context-name---------------------------------+ +-TYPE--type-name--+----------+---------------------------------+ | '-RESTRICT-' | +-TYPE MAPPING--type-mapping-name-------------------------------+ +-USAGE LIST--usage-list-name-----------------------------------+ +-USER MAPPING FOR--+-authorization-name-+--SERVER--server-name-+ | '-USER---------------' | +-VARIABLE--variable-name--+----------+-------------------------+ | '-RESTRICT-' | +-VIEW--view-name-----------------------------------------------+ +-VIEW HIERARCHY--root-view-name--------------------------------+ +-WORK ACTION SET--work-action-set-name-------------------------+ +-WORK CLASS SET--work-class-set-name---------------------------+ +-WORKLOAD--workload-name---------------------------------------+ +-WRAPPER--wrapper-name-----------------------------------------+ '-XSROBJECT--xsrobject-name-------------------------------------'
Example:
-- comment
#top tablespaces¶
#top TABLESPACE CREATE¶
Zobacz także TABLESPACE CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000929.html
Składnia:
>>-CREATE--+-----------------------+---------------------------->
+-LARGE-----------------+
+-REGULAR---------------+
| .-SYSTEM-. |
'-+--------+--TEMPORARY-'
'-USER---'
>--TABLESPACE--tablespace-name---------------------------------->
>--+-----------------------------------------------------------+-->
| .-DATABASE PARTITION GROUP-. |
'-IN--+--------------------------+--db-partition-group-name-'
>--+--------------------------+--------------------------------->
'-PAGESIZE--integer--+---+-'
'-K-'
.-MANAGED BY--AUTOMATIC STORAGE--| storage-group |--| size-attributes |--.
>--+------------------------------------------------------------------------+-->
'-MANAGED BY--+-SYSTEM--| system-containers |--------------------------+-'
'-DATABASE--| database-containers |--| size-attributes |-'
>--+---------------------------------+-------------------------->
'-EXTENTSIZE--+-number-of-pages-+-'
'-integer--+-K-+--'
'-M-'
>--+-----------------------------------+------------------------>
'-PREFETCHSIZE--+-AUTOMATIC-------+-'
+-number-of-pages-+
'-integer--+-K-+--'
'-M-'
>--+-----------------------------+------------------------------>
'-BUFFERPOOL--bufferpool-name-'
>--+--------------------------------------+--------------------->
'-OVERHEAD--+-number-of-milliseconds-+-'
'-INHERIT----------------'
>--+------------------------+----------------------------------->
+-NO FILE SYSTEM CACHING-+
'-FILE SYSTEM CACHING----'
>--+------------------------------------------+----------------->
'-TRANSFERRATE--+-number-of-milliseconds-+-'
'-INHERIT----------------'
>--+--------------------------------+--------------------------->
'-DATA TAG--+-integer-constant-+-'
+-INHERIT----------+
'-NONE-------------'
>--+---------------------------------+-------------------------><
'-DROPPED TABLE RECOVERY--+-ON--+-'
'-OFF-'
storage-group
|--+-----------------------------------+------------------------|
'-USING STOGROUP--storagegroup-name-'
size-attributes
|--+---------------------+--+-----------------------------+----->
'-AUTORESIZE--+-NO--+-' '-INITIALSIZE--integer--+-K-+-'
'-YES-' +-M-+
'-G-'
>--+------------------------------------+----------------------->
'-INCREASESIZE--integer--+-PERCENT-+-'
'-+-K-+---'
+-M-+
'-G-'
>--+-----------------------------+------------------------------|
'-MAXSIZE--+-integer--+-K-+-+-'
| +-M-+ |
| '-G-' |
'-NONE-----------'
system-containers
.----------------------------------------------------------------------.
| .-,------------------. |
V V | |
|----USING--(----'container-string'-+--)--+-----------------------------+-+--|
'-| on-db-partitions-clause |-'
database-containers
.--------------------------------------------------------------.
V |
|----USING--| container-clause |--+-----------------------------+-+--|
'-| on-db-partitions-clause |-'
container-clause
.-,---------------------------------------------------.
V |
|--(----+-FILE---+--'container-string'--+-number-of-pages-+-+--)--|
'-DEVICE-' '-integer--+-K-+--'
+-M-+
'-G-'
on-db-partitions-clause
|--ON--+-DBPARTITIONNUM--+-------------------------------------->
'-DBPARTITIONNUMS-'
.-,--------------------------------------------------.
V |
>--(----db-partition-number1--+--------------------------+-+--)--|
'-TO--db-partition-number2-'
Example:
-- comment
#top TABLESPACE ALTER¶
Zobacz także TABLESPACE ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000890.html
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0003492.html
Składnia:
>>-ALTER TABLESPACE--tablespace-name---------------------------->
.------------------------------------------------------------------------------------.
V |
>----+-ADD--| add-clause |------------------------------------------------------------+-+-><
+-BEGIN NEW STRIPE SET--| db-container-clause |--+-----------------------------+-+
| '-| on-db-partitions-clause |-' |
+-DROP--| drop-container-clause |--+-----------------------------+---------------+
| '-| on-db-partitions-clause |-' |
+-REDUCE--+---------------------------+--+-----------------------------+---------+
| +-| db-container-clause |---+ '-| on-db-partitions-clause |-' |
| +-| all-containers-clause |-+ |
| +-MAX-----------------------+ |
| +-STOP----------------------+ |
| '-integer--+---------+------' |
| +-K-------+ |
| +-M-------+ |
| +-G-------+ |
| '-PERCENT-' |
+-+-EXTEND-+--+-| db-container-clause |---+--+-----------------------------+-----+
| '-RESIZE-' '-| all-containers-clause |-' '-| on-db-partitions-clause |-' |
+-REBALANCE--+---------+---------------------------------------------------------+
| +-SUSPEND-+ |
| '-RESUME--' |
+-PREFETCHSIZE--+-AUTOMATIC-------+----------------------------------------------+
| +-number-of-pages-+ |
| '-integer--+-K-+--' |
| '-M-' |
+-BUFFERPOOL--bufferpool-name----------------------------------------------------+
+-OVERHEAD--+-number-of-milliseconds-+-------------------------------------------+
| '-INHERIT----------------' |
+-TRANSFERRATE--+-number-of-milliseconds-+---------------------------------------+
| '-INHERIT----------------' |
+-+-FILE SYSTEM CACHING----+-----------------------------------------------------+
| '-NO FILE SYSTEM CACHING-' |
+-DROPPED TABLE RECOVERY--+-ON--+------------------------------------------------+
| '-OFF-' |
+-SWITCH ONLINE------------------------------------------------------------------+
+-AUTORESIZE--+-NO--+------------------------------------------------------------+
| '-YES-' |
+-INCREASESIZE--integer--+-PERCENT-+---------------------------------------------+
| '-+-K-+---' |
| +-M-+ |
| '-G-' |
+-MAXSIZE--+-integer--+-K-+-+----------------------------------------------------+
| | +-M-+ | |
| | '-G-' | |
| '-NONE-----------' |
+-CONVERT TO LARGE---------------------------------------------------------------+
+-LOWER HIGH WATER MARK--+------+------------------------------------------------+
| '-STOP-' |
+-USING STOGROUP--storagegroup-name----------------------------------------------+
+-DATA TAG--+-integer-constant-+-------------------------------------------------+
| +-INHERIT----------+ |
| '-NONE-------------' |
'-MANAGED BY AUTOMATIC STORAGE---------------------------------------------------'
add-clause
|--+-+--------------------------+--| db-container-clause |--+-----------------------------+-+--|
| '-TO STRIPE SET--stripeset-' '-| on-db-partitions-clause |-' |
'-| system-container-clause |--| on-db-partitions-clause |-------------------------------'
db-container-clause
.-,---------------------------------------------------.
V |
|--(----+-FILE---+--'container-string'--+-number-of-pages-+-+--)--|
'-DEVICE-' '-integer--+-K-+--'
+-M-+
'-G-'
drop-container-clause
.-,------------------------------.
V |
|--(----+-FILE---+--'container-string'-+--)---------------------|
'-DEVICE-'
system-container-clause
.-,------------------.
V |
|--(----'container-string'-+--)---------------------------------|
on-db-partitions-clause
|--ON--+-DBPARTITIONNUM--+-------------------------------------->
'-DBPARTITIONNUMS-'
.-,--------------------------------------------------.
V |
>--(----db-partition-number1--+--------------------------+-+--)--|
'-TO--db-partition-number2-'
all-containers-clause
.-CONTAINERS-.
|--(--ALL--+------------+--+-number-of-pages-+--)---------------|
'-integer--+-K-+--'
+-M-+
'-G-'
Składnia:>>-RENAME--TABLESPACE--source-tablespace-name-------------------> >--TO--target-tablespace-name----------------------------------><
Example:
-- comment
#top TABLESPACE DROP¶
Zobacz także TABLESPACE DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000945.html
Składnia:
>>-DROP---------------------------------------------------------> >--+-| alias-designator |------------------------------------------+->< +-AUDIT POLICY--policy-name-------------------------------------+ +-BUFFERPOOL--bufferpool-name-----------------------------------+ +-DATABASE PARTITION GROUP--db-partition-group-name-------------+ +-EVENT MONITOR--event-monitor-name-----------------------------+ +-| function-designator |--+----------+-------------------------+ | '-RESTRICT-' | +-FUNCTION MAPPING--function-mapping-name-----------------------+ +-HISTOGRAM TEMPLATE--template-name-----------------------------+ | (1) | +-INDEX--index-name---------------------------------------------+ +-INDEX EXTENSION--index-extension-name--RESTRICT---------------+ +-MASK--mask-name-----------------------------------------------+ +-| method-designator |--+----------+---------------------------+ | '-RESTRICT-' | +-MODULE--module-name-------------------------------------------+ +-NICKNAME--nickname--------------------------------------------+ +-PACKAGE--package-name--+-------------------------+------------+ | | .-VERSION-. | | | '-+---------+--version-id-' | +-PERMISSION--permission-name-----------------------------------+ +-| procedure-designator |--+----------+------------------------+ | '-RESTRICT-' | +-ROLE--role-name-----------------------------------------------+ +-SCHEMA--schema-name--RESTRICT---------------------------------+ | .-RESTRICT-. | +-SECURITY LABEL--security-label-name--+----------+-------------+ | .-RESTRICT-. | +-SECURITY LABEL COMPONENT--sec-label-comp-name--+----------+---+ | .-RESTRICT-. | +-SECURITY POLICY--security-policy-name--+----------+-----------+ +-SEQUENCE--sequence-name--+----------+-------------------------+ | '-RESTRICT-' | +-SERVER--server-name-------------------------------------------+ | .-RESTRICT-. | +-| service-class-designator |--+----------+--------------------+ | .-RESTRICT-. | +-STOGROUP--storagegroup-name--+----------+---------------------+ +-TABLE--table-name---------------------------------------------+ +-TABLE HIERARCHY--root-table-name------------------------------+ | .-,---------------. | | V | | +-+-TABLESPACE--+----tablespace-name-+--------------------------+ | '-TABLESPACES-' | +-+-TRANSFORM--+--+-ALL--------+--FOR--type-name----------------+ | '-TRANSFORMS-' '-group-name-' | +-THRESHOLD--threshold-name-------------------------------------+ +-TRIGGER--trigger-name-----------------------------------------+ +-TRUSTED CONTEXT--context-name---------------------------------+ +-TYPE--type-name--+----------+---------------------------------+ | '-RESTRICT-' | +-TYPE MAPPING--type-mapping-name-------------------------------+ +-USAGE LIST--usage-list-name-----------------------------------+ +-USER MAPPING FOR--+-authorization-name-+--SERVER--server-name-+ | '-USER---------------' | +-VARIABLE--variable-name--+----------+-------------------------+ | '-RESTRICT-' | +-VIEW--view-name-----------------------------------------------+ +-VIEW HIERARCHY--root-view-name--------------------------------+ +-WORK ACTION SET--work-action-set-name-------------------------+ +-WORK CLASS SET--work-class-set-name---------------------------+ +-WORKLOAD--workload-name---------------------------------------+ +-WRAPPER--wrapper-name-----------------------------------------+ '-XSROBJECT--xsrobject-name-------------------------------------'
Example:
-- comment
#top tables¶
#top TABLE CREATE¶
Zobacz także TABLE CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html
Składnia:
>>-CREATE TABLE--table-name------------------------------------->
>--+-| element-list |----------------------------+--*----------->
+-OF--type-name1--+-------------------------+-+
| '-| typed-table-options |-' |
+-LIKE--+-table-name1-+--+------------------+-+
| +-view-name---+ '-| copy-options |-' |
| '-nickname----' |
+-| as-result-table |--+------------------+---+
| '-| copy-options |-' |
+-| materialized-query-definition |-----------+
'-| staging-table-definition |----------------'
>--+-----------------------------------------------------------------------------+-->
'-ORGANIZE BY--+-ROW--------------------------------------------------------+-'
+-COLUMN-----------------------------------------------------+
'-+---------------+--+-| dimensions-clause |---------------+-'
| (1) | +-KEY SEQUENCE--| sequence-key-spec |-+
'-ROW USING-----' '-INSERT TIME-------------------------'
>--*--+---------------------------+--*-------------------------->
'-DATA CAPTURE--+-NONE----+-'
'-CHANGES-'
>--+------------------------+--*--+-------------------------+--->
'-| tablespace-clauses |-' '-| distribution-clause |-'
>--*--+-------------------------+------------------------------->
'-| partitioning-clause |-'
.-COMPRESS NO---------------.
>--*--+---------------------------+--*--+-------------------+--->
| .-ADAPTIVE-. | '-VALUE COMPRESSION-'
'-COMPRESS YES-+----------+-'
'-STATIC---'
>--*--+-----------------------+--*--+----------------------+---->
'-WITH RESTRICT ON DROP-' '-NOT LOGGED INITIALLY-'
>--*--+--------------------+------------------------------------>
'-CCSID--+-ASCII---+-'
'-UNICODE-'
>--*--+------------------------------+--*----------------------->
'-SECURITY POLICY--policy name-'
>--+-------------------------------------------------------+---><
| .-,----------------------------------. |
| V | |
'-OPTIONS--(----table-option-name--string-constant-+--)-'
element-list
.-,------------------------------.
V |
|--(----+-| column-definition |------+-+--)---------------------|
+-| period-definition |------+
+-| unique-constraint |------+
+-| referential-constraint |-+
'-| check-constraint |-------'
column-definition
|--column-name--+-------------------+--+--------------------+---|
| (2) | '-| column-options |-'
'-| data-type |-----'
data-type
|--+-| built-in-type |------+-----------------------------------|
| (3) |
+-distinct-type-name-----+
+-structured-type-name---+
'-REF--(type-name2)------'
built-in-type
|--+-+-SMALLINT----+----------------------------------------------------------------------+--|
| +-+-INTEGER-+-+ |
| | '-INT-----' | |
| '-BIGINT------' |
| .-(5,0)-------------------. |
+-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+
| | '-DEC-----' | | .-,0-------. | |
| '-+-NUMERIC-+-' '-(integer-+----------+-)-' |
| '-NUM-----' '-,integer-' |
| .-(53)------. |
+-+-FLOAT--+-----------+--+------------------------------------------------------------+
| | '-(integer)-' | |
| +-REAL------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+-' |
| .-(34)-. |
+-DECFLOAT--+------+-------------------------------------------------------------------+
| '-(16)-' |
| .-(1)------------------------. |
+-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+
| | | '-CHAR------' '-(integer-+-------------+-)-' | | (4) | | |
| | | +-OCTETS------+ | '-FOR BIT DATA-----' | |
| | | '-CODEUNITS32-' | | |
| | '-+-VARCHAR----------------+--(integer-+-------------+-)-' | |
| | '-+-CHARACTER-+--VARYING-' +-OCTETS------+ | |
| | '-CHAR------' '-CODEUNITS32-' | |
| | .-(1M)-----------------------------. | |
| '-+-CLOB------------------------+--+----------------------------------+------------' |
| '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+-------------+-)-' |
| '-CHAR------' +-K-+ +-OCTETS------+ |
| +-M-+ '-CODEUNITS32-' |
| '-G-' |
| .-(1)------------------------. |
+-+-GRAPHIC--+----------------------------+------+-------------------------------------+
| | '-(integer-+-------------+-)-' | |
| | +-CODEUNITS16-+ | |
| | '-CODEUNITS32-' | |
| +-VARGRAPHIC--(integer-+-------------+-)-------+ |
| | +-CODEUNITS16-+ | |
| | '-CODEUNITS32-' | |
| | .-(1M)-----------------------------. | |
| '-DBCLOB--+----------------------------------+-' |
| '-(integer-+---+-+-------------+-)-' |
| +-K-+ +-CODEUNITS16-+ |
| +-M-+ '-CODEUNITS32-' |
| '-G-' |
| .-(1)-------. |
+-+-+-+-NCHAR-------------------+--+-----------+------+-------+------------------------+
| | | '-NATIONAL--+-CHAR------+-' '-(integer)-' | | |
| | | '-CHARACTER-' | | |
| | '-+-NVARCHAR-------------------------+--(integer)-' | |
| | +-NCHAR VARYING--------------------+ | |
| | '-NATIONAL--+-CHAR------+--VARYING-' | |
| | '-CHARACTER-' | |
| | .-(1M)-------------. | |
| '-+-NCLOB---------------------------+--+------------------+-' |
| +-NCHAR LARGE OBJECT--------------+ '-(integer-+---+-)-' |
| '-NATIONAL CHARACTER LARGE OBJECT-' +-K-+ |
| +-M-+ |
| '-G-' |
| .-(1M)-------------. |
+-+-BLOB----------------+--+------------------+----------------------------------------+
| '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
+-+-DATE-------------------------+-----------------------------------------------------+
| +-TIME-------------------------+ |
| | .-(--6--)-------. | |
| '-TIMESTAMP--+---------------+-' |
| '-(--integer--)-' |
+-XML----------------------------------------------------------------------------------+
| .-SYSPROC.-. (5) (6) |
'-+----------+--DB2SECURITYLABEL-------------------------------------------------------'
column-options
.--------------------------------------------------------------------------------------------------.
V |
|----+----------------------------------------------------------------------------------------------+-+--|
+-NOT NULL-------------------------------------------------------------------------------------+
| (7) |
+-| lob-options |------------------------------------------------------------------------------+
| (8) |
+-SCOPE--+-typed-table-name-+------------------------------------------------------------------+
| '-typed-view-name--' |
+-+-----------------------------+--+-+-PRIMARY KEY-+--------------+--| constraint-attributes |-+
| '-CONSTRAINT--constraint-name-' | '-UNIQUE------' | |
| +-| references-clause |--------+ |
| '-CHECK--(--check-condition--)-' |
| (9) |
+-----+-| default-clause |---+-----------------------------------------------------------------+
| '-| generated-clause |-' |
| (10) |
+-INLINE LENGTH--integer-----------------------------------------------------------------------+
+-COMPRESS SYSTEM DEFAULT----------------------------------------------------------------------+
| .-COLUMN-. |
+-+--------+--SECURED WITH--security-label-name------------------------------------------------+
| .-NOT HIDDEN--------. |
'-+-IMPLICITLY HIDDEN-+------------------------------------------------------------------------'
lob-options
.-LOGGED-----. .-NOT COMPACT-.
|--*--+------------+--*--+-------------+--*---------------------|
'-NOT LOGGED-' '-COMPACT-----'
references-clause
|--REFERENCES--+-table-name-+--+-----------------------+-------->
'-nickname---' | .-,-----------. |
| V | |
'-(----column-name-+--)-'
>--| rule-clause |--| constraint-attributes |-------------------|
rule-clause
.-ON DELETE NO ACTION-----. .-ON UPDATE NO ACTION-.
|--*--+-------------------------+--*--+---------------------+--*--|
'-ON DELETE--+-RESTRICT-+-' '-ON UPDATE RESTRICT--'
+-CASCADE--+
'-SET NULL-'
constraint-attributes
.-ENFORCED----------------------.
|--*--+-------------------------------+--*---------------------->
| .-TRUSTED-----. |
'-NOT ENFORCED--+-------------+-'
'-NOT TRUSTED-'
.-ENABLE QUERY OPTIMIZATION--.
>--+----------------------------+--*----------------------------|
'-DISABLE QUERY OPTIMIZATION-'
default-clause
.-WITH-.
|--+------+--DEFAULT--+--------------------+--------------------|
'-| default-values |-'
default-values
|--+-constant-------------------------------------------+-------|
+-datetime-special-register--------------------------+
+-user-special-register------------------------------+
+-CURRENT SCHEMA-------------------------------------+
+-CURRENT MEMBER-------------------------------------+
+-NULL-----------------------------------------------+
+-cast-function--(--+-constant------------------+--)-+
| +-datetime-special-register-+ |
| +-user-special-register-----+ |
| '-CURRENT SCHEMA------------' |
+-EMPTY_CLOB()---------------------------------------+
+-EMPTY_DBCLOB()-------------------------------------+
+-EMPTY_NCLOB()--------------------------------------+
'-EMPTY_BLOB()---------------------------------------'
generated-clause
.-ALWAYS-----.
|--+-GENERATED--+------------+--+-| identity-options |---------------+--+--|
| '-BY DEFAULT-' '-| as-row-change-timestamp-clause |-' |
| .-ALWAYS-. |
'-GENERATED--+--------+--+-| as-generated-expression-clause |------+-'
+-| as-row-transaction-timestamp-clause |-+
'-| as-row-transaction-start-id-clause |--'
identity-options
|--AS IDENTITY--+-----------------------------------------------------------+--|
| .---------------------------------------------. |
| V (11) .-1----------------. | |
'-(-----------+-START WITH--+-numeric-constant-+---+-+----)-'
| .-1----------------. |
+-INCREMENT BY--+-numeric-constant-+-+
| .-NO MINVALUE----------------. |
+-+-MINVALUE--numeric-constant-+-----+
| .-NO MAXVALUE----------------. |
+-+-MAXVALUE--numeric-constant-+-----+
| .-NO CYCLE-. |
+-+-CYCLE----+-----------------------+
| .-CACHE 20----------------. |
+-+-NO CACHE----------------+--------+
| '-CACHE--integer-constant-' |
| .-NO ORDER-. |
'-+-ORDER----+-----------------------'
as-row-change-timestamp-clause
(12)
|---------FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP--------|
as-generated-expression-clause
|--AS--(--generation-expression--)------------------------------|
as-row-transaction-timestamp-clause
(13)
|---------AS--ROW--+-BEGIN-+------------------------------------|
'-END---'
as-row-transaction-start-id-clause
(14)
|---------AS--TRANSACTION START ID------------------------------|
period-definition
|--PERIOD--+-SYSTEM_TIME---+--(--begin-column-name--,--end-column-name--)--|
'-BUSINESS_TIME-'
unique-constraint
|--+-----------------------------+--+-UNIQUE------+------------->
'-CONSTRAINT--constraint-name-' '-PRIMARY KEY-'
.-,---------------.
V |
>--(------column-name---+--+------------------------------------+--)-->
'-,--BUSINESS_TIME--WITHOUT OVERLAPS-'
>--| constraint-attributes |------------------------------------|
referential-constraint
|--+-----------------------------+--FOREIGN KEY----------------->
'-CONSTRAINT--constraint-name-'
.-,-----------.
V |
>--(----column-name-+--)--| references-clause |-----------------|
check-constraint
|--+-----------------------------+------------------------------>
'-CONSTRAINT--constraint-name-'
>--CHECK--(--| check-condition |--)----------------------------->
>--| constraint-attributes |------------------------------------|
check-condition
|--+-search-condition----------+--------------------------------|
'-| functional-dependency |-'
functional-dependency
|--+-column-name-----------+--DETERMINED BY--+-column-name-----------+--|
| .-,-----------. | | .-,-----------. |
| V | | | V | |
'-(----column-name-+--)-' '-(----column-name-+--)-'
typed-table-options
|--+---------------------------+--+------------------------+----|
+-HIERARCHY--hierarchy-name-+ '-| typed-element-list |-'
'-| under-clause |----------'
under-clause
|--UNDER--supertable-name--INHERIT SELECT PRIVILEGES------------|
typed-element-list
.-,-----------------------------.
V |
|--(----+-| OID-column-definition |-+-+--)----------------------|
+-| with-options |----------+
+-| unique-constraint |-----+
'-| check-constraint |------'
OID-column-definition
|--REF IS--OID-column-name--USER GENERATED----------------------|
with-options
|--column-name--WITH OPTIONS--| column-options |----------------|
as-result-table
|--+-----------------------+--AS--(--fullselect--)-------------->
| .-,-----------. |
| V | |
'-(----column-name-+--)-'
>--WITH NO DATA-------------------------------------------------|
materialized-query-definition
|--+-----------------------+--AS--(--fullselect--)-------------->
| .-,-----------. |
| V | |
'-(----column-name-+--)-'
>--| refreshable-table-options |--------------------------------|
copy-options
|--*--+-------------------------------------+--*---------------->
| .-COLUMN-. |
'-+-INCLUDING-+--+--------+--DEFAULTS-'
'-EXCLUDING-'
.-COLUMN ATTRIBUTES-.
.-EXCLUDING IDENTITY--+-------------------+-.
>--+-------------------------------------------+--*-------------|
| .-COLUMN ATTRIBUTES-. |
'-INCLUDING IDENTITY--+-------------------+-'
refreshable-table-options
|--DATA INITIALLY DEFERRED--REFRESH--+-DEFERRED--+--*----------->
'-IMMEDIATE-'
.-ENABLE QUERY OPTIMIZATION--.
>--+----------------------------+--*---------------------------->
'-DISABLE QUERY OPTIMIZATION-'
>--+-----------------------------------+--*---------------------|
'-MAINTAINED BY--+-SYSTEM---------+-'
+-USER-----------+
+-REPLICATION----+
'-FEDERATED_TOOL-'
staging-table-definition
|--+-------------------------------+--FOR--table-name2---------->
| .-,-------------------. |
| V | |
'-(----staging-column-name-+--)-'
>--PROPAGATE IMMEDIATE------------------------------------------|
dimensions-clause
.-,-------------------------.
.-DIMENSIONS-. V |
|--+------------+--(----+-column-name-----------+-+--)----------|
| .-,-----------. |
| V | |
'-(----column-name-+--)-'
sequence-key-spec
.-,-----------------------------------------------------------------------.
V .-AT-. |
|--(----column-name--+------------------------------+--ENDING--+----+--constant-+--)-->
| .-FROM-. |
'-STARTING--+------+--constant-'
>--+-ALLOW OVERFLOW----+--+------------------+------------------|
'-DISALLOW OVERFLOW-' '-PCTFREE--integer-'
tablespace-clauses
|--+---------------------------------------+--*----------------->
| .-,---------------. |
| V | .-CYCLE----. |
'-IN----tablespace-name-+--+----------+-'
'-NO CYCLE-'
>--+--------------------------------+--------------------------->
| (15) |
'-INDEX IN--tablespace-name------'
>--+------------------------------+-----------------------------|
| .-,---------------. |
| V | |
'-LONG IN----tablespace-name-+-'
distribution-clause
.-,-----------.
.-HASH-. V |
|--DISTRIBUTE BY--+-+------+--(----column-name-+--)-+-----------|
'-REPLICATION---------------------'
partitioning-clause
.-RANGE-.
|--PARTITION BY--+-------+--| range-partition-spec |------------|
range-partition-spec
.-,------------------------.
V |
|--(----| partition-expression |-+--)--------------------------->
.-,---------------------.
V |
>--(----| partition-element |-+--)------------------------------|
partition-expression
.-NULLS LAST--.
|--column-name--+-------------+---------------------------------|
'-NULLS FIRST-'
partition-element
|--+-+---------------------------+--| boundary-spec |--| partition-tablespace-options |-+--|
| '-PARTITION--partition-name-' |
'-| boundary-spec |--EVERY--+-(--constant--+-------------------------+--)-+----------'
| | (16) | |
| '-| duration-label |------' |
'-constant--+-------------------------+-------'
| (16) |
'-| duration-label |------'
boundary-spec
(17)
|--+-| starting-clause |-------| ending-clause |-+--------------|
'-| ending-clause |---------------------------'
starting-clause
.-,------------.
.-FROM-. V |
|--STARTING--+------+--+-(----+-constant-+-+--)-+--------------->
| +-MINVALUE-+ |
| '-MAXVALUE-' |
'-+-constant-+-----------'
+-MINVALUE-+
'-MAXVALUE-'
.-INCLUSIVE-.
>--+-----------+------------------------------------------------|
'-EXCLUSIVE-'
ending-clause
.-,------------.
.-AT-. V | .-INCLUSIVE-.
|--ENDING--+----+--+-(----+-constant-+-+--)-+--+-----------+----|
| +-MINVALUE-+ | '-EXCLUSIVE-'
| '-MAXVALUE-' |
'-+-constant-+-----------'
+-MINVALUE-+
'-MAXVALUE-'
partition-tablespace-options
|--+---------------------+--+---------------------------+------->
'-IN--tablespace-name-' '-INDEX IN--tablespace-name-'
>--+--------------------------+---------------------------------|
'-LONG IN--tablespace-name-'
duration-label
|--+-YEAR---------+---------------------------------------------|
+-YEARS--------+
+-MONTH--------+
+-MONTHS-------+
+-DAY----------+
+-DAYS---------+
+-HOUR---------+
+-HOURS--------+
+-MINUTE-------+
+-MINUTES------+
+-SECOND-------+
+-SECONDS------+
+-MICROSECOND--+
'-MICROSECONDS-'
#top TABLE CREATE LIKE¶
Zobacz także TABLE CREATE LIKE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE CREATE AS¶
Zobacz także TABLE CREATE AS dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
Nie jest to wierna kopia struktury originalnej tabeli !!!
-- comment
#top TABLE CREATE with PRIMARY KEY¶
Zobacz także TABLE CREATE with PRIMARY KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE CREATE with auto_increment¶
Zobacz także TABLE CREATE with auto_increment dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE CREATE with UNIQUE KEY (singlecolumn)¶
Zobacz także TABLE CREATE with UNIQUE KEY (singlecolumn) dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE CREATE with UNIQUE KEY (multicolumn)¶
Zobacz także TABLE CREATE UNIQUE KEY (multicolumn) dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE CREATE with FULLTEXT KEY¶
Zobacz także TABLE CREATE with FULLTEXT KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE CREATE with FOREIGN KEY¶
Zobacz także TABLE CREATE with FOREIGN KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE CREATE with INDEX¶
Zobacz także TABLE CREATE with INDEX dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE CREATE with partitioned¶
Zobacz także TABLE CREATE with partitioned dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE CREATE with COLLATE without UNIQUE¶
Zobacz także TABLE CREATE with COLLATE without UNIQUE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE CREATE with COLLATE with UNIQUE¶
Zobacz także TABLE CREATE with COLLATE with UNIQUE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE ALTER¶
Zobacz także TABLE ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html
Składnia:
>>-ALTER TABLE--table-name-------------------------------------->
.-----------------------------------------------------------------------------.
V (1) .-COLUMN-. |
>--+-----------+-ADD--+-+--------+--| column-definition |-+--------------------------+-+-----+-><
| | +-| unique-constraint |-------------+ | |
| | +-| referential-constraint |--------+ | |
| | +-| check-constraint |--------------+ | |
| | +-| distribution-clause |-----------+ | |
| | '-RESTRICT ON DROP------------------' | |
| | .-MATERIALIZED-. | |
| | .-+--------------+--QUERY-. | |
| +-ADD--+-------------------------+--| materialized-query-definition |-+ |
| +-ALTER--+-FOREIGN KEY-+--constraint-name--| constraint-alteration |--+ |
| | '-CHECK-------' | |
| | .-COLUMN-. | |
| +-ALTER--+--------+--| column-alteration |----------------------------+ |
| | (2) | |
| +-+-ACTIVATE---+--ROW ACCESS CONTROL----------------------------------+ |
| | '-DEACTIVATE-' | |
| | (3) | |
| +-+-ACTIVATE---+--COLUMN ACCESS CONTROL-------------------------------+ |
| | '-DEACTIVATE-' | |
| +-RENAME COLUMN--source-column-name--TO--target-column-name-----------+ |
| +-DROP--+-PRIMARY KEY---------------------------+---------------------+ |
| | +-+-FOREIGN KEY-+--constraint-name------+ | |
| | | +-UNIQUE------+ | | |
| | | +-CHECK-------+ | | |
| | | '-CONSTRAINT--' | | |
| | | .-COLUMN-. .-CASCADE--. | | |
| | +-+--------+--column-name--+----------+-+ | |
| | | '-RESTRICT-' | | |
| | '-RESTRICT ON DROP----------------------' | |
| +-DROP DISTRIBUTION---------------------------------------------------+ |
| | .-MATERIALIZED-. | |
| +-DROP--+--------------+--QUERY---------------------------------------+ |
| +-ADD PERIOD--| period-definition |-----------------------------------+ |
| +-DROP PERIOD--period-name--------------------------------------------+ |
| +-DATA CAPTURE--+-NONE---------------------------------+--------------+ |
| | '-CHANGES--+-------------------------+-' | |
| | '-INCLUDE LONGVAR COLUMNS-' | |
| +-ACTIVATE NOT LOGGED INITIALLY--+------------------+-----------------+ |
| | '-WITH EMPTY TABLE-' | |
| +-PCTFREE--integer----------------------------------------------------+ |
| +-LOCKSIZE--+-ROW---------+-------------------------------------------+ |
| | +-BLOCKINSERT-+ | |
| | '-TABLE-------' | |
| +-APPEND--+-ON--+-----------------------------------------------------+ |
| | '-OFF-' | |
| | .-CARDINALITY-. | |
| +-+-VOLATILE-----+--+-------------+-----------------------------------+ |
| | '-NOT VOLATILE-' | |
| | .-ADAPTIVE-. | |
| +-COMPRESS--+-YES-+----------+-+--------------------------------------+ |
| | | '-STATIC---' | | |
| | '-NO---------------' | |
| +-+-ACTIVATE---+--VALUE COMPRESSION-----------------------------------+ |
| | '-DEACTIVATE-' | |
| '-LOG INDEX BUILD--+-NULL-+-------------------------------------------' |
| +-OFF--+ |
| '-ON---' |
+-ADD PARTITION--| add-partition |--------------------------------------------------------+
+-ATTACH PARTITION--| attach-partition |--------------------------------------------------+
+-DETACH PARTITION--partition-name--INTO--table-name1-------------------------------------+
+-ADD SECURITY POLICY--policy-name--------------------------------------------------------+
+-DROP SECURITY POLICY--------------------------------------------------------------------+
+-ADD VERSIONING--USE HISTORY TABLE--history-table-name-----------------------------------+
'-DROP VERSIONING-------------------------------------------------------------------------'
add-partition
|--+----------------+--| boundary-spec |--+---------------------+-->
'-partition-name-' '-IN--tablespace-name-'
>--+---------------------------------------------------------+--|
'-INDEX IN--tablespace-name--+--------------------------+-'
'-LONG IN--tablespace-name-'
boundary-spec
|--+-| starting-clause |--| ending-clause |-+-------------------|
'-| ending-clause |----------------------'
starting-clause
.-,------------.
.-FROM-. V |
|--STARTING--+------+--+-(----+-constant-+-+--)-+--------------->
| +-MINVALUE-+ |
| '-MAXVALUE-' |
'-+-constant-+-----------'
+-MINVALUE-+
'-MAXVALUE-'
.-INCLUSIVE-.
>--+-----------+------------------------------------------------|
'-EXCLUSIVE-'
ending-clause
.-,------------.
.-AT-. V | .-INCLUSIVE-.
|--ENDING--+----+--+-(----+-constant-+-+--)-+--+-----------+----|
| +-MINVALUE-+ | '-EXCLUSIVE-'
| '-MAXVALUE-' |
'-+-constant-+-----------'
+-MINVALUE-+
'-MAXVALUE-'
attach-partition
|--+----------------+--| boundary-spec |--FROM--table-name------>
'-partition-name-'
.-BUILD MISSING INDEXES----.
>--+--------------------------+---------------------------------|
'-REQUIRE MATCHING INDEXES-'
column-definition
|--column-name--+-------------------+--+--------------------+---|
| (4) | '-| column-options |-'
'-| data-type |-----'
column-options
.--------------------------------------------------------------------------------------------------.
V |
|----+----------------------------------------------------------------------------------------------+-+--|
+-NOT NULL-------------------------------------------------------------------------------------+
| (5) |
+-| lob-options |------------------------------------------------------------------------------+
| (6) |
+-SCOPE--+-typed-table-name2-+-----------------------------------------------------------------+
| '-typed-view-name2--' |
+-+-----------------------------+--+-+-UNIQUE------+--------------+--| constraint-attributes |-+
| '-CONSTRAINT--constraint-name-' | '-PRIMARY KEY-' | |
| +-| references-clause |--------+ |
| '-CHECK--(--check-condition--)-' |
| (7) |
+-----+-| default-clause |---+-----------------------------------------------------------------+
| '-| generated-clause |-' |
+-COMPRESS SYSTEM DEFAULT----------------------------------------------------------------------+
| .-COLUMN-. |
+-+--------+--SECURED WITH--security-label-name------------------------------------------------+
| .-NOT HIDDEN--------. |
'-+-IMPLICITLY HIDDEN-+------------------------------------------------------------------------'
lob-options
.-LOGGED-----. .-NOT COMPACT-.
|--*--+------------+--*--+-------------+--*---------------------|
'-NOT LOGGED-' '-COMPACT-----'
references-clause
|--REFERENCES--+-table-name-+--+-----------------------+-------->
'-nickname---' | .-,-----------. |
| V | |
'-(----column-name-+--)-'
>--| rule-clause |--| constraint-attributes |-------------------|
rule-clause
.-ON DELETE NO ACTION-----. .-ON UPDATE NO ACTION-.
|--*--+-------------------------+--*--+---------------------+--*--|
'-ON DELETE--+-RESTRICT-+-' '-ON UPDATE RESTRICT--'
+-CASCADE--+
'-SET NULL-'
constraint-attributes
.-ENFORCED----------------------.
|--*--+-------------------------------+--*---------------------->
| .-TRUSTED-----. |
'-NOT ENFORCED--+-------------+-'
'-NOT TRUSTED-'
.-ENABLE QUERY OPTIMIZATION--.
>--+----------------------------+--*----------------------------|
'-DISABLE QUERY OPTIMIZATION-'
default-clause
.-WITH-.
|--+------+--DEFAULT--+----------------------------------------------------+--|
+-constant-------------------------------------------+
+-datetime-special-register--------------------------+
+-user-special-register------------------------------+
+-CURRENT SCHEMA-------------------------------------+
+-CURRENT MEMBER-------------------------------------+
+-NULL-----------------------------------------------+
+-cast-function--(--+-constant------------------+--)-+
| +-datetime-special-register-+ |
| +-user-special-register-----+ |
| '-CURRENT SCHEMA------------' |
+-EMPTY_CLOB()---------------------------------------+
+-EMPTY_DBCLOB()-------------------------------------+
'-EMPTY_BLOB()---------------------------------------'
generated-clause
.-ALWAYS-----.
|--+-GENERATED--+------------+--| as-row-change-timestamp-clause |------+--|
| '-BY DEFAULT-' |
| .-ALWAYS-. |
'-GENERATED--+--------+--+-| as-generated-expression-clause |------+-'
+-| as-row-transaction-timestamp-clause |-+
'-| as-row-transaction-start-id-clause |--'
as-row-change-timestamp-clause
(8)
|--------FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP---------|
as-generated-expression-clause
|--AS--(--generation-expression--)------------------------------|
as-row-transaction-timestamp-clause
|--AS--ROW--+-BEGIN-+-------------------------------------------|
'-END---'
as-row-transaction-start-id-clause
|--AS--TRANSACTION START ID-------------------------------------|
unique-constraint
|--+-----------------------------+--+-UNIQUE------+------------->
'-CONSTRAINT--constraint-name-' '-PRIMARY KEY-'
.-,---------------.
V |
>--(------column-name---+--+------------------------------------+--)-->
'-,--BUSINESS_TIME--WITHOUT OVERLAPS-'
>--| constraint-attributes |------------------------------------|
referential-constraint
|--+-----------------------------+------------------------------>
'-CONSTRAINT--constraint-name-'
.-,-----------.
V |
>--FOREIGN KEY--(----column-name-+--)--| references-clause |----|
check-constraint
|--+-----------------------------+------------------------------>
'-CONSTRAINT--constraint-name-'
>--CHECK--(--| check-condition |--)----------------------------->
>--| constraint-attributes |------------------------------------|
check-condition
|--+-search-condition----------+--------------------------------|
'-| functional-dependency |-'
functional-dependency
|--+-column-name-----------+--DETERMINED BY--+-column-name-----------+--|
| .-,-----------. | | .-,-----------. |
| V | | | V | |
'-(----column-name-+--)-' '-(----column-name-+--)-'
distribution-clause
.-,-----------.
.-HASH-. V |
|--DISTRIBUTE BY--+------+--(----column-name-+--)---------------|
materialized-query-definition
|--(--fullselect--)--| refreshable-table-options |--------------|
refreshable-table-options
|--*--DATA INITIALLY DEFERRED--*--REFRESH--+-DEFERRED--+--*----->
'-IMMEDIATE-'
.-ENABLE QUERY OPTIMIZATION--.
>--+----------------------------+--*---------------------------->
'-DISABLE QUERY OPTIMIZATION-'
>--+-----------------------------------+--*---------------------|
'-MAINTAINED BY--+-SYSTEM---------+-'
+-USER-----------+
+-REPLICATION----+
'-FEDERATED_TOOL-'
constraint-alteration
.-------------------------------------------.
V (9) |
|--------+-+-ENABLE--+--QUERY OPTIMIZATION---+-+----------------|
| '-DISABLE-' |
'-+-ENFORCED----------------------+-'
| .-TRUSTED-----. |
'-NOT ENFORCED--+-------------+-'
'-NOT TRUSTED-'
column-alteration
|--column-name--+-SET--+-DATA TYPE--| altered-data-type |---------------+----------------+--|
| +-NOT NULL---------------------------------------+ |
| +-INLINE LENGTH--integer-------------------------+ |
| +-| default-clause |-----------------------------+ |
| +-EXPRESSION--| as-generated-expression-clause |-+ |
| '-+-NOT HIDDEN--------+--------------------------' |
| '-IMPLICITLY HIDDEN-' |
+-SET--| generation-alteration |-----------------------------------------+
+-+--------------------------------+--| identity-alteration |------------+
| '-SET--| generation-alteration |-' |
+-SET--| generation-attribute |--| as-identity-clause |------------------+
| .-ALWAYS-. |
+-SET GENERATED--+--------+--+-| as-generated-expression-clause |------+-+
| +-| as-row-transacton-start-id-clause |---+ |
| '-| as-row-transaction-timestamp-clause |-' |
+-DROP--+-DEFAULT---+----------------------------------------------------+
| +-GENERATED-+ |
| '-NOT NULL--' |
+-ADD SCOPE--+-typed-table-name-+----------------------------------------+
| '-typed-view-name--' |
+-COMPRESS--+-SYSTEM DEFAULT-+-------------------------------------------+
| '-OFF------------' |
+-SECURED WITH--security-label-name--------------------------------------+
'-DROP COLUMN SECURITY---------------------------------------------------'
altered-data-type
|--+-| built-in-type |-------+----------------------------------|
| (10) |
'-distinct-type-name------'
built-in-type
|--+-+-+-INTEGER-+-+-----------------------------------------------------------------------+--|
| | '-INT-----' | |
| '-BIGINT------' |
| .-(5,0)-------------------. |
+-+-+-DECIMAL-+-+--+-------------------------+------------------------------------------+
| | '-DEC-----' | | .-,0-------. | |
| '-+-NUMERIC-+-' '-(integer-+----------+-)-' |
| '-NUM-----' '-,integer-' |
| .-(53)------. |
+-+-FLOAT--+-----------+--+-------------------------------------------------------------+
| | '-(integer)-' | |
| +-REAL------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+-' |
| .-(34)-. |
+-DECFLOAT--+------+--------------------------------------------------------------------+
| '-(16)-' |
| .-(1)------------------------. |
+-+-+-+-CHARACTER-+--+----------------------------+----------+--+-------------------+-+-+
| | | '-CHAR------' '-(integer-+-------------+-)-' | | (11) | | |
| | | +-OCTETS------+ | '-FOR BIT DATA------' | |
| | | '-CODEUNITS32-' | | |
| | '-+-VARCHAR----------------+--(integer-+-------------+-)-' | |
| | '-+-CHARACTER-+--VARYING-' +-OCTETS------+ | |
| | '-CHAR------' '-CODEUNITS32-' | |
| | .-(1M)-----------------------------. | |
| '-+-CLOB------------------------+--+----------------------------------+-------------' |
| '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+-------------+-)-' |
| '-CHAR------' +-K-+ +-OCTETS------+ |
| +-M-+ '-CODEUNITS32-' |
| '-G-' |
| .-(1)------------------------. |
+-+-GRAPHIC--+----------------------------+------+--------------------------------------+
| | '-(integer-+-------------+-)-' | |
| | +-CODEUNITS16-+ | |
| | '-CODEUNITS32-' | |
| +-VARGRAPHIC--(integer-+-------------+-)-------+ |
| | +-CODEUNITS16-+ | |
| | '-CODEUNITS32-' | |
| | .-(1M)-----------------------------. | |
| '-DBCLOB--+----------------------------------+-' |
| '-(integer-+---+-+-------------+-)-' |
| +-K-+ +-CODEUNITS16-+ |
| +-M-+ '-CODEUNITS32-' |
| '-G-' |
| .-(1M)-------------. |
'-+-BLOB----------------+--+------------------+-----------------------------------------'
'-BINARY LARGE OBJECT-' '-(integer-+---+-)-'
+-K-+
+-M-+
'-G-'
as-identity-clause
|--AS IDENTITY-------------------------------------------------->
>--+------------------------------------------------------+-----|
| .--------------------------------------------. |
| V (9) .-1----------------. | |
'-(--------+-START WITH--+-numeric-constant-+---+-+--)-'
| .-1----------------. |
+-INCREMENT BY--+-numeric-constant-+-+
| .-NO MINVALUE----------------. |
+-+-MINVALUE--numeric-constant-+-----+
| .-NO MAXVALUE----------------. |
+-+-MAXVALUE--numeric-constant-+-----+
| .-NO CYCLE-. |
+-+-CYCLE----+-----------------------+
| .-CACHE 20----------------. |
'-+-NO CACHE----------------+--------'
'-CACHE--integer-constant-'
generation-alteration
|--SET GENERATED--+-ALWAYS-----+--------------------------------|
'-BY DEFAULT-'
identity-alteration
.---------------------------------------------.
V (9) |
|--------+-SET INCREMENT BY--numeric-constant--+-+--------------|
+-SET--+-NO MINVALUE----------------+-+
| '-MINVALUE--numeric-constant-' |
+-SET--+-NO MAXVALUE----------------+-+
| '-MAXVALUE--numeric-constant-' |
+-SET--+-NO CYCLE-+-------------------+
| '-CYCLE----' |
+-SET--+-NO CACHE----------------+----+
| '-CACHE--integer-constant-' |
+-SET--+-NO ORDER-+-------------------+
| '-ORDER----' |
'-RESTART--+------------------------+-'
'-WITH--numeric-constant-'
generation-attribute
.-ALWAYS-----.
|--GENERATED--+------------+------------------------------------|
'-BY DEFAULT-'
period-definition
|--+-SYSTEM_TIME---+--(--begin-column-name--,--end-column-name--)--|
'-BUSINESS_TIME-'
#top TABLE ALTER with PRIMARY KEY¶
Zobacz także TABLE ALTER with PRIMARY KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE ALTER with auto increment¶
Zobacz także TABLE ALTER with auto_increment dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE ALTER with UNIQUE KEY (singlecolumn)¶
Zobacz także TABLE ALTER with UNIQUE KEY (singlecolumn) dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE ALTER with UNIQUE KEY (multicolumn)¶
Zobacz także TABLE ALTER UNIQUE KEY (multicolumn) dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE ALTER with FULLTEXT KEY¶
Zobacz także TABLE ALTER with FULLTEXT KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE ALTER with FOREIGN KEY¶
Zobacz także TABLE ALTER with FOREIGN KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE ALTER with INDEX¶
Zobacz także TABLE ALTER with INDEX dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE ALTER with partitioned¶
Zobacz także TABLE ALTER with partitioned dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE ALTER with COLLATE without UNIQUE¶
Zobacz także TABLE ALTER with COLLATE without UNIQUE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE ALTER with COLLATE with UNIQUE¶
Zobacz także TABLE ALTER with COLLATE with UNIQUE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Example:
-- comment
#top TABLE RENAME¶
Zobacz także TABLE RENAME dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000980.html
Składnia:
.-TABLE-.
>>-RENAME--+-+-------+--source-table-name-+--TO--target-identifier-><
'-INDEX--source-index-name-----'
Example: rename table
-- comment
#top TABLE TRUNCATE¶
Zobacz także TABLE TRUNCATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0053474.html
TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.
Składnia:
.-TABLE-. .-DROP STORAGE--.
>>-TRUNCATE--+-------+--table-name--+---------------+----------->
'-REUSE STORAGE-'
.-IGNORE DELETE TRIGGERS--------.
>--+-------------------------------+---------------------------->
'-RESTRICT WHEN DELETE TRIGGERS-'
.-CONTINUE IDENTITY-.
>--+-------------------+--IMMEDIATE----------------------------><
Example:
-- comment
#top TABLE DROP¶
Zobacz także TABLE DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000945.html
Składnia:
>>-DROP---------------------------------------------------------> >--+-| alias-designator |------------------------------------------+->< +-AUDIT POLICY--policy-name-------------------------------------+ +-BUFFERPOOL--bufferpool-name-----------------------------------+ +-DATABASE PARTITION GROUP--db-partition-group-name-------------+ +-EVENT MONITOR--event-monitor-name-----------------------------+ +-| function-designator |--+----------+-------------------------+ | '-RESTRICT-' | +-FUNCTION MAPPING--function-mapping-name-----------------------+ +-HISTOGRAM TEMPLATE--template-name-----------------------------+ | (1) | +-INDEX--index-name---------------------------------------------+ +-INDEX EXTENSION--index-extension-name--RESTRICT---------------+ +-MASK--mask-name-----------------------------------------------+ +-| method-designator |--+----------+---------------------------+ | '-RESTRICT-' | +-MODULE--module-name-------------------------------------------+ +-NICKNAME--nickname--------------------------------------------+ +-PACKAGE--package-name--+-------------------------+------------+ | | .-VERSION-. | | | '-+---------+--version-id-' | +-PERMISSION--permission-name-----------------------------------+ +-| procedure-designator |--+----------+------------------------+ | '-RESTRICT-' | +-ROLE--role-name-----------------------------------------------+ +-SCHEMA--schema-name--RESTRICT---------------------------------+ | .-RESTRICT-. | +-SECURITY LABEL--security-label-name--+----------+-------------+ | .-RESTRICT-. | +-SECURITY LABEL COMPONENT--sec-label-comp-name--+----------+---+ | .-RESTRICT-. | +-SECURITY POLICY--security-policy-name--+----------+-----------+ +-SEQUENCE--sequence-name--+----------+-------------------------+ | '-RESTRICT-' | +-SERVER--server-name-------------------------------------------+ | .-RESTRICT-. | +-| service-class-designator |--+----------+--------------------+ | .-RESTRICT-. | +-STOGROUP--storagegroup-name--+----------+---------------------+ +-TABLE--table-name---------------------------------------------+ +-TABLE HIERARCHY--root-table-name------------------------------+ | .-,---------------. | | V | | +-+-TABLESPACE--+----tablespace-name-+--------------------------+ | '-TABLESPACES-' | +-+-TRANSFORM--+--+-ALL--------+--FOR--type-name----------------+ | '-TRANSFORMS-' '-group-name-' | +-THRESHOLD--threshold-name-------------------------------------+ +-TRIGGER--trigger-name-----------------------------------------+ +-TRUSTED CONTEXT--context-name---------------------------------+ +-TYPE--type-name--+----------+---------------------------------+ | '-RESTRICT-' | +-TYPE MAPPING--type-mapping-name-------------------------------+ +-USAGE LIST--usage-list-name-----------------------------------+ +-USER MAPPING FOR--+-authorization-name-+--SERVER--server-name-+ | '-USER---------------' | +-VARIABLE--variable-name--+----------+-------------------------+ | '-RESTRICT-' | +-VIEW--view-name-----------------------------------------------+ +-VIEW HIERARCHY--root-view-name--------------------------------+ +-WORK ACTION SET--work-action-set-name-------------------------+ +-WORK CLASS SET--work-class-set-name---------------------------+ +-WORKLOAD--workload-name---------------------------------------+ +-WRAPPER--wrapper-name-----------------------------------------+ '-XSROBJECT--xsrobject-name-------------------------------------'
Example:
-- comment
#top sequences¶
#top SEQUENCE CREATE¶
Zobacz także SEQUENCE CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0004201.html
Składnia:
>>-CREATE--+------------+--SEQUENCE--sequence-name--*----------->
'-OR REPLACE-'
.-AS INTEGER--------.
>--+-------------------+--*--+------------------------------+--->
'-AS--| data-type |-' '-START WITH--numeric-constant-'
.-INCREMENT BY 1-----------------.
>--*--+--------------------------------+--*--------------------->
'-INCREMENT BY--numeric-constant-'
.-NO MINVALUE----------------.
>--+----------------------------+--*---------------------------->
'-MINVALUE--numeric-constant-'
.-NO MAXVALUE----------------. .-NO CYCLE-.
>--+----------------------------+--*--+----------+--*----------->
'-MAXVALUE--numeric-constant-' '-CYCLE----'
.-CACHE 20----------------. .-NO ORDER-.
>--+-------------------------+--*--+----------+--*-------------><
+-CACHE--integer-constant-+ '-ORDER----'
'-NO CACHE----------------'
data-type
|--+-| built-in-type |------+-----------------------------------|
| (1) |
'-distinct-type-name-----'
built-in-type
|--+-+-SMALLINT----+------------------------------+-------------|
| +-+-INTEGER-+-+ |
| | '-INT-----' | |
| '-BIGINT------' |
| .-(5,0)-------------------. |
'-+-+-DECIMAL-+-+--+-------------------------+-'
| '-DEC-----' | | .-,0-------. |
'-+-NUMERIC-+-' '-(integer-+----------+-)-'
'-NUM-----' '-,integer-'
Example:
-- comment
#top SEQUENCE ALTER¶
Zobacz także SEQUENCE ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0004200.html
Składnia:
>>-ALTER SEQUENCE--sequence-name-------------------------------->
.-----------------------------------------------.
V (1) |
>----------+-RESTART--+------------------------+-+-+-----------><
| '-WITH--numeric-constant-' |
+-INCREMENT BY--numeric-constant------+
+-+-MINVALUE--numeric-constant-+------+
| '-NO MINVALUE----------------' |
+-+-MAXVALUE--numeric-constant-+------+
| '-NO MAXVALUE----------------' |
+-+-CYCLE----+------------------------+
| '-NO CYCLE-' |
+-+-CACHE--integer-constant-+---------+
| '-NO CACHE----------------' |
'-+-ORDER----+------------------------'
'-NO ORDER-'
Example:
-- comment
#top SEQUENCE DROP¶
Zobacz także SEQUENCE DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000945.html
Składnia:
>>-DROP---------------------------------------------------------> >--+-| alias-designator |------------------------------------------+->< +-AUDIT POLICY--policy-name-------------------------------------+ +-BUFFERPOOL--bufferpool-name-----------------------------------+ +-DATABASE PARTITION GROUP--db-partition-group-name-------------+ +-EVENT MONITOR--event-monitor-name-----------------------------+ +-| function-designator |--+----------+-------------------------+ | '-RESTRICT-' | +-FUNCTION MAPPING--function-mapping-name-----------------------+ +-HISTOGRAM TEMPLATE--template-name-----------------------------+ | (1) | +-INDEX--index-name---------------------------------------------+ +-INDEX EXTENSION--index-extension-name--RESTRICT---------------+ +-MASK--mask-name-----------------------------------------------+ +-| method-designator |--+----------+---------------------------+ | '-RESTRICT-' | +-MODULE--module-name-------------------------------------------+ +-NICKNAME--nickname--------------------------------------------+ +-PACKAGE--package-name--+-------------------------+------------+ | | .-VERSION-. | | | '-+---------+--version-id-' | +-PERMISSION--permission-name-----------------------------------+ +-| procedure-designator |--+----------+------------------------+ | '-RESTRICT-' | +-ROLE--role-name-----------------------------------------------+ +-SCHEMA--schema-name--RESTRICT---------------------------------+ | .-RESTRICT-. | +-SECURITY LABEL--security-label-name--+----------+-------------+ | .-RESTRICT-. | +-SECURITY LABEL COMPONENT--sec-label-comp-name--+----------+---+ | .-RESTRICT-. | +-SECURITY POLICY--security-policy-name--+----------+-----------+ +-SEQUENCE--sequence-name--+----------+-------------------------+ | '-RESTRICT-' | +-SERVER--server-name-------------------------------------------+ | .-RESTRICT-. | +-| service-class-designator |--+----------+--------------------+ | .-RESTRICT-. | +-STOGROUP--storagegroup-name--+----------+---------------------+ +-TABLE--table-name---------------------------------------------+ +-TABLE HIERARCHY--root-table-name------------------------------+ | .-,---------------. | | V | | +-+-TABLESPACE--+----tablespace-name-+--------------------------+ | '-TABLESPACES-' | +-+-TRANSFORM--+--+-ALL--------+--FOR--type-name----------------+ | '-TRANSFORMS-' '-group-name-' | +-THRESHOLD--threshold-name-------------------------------------+ +-TRIGGER--trigger-name-----------------------------------------+ +-TRUSTED CONTEXT--context-name---------------------------------+ +-TYPE--type-name--+----------+---------------------------------+ | '-RESTRICT-' | +-TYPE MAPPING--type-mapping-name-------------------------------+ +-USAGE LIST--usage-list-name-----------------------------------+ +-USER MAPPING FOR--+-authorization-name-+--SERVER--server-name-+ | '-USER---------------' | +-VARIABLE--variable-name--+----------+-------------------------+ | '-RESTRICT-' | +-VIEW--view-name-----------------------------------------------+ +-VIEW HIERARCHY--root-view-name--------------------------------+ +-WORK ACTION SET--work-action-set-name-------------------------+ +-WORK CLASS SET--work-class-set-name---------------------------+ +-WORKLOAD--workload-name---------------------------------------+ +-WRAPPER--wrapper-name-----------------------------------------+ '-XSROBJECT--xsrobject-name-------------------------------------'
Example:
-- comment
#top indexes¶
#top INDEX CREATE¶
Zobacz także INDEX CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000919.html
Składnia:
>>-CREATE--+--------+--INDEX--index-name------------------------>
'-UNIQUE-'
.-,--------------------------------------.
(1) V .-ASC----. |
>--ON--+-table-name-----+--(----+-+-column-name----+--+--------+-----+-+--)-->
| (2) | | '-key-expression-' +-DESC---+ |
'-nickname-------' | '-RANDOM-' |
| (3) |
'-BUSINESS_TIME WITHOUT OVERLAPS-----'
>--+-----------------+--*--+-------------------------+--*------->
+-PARTITIONED-----+ | (4) |
'-NOT PARTITIONED-' '-IN--tablespace-name-----'
>--+--------------------+--*------------------------------------>
'-SPECIFICATION ONLY-'
>--+-------------------------------------------+--*------------->
| .-,------------------. |
| (5) V | |
'-INCLUDE------(----+-column-name----+-+--)-'
'-key-expression-'
>--+-----------------------------------------------------------------------+--*-->
| (6) |
+-| xml-index-specification |-------------------------------------------+
+-CLUSTER---------------------------------------------------------------+
'-EXTEND USING--index-extension-name--+-------------------------------+-'
| .-,-------------------. |
| V | |
'-(----constant-expression-+--)-'
.-PCTFREE 10-------.
>--+------------------+--*--+-------------------------+--*------>
'-PCTFREE--integer-' '-LEVEL2 PCTFREE--integer-'
.-ALLOW REVERSE SCANS----.
>--+---------------------+--*--+------------------------+--*---->
'-MINPCTUSED--integer-' '-DISALLOW REVERSE SCANS-'
.-PAGE SPLIT SYMMETRIC-.
>--+----------------------+--*---------------------------------->
'-PAGE SPLIT--+-HIGH-+-'
'-LOW--'
>--+--------------------------------------------------+--*------>
'-COLLECT--+-------------------------+--STATISTICS-'
| .-SAMPLED---. |
'-+-----------+--DETAILED-'
'-UNSAMPLED-'
.-INCLUDE NULL KEYS-.
>--+-------------------+--*--+-------------------+--*----------><
'-COMPRESS--+-NO--+-' '-EXCLUDE NULL KEYS-'
'-YES-'
Example: alter table add INDEX
-- comment
#top INDEX ALTER¶
Zobacz także INDEX ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0054932.html
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000980.html
Składnia:
>>-ALTER INDEX--index-name --COMPRESS--+-NO--+-----------------><
'-YES-'
Example: alter table add INDEX
-- comment
#top INDEX DROP¶
Zobacz także INDEX DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000945.html
Składnia:
>>-DROP---------------------------------------------------------> >--+-| alias-designator |------------------------------------------+->< +-AUDIT POLICY--policy-name-------------------------------------+ +-BUFFERPOOL--bufferpool-name-----------------------------------+ +-DATABASE PARTITION GROUP--db-partition-group-name-------------+ +-EVENT MONITOR--event-monitor-name-----------------------------+ +-| function-designator |--+----------+-------------------------+ | '-RESTRICT-' | +-FUNCTION MAPPING--function-mapping-name-----------------------+ +-HISTOGRAM TEMPLATE--template-name-----------------------------+ | (1) | +-INDEX--index-name---------------------------------------------+ +-INDEX EXTENSION--index-extension-name--RESTRICT---------------+ +-MASK--mask-name-----------------------------------------------+ +-| method-designator |--+----------+---------------------------+ | '-RESTRICT-' | +-MODULE--module-name-------------------------------------------+ +-NICKNAME--nickname--------------------------------------------+ +-PACKAGE--package-name--+-------------------------+------------+ | | .-VERSION-. | | | '-+---------+--version-id-' | +-PERMISSION--permission-name-----------------------------------+ +-| procedure-designator |--+----------+------------------------+ | '-RESTRICT-' | +-ROLE--role-name-----------------------------------------------+ +-SCHEMA--schema-name--RESTRICT---------------------------------+ | .-RESTRICT-. | +-SECURITY LABEL--security-label-name--+----------+-------------+ | .-RESTRICT-. | +-SECURITY LABEL COMPONENT--sec-label-comp-name--+----------+---+ | .-RESTRICT-. | +-SECURITY POLICY--security-policy-name--+----------+-----------+ +-SEQUENCE--sequence-name--+----------+-------------------------+ | '-RESTRICT-' | +-SERVER--server-name-------------------------------------------+ | .-RESTRICT-. | +-| service-class-designator |--+----------+--------------------+ | .-RESTRICT-. | +-STOGROUP--storagegroup-name--+----------+---------------------+ +-TABLE--table-name---------------------------------------------+ +-TABLE HIERARCHY--root-table-name------------------------------+ | .-,---------------. | | V | | +-+-TABLESPACE--+----tablespace-name-+--------------------------+ | '-TABLESPACES-' | +-+-TRANSFORM--+--+-ALL--------+--FOR--type-name----------------+ | '-TRANSFORMS-' '-group-name-' | +-THRESHOLD--threshold-name-------------------------------------+ +-TRIGGER--trigger-name-----------------------------------------+ +-TRUSTED CONTEXT--context-name---------------------------------+ +-TYPE--type-name--+----------+---------------------------------+ | '-RESTRICT-' | +-TYPE MAPPING--type-mapping-name-------------------------------+ +-USAGE LIST--usage-list-name-----------------------------------+ +-USER MAPPING FOR--+-authorization-name-+--SERVER--server-name-+ | '-USER---------------' | +-VARIABLE--variable-name--+----------+-------------------------+ | '-RESTRICT-' | +-VIEW--view-name-----------------------------------------------+ +-VIEW HIERARCHY--root-view-name--------------------------------+ +-WORK ACTION SET--work-action-set-name-------------------------+ +-WORK CLASS SET--work-class-set-name---------------------------+ +-WORKLOAD--workload-name---------------------------------------+ +-WRAPPER--wrapper-name-----------------------------------------+ '-XSROBJECT--xsrobject-name-------------------------------------'
Example: alter table drop INDEX
-- comment
#top functions¶
#top Functions and Operators¶
Zobacz także Functions and Operators dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/pl/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0011043.html
Example:
-- comment
#top DATA Manipulations¶
#top SELECT with LIMIT and OFFSET¶
Zobacz także SELECT with LIMIT and OFFSET dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/limit_offset
Składnia:
comment
Example:
-- comment
Po wykonaniu powyższych zapytań mających na celu utworzenie tabeli oraz dodaniu przykładowych danych można wykonać poniższe zapytania w celu sprawdzenia działania klauzul LIMIT przy wyświetlaniu danych z tabeli:
-- comment
Uzyskany rezultat powinien być analogiczny do poniższego:
comment
Po wykonaniu powyższych zapytań mających na celu utworzenie tabeli oraz dodaniu przykładowych danych można wykonać poniższe zapytania w celu sprawdzenia działania klauzul LIMIT oraz OFFSET przy wyświetlaniu danych z tabeli:
-- comment
Uzyskany rezultat powinien być analogiczny do poniższego:
comment
#top WHERE IN subselect with multiple columns¶
Zobacz także WHERE IN subselect with multiple columns dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
#top Deleting duplicates rows¶
Zobacz także Deleting duplicates rows dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
#top REPLACE INTO¶
Zobacz także REPLACE INTO dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
REPLACE INTO equivalent
----------------------------------------------
Use
"merge into":-------------------------------------
MERGE INTO table_to_upsert AS tab USING (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) -- more rows ) AS merge (C1, C2, C3) ON tab.key_to_match = merge.key_to_match WHEN MATCHED THEN UPDATE SET tab.C1 = merge.C1, tab.C2 = merge.C2, tab.C3 = merge.C3 WHEN NOT MATCHED THEN INSERT (C1, C2, C3) VALUES (merge.C1, merge.C2, merge.C3)
For example:
------------
MERGE INTO employees AS tab USING (VALUES (123456,'smith','bob') ) AS merge (id,last_name,first_name) ON tab.id = merge.id WHEN MATCHED THEN UPDATE SET tab.id = merge.id, tab.last_name = merge.last_name, tab.first_name = merge.first_name WHEN NOT MATCHED THEN INSERT (id,last_name,first_name) VALUES (merge.id, merge.last_name, merge.first_name)
#top GROUP with CONCAT¶
Zobacz także REPLACE INTO dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase
Dokumentacja IBM DB2:
Składnia:
-- comment
Zmodyfikowany ostatnio: 2017/11/01 23:56:59 (8 lat temu),
textsize: 139 kB,
htmlsize: 181 kB
Zapraszam do komentowania, zgłaszania sugestii, propozycji, własnych przykładów, ...
Dodaj komentarzKomentarze użytkowników