MySQL tutorial for Begginers

@pixabay

Story

My friend, A frontend engineer, has to learn how to use MySQL. But he is not sure should learn from anywhere. So I wrote a tutorial for him.

Prepare

If you can use MacOSX, you use brew install mysql command.

% brew install mysql

If you can use MacOSX, you use brew install mysql command. After installing it, check the version. My situation was 5.7.17.

% mysql --version
`5.7.17`

start, stop

You can run and stop MySQL using the command like bellow.

mysql.server start
mysql.server stop

01: interactive mode

If you installed MySQL and change something, you can log in as root user without password.

mysql -u root

Login Success, Terminal should say like below:

mysql> 

Let's show databases already exits.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

The time being, run the show tables;

mysql> use mysql;
Database changed

Next, run the show tables;

mysql> show tables;
...
31 rows in set (0.00 sec)

Let's show a table user.

mysql> select Host, User from user;
+-----------+-----------+
| Host      | User      |
+-----------+-----------+
| localhost | mysql.sys |
| localhost | root      |
+-----------+-----------+
2 rows in set (0.00 sec)

In this case, specified the display columns are Host, User. If you want to see all columns, typing like below:

select * from user;

Then, the columns are too long to see, So change the semicolon to ¥G. It will be easy to see.

select * from user\G

run the command exit; and stop the interactive mode.

mysql> exit;

02: command line

Other ways, you can use command line interface.

mysql -u root mysql -e 'show tables;'

You can write some commands into a file and use it as below:

% cat example.sql
show tables;

Please try it.

mysql -u root mysql < example.sql

03: create database

Let's practice.

mysql -u root -e 'create database mydb'

Let's start interactive mode. You can specify which database use at this point.

mysql -u root mydb;

It is as same as below.

mysql -u root
> use mydb;

04: create Table

create a flile scheme.sql

DROP TABLE IF EXISTS todos;
CREATE TABLE `todos` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `status` int(1) DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You can create and drop table just run below command. So you can retry this tutorial many times.

mysql -u root mydb < schema.sql 

05: add, show, update and delete the record

add the records

mysql> INSERT INTO todos (title, status) VALUES ("shopping", 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO todos (title, status) VALUES ("cooking", 0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO todos (title, status) VALUES ("exercising", 0);
Query OK, 1 row affected (0.01 sec)

show it all.

mysql> select * from todos;
+----+--------------+--------+---------------------+---------------------+
| id | title        | status | created             | updated             |
+----+--------------+--------+---------------------+---------------------+
|  1 | shopping     |      1 | 2017-04-06 13:36:00 | 2017-04-06 13:36:00 |
|  2 | cooking      |      0 | 2017-04-06 13:36:00 | 2017-04-06 13:36:00 |
|  3 | exercising   |      0 | 2017-04-06 13:36:01 | 2017-04-06 13:36:01 |
+----+--------------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)

Let's s select some records, not complete tasks.

select * from todos where status = 0;
+----+--------------+--------+---------------------+---------------------+
| id | title        | status | created             | updated             |
+----+--------------+--------+---------------------+---------------------+
|  2 | cooking      |      0 | 2017-04-06 13:36:00 | 2017-04-06 13:36:00 |
|  3 | exercising   |      0 | 2017-04-06 13:36:01 | 2017-04-06 13:36:01 |
+----+--------------+--------+---------------------+---------------------+
2 rows in set (0.00 sec)

Then finished cooking, update the task to complete.

mysql> select * from todos where id = 2;
mysql> update todos set status = 1 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Let's delete the record. I recommend selecting the record just before delete it.

mysql> select * from todos where id = 2;
mysql> delete from todos where id = 2;

Because if you forgot to specify where id = 2. The records will be gone in todos table.

06: backup and restore

create a backup file named dump.sql

% mysqldump -u root mydb > dump.sql 

This is just an example; we accidentally delete all records.

mysql -u root mydb -e 'delete from todos'
mysql -u root mydb -e 'select count(*) from todos' 
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Let's restore it.

mysql -u root mydb < dump.sql  

If you save dump file regularly, you can restore the database:)

mysql -u root mydb -e 'select count(*) from todos'
+----------+
| count(*) |
+----------+
|        2 |
+----------+

06: pager

You can specify pager, seeing many columns record.

mysql> pager less 
PAGER set to 'less -S'

you can move by arrow key

select * from mysql.user;

stop the less -S

mysql> nopager
PAGER set to stdout

Anyway, you want to copy the terminal character; there is a way to change the output to file from terminal.

mysql> pager cat > output.txt
PAGER set to 'cat > output.txt'
mysql> select * from mysql.user;
2 rows in set (0.00 sec)
mysql> nopager
PAGER set to stdout

07: CSV

Sometimes my coworker asks me to get CSV data from a database. There are many ways; my recommendation is using tools like a MySQL Workbench.

2017-04-06 14.34.25.png

summarize

What I think is it okay to forgot the pager and CSV. Just Please keep that in your mind.

Enjoy:)