[WEB] 웹 개발을 위한 SQL 기본 정리

1. SQL Select

 

 

 

2. SQL Where

- 아닌 것 모두 찾기 : NOT

ex) SELECT * FROM Customers

WHERE NOT City = "Berlin";

 

- 조건 여러개 : AND / OR

ex) SELECT * FROM Customers

WHERE City = 'Berlin'

OR City = 'London';

 

 

 

3. SQL Order By

- 내림차순 : DESC

ex) SELECT * FROM Customers

ORDER BY City DESC;

 

- 정렬 조건 여러개

ex) SELECT * FROM Customers

ORDER BY Country, City; 

 

 

 

4. SQL Insert

- INSERT INTO [table name] ( [Column names] ) VALUES ( [value]);

ex) INSERT INTO Customers (

CustomerName,

Address,

City,

PostalCode,

Country)

VALUES (

'Hekken Burger',

'Gateveien 15',

'Sandnes',

'4306',

'Norway');

 

 

 

 

 

5. SQL Null

- NULL인지 아닌지 : IS NULL / IS NOT NULL

ex) SELECT * FROM Customers

WHERE PostalCode IS NULL;

 

 

 

 

6. SQL Update

- UPDATE [table name] SET [column name] = [new value];

ex) UPDATE Customers

SET City = 'Oslo',

Country = 'Norway'

WHERE CustomerID = 32;

 

 

 

 

7. SQL Delete

- DELETE FROM [table name]

ex) DELETE FROM Customers

WHERE CustomerID = 32;

 

 

 

 

8. SQL Functions

- 최소값 / 최대값 : MIN / MAX

ex) SELECT MAX(Price)

FROM Products;

 

- COUNT

ex) SELECT COUNT(*)

FROM Products

WHERE Price = 18;

 

- 평균  : AVG

ex) SELECT AVG(Price)

FROM Products;

 

- 합 : SUM

 

 

 

 

9. SQL Like

- LIKE

// Start with a

ex) SELECT * FROM Customers

WHERE City LIKE "a%";

// End with a

ex) SELECT * FROM Customers

WHERE City LIKE "%a";

// Contains a

ex) SELECT * FROM Customers

WHERE City LIKE "%a%";

// Start with "a" and End with "b"

ex) SELECT * FROM Customers

WHERE City LIKE "a%b";

 

-NOT LIKE

ex) SELECT * FROM Customers

WHERE City NOT LIKE 'a%';

 

 

 

 

 

10. SQL Wildcards

- 한글자 처리 : _

ex) //second letter is a

SELECT * FROM Customers

WHERE City LIKE '_a%';

 

- OR 문자 선택 : [문자열]

ex) //first letter is a or c or s

SELECT * FROM Customers

WHERE City LIKE '[acs]%';

 

- 특정 구간 문자 선택 : [문자-문자]

ex) //first letter is a or c or s

SELECT * FROM Customers

WHERE City LIKE '[a-f]%';

 

- [!문자열]

ex) //first letter is not a or c or s

SELECT * FROM Customers

WHERE City LIKE '[!acs]%';

 

 

 

 

 

11. SQL In

- IN

ex) SELECt * FROM Customers

WHERE Country IN ('Norway', 'France');

 

- NOT IN

ex) SELECt * FROM Customers

WHERE Country NOT IN ('Norway', 'France');

 

 

 

 

12. SQL Between : 숫자 뿐 문자도 가능. 

- BETWEEN

- NOT BETWEEN 

ex) SELECT * FROM Products

WHERE Price NOT BETWEEN 10 AND 20;

 

 

 

 

13. SQL Alias

- AS

ex) SELECT CustomerName, Address, PostalCode AS Pno

FROM Customers;

 

 

 

 

 

14. SQL Join

- FROM의 테이블의 값 모두 : LEFT JOIN .... ON ....

- 조인하는 두 테이블의 모든 값을 적용 : INNER JOIN .... ON ....

- JOIN의 테이블의 값 모두 : RIGHT JOIN ... ON ...

ex) SELECT *

FROM Orders

RIGHT JOIN Customers

ON Order.CustomerID = Customers.CustomerID;

 

 

 

 

15. SQL Group By

- GROUP BY

ex) SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

ORDER BY COUNT(CustomerID) DESC;

 

 

 

 

 

 

16. SQL Database

- CREATE

ex) CREATE DATABASE testDB;

 

- DROP

ex) DROP DATABASE testDB;

 

- CREATE TABLE

ex) CREATE TABLE Persons (

PersonID int, LastName varchar(255), FirstName avrchar(255)

);

 

- DROP TABLE

ex) DROP TABLE Persons;

 

- TRUNCATE TABLE

ex) TRUNCATE TABLE Persons;

 

- ALTER TABLE

ex) // ALTER TABLE [테이블명] ADD [컬럼명] [컬럼type]

ALTER TABLE Persons

ADD Birthday DATE;

ex) // ALTER TABLE [테이블명] DROP COLUMN [컬럼명]

ALTER TABLE Persons

DROP COLUMN Birthday;