You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
107 lines
2.7 KiB
Plaintext
107 lines
2.7 KiB
Plaintext
--Create a schema
|
|
CREATE SCHEMA sch1
|
|
|
|
--Create a table specifying primary key
|
|
CREATE TABLE tbl1 (col1 CHAR(1) NOT NULL PRIMARY KEY)
|
|
CREATE TABLE tbl2 (col1 INT NOT NULL, col2 DATE NOT NULL, PRIMARY KEY (col1, col2))
|
|
|
|
--Create a table specifying tablespaces
|
|
CREATE TABLE tbl3 (col1 INT NOT NULL, col2 CHAR(1)) IN ts1 INDEX IN ts2
|
|
|
|
--Create a table specifying schema
|
|
CREATE TABLE sch1.tbl4 (col1 INT)
|
|
|
|
--Create a table with auto incremental column
|
|
CREATE TABLE tbl5 (col1 INT NOT NULL GENERATED AS IDENTITY)
|
|
|
|
--Create a table like another one
|
|
CREATE TABLE tbl6 LIKE tbl1 IN ts1 INDEX IN ts2
|
|
|
|
--Comment on table and column
|
|
COMMENT ON TABLE tbl1 IS 'Comment in table'
|
|
COMMENT ON COLUMN tbl1.col1 IS 'Description of the field'
|
|
|
|
--Declare a temporary table (session schema)
|
|
DECLARE GLOBAL TEMPORARY TABLE tmp1 (col1 INT, col2 DATE) ON COMMIT PRESERVE ROWS
|
|
|
|
--Create a global temporary tablespace
|
|
CREATE GLOBAL TEMPORARY TABLE tmp2 (col1 INT)
|
|
|
|
--Create an index
|
|
CREATE INDEX idx1 ON tbl2 (col2)
|
|
|
|
--Create a unique index
|
|
CREATE UNIQUE INDEX idx2 ON tbl5 (col1)
|
|
|
|
--Drop an index
|
|
DROP INDEX idx1
|
|
|
|
--Add a column (requires Reorg table)
|
|
ALTER TABLE tbl1 ADD COLUMN col3 timestamp
|
|
|
|
--Change nullability
|
|
ALTER TABLE tbl1 ALTER COLUMN col3 SET NOT NULL
|
|
|
|
--Drop nullability
|
|
ALTER TABLE tbl1 ALTER COLUMN col3 DROP NOT NULL
|
|
|
|
--Rename a column
|
|
ALTER TABLE tbl1 RENAME COLUMN col3 TO new3
|
|
|
|
--Drop column
|
|
ALTER TABLE tbl1 DROP COLUMN new3
|
|
|
|
--Create a primary key constraint
|
|
ALTER TABLE tbl5 ADD CONSTRAINT pkt5 PRIMARY KEY (col1)
|
|
|
|
--Drop primary key
|
|
ALTER TABLE tbl5 DROP PRIMARY KEY
|
|
|
|
--Add identity
|
|
ALTER TABLE tbl2 ALTER col1 SET GENERATED ALWAYS AS IDENTITY
|
|
|
|
--Restart identity
|
|
ALTER TABLE tbl2 ALTER col1 RESTART WITH 1
|
|
|
|
--Drop identity
|
|
ALTER TABLE tbl2 ALTER col1 DROP IDENTITY
|
|
|
|
--Create a foreign key
|
|
ALTER TABLE tbl5 ADD CONSTRAINT fkt5 FOREIGN KEY (col1) REFERENCES tbl11 (col1)
|
|
|
|
--Create a check constraint
|
|
ALTER TABLE tbl1 ADD CONSTRAINT chk CHECK (col1 in ('a', 'b', 'c'))
|
|
|
|
--Enforce a constraint
|
|
ALTER TABLE tbl1 ALTER CHECK chk ENFORCED
|
|
|
|
--Not enforce a constraint
|
|
ALTER TABLE tbl5 ALTER FOREIGN KEY fkt5 NOT ENFORCED
|
|
|
|
--Change the granularity of the locks
|
|
ALTER TABLE tbl1 LOCKSIZE TABLE
|
|
|
|
--Drop a table
|
|
DROP TABLE tbl1
|
|
|
|
--Rename a table
|
|
RENAME TABLE tbl2 TO table2
|
|
|
|
--Truncate a table
|
|
TRUNCATE TABLE tbl1 IMMEDIATE
|
|
|
|
--Create a sequence
|
|
CREATE SEQUENCE seq AS INTEGER
|
|
|
|
--Restart sequence
|
|
ALTER SEQUENCE seq RESTART WITH 15
|
|
|
|
--Create a stored procedure
|
|
CREATE OR REPLACE PROCEDURE prc1 (IN val INT, OUT ret DATE) SPECIFIC mypr BEGIN SET ret = (SELECT col2 FROM tbl2 WHERE col1 = val); END @
|
|
|
|
--Create a trigger
|
|
CREATE TRIGGER cp_val AFTER INSERT ON tbl1 REFERENCING NEW AS n FOR EACH ROW INSERT INTO tbl2 VALUES (n.col1, n.col2)
|
|
|
|
--Create a view
|
|
CREATE VIEW vw1 AS SELECT col2 FROM tbl1
|