SQL语句

重置root密码

# /etc/init.d/mysqld stop  
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &  
# mysql -uroot -p   
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';   
mysql> FLUSH PRIVILEGES;   
mysql> quit  
# /etc/init.d/mysql restart 

报如下错误

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

解决：
直接mysql回车就好了，不要加-u -p参数，就会使用默认的root@localhost登录

格式化输出查询结果

select * from mysql\G;

命令行链接远程数据库

sudo mysql -h ip -uroot -p密码 \

创建库并设置此库字符集utf8

CREATE DATABASE 库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

添加mysql用户

INSERT INTO mysql.user(Host,User,Password) VALUES("localhost","用户名",password("密码")); 

赋权

grant all on *.* to username@"%" Identified by "password"; 
GRANT ALL ON 库名.操作名 TO 用户名@"%(远程)|localhost(本地)" IDENTIFIED BY "密码" WITH GRANT OPTION;;

grant all privileges on 库名.* to 用户名@localhost identified by '密码';

刷新系统权限表

flush privileges;

在未登录MySQL的时候执行，-p 后面的是要导出库的名字

mysqldump --default-character-set=utf8 -u root -p ecloud_auth>/app/fileName.sql

mysql导入数据库

use databaseName;
set names utf8;
source /app/fileName.sql;

创建索引（PRIMARY KEY，INDEX，UNIQUE）

ALTER TABLE tbl_name ADD INDEX index_name (column list);
ALTER TABLE tbl_name ADD UNIQUE index_name (column list);
ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column list);
 

删除索引（PRIMARY KEY，INDEX，UNIQUE）

ALTER TABLE tbl_name DROP INDEX index_name (column list);
ALTER TABLE tbl_name DROP UNIQUE index_name (column list);
ALTER TABLE tbl_name DROP PRIMARY KEY index_name (column list); 

添加列

ALTER TABLE ukey ADD COLUMN description VARCHAR(255) AFTER uuid;
alter table 表名 add [column 可选] 列名 varchar(长度) [after 列名--在此列名之后添加]

ALTER TABLE ukey ADD COLUMN create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER uuid;
在ukey表中添加时间timestamp字段create_time 默认值为当前时间

查询table中某字段最大的10条记录

select * from 表 order by 要最大值的字段 desc limit 0（最大值），10（第10个值）

查itemfile表中itemdesc、re_level列的信息，要求re_level为NULL时显示为0，写出相应语句。

select itemdesc,ifnull(re_level,’0’) from itemfile;

清空表数据

truncate table 表名;

查看mysql

mysql> status  
--------------  
mysql  Ver 14.14 Distrib 5.5.29, for linux2.6 (i686) using readline 5.1  
  
Connection id:      2  
Current database:     
Current user:       root@localhost  
SSL:            Not in use  
Current pager:      stdout  
Using outfile:      ''  
Using delimiter:    ;  
Server version:     5.5.29-log MySQL Community Server (GPL)  
Protocol version:   10  
Connection:     Localhost via UNIX socket  
Server characterset:    utf8  
Db     characterset:    utf8  
Client characterset:    utf8  
Conn.  characterset:    utf8  
UNIX socket:        /tmp/mysql.sock  
Uptime:         1 min 10 sec 

设置mysql字符集

[client]  
default-character-set=utf8  
  
[mysqld]  
character_set_server=utf8 

mysql中Table is read only 的解决方法

# 修改数据库所有组 所有者
chown -R mysql:mysql /data/mysql

# 刷新表(清除缓存)
/usr/local/mysql/bin/mysqladmin -h 127.0.0.1 -u root -p flush-tables