MySQL的SQL语句写法，除了那些基本的之外，还有一些也算比较常用的，这里记录下来，以便以后查找。
好记性不如烂笔头，这话说的太有道理了，一段时间不写它，还真容易忘记。大家不要纠结这些SQL语句包含的业务或是其它问题，本文只是一篇笔记而已。
1.将数据从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.两表的关联更新
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.自己和自己关联更新
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.两表关联删除，将删除两表中有关联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.将统计结果插入到表
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. 三表关联更新
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.带条件的关联更新
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 END
WHERe
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修改数据类型，CHANGE修改列名。
ALTER TABLE PROJECT CHANGE DICT_ID DICT_ID1 INT NOT NULL, MODIFY PROJECT_STATUS TINYINT NOT NULL COMMENT 'xxx';
作者：菠萝大象
文章源自：http://www.blogjava.net/bolo
