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;
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;
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)