Database | SQL

SQL (Structured Query Language)

- 데이터를 조작 및 정의, 액세스 제어를 제공하기 위해 관계형 데이터베이스에서 사용되는 구조화된 질의 언어

---

종류

1. 데이터 정의어 (DDL, Data Definition Language)

- DB의 구조를 정의하거나 데이터를 생성, 변경, 삭제하기 위한 언어로, DB 관리자나 설계자가 주로 사용
- CREATE(생성), ATLER(수정), DROP(삭제), TRUNCATE(초기화), RENAME(이름 변경)

예시)

테이블 생성 : CREATE TABLE <테이블명> (<컬럼명><자료형>[옵션][제약조건],..);
DB 조회 : SHOW DATABASES;
테이블 조회 : SHOW TABLES; / DESC; // 테이블 구조 확인
테이블 컬럼 추가 : ALTER TABLE <테이블명> ADD <컬럼><자료형>[옵션][제약조건][위치];
위치 옵션 : FRIST = 가장 위에 추가 / AFTER = 해당 컬럼 뒤에 추가
테이블 컬럼 수정 : ALTER TABLE <테이블명> MODIFY <컬럼> <자료형> [옵션] [제약조건] ;
컬럼의 속성 값 수정하기(전체를 바꾸는 것이 아닌 일부만 바꾸기)
테이블 컬럼 교체 : ALTER TABLE <테이블명> CHANGE <기존컬럼> <새컬럼> <자료형> [옵션] [제약조건] ;
컬럼 내용 모두 변경 (조건을 걸지 않으면 테이블 전체가 바뀔 수 있으니 주의)
테이블 컬럼 삭제 : ALTER TABLE <테이블명> DROP <컬러명>;
테이블 이름 변경 : ALTER TABLE <기존 테이블명> RENAME <변경할 테이블명>; RENAME TABLE <기존 테이블명> TO <변경할 테이블>;
테이블 초기화 : TRUNCATE <테이블명>;
테이블 삭제 : DROP TABLE <테이블명>;

 

 

2. 데이터 조작어 (DML, Data Manipulation Language)

- 데이터베이스에 입력된 레코드를 조회하거나 조작하기 위해 사용하는 언어 (삽입, 수정, 삭제 등)
- INSERT(삽입), UPDATE(수정), DELETE(삭제), SELECT(조회)

예시)

데이터 삽입 : INSERT INTO <테이블> VALUES (데이터);
                         INSERT INTO <테이블> (컬럼명,..) VALUES (데이터,...); // 각 순서와 개수가 일치 해야 한다.
                         INSERT INTO <테이블> SET <컬럼명> = '<데이터>' ,...; // 특정 컬럼을 지정해서 추가할 때
데이터 조회 : SELECT * FROM <테이블>; / 테이블 내 모든 자료 조회
                         SELECT <컬럼> FROM <테이블>; / 보고싶은 컬럼의 자료만 조회
                         SELECT <컬럼> FROM <테이블> WHERE <조건> ; / 조건에 해당하는 자료 조회
※ 조건 검색시 AND가 ORD보다 우선순위가 높으며 연산시 우선순위를 고려 가장먼저 처리할때는 ( )로
  % = 자리 수 제한 없이 모든 문자,  _ = 임의의 한 자리 수
                         SELECT * FROM <테이블> LIMIT [개수]; / 위에서 부터 해당 개수만큼의 자료 조회
                         SELECT * FROM <테이블> LIMIT [개수] OFFSET [위치]; 
                         SELECT * FROM <테이블> LIMIT [위치],[개수]; 해당 위치 부터 지정한 개수 만큼 표시
                         SELECT * FROM <테이블> ORDER BY [기준 컬럼][DESC : 내림차, ASC : 오름차]; / 정렬해서 조회
                         SELECT DISTINCT * FROM <테이블>; / 중복 값을 제외하고 조회
                         SELECT * FROM <테이블> GROUP BY [컬럼]; / 해당 컬럼을 기준으로 그룹화를 시킨다.
※ COUNT(*) = 숫자 카운트, SUM(컬럼) = 합 구하기, AVG(컬럼) = 평균 구하기 // 집계 함수
      HAVING = GROUP BY에서 사용하는 조건문 (WHERE에서는 집계 함수 사용 불가)
데이터 수정 : UPDATE <테이블> SET <컬럼> = <수정데이터> [조건];  / 데이터 변경
데이터 삭제 : DELETE FROM <테이블> [조건] ; / 조건 없으면 자료 전체 삭제되니 주의

3. 데이터 제어어 (DCL, Data Control Language)

- DB에 대한 접근 관리, 보안, 무결성, 복구 등 DBMS를 제어하기 위한 언어
- GRANT(권한부여), REVOKE(권한삭제) 
- COMMIT(실행), ROLLBACK(되돌리기), SAVEPOINT(저장점 지정) // TCL (TRANSECTION CONTROL LANGUAGE)

예시)

DESC MYSQL.USER // DBMS계정 정보, <DB명>.<TB명> = 다른 DB의 테이블 조회
SELECT USER,HOST,PASSWORD FROM MYSQL.USER; // 계정테이블 조회
※ LOCALHOST = DBMS에 설치된 로컬 호스트, % = 모든 네트워크 , IP+% = 해당 IP의 영역 , IP = 해당 IP
CREATE USER 'USER명'@'접속경로' [IDENTIFIED BY '패스워드']; // 패스워드를 지정 후 계정 생성
※ IDENTIFIED BY를 생략시 패스워드 없는 계정이 생성된다.
INSERT INTO MYSQL.USER(USER,HOST,PASSWORD) VALUES (계정명, 경로, PASSWORD(비밀번호)); / 계정 추가
UPDATE MYSQL.USER SET PASSWORD = PASSWORD('비밀번호') WHERE USER = '계정명'; 특정 계정 비밀번호 변경
DROP USER '계정명'[@'경로']; // 계정 삭제 / 경로 생략시 % 자동 적용
FLUSH PRIVILEGES; // 계정 정보 갱신 (데이터 제어어 사용 시엔 반드시 적용)
SHOW GRANTS [FOR '계정명'@'경로']; // 계정의 권한 확인하기
GRANT PRIVILEGES[(컬럼)] ON ITEM(*.*) TO '계정명'@'경로' [IDENTIFIED BY PASSWORD '비밀번호'][WITH GRANT] // 계정에 권한 추가하기
PRIVILEGES[(컬럼)] = 설정할 권한
1. ALL PRIVILEGES = 모든 권한
2. USAGE = 접속권한을 제외한 아무 권한도 주지 않는다.
ITEM = 권한을 설정할 객체 / <DB>.<TB>
IDENTIFIED BY PASSWORD '비밀번호' = 권한 부여 시 비밀번호 설정
WITH GRANT OPTION = 다른 계정의 권한도 변경할 수 있는 권한을 부여 [생략가능]
REVOKE PRIVILEGES[(컬럼)] ON ITEM FROM '계정'@'경로'; / 권한 삭제
외래키 생성 : CREATE TABLE <테이블명> (
<컬럼명><자료형>[옵션][제약조건],
CONSTRAINT `제약설정` // ''와 ``를 주의해서 보자
FOREIGN KEY (`외래키 필드`)
REFERENCES `참조 테이블` (`참조 필드명`)
ON DELETE [옵션]
ON UPDATE [옵션]
);
CASCADE = 부모 데이터 삭제 / 변경 시 자식 데이터도 함께 적용
SET NULL = 부모 데이터 삭제 시 자식 데이터에 NULL 값 적용
SET DEFAULT = 부모 데이터 삭제 시 자식 데이터 기본 값 적용
RESTRICT = 부모 테이블에 데이터가 남아 있을 시 자식 테이블 데이터 삭제 / 수정 불가
ALTER TABLE <테이블> DROP FOREIGN KEY `[제약설정]` ; // 외래키 삭제
ALTER TABLE <테이블> ADD // 테이블이 있는 상태에서 외래키 추가