CREATE DATABASE DB_NAME;

-- Default 부터는 줘도 되고 안줘도 됨
-- PRIMARY KEY는 기본이 되는 컬럼이 어딘지 지정해줌(ID를 담고 있는 컬럼)
CREATE TABLE TABLE_NAME (
    [컬럼이름] [데이터 타입] [속성] [기본값] [코멘트],
    [컬럼이름] [데이터 타입] [속성] [기본값] [코멘트],
    [컬럼이름] [데이터 타입] [속성] [기본값] [코멘트],
    ...
    PRIMARY KEY (컬럼이름)
);

CREATE DATABASE user;

ALTER TABLE delivery.store_menu 
add INDEX idx_store_id (store_id ASC) visible;

ALTER TABLE store 
	CHANGE thumbnail_url thumbnail_url VARCHAR(500) Not Null;

ALTER TABLE delivery.user_order_id
ADD CONSTRAINT fk_store_user_order_id
FOREIGN KEY (store_id)
REFERENCES delivery.store (id);

TRUNCATE [테이블 명]

TRUNCATE `user`.`user`

DROP TABLE [테이블명]

select [컬럼목록]
from [첫 번째 테이블]
  inner join [두 번째 테이블] on [조인조건]
where [검색조건]

use market_db;
 
select *
from buy cross join member;

USE market_db;
CREATE TABLE emp_table (emp CHAR(4), manager CHAR(4), phone VARCHAR(8));
 
INSERT INTO emp_table VALUES('대표', NULL, '0000');
INSERT INTO emp_table VALUES('영업이사', '대표', '1111');
INSERT INTO emp_table VALUES('관리이사', '대표', '2222');
INSERT INTO emp_table VALUES('정보이사', '대표', '3333');
INSERT INTO emp_table VALUES('영업과장', '영업이사', '1111-1');
INSERT INTO emp_table VALUES('경리부장', '관리이사', '2222-1');
INSERT INTO emp_table VALUES('인사부장', '관리이사', '2222-2');
INSERT INTO emp_table VALUES('개발팀장', '정보이사', '3333-1');
INSERT INTO emp_table VALUES('개발주임', '정보이사', '3333-1-1');

use market_db;
select A.emp '직원', B.emp '직송상관', B.phone '직속상관연락처'
from emp_table A
         inner join emp_table B on A.manager = B.emp;

use market_db;
select A.emp '직원', B.emp '직송상관', B.phone '직속상관연락처'
from emp_table A
         inner join emp_table B on A.manager = B.emp
where A.emp = '경리부장';

select b.mem_id, m.mem_name, b.prod_name, m.addr, concat(m.phone1, m.phone2) '연락처'
from buy as b
         inner join member as m
                    on b.mem_id = m.mem_id;

UPDATE [테이블 이름] SET 
[컬럼이름] = [값]
WHERE [조건절]

UPDATE user.user SET 
age = 30
WHERE name = '한국사랑'

SELECT [선택할 필드] 
FROM [테이블 명] AS [별칭]
WHERE [조건절]

create table SQLD.member (
    GRADE varchar(50),
    NAME varchar(100),
    MEMBER_NO BIGINT(50)
);
 
insert into SQLD.member (GRADE, NAME, MEMBER_NO) values ('3등급', '남길동', 5);
insert into SQLD.member (GRADE, NAME, MEMBER_NO) values ('4등급', '북길동', 6);
insert into SQLD.member (GRADE, NAME, MEMBER_NO) values ('2등급', '홍길동', 2);
insert into SQLD.member (GRADE, NAME, MEMBER_NO) values ('2등급', '서길동', 1);
insert into SQLD.member (GRADE, NAME, MEMBER_NO) values ('1등급', '동길동', 3);
 
# 1 ~ 4 등급까지 정렬
select GRADE, NAME, MEMBER_NO from SQLD.member  ORDER BY GRADE ASC, MEMBER_NO;
 
# 1 ~ 4 등급까지 정렬하고 같은 등급은 MEMBER_NO 내림차순으로 정렬
select GRADE, NAME, MEMBER_NO from SQLD.member  ORDER BY GRADE ASC, MEMBER_NO DESC;

# 3개 
select mem_name, height from market_db.member order by height desc limit 3;
# 3위부터 2개
select mem_name, height from market_db.member order by height desc limit 3, 2;

select mem_id, sum(amount) from market_db.buy group by mem_id;
select mem_id '회원 아이디', sum(price*amount) '총 구매 개수' from market_db.buy group by mem_id;

select AVG(col1) as result1, SUM(col2) as result2, count(col3) as result3 from SQLD.sample;

# addr에서 중복 제거 
select distinct addr from market_db.member order by addr;

INSERT INTO [테이블 이름] (
  [컬럼이름1],
  [컬럼이름1],
  [컬럼이름3]
)
VALUES(
  [컬럼1의 데이터 값],
  [컬럼2의 데이터 값],
  [컬럼3의 데이터 값],
)

INSERT INTO `user`.`user`
(
	`name`,
	`age`,
	`email`
) 
VALUES
(
	'홍길동',
	10,
	'rookedsysc36@gmail.com'
) 

DELETE FROM [테이블 명] WHERE [조건절]

DELETE FROM `user`.`user` 
WHERE name = '한국사랑'

SELECT * FROM book_store.`user` WHERE score > 95;

delimiter //
create procedure myProc()
BEGIN
    select * from shop_db.member where member_name = '나훈아';
    select * from shop_db.product where product_name = '삼각김밥';
end //
delimiter ;

call myProc();

IF <조건식> THEN 
BEGIN 
  SQL 문장들
END IF;
END$$

drop procedure  if exists  ifProc1;
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
    IF 100 = 100 THEN
        SELECT '100 = 100' AS RESULT;
    ELSE
        SELECT '100 != 100' AS RESULT;
    END IF;
END$$
DELIMITER ;
CALL ifProc1();

DROP PROCEDURE IF EXISTS  ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
    DECLARE  debuteDate DATE; -- 데뷔 일자
    DECLARE  curDate DATE; -- 오늘
    DECLARE days INT; -- 활동한 일수
 
    SELECT debut_date INTO debuteDate
        FROM market_db.member
            WHERE mem_id = 'APN';
 
    SET curDate = CURRENT_DATE();
    SET days = DATEDIFF(curDate, debuteDate);
 
    IF (days/365) >= 5 THEN -- 5년이 지났다면
        SELECT CONCAT('데뷔한 지 ', days, '일이 지났습니다.');
    ELSE
        SELECT CONCAT('데뷔한 지 ', days, '일 밖에 안되었네요. 더 열심히 하세요.');
    END IF;
END $$
DELIMITER ;
 
Call ifProc3();

CASE 
  WHEN 조건1 THEN
    SQL 문장들
  WHEN 조건2 THEN 
    SQL 문장들
  ELSE
    모두 해당 안될 경우 실행되는 SQL 문장들
END CASE;

WHILE <조건식> D0 
  SQL 문장들
END WHILE;

DROP PROCEDURE IF EXISTS whileProc; 
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수
    DECLARE hap INT; -- 더한 값을 누적할 변수
    SET i = 1;
    SET hap = 0;
 
    WHILE (i <= 100) DO
        SET hap = hap + i;  -- hap의 원래의 값에 i를 더해서 다시 hap에 넣으라는 의미
        SET i = i + 1;      -- i의 원래의 값에 1을 더해서 다시 i에 넣으라는 의미
    END WHILE;
 
    SELECT '1부터 100까지의 합 ==>', hap;   
END $$
DELIMITER ;
CALL whileProc();

use market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;

DROP TABLE IF EXISTS gate_table;
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);
 
SET @curDate = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간
 
PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate; -- ? 부분에 curDate를 넣어줌
DEALLOCATE PREPARE myQuery;
 
SELECT * FROM gate_table;

CREATE TABLE member (
  mem_id CHAR(8) PRIMARY KEY,
  mem_name VARCHAR(20) NOT NULL,
  height TINYINT UNSINGED NULL,
);

CREATE TABLE member (
  mem_id CHAR(8) NOT NULL,
  mem_name VARCHAR(20) NOT NULL,
  height TINYINT UNSINGED NULL,
  PRIMARY KEY (mem_id)
);

FOREIGN KEY(현재_테이블의_컬럼명) REFERENCES 관계_맺을_테이블(관계_맺을_테이블의_컬럼명)

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
( mem_id  CHAR(8) NOT NULL PRIMARY KEY,
  mem_name    VARCHAR(10) NOT NULL,
  height      TINYINT UNSIGNED NULL
);
CREATE TABLE buy
(  num         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   buy_member_id CHAR(8) NOT NULL,
   prod_name     CHAR(6) NOT NULL,
   FOREIGN KEY(buy_member_id) REFERENCES member(mem_id)
);

use market_db;
DROP TABLE IF EXISTS buy;
CREATE TABLE buy 
(  num         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   mem_id      CHAR(8) NOT NULL, 
   prod_name     CHAR(6) NOT NULL
);
ALTER TABLE buy
    ADD CONSTRAINT 
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

INSERT INTO buy (mem_id, prod_name) VALUES('BLK', '지갑');
INSERT INTO buy (mem_id, prod_name) VALUES('BLK', '맥북');

UPDATE member SET mem_id = 'PINK' WHERE mem_id='BLK';
SELECT * FROM buy; -- mem_id가 전부 PINK로 바뀌어 있음

DELETE FROM member WHERE mem_id = 'PINK';
SELECT * FROM buy; -- 아무것도 출력되지 않음

DROP TABLE IF EXISTS user;
CREATE TABLE user (
    id BIGINT not null AUTO_INCREMENT primary key ,
    name VARCHAR(20) not null,
    height TINYINT UNSIGNED NULL CHECK ( height >= 100 )
);

height TINYINT UNSIGNED NULL DEFAULT 160