MySQL的SQL語句寫法，除了那些基本的之外孤页，還有一些也算比較常用的尔苦，這里記錄下來，以便以后查找散庶。 好記性不如爛筆頭，這話說的太有道理了凌净，一段時間不寫它悲龟，還真容易忘記。大家不要糾結(jié)這些SQL語句包含的業(yè)務或是其它問題冰寻，本文只是一篇筆記而已须教。
1.將數(shù)據(jù)從T1表導入到T2表
INSERT INTO T2 (C1,C2) SELECT C1,C2 FROM T1 [WHERE C1 = XX AND C2 = XX ORDER BY C1]
2.使用T2表的NAME來更新T1表的NAME
UPDATE T1 AS A, T2 AS B SET A.NAME = B.NAME WHERE A.TID = B.ID
3.兩表的關(guān)聯(lián)更新
UPDATE T_ROLE_USER AS A,
(
? ? SELECT
? ? ? ? ID
? ? FROM
? ? ? ? T_USER
? ? WHERE
? ? ? ? DEPARTID IN (
? ? ? ? ? ? SELECT
? ? ? ? ? ? ? ? ID
? ? ? ? ? ? FROM
? ? ? ? ? ? ? ? T_DEPART
? ? ? ? ? ? WHERE
? ? ? ? ? ? ? ? LENGTH(ORG_CODE) = 9
? ? ? ? )) AS B
SET A.ROLEID = '123456'WHERE
? ? A.USERID = B.ID
4.自己和自己關(guān)聯(lián)更新
UPDATE T_DEPART AS A,
(
? ? SELECT
? ? ? ? ID,
? ? ? ? SUBSTRING(ORG_CODE, 1, 6) ORG_CODE
? ? FROM
? ? ? ? T_DEPART
? ? WHERE
? ? ? ? LENGTH(ORG_CODE) = 8
? ? AND PARENT_DEPART_ID IS NOT NULL) AS B
SET A.PARENT_DEPART_ID = B.ID
WHERE
? ? SUBSTRING(A.ORG_CODE, 1, 6) = B.ORG_CODE
5.兩表關(guān)聯(lián)刪除，將刪除兩表中有關(guān)聯(lián)ID并且T2表NAME為空的兩表記錄
DELETE A,B FROM T1 AS A LEFT JOIN T2 AS B ON A.TID = B.ID WHERE B.NAME IS NULL
6.將統(tǒng)計結(jié)果插入到表
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
7. 三表關(guān)聯(lián)更新
UPDATE SE_STAT_USER A,
(
? ? SELECT
? ? ? ? USER_ID,
? ? ? ? SUM(INVEST_ORG_COUNT + FINANCIAL_ORG_COUNT + INTERMEDIARY_ORG_COUNT + ENTERPRISE_COUNT) AS 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) AS 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'
8.帶條件的關(guān)聯(lián)更新
UPDATE SE_STAT_USER A,
(
? ? SELECT
? ? ? ? P.CHANNEL,
? ? ? ? COUNT(P.CONT_ID) AS 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 ENDWHERE
? ? A.USER_ID = B.CUST_MGR_ID
AND DATE_FORMAT(A.RECORD_DATE, '%Y-%m-%d') = '2012-06-11'
9. 加索引
ALTER TABLE PROJECT ADD INDEX INDEX_USER_ID (USER_ID),
? ? ? ? ADD INDEX INDEX_PROJECT_STATUS (PROJECT_STATUS);
10.刪除列
ALTER TABLE PROJECT DROP COLUMN PROJECT_STATUS,
? ? ? ? DROP COLUMN EXPECT_RETURN,DROP COLUMN CURRENCY;
11.增加列
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;
12.修改列 一般用MODIFY修改數(shù)據(jù)類型斩芭，CHANGE修改列名轻腺。
ALTER TABLE PROJECT CHANGE DICT_ID DICT_ID1 INT NOT NULL,
? ? ? ? MODIFY PROJECT_STATUS TINYINT NOT NULL COMMENT 'xxx';
