[SQL] 기초 끄적끄적 (DDL / DML)
관계형 데이터베이스(RDBMS)
종류: 오라클, mysql, MariaDB, MS - Access
특징: 데이터 베이스를 테이블 형태로 표현
비관계형 데이터베이스
거대한 양의 데이터와 비정형 데이터가 포함된 데이터를 저장하고 사용하는 기술
종류: MongoDB, HBase, Cassndra
* 데이터 타입
① 가변문자열: varchar(n)
② 고정문자열: char(n)
③ 숫자: int
④ 날짜: date
⑤ 텍스트: text
⑥ 열거형: enum
* 옵션
① primary key: 기본키(중복불가, not null)
② not null: 필수입력
③ null: 널값 허용
④ unique: 유일값(중복불가)
⑤ auto_increment: 자동증가값
SHOW databases; // 어떤 데이터베이스가 있는지 보여죠
SHOW tables; // 어떤 테이블들이 있는지 보여죠
DESC table명; // 테이블 구조 보여죠
MySQL 잘못 눌렀을 때 Ctrl+C 누르면 빠져나올 수 있음
1. 데이터 정의 언어(DDL: Data Definition Language)
DB구조, 데이터 형식, 접근 방식 DB를 그축하거나 수정할 목적으로 사용하는 언어
1) CREATE(생성)
- 데이터베이스 생성
CREATE DATABASE 데이터베이스 이름;
ex> CREATE DATABASE test;
- 테이블 생성
CREATE TABLE 테이블명 (
컬럼명 데이터타입[옵션],
컬럼명 데이터타입[옵션],
...
)
ex> CREATE TABLE bbs(
num int primary key auto_increment, // 숫자 자동으로 1씩 올라가게 붙이겠다 ~
name varchar(20) not null,
email varchar(50) not null,
tel varchar(13) not null,
addr varchar(50)
);
2) ALTER(변경) ;테이블 컬럼 수정
- 테이블에 컬럼추가
ALTER TABLE 테이블명 ADD COLUMN 컬럼명 컬럼타입;
ex> ALTER TABLE bbs ADD COLUMN userId varchar(20);
- 테이블의 컬럼타입 변경하기
ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 컬럼타입;
ex> ALTER TABLE bbs MODIFY COLUMN userId int;
- 테이블의 컬럼 삭제하기
ALTER TABLE 테이블명 DROP COLUMN 컬럼명
ex> ALTER TABLE bbs drop column userId;
- 테이블의 컬럼 이름 변경하기
ALTER TABLE 테이블명 CHANGE COLUMN 컬럼명 새로운컬럼명 컬럼타입
ex> ALTER TABLE bbs change column addr address varchar(60);
3) DROP(삭제)
- 데이터베이스 삭제
DROP DATABASE 데이터베이스 이름;
- 테이블 삭제
DROP TABLE 테이블명;
2. 데이터 조작 언어(DML)
1) INSERT(데이터 생성)
INSERT INTO 테이블명(column1, column2, ...) VALUES(value1, value2, ...);
ex>
INSERT INTO bbs(name, email, tel, addr)
values('이순신', ' test@test.com', '010-1234-1234', '울산시 남구 삼산동');
INSERT into bbs(name, email, tel, addr)
VALUES('홍길동', ' test@test.com', '010-1111-1111', '울산시 남구 달동');
INSERT into bbs(name, email, tel, addr)
VALUES('김유신', ' abc@abc.com', '010-1111-1234', '울산시 동구 서부동');
2) SELECT(데이터 조회)
SELECT * FROM 테이블명;
ex> SELECT * FROM bbs;
ex> SELECT name FROM bbs;
SELECT 컬럼명
FROM 테이블명
WHTERE 컬럼명 조건절
ORDER BY 컬럼명 ASC(오름차순) | DESC(내림차순)
ex> 이름이 김승용이거나 임정제이거나 전용진인 레코드 검색
SELECT * FROM members
WHERE name IN ('김승용', '임정제', '전용진');
- 조건절
① 비교문
> 크다 / >= 크거나 같다 / <= 작거나 같다 / < 작다 / = 같다 / <> 같지 않다
② 논리
AND / OR / NOT
③ BETWEEN A AND B
④ 패턴
LIKE %
ex> LIKE '김%'
ex> LIKE '%광역시'
⑤ IS NULL, IS NOT NULL
- 정렬
① ASC 오름차순
ORDER BY 컬럼명 ASC
ex> ORDER BY pname ASC
---> pname 컬럼명을 기준으로 오름차순 정렬
② DESC 내림차순
ORDER BY 컬럼명 DESC
ex> ORDER BY pname desc
---> pname 컬럼명을 기준으로 내림차순 정렬
3) UPDATE(데이터 갱신)
UPDATE 테이블명
SET 컬럼명="변경값", 컬럼명2 = "변경값"
WHTERE 조건절
ex> UPDATE members
SET name = "김그린", tel = "052-123-4567"
WHERE no = 2;
ex> UPDATE members
SET license = "y" ;
WHERE addr like "부산%";
4) DELETE(데이터 삭제)
DELETE FROM 테이블명
WHERE 조건절
ex> DELETE FROM members
WHERE addr like "충청%";
- 예제 01
문제 01) 이름이 김씨인 사람의 모든 컬럼을 검색
SELECT * FROM employee
WHERE pname like "김%";
문제 02) 퇴사한 사람의 이름과 퇴사일을 검색
SELECT pname, firedate FROM employee
WHERE firedate IS NOT NULL;
문제 03) 보너스를 받지않는 사람의 이름을 검색
SELECT pname FROM employee
WHERE bonus IS NULL;
문제 04) job이 사원인 사람의 사원번호, 이름, 직급순으로 출력(이름을 오름차순정렬)
SELECT perno, pname, job FROM employee
WHERE job = '사원'
ORDER BY pname ASC;
문제 05) 급여의 최대값을 검색
SELECT MAX(sal) AS '최대급여' FROM employee;
문제 06) 현재 근무자 수를 검색
SELECT COUNT(*) AS '근무자 수' FROM employee
WHERE firedate IS NULL;
문제 07) 보너스를 수령한 사람 수를 검색
SELECT COUNT(*) AS '보너스 수령 인원' FROM employee
WHERE bonus IS NOT NULL;
문제 08) 직급이 과장인 사람의 평균 월급여를 검색
SELECT AVG(sal) AS '과장 평균 급여' FROM employee
WHERE job = '과장';
문제 09) 사원명으로 내림차순 정렬 후 레코드 3개만 출력
SELECT * FROM employee
ORDER BY pname DESC LIMIT 3;
문제 10) 2012년 입사자를 급여기준으로 내림차순 정렬 후 레코드 4개만 출력
SELECT * FROM employee
WHERE year(hiredate) = 2012
ORDER BY sal DESC LIMIT 4;
또는
SELECT * FROM employee
WHERE hiredate LIKE '2012%'
ORDER BY sal DESC LIMIT 4;
문제 11) 부서번호 30번인 사람의 이름으로 오름차순 정렬 후 상위 2개 레코드만 출력
SELECT * FROM employee
WHERE deptno = 30
ORDER BY pname ASC LIMIT 2;
문제 12) 입사일이 2012년인 입사자 수 검색
SELECT COUNT(*) AS '2012년 입사자 수' FROM employee
WHERE year(hiredate) =2012;
또는
SELECT COUNT(*) AS '2012년 입사자 수' FROM employee
WHERE hiredate LIKE '2012%'
문제 13) 부서번호별로 부서번호와 급여의 평균을 출력
SELECT deptno, AVG(sal) AS '부서별급여평균' FROM employee
GROUP BY deptno;
문제 14) 직급의 개수 출력
SELECT COUNT(DISTINCT job) AS '직급수' FROM employee
- 예제 02 GROUP BY 실습예제
문제 01) 이름이 장씨인 사원 검색
SELECT * FROM employee2 WHERE name LIKE '장%';
문제 02) 2부서에 있는 사원의 급여평균을 검색
SELECT AVG(sal) AS '평균급여' FROM employee
WHERE partno = '2부서';
문제 03) 부서의 개수를 출력 (증복되는 값 제외)
SELECT COUNT(DISTINCT partno) AS '부서수' FROM employee;
문제 04) 부서별로 부서번호와 급여의 평균을 출력
SELECT partno, AVG(sal) FROM employee
GROUP BY partno;
문제 05) 보너스를 받지 않는 사람의 수를 출력
SELECT COUNT(*) AS '보너스를 받지 않는 사람의 수' FROM employee
WHERE bonus IS NULL;
문제 06) 부서별로 부서번호와 월급의 합계(부서번호 1부서 또는 2부서만 출력)
SELECT partno, SUM(sal) FROM employee
GROUP BY partno
HAVING partno = '1부서' OR partno = '2부서';