[Boostcourse] SQL, MySQL

SQL

1. MySQL

데이터를 쉽고 편리하게 다룰 수 있도록 DBMS(Database management system)을 배웁니다.

데이터베이스

• 데이터의 집합
• 공용(share)데이터, 통합된 정보 저장 및 운영
• 관련 데이터 집합끼리 연관, 조직화

데이터베이스 특성

• 실시간 접근성(Real-time Accessability)
• 계속적 변화(Continuous Evolution)
• 동시 공유성(Concurrent Sharing)
• 내용 참조(Content Reference)

데이터베이스 관리 시스템(DBMS)

• DB 관리하는 소프트웨어
• 여러 응용 소프트웨어(프로그램, 시스템)이 동시에 DB에 접근하여 사용할 수 있게함
• 필수 3기능
  • 정의기능: DB의 논리적, 물리적 구조 정의
  • 조작기능: 데이터 검색, 삭제, 갱신, 삽입 기능
  • 제어기능: DB의 내용 정확성과 안전성 유지
• Oracle, SQL Server, MySQL, DB2, MyBatis, PostgreSQL, SQLite 등이 있음

DBMS 장단점

• 데이터 중복 최소화
• 데이터 일관성 및 무결성 유지
• 데이터 보안 보장

• 운영비 비쌈
• 백업 및 복구에 대한 관리가 복잡
• 부분적 DB 손실이 정체 시스템 정지

2. MySQL 실행, 종료

service

데몬(Daemon)

3. SQL(Structured Query Language)

씨퀄(Structured English Query Language) → SQL

SQL 탄생배경 [http://www.ciokorea.com/print/35385]

• SQL는 데이터를 쉽게 검색, 추가, 삭제, 수정 등의 조작을 할 수 있도록 고안된 컴퓨터 언어입니다.
• 관계형 데이터베이스에서 데이터를 조작하고 쿼리하는 표준 수단입니다.

1) DDL (Data Definition Language) : 데이터베이스의 스키마 정의, 조작

​ CREATE, DROP, ALTER

2) DML(Data Manipulation Language) : 데이터 조작

​ INSERT, UPDATE, DELETE, SELECT

3) DCL (Data Control Language) : 데이터 제어, 권한 관리, 보안, 무결성 정의

​ GRANT, REVOKE

MySQL 접속

> mysql -uroot -p

user root, password 입력하여 접속

데이터베이스 생성

> create database connectdb;

권한설정

> grant all privileges on connectdb.* to connectuser @'%' identified by 'connect123!@#';
> grant all privileges on connectdb.* to connectuser @'localhost' identified by 'connect123!@#';
> flush privileges; 

grant all privileges on (db이름) to (user이름)

@'%' (모든 서버에게, %대신 localhost라고 하면 자기서버만)

identified by '(비밀번호)'

데이터베이스 접속

> mysql -h127.0.0.1 -uconnectuser -p connectdb
> password 입력

데이터베이스 연결종료

> quit;

MySQL 버전과 현재 날짜 구하기

> select version(), current_date;

프롬프트에서 SQL을 입력합니다.

SQL은 세미콜론(;)으로 끝납니다.

SQL은 쿼리(Query)라고 읽고, DBMS에게 명령을 내릴 때 사용하는 문장입니다.

SELEECT는 조회할 때 사용합니다.

MySQL은 쿼리결과의 전체 row와 쿼리실행 시간을 반환합니다.

• 키워드(SELECT 등)는 대소문자를 구별하지 않습니다.

> select now();

+---------------------+
| NOW()               |
+---------------------+
| 2019-11-21 15:28:22 |
+---------------------+
1 row in set (0.000 sec)

계산도 가능합니다.

> SELECT SIN(PI()/4), (4+1)*5;

+--------------------+---------+
| SIN(PI()/4)        | (4+1)*5 |
+--------------------+---------+
| 0.7071067811865476 |      25 |
+--------------------+---------+
1 row in set (0.006 sec)

• 한 줄 또는 여러줄로 입력이 가능합니다. - 가독성을 살려서 씁시다.

• 중간에 취소해야할 경우 \c를 입력하면됩니다.

DBMS에 존재하는 데이터베이스목록 확인

> show databases

사용중인 데이터베이스 전환

> use mydb;

전환하려는 db에 사용권한이 있어야 하고, 데이터베이스가 존재할 때 전환가능

테이블(table) 구성요소

테이블: RDBMS의 기본 저장구조, 한 개 이상의 column과 0개 이상의 row로 구성

열(Column): 테이블 상에서 단일 종류의 데이터를 나타냄, 특정 데이터 타입 및 크기를 가짐

행(Row): Column들의 값의 조합, 레코드(record)라고도 불림. 기본키(PK, primary key)에 의해 구분됨, 기본키는 중복을 허용하지 않고 꼭 존재해야 함.

Field: Row와 Column의 교차점, Field는 데이터를 포함할 수도 있고, NULL을 가지기도 함

테이블 조회

> show tables;

examples.sql 파일 MySQL에 table로 삽입

cd (examples.sql파일이 있는 경로)
mysql -uconnectuser -p connectdb < examples.sql

접속

mysql -uconnectuser -p connectdb
> show tables;
+-----------------------+
| Tables_in_connectdb   |
+-----------------------+
| bonus                 |
| department            |
| employee              |
| project               |
| project_participation |
| role                  |
| salarygrade           |
+-----------------------+
7 rows in set (0.001 sec)

접속 안 될 경우

mysql -uroot -p
> grant all privileges on connectdb.* to connectuser @'localhost' identified by 'connect123!@#';
> flush privileges; 

root로 접속 후, localhost에서도 권한을 수여합니다.

테이블 정보 살펴보기

> desc bonus;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| ename | varchar(10)  | NO   | PRI | NULL    |       |
| job   | varchar(9)   | NO   | PRI | NULL    |       |
| sal   | decimal(7,2) | YES  |     | NULL    |       |
| comm  | decimal(7,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.039 sec)

desc는 describe의 약자입니다.(desc대신 describe 명령어를 사용해도 됩니다.)

3-2) DML (select, insert, update, delete)

SELECT

SELECT(DISTINCT) 칼럼명(ALIAS)
FROM 테이블명;

DISTINCT는 중복행 제거

ALIAS는 결과 칼럼에 다른 이름 부여

SELECT * FROM department;

SELECT deptno 부서번호 FROM department;
SELECT deptno as 부서번호 FROM department;
+----------+
| 부서번호 |
+----------+
|       10 |
|       20 |
|       30 |
|       40 |
+----------+

문자열 결합함수 concat

SELECT concat(empno, '-', deptno) as '사번-부서번호' FROM employee;
+---------------+
| 사번-부서번호 |
+---------------+
| 7782-10       |
| 7839-10       |
| 7934-10       |
| 7369-20       |
| 7566-20       |
| 7788-20       |
| 7876-20       |
| 7902-20       |
| 7499-30       |
| 7521-30       |
| 7654-30       |
| 7698-30       |
| 7844-30       |
| 7900-30       |
+---------------+
14 rows in set (0.002 sec)

중복제거 distinct

SELECT DISTINCT deptno FROM employee;
 +--------+
| deptno |
+--------+
|     10 |
|     20 |
|     30 |
+--------+
3 rows in set (0.001 sec)

순서대로 정렬 order by

SELECT empno, name FROM employee ORDER BY name; (오름차순)
SELECT empno, name FROM employee ORDER BY name ASC; (위와 같음)
SELECT empno, name FROM employee ORDER BY 2; (2번째 열 기준 정렬, 위와 같음)
SELECT empno, name FROM employee ORDER BY 2 ASC; (위와 같음)

SELECT empno, name FROM employee order by name desc; (내림차순)

특정 행 검색 - Where절

SELECT 칼럼명
FROM 테이블명
WHERE 조건식

• =, <, >, <=, >=, and, or

• in

  SELECT *
  FROM employee
  WHERE deptno IN (10, 30);
  
  SELECT *
  FROM employee
  WHERE deptno = 10 or deptno = 30;

  위 두 구문은 같은 논리입니다.

• LIKE

  와일드카드 사용해서 특정 문자 포함한 값 조건 처리

  %는 0개 이상 문자열 나타냄

  _는 하나의 문자를 나타냄

  SELECT *
  FROM employee
  WHERE name LIKE 'A%';

UPPER, LOWER

SELECT LOWER(name) FROM employee;

행에 있는 각 값을 모두 소문자(LOWER)로 변환하여 보여줍니다.

substring

MySQL은 시작 인덱스가 1

SELECT SUBSTRING('Happy Day', 3, 2);
+------------------------------+
| SUBSTRING('Happy Day', 3, 2) |
+------------------------------+
| pp                           |
+------------------------------+

LPAD, RPAD

왼쪽 오른쪽에 패딩(채워줌)

SELECT LPAD(name, 10, '+') FROM employee;

TRIM, LTRIM, RTRIM

공백을 없앱니다.

SELECT LTRIM('   hello'), RTRIM('hello  ');

• ABS(x) : x 절댓값

• MOD(n, m) : n % m, n 나머지 m

• FLOOR(x) : 버림, x보다 크지 않은 가장 큰 정수, BIGINT로 자동 변환합니다.

• CEILING(x) : 올림, x보다 작지 않은 가장 작은 정수

• ROUND(x) : 반올림, x에 가장 근접한 정수

• POW(x,y) POWER(x,y) : x의 y 제곱 승

• GREATEST(x,y,...) : 가장 큰 값

• LEAST(x,y,...) : 가장 작은 값

• CURDATE(),CURRENT_DATE : 오늘 날짜를 YYYY-MM-DD나 YYYYMMDD 형식으로 반환합니다.

• CURTIME(), CURRENT_TIME : 현재 시각을 HH:MM:SS나 HHMMSS 형식으로 반환합니다.

• NOW(), SYSDATE() , CURRENT_TIMESTAMP : 오늘 현시각을 YYYY-MM-DD HH:MM:SS나 YYYYMMDDHHMMSS 형식으로 반환합니다.

• DATE_FORMAT(date,format) : 입력된 date를 format 형식으로 반환합니다.

• PERIOD_DIFF(p1,p2) : YYMM이나 YYYYMM으로 표기되는 p1과 p2의 차이 개월을 반환합니다.

---

CAST 형변환

CAST(expr AS type)

CONVERT(expr, type)

CONVERT(expr, USING transcoding_name)

그룹함수(Aggregatin Function)와 groupby 절

SELECT deptno, AVG(salary), SUM(salary)
FROM employee

SELECT deptno, AVG(salary), SUM(salary)
FROM employee
group by deptno; 

부서별 결과 확인 가능

---

INSERT

INSERT INTO 테이블명(필드1, 필드2, 필드3, ...)

VALUES(필드1의 값, 필드 2의 값, 필드 3의 값, ...)

필드명 생략가능 --> 단 VALUE의 값은 디폴트로 순서대로 모두입력.

desc role;
INSERT INTO role VALUES(200, CEO);
SELECT * FROM role;
+---------+-----------------+
| role_id | description     |
+---------+-----------------+
|     100 | Developer       |
|     101 | Researcher      |
|     102 | Project manager |
|     200 | CEO             |
+---------+-----------------+
4 rows in set (0.000 sec)

---

UPDATE

UPDATE 테이블명

SET 필드1=필드1의값, ... 필드3=필드3의 값, ...

WHERE 조건식

UPDATE role SET description = 'CTO' WHERE role_id = 200;
SELECT * FROM role;
+---------+-----------------+
| role_id | description     |
+---------+-----------------+
|     100 | Developer       |
|     101 | Researcher      |
|     102 | Project manager |
|     200 | CTO             |
+---------+-----------------+

---

DELETE

DELETE

FROM 테이블명

WEHRE 조건식

DELETE FROM role WHERE role_id = 200;
SELECT * FROM role;
+---------+-----------------+
| role_id | description     |
+---------+-----------------+
|     100 | Developer       |
|     101 | Researcher      |
|     102 | Project manager |
+---------+-----------------+

3-3) DDL(CREATE, DROP)

테이블 생성, 수정, 삭제를 배워봅니다.

데이터 정의어(Data Definition Language)

MySQL 데이터 타입

• DECIMAL(n, m): n은 전체 자릿수, m은 소숫점 자릿수

• NUMERIC(n, m): n은 전체 자릿수, m은 소숫점 자릿수

  위 둘은 동일

테이블 생성

CREATE TABLE 테이블명(
    필드명1 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT],
    필드명1 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT],
    필드명1 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT],
    ...
    PRIMARY KEY(필드명)
);

테이블 수정(칼럼 추가 / 삭제 / 변경 / 이름변경)

ALTER TABLE 테이블명
    ADD 필드명 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT];

ALTER TABLE 테이블명
    DROP 필드명;

ALTER TABLE 테이블명
    CHANGE 필드명 새필드명 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREMENT];

ALTER TABLE 테이블명
    RENAME 변경이름;

테이블 삭제

DROP TABLE 테이블이름;

두 테이블 간 foreign key 제약 조건(constraint)이 있을 경우, 삭제되지 않을 수 있습니다.

예를 들면, employee 테이블은 deparment 테이블을 참조하고 있는데,

deparment 테이블을 먼저 삭제(수정)할 수 없습니다.

따라서 employee를 먼저 삭제하고, deparment를 삭제해야 합니다.

참고

[ euc-kr ] ascii 코드 + 한글

영문/숫자/기호는 1byte. 한글과 한자는 2byte

[ utf-8 ]

영문/숫자/기호는 1byte로, 한글과 한자 등은 3byte로 표현

---

Reference

https://www.edwith.org/boostcourse-web/lecture/16722/