1. The associated query data (stored in temporary table A) is compared with table B, and the data in table A is found. Table B does not exist

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. Query all data with duplicate 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 character value

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

4. The time stamp is converted to the time, minute and second display of mm / DD / yyyy

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

Register time field name

5. Query the repeated openid data (only one is found), and how many times will it be repeated

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

6. Query table a and table B, and all duplicate data of a.userid and b.phone fields

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. Query the duplicate data of two different fields in a table

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

8. Modify fields

db: Database
Table: table name
userid: field before modification
Hh09 "userid: modified field
VARCHAR(100): field type
In this statement, two fields have not been modified. Change a field to b and c field to d

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

9. Add field

ALTER TABLE `a`
ADD COLUMN `num_follow` INT(11) NULL DEFAULT 0 COMMENT 'Number of fans' AFTER `user_pic`,
ADD COLUMN `num_feed` INT(11) NULL DEFAULT 0 COMMENT 'Pay attention to quantity' AFTER `num_follow`;

10. Query how many pieces of data are there in table a, generally using the auto increment field in the table

select count(id) from a;

11.classid de duplication query

SELECT distinct classid FROM a;

12. Query table a in reverse order, 1000 pieces of data

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

13. Change field value

UPDATE hao.phome_enewsmember set money = 0.00 WHERE money > 0;