๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
  • Welcome.

:: DBMS ๐Ÿšฉ23

JOIN>๋ณดํ˜ธ์†Œ์—์„œ ์ค‘์„ฑํ™”ํ•œ ๋™๋ฌผ -- ์„œ๋ธŒ์ฟผ๋ฆฌ ์‚ฌ์šฉ SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME FROM ANIMAL_INS A JOIN ( SELECT ANIMAL_ID, ANIMAL_TYPE, NAME FROM ANIMAL_OUTS WHERE SEX_UPON_OUTCOME NOT LIKE 'Intact %' ) B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.SEX_UPON_INTAKE LIKE 'Int%' ORDER BY A.ANIMAL_ID -- ์กฐ์ธ๋งŒ ์‚ฌ์šฉ. SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME FROM ANIMAL_INS A JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.SEX_UPO.. 2023. 2. 26.
JOIN>5์›” ์‹ํ’ˆ๋“ค์˜ ์ด๋งค์ถœ ์กฐํšŒํ•˜๊ธฐ SELECT A.PRODUCT_ID, A.PRODUCT_NAME, **SUM**(B.AMOUNT*A.PRICE) AS TOTAL_SALES FROM FOOD_PRODUCT A JOIN FOOD_ORDER B ON A.PRODUCT_ID = B.PRODUCT_ID WHERE B.PRODUCE_DATE LIKE '2022-05-%' **GROUP BY PRODUCT_ID** ORDER BY TOTAL_SALES DESC, PRODUCT_ID 2023. 2. 26.
JOIN>์ฃผ๋ฌธ๋Ÿ‰์ด ๋งŽ์€ ์•„์ด์Šคํฌ๋ฆผ๋“ค ์กฐํšŒํ•˜๊ธฐ -- ์ง์ ‘ ์ง  ์ฝ”๋“œ. SELECT C.FLAVOR FROM ( SELECT A.SHIPMENT_ID, A.FLAVOR, SUM(A.TOTAL_ORDER + B.TOTAL_ORDER) AS TOTAL FROM FIRST_HALF A JOIN JULY B ON A.FLAVOR = B.FLAVOR GROUP BY FLAVOR ORDER BY TOTAL DESC ) AS C ORDER BY C.TOTAL DESC LIMIT 3 -- GPT๋กœ๋ถ€ํ„ฐ ์ฒจ์‚ญ๋ฐ›์Œ. SELECT C.FLAVOR FROM ( SELECT A.FLAVOR, SUM(A.TOTAL_ORDER + B.TOTAL_ORDER) AS TOTAL FROM FIRST_HALF A JOIN JULY B ON A.FLAVOR = B.FLAVOR GROUP BY.. 2023. 2. 26.
JOIN>๊ทธ๋ฃน๋ณ„ ์กฐ๊ฑด์— ๋งž๋Š” ์‹๋‹น ๋ชฉ๋ก ์ถœ๋ ฅํ•˜๊ธฐ -- ์ฝ”๋“œ๋ฅผ ์ž…๋ ฅํ•˜์„ธ์š” select member_name, review_text, review_date from member_profile A join rest_review B on A.member_id = B.member_id where member_name like ( select /*member_id,*/ rank() over (order by count(member_id)) as cnt from rest_review group by member_id order by cnt desc ) -- select * from rest_review select member_id, rank() over (order by count(*)) as cnt from rest_review group by member_i.. 2023. 2. 26.
JOIN>์˜ค๋žœ ๊ธฐ๊ฐ„ ๋ณดํ˜ธํ•œ ๋™๋ฌผ(1) -- JOIN ๋ฏธ์‚ฌ์šฉ SELECT NAME, DATETIME FROM ANIMAL_INS WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS) ORDER BY DATETIME LIMIT 3 --JOIN ์‚ฌ์šฉ SELECT A.NAME, A.DATETIME FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE B.ANIMAL_ID IS NULL ORDER BY DATETIME LIMIT 3 2023. 2. 26.
JOIN>์žˆ์—ˆ๋Š”๋ฐ์š” ์—†์—ˆ์Šต๋‹ˆ๋‹ค -- ์ฝ”๋“œ๋ฅผ ์ž…๋ ฅํ•˜์„ธ์š” SELECT B.ANIMAL_ID, B.NAME FROM ANIMAL_INS A JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.DATETIME > B.DATETIME ORDER BY A.DATETIME ; 2023. 2. 26.
JOIN>์—†์–ด์ง„ ๊ธฐ๋ก ์ฐพ๊ธฐ -- ์ฝ”๋“œ๋ฅผ ์ž…๋ ฅํ•˜์„ธ์š” SELECT **DISTINCT**(B.ANIMAL_ID), B.NAME FROM ANIMAL_INS A **JOIN ANIMAL_OUTS B** **WHERE B.ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS)** ORDER BY ANIMAL_ID; 2023. 2. 26.
JOIN>์ƒํ’ˆ ๋ณ„ ์˜คํ”„๋ผ์ธ ๋งค์ถœ ๊ตฌํ•˜๊ธฐ mysql -- ์ฝ”๋“œ๋ฅผ ์ž…๋ ฅํ•˜์„ธ์š” SELECT A.PRODUCT_CODE, **SUM**(A.PRICE * B.SALES_AMOUNT) AS SALES FROM PRODUCT A **JOIN** OFFLINE_SALE B ON A.PRODUCT_ID = B.PRODUCT_ID **GROUP BY PRODUCT_CODE** ORDER BY SALES DESC, PRODUCT_CODE JOIN = INNER JOIN 2023. 2. 26.
JOIN>์กฐ๊ฑด์— ๋งž๋Š” ๋„์„œ์™€ ์ €์ž ๋ฆฌ์ŠคํŠธ ์ถœ๋ ฅํ•˜๊ธฐ mysql -- ์ฝ”๋“œ๋ฅผ ์ž…๋ ฅํ•˜์„ธ์š” SELECT A.BOOK_ID, B.AUTHOR_NAME, DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') FROM BOOK A INNER JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID WHERE A.CATEGORY LIKE '๊ฒฝ์ œ' ORDER BY A.PUBLISHED_DATE 2023. 2. 26.
GROUP BY>๋Œ€์—ฌ ํšŸ์ˆ˜๊ฐ€ ๋งŽ์€ ์ž๋™์ฐจ๋“ค์˜ ์›”๋ณ„ ๋Œ€์—ฌ ํšŸ์ˆ˜ ๊ตฌํ•˜๊ธฐ mysql -- SELECT DATE_FORMAT(START_DATE, '%m') AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10' GROUP BY CAR_ID HAVING COUNT(CAR_ID) >= 5 ) GROUP BY MONTH, CAR_ID HAVING RECORDS >= 0 ORDER BY MONTH, CAR_ID DESC; SELECT MO.. 2023. 2. 26.
GROUP BY>์นดํ…Œ๊ณ ๋ฆฌ ๋ณ„ ๋„์„œ ํŒ๋งค๋Ÿ‰ ์ง‘๊ณ„ํ•˜๊ธฐ mysql -- ์ฝ”๋“œ๋ฅผ ์ž…๋ ฅํ•˜์„ธ์š” SELECT A.CATEGORY, SUM(B.SALES) AS TOTAL_SALES FROM BOOK A INNER JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_ID WHERE DATE_FORMAT(B.SALES_DATE, '%Y-%m') LIKE '2022-01' GROUP BY CATEGORY ORDER BY CATEGORY; 2023. 2. 26.
GROUP BY>์„ฑ๋ถ„์œผ๋กœ ๊ตฌ๋ถ„ํ•œ ์•„์ด์Šคํฌ๋ฆผ ์ด ์ฃผ๋ฌธ๋Ÿ‰ mysql ์ž…๋‹ˆ๋‹น -- ์ฝ”๋“œ๋ฅผ ์ž…๋ ฅํ•˜์„ธ์š” SELECT B.INGREDIENT_TYPE, SUM(A.TOTAL_ORDER) AS TOTAL_ORDER FROM FIRST_HALF A INNER JOIN ICECREAM_INFO B WHERE A.FLAVOR = B.FLAVOR GROUP BY B.INGREDIENT_TYPE ORDER BY TOTAL_ORDER; 2023. 2. 26.
GROUP BY>์ง„๋ฃŒ๊ณผ๋ณ„ ์ด ์˜ˆ์•ฝ ํšŸ์ˆ˜ ์ถœ๋ ฅํ•˜๊ธฐ -- ์ฝ”๋“œ๋ฅผ ์ž…๋ ฅํ•˜์„ธ์š” SELECT MCDP_CD AS '์ง„๋ฃŒ๊ณผ ์ฝ”๋“œ', COUNT(*) AS '5์›”์˜ˆ์•ฝ๊ฑด์ˆ˜' FROM APPOINTMENT WHERE APNT_YMD LIKE '2022-05-%' GROUP BY MCDP_CD ORDER BY COUNT(*), MCDP_CD; 2023. 2. 26.
[MySQL] - [1] - [ DB, table ์ƒ์„ฑ, insert, use, show, select ] drop database if exists bookdb; create database bookDB; show databases; use bookDB; show tables; create table booktbl( id int auto_increment primary key, -- id | intํ˜• | ์ž๋™ ์˜ค๋ฆ„์ฐจ์ˆœ ์ถœ๋ ฅ -- primary๋Š” table๋‹น ํ•˜๋‚˜์ด๋ฉฐ, row์˜ ๋Œ€ํ‘ฏ๊ฐ’์ด๋‹ค. bookname varchar(20) not null, -- not null : ๊ณต๋ฐฑ ๋ถˆ๊ฐ€. publisher varchar(20) not null, price int not null ); insert into booktbl values(null, 'book100', 'com100', 100); -- id์นธ์— null์„ ์ผ.. 2022. 1. 22.
[DBMS] SQL - ๊ฐœ๋… - 3 - [SQL - TCL / SQL - DCL] 1. SQL - TCL 2. SQL - DCL 3. ํ•จ์ˆ˜ ( NULL ์ฒ˜๋ฆฌ ํ•จ์ˆ˜, ๋‹จ์ผ ํ•จ์ˆ˜ ) ================================================================================= 1. TCL ( Transaction Control Language ) - ํŠธ๋žœ์žญ์…˜ ์ œ์–ด ์–ธ์–ด. - ํŠธ๋žœ์žญ์…˜์€ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ๋…ผ๋ฆฌ์  ์—ฐ์‚ฐ ๋‹จ์œ„์ด๋‹ค. - ํŠธ๋žœ์žญ์…˜์˜ ๋Œ€์ƒ์ด ๋˜๋Š” SQL๋ฌธ์€ UPDATE, INSERT, DELETE ๋“ฑ ๋ฐ์ดํ„ฐ๋ฅผ ์ˆ˜์ •ํ•˜๋Š” DML๋ฌธ์ด๋‹ค. - ํ•˜๋‚˜์˜ ํŠธ๋žœ์žญ์ ผ์—๋Š” ํ•˜๋‚˜ ์ด์ƒ์˜ SQL๋ฌธ์žฅ์ด ํฌํ•จ๋˜๋ฉฐ ๋ถ„ํ• ํ•  ์ˆ˜ ์—†๋Š” ์ตœ์†Œ์˜ ๋‹จ์œ„์ด๋‹ค. ์ฆ‰ ALL OR NOTHING ์ด๋‹ค. - ํŠธ๋žœ์žญ์…˜์˜ ํŠน์ง• 1. ์›์ž์„ฑ : ํŠธ๋žœ์žญ์…˜์—์„œ ์ •์˜๋œ ์—ฐ์‚ฐ๋“ค์€ ๋ชจ๋‘ ์„ฑ๊ณต์ ์œผ.. 2021. 3. 23.
728x90