231101 DB - CRUD

데이터베이스 관련 용어와 테이블 만들기, 데이터 삽입, 수정, 삭제

데이터베이스 관련 용어

그림 1. 데이터베이스 용어

 

• DBMS
  Data Managerment System(데이터베이스 관리 시스템)의 약자. 데이터베이스를 관리하는 시스템 또는 프로그램.
• DB
  조직화된 정보들의 모음 또는 데이터 집합. DB는 고유한 이름을 가져야함.
• 객체(Object)
  유무형의 정보를 가지고 있는 독립적인 실체. 종류로는 테이블, 뷰, 인덱스 등이 있음.
• 테이블(Table)
  데이터베이스 내에서 데이터를 저장할 때 가장 많이 사용하는 객체 유형. 행과 열로 이루어져있음.
• 데이터(Data)
  테이블과 같은 객체에 저장된 실제 정보를 의미함.
• 열(Column)
  컬럼 또는 속성(Attribute)라고 함. 테이블에서 세로 부분, 하나의 테이블은 여러 개의 열로 구성.
• 열 이름(Column name)
  각 열(Column)을 구분하기 위한 이름. 테이블 내에서 고유해야 함.
• 행(Row)
  레코드라고도 함. 테이블에서 가로 부분. 데이터 전체를 의미.
• SQL
  사람과 DBMS가 소통하기 위해 사용하는 언어.
• 기본키(Primary Key, PK)
  테이블의 행을 구분해 줄 수 있는 대표 컬럼에게 부여하는 제약 조건. 중복값, 빈값이 들어올 수  없음.
• 외래키(Foreign Key, FK)
  테이블과 테이블을 연결해 주는 제약조건
• 스키마(Schema)
  데이터를 담는 그릇

※MySQL에서는 스키마가 DB와 동일한 의미로 사용하나, Oracle에서는 스키마가 유저와 동일한 의미로 사용된다.

 

데이터베이스 구축 절차

그림 2. 데이터베이스 구축 절차

 

데이터베이스 생성

SQL> create schema 스키마명;

 

※이름 결정 시 유의 사항

1. 데이터베이스(스키마)명, 테이블명, 컬럼명 등은 영문을 사용해야한다.
2. 이름은 영문으로 시작해야하며, 영문/숫자/특수문자(_, #, $)가 혼합될 수 있다.
3. 영문으로 하는 이유는 호환성 등의 문제가 발생될 수 있기 때문이다.

 

테이블 생성

SQL> creat table 테이블명(컬럼명1 데이터 타입(컬럼 사이즈), 컬럼명2 데이터 타입(컬럼사이즈),...);

※선택 설정 : default 기본값, 제약조건

 

데이터타입

그림 3. 데이터 타입

 

숫자 데이터 타입에서 실수 표현은 float, double을 사용하며, 정수 표현은 int, bigint을 사용.

문자 데이터 타입 char, varchar의 차이는 xyz라는 문자를 삽입한다고 가정했을 때

char(10)의 경우 ㅁㅁㅁㅁㅁㅁㅁxyz, varchar(10)의 경우 xyz 로 나타내는 차이가 있다.

공간 효율 char < varchar, 성능,속도 char > varchar, 선호도 char < varchar이다.

그렇다면 char는 언제 쓸까? 그 예시로는 주민등록번호, 사번, 학번처럼 정해진 숫자 안에 데이터가 입력될 경우이다.

날짜 데이터 타입은 date(년,월,일), datetime(date+시간)이다.

 

제약조건

그림 4. 제약 조건

 

ex) [stu] 테이블 
학번| 이름 | 주민등록번호 | 전화번호 | 학년 | 이메일
---------------------------------------------------------------------
 nn  nn              nn                    nn     nn    
 uk                     uk                    uk                   uk

 

스키마 만들기

create schema shopdb; -- 빈 스키마인 shopdb 생성
use shopdb; -- shopdb를 사용

 

테이블만들기

members 테이블

그림 5. members 테이블 생성 조건

create table members
(member_id int primary key,
member_name varchar(8) not null,
birth date not null,
job varchar(20)
phone varchar(20) unique,
address varchar(80));


desc members; -- desc 테이블명 : 구조 정보 보기. 테이블이 제대로 생성되었는지 확인할수있다.

select * from products; -- 테이블 조회. 데이터 삽입 후 확인 필요

 

products 테이블

그림 6. products 테이블 생성 조건

create table products
(prod_id int primary key,
prod_name varchar(20) not null,
price int check(price>0),
make_data date,
company varchar(10) not null);

desc products;

 

orders 테이블

그림 7. orders 테이블 생성 조건

create orders
(order_num int primary key,
order_date datetime default now()
foreign key(member_id) references members(member_id),
foreign key(prod_id) references products(prod_id) );

desc orders;

 

date로 데이터 타입을 사용하면 시간이 나타나지 않기 때문에 datetime을 사용.

foreign key(member_id) references members(member_id),

foreign key(prod_id) references products(prod_id) 이는 table level문법으로 쓰여졌다.

table level문법은 pk, uk, ck, fk 의 제약조건이 걸려있을 때 쓴다.

fk외 나머지는 column level 문법을 사용한다고 봐도 된다.
foreign key(컬럼명) references 참조테이블명(참조컬럼명)

 

Auto_increment 속성

해당 속성이 부여된 컬럼은 데이터 삽입 시 자동으로 1부터 시작해서 1씩 증가하는 값이 반환된다.

시작값과 증가값은 변경이 가능하지만, PK, Unique 제약조건이 선언된 컬럼이면서 숫자 데이터 타입 컬럼에만 가능하다.

create table 테이블명
(컬럼명 int auto_increment primarykey,....);

 

테이블 생성 후 auto_increment 시작값 변경

alter table 테이블명 auto_increment=입력값;

 

증가값 변경

set @@auto_increment_increment=증가값;

 

stu 테이블을 생성해서 알아보자

그림 8. stu 테이블 생성 조건

create table stu
(stu_id int primary key auto_increment,
stu_name varchar(5) not null,
age int check(age>19) );

desc stu;

※데이터 삽입할 때 auto_increment 속성을 선언했다면 자동으로 값이 들어갈 것이기 때문에 null값을 써준다. 

 

데이터 삽입

insert into 테이블명 [(컬럼1, 컬럼2,...)] values(값1, 값2,.....);

 

컬럼명은 생략가능.

컬럼명 리스트를 생략해서 작성한 경우 value절 값 리스트와 짝이 맞아야한다.

ex) insert into members(member_id, members_name, birth, phone) values(101, '김영희', '2000-01-02', '010-1234-1234')

※MySQL에서는 값 작성 방식

• 숫자 그냥 작성
• 문자, 날짜 작은 따옴표 안에 작성
  단, 날짜는 '년-월-일'으로 작성

 

members 테이블에 데이터 삽입해보기

insert into members
values(100, '홍길동', '1991-12-30', '학생', '010-1111-1111', '서울시 강남시');

insert into members(member_id, member_name, birth, phone)
values(101, '김민수', '1990-03-05','010-2222-2222');
-- 생략된 컬럼에는 null값이 자동으로 삽입됨.

insert into members(member_id, member_name, phone, address) 
values (102, '최아영', ‘010-3333-3333', ’서울 강남시');
-- not null 제약조건이 선언된 컬럼에 값을 생략한 경우인데, 에러가 난다.
insert into members(member_id, member_name, phone, address, birth)-- 테이블명 순서대로 할 필요가 없음. 값과 일치만 잘 시켜주자values(102, '최아영', '010-3333-3333', '서울시 강남시', '2001-01-01');

insert into members(member_id, member_name, birth, job) 
values (101, '강주영', ‘1998-08-09’, ‘대학생'); -- PK조건이 선언된 컬럼에 중복 값이 삽입된 경우이다. 에러가 난다.
into members(member_id, member_name, birth, job) 
values (104, '강주영', ‘1998-08-09’, ‘대학생’);

insert into members(member_name, birth, job, phone, address) 
values ('고승현', '1995-07-10', '트레이너', '010-5555-5555', '경기도 부천시 원미구'); -- PK 제약조건이 선언된 컬럼에 null 값이 삽입된 경우다.
insert into members(member_id, member_name, birth, job, phone, address) 
values (105, '고승현', '1995-07-10', '트레이너', '010-5555-5555', '경기도 부천시 원미구');

select * from members;

 

 

추가 데이터 삽입해보기

members 테이블에 데이터 삽입

그림 9. members 추가 데이터 삽입

insert into members(member_id, member_name, birth, job, phone)
values(106, '정유빈', '1970-02-04', '회사원', '010-6666-666');

insert into members(member_id, member_name, birth, phone)
values(107, '이영수', '1988-12-06', '010-7777-7777' );

insert into members(member_id, member_name, birth, phone, address)
values(108, '김철수', '1999-01-15','010-8888-8888','부산시 해운대구 센텀로');

insert into members(member_id, member_name, birth, job, phone, address)
values(109, '최승현', '1995-04-22','간호사','010-3333-3333','서울시 강북고 수유동');

insert into members(member_id, member_name, birth, job, phone, address)
values(110, '한주연', '2001-08-24','승무원','010-1010-1010','대구 수성구 수정로');

select * from members;

-- 다중행 삽입시 아래와 같이 작성해도 된다.
insert into membersvalues(109, '최승현', '1995-04-22','간호사','010-3333-3333','서울시 강북고 수유동'),
			(110, '한주연', '2001-08-24','승무원','010-1010-1010','대구 수성구 수정로');

 

products 테이블에 데이터 삽입

테이블명 생략하고 null값을 수동으로 삽입. 

' ' 이것은 공백이며 null이 아님

insert into products
values(10, '냉장고', 500, null, '삼성');

 

 

그림 10. products 추가 데이터 삽입

insert into products
values(20, '컴퓨터', 150, '2022-01-13','애플'),
          (30,'세탁기',250,'2020-03-10','LG'),
         (40,'TV',200,'2021-09-30','LG'),
          (50,'전자렌지',50,'2019-06-20','삼성'),
          (60,'건조기',300,'2021-07-09','LG');
select * from products;

 

orders 테이블에 데이터 삽입

※foreign key 제약조건을 건 테이블에 데이터를 삽입할 때는 부모 컬럼에 어떤 정보가 있고 없는지를 함께 확인하면서

데이터를 삽입해줘야함. 그렇지 않은 경우 오류가 발생함.(231208 추가)

그림 11. orders 추가 데이터 삽입

insert into orders values (1, 101, 20, '2023-02-01');


-- 데이터타입이 datetime인데, 년-월-일만 작성하면 시간을 생략하면 시간 분 초 는 자동으로 00:00:00으로 됨
insert into orders values(2, 107, 40, '2023-02-05 17:51');

-- now()함수를 활용해서 데이터 삽입하기
insert into orders values(3, 106, 50, now());

-- insert 시 default 값이 선언된 컬럼은 생략하면 자동으로 default 값이 삽입
insert into orders(order_num, member_id, prod_id) values(4, 103, 10);

 

데이터 수정

update 테이블명 set 컬럼명 = 값, 컬럼명 =값,... [where 조건문];

 

products 테이블의 모든 상품의 가격을 50씩 인상해보기

update products set price = price + 50;

 

※where절 없이 update 작업 시 모든 행이 수정

products 테이블의 TV 제품 가격을 30 인상해보기

update products
set price = price+30
where prod_name = 'tv';

select * from products; -- 테이블 조회

※where절 작성 시 특정 행이 수정

 

members 테이블의 105번 회원 전화번호를 010-5050-5050으로 변경해보기

update members
set phone = '010-5050-5050'
where member_id = 105;

select * from members;

orders 테이블의 2번 주문의 주문자를 120으로 변경해보자

update orders
set member_id = 120
where order_num = 2;

 fk제약조건에 위배되기 때문에 변경할 수 없음. member_id가 120이 없기 때문

 

update orders
set member_id = 109
where orde_num = 2;

select * from orders;

null값으로 update 하는 방법 : update 테이블명 set 컬럼명 = null [where 컬럼명]

update members
set phone = null
where member_id = 109;

select * from members;

 

데이터 삭제

delete from 테이블명 [where 조건문];

delete는 특정 행을 삭제. 특정 데이터를 삭제하려는 것은 null로 update하는 것

 

DML 작업 후 저장/취소

• DML 작업 후 저장하는 명령어 : commit;
• DML 작업 후 취소하는 명령어 : rollback;

auto commit 활성화(default) : DML 발생 시 자동 저장되기 때문에 따로 저장할 필요가 없지만, 작업 취소가 안된다.
auto commit 비활성화 : 작업 후 select 구문을 사용해서 미리보기를 한 다음 저장 및

취소 결정 가능하지만, DML 작업 설정1 후 저장 및 취소 작업을 해줘야한다.

 

hr.sql

0.02MB