테이블 생성, 구조변경, 삭제, 구조수정, DML테스트, 일괄처리, SQLite (w 파이썬) (교육 52일차)

SW·2023년 2월 3일

실습> 테이블 생성하기

[DBMS]# mysql mysql
MariaDB [mysql]> DESC user;

+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
  :
  :(생략)

MariaDB [mysql]> USE kh_db
MariaDB [mysql]> \e

CREATE TABLE user
(
    Host char(60) NOT NULL,
    User char(16) NOT NULL,
    Password char(41) NOT NULL,
    PRIMARY KEY(`Host`, `User`)
)

MariaDB [kh_db]> SHOW TABLES;

+-----------------+
| Tables_in_kh_db |
+-----------------+
| kh_member       |
| user            |
+-----------------+
2 rows in set (0.00 sec)

MariaDB [kh_db]> DESC user;

+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Host     | char(60) | NO   | PRI | NULL    |       |
| User     | char(16) | NO   | PRI | NULL    |       |
| Password | char(41) | NO   |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [kh_db]> DROP TABLE user;
Query OK, 0 rows affected (0.00 sec)

MariaDB [kh_db]> SHOW TABLES;

+-----------------+
| Tables_in_kh_db |
+-----------------+
| kh_member       |
+-----------------+
1 row in set (0.00 sec)

실습> 테이블 생성하기

데이터 타입(자료형)
C & C++ & Java
자료형 변수명;
int a;
a = 1;

SQL
컬럼명 자료형;

1byte 8bit
0 ~ 255 까지의 값을 가진다.

signed (+, - 를 표현할 수 있다.)
맨 왼쪽이 부호비트를 의미하므로 7bit 만 사용이 가능한다.

unsigned (+ 만 표현할 수 있다., -는 표현할 수 없다.)
맨 왼쪽도 값으로 사용할 수 있으므로 8bit 전체를 사용이 가능한다.

int (4byte) signed -2147483648 ~ 2147483647
int (4byte) unsigned 0 ~ 4294967295

[DBMS]# yum -y install python3
[DBMS]# python3

>>> 2 ** 8
256  
>>> 2 ** 8 - 1
255
>>> 2 ** 7 
128
>>> 2 ** 7 - 1
127

tinyint signed 범위: -128 ~ 127

가장 첫 번째 bit를 부호비트로 사용

  +-- + : 0 
  |
+---+---+---+---+---+---+---+---+
| + | 0 | 0 | 0 | 0 | 0 | 0 | 0 |       
+---+---+---+---+---+---+---+---+
+---+---+---+---+---+---+---+---+
| + | 1 | 1 | 1 | 1 | 1 | 1 | 1 |       
+---+---+---+---+---+---+---+---+

  +-- + : 1 
  |
+---+---+---+---+---+---+---+---+
| - | 0 | 0 | 0 | 0 | 0 | 0 | 0 |       
+---+---+---+---+---+---+---+---+
+---+---+---+---+---+---+---+---+
| - | 1 | 1 | 1 | 1 | 1 | 1 | 1 |       
+---+---+---+---+---+---+---+---+

숫자 자료형 테스트

MariaDB [kh_db]> CREATE TABLE t1(no tinyint);
Query OK, 0 rows affected (0.01 sec)

MariaDB [kh_db]> DESC t1;

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| no    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

데이터 입력 형식:
INSERT INTO <테이블명> VALUES( 값 ... );
MariaDB [kh_db]> INSERT INTO t1 VALUES(128);
Query OK, 1 row affected, 1 warning (0.00 sec)

데이터 입력 형식:
SELECT <컬럼명> FROM <테이블명>;
MariaDB [kh_db]> SELECT * FROM t1;

+------+
| no   |
+------+
|  127 |  <-- 값의 범위를 넘어가면 에러가 아니라 마지막 숫자가 저장된다.
+------+
1 row in set (0.00 sec)

-128까지만 저장되기 때문에 범위를 넘어가면 가장 마지막 숫자가 저장된다.
MariaDB [kh_db]> INSERT INTO t1 VALUES(-129);
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [kh_db]> SELECT * FROM t1;

+------+
| no   |
+------+
|  127 |
| -128 |
+------+
2 rows in set (0.00 sec)

tinyint unsigned 범위: 0 ~ 255
가장 첫 번째 bit를 부호비트로 사용하지 않는다.

+---+---+---+---+---+---+---+---+
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |       
+---+---+---+---+---+---+---+---+
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |       
+---+---+---+---+---+---+---+---+

unsigned를 사용하기 위해서는 자료형 뒤에서 써야 한다.
MariaDB [kh_db]> CREATE TABLE t2(no tinyint unsigned);
Query OK, 0 rows affected (0.00 sec)

MariaDB [kh_db]> DESC t2;

+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| no    | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [kh_db]> INSERT INTO t2 VALUES(-1);
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [kh_db]> SELECT no FROM t2;

+------+
| no   |
+------+
|    0 |  <-- 값의 범위를 넘어가면 에러가 아니라 가장 작은 숫자 0이 저장된다.
+------+
1 row in set (0.00 sec)

MariaDB [kh_db]> INSERT INTO t2 VALUES(256);
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [kh_db]> SELECT * FROM t2;

+------+
| no   |
+------+
|    0 |
|  255 |  <-- 값의 범위를 넘어가면 에러가 아니라 가장 큰 숫자 255가 저장된다.
+------+
2 rows in set (0.00 sec)

문자 자료형 테스트
MariaDB [kh_db]> CREATE TABLE t3 (name char(1));
Query OK, 0 rows affected (0.01 sec)

MariaDB [kh_db]> DESC t3;

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [kh_db]> INSERT INTO t3 VALUES('a');
Query OK, 1 row affected (0.00 sec)

MariaDB [kh_db]> SELECT * FROM t3;

+------+
| name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

글자 2개는 범위가 벗어났기 때문에 저장이 안된다.
MariaDB [kh_db]> INSERT INTO t3 VALUES('ab');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [kh_db]> SELECT name FROM t3;

+------+
| name |
+------+
| a    |
| a    |
+------+
2 rows in set (0.00 sec)

MariaDB [kh_db]> \s
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8

MariaDB [kh_db]> INSERT INTO t3 VALUES('가');
Query OK, 1 row affected (0.00 sec)

MariaDB [kh_db]> SELECT name FROM t3;

+------+
| name |
+------+
| a    |
| a    |
| 가   |  <-- utf8 형식의 한글 한 글자는 3byte 이지만 저장된 이유는 char(1)은 글자수를 의미한다.
+------+
3 rows in set (0.00 sec)

두 글자를 저장하면 저장이 안된다.
MariaDB [kh_db]> INSERT INTO t3 VALUES('가나');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [kh_db]> SELECT name FROM t3;

+------+
| name |
+------+
| a    |
| a    |
| 가   |
| 가   |  <-- 범위가 벗어났기 때문에 두 글자는 저장이 안된다.
+------+
4 rows in set (0.00 sec)

MariaDB [kh_db]> exit

UTF-8, utf8 형식의 한글 byte를 확인한다.

# echo $LANG
ko_KR.UTF-8

# echo 가 > a.txt
# ll a.txt 
-rw-r--r--. 1 root root 4  2월  3 01:45 a.txt
# od -c a.txt 
0000000 352 260 200  \n  <-- (엔터)
0000004
# cat a.txt 
가
#  <-- 프롬프트가 밑으로 떨어지는 이유는 a.txt 파일의 끝에 엔터(\n)가 있기 때문이다.

실습> 테이블 구조 변경하기

ALTER: 바꾸다, 고치다
ALTER TABLE 명령어를 사용한다.

ALTER TABLE 형식
ALTER TABLE <테이블명> <컬럼명 자료형 옵션 제약조건>;

ACTION 종류
ADD: 컬럼 추가
DROP: 컬럼 삭제
MODIFY: 컬럼 수정
CHANGE: 컬럼 변경
RENAME: 테이블명 변경

1. 컬럼 추가
ADD로 컬럼을 추가하면 마지막 컬럼에 추가된다. (iptables -A INPUT 을 생각하면 된다.)
ALTER TABLE kh_member ADD nickname VARCHAR(30) UNIQUE;

MariaDB [kh_db]> ALTER TABLE kh_member ADD nickname VARCHAR(30) UNIQUE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0