웹 백엔드 (1) - SQL

SQL은 데이터를 보다 쉽게 검색하고 추가, 삭제, 수정 같은 조작을 할 수 있도록 고안된 컴퓨터 언어입니다.

관계형 데이터베이스에서 데이터를 조작하고 쿼리(요청)하는 표준 수단입니다.

데이터베이스를 관리하는 소프트웨어(DBMS)에 내리는 명령이라고 보시면 되겠습니다.

 

SQL은 크게 DML(Data Manipulation Language), DDL(Data Definition Language), DCL(Data Control Language)로 나뉘어집니다.

 

DML은 조작하기 위해 사용되는, 입력하거나 삭제하거나 선택하는 등의 기능을 담당합니다. INSERT, UPDATE, DELETE, SELECT 등이 해당됩니다.

 

DDL은 데이터베이스의 스키마를 정의하거나 조작하기 위해 사용됩니다. 데이터베이스 스키마란 데이터베이스의 구조와 제약조건에 관한 내용을 형식 언어로 서술한 것으로, 속성과 속성, 개체와 개체 사이에 존재하는 관계에 대한 정보를 기술한 것입니다. CREATE, DROP, ALTER등이 DDL에 해당됩니다. 

 

DCL은 권한을 주거나 파기하거나 트랜젝션을 부여하거나 하는 기능을 수행합니다. 데이터의 보안이나 무결성을 유지하게 하는 역할을 합니다. GRANT, REVOKE 등이 해당됩니다.

 

mysql에서 데이터베이스를 생성해보겠습니다. 생성 명령은 create를 사용해 쉽게 표현 가능합니다.

create database DB이름

데이터베이스를 생성했다면, 이 데이터베이스를 사용하는 계정을 생성해야 하고, 해당 계정에 데이터베이스의 접근 권한도 주어야 합니다.

데이터베이스 이용 권한을 주는 명령은 DCL을 사용하여 아래와 같이 표현합니다.

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

 

@'%'는 어떤 클라이언트에서든 접근 가능하다는 의미이고, @'localhost'는 해당 컴퓨터에서만 접근 가능하다는 의미입니다.

flush privileges는 입력된 명령을 DBMS에 적용하라는 의미입니다. 이 명령을 실행해주어야 사용자에게 권한이 주어집니다.

 

mysql에서 생성된 사용자는 아래와 같이 확인할 수 있습니다.

 

use mysql;
select user, host from user;

 

MySQL 연결을 끊고 싶을 때는 한 단어만 입력하시면 됩니다.

mysql>quit

또는

mysql>exit

 

SQL에선 키워드의 대소문자를 구분하지 않습니다. 단, ' '나 " " 안에 들어가는 값에는 대소문자를 구분하여 입력해야 합니다.

SQL문은 여러 문장을 한 줄에 연속으로 붙여서 실행할 수 있습니다. 하나의 SQL을 여러 줄로도 입력이 가능합니다. 명령이 끝날 때에 세미콜론(;)이 붙는다는 것만 잘 기억하면 원하는 부분만큼 나누거나 합쳐서 사용하실 수 있습니다.

 

이제 MySQL에서 자주 쓰는 명령어를 정리하겠습니다.

사용중인 데이터베이스를 전환하는 방법입니다.

use db명;

단, 해당 이름의 데이터베이스가 존재하여야 하고, 현재 접속중인 계정이 이 데이터베이스에 접근할 권한을 가지고 있어야 합니다.

database changed라는 메시지가 출력된다면 데이터베이스가 정상적으로 변경된 것입니다.

 

해당 DB의 모든 테이블을 보여주는 명령어입니다.

show tables;

테이블 구조 확인

desc 테이블명

컬럼명, 타입, Null여부, 키 등 출력

 

테이블 출력 시 지정한 이름으로 컬럼명1,2 출력

select 컬럼명1 as 지정한 이름, 컬럼명2 as 지정한 이름 from 테이블명;

ex) select deptno as 부서번호, name as 부서명 from department;

그런데 콤마 없이 나올 수 있는 것은 Alias(지정할 이름)나 from밖에 없기 때문에 as를 쓰지 않아도 무방

select 컬럼명1 지정한 이름, 컬럼명2 지정한 이름 from 테이블명;

ex) select deptno 부서번호, name 부서명 from department;

단, Alias에 공백이 있을 때는 작은 따옴표로 감싸주어야 함

 

문자열 결함함수 concat

concat(컬럼1, '사이에 들어갈 문자', 컬럼2)

ex) select concat(empno, '-', deptno) as '사번-부서번호';

 

중복 없이 출력

select distinct 컬럼명 from 테이블명;

 

컬럼명1을 기준으로 오름차순으로 정렬하기

select distinct 컬럼명1, 컬럼명2 from 테이블명 order by 컬럼명1;

또는

select distinct 컬럼명1, 컬럼명2 from 테이블명 order by 컬럼명1 asc;

 

컬럼명1을 기준으로 내름차순으로 정렬하기

select distinct 컬럼명1, 컬럼명2 from 테이블명 order by 컬럼명1 desc;

 

어떤 컬럼에서 특정 값을 갖는 행만 출력

select * from 테이블명 where 컬럼명 = 값;

select * from 테이블명 where 컬럼명 in (값1, 값2);

 

어떤 컬럼이 특정 단어를 포함하는 값을 가진 행 출력

select * from 테이블명 where 컬럼명 like '시작할 단어%'

select * from 테이블명 where 컬럼명 like '%끝낼 단어'

select * from 테이블명 where 컬럼명 like '%포함할 단어%'

 

두번째 글자부터 특정 단어를 포함하는 행 출력

select * from 테이블명 where 컬럼명 like '_포함할 단어%'

 

문자를 끊어내기

substring('문자열', 시작 인덱스, 글자수)

MySQL은 가장 앞의 글자가 1번 인덱스와 매칭됩니다. 

 

데이터를 입력하는 방법입니다.

insert into 테이블명(필드1, 필드2, 필드3, 필드4, ...)

     values (필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, ...)

  

insert into 테이블명

     values (필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, ...)

필드명은 생략이 가능하지만 생략했을 때는 반드시 모든 필드의 값을 채워주셔야 합니다.

생략하지 않고 원하는 컬럼에만 값을 입력하더라도, Primary Key에 들어갈 값은 반드시  채워져야 합니다.