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  -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'

避免删除数据引起外键冲突

"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 "ybybyb" 

修改ROOT密码 进入  use  mysql库

update user set password=PASSWORD("ybybyb") where user='root';   之后重启MYSQL

获取所有表

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";  


ALTER操作

//增加主键
alter table player_role add bdcc int(5)  not null auto_increment ,add primary key (bdcc);
//增加一个新列
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 player_role rename player2;


//加索引
ALTER TABLE player_role ADD sss int(8) NOT NULL COMMENT '等级' ;
alter table player_role add index ins(sss);




//删除某个索引
alter table player_role drop index xxxx; //删除普通索引
ALTER TABLE player_role DROP PRIMARY KEY;   //删除主索引
//加主关键字的索引
alter table player_role add primary key(player_id);


//修改表：
//增加字段：
ALTER TABLE player_role ADD sss int(8) NOT NULL COMMENT '等级' ;
//修改原字段名称及类型：
ALTER TABLE player_role CHANGE sss aaa int(6);
//删除字段：
ALTER TABLE player_role DROP aaa;


安装

#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