MariaDB로 SQL 공부_4

엘·2023년 5월 31일
sql
0

SQL

목록 보기
4/4
post-thumbnail

💻 터미널에서 SQL 기초 문법 공부하기

GROUP BY

SELECT depart, AVG(salary) FROM tStaff GROUP BY depart;
+-----------+-------------+
| depart    | AVG(salary) |
+-----------+-------------+
| 영업부    |    347.8571 |
| 인사과    |    327.2857 |
| 총무부    |    370.0000 |
+-----------+-------------+

GROUP BY 함수는 기준이 되는 필드를 적어 필드가 같은 레코드를 모은 결과를 출력한다. GROUP BY 절에서는 필드 목록에 기준 필드나 집계 함수만이 올 수 있다. 그룹과 관계없는 필드는 집계 함수없이 단독으로 출력할 수 없다.

HAVING

GROUP BY의 조건절. GROUP BY 다음에 와서 통계 결과 중 출력할 그룹의 조건을 지정한다.

SELECT depart, AVG(salary) FROM tStaff GROUP BY depart HAVING AVG(salary) >=349;
+-----------+-------------+
| depart    | AVG(salary) |
+-----------+-------------+
| 총무부    |    370.0000 |
+-----------+-------------+

원본테이블 ->(WHERE) 통계 대상 선정 ->(GROUP BY/AVG) 집계 ->(HAVING) 출력 대상 -> (ORDER BY) 출력

INSERT

새로운 레코드의 추가

INSERT INTO 테이블 (필드목록) VALUES (값 목록)

COMMIT 임시 영역의 변경 사항이 이상이 없다고 판단한 후 테이블에 영구 적용하는 것
ROLLBACK 변경 사항의 취소

// 테이블을 원래대로 초기화하고 싶은 경우
TRUNCATE TABLE tCity;

INSERT SELECT

INSERT INFO 대상테이블(필드목록) SELECT 필드목록 FROM 원본테이블
CREATE TABLE 대상테이블 AS SELECT 필드목록 FROM 원본테이블

DELETE와 TRUNCATE의 차이

DELETE의 경우 테이블의 모든 레코드를 삭제하지만, 레코드를 논리적인 방법으로 지우고 로그를 남겨 느리다.
TRUNCATE는 물리적인 기억장소도 비우고 임시 영역에 로그를 남기지 않기 때문에 DELETE보다 훨씬 빠르다. 단, ROLLBACK 으로 취소가 불가능하다.

UPDATE

레코드의 필드 값 변경

UPDATE 테이블 SET 필드 = 값 [,필드=값] WHERE 조건

// UPDATE tCity SET popu=1000 WHERE name='서울';

무결성 관리

  1. 클라이언트 측 - 입력할 정보에 맞는 라디오 버튼, 리스트 박스 등의 컨트롤로 오입력 차단. 자바 등의 언어로 섬세한 처리를 할 수 있지만 클라이언트가 무거워지는 단점
  2. 서버 측 - DBMS가 규칙을 기억한 후 비정상 데이터의 입력을 거부
  • 컬럼 무결성(도메인 무결성) : 타입 지정, 널 허용 여부, 체크, 기본값 제약
  • 엔터티 무결성 : 기본키와 무결성 제약
  • 참조 무결성 : 외래키 제약

✔ null 허용 대신 default를 사용하는 것이 성능상 유리함

  • 수치형 : 0 사용
  • 문자열 : N/A 사용
CREATE TABLE tCityDefault
    -> (
    -> name CHAR(10) PRIMARY KEY,
    -> area INT NULL,
    -> popu INT NULL,
    -> metro CHAR(1) DEFAULT 'n' NOT NULL,
    -> region CHAR(6) NOT NULL
    -> );
    
INSERT INTO tCityDefault VALUES ('강릉', 1111,22,DEFAULT, '강원');

SELECT*FROM tCityDefault;
+--------+------+------+-------+--------+
| name   | area | popu | metro | region |
+--------+------+------+-------+--------+
| 강릉   | 1111 |   22 | n     | 강원   |
+--------+------+------+-------+--------+

체크 제약

CREATE TABLE tCheckTest
    -> (
    -> gender CHAR(3) NULL CHECK(gender ='남' OR gender='여'),
    -> grade INT NULL CHECK (grade>=1 AND grade <=3),
    -> origin CHAR(3) NULL CHECK(origin IN ('동','서','남','북')),
    -> name CHAR(10) NULL CHECK(name LIKE '김%')
    -> );

복합키

CREATE TABLE tCity
    -> (
    -> name CHAR(10) PRIMARY KEY,
    -> region CHAR(6) PRIMARY KEY,
    -> area INT NULL,
    -> popu INT NULL,
    -> metro CHAR(1) NOT NULL
    -> );

UNIQUE

필드의 중복값을 방지하여 모든 필드가 고유한 값을 가지도록 강제. 기본키를 보조하는 중복 방지 제약

  • 기본키는 NULL을 허용하지 않지만 유니크는 NULL 허용 (단, 하나의 NULL 만 존재 가능)
  • UNIQUE와 NOT NULL을 동시 지정 시 기본키와 유사해짐. 기본키는 테이블 당 하나만 지정할 수 있지만 유니크는 개수에 상관없이 지정 가능
  • 기본키는 자동으로 인덱스 생성, 레코드의 정렬 순서를 결정하지만 유니크는 인덱스 생성 시 기본키의 인덱스와 종류 및 효율이 다름
CREATE TABLE tCityUnique
    -> (
    -> name CHAR(10) PRIMARY KEY,
    -> area INT NULL,
    -> popu INT UNIQUE NULL,
    -> metro CHAR(1) NOT NULL,
    -> region CHAR(6) NOT NULL
    -> );
    
CREATE TABLE tCityUnique
    -> (
    -> name CHAR(10) PRIMARY KEY,
    -> area INT NULL,
    -> popu INT NULL,
    -> metro CHAR(1) NOT NULL,
    -> region CHAR(6) NOT NULL
    -> CONSTRAINT Unique_tCity_area_pupu UNIQUE(area,popu)
    -> );

시퀀스

오라클과 SQL Server에는 시퀀스 객체 지원. 객체를 초기화해 놓으면 요청 시마다 증가한 값을 자동으로 돌려줌.

CREATE SEQUENCE 이름 [AS 타입] START WITH 시작값 INCREMENT BY 증가값
MINVALUE 최솟값 MAXVALUE 최댓값 CYCLE | NOCYCLE CACKE | NOCACHE
CREATE SEQUENCE seqSale START WITH 1 INCREMENT BY 1;
CREATE TABLE tSale
    -> (
    -> saleno INT PRIMARY KEY,
    -> customer VARCHAR(20),
    -> product VARCHAR(30)
    -> );
    
INSERT INTO tSale VALUES(NEXT VALUE FOR seqSale, '단군', '지팡이');
INSERT INTO tSale VALUES(NEXT VALUE FOR seqSale, '고주몽','고등어');

SELECT*FROM tSale;
+--------+-----------+-----------+
| saleno | customer  | product   |
+--------+-----------+-----------+
|      1 | 단군      | 지팡이    |
|      2 | 고주몽    | 고등어    |
+--------+-----------+-----------+

DELETE FROM tSale WHERE saleno =2;
INSERT INTO tSale VALUES(NEXT VALUE FOR seqSale, '박혁거세','계란');

// 시퀀스는 테이블의 레코드가 삭제됨을 모르며, 카운트 된 다음 번호를 출력
SELECT*FROM tSale;
+--------+--------------+-----------+
| saleno | customer     | product   |
+--------+--------------+-----------+
|      1 | 단군         | 지팡이    |
|      3 | 박혁거세     | 계란      |
+--------+--------------+-----------+

AUTO_INCREMENT

마리아 DB는 시퀀스 지원X

CREATE TABLE tTestSale
    -> (
    -> seleno INT PRIMARY KEY AUTO_INCREMENT,
    -> customer NCHAR(10),
    -> product NCHAR(30)
    -> );
    
INSERT INTO tTestSale (customer,product) VALUES('단군', '지팡이');
INSERT INTO tTestSale (customer,product) VALUES('고주몽', '고등어');
INSERT INTO tTestSale(customer,product) VALUES('박혁거세','계란');

UPDATE tTestSale SET product = '너구리' WHERE seleno = LAST_INSERT_ID();

SELECT*FROM tTestSale;
+--------+--------------+-----------+
| seleno | customer     | product   |
+--------+--------------+-----------+
|      1 | 단군         | 지팡이    |
|      2 | 고주몽       | 고등어    |
|      3 | 박혁거세     | 너구리    |
+--------+--------------+-----------+

DB별 자동 증가 필드

  • 오라클
    saleno INT GENERATED AS PRIMARY KEY IDENTITY
  • SQL Server
    saleno INT PRIMARY KEY IDENTITY
  • MariaDB
    saleno INT PRIMARY KEY AUTO_INCREMENT

🤍 참조

' 김상형의 sql 정복 ', 김상형 저, 6장 데이터 관리 ~ 7장 제약

profile
엘
화이트해커 꿈나무 엘입니다😉
이전 포스트

MariaDB로 SQL 공부_3

0개의 댓글