Learning SQL 1장 - 2장
계층형 데이터베이스 시스템에서 데이터는 하나 이상의 트리구조로 표현된다. 계층형 데이터베이스 시스템은 특정 고객의 트리를 찾은 다음 트리를 탐색하여 원하는 계좌 또는 거래 내역을 찾을 수 있는 도구를 제공한다. 트리의 각 노드는 0개 또는 1개의 상위 노드와, 0개나 1개 혹은 더 많은 수의 하위노드를 가질 수 있는데 이 구성을 단일 상위 계층 구조 : single-parent hierarchy 라고 한다.
또 다른 일반적인 접근 방식인 네트워크 데이터 베이스 시스템은 서로 다른 레코드 간의 관계를 정의하는 레코드 셋과 링크 셋을 나타낸다. 수의 자금 계좌에 게시된 거래를 찾으려면 다음과 같은 단계를 수행한다.
1) a의 고객기록을 찾는다.
2) a의 고객기록으로부터 계좌 목록으로 연결되는 링크를 추적한다.
3) 자금 계좌를 찾을 때까지 계좌 체인을 탐색한다.
4) 자금 기록에서 거래 목록으로 연결되는 링크를 따라간다.
계좌 정보 레코드는 여러 위치(고객 정보 레코드 및 상품 레코드)에서 액세스할 수 있으므로 네트워크 데이터베이스가 다중 상위 계층 구조 multi-parent hierarchy로 동작할 수 있다.
1970년대 들어서 데이터의 관계형 모델이라는 논문에서 데이터를 테이블 table 집합으로 나타낸다. 포인터를 사용하여 관련 엔터티를 탐색하는 대신, 중복 데이터를 사용하여 서로 다른 테이블의 레코드를 연결한다.
관계형 데이터베이스의 각 테이블에는 엔터티를 완전히 설명하는 데 필요한 추가 정보와 함께 해당 테이블의 행을 고유하게 식별하는 정보인 기본키 primary key가 포함된다.
fname과 lname 열의 조합을 기본키로 사용할 수 있겠지만, 이름과 성이 같은 사람이 있을 수 있으므로, customer 테이블의 cust_id가 기본키열이 된다.
SQL문은 여러가지로 나뉜다.
SQL 스키마문: 데이터베이스에 저장된 데이터 구조를 정의할 떄 사용
SQL 데이터문: SQL 스키마문으로 정의한 데이터 구조를 조작할 때 사용
SQL 트랜잭션 문: 트랜잭션의 시작과 종료 및 롤백에 사용
데이터 베이스에서 새 테이블을 작성하려면 create table이라는 SQL 스키마 문을 사용하는 반면, 새 테이블을 데이터로 채우는 과정에서는 insert라는 SQL 데이터 문이 필요하다.
corporation이라는 table을 생성하는 SQL 스키마문은 다음과 같다.
CREATE TABLE corporation
(corp_id SMALLINT,
name VARCHAR(30),
CONSTRAINT pk_corporation PRIMARY KEY (corp_id)
);
이 구문은 corp_id 및 name이라는 두 개의 열을 가진 테이블을 생성하며, corp_id 열은 테이블의 기본 키로 식별된다.
다음은 corporation 테이블에 하나의 행을 삽입하는 SQL 데이터 문이다. 이 구문은 corporation 테이블에 corp_id 열의 값은 27이고, name 열의 값은 Acme Paper Corporation인 행을 추가한다.
INSER INTO corporation (corp_id, name)
VALUES (27, 'Acme Paper Corporation');
방금 생성된 데이터를 가져오는 간단한 select 문은 다음과 같다.
Mysql > SELECT name
-> FROM corporation
-> WHERE corp_id = 27;
SQL스키마문을 통해 작성된 모든 데이터베이스 요소는 데이터 딕셔너리라는 특수한 테이블셋에 저장된다. '데이터베이스에 관한 정보 데이터'를 통칭 메타데이터라고 한다. 위에서 생성한 테이블과 마찬가지로 select문을 통해 데이터 딕셔너리 테이블에 쿼리할 수 있으므로, 런타임 시에도 데이터 베이스에 배포된 현재 데이터 구조를 살펴볼 수 있다.
SQL문은 비절차적 언어이다. 필요한 입력과 출력을 정의하지만, 실제 명령문 실행 방식은 옵티마이저 optimizer라는 데이터베이스 엔진의 구성요소에게 일임되므로 직접 제어하지 않는다. 옵티마이저는 SQL문을 보고 테이블 구성방법과 사용가능한 인덱스를 고려하여 가장 효율적인 실행 경로를 결정한다. 즉, sql만으로 완전한 애플리케이션을 작성할 수 없어 특정 데이터를 조작하는 간단한 스크립트를 작성하지 않는 한 SQL을 선호하는 프로그래밍 언어와 통합해야 한다. 예를 들면, 자바와 같은 비 데이터베이스 언어를 사용하는 경우, 툴킷과 API를 사용하여 코드내에서 SQL문을 실행해야 한다.
SELECT t.txn_id, t.txn_type_cd, t.txn_date, t.amount
FROM individual i
INNER JOIN account a ON i.cust_id = a.cust_id
INNER JOIN product p ON p.product_cd = a.product_cd
INNER JOIN transaction t ON t.account_id = a.account_id
WHERE i.fname = 'George' AND i.lname = 'Blake'
AND p.name = 'checking account';
-------------------------------------------
txt_id | txt_type_cd | txn_date | amount
조지 블레이크의 자유 입출금 계좌에 대한 모든 거래를 조회하는 SQL문은 위와 같이 작성할 수 있다.
개별 테이블 행과 'Checking(자유 입출금)' 상품에 대한 product 테이블 행을 식별하고, 이 individual/product 조합에 대한 account 테이블의 행을 찾고, 이 항목에 게시된 모든 트랜잭션에 대해 transaction 테이블의 4개 열을 반환한다.
SELECT t.txn_id, t.txn_type_cd, t.txn_data, t.amount
FROM account a
INNER join transaction t ON t.account_id = a.account_id
where a.cust_id = 8 and a.product_cd = 'CHK';
위는 고객 ID가 8이고, 자유 입출금 계좌가 'CHK'코드로 지정되었다는 것을 알고 있을 때 고객 ID를 바탕으로 account 테이블에서 조지 블레이크의 예금 계좌를 쉽게 찾을 수 있고 계좌 ID를 사용하여 그에 맞는 거래를 찾을 수 있다.
SELECT cust_id, fname
FROM individual
WHERE lname = 'Smith';
이 쿼리는 lname 열이 문자열 'Smith'와 일치하는 모든 행을 individual 테이블에서 검색하고 해당 행에서 cust_id 및 fname 열을 반환한다. 데이터베이스 쿼리와 더불어 대부분의 작업은 데이터베이스의 데이터를 채우고 수정하는 것과 관련이 있는데, product 테이블에 새 행을 추가하는 간단한 예제 쿼리는 다음과 같다.
INSERT INTO product (product_Cd, name)
VALUES ('CD', 'Certificate of Depositt')
---------------------------- #Deposit의 철자가 틀린 경우, Update문으로 해결할 수 있다.
UPDATE product
SET name = 'Certifiacate of Deposit'
WHERE product_cd = 'CD';
mysql 로그인 후 데이터 베이스 확인 및 작업할 DB 지정
마지막 문장은 mysql 명령줄 도구를 호출할 때마다 사용자 이름과 데이터베이스를 모두 지정하는 문장.
mysql -u root -p
mysql> show databases;
mysql> use sakila;
mysql -u root -p sakila; /* 이 문장 입력 시 위에 use sakila문이 필요 없어짐 */SELECT now()
현재 날짜와 시간을 알고 싶을 때 실행
형식: 2019-04-04 20:44:26
now() 함수는 현재 날짜와 시간을 리턴하는 내장 함수이다. from절이 없어서 쿼리가 실행되지 않으면 FROM dual;을 붙인다. XML이나 JSON문서 또는 공간 데이터와 같은 특수 자료형은 쿼리하는 방법이 다르다.
문자 데이터는 고정 길이 또는 가변 길이 문자열로 저장될 수 있다. 고정길이 문자열은 공백으로 오른쪽이 채워지고, 가변길이 문자열은 공백으로 오른쪽이 채워지지 않고 항상 동일한 수의 바이트를 사용하지 않는다.
char(20) /* fixed-length */
varchar(20) /* variable-length */
char 열의 최대 길이는 255 바이트인 반면, varchar 열은 최대 65535 바이트까지 사용할 수 있다. 오라클 데이터베이스에서는 varchar2 자료형을 사용한다
텍스트 데이터의 경우, 자료형은 tinytext, text, mediumtext, longtext가 있다. 회사의 고객 서비스 부서에서 고객과의 상담내역을 저장하려고 자유형식의 데이터 입력용 열을 만들 때는 varchar이 적합하지만, 문서를 저장할 때는 mediumtext 혹은 longtext 자료형을 선택해야 한다.
숫자 데이터의 경우에는 tinyint, smallint, mediumint, int, bigint 등을 사용하는데 고객 주문의 배송여부를 나타내는 자료형으로는 boolean을 사용한다. My SQL의 경우에는 정수 integer, 부호없는 unsigned(0보다 크거나 같은) 등ㄱ의 자료형을 사용한다. 부동소수점 자료형의 경우에는 float(4,2)로 정의된 열은 소수 왼쪽 2자리, 소수 오른쪽 2자리를 저장한다. 17.8675의 경우에는 17.87로 반올림되며 178.375를 float(4,2)에 저장하면 오류가 발생한다.
시간 데이터의 경우에는 date(YYYY-MM-DD), datetime(YYYY-MM-DD HH:MI:SS), timestamp(YYYY-MM-DD HH:MI:SS), year(YYYY), time(HHH:MI:SS) 등을 사용하는데 마이크로초까지 사용할 수 있다. datetime(2)의 경우에는 시간값이 100분의 1초를 포함할 수 있다. 고객주문의 예상 배송일과 직원의 생년월일을 저장하는 열에는 date 자료형이 사용된다. 고객주문정보를 저장하는 열에는 datetime 자료형이 사용된다. 사용자가 테이블의 특정행을 마지막으로 수정한 시기를 추적하는 열에는 timestamp 자료형을 사용한다. timestamp 자료형에는 datetime 자료형과 동일한 정보(년, 월, 일, 시, 분, 초)가 저장되지만 테이블에 행이 추가되거나 수정될때 현재 날짜/시간으로 timestamp열이 자동으로 채워진다. 연도 데이터만 있는 열에는 year 자료형이 사용된다. 작업완료까지 필요한 시간 데이터를 포함하는 열에는 time 자료형이 사용된다.
테이블을 다음과 같이 생성하는데, 이름, 눈동자색, 생일, 주소, 좋아하는 음식등을 포함한다. name은 이름과 성으로 구성된 복합 객체이고, 여러사람이 동일한 이름, 눈동자색, 생일 등을 가질 수 있으므로 person table에는 고유성을 보장하는 열이 없다. favorite_foods 열은 0개 또는 1개 이상의 독립적인 항목을 포함하는 목록으로, 특정 음식이 어떤 사람에게 귀속될 수 있는지 알 수 있도록 데이터에 대한 별도의 테이블을 작성하고 person 테이블에 대한 외래키가 포함되도록하는 것이 가장 좋다. person 테이블에는 고유성을 보장하는 기본키(person_id)가 있으므로 person 테이블의 외래키가 포함된 favorite_food 테이블을 만들어야 한다. person_id 및 food 열은 favorite_food 테이블의 기본키를 구성하는데, person_id 열은 person 테이블에 대한 외래 키(두개의 테이블을 연결해주는 연결다리 역할)이기도 하다.
sql 스키마문을 다음과 같이 생성한다.
use sakila;
create table person
(person_id smallint unsigned,
fname varchar(20),
lname varchar(20),
eye_color char(2),
birth_date date,
street varchar(30),
city varchar(20),
state varchar(20),
country varchar(20),
postal_code varchar(20),
constraint pk_person primary key (person_id));테이블을 정의할 때는 기본키로 사용할 열을 데이터베이스 서버에 알려주어야 하므로 테이블에 제약조건을 만들어 작업을 수행한다. 테이블 정의에 여러 유형의 제약조건을 추가할 수 있는데, 현재 추가한 제약조건은 기본 키 제약조건이다. person_id 열에 생성되며 pk_person 이라는 이름이 지정된다.
person 테이블에 유용한 다른 유형의 제약조건이 있는데 특정 열에 대해 허용가능한 값을 제한하는 체크 제약조건이 그것이다. MySQL은 다음과 같은 체크 제약조건을 열 정의에 첨부할 수 있는데
eye_color CHAR(2) check (eye_color in ('br', 'bl', 'gr')),
eye_color enum('br', 'bl', 'gr'),위는 체크 제약조건을 사용한 예시이고, 아래는 My SQL서버에서 체크 제약조건을 자료형 정의에 병합할 수 있도록 enum이라는 문자 자료형을 사용한 예시이다. eye_color 열의 정의는 두번째와 같다.
use sakila;
create table person1
(person_id smallint unsigned AUTO_INCREMENT,
fname varchar(20),
lname varchar(20),
eye_color enum('br','bl','gr'),
birth_date date,
street varchar(30),
city varchar(20),
state varchar(20),
country varchar(20),
postal_code varchar(20),
constraint pk_person primary key (person_id));위와 같이 테이블 생성문을 실행한 후, desc person1 명령어를 사용하여 테이블 정의를 다음과같이 확인한다.
3열은 데이터를 테이블에 삽입할 때 특정 열의 데이터를 생략할 수 있는지를 나타내고, 4열은 기본 키나 외래키에 열이 포함되는지 여부를 나타낸다. person_id가 현재 기본 키에 해당된다. extra로 출력된 6열은 열에 적용될 수 있는 기타 정보를 표시한다. null은 (해당사항 없음, 알수 없음, 비어있는 셋)의 경우와 같이 값을 제공할 수 없는 다양한 경우에 사용된다.
create table favorite_food
(person_id smallint unsigned,
food varchar(20),
constraint pk_favorite_food primary key (person_id, food),
constraint fk_fav_food_person_id foreign key (person_id)
references person1 (person_id));person 테이블에 대한 create table문과 매우 유사하지만 다음과 같은 예외가 있다. 한 사람이 좋아하는 음식이 두 가지 이상일 수 있으므로, 테이블의 고유성을 보장하려면 person_id 열 이상의 것이 필요하다. 따라서 이 테이블에는 person_id 와 food라는 두 개의 기본키가 있다. favorite_food 테이블에는 외래키 제약조건이라는 또 다른 유형의 제약조건이 포함되는데, person_id 열의 값에 person 테이블에 있는 값만 포함되도록 제한한다. 예를 들면, person 테이블에 person_id가 27인 행이 아직 없으면 favorite_food 테이블에도 person_id가 27인 사람이 피자를 좋아한다는 것을 나타내는 행을 추가할 수 없다. 테이블을 처음 만들 때 외래키 제약조건을 만드는 것을 잊어버렸다면 나중에 alter table문으로 외래키 제약조건을 추가할 수 있다.
insert 문의 3가지 구성요소는
데이터를 추가할 테이블 이름 / 데이터를 채울 테이블의 열 이름 / 열을 채울 값 으로 구성되는데,
테이블의 모든 열이 not null로 정의되지 않는 한, 테이블의 모든 열에 데이터를 제공할 필요는 없다.
그러나, 특정 경우(발송 직전에 취소된 고객 주문의 배송일자)에는 특정데이터 행의 값을 update문을 통해서도 적용하지 못할 수도 있다.
모든 데이터베이스 서버는 안전하고 강력한 숫자키 생성방법을 제공하는데, 오라클 DB에서는 시퀀스 라는 별도의 스키마 개체가 사용되지만, My SQL의 경우에는 기본 키 열에 대한 자동증가 기능만 켜면 된다.
alter table person modify person_id smallint unsigned auto_increment;위와 같이, person 테이블의 person_id 열을 재정의하면, person의 extra 열 아래에 자동증가 기능이 표시된다.
Error Code: 1833. Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'sakila.favorite_food'person 테이블에 데이터를 삽입할 때 person_id 열에 null값을 제공하기만 하면 My SQL은 사용가능한 다음 숫자로 열을 채운다. My SQL에서는 자동 증가열일 때 기본적으로 1부터 시작된다. 위와 같이 오류가 발생하는 이유는, favorite_food 테이블에 외래키제약(fk_fav_food_person_id)이 걸려있기 때문이다. 이 쿼리를 실습할 때는 이 제약조건을 비활성화 한다음, 실습을 마치고 다시 제약조건을 활성화 해야한다.
set foreign_key_checks=0;
alter table person modify person_id smallint unsigned auto_increment;
set foreign_key_checks=1;
이후, insert문을 통해 데이터를 추가한다.
insert into person
(person_id, fname, lname, eye_color, birth_date)
values (null, 'William', 'Turner', 'BR', '1972-05-27');
When you omit the column names in an INSERT statement, you need to provide values for every column in the table in the correct order. 만약 위와 같이 insert 했는데 아래와 같은 오류가 발생할 수 있다.
Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.sakila를 기본 schema로 설정하면 오류가 해결된다. 그래도 아래와 같은 오류가 뜰 수 있다.
Error Code: 1048. Column 'person_id' cannot be null
이건 아까 기본 키 열에 대한 자동증가 기능을 켜지 않았기 때문이다. 자동으로 기본키인 person_id가 채워지지 않아서 발생하는 오류이다.
이와 같이 기본키가 1의 값에 해당하지만, person 테이블에 행이 아직 하나뿐이므로 관심있는 행을 지정하지 않고 테이블의 행을 모두 검색했다. 만약 테이블에 행이 두 개 이상 있다면 where 절을 추가하여 person_id 열의 값이 1인 행에 대해서만 데이터를 검색할 수 있다.
select person_id, fname, lname, birth_date
from person1
where person_id = 1;
birth_date 열에 제공된 값은 문자열인데, 필수형식과 일치하면 문자열은 날짜로 변환된다. 단, 열 이름과 값은 각각 그 개수와 자료형이 일치해야 한다. 7개의 열에 이름을 지정하고 6개의 값만 제공하거나, 해당 열에 적합한 자료형으로 변환할 수 없는 값을 제공하면 오류가 발생한다.
insert into favorite_food (person_id, food)
values (1, 'pizza');
insert into favorite_food (person_id, food)
values (1, 'cookies');
insert into favorite_food (person_id, food)
values (1, 'nachos');favorite_food에 insert 하는데 다음과 같은 오류가 발생한다.
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`sakila`.`favorite_food`, CONSTRAINT `fk_fav_food_person_id` FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`))
이를 해결하려면 외래키 관계를 재설정한다. 아마 위에서 제대로 입력이 되지 않아서일 것이다. (person -> person1)
DROP TABLE IF EXISTS favorite_food;
CREATE TABLE favorite_food
(
person_id SMALLINT UNSIGNED,
food VARCHAR(20),
CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
REFERENCES person1 (person_id)
);
다음으로 order by 절을 통해, 가장 좋아하는 음식을 알파벳 순서로 검색한다.
insert into person1
(person_id, fname, lname, eye_color, birth_date,
street, city, state, country, postal_code)
values (null, 'susan', 'smith', 'bl', '1975-11-02',
'23 maple st.', 'arlignton', 'va', 'usa', '20220');
데이터를 xml로 가져오려면 --xml 옵션을 사용한다.(My SQL에서)
mysql -u lrngsql -p --xml bank
select * from favorite_food;SQL 서버를 사용하면, 아래와 같이 쿼리 끝에 for xml 절만 추가하면 된다.
select * from favorite_food
for xml auto, elements
데이터를 수정하려면 UPDATE 문을 사용한다.
UPDATE person1
SET street = '1224 Tremont St.',
city = 'Boston',
state = 'MA',
country = 'USA',
postal_code = '02138'
where person_id = 1;where person_id < 10이면 두 행 모두 수정된다.
데이터를 삭제하려면 DELETE 문을 사용한다.
delete from person1
where person_id = 2;
기본키를 사용하여 대상이 되는 행을 분리하고, 테이블에서 행 하나가 삭제된다.
update 문과 마찬가지로 where 절의 조건에 따라 둘 이상의 행을 삭제할 수 있고, where 절을 생략하면 모든 행이 삭제 된다.
좋은 구문을 다음과 같은 이유로 망칠 수 있다. 예를 들면, person과 favorite_food 테이블에서 데이터를 삽입하거나 수정할 떄 충돌할 수도 있다. 발생할 수 있는 몇가지의 일반적인 실수는 다음과 같다.
고유하지 않은 기본 키의 경우, 테이블 정의에는 기본 키 제약 조건 생성이 포함된다. My SQL은 중복 키값을 테이블에 삽입하지 않아야 한다. person_id 열의 자동 증가 기능을 무시하고 person 테이블에 person_id가 1인 다른 행을 생성하려다가 실패한다.
insert into person1
(person_id, fname, lname, eye_color, birth_date)
values (1, 'Charles', 'Fulton', 'gr', '1968-01-15');Error Code: 1062. Duplicate entry '1' for key 'person1.PRIMARY'그러나 현재의 스키마 개체에서는 person_id 열의 값만 서로 다르다면 이름, 주소, 생년월일이 전부 같은 두 행을 생성하는 것을 막을 수 없다.
존재하지 않는 외래 키의 경우, favorite_food 테이블의 테이블 정의에는 person_id 열에 대한 외래 키 제약조건이 있다. 이 제약조건은 favorite_food 테이블에 입력된 person_id 의 모든 값이 person 테이블에 존재함을 보증한다. 이 제약조건을 위반하면 다음과 같은 오류가 발생한다.
insert into favorite_food (person_id, food)
values (999, 'lasagna');Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`sakila`.`favorite_food`, CONSTRAINT `fk_fav_food_person_id` FOREIGN KEY (`person_id`) REFERENCES `person1` (`person_id`))favorite_food 테이블은 일부 데이터가 person 테이블에 의존하므로 favorite_food 테이블은 하위 child로 간주되고 person 테이블은 상위 parent로 간주된다. 두 테이블 모두에 데이터를 입력하려면, 상위 person 테이블에 관련행을 작성해야 favorite_food 테이블에 데이터를 입력할 수 있다.
열 값 위반의 경우, person테이블의 eye_color 열은 각각의 색깔일 때 일대일로 한가지 값으로 제한된다. 열값을 다른 값으로 잘못 지정하려고 하면 아래와 같이 에러가 발생한다.
update person1
set eye_color = 'zz'
where person_id = 1;Error Code: 1265. Data truncated for column 'eye_color' at row 1
날짜형식과 일치하지 않는 날짜 형식을 사용한 경우에도, 에러가 발생한다. date 열을 채울 문자열을 구성할 때, 해당 문자열이 예상 형식과 일치하지 않으면, 또 다른 오류가 발생한다.
update person1
set birth_date = 'dec-21-1980'
where person_id = 1;Error Code: 1292. Incorrect date value: 'dec-21-1980' for column 'birth_date' at row 1
기본 형식에 의존하지 않고 형식 문자열을 명시적으로 지정하는 편이 좋다. str_to_date 함수를 사용하여 사용할 형식 문자열을 지정한다.
update person1
set birth_date = str_to_date('dec-21-1980', '%b-%d-%Y')
where person_id = 1;
MySQL은 %Y를 사용하여 4글자 연도를 나타낸다. 문자열을 datetime으로 변환할 때 아래와 같은 포맷을 사용한다.
%a the short weekday name, such as Sun, Mon, ...
%b the short month name, such as Jan, Feb, ...
%c the numeric month (0..12)
%d the numeric day of the month (00.31)
%H the hour of the day 24시간 형식 (00..23)
%h the hour of the day 12시간 형식 (01..12)
%i the minutes within the hour (00..59)
%j the day of year (001..366)
%M the full month name (January.. December)
%m the numeric month
%p AM or PM
%s the number of seconds(00..59(
%W the full weekday name (Sunday..Saturday)
%w the numeric day of the week (0..6)
댓글