MYSQL Query

DataBase/MYSQL

MYSQL Query

Naranjito 2022. 1. 13. 18:36

INSERT INTO

Insert new records in a table.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO STOCK(STK_CD, STK_NM, EX_CD, NAT_CD)
       VALUES('600519','귀주모태주','SH','CN');

DELETE

DELETE
FROM STOCK
WHERE STK_CD='600519';

It is used in a WHERE clause to search for a specified pattern in a column.

- The percent sign (%) represents zero, one, or multiple characters

- The underscore sign (_) represents one, single character

SELECT  STK_CD ,STK_NM ,SEC_NM
FROM    STOCK
WHERE   STK_NM LIKE 'LG%'
ORDER BY STK_CD ASC;
[결과]
STK_CD   STK_NM            SEC_NM                  
======== ================= =========== 
001120   LG상사            유통                    
003550   LG                금융

...

# NOT LIKE 검색(SEC_NM이 '증권'이면서 STK_NM이 '투자'가 포함되지 않은 데이터 검색)
SELECT  STK_NM ,STK_CD ,EX_CD ,SEC_NM
FROM    STOCK
WHERE   SEC_NM = '증권'
AND     STK_NM NOT LIKE '%투자%'
ORDER BY STK_NM;

It allows you to specify multiple values in a WHERE clause.

SELECT  STK_CD ,STK_NM ,SEC_NM
FROM    STOCK
WHERE   SEC_NM IN ('담배','주류제조업','문구류')
ORDER BY STK_NM ASC;
[결과]
STK_CD   STK_NM            SEC_NM            
======== ================= ================= 
033780   KT&G              담배              
023150   MH에탄올          주류제조업        
043650   국순당            주류제조업        
005360   모나미            문구류     

...

SELECT  STK_NM ,STK_CD ,EX_CD ,SEC_NM
FROM    STOCK
WHERE   STK_NM LIKE '삼성%'
AND     SEC_NM NOT IN ('보험','금융','증권')
ORDER BY STK_NM;

BETWEEN

It selects values within a given range. The values can be numbers, text, or dates. Begin and end values are included.

SELECT  STK_CD ,STK_NM
FROM    STOCK
WHERE   STK_CD BETWEEN '200000' AND '200500'
ORDER BY STK_CD;

SELECT T1.STK_CD, T1.STK_NM, T2.DT, T2.C_PRC, T3.DT AFTER_DT, T3.C_PRC AFTER_PRC, 
       UFN_CHG_RT(T2.C_PRC, T3.C_PRC) AFTER_CHG_RT
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD=T1.STK_CD)
INNER JOIN HISTORY_DT T3
ON (T3.STK_CD=T1.STK_CD)
WHERE T1.STK_NM='현대차'
AND T2.DT=STR_TO_DATE('20190211','%Y%m%d')
AND T3.DT BETWEEN STR_TO_DATE('20190212','%Y%m%d') AND STR_TO_DATE('20190214','%Y%m%d') 
;

>>>

STK_CD	STK_NM	DT	C_PRC	AFTER_DT	AFTER_PRC	AFTER_CHG_RT
005380	현대차	2019-02-11	127500.000	2019-02-12	128500.000	0.78
005380	현대차	2019-02-11	127500.000	2019-02-13	124500.000	-2.35
005380	현대차	2019-02-11	127500.000	2019-02-14	121500.000	-4.71

COUNT

COUNT(*) : It counts all the value even if it constitutes NULL.

COUNT(column) : It doesn't count if the value of the columns is NULL.

SELECT T1.SEC_NM, COUNT(*) CNT
FROM STOCK T1
WHERE T1.STK_NM LIKE '동일%'
GROUP BY T1.SEC_NM
ORDER BY T1.SEC_NM;

>>>
SEC_NM	CNT
기계와장비	1
자동차부품	1
전자장비와기기	1
철강과금속	3

SUBSTRING

It extracts some characters from a string.

SUBSTRING(string, start, length)

SELECT SUBSTRING('SQL TUTORIAL',2,4);

>>>

SUBSTRING('SQL TUTORIAL',2,4)
QL T

GROUPBY

SELECT  SUBSTR(T1.STK_NM,1,2) STK_SUB_NM ,COUNT(*) CNT
FROM    STOCK T1
WHERE   (T1.STK_NM LIKE '삼성%' OR T1.STK_NM LIKE '현대%')
GROUP BY SUBSTR(T1.STK_NM,1,2)
ORDER BY SUBSTR(T1.STK_NM,1,2);

>>>

STK_SUB_NM   CNT   
============ ===== 
삼성         21    
현대         35

## Same as below ##

SELECT  SUBSTR(T1.STK_NM,1,2) STK_SUB_NM ,T1.STK_NM
FROM    STOCK T1
WHERE   (T1.STK_NM LIKE '삼성%' OR T1.STK_NM LIKE '현대%')
ORDER BY T1.STK_NM;

SET SESSION

SELECT @@SQL_MODE;

>>>
@@SQL_MODE
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

## If I want to set ONLY_FULL_GROUP_BY ##
SET SESSION SQL_MODE = CONCAT('ONLY_FULL_GROUP_BY,',@@SQL_MODE);

HAVING

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

SELECT  T1.STK_CD ,SUM(T1.C_PRC * T1.VOL) SUM_VOL_AMT
FROM    HISTORY_DT T1
WHERE   T1.DT >= STR_TO_DATE('20190201','%Y%m%d')
AND     T1.DT < STR_TO_DATE('20190301','%Y%m%d')
GROUP BY T1.STK_CD
HAVING SUM(T1.VOL * T1.C_PRC) > 2000000000000
ORDER BY STK_CD ASC;

>>>

STK_CD   SUM_VOL_AMT                
======== ====================== 
000660   4066750799700.000000   
005930   9006227143900.000000   
009150   2613378576000.000000   
025980   2372101587350.000000

ISNULL

SELECT  T1.STK_CD ,T1.STK_NM ,T1.SEC_NM
FROM    STOCK T1
WHERE   T1.STK_NM LIKE '동양%'
AND     T1.SEC_NM IS NULL
ORDER BY T1.STK_CD;

>>>

STK_CD   STK_NM        SEC_NM   
======== ============= ======== 
001525   동양우        NULL     
001527   동양2우B      NULL
001529   동양3우B      NULL

CASE

CASE
    WHEN condition1 THEN result1(If condition1 is true)
    WHEN condition2 THEN result2(If condition2 is true)
    WHEN conditionN THEN resultN
    ELSE result
END; (Always ends with END)

SELECT  T1.STK_CD
        ,T1.STK_NM
        ,CASE WHEN T1.STK_NM LIKE '삼성%' THEN '삼성 관련주'
              WHEN T1.STK_NM LIKE '현대%' THEN '현대 관련주'
              ELSE 'LG 관련주'
        END 종목구분
FROM    STOCK T1
WHERE   T1.STK_NM IN ('삼성전자','삼성SDI','현대차','현대모비스','LG전자','LG화학')
ORDER BY T1.STK_NM;

>>>

STK_CD   STK_NM            종목구분               
======== ================= ================== 
066570   LG전자            LG 관련주       
051910   LG화학            LG 관련주       
006400   삼성SDI           삼성 관련주       
005930   삼성전자          삼성 관련주       
012330   현대모비스        현대 관련주

CREATE

CREATE TABLE STOCK_TAG(
STK_CD varchar(40) NOT NULL COMMENT 'STOCKCODE',
STK_TAG_DV_NM varchar(200) NOT NULL COMMENT 'STOCKTAGDIVISIONNAME',
STK_TAG_NM varchar(200) NOT NULL COMMENT 'STOCKTAGNAME',
USE_YN varchar(200) NULL COMMENT 'USEYESNO',
REG_DTM DATETIME NULL COMMENT 'REGISTRATIONDATETIME',
PRIMARY KEY (STK_CD, STK_TAG_DV_NM,STK_TAG_NM )
)ENGINE=innoDB COMMENT='STOCKTAG';

Column	Type	Comment
STK_CD	varchar(40)	STOCKCODE
STK_TAG_DV_NM	varchar(200)	STOCKTAGDIVISIONNAME
STK_TAG_NM	varchar(200)	STOCKTAGNAME
USE_YN	varchar(200) NULL	USEYESNO
REG_DTM	datetime NULL	REGISTRATIONDATETIME

AUTOCOMMIT

Set commit the query automatically in order to avoid to unsave the query.

SELECT @@AUTOCOMMIT;

>>>
1 ## Or true means auto commit.

If not, set up the auto commit as below.

SET AUTOCOMMIT = TRUE;

UPDATE

UPDATE the table what needs to be change, Alias

SET what column?=how change?

WHERE condition

UPDATE STOCK_TAG T1
SET 
T1.USE_YN='N',
T1.STK_TAG_NM='UNUSED'
WHERE T1.USE_YN='Y';

DELETE

DELETE T1
FROM STOCK_TAG T1
WHERE T1.STK_CD='006400';

INNER JOIN

SELECT column(s)

FROM table1 alias1

INNER JOIN table2 alias2

ON (alias1.column=alias2.column) # join condition

ON (alias1.column=condition ex. '삼성전자') # filter condition

WHERE

Join condition below.

SELECT T1.STK_CD, T1.STK_NM, T1.SEC_NM, T2.STK_CD, T2.DT, T2.C_PRC
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD=T1.STK_CD) ## join condition
WHERE T1.STK_NM='삼성전자'
ORDER BY T2.DT

>>>

STK_CD	STK_NM	SEC_NM	STK_CD	DT	C_PRC
005930	삼성전자	반도체	005930	2019-01-02	38750.000
005930	삼성전자	반도체	005930	2019-01-03	37600.000

Filter condition below.

SELECT T1.STK_CD, T1.STK_NM, T2.DT, T2.C_PRC
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD=T1.STK_CD
AND T1.STK_NM LIKE '삼성%'
AND T2.DT=STR_TO_DATE('20190108','%Y%m%d')); ## filter condition

Same as upper but ON has been omited.

SELECT T1.STK_CD, T1.STK_NM, T2.DT, T2.C_PRC
FROM STOCK T1
INNER JOIN HISTORY_DT T2
WHERE T2.STK_CD=T1.STK_CD ## join condition
AND T1.STK_NM LIKE '삼성%'
AND T2.DT=STR_TO_DATE('20190108','%Y%m%d'); ## filter condition

Using with GROUP BY.

SELECT T1.SEC_NM, SUM(T2.VOL*T2.C_PRC) VOL_AMT
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD=T1.STK_CD) ## join condition
WHERE T2.DT=STR_TO_DATE('20190103','%Y%m%d') ##filter condition
GROUP BY T1.SEC_NM
ORDER BY 2 DESC

ROUND

ROUND(number, decimals)

SELECT T1.STK_CD, T1.STK_NM, T1.SEC_NM, T1.EX_CD, T2.DT, T2.C_PRC, T2.VOL, 
ROUND((T2.VOL*T2.C_PRC)/1e8,1) 'TRADINGPRICE(억)' ## Round up
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD=T1.STK_CD)
WHERE T1.STK_NM LIKE '삼성%'
AND T2.DT=STR_TO_DATE('20190108','%Y%m%d')
ORDER BY ROUND((T2.VOL*T2.C_PRC)/1e8,1) DESC;

GROUP BY

SELECT column1, COUNT(column2), MAX(column3)

FROM table1 alias1

WHERE

GROUP BY column1 ## Except for column1, column2, column3 must used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG())

SELECT T1.STK_CD, T1.STK_NM, ROUND(SUM(T2.VOL),1) SUM_VOL, ROUND(SUM(T2.VOL*T2.C_PRC)/1e8,1) TRADINGPRICE
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON T2.STK_CD=T1.STK_CD
WHERE T1.STK_NM IN ('삼성바이오로직스','셀트리온')
AND T2.DT>=STR_TO_DATE('20190301','%Y%m%d')
AND T2.DT<STR_TO_DATE('20190401','%Y%m%d')
GROUP BY T1.STK_CD, T1.STK_NM
ORDER BY TRADINGPRICE DESC;

DATE_FORMAT

Change the date format.

SELECT DATE_FORMAT(T2.DT, '%Y%m') YM, T1.STK_CD, T1.STK_NM, ROUND(SUM(T2.VOL*T2.C_PRC)/1.e8,1) TRADINGPRICE
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON T2.STK_CD=T1.STK_CD
WHERE T1.STK_NM IN ('삼성전자','SK하이닉스')
AND T2.DT>=STR_TO_DATE('20190101','%Y%m%d')
AND T2.DT<STR_TO_DATE('20200101','%Y%m%d')
GROUP BY YM, T1.STK_CD, T1.STK_NM
ORDER BY YM ASC, T1.STK_CD ASC;

>>> DataBase
ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A349996	Cat	2018-01-22 14:32:00	Normal	Sugar	Neutered Male
A350276	Cat	2017-08-13 13:50:00	Normal	Jewel	Spayed Female

>>>Query
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME,'%Y-%m-%d') 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

>>>Result
ANIMAL_ID	NAME	날짜
A349996	Sugar	2018-01-22
A350276	Jewel	2017-08-13

LEFT OUTER JOIN

It returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

SELECT T1.STK_CD, T1.STK_NM, T2.DT, T2.C_PRC
FROM STOCK T1
LEFT OUTER JOIN HISTORY_DT T2
ON (T2.STK_CD=T1.STK_CD
AND T2.DT=STR_TO_DATE('20190401','%Y%m%d'))
WHERE T1.STK_NM IN ('삼성전자','영신금속');

RIGHT OUTER JOIN

It returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

IFNULL

Return the specified value IF the expression is NULL, otherwise return the expression.

IFNULL(value, 0) : Return the value if value is not NULL, otherwise return 0.

SELECT T1.STK_CD, T1.STK_NM, SUM(T2.VOL) VOL_SUM1, IFNULL(SUM(T2.VOL),0) VOL_SUM2
FROM STOCK T1
LEFT OUTER JOIN HISTORY_DT T2
ON T2.STK_CD=T1.STK_CD
AND T2.DT>=STR_TO_DATE('20190409','%Y%m%d')
AND T2.DT<=STR_TO_DATE('20190411','%Y%m%d')
WHERE T1.STK_NM IN ('삼성전자','영신금속')
GROUP BY T1.STK_CD, T1.STK_NM
ORDER BY T1.STK_CD;

>>>

STK_CD   STK_NM         VOL_SUM1       VOL_SUM2       
======== ============== ============== ============== 
005930   삼성전자       32460155.000   32460155.000   
007530   영신금속       NULL           0.000

SELECT SUBQUERY

A query within a SELECT query.

SELECT T1.EX_CD, 
       (SELECT A.BAS_CD_NM
       FROM BASECODE A
       WHERE A.BAS_CD_DV='EX_CD' AND A.BAS_CD=T1.EX_CD) EX_CD_NM, ##Correlated Subquery
       T1.NAT_CD,
       (SELECT A.BAS_CD_NM
       FROM BASECODE A
       WHERE A.BAS_CD_DV='NAT_CD' AND A.BAS_CD=T1.NAT_CD) NAT_CD_NM,
       T1.STK_CD, T1.STK_NM
FROM STOCK T1
WHERE T1.STK_NM IN ('삼성전자','서울반도체');

Is same as below.

SELECT  T1.EX_CD ,T2.BAS_CD_NM EX_CD_NM
        ,T1.NAT_CD ,T3.BAS_CD_NM NAT_CD_NM
        ,T1.STK_CD ,T1.STK_NM
FROM    STOCK T1
        LEFT OUTER JOIN BASECODE T2
           ON (T2.BAS_CD_DV = 'EX_CD' AND T2.BAS_CD = T1.EX_CD)
        LEFT OUTER JOIN BASECODE T3
           ON (T3.BAS_CD_DV = 'NAT_CD' AND T3.BAS_CD = T1.NAT_CD)
WHERE   T1.STK_NM IN ('삼성전자','서울반도체');

WHERE SUBQUERY

A query within a WHERE query.

SELECT T1.STK_CD, T1.STK_NM, T2.DT, T2.C_PRC
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD=T1.STK_CD)
WHERE T1.STK_NM='삼성전자'
AND T2.DT=(SELECT MAX(A.DT)
          FROM HISTORY_DT A
          WHERE A.DT<STR_TO_DATE('20190401','%Y%m%d'))
ORDER BY T1.STK_CD;

FROM SUBQUERY(INLINE VIEW)

A query within a FROM query.

SELECT T3.*, T4.C_PRC C_PRC_0401, T5.C_PRC C_PRC_1230
FROM
(SELECT T1.STK_CD, MAX(T1.STK_NM) STK_NM, ###### From here to below, inline view
       ROUND(SUM(T2.C_PRC * T2.VOL)/1e8,1) VOL_AMT 
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD=T1.STK_CD)
WHERE T1.EX_CD='KD'
AND T2.DT>=STR_TO_DATE('20190301','%Y%m%d')
AND T2.DT<STR_TO_DATE('20190401','%Y%m%d')
GROUP BY T1.STK_CD
ORDER BY VOL_AMT DESC
LIMIT 3) T3 ###### Until here
INNER JOIN HISTORY_DT T4
ON (T4.STK_CD=T3.STK_CD
AND T4.DT=STR_TO_DATE('20190401','%Y%m%d'))
INNER JOIN HISTORY_DT T5
ON (T5.STK_CD=T3.STK_CD
AND T5.DT=STR_TO_DATE('20191230','%Y%m%d'))
ORDER BY T3.VOL_AMT DESC;

SELECT T5.*
FROM
   (SELECT T4.*
    FROM
        (SELECT T1.STK_CD, T1.STK_NM, T2.DT DT_1, 
                ROUND(SUM(T2.C_PRC*T2.VOL)/1e8,1) VOL_AMT_1,
                T3.DT DT_2,
                ROUND(SUM(T3.C_PRC*T3.VOL)/1e8,1) VOL_AMT_2
         FROM STOCK T1
         INNER JOIN HISTORY_DT T2
         ON (T2.STK_CD=T1.STK_CD)
         INNER JOIN HISTORY_DT T3
         ON (T3.STK_CD=T1.STK_CD)
         WHERE T2.DT=STR_TO_DATE('20190102','%Y%m%d')
         AND T3.DT=STR_TO_DATE('20191227','%Y%m%d')
         GROUP BY T1.STK_CD, T1.STK_NM, T2.DT, T3.DT
         ORDER BY VOL_AMT_1 DESC
         LIMIT 10) T4
    ORDER BY T4.VOL_AMT_2 DESC
    LIMIT 3) T5;
    
######### Is same as below. #########

SELECT T1.STK_CD, T1.STK_NM, T3.DT DT_1, T3.VOL_AMT VOL_AMT_1, 
       T5.DT DT_2, T5.VOL_AMT VOL_AMT_2
FROM STOCK T1
INNER JOIN 
         (SELECT T2.STK_CD, T2.DT, T2.VOL*T2.C_PRC VOL_AMT
          FROM HISTORY_DT T2
          WHERE T2.DT=STR_TO_DATE('20190102','%Y%m%d')
          ORDER BY T2.VOL*T2.C_PRC DESC
          LIMIT 10) T3
ON (T3.STK_CD=T1.STK_CD)

INNER JOIN
         (SELECT T4.STK_CD, T4.DT, T4.VOL*T4.C_PRC VOL_AMT
          FROM HISTORY_DT T4
          WHERE T4.DT=STR_TO_DATE('20191227','%Y%m%d')
          ORDER BY T4.VOL*T4.C_PRC DESC
          LIMIT 10) T5
ON (T5.STK_CD=T1.STK_CD)

ORDER BY T3.VOL_AMT DESC;

EXISTS

It is used to test for the existence of any record in a subquery and returns TRUE if the subquery returns one or more records.

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

SELECT T1.STK_CD, T1.STK_NM, T2.DT, T2.C_PRC
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON T2.STK_CD=T1.STK_CD
WHERE T2.DT=STR_TO_DATE('20190401','%Y%m%d')
AND EXISTS
    (SELECT *
    FROM HISTORY_DT A
    WHERE A.STK_CD=T1.STK_CD
    AND A.DT=STR_TO_DATE('20190329','%Y%m%d')
    AND A.C_PRC*A.VOL/1e8>3000)
ORDER BY T1.STK_CD;

SET GLOBAL

Set up for User Defined Function.

SET GLOBAL  log_bin_trust_function_creators = 1;

DECIMAL
NUMERIC

DECIMAL(digits, decimal places)

NUMERIC(digits, decimal places)

NUMERIC(6,2);

The range is from 9999.99 to -9999.99

BEGIN

CREATE FUNCTION dbname.functonname

BEGIN

DECRARE

SET

RETURN

END

DELIMITER $$

CREATE FUNCTION DB_SQLSTK.UFN_CHG_RT
(
 p_BUY_PRC NUMERIC(28,8)
 ,p_SELL_PRC NUMERIC(28,8)
) RETURNS NUMERIC(28,2)

BEGIN
 DECLARE r_CHG_RT NUMERIC(28,2);
 SET r_CHG_RT = (p_SELL_PRC - p_BUY_PRC) / p_BUY_PRC * 100;
 RETURN r_CHG_RT;

END$$

DELIMITER;

SELECT  T1.STK_CD ,T1.STK_NM
        ,T_BUY.DT BUY_DT ,T_BUY.C_PRC BUY_PRC
        ,T_SELL.DT SELL_DT ,T_SELL.C_PRC SELL_PRC
        ,UFN_CHG_RT(T_BUY.C_PRC ,T_SELL.C_PRC) CHG_RT
        ,10000 BUY_AMT
        ,UFN_PROF_AMT(T_BUY.C_PRC ,T_SELL.C_PRC,10000) PROF_AMT 
FROM    STOCK T1
        INNER JOIN HISTORY_DT T_BUY
           ON (T_BUY.STK_CD = T1.STK_CD
               AND T_BUY.DT = STR_TO_DATE('20190104','%Y%m%d'))
        INNER JOIN HISTORY_DT T_SELL
           ON (T_SELL.STK_CD = T1.STK_CD
               AND T_SELL.DT = STR_TO_DATE('20191127','%Y%m%d'))
WHERE   T1.STK_NM LIKE '셀트리온%'
ORDER BY UFN_CHG_RT(T_BUY.C_PRC ,T_SELL.C_PRC) DESC;

>>>

STK_CD	STK_NM	BUY_DT	BUY_PRC	SELL_DT	SELL_PRC	CHG_RT	BUY_AMT	PROF_AMT
068270	셀트리온	2019-01-04	206338.000	2019-11-27	172574.000	-16.36	10000	-33764.00
091990	셀트리온헬스케어	2019-01-04	69714.000	2019-11-27	48968.000	-29.76	10000	-20746.00
068760	셀트리온제약	2019-01-04	57920.000	2019-11-27	34892.000	-39.76	10000	-23028.00

LIMIT

It select a limit number of records.

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

SELECT T1.STK_NM, T2.DT, T2.VOL
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD=T1.STK_CD)
WHERE T1.STK_NM='삼성전자'
ORDER BY T2.VOL ASC
LIMIT 3;

CREATE VIEW AS

Create the view.

CREATE VIEW VW_STOCK_HISTORY AS
SELECT T1.STK_CD, T1.STK_NM, T1.SEC_NM, T2.DT, T2.C_PRC
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD=T1.STK_CD)
ORDER BY T1.STK_CD, T2.DT;

Using the view.

SELECT T1.STK_CD, T1.STK_NM, T1.SEC_NM, T1.DT, T1.C_PRC
FROM VW_STOCK_HISTORY T1
WHERE T1.STK_NM='삼성전자'
AND T1.DT=STR_TO_DATE('20190108','%Y%m%d')
ORDER BY T1.STK_CD, T1.DT;

DROP VIEW IF EXISTS

DROP VIEW IF EXISTS VW_STOCK_HISTORY;

WITH AS

WITH

SELECT

FROM

WHERE

ORDRE BY

WITH 
RES1 AS
           (SELECT T1.STK_CD, T1.STK_NM, T2.DT, T2.C_PRC 0228_PRC,
                   T3.C_PRC 0201_PRC,
                   ROUND((T2.C_PRC-T3.C_PRC)/T3.C_PRC*100,2) CHG_RT
            FROM STOCK T1
            INNER JOIN HISTORY_DT T2
            ON (T2.STK_CD=T1.STK_CD)
            INNER JOIN HISTORY_DT T3
            ON (T3.STK_CD=T1.STK_CD)
            WHERE T2.DT=STR_TO_DATE('20190228','%Y%m%d')
            AND T3.DT=STR_TO_DATE('20190201','%Y%m%d')
            ORDER BY CHG_RT DESC
            LIMIT 10),

RES2 AS
           (SELECT T4.*
            FROM RES1 T4
            ORDER BY T4.0228_PRC ASC
            LIMIT 3)

SELECT T5.*
FROM RES2 T5;

RECURSIVE CTE

Common Table Expressions

A CTE is defined using WITH clause.

The recursive CTEs are defined using WITH RECURSIVE clause.

WITH RECURSIVE 
odd_no (sr_no, n) AS
(
SELECT 1, 1 
union all
SELECT sr_no+1, n+2 from odd_no where sr_no < 5 
)
SELECT * FROM odd_no; 

>>>

+---------+-------+
| sr_no   |  n    |
+---------+-------+
| 1       |  1    |
| 2       |  3    |
| 3       |  5    |
| 4       |  7    |
| 5       |  9    |
+---------+-------+

DISTINCT

It is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

>>> DataBase
ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A349996	Cat	2018-01-22 14:32:00	Normal	Sugar	Neutered Male
A350276	Cat	2017-08-13 13:50:00	Normal	Jewel	Spayed Female

>>>Query
SELECT COUNT(DISTINCT NAME) count
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
;

>>>Result
count
96

저작자표시

'DataBase > MYSQL' 카테고리의 다른 글

Primary Key, Foreign Key (0)	2022.07.25
SQL, Docker-compose, Python module (0)	2022.02.03
Common SQL queries in Pandas (0)	2022.01.24
SQL error:may be the input source is a directory or a block device (0)	2022.01.24
How to run mySQL through docker-compose. (0)	2022.01.13

현재글MYSQL Query

d3js, Step Function, kafka, nvidia-smi, Regular Expression, axis, global variable, batch size, selectall, cross-entropy, yield from, Filter, textdistance, zeros, abstractmethod, Sigmoid function, forward propagation, docker-compose, randn, classmethod,

¡Hola, Mundo!

MYSQL Query

'DataBase > MYSQL' 카테고리의 다른 글

'DataBase/MYSQL'의 다른글

티스토리툴바

« 2025/01 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

MYSQL Query

'DataBase > MYSQL' 카테고리의 다른 글

'DataBase/MYSQL'의 다른글

관련글

티스토리툴바