当前位置:首页 » Mysql技术教程

Mysql常用操作

2011-09-05 14:15 本站整理 浏览(3)


库操作

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