mysql cheat-sheet

·2021년 11월 25일
0

cheet-sheet

목록 보기
1/1
post-thumbnail
// 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

profile
새로운 것에 관심이 많고, 프로젝트 설계 및 최적화를 좋아합니다.

0개의 댓글