[DB] 기초 SQL문

데이터베이스

-- 데이터베이스 생성
CREATE DATABASE IF NOT EXISTS <database_name>;

-- 삭제
DROP DATABASE <database_name>;

-- 목록 확인
SHOW DATABASES;

-- 수정
ALTER DATABASE IF EXISTS <database_name> [option];

-- 수정 옵션
CHARACTER SET = &lt;문자열집합 이름&gt;
COLLATE = &lt;콜레이션 이름&gt;
READ ONLY = &lt;1 | 0&gt;

테이블

-- 테이블 생성
CREATE TABLE IF NOT EXISTS <table_name>;

-- ex)
CREATE TABLE IF NOT EXISTS TMEMBER (
		FID			  VARCHAR(15)						    COMMENT '회원 ID',
    FNAME		  VARCHAR(4)		NOT NULL		COMMENT '회원 이름',
    FBIRTH		YEAR			    NOT NULL		COMMENT '생년',
    FPHONE		CHAR(11)		  NOT NULL		COMMENT '회원 연락처',
    FADDR		  VARCHAR(10)		NOT NULL		COMMENT '회원 주소',
    FREGDATE	DATE			    NOT NULL		COMMENT '회원 가입일',
    PRIMARY KEY(FID) --pk 지정
)

CREATE TABLE TORDER (
		FIDX		  int				 auto_increment	COMMENT '주문 일련번호',
    FMEM_ID		VARCHAR(20)		NOT  NULL		COMMENT 'fn_tmember_fmem_id',
    FGOODS		VARCHAR(10)		NOT NULL		COMMENT '구매 상품',
    FCOST		  MEDIUMINT 		NOT NULL		COMMENT '상품 단가',
    FNUM		  TINYINT			  NOT NULL		COMMENT '구매상품 수량',
    CONSTRAINT pk_torder_fidx PRIMARY KEY(FIDX), -- 고유성 보장되는 pk
    CONSTRAINT fk_tmember_fid_fmem_id
    FOREIGN KEY (FMEM_ID) REFERENCES TMEMBER (FID), -- 고유성 보장되는 fk
    ON UPDATE CASCADE	-- REFERENCES에 정의된 데이터 수정에 따라 함께 변경
    ON DELETE CASCADE,	-- REFERENCES에 정의된 데이터 수정에 따라 함께 삭제
)

제약조건

• NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT, UNSIGNED, AUTO_INCREMENT

--삭제
DROP TABLE <table_name>;

-- 목록 확인
SHOW TABLES;

-- 이름 수정
RENAME TABLE <table_name> TO <new_table_name>;


-- 수정
ALTER TABLE IF EXISTS <table_name> [option];

-- 수정 옵션
ADD <column_name> <data_type> [AFTER <column_name>];
DROP <column_name>;
CHANGE <column_name> <new_column_name> <data_type>;
MODIFY <column_name> <data_type>;
ADD PRIMARY KEY (<column_name>);
DROP PRIMARY KEY;
ADD CONSTRAINT <foreign_key_name> 
	FOREIGN KEY (<column_name>) REFERENCES <table_name> (<column_name>);
DROP FOREIGN KEY <foreign_key_name>;
ADD CONSTRAINT <unique_name> UNIQUE(<column_name>);
DROP INDEX <unique_name>;

인덱스

• 데이터 변경의 성능을 저하시키지만 검색 속도를 높여주는 자료구조
• ALTER 에서 ADD로 넣어도 됌

-- 보조 인덱스 생성 (중복 허용)
CREATE INDEX <index_name> ON <table_name> (<column_name>); 

-- 보조 인덱스 생성 (중복 비허용)
CREATE UNIQUE INDEX <index_name> ON <table_name> (<column_name>);

-- 검색 내용이 많은 텍스트 검색에 용이
CREATE FULLTEXT INDEX <index_name> ON <table_name> (<column_name>);

-- 생성한 인덱스 적용
ANALYZE TABLE <table_name>;

-- 삭제
DROP INDEX <index_name> ON <table_name>;
ALTER TABLE <table_name> DROP INDEX <index_name>;

사용자 관리

-- 사용자 추가
CREATE USER 'user_name'@'host' IDENTIFIED [WITH 비밀번호 암호화 선택] BY 'password';

-- ex)
CREATE USER 'lee'@'localhost' IDENTIFIED BY '1111';
CREATE USER 'lee'@'localhost' IDENTIFIED WITH cashing_sha2_password BY '1111';
CREATE USER 'lee'@'%' IDENTIFIED WITH mysql_native_password BY '1111';

-- 사용자 삭제
DROP USER 'user_name'@'host';

호스트

• localhost : mysql이 설치된 host에서 로그인한 사용자
• % : 모든 host의 접속 허용
• ip : 지정된 ip주소의 접속자만 허용

비밀번호 정책

• cashing_sha2_password : 보안성이 높고 mysql 8.0이후 기본 값
• mysql_native_password : 이전 버전과의 호환성이 필요한 경우 사용

사용자 권한

-- 권한 확인
SHOW GRANTS FOR 'user_name'@'host';

-- 권한 부여
GRANT &lt;권한목록&gt; ON <db_name>.<table_name> TO 'user_name'@'host'
[IDENTIFIED BY 'password'] [WITH GRANT OPTION];

-- 권한 제거
REVOKE &lt;제거할 권한 목록&gt; ON <db_name>.<table_name> FROM 'user_name'@'host';

• 권한 목록
  • SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, LOCK TABLES
• IDENTIFIED BY : 사용자 비밀번호 지정/변경
• WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 옵션