[Database][MariaDB]MariaDB Tutorial with Command, MariaDB 튜토리얼, 예제

0. Login

1. Create and Showing

2. CRUD

3. Advanced

0. Login

- Docker 사용시 container 실행

docker exec -it mariadb /bin/bash

- MariaDB login

# root
mysql -u root -p

# other account
mysql -u account -p

1. Create or Select and Showing

- 상태확인

status

- Create database

CREATE DATABASE database_name;

- List of Databases

SHOW DATABASEs;

- Select database

USE database_name;

# example
USE project_pb

- Create Table

CREATE TABLE tableName (columnName columnType);

CREATE TABLE Book(  
id INT NOT NULL AUTO_INCREMENT,  
name VARCHAR(100) NOT NULL,  
PRIMARY KEY (id));

CREATE TABLE Price(  
id INT NOT NULL AUTO_INCREMENT,  
price float NOT NULL,  
PRIMARY KEY (id));

- Showing Table

DESC TableName;

# example
DESC cbc;

2. CRUD

- INSERT

# single record
INSERT INTO cbc
(order_id, RBC_Size, WBC_Number, Platelet_Number)
VALUES('o123456', 12,10,5);

# multiple records
INSERT INTO cbc
(order_id, RBC_Size, WBC_Number, Platelet_Number)
VALUES
('o123456', 12,10,5),
('o123457', 1,21,9),
('o123458', 15,12,20),
('o123459', 18,5,50),
('o123452', 20,1,56),
('o123453', 21,12,2);

- SELECT

SELECT * FROM cbc;

# single record

# multiple records

- UPDATE

UPDATE tableName SET field=newValue, field2=newValue2,...  
[WHERE ...]

# single change

UPDATE cbc
SET RBC_SIze = 51
WHERE order_id = 'o123453';

# multiple changes

UPDATE cbc
SET RBC_Size = 40, WBC_Number=11
WHERE order_id = 'o123452';

- DELETE

DELETE FROM tableName  
[WHERE condition(s)]  
[ORDER BY exp [ ASC | DESC ]]  
[LIMIT numberRows];

DELETE FROM cbc
WHERE order_id = 'o123457';

- WHERE

SELECT * FROM cbc WHERE RBC_Size <20;

SELECT * FROM cbc WHERE RBC_Size <20 AND WBC_Number <20;

SELECT * FROM cbc WHERE RBC_Size <20 OR Platelet_Number >50;

- LIKE

SELECT field_1, field_2,... FROM tableName1, tableName2,...  
WHERE fieldName LIKE condition;

# '%'

# o로 시작

SELECT order_id FROM cbc
WHERE order_id LIKE 'o%';

# 6으로 끝

SELECT order_id FROM cbc
WHERE order_id LIKE '%6';

# 234 포함

SELECT order_id FROM cbc
WHERE order_id LIKE '%234%';

SELECT order_id FROM cbc 
WHERE order_id NOT LIKE '%234%';

# '_'

SELECT RBC_Chromicity FROM cbc 
WHERE RBC_Chromicity LIKE '1__';

SELECT RBC_Chromicity FROM cbc 
WHERE RBC_Chromicity LIKE '_1_';

- ORDER BY

SELECT expression(s)  
FROM tables  
[WHERE condition(s)]  
ORDER BY exp [ ASC | DESC ];

- DISTINCT

SELECT DISTINCT expression(s)  
FROM tableName  
[WHERE condition(s)];

SELECT DISTINCT order_id FROM cbc;

3. Advanced

- Function

CREATE 
[ DEFINER = { CURRENT-USER | username } ] 
FUNCTION function-name [(parameter datatype [, parameter datatype]) ]
RETURNS datatype [LANGUAGE SQL
                        | DETERMINISTIC
                        | NOT DETERMINISTIC
                        | {CONTAINS SQL 
                          | NO SQL
                          | READS SQL DATA
                          | MODIFIES SQL DATA}
                        | SQL SECURITY {DEFINER | INVOKER}
                        | COMMENT 'comment'

BEGIN

   declaration-section

   executable-section

END;

- JOIN

SELECT columns  
FROM table-1   
INNER JOIN table-2  
ON table-1.column = table-2.column;

# inner join

SELECT columns  
FROM table-1   
INNER JOIN table-2  
ON table-1.column = table-2.column;  

# example
SELECT book.name, price.price 
FROM book   
INNER JOIN price 
ON book.id = price.id;

# left join

SELECT columns  
FROM table-1  
LEFT [OUTER] JOIN table-2  
ON table-1.column = table-2.column;

# example
SELECT book.name, price.price 
FROM book   
LEFT JOIN price 
ON book.id = price.id;

# right join

SELECT book.name, price.price 
FROM book   
RIGHT JOIN price
ON book.id = price.id;

# example
SELECT book.name, price.price 
FROM book   
RIGHT JOIN price
ON book.id = price.id;

ref: https://www.guru99.com/mariadb-tutorial-install.html

https://mariadb.com/kb/en/mysql-command-line-client/