Common MySQL SQL Statements and Table Alteration Cheat Sheet

This note records commonly used MySQL SQL statements beyond the basics for quick reference.

Insert data from one table to another using:

INSERT INTO T2 (C1, C2) SELECT C1, C2 FROM T1 [WHERE C1 = XX AND C2 = XX ORDER BY C1];

Update a table with values from another table via a join:

UPDATE T1 AS A, T2 AS B SET A.NAME = B.NAME WHERE A.TID = B.ID;

Delete related rows from two tables where a column is NULL:

DELETE A, B FROM T1 AS A LEFT JOIN T2 AS B ON A.TID = B.ID WHERE B.NAME IS NULL;

Insert aggregated statistical results into a summary table:

INSERT INTO SE_STAT_ORG (RECORD_DATE, ORG_ID, ORG_NAME, SIGN_CONT_COUNT, SIGN_ARRI_CONT_COUNT, SIGN_CONT_MONEY, SIGN_ARRI_CONT_MONEY, TOTAL_ARRI_CONT_COUNT, TOTAL_ARRI_MONEY, PUBLISH_TOTAL_COUNT, PROJECT_COUNT) SELECT * FROM (SELECT '2012-06-09' RECORD_DATE, PARENT_ORG_ID, PARENT_ORG_NAME, SUM(SIGN_CONT_COUNT) SIGN_CONT_COUNT, SUM(SIGN_ARRI_CONT_COUNT) SIGN_ARRI_CONT_COUNT, SUM(SIGN_CONT_MONEY) SIGN_CONT_MONEY, SUM(SIGN_ARRI_CONT_MONEY) SIGN_ARRI_CONT_MONEY, SUM(TOTAL_ARRI_CONT_COUNT) TOTAL_ARRI_CONT_COUNT, SUM(TOTAL_ARRI_MONEY) TOTAL_ARRI_MONEY, SUM(PUBLISH_TOTAL_COUNT) PUBLISH_TOTAL_COUNT, SUM(PROJECT_COUNT) PROJECT_COUNT FROM SE_STAT_USER WHERE DATE_FORMAT(RECORD_DATE, '%Y-%m-%d') = '2012-06-09' GROUP BY PARENT_ORG_ID) M;

Perform a three‑table update by joining aggregated subqueries:

UPDATE SE_STAT_USER A, (SELECT USER_ID, SUM(INVEST_ORG_COUNT+FINANCIAL_ORG_COUNT+INTERMEDIARY_ORG_COUNT+ENTERPRISE_COUNT) COMMON_COUNT FROM SE_STAT_USER WHERE DATE_FORMAT(RECORD_DATE, '%Y-%m-%d') = '2012-06-09' GROUP BY USER_ID) B, (SELECT USER_ID, SUM(ESTABLISH_COUNT+STOCK_COUNT+MERGER_COUNT+ACHIEVE_COUNT) PROJECT_COUNT FROM SE_STAT_USER WHERE DATE_FORMAT(RECORD_DATE, '%Y-%m-%d') = '2012-06-09' GROUP BY USER_ID) C SET A.COMMON_COUNT = B.COMMON_COUNT, A.PROJECT_COUNT = C.PROJECT_COUNT WHERE A.USER_ID = B.USER_ID AND A.USER_ID = C.USER_ID AND DATE_FORMAT(A.RECORD_DATE, '%Y-%m-%d') = '2012-06-09';

Conditional update using CASE expressions based on channel values:

UPDATE SE_STAT_USER A, (SELECT P.CHANNEL, COUNT(P.CONT_ID) CONT_COUNT, C.CUST_MGR_ID FROM (SELECT CHANNEL, CONT_ID FROM SK_PROJECT WHERE PROJECT_STATUS = 6 AND DATE_FORMAT(AUDIT_TIME, '%Y-%m-%d') = '2012-06-11') P INNER JOIN SE_CONTRACT C ON P.CONT_ID = C.CONT_ID GROUP BY P.CHANNEL, C.CUST_MGR_ID) B SET A.STOCK_COUNT = CASE WHEN B.CHANNEL = 2 THEN B.CONT_COUNT ELSE 0 END, A.ESTABLISH_COUNT = CASE WHEN B.CHANNEL = 3 THEN B.CONT_COUNT ELSE 0 END, A.ACHIEVE_COUNT = CASE WHEN B.CHANNEL = 4 THEN B.CONT_COUNT ELSE 0 END, A.BRAND_COUNT = CASE WHEN B.CHANNEL = 5 THEN B.CONT_COUNT ELSE 0 END, A.MERGER_COUNT = CASE WHEN B.CHANNEL = 6 THEN B.CONT_COUNT ELSE 0 END WHERE A.USER_ID = B.CUST_MGR_ID AND DATE_FORMAT(A.RECORD_DATE, '%Y-%m-%d') = '2012-06-11';

Add indexes to improve query performance:

ALTER TABLE PROJECT ADD INDEX INDEX_USER_ID (USER_ID), ADD INDEX INDEX_PROJECT_STATUS (PROJECT_STATUS);

Drop unnecessary columns:

ALTER TABLE PROJECT DROP COLUMN PROJECT_STATUS, DROP COLUMN EXPECT_RETURN, DROP COLUMN CURRENCY;

Add new columns with default values and comments:

ALTER TABLE PROJECT ADD COLUMN DICT_ID INT DEFAULT NULL COMMENT 'xxx' AFTER PROJECT_SITE, ADD COLUMN INTRODUCE TEXT DEFAULT NULL COMMENT 'xx' AFTER DICT_ID, ADD COLUMN STAGE INT DEFAULT NULL COMMENT 'xx' AFTER ID, ADD COLUMN ATTACH_URI VARCHAR(8) DEFAULT NULL COMMENT 'xxx' AFTER INTRODUCE;

Modify existing columns using CHANGE (rename) or MODIFY (change type):

ALTER TABLE PROJECT CHANGE DICT_ID DICT_ID1 INT NOT NULL, MODIFY PROJECT_STATUS TINYINT NOT NULL COMMENT 'xxx';