#登录
mysql -u root -pxxxx;

#创建了一个名为：phplamp 密码为：1234 的用户
insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject)
 values("localhost","pppadmin",password("passwd"),'','','');

#退出
exit;

#使用刚刚创建的账户登录
#登录成功
mYsql -u pppadmin -p1234

#为用户授权
#登录MYSQL（有ROOT权限）
mysql -u root -pxxx

#创建一个数据库(testdb)
create database testdb;

#授权pppadmin用户拥有testdb数据库的所有权限。
grant all privileges on testdb.* to pppadmin@localhost identified by 'passwd';

#刷新系统权限表
flush privileges;

#指定部分权限给一用户，可以这样来写:
grant select,update on testdb.* to pppadmin@localhost identified by '1234';

#刷新系统权限表
flush privileges;

#删除用户
Delete FROM user Where User="pppadmin" and Host="localhost";

#刷新系统权限表
flush privileges;

#删除数据库
drop database 数据库名;

#删除表
drop table 数据表名;

# 导出数据库结构及数据
mysql -h xxx.com.cn -P 6620 -uchangeme -p123456 -Dtest<de1.sql

# 备份
mysqldump -u 用户名 -p密码 数据库名>data.bak
注意-p后面没有空格
# 恢复
mysql -u 用户名 -p密码 数据库名<data.bak

#查看数据库中所有表的记录数 start
use information_schema;

select table_name,table_rows from tables
where TABLE_SCHEMA = 'testdb'
order by table_rows desc;
#查看数据库中所有表的记录数 end


#显示  table user_info的create 语句
show create table user_info;

# 描述 user_info 表
desc user_info

#PRI代表主键, UNI 代表唯一索引, MUL 代表普通索引
+------------------+--------------+------+-----+-------------------+----------------+
| Field            | Type         | Null | Key | Default           | Extra          |
+------------------+--------------+------+-----+-------------------+----------------+
| id               | bigint(20)   | NO   | PRI | NULL              | auto_increment |
| id_card_no       | varchar(20)  | YES  | UNI | NULL              |                |
| user_no          | varchar(255) | YES  | UNI | NULL              |                |
| real_name        | varchar(32)  | YES  |     | NULL              |                |
| user_status      | int(11)      | YES  |     | NULL              |                |
| user_type        | int(11)      | YES  |     | NULL              |                |
| address          | varchar(255) | YES  |     | NULL              |                |
| gender           | smallint(6)  | YES  |     | NULL              |                |
| login_name       | varchar(32)  | YES  |     | NULL              |                |
| login_pwd        | varchar(32)  | YES  |     | NULL              |                |
| login_type       | smallint(6)  | YES  |     | NULL              |                |
| mailbox          | varchar(32)  | YES  |     | NULL              |                |
| mobile_no        | varchar(11)  | YES  | UNI | NULL              |                |
| pay_pwd          | varchar(32)  | YES  |     | NULL              |                |
| external_user_id | varchar(255) | YES  | MUL | NULL              |                |
| app_key          | varchar(36)  | YES  |     | NULL              |                |
| app_secret       | varchar(36)  | YES  |     | NULL              |                |
| salt             | varchar(32)  | YES  |     | NULL              |                |
| version          | int(11)      | NO   |     | NULL              |                |
| created_time     | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
| created_user     | varchar(255) | YES  |     | NULL              |                |
| updated_time     | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
| updated_user     | varchar(255) | YES  |     | NULL              |                |
+------------------+--------------+------+-----+-------------------+----------------+

#查看锁信息
show status like '%lock%';

|------------------------------------------+---------|
| "Variable_name"                          | "Value" |
| Com_lock_tables                          | "0"     |
| Com_unlock_tables                        | "0"     |
| Handler_external_lock                    | "0"     |
| Innodb_row_lock_current_waits            | "0"     |
| Innodb_row_lock_time                     | "30468" |
| Innodb_row_lock_time_avg                 | "338"   |
| Innodb_row_lock_time_max                 | "15053" |
| Innodb_row_lock_waits                    | "90"    |
| Key_blocks_not_flushed                   | "0"     |
| Key_blocks_unused                        | "6696"  |
| Key_blocks_used                          | "2"     |
| Performance_schema_locker_lost           | "0"     |
| Performance_schema_rwlock_classes_lost   | "0"     |
| Performance_schema_rwlock_instances_lost | "0"     |
| Qcache_free_blocks                       | "1"     |
| Qcache_total_blocks                      | "1"     |
| Table_locks_immediate                    | "4766"  |
| Table_locks_waited                       | "0"     |
|------------------------------------------+---------|