1. Oracle

2. MYSQL

2.1. phpMyAdmin 접속 (GUI)

Starting your MySQL database....
This process can take up to a minute.
      
MySQL database started, waiting for all services to be ready....
    
Your MySQL database is now ready to use and available with username: root password: password

You can access your MySQL database via:
 • The browser at: https://blahblah_phpMyAdmin_link
 • CommandLine: mysql --host=127.0.0.1 --port=3306 --user=root --password=password

2.2. CommandLine 접속 (CLI)

] mysql --host=127.0.0.1 --port=3306 --user=root --password=password
mysql> \q # 세션 종료

2.3. DDL, DML

Database 생성

mysql> CREATE DATABASE sales default CHARACTER SET UTF8;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sales              |
| sys                |
+--------------------+

테이블 생성

mysql> CREATE TABLE `sales`.`sales_data` ( `product_id` VARCHAR(4) NOT NULL , `customer_id` VARCHAR(6) NOT NULL , `price` INT(20) NOT NULL , `quantity` INT(10) NOT NULL , `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`product_id`)) ENGINE = InnoDB;

mysql> INSERT INTO `sales`.`oltpdata` (`COL 1`, `COL 2`, `COL 3`, `COL 4`, `COL 5`) VALUES (6739, 76305, 230, 1, '2020-09-05 16:20:03'), (7460, 81008, 1455, 4, '2020-09-05 16:20:04');

테이블 조회

mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM countrylanguage WHERE countrycode='CAN';
+-------------+------------------+------------+------------+
| CountryCode | Language         | IsOfficial | Percentage |
+-------------+------------------+------------+------------+
| CAN         | Chinese          | F          |        2.5 |
| CAN         | Dutch            | F          |        0.5 |
| CAN         | English          | T          |       60.4 |
| CAN         | Eskimo Languages | F          |        0.1 |
| CAN         | French           | T          |       23.4 |
| CAN         | German           | F          |        1.6 |
| CAN         | Italian          | F          |        1.7 |
| CAN         | Polish           | F          |        0.7 |
| CAN         | Portuguese       | F          |        0.7 |
| CAN         | Punjabi          | F          |        0.7 |
| CAN         | Spanish          | F          |        0.7 |
| CAN         | Ukrainian        | F          |        0.6 |
+-------------+------------------+------------+------------+
12 rows in set (0.03 sec)

테이블 export

] mysqldump -p --user=root sales sales_data > sales_data.sql

테이블 import

] mysql -u root -p sales < sales.sql # -p옵션 뒤에 database 명시

2.4. admin

User create & drop

mysql> create user user1@localhost identified by 'password';
mysql> create user 'user2'@'%' identified by 'password'; # wildcard(%)사용으로 누구나 접속 가능
mysql> drop user 'userid';

mysql> SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('new_password');

User privileges grant & revoke

mysql> grant all privileges on sales.* to 'user2'@'%' identified by 'password';
mysql> flush privileges; 
mysql> grant select, insert, update on sales.sales_data to user3@host identified by 'password';
mysql> flush privileges; 
mysql> show grants for userid@host;
mysql> revoke all on dbname.table from userid@host;
mysql> flush privileges; 