Mysql技巧

Mysql 导入

创建数据库: mysql> create database testdata;

 

1, 首先在命令行控制台中打开mysql 
或许命令的如下: 
mysql -u root -p database_name 
然后或许会提示输入对应的密码 

 

2, 下面这条命令或许对你有用,当你想更换一个数据库的时候 
mysql>use database_name; 

3, 然后使用下面这个命令 
mysql>source d:\datafilename.sql 

监控Mysql 中执行的sql

http://www.cnblogs.com/superjt/p/3242788.html

 

windows平台

修改my.ini，在mysqld下添加log一行，

[mysqld]
log = "D:/tmp/mysql_log/mysql_log.sql"

然后，重新启动mysql，就可以实时看到myql服务器当前正在执行的语句了。

 

Linux平台下为例：

可以在/etc/my.cnf中的[mysqld]下添加：

#vi /etc/my.cnf

 

log =/tmp/mysqls.log

#如果需要监控慢查询可以添加如下内容：

log-slow-queries = /tmp/mysqlslowquery.log

long_query_time = 1

优化

If you do not use columns from all tables named in a query, MySQL stops scanning any unused tables as soon as it finds the first match. In the following case, assuming that t1 is used before t2 (which you can check with EXPLAIN), MySQL stops reading from t2 (for any particular row in t1) when it finds the first row int2:
SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

mysql alter 语句用法,添加、修改、删除字段等

//主键549830479
alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);

//增加一个新列549830479
alter table t2 add d timestamp;
alter table infos add ex tinyint not null default '0';

//删除列549830479
alter table t2 drop column c;

//重命名列549830479
alter table t1 change a b integer;

//改变列的类型549830479
alter table t1 change b b bigint not null;
alter table infos change list list tinyint not null default '0';

//重命名表549830479
alter table t1 rename t2;

//加索引549830479
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);

//加主关键字的索引549830479
mysql> alter table tablename add primary key(id);

//加唯一限制条件的索引549830479
mysql> alter table tablename add unique emp_name2(cardnumber);

//删除某个索引549830479
mysql>alter table tablename drop index emp_name;

//修改表：549830479
//增加字段：549830479
mysql> ALTER TABLE table_name ADD field_name field_type;

//修改原字段名称及类型：549830479
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

//删除字段：549830479
mysql> ALTER TABLE table_name DROP field_name;