我常用的mysql语句1

3、select a.udevno, a.mac, a.type, b.postcode, b.model, a.vip, a.mcode, a.isactiv 

   from udev_basic a, udev_extend b where a.udevno = b.udevno and a.mac='450e27aba7f1f5d8fe54a812a75d1c72';

4、 select  * from   t_Upass_DevSid_Update  group   by udevno  having   count(*)  > 1

查找udevno相同的记录

5、insert ignore into t_Upass_DevSid_Update(udevno) value(846874);

有则插入， 无则不插入

6、DELETE from t_Upass_DevSid_Update where udevno = '0000';

删除所有的udevno='0000'的所有字段的所有记录

8、mysql_query(mysql, "insert ignore into t_Upass_DevSid_Update(udevno) value(846874));

只要不是真正的插入失败均返回0, 表示执行成功

9、SELECT mac FROM `udev_basic` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(udevno) FROM `udev_basic`)-(SELECT MIN(udevno) FROM `udev_basic`))+(SELECT MIN(udevno) FROM `udev_basic`)) AS udevno) AS t2 WHERE t1.udevno>= t2.udevno and vip is not null and mcode is not null ORDER BY t1.udevno LIMIT 1;

AS 说明之后是之前的别名     ORDER排序        LIMIT 1排序出来后的第一个

10、delete user from user left OUTER JOIN t_Upass_UserSid_Update ON user.utbno = t_Upass_UserSid_Update.user_no where t_Upass_UserSid_Update.user_no is NULL 

删除user表中utbno存在而t_Upass_UserSid_Update表中user_no不存在的记录, 其中utbno和user_no相同

 

mysql 对结果集中加入行号

传入用户变量：

set @mycnt = 0;

select (@mycnt := @mycnt + 1) as ROWNUM , b.channlename from statistics.TOP_Channel_TW a, haierdb.epg_channle b where a.cityid = '0' and a.channelid = b.channleid

ORDER BY a.shr DESC limit 50

11、mysql 分类排序

select cityid, start, end, '0', channelid, Ratings, MartShr from statistics.TOP_Channel_TW_Ratings  
ORDER BY  cityid asc, start asc,   Ratings desc , MartShr desc

 把输出结果按cityid 升序， 然后日期升序
相同的cityid和start 按Ratings降序

原则上每个字段都跟排序方式， 否则为默认的升序

12、group by 默认是分完组 然后排序。加上order by null就不用在进行排序了, 这样执行速度更快些

 13、alter table epg_program_syn drop primary key,add column id  int auto_increment primary key ,add unique key(`epg_date`,`epg_stime`,`epg_etime`,`epg_channleid`,`epg_programid`);去掉联合主键， 增加自增列为主键， 将原来的主键改为联合唯一性索引 

alter table TOP_TVRate_Mid modify column id int auto_increment  first; 将id改为第一个字段

14、CREATE TABLE t_UserWatchProgramInfo(
 id  int auto_increment primary key,
 mac varchar(50),
 epg_date date,
 epg_channleid int(10),
 epg_programid int(10),
 epg_propertyid varchar(500),
 epg_stime datetime,
 epg_etime datetime,
 dev_stime datetime,
 dev_etime datetime,
 unique key(`mac`, `epg_date`, `epg_channleid`, `epg_programid`, `epg_stime`)
) 

创建表，自增主键+唯一性联合索引

15、select week('2013-01-06')
select MONTH('2013-08-10')
select YEAR('2013-08-10')
select FROM_UNIXTIME(1388401260)
select UNIX_TIMESTAMP('2013-12-31 20:00:00')

时间处理函数

16、delete a from t_devChannelInfo  a inner join(select mac,start,end,min(id) as id from t_devChannelInfo group by mac,
start, end having count(*) > 1) as b on a.mac=b.mac and a.start=b.start and a.end=b.end and a.id > b.id;

表中mac, start, end 相同的记录值保留id最小的记录， 其余重复的删除

17、alter table t_devChannelInfo  add unique key(`cid`,`mac`,`start`,`end`); 修改表加入唯一性索引

ALTER TABLE t_devChannelInfo DROP INDEX cid 删除唯一性索引

show create table  t_devChannelInfo 查看表结构