[DB]데이터 다루기

-- 특정 패턴 조회하기

SELECT * FROM [테이블명] WHERE text LIKE '%아름다운%';

SELECT * FROM [테이블명] WHERE number BETWEEN 1 and 3;

SELECT * FROM [테이블명] WHERE text IN (1, 2, 3);

SELECT * FROM [테이블명] WHERE text IS NULL;

-- 데이터 가공하기 SELECT 1 - 2 + 2 * 3;

SELECT MOD(10, 3) SELECT ROUND(30.60, 1)

SELECT CONCAT('아름다운', '풍경')

SELECT SUBSTRING('20230215', 3, 1)

SELECT CURDATE(); 
SELECT CURTIME();

-- 데이터 집계하기

SELECT COUNT(*) FROM [테이블명]

SELECT DISTINCT [열명] FROM [테이블명]

SELECT SUM([열명]) FROM [테이블명]

-- 쿼리 중첩하기
SELECT MIN(a) FROM sample;
DELETE FROM sample WHERE a = (SELECT MIN(a) FROM sample);
SELECT (SELECT COUNT(*) FROM [Customers] WHERE Country = 'Germany') AS GermanyCount,
       (SELECT COUNT(*) FROM [Customers] WHERE Country = 'Mexico') AS MexicoCount;

-- 서브쿼리가 부모쿼리와 연관된 경우
DELETE FROM [Customers] WHERE EXISTS (SELECT * FROM [Orders] WHERE OrderDate >= "1996-07-08");

-- 여러테이블 다루기: 합집합
SELECT Country FROM [Customers] UNION SELECT Country FROM [Suppliers] ORDER BY Country;
SELECT Country FROM [Customers] UNION ALL SELECT Country FROM [Suppliers] ORDER BY Country;

-- 여러테이블 다루기: 내부결합
SELECT * FROM [Products], [Employees] WHERE [Employees].EmployeeID = [Products].SupplierID;
SELECT * FROM [Products] INNER JOIN [OrderDetails] ON Products.ProductID = OrderDetails.ProductID;

-- 여러테이블 다루기: 외부결합
SELECT * FROM [Products] LEFT JOIN [Employees] ON [Employees].EmployeeID = [Products].SupplierID;
SELECT * FROM [Employees] RIGHT JOIN [Products] ON [Employees].EmployeeID = [Products].SupplierID;

참고: https://365kim.tistory.com/102