[Practice] DDL, DML

1. DDL

1) 테이블 생성

- DB명: practice

member 테이블
컬럼	데이터 타입	NULL 유무	옵션	코멘트
id	VARCHAR(20)	NOT NULL	UNIQUE	아이디
pass	VARCHAR(20)	NOT NULL		비밀번호
name	VARCHAR(20)	NOT NULL		이름
sex	CHAR(1)	NULL		성별
tel	VARCHAR(20)	NULL	DEFAULT '010-0000-0000'	전화번호
address	VARCHAR(90)	NULL		주소

freeboard 테이블
컬럼	데이터 타입	NULL 유무	옵션	코멘트
num	INT	NOT NULL	AUTO_INCREMENT PRIMARY KEY	일련번호
name	VARCHAR(20)	NOT NULL		이름
email	VARCHAR(20)	NULL		메일 주소
subject	VARCHAR(100)	NOT NULL		제목
content	VARCHAR(500)	NOT NULL		글 내용
regist_day	VARCHAR(20)	NULL		글쓴 날짜
ip	VARCHAR(20)	NULL		접속 IP

- 실습 쿼리문

DROP DATABASE IF EXISTS `practice`;
CREATE DATABASE `practice`;
USE `practice`;

DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
  `id` VARCHAR(20) NOT NULL UNIQUE COMMENT "아이디",
  `pass` VARCHAR(20) NOT NULL COMMENT "비밀번호",
  `name` VARCHAR(20) NOT NULL COMMENT "이름",
  `sex` CHAR(1) NULL COMMENT "성별",
  `tel` VARCHAR(20) NULL DEFAULT "010-0000-0000" COMMENT "전화번호",
  `address` VARCHAR(90) NULL COMMENT "주소"
);

DROP TABLE IF EXISTS `freeboard`;
CREATE TABLE `freeboard` (
  `num` INT NOT NULL AUTO_INCREMENT COMMENT "일련번호",
  `name` VARCHAR(20) NOT NULL COMMENT "이름",
  `email` VARCHAR(20) NULL COMMENT "메일주소",
  `subject` VARCHAR(100) NOT NULL COMMENT "제목",
  `content` VARCHAR(500) NOT NULL COMMENT "글내용",
  `regist_day` VARCHAR(20) NULL COMMENT "글쓴날짜",
  `ip` VARCHAR(20) NULL COMMENT "접속 IP",
  PRIMARY KEY(`num`)
);

2) 테이블 구조 변경

순서	내용
1	member 테이블 가장 위에 일련 번호 칼럼 추가 - 컬럼명: no, 데이터 타입: int, 옵션: 번호 자동 증가, 제약 조건: PK
2	member 테이블의 비밀번호 컬럼 데이터 타입 VARCHAR(50)으로 변경
3	member 테이블의 성별 컬럼을 나이 컬럼으로 변경 - 컬럼명: age, 데이터 타입: INT, 옵션 및 제약 조건 없음
4	member 테이블의 주소 컬럼을 작성 일시 컬럼으로 변경 - 컬럼명: reg_date, 데이터 타입: DATETIME, 옵션 없음, 제약조건: NOT NULL
5	member 테이블의 전화번호 컬럼 삭제
6	member 테이블 이름을 'mem'으로 변경
7	freeboard 테이블의 이름 컬럼 다음에 게시글 비밀번호 컬럼 추가 - 컬럼명: passwd, 데이터 타입: VARCHAR(20), 옵션: 기본값 0000, 제약조건 없음
8	freeboard 테이블의 글 내용 컬럼 데이터 타입을 TEXT로 변경
9	freeboard 테이블의 글쓴 날짜 컬럼명 및 데이터 타입 변경 - 컬럼명: reg_date, 데이터 타입: DATETIME, 옵션 없음, 제약 조건: NOT NULL
10	freeboard 테이블의 메일 주소 컬럼 삭제

- 실습 쿼리문

/* member 테이블의 가장 위에 일련 번호 컬럼 추가
컬렴명 : no , 데이터 타입 : int , 옵션 : 번호 자동증가 , 제약조건 : primary key */
ALTER TABLE `member` ADD `no` INT AUTO_INCREMENT PRIMARY KEY FIRST;

/* member 테이블의 비밀번호 컬럼 데이터 타입을 varchar(50) 으로 수정 */
ALTER TABLE `member` MODIFY `pass` VARCHAR(50);

/* member 테이블의 성별 컬럼을 나이 컬럼으로 변경
컬럼명 : age , 데이터 타입 : int , 옵션 및 제약조건 없음 */
ALTER TABLE `member` CHANGE `sex` `age` INT;

/* member 테이블의 주소 컬럼을 작성일시 컬럼으로 변경
컬럼명: reg_date , 데이터 타입 : datetime , 옵션 없음 , 제약조건 : not null */
ALTER TABLE `member` CHANGE `address` `reg_date` DATETIME NOT NULL;

/* member 테이블의 전화번호 컬럼 삭제 */
ALTER TABLE `member` DROP `tel`;

/* member 테이블 이름을 kh_mem 으로 변경 */
ALTER TABLE `member` RENAME `mem`;
RENAME TABLE `member` TO `mem`;

/* freeboard 테이블의 이름 컬럼 다음에 게시글 비밀번호 컬럼 추가
컬럼명 : passwd , 데이터 타입 : varchar(20) , 옵션 : 기본값 0000 으로 설정 , 제약조건 없음 */
ALTER TABLE `freeboard` ADD `passwd` VARCHAR(20) DEFAULT "0000" AFTER `name`;

/* freeboard 테이블의 글 내용 컬럼 데이터 타입을 text 로 수정 */
ALTER TABLE `freeboard` MODIFY `content` TEXT;

/* freeboard 테이블의 글쓴 날짜 컬럼 이름 및 데이터 타입 변경
컬럼명 : reg_date , 데이터 타입: datetime , 옵션 없음 , 제약조건 : not null */
ALTER TABLE `freeboard` CHANGE `regist_day` `reg_date` DATETIME NOT NULL;

/* freeboard 테이블의 메일주소 컬럼 삭제 */
ALTER TABLE `freeboard` DROP `email`;

 

 

 

2. DML

1) 쿼리 일괄 처리

쿼리 일괄 처리

drop database if exists naver_db;
create database naver_db;

use naver_db

create table member (
no int auto_increment primary key,
id varchar(10) not null unique,
name varchar(20) not null,
sex char,
post_num char(8),
address varchar(80),
tel varchar(15),
age int );

insert into member values ('', 'yjhwang', '황영주', 'M', '100-011', '서울시 중구 충무로1가', '234-8879', 35);
insert into member values ('', 'khshul', '설기형', 'M', '607-010', '부산시 동래구 명륜동', '764-3784', 33);
insert into member values ('', 'chpark', '박철호', 'M', '503-200', '광주시 남구 지석동', '298-9730', 34);
insert into member values ('', 'shlee', '이상훈', 'M', '503-200', '광주시 남구 도금동', '838-4347', 32);
insert into member values ('', 'jyjang', '장영숙', 'W', '503-201', '부산시 영도구 봉래동5가', '399-9809', 24);
insert into member values ('', 'yjbae', '배용진', 'M', '606-065', '서울시 은평구 응암4동', '857-5683', 30);
insert into member values ('', 'hbpark', '박혜빈', 'W', '122-014', '경기도 과천시 중앙동', '234-7677', 22);
insert into member values ('', 'mskim', '김문수', 'M', '427-760', '경기도 시흥시 신천동', '370-6003', 63);
insert into member values ('', 'bkcha', '차범길', 'M', '429-020', '대전시 서구 둔산1동', '432-9877', 49);
insert into member values ('', 'kskim', '김길수', 'M', '302-121', '경기도 수원시 장안구 파장동', '324-5875', 54);
insert into member values ('', 'srkim', '김수련', 'M', '440-747', '대구시 달서구 신당동', '987-3688', 23);
insert into member values ('', 'srlee', '이성현', 'M', '704-701', '경기도 수원시 권선구 매산로1가', '243-6844', 36);
insert into member values ('', 'hnjang', '정한나', 'W', '441-081', '광주시 서구 화정4동', '845-4547', 58);
insert into member values ('', 'mylee', '이명연', 'W', '502-791', '광주시 서구 쌍촌동', '837-9432', 33);
insert into member values ('', 'yskim', '김영숙', 'W', '429-010', '경기도 시흥시 대야동', '374-8438', 53);

2) 데이터 추가 및 변경

순서	내용
1	member 테이블에서 작업
2	자신의 계정 추가
3	'배용진'의 모든 정보 출력
4	20대의 이름, 주소, 나이 출력
5	남성의 이름, 나이, 성별, 전화번호, 아이디 출력
6	30세 미만 또는 50세 이상 여성의 나이, 주소 출력
7	20대 혹은 40대 남성의 나이, 아이디, 전화번호 출력
8	김씨 성을 가진 남자의 이름, 나이, 전화번호 출력
9	주소가 서울 인 사람의 이름, 우편번호, 주소, 전화번호 출력
10	광주에 사는 50세 이상의 이름, 성별, 아이디 출력
11	이름이 '수'나 '숙'으로 끝나는 50대의 성별, 이름, 나이, 출력
12	경기도에 사는 여성 중 20대의 이름, 주소, 전화번호 출력
13	아이디의 세번째 자리가 p나 k인 사람의 아이디, 나이 출력
14	'박철호' 이사 (주소 광주에서 서울시 동대문구 왕십리로 변경)
15	'이성현' 회원 탈퇴 (계정 정보 삭제)

- 실습 쿼리문

/* 자신의 계정 추가 */
INSERT INTO `member` VALUES ('', 'ever', '조', 'M', '133-136', '서울시 성동구 홍익동', '010-1234-5678', 35);

/* '배용진' 의 모든 정보 출력 */
SELECT * FROM `member` WHERE name = '배용진';

/* 20대 여성의 이름, 주소, 나이 출력 */
SELECT name, address, age FROM `member` WHERE age >= 20 AND age < 30;
SELECT name, address, age FROM `member` WHERE age BETWEEN 20 AND 29;
SELECT name, address, age FROM `member` WHERE age LIKE '2%';

/* 남성의 이름, 나이, 성별, 전화번호, 아이디 출력 */
SELECT name, age, sex, post_num, id FROM `member` WHERE sex = 'M';

/* 30세 미만 또는 50세 이상 여성의 나이, 주소 출력 */
SELECT age, address FROM `member` WHERE (age < 30 OR age >= 50) AND sex = 'W';
-- ㄴ AND 연산자가 OR 연산자보다 연산의 우선 순위가 높다(주의)

/* 20대 또는 40대 남성의 나이, 아이디, 전화번호 출력 */
SELECT age, id, tel FROM `member`
  WHERE ((age >= 20 AND age < 30)
  OR (age >= 40 AND age < 50)) AND sex = 'M';

SELECT age, id, tel FROM `member`
  WHERE (age LIKE '2%' OR age LIKE '4%') AND sex = 'M';

/* 김씨 성을 가진 남자의 이름, 나이, 전화번호 출력 */
SELECT name, age, tel FROM `member` WHERE name = '김%';

/* 주소가 서울 인 사람의 이름 , 우편번호, 주소, 전화번호 출력 */
SELECT name, post_num, address, tel FROM `member` WHERE address LIKE '서울%';

/* 광주에 사는 50세 이상의 이름, 성별, 아이디 출력 */
SELECT name, sex, id FROM `member`
  WHERE address LIKE '광주%' AND age >= 50;

/* 이름이 '수' 나 '숙' 으로 끝나는 50대의 성별, 이름, 나이 출력 */
SELECT sex, name, age FROM `member`
  WHERE name LIKE '%수' OR name LIKE '%숙';

/* 경기도에 사는 여성 중 20대의 이름, 주소, 전화번호 출력 */
SELECT name, address, tel FROM `member`
  WHERE address LIKE '경기%' 
    AND sex = 'W'
      AND age >= 20 AND age < 30;

/* 아이디의 세번째 자리가 p 나 k 인 사람의 아이디, 나이 출력 */
SELECT id, age FROM `member`
  WHERE id LIKE '__p%' OR id LIKE '__k%';

/* ‘박철호’ 이사(주소 광주에서 서울시 동대문구 왕십리로 변경) */
UPDATE `member` SET address = '서울시 동대문구 왕십리' WHERE name = '박철호';

/* ‘이성현’ 회원 탈퇴(계정 정보 삭제) */
DELETE FROM `member` WHERE name = '이성현';