생활코딩( Database2 MySQL ) (6) - CRUD of Select

1. Select

다음 기본 syntax 사용

SELECT [<column name>] FROM <table name> [WHERE <condition>] \
	[ORDER BY <column name> [HOW]] [LIMIT <number>]

 

1-1) SQL script

-- select columns inside table
USE opentutorials;
-- select everything
\! echo select everything!
SELECT * FROM topic;
\! echo

\! echo select few columns!
-- params after SELECT are aka projections
SELECT id, title, created, author FROM topic;
\! echo

-- select rows with condition
-- eg) my name
\! echo select few columns with condition!
SELECT id, title, created, author FROM topic WHERE author='HJ Yang' ;
\! echo

-- order(sort) data by order
\! echo select few columns with condition and with order!
SELECT id, title, created, author FROM topic WHERE description != '' ORDER BY created DESC;
\! echo

-- limit data output
\! echo select few columns with condition and with order and with limit of number 2!
SELECT id, title, created, author FROM topic WHERE description != '' ORDER BY created DESC LIMIT 2;
\! echo

1) SELECT 다음 column projection이 들어감

    - 어떤 column을 선택할지에 대한 것

2) SELECT <param projection> FROM <table name> 이후 data 제약 조건들이 위치

3) LIMIT 은 수많은 데이터 출력보다 몇개만 필요할 경우가 있을 때 유용하게 사용( query 속도 향상 )

4) ORDER BY로 순위를 주고, DESC(내림차순) ASC(오름차순)

    - Descending, Ascending

 

1-2) 출력

Database changed
select everything!
+----+---------------+---------------------+---------------------+---------+-------------+
| id | title         | description         | created             | author  | profile     |
+----+---------------+---------------------+---------------------+---------+-------------+
|  1 | MySQL         | MySQL is...         | 2021-05-31 22:31:50 | HJ Yang | developer   |
|  2 | MySQL         | MySQL is...         | 2021-05-31 22:34:53 | HJ Yang | developer   |
|  3 | MySQL         | MySQL is...         | 2021-05-31 22:35:47 | HJ Yang | developer   |
|  4 | MySQL         | MySQL is...         | 2021-05-31 23:06:02 | HJ Yang | developer   |
|  5 | SQL Server    | SQL Server is...    | 2021-05-31 23:06:02 | duruwa  | data admin  |
|  6 | Egoin           | Egoing is...         | 2021-06-01 23:48:46 | Unknown | creator     |
|  7 | Mongus Server | Mongus Server is... | 2021-06-01 23:48:46 | Admin   | is admin... |
+----+---------------+---------------------+---------------------+---------+-------------+
7 rows in set (0.00 sec)

ECHO가 설정되어 있습니다.
select few columns!
+----+---------------+---------------------+---------+
| id | title         | created             | author  |
+----+---------------+---------------------+---------+
|  1 | MySQL         | 2021-05-31 22:31:50 | HJ Yang |
|  2 | MySQL         | 2021-05-31 22:34:53 | HJ Yang |
|  3 | MySQL         | 2021-05-31 22:35:47 | HJ Yang |
|  4 | MySQL         | 2021-05-31 23:06:02 | HJ Yang |
|  5 | SQL Server    | 2021-05-31 23:06:02 | duruwa  |
|  6 | Egoin         | 2021-06-01 23:48:46 | Unknown |
|  7 | Mongus Server | 2021-06-01 23:48:46 | Admin   |
+----+---------------+---------------------+---------+
7 rows in set (0.00 sec)

ECHO가 설정되어 있습니다.
select few columns with condition!
+----+-------+---------------------+---------+
| id | title | created             | author  |
+----+-------+---------------------+---------+
|  1 | MySQL | 2021-05-31 22:31:50 | HJ Yang |
|  2 | MySQL | 2021-05-31 22:34:53 | HJ Yang |
|  3 | MySQL | 2021-05-31 22:35:47 | HJ Yang |
|  4 | MySQL | 2021-05-31 23:06:02 | HJ Yang |
+----+-------+---------------------+---------+
4 rows in set (0.00 sec)

ECHO가 설정되어 있습니다.
select few columns with condition and with order!
+----+---------------+---------------------+---------+
| id | title         | created             | author  |
+----+---------------+---------------------+---------+
|  6 | Egoin         | 2021-06-01 23:48:46 | Unknown |
|  7 | Mongus Server | 2021-06-01 23:48:46 | Admin   |
|  4 | MySQL         | 2021-05-31 23:06:02 | HJ Yang |
|  5 | SQL Server    | 2021-05-31 23:06:02 | duruwa  |
|  3 | MySQL         | 2021-05-31 22:35:47 | HJ Yang |
|  2 | MySQL         | 2021-05-31 22:34:53 | HJ Yang |
|  1 | MySQL         | 2021-05-31 22:31:50 | HJ Yang |
+----+---------------+---------------------+---------+
7 rows in set (0.00 sec)

ECHO가 설정되어 있습니다.
select few columns with condition and with order and with limit of number 2!
+----+---------------+---------------------+---------+
| id | title         | created             | author  |
+----+---------------+---------------------+---------+
|  6 | Egoin         | 2021-06-01 23:48:46 | Unknown |
|  7 | Mongus Server | 2021-06-01 23:48:46 | Admin   |
+----+---------------+---------------------+---------+
2 rows in set (0.00 sec)

ECHO가 설정되어 있습니다.
mysql>

 

---

참조

https://opentutorials.org/course/3161/19540

SQL의 SELECT 구문 - 생활코딩