데이터베이스 By mariaDB
Maria DB (1)
- 데이터베이스와 dbms, maria DB 간단한 소개
- maria DB 실습 환경 구축
- select, where query 연습
-
데이터베이스와 dbms, maria DB 간단한 소개
Maria DB는 MySql에서 파생된 RDBMS의 하나로 MySql과 비슷한 문법에 빠른 속도와 경량화를 자랑하는 데이터 베이스이다.
-
mariaDB 실습 환경 구축
1) 먼저 maria DB를 운영체제에 맞게 설치한다.
2) db의 bin을 path의 환경변수로 등록한다.
3) 이후 window 기준 powershell을 통해 mariaDB에 접근 가능하고 heidiSql 콘솔을 통해 GUI로 db를 실행 가능하다.
( 파워쉘을 통해 maria DB를 실행한 모습)
(heidiSql에서 세션을 생성하여 DBMS 생성)-
권한 부여 단계
select / where
> 특정 열을 또는 열들을 RESULT SET으로써 조회하는 키워드 SELECT
> ALIAS로 열 별칭을 설정할 수 있다.
SELECT * FROM EMPLOYEE;
SELECT emp_no, emp_id FROM employee;
SELECT emp_no AS 'no', emp_id 'id', emp_name `name`,
job_code jc
FROM EMPLOYEE;
> DISTINCT, LIMIT
> DISTINCT는 중복행을 제거하는 키워드, 여러 COLS 사용 가능
> LIMIT은 출력 행을 제한하는 키워드
SELECT DISTINCT mobile1, mobile2 FROM employee;
SELECT * FROM employee
LIMIT 1000;> 조회 조건을 지정하는 키워드 WHERE
> 비교 연산자 (=,!=, ..) , 관계 연산자 (>=, ..), 논리연산자 (AND, OR, BETWEEN ~ AND ~ (이상 ,이하)), IN/NOT IN 연산자
, 문자열에 사용하는 LIKE 연산자, IS NULL/IS NOT NULL (NULL비교는
IS/IS NOT과 함께 사용) 등의 연산자를 사용한다.
-- BETWEEN AND 연산자 사용
-- usertbl 테이블에서 키가 170 이상 182 이하인 회원의 모든 데이터 조회
SELECT *
FROM usertbl
-- WHERE height BETWEEN 170 AND 182
-- WHERE height NOT BETWEEN 170 AND 182
WHERE NOT height BETWEEN 170 AND 182
-- IN 연산자 사용
-- 주소가 경남, 경북, 전남인 회원의 이름, 주소 조회
SELECT name, addr
FROM usertbl
-- WHERE addr = '경남' OR addr = '경북' OR addr = '전남';
-- WHERE addr IN ('경남', '경북', '전남');
-- WHERE NOT addr IN ('경남', '경북', '전남');
WHERE addr NOT IN ('경남', '경북', '전남');
-- employee 테이블에서 전화번호 처음 3자리가 010이 아닌 사원의 이름, 전화번호 조회
SELECT emp_name, IFNULL(phone, '번호없음')
FROM employee
WHERE IFNULL(phone, '번호없음') NOT LIKE '010%';
-- employee 테이블에서 이메일 중 _ 앞 글자가 3자리인 주소를 가진 사원의 사번, 직원명, 이메일 조회
-- 예시) sun_di@kh.or.kr
SELECT emp_id, emp_name, email
FROM employee
-- WHERE email LIKE '___\_%';
WHERE email LIKE '___$_%' ESCAPE '$';ORDER BY/ GROUP BY
> 행을 정렬하는 키워드 ORDER BY
> 특정열을 정렬 기준으로 사용 할 수 있고, 후순위 정렬 기준을 지정할 수 있다.
> 기본이 오름차순이고 ASC/DESC 키워드로 오름차순/내림차순을 명시 할 수 있다.
-- usertbl 테이블에서 addr으로 오름차순 정렬
-- 단, addr 일치할 경우 mDate를 가지고 내림차순 정렬
SELECT userid, NAME, addr, mDate
FROM usertbl
-- ORDER BY addr, mDate DESC;
ORDER BY addr ASC, mDate DESC;
-- usertbl 테이블에서 아이디, 이름, 가입일 조회(별칭 포함)
-- 단, 이름으로 내림차순 정렬
SELECT userid AS '아이디',
NAME AS '이름',
mDate AS '가입일'
FROM usertbl
-- ORDER BY NAME DESC;
-- ORDER BY 2 DESC; -- 열의 순번 사용
ORDER BY `이름` DESC; -- 별칭 사용
> 데이터를 그룹화 하는 키워드 GROUP BY
> 특정 열의 값을 기준으로 데이터를 그룹화하여 RESULT SET을 제공
> COUNT(*) 연산자와 자주 사용, HAVING 절로 그룹 조건 설정
> 마찬가지로 여러 컬럼을 그룹 기준으로 사용할 수 있다
(EX) GROUP BY X,Y -> X와 Y의 모든 조합을 그룹으로 사용
-- employee 테이블에서 부서별 사원의 수, 보너스를 받는 사원의 수, 급여의 합, 평균 급여, 최고 급여, 최저 급여를 조회 (부서별 내림차순)
SELECT dept_code,
COUNT(*),
COUNT(bonus),
SUM(salary),
FLOOR(AVG(salary)),
MAX(salary),
MIN(salary)
from employee
GROUP BY dept_code
ORDER BY dept_code DESC;
-- HAVING 실습
-- buytbl 테이블에서 총 구매액이 1000 이상인 회원의 아이디를 조회
SELECT userid,
SUM(price * amount) AS '구매액'
FROM buytbl
-- 집계 함수의 결과를 WHERE 절에서 조건으로 사용할 수 없다.
-- WHERE SUM(price * amount) >= 1000
-- HAVING 절은 반드시 GROUP BY절 다음에 작성해야 한다.
-- HAVING SUM(price * amount) >= 1000
GROUP BY userid
HAVING SUM(price * amount) >= 1000
ORDER BY '구매액'
* 조회문에서 키워드 실행 순서
1) FROM
2) WHERE
3) GROUP BY
4) HAVING
5) ORDER BY
6) SELECT
7) LIMIT
DB - 데이터 변경
- INSERT
- UPDATE
- DELETE
- 조건부 데이터 입력, 변경
INSERT
> 기본키 필드, not Nullable 필드는 반드시 insert해야 한다.
> default 제약조건, AUTO_INCREMENT, Nullable 필드는 생략 가능
-- 필드를 인자로 지정해주어 값을 넣어준다.
INSERT INTO usertbl(userid, NAME, birthYear, addr) VALUES ('hong123', '홍길동', 2015, '서울');
INSERT INTO usertbl(NAME, addr, userid, birthyear) VALUES ('유영', '안산', 'JYY', 2001);
-- 필드 인자를 명시 하지 않으면, 모든 필드 값을 테이블 열 순서대로 전달해주어야 한다.
INSERT INTO usertbl VALUES('SIL', '이성일', 1998, '서울');
INSERT INTO usertbl VALUES('SIL','이성일', 1998, '서울', '010', '12345678', 179, NULL);
-- 여러 개의 행 삽입
INSERT INTO usertbl(USERID, NAME, BIRTHYEAR, ADDR) VALUES ('OH', '오혜성', 1998, '경기'), ('YSW', '양수우', 1997, '화정');
CREATE TABLE usertbl_copy(
SELECT *
FROM usertbl
WHERE 1 = 0
);
-- 다른 테이블로부터 SELECT 한 결과를 테이블에 삽입
INSERT INTO USERTBL_COPY (
SELECT *
FROM usertbl
WHERE ADDR = '강원'
);
-- 열의 순서와 데이터 유형이 맞아야 행이 삽입된다.
INSERT INTO usertbl_copy(userid, name, birthYear, addr) (
SELECT userid,
name,
birthYear,
addr
FROM usertbl
WHERE addr = '강원'
);
DROP TABLE usertbl_copy;
UPDATE
> UPDATE 'TABLE NAME' SET 'FIELD' = 'VALUE' WHERE 'FIELD' = VALUE 형식
> WHERE 절을 생략 하면 모든 행에 적용
UPDATE usertbl
SET `name` = '고길동'
WHERE `name` = '홍길동';
DELETE
> DELETE FROM 'TABLE' WHERE ~ LIMIT `No`
> DELETE 또한 WHERE 절을 추가 하지 않으면 모든 행을 삭제 한다.
DELETE FROM usertbl WHERE NAME = '유영';
DELETE FROM usertbl WHERE MOBILE1 IS NULL LIMIT 2;
조건부 데이터 입력, 변경과 COMMIT/ROLLBACK
> IGNORE -> 기본 키가 중복된 데이터 삽입 시 경고를 출력한다.
> INSERT INTO `TABLE` VALUES ~ ON DUPLICATE KEY UPDATE ~ -> 데이터 없으면 INSERT, 있으면 UPDATE
INSERT IGNORE INTO usertbl(userid, NAME, birthYear, addr)
VALUES ('BBK', '바보킴', 1999, '인천');
-- usertbl 테이블에 userid가 BBK인 회원이 없으면 INSERT를 수행하고
-- userid가 BBK인 회원이 있으면 UPDATE를 수행한다.
INSERT INTO usertbl(userid, NAME, birthYear, addr)
VALUES ('BBK', '바보킴', 1999, '인천')
ON DUPLICATE KEY UPDATE NAME = '바비킴', addr = '인천';
> AUTOCOMMIT은 사용자가 COMMIT 명령을 실행하지 않아도 자동으로 모든 명령이 COMMIT 되어 즉시 반영되는 것을 말한다.
SELECT @@AUTOCOMMIT;
SET autocommit = 1; -- 활성화
SET autocommit = 0; -- 비활성화
-- autocommit이 활성화된 경우 ROLLBACK으로 실행이 취소되지 않는다.
DELETE FROM usertbl WHERE userid = 'HONG';
SELECT * FROM usertbl;
ROLLBACK;
-- autocommit이 비활성화된 경우 ROLLBACK으로 실행이 취소된다.
DELETE FROM usertbl WHERE userid = 'sung123';
SELECT * FROM usertbl;
ROLLBACK;
-- autocommit이 비활성화된 경우에 변경 사항을 확정하려면 COMMIT을 실행해야 한다.
DELETE FROM usertbl WHERE userid = 'sung123';
SELECT * FROM usertbl;
COMMIT;
ROLLBACK;
O_O