Oracle Ex

CREATE TABLE member (
member_id VARCHAR2(20) NOT NULL,
member_pw VARCHAR2(50) NOT NULL,
member_nickname NVARCHAR2(20) NOT NULL,
member_name NVARCHAR2(30) NOT NULL,
member_gender CHAR(2) NOT NULL,
member_email VARCHAR2(50) NOT NULL,
member_phone VARCHAR2(20) NOT NULL,
member_birth DATE NOT NULL,
member_zip VARCHAR2(10) NULL,
member_address_basic VARCHAR2(255) NULL,
member_address_detail VARCHAR2(255) NULL,
member_joindate TIMESTAMP NOT NULL,
PRIMARY KEY(member_id));

member 테이블 생성

 

// 변경 + 제약조건 달아주기

ALTER TABLE member ADD CONSTRAINT member_id_pk PRIMARY KEY(member_id);
-- ALTER TABLE member ADD CONSTRAINT member_password_nn CHECK(member_password IS NOT NULL);
ALTER TABLE member MODIFY (member_password CONSTRAINT member_password_nn NOT NULL);
-- ALTER TABLE member ADD CONSTRAINT member_nickname_nn CHECK(member_nickname IS NOT NULL);
ALTER TABLE member MODIFY (member_nickname CONSTRAINT member_nickname_nn NOT NULL);
-- ALTER TABLE member ADD CONSTRAINT member_name_nn CHECK(member_name IS NOT NULL);
ALTER TABLE member MODIFY (member_name CONSTRAINT member_name_nn NOT NULL);
-- ALTER TABLE member ADD CONSTRAINT member_gender_nn CHECK(member_gender IS NOT NULL);
ALTER TABLE member MODIFY (member_gender CONSTRAINT member_gender_nn NOT NULL);
ALTER TABLE member ADD CONSTRAINT member_gender_ck CHECK(member_gender IN ('m', 'f'));
-- ALTER TABLE member ADD CONSTRAINT member_email_nn CHECK(member_email IS NOT NULL);
ALTER TABLE member MODIFY (member_email CONSTRAINT member_email_nn NOT NULL);
ALTER TABLE member ADD CONSTRAINT member_email_un UNIQUE(member_email);
-- ALTER TABLE member ADD CONSTRAINT member_phone_nn CHECK(member_phone IS NOT NULL);    
ALTER TABLE member MODIFY (member_phone CONSTRAINT member_phone_nn NOT NULL);
-- ALTER TABLE member ADD CONSTRAINT member_birth_nn CHECK(member_birth IS NOT NULL);
ALTER TABLE member MODIFY (member_birth CONSTRAINT member_birth_nn NOT NULL);
ALTER TABLE member ADD CONSTRAINT member_joindate_nn CHECK(member_joindate IS NOT NULL);

컬럼에 주석 달아주기

COMMENT ON COLUMN TABLE.column IS '주석';

주석 --

COMMENT ON COLUMN member.member_id IS '아이디';
COMMENT ON COLUMN member.member_pw IS '비밀번호';
COMMENT ON COLUMN member.member_nickname IS '별명';
COMMENT ON COLUMN member.member_gender IS '성별';
COMMENT ON COLUMN member.member_name IS '이름';
COMMENT ON COLUMN member.member_email IS '메일주소';
COMMENT ON COLUMN member.member_phone IS '핸드폰번호';
COMMENT ON COLUMN member.member_birth IS '생일';
COMMENT ON COLUMN member.member_zip IS '우편번호';
COMMENT ON COLUMN member.member_address_basic IS '집 주소';
COMMENT ON COLUMN member.member_address_detail IS '상세 집주소';
COMMENT ON COLUMN member.member_joindate IS '가입일';

INSERT INTO table VALUES 로 값을 넣어보겠습니다.

INSERT INTO member VALUES ('thej', '1234@', '더조은', '1', '홍길동', 'theJ@jjjj.com', '010-111-1234', '2010-10-10',
'06664','서울시 서초구','신논현역 바로앞 ',SYSDATE);

 

SELECT FROM TABLE; 을 사용하여 테이블 출력

||

SELECT * FROM member WHERE member_id='thej';

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE member (
member_id VARCHAR2(20) CONSTRAINT member_id_pk PRIMARY KEY,
member_pw VARCHAR2(50) CONSTRAINT member_pw_nn NOT NULL,
member_nickname NVARCHAR2(20) CONSTRAINT member_nickname_nn NOT NULL,
member_name NVARCHAR2(30) CONSTRAINT member_name_nn NOT NULL,
member_gender CHAR(20) CONSTRAINT member_gender_nn NOT NULL,
member_email VARCHAR2(50) CONSTRAINT member_email_nn NOT NULL,
member_phone VARCHAR2(20) CONSTRAINT member_phone_nn NOT NULL,
member_birth DATE CONSTRAINT member_brith_nn NOT NULL,
member_zip VARCHAR2(10) NULL,
member_address_basic VARCHAR2(255) NULL,
member_address_detail VARCHAR2(255) NULL,
member_joindate TIMESTAMP CONSTRAINT member_joindate_nn NOT NULL);

보면 member_id나 밑에에 보면  _pk _nn 같은 것들이 적혀있다 

이건 제약조건, 약어라고 생각하면 됩니다.

제약 조건	약어	Constraint Type
UNIQUE	_UK	U
PRIMARY KEY	_PK	P
FOREIGN KET	_FK	R
NOT NULL	_NN	C
CHECK	_CK	C