Use mycli and Learn MariaDB/MySQL comfortably in a terminal!

Kumamoto🍡 — Sat, 12 Dec 2020 04:09:04 +0000

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, 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,
please go to this one.

Preparation Let's set up mycli!

What is mycli anyway?

mycli 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.

The screenshot above shows the connection screen using the normal mysql command. It's hard to see and the command completion doesn't work.

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.

# 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.

Here's what the relation looks like(created by tbls)

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,,,

> 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
MyCLI – A MySQL/MariaDB Client with Auto-completion and Syntax-highlighting / TecMint

DEV Community: Kumamoto🍡