mysql常用命令

归档记录

/*查找表中符合条件的所有记录*/

SELECT * FROM my_test WHERE name='test'

/*查找表中符合条件的并且name字段不重复的所有记录数*/
SELECT COUNT(DISTINCT name) FROM my_test WHERE starttime>='2018-05-09 00:00:00' AND endtime<='2018-05-09 23:59:59'
/*联表查询*/
SELECT  a.phone,a.amount, b.amount,b.seqno FROM

  (SELECT phone,amount  FROM my_test1 WHERE STATUS = '1' AND amount> 0 AND createtime LIKE '2018-05-15%') a  LEFT JOIN (SELECT phone,amount,seqno FROM my_test2 WHERE STATUS = '1' AND createtime LIKE '2018-05-15%') b ON a.phone = b.phone ORDER BY phone , seqno

/*从两个表中合并数据称一个新表，语句中''表示为默认值，比如自增的index，或者为空*/

insert into new_table select '',a.phone,name,age,address,'', b.birth
from
  students a,
  info_table b 
where studentnum in (
    '20180722121735',
    '20180721174759',
    '20180720103621',
    '20180718122414'
  )

  and a.phone= b.phone

/*根据某个属性统计各属性中记录个数,比如统计学校里广东各区学生数目*/

SELECT areacode,COUNT(*) FROM studentsinfo WHERE province='gd' GROUP BY areacode

/*根据日期统计每天各类产品生产个数*/

SELECT producttype, DATE_FORMAT(registertime, '%Y-%m-%d') cycle, COUNT(*) num FROM product_table_record WHERE DATE_FORMAT(registertime, '%Y-%m-%d') <= '2018-05-20' AND DATE_FORMAT(registertime, '%Y-%m-%d') >= '2018-05-14' GROUP BY producttype, DATE_FORMAT(registertime, '%Y-%m-%d')

/*联表更新*/

UPDATE `table_a` a,`table_b` b 
SET a.lgpd=b.lgpd,a.pmpd=b.pmpd,a.province=b.province,a.areacode=b.areacode WHERE a.phone= b.phone

/*给某字段设置某个范围的随机值*/

若要在i ≤ R ≤ j 这个范围得到一个随机整数R ，需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。例如， 若要在8到 13 的范围（包括8和13）内得到一个随机整数, 可使用以下语句：

SELECT FLOOR(8 + (RAND() * 6));

/*给某字段设置值，由其他字段换算和拼接而成，比如设置ip*/

UPDATE  tablename SET `ip`=CONCAT("10.101.", CAST(ROUND(字段1/255) AS CHAR),".",CAST(ROUND(字段2/255) AS CHAR)) WHERE idx=10;

其中round（字段1/255），表示将字段1对255取模后取整，然后使用cast as char转成字符串

/*mysql查询一张表字段在另一张表中出现的次数*/

SELECT t1.idx,t1.country,t1.city,t1.sim_operator_name,t1.sim_operator,t1.longitude,t1.latitude,COUNT(t2.location_id) usernum FROM (SELECT * FROM table_xxx WHERE STATUS='1' AND sim_operator='45412' ) t1 
LEFT JOIN (SELECT * FROM table_yyy) t2 ON t1.idx = t2.location_id GROUP BY t1.idx  ORDER BY usernum ASC

/*时区设置--这条是自我笔记，有不完善处，看官不要参考*/
UPDATE tablename SET timezone=IF(-longitude%15<=7.5,CONCAT("GMT",CAST(-FLOOR(-longitude/15)AS CHAR),":00"),CONCAT("GMT",CAST(-FLOOR(-(longitude/15)+1)AS CHAR),":00")) WHERE xxxx='yyyy'

更多联表查询可参考： 

 https://www.cnblogs.com/Crius/p/6895965.html