sql cookbook

Create and Alter user

CREATE USER '<USER_NAME>'@'localhost' IDENTIFIED BY 'password'; --CREATE USER WITH PASSWORD--
GRANT ALL ON *.* TO '<USER_NAME>'@'%' WITH GRANT OPTION; --GRANT USER PERMISSION--

ALTER TABLE

ALTER TABLE <TABLE_NAME> CHANGE <COLUMN1> <COLUMN2> <DATATYPE> --change column name--
ALTER TABLE <TABLE_NAME> DROP <COLUMN_NAME>
ALTER TABLE <TABLE_NAME> MODIFY <COLUMN_NAME> <DATA_TYPE> NOT NULL PRIMARY KEY

INSERT STATEMENT

존재하지 않는 것만 추가하기

INSERT INTO <TABLE> (<COLUMN1>, <COLUMN2>) 
	VALUES (<VALUE1>, <VALUE2>) 
	WHERE NOT EXISTS (
		SELECT <COLUMN> FROM <TABLE2> WHERE id=<value>
  )

SELECT STATEMENT

LIKE

SELECT * FROM <table_name> WHERE <COLUMN> LIKE "%<CONTENT>%";

ORDER BY

SELECT * FROM <TABLE_NAME> WHERE <CONDITION> ORDER BY <COLUMN1> DESC, <COLUMN2> ASC

LIMIT

SELECT * FROM <TABLE> LIMIT <INDEX>, <NUMBER>

GROUP BY

SELECT u.name, MAX(c.price)
FROM user u LEFT JOIN car c ON u.id = c.user_id
WHERE u.id = 4;
GROUP BY u.name

PROBLEM SOLVING

where 절 없이 업데이트 실패하는 경우

SET SQL_SAFE_UPDATES = 0;