MySQL - Intro

March 23, 2020

Page content

This is a draft page.

Connect to MySQL

Open your terminal and execute the following.

mysql -u root -p

mysql: Connect to MySQL.
-u root: as user root.
-p : with the password.

This command prompt you to input the password. After input your password, MySQL prmpt mysql> will appear.

Make a normal user

Create normal user

There are users im MySQL, different from OS user. I recommend to make a new simple user in order to access control. The following command create the user, which name is dbuser and the password is foobar.

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'foobar';

Notes. I write MySQL reserved words in capital case. In above example, CREATE, USER, IDENTIFIED BY` are MySQL reserved words.

Log out first and log in as a user dbuser.

EXIT;

mysql -u dbuser -p

Check the MySQL status

Show initial databases

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

Allof these are special databases for MySQL itself.

Show the user list (check the user was created)

SELECT * FROM mysql.user;

You will be the messy format because the command shows you full information about users. Let’s try to see user name only.

mysql> SELECT USER FROM mysql.user;
+------------------+
| USER             |
+------------------+
| dbuser           |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
5 rows in set (0.00 sec)

We can see the user dbuser !

Create a database

I want to create a table (an element of a database). So, I should create a database first. Following snippet create the database “Assets”.

mysql> CREATE DATABASE Assets;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| Assets             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Use created database

USE Assets;
Database changed

Check it is an empty database.

SHOW TABLES;
Empty set (0.00 sec)

Concept: Database -> Tables(we manipulate)

Auth an user to edit the database

with root user.

mysql> GRANT ALL PRIVILEGES ON Assets.* TO 'dbuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Concept of RDB

DB contains tables, you can image the a table as column-row table (if you familiar to Excel, it will be easy to imagine).

Create a table

Before a command

We should dedsign what kind of value shoul we put. This is the interesting part of the RDB.

For example, I want to make a simple private assets table, so following values may be reqiured.

added_Date: The date an asset added.
Item: Detail about an item.
is_credit: The transaction is credit or not.
Revenues: Revenues.
Expenses: Expenses.

And we should decide also the type of an asset. Here is the data type list of MySQL. https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Date: DATE
Item: VARCHAR(32)
is_credit: BOOLEAN
Revenues: INT
Expenses: INT

About Revenues and Expenses, I’ll input the value in unit of Cent.

https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency

And build the SQL query.

CREATE TABLE IF NOT EXISTS tblSample(
  id_item INT AUTO_INCREMENT PRIMARY KEY,
  added_date DATE,
  Item VARCHAR(32),
  Category VARCHAR(32),
  is_credit BOOLEAN,
  Revenues INT,
  Expenses INT
) ENGINE=INNODB;

https://www.access-programmers.co.uk/forums/threads/personal-monthly-budget-db-design.227519/

Check the tables

mysql> SHOW TABLES;
+------------------+
| Tables_in_assets |
+------------------+
| tblSample        |
+------------------+
1 row in set (0.00 sec)

SELECT * FROM tblSample; Empty set (0.00 sec)

Check the clolumns.

mysql> SHOW COLUMNS FROM tblSample;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id_item    | int         | NO   | PRI | NULL    | auto_increment |
| added_date | date        | YES  |     | NULL    |                |
| Item       | varchar(32) | YES  |     | NULL    |                |
| is_credit  | tinyint(1)  | YES  |     | NULL    |                |
| Revenues   | int         | YES  |     | NULL    |                |
| EXPENSES   | int         | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

Add the record

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES(UTC_DATE(), "VRR EinzelTicket", 0, 0, 290);

check

mysql> SELECT * FROM tblSample;
+---------+------------+------------------+-----------+----------+----------+
| id_item | added_date | Item             | is_credit | Revenues | Expenses |
+---------+------------+------------------+-----------+----------+----------+
|       1 | 2020-03-03 | VRR EinzelTicket |         0 |        0 |      290 |
+---------+------------+------------------+-----------+----------+----------+
1 row in set (0.01 sec)

Change a value in record (row)

mysql> select * from tblSample;
+---------+------------+-----------------------------+-----------+----------+----------+
| id_item | added_date | Item                        | is_credit | Revenues | Expenses |
+---------+------------+-----------------------------+-----------+----------+----------+
|       1 | 2020-03-03 | VRR EinzelTicket            |         0 |        0 |      290 |
+---------+------------+-----------------------------+-----------+----------+----------+
|      ...| ...        | ...                         |       ... |      ... |      ... |
+---------+------------+-----------------------------+-----------+----------+----------+
|      37 | 2020-03-09 | Jumbo Bio OlijFoile EV      |         0 |        0 |       78 |
+---------+------------+-----------------------------+-----------+----------+----------+
37 rows in set (0.00 sec)

UPDATE tblSample SET Item = "BIO Spaghetti" WHERE id_item = 37;

mysql> select * from tblSample;
+---------+------------+-----------------------------+-----------+----------+----------+
| id_item | added_date | Item                        | is_credit | Revenues | Expenses |
+---------+------------+-----------------------------+-----------+----------+----------+
|       1 | 2020-03-03 | VRR EinzelTicket            |         0 |        0 |      290 |
+---------+------------+-----------------------------+-----------+----------+----------+
|      ...| ...        | ...                         |       ... |      ... |      ... |
+---------+------------+-----------------------------+-----------+----------+----------+
|      37 | 2020-03-09 | BIO Spaghetti               |         0 |        0 |       78 |
+---------+------------+-----------------------------+-----------+----------+----------+
37 rows in set (0.00 sec)

Delete a record (column)

DELETE FROM tblSample WHERE id_item = 36;

Delete an table

DROP TABLE tblSample;

my - add data

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES("2020-03-02", "Bio Joghurt 0,1%", 0, 0, 79);

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES("2020-03-02", "Ja! MiWa Still", 0, 0, 19);

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES("2020-03-02", "Postwertzeichen", 0, 0, 80);

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES("2020-03-02", "OV-chipkaart", 0, 0, 750);

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES("2020-03-02", "McDonald's HCC HoneyM GrtMn", 0, 0, 1185);

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES("2020-03-03", "Jumbo Water Rood 1.5", 0, 0, 39);

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES("2020-03-03", "Lay's Chips Naturel", 0, 0, 93);

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES("2020-03-03", "Abrikozenvlaai half", 0, 0, 300);

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES("2020-03-03", "Bigking XXL Fries Large", 0, 0, 1000);

INSERT INTO tblSample (added_date, Item, is_credit, Revenues, Expenses)
VALUES("2020-03-02", "Mexican Hot&Spicy Pizza", 0, 0, 1000);

Sum the column values

mysql> SELECT SUM(Expenses) FROM tblSample;
+---------------+
| SUM(Expenses) |
+---------------+
|          3085 |
+---------------+
1 row in set (0.00 sec)

show data in some day

SELECT * from tblSample WHERE DATE(added_date) = '2020-03-02';

+---------+------------+-----------------------------+-----------+----------+----------+
| id_item | added_date | Item                        | is_credit | Revenues | Expenses |
+---------+------------+-----------------------------+-----------+----------+----------+
|       2 | 2020-03-02 | Bio Joghurt 0,1%            |         0 |        0 |       79 |
|       3 | 2020-03-02 | Ja! MiWa Still              |         0 |        0 |       19 |
|       4 | 2020-03-02 | Postwertzeichen             |         0 |        0 |       80 |
|       5 | 2020-03-02 | McDonald's HCC HoneyM GrtMn |         0 |        0 |     1185 |
|      10 | 2020-03-02 | OV-chipkaart                |         0 |        0 |      750 |
|      11 | 2020-03-02 | Mexican Hot&Spicy Pizza     |         0 |        0 |     1000 |
+---------+------------+-----------------------------+-----------+----------+----------+
6 rows in set (0.00 sec)

Change the name of a table

RENAME TABLE tblSample TO tblEuro;

Tips - clear the screen in mac

Option + command + L

Tips - copy a record

https://dba.stackexchange.com/questions/142414/easiest-way-to-duplicate-rows

Copy next line

+---------+------------+---------------------+-----------+----------+----------+
| id_item | added_date | Item                | is_credit | Revenues | Expenses |
+---------+------------+---------------------+-----------+----------+----------+
|      39 | 2020-03-09 | JMB Rasp Mozzarella |         0 |        0 |      145 |
+---------+------------+---------------------+-----------+----------+----------+


INSERT INTO tblEuro
  (added_date, Item, is_credit, Revenues, Expenses) 
SELECT
  "2020-03-12", Item, is_credit, Revenues, Expenses
FROM
  tblEuro
WHERE 
  id_item = 39;

LIMIT N

show the result only first 2 lines.

Tips: clear

mysql> system clear;