MySql Start

C:\>cd mysql/bin

 

C:\mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.53-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

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

 

mysql> use mysql;
Database changed

mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.00 sec)

 

mysql> select host,user,password from user;
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost | root |          |
| %         | root |          |
| localhost |      |          |
| %         |      |          |
+-----------+------+----------+
4 rows in set (0.02 sec)

 

mysql> update user set password = password('1111') where user='root';
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2  Changed: 2  Warnings: 0

 

mysql> select host,user,password from user;
+-----------+------+------------------+
| host      | user | password         |
+-----------+------+------------------+
| localhost | root | 45271aba0b765d95 |    <---  지정한 비밀번호를 암호화해서 보여줌.
| %         | root | 45271aba0b765d95 |
| localhost |      |                  |
| %         |      |                  |
+-----------+------+------------------+
4 rows in set (0.00 sec)

 

mysql> quit;
Bye

 

C:\mysql\bin>mysqladmin reload

C:\mysql\bin>mysql -u root -p mysql
Enter password: ****     <--- 위에서 root에 지정했던 비밀번호 입력
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 3.23.53-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL PRIVILEGES ON *.* TO small@localhost IDENTIFIED BY 'password'
    -> WITH GRANT OPTION;   <--- *.* : 전체 데이터베이스에 접근할 수 있는 권한
Query OK, 0 rows affected (0.00 sec)

 

mysql> select host,user,password from user;
+-----------+-------+------------------+
| host      | user  | password         |
+-----------+-------+------------------+
| localhost | root  | 45271aba0b765d95 |
| %         | root  | 45271aba0b765d95 |
| localhost |       |                  |
| %         |       |                  |
| localhost | small | 5d2e19393cc5ef67 |   <--- 새로운 사용자 추가됨.
+-----------+-------+------------------+
5 rows in set (0.00 sec)

 

mysql> GRANT ALL PRIVILEGES ON company.* TO small@localhost IDENTIFIED BY 'password'
    -> WITH GRANT OPTION;       <--- company.* : company 데이터베이스에 접근할 수 있는 권한
Query OK, 0 rows affected (0.00 sec)

 

mysql> create database JPControls;
Query OK, 1 row affected (0.08 sec)

 

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

 

mysql> drop database jpcontrols;
Query OK, 0 rows affected (0.00 sec)

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

 

mysql> create database company;
Query OK, 1 row affected (0.00 sec)

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

mysql> GRANT ALL ON company.* TO small;
Query OK, 0 rows affected (0.00 sec)

 

mysql> create table friend (
    -> no int,
    -> name char(15),
    -> phone int(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

 

mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| friend          |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
7 rows in set (0.00 sec)

 

mysql> drop table friend;
Query OK, 0 rows affected (0.00 sec)

 

mysql> use company
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table friend (
    -> no int,
    -> name char(15),
    -> phone int(20)
    -> );
Query OK, 0 rows affected (0.00 sec

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| friend            |
+-------------------+
1 row in set (0.00 sec)

 

mysql> desc friend;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| no    | int(11)  | YES  |     | NULL    |       |
| name  | char(15) | YES  |     | NULL    |       |
| phone | int(20)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE friend ADD email varchar(40);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc friend;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| phone | int(20)     | YES  |     | NULL    |       |
| email | varchar(40) | YES  |     | NULL    |       |  <--- 새로 추가된 필드
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

mysql> alter table friend change phone tel int(30) not null;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc friend;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| tel   | int(30)     |      |     | 0       |       |           <--- 변경된 필드
| email | varchar(40) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

mysql> alter table friend drop email;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0