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
