// List databases :
Show databases;
// Creating database :
CREATE DATABASE <database_name>;
CREATE SCHEMA <schema_name>;
// ex) CREATE DATABASE soup_store;
// Remove database :
DROP DATABASE <database_name>;
// Use database :
USE <database_name>;
// Check current using database :
SELECT database();
// Create table :
CREATE TABLE <database_name> (
<column_name> data_type,
<column_name> data_type,
...
);
// ex) CREATE TABLE cats (
// name VARCHAR(100),
// age INT
// );
// Show table :
SHOW TABLES;
// Show table with column & row :
DESC <table_name>;
// Dropping table :
DROP TABLE <table_name>;
// Read data :
SELECT * FROM <table_name>;
// Read data (using where) :
SELECT * FROM <table_name> WHRER ~;
// Alias :
SELECT * <column_name> AS <Alias_name>
FROM <table_name>;
// Create data :
INSERT INTO table_name (<column_name>)
VALUES (<value>);
// Create datas (múltiple) :
INSERT INTO table_name (<column_name>, <column_name>, ...)
VALUES (<value>, <value>, ...),
(<value>, <value>, ...),
(<value>, <value>, ...);
// View warnings(errors) :
SHOW WARNINGS;
// Setting not NULL :
CREATE TABLE <table_name> (
<column_name> <data_type> NOT NULL
);
// Setting Default :
CREATE TABLE <table_name> (
<column_name> <data_type> DEFAULT <default_value>
);
// Define primary key :
CREATE TABLE unique_cats (
<column_name> <data_type>, PRIMARY KEY (column_name)
);
// Define primary key with adding AUTO_INCREMENT :
CREATE TABLE unique_cats (
<column_name> <data_type> NOT NULL AUTO_INCREMENT,
PRIMARY KEY (column_name));
// Update data :
UPDATE <table_name> SET ~ WHERE ~;
// DELETE data :
DELETE <table_name> WHERE ~;
// Sql file execution :
source <file_name.sql>
// CONCAT() :
SELECT CONCAT (
<column_name>,
<another_column_name>,
...
) FROM <table_name> ~;
// CONCAT_WS (with separator) :
SELECT CONCAT_WS (
'<separator>',
<column_name>,
<another_column_name>,
...
) FROM <table_name> ~;
// SUBSTRING() :
SELECT SUBSTRING(SUBSTR)('<value>', <start_number>)
FROM <table_name> ~;
SELECT SUBSTRING(SUBSTR)('<value>', <start_point>, <end_point>)
FROM <table_name> ~;
// REPLACE() :
SELECT REPLACE(<column_name>, '<current_value>','<changed_value>')
FROM <table_name> ~;
// REVERSE() :
SELECT REVERSE('<value>')
FROM <table_name> ~;
// CHAR_LENGTH() :
SELECT CHAR_LENGTH('<value>')
FROM <table_name> ~;
// UPPER() :
SELECT UPPER('<value>');
// LOWER() :
SELECT LOWER('<value>');
// DISTINCT :
SELECT DISTINCT <column_name>, …
FROM <table_name>;
// ORDER (sorting) :
SELECT <column_name>,…
FROM <table_name>
ORDER BY <column_name>, … (DESC);
// LIMIT :
SELECT <column_name>, …
FROM <table_name>
LIMIT <limit_number>;
SELECT <column_name>, …
FROM <table_name>
LIMIT <start_point>, <end_point>;
// LIKE :
SELECT ~ FROM ~
WHERE <column_name>
LIKE <wild-card>;
// COUNT() :
SELECT COUNT(<column_name>)
FROM <table_name> ~;
// GROUP BY :
SELECT <column_name>
FROM <table_name>
GROUP BY <column_name>;
// MIN/MAX() :
SELECT MIN/MAX(<column_name>)
FROM <table_name> ~;
// SUM() :
SELECT SUM(<column_name>)
FROM <table_name>;
// AVG() :
SELECT AVG(<column_name>)
FROM <table_name>;
// CURDATE() :
CURDATE();
// CURTIME() :
CURTIME();
// NOW() :
NOW();
// DAY() :
DAY(date);
// DATNAME() :
DAYNAME(date);
// DAYOFWEEK() :
DAYOFWEEK(date);
// DAYOFYEAR() :
DAYOFYEAR(date);
// DATE_FORMAT():
DATE_FORMAT(date, format);
ref.(https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format)
// DATEDIFF() :
DATEDIFF(expr1, expr2);
// DATE_ADD():
DATE_ADD(date, INTERVAL expr_unit);
ref.(https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add)
Ref2. =You can use shortcut (+/-)
ex) DATE_ADD(date, INTERVAL expr_unit) => date + INTERVAL expr_unit
// NOT LIKE :
SELECT ~ FROM ~
WHERE <column_name>
NOT LIKE <wild-card>;
// AND :
SELECT ~ FROM ~
WHERE ~
AND ~ ;
// OR :
SELECT ~ FROM ~
WHERE ~
OR ~ ;
// BETWEEN :
SELECT ~ FROM ~
WHERE <column_name>
BETWEEN <value> AND <value>;
// NOT BETWEEN :
SELECT ~ FROM ~
WHERE <column_name>
NOT BETWEEN <value> AND <value>;
// CAST() :
SELECT ~ FROM ~
CAST(<current_data> AS <changed data>);
// IN :
SELECT ~ FROM ~
WHERE <column_name>
IN (value, value, value, …);
// NOT IN :
SELECT ~ FROM ~
WHERE <column_name>
NOT IN (value, value, value, …);
// CASE :
SELECT ~ FROM ~
CASE
WHEN ~ THEN <true_value>
ELSE <else_value> END AS ~
SELECT ~ FROM ~
IF(~ , <true_value>, <else_value>) AS ~
// FOREIGN KEY :
FOREIGN KEY (<column_name>) REFERENCES <table_name>(<ref_data>);
// CROSS JOIN :
SELECT ~ FROM <table_name1>, <table_name2>, …
// IMPLICIT INNER JOIN :
SELECT ~ FROM <table_name1>, <table_name2>
WHERE
<table_name1>.<PRIMARY KEY> = <table_name2>.<FOREIGN KEY>;
// EXPLICIT INNER JOIN :
SELECT ~ FROM <table_name1>
JOIN <table_name2>
ON <table_name1>.<PRIMARY KEY> = <table_name2>.<FOREIGN KEY>;
// LEFT JOIN :
SELECT ~ FROM <table_name1>
LEFT JOIN <table_name2>
ON <table_name1>.<PRIMARY KEY> = <table_name2>.<FOREIGN KEY>;
// RIGHT JOIN :
SELECT ~ FROM <table_name1>
RIGHT JOIN <table_name2>
ON <table_name1>.<PRIMARY KEY> = <table_name2>.<FOREIGN KEY>;
// IFNULL() :
IFNULL(function(), <changed_value>);
// ON DELETE CASCADE :
FOREIGN KEY (<column_name>)
REFERENCES <table_name>(<ref_data>)
ON DELETE CASCADE;
// ROUND() :
SELECT ROUND(<column_name>) ~
// JOIN (MANY TO MANY) :
SELECT ~ FROM <column_name 1>
JOIN <column_name 2>
ON ~
JOIN <column_name 3>
ON ~
// HAVING :
SELECT ~ FROM <column_name 1>
JOIN <column_name 2>
ON ~
HAVING ~
// TRIGGER :
DELIMITER
$$
CREATE TRIGGER <trigger_name> <trigger_time> <table_event>
ON <table_name>
FOR EACH ROW
BEGIN
IF ~ THEN SIGNAL SQLSTATE ‘<state_number>’
SET MESSAGE_TEXT = ‘<message>';
END IF;
END;
$$
DELIMITER;
v1.0