[DB] MySQL / 테이블에 컬럼 추가 / UPDATE / GROUP BY / HAVING / ORDER BY / LIMIT 예제

Onam Kwon·2023년 1월 1일
0

DB

목록 보기
3/11

SQL

테이블에 새로운 컬럼 추가

ALTER TABLE table_name ADD column_name data_type
  • 맨 뒤에 새로운 컬럼을 추가하는 방법.
ALTER TABLE table_name ADD column_name data_type FIRST
  • 맨 앞에 새로운 컬럼을 추가하는 방법.
ALTER TABLE table_name ADD column_name data_type AFTER particular_column
  • 특정 컬럼 다음에 추가하는 방법.
mysql> ALTER TABLE Cities ADD city_population INT NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE Cities;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| city_num        | int          | NO   | PRI | NULL    | auto_increment |
| city_name       | varchar(255) | NO   |     | NULL    |                |
| city_population | int          | NO   |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
  • 기존 Cities테이블에 새로운 컬럼 city_population을 추가했다.

UPDATE

UPDATE tablename SET filedA='456' WHERE test='123' LIMIT 10;
  • 이미 존재하고 있는 값을 수정할때 UPDATE를 사용할 수 있다.
mysql> UPDATE Cities SET city_population=25000 WHERE city_num=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Cities SET city_population=130000 WHERE city_num=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Cities SET city_population=400000 WHERE city_num=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE Cities SET city_population=160000 WHERE city_num=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM Cities;
+----------+-------------+-----------------+
| city_num | city_name   | city_population |
+----------+-------------+-----------------+
|        1 | Emporia     |           25000 |
|        2 | Topeka      |          130000 |
|        3 | Wichita     |          400000 |
|        4 | Kansas City |          160000 |
+----------+-------------+-----------------+
4 rows in set (0.00 sec)
  • 기존에 있던 테이블에 인구 컬럼을 새로 추가했으므로, 값을 수정해줬다.

GROUP BY

SELECT column_name FROM table_name
WHERE condition
GROUP BY column_name
ORDER BY column_name;
  • GROUP BY는 동일한 값을 가진 컬럼을 기준으로 그룹별 연산을 제공한다.
    • 보통 집계함수 COUNT(), MAX(), MIN(), SUM(), AVG()등과 같이 함꼐 쓰인다.
mysql> ALTER TABLE Cities ADD state_name CHAR(2) AFTER city_num;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE Cities SET state_name='KS';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM Cities;
+----------+------------+-------------+-----------------+
| city_num | state_name | city_name   | city_population |
+----------+------------+-------------+-----------------+
|        1 | KS         | Emporia     |           25000 |
|        2 | KS         | Topeka      |          130000 |
|        3 | KS         | Wichita     |          400000 |
|        4 | KS         | Kansas City |          160000 |
+----------+------------+-------------+-----------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO Cities (state_name,city_name,city_population) VALUES ('TX','Dallas',1300000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Cities (state_name,city_name,city_population) VALUES ('TX','Houston',2300000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Cities (state_name,city_name,city_population) VALUES ('TX','El Paso',680000);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM Cities;
+----------+------------+-------------+-----------------+
| city_num | state_name | city_name   | city_population |
+----------+------------+-------------+-----------------+
|        1 | KS         | Emporia     |           25000 |
|        2 | KS         | Topeka      |          130000 |
|        3 | KS         | Wichita     |          400000 |
|        4 | KS         | Kansas City |          160000 |
|        5 | TX         | Dallas      |         1300000 |
|        6 | TX         | Houston     |         2300000 |
|        7 | TX         | El Paso     |          680000 |
+----------+------------+-------------+-----------------+
7 rows in set (0.00 sec)
  • 일단 테이블을 위와 같이 만들었다.
mysql> SELECT state_name,COUNT(*) AS Number_Of_Cities FROM Cities GROUP BY state_name;
+------------+------------------+
| state_name | Number_Of_Cities |
+------------+------------------+
| KS         |                4 |
| TX         |                3 |
+------------+------------------+
2 rows in set (0.00 sec)
  • 각 주마다 포함하고 있는 도시의 개수를 나타낼때는 위와같이 할 수 있다.

HAVING

SELECT column_name FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition
ORDER BY column_name;
  • GROUP BY에 적용되는 조건부절.
mysql> SELECT state_name,COUNT(*) AS Number_Of_Cities FROM Cities GROUP BY state_name HAVING COUNT(*)>3;
+------------+------------------+
| state_name | Number_Of_Cities |
+------------+------------------+
| KS         |                4 |
+------------+------------------+
1 row in set (0.00 sec)
  • 이런식으로 3개 이상의 도시를 포함하고 있는 주만 출력도 가능하다.

ORDER BY

  • 데이터를 지정된 컬럼으로 정렬할때 사용한다.
    • ASC: Ascending의 약자로 오름차순.
    • DESC: Descending의 약자로 내림차순.
mysql> SELECT * FROM Cities ORDER BY city_num DESC;
+----------+------------+-------------+-----------------+
| city_num | state_name | city_name   | city_population |
+----------+------------+-------------+-----------------+
|        7 | TX         | El Paso     |          680000 |
|        6 | TX         | Houston     |         2300000 |
|        5 | TX         | Dallas      |         1300000 |
|        4 | KS         | Kansas City |          160000 |
|        3 | KS         | Wichita     |          400000 |
|        2 | KS         | Topeka      |          130000 |
|        1 | KS         | Emporia     |           25000 |
+----------+------------+-------------+-----------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM Cities ORDER BY city_population DESC;
+----------+------------+-------------+-----------------+
| city_num | state_name | city_name   | city_population |
+----------+------------+-------------+-----------------+
|        6 | TX         | Houston     |         2300000 |
|        5 | TX         | Dallas      |         1300000 |
|        7 | TX         | El Paso     |          680000 |
|        3 | KS         | Wichita     |          400000 |
|        4 | KS         | Kansas City |          160000 |
|        2 | KS         | Topeka      |          130000 |
|        1 | KS         | Emporia     |           25000 |
+----------+------------+-------------+-----------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM Cities ORDER BY city_population;
+----------+------------+-------------+-----------------+
| city_num | state_name | city_name   | city_population |
+----------+------------+-------------+-----------------+
|        1 | KS         | Emporia     |           25000 |
|        2 | KS         | Topeka      |          130000 |
|        4 | KS         | Kansas City |          160000 |
|        3 | KS         | Wichita     |          400000 |
|        7 | TX         | El Paso     |          680000 |
|        5 | TX         | Dallas      |         1300000 |
|        6 | TX         | Houston     |         2300000 |
+----------+------------+-------------+-----------------+
7 rows in set (0.00 sec)

LIMIT

SELECT column_name FROM table_name
LIMIT n;
  • N개의 데이터만 출력한다.
mysql> SELECT * FROM Cities ORDER BY city_population DESC LIMIT 5;
+----------+------------+-------------+-----------------+
| city_num | state_name | city_name   | city_population |
+----------+------------+-------------+-----------------+
|        6 | TX         | Houston     |         2300000 |
|        5 | TX         | Dallas      |         1300000 |
|        7 | TX         | El Paso     |          680000 |
|        3 | KS         | Wichita     |          400000 |
|        4 | KS         | Kansas City |          160000 |
+----------+------------+-------------+-----------------+
5 rows in set (0.00 sec)
profile
권오남 / Onam Kwon

0개의 댓글