常用mysql操作记录

库操作 
repair ! 
/usr/local/webserver/mysql/bin/myisamchk -r /data0/mysql/3306/data/aceor/uchome_feed.MYI 

./myisamchk -r /data0/mysql/3306/data/aceor/uchome_feed.MYI 
create !
CREATE SCHEMA IF NOT EXISTS `webgame1`    DEFAULT CHARACTER SET utf8    COLLATE utf8_general_ci; 

mysqldump --all-databases -p -u root >databases.sql

mysqldump  -uroot -pybybyb --default-character-set=utf8 webgame1>  webgame1.sql
mysql -uroot -pybybyb --default-character-set=utf8 mr_admin < mr_admin 
查看连接数 
mysqladmin -uroot -pybybyb processlist
 
run-->cmd--->
c:\mysqldump -uroot -pjava csincity>20090115.sql回车即可，后面不要加分号,此时导出的为数据库中所有的数据。

c:\mysqldump -d -uroot -pjava csincity>20090115.sql
导出的为数据库中所有表的结构
c:\mysqldump -uroot -pjava csincity user>20090115.sql
导出的为数据库中user表的结构 

查询相关
//查找重复记录
select xh , count(*) from t_g_xs0100 group by xh having count(*) > 1
SELECT `mcua_id` , count( `mc_id` ) 
FROM map_castle_upgrade_accumulation
GROUP BY `mc_id` 
HAVING count( * ) >1 
根据字条串长度  
1.mysql> SELECT LENGTH(text);
        -> 4 
 2."select i.catid,i.itemid,i.subject,i.subjectimage,i.dateline,m.message
 from
 jsupe_mp3items  as i,jsupe_mp3message as m

 where i.itemid = m.itemid   and LENGTH(i.subjectimage)> 0  order by i.dateline desc  limit 1,1";

所有的查询函数  

http://dev.mysql.com/doc/refman/5.1/zh/functions.html

备份
SELECT * INTO OUTFILE  '/var/log/webgame_memory_log/20100113_160001/user.sql' FROM user
还原
Load Data local InFile '$filePath' Into Table `$file` FIELDS TERMINATED BY '\t' 
出现42000 错误解决方法
可以在执行命中加上--local-infile=1 参数即可解决。
如：/usr/local/mysql/bin/mysql -uroot -h192.168.0.2 -proot databaseName --local-infile=1 -e "LOAD DATA LOCAL INFILE 'data.txt' into table test(name,sex) "

避免删除数据引起外键冲突 
"SET FOREIGN_KEY_CHECKS = 0;" 
NULL数据 
"select u_id ,logintime  from user where logintime < '2010-03-01 00:00:01' OR logintime IS NULL  "; 
  
设置MYSQL初始密码 
mysqladmin -uroot password "321456" 
修改ROOT密码 进入  use  mysql库 
update user set password=PASSWORD("321456") where user='root';   之后重启MYSQL
mysqladmin -uroot password "321456" 
获取所有表 
select table_schema,table_name from information_schema.tables where table_schema='m2_data' 
判断字段是否存在 
列出所有字段   describe  user;
show columns from user like 'u_id'; 
基本操作 
删除库  DROP DATABASE `phpwind`; 

移除外键
ALTER TABLE map_market_customer DROP FOREIGN KEY map_market_customer_ibfk_1;  
ALTER TABLE map_market_customer DROP FOREIGN KEY mc_id; 
查看外键状态 
SHOW CREATE TABLE map_market_customer; 
SHOW TABLE STATUS FROM webgame1 LIKE 'map_market_customer'; 
查看错误报告 
show innodb status;  

创建新用户
insert into mysql.user(Host,User,Password)    
values("localhost","aoccenter",password("123456"));   
flush privileges;   
grant all privileges on aoccenter.* to aoccenter @localhost identified by '123456';   
删除用户 
DELETE FROM user WHERE User="min" and Host="localhost"; 
修改用户密码  
update mysql.user set password=password('123456')   
where User="min" and Host="localhost";  
//本地操作的权限 
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION; 

//远程操作的权限

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

flush privileges;

ALTER操作
//主键 
alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id); 
//增加一个新列 
alter table t2 add d timestamp;
alter table infos add ex tinyint not null default '0'; 
//删除列 
alter table t2 drop column c; 
//重命名列 
alter table t1 change a b integer; 
//改变列的类型  
alter table t1 change b b bigint not null;
alter table infos change list list tinyint not null default '0'; 
//重命名表 
alter table t1 rename t2; 
加索引 
mysql> alter table tablename change depno depno int(5) not null;
mysql> alter table tablename add index索引名(字段名1[，字段名2 …]);
mysql> alter table tablename add index emp_name (name); 
加主关键字的索引 
mysql> alter table tablename add primary key(id); 
加唯一限制条件的索引 
mysql> alter table tablename add unique emp_name2(cardnumber); 
删除某个索引 
mysql>alter table tablename drop index emp_name; 
修改表： 
增加字段： 
mysql> ALTER TABLE table_name ADD field_name field_type; 
修改原字段名称及类型： 
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type; 
删除字段： 
mysql> ALTER TABLE table_name DROP field_name;
-----------------------------------------------------------------------------------------------------
查看所有表的记录数
use information_schema; 
select table_schema,table_name,table_rows from tables order by table_rows desc;
查看数据库大小
SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='gamedb'; 
得到的结果是以字节为单位，除1024为K，除1048576(=1024*1024)为M
-----------------------------------------------------------------------------------------------------
安装  
#wget hackmysql.com/scripts/mysqlreport
chmod 755 mysqlreport 
#mysqlreport  --user root  --password ybybyb


#mysqlreport --help        
mysqlreport v3.5 Apr 16 2008
mysqlreport makes an easy-to-read report of important MySQL status values.


Command line options (abbreviations work):
   --user USER       Connect to MySQL as USER
   --password PASS   Use PASS or prompt for MySQL user's password
   --host ADDRESS    Connect to MySQL at ADDRESS
   --port PORT       Connect to MySQL at PORT
   --socket SOCKET   Connect to MySQL at SOCKET
   --no-mycnf        Don't read ~/.my.cnf
   --infile FILE     Read status values from FILE instead of MySQL
   --outfile FILE    Write report to FILE
   --email ADDRESS   Email report to ADDRESS (doesn't work on Windows)
   --flush-status    Issue FLUSH STATUS; after getting current values
   --relative X      Generate relative reports. If X is an integer,
                     reports are live from the MySQL server X seconds apart.
                     If X is a list of infiles (file1 file2 etc.),
                     reports are generated from the infiles in the order
                     that they are given.
   --report-count N  Collect N number of live relative reports (default 1)
   --detach          Fork and detach from terminal (run in background)
   --help            Prints this
   --debug           Print debugging information

Visit http://hackmysql.com/mysqlreport for more information.

1、开启慢查询 
找到 MySQL 的配置文件 ，my.cnf （Windows 为 my.ini ），在 MySQL 下增加下面几行： 
long_query_time=2 
log-slow-queries= /usr/var/slowquery.log

查看连接超时时间
show global variables like 'wait_timeout';
my.cnf 
wait_timeout=1814400 
interactive_timeout=1814400

关闭数据库
mysqladmin -u tjbxx -p shutdown
启动数据库
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/data_3306/my.cnf --user=mysql --port=3306 &

多实例的数据库操作
/usr/local/mysql/bin/mysql -S /data/mysql/mysql_3306.sock -uroot -p
-- 快速操作 

E:\eis>mysqldump -uroot -p eis_db goodclassification -e –max_allowed_packet=1048576 –net_buffer_length=16384 >good3.sql

player_role存在player不存在:

select count(*) from player_role where player_role.player_id not in (select player_id from player);
select * from player_role where player_role.player_role_id not in (select player_role_id from player_role_data);
select * from player where player.player_id not in (select player_id from player_role);

转载于:https://my.oschina.net/ffs/blog/167909