[ORACLE]USSOFT ORACLEトレーニング初日

TABLEの作成

CREATE TABLE STUDENTS(
	NO_SEQ NUMBER(11),
	DS_NAME VARCHAR2(100),
	NO_SEX NUMBER(1),
	NO_AGE NUMBER(10),
	NO_GRADE NUMBER(1),
	NO_CLASS NUMBER(1),
	PRIMARY KEY(NO_SEQ)
);

SEQUENCE TABLEの作成

CREATE SEQUENCE STUDENTS_SEQ;

INSERT構文の実行

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '이현식', 0, 15, 2, 1);

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '김영서', 0, 30, 2, 1);

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '박지수', 0, 27, 2, 1);

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '김예지', 0, 29, 1, 1);

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '이혜수', 0, 23, 3, 1);

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '채지원', 0, 24, 2, 1);

すべてのクエリー

SELECT * FROM STUDENTS;

UNIQUE INDEXの作成

CREATE UNIQUE INDEX IDX_STUDENTS ON STUDENTS(NO_GRADE, NO_CLASS, DS_NAME, NO_SEX, NO_AGE);

📌UNIQUE INDEXとは?

INSERT INTO STUDENTS(NO_SEQ, DS_NAME, NO_SEX, NO_AGE, NO_GRADE, NO_CLASS)
VALUES(STUDENTS_SEQ.NEXTVAL, '이현식', 0, 15, 2, 3);

DMLの後ろでDDLを実行すると自動的にコミットされます

insert into hs_students (no_seq, ds_name, no_sex, no_age, no_grade, no_class)
values (STUDNETS_SEQ.NEXTVAL, '박지수', 0, 15, 2, 1);

CREATE TABLE TEMP_STUDENT2 AS
SELECT * FROM students;

一時TABLEの作成(コピーTABLE)

CREATE TABLE TEMP_STUDENTS AS 
	SELECT * FROM STUDENTS s;

📌DDL、DML、DCLとは?

DDL

DML

DCL

ORDER BYソートの使用

SELECT NO_SEQ, DS_NAME, NO_SEX FROM STUDENTS WHERE NO_CLASS=1 ORDER BY NO_SEQ;

同じ結果値を持つクエリは、効率が異なりますか?

select NO_SEQ, DS_NAME, CASE NO_SEX 
    WHEN 0 THEN '남자' 
    WHEN 1 THEN '여자'
    ELSE ''
    end ds_sex
    FROM students
    where no_class = 1
    order by no_seq;

SELECT NO_SEQ, DS_NAME,
    CASE WHEN NO_SEX = 0 THEN '남자'
         WHEN NO_SEX = 1 THEN '여자'
         ELSE ''
         END DS_SEX
         FROM students
         where no_class= 1
         order by no_seq;

SELECT NO_SEQ,
       DS_NAME,
       DECODE(NO_SEX, 0, '남자', 1, '여자', '') DS_SEX
       FROM students
       where no_class = 1
       order by no_seq;

SELECT NO_SEQ,
       DS_NAME,
       DECODE(NO_SEX, 0, '남자', 1, '여자', '') DS_SEX
       FROM students A,
       (
        SELECT 0 CD_SEX, '남자' DS_SEX FROM DUAL UNION ALL
        SELECT 1 CD_SEQ, '여자' DS_SEX FROM DUAL
        )B
        WHERE A.NO_CLASS = 1
        AND A.NO_SEX = B.CD_SEX
        ORDER BY A.NO_SEQ;

SELECT NO_SEQ,
        DS_NAME,
        (
            SELECT DS_SEX
            FROM(
            SELECT 0 CD_SEX, '남자' DS_SEX FROM DUAL UNION ALL
            SELECT 1 CD_SEQ, '여자' DS_SEX FROM DUAL
            )
            WHERE CD_SEX = STUDENTS.NO_SEX
            ) DS_SEX
            FROM students
            WHERE NO_CLASS = 1
            ORDER BY NO_SEQ;

SELECT NO_SEQ,
       DS_NAME,
       DECODE(NO_SEX, 0, '남자', 1, '여자', '') DS_SEX
       FROM students A,
       (
        SELECT 0 CD_SEX, '남자' DS_SEX FROM DUAL UNION ALL
        SELECT 1 CD_SEQ, '여자' DS_SEX FROM DUAL
        )B
        WHERE A.NO_CLASS = 1
        AND A.NO_SEX = B.CD_SEX
        ORDER BY A.NO_SEQ;

SELECT A.NO_SEQ,
    A.DS_NAME,
    A.DS_SEX,
    FROM students A,
    codes b
    where a.no_class =1
    and a.no_sex = b.cd_sex
    order by a.no_seq;