Oracle 삭제방법, SQL plus / 회원관리 스키마 , 운영쿼리

Luna·2022년 12월 20일

ALTER CASEWHEN oracle sqlplus sysdba 스키마 운영쿼리 회원관리

EZEN

목록 보기

8/40

Oracle 삭제 방법

제어판에서 삭제하게 되면 레지스터리는 남아있어서 재설치시 문제가 생긴다.
설치 프로그램(setup.exe)을 실행 한 후 Remove 선택해서 지워줘야 재설치시 문제가 생기지 않는다.
만약 제어판에서 먼저 삭제를 실행했다면 수동으로 삭제해야 한다.

참고 블로그 : [ORACLE] 오라클(11g) 수동 완전 삭제

SQL Plus

SQL plus는 오라클에서 기본적으로 제공하는 도구로 데이터베이스를 운영할 때 발생할 수 있는 문제에 대비해서 사용자 환경에 구애받지 않고 사용 할 수 있는 장점 뿐만 아니라, 오라클을 설치할 때 같이 설치하기 때문에 따로 설치할 필요 없는 장점을 가지고 있다.

관리자 계정으로 접속 하기

처음 접속할 때 (비밀번호 생각 안날 때) sqlplus / as sysdba 라고 입력하면 관리자 권한으로 들어가짐
비밀번호를 입력하라고 뜨는데 화면에 나오지 않으니 오타나지 않게 잘 설정하면 됨.
그 다음에 sqlplus sys/1234 as sysdba 라고 입력하면 들어가짐.

비밀번호 변경

sys 계정은 어떤 계정이든 비밀번호 변경이 가능하다 ALTER user 계정명 identified by 비밀번호;

회원관리

등급 설정 하는 방법

TABLE로 연결 - JOIN을 해야 함.

CASE WHEN 이용하기 : SELECT CASE WHEN 조건 THEN 바꿀데이터 END

회원 등급 관리 (변수명)
1 : gradeNo
일반회원 : gradeName

등급 관리 : CASE WHEN 사용

회원관리 스키마

-- 회원관리 스키마
-- 1. 제거
DROP TABLE member CASCADE CONSTRAINTS;

-- 2. 생성
-- VARCHAR2는 길이를 반드시 지정해야 하고, NUMBER는 선택사항이다.
-- DEFAULT 값을 세팅 해 놓으면 NOT NULL은 의미가 없으므로 사용하지 않는다.
CREATE TABLE member (
    id VARCHAR2(20) PRIMARY KEY,
    pw VARCHAR2(20) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    -- CHECK 제약조건 : gender = '여자' or gender = '남자'
    -- 데이터가 정확한지 아닌지 판별하기 위해 제약 조건을 걸어 놓는다.
    gender VARCHAR2(6) NOT NULL CHECK(gender in('여자','남자')),
    birth DATE NOT NULL,
    tel VARCHAR2(13),
    email VARCHAR2(50) NOT NULL,
    regDate DATE DEFAULT SYSDATE,
    conDate DATE DEFAULT SYSDATE,
    -- DEFAULT를 먼저 작성 하고 제약조건은 그 뒤에 작성한다.
    status VARCHAR2(6) DEFAULT '정상' CHECK(status in('정상','강퇴','탈퇴','휴면')),
    grade NUMBER(2) DEFAULT 1 -- 1:일반회원, 9:관리자
);

-- 3. 샘플데이터 - 일반회원(1), 관리자(9)
-- 아이디, 비밀번호, 이름, 성별, 생년월일, 연락처, 이메일, 등급(관리자인 경우:9)
-- 컬럼을 입력하지 않으면 기본값(DEFAULT)으로 나옴.
-- 일반회원(등급이 1 - 기본)의 회원가입
INSERT INTO member (id, pw, name, gender, birth, tel, email)
VALUES ('test', '1111', '홍길동', '남자', '1990-01-01', '010-1234-4567', 'hong@naver.com');
-- 관리자의 회원가입
INSERT INTO member (id, pw, name, gender, birth, tel, email, grade)
VALUES ('admin', '3333', '관리자', '여자', '1992-01-01', '010-4567-1234', 'admin@naver.com', 9);
COMMIT;

-- 4. 확인
SELECT * FROM member;

회원관리 운영 쿼리

-- 회원관리 운영 쿼리
-- 1. 리스트 (관리자) - List<VO>
-- 아이디, 이름, 성별, 생년월일, 연락처, 최근접속일, 상태, 등급(등급번호, 등급명)
SELECT id, name, gender, birth ,tel, conDate, status, grade
FROM member
-- WHERE status = '정상' -- 정상인 회원만 보기
-- WHERE status != '탈퇴' or status != '강퇴' -- 강퇴, 탈퇴 회원만 제외하고 보기
ORDER BY id ASC;

-- 2. 보기 (관리자: 회원정보보기 / 일반회원: 내정보보기 - id ='test'
SELECT id, name, gender, birth, tel, email, regDate, status, grade
FROM member
WHERE id = 'test';

-- 3. 등록 (회원가입) -- 아이디, 비밀번호, 이름, 성별, 생년월일, 연락처, 이메일
-- 아이디 중복 확인 (2.보기)
SELECT id FROM member where id = 'test';
-- 사용자에게 처리 결과(중복된 아이디 입니다)를 보여줘야 하므로 service를 따로 만들어서 처리해야 한다.

SELECT id FROM member where id = 'snow';
-- null값이 나오면 이 아이디는 사용 가능하다고 보여주면 됨

INSERT INTO member (id, pw, name, gender, birth, tel, email)
VALUES ('kyh', '1111', '김영희', '여자', '1991-03-05', '010-1111-2222', 'kyh@naver.com');
INSERT INTO member (id, pw, name, gender, birth, tel, email)
VALUES ('kcs', '1111', '김철수', '남자', '1991-03-03', '010-5555-6666', 'kcs@naver.com');
COMMIT;

-- 4. 수정 (일반회원 : 회원정보수정 / 관리자: 등급이나 상태 변경)
UPDATE member SET tel='010-2222-3333'
WHERE name = '김영희' AND pw ='1111';
COMMIT;

-- 상태 변경 (관리자 또는 시스템)
-- 접속 날짜가 00일 이상이면 휴면으로 바꾸는 쿼리를 작성하고, 휴면 해제 요청시 휴면 회원을 정상으로 바꾸는 쿼리를 작성한다.
UPDATE member SET status='휴면'
WHERE id = 'kyh';
COMMIT;

-- 등급 변경
UPDATE member SET grade=9
WHERE id = 'kcs';
COMMIT;

-- 5. 삭제 (없음 - 상태를 '정상'에서 '탈퇴'로 변경 : update)
UPDATE member SET status='탈퇴'
WHERE name ='홍길동';
COMMIT;

-- 6. 로그인 (2.보기) / 로그아웃 (DB 필요 없음)
-- id와 pw를 입력하면 DB로 로그인에 필요한 정보를 보낸다.
SELECT id, name, gender, birth, grade
FROM member
WHERE id = 'kcs' AND pw ='1111';
COMMIT;
-- 데이터가 NULL로 나오면 '아이디나 패스워드를 확인 해 주세요'

-- 7. 아이디 찾기 (2.보기)
-- 비밀번호 찾기 (2.보기 또는 4.수정 - JAvA에서 비밀번호를 만든다 -> DB 비밀번호를 수정한다 -> JAVA에서 수정한 데이터를 보여준다.)
-- 임시 비밀번호를 만들어서 메일로 보내주거나(나중에 사용자가 수정하게끔), 비밀번호 변경 링크를 메일로 보내준다.
SELECT id
FROM member
WHERE name = '김철수' AND tel = '010-5555-6666';
COMMIT;

CASE WHEN

SELECT id, name, gender, birth, grade as gradeNo,
case when grade = 1 then '일반회원'
     when grade = 0 then '관리자'
     end as gradeName
FROM member
WHERE id = 'kcs' AND pw ='1111';
COMMIT;
grade의 값을 gradeNo 변수명으로 가져온다.
grade의 값을 gradeName 변수명에 '일반회원', '관리자'로 가져온다.
as는 생략 가능하다.

오류 발생 시 확인 방법

만약에 SYS_C007082의 오류가 뜬다면 구글링으로 오류를 해결 할 수 없다. 이 번호는 테이블 생성시마다 계속 바뀌는 번호(오라클이 알아서 지정해주는)이므로, 저렇게 일련번호로 오류가 뜬다면 테이블을 열어서 어느 컬럼에서 오류가 났는지 확인 할 수 있어야 한다.

등급 관리 : TABLE 생성해서 사용

member테이블의 grade는 grade테이블의 gradeNo와 gradeName을 참조하고 있으므로 grade 테이블을 먼저 만들고 member 테이블을 만든다. 삭제를 할 때는 member테이블 먼저 지우고 grade를 나중에 지워야 한다.

선택한 테이블의 테두리가 빨간색으로 보인다.

회원관리 스키마

-- 회원관리 스키마
-- mamber테이이블이 grade테이블을 참조하고 있다.

-- 1. 제거 (FK->PK)
DROP TABLE member CASCADE CONSTRAINTS;
DROP TABLE grade CASCADE CONSTRAINTS;

-- 2. 생성(PK->FK)
CREATE TABLE grade (
    gradeNo number(2) PRIMARY KEY, -- member 테이블의 grade와 타입이 같아야 한다.
    gradeName VARCHAR2(30) NOT NULL
);

CREATE TABLE member (
    id VARCHAR2(20) PRIMARY KEY,
    pw VARCHAR2(20) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    gender VARCHAR2(6) NOT NULL CHECK(gender in('여자','남자')),
    birth DATE NOT NULL,
    tel VARCHAR2(13),
    email VARCHAR2(50) NOT NULL,
    regDate DATE DEFAULT SYSDATE,
    conDate DATE DEFAULT SYSDATE,
    status VARCHAR2(6) DEFAULT '정상' CHECK(status in('정상','강퇴','탈퇴','휴면')),
     -- 1:일반회원, 9:관리자 - grade 테이블로 연결
    grade NUMBER(2) DEFAULT 1 REFERENCES grade(gradeNo)
);

-- 3. 샘플데이터 (PK->FK) - 일반회원(1), 관리자(9)
-- PK에 데이터를 먼저 집어 넣어야 FK에서 사용할 수 있다.
-- 테이블의 모든 항목의 데이터를 순서대로 입력할 때 컬럼 이름을 생략할 수 있다. (보편적으로는 쓰기 때문에 다 쓰는게 좋음)
INSERT INTO grade (gradeNo, gradeName)
VALUES(1, '일반회원');
INSERT INTO grade -- 컬럼 이름 생략 가능
VALUES(9, '관리자'); -- 순서에 맞게 데이터를 넣는다.
COMMIT;

-- 아이디, 비밀번호, 이름, 성별, 생년월일, 연락처, 이메일, 등급(관리자인 경우:9)
-- 컬럼을 입력하지 않으면 기본값(DEFAULT)으로 나옴.
-- 일반회원(등급이 1 - 기본)의 회원가입
INSERT INTO member (id, pw, name, gender, birth, tel, email)
VALUES ('test', '1111', '홍길동', '남자', '1990-01-01', '010-1234-4567', 'hong@naver.com');
-- 관리자의 회원가입
INSERT INTO member (id, pw, name, gender, birth, tel, email, grade)
VALUES ('admin', '3333', '관리자', '여자', '1992-01-01', '010-4567-1234', 'admin@naver.com', 9);
COMMIT;

-- 4. 확인
SELECT * FROM member;

회원관리 운영 쿼리

-- 회원관리 운영 쿼리
-- 1. 리스트 (관리자) - List<VO>
-- 아이디, 이름, 성별, 생년월일, 연락처, 최근접속일, 상태, 등급(등급번호, 등급명)
SELECT id, name, gender, birth ,tel, conDate, status, grade gradeNo,
case when grade = 1 then '일반회원'
     when grade = 9 then '관리자'
     end as gradeName
FROM member
-- WHERE status = '정상' -- 정상인 회원만 보기
-- WHERE status != '탈퇴' or status != '강퇴' -- 강퇴, 탈퇴 회원만 제외하고 보기
ORDER BY id ASC;

-- 2. 보기 (관리자: 회원정보보기 / 일반회원: 내정보보기 - id ='test'
SELECT id, name, gender, birth, tel, email, regDate, status, grade gradeNo,
case when grade = 1 then '일반회원'
     when grade = 9 then '관리자'
     end as gradeName
FROM member
WHERE id = 'test';

-- 3. 등록 (회원가입) -- 아이디, 비밀번호, 이름, 성별, 생년월일, 연락처, 이메일
-- 아이디 중복 확인 (2.보기)
SELECT id FROM member where id = 'test';
-- 사용자에게 처리 결과(중복된 아이디 입니다)를 보여줘야 하므로 service를 따로 만들어서 처리해야 한다.

SELECT id FROM member where id = 'snow';
-- null값이 나오면 이 아이디는 사용 가능하다고 보여주면 됨

INSERT INTO member (id, pw, name, gender, birth, tel, email)
VALUES ('kyh', '1111', '김영희', '여자', '1991-03-05', '010-1111-2222', 'kyh@naver.com');
INSERT INTO member (id, pw, name, gender, birth, tel, email)
VALUES ('kcs', '1111', '김철수', '남자', '1991-03-03', '010-5555-6666', 'kcs@naver.com');
COMMIT;

-- 4. 수정 (일반회원 : 회원정보수정 / 관리자: 등급이나 상태 변경)
UPDATE member SET tel='010-2222-3333'
WHERE name = '김영희' AND pw ='1111';
COMMIT;

-- 상태 변경 (관리자 또는 시스템)
-- 접속 날짜가 00일 이상이면 휴면으로 바꾸는 쿼리를 작성하고, 휴면 해제 요청시 휴면 회원을 정상으로 바꾸는 쿼리를 작성한다.
UPDATE member SET status='휴면'
WHERE id = 'kyh';
COMMIT;

-- 등급 변경
UPDATE member SET grade=9
WHERE id = 'kcs';
COMMIT;

-- 5. 삭제 (없음 - 상태를 '정상'에서 '탈퇴'로 변경 : update)
UPDATE member SET status='탈퇴'
WHERE name ='홍길동';
COMMIT;

-- 6. 로그인 (2.보기) / 로그아웃 (DB 필요 없음)
-- id와 pw를 입력하면 DB로 로그인에 필요한 정보를 보낸다.
SELECT id, name, gender, birth, grade as gradeNo,
case when grade = 1 then '일반회원'
     when grade = 9 then '관리자'
     end as gradeName
FROM member
WHERE id = 'kcs' AND pw ='1111';
COMMIT;
-- 데이터가 NULL로 나오면 '아이디나 패스워드를 확인 해 주세요'

-- 7. 아이디 찾기 (2.보기)
-- 비밀번호 찾기 (2.보기 또는 4.수정 - JAvA에서 비밀번호를 만든다 -> DB 비밀번호를 수정한다 -> JAVA에서 수정한 데이터를 보여준다.)
-- 임시 비밀번호를 만들어서 메일로 보내주거나(나중에 사용자가 수정하게끔), 비밀번호 변경 링크를 메일로 보내준다.
SELECT id
FROM member
WHERE name = '김철수' AND tel = '010-5555-6666';
COMMIT;