[Linux] 데이터베이스 SQL : DML(Data Manipulation Language)
📌INDEX
✔️ 데이터베이스란?
- 데이터를 효율적으로 저장/관리하기 위해 사용
- 1950년대에 미국에서 처음 사용(용어)
- 데이터베이스 종류
- 계층형 DB
- 관계형 DB
- 가장 광범위 하게 사용
- ex) 오라클 DB, MariaDB 등
- NoSQL
- ex) AWS DynamoDB 등
- 데이터 베이스의 특징
- 실시간 접근성
- 지속적인 변화
- 동시 공유
- 내용에 대한 참조
- 데이터 논리적 독립성
- 용어 설명
- DB : 데이터베이스
- DBMS : 데이터베이스 관리 시스템
- DBA : 데이터 베이스 관리자
- 테이블 : 데이터가 저장된 객체
- 컬럼(Column) : 테이블에서 데이터들의 속성이 같은 값
- 행(Row) : 특정 테이블에서 의미 있는 하나의 데이터 집합
- 필드 : 각가의 데이터 하나를 의미
- 레코드 : 여러 필드의 조합을 의미
✔️ 데이터 베이스 설치 및 설정
패키지 설치
- mariadb-server 혹은 mariadb, mariadb-client
- 오류가 날 때는 nmcli con up ens33
t@server ~]# yum -y install mariadb-server
서비스 활성화
[root@server ~]# systemctl start mariadb
방화벽 설정
[root@server ~]# firewall-cmd --add-service=mysql
success
기본 보안 설정
[root@server ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
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):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] n
... skipping.
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
설정파일
- /etc/my.cnf
[root@server ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d사용 시작
- mysqul -u [계정] -p
[root@server ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
✔️ SQL 문법
- 데이터 베이스에서 데이터 관리를 위해 사용하는 약속된 언어
- 검색/추가/제거 등 다양한 구문이 존재
🔹 SQL 문법의 분류 🔹 WHERE 조건 🔹 LIKE와 함께 사용하는 와일드 문자 ✔️ DML 구문 🔹 SELECT 사용 예 🔹 INSERT 사용 예 🔹 UPDATE 사용 예 🔹 DELETE 사용 예
SELECT 컬럼이름
FROM 테이블 이름
WHERE 조건문
ORDER BY 정렬방식 #ASC(오름차순), DESC(내림차순)MariaDB [mysql]> SELECT host
-> FROM user;
+-----------+
| host |
+-----------+
| 127.0.0.1 |
| ::1 |
| localhost |
+-----------+
3 rows in set (0.00 sec)MariaDB [mysql]> SELECT host,user,password,max_updates
-> FROM user
-> WHERE max_updates=0;
+-----------+------+-------------------------------------------+-------------+
| host | user | password | max_updates |
+-----------+------+-------------------------------------------+-------------+
| localhost | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | 0 |
| 127.0.0.1 | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | 0 |
| ::1 | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | 0 |
+-----------+------+-------------------------------------------+-------------+
3 rows in set (0.00 sec)MariaDB [mysql]> SELECT host,user,password,max_updates FROM user WHERE max_updates BETWEEN -1 AND 1;
+-----------+------+-------------------------------------------+-------------+
| host | user | password | max_updates |
+-----------+------+-------------------------------------------+-------------+
| localhost | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | 0 |
| 127.0.0.1 | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | 0 |
| ::1 | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | 0 |
+-----------+------+-------------------------------------------+-------------+
3 rows in set (0.01 sec)MariaDB [mysql]> SELECT host,user FROM user WHERE host LIKE 'localhost';
+-----------+------+
| host | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)MariaDB [mysql]> SELECT host,user FROM user WHERE host LIKE 'local%';
+-----------+------+
| host | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)
INSERT INTO 테이블 명 및 컬럼이름
VALUES 입력할 데이터MariaDB [mysql]> SELECT * FROM test;
Empty set (0.00 sec)
MariaDB [mysql]> INSERT INTO test
-> VALUES ('Lee', 1, 'Seoul', 'lee@test.example.com');
Query OK, 1 row affected (0.01 sec)
MariaDB [mysql]> SELECT * FROM test;
+------+------+--------+----------------------+
| name | uid | locate | email |
+------+------+--------+----------------------+
| Lee | 1 | Seoul | lee@test.example.com |
+------+------+--------+----------------------+
1 row in set (0.00 sec)MariaDB [mysql]> INSERT INTO test
-> VALUES ('Kim', 3, 'Busan' ,'kim@example.com');
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> SELECT * FROM test;
+------+------+--------+----------------------+
| name | uid | locate | email |
+------+------+--------+----------------------+
| Lee | 1 | Seoul | lee@test.example.com |
| Kim | 3 | Busan | kim@example.com |
+------+------+--------+----------------------+
2 rows in set (0.00 sec)
UPDATE 테이블명 및 컬럼이름
SET 새로 입력할 데이터
WHERE 수정할 위치MariaDB [mysql]> SELECT * FROM test;
+------+------+--------+----------------------+
| name | uid | locate | email |
+------+------+--------+----------------------+
| Lee | 1 | Seoul | lee@test.example.com |
| Kim | 3 | Busan | kim@example.com |
+------+------+--------+----------------------+
2 rows in set (0.00 sec)
MariaDB [mysql]> UPDATE test
-> SET name = 'Park'
-> WHERE uid=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mysql]> SELECT * FROM test;
+------+------+--------+----------------------+
| name | uid | locate | email |
+------+------+--------+----------------------+
| Lee | 1 | Seoul | lee@test.example.com |
| Park | 3 | Busan | kim@example.com |
+------+------+--------+----------------------+
2 rows in set (0.00 sec)
DELETE FROM 테이블이름
WHERE 조건MariaDB [mysql]> SELECT * FROM test;
+------+------+--------+----------------------+
| name | uid | locate | email |
+------+------+--------+----------------------+
| Lee | 1 | Seoul | lee@test.example.com |
| Park | 3 | Busan | kim@example.com |
+------+------+--------+----------------------+
2 rows in set (0.00 sec)
MariaDB [mysql]> DELETE FROM test
-> WHERE uid=3;
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> SELECT * FROM test;
+------+------+--------+----------------------+
| name | uid | locate | email |
+------+------+--------+----------------------+
| Le | 1 | Seoul | lee@test.example.com |
+------+------+--------+----------------------+
1 row in set (0.00 sec)