SQL_05

functionMan·2024년 2월 1일
mysqlsql

SQL

목록 보기
5/5
post-thumbnail

1. 데이터베이스 생성

  • 데이터베이스 생성
    CREATE DATABASE 데이터베이스명;
    | 관리자 계정으로 생성가능

DDL DDL Data Definition Language : 데이터 정의어

  • 데이터베이스 객체(테이블,뷰, 인덱스)의 구조정의
    CREATE : 생성
ALTER : 변경
DROP : 삭제
RENAME : 객체 이름 변경
TRUNCATE : 삭제(저장공간까지 삭제, 데이터 테이블 초기화)

2. DDL-CREATE 테이블 생성

  • 객체(테이블,뷰...)를 생성할때 사용

  • 구조

    CREATE TABLE [IF NOT EXISTS] 테이블명 (
{ 컬럼명 데이터타입 [제약조건], }
[PRIMARY KEY 컬럼명]
[[CONSTRAINT 외래키명] FOREIGN KEY 컬럼명 REFERENCES 테이블명(컬럼명)
       [ON DELETE CASCADE|SET NULL]
   ]
);
{} : 반복가능 
[] : 선택적 사용 
| :선택
    • { 컬럼명 ... } : 테이블을 이루는 컬럼 지정, 테이블에 최소 1개 이상 필요
    • 데이터타입 : SQL01 - 5. MySQL 데이터 타입 참고
    • 제약조건
      • NULL : NULL 값 허용
      • NOT NULL : NULL 값 허용 X
      • UNIQUE : 컬럼내에서 유일한 값만 저장 가능
      • DEFAULT 값 : 기본값 설정
      • CHECK 체크 조건 : 값에 대한 조건 부여
      • PRIMARY KEY : 기본키 지정
      • FOREIGN KEY : 외래키 지정
      • AUTO_INCEREMENT : 해당 컬럼에 값을 1부터 시작하여 새로운 레코드가 추가될때마다 1씩 증가된 값을 저장(자동 넘버링)

  • PRIMARY KEY 기본키

    • NOT NULL + UNIQUE 특징을 가지며, 테이블 내에서 데이터들끼리의 유일성을 보장하는 컬럼에 설정, 테이블당 한개만 지정가능

  • FOREIGN KEY 외래키
    - 두개의 테이블을 서로 참조도록 설정한, 현재 테이블에 있는 다른 테이블의 PRIMARY KEY를 현재 테이블의 FOREIGN KEY라 한다

    - 위 테이블에서, child 인 student 테이블에 데이터가 입력될때, 오른쪽 professor테이블 의 profno값을 먼저 확인하게 된다. 확인결과 사용자가 입력한 student의 profno값이 professor의 profno 컬럼에 존재하는 값이면, student 테이블에 입력을 허용하고, 만약 존재하지 않을 경우 에러 발생시키고 입력을 거부함.
    - Foreign Key 설정 시 주요 사항 : 부모 테이블에 데이터가 10억건이 있다고 가정하면, 자 식테이블에 데이터 1건 입력하기 위해, 부모테이블에 있는 10억건을 전부 읽어서 검사한 후에 작업을 할 수 있다는 뜻. 부모 테이블에 있는 데이터를 변경하거나 삭제하기 전에, 자 식 테이블에가서 해당 데이터 유무를 찾아본 후, 자식 테이블에 해당 데이터가 없으면 데 이터를 변경하게 됨. -> 비효율적인 상황 발생
    이를 해결하기 위해 참조키 제약조건을 설정할 때는 자식 테이블과 부모테이블의 해당 컬 럼에 반드시 적절한 인덱스를 생성해 주어야함.
    즉, 자식 테이블 컬럼과 부모테이블 컬럼 모두 적절한 인덱스가 생성되어 있어야, 참조키 제약조건 성능이 좋아짐.

  • 테이블 조회

    SHOW TABLES; -- 태이블 목록
SHOW TABLE STATUS; -- 목록 자세히
DESC 테이블명; -- 테이블 구성도

3. DDL-ALTER 테이블 수정

  • 생성된 테이블의 속성, 제약조건 변경

  • 생성된 테이블의 기본키, 외래키 변경

  • 테이블명 변경 ALTER - RENAME

    ALTER TABLE 테이블명 RENAME 새테이블명;

  • 컬럼 추가 ALTER - ADD

    ALTER TABLE 테이블명 ADD 새컬럼명 데이터타입 [제약조건] [ AFTER 기존컬럼명 ];

    -> AFTER 기존컬럼명 : 기존 존재하는 특정 컬럼명 뒤에 일치하도록 지정
    -> 테이블에 이미 데이터가 있을 경우 NOT NULL 옵션을 줄 수 없다. DEAFAULT 가능

  • 컬럼 수정 ALTER - CHANGE

    ALTER TABLE 테이블명 CHANGE 구컬럼명 TO 새컬럼명 데이터타입;

  • 컬럼 타입만 변경 ALTER-MODIFY

    ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [제약조건];
    • 해당 컬럼의 크기를 늘릴 수는 있지만, 줄이지는 못함. 기존 저장된 데이터 훼손 우려.
    • 해당 컬럼이 NULL값만 가지고 있거나, 테이블에 데이터가 없으면 컬럼의 크기를 줄이 수 있다.
    • 해당 컬럼이 NULL 값만 가지고 있으면 데이터타입 변경 가능
    • 해당 컬럼의 DEFAULT 값을 변경하면, 변경 작업 이후 발생하는 레코드에만 영향을 미친다.
    • 해당 컬럼의 NULL 값이 없을 경우에만 NOT NULL 제약 조건 추가 가능

  • 제약 조건 추가 ALTER-ADD CONSTRAINT

    • 기본키 추가

      ALTER TABLE 테이블명 ADD CONSTRAINT [제약조건명] PRIMARY KEY(컬럼명);

    • 외래키 추가

      ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) 
REFERENCES 참조테이블명(참조컬럼명);

      | FOREIGN KEY 설정시, 참조테이블의 참조컬럼이 PRIMARY KEY 또는 UNIQUE 이여햐 함.

    • 그 외 제약조건 추가

      ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건(컬럼명);

    • 제약조건 추가 두가지 방법

      • 테이블 생성 시 추가
      • 테이블 생성 후 추가
        • 테이블 생성시에는 설정하지 않고, 추후 운영중 필요성이 생겨 추가해야 할 경우

  • 제약조건 삭제 ALTER - DROP - KEY

    • 기본키 삭제
      ALTER TABLE 테이블명 DROP PRIMARY KEY;
    • 외래키 삭제
      ALTER TABLE 테이블명 DROP FOREIGN KEY 외래키명;
      • 외래키 생성 시, INDEX 자동 생성
      • 외래키 삭제 시, INDEX 삭제 X -> 인덱스 직접 삭제 처리
        ALTER TABLE 테이블명 DROP INDEX 인덱스명; <-인덱스명은 외래키명과 동일함
    • 그 외 제약조건 삭제
      ALTER TABLE 테이블명 DROP CONSTAINT 제약조건명;

4. DDL-DROP 테이블 삭제

  • 테이블 삭제 후, roll back 불가능
  • 테이블이 사용하던 저장 공간 반납
    DROP TABLE 테이블명;

5. DML-INSERT 데이터 추가

  • 테이블에 새로운 데이터(레코드, 행) 추가
  • 컬럼명 작성
    INSERT INTO 테이블(컬럼명, 컬럼명,...)VALUES(값, 값,...);
    • 원하는 컬럼명과 그에 부합되는 값만 작성가능(NOT NULL 제약조건은 값대입 필수)
    • 컬럼명를 생략한 컬럼에 DEFAULT가 지정되어 있으면 DEFAULT 값으로 저장됨.
    • 누락된 컬럼은 경우에 따라 NULL값이 될 수도 있다.
    • 컬럼명은 원하는 순서대로 작성해도 되나, 값을 적을때 앞의 컬럼명과 일치되는 순으로 작성.
  • 컬럼명 생략
    INSERT INTO 테이블 VALUES(값, 값,...);
    • 컬럼명 생략 시에는 VALUES의 값들을 테이블 생성시 지정한 컬럼 순서대로 작성.
    • VALUES안에 모든 데이터값을 작성할 때 컬럼명을 생략 가능
    • DEFAULT 적용 안됨.

6. DML-UPDATE 데이터 수정

  • 테이블안에 저장된 데이터를 수정
    UPDATE 테이블명 SET 컬럼=새값, 컬럼=새값,... [WHERE 조건];
    • 주의 : WHERE 절이 없으면 컬럼의 모든 값이 지정한 값으로 일괄 변경됨
      WHERE절로 특정 레코드의 컬럼값을 변경하도록 지정

7. DML-DELETE데이터 삭제 / DDL-TRUNCATE 데이터 완전 삭제(테이블 초기화)

  • 두가지 명령 모두 테이블안의 데이터를 삭제하는 기능
  • 테이블 구조 자체는 유지
  • 차이점
    • DELETE
      • DML
      • 데이터 삭제
      • 부분 삭제 가능
      • ROLL BACK 가능 (휴지통 개념) 저장공간 유지
      • 인덱스 유지 AUTO_INCEREMENT 유지
    • TRUNCATE
      • DDL
      • 데이터 전체 삭제만 가능
      • ROLL BACK 불가능
      • 저장공간 반납
      • 인덱스 삭제
      • AUTO_INCREMENT 초기화
  • DELETE 구조
    • 레코드 전체 삭제
      DELETE FROM 테이블명;
    • 레코드 부분 삭제
      DELETE FROM 테이블명 WHERE 조건;
  • TRUNCATE 구조
    TRUNCATE TABLE 테이블명;

8. 자바에서 SQL 사용

  • 자바에서 MySQL Database에 접속하려면, 라이브러리 필요
    • JDBC Java DataBase Connectivity
    • DataBase 드라이버 : DB 벤더사에서 제공
  • MySQL 버전에 맞는 드라이버 다운받기
  • MySQL 드라이버를 자바 프로젝트에 배치 (이클립스)
    • 프로젝트 폴더선택, 우클릭 > Properties > Java Build Path > Libraries탭 >
      'module path' 선택 > Add External Jars.... > 다운받은 jar파일 선택 > Apply and Close!
    • 왼쪽 프로젝트 파일 목록에 'Referenced Libraries' 폴더와 그안에 mysql-connector-j 배치된 것 눈으로 확인
profile
functionMan
functionMan
이전 포스트

SQL_04

0개의 댓글