반응형
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 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> CREATE TABLE topic(
-> id INT(11) NOT NULL AUTO_INCREMENT,
-> title VARCHAR(30) NOT NULL,
-> description TEXT NULL,
-> created DATETIME NOT NULL,
-> author_id INT(11) NULL,
-> PRIMARY KEY(id)
-> );
ERROR 1046 (3D000): No database selected
mysql>
mysql> description TEXT NULL,
-> ;
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 'description TEXT NULL,' at line 1
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| opentutorials |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> USE opentutorials;
Database changed
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| opentutorials |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> DELETE DATABASE world;
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 'DATABASE world' at line 1
mysql> DROP DATABASE information_schema;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
mysql> DROP DATABASE sys;
Query OK, 101 rows affected (0.39 sec)
mysql> DROP DATABASE world;
Query OK, 3 rows affected (0.12 sec)
mysql> DROP DATABASE sakila;
Query OK, 23 rows affected (0.76 sec)
mysql> DROP DATABASE performance_schema;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'performance_schema'
mysql> DROP DATABASE mysql;
ERROR 3552 (HY000): Access to system schema 'mysql' is rejected.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| opentutorials |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)
mysql> USE opentutorials;
Database changed
mysql> CREATE TABLE topic(
-> -> id INT(11) NOT NULL AUTO_INCREMENT,
-> -> title VARCHAR(30) NOT NULL,
-> -> description TEXT NULL,
-> -> created DATETIME NOT NULL,
-> -> author_id INT(11) NULL,
-> -> PRIMARY KEY(id)
-> -> );
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 '-> id INT(11) NOT NULL AUTO_INCREMENT,
-> title VARCHAR(30) NOT NULL,
' at line 2
mysql> DESC topic;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| title | varchar(30) | NO | | NULL | |
| description | text | YES | | NULL | |
| created | datetime | NO | | NULL | |
| author_id | int | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> CREATE TABLE author(
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(20) NOT NULL,
-> `profile` varchar(200) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> DESC autor;
ERROR 1146 (42S02): Table 'opentutorials.autor' doesn't exist
mysql> DESC author;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| profile | varchar(200) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> INSERT INTO `author` VALUES (1,'egoing','developer');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `author` VALUES (2,'duru','database administrator');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `author` VALUES (3,'taeho','data scientist, developer');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `topic` VALUES (1,'MySQL','MySQL is...','2018-01-01 12:10:11',1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `topic` VALUES (2,'Oracle','Oracle is ...','2018-01-03 13:01:10',1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `topic` VALUES (3,'SQL Server','SQL Server is ...','2018-01-20 11:01:10',2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `topic` VALUES (4,'PostgreSQL','PostgreSQL is ...','2018-01-23 01:03:03',3);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `topic` VALUES (5,'MongoDB','MongoDB is ...','2018-01-30 12:31:03',1);
Query OK, 1 row affected (0.01 sec)
mysql> DESC author;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| profile | varchar(200) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM author;
+----+--------+---------------------------+
| id | name | profile |
+----+--------+---------------------------+
| 1 | egoing | developer |
| 2 | duru | database administrator |
| 3 | taeho | data scientist, developer |
+----+--------+---------------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM topic;
+----+------------+-------------------+---------------------+-----------+
| id | title | description | created | author_id |
+----+------------+-------------------+---------------------+-----------+
| 1 | MySQL | MySQL is... | 2018-01-01 12:10:11 | 1 |
| 2 | Oracle | Oracle is ... | 2018-01-03 13:01:10 | 1 |
| 3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 | 2 |
| 4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 | 3 |
| 5 | MongoDB | MongoDB is ... | 2018-01-30 12:31:03 | 1 |
+----+------------+-------------------+---------------------+-----------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
| id | title | description | created | author_id | id | name | profile |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
| 1 | MySQL | MySQL is... | 2018-01-01 12:10:11 | 1 | 1 | egoing | developer |
| 2 | Oracle | Oracle is ... | 2018-01-03 13:01:10 | 1 | 1 | egoing | developer |
| 3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 | 2 | 2 | duru | database administrator |
| 4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 | 3 | 3 | taeho | data scientist, developer |
| 5 | MongoDB | MongoDB is ... | 2018-01-30 12:31:03 | 1 | 1 | egoing | developer |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM topic JOIN author ON topic.author_id = author.id;
+----+------------+-------------------+---------------------+-----------+----+--------+---------------------------+
| id | title | description | created | author_id | id | name | profile |
+----+------------+-------------------+---------------------+-----------+----+--------+---------------------------+
| 1 | MySQL | MySQL is... | 2018-01-01 12:10:11 | 1 | 1 | egoing | developer |
| 2 | Oracle | Oracle is ... | 2018-01-03 13:01:10 | 1 | 1 | egoing | developer |
| 3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 | 2 | 2 | duru | database administrator |
| 4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 | 3 | 3 | taeho | data scientist, developer |
| 5 | MongoDB | MongoDB is ... | 2018-01-30 12:31:03 | 1 | 1 | egoing | developer |
+----+------------+-------------------+---------------------+-----------+----+--------+---------------------------+
5 rows in set (0.00 sec)
mysql> SELECT id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
ERROR 1052 (23000): Column 'id' in field list is ambiguous
mysql> SELECT topic.id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title | description | created | name | profile |
+----+------------+-------------------+---------------------+--------+---------------------------+
| 1 | MySQL | MySQL is... | 2018-01-01 12:10:11 | egoing | developer |
| 2 | Oracle | Oracle is ... | 2018-01-03 13:01:10 | egoing | developer |
| 3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 | duru | database administrator |
| 4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 | taeho | data scientist, developer |
| 5 | MongoDB | MongoDB is ... | 2018-01-30 12:31:03 | egoing | developer |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)
mysql> SELECT topic.id AS topic_id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----------+------------+-------------------+---------------------+--------+---------------------------+
| topic_id | title | description | created | name | profile |
+----------+------------+-------------------+---------------------+--------+---------------------------+
| 1 | MySQL | MySQL is... | 2018-01-01 12:10:11 | egoing | developer |
| 2 | Oracle | Oracle is ... | 2018-01-03 13:01:10 | egoing | developer |
| 3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 | duru | database administrator |
| 4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 | taeho | data scientist, developer |
| 5 | MongoDB | MongoDB is ... | 2018-01-30 12:31:03 | egoing | developer |
+----------+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.00 sec)
mysql>Q. 위 구문에서 과연 JOIN을 실행하였을때, 기준은 엑셀의 VLOOKUP과 같이 ID나 AUTHOR 에 맞게 정열이 되는 것인지 아니면 그냥 각 열에 맞게 나열이되는것인지...
반응형
댓글