sql语句记录

1.联查到的数据（存于临时表A中）与B表比较，查找到A表存在的数据，B表不存在

SELECT * FROM (SELECT a.userid FROM a,b where a.userid=b.userid and groupid=5) as a  WHERE a.userid NOT IN (SaaELECT a.userid FROM b);

2.查询openid重复的所有数据

select *, username,openid,phone,FROM_UNIXTIME(registertime, '%Y-%m-%d %h:%m:%s') from a, b where a.userid=b.userid and openid in (select openid from a group by openid having count(1) > 1) and openid != '' and phone!='' ORDER BY registertime desc;

3.拼接设置字符值 concat

update a set phone = concat('hqs_' + username) where userid in ('42496', '42645')

4.时间戳转化为年月日时分秒显示

FROM_UNIXTIME(registertime, '%Y-%m-%d %h:%m:%s') 

registertime 字段名

5.查询openid重复的数据（仅查询出一条），并将重复多少次查询出来

SELECT openid, count(*) from a GROUP BY openid having count(*)>1;

6.查询a表和b表，a.userid和b.phone字段重复的所有数据

select a.userid,a.username,b.userid,b.phone,FROM_UNIXTIME(registertime, '%Y-%m-%d')  from a, b where a.username =b.phone group by a.userid desc ;

7.查询一个表中，两个不同字段重复的数据

SELECT username, openid from phome_enewsmember GROUP BY username,openid HAVING count(*)>1;

8.修改字段

db：数据库
table：表名
userid：修改前字段
hh09_userid：修改后字段
VARCHAR(100)：字段类型
这个语句未修改了两个字段，将a字段修改为b，将c字段修改为d

ALTER TABLE `db`.`table` CHANGE userid hh09_userid VARCHAR(100) NULL, CHANGE phone hh09_phone int(11) DEFAULT 1; 

9.添加字段

ALTER TABLE `a`
ADD COLUMN `num_follow` INT(11) NULL DEFAULT 0 COMMENT '粉丝数量' AFTER `user_pic`,
ADD COLUMN `num_feed` INT(11) NULL DEFAULT 0 COMMENT '关注数量' AFTER `num_follow`;

10.查询表a有多少条数据，一般用表中的自增字段

select count(id) from a;

11.classid去重查询

SELECT distinct classid FROM a;

12.倒序查询a表，1000条数据

SELECT * FROM a ORDER BY id desc LIMIT 0, 1000;

13.更改字段值

UPDATE a set money = 0.00 WHERE money > 0;

14.给一个字段递增赋值

set @r:=0;
update gt096_square set tc096_createtime = (@r:=@r+1)

15.将 photo字段数据中的6个冒号替换为逗号

 UPDATE a set photo=REPLACE(photo,'::::::',',') where photo != '';

16.删除photo字段中数据最后一个字符

 update a set photo=left(photo, length(photo)-1) where photo != '';

17.mysql正则条件查询

 SELECT photo FROM a where photo REGEXP '.png|.jpg|.bmp|.jpeg' and photo != '';

其他正则条件查询

18.mysql 将content字段更改为保留从右往左数的9个字符（1个汉字也算一个字符）

 update a set content=right(content, 9) where content like '%xxxx%';

18.workbench 1175错误，

方法一：

    SET SQL_SAFE_UPDATES = 0;

方法二：