240311 DAY 43

240311 SQL.txt

0.01MB

240311 naver_db1.sql

0.00MB

 

 

 

DML (Data Manipulation Language)

데이터 조작어(DML, Data Manipulation Language)

data를 조작하기 위해 사용하는 언어

data의 삽입, 수정, 삭제, 조회 등의 동작을 제어

INSERT(데이터 생성), UPDATE(데이터 수정), DELETE(데이터 삭제), SELECT(데이터 조회)

 

데이터베이스와 테이블이 미리 만들어져 있어야 함

• DB : koracademy
• Table : kor_mem

 

DATA 생성 1

테이블의 모든 컬럼에 데이터 입력할 경우

입력할 데이터는 반드시 컬럼의 순서와 개수가 일치해야 함

MariaDB> insert into <테이블명> values (입력데이터 , …) ;

 

no 는 auto_increment 옵션에 의해 값이 자동으로 입력되므로 빈 값만 전달

문자형은 ‘ (싱글쿼터) 나 “ (더블쿼터) 로 묶어서 입력하고 숫자형은 값만 입력

now() : 현재 날짜와 시간을 알아오는 MariaDB 내장 함수 

 

 

DATA 생성 2

특정 컬럼을 지정해서 데이터 입력할 경우

앞에 입력한 컬럼명과 입력데이터의 순서와 개수가 일치해야 함

MariaDB> insert into <테이블명> (컬럼명 , …) values (입력데이터 , …) ;

 

 

DATA 생성 3

특정 컬럼을 지정해서 데이터 입력할 경우

MariaDB> insert into <테이블명> set <컬럼명> = <입력데이터> [ , ... ] ; 

 

 

 

 

DATA 조회 1

테이블 내 모든 데이터 조회

테이블에 저장된 컬럼 순서대로 조회

MariaDB> select * from <테이블 명>;

 

DATA 조회 2

특정 컬럼을 지정해서 조회

컬럼 순서는 테이블에 저장된 순서가 아닌 조회할 때 지정한 순서로 조회됨

MariaDB> select <컬럼명 [ , ... ] > from <테이블 명>;

 

DATA 조회 3

조건을 지정해서 원하는 데이터만 조회

조건은 기본 연산자를 이용해 지정 가능

조건이 여러 개인 경우 논리연산자(and , or) 를 이용해 하나의 결과 값으로 만들어 줘야 함

MariaDB> select <컬럼명 [ , ... ]> from <테이블 명> where <조건>;

 

- 조건 1개 일치하는 데이터 조회 

> select * from kor_mem where id = ‘test1’;

 

- 조건 2개 일치하는 데이터 조회

> select * from kor_mem where age > 20 and age < 29;

 

- 조건 3개 일치하는 데이터 조회

> select * from kor_mem where id = ‘test1’ or no = 3 and name = ‘test3’;

조건을 3개 이상 지정하는 경우 연산자 우선순위를 잘 고려 해야 함

and 가 or 보다 우선순위가 높음

 

만약 위 예제에서 or를 먼저 실행하고 싶은 경우는

먼저 연산하고 싶은 조건에 ( ) 를 입력

> select * from kor_mem where (id = ‘test’ or no = 3) and name = ‘test3’;

 

 

- 조건과 비슷한 데이터 조회( like 연산자)

메타 문자

• > % : 자리 수 제한 없이 모든 문자를 의미
• > _ : 한 자리 수의 모든 문자를 의미

 

총 4글자 중 앞에 tes 로 시작하고 마지막 한 글자는 아무거나 모두 조회

> select * from kor_mem where id like 'tes_';

 

글자수 제한 없고 가장 앞에 tes 로 시작하는 데이터 조회

> select * from kor_mem where id like 'tes%';

 

글자수 제한 없고 12 가 포함된 모든 데이터 조회

> select * from kor_mem where pass like '%12%';

 

 

 

DATA 수정

where 로 조건을 지정하지 않으면 테이블 내 모든 레코드의 값이 변경됨

MariaDB> update <테이블명> set <컬럼명> = <수정데이터> [ , ... ] [ where <조건> ] ;

 

> update kor_mem set pass = '1q2w3e4r', age = 31 where id = 'test3' ;

> select * from kor_mem where id = 'test3';

 

 

 

DATA 삭제

where 로 조건을 지정하지 않으면 테이블 내 모든 데이터가 삭제됨

MariaDB> delete from <테이블명> [ where <조건> ] ;

> delete from kor_mem where no = 4; 

> select * from kor_mem; 

 

모든 데이터 삭제

> delete from kor_mem; 

> select * from kor_mem; 

 

※ update, delete뒤에는 무조건 where

where가 없으면 모든 데이터가 수정되거나 모든 데이터가 삭제된다는 뜻이므로 where

 

 

[root@localhost ~]# mariadb

MariaDB [(none)]> show databases; 

MariaDB [(none)]> use k_aca

Database changed
MariaDB [k_aca]> show tables;

테이블 확인 후 이렇게 똑같이 되어있어야 된다 

 

 

 

데이터 입력 insert into

MariaDB [k_aca]> insert into kor_mem values(null,'test1','qwer1234','test1',25,now());
Query OK, 1 row affected (0.002 sec)

MariaDB [k_aca]> select * from kor_mem;

 

 

 

특정 컬럼을 지정해서 데이터 입력

MariaDB [k_aca]> insert into kor_mem (name,id,pass,reg_date) values('test2','test2','1234qwer',now());
Query OK, 1 row affected (0.001 sec)

MariaDB [k_aca]> select * from kor_mem; → 데이터 확인 

 

 

MariaDB [k_aca]> insert into kor_mem set id='tset3', pass='P@ssw0rd', name='test3', age=30, reg_date=now();
Query OK, 1 row affected (0.001 sec)

MariaDB [k_aca]> select * from kor_mem; → 데이터 확인 

 

 

 

모든 열(컬럼)의 데이터 확인 

MariaDB [k_aca]> select * from kor_mem;

 

특정 컬럼을 지정해서 조회

MariaDB [k_aca]> select name from kor_mem; → name 열의 데이터만 확인 

 

MariaDB [k_aca]> select id, name from kor_mem; → id, name 열의 데이터만 확인 

 

MariaDB [k_aca]> select id, name, pass from kor_mem; → id, name, pass 열의 데이터만 확인 

 

조건 1개 일치하는 데이터 조회 

MariaDB [k_aca]> select * from kor_mem where id = 'test1';

 

조건 2개 

MariaDB [k_aca]> select age from kor_mem where id = 'tset3';

 

조건 2개 일치하는 데이터 조회 

MariaDB [k_aca]> select * from kor_mem where age > 20 and age < 29;

20살 초과 29살 미만이라는 뜻으로 21 ~ 28살 사이에 있는 데이터를 조회한다 

일단 보면 test1의 age가 25살이므로 test1의 데이터만 나와야 한다 

 

아래와 같이 test1만 나왔다 

 

테이블 수정 

MariaDB [k_aca]> update kor_mem
    -> set id = 'test3' where no = 3;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 tset3에서 test3로 수정 완료 

 

 

조건 3개 일치하는 데이터 조회 

조건을 3개 이상 지정하는 경우 연산자 우선순위를 잘 고려 해야 함

and 가 or 보다 우선순위가 높음

MariaDB [k_aca]> select * from kor_mem where id = 'test1' or no = 3 and name = 'test3';

 

만약 위 예제에서 or를 먼저 실행하고 싶은 경우는

먼저 연산하고 싶은 조건에 ( ) 를 입력

MariaDB [k_aca]> select * from kor_mem where (id = 'test' or no = 3) and name = 'test3';

 

 

 

 

조건과 비슷한 데이터 조회 (like 연산자)

- 총 4글자 중에 앞에 test로 시작하고 마지막 한 글자는 아무거나 모두 조회 

만약에 test가를 조회하고 싶다면 Underbar 'test___';이렇게 해야한다

왜냐면 한글문자 1개 = 3byte임 

하나도 기억안나면 ______ 6개를 주면되지만 이게 불편하므로 %를 넣어준다

MariaDB [k_aca]> select * from kor_mem where id like 'test_';

 

- 글자수 제한 없고 가장 앞에 test로 시작하는 데이터 조회

MariaDB [k_aca]> select * from kor_mem where id like 'tes%';

 

- 글자수 제한 없고 12가 포함된 모든 데이터 조회 

MariaDB [k_aca]> select * from kor_mem where pass like '%12%';

 

Data 수정 

where로 조건을 지정하지 않으면 테이블 내 모든 레코드의 값이 변경되므로 항상 where 조건을 붙인다 

MariaDB [k_aca]> update kor_mem
    -> set pass = '1q2w3e4r',
    -> age = 31
    -> where id = 'test3';

id가 test3인 행에 pass는 qwer1234에서 1q2w3e4r로,

name test1은 test3로 변경된다는 뜻이다 
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [k_aca]> select * from kor_mem where id = 'test3';

 

MariaDB [k_aca]> update kor_mem
    -> set name = 'test' where no = 1;

no 1의 name test3는 test으로 변경 
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [k_aca]> select * from kor_mem where id = 'test1';

 

Data 삭제

where로 조건을 지정하지 않으면 테이블 내 모든 데이터 삭제되므로 where 조건을 붙인다 

MariaDB [k_aca]> delete from kor_mem where no = 3;
Query OK, 1 row affected (0.001 sec)

MariaDB [k_aca]> select * from kor_mem;

delete는 행단위로 처리된다 

MariaDB [k_aca]> delete from kor_mem where = id;
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 '= id' at line 1

id만 지우려고 하니까 오류뜸 

 

 

MariaDB [k_aca]> delete from kor_mem;
Query OK, 2 rows affected (0.001 sec)

MariaDB [k_aca]> select * from kor_mem;
Empty set (0.000 sec)

delete는 테이블 틀만 남아 있으므로 Empty라고 뜬다

아예 싹 다 지우려면 drop 

 

그래서 확인해보면 틀은 남아있음

MariaDB [k_aca]> desc kor_mem;

 

# 

 

 

 

 

 

query 일괄처리 

query 일괄처리

파일의 형태로 SQL 명령을 작성해서 저장한 뒤 DBMS에 바로 적용 시키는 방법

 

파일 생성

텍스트 파일에 처리할 SQL 명령을 작성한 후 저장

저장할 때 확장자를 .sql 로 지정, 인코딩 형식을 UTF-8로 지정

 

 

DBMS 서버로 파일 이동

이동 방법은 상황에 따라 알아서 이동할 것

wget , ftp , scp , USB 등..

 

일괄 처리

# mysql –uroot –p

> show variables like ‘c%’; 문자집합 확인(latin1 – 영어만 가능)

> create database naver_db default character set utf8 collate utf8_general_ci;

데이터베이스를 생성하면서 CharacterSet을 utf8로 지정

UTF-8 : 전 세계 언어에서 사용되는 문자들을 하나의 체계로 통합, Unicode를 위한 방식

> exit

# mysql < 파일명

# mysql

> use naver_db;

> desc member;

> select * from member; → 한글 내용이 있는 데이터 확인

 

 

💡DML 실습 

사전작업

#dnf -y install ftp → ftp 패키지 설치 

[root@localhost ~]# ftp 10.10.56.137
Connected to 10.10.56.137 (10.10.56.137).
220 Microsoft FTP Service
Name (10.10.56.137:root): anonymous
331 Anonymous access allowed, send identity (e-mail name) as password.
Password: [ENTER]
230 User logged in.
Remote system type is Windows_NT.
ftp> 

만약에 failed 뜨면 ftp> bye라고 입력하면 #으로 빠져나온다 

ftp> bin
200 Type set to I.
ftp> hash
Hash mark printing on (1024 bytes/hash mark). hash 마크 있으면 다운로드가 완료되었다는 의미 
ftp> prompt
Interactive mode off.
ftp> ls
227 Entering Passive Mode (10,10,56,137,240,201).
125 Data connection already open; Transfer starting.
03-10-24  01:00PM       <DIR>          DOC
03-07-24  10:13AM       <DIR>          ISO
03-07-24  10:12AM       <DIR>          Program
03-10-24  08:08PM       <DIR>          source
03-10-24  11:39PM       <DIR>          source_1
03-07-24  12:46PM                  756 Topology�� ĥ�� ����(lg.comȸ���� ����).txt
226 Transfer complete.

 

ftp> cd doc
250 CWD command successful.
ftp> !pwd
/root

ftp Client는 기본적으로 현재 디렉토리에 다운로드가 된다?

 

그래서 Local Change Delectory lcd 명령어 사용 

ftp> lcd /
Local directory now /
ftp> get naver_db1.sql
local: naver_db1.sql remote: naver_db1.sql
227 Entering Passive Mode (10,10,56,137,240,227).
125 Data connection already open; Transfer starting.
##
226 Transfer complete.
2077 bytes received in 0.000587 secs (3538.33 Kbytes/sec)
ftp> bye

 

[root@localhost ~]# cd /
[root@localhost /]# ls

 

일괄처리 

[root@localhost /]# mysql -uroot -p
Enter password: [ENTER]
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.5.22-MariaDB MariaDB Server
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 variables  like 'c%'; → 문자집합 확인(latin1 – 영어만 가능)

MariaDB [(none)]> create database naver_db default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.001 sec)
→ 데이터베이스를 생성하면서 CharacterSet을 utf8로 지정

→ UTF-8 : 전 세계 언어에서 사용되는 문자들을 하나의 체계로 통합, Unicode를 위한 방식
MariaDB [(none)]> show databases;

 

MariaDB [(none)]> use naver_db
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 [naver_db]> show tables;

 

MariaDB [naver_db]> select * from member; → 한글 데이터 확인

# 

 

 

 리눅스 창 하나 더 켜서 

[root@localhost ~]# cd /
[root@localhost /]# mysql < naver_db1.sql 

[root@localhost /]# nl naver_db1.sql
     1 use naver_db
     2
     3 create table member (
     4 no int auto_increment primary key,
     5 id varchar(10) not null unique,
     6 name varchar(20) not null,
     7 sex char,
     8 post_num char(8),
     9 address varchar(80),
    10 tel varchar(15),
    11 age int );
    12
    13 insert into member values (NULL, 'yjhwang', '황영주', 'M', '100-011', '서울시 중구 충무로1가', '234-8879', 35);
    14 insert into member values (NULL, 'khshul', '설기형', 'M', '607-010', '부산시 동래구 명륜동', '764-3784', 33);
    15 insert into member values (NULL, 'chpark', '박철호', 'M', '503-200', '광주시 남구 지석동', '298-9730', 34);
    16 insert into member values (NULL, 'shlee', '이상훈', 'M', '503-200', '광주시 남구 도금동', '838-4347', 32);
    17 insert into member values (NULL, 'jyjang', '장영숙', 'W', '503-201', '부산시 영도구 봉래동5가', '399-9809', 24);
    18 insert into member values (NULL, 'yjbae', '배용진', 'M', '606-065', '서울시 은평구 응암4동', '857-5683', 30);
    19 insert into member values (NULL, 'hbpark', '박혜빈', 'W', '122-014', '경기도 과천시 중앙동', '234-7677', 22);
    20 insert into member values (NULL, 'mskim', '김문수', 'M', '427-760', '경기도 시흥시 신천동', '370-6003', 63);
    21 insert into member values (NULL, 'bkcha', '차범길', 'M', '429-020', '대전시 서구 둔산1동', '432-9877', 49);
    22 insert into member values (NULL, 'kskim', '김길수', 'M', '302-121', '경기도 수원시 장안구 파장동', '324-5875', 54);
    23 insert into member values (NULL, 'srkim', '김수련', 'M', '440-747', '대구시 달서구 신당동', '987-3688', 23);
    24 insert into member values (NULL, 'srlee', '이성현', 'M', '704-701', '경기도 수원시 권선구 매산로1가', '243-6844', 36);
    25 insert into member values (NULL, 'hnjang', '정한나', 'W', '441-081', '광주시 서구 화정4동', '845-4547', 58);
    26 insert into member values (NULL, 'mylee', '이명연', 'W', '502-791', '광주시 서구 쌍촌동', '837-9432', 33);
    27 insert into member values (NULL, 'yskim', '김영숙', 'W', '429-010', '경기도 시흥시 대야동', '374-8438', 53);
[root@localhost /]# 

# 

 

 

 

naver_db DB 의 member 테이블에서 작업 

자신의 계정 추가(본인 입력하고 싶은 임의 값 입력) 

MariaDB [naver_db]> insert into member values (null, 'yunakim', '김연아','W','009-005','경기도 군포시 산본동','900-0905',34);
Query OK, 1 row affected (0.001 sec)

MariaDB [naver_db]> desc member;

MariaDB [naver_db]> select * from member;

하단에 김연아 계 생성 확인 

# 

 

'배용진' 의 모든 정보 출력

MariaDB [naver_db]> select * from member where name = '배용진';

# 

 

 

20대의 이름, 주소, 나이 출력

MariaDB [naver_db]> select name,address,age from member where age between 20 and 29; 

# 

 

 

 

남성의 이름, 나이, 성별, 전화번호, 아이디 출력

 

MariaDB [naver_db]> select name, age, sex, tel, id from member where sex = 'M';

# 

 

 

 

30세 미만 또는 50세 이상 여성의 나이, 주소 출력 

MariaDB [naver_db]> select age, address from member where age > 30 or age < 50 and sex = 'W';

# 

 

 

20대 또는 40대 남성의 나이, 아이디, 전화번호 출력 

MariaDB [naver_db]> select age, id, tel from member where age > 30 or age < 39 and age < 50 and sex = 'M';

# 

 

 

김씨 성을 가진 남자의 이름, 나이, 전화번호 출력

MariaDB [naver_db]> select name, age, tel from member where name like '김%' and sex = 'M';

# 

 

 

주소가 서울 인 사람의 이름 , 우편번호, 주소, 전화번호 출력 

MariaDB [naver_db]> select name, post_num, address, tel from member where address like '서울%';

# 

 

 

광주에 사는 50세 이상의 이름, 성별, 아이디 출력

 

MariaDB [naver_db]> select name, sex, id from member where address like '광주%' and age < 50;

# 

 

 

이름이 '수' 나 '숙' 으로 끝나는 50대의 성별, 이름, 나이 출력

MariaDB [naver_db]> select sex, name, age from member where name like '%%수' or name like '%%숙' and age between 49 and 60;

# 

 

 

경기도에 사는 여성 중 20대의 이름, 주소, 전화번호 출력

MariaDB [naver_db]> select name, address, tel from member where address like '경기%' and age between 20 and 30 and sex = 'W';

# 

 

 

아이디의 세번째 자리가 p 나 k 인 사람의 아이디, 나이 출력

MariaDB [naver_db]> select id, name from member where id like  '__p%' or id like  '__k%';

# 

 

 

 

‘박철호’ 이사(주소 광주에서 서울시 동대문구 왕십리로 변경) 

MariaDB [naver_db]> update member
    -> set address = '서울시 동대문구 왕십리'
    -> where name = '박철호';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [naver_db]> select * from member where name = '박철호';

# 

 

 

‘이성현’ 회원 탈퇴(계정 정보 삭제)

 

MariaDB [naver_db]> delete from member where name = '이성현';
Query OK, 1 row affected (0.001 sec)

MariaDB [naver_db]> select * from member where name = '이성현';
Empty set (0.000 sec) → 삭제 확인 

# 

 

 

 

 

강사님 답안 확인 (내거랑 비교 및 더블체크)  

1. 자신의 계정 추가(본인 입력하고 싶은 임의 값 입력)
     > insert into member values(null,'koruser','케이','M','135-933','서울시 강남구 역삼동','1544-9970',17);

null: 비어있는

🤔왜 값을 입력하지 않을까❓

auto increment를 줬기 때문에 자동적으로 증가시키기 때문에 null입력한다 

 

  2. '배용진＇ 의 모든 정보 출력 

MariaDB [naver_db]> select * from member where name = '배용진'; 

# 

 

  3. 20대의 이름, 주소, 나이 출력

MariaDB [naver_db]> select name, address, age from member where age >=20 or age < 30;

# 

 

  4. 남성의 이름, 나이, 성별, 전화번호, 아이디 출력

MariaDB [naver_db]> select name, age, sex, tel, id from member where sex = 'M';

# 

 

  5. 30세 미만 또는 50세 이상 여성의 나이, 주소 출력 

MariaDB [naver_db]> select age, address from member where (age < 30 or age > 50) and sex = 'W';

 

강사님은 이렇게 하심 ↓

MariaDB [naver_db]> select age, address from member where (age<30 or age >= 50) and sex = 'W';

 

  6. 20대 또는 40대 남성의 나이, 아이디, 전화번호 출력 

MariaDB [naver_db]> select age, id, tel from member where (( age between 20  and  29) or (age between 40 and 49)) and sex='M';

between A and B 쓰고 싶으면 20대니까 20 ~ 29이렇게 해야할듯? 아까 20~30하니까 30대가 튀어나옴

 

강사님 답안 ↓

MariaDB [naver_db]> select age, id, tel from member where ((age >= 20 and age < 30) or (age >= 40 and age < 50)) and sex='M';

#

 

  7. 김씨 성을 가진 남자의 이름, 나이, 전화번호 출력

MariaDB [naver_db]> select name, age, tel from member where name like '김%' and sex = 'M';

# 

 

 

8. 주소가 서울 인 사람의 이름 , 우편번호, 주소, 전화번호 출력

MariaDB [naver_db]> select name, post_num, address, tel from member where address like '서울%'; 

# 

 

  9. 광주에 사는 50세 이상의 이름, 성별, 아이디 출력

MariaDB [naver_db]> select name, sex, id from member where address like '광주%' and age >=50;

# 

 

  10. 이름이 '수' 나 '숙' 으로 끝나는 50대의 성별, 이름, 나이 출력

MariaDB [naver_db]> select sex, name, age from member where (name like '%수' or name like '%숙') and (age between 50 and 59);

 

강사님 답안 ↓

MariaDB [naver_db]> select sex, name, age from member where (name like '%수' or name like '%숙') and (age >=50 and age <60);

# 

 

  11. 경기도에 사는 여성 중 20대의 이름, 주소, 전화번호 출력

MariaDB [naver_db]> select name, address, tel from member where address like '경기도%' and sex = 'W' and
age between 20 and 29;

강사님 답안 ↓

MariaDB [naver_db]> select name, address, tel from member where address like '경기도%' and sex='W' and age>=20 and age<30;

#  

  12. 아이디의 세번째 자리가 p 나 k 인 사람의 아이디, 나이 출력

MariaDB [naver_db]> select id, age from member where id like '__p%' or id like '__k%';

# 

 

  13. ‘박철호’ 이사(주소 광주에서 서울시 동대문구 왕십리로 변경)

MariaDB [naver_db]> update member
    -> set address = '서울 동대문구 왕십리' 
    -> where name = '박철호';
Query OK, 1 row affected (0.009 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

MariaDB [naver_db]> select * from member where id = 'chpark';

 

강사님 답안↓

MariaDB [naver_db]> update member
    -> set address = '서울시 동대문구 왕십리'
    -> where id = 'chpark';

# 

 

  14. ‘이성현’ 회원 탈퇴(계정 정보 삭제)

MariaDB [naver_db]> delete from member where name = '이성현';
Query OK, 0 rows affected (0.001 sec)

MariaDB [naver_db]> select * from member where  name = '이성현';
Empty set (0.000 sec)

 

강사님 답안 ↓

MariaDB [naver_db] > delete from member where id='srlee';

# 

 

 

 

만약에 

ap 졸p

abp 졸려p

abcp 개졸려p 

 

이중에 졸려p만 출력하고 싶다면 ______p라고 하면된다

한글 하나 당 3byte이므로. 

개졸려p를 출력하고 싶다면 _________p