Skip to content

Latest commit

 

History

History
139 lines (114 loc) · 2.67 KB

1. Basic Query.md

File metadata and controls

139 lines (114 loc) · 2.67 KB

Basic Query (SELECT)

  • SELECT (DISTINCT)
  • WHERE
  • ORDER BY
  • NULL, IS NOT NULL
  • TOP, LIMIT
  • MIN(), MAX()
  • COUNT(), AVG(), SUM()
  • LIKE
  • [charlist] Wildcard
  • IN, BETWEEN

SELECT * FROM

SELECT DISTINCT column FROM table;

SELECT COUNT(DISTINCT column) FROM table;

WHERE

SELECT * FROM table
WHERE NOT column='???';

WHERE NOT column='a' AND NOT column='b';

ORDER BY

SELECT * FROM table
ORDER BY column1, column2

ORDER BY column1 ASC, column2 DESC;

NULL & IS NOT NULL

SELECT column1, column2 FROM table WHERE column2 IS NULL;

SELECT column1, column2 FROM table WHERE column2 IS NOT NULL;

TOP & LIMIT

SELECT TOP 3 * FROM table;

SELECT * FROM table
LIMIT 3;

MIN() & MAX()

SELECT MIN(column) FROM table;

SELECT MAX(column) FROM table;

COUNT() & AVG() & SUM()

SELECT COUNT(column) FROM table;

SELECT AVG(column) FROM table;

SELECT SUM(Quantity) FROM table;

LIKE

SELECT * FROM table
WHERE column LIKE 'a%';

a로 시작하는 것


SELECT * FROM table
WHERE column LIKE '%a';

a로 끝나는 것


SELECT * FROM table
WHERE column LIKE '%or%';

중간 어디든 or이 포함되어 있는 것


SELECT * FROM table
WHERE column LIKE '_a%';

a가 두번째에 있는 것


SELECT * FROM table
WHERE column LIKE 'a_%_%';

a로 시작하고 최소 3글자 이상


SELECT * FROM table
WHERE column LIKE 'a%o';

a로 시작하고 o로 끝나는 것


SELECT * FROM table
WHERE column NOT LIKE 'a%';

a로 시작하지 않는 것

[charlist] Wildcard

SELECT * FROM table
WHERE column LIKE '[bsp]%';

SELECT * FROM table
WHERE column LIKE '[a-c]%';

SELECT * FROM table
WHERE column LIKE '[!bsp]%';

SELECT * FROM table
WHERE column NOT LIKE '[bsp]%';

IN

SELECT * FROM table
WHERE column IN ('a', 'b', 'c');

SELECT * FROM table
WHERE column NOT IN ('a', 'b', 'c');

SELECT * FROM table1
WHERE column IN (SELECT column FROM table2);

BETWEEN

SELECT * FROM table
WHERE column BETWEEN 10 AND 20;

SELECT * FROM table
WHERE column NOT BETWEEN 10 AND 20;

SELECT * FROM table
WHERE column BETWEEN #07/04/1996# AND #07/09/1996#;

SELECT * FROM table
WHERE column BETWEEN 'text1' AND 'text2'
ORDER BY column;

BETWEEN with IN

SELECT * FROM table
WHERE (column1 BETWEEN 10 AND 20) AND NOT column2 IN (1, 2, 3);

EXISTS

SELECT l.languageName, u.name
FROM users u
INNER JOIN languages l ON u.language_id = l.id
WHERE EXISTS (SELECT u.name FROM users u WHERE u.name = 'Jo');