MariaDB#9 : SQL - DDL, DML, DCL(221228)6

USE 데이터베이스명;

SELECT 필드명 FROM 테이블명;

SELECT 필드명 FROM 테이블명 WHERE 조건;

 

- use - db선택

- (*은 전체선택)

- (.연산자 : DB이름.테이블 이름)

- (필드명에는 큰따옴표("")를 반드시 넣어줘야 한다.) - 외부에서 치는 문자열은 반드시 큰따옴표!!

- 데이터베이스는 대소문자를 인식하지 않는다. (대문자와 소문자 상관없음)

 

 

---

CREATE TABLE 테이블명 (필드명 필드데이터형);

INSERT INTO 테이블명

  SELECT 필드명

  FROM 가지고올테이블

  LIMIT 조건;

SELECT * FROM 테이블명;

- 생성한 테이블에 조건에 해당하는 데이터를 가져와 저장하는 방법.

- CREATE TABLE 테이블명 (필드,,,,)                 로 새로운 테이블을 생성해준다.

     필드 데이터형이 varchar이라면, (name VARCHAR(5)); 이런식으로 크기를 꼭 괄호 안에 넣어주어야 한다.

 

- INSERT INTO 새로만든테이블명                    이미 존재하는 다른 테이블에서 일부분을 가지고 오기 위하여 

- SELECT 필드명                                              쿼리문으로, 가지고올 필드명들을 

- FROM 이미존재테이블명                                가지고올 테이블을 

- LIMIT 조건;                                                      가지고올 해당 데이터들의 제한 조건을 설정해준다.

 

- 그리고 확인하기 위한 SELECT * FROM 테이블명; 

- CREATE TABLE 새로운_테이블명 (SELECT 복사할열(필드) FROM 기존_테이블명) ; 

                  이렇게 테이블 복사 가능. 

 

---

SHOW DATABASES;

 혹은 

USE 데이터베이스명;

- 데이터베이스 조회

 

 

SHOW  TABLE STATUS;

- 테이블 조회

 

 

DESCRIBE 데이터베이스명;

혹은 

DESC 데이터베이스명;

- 열이름 조회

 

 

AS

- 열이름에 별칭을 주는 것

- 중간에 공백이 있으면 작은따옴표로 전체 감싸주기.

- 한글로 별칭만들면 보기 좋음

 

 

BETWEEN ... AND

- 숫자로 구성되고 연속적인 값 갖는 경우. (WHERE절에 들어가는 것)

 

 

IN()

- 이산적인 값을 가진 데이터 (WHERE절에 들어가는 것)

 

 

LIKE

- 문자열 내용검색 (WHERE절에 들어가는 것)

-    '-'앞에는 한글자

-    '%'뒤에는 여러글자

 

 

ANY

- 서브쿼리(쿼리문 안에 쿼리문이 또 있는것)

- 서브쿼리 여러개중 한가지만 만족해도 됨 (SOME과 동일)

 

 

ALL

- 서브쿼리(쿼리문 안에 쿼리문이 또 있는것)

- 서브쿼리의 모든 결과 만족시켜야함

 

 

ORDER BY

- 결과가 출력되는 순서 조절하는 구문

- 기본은 오름차순 (ASC)

- 내림차순은 열이름 뒤에 DESC

- SELECT, FROM, WHERE, GROUP BY,HAVING, ORDER BY 중에서 제일 뒤에 넣을 것

 

 

SELECT DISTINCT 필드명 FROM 테이블명;

- 중복성 최소화. (제거가 아님!) (WHERE절에 들어가는 것)

 

 

LIMIT

- 출력갯수 제한 (조건)

                    SELECT ... FROM ... ORDER BY `userName` ASC LIMIT 2;    // 상위 2개 출력

 

 

GROUP BY

- 그룹으로 묶어줌

- GROUP BY는 테이블 뒤에 넣어야한다. (SELECT FROM 사이에 올 수 없음)

- 집계 함수 (GROUP BY와 함께 사용. SUM (필드명) 등이 있음.)

-  SUM(...)같은 경우는 라이브러리 집계함수이다.

- 집계함수의 결과를 별칭으로 사용하여 따로 열로 출력할 수 있다.

                      SELECT userID AS '아이디' , SUM(price*amount) AS '총 구매액' FROM buytbl GROUP BY userID;

- 집계함수는 필드위치에 사용

 

 

집계함수 종류

- AVG()

- MIN()

- MAX()

- COUNT()     : 행의 갯수

- COUNT(DISTINCT)     : 행의 갯수(중복은 1개만)

- STDEV()     : 표준편차

- VAR_SAMP()     : 분산

 

 

조건연산자

- 데이터베이스에서는 = (하나)

-          =, <, >, <=, >=, < >, != 등

-          NOT, AND, OR 등

 

 

HAVING

- 집계함수는WHERE절에 x

- HAVING 절은 GROUP BY 절 다음에 위치

- 반드시 GROUP BY와 같이, GROUP BY 다음에 위치해서 사용

                    SELECT userID AS '사용자‘, SUM(price * amount) AS '총구매액' FROM buytbl GROUP BY userID

                                  HAVING SUM(price*amount) >1000 ORDER BY SUM(price*amount);

 

 

ROLLUP

- 총합 또는 중간 합계

- GROUP BY절과 WITH ROLLUP문 사용

                GROUP BY groupName WITH ROLLUP;

 

---

SQL문 - DML, DDL, DCL

 

DML (데이터 조작 언어)

- Data Manipulation Language

- 선택, 삽입, 수정, 삭제 (데이터 조작)

- 대상은 테이블의 행 (DML언어를 사용하려면 테이블은 이미 정의되어 있어야한다.)

- SELECT, INSERT, UPDATE, DELETE

- transaction(트랜젝션)이 발생. - > 테이블의 데이터가 변경될때 임시로 적용.(되돌림(ROLLBACK), 완전적용(COMMIT))

 

 

DDL (데이터 정의 언어)

- Data Definition Language

- 생성/삭제/변경 (데이터베이스 개체 : 데이터베이스, 테이블, 뷰, 인덱 스 등)

- CREATE, DROP, ALTER

- 실행 즉시 MariaDB에 적용

 

 

DCL (데이터 제어 언어)

- Data Control Language

- 권한 설정

- GRANT/REVOKE/ DENY

- GRANT와 REVOKE은 같이 사용 (짝꿍임)

 

---

INSERT

INSERT [INTO] 테이블명[(열1, 열2, ...)] VALUES (값1, 값2, ...)

- 데이터 삽입

- 테이블에 데이터를 삽입하는 명령어

                       INSERT INTO memberTBL VALUES ('...', '...' , '...', ...) -> 멤버테이블에 값 삽입.

 

- 자동으로 증가하는 AUTO_INCREMENT : INSERT명령어에서 해당 열은 입력하지 않음. 자동으로 1부터 증가

                                                                    꼭 기본키 혹은 UNIQUE로 지정

/* 이미 연결되어있는 것들과의 오류를 없애기 위해 꼭 지웠다가 사용하기 */
DROP TABLE IF EXISTS `test_table1`;

/* DDL */
CREATE TABLE `test_table1` (
	`id` INT(11) NOT NULL AUTO_INCREMENT, /* 자동 증가 */
    `col` VARCHAR(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    PRIMARY KEY(`id`) /* 기본키 설정 */
);

/* INSERT */
INSERT INTO test_table1(col)
VALUES('1r'), ('2r'), ('3r');

/* 지원 라이브러리 사용하면 마지막에 입력된 값 출력 */
SELECT * FROM test_table1;
SELECT last_insert_id();

 

SELECT * FROM test_table1;

SELECT last_insert_id();

- SELECT LAST_INSERT_ID()문 : 사용하면 마지막에 입력된 값 출력. 지원 라이브러리이다.

- ALTER TABLE 테이블명 : 증가 시작값 변경

                                       AUTO_INCREMENT = 시작하고싶은값;

- SET @@auto_increment_increment = 10;  : 10씩 증가

- INSERT INTO… SELECT : 기존의 대량의 데이터를 샘플 데이터로 사용할 때

 

INSERT INGNORE 과 ON DUPLICATE UPDATE

- 여러건을 입력하는데 오류가 생길 때, 진행 방법. (보통 오류생기면 그지점부터 입력이 안된다.)

- INSERT INGNORE : 오류가 생겨도 오류를 발생시키지 않아 오류 이후의 데이터는 입력됨 (특별한 경우에만 사용하기)

- ON DUPLICATE UPDATE : 중복되지 않으면 일반 INSERT, 중복되면 그 뒤의 UPDATE문 실행

USE sqldb;
-- DROP TABLE IF EXISTS `memberTBL`;
/*
CREATE TABLE memberTBL (SELECT userID, userName, addr FROM usertbl LIMIT 10);
ALTER TABLE memberTBL
ADD CONSTRAINT pk_memberTBL PRIMARY KEY (userID);
*/
-- SELECT * FROM memberTBL;

INSERT IGNORE INTO memberTBL VALUES('KAN', '김안녕', '미국');
INSERT IGNORE INTO memberTBL VALUES('KJV', '김자바', '서울');
INSERT IGNORE INTO memberTBL VALUES('KDB', '김데베', '경기');

SELECT * FROM memberTBL;

 정상적으로 추가된다. (중복은 지워짐)

USE sqldb;

INSERT INTO memberTBL VALUES('KAN', '김하이', '미국')
ON DUPLICATE KEY UPDATE userName='김하이', addr='미국';
INSERT INTO memberTBL VALUES('BYE', '김잘가', '일본')
ON DUPLICATE KEY UPDATE userName='김잘가', addr='일본'; 

SELECT * FROM memberTBL;

중복된 것은 ON DUPLICATE KEY UPDATE 뒤의 문으로 실행되고, 중복되지 않은 것은 INSERT INTO ... VALUES 값으로.

 

 

 

UPDATE

UPDATE 테이블명

    SET 열1=값1, 열2=값2, ...

    [WHERE 조건] ;

- 데이터 수정

- WHERE절 생략가능.

 

 

DELETE

DELETE FROM 테이블명

    WHERE 조건 ;

- 데이터 삭제

- 행 단위

- LIMIT와 함께 사용 가능

- DELETE FROM 테이블명;      : 구조는 남고, 데이터 삭제 (트랜젝션 로그 기록)

- DROP TABLE 테이블명;          : 다 삭제 -> 주로 사용

- TRUNCATE TABLE 테이블명;  : 메타데이터는 남는다 (거의 사용 x)