Mysql常用操作

1>mysql 建数据库

create database db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

2>查询所有数据库中有多少表

select count(*) from information_schema.tables;

3>查询某个数据库中有多少表

select count(*) from information_schema.tables where table_schema='数据库名';

4>查询某张表在哪个数据库中(前提是：你得有information_schema的访问权限)

select table_schema from information_schema.tables where table_name ='要查询的表名';

5>查询一张表有哪些字段

show columns from 表名;
desc 表名;	

6>远程登陆
允许用户root从ip为%(不限制ip，或者具体某个ip)的主机连接到mysql服务器，并使 用password作为密码

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

其他：

1. dbcp数据库连接属性说明:

jdbc.initialSize=0   //初始化连接
jdbc.maxActive=30 //连接池的最大数据库连接数，设为0表示无限制
jdbc.maxIdle=20 //没有人用连接的时候，最大闲置的连接个数，设置为0时，表示没有限制。
jdbc.maxWait=1000    //超时等待时间以毫秒为单位
jdbc.removeAbandoned=true //是否自动回收超时连接
jdbc.removeAbandonedTimeout=60 //设置被遗弃的连接的超时的时间（以秒数为单位）， 即当一个连接被遗弃的时间超过设置的时间，则它会自动转换成可利用的连 接。默认的超时时间是300秒。
jdbc.logAbandoned = true //是否在自动回收超时连接的时候打印连接的超时错误
jdbc.validationQuery=select 1 from dual //给出一条简单的sql语句进行验证
jdbc.testOnBorrow=true //在取出连接时进行有效验证
2. 查询MySQL服务器最大连接数
show variables like 'max_connections';
3. 查询MySQL服务器过去的最大连接数
show global status like 'Max_used_connections';
比较理想的设置是：Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接 上线就设置得过高了
4. data too long for colum
解决办法：1.在my.ini配置中设置max_allowed_packet=16M(或更大)
            2.可能是中文字符问题
5. 为什么varchar类型的字段使用数字0来匹配时还能出结果
当比较数字和varchar时，mysql会把varchar转换为数字，首字符为数字0的，或者其他字符的都会转换为0，varchar类型字段=0 只能排除首字符为非零数字的字符串
注意：所以在进行sql语句的编写时要特别注意varchar类型的和varchar类型的比，如果传入条件的数字，一定要转为字符串，防止出现此类情况
6. 性能分析
1> explain
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
7. mysql数据库层实现SaveOrUpdate

insert into table_name(colum1,colum2)  values (colum1_value,colum2_value)  ON DUPLICATE KEY UPDATE colum2=VALUES(colum2)…;

colum1可以是主键、联合主键、唯一索引等可以唯一标识一条记录的列或多个列，但是尽量保证只使用上述三种的一种，否则后面的UPDATE要将所有的列都填进去
8. Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
在jdbc的url加上   zeroDateTimeBehavior参数：zeroDateTimeBehavior=convertToNull

详见http://blog.csdn.net/bz201/article/details/50685403

9. 还原数据，遇错继续执行

mysql -h localhost -u root  -f database < D:/filename.sql

10.建表，推荐方式

CREATE TABLE IF NOT EXISTS table_name(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_school_id` varchar(20) NOT NULL DEFAULT '' COMMENT '数据源学校标识',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT '**表';