mysql常用语句

一、修改mysql用户密码：

mysql -h localhost -u root -p;命令行登录
update user set password = password('123456') where user = 'root'

二、数据库操作

1. show databases; 显示数据库
2. create database name;创建数据库
3. use databasename;选择数据库
4. drop database name;直接删除数据库，不提醒
5. mysqladmin drop databasename;删除数据库前，有提示

三、字符集相关：

1. 查看数据库支持的所有字符集：show character set;或show char set;
2. 查看系统字符集设置，包括所有的字符集设置：show variables like 'char%';
3. 查看数据表中字符集设置：show full columns from tablename; 或者 show create table tablenameG
4. 查看数据库编码：show create database dnname;
5. 修改数据库字符集：alter database dnname default character set utf8
6. 修改数据表字符集：alter table table_name default character set utf8
7. 修改mysql字符集：
• set character_set_client=utf8;
• set character_set_connection=utf8;
• set character_set_database=utf8;
• set character_set_server=utf8;
• set character_set_results=utf8;

四、表相关操作：

1. 显示表：show tables;
2. 显示表结构：describe tablename或 desc tablename;
3. 查看表索引：show create table table_name 或者 show index from table_name
4. show index from tablename得到的结果中，Cardinality表示散列程度，表示某个索引对应的列包含多少个不同的值，如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。
5. 修复索引：ANALYZE local table table_name;
6. 导出结果集：select * from table_name into outfile '/tmp/xyz.txt';
7. 新建索引：
   普通索引：alter table table_name add key `key_name`(field_name)
   唯一索引：alter table table_name add unique key `key_name`(field_name)
   主键索引：alter table table_name add primary key (field_name)
8. 删除索引：alter table table_name drop index key_name
9. 导入数据：source /tmp/users.sql //先登录mysql
10. 清除磁盘碎片：Optimize local table table_name
11. 检查表是否损坏：check table table_name
12. 修复损坏的表：repair table table_name
13. 检查是否有碎片：select TABLE_NAME,data_free from information_schema.tables where data_free > 0;
14. 修改字段名： 
    ALTER TABLE `ppd_ev_cat` CHANGE `ev_cat_id` `submission_to_catalog_id` int(10) unsigned NOT NULL AUTO_INCREMENT;
    ALTER TABLE `ppd_ev_cat` CHANGE `ev_catalog_id` `catalog_id` int(10) unsigned NOT NULL;
15. 修改表名：ALTER TABLE `ppd_ev_cat` RENAME TO `ppd_submission_to_catalog`;

五、sql语句：

1. 查询去除重复记录：distinct;
2. 显示当前mysql版本和当前日期：select version(),current_date;
3. 把时间戳格式化为“2001-01-02 00:00:00”格式：select from_unixtime('1234564');
4. 把时间格式化为时间戳（转为整型）：select unix_timestamp('2011-01-01');
5. 查询连接字符串：select concat(field_name,field_name2);
6. 查询逗号隔开的字段：WHERE FIND_IN_SET('1', responsible_user)
7. 字符串截取： select left(content, 20); select right(content, 20);
8. 获得插入记录的ID：
   INSERT INTO `abgent_modules` (`parent_id`, `name`, `description`, `status`, `is_show`, `sort`) VALUES (4, 'feedback_categories', 'Feedback Categories', 1, 1, 0);
   INSERT INTO `abgent_modules` (`parent_id`, `name`, `description`, `status`, `is_show`, `sort`) VALUES 
   ((SELECT LAST_INSERT_ID()), 'feedback_categories.index', 'List', 1, 1, 0),
   ((SELECT LAST_INSERT_ID()), 'feedback_categories.category_add', 'Add', 1, 1, 0),
   ((SELECT LAST_INSERT_ID()), 'feedback_categories.category_detail', 'Detail', 1, 0, 0),
   ((SELECT LAST_INSERT_ID()), 'feedback_categories.category_modify', 'Modify' , 1, 0, 0);
9. 查询结果插入另一个表：
   SELECT INTO FROM语句，语句形式为：SELECT vale1, value2 into Table2 from Table1，要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。
   INSERT INTO SELECT语句，语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1，要求目标表Table2必须存在。
   insert into `sta_local`.`sta_news` (`title`, `content`, `news_time`, `out_link`, `img_path`, `is_top`, `operator_id`) select `title`, `content`, `newtime`, `outlink`, `imgpath`, `istop`, 2 from `wuxi_global`.`p_news`;

六、检验sql语句性能：（explain或者describe）explain select ....

七、查看mysql当前状态：status或者s

八、查看mysql进程：show processlist

1. id，线程编号，当要关闭某一进程时执行 kill id；
2. user列，显示当前进程用户；
3. host列，显示当前进程是从哪个IP地址和哪个端口号发出来的；
4. db列，显示当前这个进程目前连接的是哪个数据库；
5. command列，显示当前连接进程所执行命令的类型或状态，一般就是休眠（sleep），查询（query），连接 （connect）；
6. time列，这个状态持续的时间，单位是秒；
7. state列，显示使用当前连接sql语句的状态，如查询语句，可能中间需要经历copying to tmp table，Sorting result，Sending data等状态才可以完成；
8. info列，显示这个连接所执行的sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

九、查看mysql状态：show global status like 'open_%';

十、查看mysql配置

1. show global variables like '%open%';
2. show global variables like 'max_connections';

十一、指针复位(php函数)

$result = mysql_query($sql);
mysql_data_seek($result, 0);

十二、BENCHMARK： SELECT BENCHMARK，它显示MySQL在该系统上在X秒内可以执行1,000,000个简单的+表达式运算。

十三、设置时区

show variables like '%time_zone%';
SET time_zone=timezone 
例如：比如北京时间（GMT+0800） 
SET time_zone='+8:00'

十四、查看sqlite表结构

sqlite3 /usr/sqlite/ot.db 
select * from sqlite_master where type="table" and name="nmo_message_log";