mysql

================================常用命令==========================================
mysql -h 主机 -u 用户 -P 端口 -D 数据库名 -p 密码                 // 登录

source script.sql                                                        // 执行sql脚本
create database dbname                                            // 创建数据库
show databases                                                        // 显示数据库   show tables 显示表

查询表结构：

1 desc table_name;                                                                  // describe 命令简写
2 show columns from table_name;                                            // 查询表结构，与1效果类似
3 show create table table_name;                                              // 显示建表语句
4 use information_schema; select * from columns where table_name='table_name';

=================================修改root密码===================================
1 set password for 'root'@'localhost' = password('new_passwd');     // 该root密码
2 mysqladmin -u root password "newpass" -p
3 use mysql;UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root'; FLUSH PRIVILEGES;
ps:忘记root密码，先在my.cnf配置文件中[mysqld]中添加skip-grant-tables 重起mysqld 再使用方式3修改密码

==================================时间查询==================================
select * from table_name where time_field = '2015-09-21 15:04:05';  // 准确查找某个时刻的记录
select * from table_name where date(time_field) = '2015-09-21';     // 查找某个日期的函数

==================================优化===============================================
set bulk_insert_buffer_size = 100000                                // 插入优化

 

================================数据定义语句============================

创建表：

格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    [(create_definition,...)]

    [table_options] [select_statement]

示例：

   CREATE TABLE IF NOT EXISTS t_android_log (

　　　　id INT PRIMARY KEY,

　　　　level CHAR(10), tag CHAR(20),

　　　　content VARCHAR(2048)

　　);

 

==================================重装mysql==============================
sudo apt-get autoremove --purge mysql-server-5.5
sudo apt-get autoremove mysql-client
sudo apt-get autoremove mysql-server
sudo apt-get autoremove mysql-common
sudo apt-get install mysql-server mysql-client

==================================提供外网访问用户授权===============================

GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password' WITH GRANT OPTION;

FLUSH PRIVILEGES;