[Linux - 리눅스 / Ubuntu - 우분투] MariaDB 사용 및 관리하기 - select, update, insert into, where, alter, explain, mysqladmin

MaridDB

- MariaDB 를 사용하기 위한 명령은 'mysql' - 'mariadb' 가 아니니 주의 - 바로 'mysql' 명령으로 접속시 오류 메시지 출력 ========================================================================== sjw@sjw-HP-Mini-110-3500:~$ mysql ERROR 1045 (28000): Access denied for user 'sjw'@'localhost' (using password: NO) sjw@sjw-HP-Mini-110-3500:~$ ========================================================================== - MariaDB를 설치할 때 root 계정의 암호를 설정 - root 계정을 지정하고 암호를 입력해야 MariaDB에 접속할 수 있음 ========================================================================== sjw@sjw-HP-Mini-110-3500:~$ mysql -u root -p Enter password: Welcome to the MariaDB monitor.  Commands end with ; or \g. Your MariaDB connection id is 38 Server version: 10.0.17-MariaDB-1~trusty-log mariadb.org binary distribution Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> ==========================================================================   - MariaDB 의 기본 프롬프트는 'MariaDB [(none)]' - 사용할 DB를 선택하면 none 이 DB으 이름으로 바뀜 - MaridDB에서 종료하려면 'exit' 를 입력

데이터베이스 목록 확인 : show databases

- 데이터베이스 목록을 확인하는 명령은  show databases

========================================================================== MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]>   ==========================================================================

데이터베이스 생성하기

- SQL 명령이 오류 없이 실행되면 'Query OK' 라는 메시지가 출력 - show databases 명령으로 확인

========================================================================== MariaDB [(none)]> create database st_db; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | st_db              | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> ==========================================================================

데이터베이스 사용하기

- 데이터베이스에 테이블을 만드는 작업을 하려면 사용할 데이터베이스를 'use' 명령으로 지정해야함 - 'use st_db' 사용시  * 프롬프트의 (none)이 st_db로 바뀜  * 현재 사용하고 있는 데이터베이스가 'st_db' 임을 알려줌

========================================================================== MariaDB [(none)]> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | st_db              | +--------------------+ 4 rows in set (0.01 sec) MariaDB [(none)]> use st_db Database changed MariaDB [st_db]> ==========================================================================

테이블 생성하기

1) ========================================================================== MariaDB [st_db]> show tables; Empty set (0.00 sec) MariaDB [st_db]> ========================================================================== 2) - 테이블을 생성할 때 기본 문제 집합을 'utf8'로 설정해야 한글이 깨지지 않음 - 기몬 문자 집합의 설정은 필드 정보 다음에 'default charset=utf8' 을 추가 ========================================================================== MariaDB [st_db]> create table st_info (ST_ID int, NAME varchar(20), DEPT varchar(25)) default charset=utf8; Query OK, 0 rows affected (0.31 sec) MariaDB [st_db]> ========================================================================== 3) - 이 테이블에는 문자 집합을 지정하지 않음 ========================================================================== MariaDB [st_db]> create table st_grade (ST_ID int, Linux int, DB int); Query OK, 0 rows affected (0.53 sec) MariaDB [st_db]> ========================================================================== 4) - 테이블이 정상적으로 생성되었는지 'show tables' 명령을 사용하여 확인 ========================================================================== MariaDB [st_db]> show tables; +-----------------+ | Tables_in_st_db | +-----------------+ | st_grade        | | st_info         | +-----------------+ 2 rows in set (0.00 sec) MariaDB [st_db]> ==========================================================================

테이블 구조 확인하기

- 테이블의 구조를 확인하는 명령은 'explain 테이블명'

========================================================================== MariaDB [st_db]> explain st_info; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ST_ID | int(11)     | YES  |     | NULL    |       | | NAME  | varchar(20) | YES  |     | NULL    |       | | DEPT  | varchar(25) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) MariaDB [st_db]> explain st_grade; +-------+---------+------+-----+---------+-------+ | Field | Type    | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | ST_ID | int(11) | YES  |     | NULL    |       | | Linux | int(11) | YES  |     | NULL    |       | | DB    | int(11) | YES  |     | NULL    |       | +-------+---------+------+-----+---------+-------+ 3 rows in set (0.01 sec) MariaDB [st_db]> ==========================================================================

기본 키 추가하기

1) - 'Null' 은 값이 없다는 것으로 기본 키 값이 Null 을 허용하면 이상 - 'Null' 을 허용하지 않도록 수정 ========================================================================== MariaDB [st_db]> alter table st_info modify ST_ID int not Null; Query OK, 0 rows affected (0.60 sec)               Records: 0  Duplicates: 0  Warnings: 0 MariaDB [st_db]> alter table st_grade modify ST_ID int not Null; Query OK, 0 rows affected (1.30 sec)               Records: 0  Duplicates: 0  Warnings: 0 MariaDB [st_db]> ========================================================================== 제약 조건 추가, 삭제 ========================================================================== 추가 ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명); 삭제 ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명; 참고 : http://runtoyourdream.tistory.com/245 ========================================================================== 2) - 'add constraint' => 새로운 제약 조건 추가 참고 : http://www.cubrid.org/ko_manual90/entry/ADD%20CONSTRAINT%20%EC%A0%88 참고 : http://runtoyourdream.tistory.com/245 ========================================================================== MariaDB [st_db]> alter table st_info add constraint pk_stinfo primary key(ST_ID); Query OK, 0 rows affected (0.37 sec) Records: 0  Duplicates: 0  Warnings: 0 MariaDB [st_db]> alter table st_grade add constraint pk_stgrade primary key(ST_ID); Query OK, 0 rows affected (0.61 sec) Records: 0  Duplicates: 0  Warnings: 0 MariaDB [st_db]> ========================================================================== 3) - 'explain '명령으로 테이블 구조 재확인 - ST_ID 필드의 Null을 허용하지 않고 기본 키로 설정되어 있는것을 확인할 수 있음 ========================================================================== MariaDB [st_db]> explain st_info; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ST_ID | int(11)     | NO   | PRI | NULL    |       | | NAME  | varchar(20) | YES  |     | NULL    |       | | DEPT  | varchar(25) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) MariaDB [st_db]> explain st_grade; +-------+---------+------+-----+---------+-------+ | Field | Type    | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | ST_ID | int(11) | NO   | PRI | NULL    |       | | Linux | int(11) | YES  |     | NULL    |       | | DB    | int(11) | YES  |     | NULL    |       | +-------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec) MariaDB [st_db]> ========================================================================== 4) - 레코드 입력(데이터 추가) ========================================================================== MariaDB [st_db]> insert into st_info values(201401, '이길동', 'Game'); Query OK, 1 row affected (0.09 sec) MariaDB [st_db]> insert into st_info values(201402, '김길동', 'Computer'); Query OK, 1 row affected (0.04 sec) MariaDB [st_db]> insert into st_info values(201403, '홍길동', 'Game'); Query OK, 1 row affected (0.05 sec) MariaDB [st_db]> insert into st_grade values(201401, 90, 80); Query OK, 1 row affected (0.04 sec) MariaDB [st_db]> insert into st_grade values(201401, 70, 95); ERROR 1062 (23000): Duplicate entry '201401' for key 'PRIMARY' MariaDB [st_db]> insert into st_grade values(201402, 70, 95); Query OK, 1 row affected (0.05 sec) MariaDB [st_db]> ========================================================================== 5) - 테이블의 전체 레코드 검색 : 'st_info' 테이블의 전체 레코드를 검색 ========================================================================== MariaDB [st_db]> select * from st_info; +--------+-----------+----------+ | ST_ID  | NAME      | DEPT     | +--------+-----------+----------+ | 201401 | 이길동    | Game     | | 201402 | 김길동    | Computer | | 201403 | 홍길동    | Game     | +--------+-----------+----------+ 3 rows in set (0.03 sec) MariaDB [st_db]> ========================================================================== 6) - 학번이 201401인 학생의 이름과 학과를 검색 ========================================================================== MariaDB [st_db]> select * from st_info; +--------+-----------+----------+ | ST_ID  | NAME      | DEPT     | +--------+-----------+----------+ | 201401 | 이길동    | Game     | | 201402 | 김길동    | Computer | | 201403 | 홍길동    | Game     | +--------+-----------+----------+ 3 rows in set (0.03 sec) MariaDB [st_db]> select NAME, DEPT from st_info where ST_ID=201401; +-----------+------+ | NAME      | DEPT | +-----------+------+ | 이길동    | Game | +-----------+------+ 1 row in set (0.03 sec) MariaDB [st_db]> select NAME from st_info where ST_ID=201401; +-----------+ | NAME      | +-----------+ | 이길동    | +-----------+ 1 row in set (0.00 sec) MariaDB [st_db]> ========================================================================== 7) 학번이 201401 인 학생의 리눅스 성적을 검색 ========================================================================== MariaDB [st_db]> select Linux from st_grade where ST_ID=201401; +-------+ | Linux | +-------+ |    90 | +-------+ 1 row in set (0.00 sec) MariaDB [st_db]> ========================================================================== 8) - 학번이 201401인 학생의 이름과 학과, DB 성적을 한 번에 검색 - 학생의 이름과 학과(st_info 테이블), DB 성적(st_grade 테이블) ========================================================================== MariaDB [st_db]> select st_info.NAME, st_info.DEPT, st_grade.DB     -> from st_info, st_grade     -> where st_info.ST_ID=201401 and st_grade.ST_ID=201401; +-----------+------+------+ | NAME      | DEPT | DB   | +-----------+------+------+ | 이길동    | Game |   80 | +-----------+------+------+ 1 row in set (0.00 sec) MariaDB [st_db]> ==========================================================================

데이터 수정하기

- 예를 들어 학번이 201401인 학생의 DB 성적을 80점에서 90점으로 수정해야할 경우   update 문을 사용

========================================================================== MariaDB [st_db]> select * from st_grade where ST_ID=201401; +--------+-------+------+ | ST_ID  | Linux | DB   | +--------+-------+------+ | 201401 |    90 |   80 | +--------+-------+------+ 1 row in set (0.00 sec) MariaDB [st_db]> update st_grade set DB=90 where ST_ID=201401; Query OK, 1 row affected (0.05 sec) Rows matched: 1  Changed: 1  Warnings: 0 MariaDB [st_db]> select * from st_grade where ST_ID=201401; +--------+-------+------+ | ST_ID  | Linux | DB   | +--------+-------+------+ | 201401 |    90 |   90 | +--------+-------+------+ 1 row in set (0.00 sec) MariaDB [st_db]> ==========================================================================

Maria DB 관리하기

- MariaDB를 관리하기 위해 사용하는 명령은 'mysqladmin' - MariaDB 계정의 암호를 설정하고 상태도 확인가능

mysqladmin

- 기능 :  MariaDB 서버를 관리 - 형식 : mysqladmin 명령 - 명령 :  * version : MariaDB 서버의 버전 정보를 출력  * status : MariaDB 서버의 현재 상태 정보를 출력  * password 암호 : 계정의 암호를 지정 - 사용예 : mysqladmin password "123456"

MariaDb 상태 정보 출력

- 'mysqladmin status' 명령은 MySQL 서버의 현재 상태 정보를 출력 - 암호가 설정되어 있으므로 다음과 같이 실행해야함   ========================================================================== sjw@sjw-HP-Mini-110-3500:~$ mysqladmin status mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'sjw'@'localhost' (using password: NO)' sjw@sjw-HP-Mini-110-3500:~$ mysqladmin -u root -p status Enter password: Uptime: 23332  Threads: 1  Questions: 143  Slow queries: 0  Opens: 23  Flush tables: 1  Open tables: 80  Queries per second avg: 0.006 sjw@sjw-HP-Mini-110-3500:~$ ==========================================================================

상태 정보로 출력되는 주요 항목  * Uptime : 서버가 동작한 시간을 초 단위로 표시  * Threads : 현재 동작 중인 MySQL 서버 쓰레드 수  * Questions : 서버가 동작한 이후 처리한 질의 수  * Slow queries : 일정 시간보다 처리 시간이 길어진 질의 수  * Opens : 서버가 열었던 테이블 수  * Flush : flush, refresh, reload 명령을 수행한 횟수  * Open tables : 현재 열려 있는 테이블 수

MariaDB 버전 정보 출력

- 'mysqladmin version' 명령은 MariaDB 서버의 버전 정보를 출력

========================================================================== sjw@sjw-HP-Mini-110-3500:~$ mysqladmin -u root -p version Enter password: mysqladmin  Ver 9.1 Distrib 10.0.17-MariaDB, for debian-linux-gnu on x86_64 Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Server version        10.0.17-MariaDB-1~trusty-log Protocol version    10 Connection        Localhost via UNIX socket UNIX socket        /var/run/mysqld/mysqld.sock Uptime:            6 hours 35 min 7 sec Threads: 1  Questions: 144  Slow queries: 0  Opens: 23  Flush tables: 1  Open tables: 80  Queries per second avg: 0.006 sjw@sjw-HP-Mini-110-3500:~$ ==========================================================================

MariaDB 서버 암호 설정하기

- mysqladmin password 명령으로 MariaDB 서버에 암호를 설정할 수 있음

========================================================================== sjw@sjw-HP-Mini-110-3500:~$ mysqladmin -u root -p password '123456' Enter password: ==========================================================================