[ SQL ] 테이블 생성 및 더미 데이터 삽입

환경 : DBeaver

 

 

해당 프로젝트에서 사용하게 될 테이블 정보 입니다.

CREATE TABLE user_info( --테이블 생성
    user_id VARCHAR2(20) PRIMARY KEY,
    user_password VARCHAR2(15) NOT NULL,
    user_nickname VARCHAR2(30) NOT NULL UNIQUE,
    user_birthdate CHAR(10),
    user_phone CHAR(11),
    user_email VARCHAR2(60) NOT NULL,
    user_zipcode VARCHAR2(6),
    user_address_line1 VARCHAR2(300),
    user_address_line2 VARCHAR2(300),
    user_role VARCHAR2(12) DEFAULT 'RegularUser' NOT NULL,
    user_reward_points NUMBER DEFAULT 0 NOT NULL,
    user_registration_date DATE DEFAULT SYSDATE NOT NULL,
    user_last_login_date DATE,
    CHECK(REGEXP_LIKE(user_id, '^[a-z][a-z0-9]{7,19}$')),
    CHECK(REGEXP_LIKE(user_password, '^[A-Za-z0-9!@#$]{6,15}$')
        AND
        REGEXP_LIKE(user_password, '[A-Z]+')
        AND
        REGEXP_LIKE(user_password, '[a-z]+')
        AND
        REGEXP_LIKE(user_password, '[0-9]+')
        AND
        REGEXP_LIKE(user_password, '[!@#$]+')
    ),
    CHECK(REGEXP_LIKE(user_nickname, '^[가-힣][가-힣0-9]{1,9}$')),
    CHECK(REGEXP_LIKE(user_phone, '^010[1-9][0-9]{3}[0-9]{4}$')),
    CHECK(REGEXP_LIKE(user_birthdate, '^(19[0-9]{2}|20[0-9]{2})-(02-(0[1-9]|1[0-9]|2[0-8])|(0[469]|11)-(0[1-9]|1[0-9]|2[0-9]|30)|(0[13578]|1[02])-(0[1-9]|1[0-9]|2[0-9]|3[01]))$')),
    CHECK(REGEXP_LIKE(user_email, '@')),
    CHECK(REGEXP_LIKE(user_zipcode, '^[0-9]{5,6}$')),
    CHECK((user_zipcode IS NULL AND user_address_line1 IS NULL AND user_address_line2 IS NULL)
        OR
        (user_zipcode IS NOT NULL AND user_address_line1 IS NOT NULL AND user_address_line2 IS NOT NULL)
    ),
    CHECK(user_role IN ('RegularUser', 'PremiumUser', 'Admin')),
    CHECK(user_reward_points >= 0)
);
DROP TABLE user_info; --테이블 삭제

-- 더미 데이터 삽입
INSERT INTO user_info (user_id, user_password, user_nickname, user_birthdate, user_phone, user_email, user_zipcode, user_address_line1, user_address_line2, user_role, user_reward_points, user_registration_date, user_last_login_date)
VALUES('johndoe123', 'Passw0rd!', '존도', '1990-05-15', '01012345678', 'john.doe@email.com', '12345', '123 Main St', 'Apt 45', 'RegularUser', 100, SYSDATE, SYSDATE);

INSERT INTO user_info (user_id, user_password, user_nickname, user_birthdate, user_phone, user_email, user_zipcode, user_address_line1, user_address_line2, user_role, user_reward_points, user_registration_date, user_last_login_date)
VALUES('janesmith456', 'SecurePwd123!', '제인스미스', '1985-09-20', '01098765432', 'jane.smith@email.com', '54321', '456 Oak St', 'Unit 67', 'PremiumUser', 250, SYSDATE, NULL);

INSERT INTO user_info (user_id, user_password, user_nickname, user_birthdate, user_phone, user_email, user_zipcode, user_address_line1, user_address_line2, user_role, user_reward_points, user_registration_date, user_last_login_date)
VALUES('adminmaster1', 'AdminPass123!', '관리자1', '1980-01-01', '01011112222', 'admin@email.com', '98765', '789 Admin St', 'Floor 12', 'Admin', 500, SYSDATE, NULL);

INSERT INTO user_info (user_id, user_password, user_nickname, user_birthdate, user_phone, user_email, user_zipcode, user_address_line1, user_address_line2, user_role, user_reward_points, user_registration_date, user_last_login_date)
VALUES('alice8956', 'AlicePwd456!', '앨리스', '1989-08-10', '01055556666', 'alice@email.com', '56789', '789 Maple St', 'Apt 23', 'RegularUser', 150, SYSDATE, NULL);

INSERT INTO user_info (user_id, user_password, user_nickname, user_birthdate, user_phone, user_email, user_zipcode, user_address_line1, user_address_line2, user_role, user_reward_points, user_registration_date, user_last_login_date)
VALUES('bobjones', 'BobSecure789!', '밥존', '1982-04-25', '01077778888', 'bob@email.com', '34567', '456 Pine St', 'Unit 89', 'RegularUser', 75, SYSDATE, NULL);

INSERT INTO user_info (user_id, user_password, user_nickname, user_birthdate, user_phone, user_email, user_zipcode, user_address_line1, user_address_line2, user_role, user_reward_points, user_registration_date, user_last_login_date)
VALUES('gracekim', 'GracePass321!', '그레이스', '1995-11-30', '01099990000', 'grace@email.com', '67890', '901 Oak St', 'Floor 5', 'PremiumUser', 300, SYSDATE, NULL);


SELECT * FROM user_info; --테이블 정보 확인

---

[ 테이블 생성 설명 ]

 - 테이블의 기본 정보(변수명, 변수타입, 정규표현식을 제외한 조건)을 먼저 설정한 후, CHECK 조건을 사용하는 모든 변수에 대한 조건을 작성하였습니다. 

      → 이렇게 설정함으로써 모든 조건을 한 번에 체크할 수 있어서 성능면에서 상대적으로 좋습니다. 

 - { 각 변수에 따른 조건 설명 }

변수명	조건
user_id	기본키(Primary Key), 소문자 영문으로 시작하며 영문소문자/숫자 8-20자로 이루어짐
user_password	필수항목, 영어대소문자/숫자/특수문자(!@#$)를 각각 하나 이상 포함한 6-15자 비밀번호
user_nickname	필수항목, 한글로 시작하고, 한글/숫자로 2-10자로 이루어짐
user_birthdate	선택항목, YYYY-MM-DD형식의 문자열
user_phone	선택항목, 010XXXXXXXX 형태의 문자열
user_email	필수항목, '@'가 들어간 60bytes 이내의 문자열
user_zipcode	선택항목, 5-6자리 숫자로 이루어짐
user_address_line1	선택항목, 주소1
user_address_line2	선택항목, 주소2(상세주소)
	user_zipcode, user_address_line1, user_address_line2가 모두 NULL이거나 NOT NULL(모두 값이 존재) 이거나만 가능
사용자가 입력하지 않는 정보 ↓
user_role	RegularUser/PremiumUser/Admin 중 하나 기입. 가입 시 ReqularUser로 등록
user_reward_points	0점 이상. 가입 시 0으로 자동 설정
user_registration_date	날짜 데이터. 가입 시 현재 시각으로 자동 설정
user_last_login_date	날짜 데이터. 가입 시 null, 로그인 시 최근 로그인 시간으로 변경

 

 + 시퀀스를 추가적으로 사용해도 된다.

 

 

 

 

개인 공부 기록용입니다.