1 / 6

SQL for Web Nerds

SQL for Web Nerds. http://philip.greenspun.com/sql 자료를 이용함 !. 시작 1. Mailing List 관리를 위한 테이블로 create table mailing_list ( email varchar(100) not null primary key, name varchar(100) ); not null 이란 ? Primary key 이란 ? Insert 명령문 insert into mailing_list (name, email)

deborah-house
Download Presentation

SQL for Web Nerds

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL for Web Nerds http://philip.greenspun.com/sql자료를 이용함 !

  2. 시작 1 • Mailing List 관리를 위한 테이블로 • create table mailing_list • ( email varchar(100) not null primary key, • name varchar(100) ); • not null 이란? Primary key 이란? • Insert 명령문 • insert into mailing_list (name, email) • values ('Philip Greenspun','philg@mit.edu'); • (name, email) 을 지정해 주는 것이 테이블 구조의 변화에도 영향을 안 받음 ! • 테이블 구조 변경 • alter table mailing_list add (phone_number varchar(20));

  3. 시작 2 • 입력 문자중에 ‘ 가 있는 경우 • insert into mailing_list (name, email) • values ('Michael O''Grady','ogrady@fastbuck.com'); • select 명령문의 예제 • SQL> column email format a25 • SQL> column name format a25 • SQL> column phone_number format a12 • SQL> set feedback on • SQL> select * from mailing_list;

  4. 시작 3 • 이메일과 전화 번호를 같이 저장할려고 하면~~ 그리고 핸폰/직장/집/가게 등등의 한 사람이 여러개의 전화를 소유하고 있는 경우는? • drop table mailing_list; • create table mailing_list • ( email varchar(100) not null primary key, • name varchar(100) ); • create table phone_numbers • ( email varchar(100) not null references mailing_list(email), number_type varchar(15) check (number_type in ('work','home','cell','beeper')), • phone_number varchar(20) not null ); • integrity error : foreign key 값이 존재하지 않는 경우~~ • SQL> insert into phone_numbers values ('ogrady@fastbuck.com','work','(800) 555-1212'); • ORA-02291: integrity constraint (SCOTT.SYS_C001080) violated - parent key not found

  5. 시작 4 • referential integrity constraint ("references mailing_list") • insert into mailing_list (name, email) • values ('Philip Greenspun','philg@mit.edu'); • insert into mailing_list (name, email) • values ('Michael O''Grady','ogrady@fastbuck.com'); • insert into phone_numbers • values ('ogrady@fastbuck.com','work','(800) 555-1212'); • insert into phone_numbers • values ('ogrady@fastbuck.com','home','(617) 495-6000'); • insert into phone_numbers • values ('philg@mit.edu','work','(617) 253-8574'); • insert into phone_numbers • values ('ogrady@fastbuck.com','beper','(617) 222-3456');

  6. 시작 5 • check error : check (number_type in ('work','home','cell','beeper')) • ORA-02290: check constraint (SCOTT.SYS_C001079) violated • join 예제 1 ( Cartesian Product : 테이블 서로 짝 짓기 !) N*M 개의 row • select * from mailing_list, phone_numbers; • join 예제 2 (공통되는 key 값을 갖는 것들 만 !) • select * from mailing_list, phone_numbers where mailing_list.email = phone_numbers.email; • delete 예제 • delete from phone_numbers where email = 'philg@mit.edu'; • update 예제 • update mailing_list set name = 'Phil-baby Greenspun' where email = 'philg@mit.edu';

More Related