SQL (Structured Query Language) 예제 1
2022. 12. 4. 16:46ㆍ리눅스(Linux)
SQL (Structured Query Language)
$ SHOW , DESC 사용하기
MariaDB [mysql]> show databases; ( Maria DB내의 존재하는 Database 목록을 확인 )
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| mysql |
| performance_schema |
+-----------------------+
3 rows in set (0.00 sec)
MariaDB [mysql]> show tables; ( 현재 접속 중인 mysql DB가 가지고 있는 Table 목록 확인 )
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
--------- 생략 --------------
| user |
+---------------------------+
24 rows in set (0.00 sec)
- Table 목록 중 user , db Table의 기능에 대해 살펴 본다.
- user 테이블은 데이터베이스 사용자관리와 관련 되어있으며, db 테이블은 DB와 사용자간 연동(권한)에 관한 정보를 담고 있다.
MariaDB [mysql]> desc db; ( db Table의 Column 정보를 확인 )
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
- 실제 데이터 값(value)을 저장 시 Column의 속성에 맞추어 데이터를 저장해야 한다.
MariaDB [mysql]> desc user; ( user Table의 Column 정보를 확인 )
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)
- 실제 데이터 값(value)을 저장 시 Column의 속성에 맞추어 데이터를 저장해야 한다.
$ CREATE , USE , ALTER , DROP 사용하기
MariaDB [mysql]> create database testdb; ( 새로운 데이터 베이스 생성 )
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> show databases; ( 데이터 베이스 목록 확인 )
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
+-----------------------+
4 rows in set (0.00 sec)
MariaDB [mysql]> use testdb; ( 위에서 생성한 testdb로 작업공간 변경 )
Database changed
MariaDB [testdb]> show tables; ( 현재 생성 된 Table이 없으므로 아무 것도 표시가 되지 않는다. )
Empty set (0.00 sec)
MariaDB [testdb]> create table testtable;
ERROR 1113 (42000): A table must have at least 1 column ( 테이블 생성 시 반드시 1개 이상의 Column을 같이 생성해야 한다. )
MariaDB [testdb]> create table testtable (age int(10), name char(20));
Query OK, 0 rows affected (0.01 sec)
- Table 생성 시 Column을 2개를 생성했으며, age의 속성은 int 정수형 데이터 입력, name의 속성은 char 문자형 데이터 입력으로 정의
MariaDB [testdb]> show tables; ( 생성한 Table 목록 확인 )
+------------------+
| Tables_in_testdb |
+------------------+
| testtable |
+------------------+
1 row in set (0.00 sec)
MariaDB [testdb]> desc testtable;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| age | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [testdb]> alter table testtable add address varchar(100); ( Alter를 이용한 Column 추가 )
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> desc testtable;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| age | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [testdb]> alter table testtable modify address char(50); ( Address Column의 속성을 char로 변경 )
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> desc testtable;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| age | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| address | char(50) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [testdb]> alter table testtable change address test int (10); ( Address Column을 test로 이름을 변경 후 속성도 변경 )
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 변경 할 이름을 정의 후 속성도 반드시 같이 정의해야 한다, 같은 속성을 사용해도 이름만 변경은 불가능
MariaDB [testdb]> desc testtable;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| age | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| test | int(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [testdb]> alter table testtable drop test; ( test Column을 삭제 )
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> desc testtable;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| age | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
MariaDB [testdb]> drop table testtable; ( Table 삭제 )
Query OK, 0 rows affected (0.00 sec)
MariaDB [testdb]> show tables;
Empty set (0.00 sec)
MariaDB [testdb]> drop database testdb; ( DB 삭제 )
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)
MariaDB [mysql]> use mysql; ( 기본 관리 mysql DB로 작업 DB 변경 )
Database changed
MariaDB [mysql]> create user itbank@localhost identified by 'itbank'; ( Create를 이용하여 사용자 생성하기 )
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> create user test@'%' identified by 'itbank';
Query OK, 0 rows affected (0.01 sec)
- "%"의 의미는 모든 원격지에서 접속을 허용한다는 의미가 된다.
- "192.168.1.%"의 경우는 192.168.1.0 대역에서만 접속을 허용한다는 의미가 된다.
- "192.168.1.129"의 경우는 192.168.1.129 Host에서만 원격접속을 허용한다는 의미가 된다.
MariaDB [mysql]> select host, user, password from user;
+-----------+--------+-------------------------------------------+
| host | user | password |
+-----------+--------+-------------------------------------------+
| localhost | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| 127.0.0.1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| ::1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| % | test | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| localhost | itbank | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
+-----------+--------+-------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [mysql]> drop user itbank@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> drop user test@'%'; ( "%"의 의미는 Localhost가 아닌 다른 곳에서 접속을 허용 하겠다는 뜻이 된다. )
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| 127.0.0.1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| ::1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)
$ SELECT , INSERT , DELETE , UPDATE 사용하기
MariaDB [mysql]> select * from user; ( user Table의 Column 및 Value 확인 )
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| 127.0.0.1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| ::1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.00 sec)
- 모든 정보가 한번에 표시되어 확인이 어렵다, 원하는 정보만 보기 위해서는 " * " 대신 Column명을 지정해 준다.
MariaDB [mysql]> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| 127.0.0.1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| ::1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
+-----------+------+-------------------------------------------+
3 rows in set (0.01 sec)
- host, user, password 부분만 출력하여 확인
MariaDB [mysql]> insert into user (host, user, password) value ('localhost','itbank',password('itbank')); (사용자명: itbank , P/W: itbank생성 )
Query OK, 1 row affected, 4 warnings (0.01 sec)
- Column의 순서에 맞게 Value를 지정해야 한다.
- 사용자의 Password를 지정할 때에는 반드시 password 함수를 같이 사용하여, password를 암호화해서 저장한다.
MariaDB [mysql]> select host, user, password from user;
+-----------+--------+-------------------------------------------+
| host | user | password |
+-----------+--------+-------------------------------------------+
| localhost | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| 127.0.0.1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| ::1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| localhost | itbank | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
+-----------+--------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [mysql]> update user set password=password('test') where user='itbank'; ( itbank 사용자의 P/W 변경하기 )
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- where은 조건을 말하며, 지정 된 조건에 만족하는 Value만 수정이 이루어지게 된다.
MariaDB [mysql]> select host, user, password from user;
+-----------+--------+-------------------------------------------+
| host | user | password |
+-----------+--------+-------------------------------------------+
| localhost | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| 127.0.0.1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| ::1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| localhost | itbank | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-----------+--------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [mysql]> delete from user where user='itbank'; ( itbank 사용자 삭제 )
Query OK, 1 row affected (0.00 sec)
- 삭제 작업도 마찬 가지로 조건에 만족하는 대상만 삭제
MariaDB [mysql]> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| 127.0.0.1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| ::1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)
$ GRANT , REVOKE 사용하기
- "GRANT 권한부여, REVOKE 권한제거"
# flush privileges;
- 권한, 사용자 계정 정보등이 변경되었을 경우 사용
- 권한 설정 작업 시 권한 값 정보를 저장하는 정보를 초기화 후 변경 된 정보를 다시 메모리에 업로드하기 위해사용
- 자주 그리고 많이 사용할 경우 DB의 성능저하의 원인이 된다
# GRANT [ 허용할 권한 설정 ] on [DB NAME].[TABLE NAME] to [USER NAME]@[HOST] [with grant option];
- GRANT 기본 사용 형식
허용할 권한 설정
- all privileges ( 모든 권한 부여 )
- select, update, insert, delete ( 지정한 권한 부여 )
- select(name) ( 지정한 Table내의 특정 Column만 권한 부여 )
DB NAME : 권한 부여를 진행할 DB 지정, 특정 DB를 지정할 수 있으며, "*"을 이용하여 모든 DB를 지정 할 수도 있다.
TABLE NAME : 위에서 선택된 DB하위의 TABLE을 지정, 특정 TABLE을 지정할 수 있으며, "*"을 이용하여 모든 TABLE을 지정 할 수도 있다.
USER NAME : 권한 부여를 지정 할 사용자명
HOST : 권한 부여를 지정 할 사용자에 정의되어있는 HOST
with grant option : 상위 개체로부터 받은 권한을 다른 사용자에게 부여 할 수 있는 옵션
A->B->C 사용자가 있을 경우 A사용자의 Table을 B사용자가 참조하여 사용 중이고, C 사용자가 B 사용자의
Table을 확인하고 싶을 경우 With grant Option이 있어야 정상적으로 B사용자의 Table을 확인할 수 있다.
(우리는 간단한 모든 권한 부여 용도로 사용함.)
MariaDB [mysql]> show grants; ( 사용자의 권한을 확인 )
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*E1B74369B47EB3AD8EF858144E1A0364E3259329' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [mysql]> create database userdb; ( 권한부여 TEST를 진행 할 DB 생성 )
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| userdb |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [mysql]> use userdb;
Database changed
MariaDB [userdb]> create table usertable ( name char(10) not null, address varchar(50)); ( 권한부여 TEST를 진행 할 Table 생성 )
Query OK, 0 rows affected (0.00 sec)
- Not null은 필수 입력을 해야 하는 부분을 정의할 때 사용 한다.
MariaDB [userdb]> desc usertable;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | char(10) | NO | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
MariaDB [mysql]> grant all privileges on userdb.* to itbank@localhost identified by 'itbank'; ( 사용자 추가 + 권한 할당 동시 작업 하기 )
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select host,user,password from user;
+-----------+--------+-------------------------------------------+
| host | user | password |
+-----------+--------+-------------------------------------------+
| localhost | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| 127.0.0.1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| ::1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| localhost | itbank | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
+-----------+--------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [mysql]> show grants for itbank@localhost; ( 지정한 사용자의 권한 확인 반드시 host명도 같이 적는다. )
+---------------------------------------------------------------------------------------------------------------+
| Grants for itbank@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'itbank'@'localhost' IDENTIFIED BY PASSWORD '*E1B74369B47EB3AD8EF858144E1A0364E3259329' |
| GRANT ALL PRIVILEGES ON `userdb`.* TO 'itbank'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [mysql]> create user test@'%' identified by 'itbank'; ( 기존에 생성되어있던 사용자에게 권한 할당 )
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select host,user,password from user;
+-----------+--------+-------------------------------------------+
| host | user | password |
+-----------+--------+-------------------------------------------+
| localhost | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| % | test | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| 127.0.0.1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| ::1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| localhost | itbank | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
+-----------+--------+-------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [mysql]> grant all privileges on userdb.* to test@'%';
Query OK, 0 rows affected (0.00 sec)
- identified by 옵션을 같이 사용할 경우 권한 할당이 진행 되면서, 해당 사용자의 P/W가 변경이 되므로 주의한다 !!
MariaDB [mysql]> show grants for test@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for test@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*E1B74369B47EB3AD8EF858144E1A0364E3259329' |
| GRANT ALL PRIVILEGES ON `userdb`.* TO 'test'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [userdb]> grant select, insert, delete on userdb.* to itbank2@localhost identified by 'itbank'; ( itbank2 계정에 특정 권한만 부여 )
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> show grants for itbank2@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for itbank2@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'itbank2'@'localhost' IDENTIFIED BY PASSWORD '*E1B74369B47EB3AD8EF858144E1A0364E3259329' |
| GRANT SELECT, INSERT, DELETE ON `userdb`.* TO 'itbank2'@'localhost' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [userdb]> grant select(address), insert(name) on userdb.usertable to itbank3@localhost identified by 'itbank'; ( itbank3 계정에 Column address, name에 특정 권한 부여 )
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> show grants for itbank3@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for itbank3@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'itbank3'@'localhost' IDENTIFIED BY PASSWORD '*E1B74369B47EB3AD8EF858144E1A0364E3259329' |
| GRANT SELECT (address), INSERT (name) ON `userdb`.`usertable` TO 'itbank3'@'localhost' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [userdb]> grant all privileges on *.* to itbank4@localhost identified by 'itbank' with grant option; ( itbank4 계정에 Maria DB에 존재하는 모든 DB에 모든 권한 할당 )
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> show grants for itbank4@localhost;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for itbank4@localhost |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'itbank4'@'localhost' IDENTIFIED BY PASSWORD '*E1B74369B47EB3AD8EF858144E1A0364E3259329' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[ Revoke를 이용하여 권한 제거 ]
MariaDB [userdb]> revoke all on userdb.* from itbank@localhost; ( itbank 계정의 권한을 삭제 )
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> drop user itbank@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> show grants for itbank@localhost; ( 권한 제거 확인 ERROR Message가 나오면 정상적으로 계정삭제까지 완료 된 것이다.)
ERROR 1141 (42000): There is no such grant defined for user 'itbank' on host 'localhost'
MariaDB [userdb]> revoke all on userdb.* from test@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> drop user test@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> revoke all on userdb.* from itbank2@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> drop user itbank2@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> revoke select(address), insert(name) on userdb.usertable from itbank3@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> drop user itbank3@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> revoke all on *.* from itbank4@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> drop user itbank4@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [userdb]> drop database userdb
Query OK, 0 rows affected (0.00 sec)
$ SQL 연산자 활용
MariaDB [dbtest]> create database Sample;
Query OK, 1 row affected (0.00 sec)
MariaDB [dbtest]> use Sample;
Database changed
MariaDB [Sample]> create table tb ( No int(10), ID char(20), name char(20), date date, point int(100));
Query OK, 0 rows affected (0.05 sec)
- 순서번호 , ID , 이름 , 나이 , 가입일 , 포인트 ( date 제약조건 형식 : 년 - 월 - 일 )
MariaDB [Sample]> desc tb;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| No | int(10) | YES | | NULL | |
| ID | char(20) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| date | date | YES | | NULL | |
| point | int(100) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
MariaDB [Sample]> insert into tb value (1, "s1", "choi_1", "2020-03-20", 100);
Query OK, 1 row affected (0.01 sec)
MariaDB [Sample]> insert into tb value (2, "s2", "choi_2", "2020-03-20", 100);
Query OK, 1 row affected (0.00 sec)
MariaDB [Sample]> insert into tb value (3, "s3", "choi_3", "1995-03-20", 200);
Query OK, 1 row affected (0.00 sec)
MariaDB [Sample]> insert into tb value (4, "s4", "choi_4", "1995-03-20", 300);
Query OK, 1 row affected (0.00 sec)
MariaDB [Sample]> insert into tb value (5, "s5", "choi_5", "2000-01-01", 500);
Query OK, 1 row affected (0.00 sec)
MariaDB [Sample]> select * from tb;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
| 2 | s2 | choi_2 | 2020-03-20 | 100 |
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
+------+------+--------+------------+-------+
5 rows in set (0.00 sec)관계연산자
- "< , > , <= , >= , = , != , <>"
MariaDB [Sample]> select * from tb where No = 5;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
+------+------+--------+------------+-------+
1 row in set (0.00 sec)
- 회원번호 "5번" 사용자 검색
MariaDB [Sample]> select * from tb where No != 5;
MariaDB [Sample]> select * from tb where No <> 5;
MariaDB [Sample]> select * from tb where not No = 5;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
| 2 | s2 | choi_2 | 2020-03-20 | 100 |
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
+------+------+--------+------------+-------+
4 rows in set (0.00 sec)
- 회원번호가 "5번"이 아닌 사용자 검색
MariaDB [Sample]> select * from tb where No > 3 or No < 3;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
| 2 | s2 | choi_2 | 2020-03-20 | 100 |
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
+------+------+--------+------------+-------+
4 rows in set (0.00 sec)
- 회원번호가 "3번"보다 크거나 "3번"보다 작은 사용자 검색 ( 즉, 3번제외 전부 출력 )
논리 연산자
- and 논리곱 양쪽 모두 참일경우 참
- or 논리합 한쪽이라도 참일경우 참
- not 부정 참이면 거짓, 거짓이면 참
- between-and ~부터 ~까지 ( where 필드명 between 시작값 and 끝값 )
- in 연산자 ~중에 ( where 필드명 in (값1,값2,값3...) )
- like 문자열 포함 검색 ( where 필드명 like 검색어 )
- not null,null 데이터가 있거나 없거나
MariaDB [Sample]> select * from tb where point < 500 and point > 100;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
+------+------+--------+------------+-------+
2 rows in set (0.00 sec)
MariaDB [Sample]> select * from tb where point = 500 or point = 300;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
+------+------+--------+------------+-------+
2 rows in set (0.00 sec)
MariaDB [Sample]> select * from tb where not point = 500;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
| 2 | s2 | choi_2 | 2020-03-20 | 100 |
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
+------+------+--------+------------+-------+
4 rows in set (0.00 sec)
MariaDB [Sample]> select * from tb where date between '2000-01-01' and '2020-12-31';
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
| 2 | s2 | choi_2 | 2020-03-20 | 100 |
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
+------+------+--------+------------+-------+
3 rows in set (0.00 sec)
MariaDB [Sample]> select * from tb where No between 2 and 4;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 2 | s2 | choi_2 | 2020-03-20 | 100 |
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
+------+------+--------+------------+-------+
3 rows in set (0.00 sec)
MariaDB [Sample]> select * from tb where No in ( 1,3,5 );
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
+------+------+--------+------------+-------+
3 rows in set (0.00 sec)
MariaDB [Sample]> select * from tb where id like 's%';
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
| 2 | s2 | choi_2 | 2020-03-20 | 100 |
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
+------+------+--------+------------+-------+
5 rows in set (0.00 sec)
- 값% : 시작
MariaDB [Sample]> select * from tb where id like '%1';
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
+------+------+--------+------------+-------+
1 row in set (0.00 sec)
- %값 : 끝
MariaDB [Sample]> select * from tb where name like '%_3%';
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
+------+------+--------+------------+-------+
1 row in set (0.00 sec)
- %값% : 포함
MariaDB [Sample]> select * from tb where date like '%-01-%' or date like '%-06-%';
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
+------+------+--------+------------+-------+
1 row in set (0.00 sec)
- 가입일이 01월이거나 06월인 데이터 검색
MariaDB [Sample]> select * from tb where name is null;
Empty set (0.00 sec)
- name field가 비어있는 데이터 검색
MariaDB [Sample]> select * from tb where name is not null;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
| 2 | s2 | choi_2 | 2020-03-20 | 100 |
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
+------+------+--------+------------+-------+
5 rows in set (0.00 sec)
- name field에 데이터가 있을경우 조건 매치정렬 조건
- order by 필드명 { asc | desc }
- asc ( 오름차순 )
- desc ( 내림차순 )
※ 반드시 SQL문 가장 마지막에 작성한다.
MariaDB [Sample]> select * from tb order by date asc;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
| 2 | s2 | choi_2 | 2020-03-20 | 100 |
+------+------+--------+------------+-------+
5 rows in set (0.00 sec)
MariaDB [Sample]> select * from tb order by date desc;
+------+------+--------+------------+-------+
| No | ID | name | date | point |
+------+------+--------+------------+-------+
| 1 | s1 | choi_1 | 2020-03-20 | 100 |
| 2 | s2 | choi_2 | 2020-03-20 | 100 |
| 5 | s5 | choi_5 | 2000-01-01 | 500 |
| 3 | s3 | choi_3 | 1995-03-20 | 200 |
| 4 | s4 | choi_4 | 1995-03-20 | 300 |
+------+------+--------+------------+-------+
5 rows in set (0.00 sec)
[ as : 별칭 ]
MariaDB [Sample]> select No as 번호, name as 이름, id as 아이디 from tb;
+--------+--------+-----------+
| 번호 | 이름 | 아이디 |
+--------+--------+-----------+
| 1 | choi_1 | s1 |
| 2 | choi_2 | s2 |
| 3 | choi_3 | s3 |
| 4 | choi_4 | s4 |
| 5 | choi_5 | s5 |
+--------+--------+-----------+
5 rows in set (0.00 sec)
[ distinct : 중복제거 ]
MariaDB [Sample]> select distinct point from tb;
+-------+
| point |
+-------+
| 100 |
| 200 |
| 300 |
| 500 |
+-------+
[ 데이터 입력 시 현재 시간 및 날짜 자동입력 ]
MariaDB [Sample]> create table tb_2 ( No int(10), Name varchar(20), date timestamp not null default current_timestamp );
Query OK, 0 rows affected (0.00 sec)
MariaDB [Sample]> insert into tb_2 (No, Name) value ( 1, "Choi_1" );
Query OK, 1 row affected (0.00 sec)
MariaDB [Sample]> select * from tb_2;
+------+--------+---------------------+
| No | Name | date |
+------+--------+---------------------+
| 1 | Choi_1 | 2020-03-23 20:13:31 |
+------+--------+---------------------+
1 row in set (0.00 sec)'리눅스(Linux)' 카테고리의 다른 글
| [Centos 7] Linux Log 관리 (who, last, lastb, lastlog, tail, Psacct Tool, logrotate) (1) | 2022.12.04 |
|---|---|
| SQL (Structured Query Language) 예제 2 (0) | 2022.12.04 |
| [Centos 7] DB (Database) & DB Replication ( 읽기 전용 복제본 ) (0) | 2022.12.04 |
| [Centos 7] 보안 프로토콜(SSL/TLS) (0) | 2022.12.02 |
| 암호화 기초 알고리즘[ 대칭 키(비밀 키), 비대칭 키(공개 키), 키 교환, 해시 ] (0) | 2022.12.01 |