> For the complete documentation index, see [llms.txt](https://redryan.gitbook.io/studyjoon/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://redryan.gitbook.io/studyjoon/undefined-1/undefined/4.md). # 4일차 {% code fullWidth="true" %} ```sql /*******************************************/ -- ### 데이터베이스에서의 함수 ### /*******************************************/ -- COUNT(x) : x의 개수를 반환한다. -- x는 열(Column)이거나 리터럴(Literal) -- CAST(x AS y) : 값 x를 y형태로 변환한다. -- 주로 UNSIGNED인 값 들을 연산할 때 음수가 나올 가능성이 있다면 사용. -- UNSIGNED인 값 끼리 계산하였을 때 결과로 음숙 ㅏ나오면 오류가 남. -- CAST( `대상 열` AS SIGNED) :`대상 열` 값을 SIGNED로 변경 -- CAST( `대상 열` AS UNSIGNED) :`대상 열` 값을 UNSIGNED로 변경함. -- CEILING(x) : 값 x에 대해 올림 SELECT CEILING(3.14); -- FLOOR(x) : 값 x에 대해 내림 SELECT FLOOR(3.14); -- ROUND(X) : 값 X에 대해 반올림 SELECT ROUND(3.54); -- ROUND(A, B) : 값 A에 대해 소수점 B자리 까지 반올림 SELECT ROUND(3.141502, 2); -- CONCAT(A, B, ...) : 주어진 값을 이어붙임 SELECT CONCAT(100, '%', '-', 'SDFSDF'); -- 문제 01 -- shopdb SCHEMA 만들기 CREATE SCHEMA `shopdb`; -- UserTbl 만들기 -- 열 : -- userID(8자 문자 NULL X, PK) -- name(10자 문자, NUll X) -- birthYear(정수, NULL X) -- address(20자 문자, NULL X) -- mobile1(10자 문자) -- mobile2(10자 문자) -- height(정수) -- mDa10te(날짜) CREATE TABLE `shopdb`.`userTbl` ( `userId` VARCHAR(8) NOT NULL, `name` VARCHAR(10) NOT NULL, `birthYear` INT NOT NULL, `address` VARCHAR(20) NOT NULL, `mobile1` VARCHAR(10), `mobile2` VARCHAR(10), `height` SMALLINT, `mDate` DATE, CONSTRAINT PRIMARY KEY (`userId`) ); drop table `shopdb`.`userTbl` ; -- BuyTbl 만들기 -- 열 : -- num(정수 NULL X, PK) -- userID(8자 문자, NUll X) -- prodName(10자 문자, NULL X) -- groupName(20자 문자) -- price(정수, NULL X) -- amount(정수 NULL X) CREATE TABLE `shopdb`.`buyTbl` ( `num` INT UNSIGNED NOT NULL, `userId` VARCHAR(8) NOT NULL, `prodName` VARCHAR(10) NOT NULL, `groupName` VARCHAR(20), `price` INT UNSIGNED NOT NULL, `amount` SMALLINT NOT NULL, CONSTRAINT PRIMARY KEY (`num`), CONSTRAINT FOREIGN KEY (`userId`) REFERENCES `shopdb`.`userTbl`(`userId`) ); drop table `shopdb`.`buyTbl`; -- 테이블 확인 DESC `shopdb`.`userTbl`; DESC `shopdb`.`buyTbl`; use `shopdb`; -- Usertbl 값삽입 INSERT INTO userTbl VALUES('LSG','이승기',1987,'서울','011','1111111',182,'2008-8-8'); INSERT INTO userTbl VALUES('KBS','김범수',1979,'경남','011','2222222',173,'2012-4-4'); INSERT INTO userTbl VALUES('KKH','김경호',1971,'전남','019','3333333',177,'2007-7-7'); INSERT INTO userTbl VALUES('JYP','조용필',1950,'경기','011','4444444',166,'2009-4-4'); INSERT INTO userTbl VALUES('SSK','성시경',1979,'서울',NULL,NULL,186,'2013-12-12'); INSERT INTO userTbl VALUES('LJB','임재범',1963,'서울','016','6666666',182,'2009-9-9'); INSERT INTO userTbl VALUES('YJS','윤종신',1969,'경남',NULL,NULL,170,'2005-5-5'); INSERT INTO userTbl VALUES('EJW','은지원',1972,'경북','011','8888888',174,'2014-3-3'); INSERT INTO userTbl VALUES('JKW','조관우',1965,'경기','018','9999999',172,'2010-10-10'); INSERT INTO userTbl VALUES('BBK','바비킴',1973,'서울','010','0000000',176,'2013-5-5'); select * from userTbl; -- Buytbl 값 삽입 INSERT INTO buyTbl VALUES(1,'KBS','운동화',NULL,30,2); INSERT INTO buyTbl VALUES(2,'KBS','노트북','전자',1000,1); INSERT INTO buyTbl VALUES(3,'JYP','모니터','전자',200,1); INSERT INTO buyTbl VALUES(4,'BBK','모니터','전자',200,5); INSERT INTO buyTbl VALUES(5,'KBS','청바지','의류',50,3); INSERT INTO buyTbl VALUES(6,'BBK','메모리','전자',80,10); INSERT INTO buyTbl VALUES(7,'SSK','책','서적',15,5); INSERT INTO buyTbl VALUES(8,'EJW','책','서적',15,2); INSERT INTO buyTbl VALUES(9,'EJW','청바지','의류',50,1); INSERT INTO buyTbl VALUES(10,'BBK','운동화',NULL,30,2); INSERT INTO buyTbl VALUES(11,'EJW','책','서적',15,1); INSERT INTO buyTbl VALUES(12,'BBK','운동화',NULL,30,2); SELECT * FROM `shopdb`.`buyTbl`; SELECT U.`userId`, U.`name`, B.`prodName`, B.`price`, B.`amount` FROM `shopdb`.`userTbl` AS U INNER JOIN `shopdb`.`buyTbl` AS B ON U.`userID` = B.`userID`; /***********************************/ ### 제약 조건 ### /***********************************/ -- 기본키 : CONSTRAINT PRIMARY KEY (`<열 이름>`,...) -- 유니크 : CONSTRAINT UNIQUE (`<열 이름>`,...) -- 외래키 : CONSTRAINT FOREIGN KEY (`<대상 열>`,...) REFERENCES `스키마`,`테이블` (`<참고 열>,...) -- 단, 외래키가 걸리는 `참고 열`은 기본키이거나 UNIQUE여야 한다. -- 그리고 `<대상 열>`과 `<참고 열>`은 타입이 같아야 한다. -- FOREIGN KEY ==> 중요함!!! -- 문제 -- 1 구매양(amount)가 5개 이상인 행을 출력 SELECT * FROM `shopdb`.`buyTbl` WHERE `amount` >= 5; -- 2 가격이(price) 50 이상 500 이하인 행의 UserID와 prodName 만 출력 SELECT `userID`, `prodName` FROM `shopdb`.`buyTbl` WHERE `price` >= 50 AND `price` <= 500; SELECT `userID`, `prodname` FROM `shopdb`.`buyTbl` WHERE `price` between 50 and 500; -- 3 구매양(amount)이 10 이상 이거나 가격이 100 이상인 행 출력 SELECT * FROM `shopdb`.`buyTbl` WHERE `amount` >= 10 or `price` >= 100; -- 4 UserID 가 K로 시작하는 행 출력 SELECT * FROM `shopdb`.`buyTbl` WHERE `userId`LIKE 'k%'; -- 5 상품(prodName)이 책이거나 userID가 W로 끝나는 행출력 SELECT * FROM `shopdb`.`buyTbl` WHERE `prodName` = '책' or `userID` LIKE '%w'; -- 6 groupname이 비어있지 않는 행만 출력 (<>) SELECT * FROM `shopdb`.`buyTbl` WHERE `groupName` != 'null'; -- 문제 -- 1 userId 순으로 오름차순 정렬(userTbl, buyTbl) SELECT * FROM `shopdb`.`userTbl` ORDER BY `userID` ASC; SELECT * FROM `shopdb`.`buyTbl` ORDER BY `userID` ASC; -- 2 price 순으로 내림차순 정렬 SELECT * FROM `shopdb`.`buyTbl` ORDER BY `price` DESC; -- 3 구매양(amount)가 3이상인 행4 prodName 내림차순으로정렬 SELECT * FROM `shopdb`.`buyTbl` WHERE `amount` >= 3 ORDER BY `prodName` DESC; -- 테이블 생성 CREATE TABLE `shopdb`.`employee` ( `emp_id` INT PRIMARY KEY, `emp_name` VARCHAR(50), `department_id` INT ); CREATE TABLE `shopdb`.`department` ( `department_id` INT PRIMARY KEY, `department_name` VARCHAR(50) ); -- 데이터 삽입 INSERT INTO `shopdb`.`employee` (`emp_id`, `emp_name`, `department_id`) VALUES (1, 'John Doe', 1), (2, 'Jane Smith', 2), (3, 'Michael Johnson', 1), (4, 'Emily Vrown', 2), (5, 'Chris Lee', 3), (6, 'Jane Lee', 4); INSERT INTO `shopdb`.`department` (`department_id`, `department_name`) VALUES (1, 'Sales'), (2, 'Marketing'), (3, 'Finance'); select * from `shopdb`.`employee`; -- INNER JOIN : 두 테이블 간의 일치하는 행을 결합 -- : 쉽게 말해, JOIN을 기준으로 왼쪽테이블을 기준으로 가지고 있어야한다는 의미이다. -- LEFT JOIN (또는 LEFT OUTER JOIN) : 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 결합 -- : 쉽게 말해, JOIN을 기준으로 왼쪽테이블을 기준으로 가지고 있어야한다는 의미이다. -- RIGHT JOIN (또는 RIGHT OUTER JOIN) : LEFT JOIN의 반대 -- CROSS JOIN : 첫번째 테이블 각 행에 두번째 테이블의 모든 행을 결합, 조건 없이 모든 가능한 조합 생성 SELECT * FROM `shopdb`.`employee`; -- INNER JOIN SELECT `employee`.`emp_id`, `employee`.`emp_name`, `department`.`department_name` FROM `shopdb`.`employee` INNER JOIN `shopdb`.`department` ON `department`.`department_id` = `employee`.`department_id`; /* 1 John Doe Sales 2 Jane Smith Marketing 3 Michael Johnson Sales 4 Emily Vrown Marketing 5 Chris Lee Finance */ -- LEFT JOIN -- INNER JOIN SELECT `employee`.`emp_id`, `employee`.`emp_name`, `department`.`department_name` FROM `shopdb`.`employee` LEFT JOIN `shopdb`.`department` ON `department`.`department_id` = `employee`.`department_id`; /* 1 John Doe Sales 2 Jane Smith Marketing 3 Michael Johnson Sales 4 Emily Vrown Marketing 5 Chris Lee Finance 6 Jane Lee 0 */ -- RIGHT JOIN SELECT `employee`.`emp_id`, `employee`.`emp_name`, `department`.`department_name` FROM `shopdb`.`employee` RIGHT JOIN `shopdb`.`department` ON `department`.`department_id` = `employee`.`department_id`; select * from `shopdb`.`userTbl`; select * from `shopdb`.`buyTbl`; -- 1 바비킴의 userID,birthYEar,prodName,GroupName 을 출력하세요 SELECT `userTbl`.`userID`, `birthYear`, `prodName`, `GroupName` FROM `shopdb`.`userTbl` INNER JOIN `shopdb`.`buyTbl` WHERE `name` = '바비킴'; -- 2 amount * price 의 값이 100 이상인 행의 name,addr,prodname, mobile1-mobile2를 출력하세요 SELECT `name`, `address`, `prodname`, CONCAT(mobile1, '-', mobile2) AS `전화번호`, (`amount` * `price`) AS `구매총량` FROM `shopdb`.`userTbl` INNER JOIN `shopdb`.`buyTbl` ON `userTbl`.`userID` = `buyTbl`.`userID` WHERE `amount` * `price` >= 100; -- 3 groupname이 전자인 행의 userid, name, birthyear, prodname을 출력하세요 SELECT `userTbl`.`userID`, `name`, `birthYear`, `prodName` FROM `shopdb`.`userTbl` INNER JOIN `shopdb`.`buyTbl` ON `userTbl`.`userID` = `buyTbl`.`userID` WHERE `groupName` = '전자'; ``` {% endcode %}