mysql 语句总结
1.多表查询
SELECT a.id,catid,thumb,title FROM v9_gamedown as a LEFT JOIN v9_gamedown_data as b ON a.id=b.id WHERE a.status=99 and b.artist="'.$artist.'" limit 0,'.$num
2.把一张表中的字段移动到另一张表
update v9_gamedown_data as a set a.n_language = (select language from v9_gamedown as b where b.id = a.id)
update v9_gamedown_data as a set a.n_thumb2 = (select thumb2 from v9_gamedown as b where b.id = a.id)
3.字符串替换
UPDATE `v9_record_data` SET `attach2` = REPLACE(`attach2`, 'http://www.fengzigame.com/uploadfile/2014/0326/', 'http://files.fengzigame.com/cd/share/') WHERE `id` > 0;
UPDATE `v9_record_data` SET `attach` = REPLACE(`attach`, 'http://www.fengzigame.com/uploadfile/', 'http://files.fengzigame.com/cd/pro/') WHERE `id` > 0;
4.把一张表的字段复制到另外一张表中
insert into apple_id (`apple_id`,`password`,`UDID`) select `apple_id`,`password`,`UDID` from appleaccount where UDID is not NULL;
5.建表
CREATE TABLE IF NOT EXISTS `v9_guan_history` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `tagid` int(10) DEFAULT '0' COMMENT 'tagid', `email` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'appleid', `idfa` VARCHAR(64) DEFAULT '' COMMENT '设备码', `status` tinyint(1) unsigned DEFAULT 0 COMMENT '类型', `activedate` VARCHAR(12) DEFAULT '' COMMENT '激活时间', `installdate` VARCHAR(12) DEFAULT '' COMMENT '装机时间', PRIMARY KEY (`id`), KEY `mykey` (`tagid`,`installdate`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
6.删除字段
ALTER TABLE `haha2` DROP `name`
7.添加字段
ALTER TABLE `v9_fx_history` ADD `devid` int(11) DEFAULT 0 AFTER `uid` ;
8.修改字段名称
ALTER TABLE `v9_dxt_history` CHANGE `madein` `area` varchar(16) DEFAULT ''
9.修改字段类型
ALTER TABLE `haha2` CHANGE `num` `num` INT( 11 ) NULL DEFAULT '0'
10.修改表名
ALTER TABLE `haha2` RENAME `haha` ;
11.修改字段的位置
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;
12.修改表的存储引擎
ALTER TABLE tb_dev ENGINE=MyISAM;
11.
复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO 新表 SELECT * FROM 旧表
复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,…….) SELECT 字段1,字段2,…… FROM 旧表
12.多表更新
UPDATE v9_qd_dev dev, v9_qd_account user SET dev.storename = user.storename WHERE dev.storeid = user.id ;
13.添加索引
ALTER TABLE book ADD INDEX mykey(id,name,num);
ALTER TABLE book ADD UNIQUE INDEX uniquekey (tagid);
14.查看表中的索引
SHOW INDEX FROM book \G;
15.删除索引
ALTER TABLE book DROP INDEX mykey;
DROP INDEX mykey ON book;
16.三张关联表的查询
select v9_app_enjoy.id,v9_app_enjoy.gid,title,tagid,attach4 from v9_app_enjoy left join v9_gamedown2 on v9_app_enjoy.gid = v9_gamedown2.id left join v9_gamedown2_data on v9_gamedown2_data.id=v9_gamedown2.id;
select title,tagid,attach4 from v9_app_enjoy as a left join v9_gamedown2 as b on a.gid = b.id left join v9_gamedown2_data as c on c.id=b.id where a.uid in (5634,11211) ORDER BY a.id desc limit 2;
17. 多表分组更新
update v9_qd_account as u,(select sum(money) as money, storeid from v9_qd_mingxi where inputtime>0 group by storeid) as m set now_money= now_money+money where u.id = m.storeid
18.关联删除
delete from v9_gamedown2 where status = 0 delete v9_gamedown2_data from v9_gamedown2_data LEFT JOIN v9_gamedown2 ON v9_gamedown2_data.id=v9_gamedown2.id WHERE v9_gamedown2.id IS NULL
20.在一个字段前添加字符串
update aa set name=concat('x',name)
21.模糊查询 关联度匹配
select tagid,catid from v9_gh_app where iscan != 1 and ( title LIKE '%无敌%' OR title LIKE '%流浪%' OR title LIKE '%方舟%') ORDER BY (CASE WHEN title LIKE '%无敌%' THEN 1000 ELSE 0 END) + (CASE WHEN title LIKE '%流浪%' THEN 990 ELSE 0 END) + (CASE WHEN title LIKE '%方舟%' THEN 980 ELSE 0 END) DESC
22.首次购买某个APP的用户
SELECT udid,id FROM (SELECT * FROM ( SELECT id, udid, tagid FROM v9_gh_buy_log WHERE origin = 111111004 AND udid != '' GROUP BY udid, id ASC ) AS A GROUP BY udid ) AS B WHERE B.tagid = 1486637414
23.首次购买某个APP的用户，购买的其他APP
select count(*) as tt,tagid from v9_gh_buy_log JOIN
(SELECT udid,id FROM
(SELECT * FROM
( SELECT id, udid, tagid FROM v9_gh_buy_log WHERE origin = 111111004 AND udid != '' GROUP BY udid, id ASC ) AS A
GROUP BY udid ) AS B
WHERE B.tagid = 1486637414) as C
ON v9_gh_buy_log.udid = C.udid where v9_gh_buy_log.id > 169736 and v9_gh_buy_log.tagid != 1486637414 order by tt desc
