mysql 을 사용할때 쓰이는 기본적인 명령어 모음집
◈ 데이터베이스 보기
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| tc |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql>
◈ 데이터베이스 생성
mysql> create database a1net;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| a1net |
| mysql |
| tc |
| test |
+--------------------+
5 rows in set (0.00 sec)
◈ 데이터베이스 삭제 (test123 데이터베이스 생성 후 삭제)
mysql> create database test123;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| a1net |
| mysql |
| tc |
| test |
| test123 |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database test123;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| a1net |
| mysql |
| tc |
| test |
+--------------------+
5 rows in set (0.00 sec)
◈ 데이터 베이스 선택
mysql> use a1net;
Database changed
◈ 데이터베이스 설정 확인 (DB 선택 후 사용)
mysql> status
--------------
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.1.58, for pc-linux-gnu (i686) using EditLine wrapper
Connection id: 2
Current database: tc
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.58-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: euckr
Db characterset: euckr
Client characterset: euckr
Conn. characterset: euckr
UNIX socket: /tmp/mysql.sock
Uptime: 5 days 3 hours 8 min 33 sec
Threads: 1 Questions: 43 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 10 Queries per second avg: 0.0
--------------
◈ 테이블 생성
mysql> create table test(name varchar(20), id varchar(20), password varchar(20));
Query OK, 0 rows affected (0.02 sec)
◈ 테이블 보기
mysql> show tables;
+-----------------+
| Tables_in_a1net |
+-----------------+
| test |
+-----------------+
1 row in set (0.00 sec)
◈ 테이블 구조 보기
mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| id | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
◈ 테이블에 새로운 열 추가
mysql> alter table test add email varchar(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| id | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
◈ 테이블에 새로운 열 제거
mysql> alter table test drop email;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| id | varchar(20) | YES | | NULL | |
| password| varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
◈ 열 이름을 변경 (id -> userid)
mysql> alter table test change id userid varchar(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| userid | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
◈ 테이블 삭제 (test2 테이블 생성 후 삭제)
mysql> create table test2(name varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_a1net |
+-----------------+
| test |
| test2 |
+-----------------+
2 rows in set (0.00 sec)
mysql> drop table test2;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_a1net |
+-----------------+
| test |
+-----------------+
1 row in set (0.00 sec)
◈ 테이블에 데이터 삽입
mysql> insert into test (name, userid, password) values ('에이원','a1net','1234');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+--------+--------+----------+
| name | userid | password|
+--------+--------+----------+
| 에이원 | a1net | 1234 |
+--------+--------+----------+
1 row in set (0.00 sec)
◈ 테이블에서 검색 (검색테스트를 위해 데이터를 추가로 삽입하고 serid 에서 a1net 을검색)
mysql> insert into test (name, userid, password) values ('테스트','test','1234');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+--------+--------+----------+
| name | userid | password|
+--------+--------+----------+
| 에이원 | a1net | 1234 |
| 테스트 | test | 1234 |
+--------+--------+----------+
2 rows in set (0.00 sec)
mysql> select * from test where userid = 'a1net';
+--------+--------+----------+
| name | userid | password|
+--------+--------+----------+
| 에이원 | a1net | 1234 |
+--------+--------+----------+
1 row in set (0.00 sec)
◈ 테이블 데이터 변경( name 에이원의 password 를 456으로 변경)
mysql> select * from test;
+--------+--------+----------+
| name | userid | password|
+--------+--------+----------+
| 에이원 | a1net | 1234 |
| 테스트 | test | 1234 |
+--------+--------+----------+
2 rows in set (0.00 sec)
mysql> update test set password = '456' where name = '에이원';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test;
+--------+--------+----------+
| name | userid | password|
+--------+--------+----------+
| 에이원 | a1net | 456 |
| 테스트 | test | 1234 |
+--------+--------+----------+
2 rows in set (0.00 sec)
◈ 유저생성 ()
mysql> create user 'a1net'@'localhost' identified by '1234';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# /usr/local/mysql/bin/mysql -u a1net -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.58-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
◈ 유저 권한 보기(a1net)
mysql> show grants for 'a1net'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for a1net@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'a1net'@'localhost' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
◈ 권한 설정
mysql> grant all privileges on a1net.* to 'a1net'@'localhost' identified by '1234';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'a1net'@'localhost';
+----------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------ -------------+
| Grants fora1net@localhost |
+----------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------ -------------+
| GRANT USAGE ON *.* TO 'a1net'@'localhost' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C88 86EBFCF' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLE S, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `a1net`.* TO 'a1net' @'localhost' |
+----------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------ -------------+
2 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# /usr/local/mysql/bin/mysql -u a1net -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.58-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| a1net |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> use a1net;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_a1net |
+-----------------+
| test |
+-----------------+
1 row in set (0.00 sec)
◈ 유저 리스트 확인
mysql> use mysql;
Database changed
mysql> select user, host, password from user;
+-------+-----------+-------------------------------------------+
| user | host | password |
+-------+-----------+-------------------------------------------+
| root | localhost | *092612CB18582D7A9C446B1A3FB02A648845FAF3 |
| a1net | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF |
+-------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
◈ 유저 삭제
mysql> create user 'test123'@'localhost' identified by '1234';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user, host, password from user;
+---------+-----------+-------------------------------------------+
| user | host | password |
+---------+-----------+-------------------------------------------+
| root | localhost | *092612CB18582D7A9C446B1A3FB02A648845FAF3 |
| a1net | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF |
| test123 | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF |
+---------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> delete from user where user='test123';
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user, host, password from user;
+-------+-----------+-------------------------------------------+
| user | host | password |
+-------+-----------+-------------------------------------------+
| root | localhost | *092612CB18582D7A9C446B1A3FB02A648845FAF3 |
| a1net | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF |
+-------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
'기술자료 > 기술운영자료' 카테고리의 다른 글
| MSSQL Server table 용량 체크 (0) | 2016.03.20 |
|---|---|
| ORACLE 유지보수 소개 (0) | 2016.03.20 |
| mysql import시 글자 깨지는 현상 (0) | 2016.03.20 |
| Mysql mysqlbinlog 복구 활용 (0) | 2016.03.20 |
| [ODBC]ACCESS 연결이 안될때 (0) | 2016.03.20 |
