SQL Programming (Basic)

Create/Drop DB

Create DB

CREATE DATABASE [IF NOT EXISTS] db_name
CHARACTER SET utf8 COLLATE utf8_bin;

Collate values:

utf8_bin - compare the binary value of the string
utf8_general_ci - general language search, case insensitive
utf8_general_cs - general language search, case sensitive

Drop DB

DROP DATABASE [IF EXISTS] db_name

Create/Drop table

Create table

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (
  p_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  p_ref TINYINT UNSIGNED NOT NULL,
  p_title VARCHAR(10) NOT NULL,
  p_amount DECIMAL(8,2) NOT NULL,
  p_code CHAR(6),
  p_status ENUM ('ACTV', 'CLOS') NOT NULL DEFAULT 'ACTV',
  p_u_id INT UNSIGNED NOT NULL REFERENCES users(u_id),
  modify_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  create_time TIMESTAMP DEFAULT 0,
  PRIMARY KEY (p_id)
) ENGINE = MyISAM;

ALTER TABLE tbl ADD INDEX idx_tbl_uid (u_id);
ALTER TABLE tbl ADD CONSTRAINT uk_tbl_pid UNIQUE (p_id);

Drop table

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

Insert

INSERT INTO tbl SET v1=1, v2=2
INSERT INTO tbl (v1,v2,v3) VALUES(1,2,3),(4,5,6),(7,8,9);
INSERT INTO tbl (v1, v2)
  SELECT d1, d2
  FROM tbl2 WHERE d1 > 100;

Insert if not present, otherwise update the row

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE b=2, c=3;

Other options

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

Update

UPDATE [LOW_PRIORITY] [IGNORE] tb11, tbl2, tbl3
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
UPDATE tbl1 AS t1
INNER JOIN tbl2 AS t2 ON t1.v1 = t2.v1
SET t1.v2 = t2.v2;

Delete

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

Delete rows in t1 & t2 only

DELETE t1, t2 FROM t1
INNER JOIN t2
INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

DELETE FROM t1, t2 USING t1
INNER JOIN t2
INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

Using alias

DELETE t1, t2 FROM table1 AS t1
INNER JOIN table2 AS t2
INNER JOIN table3
WHERE t1.id=t2.id AND t2.id=t3.id;

Delete row when there is no match

DELETE t1 FROM t1
LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;

More options

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] ...

Trunciate

TRUNCATE [TABLE] tbl_name

Select

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]
(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

UNION All will not remove duplicate while UNION (more expensive) will