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)