Mysql常用语句，权限，查看数据库，添加索引，字段重命名，修改数据库、表、字段字符集

一般后台shel登录mysql:

mysql -uroot -p

mysql中查看所有数据库：

show databases;

切换数据库：

use testDB;

列出当前数据库下所有表：

show tables;

查看表结构信息：

desc tableName

查看创建数据库语句：

show create database test;

查看创建表语句：

show create table test;

增加表字段：

alter table tb_name add col_namevarchar(10) default '-';

表字段重命名：

alter table tableName change col_old col_new varchar(50);

查看当前数据库能够支持的所有字符集：

show charset;

修改数据库字符集：

alter database test character set utf8;

修改表的字符集：

ALTER TABLE person DEFAULT CHARACTER SET utf8;

修改表字段字符集：

ALTER TABLE person CHANGE address address varchar(50) CHARACTER SET utf8;

增加索引：

ALTER TABLE test ADD INDEX index_name (column_list)

ALTER TABLE test ADD UNIQUE (column_list)

ALTER TABLE test ADD PRIMARY KEY (column_list)

或者：

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引：

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

查看索引：

show index from table_name ;
show keys from table_name ;

常看当前事务隔离级别：

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

开启事务：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#或者
mysql>start transaction;
Query OK, 0 rows affected (0.00 sec)
# 或者，设置非自动提交，相当于每次事务需要手动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

提交事务：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

获取行锁：
事务A：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from person where id=1 for update;
+----+---------+------+--------+-----------+-------------+
| id | name    | age  | gender | address   | phone       |
+----+---------+------+--------+-----------+-------------+
|  1 | Leo Han |   29 | M      | 上海市    | 16619788321 |
+----+---------+------+--------+-----------+-------------+
1 row in set (6.49 sec)

事务B：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 会阻塞在这里
mysql> select * from person where id=1 for update;

授权

查看权限：

mysql> SHOW GRANTS FOR 'root'@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
2 rows in set (0.00 sec)

赋权限

grant all privileges on db_name.table_name to 'user_name'@'host_name' identified by '123456';

grant all privileges on test_db.* to 'test'@'%' identified by '123456';