Use mycli and Learn MariaDB/MySQL comfortably in a terminal! 
Tags: mysql, mariadb, sql, mycli
Published: 2020-12-12 04:09:04

# Target audience for this article
- MariaDB (or MySQL) already installed

- Know some SQL
If you have heard of `SELECT` and `INSERT`, you should be fine.

- Want to play around with the DB using the CLI (the black screen of the terminal)
If you want to operate a full-scale DB, it is easier to use a GUI such as [Sequel Pro](https://www.sequelpro.com/), but if you just want to touch the DB, it is easier to use the mycli on terminal.

※ If you want to read the Japanese version of this article, <br>please go to [this one](https://qiita.com/digitalhimiko/items/55f838771357e739c846).

# Preparation Let's set up mycli!
## What is mycli anyway?
[mycli](https://www.mycli.net/) is a CLI interface for common DB management systems such as MySQL (and MariaDB).
※mycli stands for MySQL db client.

## So, what are you happy about using mycli?
The best part is that the completion makes it easier to type commands on the terminal and reduce typos.

- Basic commands will be complemented.
- Table names and other completion suggestions will be displayed.
- Easy to see with color settings.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/eqabqvan6dg0s7idekfy.png)
The screenshot above shows the connection screen using the normal mysql command. It's hard to see and the command completion doesn't work.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/jnlcj3npds3emm3lofih.png)
Here is a screenshot of the connection with the mycli command. It's colored and has a nice list of candidates!

## Install mycli
You can install mycli by `homebrew` or `pip`.
```
-- If you're Mac user
$ brew update
$ brew install mycli
-- If you're not
$ pip install mycli
```

After that, check mycli installed successfully.
```
$ mycli --version
Version: 1.21.1
```

## Configure mycli's setting, color, key-bindings etc,,,
We will change the configuration file `~/.myclirc` by referring to the [official mycli website](https://www.mycli.net/syntax).
```
# Change coloro(The text will be a young grass color.)
syntax_style = native
# Change key bindings
key_bindings = vi
# Display a wider selection of candidates
wider_completion_menu = True
```

# Let's try tweaking MariaDB (MySQL) with mycli.
## Connect to DB(DataBase)
If you haven't created a DB yet, or if you don't remember what kind of DB you had, omit the database name and try the following "Check DB List".
```
$ mycli -u(USER_NAME) -p(PASSWORD) [DATABASE_NAME]

-- example
$ mycli -uroot -pBARBAR foo_db
```

## Check DB List
```
> SHOW DATABASES;
```

## Create DB
```
> CREATE DATABASE IF NOT EXISTS test;
```

## Delete DB
```
> DROP DATABASE IF EXISTS test;
```

## Check the hostname of the connection destination.
```
> SHOW VARIABLES LIKE 'hostname';
```

## Create Table
The following query will create a table like this.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/fgyxuuiyme28onrp6sue.png)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/gs4gwtrzli1ljdiisa26.png)

Here's what the relation looks like(created by [tbls](https://github.com/k1LoW/tbls))
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/wszuo7tsn19fgbjfoced.png)

```
CREATE TABLE TABLE_NAME (COLUMN_NAME DATA_TYPE CONSTRAINT,...
FOREIGN KEY INDEX_NAME (COLUMN_NAME, ...) 
   REFERENCES TABLE_NAME_TO_REFERENCE (COLUMN_NAME, ...),
INDEX INDEX_NAME(COLUMN_NAME) COMMENT 'COMMENT');

-- example
> CREATE TABLE IF NOT EXISTS members (
id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(255) NOT NULL UNIQUE,
email varchar(255) NOT NULL UNIQUE,
password varchar(100) NOT NULL,
picture varchar(255) NOT NULL DEFAULT '/default_icon.jpg',
created DATETIME NOT NULL,
modified TIMESTAMP NOT NULL,
-- Index queries tend to be long, so I divide them as follows
INDEX idx_members_id_01(id) COMMENT 'for search member quickly'
);

> CREATE TABLE IF NOT EXISTS posts (
post_id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id MEDIUMINT NOT NULL,
content VARCHAR(900),
posted_at TIMESTAMP NOT NULL,
FOREIGN KEY fk_posts_userid_01(user_id)
    REFERENCES members (id)
);
```

## Show Table List
```
> SHOW TABLES;
```

## Check Table Structure
```
-- DESCRIBE is a synonym for EXPLAIN.
> DESCRIBE TABLE_NAME;

-- example
> DESCRIBE members;
```

## Show Table's Create query
You can get more detailed information than DESCRIBE statements from the following query.
```
> SHOW CREATE TABLE TABLE_NAME;
```

## Delete Table
```
> DROP TABLE IF EXISTS TABLE_NAME
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
```

## Check index
```
> SHOW INDEX FROM TABLE_NAME;
-- example
> SHOW INDEX FROM members;
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+
| Table   | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment             |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+
| members | 0          | PRIMARY           | 1            | id          | A         | 0           | <null>   | <null> |      | BTREE      |         |                           |
| members | 0          | name              | 1            | name        | A         | 0           | <null>   | <null> |      | BTREE      |         |                           |
| members | 0          | email             | 1            | email       | A         | 0           | <null>   | <null> |      | BTREE      |         |                           |
| members | 1          | idx_members_id_01 | 1            | id          | A         | 0           | <null>   | <null> |      | BTREE      |         | for search member quickly |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+
```

The meanings of the above indications are as follows.

|Item  |Description  |
|---|---|
|Table  |Table name  |
|Non_unique  |0 if the index cannot contain duplicates, 1 if it can.  |
|Key_name  |Index name if this index is primary key, the name of key is always PRIMARY  |
|Seq_in_index  |The column sequence number in the index, starting from 1.  |
|Column_name  |Column name  |
|Collation  |How the column is sorted; in MySQL, this is either "A" (ascending) or NULL (unsorted)  |
|Cardinality  |It refers to the types of values that a column can take. The higher the cardinality, the more likely it is that MySQL will use this index when performing a join.  |
|Sub_part  |If the column is only partially indexed, the number of indexed characters. NULL if the entire column is indexed.  |
|Packed  |Indicates how the key will be packed. NULL if it is not packed.  |
|NULL  |If this column can contain NULL values, then YES is used; otherwise, '' is used.  |
|Index_type  |Indexing method used (BTREE、FULLTEXT、HASH、RTREE)  |
|Comment  |Information about this index (e.g. disabled if the index is disabled)  |
|Index_comment  |Any comment that was provided for the index in the COMMENT attribute when the index was created.  |

## Add index to a column in a table that has already been created
```
> ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME(COLUMN_NAME);
```

## Delete index
```
> DROP INDEX INDEX_NAME ON TABLE_NAME;
-- Or that following is also OK
> ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;
```

## Update index comment
```
> ALTER TABLE TABLE_NAME
 DROP INDEX INDEX_NAME,
 ADD FULLTEXT INDEX INDEX_NAME (COLUMN_NAME) COMMENT "COMMENT";

-- example
> ALTER TABLE members 
 DROP INDEX idx_members_id_01, 
 ADD INDEX idx_members_01 (id) COMMENT "ARA ARA";
```

## Insert data
```
> INSERT INTO TABLE_NAME (COLUMN_NAME) VALUES (VALUE);

-- example1
> INSERT INTO members 
VALUES
(1, 'Nobunaga Oda', 'nobu_oda@example.com', 'HOGE', '/oda_icon.jpg', '2020-12-02 1:14:22', '2020-12-02 1:14:22');

-- example2
> INSERT INTO members
(name, email, password, picture, created)
VALUES
('Hideyoshi Toyotomi', 'hide_toyo@example.com', 'FUGA', '/toyo_icon.jpg', '2020-12-01 10:00:00');
```

## Delete data
```
> DELETE FROM TABLE_NAME;

-- example1
DELETE FROM members;
-- example2
DELETE FROM members WHERE id=3;
```

## Select data
```
> SELECT COLUMN_NAME, FROM TABLE_NAME;

-- example1
> SELECT id, name FROM members;
-- example2
> SELECT * FROM members;
-- example3
> SELECT * FROM members WHERE id=3;
```

# +α Where the author once got stuck.
## Columns with `AUTO_INCREMENT` constraint must be set to key.

Let's add indexes and set `PRIMARY KEY`.
```
CREATE TABLE members (
id INT NOT NULL AUTO_INCREMENT,
name varchar(255)
);
(1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')
```

## Can not `DROP INDEX`
Check an following index using by `SHOW INDEX`, and try to delete `PRIMARY` but,,,
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/g2v8xgdrnn3jep69qfel.png)
```
> ALTER TABLE members DROP INDEX PRIMARY;
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PRIMARY ON members' at line 1")
```

The reason is that the index name of the primary key is always PRIMARY, but PRIMARY is a reserved word, so you have to specify it as a quoted identifier or else an error will occur. It will work if you enclose it in a backquote as follows

```
> ALTER TABLE members DROP INDEX `PRIMARY`;
```

Note: you can't DROP the KEY of a column which has only one KEY because `AUTO_INCREMENT` is set.


# Reference
[mycli official document](https://www.mycli.net/)
[MyCLI – A MySQL/MariaDB Client with Auto-completion and Syntax-highlighting / TecMint](https://www.tecmint.com/mycli-mysql-client-with-auto-completion-syntax-highlighting/#:%7E:text=MyCLI%20is%20an%20easy%2Dto,SSL%20to%20the%20MySQL%20server.)