4、 select * from t_Upass_DevSid_Update group by udevno having count(*) > 1
5、insert ignore into t_Upass_DevSid_Update(udevno) value(846874);
6、DELETE from t_Upass_DevSid_Update where udevno = '0000';
8、mysql_query(mysql, "insert ignore into t_Upass_DevSid_Update(udevno) value(846874));
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;
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
mysql 对结果集中加入行号
ORDER BY cityid asc, start asc, Ratings desc , MartShr desc
相同的cityid和start 按Ratings降序
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 查看表结构