2023.04.17 57일차 SpringBoot
57일차 SpringBoot
서버 - db로 일시키는 쿼리 sql을 보여주고 이삳.
서버는 잘가지고와서 가공하고 view로 컨트롤러가 건네주는 방법을 배우고 잇다.
5. order by
결과의 순서를 정할때 사용한다.
ORDER BY 기준컬럼명 오름OR내림차순
오름차순 ASC 내림차순 DESC 오름차순은 생략할 수 있다.
순서를 정렬할때 컬럼명을 적어도되고 컬럼 INDEX를 사용해도 된다.
SELECT CustomerId, CustomerName, ContactName -- colum명들
From Customers -- table
Where CustomerID < 10 -- 선택할 records의 조건
ORDER BY ContactName ASC;이름순 정렬
SELECT * From Customers
ORDER BY CustomerName DESC;나라순 정렬
SELECT * From Customers
ORDER BY Country ASC;모든 직원 생년월일 순 조회 (어린 직원이 먼저 조회)
SELECT * FROM Employees ORDER BY BirthDate DESC;모든 상품을 가격순으로 조회(낮은 가격이 먼저 조회)
SELECT * FROM Products ORDER BY PRICE;
SELECT * FROM Products ORDER BY 6;컬럼 INDEX 사용해서 고객을 도시 이름 순 정렬 조회 5번 City
SELECT * FROM Customers ORDER BY 5;컬럼인덱스 사용할때 주의할점은 모든 컬럼조회가 아니라면 기존 테이블 기준으로하면안된다.
조회하는 컬럼의 개수 기준 INDEX를 사용해야한다.
여러 컬럼을 기준으로 정렬하는 것도 가능하다.
첫번째 기준으로 정렬한후 다음 정렬기준으로 정렬한다.
SELECT * FROM Customers ORDER BY Country, City;여러 컬럼 기준으로 정렬시 컬럼당 오름차순 내림차순 지정가능
SELECT * FROM Customers ORDER BY Country ASC, City DESC;
SELECT * FROM Customers ORDER BY Country DESC, City ASC;
SELECT * FROM Customers ORDER BY Country DESC, City DESC;상품을 카테고리(오름차순) 가격(내림차순)으로 정렬조회
SELECT * FROM Products ORDER BY CategoryID ASC, Price DESC;6. LIMIT
조회되는 특정 레코드의 개수를 제한 한다.
SELECT * FROM Customers LIMIT 5; -- 5개 레코드만가격이 높은 상품 3개 조회
SELECT * FROM Products ORDER BY Price DESC LIMIT 3;2번 카테고리 상품 중 비싼 것 조회
SELECT * FROM Products
WHERE CategoryID = 2
ORDER BY Price DESC
LIMIT 3;나이 많은 직원 3명 조회
SELECT * FROM Employees ORDER BY BirthDate LIMIT 3;브라질 고객 중 아무나 3명 조회
SELECT * FROM Customers WHERE Country = 'Brazil' LIMIT 3;3번 카테고리 상품중 가격이 싼 상품 2개 조회
SELECT * FROM Products WHERE CategoryID = 3 ORDER BY Price ASC LIMIT 2;LIMIT n LIMIT에 숫자가 하나면 위에서 부터 n개를 이미한다.
LIMIT n, m LIMIT에 숫자가 두개라면 n번부터 m개를 의미한다.
주의할점은 0번부터 record index가 시작한다는 것을 알아야한다.
SELECT * FROM Customers ORDER BY CustomerID LIMIT 3; -- 1~3고객
SELECT * FROM Customers ORDER BY CustomerID LIMIT 0, 3; -- 0번부터 3개 1~3번고객
SELECT * FROM Customers ORDER BY CustomerID LIMIT 3, 3; -- 3번부터 3개 4~6번고객 두 번째로 나이가 많은 직원 조회ALTER
SELECT * FROM Employees ORDER BY BirthDate LIMIT 1, 1;2번 카테고리 상품 중 두번째로 가격이 비싼 상품 조회
SELECT * FROM Products WHERE CategoryID = 2 ORDER BY Price DESC LIMIT 1, 1;7. 내장 함수(집계함수)
MIN MAX COUNT AVG SUM 기본적으로 제공해주는 함수이다.
버전마다 다르니 알아만 두자.
7.1 COUNT : 레코드 수 리턴
SELECT COUNT(CustomerID) FROM Customers; -- 91
SELECT COUNT(CustomerName) FROM Customers; -- 91
SELECT COUNT(Country) FROM Customers; -- 91
SELECT COUNT(CustomerID) FROM Customers WHERE Country = 'Brazil'; -- 97.2 DISTINCT : 중복제거
SELECT Country from Customers; -- 91개
SELECT DISTINCT Country from Customers; -- 21개중복된 나라 제거하고 개수 세기
SELECT COUNT(DISTINCT Country) from Customers; 전체 직원 수
SELECT Count(*) FROM Employees;상파울로에 사는 고객 수
SELECT Count(CustomerID) FROM Customers WHERE City = 'São Paulo';공급자(suppliers)가 있는 나라 수(중복제거)
SELECT COUNT(DISTINCT Country) FROM Suppliers;7.3 MIN MAX : 가장작은값, 큰값
가장 나이 많은 직원의 생년월일
SELECT MIN(BirthDate) FROM Employees;가장 어린 직원의 생년월일
SELECT MAX(BirthDate) FROM Employees;3번 카테고리에 가장 비싼 상품의 가격
SELECT MAX(Price) FROM Products WHERE CategoryID = 3; -- 815번 카테고리에 가장 싼 상품의 가격
SELECT MIN(Price) FROM Products WHERE CategoryID = 5; -- 77.4 AVG(평균), SUM(합계)
SELECT SUM(Price) FROM Products; -- 2222.71
SELECT AVG(Price) FROM Products; -- 28.8663645번 카테고리 상품 값의 합
SELECT SUM(Price) FROM Products WHERE CategoryID = 5; -- 141.751번 카테고리 상품 값의 평균
SELECT AVG(Price) FROM Products WHERE CategoryID = 1; -- 37.979167 8. GROUP BY
지금까지 배운 내장함수들을 집계함수라고 한다.
여러 행으로부터 하나의 결과값을 반환하는 함수이다.
이 집계함수들을 활용하는 GROUP BY키워드가 있다.
소계 그룹별로 모아서 리턴하고 싶을때 사용한다.
GROUP BY에 사용한 것을 SELECT절에 추가하면 같이 볼 수 있다.
소계한 컬럼과 상관없는 컬럼을 추가하면 오류가 날 수 있어서 작성하지 않는게 좋다.
카테고리별로 가격의 합
SELECT CategoryID, SUM(PRICE) FROM Products GROUP BY CategoryID;나라별 고객 수를 나라 이름 역순으로 조회
SELECT Country, COUNT(CustomerID) FROM Customers GROUP BY Country ORDER BY Country DESC;고객이 많은 나라 순 정렬
SELECT Country, COUNT(CustomerID) FROM Customers GROUP BY Country ORDER BY 2 DESC;고객이 가장 많은 나라 조회
SELECT Country, COUNT(CustomerID) FROM Customers GROUP BY Country ORDER BY 2 DESC LIMIT 0, 1;카테고리별 상품의 평균
SELECT CategoryID, AVG(PRICE) FROM Products GROUP BY CategoryID;카테고리별 가장 비싼 상품의 가격
SELECT CategoryID, MAX(PRICE) FROM Products GROUP BY CategoryID;카테고리별 가장 싼 상품의 가격
SELECT CategoryID, MIN(PRICE) FROM Products GROUP BY CategoryID;상품의 가격의 평균이 가장 높은 카테고리 조회
SELECT CategoryID, AVG(PRICE) FROM Products GROUP BY CategoryID ORDER BY 2 DESC LIMIT 1; -- 54여러 컬럼으로 그루핑을 할 수 있다.
대그룹 - 소그룹
SELECT Country, City, COUNT(Customerid) FROM Customers GROUP BY Country, City;나라별 도시별 공급 자 수 조회하기
SELECT Country, City, COUNT(SupplierID) FROM Suppliers GROUP BY Country, City;9. alias(별칭)
집계함수를 사용하다보니 컬럼명이 너무 길어지는 경우가 있다.
조회하는 컬럼에 대해 별칭을 붙일 수 있다.
데이터를 쉽게 가공하기 위해서 별칭을 붙이는 것이 좋다.
결과 테이블에서의 컬럼명을 변경하는 것이다.
컬럼명 AS 별칭 AS는 생략이 가능하다.
SELECT CustomerID AS ID, CustomerName AS name FROM Customers;
SELECT CustomerID ID, CustomerName name FROM Customers;별칭을 지엇다면 다른 구절에서 사용할 수도 있다.
SELECT CustomerID ID, CustomerName name FROM Customers ORDER BY CustomerID;
SELECT CustomerID ID, CustomerName name FROM Customers ORDER BY ID;굳이 keyward를 별칭으로 사용하고 싶다면 backtick(백틱)을 사용하자.
SELECT COUNTRY, CustomerName AS `FROM` FROM Customers;그래서 이것을 방지하기 위해 모든 곳에 백틱을 쓰는 경우도 있다.
이런식으로 되어 있을 경우 구분을 하기위해 알아 두자.
SELECT `COUNTRY`, `CustomerName` AS `FROM` FROM `Customers`;10. HAVING
HAVING절은 집계 함수의 조건 설정할때 사용한다.
WHERE절은 특정 레코드를 거르는 조건 문이다.
조회결과를 거르는게 아니라 그루핑 하기전에 원본 테이블의 레코드를 거르는데 사용하는 것이다.
그래서 그루핑한 후 조회결과를 조건처리하기 위해서는 HAVING절을 사용해야한다.
나라별로 고객수가 5명 이상인 나라 조회
SELECT Country, COUNT(Customerid) numOfCustomer FROM Customers GROUP BY Country HAVING numOfCustomer > 5;상품의 평균 가격이 30 달러 이상인 카테고리 조회하기
SELECT CategoryID, AVG(Price) PAVG FROM Products GROUP BY CategoryID HAVING PAVG >= 30.00;카테고리별 상품의 최고 가격이 100달러 이상인 카테고리 조회하기
SELECT CategoryID, MAX(Price) PMAX FROM Products GROUP BY CategoryID HAVING PMAX >= 100;11. MYSQL 연산자
11.1 산술연산자
+, -, * ,/ , % 등 산술연산자를 사용할 수 있다.
SELECT ProductName, Price * 1300 FROM Products;
SELECT ProductName, Price / 100 FROM Products;
SELECT ProductName, Price + 100 FROM Products;
SELECT ProductName, Price - 100 FROM Products;11.2 문자열 연결
+는 산술연산이기때문에 문자열연결이 불가능하다.
연산자가 따로 있지 않고 연결하는 함수를 사용해야한다.
CONCAT()
CONCAT()함수에는 여러 문자열을 넣을 수 있으니 적절히 조합해서 사용할 수 있다.
SELECT CONCAT(City, ',', Country) cityOfCountry FROM Customers;
SELECT CONCAT(City, ' ', Country) cityOfCountry FROM Customers;직원 이름을 "fristName, LastName" 형식으로 조회하기
SELECT CONCAT(firstName, ', ', lastName) FROM Employees;원화(*1300)로 10만원 이상인 상품들 조회하기
SELECT * FROM Products WHERE Price >= 100000/1300;
SELECT ProductId, ProductName, SupplierID, CategoryID, Price dollor, Price * 1300 won FROM Products WHERE Price >= 100000 / 1300;11.3 부분 문자열 : substring
MYSQL에서 작동하고 다른 DB에선 작동안할 수도 있다.
substring(문자열, 시작위치, 길이) 자바는 0번 index부터이지만 여기서는 1번 index부터 시작한다.
첫번째 파라미터 : 원본 문자열
두번째 파라미터 : 시작 인덱스
세번째 파라미터 : 길이
고객 이름 앞에서 3글자만 조회하기
SELECT substring(CustomerName, 1, 3) From Customers;고객테이블에서 고객명, 나라이름(앞 3글자만 )조회
SELECT CustomerName, substring(Country, 1, 3) Country FROM Customers;여러 연산자 함수 집계함수 등등이 있다. 컬럼명이 길어지니 별칭을 줄 수 있다.
테이블에서 조회햇으니 원래 데이터를 삭제 수정 등등할 수 있다.
12. insert
insert : 테이블에 새 레코드 입력
INSERT INTO 테이블명(컬럼명들), VALUES(값들);
컬럼명과 값들을 개수가 맞게 순서가 맞게 설정해줘야한다.
INSERT INTO Customers(Customerid, CustomerName, Contactname, Address, city, postalcode, country)
Values(92, '박지성', '두개의심장', 'london', 'seoul', '1234567', 'Korea');모든 컬럼에 값을 다 넣는다면 컬럼명을 생략할 수 있다.
INSERT INTO Customers
Values(93, '차범근', '차붐', '프랑크푸르트', 'seoul', '123134', 'Korea');특정컬럼에만 값을 넣을때는 컬럼명을 작성해야한다.
INSERT INTO Customers(Customerid, CustomerName, city, country)
Values(94, '송태섭', 'Tokyo', 'Japan');surpplier테이블에 30번째 공급자 추가(모든열)
Suppier테이블에 31번째 공급자 Supplierid,suppliername, city, country
INSERT INTO Suppliers Values(30, 'park', 'chun', 'goyang', 'goyansi', '980105', 'Korea', '(010)123-457');
INSERT INTO Suppliers(Supplierid, suppliername, city, country) VALUES (31, 'KIM', 'seoul', 'Korea');13. NULL
값을 넣다보면 특정컬럼에만 값이 있고 어떤 컬럼에는 값이 없다.
이 표시를 NULL로 한다.
13.1 IS NULL
NULL인가를 물어보는 연산자이다.
'' 빈문자열과 NULL은 비교되니 확실히 작성해야한다.
SELECT * FROM Suppliers WHERE ContactName IS NULL;13.2 NOT NULL
NULL이 아닌가물어보는 연산자이다. IS NOT NULL이나 WHERE절 전체에 NOT을 붙여주면된다.
SELECT * FROM Suppliers WHERE NOT ContactName IS NULL;
SELECT * FROM Suppliers WHERE ContactName IS NOT NULL;
SELECT * FROM Suppliers WHERE NOT ContactName IS NULL;CUSTMERS 테이블에서 Address 컬럼이 null인 레코드 조회
SELECT * FROM Customers WHERE ADDRESS IS NULL;CUSTMERS 테이블에서 Address 컬럼이 null이 아닌 레코드 조회
SELECT * FROM Customers WHERE ADDRESS IS NOT NULL;13.3 집계함수 NULL제외
집계함수로 집계할때 NULL값 은 제외된다.
SELECT COUNT(CustomerID) FROM Customers; -- 94
SELECT COUNT(Address) FROM Customers; -- 93어떤게 있는지 없는지 모르겠다면 전체 개수를 조회하면된다.
SELECT COUNT(*) FROM Customers;공급자 테이블에서 Supplierid 컬럼의 값 수 구하기
공급자 테이블에서 ContactName 컬럼의 값 수 구하기
SELECT COUNT(SupplierID) FROM Suppliers; -- 31
SELECT COUNT(ContactName) FROM Suppliers; -- 30
SELECT SUM(Price) FROM Products WHERE CategoryID = 2; -- 276.75
SELECT AVG(Price) FROM Products WHERE CategoryID = 2; -- 23.062500명시적으로 값을 안넣으려면 NULL으로 INSERT하면된다.
INSERT INTO Products VALUES (78, 'cake', 12, 2, '조각', NULL);
INSERT INTO Products(Productid, ProductName, Supplierid, categoryid, unit)
VALUES (79, 'pizza', 12, 2, '조각');13.4 IFNULL
NULL을 0으로 판단할것인가 없는 값으로 판단할것인가
IFNULL은 NULL이면 다른 값으로 변경해주는 연산자이다.
IFNULL(파라미터1, 파라미터2) 파라미터1이 NULL이면 파라미터2값을 넣어준다.
SELECT ProductID, ProductName, SupplierID, CategoryID, UNIT, IFNULL(Price, 0) Price FROM Products ORDER BY 1 DESC;
SELECT AVG(Price) Average FROM Products WHERE CategoryID = 2; -- 23.0625
SELECT AVG(IFNULL(Price, 0)) Average FROM Products WHERE CategoryID = 2; -- 19.76고객 테이블에서 CustomerID, CustomerName, ContactName, Address조회하는데
ContactName이 NULL이면 'Anonymous'로 Address가 NULL이면 'Homeless'로 조회하기
SELECT CustomerID, CustomerName, IFNULL(ContactName, 'Anonymous') ContactName, IFNULL(Address, 'Homeless') Address FROM Customers;조회할때 걸러서 조회하는 것이지 실제 데이터가 바뀌는게 아님을 명심하자.
13.5 NULL 연산
NULL과의 연산은 모두 FALSE이다.
SELECT * FROM Products WHERE CategoryID = 2; -- 14개
SELECT * FROM Products WHERE CategoryID = 2 AND Price >= 25.00; -- 4개
SELECT * FROM Products WHERE CategoryID = 2 AND Price < 25.00; -- 10x 8개
SELECT * FROM Products WHERE CategoryID = 2 AND IFNULL(Price, 0) < 25.00; -- 10개14 DELETE
DELETE문은 테이블의 record를 지운다.
매우 주의해서 사용해야한다.
DELETE FROM 테이블명 WHERE 조건
DELETE FROM Customers -- 지울 테이블명
WHERE CustomerID = 94; -- 지울 레코드의 조건같은 WHERE절로 SELECT해보고 지울게 맞다면 실행시켜서 검증하면 조금 더 위험을 줄일 수 있다.
93번 고객 지우기
78번 상품 지우기
79번 상품 지우기
DELETE FROM Customers WHERE CustomerID = 93;
DELETE FROM Products WHERE ProductID = 78;
DELETE FROM Products WHERE ProductID = 79;사고 사례들이 굉장히 많으니 잘 보고 처리하자.
14. DELETE - 2
여러 레코드를 지울 거면 WHERE의 조건을 여러개 지정하도록 하면된다.
WHERE절을 지정하지 않으면 모든 레코드가 지워진다.
DELETE FROM Products WHERE CategoryID = 1; -- 12개 레코드 삭제
DELETE FROM Products;고객테이블에서 USA 인 회원 고객들 지우기
DELETE FROM Customers WHERE Country = 'USA';모든 직원 지우기
DELETE FROM Employees;15 UPDATE
UPDATE 레코드 수정하는 명령어이다.
DELETE만큼 위험하기 때문에 신중하게 작성해야한다.
UPDATE 테이블명 SET 변경시킬컬럼명 = 값 WHERE 조건;
UPDATE Customers -- 테이블명
SET CustomerName = '서태웅' -- 변경할 컬럼과 값
WHERE CustomerID = 1; -- 변경할 레코드 조건여러개의 컬럼을 변경하려면 ,로 구분해서 나열하면된다.
UPDATE Customers
SET CustomerName = '정대만', City= 'Seoul', Country = 'Korea'
WHERE CustomerID = 1; 1번 공급자의 이름 도시 나라 변경
UPDATE Suppliers SET SupplierName = 'park', city = 'goyang', Country = 'Korea' WHERE SupplierID = 1;역시나 조건을 지정하지 않으면 모든 레코드가 변경이 되니 주의해야한다.
UPDATE Suppliers SET ContactName = '북산', City = 'Seoul';원래 값에 무언가를 붙여서 업데이트 하고 싶다면 원래 값을 조정해서 넣으면된다.
UPDATE Suppliers SET PostalCode = concat('J', PostalCode) WHERE Country = 'Japan';2023.04.18
SQL을 통해 데이터를 수정 삭제 삽입하는 방법을 배웠다.
기타 많은 함수도 배웠지만 DB마다 다를 수 잇으니 확인할 필요가 있다.