SQL Syntax Notes#

Basic Select Statement#

SELECT * FROM table;

SELECT col1, col2 FROM table;

SELECT * FROM table WHERE col1 = 'ABC';

SELECT * FROM table WHERE col1 IS NULL;

SELECT * FROM table WHERE col1 = 'ABC' ORDER BY col1, col2;

SELECT * FROM table WHERE col1 <> 'ABC' ORDER BY col1 DESC;

SELECT DISTINCT col1, col2 FROM table;

SELECT COUNT(*) FROM table WHERE col1 = 'ABC';

SELECT col1, col2 FROM tbl1, tbl2 WHERE tbl1.col3 = tbl2.col3;

SELECT a.col1, b.col2 FROM tbl1 a, tbl2 b WHERE a.col3 = b.col3;

Changing Data#

UPDATE table SET field=value;    --  this changes every record!

UPDATE table SET field=value WHERE keyfield = keyvalue;

UPDATE table SET field=value WHERE EXISTS (SELECT field2 FROM table2 WHERE table.field = table2.field2);

Deleting Data#

DELETE FROM table;    --  this deletes every record!

DELETE FROM table WHERE keyfield = keyvalue;

Adding Data#

INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3);

Creating and Altering a Table#

CREATE TABLE table (
    col1 CHARACTER(5) NOT NULL PRIMARY KEY,
    col2 VARCHAR(30),
    col3 INTEGER UNIQUE,
    col4 INTEGER DEFAULT 0 NOT NULL,
    col5 DOUBLE PRECISION,
    col6 SMALLINT,
    col7 TIMESTAMP,
    CONSTRAINT cname PRIMARY KEY (col1, col2),
    CONSTRAINT cname UNIQUE (col2, col3),
    CONSTRAINT cname FOREIGN KEY (col1) REFERENCES other_table (ocol1),
    CONSTRAINT cname CHECK (col3 > 4)
);

DROP TABLE table;

CREATE [UNIQUE] INDEX name ON table (col1, col2);

CREATE [UNIQUE] INDEX name ON table (lower(col1), col2);

DROP INDEX name;

ALTER TABLE table ADD COLUMN newcol VARCHAR(20);

ALTER TABLE table DROP COLUMN oldcol;

ALTER TABLE table ALTER COLUMN col SET DEFAULT val;

ALTER TABLE table ALTER COLUMN col DROP DEFAULT;