我的常用mysql语句2

create table `t_umw_user_roles_push_rule` (

`msg_id` int(11) not null,

`operation_id` int(11) not null,

`userId` int(11) not null,

`ageArea` int(11) not null,

`sex` bit not null,

`effect_flag` bit not null,

effect_dt datetime not null,

insert_dt datetime not null,

PRIMARY key (msg_id, operation_id, userId, ageArea, sex),

INDEX(effect_flag)

)ENGINE=INNODB;

 

create table epg_program_bak select * from epg_program

 

drop table epg_program_bak

create table epg_program_bak like epg_program

insert into epg_program_bak select * from epg_program

 

select min(epg_date) from haierdb.epg_program union select min(epg_date) from haierdb.epg_program_copy;

select devtype from udev_basic GROUP BY devtype

desc haierdb.epg_channle

 

select a.channelid, a.tvratings, a.shr, b.channlename from statistics.TOP_Channel_Month a, haierdb.epg_channle b  

  where a.cityid='0' and a.tlable='4' and a.channelid = b.channleid;

select epg_propertyid REGEXP '^\,55\,' from epg_program

insert into statistics.TOP_TVRating(programid, channelid, cityid, epg_date,  epg_btime, epg_type, epg_name, epg_vtime, epg_allvtime)  values('86741', '38', '0', '2013-06-03', '36', '55,84,127,', '健康就好', '72', '0')

select * from statistics.t_devChannelInfo where cid = 4 and DATE_FORMAT(start, '%Y-%m-%d')=DATE_FORMAT("2013-06-21 07:01:54", '%Y-%m-%d')

desc statistics.TOP_Epg_TM

alter table statistics.TOP_Channel_TM drop primary key

alter table statistics.TOP_Channel_TM add primary key(channelid, cityid, epg_type, tlable)

show TRIGGERS

 

SELECT cid from statistics.t_devChannelInfo where cid !=0 and (DATE_FORMAT(start, '%Y-%m-%d')=DATE_FORMAT('2013-07-14', '%Y-%m-%d'))  group by cid 

union SELECT cid from statistics.t_devChannelInfo where cid !=0 and (DATE_FORMAT(end, '%Y-%m-%d')=DATE_FORMAT('2013-07-14', '%Y-%m-%d')) group by cid

 

desc t_Upass_DevSid_Update;

可查看主键， PRI，主键本身即带索引， 查找索引很快

例如：建立索引

create index t_user_sid ON t_Upass_UserSid_Update(sid);

t_user_sid 为建立的索引名称， 可随便取、

show PROCESSLIST显示对数据库操作的所有进程状态

mysql_more_results检查是否还存在其他结果

mysql_next_result() 在多语句执行过程中返回/初始化下一个结果。

CREATE TABLE UserEnjoyInfo(
mac varchar(50),
epg_propertyid int(11),
count int(11) DEFAULT 0,
uptime  datetime, 
PRIMARY key(mac, epg_propertyid)
);

UPDATE statistics.UserENjoyInfo set cout = '%d', set uptime = NOW() where mac = '%s' and epg_propertyid = '%d'

CREATE TABLE TOP_Channel_Date_Ratings like TOP_Channel_Month_Ratings

desc TOP_Channel_Month_Ratings

select channelid, programid, Ratings, MartShr from statistics.TOP_Epg_Date_Ratings where cityid = 0 and 
 start = '2013-11-20' and end = '2013-11-20 22:30' ORDER BY Ratings DESC,MartShr desc limit 50

SELECT  a.channlename, t2.epg_name, t1.Ratings, t1.MartShr from haierdb.epg_channle a, 
                (select channelid, programid, Ratings, MartShr from statistics.TOP_Epg_Date_Ratings where cityid = 0 and 
                start = '2013-11-20' and end = '2013-11-20' ORDER BY Ratings DESC,MartShr ) as t1, 
                (select  epg_name, epg_channleid, epg_programid from statistics.epg_program_syn where epg_date = '2013-11-20' and 
epg_stime BETWEEN '2013-11-20 19:30:00' 
                and '2013-11-20 23:50:00' GROUP BY epg_channleid, epg_programid) as t2 where  a.channleid = t1.channelid and 
t2.epg_channleid = t1.channelid and t2.epg_programid = t1.programid ORDER BY t1.Ratings DESC,t1.MartShr desc