Mysql or MariaDB常用命令

交互式登陆命令：

mysql:
    常见参数：
        -u:指定用户名
        -p:指定密码
        -h:指定登陆主机
        -P:指定端口
        -S:指定socket文件
范例:
[root@VM_87_96_centos ~]# mysql -uroot -p123456 -P3306 -S /tmp/mysql.sock -hlocalhost
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 119927
Server version: 5.5.48-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

登陆了mysql，当然要执行些有意思的查询啦

#查询数据库：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test                 |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.05 sec)
#创建一个库
mysql> create database tommy;
Query OK, 1 row affected (0.01 sec)
#进入一个数据库
mysql> use tommy
Database changed
#查询当前所在数据库
mysql> select database();
+------------+
| database() |
+------------+
| tommy      |
+------------+
1 row in set (0.00 sec)
#新建一个表试试
mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.18 sec)
#查询当前数据库所有表
mysql> show tables;
+-----------------+
| Tables_in_tommy |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.39 sec)
#查询表结构
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

表数据的增删改查：

增加一张表：auto_increment自增
mysql> create table iyunw(id int not null auto_increment,name varchar(20),primary key(id));
Query OK, 0 rows affected (0.08 sec)
#查询刚创建的表
mysql> desc iyunw;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
表里增加一行数据
mysql> insert into iyunw(name) values('Tommy');
Query OK, 1 row affected (0.02 sec)
#查询当前表所有数据
mysql> select * from iyunw;
+----+-------+
| id | name  |
+----+-------+
|  1 | Tommy |
+----+-------+
1 row in set (0.00 sec)
增加多行数据：
mysql> insert into iyunw(name) values('Tommy1'),('tommy2'),('tommy3');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
delete删除某一行数据,where指定删除条件，不然会全删
mysql> delete from iyunw where name='Tommy';
Query OK, 1 row affected (0.02 sec)
mysql> select * from iyunw;
+----+--------+
| id | name   |
+----+--------+
|  2 | Tommy1 |
|  3 | tommy2 |
|  4 | tommy3 |
+----+--------+
3 rows in set (0.00 sec)
update更改数据：
mysql> update iyunw set name='jarry' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from iyunw;
+----+--------+
| id | name   |
+----+--------+
|  2 | Tommy1 |
|  3 | jarry  |
|  4 | tommy3 |
+----+--------+
3 rows in set (0.00 sec)
alter修改表：ALTER TABLE
      alter table 表名 rename 新表名；
      alter table 表名 modify id int(10); 修改表中id字段字段类型
      alter table 表名 change 原字段名 新字段名 新字段类型；
      alter table hd1 add id enum("B","d"); 添加一个字段枚举字段id类型只能选B或者d
      alter table hd1 add id_id int(10) first ;天假一个字段并在第一个字段
      alter table hd1 drop id;删除id字段

权限相关：

    用户权限管理：
        创建：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
        删除：DORP USER 'username'@'host';
    授权：权限级别：管理权限、数据库、表、字段、存储例程；
        GRANT priv_type,.... ON dbname.tbname TO 'user'@'host' [IDENTIFIED BY 'password']
    回收权限：
        REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host';

其他：

查询查询操作是否用到索引:EXPLAIN :
查询当前服务器统计数据：SHOW GLOBAL STATUS;
更新权限：FLUSH PRIVILEGES;
查询引擎:SHOW ENGINES; 
查看支持所有字符集：SHOW CHARACTER SET;
查看支持所有排序规则：SHOW COLLATIONS;
查询表创建过程：SHOW CREATE TABLE student;
查看表当前状态：SHOW TABLE STATUS LINK 'student'\G
查询表的索引：SHOW INDEX FROM tb_name;
查看用户权限：SHOW GRANTS FOR user@host;
从库查看当前状态：show processlist;
查询详细参数：show variables；
设置参数：set global ""
锁表：5.1：flush tables with read lock;
         5.5:flush table with read lock;