[23/08/01] SQL (1)

yeju·2023년 8월 1일

sql

SQL

목록 보기

1/3

<<오라클 데이터베이스 연결하기>>

1. 오라클 DB, SQL Developer 다운로드 후 설치하기

Oracle DB 21c 다운로드🔗
sqldeveloper 다운로드🔗 sqldeveloper-18.1.0.095.1630-x64

2. cmd 실행 후 아래 내용 입력하기

sqlplus
사용자 이름 system, 비밀번호 1234(설정한 값)

-- 사용자 계정 생성하고 권한 부여하기
create user 유저이름 identified by 비밀번호;
grant resource, connect to 유저이름;
alter user 유저이름 default tablespace users quota unlimited on users;

-- 생성한 계정으로 재로그인 후 확인하기
conn 유저이름 비밀번호
show user;

3. SQL Developer 실행

+ 버튼 눌러 사용자 이름과 비밀번호에 생성한 정보 넣고
호스트 이름 localhost, 포트 1521, SID xe 로 테스트해보기

4. 회원 테이블 만들기

create table t_member(
	id varchar2(10) primary key,
	pwd varchar2(10),
	name varchar2(50),
	email varchar2(50),
	joinDate date default sysdate
);

insert into t_member(id, pwd, name, email) values('hong','1212','홍길동','hong@gmail.com');
insert into t_member(id, pwd, name, email) values('lee','1212','이순신','lee@test.com');
insert into t_member(id, pwd, name, email) values('kim','1212','김유신','kim@web.com');
commit;
select * from t_member;

📖 3. SQL 기본 문법

📌 3-1. SELECT ~ FROM ~ WHERE

회원과 구매 정보 테이블을 생성하며 실전 SQL 체험하기

1. 데이터베이스와 테이블 생성/삭제/수정하기

DROP DATABASE IF EXISTS market_db; -- 만약 market_db가 존재하면 우선 삭제
CREATE DATABASE market_db; -- 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 buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
...

2. 기본 조회하기 : SELECT ~ FROM

SELECT 열_이름 FROM 테이블_이름

SELECT * FROM member; -- member 테이블에서 모든 열(*)의 내용을 가져옴
SELECT * FROM buy;
-- USE로 DB명을 지정하지 않을 시 market_db.member처럼 DB명을 명시해야 함

SELECT addr AS '주소' FROM member;
-- 열 이름에 별칭(alias) 지정, AS는 생략 가능

3. 특정 조건 조회하기 : SELECT ~ FROM ~ WHERE

SELECT 열_이름 FROM 테이블_이름 WHERE 조건문

SELECT * FROM member WHERE mem_name = '블랙핑크';
-- 이름(mem_name) 컬럼의 값이 블랙핑크인 레코드를 찾아 조회

WHERE절 조건식에 관계 연산자 (>, <, >=, <=, = 등), 논리 연산자 (AND, OR) 사용 가능

SELECT mem_name, height, mem_number FROM member WHERE height>=165 AND mem_number>6;
-- 평균키(height) 165 이상이고(AND)
-- 멤버 수(mem_number)가 6보다 큰 레코드의 이름, 키, 멤버수 컬럼 조회

조건식에 숫자 범위가 들어갈 때 BETWEEN ~ AND ~ 사용 가능

SELECT mem_name, height FROM member WHERE height BETWEEN 163 AND 165;
-- 평균키 163이상 165이하 조회

IN() : 데이터가 () 안의 여러 값 중 하나에 해당되는지 검색할 때 사용
(유사한 결과는 검색x, 항상 일치해야 함)

SELECT mem_name, addr FROM member WHERE addr IN ('경기', '전남', '경남');
-- 주소(addr) 컬럼 값이 경기, 전남, 경남 중 하나인 레코드 검색

LIKE : 문자열의 일부 글자 검색
% : 무엇이든 허용 / _ : 자릿수 제한

SELECT * FROM member WHERE mem_name LIKE '우%';
-- 이름이 '우' 로 시작하는 모든 레코드 검색 (자릿수 무관)
SELECT * FROM member WHERE mem_name LIKE '__핑크';
-- 이름이 '핑크' 로 끝나고 4글자로 된 모든 레코드 검색

서브쿼리 : SELECT 안에 (주로 WHERE절) SELECT 문이 있는 것

SELECT mem_name, height FROM member
WHERE height > (SELECT height FROM member WHERE mem_name = '에이핑크');
-- 이름이 '에이핑크' 인 레코드의 평균키를 검색하고, 그 값을 메인 SELECT문의 WHERE 조건식의 값으로 사용

📌 3-2. SELECT문 심화

1. ORDER BY절 : 레코드가 출력될 순서 지정

ASC : 오름차순(기본값)
DESC : 내림차순

SELECT mem_id, mem_name, debut_date FROM member ORDER BY debut_date ASC;
-- 데뷔일 오름차순으로 정렬

WHERE절과 ORDER BY절을 함께 쓸 때는 ORDER BY절이 뒤에 와야 함
정렬 기준은 여러 개 지정 가능함

SELECT mem_id, mem_name, debut_date, height FROM member
WHERE height >= 164 ORDER BY height DESC, debut_date ASC;
-- 평균키가 164 이상인 그룹을 평균키 내림차순으로 정렬
-- 평균키가 같으면 데뷔 일자 오름차순으로 정렬 (날짜는 오래될수록 낮은 값)

2. LIMIT절 : 출력 레코드 개수 제한

숫자 하나만 쓰면 앞에서 n개, 두개 쓰면 n번에서 m개만큼 보여줌 (시작 값은 0)
ex) LIMIT 3 : 앞에서 3개 (= LIMIT 0, 3)
LIMIT 3, 2 : 3번부터 2개 (0번부터 시작하므로 실제로는 4번째로 오는 레코드부터 시작)

SELECT mem_name, debut_date FROM member ORDER BY debut_date LIMIT 3;
-- 0번째부터 3개만 보여줌 (= LIMIT 0,3)
SELECT mem_name, height FROM member ORDER BY height DESC LIMIT 3,2;
-- 3번째부터 2개만 보여줌

3. DISTINCT : 중복된 결과를 제거

컬럼명 앞에 붙이면 중복 값을 제거해 결과를 출력
컬럼이 여러 개일 경우, 한 컬럼의 값은 같지만 다른 컬럼의 값이 서로 다른 레코드면 해당 레코드들은 모두 출력됨

SELECT DISTINCT addr FROM member;
-- 주소 컬럼의 값을 중복을 제거하여 출력

4. GROUP BY절과 집계함수

특정 컬럼을 기준으로 레코드를 분류해 묶어줌, 주로 집계함수와 함께 사용

집계함수란? 컬럼의 값을 받아 특정한 연산을 하는 함수, 주로 GROUP BY절과 함께 사용

함수명	설명
SUM()	컬럼의 합계를 구함
AVG()	컬럼의 평균을 구함
MIN() / MAX()	컬럼의 최솟값, 최댓값을 구함
COUNT()	레코드의 개수를 셈 지정한 컬럼의 값이 null 이면 해당 레코드는 제외하고 셈
COUNT(DISTINCT)	중복값을 제외하고 레코드의 개수를 셈

SELECT mem_id, SUM(amount) FROM buy GROUP BY mem_id;
-- 회원 아이디, amount의 합계(SUM)를 검색하는데 합계를 계산할 때
-- 회원 아이디별로 묶어서(GROUP BY) 계산함
-- GROUP BY가 없으면 모든 레코드의 amount를 구분 없이 합하므로 결과 레코드도 1개만 나옴

SELECT mem_id "회원 아이디", SUM(price*amount) "총 구매 금액"
FROM buy GROUP BY mem_id; -- 회원 아이디별로 묶어서 가격*수량 합계를 계산함

SELECT mem_id, AVG(amount) '평균 구매 개수' FROM buy GROUP BY mem_id;
-- 회원 아이디별로 묶어서 수량 평균을 계산함

SELECT COUNT(phone1) '연락처가 있는 회원' FROM member;
-- phone1 컬럼의 값이 null이 아닌 레코드의 수

5. HAVING절

집계함수가 들어가는 조건은 WHERE절 대신 HAVING절을 사용해야 함
HAVING절은 GROUP BY절 뒤에 위치해야 함

SELECT mem_id "아이디", SUM(price*amount) "총 구매금액" FROM buy
GROUP BY mem_id
HAVING SUM(price*amount) > 1000
ORDER BY SUM(price*amount) DESC;
-- 총 구매금액을 회원 아이디별로 묶어서 계산하고,
-- 구매금액이 1000 이상인 레코드만 내림차순으로 출력

📌 3-3. 데이터 변경을 위한 SQL문

1. INSERT문

INSERT INTO 테이블명[(컬럼1, 컬럼2, ...)] VALUES(값1, 값2, ...);

테이블명 뒤에 컬럼명을 생략할 시 테이블의 모든 컬럼의 순서 및 개수에 맞춰 값을 써야 함

-- 테이블 만들기
CREATE TABLE hongong1 (toy_id INT, toy_name CHAR(4), age INT);
INSERT INTO hongong1 VALUES(1, '우디', 25); -- 모든 컬럼의 값 지정
INSERT INTO hongong1(toy_id, toy_name) VALUES(2, '버즈');
-- 두 컬럼의 값만 지정, age에는 null이 자동으로 들어감

여러 개의 INSERT 실행 시 다음과 같이 한 줄로도 작성 가능

INSERT INTO 테이블명 VALUES(값1, 값2, ...), (값3, 값4, ...), (값5, 값6, ...);

1-2. AUTO_INCREMENT : 특정 컬럼 값을 1부터 자동으로 증가하도록 함

반드시 기본 키로 지정해야 하고, insert시 해당 컬럼 값은 null로 지정함

CREATE TABLE hongong2 (
	toy_id INT AUTO_INCREMENT PRIMARY KEY,
	toy_name CHAR(4),
	age INT
);

-- toy_id는 자동 증가하는 값이므로 null로 지정하면 자동으로 채워짐
INSERT INTO hongong2 VALUES(NULL, '보핍', 25);
INSERT INTO hongong2 VALUES(NULL, '슬링키', 22);
INSERT INTO hongong2 VALUES(NULL, '렉스', 21);

자동 증가되는 값을 100부터 시작하도록 바꾸고 싶다면 다음과 같이 SQL 실행

ALTER TABLE hongong2 AUTO_INCREMENT = 100;

1-3. INSERT INTO ~ SELECT문 : 다른 테이블의 데이터를 한꺼번에 INSERT

INSERT INTO 테이블명 SELECT문; -- SELECT한 레코드를 테이블에 추가하기

2. UPDATE문

UPDATE 테이블명 SET 컬럼1=값1, 컬럼2=값2, ... WHERE 조건문;
-- 조건에 해당하는 레코드를 찾아 해당 컬럼의 값 변경

(주의) WHERE절 없이 UPDATE문을 실행하면 모든 레코드의 값이 변경됨

3. DELETE문

DELETE FROM 테이블명 WHERE 조건문;
-- 조건에 해당하는 레코드를 찾아 해당 레코드 삭제

3-1. 테이블 삭제 시 사용할 수 있는 명령어

DELETE FROM 테이블명;
DROP TABLE 테이블명;
TRUNCATE TABLE 테이블명;

DROP은 테이블 자체를 삭제하는 명령어
DELETE와 TRUNCATE는 테이블의 구조는 남겨놓고 안의 레코드만 삭제하는 명령어
TRUNCATE가 속도가 더 빠르므로 후자의 경우에는 TRUNCATE 사용 권장

yeju

🌱

다음 포스트