SQL語言備忘錄

Boolean Data
BOOLEAN   TURE/FALSE
Numeric Data
Exact Numbers
INTEGER (INT，INT4) 2,147,483,648 to 2,147,483,647
SMALLINT -32768 to 32767
BIGINT (INT8) 8位整數
Approximate Numbers
FLOAT[precision]
REAL
DOUBLE PRECISION
Formatted Numbers
DECIMAL(i,j), DEC(i,j) or NUMERIC(i,j)
 
CREATE TABLE TT (A DATE, B DATE, C INTERVAL); (creat 錯)
INSERT INTO TT(A, C) VALUES(NOW(),'1 DAY');
UPDATE TT SET B = A+ '2 MONTH'::INTERVAL;
 
‘2000-02-29’ 推薦   to_date('2000/02/29' , ‘yyyy/mm/dd' )
 
CREATE DOMAIN hour_dom AS INTEGER DEFAULT 0 CHECK (VALUE >= 0);
CREATE DOMAIN genderDom AS CHAR   DEFAULT ‘M’
       CHECK (VALUE IN (‘F’, ‘f’,‘M’ ,‘m’));  
 
CREATE TABLE BOOK
 (  Book_Id NUMERIC(6) NOT NULL,
       Edition NUMERIC(3) NOT NULL,
       CallNumber VARCHAR(8) NOT NULL UNQUE,( UNQUE 備選碼    唯一碼)
       Primary Key (Book_Id),
       FOREIGN KEY (LibCheck) REFERENCES LIBRARIAN(SSN)
 );
 
DROP TABLE MEMBER CASCADE;
DROP TABLE MEMBER RESTRICT;
 
ALTER TABLE tableName
[ADD [COLUMN] columnName dataType [NOT NULL] [UNIQUE]
[DEFAULT defaultOption] [CHECK (searchCondition)]]
[DROP [COLUMN] columnName [ RESTRICT | CASCADE ]]
[ADD [CONSTRAINT [constraintName]]tableConstraintDefinition]
[DROP CONSTRAINT constraintName [ RESTRICT | CASCADE ]]
[ALTER [COLUMN] SET DEFAULT defaultOption]
[ALTER [COLUMN] DROP DEFAULT]
 
第一：
ALTER TABLE products ADD COLUMN description text ;
ALTER TABLE products DROP COLUMN description RESTRICT;
ALTER TA