常用的MySQL语句整理_MySQL

好记性不如烂笔头，这话说的太有道理了，一段时间不写它，还真容易忘记，本文只是一篇笔记而已。

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';

不同数据库此属性不一样，MYSQL 与ORACLE,MS SQL SERVER不一样，见具体文档

http://www.w3school.com.cn/sql/sql_autoincrement.asp

自增

ALTER TABLE persons AUTO_INCREMENT=1;