[Database] MariaDB - Select, Insert, Update, Delete(2)

1. select 문 사용 여러 방법

(1)에서는 Where 절(조건)을 사용해서 특정 열만을 검색하는 방법을 알아보았습니다.

 

select 는 아래와 같이 여러 사용 방법이 있습니다.

1-1 데이터 정렬 > select * from TABLE order by DESC(내림차)|ASC(오름차)

예시) select * from users order by id DESC; --id 내림차순 정렬

MariaDB [test]> select * from users order by id desc;
+----+----------+---------------+---------------------+
| id | username | email         | created_at          |
+----+----------+---------------+---------------------+
|  4 | choi     | choi@test.com | 2024-03-13 22:54:12 |
|  3 | joe      | joe@test.com  | 2024-03-13 22:54:12 |
|  2 | kim      | kim@test.com  | 2024-03-13 22:54:12 |
|  1 | ryu      | ryu@test.com  | 2024-03-13 22:14:42 |
+----+----------+---------------+---------------------+

 

1-2 데이터 중복 제거 > select distinct (Column) from TABLE; 

예시) select distinct username from users;

MariaDB [test]> select * from users;
+----+----------+------------------+---------------------+
| id | username | email            | created_at          |
+----+----------+------------------+---------------------+
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |
|  6 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |
+----+----------+------------------+---------------------+

ryu 중복

MariaDB [test]> select distinct username from users;
+----------+
| username |
+----------+
| ryu      |
| kim      |
| joe      |
| choi     |
+----------+
4 rows in set (0.00 sec)

 

1-3 데이터 제한 > select * from TABLE LIMIT (num);

예시) select * from users limit 3;

MariaDB [test]> select * from users limit 3;
+----+----------+--------------+---------------------+
| id | username | email        | created_at          |
+----+----------+--------------+---------------------+
|  1 | ryu      | ryu@test.com | 2024-03-13 22:14:42 |
|  2 | kim      | kim@test.com | 2024-03-13 22:54:12 |
|  3 | joe      | joe@test.com | 2024-03-13 22:54:12 |
+----+----------+--------------+---------------------+
3 rows in set (0.00 sec)

 

1-4 데이터 집계 함수 > COUNT , AVG, SUM, 등으로 값 계산, select COUNT(*) from TABLE;

예시) select COUNT(*) from users;, select AVG(id) from users;, select SUM(id) from users;

MariaDB [test]> select COUNT(*) from users;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
MariaDB [test]> select AVG(id) from users;
+---------+
| AVG(id) |
+---------+
|  3.0000 |
+---------+
MariaDB [test]> select SUM(id) from users;
+---------+
| SUM(id) |
+---------+
|      15 |
+---------+

 

1-5 데이터 Join >  데이터 간의 동일한 부분 또는 여러 테이블 데이터를 연결할 때 사용합니다.

INNER JOIN, CROSS JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN 이 있습니다.

TEST에 사용 할 테이블은 아래와 같습니다.

age_users
+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | ryu      |  20 |
|  2 | choi     |  31 |
|  3 | kim      |  34 |
|  4 | joe      |  45 |
|  5 | park     |  22 |
+----+----------+-----+

users
+----+----------+------------------+---------------------+
| id | username | email            | created_at          |
+----+----------+------------------+---------------------+
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |
+----+----------+------------------+---------------------+

 

-INNER JOIN > Select * from TABLE1 INNER JOIN TABLE2 ON TABLE1.COLUMN = TABLE2.COLUMN;

select * from users inner join age_users on users.username = age_users.username;

INNER JOIN 결과
+----+----------+------------------+---------------------+----+----------+-----+
| id | username | email            | created_at          | id | username | age |
+----+----------+------------------+---------------------+----+----------+-----+
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  1 | ryu      |  20 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  1 | ryu      |  20 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  2 | choi     |  31 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  3 | kim      |  34 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  4 | joe      |  45 |
+----+----------+------------------+---------------------+----+----------+-----+

 

 

-CROSS JOIN > Select * from TABLE1 CROSS JOIN TABLE2;

select * from users CROSS JOIN age_users;

CROSS JOIN 결과
+----+----------+------------------+---------------------+----+----------+-----+
| id | username | email            | created_at          | id | username | age |
+----+----------+------------------+---------------------+----+----------+-----+
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  1 | ryu      |  20 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  1 | ryu      |  20 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  1 | ryu      |  20 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  1 | ryu      |  20 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  1 | ryu      |  20 |
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  2 | choi     |  31 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  2 | choi     |  31 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  2 | choi     |  31 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  2 | choi     |  31 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  2 | choi     |  31 |
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  3 | kim      |  34 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  3 | kim      |  34 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  3 | kim      |  34 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  3 | kim      |  34 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  3 | kim      |  34 |
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  4 | joe      |  45 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  4 | joe      |  45 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  4 | joe      |  45 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  4 | joe      |  45 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  4 | joe      |  45 |
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  5 | park     |  22 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  5 | park     |  22 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  5 | park     |  22 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  5 | park     |  22 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  5 | park     |  22 |
+----+----------+------------------+---------------------+----+----------+-----+

 

LEFT/RIGHT JOIN 차이

 

LEFT JOIN 은 from 절에 위치한 TABLE 을 모두 표시(users) 하고

age_users 테이블에 해당하는 값이 있다면 표시하고 없으면 NULL 표시

 

RIGHT JOIN 은 RIGHT JOIN 위치에 있는 TABLE 을 모두 표시(age_users)하고

users 테이블에 해당하는 값이 있다면 표시하고 없으면 NULL 표시

 

-LEFT JOIN > select * from TABLE1 LEFT JOIN from TABLE2 ON TABLE1.COULMN = TABLE2.COLUMN;

select  * from users LEFT JOIN age_users ON users.username = age_users.username;

+----+----------+------------------+---------------------+------+----------+------+
| id | username | email            | created_at          | id   | username | age  |
+----+----------+------------------+---------------------+------+----------+------+
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |    1 | ryu      |   20 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |    1 | ryu      |   20 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |    2 | choi     |   31 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |    3 | kim      |   34 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |    4 | joe      |   45 |
+----+----------+------------------+---------------------+------+----------+------+

 

-RIGHT JOIN > select * from TABLE1 RIGHT JOIN TABLE2 ON TABLE1.COLUMN = TABLE2.COLUMN;

select * from users RIGHT JOIN age_users ON users.username = age_users.username;

+------+----------+------------------+---------------------+----+----------+-----+
| id   | username | email            | created_at          | id | username | age |
+------+----------+------------------+---------------------+----+----------+-----+
|    1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  1 | ryu      |  20 |
|    2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  3 | kim      |  34 |
|    3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  4 | joe      |  45 |
|    4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  2 | choi     |  31 |
|    5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  1 | ryu      |  20 |
| NULL | NULL     | NULL             | NULL                |  5 | park     |  22 |
+------+----------+------------------+---------------------+----+----------+-----+

 

-FULL JOIN > select * from TABLE1 FULL JOIN TABLE2;

select * from users FULL JOIN age_users;

+----+----------+------------------+---------------------+----+----------+-----+
| id | username | email            | created_at          | id | username | age |
+----+----------+------------------+---------------------+----+----------+-----+
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  1 | ryu      |  20 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  1 | ryu      |  20 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  1 | ryu      |  20 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  1 | ryu      |  20 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  1 | ryu      |  20 |
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  2 | choi     |  31 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  2 | choi     |  31 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  2 | choi     |  31 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  2 | choi     |  31 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  2 | choi     |  31 |
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  3 | kim      |  34 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  3 | kim      |  34 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  3 | kim      |  34 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  3 | kim      |  34 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  3 | kim      |  34 |
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  4 | joe      |  45 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  4 | joe      |  45 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  4 | joe      |  45 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  4 | joe      |  45 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  4 | joe      |  45 |
|  1 | ryu      | ryu@test.com     | 2024-03-13 22:14:42 |  5 | park     |  22 |
|  2 | kim      | kim@test.com     | 2024-03-13 22:54:12 |  5 | park     |  22 |
|  3 | joe      | joe@test.com     | 2024-03-13 22:54:12 |  5 | park     |  22 |
|  4 | choi     | choi@test.com    | 2024-03-13 22:54:12 |  5 | park     |  22 |
|  5 | ryu      | ryutest@test.com | 2024-03-14 00:39:07 |  5 | park     |  22 |
+----+----------+------------------+---------------------+----+----------+-----+