skyjump's blog

서브쿼리는 다른 쿼리에 둘러싸인 쿼리.
inner query라고도 함.

outer query, inner query
SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURAL JOIN my_contacts AS mc
WHERE jc.title IN (SELECT title FROM job_listings); 

SELECT last_name, first_name
FROM my_contacts
WHERE zip_code = (SELECT zip_code FROM zip_code WHERE city = 'Memphis' AND state = 'TN'); 

위 코드는 다음 조인으로 표현한 쿼리랑 같다.
SELECT last_name, first_name
FROM my_contacts mc NATURAL JOIN zip_code zc
WHERE zc.city = 'MEMPHIS'
AND zc.state = 'TN';

내부 쿼리는 하나의 값만을 반환한다. IN은 예외.
 
Select 열에서 서브 쿼리 사용.
SELECT mc.first_name, mc.last_name
(SELECT state
FROM zip_code
WHERE mc.zip_code = zip_code) AS state
FROM my_contacts mc; 
한 열에서 하나의 값만을 반환할 수 있다.

Noncorrelated subquery(비상관 서브쿼리)
서브 쿼리가 외부 쿼리를 참조하지 않고 독립적으로 사용되는 것.

SELECT mc.first_name, mc.last_name, jc.salary
FROM
my_contacts AS mc NATURAL JOIN job_current AS jc
WHERE
jc.salary > (SELECT jc.salary
FROM my_contacts mc NATURAL JOIN job_current jc
WHERE email='andy@weatherorama.com');

여러값을 반환하는 비상관 서브트리 (IN, NOT IN)
회원들의 현재 직업 중에서 구직란에 있는 직업이 있는지, 있으면 회원 정보 출력.
SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURAL JOIN my_contacts AS mc
WHERE jc.title IN (SELECT title FROM job_listings);

Correlated subquery(상관 서브쿼리)
서브쿼리가 외부쿼리에 의존
같은 별명을 사용.
SELECT mc.first_name, mc.last_name
FROM my_contact AS mc
WHERE 3 = (SELECT COUNT(*) FROM contact_interest WHERE contact_id = mc.contact_id);

NOT EXISTS를 사용한 상관 서브 쿼리

my_contacts 테이블에서 job current테이블에 없는 사람들의 이름, 성, 이메일 주소 찾기
SELECT mc.first_name firstname, mc.last_name lastname, mc.email email
FROM my_contacts mc
WHERE NOT EXISTS
(SELECT * FROM job_current jc
WHERE mc.contact_id = jc.contact_id)

EXISTS도 있다.

 

 
별명(Alias) 사용
열이름을 간단하고 사용하기 쉽게 한다. 
CREATE TABLE profession
(
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    profession varchar(20)
);
INSERT INTO profession (profession)
    SELECT profession AS mc_prof FROM my_contacts
    GROUP BY mc_prof
    ORDER BY mc_prof

결과 테이블의 열의 이름이 alias값, 즉 mc_prof로 나온다.
원래 열의 이름은 변하지 않음. 저렇게만 보여주는 것임.

테이블 별명
SELECT profession AS mc_prof
FROM my_contacts AS mc
GROUP BY mc_prof
ORDER BY mc_prof;

AS는 생략 가능하다.

Inner join(내부 조인)
1) Cartesian 조인
한 테이블의 모든 행과 다른 테이블의 모든 행을 짝지워 반환한다.

SELECT t.boy, b.boy
FROM toys AS t
CROSS JOIN
boys AS b;

다음 쿼리도 같은 결과
SELECT toys.toy, boys.boy
FROM toys, boys;

2) equijoin(동등 조인)
같은지를 테스트하는 내부 조인
SELECT boys.boy, toys.toy
FROM boys
   INNER JOIN
   toys
ON boys.toy_id = toys.toy_id;

3) non-equijoin(비동등 조인)
내부 조인이 같지 않은지를 테스트.
SELECT boys.boy, toys.toy
FROM boys
    INNER JOIN
    toys
ON boys.toy_id <> toys.toy_id
ORDER BY boys.boy

4) natural join (자연조인)
두 테이블에 같은 이름의 열이 있을 때
SELECT boys.boy, toys.toy
FROM boys
    NATURAL JOIN
    toys;
 

CREATE TABLE interests (
int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
interest VARCHAR(50) NOT NULL,
contract_id INT NOT NULL,
CONSTRAINT my_contacts_contact_id_fk
FOREIGN KEY (contact_id)
REFERENCES my_contacts (contact_id)
);
CONSTRAINT my_contacts_contact_id_fk
제약조건.
my_contacts : 참조키가 어느 테이블을 참조하는지.
contact_id : 키의 이름
fk : 참조키이다.

FOREIGN KEY (contact_id) 
contact_id가 참조키이다.

REFERENCES my_contacts (contact_id)
참조키가 my_contacts 테이블을 참조하고 contact_id 열을 참조한다.

테이블간의 관계

1. 일대일
테이블 A의 레코드가 테이블 B에 많아야 한 개의 레코드와 연결된다.
자주 이용되지는 않음.
보안목적. 일부 데이터를 위한 테이블.
큰 데이터 따로 저장.
모르는 값을 따로 저장.
따로 때어냄으로써 데이터 조회 속도 향상 등.

2. 일대다
테이블 A의 한 레코드에 테이블 B의 레코드 여러개가 연결되고 B의 레코드는 테이블 A의 한 레코드만 연결될 수 있다.
예) 한 직업에 여러명의 사람들. 사람들마다 직업이 많아야 하나.

3. 다대다
여러 레코드에 여러 레코드 연결.
신발가게의 신발과 고객과의 관계. 
중복데이터가 생겨 연결테이블을 만든다.

연결테이블

다대다관계일 때는 중간에 테이블을 추가하여 일대다의 관계로 단순화.
두 테이블의 유니크키들을 열로 가지는 연결 테이블

이전까진 테이블의 데이터가 다른 테이블과 서로 어떻게 연결되는지(일대일, 일대다)에 대한 것.
이제부턴 한 테이블 내의 열들이 어떻게 연결되는지에 대한 것.

합성키
여러 개의 열들로 구성되어 유일무이한 키를 만드는 기본키.

Functionally dependent
열의 데이터가 변경되면 다른 열의 데이터도 변경될 때. 한 열이 다른 열과 종속관계에 있다.

Independent
다른 열에 종속되지 않는 열

Partial functional dependency
키가 아닌 열이 합성키의 일부에 종속되는 경우.
기본키로 테이블 인덱스용 id 필드를 사용하면 다른 열이 이 열에 종속되지 않기 때문에 부분 종속 관계를 피할 수 있음.

transitive functional dependency
키가 아닌 열이 변경될 때 키가 아닌 다른 열이 변경될 경우

제2정규형
제1정규형이고
Partial functional dependency가 없어야 한다.

제3정규형
제2정규형이고 transitive functional dependency가 없다. 

my_contacts 테이블에 contact_id를 추가하고 첫번째에 위치시키고, PRIMARY KEY로 변경하기

ALTER TABLE my_contacts

ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST,

ADD PRIMARY KEY(contact_id); 

FIRST, AFTER, BEFORE, LAST, SECOND, THIRD 등등을 사용하여 테이블의 원하는 위치에 열을 놓기

ALTER TABLE my_contacts

ADD COLUMN phone VARCHAR(10)

AFTER first_name; 

테이블 이름 바꾸기

projekts --> project_list

ALTER TABLE projekts

RENAME TO project_list;

ALTER와 CHANGE

number 열이름을 proj_id로 바꾸고, INT형, NOT NULL, 자동 증가 설정하기 

ALTER TABLE project_lsit

CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT,

ADD PRIMARY KEY(proj_id);

MODIFY

열의 데이터 타입을 바꾸고 이름은 그대로 사용하고 싶을 때.

ALTER TABLE project_list

MODIFY COLUMN proj_desc VARCHAR(120);

DROP

열 삭제하기

ALTER TABLE project_table

DROP COLUMN start_date;

기본키 삭제하기

ALTER TABLE your_table DROP PRIMARY KEY;

AUTO_INCREMENT 제거하기

your_id가 원래 auto increment였을 때, CHANGE로 auto increment 없는걸로 다시 설정하기.

ALTER TABLE your_table CHANGE your_id your_id INT(11) NOT NULL;

문자 함수

RIGHT(), LEFT() - 문자열의 일부를 추출할 수 있다.

location열에서 오른쪽부터 2개의 문자를 선택하기

SELECT RIGHT(location, 2) FROM my_contacts;

 

SUBSTRING_INDEX() - 문자열의 일부를 추출할 수 있다.

첫번째 ','를 찾는다.첫번째 ',' 앞의 모든 문자열을 반환한다.

SELECT SUBSTRING_INDEX(location, ',' , 1) FROM my_contacts;

 

SELECT UPPER('uSa'); 대문자 변환

SELECT LOWER('spaGHEtti'); 소문자 변환

SELECT REVERSE('spaGHEtti'); 문자열 역순으로 만들기

SELECT LTRIM(' spaGHEtti '); 왼쪽 공백 제거

SELECT RTRIM(' spaGHEtti '); 오른쪽 공백 제거

SELECT LENGTH('San Antonio, TX'); 문자열의 문자수 반환 

UPDATE와 SET이 함께 동작

문자열에서 오른쪽에 있는 두개 문자를 state에 설정하기
UPDATE my_contacts

SET state = RIGHT('Chester,NJ',2); 

1. 테이블로 표현하려는 것을 찾는다.
2. 테이블을 사용할 때, 표현하려는 것에 대한 정보들의 리스트를 만든다.
3. 리스트를 이용해서 그 정보들을 조각들로 쪼개고, 조각들은 테이블을 잘 만드는데 이용된다.

 쪼개는 방식

원자적으로 쪼갠다.
효율적인 테이블을 만드는데 필요한 한도 내에서 가능하면 작은 조각으로 나눠야 한다.
쿼리가 짧아지고 정확해진다.

원자적 데이터에 대한 규칙
1. 원자적 데이터로 구성된 열은 그 열에 같은 타입의 데이터를 여러 개 가질 수 없다.
2. 원자적 데이터로 구성된 테이블은 같은 타입의 데이터를 여러 열에 가질 수 없다.

정규화 첫단계

데이터를 원자적으로 만들기.
정규테이블은 중복데이터가 없어 데이터베이스 크기를 줄여주고, 찾아야 할 데이터가 적어 쿼리가 더 빨라진다.

정규화 두번째 단계

각 행은 기본키(primary key)를 가지고 있어야 한다.
각 레코드를 다른 레코드와 구분할 수 있는 열. 예 id
기본키는 NULL이 될 수 없다.
값이 반드시 있어야 한다.

간결해야 한다.
기본키 값은 변경할 수 없다.

SHOW 명령어

SHOW CREATE TABLE my_contacts;
테이블 생성 코드
SHOW COLUMNS FROM tablename;
테이블의 모든열 표시. 데이터 타입과 열의 세부사항
SHOW CREATE DATABASE databasename;
데이터베이스 생성 코드
SHOW INDEX FROM tablename;
인덱스 정보

경고 메시지 확인
SHOW WARNINGS;

기본키가 있는 테이블 생성하기
 

 
자동 증가 

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 

DELETE FROM clown_info
WHERE
activities = 'dancing';

UPDATE

UPDATE doughnut_ratings
SET
type='glazed',
comments='super!'
WHERE type = 'plain glazed';

UPDATE문은 테이블의 여러 레코드에 사용될 수 있다.
숫자 조작에는 기본적인 수학연산과 함께 사용하기.

UPDATE drink_info
SET cost = cost + 1
WHERE
drink_name = 'Blue Moon'
OR
drink_name = 'Oh my Gosh'
OR
drink_name = 'Lime Fizz'; 

Select * from my_contacts
where first_name = 'Anne';

*의 의미

모든 열을 보여 달라.

VARCHAR, CHAR, BLOB, DATE, TIME 등 문자열 타입은 작은 따옴표를 사용하고
DEC, INT등 숫자 타입은 작은 따옴표를 사용하지 않는다.
숫자는 빈칸으로 구분되지만 문자열 타입은 중간에 빈칸도 있기 때문에 작은 따옴표같은 구분해주는 뭔가가 있어야 하기 때문.

 문자열에 작은따옴표 사용하려면 \' 또는 '' (작은따옴표 두번)

원하는 열만 SELECT 하기

SELECT drink_name, main, second
from easy_drinks
where main = 'soda';

AND

Select location
From doughnut_ratings
Where type = 'plain glazed'
And
rating = 10; 

And로 쿼리들을 결합.

비교연산자

=, <>(같지않다), < , > , <=, >=

Select drink_name from drink_info
where
cost >= 3.5;
And
calories < 50;

OR

Select drink_name
From easy_drinks
Where
main = 'orange juice'
or
main = 'apple juice'; 

NULL 찾기

Select drink_name
From drink_info
Where
calories IS_NULL; 

LIKE

Select * from my_contacts
Where location LIKE '%CA';

location 열이 CA로 끝나는 모든 값 찾기. 문자열의 일부분

_
하나의 불특정 문자.
Select first_name from my_contacts
where first_name like '_im';

Kim, Jim 등등이 나올 수 있다.

Between 키워드

Select drink_name From drink_info
where calories between 30 and 60 
부등호와 AND를 이용해서 바꿀 수 있다.

IN 키워드

Select date_name
from black_book
where
rating IN ('innovative', 'fabulous', 'delightful', 'pretty good');

열의 값이 괄호안의 하나와 일치하면 그 행이 반환된다.
같은 열에 대해 or로 표현 된 것을 줄일 수 있다.

NOT IN

Select date_name
from black_book
where
rating Not IN ('innovative', 'fabulous', 'delightful', 'pretty good');

괄호 안에 있는 값이 없는 행들을 반환한다.

NOT

Between이나 Like와 함께 사용할 수 있다.
보통 WHERE 바로 다음에 NOT이 나온다.
AND와 OR과 같이 사용하면 And, or 뒤에 not을 붙인다.

NULL하고는 어떻게 동작?
WHERE NOT main IS NULL;또는
WHERE main IS NOT NULL;