Seunghee Ryu

Seunghee Ryu

하루 하루 배우는 것을 기록합니다.

2020-10-26 TIL

29 minute read

stateful, stateless

Stateless 의 문제점

  • 한 번 연결하고 응답하고 나면 연결이 끊기는데 어떻게 클라이언트를 특정할 수 있는지?

DataInput/OutputStram

  • 데이터를 타입 별로 받아들이기 좋다.

ex04 stateful

  • 메인 스레드가 모든 일을 다 하기 때문에 하나의 클라이언트와 연결되면 다른 클라이언트는 accept() 에서 기다리고 있어야 한다.
  • 처음 연결된 클라이언트가 종료되고 나면 두번째로 연결된 클라이언트에 응답을 흔다.

ex04 stateful 2

  • 계산 결과를 서버에 저장해둔다.
  • 메인 스레드가 혼자 일을 한다.

ex04 stateful 3

  • 스레드를 사용해서 동시에 여러 클라이언트의 요청에 응답할 수 있다.
  • 소켓을 통해서 다이렉트로 어드레스를 받아오는 것보다 주소를 다루는 별도의 클래스를 분리시킨 것이 InetSocketAddress 이다.
    • InetSocketAddress 객체를 통해 주소화 포트번호가 담긴 객체를 얻은 다음에 거기서 뽑아서 쓰는 것이 확장성이 더 좋다.
  • 메인 스레드는 클라이언트 요청이 들어오면 accept 를 통해 socket 객체를 만들어서 새로운 스레드에게 일을 시킨다.
  • run() 메소드를 실행하고 processRequest를 호출해서 클라이언트의 요청을 해결하는 동안 만들어지는 모든 로컬 변수는 각 스레드에서 생성되는 stack 메모리에 저장된다.

ex04 stateless

  • 서버와의 연결을 최소한으로 하기 위해서 클라이언트에 값을 다 입력하고 나서 연산을 처리할 때만(엔터를 칠 때) 연결한다.
  • 버퍼 스트림을 쓸 때는 out.flush()를 꼭 사용해야 한다.
  • 스테이트리스는 둘 사이의 요청 응답이 끝나면 연결을 끊는다.

ex04 stateless 2

  • stateless 에서 클라이언트를 구분하고 작업 결과를 유지하는 방법
  • ID 를 Map 에 등록하고 클라이언트가 서버에서 반환된 ID 를 저장해둔다.

  • ID와 함께 저장된 result 값을 다음 연산에 사용한다.

  • 쿠키 : 웹 서버가 웹 브라우저에게 보내는 정보(객체), 인증 정보를 웹 브라우저에 저장하고 사용자가 요청을 할 때 그 정보를 함께 보내서 사용자를 식별할 수 있도록 도와준다.
  • 세션 : 로그인, 사용자 닉네임, 접속 시간 등 서버가 알아야 할 정보를 저장해 놓는다. 만료가 되었다는 것은 세션에 지정된 시간이 종료되었다는 것을 뜻하는 듯하다.

sql (eomcs-docs-sql)

DDL(Data Definition Language)

DB 객체(테이블, 뷰, 함수, 트리거 등)를 생성, 변경, 삭제하는 SQL 명령이다.

sql

  • 색인표에 특정 컬럼의 값을 정렬시키는 문법이 인덱스이다.
  • 색인표에 자료를 정렬해두고 찾으면
    • 장점 : 훨씬 빠른 속도로 찾을 수 있게 된다.
    • 단점 : 파일의 크기가 커진다. (하드디스크 메모리를 사용한다.)
  • 기준 항목에 대해 정렬하는 것이 색인표이다.
  • 새 데이터를 추가하거나 데이터를 변경하면 색인표도 갱신해야 한다.
    • 추가, 변경, 삭제 시 실행 속도가 느리다.
    • 대신 검색 속도가 빠르다.

컬럼 값 자동 증가 (auto-increment)

  • 숫자 타입의 PK 컬럼인 경우 값을 1씩 자동 증가시킬 수 있다.
  • 즉, 데이터를 입력할 때 해당 컬럼의 값을 넣지 않아도 자동으로 증가된다.
  • 단, 삭제를 해서 중간에 비어있는 번호는 다시 채우지 않는다. 증가된 번호는 계속 앞으로 증가할 뿐이다.
  • 프라이머리에 낫널 컬럼이라도 오토-인크리먼트라면 입력이 가능하다.
  • 마리아db는 인서트를 하다가 실패하면 번호가 올라가지 않지만, 다른 db는 인서트를 하다가 실패하면 번호가 자동으로 증가될 때가 있다.

뷰 (view)

  • 프로젝션 : 어떤 컬럼 값을 꺼낼 지 선택하는 것.
  • 셀렉션 : 데이터(row) 를 선택하는 것.
  • 웨어 : 레코드를 선택하는 것.
  • 뷰로 만들어진 테이블은 가상 테이블이다.
Last login: Mon Oct 26 14:02:02 on ttys000
 rsh@rsh  ~
 mysql -u study -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.6-MariaDB Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| studydb            |
+--------------------+
2 rows in set (0.005 sec)

MariaDB [(none)]> use studydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [studydb]> show tables;
+-------------------+
| Tables_in_studydb |
+-------------------+
| test1             |
+-------------------+
1 row in set (0.000 sec)

MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.009 sec)

MariaDB [studydb]> show tables;
Empty set (0.000 sec)

MariaDB [studydb]> create table test1(
    ->   no int primary key,
    ->   name varchar(20),
    ->   age int,
    ->   kor int,
    ->   eng int,
    ->   math int,
    ->   constraint test1_uk unique (name, age),
    ->   fulltext index test1_name_idx (name)
    -> );
Query OK, 0 rows affected (0.033 sec)

MariaDB [studydb]> insert into test1(no,name,age,kor,eng,math) values(1,'aaa',20,80,80,80);
Query OK, 1 row affected (0.004 sec)

MariaDB [studydb]> insert into test1(no,name,age,kor,eng,math) values(2,'bbb',21,90,80,80);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(no,name,age,kor,eng,math) values(3,'ccc',20,80,80,80);
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> insert into test1(no,name,age,kor,eng,math) values(4,'ddd',22,90,80,80);
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> insert into test1(no,name,age,kor,eng,math) values(5,'eee',20,80,80,80); 
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+----+------+------+------+------+------+
| no | name | age  | kor  | eng  | math |
+----+------+------+------+------+------+
|  1 | aaa  |   20 |   80 |   80 |   80 |
|  2 | bbb  |   21 |   90 |   80 |   80 |
|  3 | ccc  |   20 |   80 |   80 |   80 |
|  4 | ddd  |   22 |   90 |   80 |   80 |
|  5 | eee  |   20 |   80 |   80 |   80 |
+----+------+------+------+------+------+
5 rows in set (0.002 sec)

MariaDB [studydb]> select * from test1 where name = 'ccc';
+----+------+------+------+------+------+
| no | name | age  | kor  | eng  | math |
+----+------+------+------+------+------+
|  3 | ccc  |   20 |   80 |   80 |   80 |
+----+------+------+------+------+------+
1 row in set (0.006 sec)

MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.016 sec)

MariaDB [studydb]> create table test1 (
    ->   name varchar(3),
    ->   kor int,
    ->   eng int,
    ->   math int,
    ->   sum int,
    ->   aver int
    -> );
Query OK, 0 rows affected (0.012 sec)

MariaDB [studydb]> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(3) | YES  |     | NULL    |       |
| kor   | int(11)    | YES  |     | NULL    |       |
| eng   | int(11)    | YES  |     | NULL    |       |
| math  | int(11)    | YES  |     | NULL    |       |
| sum   | int(11)    | YES  |     | NULL    |       |
| aver  | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
6 rows in set (0.006 sec)

MariaDB [studydb]> alter table test1
    ->   add column no int;
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [studydb]> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(3) | YES  |     | NULL    |       |
| kor   | int(11)    | YES  |     | NULL    |       |
| eng   | int(11)    | YES  |     | NULL    |       |
| math  | int(11)    | YES  |     | NULL    |       |
| sum   | int(11)    | YES  |     | NULL    |       |
| aver  | int(11)    | YES  |     | NULL    |       |
| no    | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
7 rows in set (0.003 sec)

MariaDB [studydb]> alter table test1
    ->   add column age int; 
Query OK, 0 rows affected (0.020 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [studydb]> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(3) | YES  |     | NULL    |       |
| kor   | int(11)    | YES  |     | NULL    |       |
| eng   | int(11)    | YES  |     | NULL    |       |
| math  | int(11)    | YES  |     | NULL    |       |
| sum   | int(11)    | YES  |     | NULL    |       |
| aver  | int(11)    | YES  |     | NULL    |       |
| no    | int(11)    | YES  |     | NULL    |       |
| age   | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
8 rows in set (0.004 sec)

MariaDB [studydb]> alter table test1
    ->   add column no2 int,
    ->   add column age2 int;  
Query OK, 0 rows affected (0.014 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [studydb]> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(3) | YES  |     | NULL    |       |
| kor   | int(11)    | YES  |     | NULL    |       |
| eng   | int(11)    | YES  |     | NULL    |       |
| math  | int(11)    | YES  |     | NULL    |       |
| sum   | int(11)    | YES  |     | NULL    |       |
| aver  | int(11)    | YES  |     | NULL    |       |
| no    | int(11)    | YES  |     | NULL    |       |
| age   | int(11)    | YES  |     | NULL    |       |
| no2   | int(11)    | YES  |     | NULL    |       |
| age2  | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
10 rows in set (0.003 sec)

MariaDB [studydb]> alter table test1
    ->   add constraint test1_pk primary key (no),
    ->   add constraint test1_uk unique (name, age),
    ->   add fulltext index test1_name_idx (name);
Query OK, 0 rows affected, 1 warning (0.041 sec)
Records: 0  Duplicates: 0  Warnings: 1

MariaDB [studydb]> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(3) | YES  | MUL | NULL    |       |
| kor   | int(11)    | YES  |     | NULL    |       |
| eng   | int(11)    | YES  |     | NULL    |       |
| math  | int(11)    | YES  |     | NULL    |       |
| sum   | int(11)    | YES  |     | NULL    |       |
| aver  | int(11)    | YES  |     | NULL    |       |
| no    | int(11)    | NO   | PRI | NULL    |       |
| age   | int(11)    | YES  |     | NULL    |       |
| no2   | int(11)    | YES  |     | NULL    |       |
| age2  | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
10 rows in set (0.004 sec)

MariaDB [studydb]> alter table test1
    ->   modify column name varchar(20) not null,
    ->   modify column age int not null,
    ->   modify column kor int not null,
    ->   modify column eng int not null,
    ->   modify column math int not null,
    ->   modify column sum int not null,
    ->   modify column aver float not null;
Query OK, 0 rows affected (0.051 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [studydb]> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | NO   | MUL | NULL    |       |
| kor   | int(11)     | NO   |     | NULL    |       |
| eng   | int(11)     | NO   |     | NULL    |       |
| math  | int(11)     | NO   |     | NULL    |       |
| sum   | int(11)     | NO   |     | NULL    |       |
| aver  | float       | NO   |     | NULL    |       |
| no    | int(11)     | NO   | PRI | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
| no2   | int(11)     | YES  |     | NULL    |       |
| age2  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
10 rows in set (0.003 sec)

MariaDB [studydb]> insert into test1(no,name,age,kor,eng,math,sum,aver)
    ->   values(1,'aaa',20,100,100,100,300,100);
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> insert into test1(no,name,age,kor,eng,math,sum,aver)
    ->   values(2,'bbb',21,100,100,100,300,100);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | NO   | MUL | NULL    |       |
| kor   | int(11)     | NO   |     | NULL    |       |
| eng   | int(11)     | NO   |     | NULL    |       |
| math  | int(11)     | NO   |     | NULL    |       |
| sum   | int(11)     | NO   |     | NULL    |       |
| aver  | float       | NO   |     | NULL    |       |
| no    | int(11)     | NO   | PRI | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
| no2   | int(11)     | YES  |     | NULL    |       |
| age2  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
10 rows in set (0.003 sec)

MariaDB [studydb]> select * from test1;
+------+-----+-----+------+-----+------+----+-----+------+------+
| name | kor | eng | math | sum | aver | no | age | no2  | age2 |
+------+-----+-----+------+-----+------+----+-----+------+------+
| aaa  | 100 | 100 |  100 | 300 |  100 |  1 |  20 | NULL | NULL |
| bbb  | 100 | 100 |  100 | 300 |  100 |  2 |  21 | NULL | NULL |
+------+-----+-----+------+-----+------+----+-----+------+------+
2 rows in set (0.001 sec)

MariaDB [studydb]> insert into test1(no,name,age,kor,eng,math,sum,aver)
    ->   values(3,'bbb',21,100,100,100,300,100); 
ERROR 1062 (23000): Duplicate entry 'bbb-21' for key 'test1_uk'
MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.010 sec)

MariaDB [studydb]> create table test1(
    ->   no int not null,
    ->   name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.016 sec)

MariaDB [studydb]> alter table test1
    ->   modify column no int not null auto_increment;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
MariaDB [studydb]> alter table test1
    ->   add constraint primary key (no); /* 일단 no를 pk로 지정한다.*/
Query OK, 0 rows affected (0.016 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [studydb]> alter table test1
    -> 
    -> ;
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [studydb]> alter table test1
    ->   modify column no int not null auto_increment;
Query OK, 0 rows affected (0.034 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [studydb]> desc test1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| no    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.005 sec)

MariaDB [studydb]> insert into test1(no, name) values(1, 'xxx');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+----+------+
| no | name |
+----+------+
|  1 | xxx  |
+----+------+
1 row in set (0.000 sec)

MariaDB [studydb]> insert into test1(name) values('aaa');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+----+------+
| no | name |
+----+------+
|  1 | xxx  |
|  2 | aaa  |
+----+------+
2 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(no, name) values(100, 'yyy');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+-----+------+
| no  | name |
+-----+------+
|   1 | xxx  |
|   2 | aaa  |
| 100 | yyy  |
+-----+------+
3 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(name) values('bbb');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+-----+------+
| no  | name |
+-----+------+
|   1 | xxx  |
|   2 | aaa  |
| 100 | yyy  |
| 101 | bbb  |
+-----+------+
4 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(name) values('ccc');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(name) values('ddd');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+-----+------+
| no  | name |
+-----+------+
|   1 | xxx  |
|   2 | aaa  |
| 100 | yyy  |
| 101 | bbb  |
| 102 | ccc  |
| 103 | ddd  |
+-----+------+
6 rows in set (0.000 sec)

MariaDB [studydb]> delete from test1 where no=103;
Query OK, 1 row affected (0.003 sec)

MariaDB [studydb]> insert into test1(name) values('eee');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+-----+------+
| no  | name |
+-----+------+
|   1 | xxx  |
|   2 | aaa  |
| 100 | yyy  |
| 101 | bbb  |
| 102 | ccc  |
| 104 | eee  |
+-----+------+
6 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(name) values('fff');
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> select * from test1;
+-----+------+
| no  | name |
+-----+------+
|   1 | xxx  |
|   2 | aaa  |
| 100 | yyy  |
| 101 | bbb  |
| 102 | ccc  |
| 104 | eee  |
| 105 | fff  |
+-----+------+
7 rows in set (0.000 sec)

MariaDB [studydb]> 
MariaDB [studydb]> insert into test1(name) values('123456789012345678901234');
ERROR 1406 (22001): Data too long for column 'name' at row 1
MariaDB [studydb]> insert into test1(name) values('ggg');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+-----+------+
| no  | name |
+-----+------+
|   1 | xxx  |
|   2 | aaa  |
| 100 | yyy  |
| 101 | bbb  |
| 102 | ccc  |
| 104 | eee  |
| 105 | fff  |
| 106 | ggg  |
+-----+------+
8 rows in set (0.000 sec)

MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.004 sec)

MariaDB [studydb]> create table test1 (
    ->   no int primary key auto_increment,
    ->   name varchar(20) not null,
    ->   class varchar(10) not null,
    ->   working char(1) not null,
    ->   tel varchar(20)
    -> );
Query OK, 0 rows affected (0.017 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('aaa','java100','Y');
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('bbb','java100','N');
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('ccc','java100','Y');
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('ddd','java100','N');
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('eee','java100','Y');
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('kkk','java101','N');
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('lll','java101','Y');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('mmm','java101','N');
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('nnn','java101','Y');
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('ooo','java101','N'); 
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+----+------+---------+---------+------+
| no | name | class   | working | tel  |
+----+------+---------+---------+------+
|  1 | aaa  | java100 | Y       | NULL |
|  2 | bbb  | java100 | N       | NULL |
|  3 | ccc  | java100 | Y       | NULL |
|  4 | ddd  | java100 | N       | NULL |
|  5 | eee  | java100 | Y       | NULL |
|  6 | kkk  | java101 | N       | NULL |
|  7 | lll  | java101 | Y       | NULL |
|  8 | mmm  | java101 | N       | NULL |
|  9 | nnn  | java101 | Y       | NULL |
| 10 | ooo  | java101 | N       | NULL |
+----+------+---------+---------+------+
10 rows in set (0.000 sec)

MariaDB [studydb]> select no, name, class from test1 where working = 'Y';
+----+------+---------+
| no | name | class   |
+----+------+---------+
|  1 | aaa  | java100 |
|  3 | ccc  | java100 |
|  5 | eee  | java100 |
|  7 | lll  | java101 |
|  9 | nnn  | java101 |
+----+------+---------+
5 rows in set (0.000 sec)

MariaDB [studydb]> select no, name, class from test1 where working = 'N';
+----+------+---------+
| no | name | class   |
+----+------+---------+
|  2 | bbb  | java100 |
|  4 | ddd  | java100 |
|  6 | kkk  | java101 |
|  8 | mmm  | java101 |
| 10 | ooo  | java101 |
+----+------+---------+
5 rows in set (0.000 sec)

MariaDB [studydb]> create view worker
    ->   as select no, name, class from test1 where working = 'Y';
Query OK, 0 rows affected (0.015 sec)

MariaDB [studydb]> show tables;
+-------------------+
| Tables_in_studydb |
+-------------------+
| test1             |
| worker            |
+-------------------+
2 rows in set (0.000 sec)

MariaDB [studydb]> select * from worker;
+----+------+---------+
| no | name | class   |
+----+------+---------+
|  1 | aaa  | java100 |
|  3 | ccc  | java100 |
|  5 | eee  | java100 |
|  7 | lll  | java101 |
|  9 | nnn  | java101 |
+----+------+---------+
5 rows in set (0.003 sec)

MariaDB [studydb]> insert into test1(name,class,working) values('ppp','java101','Y');
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> select * from worker;
+----+------+---------+
| no | name | class   |
+----+------+---------+
|  1 | aaa  | java100 |
|  3 | ccc  | java100 |
|  5 | eee  | java100 |
|  7 | lll  | java101 |
|  9 | nnn  | java101 |
| 11 | ppp  | java101 |
+----+------+---------+
6 rows in set (0.000 sec)

MariaDB [studydb]> drop view worker;
Query OK, 0 rows affected (0.002 sec)

MariaDB [studydb]> show tables;
+-------------------+
| Tables_in_studydb |
+-------------------+
| test1             |
+-------------------+
1 row in set (0.000 sec)

MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.003 sec)

MariaDB [studydb]> show tables;
Empty set (0.000 sec)

MariaDB [studydb]>

DML(Data Manipulation Language)

데이터 등록, 변경, 삭제를 다루는 SQL 문법

insert

  • 데이터를 입력할 때 사용하는 문법이다.
  • select 결과를 테이블이 insert 할 때
    • where 을 먼저 실행한 뒤 그 결과에서 필요한 값을 뽑아낸다.
  • select 결과를 테이블에 바롱 립력하기
    • 셀렉트 결과의 컬럼명과 인서트 테이블의 컬럼명이 같을 필요는 없다.
    • 그러나 결과의 컬럼 개수와 인서트 하려는 컬럼 개수가 같아야 한다.
    • 결과의 컬럼 타입과 인서트 하려는 컬럼의 타입이 같거나 입력할 수 있는 타입이어야 한다.
MariaDB [studydb]> create table test1 (
    ->   no int not null,
    ->   name varchar(20) not null,
    ->   tel varchar(20) not null,
    ->   fax varchar(20),
    ->   pstno varchar(5),
    ->   addr varchar(200)
    -> );
Query OK, 0 rows affected (0.016 sec)

MariaDB [studydb]> alter table test1
    ->   add constraint test1_pk primary key (no);
Query OK, 0 rows affected, 1 warning (0.019 sec)
Records: 0  Duplicates: 0  Warnings: 1

MariaDB [studydb]> alter table test1
    ->   modify column no int not null auto_increment;
Query OK, 0 rows affected (0.031 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [studydb]> desc test1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| no    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)  | NO   |     | NULL    |                |
| tel   | varchar(20)  | NO   |     | NULL    |                |
| fax   | varchar(20)  | YES  |     | NULL    |                |
| pstno | varchar(5)   | YES  |     | NULL    |                |
| addr  | varchar(200) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
6 rows in set (0.005 sec)

MariaDB [studydb]> insert into test1 values(null,'aaa','111','222','10101','seoul');
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> insert into test1(name,fax,tel,no,pstno,addr) 
    ->     values('bbb','222','111',null,'10101','seoul');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+----+------+-----+------+-------+-------+
| no | name | tel | fax  | pstno | addr  |
+----+------+-----+------+-------+-------+
|  1 | aaa  | 111 | 222  | 10101 | seoul |
|  2 | bbb  | 111 | 222  | 10101 | seoul |
+----+------+-----+------+-------+-------+
2 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(name,tel) values('ccc','333');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+----+------+-----+------+-------+-------+
| no | name | tel | fax  | pstno | addr  |
+----+------+-----+------+-------+-------+
|  1 | aaa  | 111 | 222  | 10101 | seoul |
|  2 | bbb  | 111 | 222  | 10101 | seoul |
|  3 | ccc  | 333 | NULL | NULL  | NULL  |
+----+------+-----+------+-------+-------+
3 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(name,tel) values
    -> ('aaa', '1111'),
    -> ('bbb', '2222'),
    -> ('ccc', '3333');
Query OK, 3 rows affected (0.001 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [studydb]> select * from test1;
+----+------+------+------+-------+-------+
| no | name | tel  | fax  | pstno | addr  |
+----+------+------+------+-------+-------+
|  1 | aaa  | 111  | 222  | 10101 | seoul |
|  2 | bbb  | 111  | 222  | 10101 | seoul |
|  3 | ccc  | 333  | NULL | NULL  | NULL  |
|  4 | aaa  | 1111 | NULL | NULL  | NULL  |
|  5 | bbb  | 2222 | NULL | NULL  | NULL  |
|  6 | ccc  | 3333 | NULL | NULL  | NULL  |
+----+------+------+------+-------+-------+
6 rows in set (0.000 sec)

MariaDB [studydb]> create table test2 (
    ->   no int not null primary key auto_increment,
    ->   name varchar(20) not null,
    ->   tel varchar(20) not null,
    ->   kor int,
    ->   eng int,
    ->   math int
    -> );
Query OK, 0 rows affected (0.016 sec)

MariaDB [studydb]> show tables;
+-------------------+
| Tables_in_studydb |
+-------------------+
| test1             |
| test2             |
+-------------------+
2 rows in set (0.000 sec)

MariaDB [studydb]>   select name, tel from test1 where addr='seoul'; 
+------+-----+
| name | tel |
+------+-----+
| aaa  | 111 |
| bbb  | 111 |
+------+-----+
2 rows in set (0.000 sec)

MariaDB [studydb]>   select name, tel from test1;
+------+------+
| name | tel  |
+------+------+
| aaa  | 111  |
| bbb  | 111  |
| ccc  | 333  |
| aaa  | 1111 |
| bbb  | 2222 |
| ccc  | 3333 |
+------+------+
6 rows in set (0.000 sec)

MariaDB [studydb]> select * from test1;
+----+------+------+------+-------+-------+
| no | name | tel  | fax  | pstno | addr  |
+----+------+------+------+-------+-------+
|  1 | aaa  | 111  | 222  | 10101 | seoul |
|  2 | bbb  | 111  | 222  | 10101 | seoul |
|  3 | ccc  | 333  | NULL | NULL  | NULL  |
|  4 | aaa  | 1111 | NULL | NULL  | NULL  |
|  5 | bbb  | 2222 | NULL | NULL  | NULL  |
|  6 | ccc  | 3333 | NULL | NULL  | NULL  |
+----+------+------+------+-------+-------+
6 rows in set (0.000 sec)

MariaDB [studydb]>   select name, tel from test1 where addr='seoul'; 
+------+-----+
| name | tel |
+------+-----+
| aaa  | 111 |
| bbb  | 111 |
+------+-----+
2 rows in set (0.000 sec)

MariaDB [studydb]> desc test2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| no    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| tel   | varchar(20) | NO   |     | NULL    |                |
| kor   | int(11)     | YES  |     | NULL    |                |
| eng   | int(11)     | YES  |     | NULL    |                |
| math  | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.003 sec)

MariaDB [studydb]> drop table test2;
Query OK, 0 rows affected (0.003 sec)

MariaDB [studydb]> create table test2 (
    ->   no int not null primary key auto_increment,
    ->   fullname varchar(20) not null,
    ->   phone varchar(20) not null,
    ->   kor int,
    ->   eng int,
    ->   math int
    -> );
Query OK, 0 rows affected (0.021 sec)

MariaDB [studydb]> insert into test2(fullname,phone)
    ->   select name, tel from test1 where addr='seoul'; 
Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [studydb]> select * from test2;
+----+----------+-------+------+------+------+
| no | fullname | phone | kor  | eng  | math |
+----+----------+-------+------+------+------+
|  1 | aaa      | 111   | NULL | NULL | NULL |
|  2 | bbb      | 111   | NULL | NULL | NULL |
+----+----------+-------+------+------+------+
2 rows in set (0.000 sec)

MariaDB [studydb]> insert into test2(name,tel)
    ->   select name, tel, fax from test1 where addr='seoul'; 
ERROR 1136 (21S01): Column count doesn't match value count at row 1
MariaDB [studydb]> insert into test2(name,kor)
    ->   select name, tel from test1 where addr='seoul'; 
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
MariaDB [studydb]>

update

  • 등록된 데이터를 변경할 때 사용하는 명령이다.
  • qutocommit
    • 명령창에서 실행하면 바로 실제 테이블에 적용된다.
    • set autocommit=false; 로 하면 오토 커밋이 되지 않는다.
  • commit; 을 하면 입력한 명령어들이 전부 테이블에 반영되어 고정된다.
  • commit; 이전에 작업한 것들은 rollback 되지 않는다.
  • 기존 명령창에서 데이터 변경 후 commit; 을 하고 다른 명령창에서 새로 접속해서 살펴보면 최신 데이터를 보여주게 된다.
    • mysql은 다른 명령창에서 auto-commit 을 true; 로 설정해놔야 기존 명령창에서 커밋한 것들이 자동으로 업데이트 되어 보여진다.
    • flase 인 경우 다른 명령창에서 commit; 명령어를 먼저 입력해야 한다.
    • 오라클인 경우는 다른 명령창의 커밋 설정이 오토 커밋을 기본값으로 가지기 때문에 오토 커밋을 트루로 할 필요가 없다.
MariaDB [studydb]> select * from test1;
+----+------+------+------+-------+-------+
| no | name | tel  | fax  | pstno | addr  |
+----+------+------+------+-------+-------+
|  1 | aaa  | 111  | 222  | 10101 | seoul |
|  2 | bbb  | 111  | 222  | 10101 | seoul |
|  3 | ccc  | 333  | NULL | NULL  | NULL  |
|  4 | aaa  | 1111 | NULL | NULL  | NULL  |
|  5 | bbb  | 2222 | NULL | NULL  | NULL  |
|  6 | ccc  | 3333 | NULL | NULL  | NULL  |
+----+------+------+------+-------+-------+
6 rows in set (0.000 sec)

MariaDB [studydb]> update test1 set pstno='11111', fax='222' where no=3;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [studydb]> select * from test1;
+----+------+------+------+-------+-------+
| no | name | tel  | fax  | pstno | addr  |
+----+------+------+------+-------+-------+
|  1 | aaa  | 111  | 222  | 10101 | seoul |
|  2 | bbb  | 111  | 222  | 10101 | seoul |
|  3 | ccc  | 333  | 222  | 11111 | NULL  |
|  4 | aaa  | 1111 | NULL | NULL  | NULL  |
|  5 | bbb  | 2222 | NULL | NULL  | NULL  |
|  6 | ccc  | 3333 | NULL | NULL  | NULL  |
+----+------+------+------+-------+-------+
6 rows in set (0.000 sec)

MariaDB [studydb]> update test1 set tel='3030', fax='1212' where no=2;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [studydb]> select * from test1;
+----+------+------+------+-------+-------+
| no | name | tel  | fax  | pstno | addr  |
+----+------+------+------+-------+-------+
|  1 | aaa  | 111  | 222  | 10101 | seoul |
|  2 | bbb  | 3030 | 1212 | 10101 | seoul |
|  3 | ccc  | 333  | 222  | 11111 | NULL  |
|  4 | aaa  | 1111 | NULL | NULL  | NULL  |
|  5 | bbb  | 2222 | NULL | NULL  | NULL  |
|  6 | ccc  | 3333 | NULL | NULL  | NULL  |
+----+------+------+------+-------+-------+
6 rows in set (0.000 sec)

MariaDB [studydb]> rollback;
Query OK, 0 rows affected (0.000 sec)

MariaDB [studydb]> select * from test1;
+----+------+------+------+-------+-------+
| no | name | tel  | fax  | pstno | addr  |
+----+------+------+------+-------+-------+
|  1 | aaa  | 111  | 222  | 10101 | seoul |
|  2 | bbb  | 3030 | 1212 | 10101 | seoul |
|  3 | ccc  | 333  | 222  | 11111 | NULL  |
|  4 | aaa  | 1111 | NULL | NULL  | NULL  |
|  5 | bbb  | 2222 | NULL | NULL  | NULL  |
|  6 | ccc  | 3333 | NULL | NULL  | NULL  |
+----+------+------+------+-------+-------+
6 rows in set (0.000 sec)

MariaDB [studydb]> set autocommit=false;
Query OK, 0 rows affected (0.003 sec)

MariaDB [studydb]> delete from test1 where no = 4;
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> select * from test1;
+----+------+------+------+-------+-------+
| no | name | tel  | fax  | pstno | addr  |
+----+------+------+------+-------+-------+
|  1 | aaa  | 111  | 222  | 10101 | seoul |
|  2 | bbb  | 3030 | 1212 | 10101 | seoul |
|  3 | ccc  | 333  | 222  | 11111 | NULL  |
|  5 | bbb  | 2222 | NULL | NULL  | NULL  |
|  6 | ccc  | 3333 | NULL | NULL  | NULL  |
+----+------+------+------+-------+-------+
5 rows in set (0.000 sec)

MariaDB [studydb]> update test1 set tel = '777';
Query OK, 5 rows affected (0.000 sec)
Rows matched: 5  Changed: 5  Warnings: 0

MariaDB [studydb]> insert into test1(name, tel) valued('hong', '1212');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'valued('hong', '1212')' at line 1
MariaDB [studydb]> insert into test1(name, tel) values('hong', '1212);
    '> ;
    '> );
    '> quit
    '> ');
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> select * from test1;
+----+------+-------------------+------+-------+-------+
| no | name | tel               | fax  | pstno | addr  |
+----+------+-------------------+------+-------+-------+
|  1 | aaa  | 777               | 222  | 10101 | seoul |
|  2 | bbb  | 777               | 1212 | 10101 | seoul |
|  3 | ccc  | 777               | 222  | 11111 | NULL  |
|  5 | bbb  | 777               | NULL | NULL  | NULL  |
|  6 | ccc  | 777               | NULL | NULL  | NULL  |
|  7 | hong | 1212);
;
);
quit
 | NULL | NULL  | NULL  |
+----+------+-------------------+------+-------+-------+
6 rows in set (0.000 sec)

MariaDB [studydb]> rollback;
Query OK, 0 rows affected (0.002 sec)

MariaDB [studydb]> select * from test1;
+----+------+------+------+-------+-------+
| no | name | tel  | fax  | pstno | addr  |
+----+------+------+------+-------+-------+
|  1 | aaa  | 111  | 222  | 10101 | seoul |
|  2 | bbb  | 3030 | 1212 | 10101 | seoul |
|  3 | ccc  | 333  | 222  | 11111 | NULL  |
|  4 | aaa  | 1111 | NULL | NULL  | NULL  |
|  5 | bbb  | 2222 | NULL | NULL  | NULL  |
|  6 | ccc  | 3333 | NULL | NULL  | NULL  |
+----+------+------+------+-------+-------+
6 rows in set (0.000 sec)

MariaDB [studydb]> delete from test1 where no = 4;
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> update test1 set tel = '777';
Query OK, 5 rows affected (0.000 sec)
Rows matched: 5  Changed: 5  Warnings: 0

MariaDB [studydb]> commit;
Query OK, 0 rows affected (0.001 sec)

MariaDB [studydb]> insert into test1(name, tel) values('hong', '1212');
Query OK, 1 row affected (0.000 sec)

MariaDB [studydb]> select * from test1;
+----+------+------+------+-------+-------+
| no | name | tel  | fax  | pstno | addr  |
+----+------+------+------+-------+-------+
|  1 | aaa  | 777  | 222  | 10101 | seoul |
|  2 | bbb  | 777  | 1212 | 10101 | seoul |
|  3 | ccc  | 777  | 222  | 11111 | NULL  |
|  5 | bbb  | 777  | NULL | NULL  | NULL  |
|  6 | ccc  | 777  | NULL | NULL  | NULL  |
|  8 | hong | 1212 | NULL | NULL  | NULL  |
+----+------+------+------+-------+-------+
6 rows in set (0.000 sec)

MariaDB [studydb]> rollback;
Query OK, 0 rows affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+----+------+-----+------+-------+-------+
| no | name | tel | fax  | pstno | addr  |
+----+------+-----+------+-------+-------+
|  1 | aaa  | 777 | 222  | 10101 | seoul |
|  2 | bbb  | 777 | 1212 | 10101 | seoul |
|  3 | ccc  | 777 | 222  | 11111 | NULL  |
|  5 | bbb  | 777 | NULL | NULL  | NULL  |
|  6 | ccc  | 777 | NULL | NULL  | NULL  |
+----+------+-----+------+-------+-------+
5 rows in set (0.000 sec)

마이그레이션

  • 기존 버젼의 테이블 구조에서 신규 구조의 테이블에 맞춰서 데이터를 옮기는 것.

Categories:

Updated:

You may also enjoy