sql语句

1.增加一个字段（列）

ALTER TABLE hera.s1_sys_report ADD COLUMN openid varchar(225);   

新增一个字段，并且给该字段注释

ALTER TABLE hera.d4_gfoa_user ADD COLUMN modify_user varchar(225) DEFAULT NULL COMMENT '最后修改人'

新增多个字段，而且给字段添加注释

//注意表名不需要加“”
ALTER TABLE d10_user_general ADD (  
    flag6 int(11) DEFAULT 0 COMMENT '对应flag2的重新授信，0没有填写，1已经填写',
    flag7 int(11) DEFAULT 0 COMMENT '对应flag3的重新授信，0没有填写，1已经填写',
    flag8 int(11) DEFAULT 0 COMMENT '对应flag4的重新授信，0没有填写，1已经填写'
);

2.修改一个字段（列）

ALTER TABLE hera.s1_sys_report MODIFY  COLUMN retCode varchar(225);

3. NOT IN 和 OR 关键字用法

select * from hera.s1_sys_report where reporttime > SUBDATE(now(),interval 1 hour) AND 
(delaytime >=2000 OR retCode NOT IN('0_undefined','0_0','0_000000','undefined_0','undefined_000000'));

4.查询字符串长度估计的数据

SELECT `customerid`  FROM `d1_user` where length(`customerid`) < 10;

5.连接两个字符串

concat(a.prod_code, '-', c.prod_name) as prod_name

6.isNULL和IF语句的用法

select T2.customerid, IF (isnull(t2.customerid),0,1) reReadonly from d1_user t2;

7.修改一条记录（行）

UPDATE  d10_user_general  SET flag2=1,flag3=1,flag4=1 WHERE id=1;

难点在于更新查询另外一个表中的查询的数据，而不是指定的值。例如下面：

UPDATE hera.d12_user_contact d11
LEFT JOIN (
    SELECT
        t2.id,
        t2.mate_name,
        t2.relation,
        t2.mate_mobile,
        t2.contact_name,
        t2.contact_mobile
    FROM
        hera.`d22_recredit_contact` t2 where t2.status = 1
) s1 ON (d11.id = s1.id)
SET d11.mate_name = s1.mate_name,
 d11.relation = s1.relation,
 d11.mate_mobile = d11.mate_mobile,
 d11.contact_name = s1.contact_name,
 d11.contact_mobile = s1.contact_mobile
WHERE
    d11.id IN (
        SELECT
            E.ID
        FROM
            (
                SELECT
                    t4.id
                FROM
                    hera.`d12_user_contact` t3,
                    hera.`d22_recredit_contact` t4
                WHERE
                    t4.`status` = 1
                AND t3.id = t4.id
                AND (
                    t3.mate_name != t4.mate_name
                    OR t3.relation != t4.relation
                    OR t3.mate_mobile != t4.mate_mobile
                    
                    OR t3.contact_name != t4.contact_name
                    OR t3.contact_mobile != t4.contact_mobile
                )
            ) E
    );

也可以是这样，逗号表示两个表连接。

update hera.`d11_user_realname` t1,hera.`d21_recredit_realname` t2
set t1.mobile = t2.mobile,
t1.bankcard = t2.bankcard,
t1.sex = t2.sex,
t1.education = t2.education,
t1.emailAddr = t2.emailAddr,
t1.family_address_code = t2.family_address_code,
t1.family_address_name = t2.family_address_name,
t1.family_address = t2.family_address,
t1.mobile_bank = t2.mobile_bank,
t1.bankType = t2.bankType,
t1.unionpayFlag = t2.unionpayFlag
WHERE t1.id=t2.id
                AND    t2.`status` = 1
                AND (
                    t1.mobile != t2.mobile
                    OR t1.bankcard != t2.bankcard
                    OR t1.sex != t2.sex
                    OR t1.education != t2.education
                    OR t1.emailAddr != t2.emailAddr
                    OR t1.family_address_code != t2.family_address_code
                    OR t1.family_address_name != t2.family_address_name
                    OR t1.family_address != t2.family_address
                    OR t1.mobile_bank != t2.mobile_bank
                    OR t1.bankType != t2.bankType
                    OR t1.unionpayFlag != t2.unionpayFlag
   ) and t1.id in(35,47,885,1322,1661,69,46,136);

 

8.一个表的数据复制到另一个表中

INSERT INTO hera.d23_recredit_work SELECT * FROM hera.`d13_user_work`WHERE id=6;

9 insert与select连用

INSERT INTO d0_user_log 
SET userid=1, username = (SELECT name from d4_gfoa_user where mobile = '13928420114')

10 mysql 值为 NULL 的记录查询

SELECT * FROM `ecs_user_bonus` WHERE by_userid is null //同理设置记录为null的sql如下： update tb set column1 is null

11 SELECT ... FOR UPDATE

 用在事务上，查询的时候，如果有其他事务在更新字段，等待读取完毕。

 

update b2_book t set t.chapter_total =
(select count(*) as number from b3_content b where t.book_id =b.book_id
Group by book_id)