1) SQL이란?

SQL(Structured Query Language)

SQL은 데이터를 보다 쉽게 검색하고 추가, 삭제, 수정 같은 조작을 할 수 있도록 고안된 컴퓨터 언어입니다.
관계형 데이터베이스에서 데이터를 조작하고 쿼리하는 표준 수단입니다.
DML (Data Manipulation Language): 데이터를 조작하기 위해 사용합니다.
INSERT, UPDATE, DELETE, SELECT 등이 여기에 해당합니다.
DDL (Data Definition Language): 데이터베이스의 스키마를 정의하거나 조작하기 위해 사용합니다.
CREATE, DROP, ALTER 등이 여기에 해당합니다.
DCL (Data Control Language) : 데이터를 제어하는 언어입니다.
권한을 관리하고, 데이터의 보안, 무결성 등을 정의합니다.
GRANT, REVOKE 등이 여기에 해당합니다.

Database 생성하기

콘솔에서 다음과 같이 명령을 실행합니다.

MySQL 관리자 계정인 root로 데이터베이스 관리 시스템에 접속하겠다는 것입니다.

mysql –uroot  -p

window 사용자는 설치 시에 입력했던 암호를 입력합니다.

MySQL DBMS에 접속하면 “mysql>” 프롬프트가 보입니다.

관리자 계정으로 MySQL에 접속했다면, 다음과 같은 명령으로 데이터베이스를 생성합니다.

mysql> create database DB이름;
mysql> create database connectdb;

Database 사용자 생성과 권한 주기

Database를 생성했다면, 해당 데이터베이스를 사용하는 계정을 생성해야 합니다.
또한, 해당 계정이 데이터베이스를 이용할 수 있는 권한을 줘야 합니다.
아래와 같은 명령을 이용해서 사용자 생성과 권한을 줄 수 있습니다.
db이름 뒤의 * 는 모든 권한을 의미합니다.
@’%’는 어떤 클라이언트에서는 접근 가능하다는 의미이고, @’localhost’는 해당 컴퓨터에서만 접근 가능하다는 의미입니다.
flush privileges는 DBMS에게 적용을 하라는 의미입니다.
해당 명령을 반드시 실행해줘야 합니다.

grant all privileges on db이름.* to 계정이름@'%' identified by ＇암호’;
grant all privileges on db이름.* to 계정이름@'localhost' identified by ＇암호’;
flush privileges;

사용자 계정이름은 'connectuser', 암호는 'connect123!@#', 해당 사용자가 사용하는 데이터베이스는 'connectdb'로 계정을 생성하려면 다음과 같이 명령을 수행합니다.

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

강의에서는 mysql 5 버전으로 설명하는데

mysql 8버전에서는 create user를 먼저 해주고 grant를 해야 한다. ㅡ error log

방법 1

CREATE user connectuser @'%' identified by 'connect123!@#';
GRANT ALL privileges on connectdb.* to connectuser @'%';

방법 2

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'username'@'localhost';
flush privileges;

생성한 Database에 접속하기

아래와 같이 명령을 실행하여 원하는 데이터베이스에 접속할 수 있습니다.

mysql –h호스트명 –uDB계정명 –p 데이터베이스이름

db이름이 connectdb, db계정이 connectuser, 암호가 connect123!@# 일 경우 콘솔창에서 다음과 같이 입력합니다.

mysql –h127.0.0.1 –uconnectuser –p connectdb

MySQL 연결끊기

프롬프트에서 quit혹은 exit라고 입력합니다.

mysql> QUIT
mysql> exit

Bye라고 나오면 연결 끊기에 성공한 것입니다.

MySQL 버전과 현재 날짜 구하기

mysql> SELECT VERSION(), CURRENT_DATE;

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

SQL은 semicolon (;)으로 끝납니다.

SQL은 쿼리(Query)라고 읽습니다.

쿼리는 DBMS에게 명령을 내릴 때 사용하는 문장이라고 생각하면 쉽습니다.

SELECT는 어떤 내용을 조회할 때 사용하는 키워드입니다.

MySQL은 쿼리에 해당하는 결과의 전체 row를 출력하고 마지막에 전체 row 수와 쿼리실행에 걸린 시간을 표시합니다.

키워드는 대소문자를 구별하지 않습니다.

다음 쿼리들은 모두 같습니다.

mysql> SELECT VERSION(), CURRENT\_DATE;
mysql> select version(), current\_date;
mysql> SeLeCt vErSiOn(), current\_DATE;

쿼리를 이용해서 계산식의 결과도 구할 수 있습니다.

함수 및 수식 사용 예제

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

여러 문장을 한 줄에 연속으로 붙여서 실행이 가능합니다.

각 문장에 semicolon(;)만 붙여주면 됩니다.

mysql> SELECT VERSION(); SELECT NOW();

하나의 SQL은 여러 줄로 입력가능하다.

MySQL은 문장의 끝을 라인으로 구분하는 것이 아니라 semicolon(;)으로 구분하기 때문에 여러 줄에 거쳐 문장을 쓰는 것도 가능합니다.

mysql> SELECT
   -> USER()
   -> ,
   -> CURRENT\_DATE;

SQL을 입력하는 도중에 취소할 수 있다.

긴 쿼리를 작성하다가 중간에 취소해야 하는 경우에는 즉시 \c를 붙여주면 됩니다.

mysql> SELECT
   -> USER()
   -> \c
mysql>

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

작업하기 위한 데이터베이스를 선택하기 위해서는 어떤 데이터베이스가 존재하는지 알아보아야 합니다.

현재 서버에 존재하는 데이터베이스를 찾아보기 위해서 SHOW statement을 사용합니다.

mysql> show databases;

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

Database을 선택하기 위해, “use” command 사용합니다.

mysql> use mydb;

데이터베이스를 전환하려면, 이미 데이터베이스가 존재해야 하며 현재 접속 중인 계정이 해당 데이터베이스를 사용할 수 있는 권한이 있어야 합니다.

데이터를 저장하는 공간 테이블(Table)

마이크로소프트의 엑셀(Excel)을 실행하면 표가 나옵니다. 이러한 표에 각종 값을 저장할 수 있습니다.
데이터베이스도 엑셀의 표와 유사한 테이블을 가질 수 있습니다.
엑셀과 다른 점은 데이터베이스를 생성해도 테이블은 존재하지 않는다는 것입니다.
테이블을 사용하려면 테이블을 생성하는 SQL을 사용해야 합니다.
그리고, 테이블에 값을 저장하려면 저장하기 위한 SQL을 사용해야 합니다.

테이블(table)의 구성요소

테이블 : RDBMS의 기본적 저장구조 한 개 이상의 column과 0개 이상의 row로 구성합니다.
열(Column) : 테이블 상에서의 단일 종류의 데이터를 나타냄. 특정 데이터 타입 및 크기를 가지고 있습니다.
행(Row) : Column들의 값의 조합. 레코드라고 불림. 기본키(PK)에 의해 구분. 기본키는 중복을 허용하지 않으며 없어서는 안 됩니다.
Field : Row와 Column의 교차점으로 Field는 데이터를 포함할 수 있고 없을 때는 NULL 값을 가지고 있습니다.

현재 데이터베이스에 존재하는 테이블 목록 확인하기

Database를 선택 후, Database의 전체 테이블 목록을 출력합니다.

mysql> show tables;
Empty set (0.02 sec)

“empty set” 은 데이터베이스에 어떤 테이블도 아직 생성되지 않았다는 것을 알려줍니다.

테이블 구조를 확인하기 위한 DESCRIBE 명령

table 구조를 확인하기 위해, DESCRIBE 명령을 사용할 수 있습니다.

짧게 DESC라고 사용해도 됩니다.

EMPLOYEE테이블의 구조를 확인해 봅시다.

**mysql> desc EMPLOYEE;

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

데이터 조작어(Data Manipulation Language, DML)의 종류

데이터 조작어는 모두 동사로 시작합니다.

시작하는 동사에 따라서 다음과 같은 4가지 조작어가 있습니다.

SELECT – 검색
INSERT - 등록
UPDATE - 수정
DELETE - 삭제

SELECT 구문의 기본문형

전체 데이터 검색

SELECT 뒤에 * 를 기술함으로써 나타낼 수 있다.

예제 : departments 테이블의 모든 데이터를 출력하시오.
```
SELECT * FROM  DEPARTMENT;
```

특정 컬럼 검색

SELECT 뒤에 컬럼을 콤마(,)로 구별해서 나열

예제 : employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력하시오.
```
select empno, name, job from employee;
```

(어떤 컬럼이 있는지는 desc명령으로 확인할 수 있다)

컬럼에 Alias부여하기

컬럼에 대한 ALIAS(별칭)을 부여해서 나타내는 칼럼의 HEADING을 변경할 수 있다.

예제 : employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력하시오.

select empno as 사번, name as 이름, job as 직업 from employee;
select empno 사번, name 이름, job 직업 from employee; (공백 가능)

컬럼의 합성(Concatenation)

문자열 결합함수 concat 사용

예제 : employee 테이블에서 사번과 부서번호를 하나의 칼럼으로 출력하시오.
```
SELECT concat( empno, '-', deptno) AS '사번-부서번호' FROM employee;
```

중복행의 제거

중복되는 행이 출력되는 경우, DISTINCT 키워드로 중복행을 제거

예제1 : 사원 테이블의 모든 부서번호 출력하시오. (사원 수만큼 출력된다.)
```
select deptno from employee;
```
예제2 : 사원 테이블의 부서번호를 중복되지 않게 출력하시오.
```
select distinct deptno from employee;
```

정렬하기

예제 : employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력하시오. 단, 이름을 기준으로 오름차순 정렬합니다.

1 alias를 사용하지 않았을 경우
select empno, name, job from employee order by name;
select empno, name, job from employee order by 2; (2번째 col 이란 소리)

2 alias를 사용했을 경우
select empno as 사번, name as 이름, job as 직업 from employee order by 이름;

예제 : employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력하시오. 단, 이름을 기준으로 내림차순 정렬합니다.
```
select empno, name, job from employee order by name desc;
```

특정 행 검색- where절

산술비교 연산자

예제 : employee 테이블에서 고용일(hiredate)이 1981년 이전의 사원이름과 고용일을 출력하시오.
```
select name, hiredate from employee where hiredate < '1981-01-01';
```
논리연산자

예제 : employee 테이블에서 부서번호가 30인 사원이름과 부서번호를 출력하시오.
```
select name, deptno from employee where deptno = 30;
```
IN 키워드

예제 : employee 테이블에서 부서번호가 10또는 30인 사원이름과 부서번호를 출력하시오.
```
select name, deptno from employee where deptno in (10, 30);
select * from employee where deptno = 10 or deptno = 30;
```
LIKE 키워드

와일드 카드를 사용하여 특정 문자를 포함한 값에 대한 조건을 처리

%: 0에서부터 여러 개의 문자열을 나타냄

_ : 단 하나의 문자를 나타내는 와일드 카드

예제 : employee 테이블에서 이름에 'A'가 포함된 사원의 이름(name)과 직업(job)을 출력하시오.
```
select name, job from employee where name like '%A%';
```
```
’A%’   A로 시작한 사람들
’%A’   A로 끝나는 사람들
‘_A%’ 반드시 2번째 A가 오는 사람들
```

함수의 사용

UCASE, UPPER (from 다음에 테이블이 없을 경우에는 테이블에서 조회하는 것이 아닙니다.)
LCASE. LOWER
- ```
 SELECT lower(name) FROM employee;
```
substring

LPAD, RPAD

select LPAD (name, 10, ‘+’) from employee;

TRIM, LTRIM, RTRIM
ABS(x) : x의 절대값을 구합니다.
MOD(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의 차이 개월을 반환합니다.

참고사항: mysql은 시작 idx가 1d이다.

CAST 형변환

cast(expression as type)
convert(____, ____)
convert(____ USING transcoding_name)

예제

mysql> select cast(now() as date);
+---------------------+
| cast(now() as date) |
+---------------------+
| 2003-09-25          |
+---------------------+
1 row in set (0.00 sec)

mysql> select cast(1-2 as unsigned);
+----------------------------+
|   cast(1-2 as unsigned)    |
+----------------------------+
|  18446744073709551615      |
+----------------------------+

그룹함수

ex) count(boss) count(comm)

예제 : employee 테이블에서 부서번호가 30인 직원의 급여 평균과 총합계를 출력하시오.
```
SELECT AVG(salary) , SUM(salary) FROM employee WHERE deptno = 30;
```

그룹함수와 group by 절

그룹화하지 않아도 그룹해주기에 그룹 함수의 경우 주의가 필요하다.

예제 : employee 테이블에서 부서별 직원의 부서번호, 급여 평균과 총합계를 출력하시오.
```
SELECT deptno, AVG(salary) , SUM(salary) FROM employee group by deptno;
```

select문과 join

select emp_name as  사원명 dept_name as 부서명 
where employee as A 
join department as B 
where a.dept_no =b.dept_no;

데이터 입력 (INSERT문)

INSERT INTO 테이블명(필드1, 필드2, 필드3, 필드4, … ) 
    VALUES ( 필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, … );
INSERT INTO 테이블명
    VALUES ( 필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, … );
INSERT INTO employee (empno, name, deptno) VALUES (1111, ‘kang’, 100);

필드명을 지정해주는 방식은 디폴트 값이 세팅되는 필드는 생력할 수 있습니다.

필드명을 지정해주는 방식은 추 후, 필드가 추가/변경/수정 되는 변경에 유연하게 대처 가능합니다.

필드명을 생략했을 경우에는 모든 필드 값을 반드시 입력해야 합니다.

null이 불가능한 조건 (not-null or primary)의 컬럼은 반드시 나열하여 값을 지정해야 한다. (오류발생)

예제 : ROLE테이블에 role_id는 200, description에는 'CEO'로 한건의 데이터를 저장하시오.

insert into ROLE (role_id, description) values ( 200, 'CEO');
or
insert into ROLE values (200, 'CEO'); // col이 2개라서 가능하다

데이터 수정(UPDATE문)

UPDATE 테이블명
SET 필드1=필드1의값, 필드2=필드2의값, 필드3=필드3의값, …
WHERE 조건식

조건식을 통해 특정 row만 변경할 수 있습니다.

조건식(where절)을 주지 않으면 전체 로우가 영향을 미치니 조심해서 사용하도록 합니다.

예제 : ROLE테이블에 role_id가 200일 경우 description을 'CTO'로 수정하시오.
```
UPDATE ROLE SET description = 'CTO' WHERE role_id = 200;
```

데이터 삭제(DELETE문)

DELETE FROM 테이블명 WHERE 조건식

조건식을 통해 특정 row만 삭제할 수 있습니다.

조건식(where절)을 주지 않으면 전체가 다 지워질 수 있으니 조심해서 사용하도록 합니다.

예제 : ROLE테이블에서 role_id는 200인 정보를 삭제하시오.
```
DELETE FROM ROLE WHERE role_id = 200;
```

3) DDL(create, drop)

MySQL 데이터 타입

테이블 생성하기

create table 테이블명(
    필드명1 타입 [NULL | NOT NULL][DEFAULT ][AUTO_INCREMENT],
    필드명2 타입 [NULL | NOT NULL][DEFAULT ][AUTO_INCREMENT],
    필드명3 타입 [NULL | NOT NULL][DEFAULT ][AUTO_INCREMENT],    
    ...........
    PRIMARY KEY(필드명)
);

데이터 형 외에도 속성값의 빈 값 허용 여부는 NULL 또는 NOT NULL로 설정

DEFAULT 키워드와 함께 입력하지 않았을 때의 초기값을 지정

입력하지 않고 자동으로 1씩 증가하는 번호를 위한 AUTO_INCREMENT이 있다.

실습: EMPLOYEE와 같은 구조를 가진 EMPLOYEE2 테이블을 생성하시오.

CREATE TABLE EMPLOYEE2(
empno INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(10),
job VARCHAR(9),
boss INTEGER,
hiredate VARCHAR(12),
salary DECIMAL(7, 2),
comm DECIMAL(7, 2),
deptno INTEGER);

테이블 수정 (컬럼 추가 / 삭제)

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

ALTER TABLE 테이블명
    DROP 필드명;

실습_컬럼추가 – EMPLOYEE2 테이블에 생일(birthdate)칼럼을 varchar(12)형식으로 추가하시오.
```
alter table EMPLOYEE2
add birthdate varchar(12);
```
실습_컬럼삭제 – EMPLOYEE2 테이블의 생일(birthdate)칼럼을 삭제하시오.
```
alter table EMPLOYEE2
drop birthdate;
```

테이블 수정 (컬럼 수정)

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

change 키워드를 사용하고 칼럼을 새롭게 재정의 (이름부터 속성까지 전부)

실습 – EMPLOYEE2 테이블의 부서번호(deptno)를 dept_no로 수정하시오.
```
alter table EMPLOYEE2
change deptno dept_no int(11);
```

테이블 이름 변경

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

실습 – EMPLOYEE2 테이블의 이름을 EMPLOYEE3로 변경하시오.
```
alter table EMPLOYEE2
rename EMPLOYEE3;
```

테이블 삭제하기

DROP TABLE 테이블이름;

참고로, 제약 조건이 있을 경우에는 drop table 명령으로도 테이블이 삭제되지 않을 수 있습니다.

그럴 경우는 테이블을 생성한 반대 순서로 삭제를 해야합니다.

* 테이블 삭제 후 desc 명령을 수행하면, 존재하지 않는 테이블이라고 표시됩니다.

실습 – EMPLOYEE2 테이블을 삭제하시오.
```
drop table EMPLOYEE2;
```

'Spring > Boost Course Web' 카테고리의 다른 글

2.2 웹 개발환경 설정 (0)	2020.11.19
2.1 Web개발의 이해 (0)	2020.11.19
1.5 JDBC (0)	2020.11.19
1.4 Maven (0)	2020.11.19
1.3 개발환경 설정 (0)	2020.11.09
1.1 MySQL (0)	2020.11.09

1.2 SQL