SQL

SQL의 개념에 대해 간단히 설명하고, MySQL을 이용하여 DB를 다루는법을 공부한다.

– SQL이란?

SQL(Structured Query Lanaguage)은 데이터를 쉽게 검색하고 조작(추가, 삭제, 수정 등) 할 수 있도록 고안된 컴퓨터 언어이다. SQL은 아래 3가지 종류로 분류된다.

SQL의 분류

DML (Data Manipulation Language): 데이터 조작 ex) INSERT, UPDATE, DELETE, SELECT
DDL (Data Definition Lanaguage): 데이터베이스의 스키마 정의 또는 조작. ex) CREATE, DROP, ALTER
DCL (Data Control Lanaguage): 데이터 제어. 권한 관리, 데이터 보안, 무결성 등을 정의. ex) GRANT, REVOKE

– MySQL 실습 (Mac기준)

DB 생성하기

터미널에서 아래 커맨드를 입력하여 mysql에 접속한다. root 계정으로 mysql에 접속

mysql -uroot

새로운 DB 생성. DB의 이름은 connectdb

create database connectdb

사용자 생성 및 권한 주기

사용자 계정 생성 – 계정 이름은 ‘connectuser’, 해당 계정은 어떤 클라이언트에서든 접근이 가능하며, 암호는 ‘connect123!@#’

create user connectionuser@'%' identified by 'connect123!@#';

@’%’ : 어떤 클라이언트에서든 접근이 가능
@localhost: 해당 컴퓨터에서만 접근이 가능

계정을 생성했으면 어떤 DB에 접근할 수 있는지 권한을 지정해주어야 한다.
계정 connectionuser는 데이터베이스 ‘connectdb’에 모든 권한을 가지도록 설정

grant all privileges on connectdb.* to connectionuser@'%';

db 이름.* : 모든 권한을 의미

권한 변경 후에는 DBMS에 변경된 권한을 적용해야한다. DBMS 적용.

flush privileges;

커맨드만 모아보면 아래와 같다.

create user connectionuser@'%' identified by 'connect123!@#';
grant all privileges on connectdb.* to connectionuser@'%';
flush privileges;

원래는 계정 생성 및 권한 부여를 하나의 커맨드에서 동시에 할 수 있었는데, MySQL 8부터 불가능하다고 한다.

https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0

강의 예제에 동시에 하는 커맨드로 나와있어서 입력했더니 에러가 났다.

grant all on connectdb.* to connectionuser@'%' identified by 'connect123!@#';

DB에 접속하기

새로 생성한 계정으로 DB에 접속해본다. 위에서 이미 root 계정으로 접속했기 때문에, 새로운 계정으로 접속하려면 mysql 연결을 종료해주어야 한다. quit 또는 exit 커맨드를 입력하면 접속을 종료한다.

quit;
exit;

다음 커맨드로 connectionuser 계정으로 connectdb 데이터베이스에 접속한다.

mysql -h localhost -u connectionuser -p connectdb;

mysql -h (호스트명) – u (DB계정명) – p (데이터베이스 이름) 구조이다.
-p 후에 비밀번호를 입력하지 않기 때문에, 그 다음 라인에서 비밀번호를 물어본다. 원하면 -p 다음에 비밀번호를 입력해서 접속해도 된다.

정상적으로 연결되었다면 Welcome to the MySQL monitor라고 출력된다.

접속이 잘 안되거나 syntax 에러가 발생한다면 자신의 mysql 버전에 맞는 reference manual을 참조하는것이 좋다. MySQL 8.0 refernece manual

설치된 MySQL 버전을 확인하는 방법은 아래 내용을 참조.

간단한 쿼리 연습 – MySQL 버전과 오늘 날짜 출력

아래 커맨드를 입력하면 설치된 MySQL 버전과 오늘 날짜가 출력된다. 버전만 보고싶으면 select version(); 만 입력하면 된다.

select version(), current_date;

MySQL 쿼리의 특징

키워드는 대소문자를 구분하지 않는다.

쿼리를 이용해서 계산도 할 수 있다

여러 문장을 한 줄에 연속으로 입력이 가능하다. 문장을 세미콜론(;)으로 구분만 해주면 된다.

한 줄에 입력할 필요는 없다.

SQL을 입력하는 도중에 취소할 수 있다. \c를 입력해주면 된다.

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

show databases;

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

use db명;

접속되어있는 계정이 해당 DB에 접근 권한이 있어야 한다.

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

DBMS의 종류에는 여러 종류(관계형, 계층형, 네트워크형 등)가 있는데, 현 세대에서는 관계형 DBMS가 주류를 이루고 있다. 지금 다루고있는 MySQL도 관계형 DBMS이며, Oracle, MariaDB, SQLServer 등도 모두 관계형이다. 관계형 DBMS는 테이블 구조로 데이터를 다룬다. 데이터 간의 관계를 테이블 형태로 구성하는 것이다.

테이블의 구성 요소는 다음과 같다.

table(테이블): RDBMS(관계형 DBMS)의 기본적 저장구조. 한 개 이상의 column과 0개 이상의 row로 구성된다.
column(열): 테이블 상에서의 단일 종류를 나타냄. 특정 데이터 타입 및 크기를 가지고 있다.
row(행): column 들의 조합. 레코드라고 불림. 기본키에 의해서 구분되며, 기본 키는 중복값을 허용하지 않는다.
field(필드): row와 column의 교차점. 데이터를 포함할 수 있고, 포함할 수 없을 때는 null 값을 가진다. –> 설명은 이렇게 써있는데, 실제로는 column을 칭하는 의미로 많이 사용되는것 같다.

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

database에 연결한 후(ex. connectdb에 연결) 아래 커맨드를 입력하면 해당 database에 존재하는 테이블의 목록을 확인할 수 있다.

show tables;

테이블을 생성하지 않았어서 empty set이라고 출력이 되었다.

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

example.sql을 다운로드 한다. 터미널에서 examples.sql이 있는 폴더로 이동한 후, 아래와 같이 커맨드를 입력한다. 입력 후 암호를 입력한다.

mysql - connectionuser -p connectdb < examples.sql

connectionuser의 계정으로 connectdb에 examples.sql의 테이블을 생성한다.

그 후 show tables 명령어로 존재하는 table을 확인하면 아래와 같이 나온다.

테이블 구조 확인 (describe 명령어)

desc 명령어로 테이블의 구조를 확인할 수 있다. desc는 describe의 축약어로, describe라고 써도 같게 동작한다.

bonus 테이블의 구조를 확인

desc bonus;

[Spring을 이용한 웹 백엔드 기초] 1. SQL & JDBC 프로그래밍 – 2) SQL

SQL