Mysql or MariaDB常用命令

发表评论
3,421

A+

所属分类：数据库

交互式登陆命令：

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:

常见参数：

-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

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)

#查询数据库：

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;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | 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字段

增加一张表：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;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+-------+-------------+------+-----+---------+----------------+

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';

用户权限管理：

创建：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;

查询查询操作是否用到索引: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;

我的微信
这是我的微信扫一扫

我的微信公众号
我的微信公众号扫一扫

发表评论取消回复

登录 注册

登录注册