테이블 생성
데이터베이스에서 테이블이란 Excal 시트와 같이 첫 행에 항목이 있고 가로, 세로로 셀을 나눈 표를 떠올리면 이해하기 쉽다. 저장 데이터는 항목으로, 표의 열에 있는 칼럼(또는 필드)에 할당된다.
| 레코드→ | 이름 | 주소 | 나이 | 직업 |
| 1건째 | ↓칼럼(또는 필드) | |||
| 2건째 | 행 | |||
| 3건째 | 열 | |||
| 4건째 | ||||
| 5건째 |
각각의 칼럼은 이름을 가지고 있고 데이터의 형과 길이를 지정할 수 있다. 가로로 늘어서 있는 각각의 칼럼(표의 '행'에 해당하는 부분)이 모여 1건의 데이터 또는 하나의 레코드가 된다. 이런 구조를 스키마라고 부른다.
테이블을 작성하려면 CREATE TABLE 문을 사용해서 작성할 수 있다. CREATE TABLE 테이블명 ();이 기본적인 형태이다. 테이블명 뒤에 들어오는 ()안에 각 칼럼을 지정한다.
|
1
2
3
4
5
|
CREATE TABLE 테이블명 (
칼럼명 데이터형,
칼럼명 데이터형,
프라이머리 키
);
|
cs |
칼럼명은 직접 작성하고 칼럼명은 공백으로 구분하며, MySQL에서 제공되는 데이터형을 설정한다. 프라이머리 키(Primary Kay)는 일련번호로 비어 있는 값과 값의 중복이 없도록 하는 것이 중요하다. 각 칼럼과 프라이머리 키는 ,로 연결한다.
데이터베이스에서 사용 가능한 문자는 따로 지정이 되어 있다.
| 바이트 | 사용 가능한 문자 | |
| 데이터베이스 | 64 | '/' '\' '.' 이외에 디렉터리명에 사용 가능한 문자 |
| 테이블 | 64 | '/' '.' 이외에 파일명에 사용 가능한 문자 |
| 칼럼 | 64 | 모든 문자 |
테이블명이나 칼럼, 데이터베이스에 사용하는 문자는 위의 표와 같다. 한글을 사용하면 예상치 못한 오류가 발생할 수 있기 때문에 되도록 영문자 또는 숫자를 사용하는 것이 좋다.
또 칼럼에 정수를 설정할 수 있는데 정수형에는 수치의 범위가 존재한다.
| 정수형 | 바이트 수 | 수치 범위 | |
| 부호 있음 | 부호 없음 | ||
| tinyint | 1 | -128~127 | 0~255 |
| smallint | 2 | -32768~32677 | 0~65535 |
| int | 3 | -2^31~2^31-1 | 0~4294967295 |
| bigint | 4 | -2^63~2^63-1 | 0~18446744073709551615 |
칼럼에 문자열형을 설정할 때는 CHAR과 VARCHAR의 형태가 존재한다.
| CHAR(4) | 입력 데이터 | 할당 데이터 | |
| '' | ' ' | 아무것도 입력하지 않아도 문자 수많큼 공백 | |
| 'ab' | 'ab ' | 부족한 문자 수를 공백으로 채움 | |
| 'abcde' | 'abcd' | 마지막 문자는 등록되지 않음 | |
| VARCHAR(4) | 입력 데이터 | 할당 데이터 | |
| '' | '' | ||
| 'ab' | 'ab' | ||
| 'abcde' | 'abcd' | 마지막 문자는 등록되지 않음 |
프라이머리 키는 설정하면 테이블 안에 다른 데이터를 식별하는 열쇠가 되는 데이터이다. 칼럼에 프라이머리 키 제약을 설정하면 칼럼 안에서 같은 값이 없도록 유일(유니크)해야 하며 NULL 값은 등록할 수 없게 된다. 프라이머리 키 제약을 설정할 칼럼은 하나의 테이블당 하나만 가능하다. 회원번호와 같이 회원을 식별할 수 있는 데이터에 프라이머리 키 제약을 설정한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
CREATE TABLE 테이블명 (
칼럼명 데이터형,
칼럼명 데이터형,
프라이머리 키
);
MariaDB [(none)]> use phpdb;
Database changed
MariaDB [(none)]> CREATE TABLE memder (
-> id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> last_name VARCHAR(50),
-> first_name VARCHAR(50),
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.03 sec)
|
cs |
위와 같은 식으로 명령 프롬프트에 mysql 서버에서 테이블을 작성할 수 있다. 테이블을 확인하려면 SHOW TABLE;을 입력하면 알 수 있다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
CREATE TABLE 테이블명 (
칼럼명 데이터형,
칼럼명 데이터형,
프라이머리 키
);
MariaDB [(none)]> use phpdb;
Database changed
MariaDB [(none)]> CREATE TABLE memder (
-> id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> last_name VARCHAR(50),
-> first_name VARCHAR(50),
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.03 sec)
MariaDB [phpdb]> SHOW TABLES;
+-----------------+
| Tables_in_phpdb |
+-----------------+
| member |
+-----------------+
1 row in set (0.001 sec)
|
cs |
또 테이블을 삭제하려면 DROP TABLE문을 사용하는데 DROP TABLE 테이블명;을 통해 테이블명에 지정된 테이블을 삭제할 수 있다.
테이블 작성 시에 스토로지 엔진을 지정할 수 있다. SHOW ENGINES를 사용하면 MySQL 서버가 지원하는 스토로지 엔진이 표시된다. 스토로지 엔진은 데이터를 저장하고 접근하는 기능을 말한다. MySQL의 경우에는 멀티 스토로지 엔진이라는 여러 가지 기능이 있는 스토로지 엔진을 사용할 수 있다.
| 종류 | 설명 |
| InnoDB | MySQL 5.5.5부터 기본 설정 스토로지 엔진, 트랜잭션(Transaction), 외래 키(Foreign Key), 제약, 충돌 복구(Crash Recovery), 행 단위 잠금(Row Level Lock) 사용이 가능하다. |
| MyISAM | 고속 동작, 풀 텍스트(Full-Text) 검색 대응. 트랜잭션이나 외래 키 제약은 없다. 현재도 대부분의 MySQL이 MyISAM을 적용하고 있다. |
| Memory | 모든 데이터를 RAM에 보존하려 고속 동작. 이전에는 HEAP라고 불렀다. |
| Merge | 같은 형태의 테이블을 그룹화. 등록 대용량 데이터 처리에 효과적이다. |
테이블에 데이터 입력
쿼리(Query)란 데이터베이스 관리 시스템(MySQL 등)에 대한 처리 요청을 문자열로 나타낸 것이다. 데이터 입력이나, 수정, 삭제 등의 명령에 사용된다. 관계령 데이터베이스에서는 쿼리를 작성할 때 SQL이라는 언어를 사용한다. CREATE TABLE 역시 SQL 언어 중 하나이다.
SQL(Structed Query Language)은 IBM사에서 데이터베이스를 조작하기 위해 개발한 언어로, 관계령 데이터베이스에서 폭넓게 사용한다. 특히 데이터베이스에 데이터를 입력하는 것과 같은 조작을 할 때 SQL은 관계형 데이터베이스에 따른 미묘한 차이나, 작성 방법이 완전히 다를 수 있어 SQL을 작성할 때는 이용하는 데이터베이스의 메뉴얼을 옆에 두고 확인 하는 것이 좋다.
데이터베이스 조작의 흐름으로는 아래와 같은 순서를 가지고 있다.
1. MySQL에 사용자 권한으로 로그인
2. 대상이 되는 데이터베이스를 선택(또는 선택한 상태로 로그인)
3. 테이블명을 지정하여 작업
PHP에서의 조작도 이와 마찬가지이므로 이 흐음을 기억하고 익혀두는게 좋다.
테이블의 구조를 확인하려면 SHOW FIELDS FROM 테이블명;을 입력하면 된다.
|
1
2
3
4
5
6
7
8
9
|
MariaDB [phpdb]> SHOW FIELDS FROM member;
+------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+----------------+
| id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| last_name | varchar(50) | YES | | NULL | |
| first_name | varchar(50) | YES | | NULL | |
+------------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.012 sec)
|
cs |
이전에 생성했던 member 테이블을 SHOW FIELDS FROM member;와 같이 입력하면 위와 같이 테이블의 구조를 출력해 확인 할 수 있다.
테이블에 데이터를 등록하려면 INSERT문을 사용한다. INSERT INTO 테이블명(칼럼1, 칼럼2...) VALUES ('문자열1', '문자열2'...); 와 같은 형태로 사용한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
MariaDB [phpdb]> SHOW FIELDS FROM member;
+------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+----------------+
| id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| last_name | varchar(50) | YES | | NULL | |
| first_name | varchar(50) | YES | | NULL | |
+------------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.012 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name) VALUES('고','길동');
Query OK, 1 row affected, 2 warnings (0.026 sec)
|
cs |
주의할 점은 칼럼명과 데이터가 대응하도록 주의해서 나열해야 한다. 문자열은 '로 묶으며 데이터는 ,로 구분한다. 맨 끝은 ;로 마치도록 한다. 작성된 데이블 member의 칼럼 id는 데이터를 입력하지 않아도 자동으로 번호가 입력된다.
테이블에 데이터 검색
테이블에 입력된 데이터를 SELECT문으로 검색할 수 있다. 형태는 SELECT * FROM 테이블명;과 같은 형태를 가진다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
MariaDB [phpdb]> DROP TABLE member;
Query OK, 0 rows affected (0.019 sec)
MariaDB [phpdb]> CREATE TABLE member (
-> id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> last_name VARCHAR(50),
-> first_name VARCHAR(50),
-> age TINYINT UNSIGNED,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.023 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name, age) VALUES ('고', '길동', 45);
Query OK, 1 row affected, 2 warnings (0.012 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name, age) VALUES ('김', '철수', 32);
Query OK, 1 row affected, 2 warnings (0.010 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name, age) VALUES ('박', '은혜', 26);
Query OK, 1 row affected, 2 warnings (0.010 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name, age) VALUES ('최', '원희', 25);
Query OK, 1 row affected, 2 warnings (0.009 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name, age) VALUES ('강', '진수', 21);
Query OK, 1 row affected, 2 warnings (0.009 sec)
MariaDB [phpdb]> SELECT * FROM member;
+----+-----------+------------+------+
| id | last_name | first_name | age |
+----+-----------+------------+------+
| 1 | 고 | 길동 | 45 |
| 2 | 김 | 철수 | 32 |
| 3 | 박 | 은혜 | 26 |
| 4 | 최 | 원희 | 25 |
| 5 | 강 | 진수 | 21 |
+----+-----------+------------+------+
5 rows in set (0.009 sec)
|
cs |
모든 데이터를 검색하려면 위에 처럼 SELECT * FROM member;를 사용하지만 WHERE을 사용해 조건식을 통해 특정 조건으로 데이터를 검색할 수 있다. 형태는 SELECT * FROM 테이블명 WHERE 조건식;의 형태로 사용한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
MariaDB [phpdb]> DROP TABLE member;
Query OK, 0 rows affected (0.019 sec)
MariaDB [phpdb]> CREATE TABLE member (
-> id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> last_name VARCHAR(50),
-> first_name VARCHAR(50),
-> age TINYINT UNSIGNED,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.023 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name, age) VALUES ('고', '길동', 45);
Query OK, 1 row affected, 2 warnings (0.012 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name, age) VALUES ('김', '철수', 32);
Query OK, 1 row affected, 2 warnings (0.010 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name, age) VALUES ('박', '은혜', 26);
Query OK, 1 row affected, 2 warnings (0.010 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name, age) VALUES ('최', '원희', 25);
Query OK, 1 row affected, 2 warnings (0.009 sec)
MariaDB [phpdb]> INSERT INTO member (last_name, first_name, age) VALUES ('강', '진수', 21);
Query OK, 1 row affected, 2 warnings (0.009 sec)
MariaDB [phpdb]> SELECT * FROM member;
+----+-----------+------------+------+
| id | last_name | first_name | age |
+----+-----------+------------+------+
| 1 | 고 | 길동 | 45 |
| 2 | 김 | 철수 | 32 |
| 3 | 박 | 은혜 | 26 |
| 4 | 최 | 원희 | 25 |
| 5 | 강 | 진수 | 21 |
+----+-----------+------------+------+
5 rows in set (0.009 sec)
MariaDB [phpdb]> SELECT * FROM member WHERE age > 40;
+----+-----------+------------+------+
| id | last_name | first_name | age |
+----+-----------+------------+------+
| 1 | 고 | 길동 | 45 |
+----+-----------+------------+------+
1 row in set (0.000 sec)
|
cs |
SELECT * FROM member WHERE age > 40;을 입력해 age의 값이 40이 넘어가는 데이터를 조건으로 검색하면 위와 같은 결과를 볼 수 있다.
테이블에 데이터 수정
데이터를 수정하려면 UPDATE문을 사용한다. 형태는 UPDATE 테이블명 SET 칼럼명 = "수정데이터";의 형태로 사용한다. 주의할 점은 UPDATE문을 사용하면 테이블 안의 모든 데이터가 SET으로 지정한 칼럼명에 할당된 값으로 수정이 된다.
UPDATE 테이블A SET 나이 = 20;으로 작성하면 모든 나이가 20으로 수정이 된다는 것이다. 따라서 원하는 레코드만 수정을 하려면 반드시 WHERE을 이용해서 조건을 사용해 조건에 해당하는 데이터만 수정해야한다. 주로 프라이머리 키에 해당하는 것을 조건으로 조건에 맞는 데이터에 칼럼명을 입력해 바꾸는 식으로 사용한다.
SELECT 칼럼명1, 칼럼명2... FROM 테이블명;을 사용하면 입력한 칼럼명의 데이터값만 볼 수 있다.
UPDATE 테이블명 SET 칼럼명 = "수정데이터" WHERE 프라이머리 키명 = "프라이머리 키 값";과 같은 형태로 안전하게 변경을 할 수 있다.
테이블에 데이터 삭제
데이터를 삭제하려면 DELETE를 사용한다. 형태는 DELETE FROM 테이블명;의 형태로 사용하며 이것은 등록된 데이터를 레코드 단위(1건의 데이터 전부)로 삭제할 수 있다. UPDATE와는 다르게 FROM 으로 되어 있다.
역시 UPDATE와 마찬가지로 조건을 입력하지 않으면 테이블의 모든 데이터가 삭제된다. 따라서 조건을 사용해 특정 데이터만을 삭제하게 만들어야 한다.
SELECT문에 ORDER BY 문을 사용해 SELECT 칼럼명1, 칼럼명2... FROM 테이블명 ORDER BY 칼럼명;을 사용하면 ORDER BY 뒤에 있는 칼럼명을 기준으로 오름차순으로 정렬이 된다. 내림차순으로 정리 하려면 ORDER BY 칼럼명 DESC;를 사용한다.
DELETE FROM 테이블명 WHERE 조건식;을 통해 특정 조건에 맞는 데이터를 골라서 삭제가 가능하다. LIMIT을 사용하면 검색이나 삭제에서 조회하는 건수를 제한할 수 있다.
| 명칭 | 형태 | 설명 |
| CREATE TABLE | CREATE TABLE 테이블명(칼럼명 데이터형..., 프라이머리 키); | 테이블을 생성 |
| SHOW TABLES | SHOW TABLES; | 테이블을 확인 |
| SHOW FIELDS | SHOW FIELDS FROM 테이블명; | 테이블 구조를 확인 |
| INSERT | INSERT INTO 테이블명(칼럼...) VALUES('문자열'...); | 테이블명에 데이터를 등록 |
| SELECT | SELECT * FROM 테이블명; | 데이터를 검색 |
| SELECT-WHERE | SELECT * FROM 테이블명 WHERE 조건식; | 조건식을 사용하여 데이터 검색 |
| UPDATE | UPDATE 테이블명 SET 칼럼명 = "수정데이터"; | 데이터를 수정 |
| UPDATE-WHERE | UPDATE 테이블명 SET 칼럼명 = "수정데이터" WHERE 조건식; | 조건식을 사용하여 데이터 수정 |
| DELETE | DELETE FROM 테이블명; | 데이터를 삭제 |
| DELETE-WHERE | DELETE FROM 테이블명 WHERE 조건식; | 조건식을 사용하여 데이터 삭제 |
| SELECT-FROM | SELECT 칼럼명...FROM 테이블명; | 테이블에 특정 칼럼명만 표시 |
| ORDER BY | ORDER BY 칼럼명; | 특정 칼럼명 기준 오름차순 정렬 |
| ORDER BY-DESC | ORDER BY 칼럼명 DESC; | 특정 칼럼명 기준 내림차순 정렬 |
| LIMT | LIMIT 숫자; | 검색, 삭제에서 조회 건수 제한 |
'프로그래밍 공부 > PHP' 카테고리의 다른 글
| PHP - PHP에서 데이터베이스 조작 中 (0) | 2020.02.12 |
|---|---|
| PHP - PHP에서 데이터베이스 조작 上 (0) | 2020.02.11 |
| PHP - 데이터베이스 (0) | 2020.02.07 |
| PHP - 클래스의 설계와 Iterator (0) | 2020.02.06 |
| PHP - 인스턴스, 메서드 그리고 상속과 트레이트 (0) | 2020.02.05 |
