23.03.29 / DBMS 구축 & DB 테이블 생성과 활용(insert,delete,update,select)

1. DBMS인 mysql 설치
2. mysql 데이터 베이스 관리자인 root로 로그인
3. 데이터 베이스 DB1 생성
4. 일반 사용자 계정 생성 및 데이터 베이스 접근 권한 부여
5. 일반 사용자 계정 로그인
6. 해당 데이터 베이스 내에 테이블을 생성한다.
7. 테이블 내에 입력(insert) / 수정(update) / 삭제(delete) / 선택(select) 할 수 있다. / 특히 select 아주 양이 많고 중요

 

mysql로 데이터 베이스 서버에 접근하려면 IP, DB접속 아이디, DB접속 비밀번호, 포트넘버, DB네임 알아야 한다.

2.  mysql 데이터 베이스 관리자인 root로 로그인

 

 

도구 - 환경설정 - SQL - 편집기 글꼴에서 폰트 설정

 

3. 데이터 베이스 DB1 생성  &  4. 일반 사용자 계정 생성 및 데이터 베이스 접근 권한 부여

-- DB 생성
CREATE DATABASE DB명 DEFAULT CHARACTER SET UTF8;
-- 사용자 추가 (일반 사용자 계정 생성)
CREATE USER 'DB아이디'@'%' IDENTIFIED BY 'DB비밀번호';
-- 사용자 권한 설정 
GRANT ALL PRIVILEGES ON DB명.* TO 'DB아이디'@'%';
-- 변경된 내용을 메모리에 반영
FLUSH PRIVILEGES;


@'%' ? 호스트를 %로 설정하는 것
mysql은 기본적으로 로컬(localhost)에서만 접속이 가능하고 외부에서는 접속이 불가능하게 되어 있다.
기본 호스트 상태 : localhost
외부 망에서도 DB아이디를 통해 DB에 원격으로 접속할 수 있도록 한다.
(모든 DB아이디가 외부에서 접근 가능. 단, localhost인 DB아이디는 접근 불가능)

F9(쿼리 실행)해서 설정 등록

 

5. 관리자 계정 접속 종료하고 일반 사용자 계정 로그인

 

 

6. 해당 데이터 베이스 내에 테이블을 생성한다

(생성 코드를 입력하고 쿼리 창에서 F9쿼리실행 - 데이터베이스 창에서 F5새로고침)

create table 테이블이름(
name1 VARCHAR(10) NOT NULL PRIMARY KEY, 
name2 VARCHAR(10) NOT NULL,
name3 VARCHAR(10) NOT NULL,
name4 VARCHAR(20) NOT NULL,
name5 VARCHAR(80)
)DEFAULT CHARSET=euckr;

insert into 테이블이름 values('value1-1','value2','value3','value4',NOW()); --회원1
insert into 테이블이름 values('value1-2','value2','value3','value4',NOW()); --회원2
insert into 테이블이름 values('value1-3','value2','value3','value4',NOW()); --회원3
insert into 테이블이름 values('value1-4','value2','value3','value4',NOW()); --회원4

PRIMARY KEY ?  기본 키라고 한다. 
데이터를 구분할 수 있는 식별자
NULL 값을 가질 수 없음 (NOT NULL) / Unique key(중복 불가능)

VARCHAR() ? 가변 문자열
50byte 까지 입력할 수 있는 문자열 (영문자 기준 50글자)
문자열에 1234를 입력했을 때 "1234"가 입력된다.
이름,주소 같이 글자 길이가 일정하지 않은 경우 사용할 수 있다.

CHAR() ? 불가변 문자열
50글자 까지 입력할 수 있는 문자열
문자열에 1234를 입력했을 때 "1234            "가 입력된다.(50글자까지 공백입력)
주민등록번호 같이 글자 길이가 일정한 경우 사용할 수 있다.

NOW() ? 현재 날짜와 시간
2023-03-29 14:25:35

 

데이터 창에서 테이블이 생성된 걸 확인할 수 있다.

 

-- insert 쿼리 문장
-- '테이블이름' 테이블에 회원1의 아이디부터 이메일까지 입력
INSERT INTO 테이블이름 VALUES ('아이디1', '비번', '권한', '이름', '이메일');


-- update 쿼리 문장
-- '테이블이름' 테이블에서 아이디 컬럼값이 아이디1인 조건에 일치하는 비밀번호부터 이메일까지 변경
UPDATE 테이블이름
	SET
		비번 = '1',
		권한 = '2',
		이름 = '3',
		이메일 = '4'
	WHERE 아이디 = '아이디1';


-- delete 쿼리 문장
-- '테이블이름' 테이블에서 아이디 컬럼값이 아이디6인 한줄(레코드) 삭제하는 delete 쿼리문장
DELETE FROM 테이블이름 WHERE 아이디='아이디6'


-- select 쿼리 문장
-- '테이블이름' 테이블의 전체 조회
select * from 테이블이름;

-- '테이블이름' 테이블의 전체 개수 조회
select coun(*) from 테이블이름;

-- '테이블이름' 테이블의 전체 개수에 이름 붙여서 조회
select count(*) as 개수조회이름 from 테이블이름;

-- '테이블이름' 테이블에서 아이디 컬럼값이 아이디5인 한줄(레코드) 조회
select * from 테이블이름 WHERE 아이디='id005';

-- '테이블이름'r 테이블에서 아이디 컬럼값이 아이디5인 조건에서 비번만 조회
select 비번 from 테이블이름 WHERE 아이디='아이디5';


-- '테이블이름' 테이블에서 아이디 컬럼값이 아이디5인 조건에서 권한만 조회
select 권한 from 테이블이름 WHERE 아이디='아이디5';

-- '테이블이름' 테이블에서 아이디 컬럼값이 아이디5인 조건에서 이름만 조회
select 이름 from 테이블이름 WHERE 아이디='아이디5';

-- '테이블이름' 테이블에서 아이디 컬럼값이 아이디5인 조건에서 이메일만 조회
select 이메일 from 테이블이름 WHERE 아이디='아이디5';

 

-- tb_goods 테이블에서 해당 컬럼명의 모든 값을 문자열 인덱스 부터 조회
select substring(g_code,7)  from tb_goods;
-- 컬럼명 g_code에 입력되어 있는 데이터가 gcode_1 , gcode_2, gcode_20이 있다면
-- 7번째 문자열부터 출력되기 때문에 인 1, 2, 20이 출력된다.

-- tb_goods 테이블에서 해당 컬럼명의 마지막 값 문자열 인덱스 부터 조회
select MAX(substring(g_code,7))  from tb_goods;
-- 마지막 값이 gcode_3이라면 3 출력

-- tb_goods 테이블에서 해당 컬럼명의 문자열 인덱스부터 조회해서 DECIMAL(10진법)로 형변환 후 최대값 조회
select max(CAST(substring(g_code,7) AS DECIMAL))  from from tb_goods;

-- tb_goods 테이블과 과 tb_member 테이블 병합해서 테이블 전체 조회
select tb_member.*, tb_goods.* from tb_member 
inner join tb_goods on tb_member.m_id = tb_goods.m_id;

-- tb_goods 테이블과 과 tb_member 테이블 병합해서 테이블 전체 조회
-- 기간별 조회
select tb_member.*, tb_goods.* from tb_member 
inner join tb_goods on tb_member.m_id = tb_goods.m_id 
and DATE_FORMAT(g_date,'%Y-%m-%d') between '2023-04-01' and '2023-04-30' ;

-- tb_goods 테이블과 과 tb_member 테이블 병합해서 테이블 전체 조회
-- 기간별 조회 / 날짜 오름차순
select tb_member.*, tb_goods.* from tb_member 
inner join tb_goods on tb_member.m_id = tb_goods.m_id 
and DATE_FORMAT(g_date,'%Y-%m-%d') between '2023-04-01' and '2023-04-30' 
order by g_date;

-- tb_goods 테이블과 과 tb_member 테이블 병합해서 테이블 전체 조회
-- 기간별 조회 / 날짜 내림차순
select tb_member.*, tb_goods.* from tb_member 
inner join tb_goods on tb_member.m_id = tb_goods.m_id 
and DATE_FORMAT(g_date,'%Y-%m-%d') between '2023-04-01' and '2023-04-30' 
order by g_date desc;


-- tb_goods 테이블과 과 tb_member 테이블 병합해서 테이블 전체 조회
-- 기간별 , 카테고리별 조회 / 가격 오름차순
select tb_member.*, tb_goods.* from tb_member 
inner join tb_goods on tb_member.m_id = tb_goods.m_id 
and DATE_FORMAT(g_date,'%Y-%m-%d') between '2023-04-01' and '2023-04-30' 
AND g_cate='콜드컵'
order by g_price;

-- tb_goods 테이블과 과 tb_member 테이블 병합해서 테이블 전체 조회
-- 기간별 , 카테고리별, 가격대별 조회 / 가격 내림차순
select tb_member.*, tb_goods.* from tb_member 
inner join tb_goods on tb_member.m_id = tb_goods.m_id 
and DATE_FORMAT(g_date,'%Y-%m-%d') between '2023-04-01' and '2023-04-30' 
AND g_cate='콜드컵'
AND g_price >= 10000 and g_price <= 19999
order by g_price desc;

-- tb_goods 테이블과 과 tb_member 테이블 병합해서 테이블 전체 조회
-- 기간별 , 카테고리별, 가격대별, 제품이름으로 조회 / 날짜 오름차순						
select tb_member.*, tb_goods.* from tb_member 							
inner join tb_goods on tb_member.m_id = tb_goods.m_id 							
and DATE_FORMAT(g_date,'%Y-%m-%d') between '2023-04-01' and '2023-04-30' 							
AND g_cate='콜드컵'							
AND g_price >= 10000 and g_price <= 19999							
AND g_name ='그린 팝핸들 콜드컵'							
ORDER BY g_date;		

-- tb_goods 테이블과 과 tb_member 테이블 병합해서 테이블 전체 조회
-- 기간별 , 카테고리별, 가격대별, 아이디로 조회 / 날짜 오름차순						
select tb_member.*, tb_goods.* from tb_member 							
inner join tb_goods on tb_member.m_id = tb_goods.m_id 							
and DATE_FORMAT(g_date,'%Y-%m-%d') between '2023-04-01' and '2023-04-30' 							
AND g_cate='콜드컵'							
AND g_price >= 10000 and g_price <= 19999							
AND tb_goods.m_id ='lsr123'							
ORDER BY g_date;