SQL_01(테이블, SQL(DDL, DML, DCL))

lsc99 2023. 8. 30. 18:48

테이블(Table) : 데이터 베이스에서 데이터를 저장하는 단위
▪ Entity
- 시스템이 독립적으로 관리하길 원하는 데이터
▪ Table
- Entity를 물리적 데이터베이스에 표현하는 방식 -> Entity를 구현
- 열(Column)과 행(Row, Record) 의 이차원 표 형식으로 관리한다.
▪ 열(Column, Attribute)
– 테이블에 보관되는 Data를 구성하는 속성
▪ 행(Row, Record)
– 테이블이 관리하는 하나의 Data

▪ 데이터베이스를 서버에서 운용해야 하는 이유
- 각자의 컴퓨터에 설치하게 되면 데이터의 공유가 불가능하기 때문에 한 곳에서 관리해야 여러 사용자가 데이터의 관리를 할 수 있기 때문이다.

기본 SQL(Structured Query Language)
SQL
▪ Structured Query Language
▪ 데이터베이스에 데이터를 질의, 등록, 수정, 삭제 등을 요청하기 위한 표준언어.
▪ DML(Data Manipulation Language) – INSERT, UPDATE, DELETE, SELECT
- 데이터 조작하는데 사용하는 언어.
- Table에 Data 삽입(추가), 수정, 삭제, 조회(선택)
- DML은 트랜잭션이 발생하여 SQL문은 임시로 적용되며 commit을 이용해 완전 적용시키거나 Rollback을 이용해 처리를 취소할 수 있다.
- 트랜잭션 : 프로그램의 시작과 끝의 단위, 끝마치면 commit, 마치지못하여 프로그램의 시작으로 되돌아가는 것은 Rollback
▪ DDL(Data Definition Language) – CREATE, ALTER, DROP, TRUNCATE
- Database 스키마(Database, Table, View, Index등의 object)를 생성, 수정, 삭제하는 언어.
- DDL은 트랜잭션이 발생하지 않아 명령어를 실행하면 Rollback 시킬 수 없다.
▪ DCL(Data Control Language) – GRANT, REVOKE
- 사용자에게 권한을 주거나 권한을 없애는 것과 같은 Data 접근을 제어하기 위한 언어.

DDL

Database 생성 및 조회(관리자 계정으로 로그인) -> 생성(CREATE DATABASE 이름) -> 조회(SHOW DATABASES) ->

사용(USE 이름)

사용자 계정 생성 및 권한 설정
▪ 사용자 계정 생성
– CREATE USER 계정명@HOST IDENTIFIED BY ‘PASSWORD’
▪ HOST
– localhost : LOCAL 접속 계정
– ‘%’ : 원격 접속 계정
– ex)
▪ CREATE USER scott@localhost IDENTIFIED BY 'tiger';
▪ CREATE USER scott@'%' IDENTIFIED BY 'tiger';

▪ 사용자 계정 삭제
– DROP USER 계정명@host
– ex)
▪ DROP USER scott@localhost;
▪ DROP USER scott@'%’;

▪ 권한 지정
– GRANT 권한 ON DB.TABLE TO 계정@host
– ex)

▪ GRANT ALL PRIVILEGES ON *.* TO scott@localhost;
▪ GRANT ALL PRIVILEGES ON *.* TO scott@'%';

▪ 생성된 계정조회
- USE (사용할 Database명)
- SELECT host, user FROM user;

-- 주석, sql문 실행 -> control + enter
/* block */
-- 사용자 계정 생성
-- local 접속 계정
create user 'playdata'@localhost identified by '1111';

-- 원격 접속 계정
create user 'playdata'@'%' identified by '1111';

-- 등록된 사용자계정들을 조회
select user, host from mysql.user;

-- 계정에 권한 부여
grant all privileges on *.* to 'playdata'@localhost;
grant all privileges on *.* to 'playdata'@'%';

▪ 테이블 생성
- SQL은 키워드나 테이블, 컬럼명의 경우 대소문자를 구분하지 않는다.

CREATE TABLE 테이블이름
( 컬럼이름 datatype [제약조건]
[,컬럼이름 datatype . . .]
[,제약조건]
)
▪ 테이블명, 컬럼명 규칙
– 영문자, 숫자, _ 만 가능하다.
– 첫 글자는 반드시 영문자여야 한다.
▪ CREATE TABLE DEPARTMENT(
DEPARTMENT_ID INT CONSTRAINT dept_pk_deptno PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(30),
LOCATION VARCHAR(100)
)
▪ 데이터베이스에 생성된 테이블들 조회
– show tables;

-- database 를 생성
create database testdb;   -- 이름 testdb 인 database 를 생성

-- database를 확인
show databases;

-- database 생성 및 삭제
create database mydb;
drop database mydb; -- 삭제

use testdb; -- testdb를 사용한다.
-- table을 생성 - 회원(member) 테이블을 생성.
-- 속성이름  데이터타입  제약조건

테이블 생성 - 데이터 타입
문자열 타입
- CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT 의 문자열 타입이 있다.
- 보통 CHAR, VARCHAR를 많이 사용한다. VARCHAR는 MAX LENGTH를 지정할 수 있다. ex) VARCHAR(50) : 최대 50글자까지 넣을 수 있다.
- 길이 생략하면 1 (1글자) 로 설정 된다.
- 고정 길이(CHAR) : 입력된 데이터의 글자 수가 모자라면 공백으로 채운다.
- 가변 길이(CHAR을 제외한 모든 것) : 최초 지정된 길이는 최대 크기이며 입력된 데이터의 글자수에 따라 저장 크기가 변경되는 타입.
- 문자열(char, varchar) 의 값은 작은 따옴표로 감싼다

Number 타입
- 많은 Number 타입이 있지만 INT(정수)를 기준으로 잡아서 INT의 범위(약 -21억 ~ 21억)을 넘어간다면 BIGINT를 사용한다.
- 실수의 경우 보통 DOUBLE을 사용한다(FLOAT보다 소수점 이하를 더 많이 표현할 수 있기 때문) 계산의 정확도가 필요하다면 DECIMAL 사용

날짜 타입
- DATE, TIME, DATETIME, TIMESTAMP, YEAR의 날짜 타입이 있다.
- 날짜를 표현하려면 DATE, 시간을 표현하려면 TIME, 둘다 표현하려면 DATETIME, System의 time zone을 변경하려면 TIMESTAMP를 사용한다.
- 결측치
- NULL : 없는 값, 모르는 값의 의미를 가진다.

테이블 생성 - 제약조건(key) : 컬럼(열)이 가질 수 있는 값에 대한 제약조건을 지정할 때 사용한다.
기본 구문
– 컬럼 설정 시 지정
– 컬럼 설정 다음에 따로 설정
▪ constraint 제약조건이름 제약조건 (컬럼) ex) CONSTRAINT pk_id PRIMARY KEY (id), ...

PRIMARY KEY(PK) : NULL을 값으로 가질수 없고(NOT NULL의 특징), 테이블의 모든 행이 다른 값을 가져야 한다(UNIQUE KEY의 특징). UNIQUE KEY(UK) + NOT NULL(NN) -> PRIMARY KEY(PK)
CHECK(CK) : 컬럼에 들어가는 값의 조건을 지정
AUTO_INCREMENT : 자동 증가 정수 컬럼(MY SQL에서 사용). 정수컬럼으로 값이 입력되면 1씩 증가하는 값을 가진다.
이외에 FOREIGN KEY(FK), DEFAULT 정도가 있다.

테이블 삭제
▪ 구문 : DROP TABLE 테이블이름
– 삭제 하면 되돌릴 수 없다.
– ex)
▪ DROP TABLE MEMBER;
▪ DROP TABLE IF EXISTS MEMBER; -> 조건을 넣은 경우
▪ 자식테이블에서 참조되고 있는 부모테이블을 삭제
– 자식테이블들을 먼저 삭제한다.
– Foreign key check 옵션을 끄고 해야 한다.
set foreign_key_checks = 0 --옵션 끄기 (1을 주면 옵션을 킨다.)

drop table member; -- table member를 삭제
create table member (
	id varchar(10)  primary key,
    password varchar(10) not null,
    name varchar(50) not null,
    point int default 1000,
    email varchar(100) unique key,
    gender char(1) check(gender in('m', 'f')),
    age int check(age > 0),
    join_date timestamp not null default current_timestamp 
);

-- 테이블을 확인
show tables;

-- 테이블의 속성(컬럼)들을 확인
desc member;

ALTER는 추후에 학습

DML : INSERT, DELETE, UPDATE, SELECT (SELECT는 조회의 의미를 가지기 때문에 DQL이라고도 한다.)

INSERT (데이터 삽입)

▪ INSERT 기본구문
INSERT INTO 테이블이름 (컬럼명, 컬럼명 [,….]) VALUES (값1, 값2 [,....])
– ex) INSERT INTO DEPTARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION) VALUES (100, ‘기획부’, ‘서울’)
– INSERT는 한 행(Row, 레코드) 씩 처리한다.
– 문자열의 경우 삽입할 값을 ‘ ’로 감싸준다.
– 날짜는 형태에 맞게 문자열로 넣어준다. ( 날짜: - 나 / 로 구분, 시간 : 로 구분) ex) '2023/08/03', '2023-08-05'
– 테이블의 모든 컬럼에 데이터를 넣을 경우 컬럼 항목은 생략할 수 있다. (테이블을 생성할 때 생성한 컬럼 순서대로 값이 들어간다.)
– 특정 컬럼에 값을 안 넣을 경우 INSERT할 컬럼 대상에서 제외하거나 null 키워드를 값에 넣는다.

-- member 테이블에 데이터를 insert
insert into member values('id-111', '1111', '홍길동', 3000, 'a@a.com', 'm', 20, '2010/2/3 10:20:30');

-- 일부컬럼의 값을 insert -> 생략할 수 있는 컬럼: nullable, default 값이 있는 컬럼, 둘 중에 하나라도 만족하면 생략 가능
insert into member (id, password, name) values ('id-222', '1212', '강감찬'); 

-- 제약조건 테스트
-- email : Unique key 제약조건.
insert into member (id, password, name, email) values ('id-333', '1111', '유관순', null); -- email의 Unique key 제약조건에 어긋난다. null은 Unique key 제약조건에서 벗어난다.(중복가능)

-- gender : check -> 'm', 'f', age : check 양수
insert into member (id, password, name, gender, age) values ('id-555', '1111', '유재석', 'a', 30);

-- insert한 데이터들 확인
select * from member;