요약
mysql
SHOW DATABASES;
USE database_name;
SHOW TABLES;
DESC table_name;
SHOW TABLE STATUS;
SELECT * FROM table_name;
exit |
마리아DB 설치 명령
apt install mariadb-server 입력해주면 아래와 같이 실행된다.
root@ncpmicro:~# apt install mariadb-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
...
...
...
0 upgraded, 29 newly installed, 0 to remove and 19 not upgraded.
Need to get 24.0 MB of archives.
After this operation, 184 MB of additional disk space will be used.
Do you want to continue? [Y/n]
Y를 입력하면 한동안 설치 작업을 한다.
Get:1 http://kr.archive.ubuntu.com/ubuntu bionic/main amd64 mysql-common all 5.8+1.0.4 [7,308 B]
...
(중략)
...
Get:29 http://kr.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 mariadb-server all 1:10.1.48-0ubuntu0.18.04.1 [12.9 kB]
Fetched 24.0 MB in 7s (3,242 kB/s)
Preconfiguring packages ...
Selecting previously unselected package mysql-common.
(Reading database ... 126286 files and directories currently installed.)
...
...
Unpacking mariadb-server (1:10.1.48-0ubuntu0.18.04.1) ...
Setting up libhtml-tagset-perl (3.20-3) ...
...
...
Setting up mariadb-server-10.1 (1:10.1.48-0ubuntu0.18.04.1) ...
Setting up mariadb-server (1:10.1.48-0ubuntu0.18.04.1) ...
Processing triggers for libc-bin (2.27-3ubuntu1.4) ...
...
root@ncpmicro:~#
몇분 걸린듯 설치 끝 !!
시작하고 종료해 보기
mysql
exit
root@ncpmicro:~# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
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)]>
MariaDB [(none)]> exit
Bye
root@ncpmicro:~#
데이터베이스 명령어는 대문자 소문자를 가리지 않지만
가독성을 위해 대문자를 사용한다고 합니다.
데이터베이스 목록 보고 사용하기
SHOW DATABASES;
USE database;
root@ncpmicro:~# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
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)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> use mysql
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
MariaDB [mysql]>
사용할 데이터베이스 테이블 조회
SHOW TABLES;
MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
30 rows in set (0.00 sec)
MariaDB [mysql]>
테이블 속성 보기
DESC table_name;
MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | 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 | |
| password_expired | enum('N','Y') | NO | | N | |
| is_role | enum('N','Y') | NO | | N | |
| default_role | char(80) | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+-----------------------------------+------+-----+----------+-------+
46 rows in set (0.00 sec)
MariaDB [mysql]>
테이블 상태 조회
SHOW TABLE STATUS;
MariaDB [mysql]> show table status;
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+---------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+---------------------------------------------------+
| column_stats | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 4096 | 0 | NULL | 2021-08-26 15:26:50 | 2021-08-26 15:26:50 | NULL | utf8_bin | NULL | | Statistics on Columns |
| columns_priv | MyISAM | 10 | Fixed | 0 | 0 | 0 | 282037926664077311 | 4096 | 0 | NULL | 2021-08-26 15:27:19 | 2021-08-26 15:27:19 | 2021-08-26 15:27:19 | utf8_bin | NULL | | Column privileges |
...
...
| user | MyISAM | 10 | Dynamic | 1 | 72 | 72 | 281474976710655 | 4096 | 0 | NULL | 2021-08-26 15:27:31 | 2021-08-26 15:27:31 | NULL | utf8_bin | NULL | | Users and global privileges |
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+---------------------------------------------------+
테이블 데이터 조회하기
SELECT * FROM table;
MariaDB [mysql]> select * from help_category;
+------------------+-----------------------------------------------+--------------------+-----+
| help_category_id | name | parent_category_id | url |
+------------------+-----------------------------------------------+--------------------+-----+
| 1 | Geographic | 0 | |
| 2 | Polygon properties | 34 | |
| 3 | WKT | 34 | |
| 4 | Numeric Functions | 38 | |
| 5 | Plugins | 35 | |
| 6 | MBR | 34 | |
| 7 | Control flow functions | 38 | |
| 8 | Transactions | 35 | |
| 9 | Help Metadata | 35 | |
| 10 | Account Management | 35 | |
| 11 | Point properties | 34 | |
| 12 | Encryption Functions | 38 | |
| 13 | LineString properties | 34 | |
| 14 | Miscellaneous Functions | 38 | |
| 15 | Logical operators | 38 | |
| 16 | Functions and Modifiers for Use with GROUP BY | 35 | |
| 17 | Information Functions | 38 | |
| 18 | Comparison operators | 38 | |
| 19 | Bit Functions | 38 | |
| 20 | Table Maintenance | 35 | |
| 21 | User-Defined Functions | 35 | |
| 22 | Data Types | 35 | |
| 23 | Compound Statements | 35 | |
| 24 | Geometry constructors | 34 | |
| 25 | GeometryCollection properties | 1 | |
| 26 | Administration | 35 | |
| 27 | Data Manipulation | 35 | |
| 28 | Utility | 35 | |
| 29 | Language Structure | 35 | |
| 30 | Geometry relations | 34 | |
| 31 | Date and Time Functions | 38 | |
| 32 | WKB | 34 | |
| 33 | Procedures | 35 | |
| 34 | Geographic Features | 35 | |
| 35 | Contents | 0 | |
| 36 | Geometry properties | 34 | |
| 37 | String Functions | 38 | |
| 38 | Functions | 35 | |
| 39 | Data Definition | 35 | |
+------------------+-----------------------------------------------+--------------------+-----+
39 rows in set (0.00 sec)
MariaDB [mysql]>
요약
mysql
SHOW DATABASES;
USE database_name;
SHOW TABLES;
SHOW TABLE STATUS;
SELECT * FROM table_name;
exit |
