mysql 기본 명령어 및 수정

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

 

 

mysql> 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
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

mysql>

mysql> CREATE DATABASE shopping_db
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| shopping_db        |
| test               |
+--------------------+
5 rows in set (0.00 sec)

 

 

mysql> USE shopping_db;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> DROP DATABASE shopping_db;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> CREATE DATABASE shopping_db;
Query OK, 1 row affected (0.00 sec)

mysql> USE shopping_db;
Database changed

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> CREATE TABLE customer (id CHAR(10), name VARCHAR(10), age INT(3), address VARCHAR(30));
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_shopping_db |
+-----------------------+
| customer              |
+-----------------------+
1 row in set (0.00 sec)

 

 

 

mysql> DROP TABLE customer;

mysql> CREATE DATABASE shopping_db;
Query OK, 1 row affected (0.00 sec)

mysql> USE shopping_db;
Database changed

mysql> CREATE TABLE customer (
    -> id CHAR(10),
    -> name VARCHAR(10),
    -> age INT(3),
    -> address VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> EXPLAIN customer;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | char(10)    | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| age     | int(3)      | YES  |     | NULL    |       |
| address | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> DESC customer;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | char(10)    | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| age     | int(3)      | YES  |     | NULL    |       |
| address | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

 

mysql> ALTER TABLE customer MODIFY name VARCHAR(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN customer;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | char(10)    | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| age     | int(3)      | YES  |     | NULL    |       |
| address | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

 

mysql> ALTER TABLE customer CHANGE name fullname VARCHAR(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN customer;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | char(10)    | YES  |     | NULL    |       |
| fullname | varchar(20) | YES  |     | NULL    |       |
| age      | int(3)      | YES  |     | NULL    |       |
| address  | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

 

mysql> ALTER TABLE customer ADD phone CHAR(13) AFTER address;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN customer;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | char(10)    | YES  |     | NULL    |       |
| fullname | varchar(20) | YES  |     | NULL    |       |
| age      | int(3)      | YES  |     | NULL    |       |
| address  | varchar(30) | YES  |     | NULL    |       |
| phone    | char(13)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE customer DROP age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

mysql> EXPLAIN customer;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | char(10)    | YES  |     | NULL    |       |
| fullname | varchar(20) | YES  |     | NULL    |       |
| address  | varchar(30) | YES  |     | NULL    |       |
| phone    | char(13)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE customer ADD no INT(4) FIRST;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN customer;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| no       | int(4)      | YES  |     | NULL    |       |
| id       | char(10)    | YES  |     | NULL    |       |
| fullname | varchar(20) | YES  |     | NULL    |       |
| address  | varchar(30) | YES  |     | NULL    |       |
| phone    | char(13)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

 

5 rows in set (0.00 sec)

mysql>

mysql> CREATE TABLE purchase (no INT(4), cust_id CHAR(10), date DATE, product VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------------+
| Tables_in_shopping_db |
+-----------------------+
| customer              |
| purchase              |
+-----------------------+
2 rows in set (0.00 sec)

mysql> explain purchase;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| no      | int(4)      | YES  |     | NULL    |       |
| cust_id | char(10)    | YES  |     | NULL    |       |
| date    | date        | YES  |     | NULL    |       |
| product | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> explain customer;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | char(10)    | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| age     | int(3)      | YES  |     | NULL    |       |
| address | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> INSERT INTO customer VALUE (
    -> "hong","Hong Gil Dong",22,"Kyounggi-do");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM customer;
+------+---------------+------+-------------+
| id   | name          | age  | address     |
+------+---------------+------+-------------+
| hong | Hong Gil Dong |   22 | Kyounggi-do |
+------+---------------+------+-------------+
1 row in set (0.00 sec)

mysql> SELECT id,name FROM customer;
+------+---------------+
| id   | name          |
+------+---------------+
| hong | Hong Gil Dong |
+------+---------------+
1 row in set (0.00 sec)

mysql> INSERT INTO customer VALUE (
    -> "dang","Dang Tang i",23,"Chung Book");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM customer;
+------+---------------+------+-------------+
| id   | name          | age  | address     |
+------+---------------+------+-------------+
| hong | Hong Gil Dong |   22 | Kyounggi-do |
| dang | Dang Tang i   |   23 | Chung Book  |
+------+---------------+------+-------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO customer VALUE (
    -> "ppuni","E PPuni",30,"Seoul");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUE (
    -> "john","john Bann",28,"Kang Won");
Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT * FROM customer;
+-------+---------------+------+-------------+
| id    | name          | age  | address     |
+-------+---------------+------+-------------+
| hong  | Hong Gil Dong |   22 | Kyounggi-do |
| dang  | Dang Tang i   |   23 | Chung Book  |
| ppuni | E PPuni       |   30 | Seoul       |
| john  | john Bann     |   28 | Kang Won    |
+-------+---------------+------+-------------+
4 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM customer WHERE id="hong";
+------+---------------+------+-------------+
| id   | name          | age  | address     |
+------+---------------+------+-------------+
| hong | Hong Gil Dong |   22 | Kyounggi-do |
+------+---------------+------+-------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM customer WHERE age>25;
+-------+-----------+------+----------+
| id    | name      | age  | address  |
+-------+-----------+------+----------+
| ppuni | E PPuni   |   30 | Seoul    |
| john  | john Bann |   28 | Kang Won |
+-------+-----------+------+----------+
2 rows in se

t (0.00 sec)

mysql>
mysql> SELECT * FROM customer WHERE age>=23;

+-------+-------------+------+------------+

| id    | name        | age  | address    |
+-------+-------------+------+------------+
| dang  | Dang Tang i |   23 | Chung Book |
| ppuni | E PPuni     |   30 | Seoul      |
| john  | john Bann   |   28 | Kang Won   |
+-------+-------------+------+------------+
3 rows in set (0.00 sec)

mysql>
mysql> SELECT id,age FROM customer WHERE age>=23;
+-------+------+
| id    | age  |
+-------+------+
| dang  |   23 |
| ppuni |   30 |
| john  |   28 |
+-------+------+
3 rows in set (0.00 sec)

mysql>
mysql> UPDATE customer SET address="Kyounggi-do" WHERE id="ppuni";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> SELECT * FROM customer;
+-------+---------------+------+-------------+
| id    | name          | age  | address     |
+-------+---------------+------+-------------+
| hong  | Hong Gil Dong |   22 | Kyounggi-do |
| dang  | Dang Tang i   |   23 | Chung Book  |
| ppuni | E PPuni       |   30 | Kyounggi-do |
| john  | john Bann     |   28 | Kang Won    |
+-------+---------------+------+-------------+
4 rows in set (0.00 sec)

 

mysql> UPDATE customer SET address="Kyounggi-do" WHERE age>=23;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

 

 

mysql> SELECT * FROM customer;
+-------+---------------+------+-------------+
| id    | name          | age  | address     |
+-------+---------------+------+-------------+
| hong  | Hong Gil Dong |   22 | Kyounggi-do |
| dang  | Dang Tang i   |   23 | Kyounggi-do |
| ppuni | E PPuni       |   30 | Kyounggi-do |
| john  | john Bann     |   28 | Kyounggi-do |
+-------+---------------+------+-------------+
4 rows in set (0.00 sec)

 

mysql> DELETE FROM customer WHERE id="ppuni";
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM customer;
+------+---------------+------+-------------+
| id   | name          | age  | address     |
+------+---------------+------+-------------+
| hong | Hong Gil Dong |   22 | Kyounggi-do |
| dang | Dang Tang i   |   23 | Kyounggi-do |
| john | john Bann     |   28 | Kyounggi-do |
+------+---------------+------+-------------+
3 rows in set (0.00 sec)

mysql>

회원 정보 수정
dang -> address="Seoul"
john -> age=30

hong 회원탈퇴

ppuni 회원가입
id="ppuni" name="E BBuni" age=25 address="Jeju"

mysql> UPDATE customer SET address="Seoul" WHERE id="dang";
mysql> UPDATE customer SET age=30 WHERE id="john";
mysql> DELETE FROM customer WHERE id="hong";
mysql> INSERT INTO customer VALUE ( "ppuni","E PPuni",25,"Jeju");

mysql> SELECT * FROM customer;
+-------+-------------+------+-------------+
| id    | name        | age  | address     |
+-------+-------------+------+-------------+
| ppuni | E PPuni     |   25 | Jeju        |
| dang  | Dang Tang i |   23 | Seoul       |
| john  | john Bann   |   30 | Kyounggi-do |
+-------+-------------+------+-------------+
3 rows in set (0.00 sec)