DDL (CREATE, DROP, ALTER)
📝 DDL(Data Definition Language) : 데이터 정의어. SCHEMA, DOMAIN, TABLE, VIEW, INDEX와 같은 데이터베이스 객체를 정의하는 명령어로, DB관리자 및 설계자가 주로 사용한다.
I. DB 관련
👀 생성된 데이터베이스들 보기
SHOW DATABASES;✨ 데이터베이스 생성하기
CREATE DATABASE db이름;👉 사용할 데이터베이스 선택하기
USE db이름;❌ 데이터베이스 삭제하기
DROP DATABASE db이름;♻ 데이터베이스 전체적 특성 수정하기
- 참고로 이러한 특성들은 db.opt라는 파일에 저장이 됨.
ALTER DATABASE db이름 바꿀것들;
e.g.,
ALTER DATABASE db이름
CHANGE COLLUMN `id` `id` INT NOT NULL ,
CHARSET= ⋯ ,
COLLATE= ⋯ ,
ADD PRIMARY KEY (`id`) 등등
II. TABLE 관련
✨ 테이블 생성하기
CREATE TABLE `userinfo` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT,
`name` char(4) NOT NULL,
`gender` enum('Male','Female') NOT NULL,
`address` varchar(50) NOT NULL,
`birthday` datetime NOT NULL,
`salary` numeric(8, 2) NOT NULL, ### 정수 8자리, 소수점 2자리
PRIMARY KEY(id), /* id를 primary key로 설정 */
CONSTRAINT myfirst FOREIGN KEY(salary) REFERENCES department(pID),
/* userinfo의 salary컬럼이 department의 pID컬럼 참조 */
CHECK(gender in ('Male','Female'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;제약조건에 대해서는 Chapter 9를 참고하자
- NOT NULL : 컬럼에 값이 반드시 존재해야함을 명시. 만약 값이 없다면 오류출력. (참고로 primary key는 반드시 NOT NULL이어야 한다. 그래야만 각 row를 식별할 수 있기 때문이다.)
- PRIMARY KEY(기본키) : 테이블에서 하나의 row를 고유하게 식별할 수 있도록 해주는 column
- 종류 : Natural Key, Surrogate Key
- Natural Key : 실제 어떤 개체가 갖고있는 속성을 나타내는 컬럼이 primary key가 됐을 때 이를 Natural Key라고 한다. ex) 사람은 주민등록번호로 특정 인물을 식별할 수 있다 또한, 쇼핑몰에서 각 회원을 식별할 수 있는 실제 속성인 휴대전화번호도 Natural key가 될 수 있다
- Surrogate Key : 위에서 설정했던 id column같은 primary key를 의미한다. 사실 id column은 어떤 회원의 속성을 직접적으로 나타내는 컬럼이 아니라 primary key로 사용하기 위해 인위적으로 생성한 column이다. 이렇게 어떤 개체의 실제 속성은 아니지만 primary key로 쓰기 위해 추가한 컬럼을 Surrogate Key라고 한다. id 컬럼의 값으로 특정 row를 식별할 수 있기 위해 보통 AUTO_INCREMENT를 사용한다.
- 종류 : Natural Key, Surrogate Key
- FOREIGN KEY : Chapter 3. 관계형 데이터모델의 1.6 참조무결성제약 부분을 참고하자 !
- CHARSET : 문자를 설정. 대표적으로 utf-8, euckr이 있다.
- COLLATE : db에서 검색이나 정렬같은 작업을 할 때 사용하는 문자열 비교를 위한 규칙의 집합
- 대표적으로 utf8_bin, utf8_general_ci(가장많이 사용), euckr_bin, euckr_korean_ci가 있다.(참고 : ci란 대소문자 구분을 하지 않는다는 뜻.)
- ci 는 대소문자 구분을 하지 않는데, 일반적으로 우리는 db관리자가 아니므로 원하는 문자열을 조회할 때 반드시 대소문자 구분없이 문자열을 조회할 수 밖에 없나? 라는 의문이 들지도 모른다. 하지만 문자열 조회시 BINARY 키워드를 사용하면 대/소문자를 구별해서 조회해주므로 걱정할 필요가 없다. ex) SELECT * FROM test WHERE column LIKE BINARY '%g%' 는 소문자 g만 포함된 문자열을 찾는 구문이다. 즉, 대문자G는 검색하지 않는다.
👀 db내의 모든 table 보기
SHOW tables;👁 테이블 구조 보기
DESC table이름;🔃 해당 테이블의 스키마 변경하기
### 테이블 컬럼 추가
ALTER TABLE `student` ADD height int;
ALTER TABLE `Member` ADD phone varchar(100);
ALTER TABLE `student` ADD
CONSTRAINT myConst FOREIGN KEY(deptName)
REFERENCES DEPARTMENT;
### 테이블 컬럼 삭제
ALTER TABLE `Member` DROP COLUMN;
### 테이블 열 이름 수정
ALTER TABLE `Member` CHANGE COLUMN passwd password varchar(50);
### 테이블 자체 이름 변경
RENAME TABLE `Member` TO Student;❌ 해당 테이블 삭제하기
DROP TABLE IF EXISTS table이름;DML (INSERT, SELECT, UPDATE, DELETE)
📝 DML(Data Manipulation Language) : 데이터조작어. 데이터의 Create(생성), Read(읽기), Update(갱신), Delete(삭제)할 때 사용하는 명령어. (CRUD) 사용자가 저장된 데이터를 실질적으로 처리할때 사용한다.
I. 테이블에 데이터 저장 (INSERT)
# 한 행만 추가
INSERT INTO `table이름` VALUES (2, '박재숙', '남자', '서울', '1985-10-26 00:00:00');
# 여러 행 추가
INSERT INTO `table이름` (`컬럼1`, `컬럼2`, `컬럼3`, `컬럼4`, `컬럼5`) VALUES
(1, 'codeit@naver.com', 27, 'm', '2019-03-26'),
(2, 'korin02@google.com',42,'f', '2021-08-14'),
(3, 'king423@daum.net',30,'f', '2011-11-03'),
(4, 'iwanthack@tistory.com',34,'m', '2018-05-22'),
(5, 'seyeop03@ssu.ac.kr',26,'m', '2014-01-10');
/* 참고로 csv파일이란 모든 값들이 콤마(,)로 구분된 형식의 내용을 가진 파일을 말하는데,
Comma Seperated Values의 약자이다. 이미 만들어진 데이터들을 콤마로 구분해놓은 것이다 */II. 테이블에 데이터 변경(UPDATE)
### 일반적으로 UPDATE문은 다음과 같은 형식으로 자주 사용한다.
UPDATE `table명` SET column명=값 WHERE 조건;
### CASE ~ END 분기에 따라 professor 봉급을 변경
UPDATE `professor` SET salary = CASE
WHEN salary<=7000 THEN salary*1.5
ELSE salary*1.3
END;
### ?????????????????????
UPDATE `student` SET totalCredit =
(SELECT sum(credit) FROM takes
natural join course
where student.sID=takes.sID and grade<>'F' and grade is NOT NULL
);III. 테이블 내 데이터 삭제(DELETE)
### 부서명이 EE인 교수 테이블 삭제
DELETE FROM professor WHERE deptName='EE';IV. 테이블의 데이터를 조회(SELECT)
0x01 기본적인 SELECT 구문
### 각 row의 모든 column들의 값을 조회
SELECT * FROM table이름;
### 특정 컬럼의 값들만 조회
SELECT 컬럼1, 컬럼2 FROM table이름;편의를 위해 앞으로는 table이름과 column명을 임의로 지어서 작성함.
0x02 WHERE절 사용
/* where 구문 사용하여 조회 */
### 특정 문자열을 만족하는 데이터 조회
SELECT * FROM table이름 WHERE email = 'seyeop03@ssu.ac.kr';
### 특정 정수 범위의 데이터 조회
SELECT * FROM table이름 WHERE age >= 27; # 27살 이상
SELECT * FROM table이름 WHERE age IN (27,30) # 27살과 30살만 조회
SELECT * FROM table이름 WHERE age BETWEEN 30 AND 39; # 30~39살
SELECT * FROM table이름 WHERE age NOT BETWEEN 30 AND 39; # 30~39살 아닌 놈
SELECT * FROM table이름 WHERE gender <> 'm' # <>는 !=와 완전히 동일함.
SELECT * FROM table이름 WHERE sign_up_day > '2019-01-20'; # DATE type도 가능0x03 LIKE 제한자 사용
- 위와 같은 경우 특정 문자열이 포함된 데이터를 조회하는 것이 불가능 하다. 위의 =, !=, >, <,<> <=, >= 연산자는 정수 범위 혹은 특정한 값이 딱 맞아야 조회가 가능하기 때문이다. 이러한 문제를 해결하는데에 LIKE가 쓰인다. (=와 달리 LIKE는 패턴매칭이 가능하기 때문)
/* LIKE 구문을 이용한 조회 */
### 맨 첫번째에 "서울"이라는 문자열이 포함된 address 컬럼의 값을 조회
SELECT * FROM table이름 WHERE address LIKE '서울%';
### address 컬럼의 데이터 중 "고양시" 라는 문자열이 들어가기만 하면 조회
SELECT * FROM table이름 WHERE address LIKE '%고양시%';
### email 컬럼의 데이터 중 k로 시작하면서 나머지 뒷부분이 6글자인 데이터 조회
SELECT * FROM table이름 WHERE email LIKE 'k______@%';
// 언더바(_)는 임의의 문자 한개를 나타낸다.0x04 여러 개의 조건 달기 (AND, OR)
/* AND로 조건 달기 */
### 서울에 사는 남성 조회
SELECT * FROM member WHERE gender='m' AND address LIKE '서울%';
### 서울에 사는 25세~29세까지의 남성 조회
SELECT * FROM member WHERE gender='m'
AND address LIKE '서울%'
AND age BETWEEN 25 AND 29;
/* OR로 조건 달기 */
### 3월~5월(봄) 혹은 9월~11월(가을)에 가입한 회원 조회
SELECT * FROM member WHERE MONTH(sign_up_day) BETWEEN 3 AND 5
OR MONTH(sign_up_day) BETWEEN 9 AND 11;
/* AND, OR 섞어서 쓰기 */
### 키 180이상의 남성 혹은 키 170이상의 여성 조회
SELECT * FROM member WHERE (gender = 'm' AND height >= 180)
OR (gender = 'f' AND height >= 170);📌 주의사항 !
1.select * from member where id=1 or 2와 같이 적으면 or 뒤의 값은 항상 TRUE이 되어 모든 컬럼이 출력되므로id=1 or id=2와 같이 적어주어야 한다.
2. AND는 OR보다 우선순위가 높기때문에 select * from member where gender='f' OR age<30 AND height>180 에서 굵게 표시된 조건을 먼저 검사한다.
0x05 정렬해서 보기 (ORDER BY)
- SQL에서 정렬한다는 의미는 row들을 특정 컬럼을 기준으로 순서대로 출력한다는 뜻이다.
- 참고로 ORDER BY는 반드시 WHERE절 뒤에 써주어야 하는데, SQL의 정해진 규칙이므로 꼭 지켜주어야 한다. 아니면 오류가 난다.
/* ORDER BY로 정렬하기 */
### 모든 row에 대해 키 오름차순으로 정렬하기 (ASC는 ORDER BY의 default이므로 생략가능)
SELECT * FROM member ORDER BY height ASC;
### 모든 row에 대해 키 내림차순으로 정렬하기
SELECT * FROM member ORDER BY height DESC;
### ONLY 70kg이상의 남성인 row에 대해서 키 오름차순으로 정렬하기
SELECT * FROM member WHERE gendeer='m' AND weight >= 70 ORDER BY height;
/* ORDER BY 정렬 기준을 여러개 두기 */
### 년도 기준 내림차순으로 정렬하고, 년도가 같다면 email 기준 오름차순으로 정렬하기
SELECT * FROM member ORDER BY YEAR(sign_up_day) DESC, email ASC;
// 이름을 먼저 쓴 컬럼을 우선으로 해서 정렬이 차례대로 수행된다.!참고사항 : 자료형이 TEXT인 문자열 비교는 첫번째 문자를 비교를 하고 같으면 그 다음번째의 문자를 계속 비교하는 식이다. 예를들어 '120'과 '97'이라는 문자열이 있으면 1과 9를 비교하여 9가 더 크므로 결과적으로 97이 더 크다는 결과가 출력된다.
근데 위 '120' '90'이라는 문자열들을 정수형으로 보는 방법은 없을까? 에 대한 해결책을 만들어 놓은 것이 CAST() 함수이다. CAST()함수는 자료형 캐스팅 함수이며, 위에 대한 해결을 위해 CAST(컬럼명 AS signed)처럼 사용하면 문자열 컬럼일 경우 signed 정수형으로 변환해준다. 만약 문자열 타입으로 저장된 숫자값에 소수점이 포함되어 있다면 decimal을 사용하면 된다. CAST(컬럼명 AS decimal)
0x06 데이터 일부만 추려보기 (LIMIT)
- LIMIT는 ORDER BY보다도 뒤에 써야 한다.
즉, WHERE - ORDER BY - LIMIT 순이다. - LIMIT는 ORDER BY와 함께 많이 사용된다.
### 키 오름차순으로 정렬하여 10개의 row만 보기
SELECT * FROM member ORDER BY height LIMIT 10;
### 키 오름차순으로 정렬하여 9번째 row부터 2개의 row만 보기
SELECT * FROM member ORDER BY height LIMIT 8, 2;
-- row는 0번째부터 시작함을 주의..! SELECT COUNT(*) # (5)
FROM member # (1)
WHERE id=>3 # (2)
GROUP BY gender # (3)
HAVING # (4)
ORDER BY age # (6)
LIMIT 10 # (7)게시판에서 흔히 볼 수 있는 < 1 2 3 4 5 >와 같은 이미지를 본 적이 많을 것이다. 매 페이지에는 다음과 같은 식으로 서로 다른 내용들이 담겨있다. 1페이지: 1~10번까지 // 2페이지: 10~20번 까지⋯ 이렇게 새로운 페이지를 누를 때마다 10개의 새로운 내용들을 로드하는데 이를 Pagination이라고 한다. 전체 결과를 한번에 로드하는 게 아니라 페이지 단위로 쪼개서 그때그때 요청이 있을 때마다 부분 결과를 조금씩 로드하는 방식이다.
이 Pagination은 LIMIT와 서로 관계가 깊다. 예를들어 1페이지를 로드할 때 LIMIT를 이용하여 SELECT * FROM db.search_result ~ ORDER BY registration_date DESC LIMIT 0, 10과 같이 쓸 수 있다. 당연히 실무에서는 위보다 훨씬 다양하고 복잡한 방식을 사용하는데, 다만 위와같은 근본적인 원리가 사용된다는 것만큼은 기억하자.
hello world :)