mysql 总结

mysql的一些常用命令和知识

新安装的mysql存在一个名为root的管理账号，如果安装中的账号设置中没有允许从网络访问的话，MySQL将不能从网络上使用，但我们又不希望root成为默认的远程访问账号。

以下便是添加从远程用于连接的用户名：myuser;密码：mypassword。该用户可以从任何地方访问任何表。
使用授权方式添加
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION

添加字段
ALTER TABLE `trade_info`
ADD COLUMN `market_eastern_asia` float NOT NULL DEFAULT '0' COMMENT '东亚';
ALTER TABLE `user`
ADD COLUMN `sex` smallint(6) NOT NULL DEFAULT '0' COMMENT '用户性别 0女  1男';

修改字段

ALTER TABLE `picture_lib` CHANGE  原字段 新字段 varchar(40) NOT NULL DEFAULT '' COMMENT 'company_img';

添加索引

 create index index_name on user (name);   

 alter table user add index index_name (name)[USING BTREE]; 

 [USING BTREE]BTREE是默认的存储引擎，如果要指定引擎，直接修改。

删除索引

drop index index_name on user;
alter table user drop index index_name;

查看索引

show index from table_name;

查找catTree字段中存在,号等于4个的

select * from v1_standard_category where length(catTree)-length(replace(catTree,',',''))=4

--------------------------------------------------------------------------------------------------

按周统计
SELECT DATE_FORMAT(open_date, '%x%v') AS m, count(*) AS so_count FROM em_access_record GROUP BY m ORDER BY m

---------------------------------------------------------------------------------------------------

字段按中文排序
order by convert(字段 USING gbk) COLLATE gbk_chinese_ci

--------------------------------------------------------------------------------------------------

定时进行碎片处理

1、目前支持optimize命令的引擎有 MyISAM, InnoDB, and ARCHIVE，对于InnoDB，会将optimize命令映射为ALTER TABLE命令，该命令会重建数据表，更新索引统计信息、回收主键索引中空间。

2、InnoDB 和 MyISAM

如果你的MySQL是有备库的，如果你只希望在主库上执行的话，那么可以加上关键字NO_WRITE_TO_BINLOG（或者LOCAL，意思完全相同）。
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE

存储引擎是myisam的基本一周或者两周清理一次，注意执行语句的时候会锁表。

比如：mysql> optimize table use; 

--------------------------------------------------------------------------------------------------

转换命令结束符

创建存储过程时，往往声明变量等会有；结束，在客户端运行存储过程时就会出现错误，此时只需要改变分号结尾的执行意义。

mysql> delimiter ##

mysql> select  * from use ##   此时结束就必须以定义的##结束，才可以执行该语句。如果要恢复成；就再执行一次。

mysql> delimiter ；

mysql>select  * from use ；    此时又是以分号结束执行sql

--------------------------------------------------------------------------------------------------

查看系统状态

1、查看系统变量

先执行 show VARIABLES；

如果想返回具体，可以使用 select @@basedir as basedir from dual;

查看系统变量需要在变量名前加@@

--------------------------------------------------------------------------------------------------
2、查看用户变量，比如存储过程中赋值了一个变量。

@test就是自定义的变量。

3、查看系统状态

show status;

4、查看表状态

show tables status;