mysql_01

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-05-13 15:50:40 |
+---------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> create database bdb;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| bdb                |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> use bdb;
Database changed
mysql> show tables;
Empty set (0.01 sec)

mysql> create table score(
    -> name char(20),
    -> kor int,
    -> eng int,
    -> mat int,
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 6
mysql> create table score(
    -> name char(20),
    -> kor int,
    -> eng int,
    -> mat int);
Query OK, 0 rows affected (0.04 sec)

mysql> select 2 * 10;
+--------+
| 2 * 10 |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_bdb |
+---------------+
| score         |
+---------------+
1 row in set (0.01 sec)

mysql> create table Man(
    -> name char(10),
    -> age int);
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+---------------+
| Tables_in_bdb |
+---------------+
| man           |
| score         |
+---------------+
2 rows in set (0.00 sec)

========= table에 데이터 입력 ==========

mysql> insert into man values("손흥민", 30);
Query OK, 1 row affected (0.02 sec)

mysql> insert into man values("이강인", 21);
Query OK, 1 row affected (0.01 sec)

mysql> select * from man;
+--------+------+
| name   | age  |
+--------+------+
| 손흥민 |   30 |
| 이강인 |   21 |
+--------+------+
2 rows in set (0.01 sec)

=======================================
mysql> desc score;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(20) | YES  |     | NULL    |       |
| kor   | int      | YES  |     | NULL    |       |
| eng   | int      | YES  |     | NULL    |       |
| mat   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> desc man;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
======================================

mysql> insert into man values('류현진', 26);
Query OK, 1 row affected (0.01 sec)

mysql> select * from man;
+--------+------+
| name   | age  |
+--------+------+
| 손흥민 |   30 |
| 이강인 |   21 |
| 류현진 |   26 |
+--------+------+
3 rows in set (0.01 sec)

mysql> update man set age=40 where name='류현진';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

============================================================
mysql> select name as '이름', age as '나이' from man;
+--------+------+
| 이름   | 나이 |
+--------+------+
| 손흥민 |   30 |
| 이강인 |   21 |
| 류현진 |   40 |
+--------+------+
3 rows in set (0.00 sec)
========================================================

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| bdb                |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.01 sec)
======================================================
mysql> use mysql;
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
35 rows in set (0.01 sec)

=====================================================


mysql> use world;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.01 sec)

=========================================================
##desc(schema 같은거 = 구조 보기)
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

###key = primary key(절대중복되지 않는 고유한 숫자, ISBN, 주민번호 같은거), 
=========================================================

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.02 sec)

=========================================

mysql> select name, countrycode from city limit 10;
+----------------+-------------+
| name           | countrycode |
+----------------+-------------+
| Kabul          | AFG         |
| Qandahar       | AFG         |
| Herat          | AFG         |
| Mazar-e-Sharif | AFG         |
| Amsterdam      | NLD         |
| Rotterdam      | NLD         |
| Haag           | NLD         |
| Utrecht        | NLD         |
| Eindhoven      | NLD         |
| Tilburg        | NLD         |
+----------------+-------------+
10 rows in set (0.00 sec)

=================================================
한국의 도시만 검색해보기

mysql> select name, countrycode from city where countrycode = 'KOR';
+------------+-------------+
| name       | countrycode |
+------------+-------------+
| Seoul      | KOR         |
| Pusan      | KOR         |
| Inchon     | KOR         |
| Taegu      | KOR         |
| Taejon     | KOR         |
| Kwangju    | KOR         |
| Ulsan      | KOR         |
| Songnam    | KOR         |
| Puchon     | KOR         |
| Suwon      | KOR         |
| Anyang     | KOR         |
| Chonju     | KOR         |
| Chongju    | KOR         |
| Koyang     | KOR         |
| Ansan      | KOR         |
| Pohang     | KOR         |
| Chang-won  | KOR         |
| Masan      | KOR         |
| Kwangmyong | KOR         |
| Chonan     | KOR         |
| Chinju     | KOR         |
| Iksan      | KOR         |
| Pyongtaek  | KOR         |
| Kumi       | KOR         |
| Uijongbu   | KOR         |
| Kyongju    | KOR         |
| Kunsan     | KOR         |
| Cheju      | KOR         |
| Kimhae     | KOR         |
| Sunchon    | KOR         |
| Mokpo      | KOR         |
| Yong-in    | KOR         |
| Wonju      | KOR         |
| Kunpo      | KOR         |
| Chunchon   | KOR         |
| Namyangju  | KOR         |
| Kangnung   | KOR         |
| Chungju    | KOR         |
| Andong     | KOR         |
| Yosu       | KOR         |
| Kyongsan   | KOR         |
| Paju       | KOR         |
| Yangsan    | KOR         |
| Ichon      | KOR         |
| Asan       | KOR         |
| Koje       | KOR         |
| Kimchon    | KOR         |
| Nonsan     | KOR         |
| Kuri       | KOR         |
| Chong-up   | KOR         |
| Chechon    | KOR         |
| Sosan      | KOR         |
| Shihung    | KOR         |
| Tong-yong  | KOR         |
| Kongju     | KOR         |
| Yongju     | KOR         |
| Chinhae    | KOR         |
| Sangju     | KOR         |
| Poryong    | KOR         |
| Kwang-yang | KOR         |
| Miryang    | KOR         |
| Hanam      | KOR         |
| Kimje      | KOR         |
| Yongchon   | KOR         |
| Sachon     | KOR         |
| Uiwang     | KOR         |
| Naju       | KOR         |
| Namwon     | KOR         |
| Tonghae    | KOR         |
| Mun-gyong  | KOR         |
+------------+-------------+
70 rows in set (0.01 sec)

====================================
### 자료 보는 방법 ###

#DB LIST 보기
show databases;

#sys라는 DB에 접근
use sys;

#테이블 분류
show tables;

#세부정보
desc session;

===================================
원하는 자료만 제한적으로 보기

mysql> use world;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> select code, name from country order by code limit 10;
+------+----------------------+
| code | name                 |
+------+----------------------+
| ABW  | Aruba                |
| AFG  | Afghanistan          |
| AGO  | Angola               |
| AIA  | Anguilla             |
| ALB  | Albania              |
| AND  | Andorra              |
| ANT  | Netherlands Antilles |
| ARE  | United Arab Emirates |
| ARG  | Argentina            |
| ARM  | Armenia              |
+------+----------------------+
10 rows in set (0.01 sec)

====================================
mysql> select * from lunch;  => 전체항목 다보기 [*]
mysql> select menu, price from lunch;
+--------+-------+
| menu   | price |
+--------+-------+
| 짜장면 |  5000 |
| 비빔밥 |  5000 |
| 돈가스 |  5000 |
+--------+-------+
3 rows in set (0.00 sec)

===================================