Linux/CentOS
[CentOS 7] MariaDB 데이터베이스 서버 " 1 "
`작은거인`
2017. 8. 11. 19:23
리눅스 배포판에서 MySQL을 대채해 기본 데이터베이스 서버로 채택돼 사용되고 있는 MariaDB 서버에 대해 알아보자.
MariaDB 서버는 현재 CentOS를 비롯해서 REHL, Fedora, OpenSUSE, Arch Linux 등에서 데이터베이스 서버로 선택돼 사용되고 있다. MariaDB의 모태인 MySQL은 1995년에 처음 개발됐는데, 2008년 썬마이크로시스템즈가 이를 인수했고 다시 2010년 오라클사가 이를 인수했다. 이러한 과정에서 많은 MySQL 개발자들이 오라클을 떠나서 새로운 데이터베이스 프로젝트를 시작했는데, 그결과 탄생한 것이 바로 MariaDB 데이터베이스다. 여기서 Maria는 개발자의 둘째딸 이름에서, 그리고 MySQL의 My는 그의 첫째 딸의 이름에서 유래한 것이다.크게 두 가지의 버전 번호를 사용하는데, 5.x는 MySQL의 버전과 동일한 체계를 사용했고, 5.5버전 이후부터 10.x이라는 MySQL과 다른 버전 번호를 사용하고 있다. 처음 5.x 버전에서는 단지 MySQL의 대체 프로그램으로서 그 호환성에 초점을 뒀으나 10.x 이후 버전부터 독자적인 여러 다양한 기능들을 추가하고 있다.
1. 데이터베이스 기본 이해
1.1 데이터베이스 구성 요소
a. 데이터베이스
데이터베이스란 관련 있는 데이터들이 조직화돼 저장된 집합으로서 쉽게 접근, 관리, 업데이트 될 수 있어야 한다.
데이터베이스는 이러한 관련이 있는 데이터들이 저장되고 다양한 동작이 수행되는 실제 장소(디렉토리)를 의미한다.
b. DBMS
DBMS(Database Management System)는 데이터베이스를 생성, 정의, 조작하기 위해 사용되는 S/W이다.
실제 DBMS는 데이터베이스 내에 저장된 데이터에 대해 다양한 동작을 수행하기 위해 사용된는 Tool로 정의한다.
관리자는 DBMS를 사용해서 데이터베이스를 안전하게 보호할 수 있다.
가장 널리 사용되는 DBMS는 MySQL, Oracle, 마이크로소프트 SQL, IBM DB2등이 있다.
c. 구성 요소
- 사용자 : 사용자는 데이터베이스 내의 데이터를 사용하기 위해 직접 데이터베이스에 접근할 수 없고, DBMS를 통해 데이터를 사용할 수 있다. (데이터베이스 관리자, 시스템 개발자, 또는 최종 사용자)
- 데이터베이스 애플리케이션 : 데이터베이스 내의 정보를 검색하거나 상요하기 위해 DBMS를 사용하거나 사용자로부터 데이터베이스 사용 요청을 받아 DBMS에 전달하는 프로그램을 의미한다. (웹 서버가 대표적인 데이터베이스를 사용하는 어플리케이션)
- DBMS : 사용자가 데이터베이스를 생성하고 관리하고 접근할 수 있게 허용하는 소프트웨어 (MariaDB 서버도 여기에 해당)
- 데이터베이스 : 논리적인 데이터들의 집합을 의미하며, 일반적으로 디렉토리 내의 파일로 존재한다.
1.2 테이블 구성 요소
a. 테이블
Table은 중요한 구성 요소로서 하나 또는 그 이상의 칼럼을 가진 로우의 집합을 의미한다. 칼럼과 로우는 테이블을 구성하는 요소로서 로우는 테이블에서 삽입되거나 삭제되는 가장 작은 단위의 데이터이며, 칼럼이란 이러한 로우가 저장된 필드를 의미한다. 테이블은 칼럼과 로우로 구성돼 특정 유형의 데이터를 저장하는 파일로 존재한다.
테이블은 데이터베이스 내에 유일한 이름을 가지며, 같은 이름을 가진 테이블을 사용할 수 없다.
b. 테이블 구성 요소
칼럼 : 테이블은 여러 칼럼(Column)으로 구성되며, 테이블 내의 한 필드를 칼럼이라고 한다. 테이블에서 세로로 정보를 표시하며 각각의 칼럼은 그 이름을 가지고 있는데, 같은 테이블에서 동일한 이름을 사용할 수 없다. 칼럼을 생성할 때 각 칼럼은 숫자를 위해 int, 문자를 위해 char와 같이 데이터 타입을 가지며, 이것은 각 칼럼에 저장할 데이터의 종류를 정의하기 위해 사용한다.
로우 : 테이블 내의 데이터는 각각 가로로 로우(Row)에 저장되는데, 이를 레코드라고도 한다. 각 테이블에서 모든 칼럼은 하나 이상의 로우를 생성하며, 각 로우는 각 칼럼이 갖는 정보를 포함하고 있다.
키 필드 : 각 테이블은 하나 또는 그 이상의 테이블과의 연결을 위해 키 필드를 사용한다. 모든 로우는 칼럼을 이 키 필드로 사용하는데, 한 테이블 내에서 유일하게 식별하기 위한 용도로 사용한다. 한 테이블 내에서 일반적으로 로우 구별자로서 한 개의 칼럼을 사용하는 기본 키(Primary Key)와 다른 테이블의 기본 키 값이 포함된 칼럼을 사용하는 외래 키(Foreign Key)가 키 필드에서 사용된다.
2. MariaDB 서비스 시작
2.1 MariaDB 설치와 서비스 시작
a. MariaDB 서버 설치
yum을 이용해 두 개의 패키지를 설치하면 MariaDB 패키지 설치를 모두 마칠 수 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | # MariaDB 서버를 설치하기 위해 yum 저장소 파일을 생성한다. [root@master ~]# cat /etc/yum.repos.d/MariaDB.repo [mariadb] name=MariaDB # URL을 방문해서 최신 버전을 확인한 다음 버전 숫자를 변경하기 바란다. baseurl=http://yum.mariadb.org/5.5/centos6-x86/ gpgkey=http://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 # 두 개의 패키지 명령어 yum으로 패키지 설치한다. [root@master ~]# yum install mariadb-server mariadb -y # 설치된 패키지를 확인하는데, 의존 관계에 있는 다른 패키지도 설치된 것을 확인할 수 있다. [root@master ~]# rpm -qa | grep mariadb mariadb-server-5.5.52-1.el7.x86_64 mariadb-libs-5.5.52-1.el7.x86_64 mariadb-5.5.52-1.el7.x86_64 | cs |
b. MariaDB 서버 서비스 시작
패키지 설치를 완료했다면 이제 몇 가지의 간단한 과정을 마치면 MariaDB 서버를 시작해서 사용할 수 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | # MariaDB 시작한다. [root@master ~]# systemctl start mariadb # MariaDB 서버가 부팅 후 자동으로 시작되도록 설정한다. [root@master ~]# systemctl enable mariadb Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. # MariaDB 서버 상태에서 active로 서버가 시작됐다는 메시지로 이 서버가 성공적으로 시작됐다는 의미이다. [root@master ~]# systemctl status mariadb â mariadb.service - MariaDB database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled) Active: active (running) since Tue 2017-08-08 09:56:51 UTC; 10s ago Main PID: 2013 (mysqld_safe) CGroup: /system.slice/mariadb.service ââ2013 /bin/sh /usr/bin/mysqld_safe --basedir=/usr ââ2171 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-... Aug 08 09:56:48 master.chul.com mariadb-prepare-db-dir[1934]: The latest information about MariaDB is available at ht...rg/. Aug 08 09:56:48 master.chul.com mariadb-prepare-db-dir[1934]: You can find additional information about the MySQL part at: Aug 08 09:56:48 master.chul.com mariadb-prepare-db-dir[1934]: http://dev.mysql.com Aug 08 09:56:48 master.chul.com mariadb-prepare-db-dir[1934]: Support MariaDB development by buying support/new featu...iaDB Aug 08 09:56:48 master.chul.com mariadb-prepare-db-dir[1934]: Corporation Ab. You can contact us about this at sales@...com. Aug 08 09:56:48 master.chul.com mariadb-prepare-db-dir[1934]: Alternatively consider joining our community based deve...ort: Aug 08 09:56:48 master.chul.com mariadb-prepare-db-dir[1934]: http://mariadb.com/kb/en/contributing-to-the-mariadb-project/ Aug 08 09:56:49 master.chul.com mysqld_safe[2013]: 170808 09:56:49 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'. Aug 08 09:56:49 master.chul.com mysqld_safe[2013]: 170808 09:56:49 mysqld_safe Starting mysqld daemon with databases...mysql Aug 08 09:56:51 master.chul.com systemd[1]: Started MariaDB database server. Hint: Some lines were ellipsized, use -l to show in full. # 포트 3306번을 확인해보면 mysqld 데몬과 mysql 서비스를 볼 수 있는데, /etc/service 파일에 포트의 이름이 mysql로 정의돼 있기 때문이다. [root@master ~]# lsof -i tcp:3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 2171 mysql 14u IPv4 56880 0t0 TCP *:mysql (LISTEN) [root@master ~]# grep mysql /etc/services mysql 3306/tcp # MySQL mysql 3306/udp # MySQL # 프로세스 확인한다. [root@master ~]# ps -ef | grep mysqld mysql 2013 1 0 09:56 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr mysql 2171 2013 0 09:56 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock root 2229 1746 0 09:57 pts/0 00:00:00 grep --color=auto mysqld # MariaDB가 사용하는 로그 파일을 확인한다. [root@master ~]# ls -al /var/log/mariadb/mariadb.log -rw-r-----. 1 mysql mysql 1885 Aug 8 09:56 /var/log/mariadb/mariadb.log # 데이터베이스를 저장하는 디렉토리르 확인한다. [root@master ~]# ls /var/lib/mysql/ aria_log.00000001 aria_log_control ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock performance_schema test | cs |
2.2 MariaDB 서버 접속
어떤 에러 메시지도 없이 성공적으로 시작했다면 이제 직접 접속해 몇가지 기본정보를 확인할 수 있다.
이 과정을 통해 관리자로 서버 접속에 필요한 패스워드도 설정할 수 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | # MariaDB 서버 초기 설정을 위해 사용되는 명령어 [root@master mysql]# mysql_secure_installation In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): press Enter Set root password? [Y/n] y (데이터베이스 서버에 접속할 root 관리자의 패스워드 설정) Remove anonymous users? [Y/n] y (테스팅을 위해 사용되는 사용자를 제거) Disallow root login remotely? [Y/n] y (원격지에서 사용자의 root의 로그인을 허용하지 않음) Remove test database and access to it? [Y/n] y (테스트용 데이터베이스를 삭제) Reload privilege tables now? [Y/n] y (변경 사항이 바로 적용되도록 테이블을 다시 읽어 들임) All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! # 현재 사용중인 서버의 버전을 확인한다 [root@master mysql]# mysql -V mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1 # 위에 생성한 root의 패스워드를 이용해 서버에 접속한다. [root@master mysql]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; (현재 데이터베이스 목록을 확인한다) +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> SELECT VERSION(); (현재 버전 정보를 출력한다) +----------------+ | VERSION() | +----------------+ | 5.5.52-MariaDB | +----------------+ 1 row in set (0.00 sec) | cs |
2.3 Firewalld
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | # 방화벽이 정상으로 동작된 것을 확인 [root@master mysql]# systemctl status firewalld â firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2017-08-08 08:38:50 UTC; 1min 42s ago Docs: man:firewalld(1) Main PID: 2324 (firewalld) CGroup: /system.slice/firewalld.service ââ2324 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid Aug 08 08:38:49 master.chul.com systemd[1]: Starting firewalld - dynamic firewall daemon... Aug 08 08:38:50 master.chul.com systemd[1]: Started firewalld - dynamic firewall daemon. # MariaDB 포트 3306을 허용하도록 추가 [root@master mysql]# firewall-cmd --permanent --zone=public --add-port=3306/tcp success # 설정 파일 확인 [root@master mysql]# cat /etc/firewalld/zones/public.xml <?xml version="1.0" encoding="utf-8"?> <zone> <short>Public</short> <description>For use in public areas. You do not trust the other computers on networks to not harm your computer. Only selected incoming connections are accepted.</description> <service name="dhcpv6-client"/> <service name="ssh"/> <port protocol="tcp" port="3306"/> </zone> # 방화벽 설정 적용하기 [root@master mysql]# firewall-cmd --reload success | cs |
3. 데이터베이스와 테이블 관리
3.1 데이터베이스 사용
a. SQL 사용하기
데이터베이스에 접속한 후에 SQL을 사용해 데이터베이스를 생성하고 삭제하는 방법은 다음과 같다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | [root@master ~]# mysql -u root -p MariaDB [(none)]> CREATE DATABASE helloworld; (새 데이터베이스 helloworld 생성) MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS helloworld; (데이터베이스가 있는 경우 에러 메시지를 생성하지 않기 위해 사용) MariaDB [(none)]> SHOW DATABASES; (생성 된 데이터베이스 확인) +--------------------+ | Database | +--------------------+ | information_schema | | helloworld | | mysql | | performance_schema | +--------------------+ MariaDB [(none)]> SELECT database(); (현재 사용 중인 데이터베이스를 확인하기 위해 사용되는데, 로그인 이후 데이터베이스를 선택하지 않았음으로 그 값이 NULL이다.) +------------+ | database() | +------------+ | NULL | +------------+ MariaDB [(none)]> USE helloworld; (사용할 데이터베이스를 선택) Database changed MariaDB [helloworld]> SELECT database(); (앞에서 선택한 데이터베이스 이름 확인) +------------+ | database() | +------------+ | helloworld | +------------+ MariaDB [helloworld]> DROP DATABASE helloworld; (데이터베이스 삭제) MariaDB [(none)]> DROP DATABASE IF EXISTS helloworld; (존재하지 않는 데이터베이스를 삭제할 경우 에러 메시지를 발생하므로 DB가 있는 경우에만 삭제한다.) MariaDB [(none)]> SHOW DATABASES; (삭제 후 데이터베이스가 존재하지 않음) +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ MariaDB [(none)]> CREATE SCHEMA helloworld; (데이터베이스를 생성하는 또 다른 방법은 database와 schema는 동일하게 사용된다.) MariaDB [(none)]> DROP SCHEMA helloworld; (앞에서 생성된 schema를 삭제) MariaDB [(none)]> show schemas; (SHOW DATABASES 와 동일하게 사용된다.) +--------------------+ | Database | +--------------------+ | information_schema | | hello | | mysql | | performance_schema | +--------------------+ | cs |
b. 클라이언트 툴 사용하기
데이터베이스를 생성하고 삭제하는 두 번째 방법은 직접 데이터베이스 서버에 접속하지 않고 클라이언트 툴 mysql와 mysqladmin만을 사용해 데이터베이스를 생성하고 삭제할 수 있는데, 이에 대한 설명은 다음과 같다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | # mysqladmin을 이용해서 DB 생성 [root@master ~]# mysqladmin -u root -p create helloworld [root@master ~]# mysqladmin -u root -p drop helloworld Do you really want to drop the 'helloworld' database [y/N] y Database "helloworld" dropped # mysql을 이용해 DB를 생성하는데, 옵션 e(execute)는 특정 명령어를 실행하기 위해 사용 [root@master ~]# mysql -u root -p -e "create database helloworld" [root@master ~]# mysql -u root -p -e "show databases" +--------------------+ | Database | +--------------------+ | information_schema | | helloworld | | mysql | | performance_schema | +--------------------+ # 특정 DB로 바로 접속하는 경우 사용자 정보 뒤에 그 이름을 [root@master ~]# mysql -u root -p helloworld MariaDB [helloworld]> [root@master ~]# mysql -u root -p -e "drop database helloworld" | cs |
c. 데이터베이스 메타데이터 사용
메타데이터란 데이터에 관한 구조화된 데이터로, 다른 데이터를 설명해 주는 데이터이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | # 메타데이터 데이터베이스 INFORMATION_SCHEMA의 테이블 칼럼 SCHEMA_NAME에서 찾을 수 있는데, # 그리고 이 데이터베이스는 SQL 구문인 SELECT를 이용해 생성된 데이터베이스의 정보를 저장하고 있다. MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'helloworld'\G *************************** 1. row *************************** CATALOG_NAME: def SCHEMA_NAME: helloworld DEFAULT_CHARACTER_SET_NAME: latin1 DEFAULT_COLLATION_NAME: latin1_swedish_ci SQL_PATH: NULL 1 row in set (0.00 sec) # 위와 동일하며 명령어 뒤 \G가 사용되지 않은 것만이 차이점인데, 메타데이터가 일반테이블의 로우 정보와 동일한 형태를 보여준다. # ego (\G) Send command to mysql server, display result vertically. MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'helloworld' -> ; +--------------+-------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+-------------+----------------------------+------------------------+----------+ | def | helloworld | latin1 | latin1_swedish_ci | NULL | +--------------+-------------+----------------------------+------------------------+----------+ 1 row in set (0.00 sec) # 데이터베이스 생성에 사용된 명령어를 확인할 수 있다. MariaDB [(none)]> SHOW CREATE DATABASE helloworld\G *************************** 1. row *************************** Database: helloworld Create Database: CREATE DATABASE `helloworld` /*!40100 DEFAULT CHARACTER SET latin1 */ 1 row in set (0.00 sec) | cs |
3.2 테이블 사용
테이블 사용은 핵심 중의 핵심이라 할 수 있을정도로 매우 중요하다. 모든 데이터가 이 테이블 내부에 저장돼 SQL을 이용한 데이터 관리가 모두 이 테이블을 통해서 이뤄지기 때문이다. 테이블은 논리적으로 로우(row), 칼럼(column)으로 구성되고 물리적으로는 디렉토리 내부에 /var/lib/mysql/helloworld/customers.frm 과 같은 이름으로 저장되는데, 여기서 /var/lib/mysql은 MariaDB 서버의 데이터베이스 저장 디렉토리고, helloworld는 새로 생성된 데이터베이스 이름이며, customers는 이 데이터베이스 내부에 생성된 테이블 이름이다.
a. 테이블 생성
MariaDB 서버에서 테이블 생성하는 방법이 여러 가지 있는데, 여기서는 두 가지 방법(수동, 기존 테이블)을 소개한다.
- 수동으로 테이블 생성하기
테이블을 생성하는 가장 기본적인 방법은 SQL 명령어를 사용해 테이블을 직접 수동으로 생성하는 것이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | [root@master helloworld]# mysql -u root -p helloworld MariaDB [helloworld]> CREATE TABLE customers (customers 테이블 생성) -> (cust_id INT NOT NULL AUTO_INCREMENT, (칼럼 이름, 칼럼 데이터 유형, 옵션) -> cust_name CHAR(30) NOT NULL, -> cust_address CHAR(50) NULL, -> cust_email CHAR(30) NOT NULL, -> cust_zip INT NOT NULL DEFAULT 123456, -> PRIMARY KEY (cust_id) -> ) ENGINE=Aria; Query OK, 0 rows affected (0.00 sec) MariaDB [helloworld]> SHOW TABLES; (생성 후에 정보 확인을 위해 사용) +----------------------+ | Tables_in_helloworld | +----------------------+ | customers | +----------------------+ 1 row in set (0.00 sec) MariaDB [helloworld]> DESCRIBE customers; (생성된 테이블의 칼럼 이름과 데이터 유형, 사용된 옵션 확인) +--------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(30) | NO | | NULL | | | cust_address | char(50) | YES | | NULL | | | cust_email | char(30) | NO | | NULL | | | cust_zip | int(11) | NO | | 123456 | | +--------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) MariaDB [helloworld]> INSERT INTO customers -> (cust_id,cust_name,cust_address,cust_email,cust_zip) -> VALUES(1000,"Jeong Chul","Seoul","sepiros62@gmail.com",1234567); Query OK, 1 row affected (0.00 sec) (테이블에 데이터를 입력하는데, 각 칼럼에 맞는 값을 VALUES 옵션 다음에 입력) MariaDB [helloworld]> SELECT * FROM customers; (입력한 데이터가 테이블에 있는지 확인) +---------+------------+--------------+---------------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+------------+--------------+---------------------+----------+ | 1000 | Jeong Chul | Seoul | sepiros62@gmail.com | 1234567 | +---------+------------+--------------+---------------------+----------+ 1 row in set (0.00 sec) MariaDB [helloworld]> SHOW TABLE STATUS\G (생성된 테이블의 전체 상태를 확인하기 위해 사용) *************************** 1. row *************************** Name: customers Engine: Aria Version: 10 Row_format: Page Rows: 1 Avg_row_length: 16384 Data_length: 16384 Max_data_length: 68719484928 Index_length: 16384 Data_free: 0 Auto_increment: 1001 Create_time: 2017-08-09 05:26:04 Update_time: 2017-08-09 05:26:04 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL | cs |
- 기존 테이블을 이용해 테이블 생성하기
두 번째 방법은 기존 테이블이 있다면 이 테이블을 이용해 새로운 테이블을 생성하는 방법이다. 앞에서 생성했던 customers 테이블을 이용해 테스트를 진행하겠다.
SELECT [칼럼명] FROM [테이블명] WHERE [칼럼명] LIKE '특정문자열%;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | [root@master ~]# mysql -u root -p helloworld # customers의 데이터를 이용해 새 테이블 생성 MariaDB [helloworld]> CREATE TABLE customers1 SELECT * FROM customers; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [helloworld]> SELECT * FROM customers1; +---------+------------+--------------+---------------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+------------+--------------+---------------------+----------+ | 1000 | Jeong Chul | Seoul | sepiros62@gmail.com | 1234567 | +---------+------------+--------------+---------------------+----------+ # 새 테이블 customers1이 생성될 때 어떤 값들이 생성됐는지 확인 MariaDB [helloworld]> SHOW CREATE TABLE customers1\G *************************** 1. row *************************** Table: customers1 Create Table: CREATE TABLE `customers1` ( `cust_id` int(11) NOT NULL DEFAULT '0', `cust_name` char(30) NOT NULL, `cust_address` char(50) DEFAULT NULL, `cust_email` char(30) NOT NULL, `cust_zip` int(11) NOT NULL DEFAULT '123456' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) # WHERE절을 사용해 칼럼 값 cust_id=1000만 입력된 테이블 생성 MariaDB [helloworld]> CREATE TABLE customers2 SELECT * FROM customers WHERE cust_id = 1000; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [helloworld]> SELECT * FROM customers2; +---------+------------+--------------+---------------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+------------+--------------+---------------------+----------+ | 1000 | Jeong Chul | Seoul | sepiros62@gmail.com | 1234567 | +---------+------------+--------------+---------------------+----------+ # 새 테이블에서 사용하지 않고, 테이블의 칼럼 및 데이터 유형과 옵션만을 사용할 경우 WHERE절에 0을 사용해 가능하다. MariaDB [helloworld]> CREATE TABLE customers3 SELECT * FROM customers WHERE 0; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [helloworld]> SELECT * FROM customers3; Empty set (0.00 sec) # 위와 동일하며 단지 키워드 LIKE를 사용했다. MariaDB [helloworld]> CREATE TABLE customers4 LIKE customers; Query OK, 0 rows affected (0.01 sec) MariaDB [helloworld]> SELECT * FROM customers4; Empty set (0.00 sec) | cs |
b. 테이블 수정
A절에서 테이블을 생성했다면 필요에 따라 테이블을 수정해야 하는데, 여기서 테이블 생성 이후 기존 정보를 수정하는 방법을 살펴보자.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | # ADD를 이용해 기존 테이블에 새 칼럼 유형 및 옵션을 추가하는데, 위치를 키워드 AFTER로 지정할 수 있다. MariaDB [helloworld]> ALTER TABLE customers ADD cust_date DATE NOT NULL AFTER cust_email; MariaDB [helloworld]> DESCRIBE customers; +--------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(30) | NO | | NULL | | | cust_address | char(50) | YES | | NULL | | | cust_email | char(30) | NO | | NULL | | | cust_date | date | NO | | NULL | | | cust_zip | int(11) | NO | | 123456 | | +--------------+----------+------+-----+---------+----------------+ # cust_id의 옵션을 MODIFY 키워드로 변경하는데, 데이터 유형 int에서 bigint로, NULL 허용을 NOT NULL 즉 허용하지 않음으로 변경 MariaDB [helloworld]> ALTER TABLE customers MODIFY cust_id BIGINT -> NOT NULL AUTO_INCREMENT; # 칼럼 이름 변경을 위해 키워드 CHANGE를 사용해 현재 cust_email을 cust_contact로 변경 MariaDB [helloworld]> ALTER TABLE customers CHANGE cust_email -> cust_cotact CHAR(40) NOT NULL; MariaDB [helloworld]> DESCRIBE customers; +--------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------+------+-----+---------+----------------+ | cust_id | bigint(20) | NO | PRI | NULL | auto_increment | | cust_name | char(30) | NO | | NULL | | | cust_address | char(50) | YES | | NULL | | | cust_cotact | char(40) | NO | | NULL | | | cust_date | date | NO | | NULL | | | cust_zip | int(11) | NO | | 123456 | | +--------------+------------+------+-----+---------+----------------+ # 사용 중인 칼럼을 삭제하기 위해 DROP을 사용 MariaDB [helloworld]> ALTER TABLE customers DROP COLUMN cust_date; # 테이블의 이름을 변경하기 위해 키워드 RENAME TO or RENAME ~ TO을 사용 MariaDB [helloworld]> ALTER TABLE customers RENAME TO new_customers; MariaDB [helloworld]> RENAME TABLE new_customers TO customers; # 정보를 변경하기 위해 각각 키워드를 별도로 사용했지만, 이렇게 동시에 사용 가능 MariaDB [helloworld]> ALTER TABLE customers RENAME TO customers, -> ADD cust_date DATE NULL AFTER cust_name; MariaDB [helloworld]> DESCRIBE customers; +--------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------+------+-----+---------+----------------+ | cust_id | bigint(20) | NO | PRI | NULL | auto_increment | | cust_name | char(30) | NO | | NULL | | | cust_date | date | YES | | NULL | | | cust_address | char(50) | YES | | NULL | | | cust_cotact | char(40) | NO | | NULL | | | cust_zip | int(11) | NO | | 123456 | | +--------------+------------+------+-----+---------+----------------+ # 현재 테이블의 스토리지 엔진을 변경할 경우에 사용 MariaDB [helloworld]> ALTER TABLE customers2 ENGINE = Aria; 스토리지엔진은 DB에서 데이터를 어떠한 방식으로 저장하고 접근할 것인지에 대한 기능을 제공한다. | cs |
c. 테이블 삭제
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | MariaDB [helloworld]> SELECT * FROM customers; +---------+------------+------------+--------------+---------------------+----------+ | cust_id | cust_name | cust_date | cust_address | cust_cotact | cust_zip | +---------+------------+------------+--------------+---------------------+----------+ | 1000 | Jeong Chul | NULL | Seoul | sepiros62@gmail.com | 1234567 | | 1001 | Ju Dahee | 2017-08-10 | Seoul | sepiros63@naver.com | 678912 | +---------+------------+------------+--------------+---------------------+----------+ # 테이블 내의 데이터를 삭제하기 위해 명령어 DELETE를 사용하고, 특정 로우만 삭제 할 경우 WHERE절을 사용할 수 있다. MariaDB [helloworld]> DELETE FROM customers WHERE cust_id = 1001; MariaDB [helloworld]> SELECT * FROM customers; +---------+------------+-----------+--------------+---------------------+----------+ | cust_id | cust_name | cust_date | cust_address | cust_cotact | cust_zip | +---------+------------+-----------+--------------+---------------------+----------+ | 1000 | Jeong Chul | NULL | Seoul | sepiros62@gmail.com | 1234567 | +---------+------------+-----------+--------------+---------------------+----------+ # 위와 동일하게 명령어 TRUNCATE도 테이블 내의 데이터를 삭제한다. MariaDB [helloworld]> TRUNCATE TABLE customers; Query OK, 0 rows affected (0.01 sec) MariaDB [helloworld]> SELECT * FROM customers; Empty set (0.00 sec) # 기존 테이블을 삭제할 경우 명령어 DROP을 사용한다. MariaDB [helloworld]> DROP TABLE customers; Query OK, 0 rows affected (0.00 sec) MariaDB [helloworld]> SHOW TABLES; +----------------------+ | Tables_in_helloworld | +----------------------+ | customers1 | | customers2 | | customers3 | | customers4 | +----------------------+ 4 rows in set (0.00 sec) | cs |
4. SQL을 이용한 데이터 사용
4.1 INSERT 구문 사용
a. 기본 INSERT 사용
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | # INSERT INTO를 사용해 칼럼 이름과 그에 대응하는 값을 키워드 VALUSE로 입력 MariaDB [helloworld]> INSERT INTO customers -> (cust_id,cust_name,cust_address,cust_email,cust_zip) -> VALUES(1002,"Sung Park","Daegu","sepiros62@naver.com",123459); # 동일하게 INSER INTO를 사용하는데 VALUSE 대신 SET을 사용하여 입력 MariaDB [helloworld]> INSERT INTO customers SET -> cust_id=1003,cust_name="Jun Lee",cust_address="Daejeon", -> cust_email="jun@gmail.com",cust_zip=123460; # 5개가 아닌 3개의 칼럼만 입력하는데, 입력 시 옵션이 NULL인 경우 생략가능하다. MariaDB [helloworld]> INSERT INTO customers -> (cust_name,cust_address,cust_email) -> VALUES("Chulsu Cho","Incheon","minsu@naver.com"); # 5개가 아닌 2개의 칼럼만 입력 MariaDB [helloworld]> INSERT INTO customers SET ->cust_name="Minsu Mun",cust_email="mun@gmail.com"; MariaDB [helloworld]> SELECT * FROM customers; +---------+---------------+--------------+---------------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+---------------+--------------+---------------------+----------+ | 1000 | John Kim | Busan | john@naver.com | 123458 | | 1001 | Jeong Jaehwan | Daegu | Jeong@naver.com | 123459 | | 1002 | Sung Park | Daegu | sepiros62@naver.com | 123459 | | 1003 | Jun Lee | Daejeon | jun@gmail.com | 123460 | | 1004 | Chulsu Cho | Incheon | minsu@naver.com | 123456 | | 1005 | Minsu Mun | NULL | mun@gmail.com | 123456 | +---------+---------------+--------------+---------------------+----------+ # 두 개의 로우 데이터를 한번에 입력하는 방법 MariaDB [helloworld]> INSERT INTO customers -> (cust_name,cust_email) -> VALUES ("Namo Kim","namo@daum.net"), -> ("Sunny Lee","sunny@naver.com"); # 칼럼 이름을 생략하고 입력하는데, 이 경우에 특정 칼럼의 생략 없이 모든 칼럼 값이 입력되어야 한다. MariaDB [helloworld]> INSERT INTO customers -> VALUES (1008,"Chulsu Yang","Seoul","chulsu@gamil.com",123457); # 칼럼과 VALUSE에 아무런 값을 지정하지 않으면 NULL 또는 빈칸이 입력된다. MariaDB [helloworld]> INSERT INTO customers() VALUES(); Query OK, 1 row affected, 2 warnings (0.00 sec) MariaDB [helloworld]> SELECT * FROM customers; +---------+---------------+--------------+---------------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+---------------+--------------+---------------------+----------+ | 1000 | John Kim | Busan | john@naver.com | 123458 | | 1001 | Jeong Jaehwan | Daegu | Jeong@naver.com | 123459 | | 1002 | Sung Park | Daegu | sepiros62@naver.com | 123459 | | 1003 | Jun Lee | Daejeon | jun@gmail.com | 123460 | | 1004 | Chulsu Cho | Incheon | minsu@naver.com | 123456 | | 1005 | Minsu Mun | NULL | mun@gmail.com | 123456 | | 1006 | Namo Kim | NULL | namo@daum.net | 123456 | | 1007 | Sunny Lee | NULL | sunny@naver.com | 123456 | | 1008 | Chulsu Yang | Seoul | chulsu@gamil.com | 123457 | | 1009 | | NULL | | 123456 | +---------+---------------+--------------+---------------------+----------+ | cs |
b. INSERT 사용의 두 번째 방법
이 방법은 기존 테이블 내에 데이터를 이용해 입력한다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | # 현재 customers3 테이블에는 아무런 데이터가 없는 상태 MariaDB [helloworld]> SELECT * FROM customers3; Empty set (0.00 sec) # customers에 있는 데이터를 빈 테이블 customers3로 입력 MariaDB [helloworld]> INSERT INTO customers3 SELECT * FROM customers; MariaDB [helloworld]> SELECT * FROM customers3; +---------+---------------+--------------+---------------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+---------------+--------------+---------------------+----------+ | 1000 | John Kim | Busan | john@naver.com | 123458 | | 1001 | Jeong Jaehwan | Daegu | Jeong@naver.com | 123459 | | 1002 | Sung Park | Daegu | sepiros62@naver.com | 123459 | | 1003 | Jun Lee | Daejeon | jun@gmail.com | 123460 | | 1004 | Chulsu Cho | Incheon | minsu@naver.com | 123456 | | 1005 | Minsu Mun | NULL | mun@gmail.com | 123456 | | 1006 | Namo Kim | NULL | namo@daum.net | 123456 | | 1007 | Sunny Lee | NULL | sunny@naver.com | 123456 | | 1008 | Chulsu Yang | Seoul | chulsu@gamil.com | 123457 | | 1009 | | NULL | | 123456 | +---------+---------------+--------------+---------------------+----------+ 10 rows in set (0.00 sec) # 현재 customers4 테이블에도 아무런 데이터가 없는 상태 MariaDB [helloworld]> SELECT * FROM customers4; Empty set (0.00 sec) # customers에 있는 데이터를 customers4로 입력하는데, cust_id가 1005보다 크거나 같은 데이터만 입력 MariaDB [helloworld]> INSERT INTO customers4 SELECT * FROM customers -> WHERE cust_id >= 1005; MariaDB [helloworld]> SELECT * FROM customers4; +---------+-------------+--------------+------------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+-------------+--------------+------------------+----------+ | 1005 | Minsu Mun | NULL | mun@gmail.com | 123456 | | 1006 | Namo Kim | NULL | namo@daum.net | 123456 | | 1007 | Sunny Lee | NULL | sunny@naver.com | 123456 | | 1008 | Chulsu Yang | Seoul | chulsu@gamil.com | 123457 | | 1009 | | NULL | | 123456 | +---------+-------------+--------------+------------------+----------+ 5 rows in set (0.00 sec) # 빈 테이블 customers5에 customers 데이터를 입력하는데, 모든 칼럼이 아닌 일부 칼럼만 선택해 입력 MariaDB [helloworld]> INSERT INTO customers5 (cust_id,cust_name) SELECT cust_id,cust_name FROM customers; # 두 개의 칼럼만 입력돼 있고 나머지는 기본 값 NULL, 빈값으로 채워져 있음 MariaDB [helloworld]> SELECT * FROM customers5; +---------+---------------+--------------+------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+---------------+--------------+------------+----------+ | 1000 | John Kim | NULL | | 123456 | | 1001 | Jeong Jaehwan | NULL | | 123456 | | 1002 | Sung Park | NULL | | 123456 | | 1003 | Jun Lee | NULL | | 123456 | | 1004 | Chulsu Cho | NULL | | 123456 | | 1005 | Minsu Mun | NULL | | 123456 | | 1006 | Namo Kim | NULL | | 123456 | | 1007 | Sunny Lee | NULL | | 123456 | | 1008 | Chulsu Yang | NULL | | 123456 | | 1009 | | NULL | | 123456 | +---------+---------------+--------------+------------+----------+ 10 rows in set (0.00 sec) | cs |
c. REPLACE 사용
명령어 REPLACE는 INSERT처럼 데이터를 입력하기 위해 사용되는 점은 동일하지만, 기존로우를 지우고 새로운 데이터를 입력하기 위해 사용한다는 차이점이 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | # 현재 customers 테이블 칼럼 cust_id 1010의 값 확인 MariaDB [helloworld]> SELECT * FROM customers WHERE cust_id = 1010; +---------+-----------+--------------+------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+-----------+--------------+------------+----------+ | 1010 | | NULL | | 123456 | +---------+-----------+--------------+------------+----------+ # REPLACE을 이용해 cust_id=1010 값을 입력 MariaDB [helloworld]> REPLACE INTO customers VALUES -> (1010,"Jin Lee","Jeju","jin@naver.com",123461); MariaDB [helloworld]> SELECT * FROM customers WHERE cust_id = 1010; +---------+-----------+--------------+---------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+-----------+--------------+---------------+----------+ | 1010 | Jin Lee | Jeju | jin@naver.com | 123461 | +---------+-----------+--------------+---------------+----------+ # customers 테이블 칼럼 cust_id=1005에서 데이터 확인 MariaDB [helloworld]> SELECT * FROM customers WHERE cust_id = 1005; +---------+-----------+--------------+---------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+-----------+--------------+---------------+----------+ | 1005 | Minsu Mun | NULL | mun@gmail.com | 123456 | +---------+-----------+--------------+---------------+----------+ # REPLACE와 키워드 SET를 이용해 데이터를 입력하면 기존 데이터가 사라지고 입력한 데이터가 저장돼 있는 것을 확인 MariaDB [helloworld]> REPLACE INTO customers SET -> cust_id=1005,cust_name="Young Mun", cust_address="Seoul",cust_email="young@daum.net",cust_zip=123457; MariaDB [helloworld]> SELECT * FROM customers WHERE cust_id = 1005; +---------+-----------+--------------+----------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+-----------+--------------+----------------+----------+ | 1005 | Young Mun | Seoul | young@daum.net | 123457 | +---------+-----------+--------------+----------------+----------+ # customers5 테이블의 값을 확인 MariaDB [helloworld]> SELECT * FROM customers5 WHERE cust_id = 1010; Empty set (0.01 sec) # customers 테이블 칼럼 cust_id=1010에 있는 ㅔ이터를 customers5에 입력 MariaDB [helloworld]> REPLACE INTO customers5 SELECT * FROM customers -> WHERE cust_id = 1010; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [helloworld]> SELECT * FROM customers5 WHERE cust_id = 1010; +---------+-----------+--------------+---------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+-----------+--------------+---------------+----------+ | 1010 | Jin Lee | Jeju | jin@naver.com | 123461 | +---------+-----------+--------------+---------------+----------+ | cs |
4.2 SELECT 사용
a. Testing 환경 만들기
먼저 테스팅을 위해 SQL 파일을 다운로드한 후에 그 데이터를 데이터베이스로 입력하는 과정은 다음과 같다.
파일을 받아서 압축을 해제한 후에 사용하자.
mariadb_scripts.zip1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | # 데이터베이스를 명령어 CREATE로 생성 MariaDB [(none)]> CREATE DATABASE business; Query OK, 1 row affected (0.00 sec) # mysql을 사용해 데이터베이스 business에 리다이렉션으로 create.sql & populate.sql을 입력 [root@master tmp]# mysql -u root -p business < create.sql [root@master tmp]# mysql -u root -p business < populate.sql # 생성된 데이터베이스 확인 MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | business | | helloworld | | mysql | | performance_schema | +--------------------+ # 사용할 데이터베이스 선택 MariaDB [(none)]> USE business; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed # 두 sql 파일의 입력으로 생성된 테이블 정보를 확인 MariaDB [business]> SHOW TABLES; +--------------------+ | Tables_in_business | +--------------------+ | customers | | orderitems | | orders | | productnotes | | products | | vendors | +--------------------+ # orders 테이블에서 입력된 값을 확인 MariaDB [business]> SELECT * FROM orders; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2011-09-01 00:00:00 | 10001 | | 20006 | 2011-09-12 00:00:00 | 10003 | | 20007 | 2011-09-30 00:00:00 | 10004 | | 20008 | 2011-10-03 00:00:00 | 10005 | | 20009 | 2011-10-08 00:00:00 | 10001 | +-----------+---------------------+---------+ | cs |
b. 기본적인 SELECT 구문 사용
테스팅 환경이 구축됐다면 이 데이터를 이용해 SELECT 구문을 사용하는 방법을 알아보자.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | # SELECT에서 검색하기 원하는 칼럼을 ㅣ정하고 FROM 뒤에 테이블 이름을 지정 MariaDB [business]> SELECT cust_id,cust_name,cust_address FROM customers; +---------+----------------+---------------------+ | cust_id | cust_name | cust_address | +---------+----------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | | 10002 | Mouse House | 333 Fromage Lane | | 10003 | Wascals | 1 Sunny Place | | 10004 | Yosemite Place | 829 Riverside Drive | | 10005 | E Fudd | 4545 53rd Street | +---------+----------------+---------------------+ # products 테이블에서 vend_id를 검색하면 총 14개의 로우 값을 볼 수 있다. MariaDB [business]> SELECT vend_id FROM products; +---------+ | vend_id | +---------+ | 1001 | | 1001 | | 1001 | | 1002 | | 1002 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1005 | | 1005 | +---------+ # 중복되는 로우를 제거하기 위해 DISTINCT 옵션을 사용 MariaDB [business]> SELECT DISTINCT vend_id FROM products; +---------+ | vend_id | +---------+ | 1001 | | 1002 | | 1003 | | 1005 | +---------+ # LIMIT을 사용해 범위를 지정할 수 있는데, 이 경우 다섯 번째 로우까지의 데이터를 검색하라는 의미 MariaDB [business]> SELECT * FROM products LIMIT 5; +---------+---------+--------------+------------+----------------------------------------------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+--------------+------------+----------------------------------------------------------------+ | ANV01 | 1001 | .5 ton anvil | 5.99 | .5 ton anvil, black, complete with handy hook | | ANV02 | 1001 | 1 ton anvil | 9.99 | 1 ton anvil, black, complete with handy hook and carrying case | | ANV03 | 1001 | 2 ton anvil | 14.99 | 2 ton anvil, black, complete with handy hook and carrying case | | OL1 | 1002 | Oil can | 8.99 | Oil can, red | | FU1 | 1002 | Fuses | 3.42 | 1 dozen, extra long | +---------+---------+--------------+------------+----------------------------------------------------------------+ # 검색의 범위를 3번부터 그 이하로 5개까지 제한해 검색 MariaDB [business]> SELECT * FROM products LIMIT 3,5; +---------+---------+----------------+------------+-----------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+----------------+------------+-----------------------------+ | OL1 | 1002 | Oil can | 8.99 | Oil can, red | | FU1 | 1002 | Fuses | 3.42 | 1 dozen, extra long | | SLING | 1003 | Sling | 4.49 | Sling, one size fits all | | TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick | | TNT2 | 1003 | TNT (5 sticks) | 10.00 | TNT, red, pack of 10 sticks | +---------+---------+----------------+------------+-----------------------------+ # 숫자 3과 5를 각각 키워드 LIMIT과 OFFSET으로 지정하는데, 여기서 OFFSET은 몇 번째의 데이터인지, LIMIT은 몇 개의 로우 정보를 검색할지 결정 #OFFSET에서 3은 네 번째 데이터를 의미하는데, 첫 번째 데이터는 0부터 시작 MariaDB [business]> SELECT * FROM products LIMIT 5 OFFSET 3; +---------+---------+----------------+------------+-----------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+----------------+------------+-----------------------------+ | OL1 | 1002 | Oil can | 8.99 | Oil can, red | | FU1 | 1002 | Fuses | 3.42 | 1 dozen, extra long | | SLING | 1003 | Sling | 4.49 | Sling, one size fits all | | TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick | | TNT2 | 1003 | TNT (5 sticks) | 10.00 | TNT, red, pack of 10 sticks | +---------+---------+----------------+------------+-----------------------------+ # 데이터를 순서대로 정렬하기 위해 ORDER BY를 사용하고, DESC는 정렬 순서를 반대로 하라는 의미 MariaDB [business]> SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC LIMIT 5; +---------+------------+--------------+ | prod_id | prod_price | prod_name | +---------+------------+--------------+ | JP2000 | 55.00 | JetPack 2000 | | SAFE | 50.00 | Safe | | JP1000 | 35.00 | JetPack 1000 | | ANV03 | 14.99 | 2 ton anvil | | DTNTR | 13.00 | Detonator | +---------+------------+--------------+ | cs |
c. WHERE절 사용
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | # 테이블에서 두 개의 칼럼 정보를 검색하는데, prod_price <= 10은 10보다 작거나 같고 정렬은 DESC(역순)으로 정렬하고 가격이 같은 경우 prod_name이 기준 MariaDB [business]> SELECT prod_name, prod_price FROM products WHERE prod_price <= 10 ORDER BY prod_price DESC,prod_name; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | Bird seed | 10.00 | | TNT (5 sticks) | 10.00 | | 1 ton anvil | 9.99 | | Oil can | 8.99 | | .5 ton anvil | 5.99 | | Sling | 4.49 | | Fuses | 3.42 | | Carrots | 2.50 | | TNT (1 stick) | 2.50 | +----------------+------------+ # 테이블에서 두 개의 칼럼 정보를 검색하는데, vend_id가 1003이 아닌(<>) 다섯 개의 로우 정보를 보여준다. MariaDB [business]> SELECT vend_id,prod_name FROM products -> WHERE vend_id <> 1003 LIMIT 5; +---------+--------------+ | vend_id | prod_name | +---------+--------------+ | 1001 | .5 ton anvil | | 1001 | 1 ton anvil | | 1001 | 2 ton anvil | | 1002 | Oil can | | 1002 | Fuses | +---------+--------------+ # 테이블에서 두 개의 칼럼 정보를 검색하는데, BETWEEN과 AND를 사용해 칼럼의 범위를 지정할 수 있다. MariaDB [business]> SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | .5 ton anvil | 5.99 | | 1 ton anvil | 9.99 | | Oil can | 8.99 | | TNT (5 sticks) | 10.00 | | Bird seed | 10.00 | +----------------+------------+ # 테이블에서 세 개의 칼럼 정보를 검색하는데, 두 개의 조건이 공통적으로 일치된 정보만 검색한다. MariaDB [business]> SELECT prod_id, prod_price, prod_name, vend_id FROM products -> WHERE vend_id = 1003 AND prod_price <= 10 -> ORDER BY prod_price; +---------+------------+----------------+---------+ | prod_id | prod_price | prod_name | vend_id | +---------+------------+----------------+---------+ | TNT1 | 2.50 | TNT (1 stick) | 1003 | | FC | 2.50 | Carrots | 1003 | | SLING | 4.49 | Sling | 1003 | | TNT2 | 10.00 | TNT (5 sticks) | 1003 | | FB | 10.00 | Bird seed | 1003 | +---------+------------+----------------+---------+ # 키워드 OR은 두 칼럼 중 한 가지라도 일치하는 정보를 지정할 때 사용하고, AND는 두 칼럼 모두 일치하는 정보 지정 MariaDB [business]> SELECT prod_name,prod_price,vend_id FROM products -> WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10; +----------------+------------+---------+ | prod_name | prod_price | vend_id | +----------------+------------+---------+ | TNT (5 sticks) | 10.00 | 1003 | | Bird seed | 10.00 | 1003 | | Safe | 50.00 | 1003 | | Detonator | 13.00 | 1003 | +----------------+------------+---------+ # 키워드 NOT IN은 그 뒤에 오는 값을 검색 조건에서 제외할 때 사용 MariaDB [business]> SELECT prod_name,prod_price,vend_id FROM products -> WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name; +--------------+------------+---------+ | prod_name | prod_price | vend_id | +--------------+------------+---------+ | .5 ton anvil | 5.99 | 1001 | | 1 ton anvil | 9.99 | 1001 | | 2 ton anvil | 14.99 | 1001 | | JetPack 1000 | 35.00 | 1005 | | JetPack 2000 | 55.00 | 1005 | +--------------+------------+---------+ # 키워드 LIKE와 와일드카드 '%'를 사용해 검색 조건을 지정하는데, 여기선 jet로 시작되는 단어가 포함된 모든 로우 정보를 보여준다. MariaDB [business]> SELECT prod_id, prod_name FROM products -> WHERE prod_name LIKE 'jet%'; +---------+--------------+ | prod_id | prod_name | +---------+--------------+ | JP1000 | JetPack 1000 | | JP2000 | JetPack 2000 | +---------+--------------+ # 위와 동일한데 와일드ㅏ드 '%'를 사용해 첫 글자가 's', 마지막 글자 'e'로 끝나는 모든 로우 정보를 보여준다. MariaDB [business]> SELECT prod_id, prod_name FROM products -> WHERE prod_name LIKE 's%e'; +---------+-----------+ | prod_id | prod_name | +---------+-----------+ | SAFE | Safe | +---------+-----------+ # 어더스코어(_는 단지 한글자 또는 한 숫자만 일치하는 경우 사용한다. 여기에선 5개의 언더스코어가 사용됐으므로 다섯 글자로 된 이름을 검색한다. MariaDB [business]> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_____'; +---------+-----------+ | prod_id | prod_name | +---------+-----------+ | FU1 | Fuses | | SLING | Sling | +---------+-----------+ | cs |
d. 함수와 GROUP 사용
SELECT 구문에서 효과적인 데이터 검색을 위해 사용되는 함수와 GROUP절을 추가해 사용하는 방법은 다음과 같다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | # AVG 함수는 칼럼 이름과 함께 사용돼 칼럼의 평균값을 구하기 위해 사용하고, AS는 칼럼의 이름을 변경한다. MariaDB [business]> SELECT AVG(prod_price) AS price FROM products WHERE vend_id = 1001; +-----------+ | price | +-----------+ | 10.323333 | +-----------+ # COUNT 함수는 칼럼 결과의 숫자를 세기 위해 사용된다. MariaDB [business]> SELECT COUNT(DISTINCT prod_price) AS price FROM products; +-------+ | price | +-------+ | 12 | +-------+ # MAX 함수는 칼럼의 최댓값을 구하기 위해 사용된다. MariaDB [business]> SELECT MAX(prod_price) AS price FROM products; +-------+ | price | +-------+ | 55.00 | +-------+ # MIN 함수는 칼럼의 최솟값을 구하기 위해 사용된다. MariaDB [business]> SELECT MIN(prod_price) AS price FROM products; +-------+ | price | +-------+ | 2.50 | +-------+ # SUM 함수는 칼럼의 합계를 구하기 위해 사용된다. MariaDB [business]> SELECT SUM(prod_price) AS price FROM products; +--------+ | price | +--------+ | 225.87 | +--------+ # 여러 함수를 동시에 사용해 값을 구할 수 있다. MariaDB [business]> SELECT COUNT(*) AS items, MIN(prod_price) AS price_Min, -> MAX(prod_price) AS price_Max, AVG(prod_price) AS price_Avg -> FROM products; +-------+-----------+-----------+-----------+ | items | price_Min | price_Max | price_Avg | +-------+-----------+-----------+-----------+ | 14 | 2.50 | 55.00 | 16.133571 | +-------+-----------+-----------+-----------+ # GROUP BY를 사용하면 한 칼럼 내의 값을 그룹별로 묶어 검색할 수 있다. MariaDB [business]> SELECT vend_id, MIN(prod_price), MAX(prod_price), -> AVG(prod_price) FROM products GROUP BY vend_id; +---------+-----------------+-----------------+-----------------+ | vend_id | MIN(prod_price) | MAX(prod_price) | AVG(prod_price) | +---------+-----------------+-----------------+-----------------+ | 1001 | 5.99 | 14.99 | 10.323333 | | 1002 | 3.42 | 8.99 | 6.205000 | | 1003 | 2.50 | 50.00 | 13.212857 | | 1005 | 35.00 | 55.00 | 45.000000 | +---------+-----------------+-----------------+-----------------+ # GROUP BY절에 조건을 사용할 경우 HAVING절을 사용할 수 있다. MariaDB [business]> SELECT vend_id, MIN(prod_price), MAX(prod_price) -> FROM products GROUP BY vend_id HAVING MIN(prod_price) > 3 AND MAX(prod_price) < 30; +---------+-----------------+-----------------+ | vend_id | MIN(prod_price) | MAX(prod_price) | +---------+-----------------+-----------------+ | 1001 | 5.99 | 14.99 | | 1002 | 3.42 | 8.99 | +---------+-----------------+-----------------+ # GROUP BY절에 WITH ROLLUP을 사용하면 다른 검색 조건을 추가하지 않고도 그 칼럼에 대한 합계를 구할 수 있다. MariaDB [business]> SELECT vend_id,SUM(prod_price) FROM products -> GROUP BY vend_id WITH ROLLUP; +---------+-----------------+ | vend_id | SUM(prod_price) | +---------+-----------------+ | 1001 | 30.97 | | 1002 | 12.41 | | 1003 | 92.49 | | 1005 | 90.00 | | NULL | 225.87 | +---------+-----------------+ | cs |
4.3 UPDATE 구문 사용
UPDATE는 키워드 SET과 함께 기존 정보를 변경하기 위해 사용되는 구문이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | # cust_id=1000에 해당하는 로우 정보를 확인한다. MariaDB [helloworld]> SELECT * FROM customers WHERE cust_id = 1000; +---------+-----------+--------------+----------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+-----------+--------------+----------------+----------+ | 1000 | John Kim | Busan | john@naver.com | 123458 | +---------+-----------+--------------+----------------+----------+ # customers 테이블 내의 칼럼 cust_name의 정보를 키워드 SET를 이용해 변경하는데, cust_id=1000이어야 한다. MariaDB [helloworld]> UPDATE customers SET cust_name = "Chul Jeong" WHERE cust_id = 1000; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # cust_name의 John Kim -> Chul Jeong로 변경된 것을 확인할 수 있다. MariaDB [helloworld]> SELECT * FROM customers WHERE cust_id = 1000; +---------+------------+--------------+----------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+------------+--------------+----------------+----------+ | 1000 | Chul Jeong | Busan | john@naver.com | 123458 | +---------+------------+--------------+----------------+----------+ # 칼럼 cust_address의 정보를 WHERE절에 cust_zip 값을 지정해 변경한다. MariaDB [helloworld]> UPDATE customers SET cust_address = "Daegu" -> WHERE cust_zip = 123456; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 MariaDB [helloworld]> SELECT * FROM customers WHERE cust_zip = 123456; +---------+------------+--------------+-----------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+------------+--------------+-----------------+----------+ | 1004 | Chulsu Cho | Daegu | minsu@naver.com | 123456 | | 1006 | Namo Kim | Daegu | namo@daum.net | 123456 | | 1007 | Sunny Lee | Daegu | sunny@naver.com | 123456 | | 1009 | | Daegu | | 123456 | +---------+------------+--------------+-----------------+----------+ | cs |
4.4 DELETE와 TRUNCATE 사용
테이블에 저장돼 있는 데이터를 삭제하는 경우 사용할 수 있는 구문은 DELETE와 TRUNCATE다. 차이점은 DELETE의 경우 테이블 내에 있는 전체 데이터의 삭제 및 삭제하고자 하는 특정 데이터를 지정해 일부 데이터의 삭제를 지원하지만, TRUNCATE의 경우 테이블 내의 모든 데이터를 삭제하는 경우에만 삭제한다는 점이다.
1 2 3 4 5 6 7 8 9 10 11 12 | # 명령어 DELETE를 사용해 customers 테이블에서 칼럼 cust_id=1010에 해당하는 로우는 제거한다. MariaDB [helloworld]> DELETE FROM customers WHERE cust_id = 1010; Query OK, 1 row affected (0.01 sec) # 검색해보면 비어있다는 메시지를 확인 할 수 있다. MariaDB [helloworld]> SELECT * FROM customers WHERE cust_id = 1010; Empty set (0.00 sec) # 테이블 내의 모든 데이터를 삭제할 경우 사용한다. MariaDB [helloworld]> DELETE FROM customers; Query OK, 0 rows affected (0.00 sec) # DELETE처럼 테이블 내의 데이터를 모두 삭제하기 위해 사용한다. MariaDB [helloworld]> TRUNCATE TABLE customers; Query OK, 0 rows affected (0.00 sec) | cs |