Xiaobai writes sql statements

Keywords: Database

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

Posted by nvee on Fri, 10 Jan 2020 07:49:58 -0800