DEV Community

Cover image for Use mycli and Learn MariaDB/MySQL comfortably in a terminal!
Kumamoto🍡
Kumamoto🍡

Posted on • Updated on

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

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.

Alt Text
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
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
Enter fullscreen mode Exit fullscreen mode

After that, check mycli installed successfully.

$ mycli --version
Version: 1.21.1
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Check DB List

> SHOW DATABASES;
Enter fullscreen mode Exit fullscreen mode

Create DB

> CREATE DATABASE IF NOT EXISTS test;
Enter fullscreen mode Exit fullscreen mode

Delete DB

> DROP DATABASE IF EXISTS test;
Enter fullscreen mode Exit fullscreen mode

Check the hostname of the connection destination.

> SHOW VARIABLES LIKE 'hostname';
Enter fullscreen mode Exit fullscreen mode

Create Table

The following query will create a table like this.
Alt Text
Alt Text

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

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)
);
Enter fullscreen mode Exit fullscreen mode

Show Table List

> SHOW TABLES;
Enter fullscreen mode Exit fullscreen mode

Check Table Structure

-- DESCRIBE is a synonym for EXPLAIN.
> DESCRIBE TABLE_NAME;

-- example
> DESCRIBE members;
Enter fullscreen mode Exit fullscreen mode

Show Table's Create query

You can get more detailed information than DESCRIBE statements from the following query.

> SHOW CREATE TABLE TABLE_NAME;
Enter fullscreen mode Exit fullscreen mode

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!
Enter fullscreen mode Exit fullscreen mode

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 |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Delete index

> DROP INDEX INDEX_NAME ON TABLE_NAME;
-- Or that following is also OK
> ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;
Enter fullscreen mode Exit fullscreen mode

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";
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

Delete data

> DELETE FROM TABLE_NAME;

-- example1
DELETE FROM members;
-- example2
DELETE FROM members WHERE id=3;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

+α 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')
Enter fullscreen mode Exit fullscreen mode

Can not DROP INDEX

Check an following index using by SHOW INDEX, and try to delete PRIMARY but,,,
Alt Text

> 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")
Enter fullscreen mode Exit fullscreen mode

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`;
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)