分享到:

mysql会用到的语句

游客(未登录) 发表于 2009/11/26 14:03:39 16 条回复 我要回复

1 批量替换表里的指定内容 UPDATE `node` SET `title`= replace( `title`, '公交线路', '邯郸公交线路' ) WHERE `title` LIKE '%公交线路%'


2,更新一个字段为随机数 UPDATE vcms_item set click = FLOOR((RAND() * 10000))

3,从一个表复制数据到另一个表,子查询  insert into vcms_xmjs (`aid`,`body`,`type`)  select `item_id`,`content`,`type` from tb_news where tb_news.type = 1

update 的子查询好像不能跟where *=* 这样的,只能跟指定值。

mysql时间戳 UNIX_TIMESTAMP( NOW( ) )

 

1. admin - 2014/09/06 22:10:31 - 回复:mysql会用到的语句

SET foreign_key_checks=0;
TRUNCATE TABLE  `fields_term`;
 
SET foreign_key_checks=1;

2. admin - 2015/08/08 18:42:38 - 回复:mysql会用到的语句

CREATE UNIQUE INDEX catename ON wb_blog(catid); 

3. admin - 2015/08/10 03:13:51 - 回复:mysql会用到的语句

:set paste

4. admin - 2015/09/25 12:34:49 - 回复:mysql会用到的语句

mysqlimport -uroot -p123456 -L -v --fields-terminated-by=, all hongjun.txt

5. admin - 2015/10/23 19:37:57 - 回复:mysql会用到的语句

按相同fid最后一次记录查询

select * from (SELECT * FROM `p2p_ppdai_items` order by id desc) as a group by fid order by id desc limit 0, 30

6. admin - 2015/10/26 11:13:48 - 回复:mysql会用到的语句

mysqldump -uroot -p123456 -c --skip-add-locks  www_thdata_com baijiu_good > baijiu1.sql;

7. admin - 2015/10/26 19:35:05 - 回复:mysql会用到的语句

select * from mytable where status!=0 and name!='' into outfile '/home/db_bak2012' fields terminated by '|' enclosed by '"' lines terminated by 'rn' ;  

load data infile  '/home/db_bak2012' into table mytable_bak fields terminated by '|' enclosed by '"' lines terminated by 'rn' ;

 

select * from mytable where status!=0 and name!='' into outfile 'D;\\bak.csv' fields terminated by ',' enclosed by '"' lines terminated by '\n' ;  

load data infile  'D;\\bak.csv' into table mytable_bak fields terminated by ',' enclosed by '"' lines terminated by '\n' ;

8. admin - 2015/11/06 13:32:39 - 回复:mysql会用到的语句

SELECT floor(RAND() * (SELECT MAX(nid) FROM `article`)) as nid  随机id

9. admin - 2015/12/14 15:23:44 - 回复:mysql会用到的语句

查询数据库所有表有多少数据
use information_schema;
 
select table_name,table_rows from tables
where TABLE_SCHEMA = 'www_thdata_com'
order by table_rows desc;   

10. admin - 2016/01/14 16:46:33 - 回复:mysql会用到的语句

 update urge_movie_pay as a 
join  
(select id, name from urge_movie_pay where c_date = '2016-01-13') as b 
on a.id = b.id
set a.name = b.name; 
 
UPDATE zhonggai_data SET company=(SELECT zhonggai_company.id from zhonggai_company WHERE zhonggai_company.name=zhonggai_data.company)

11. admin - 2016/03/18 16:44:35 - 回复:mysql会用到的语句

The total number of locks exceeds the lock table size my.cnf innodb_buffer_pool_size=256M

12. admin - 2016/03/21 13:43:00 - 回复:mysql会用到的语句

show variables like '%innodb%';

13. admin - 2017/03/09 10:58:18 - 回复:mysql会用到的语句

MySQL索引  
1.ALTER TABLE     
ALTER TABLE table_name ADD INDEX index_name (column_list)    ALTER TABLE table_name ADD UNIQUE (column_list) ALTER TABLE table_name ADD PRIMARY KEY (column_list)      
 
2.CREATE INDEX    
 CREATE INDEX index_name ON table_name (column_list)    
CREATE UNIQUE INDEX index_name ON table_name (column_list)      

14. admin - 2017/04/21 15:00:05 - 回复:mysql会用到的语句

mysqldump --skip-extended-insert加这个参数  每条数据一个insert

15. admin - 2017/10/23 15:23:26 - 回复:mysql会用到的语句

SELECT max(num), uid, name FROM simple_online_douyu group by uid limit 0, 10; 

16. admin - 2018/07/09 13:11:21 - 回复:mysql会用到的语句

SELECT * FROM `erciyuan_bilibili2018` WHERE UNIX_TIMESTAMP(createdate) > 1519833600 limit 10;