6. SQL이란? / DB생성 / 계정 생성 및 권한 부여 / Table 명령어 / Sample sql 데이터 활용

부스트코스 교육내용(링크)을 바탕으로

학습 자료를 추가하였습니다.

SQL이란?

---

한국사람과 미국사람이 대화하기 위해서는 한쪽이 이해할 수 있는 언어를 사용해야 하듯이 DBMS에게 명령을 내릴 때도 특별한 언어(SQL)가 필요

 

학습할 키워드

• create database
• grant / create all
• flush privileges ()
• select (선택)

 

SQL(Structured Query Language)?

• Structured : 정리정돈, 구조화됨 / Query : DB에게 데이터에 대한 처리를 요청하는 것
• SQL은 데이터를 보다 쉽게 검색하고 추가, 삭제, 수정 같은 조작을 할 수 있도록 고안된 컴퓨터 언어
• 관계형 데이터베이스에서 데이터를 조작하고 쿼리하는 표준 수단
• DML (Data Manipulation Language): 데이터 조작용
  예시 : INSERT, UPDATE, DELETE, SELECT 등
• DDL (Data Definition Language): 데이터베이스의 스키마 정의 및 조작
  CREATE, DROP, ALTER 등
• DCL (Data Control Language) : 데이터 제어 언어
  권한 관리, 테이터의 보안, 무결성 등을 정의
  GRANT, REVOKE 등 해당

 

Database 생성

---

콘솔에 입력 mysql –uroot -p

의미 : MySQL 관리자 계정인 root로 데이터베이스 관리 시스템에 접속함

 

혹시나 ERROR 2003 (HY000) 에러 발생시 참고링크

 

window 사용자 : 설치할 때 입력한 MySQL 암호 입력

 

Database 생성하기

관리자 계정으로 MySQL에 접속, 다음 명령으로 데이터베이스 생성

mysql> create database DB이름;

 

DB이름을 “connectdb＂로 생성

mysql> create database connectdb;

 

Database 삭제하기

mysql> drop database DB이름;

 

 

Database 사용자 생성과 권한 주기

---

• Database 생성했다면, 해당 DB를 사용하는 계정 생성 및 해당 계정이 DB 이용 권한 부여
• db이름 뒤의 * 는 모든 권한을 의미
• @’%’는 어떤 클라이언트에서든 접근 가능
• @’localhost’는 해당 컴퓨터에서만 접근 가능
• flush privileges는 DBMS에게 적용 하라는 의미 (이 명령은 필수적으로 실행)

그러나 MySQL 8.0버전의 경우 grant가 아닌 다른 명령어를 사용해야 생성된다.

출처 : MySQL, stackoverflow, linuxize

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

// MySQL8.0 버전
CREATE USER 'connectuser'@'%' IDENTIFIED BY 'connect123!@#';
GRANT ALL ON connectdb.* TO 'connectuser'@'%';
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 8.0
CREATE USER 'connectuser'@'%' IDENTIFIED BY 'connect123!@#';
GRANT ALL ON connectdb.* TO 'connectuser'@'%';
flush privileges;

 

사용자 삭제하기 (링크)

사용자 및 호스트 검색하기

mysql > Select User,Host FROM mysql.user;

mysql> Select User,Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| connectuser      | %         |
| connectuser      | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql > drop user 'User'@'Host';

mysql> drop user 'connectuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)

'을 꼭 사용해야한다. 특히 host가 %인 경우 '을 사용하지 않으면 Syntax Error 발생

mysql> drop user connectuser@%;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%' at line 1

 

 

생성한 Database에 접속하기

---

콘솔창에 입력할 것. 위에서 생성한 데이터베이스 접속

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

 

-p에는 비밀번호를 입력하는 곳인데 보안상 위처럼만 입력해주면 Enter password : 라고 나타나는데 여기에 비밀번호를 입력하면 된다.

 

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

> mysql –hlocalhost –uconnectuser –p connectdb

+. 강의에선 -hlocalhost가 아닌 -h127.0.0.1을 입력하라고 했으나 오류 ERROR 1045 (28000) 발생

+. localhost가 127.0.0.1이란 것도 C:\windows\System32\drivers\etc\hosts file을 통해 확인했음

 

 

MySQL 연결끊기

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

mysql> QUIT

mysql> exit

 

 

MySQL 버전과 현재 날짜 구하기

mysql> select version(), current_date;
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 8.0.16    | 2019-07-17   |
+-----------+--------------+
1 row in set (0.00 sec)

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

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

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

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

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

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

 

키워드는 대소문자 구별 X

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

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

쿼리 이용 계산식 활용 가능

 함수 및 수식 사용 예제

mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
|    0.707107 |      25 |
+-------------+---------+

여러 문장을 한 줄에 붙여서 실행가능 (각 문장 별로 ; 붙일 것)

mysql> select version(); select now();
+-----------+
| version() |
+-----------+
| 8.0.16    |
+-----------+
1 row in set (0.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2019-07-17 17:14:03 |
+---------------------+
1 row in set (0.00 sec)

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

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

mysql> select
    -> user(),
    -> current_date;
+----------------+--------------+
| user()         | current_date |
+----------------+--------------+
| root@localhost | 2019-07-17   |
+----------------+--------------+
1 row in set (0.00 sec)

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

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

mysql> SELECT

    -> USER()

    -> \c

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

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

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| connectdb          |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.01 sec)

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

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

mysql> use connectdb;
Database changed

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

 

< SQL TABLE command >

---

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

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

 

테이블(table)의 구성요소

생활코딩 https://opentutorials.org/course/3161/19536

column은 데이터 타입, 구조
row는 데이터 하나하나를 의미

 

테이블(table)의 구성요소

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

                    

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

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

mysql> show tables;
Empty set (0.00 sec)

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

 

SQL 연습을 위한 테이블 생성과 값의 저장

examples.sql을 다운로드 합니다. 링크 바로가기

터미널에서 examples.sql이 있는 폴더로 이동한 후, 다음과 같이 명령을 수행합니다.

명령을 수행한 후 암호를 입력합니다.

mysql -uconnectuser -p connectdb < examples.sql

 

examples.sql에는 연습을 위한 테이블 생성문과 해당 테이블에 값을 저장하는 입력문이 존재합니다.

mysql –uconnectuser -p connectdb

 

위의 명령으로 connectdb에 접속한 후 다음과 같이 명령을 수행합니다.

mysql> show tables

 

수행결과

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

 

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

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

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

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

mysql> desc EMPLOYEE;

mysql> desc employee
    -> ;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(11)      | NO   | PRI | NULL    |       |
| name     | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| boss     | int(11)      | YES  | MUL | NULL    |       |
| hiredate | varchar(12)  | YES  |     | NULL    |       |
| salary   | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(11)      | YES  | MUL | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

 

< DML(select, insert, update, delete) >

---

DML (Data Manipulation Language): 데이터 조작용 언어 > 
예시 : INSERT, UPDATE, DELETE, SELECT 등

 

< 키워드 > 

insert : 등록

select : 검색

update : 수정

delete : 삭제

 

select 구문의 기본문형

SELECT(DISTUNCT) 칼럼명(ALIAS)

FROM Table명;

SELECT	검색할 데이터(칼럼) 나열
DISTINCT	중복행 제거
ALIAS	나타날 컬럼에 대한 다른 이름 부여
FROM	선택한 칼럼이 있는 테이블 명시

 

SELECT 구문 사용하기

*는 모든 데이터를 찾을 때 사용한다

예시 : SELECT * FROM DEPARTMENT;

 

SELECT 구문 예제 (특정 칼럼 검색)

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

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

어떤 칼럼이 있는지는 desc명령으로 확인

mysql > select empno, name, job from employee;

SELECT 구문 예제(컬럼에 Alias부여하기)

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

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

mysql > select empno as 사번, name as 이름, job as 직업 from employee;

SELECT 구문 예제(컬럼의 합성(Concatenation))

• 문자열 결합함수 concat 사용

예제 : employee 테이블에서 사번과 부서번호를 하나의 칼럼으로 출력하시오.

mysql > select concat(empno, '-', deptno) AS '사번-부서번호'

        -> FROM employee;

SELECT 구문 예제(중복행의 제거)

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

예제1 : 사원 테이블의 모든 부서번호 출력하시오. (사원 수 만큼 출력된다.)

mysql > select deptno from employee;

 

예제2 : 사원 테이블의 부서번호를 중복되지 않게 출력하시오.

mysql > select distinct deptno from employee;

 

SELECT 구문 예제(정렬하기)

 

ORDER BY 절

select(distinct) 칼럼명(ALIAS)

FROM 테이블명

ORDER BY 칼럼이나 표현식 (ASC 또는 DESC);

 

ASC	오름차순 정렬, 기본값
DESC	내림차순

SELECT 구문 예제(정렬하기)

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

단, 이름을 기준으로 오름차순 정렬합니다.

mysql > select empno, name, job from employee order by name;

mysql > select empno as 사번, name as 이름, job as 직업 from employee order by 이름;