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/
