23.03.03 Day24

MySQL

• JOIN(P.284부터 참조)

-- P.284부터 참조
-- 쇼핑몰 가입하고 물건을 한번도 구매하지 않은 회원까지 모두 출력
SELECT u.*
	, b.prodName
    , b.groupName
    , b.price
    , b.amount -- b. 찍고 자동완성 안되면 Ctrl+Space
	FROM usertbl AS u
    LEFT OUTER JOIN buytbl AS b -- u.userID와 b.userID 일치하는거 싹다 보여주고 밑에 일치하지 않는것도 다 보여줌
		ON u.userID = b.userID
        WHERE b.userID IS NULL; -- 가입했지만 물건을 사지않은 애들 출력하는 조건. buytbl에 있는애들이 뭐라도 산애들이니까 


-- 학생 중 동아리에 가입하지 않은 학생 찾기
SELECT s.stdname,s.addr
	, j.num, c.clubName, c.roomNo
	FROM stdtbl AS s
    LEFT OUTER JOIN stdclubtbl AS j 
		ON s.stdName = j.stdName
	LEFT OUTER JOIN clubtbl AS c
		ON c.clubname = j.clubName
UNION -- !동아리 가입안한애 + 아무도 가입안한 동아리!
SELECT s.stdname,s.addr -- 동아리 중 아무도 가입하지 않은 동아리
	, j.num, c.clubName, c.roomNo
	FROM stdtbl AS s
    RIGHT OUTER JOIN stdclubtbl AS j 
		ON s.stdName = j.stdName
	RIGHT OUTER JOIN clubtbl AS c
		ON c.clubname = j.clubName;

UNION 시 Column의 개수가 동일해야함

• Create/UNIQUE/CHECK

CREATE DATABASE tabledb;
USE tabledb;

CREATE TABLE usertbl(
	userID CHAR(8) NOT NULL PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    birthYear INt NOT NULL,
    addr VARCHAR(10)
);

CREATE TABLE buytbl(
	num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    userID CHAR(8) NOT NULL,
    prodName CHAR(6) NOT NULL,
    price INT,
    FOREIGN KEY(userID) REFERENCES usertbl(userID)
);

-- 실무에 가장 근접한 PK방법 / PRIMARY KEY
-- 기본 키 두개 지정 
CREATE TABLE prodtbl(
	prodCode CHAR(3) NOT NULL,
    prodID CHAR(4) NOT NULL,
    prodDate DATETIME NOT NULL,
    prodCur VARCHAR(10) NULL,
    CONSTRAINT PK_prodtbl_prodCode_prodID
		PRIMARY KEY(prodCode,prodID) -- 핵심
);

-- 제약조건
-- UNIQUE
CREATE TABLE membertbl(
	userID CHAR(8) NOT NULL PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    email VARCHAR(50) NULL UNIQUE -- 유니크 제약조건 : 유니크 설정 시 중복되는 컬럼 값 삽입 불가능
);

-- CHECK
CREATE TABLE stdtbl(
	userID CHAR(8) NOT NULL PRIMARY KEY,
    name VARCHAR(10),
    grade INT CHECK (grade>=1 AND grade <=4), -- 1~4학년 까지만
    CONSTRAINT CK_name CHECK (name IS NOT NULL) -- NOT NULL과 동일 
);

• View

-- 뷰
USE sqldb;

CREATE VIEW uv_potentialUser
AS 
	SELECT u.*,
		b. num,b.prodName,b.price,b.amount
		FROM usertbl AS u
        LEFT OUTER JOIN buytbl AS b
			ON u.userID=b.userID
		WHERE b.userID IS NULL;
 
SELECT * FROM uv_potentialUser;

• Index

USE tabledb;

CREATE TABLE TBL1(
	ID INT NOT NULL PRIMARY KEY,
	bridge INT NULL,
    content VARCHAR(5000)
);

/* sqldb DB의 usertbl 데이터중
 userId,name,birthYear,addr 만 가져와서
 tabledb.usertbl 에 다 집어넣음 */
INSERT INTO tabledb.usertbl 
SELECT userID,name,birthYear,addr
	FROM sqldb.usertbl;

SELECT * FROM usertbl WHERE name ='김범수';