2025.02.12 Database 기초공부 1
어제 공부했던 내용 복습 요약
-- (CMD) 처음 접속시
-- CMD id랑 pw 넣을 때는 세미콜론( ; )를 안 붙여도 된다.
mysql -u유저명 -p비밀번호 => exit
show databases; => DB 목록보기
use 선택DB명; => DB 선택
show tables; => 테이블 목록보기
desc 테이블명 => 테이블의 구조보기
select * from 테이블명 => 해당 테이블의 데이터보기
실습 예시 코드
테이블명 : test2
create table test2 (
id int auto_increment,
name varchar(10) not null,
age int default 20,
address varchar(20),
primary key(id)
);
insert into test2 (name, age, address) values ('홍길동', 23, '서울');
insert into test2 (name, age, address) values ('강길순', 24, '인천');
insert into test2 (name, age, address) values ('이순신', 22, '서울');
insert into test2 (name, age, address) values ('강감찬', 23, '인천');
insert into test2 (name, age, address) values ('유관순', 21, '서울');
select * from test2;
원래 있던 데이터를 2개를 지우고 홍길동 ~ 유관순을 넣었더니 id 값이 3,4,5,6,7로 생성되었다.
내가 생각했던 번호와 달라 당황했지만, DB의 목표가 뭔지를 다시 생각해보니 이해가 갔다.
예를 쇼핑몰로 들었을 때, 현재는 탈퇴한 사람이고 5년 전에 가입한 a라는 사람이 1번의 번호를 갖고 어떤 물건을 구매했다. 이후 다른 사람 B가 1번의 번호를 갖고 활동하면 나중에 통계를 잡을 때 겹치기 때문에 무결성에 어긋나는 행위가 된다. 이를 참조무결성이라고 한다.
참조 무결성이란?
- 데이터베이스에서 테이블 간의 관계가 일관성을 유지하는 것을 의미한다.
- 외래키(Foreign Key)가 참조하는 기본키(Primary Key)의 값이 항상 존재함을 보장한다.
예시 코드를 다음과 같이 들어보자.
-- 학과 테이블 (부모 테이블)
CREATE TABLE 학과 (
학과번호 INT PRIMARY KEY,
학과명 VARCHAR(50)
);
-- 학생 테이블 (자식 테이블)
CREATE TABLE 학생 (
학번 INT PRIMARY KEY,
이름 VARCHAR(50),
학과번호 INT,
FOREIGN KEY (학과번호) REFERENCES 학과(학과번호)
);
참조 무결성 규칙
- 삽입 규칙
- 자식 테이블에 데이터를 넣을 때, 부모 테이블에 해당 키가 반드시 존재해야 한다.
- 삭제 규칙
- 부모 테이블의 데이터를 삭제할 때 자식 테이블에서 참조하고 있다면
- CASCADE: 자식 데이터도 함께 삭제
- SET NULL: 자식의 외래키를 NULL로 설정
- RESTRICT: 삭제 거부
- NO ACTION: 아무 조치 없음
- 부모 테이블의 데이터를 삭제할 때 자식 테이블에서 참조하고 있다면
- 수정 규칙
- 부모 테이블의 기본키를 수정할 때도 삭제 규칙과 같은 옵션 적용
❌ 참조 무결성 위반 사례 ❌
-- 실패: 존재하지 않는 회원번호로 주문 추가 불가
INSERT INTO 주문 (주문번호, 회원번호, 상품명)
VALUES (1, 999, '노트북'); -- 999번 회원이 없다면 에러 발생-- 주문이 있는 회원을 삭제하려는 경우
DELETE FROM 회원 WHERE 회원번호 = 1; -- 제약조건 위반!
✅ 참조 무결성 해결 방법 ✅
-- 회원 삭제 대신 상태 변경
UPDATE 회원 SET 상태 = '탈퇴' WHERE 회원번호 = 1;테이블명 바꾸고 싶을 때
alter는 테이블 내의 구조를 바꾸는 것으로 이런 경우에는 rename이라는 새로운 것을 사용해야 한다. 코드 예시는 다음과 같다. 굳이굳이 alter를 사용해도 되지만 rename이 쿼리문 안에 들어가야 변경이 가능하다.
alter table 테이블명(전) to 테이블명(후); 이런거는 안된다는 것이다.
-- 테이블명 변경
rename table 테이블명(전) to 테이블명(후);update set sql문을 사용해 데이터값을 변경할 때는 여러개를 선택할 경우 or과 in을 선택할 수 있다.
update test2 set age = 20 where id = 4 or id = 5;
update test2 set age = 20 whre id in (4,5);
or 연산자보다 in 연산자가 실행 속도가 더 빠르다
as 별칭 설정(alias)
- 칼럼, 테이블, 서브쿼리, …
select name as '이름', age as '나이' from test2;
실습
원래 테이블
(🚨 tel 전화번호 텐키 동시에 눌렀더니 나온것들입니다. 특정된 번호 아님 🚨)
| num | name | age | gender | address | major | score | tel |
| 1111 | 홍길동 | 20 | m | Seoul | Computer | 89 | 010-5647-6546 |
| 2222 | 강길순 | 20 | w | Seoul | English | 87 | 010-1234-4567 |
| 3333 | 이순신 | 22 | m | Incheon | Computer | 57 | 010-6548-4213 |
| 4444 | 강감찬 | 23 | m | Incheon | English | 67 | 010-9878-1234 |
| 5555 | 유관순 | 21 | w | Suwon | Computer | 97 | 010-4567-4569 |
-- 0. 전체 선택
select * from student;
-- 1. major가 computer인 학생만 검색
select name from student where major='computer';
-- 2. major가 computer이고, score 80이상인 학생 검색
- 전체 필드가 아닌 이름만 검색
- as 사용하여 필드명 변경
select name as '이름' from student where major='computer' and score >= 80;
-- 3. score가 70~90사이인 학생 검색
- (between 70 and 90)
select name from student where score between 70 and 90;
-- 4. address가 Seoul, Suwon에 사는 학생만 검색
- or, in 둘 다 사용해보기
select name from student where address = 'suwon' or address = 'seoul';
select name from student where address in ('seoul', 'suwon');
-- 4-1. address에 s 문자가 있는 학생만 검색
select * from where address like 's%';
-- 5. 강감찬의 major를 Computer로 변경
update student set major = 'Computer' where num = 4444;
-- 6. 강감찬 데이터 삭제
delete from student where num=4444;산술연산자 (+, -, *, /) => 나머지 % 연산자가 없음.
값이 NULL이면 연산이 안 됨.
null + 1 = null
비교연산자 (>, <, >=, <=, =, <>)
- +=, -=, /=, *= 이런거 안된다.
- a = a + 1 이렇게 풀어서 써야 한다.
논리연산자 (AND, OR, NOT)
우선순위 ()
집약과 정렬
집약 : 열의 데이터 합계, 평균 같은 집계값을 구하기 위한 함수
- count : 검색된 행의 개수
- sum : 합계
- avg : 평균
- max : 최대값
- min : 최소값
- group by : 그룹별로 묶는 기능
- having : group by에 대한 조건식
- order by : 정렬(오름차순 ASC, 내림차순 DESC)
ex) order by age asc, score desc;
- limit : 시작번지, 개수 => 시작번지부터 개수만큼 추가
컴퓨터 학과의 성적 합계
- major, sum
산술연산 +는 같은 튜플의 속성 합계
이름 국어 영어 수학 합계
A 90 + 80 + 70 = 240
같은 속성 끼리의 합 = sum
select major, sum(score) from student where major = 'computer';
select major, sum(score) from student where major = 'english';
[] 친 내용은 생략 가능. 단, 순서는 지켜야 함.
select 칼럼명 from 테이블명
[where 조건]
[group by 칼럼]
[having 조건]
[order by 칼럼]
[limit]
-- 성별 성적 평균 출력 => 평균이 큰 순으로 정렬(내림차순)
select gender, avg(score) from student group by gender order by avg(score) desc;
-- 성별 성적 인원수, 평균 출력 => 평균이 큰 순으로 정렬(내림차순)
select gender as 성별, count(gender) as 인원, avg(score) as 평균 from student group by gender order by avg(score) desc;
-- computer, english 학과의 점수 합계
select major, sum(score) from student group by major having major = 'computer' or major = 'english';
-- 각 과별 성적 합계가 250 이상인 학과만 출력
select major, sum(score) from student group by major having sum(score) >= 250;
-- student 테이블에 grade(학년) 필드 추가
alter table student add grade int after age;
-- 20=1, 21=2, 22=3, 23이상은 4로 수정
update student set grade = 1 where age = 20;
update student set grade = 2 where age = 21;
update student set grade = 3 where age = 22;
update student set grade = 4 where age >= 23;
-- if문
if(조건식, 참, 거짓)
if(조건식, 참, if(조건식, 참, 거짓))
-- case문
case
when 조건 then 값
when 조건 then 값
when 조건 then 값
when 조건 then 값
else 값
end;
-- 예시)
update student set grade = case
when age=21 then 2
when age=22 then 3
else 4
end;4학년 값을 졸업테이블로 이동
1. 졸업생 테이블 생성
=> 원본 테이블 구조를 복사하여 복사 테이블 생성
- if exists(만약 있다면...)
- if not exists(만약 없다면...)
create table (if not exists) 테이블명 like 복사할 테이블명;
create table if not exists student_4 like student;
2. 4학년 학생을 이동
=> grade가 4인 학생을 복사
=> 서브쿼리를 사용하여 검색 후 복사
=> insert into values(값...)
=> 값... 검색
select * from student where grade = 4;
insert into student_4 (select * from student where grade = 4);
3. 원본테이블에 grade가 4인 학생을 삭제
delete from student where grade=4;
4. age+1, grade+1 증가 (2학년 -> 3학년, 21살 -> 22살, ...)
update student set grade = grade + 1, age = age + 1;
5. 이후 1학년추가 (1학년이 없기 때문에)성적이 90점 이상인 학생들을 장학금 테이블로 복사
1. student_best 구조 복사하여 생성 후 복사
create table if not exists student_best like student;
2. 성적이 90점 이상인 학생들을 이동
insert into student_best (select * from student where score >= 90);
3. 위에서 4학년 이상을 없애고 따로 테이블을 작성했기에 student_4 것도 가져오기
insert into student_best (select * from student_4 where score >= 90);
SQL 내장함수
산술연산함수
- 산술연산자 (+ - * /)
- 나머지를 구하는 함수 mod(값1, 값2) : 값1을 값2로 나눈 나머지
- round(값, 자리수) : 반올림
- ceil(값) : 올림 / floor(값) : 버림 => 자리수 지정 X
- sqrt : 제곱근 / pow : 제곱
문자열함수
- concat(str1, str2) : 문자열 연결
- length(str) : 문자열 길이 (영어는 1, 한글은 2로 구성)
- 한글은 byte 크기가 크기에 잘 사용되지 않는다.
- char_length(str) : 무조건 1글자로 인식
- substr(str, start, count) : 시작 위치부터 개수만큼 추출
- left(str, count) : 왼쪽에서 개수만큼 추출
- right(str, count) : 오른쪽에서 개수만큼 추출
- mid(str, start, count) : substr의 기능과 동일
- replace(str, old, new) : old 문자를 찾아 new 문자로 변경
- insert(str, 위치, 길이, 삽입할문자) : 문자에서 위치의 길이만큼 지우고 삽입
- ucase(str) : 대문자로변환 / lcase(str) : 소문자로변환
- space(숫자) : 숫자만큼 공백 추가
- space 같은 경우 concat과 같은 함수와 함께 사용
- trim(문자) : 공백 제거 / ltrim : 왼쪽공백제거 / rtrim : 오른쪽 공백제거
- trim(both '-' from '------abcd-----') : 양쪽에 있는 - 제거
- 결과 : abcd
- both(양쪽) / leading(앞) / trailing(뒤)
- lpad(str, 총길이, 채울문자) : 앞/뒤쪽에 총길이만큼 공백을 삽입, 공백부분에 채울문자를 채우기
- repeat(str, 횟수) : 문자열을 횟수만큼 반복
- reverse(str) : 문자를 거꾸로 출력
날짜 함수
- adddate / subdate : 날짜를 기준으로 ~후, ~전 날짜
- adddate(날짜, interval 5 day) : 이 날짜를 기준으로 5일 후 날짜
- interval 뒤쪽 값 : day, year, month, hour, minute, second 가능
- curdate(), curtime(), now(), sysdate() : 오늘날짜/시간
- year(날짜), month(), day(), hour(), minutes(), second() : 해당 연,월,일... 만 추출
- date(날짜) / time(날짜/시간) : 추출
- datediff(날짜1, 날짜2) : 날짜2에서 날짜1까지 남은 날짜
- dayofweek(날짜) : 요일(일=1, 월=2....)
- monthname(날짜) : 월의 영어이름을 리턴
- dayofyear(날짜) : 1년 중에 얼마나 경과했는지 리턴
- last_day(날짜) : 해당 월의 마지막 날짜
- time_to_sec(시간) : 시간을 초단위로 변환
논리함수
- if문
- if(조건식, 참, 거짓)
- if(조건식, 참, if(조건식, 참, 거짓))
- -case~when~then 문
- case when 조건 then 값 else end;
정보함수
- user(), current_user(), sessioni_user() : 현재 유저
- database(), schema() : 현재 사용중인 DB 명 리턴
-student 테이블에서 다음을 처리
등급(ranking) 칼럼을 추가
alter table student add ranking varchar(3) not null after score;
score에 따라 ranking에 A/B/C 값을 추가
1. score ≥ 90 A
2. score ≥ 80 B 나머지는 C
update student set ranking = case when score >= 90 then 'A' score >= 80 then 'B' else 'C' end;
ranking별 인원수 출력
select ranking, count(ranking) as 인원 from student group by ranking;
성적순으로 1~3등까지만 출력
select * from student order by score desc limit 3;
학과의 인원이 3명 미만인 학과를 출력
select major, count(major) as 학생수 from student group by major having count(major) < 3;'Database' 카테고리의 다른 글
| [Do it SQL] Chapter 01-3 / 02-1 (1) | 2025.03.16 |
|---|---|
| [SQL] 오답노트 (0) | 2025.03.04 |
| [DB] 기초공부 3 (0) | 2025.02.14 |
| [DB] 기초공부 2 (0) | 2025.02.13 |
| [DB] 기초공부 (1) | 2025.02.11 |