24.07.17 Day13

최지원·2024년 8월 12일
sql

삼성중공업 DT 제조 혁신 과정

목록 보기
13/62

데이터베이스

멤버 전체 출력문
SELECT * FROM member;

주의사항


더블 클릭하여 선택된 shop_db에 적용시킨 후 사용

인덱스 생성 & 인덱스를 통한 검색

CREATE INDEX idx_member_name ON member(member_name);

  • 생성완료
  • 인덱스 생성 후 테이블 검색
  • 인덱스 검색
    • 일반적인 검색일 경우 어디를 찾아갈지 몰라서 전체 테이블을 검색
    • Key Lookup은 인덱스를 통해 결과를 찾은 것
    • data양이 많을 경우 인덱스를 사용하여 정보 찾기
    • 즉, 책의 내용을 찾을 때 찾아보기 기능을 통해 시간 단축

SQL 기본 문법

SELECT 열 이름...
FROM 테이블 이름
WHERE 열이름 조건식(관계연산자, 논리연산자)

입력/수정/삭제 기능

  • UPDATE : 회원의 주소나 연락처가 변경되어 정보를 수정할 때
  • DELETE : 회원이 탈퇴해서 회원을 삭제할 때
  • INERT : 새로 가입한 회원을 테이블에 입력

! 주의사항 !

DELETE, UPDATE 전에 SELECT를 해볼 것, 지우고자 하는 데이터가 맞는지, 결과 값이 맞는지 확인하기

  • 두 개의 테이블 비교
  • 회원 테이블 = primary 키 / 구매 테이블 = 여러 구매 존재
  • 회원 테이블 - 구매 테이블 : 1대 다
  • primary키 : 중심, 유일, 중복X
  • 회원 테이블에 없는 아이디가 구매를 하는 것은 불가능
  • 다른 구매자가 구매를 할 경우 배송을 하는데 보낼 곳이 없는 것과 동일 (설계자의 중요성)

실습

사용 marketdb

DROP DATABASE IF EXISTS market_db; -- 만약 market_db가 존재하면 우선 삭제한다.
CREATE DATABASE market_db;

USE market_db; -- 데이터베이스 선택
CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    INT NOT NULL,  -- 인원수
  addr	  		CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);
CREATE TABLE buy -- 구매 테이블
(  num 		INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK), 자동으로 숫자 입력
   mem_id  	CHAR(8) NOT NULL, -- 아이디(FK)
   prod_name 	CHAR(6) NOT NULL, --  제품이름
   group_name 	CHAR(4)  , -- 분류
   price     	INT  NOT NULL, -- 가격
   amount    	SMALLINT  NOT NULL, -- 수량
   FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01');
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10');
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25');
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19');

INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES(NULL, 'APN', '아이폰', '디지털', 200, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '아이폰', '디지털', 200, 5);
INSERT INTO buy VALUES(NULL, 'BLK', '청바지', '패션', 50, 3);
INSERT INTO buy VALUES(NULL, 'MMU', '에어팟', '디지털', 80, 10);
INSERT INTO buy VALUES(NULL, 'GRL', '혼공SQL', '서적', 15, 5);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 2);
INSERT INTO buy VALUES(NULL, 'APN', '청바지', '패션', 50, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 1);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 4);

where 필요한 것들만 골라서 결과를 보는 효과

select * from member
where mem_name = '블랙핑크';

select * from member
where mem_number = 4;

관계연산자, 논리연산자

select mem_id, mem_name
	from member
    where height <= 162;

AND

select mem_name, height, mem_number
	from member
    where height >= 165 AND mem_number > 6;

OR

select mem_name, height, mem_number
	from member
	where height >= 165 OR mem_number > 6;

BETWEEN

select mem_name, height
	from member
	where height BETWEEN 163 AND 165;

IN

select mem_name, addr
	from member
    where addr in('경기','전남','경남');

Like 문자열의 일부 글자 검색

select *
	from member
    where mem_name like '우%';
   
# _을 사용하여 한글자와 매치
select *
	from member
	where mem_name like'__핑크';

select mem_name, height
	from member
    where height > (
		select height 
			from member
			where mem_name = '에이핑크'
    );

GROUP BY절

지정한 열의 데이터들을 같은 데이터끼리 묶어서 결과 추출

ORDER BY절

결과의 값이나 개수에는 영향을 미치지 않지만, 결과가 출력되는 순서 조절

select mem_id, mem_name, debut_date
	from member
    order by debut_date;

기본 값 ASC : 오름차순

DESC : 내림차순

select mem_id, mem_name, debut_date
	from member
	order by debut_date DESC;

### 평균 키가 164 이상인 회원들이 키가 큰 순서대로 출력
select mem_id, mem_name, debut_date, height
	from member
    where height >= 164
    order by height DESC;

select mem_id, mem_name, debut_date, height
	from member
    where height >= 164
    order by height DESC, debut_date ASC;

limit

select mem_name, debut_date
	from member
    order by debut_date
    limit 3;

select mem_name, height
	from member
    order by height DESC
    limit 3,2;

중복된 결과 제거 DISTINCT

select distinct addr from member;

GROUP BY절

select mem_id, sum(amount)
	from buy
    group by mem_id;

select mem_id "회원 아이디", sum(amount) "총 구매 개수"
	from buy
    group by mem_id;

select mem_id '회원 아이디', sum(price * amount) '총 구매 금액'
	from buy
    group by mem_id;

select avg(amount) from buy;

select mem_id, avg(amount) '평균 구매 개수'
	from buy
    group by mem_id;

Having절

Group by절 다음에 나와야 함

조건 제한

select mem_id '회원 아이디', sum(price * amount) '총 구매 금액'
	from buy
    group by mem_id
    having sum(price*amount) > 1000;

select mem_id '회원 아이디', sum(price * amount) '총 구매 금액'
	from buy
    group by mem_id
    having sum(price*amount) > 1000
    order by sum(price*amount) DESC;

INSERT : 데이터 입력

create table hongong1 (
	toy_id int,
    toy_name char(4),
    age int
);
insert into hongong1 values (1,'우디',25);

자동으로 증가하는 AUTO_INCREMENT

  • 열을 정의할 때 1부터 증가하는 값 입력
  • AUTO_INCREMENT로 지정하는 열은 꼭 PRIMARY KEY로 지정
create table hongong2 (
	toy_id int auto_increment primary key,
    toy_name char(4),
    age int
);

insert into hongong2 values (NULL, '보핍',25);
insert into hongong2 values (NULL, '슬링키',22);
insert into hongong2 values (NULL, '렉스',21);
select * from hongong2;

select last_insert_id();

alter table hongong2 auto_increment = 100;
insert into hongong2 values (null,'재남',35);
select * from hongong2;

create table hongong3 (
	toy_id int auto_increment primary key,
    toy_name char(4),
    age int
);

시작 숫자를 1000으로 설정 후 3씩 증가

alter table hongong3 auto_increment = 1000;
set @@auto_increment_increment = 3;

insert into hongong3 values (null, '토마스',20);
insert into hongong3 values (null, '제임스',23);
insert into hongong3 values (null, '토마스',25);
select * from hongong3;

INSERT INTO ~ SELECT

select count(*)
	from world.city;

desc world.city;

select *
	from world.city
    limit 5;

create table city_popul(
	city_name char(35),
    population int
);

insert into city_popul
	select name, population from world.city;

update : 기존에 입력되어 있는 값을 수정하는 명령

update city_popul
	set city_name = '서울'
    where city_name = 'Seoul';
select *
	from city_popul
    where city_name = '서울';

명을 변경하면서 값도 0으로 설정

update city_popul
	set city_name = '뉴욕', population = 0
    where city_name = 'New York';
select *
	from city_popul
    where city_name = '뉴욕';

delete : 데이터 삭제

delete from city_popul
	where city_name like 'New%';

create database netfilx_db;
use neflix_db;
create table moive(
	movie_id int,
    movue_title varchar(30),
    movie_director varchar(20),
    movie_star varchar(20),
    movie_script longtext,
    movie_film longblob
);

인덱스

create table table1(
	col1 int primary key,
    col2 int,
    col3 int
);

show index from table1;

create table table2(
	col1 int primary key,
    col2 int unique,
    col3 int unique
);
show index from table2;

drop table if exists buy, member;
create table member(
	mem_id char(8),
    mem_name varchar(10),
    mem_number int,
    addr char(2)
);

insert into member values('TWC','트와이스',9,'서울');
insert into member values('BLK','블랙핑크',4,'경남');
insert into member values('WMN','여자친구',6,'경기');
insert into member values('OMY','오마이걸',7,'서울');
select * from member;

alter table member
	add constraint
    primary key (mem_id);
select * from member;

alter table member drop primary key;
alter table member
	add constraint
    primary key(mem_name);
select * from member;
profile
최지원
이전 포스트

24.07.16 Day12

다음 포스트

24.07.18 Day14

0개의 댓글