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

更多联表查询可参考: 

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

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页