GROUP BY

데이터를 그룹화 하고 싶을 때 GROUP BY 문을 사용하고 일반적으로 HAVING도 같이 사용한다.

HAVING : WHERE과 비슷한 역할을 하며 그룹화된 데이터를 필터링 한다.

다음 아래에 회원 테이블 생성 및 회원 쿼리문 예제로 실습을 진행한다.

회원 테이블 생성 쿼리문

CREATE TABLE `member` (

`mb_id` varchar(20) NOT NULL,  
`mb_password` varchar(255) NOT NULL,  
`mb_name` varchar(50) NOT NULL,  
`mb_email` varchar(100) NOT NULL,  
`mb_hp` varchar(20) DEFAULT NULL,  
`mb_tel` varchar(20) DEFAULT NULL,  
`mb_addr` varchar(255) DEFAULT NULL,  
`mb_birth` date DEFAULT NULL,  
`mb_sex` char(1) DEFAULT NULL,  
`mb_level` int DEFAULT '1',  PRIMARY KEY (`mb_id`)

);

회원 데이터

INSERT INTO `member` (`mb_id`, `mb_password`, `mb_name`, `mb_email`, `mb_hp`, `mb_tel`, `mb_addr`, `mb_birth`, `mb_sex`, `mb_level`) VALUES
('user001', 'password123', '홍길동', 'hong1@example.com', '010-1111-2222', '02-123-4567', '서울시 강남구', '1990-05-12', 'M', 1),
('user002', 'password456', '김영희', 'kim2@example.com', '010-2233-4455', '031-789-1234', '경기도 수원시', '1992-07-23', 'F', 2),
('user003', 'password789', '이철수', 'lee3@example.com', '010-3344-5566', '032-456-7890', '부산시 해운대구', '1985-10-30', 'M', 3),
('user004', 'password012', '박지영', 'park4@example.com', '010-4455-6677', '033-567-8901', '인천시 부평구', '1995-03-15', 'F', 1),
('user005', 'password345', '최민수', 'choi5@example.com', '010-5566-7788', '042-678-9012', '대전시 서구', '1993-12-05', 'M', 2),
('user006', 'password678', '윤수진', 'yoon6@example.com', '010-6677-8899', '052-789-0123', '울산시 남구', '1988-01-19', 'F', 1),
('user007', 'password901', '오세훈', 'oh7@example.com', '010-7788-9900', '061-890-1234', '광주시 북구', '1987-08-22', 'M', 3),
('user008', 'password234', '한지민', 'han8@example.com', '010-8899-0011', '051-901-2345', '경상북도 포항시', '1991-04-28', 'F', 1),
('user009', 'password567', '서지수', 'seo9@example.com', '010-9900-1122', '063-234-5678', '전주시 완산구', '1994-06-13', 'F', 2),
('user010', 'password890', '장우진', 'jang10@example.com', '010-1122-3344', '02-123-4567', '서울시 종로구', '1986-09-02', 'M', 1),
('user011', 'passwordabc', '김민지', 'kim11@example.com', '010-2233-4455', '031-567-8901', '경기도 안양시', '1990-11-11', 'F', 2),
('user012', 'passworddef', '조수미', 'cho12@example.com', '010-3344-5566', '032-456-7890', '부산시 동래구', '1985-05-23', 'F', 3),
('user013', 'passwordghi', '임윤호', 'lim13@example.com', '010-4455-6677', '033-678-9012', '인천시 송도구', '1992-01-14', 'M', 1),
('user014', 'passwordjkl', '오미정', 'oh14@example.com', '010-5566-7788', '042-789-0123', '대전시 유성구', '1994-09-30', 'F', 2),
('user015', 'passwordmno', '한상우', 'han15@example.com', '010-6677-8899', '052-890-1234', '울산시 울주군', '1993-02-02', 'M', 1),
('user016', 'passwordpqr', '최진영', 'cho16@example.com', '010-7788-9900', '061-123-4567', '광주시 서구', '1990-08-19', 'F', 3),
('user017', 'passwordstu', '윤진우', 'yoon17@example.com', '010-8899-0011', '051-234-5678', '경상북도 경주시', '1987-12-11', 'M', 2),
('user018', 'passwordvwx', '김하늘', 'kim18@example.com', '010-9900-1122', '063-567-8901', '전라북도 군산시', '1995-06-25', 'F', 1),
('user019', 'passwordyz1', '장우석', 'jang19@example.com', '010-1122-3344', '02-567-8901', '서울시 마포구', '1991-10-14', 'M', 2),
('user020', 'password1234', '박수현', 'park20@example.com', '010-2233-4455', '031-890-1234', '경기도 화성시', '1992-03-01', 'F', 1);

GROUP BY 기본 형식

SELECT [열] FROM [테이블명] WHERE [열] = [조건값] GROUP BY [열] HAVING [열] = [조건값]

※ SELECT하는 열과 GROUP BY의 열은 같아야 한다

회원 권한을 그룹화

mysql> select mb_level from member group by mb_level;
+----------+
| mb_level |
+----------+
|        1 |
|        2 |
|        3 |
+----------+

회원 권한을 중복만 제거하여 출력

mysql> select distinct mb_level from member;
+----------+
| mb_level |
+----------+
|        1 |
|        2 |
|        3 |
+----------+
3 rows in set (0.00 sec)

※ DISTINCT는 단순히 중복만 제거하기때문에 계산이 필요하다면 GROUP BY를 사용해야한다.

COUNT

그룹화한 튜플의 데이터 개수를 카운트

SELECT [컬럼1], COUNT(*) FROM [테이블명] GROUP BY [컬럼1]
SELECT [컬럼1], COUNT(*) AS CNT FROM [테이블명] GROUP BY [컬럼1]

AS(alias)

alias는 별칭을 뜻하며 필드명, 테이블 명에 붙일 수 있다

SELECT mb_level AS lv, mb_addr AS addr from member AS mb limit 5;
mysql> SELECT mb_level AS lv, mb_addr AS addr from member AS mb limit 5;
+------+-----------------+
| lv   | addr            |
+------+-----------------+
|    1 | 서울시 강남구   |
|    2 | 경기도 수원시   |
|    3 | 부산시 해운대구 |
|    1 | 인천시 부평구   |
|    2 | 대전시 서구     |
+------+-----------------+
5 rows in set (0.00 sec)

member 테이블에 mb_sex 컬럼의 카운트

mysql> select mb_level, mb_sex, count(*) as cnt from member group by mb_level, mb_sex order by mb_level asc;
+----------+--------+-----+
| mb_level | mb_sex | cnt |
+----------+--------+-----+
|        1 | F      |   5 |
|        1 | M      |   4 |
|        2 | F      |   4 |
|        2 | M      |   3 |
|        3 | F      |   2 |
|        3 | M      |   2 |
+----------+--------+-----+
6 rows in set (0.00 sec)

HAVING

GROUP BY는 WHERE절을 사용할 수 없기 때문에 HAVING을 사용한다

mb_level과 mb_sex로 그룹화하고 mb_level이 1인 데이터 조회

mysql> select mb_level, mb_sex, count(*) as cnt from member group by mb_level, mb_sex having mb_level = 1;
+----------+--------+-----+
| mb_level | mb_sex | cnt |
+----------+--------+-----+
|        1 | M      |   4 |
|        1 | F      |   5 |
+----------+--------+-----+
2 rows in set (0.00 sec)

mb_sex, mb_name, mb_birth를 그룹화하고 성별이 남자인 회원만 생년월일 오름차 순으로 조회

mysql> select mb_sex, mb_name, mb_birth from member group by mb_sex, mb_name, mb_birth having mb_sex = 'M' order by mb_birth asc;
+--------+---------+------------+
| mb_sex | mb_name | mb_birth   |
+--------+---------+------------+
| M      | 이철수  | 1985-10-30 |
| M      | 장우진  | 1986-09-02 |
| M      | 오세훈  | 1987-08-22 |
| M      | 윤진우  | 1987-12-11 |
| M      | 홍길동  | 1990-05-12 |
| M      | 장우석  | 1991-10-14 |
| M      | 임윤호  | 1992-01-14 |
| M      | 한상우  | 1993-02-02 |
| M      | 최민수  | 1993-12-05 |
+--------+---------+------------+
9 rows in set (0.00 sec)
반응형
저작자표시 비영리 변경금지

'MYSQL' 카테고리의 다른 글

[MYSQL][08] ALTER  (1) 2024.11.27
[MYSQL][07] ORDER BY  (0) 2024.11.26
[MYSQL][06] 외래키(Foreign Key, FK)의 참조 무결성  (0) 2024.11.25
[MYSQL][05] KEY  (0) 2024.11.24
[MYSQL][04] SELECT  (0) 2024.11.22

ALTER

예제 쿼리문

부모 테이블 생성

mysql> create table parent_table(col1 int);
Query OK, 0 rows affected (0.02 sec)
mysql> desc parent_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

자식 테이블 생성

mysql> create table child_table(col1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> desc child_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

PK의 추가 및 삭제

PK 추가

ALTER TABLE [테이블명] ADD PRIMARY KEY(PK_COLUMN);
mysql> alter table parent_table add primary key(col1);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | int  | NO   | PRI | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

PK 삭제

ALTER TABLE [테이블명] DROP PRIMARY KEY;
mysql> alter table parent_table drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | int  | NO   |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

FK의 추가 및 삭제

FK 추가

외래키 추가시 자식 테이블에서 REFERENCES 사용하여 부모 테이블의 PK를 참조한다.

ALTER TABLE CHILD_TABLE ADD FOREIGN KEY(C_FK_COLUMN) 
REFERENCES PARENT_TABLE(P_PK_COLUMN)
mysql> alter table child_table add foreign key(col1) references parent_table(col1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc child_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | int  | YES  | MUL | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

FK 삭제

CONSTRAINT 제약 조건을 찾아서 DROP 한다.

mysql> show create table child_table;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| child_table | CREATE TABLE `child_table` (
  `col1` int DEFAULT NULL,
  KEY `col1` (`col1`),
  CONSTRAINT `child_table_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `parent_table` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table child_table drop constraint child_table_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc child_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | int  | YES  | MUL | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

필드 추가

ADD 기본형식

ALTER TABLE [테이블명]
ADD [컬럼명] [타입명] [제약조건];

ADD 여러 필드 입력

ALTER TABLE [테이블명]
ADD [컬럼명] [타입명] [제약조건],
ADD [컬럼명] [타입명] [제약조건],
ADD [컬럼명] [타입명] [제약조건];
mysql> desc parent_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | int  | NO   | PRI | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table parent_table add col3 int;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | int  | NO   | PRI | NULL    |       |
| col3  | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)

필드 위치 지정

AFTER

AFTER 컬럼 뒤에 위치하며 AFTER를 작성하지 않으면 가장 마지막에 추가된다.

ALTER TABLE [테이블명]
ADD [컬럼명] [타입명] [제약조건] AFTER [추가될 필드 앞의 컬럼];
ALTER TABLE [테이블명]
ADD col2 int [제약조건] AFTER [추가될 필드 앞의 컬럼];
mysql> alter table parent_table add col2 int after col1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | int  | NO   | PRI | NULL    |       |
| col2  | int  | YES  |     | NULL    |       |
| col3  | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

FIRST

필드가 제일 앞에 추가된다.


mysql> alter table parent_table add col0 int first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col0  | int  | YES  |     | NULL    |       |
| col1  | int  | NO   | PRI | NULL    |       |
| col2  | int  | YES  |     | NULL    |       |
| col3  | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

필드 수정

MODIFY

기본 형식

ALTER TABLE PARENT_TABLE MODIFY [컬럼명] [타입];

col3을 int->varchar(50) 타입으로 변경

mysql> alter table parent_table modify col3 varchar(50);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col0  | int         | YES  |     | NULL    |       |
| col1  | int         | NO   | PRI | NULL    |       |
| col2  | int         | YES  |     | NULL    |       |
| col3  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

col3을 col0 앞으로 이동


mysql> alter table parent_table modify col3 varchar(50) after col0;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col0  | int         | YES  |     | NULL    |       |
| col3  | varchar(50) | YES  |     | NULL    |       |
| col1  | int         | NO   | PRI | NULL    |       |
| col2  | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

col3을 제일 앞으로 이동

mysql> alter table parent_table modify col3 varchar(50) first;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col3  | varchar(50) | YES  |     | NULL    |       |
| col0  | int         | YES  |     | NULL    |       |
| col1  | int         | NO   | PRI | NULL    |       |
| col2  | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

컬럼명 타입 기재 없이 작성시 오류

mysql> alter table parent_table modify col3 after col2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after col2' at line 1

col3을 col2 뒤로 이동

mysql> alter table parent_table modify col3 varchar(50) after col2;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col0  | int         | YES  |     | NULL    |       |
| col1  | int         | NO   | PRI | NULL    |       |
| col2  | int         | YES  |     | NULL    |       |
| col3  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

※ 타입을 지정해줘야 오류가 발생하지 않는다.

필드명 변경

CHANGE(MySQL 5.x 이상 지원)

필드명 변경시 새로운 컬럼의 타입을 지정해야한다.

ALTER TABLE [테이블명] CHANGE [기존컬럼명] [새컬럼명] [새컬럼타입];

CHANGE COLUMN(MySQL 5.x 이상 지원)

CHANGE와 마찬가지로 필드명 변경시 새로운 컬럼의 타입을 지정해야한다. 

ALTER TABLE [테이블명] CHANGE COLUMN [기존컬럼명] [새컬럼명] [새컬럼타입];
mysql> alter table parent_table change col3 column3 varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| col0    | int         | YES  |     | NULL    |       |
| col1    | int         | NO   | PRI | NULL    |       |
| col2    | int         | YES  |     | NULL    |       |
| column3 | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

RENAME COLUMN(MySQL 8.0 이상 지원)

타입 지정 없이 필드명만 변경 

ALTER TABLE [테이블명] RENAME COLUMN [기존컬럼명] TO [새컬럼명];
mysql> alter table parent_table rename column col2 to column2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| col0    | int         | YES  |     | NULL    |       |
| col1    | int         | NO   | PRI | NULL    |       |
| column2 | int         | YES  |     | NULL    |       |
| column3 | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

필드 삭제

DROP

한 개의 필드 삭제

ALTER TABLE [테이블명] DROP [기존컬럼명];
mysql> alter table parent_table drop column3;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+---------+------+------+-----+---------+-------+
| Field   | Type | Null | Key | Default | Extra |
+---------+------+------+-----+---------+-------+
| col0    | int  | YES  |     | NULL    |       |
| col1    | int  | NO   | PRI | NULL    |       |
| column2 | int  | YES  |     | NULL    |       |
+---------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

여러 개의 필드 삭제

mysql> alter table parent_table drop col0, drop column2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc parent_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col1  | int  | NO   | PRI | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

PK, FK 삭제시 오류

mysql> alter table parent_table drop col1;
ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead
mysql> alter table child_table drop col1;
ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead
mysql> desc child_table;

아래의 링크에서 확인

[MYSQL][06] 외래키(Foreign Key, FK)의 참조 무결성

반응형
저작자표시 비영리 변경금지

'MYSQL' 카테고리의 다른 글

[MYSQL][09] GROUP BY  (0) 2024.11.28
[MYSQL][07] ORDER BY  (0) 2024.11.26
[MYSQL][06] 외래키(Foreign Key, FK)의 참조 무결성  (0) 2024.11.25
[MYSQL][05] KEY  (0) 2024.11.24
[MYSQL][04] SELECT  (0) 2024.11.22

ORDER BY 문

order by 기본형식은 다음과 같다.


SELECT * FROM [테이블명] ORDER BY [컬럼] [ASC|DESC];

ASC(ascending) : 오름차순(default 값)

DESC(descening) : 내림차순

두 개의 열 정렬 예시

SELECT * FROM [테이블명] WHERE [조건] ORDER BY [컬럼1], [컬럼2 DESC];

1차 정렬 : 컬럼1 ASC

2차 정렬 : 컬럼2 DESC

1차 정렬 후 2차 정렬 진행된다.

LIMIT

LIMIT절은 반환할 튜플 수를 지정할 수 있다.
예를 들어 방문자 카운트 테이블 같이 튜플이 굉장히 많은 대형 테이블에서 사용하면 좋다.

limit 기본 형식

SELECT * FROM [테이블명] LIMIT N;

조회결과 : N개 출력

mysql> select actor_id, first_name, last_name from actor limit 10;
+----------+------------+--------------+
| actor_id | first_name | last_name    |
+----------+------------+--------------+
|        1 | PENELOPE   | GUINESS      |
|        2 | NICK       | WAHLBERG     |
|        3 | ED         | CHASE        |
|        4 | JENNIFER   | DAVIS        |
|        5 | JOHNNY     | LOLLOBRIGIDA |
|        6 | BETTE      | NICHOLSON    |
|        7 | GRACE      | MOSTEL       |
|        8 | MATTHEW    | JOHANSSON    |
|        9 | JOE        | SWANK        |
|       10 | CHRISTIAN  | GABLE        |
+----------+------------+--------------+

조회결과 : 기본 정렬기준으로 10개 출력

limit 범위

SELECT * FROM [테이블명] WHERE [컬럼] = [값] LIMIT N, M;

N : offset(지정 위치), M : 개수

조회결과 : N+1 부터 M개 출력

mysql> select actor_id, first_name, last_name from actor limit 1, 10;
+----------+------------+--------------+
| actor_id | first_name | last_name    |
+----------+------------+--------------+
|        2 | NICK       | WAHLBERG     |
|        3 | ED         | CHASE        |
|        4 | JENNIFER   | DAVIS        |
|        5 | JOHNNY     | LOLLOBRIGIDA |
|        6 | BETTE      | NICHOLSON    |
|        7 | GRACE      | MOSTEL       |
|        8 | MATTHEW    | JOHANSSON    |
|        9 | JOE        | SWANK        |
|       10 | CHRISTIAN  | GABLE        |
|       11 | ZERO       | CAGE         |
+----------+------------+--------------+
10 rows in set (0.00 sec)

조회결과 : 2부터 10개 출력

SELECT * FROM [테이블명] LIMIT M OFFSET N;

조회결과 : N까지는 건너 뛰고 M개 출력

mysql> select actor_id, first_name, last_name from actor limit 1 offset 10;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|       11 | ZERO       | CAGE      |
+----------+------------+-----------+
1 row in set (0.00 sec)

조회결과 : 10까지 건너뛰고 1개 출력

LIKE

LIKE 기본 형식


select * from [테이블명] where [열] LIKE [와일드카드 패턴]

와일드카드

와일드카드 문자는 문자열에서 하나 이상의 문자를 대체할 수 있는데 = 연산자가 아닌 LIKE 연산자를 사용하여 패턴을 만들어 데이터를 조회한다.
퍼센트(%)와 언더스코어(_)를 사용하며 퍼센트는 0개 이상의 문자열을 대체, 언더스코어는 단일 문자를 대체한다.

(X) select * from [테이블명] where [열] = [패턴]
(O) select * from [테이블명] where [열] LIKE [패턴]

% : 0개 이상의 문자열을 대체

_(Under score) : 단일 문자를 대체

  • LIKE 'A%' : A로 시작하는 모든 문자열
  • LIKE '%A' : A로 끝나는 모든 문자열
  • LIKE '%A%' : A가 포함된 모든 문자열
  • LIKE 'A__' : A로 시작하는 두 글자인 문자열
  • LIKE '_A' : A로 끝나는 두 글자인 문자열
  • `LIKE '_A_'` : 세 글자 문자열 중 가운데 글자만 A인 문자열

ESCAPE

퍼센트와 언더스코어는 예약어기 때문에 퍼센트를 포함한 문자열(%%%) 또는 언더스코어를 포함한 문자열(%_%)을 검색할 수 없기 때문데 ESCAPE를 사용하여 조회를 한다.

퍼센트가 포함된 컬럼 조회

SELECT * FROM [테이블명] WHERE [열] LIKE '%\%%' ESCAPE '\';

언더스코어가 포함된 컬럼 조회

SELECT * FROM [테이블명] WHERE [열] LIKE '%\_%' ESCAPE '\';

REGEXP

정규표현식(Regular Expression)을 활용하여 문자열 패턴을 만들어 다양한 방법으로 데이터를 조회할 수 있다.

REGEXP 기본형식

SELECT * FROM [테이블명] WHERE [열] REGEXP [정규식];

정규표현식 종류

  • . : \n을 제외한 임의의 한 문자
  • * : 패턴이 0번 이상 반복
  • + : 패턴이 1번 이상 반복
  • ^ : 문자열의 처음
  • $ : 문자열의 끝
  • | : or
  • [...] : 대괄호 안 문자
  • [^...] : 대괄호 안 문자 아닌 문자여
  • {n} : n회 반복
  • {m,n} : 반복되는 횟수의 최소, 최댓값
반응형
저작자표시 비영리 변경금지

'MYSQL' 카테고리의 다른 글

[MYSQL][09] GROUP BY  (0) 2024.11.28
[MYSQL][08] ALTER  (1) 2024.11.27
[MYSQL][06] 외래키(Foreign Key, FK)의 참조 무결성  (0) 2024.11.25
[MYSQL][05] KEY  (0) 2024.11.24
[MYSQL][04] SELECT  (0) 2024.11.22

외래키(Foreign Key, FK)의 참조 무결성

외래키가 기본키를 참조할 때 참조무결성이 발생 가능한 상황

  1. 부모 테이블 - 데이터 삽입 : (X) 값이 생겨도 무관하다

  2. 부모 테이블 - 데이터 삭제 : (O) 참조 못할 수 있음

  3. 부모 테이블 - 데이터 수정 : (O) 참조 못할 수 있음

  4. 자식 테이블 - 데이터 삽입 : (O) 잘못된 값 삽입

  5. 자식 테이블 - 데이터 삭제 : (X) 참조할 테이블에 문제가 없기 때문에 가능성이 없다

  6. 자식 테이블 - 데이터 수정 : (O) 외래키가 수정되면 참조 무결성 발생

※ 부모테이블 데이터 삽입, 자식테이블 데이터 삭제시 참조 무결성이 발생하지 않는다.

부모, 자식 테이블 생성

부모테이블에 PK 설정 자식테이블에 부모테이블의 PK를 참조하는 FK 설정


mysql> create table parent_table(col1 int primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> create table child_table(col1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> alter table child_table add foreign key (col1) references parent_table(col1);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

부모 PK 데이터 INSERT

mysql> insert into parent_table value (10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into parent_table value (20);
Query OK, 1 row affected (0.00 sec)

mysql> insert into parent_table value (30);
Query OK, 1 row affected (0.00 sec)

mysql> insert into parent_table value (40);
Query OK, 1 row affected (0.00 sec)

mysql> insert into parent_table value (50);
Query OK, 1 row affected (0.00 sec)

mysql> select * from parent_table;
+------+
| col1 |
+------+
|   10 |
|   20 |
|   30 |
|   40 |
|   50 |
+------+
5 rows in set (0.00 sec)

자식 테이블 외래키가 지정된(부모 PK 참조) 필드에 부모 PK가 없는 값 입력

mysql> insert into child_table value (100);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mysite`.`child_table`, CONSTRAINT `child_table_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `parent_table` (`col1`))

부모테이블에 있는 값 입력

mysql> insert into child_table value (10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into child_table value (20);
Query OK, 1 row affected (0.00 sec)

mysql> insert into child_table value (30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from child_table;
+------+
| col1 |
+------+
|   10 |
|   20 |
|   30 |
+------+
3 rows in set (0.00 sec)

부모 테이블 데이터 삭제 시 오류

자식 테이블 데이터를 먼저 삭제하고 부모 데이터 삭제한다.

mysql> delete from parent_table where col1 = 10;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mysite`.`child_table`, CONSTRAINT `child_table_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `parent_table` (`col1`))

자식 삭제 후 부모 데이터 삭제

테이블도 자식, 부모 순으로 삭제해야한다 

mysql> delete from child_table where col1 = 10;
Query OK, 1 row affected (0.00 sec)

mysql> delete from parent_table where col1 = 10;
Query OK, 1 row affected (0.04 sec)

mysql> select * from parent_table;
+------+
| col1 |
+------+
|   20 |
|   30 |
|   40 |
|   50 |
+------+
4 rows in set (0.00 sec)

mysql> select * from child_table;
+------+
| col1 |
+------+
|   20 |
|   30 |
+------+
2 rows in set (0.00 sec)

자식테이블 제약조건 확인


mysql> show create table child_table;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| child_table | CREATE TABLE `child_table` (
  `col1` int DEFAULT NULL,
  KEY `col1` (`col1`),
  CONSTRAINT `child_table_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `parent_table` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

제약조건 제거

ALTER TABLE child_table DROP CONSTRAINT child_table_ibkf_1;
mysql> alter table child_table drop constraint child_table_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

FOREIGN KEY 비활성화

운영중인 서비스 및 타 테이블에 영향을 줄 수 있기 때문에 알아만 두고 넘어가자.

SELECT @@FOREIGN_KEY_CHECK;

비활성화

SET FOREIGN_KEY_CHECK = 0;

활성화

SET FOREIGN_KEY_CHECK = 1;
반응형
저작자표시 비영리 변경금지

'MYSQL' 카테고리의 다른 글

[MYSQL][08] ALTER  (1) 2024.11.27
[MYSQL][07] ORDER BY  (0) 2024.11.26
[MYSQL][05] KEY  (0) 2024.11.24
[MYSQL][04] SELECT  (0) 2024.11.22
[MYSQL][03] CREATE : 회원 테이블 생성  (0) 2024.11.21
KEY

상품 테이블

상품코드 회사명 단가
A 교촌 25,000
B BBQ 30,000
C 네네 23,000
D 노랑통닭 22,000

판매 테이블

판매번호 상품코드 수량 판매일 판매금액
1 B 1 2024-11-19
2 A 1 2024-11-19
3 D 1 2024-11-19
4 C 1 2024-11-19

유일성

데이터베이스 내에서 중복되지 않도록 보장하며 고유하게 식별할 수 있는 것을 말한다. 예를 들어 주민등록번호, 학번 등이 있고 이 기준으로 하나의 데이터(사람)을 식별할 수 있다.

특정 필드를 기준으로 테이블 내에서 유일하게 하나를 선택할 수 있다면 유일성을 만족하게 되며 위 상품테이블 예시에서 상품코드 기준으로 중복없이 회사명, 단가를 확인할 수 있다.

최소성

후보키를 구성하는 속성의 집합에서 어느 하나라도 제거하면 더 이상 해당 키가 테이블의 튜플(행)을 고유하게 식별할 수 없는 경우를 최소성이라 한다.

즉, 최소성이란 후보키가 데이터를 고유하게 식별하기 위해 꼭 필요한 속성만 포함하고, 불필요한 속성을 포함하지 않는 상태를 의미한다.

다른 속성과 결합하지 않아도 유일성을 유지할 수 있는 필드는 최소성을 충족한다고 말한다.

키의 개념 및 종류

상품 테이블에서 유일성, 최소성을 만족하는 필드는 상품코드 또는 회사명이다.
이러한 키를 후보키(Candidate key)라고 하며 후보키 중에서 하나가 기본키가 될 수 있으며 기본키(Primary Key, PK)는 개발자가 직접 지정한다.

후보키 중에서 기본키가 되지 못한 키를 대체키(Alternative Key)라고 하며 유일성은 만족하지만 최소성을 만족하지 못하는 키를 슈퍼키(Super Key)라고 한다.

판매 테이블이 상품코드 필드를 값으로 상품 테이블을 참조하여 상품코드가 B라면 상품명 네네, 단가 23000을 확인할 수 있다. 다른 테이블의 기본키를 참조하는 현재 테이블의 필드를 외래키(Foreign Key, FK)라고 한다

무결성

무결성에는 개체, 참조, 도메인이 있다

개체 무결성 : 기본키(Primary Key, PK)는 중복 값 입력과 null도 불가능하다. 각 행을 고유하게 식별해야할 때 PK로 지정한다

도메인 무결성 : 각 열의 데이터가 타입, 형식 제한 조건을 준수해야 함을 뜻한다. 성별 필드의 경우 남, 여의 값만 저장되어야한다 등의 제약조건이 필요하며 데이터 타입, not null, check 제약 조건 등을 사용한다.

참조 무결성 : 자식 테이블의 FK(Foreing Key, FK)를 통해 자식테이블이 부모테이블을 참조할 수 있도록 보장한다.

외래키가 기본키를 참조할 때 참조 무결성이 발생 가능한 상황

  1. 상품 테이블 - 데이터 삽입 : X, 새로운 상품코드가 생겨 무관하다
  2. 상품 테이블 - 데이터 삭제 : O, 참조 못할 수 있음
  3. 상품 테이블 - 데이터 수정 : O, 참조 못할 수 있음
  4. 판매 테이블 - 데이터 삽입 : O, 잘못된 값 삽입
  5. 판매 테이블 - 데이터 삭제 : X, 참조할 테이블에 문제가 없기 때문에 가능성이 없다
  6. 판매 테이블 - 데이터 수정 : O, 외래키가 수정되면 참조 무결성 발생

따라서 상품 테이블의 데이터 삽입 또는 판매 테이블의 데이터 삭제는 참조 무결성이 발생할 가능성이 없다.

정리

  1. 후보키(Candidate key) : 유일성, 최소성을 만족하는 키
  2. 기본키(Primary Key, PK) : 후보키 중에서 선택
  3. 대체키(Alternative Key) : 기본키가 되지 못한 후보키
  4. 슈퍼키(Super Key) : 유일성은 만족하나 최소성을 만족하지 못하는 키
  5. 외래키(Foreign Key, FK) : 다른 테이블의 기본키를 참조하는 현재 테이블의 필드
  6. 기본키는 중복 값 입력과 null도 불가능 => 개체 무결성
반응형
저작자표시 비영리 변경금지

'MYSQL' 카테고리의 다른 글

[MYSQL][07] ORDER BY  (0) 2024.11.26
[MYSQL][06] 외래키(Foreign Key, FK)의 참조 무결성  (0) 2024.11.25
[MYSQL][04] SELECT  (0) 2024.11.22
[MYSQL][03] CREATE : 회원 테이블 생성  (0) 2024.11.21
[MYSQL][02] 데이터베이스 및 테이블 생성 예제  (1) 2024.11.20

SQL 문법 - SELECT

Mysql 설치시 제공되는 Sakila, World DB를 활용한 SELECT 예제를 진행한다.

Select : 데이터 조회하기

기본 형식

SELECT [컬럼1, 컬럼2, ...] FROM [테이블]

하나의 열을 조회

SELECT column1 FROM tablename;

하나의 열을 조회

SELECT column1 FROM tablename;

두 개 이상의 열을 조회

SELECT column1, column2 FROM tablename;

전체 열을 조회

SELECT * FROM tablename;

WHERE문

테이블에 저장된 전체 데이터를 조회하게되면 시간이 오래 걸려 출력이 늦어지게 되고, 과부하를 줄이기 위해 내가 원하는 조건의 데이터만 WHERE문을 이용하여 조회한다.

기본 형식

SELECT [컬럼1, 컬럼2, ...] FROM [테이블] WHERE [컬럼] [비교연산자] [값]

비교연산자 종류

  • 컬럼 = 조건값 : 조건값과 동일한 값을 조회

  • 컬럼 !=(<>) 조건값 : 조건값과 같지 않은 값을 조회

  • 컬럼 < 조건값 : 조건값 보다 작은 값을 조회

  • 컬럼 <= 조건값 : 조건값보다 작거나 같은 값을 조회

  • 컬럼 > 조건값 : 조건값보다 큰 값을 조회

  • 컬럼 >= 조건값 : 조건값보다 크거나 같은 값을 조회

※ 익숙한 비교 연산자 외 <>, !<, !> 만 숙지하면 된다.
※ 크기를 비교하는 연산자는 숫자에만 사용하는 것을 권장한다.

where문에서 비교 연산자 사용

= 연산자

actor 테이블에서 first_name 컬럼의 값이 michael인 데이터 조회

mysql> select * from actor where first_name = 'michael';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      174 | MICHAEL    | BENING    | 2006-02-15 04:34:33 |
|      185 | MICHAEL    | BOLGER    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

<>, != 연산자

language 테이블에서 name 컬럼의 값이 English가 아닌 데이터 조회

mysql> select * from language where name <> 'English';
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
5 rows in set (0.00 sec)

> 연산자

address 테이블에서 city_id 컬럼의 값이 598보다 큰 데이터 조회

mysql> select address, district, city_id from address where city_id > 598;
+---------------------+------------+---------+
| address             | district   | city_id |
+---------------------+------------+---------+
| 346 Cam Ranh Avenue | Zhejiang   |     599 |
| 1889 Valparai Way   | Ziguinchor |     600 |
+---------------------+------------+---------+
2 rows in set (0.00 sec)

< 연산자

payment 테이블에서 payment_date 컬럼의 값이 2005-05-25 이전인 데이터 조회

mysql> select payment_id, amount, payment_date from payment where payment_date < '2005-05-25';
+------------+--------+---------------------+
| payment_id | amount | payment_date        |
+------------+--------+---------------------+
|       3504 |   2.99 | 2005-05-24 22:53:30 |
|       6003 |   6.99 | 2005-05-24 23:05:21 |
|       6440 |   4.99 | 2005-05-24 23:31:46 |
|       7274 |   1.99 | 2005-05-24 23:11:53 |
|       8987 |   4.99 | 2005-05-24 23:04:41 |
|      11032 |   3.99 | 2005-05-24 23:03:39 |
|      12377 |   2.99 | 2005-05-24 22:54:33 |
|      14728 |   0.99 | 2005-05-24 23:08:07 |
+------------+--------+---------------------+
8 rows in set (0.01 sec)

<= 연산자

payment 테이블에서 payment_id 컬럼이 5보다 작거나 같은 데이터 조회

mysql> select payment_id, amount, payment_date from payment where payment_id <= 5;
+------------+--------+---------------------+
| payment_id | amount | payment_date        |
+------------+--------+---------------------+
|          1 |   2.99 | 2005-05-25 11:30:37 |
|          2 |   0.99 | 2005-05-28 10:35:23 |
|          3 |   5.99 | 2005-06-15 00:54:12 |
|          4 |   0.99 | 2005-06-15 18:02:53 |
|          5 |   9.99 | 2005-06-15 21:08:46 |
+------------+--------+---------------------+
5 rows in set (0.00 sec)

>= 연산자

payment_id가 5보다 크거나 같은 데이터 조회


mysql> select payment_id, amount, payment_date from payment where payment_id >= 5;
+------------+--------+---------------------+
| payment_id | amount | payment_date        |
+------------+--------+---------------------+
|       5    |   9.99 | 2005-06-15 21:08:46 |
|       6    |   3.99 | 2005-06-15 23:04:20 |
|       7    |   2.99 | 2005-06-16 10:32:10 |
|       8    |   4.99 | 2005-06-16 11:01:45 |
|       9    |   1.99 | 2005-06-16 12:40:00 |
+------------+--------+---------------------+
5 rows in set (0.01 sec)

논리 연산자 종류 및 예시

AND

두 개 이상의 조건이 모두 참일 때 데이터를 조회

레벨이 1이고 성별이 남자인 회원 

SELECT * FROM member WHERE mb_level = 1 AND mb_sex = 'M';

OR

두 개 이상의 조건 중 하나라도 참이면 결과가 참

레벨이 1이거나 성별이 남자인 회원 

SELECT * FROM member WHERE mb_level = 1 OR mb_sex = 'M';

NOT

조건의 반대 값이 참일 때 결과를 반환

레벨이 100이 아닌 회원 

SELECT * FROM member WHERE NOT mb_level = 100;

LIKE

문자열 패턴과 일치하는 데이터를 찾음

이름이 김씨인 회원 (이름에 김을 포함)

SELECT * FROM member WHERE mb_name LIKE '김%';

BETWEEN

지정된 범위 내의 데이터를 조회

회원 포인트가 3000이상 5000이하인 회원

SELECT * FROM member WHERE mb_point BETWEEN 3000 and 5000;

가입일이 11월1일 ~ 11월 30일인 회원

SELECT * FROM member WHERE regDate BETWEEN '2024-11-01' and '2024-11-30';

IN

피연산자가 값 목록에 하나라도 포함되어 있다면 데이터 조회

레벨이 1, 2, 3인 회원을 조회

SELECT * FROM member WHERE mb_level IN (1, 2, 3);

EXISTS

하위쿼리에 행이 포함되면(결과가 존재하는지) 데이터를 조회

ALL

서브쿼리에서 반환된 모든 값과 비교하여 조건을 만족하는 데이터 조회

ANY

서브쿼리에서 반환된 하나 이상의 값과 비교하여 조건을 만족하는 데이터

SOME

서브쿼리에서 반환된 하나 이상의 값과 비교하여 조건을 만족하는

※ AND, OR, IN, NOT, BETWEEN, LIKE 정도가 쿼리문 작성시 자주 사용된다.

NULL

NULL값인 컬럼 조회

SELECT * FROM member WHERE mb_addr IS NULL;

NULL 값이 아닌 컬럼 조회

SELECT * FROM member WHERE mb_addr IS NOT NULL;

※ NULL은 특정 값이 아니기때문에 WHERE mb_addr = NULL와 같이 = 연산자를 사용하지 않는다.

반응형
저작자표시 비영리 변경금지

'MYSQL' 카테고리의 다른 글

[MYSQL][06] 외래키(Foreign Key, FK)의 참조 무결성  (0) 2024.11.25
[MYSQL][05] KEY  (0) 2024.11.24
[MYSQL][03] CREATE : 회원 테이블 생성  (0) 2024.11.21
[MYSQL][02] 데이터베이스 및 테이블 생성 예제  (1) 2024.11.20
[MYSQL][01] 데이터베이스(DataBase, DB)-1  (1) 2024.11.19

CREATE

회원 데이터 베이스 생성 : CREATE

CREATE DATABASE IF NOT EXISTS mysite;
mysql> CREATE DATABASE IF NOT EXISTS mysite;
Query OK, 1 row affected (0.01 sec)

※ IF NOT EXISTS [데이터베이스명] : 데이터베이스가 없다면이라는 조건

회원 테이블 생성 : CREATE

데이터베이스 선택 후 테이블 생성

mysql> use mysite;
Database changed

CREATE TABLE `member` (
    `mb_no` int AUTO_INCREMENT,  /* 회원 인덱스 */
    `mb_id` varchar(20) NOT NULL,  /* 아이디 */
    `mb_password` varchar(255) NOT NULL,  /* 비밀번호 */
    `mb_name` varchar(50) NOT NULL, /* 이름 */
    `mb_email` varchar(100) NOT NULL,  /* 이메일 */
    `mb_hp` varchar(20) DEFAULT NULL,  /* 핸드폰 */
    `mb_addr` varchar(255) DEFAULT NULL,  /* 주소 */
    `mb_birth` date DEFAULT NULL,  /* 생일 */
    `mb_sex` char(1) DEFAULT NULL,  /* 성별 */
    `mb_level` int DEFAULT '1',  /* 권한 */
    `mb_point` int DEFAULT 0, /* 포인트 */

    PRIMARY KEY (`mb_no`),
    UNIQUE (`mb_id`)
);

결과


mysql> CREATE TABLE `member` (
    ->     `mb_no` int AUTO_INCREMENT,  /* 회원 인덱스 */
    ->     `mb_id` varchar(20) NOT NULL,  /* 아이디 */
    ->     `mb_password` varchar(255) NOT NULL,  /* 비밀번호 */
    ->     `mb_name` varchar(50) NOT NULL, /* 이름 */
    ->     `mb_email` varchar(100) NOT NULL,  /* 이메일 */
    ->     `mb_hp` varchar(20) DEFAULT NULL,  /* 핸드폰 */
    ->     `mb_addr` varchar(255) DEFAULT NULL,  /* 주소 */
    ->     `mb_birth` date DEFAULT NULL,  /* 생일 */
    ->     `mb_sex` char(1) DEFAULT NULL,  /* 성별 */
    ->     `mb_level` int DEFAULT '1',  /* 권한 */
    ->     `mb_point` int DEFAULT 0, /* 포인트 */
    ->
    ->     PRIMARY KEY (`mb_no`), 
    ->     UNIQUE (`mb_id`)
    -> );
Query OK, 0 rows affected (0.04 sec)

회원 데이터 입력 : INSERT

INSERT INTO `member` (`mb_id`, `mb_password`, `mb_name`, `mb_email`, `mb_hp`,  `mb_addr`, `mb_birth`, `mb_sex`, `mb_level`, `mb_point`) VALUES
('user001', 'password123', '홍길동', 'hong1@example.com', '010-1111-2222',  '서울시 강남구', '1990-05-12', 'M', 1, 3000);
mysql> INSERT INTO `member` (`mb_id`, `mb_password`, `mb_name`, `mb_email`, `mb_hp`,  `mb_addr`, `mb_birth`, `mb_sex`, `mb_level`, `mb_point`) VALUES
    -> ('user001', 'password123', '홍길동', 'hong1@example.com', '010-1111-2222',  '서울시 강남구', '1990-05-12', 'M', 1, 3000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from member;
+-------+---------+-------------+---------+-------------------+---------------+---------------+------------+--------+----------+----------+
| mb_no | mb_id   | mb_password | mb_name | mb_email          | mb_hp         | mb_addr       | mb_birth   | mb_sex | mb_level | mb_point |
+-------+---------+-------------+---------+-------------------+---------------+---------------+------------+--------+----------+----------+
|     1 | user001 | password123 | 홍길동  | hong1@example.com | 010-1111-2222 | 서울시 강남구 | 1990-05-12 | M      |        1 |     3000 |
+-------+---------+-------------+---------+-------------------+---------------+---------------+------------+--------+----------+----------+
1 row in set (0.00 sec)

AUTO_INCREMENT

특정 열에 AUTO_INCREMENT를 사용하면 1씩 또는 사용자가 지정한만큼 증가하며 PK를 지정해줘야한다

회원 데이터 여러 명 입력 : INSERT

회원을 여러명 추가하고 기본키는 자동으로 증가가 되지만 직접 입력도 가능하다.


INSERT INTO `member` (`mb_no`,`mb_id`, `mb_password`, `mb_name`, `mb_email`, `mb_hp`,  `mb_addr`, `mb_birth`, `mb_sex`, `mb_level`, `mb_point`) VALUES
(3,'user002', 'password456', '김영희', 'kim2@example.com', '010-2233-4455', '경기도 수원시', '1992-07-23', 'F', 2, 5000),
(4,'user003', 'password789', '이철수', 'lee3@example.com', '010-3344-5566', '부산시 해운대구', '1985-10-30', 'M', 3, 5000),
(10,'user004', 'password012', '박지영', 'park4@example.com', '010-4455-6677', '인천시 부평구', '1995-03-15', 'F', 1, 5000),
(11,'user005', 'password345', '최민수', 'choi5@example.com', '010-5566-7788', '대전시 서구', '1993-12-05', 'M', 2, 5000),
(12,'user006', 'password678', '윤수진', 'yoon6@example.com', '010-6677-8899', '울산시 남구', '1988-01-19', 'F', 1, 5000);



mysql> INSERT INTO `member` (`mb_no`,`mb_id`, `mb_password`, `mb_name`, `mb_email`, `mb_hp`,  `mb_addr`, `mb_birth`, `mb_sex`, `mb_level`, `mb_point`) VALUES
    -> (3,'user002', 'password456', '김영희', 'kim2@example.com', '010-2233-4455', '경기도 수원시', '1992-07-23', 'F', 2, 5000),
    -> (4,'user003', 'password789', '이철수', 'lee3@example.com', '010-3344-5566', '부산시 해운대구', '1985-10-30', 'M', 3, 5000),
    -> (10,'user004', 'password012', '박지영', 'park4@example.com', '010-4455-6677', '인천시 부평구', '1995-03-15', 'F', 1, 5000),
    -> (11,'user005', 'password345', '최민수', 'choi5@example.com', '010-5566-7788', '대전시 서구', '1993-12-05', 'M', 2, 5000),
    -> (12,'user006', 'password678', '윤수진', 'yoon6@example.com', '010-6677-8899', '울산시 남구', '1988-01-19', 'F', 1, 5000);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from member;
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
| mb_no | mb_id   | mb_password | mb_name | mb_email          | mb_hp         | mb_addr         | mb_birth   | mb_sex | mb_level | mb_point |
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
|     1 | user001 | password123 | 홍길동  | hong1@example.com | 010-1111-2222 | 서울시 강남구   | 1990-05-12 | M      |        1 |     3000 |
|     3 | user002 | password456 | 김영희  | kim2@example.com  | 010-2233-4455 | 경기도 수원시   | 1992-07-23 | F      |        2 |     5000 |
|     4 | user003 | password789 | 이철수  | lee3@example.com  | 010-3344-5566 | 부산시 해운대구 | 1985-10-30 | M      |        3 |     5000 |
|    10 | user004 | password012 | 박지영  | park4@example.com | 010-4455-6677 | 인천시 부평구   | 1995-03-15 | F      |        1 |     5000 |
|    11 | user005 | password345 | 최민수  | choi5@example.com | 010-5566-7788 | 대전시 서구     | 1993-12-05 | M      |        2 |     5000 |
|    12 | user006 | password678 | 윤수진  | yoon6@example.com | 010-6677-8899 | 울산시 남구     | 1988-01-19 | F      |        1 |     5000 |
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
6 rows in set (0.00 sec)

AUTO_INCREMENT로 생성된 마지막 값 확인

LAST_INSERT_ID()를 사용하면 AUTO_INCREMENT로 생성된 마지막 인덱스를 알 수 있으며 직접 지정한 PK는 알 수 없다. 주로 이용하는 방법은 게시판에 글과 첨부파일을 올린다고 가정했을 때 게시글을 올리고 얻어낸 LAST_INSERT_ID()로 첨부파일 게시판에 외래키로 사용하곤 한다.

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

회원 삭제 : DELETE

mb_no(기본키)가 10보다 큰 데이터를 삭제


mysql> delete from member where mb_no > 10;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from member;
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
| mb_no | mb_id   | mb_password | mb_name | mb_email          | mb_hp         | mb_addr         | mb_birth   | mb_sex | mb_level | mb_point |
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
|     1 | user001 | password123 | 홍길동  | hong1@example.com | 010-1111-2222 | 서울시 강남구   | 1990-05-12 | M      |        1 |     3000 |
|     3 | user002 | password456 | 김영희  | kim2@example.com  | 010-2233-4455 | 경기도 수원시   | 1992-07-23 | F      |        2 |     5000 |
|     4 | user003 | password789 | 이철수  | lee3@example.com  | 010-3344-5566 | 부산시 해운대구 | 1985-10-30 | M      |        3 |     5000 |
|    10 | user004 | password012 | 박지영  | park4@example.com | 010-4455-6677 | 인천시 부평구   | 1995-03-15 | F      |        1 |     5000 |
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
4 rows in set (0.00 sec)

INSERT - SELECT

다른 테이블의 내용을 조회해서 INSERT하는 방법도 가능하다
아래 예시는 member 테이블에 mb_point가 5000이상인 사람을 조회해서 INSERT하는 방법이다.

point 테이블 생성


CREATE TABLE point (

    p_no int,
    p_name varchar(50),
    p_point int

);


mysql> CREATE TABLE point (
    ->
    ->     p_no int,
    ->     p_name varchar(50),
    ->     p_point int
    ->
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> select * from point;
Empty set (0.00 sec)

포인트가 5000 이상인 사람 조회

mysql> select mb_no as p_no, mb_name as p_name, mb_point as p_point from member where mb_point >= 5000;
+------+--------+---------+
| p_no | p_name | p_point |
+------+--------+---------+
|    3 | 김영희 |    5000 |
|    4 | 이철수 |    5000 |
|   10 | 박지영 |    5000 |
+------+--------+---------+
3 rows in set (0.00 sec)

point 테이블에 INSERT

insert into point select mb_no as p_no, mb_name as p_name, mb_point as p_point from member where mb_point >= 5000;
mysql> insert into point select mb_no as p_no, mb_name as p_name, mb_point as p_point from member where mb_point >= 5000;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from point;
+------+--------+---------+
| p_no | p_name | p_point |
+------+--------+---------+
|    3 | 김영희 |    5000 |
|    4 | 이철수 |    5000 |
|   10 | 박지영 |    5000 |
+------+--------+---------+
3 rows in set (0.00 sec)

※ 타회사 디비 이전(마이그레이션)을 할 때 INSERT SELECT를 자주 사용했었다.
테이블 구조가 유사하면 작업이 수월하지만 대부분 다르기 때문에 별칭으로 컬럼명과 순서까지 회사의 테이블 구조에 맞춰서 일단 조회를 하고 INSERT를 진행했다.

※ 로컬에서 테이블 및 sql 작업을하고 import 하는 것을 추천

※ 디비 이전시 csv파일 편집하는 방법도 가능하나 게시판의 내용에서 태그나 따옴표 등 때문에 셀 영역을 벗어나서 간단한 데이터가 아니면 하지 않은 것을 추천.

AUTO_INCREMENT 시작값 변경

AUTO_INCREMENT 시작값을 101부터 생성되게 변경

ALTER TABLE member AUTO_INCREMENT = 101;

Primary key 오름차순으로 업데이트하기

SET @idx = 0;
UPDATE `member` 
SET `mb_no` = (@idx := @idx + 1)
mysql> select * from member;
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
| mb_no | mb_id   | mb_password | mb_name | mb_email          | mb_hp         | mb_addr         | mb_birth   | mb_sex | mb_level | mb_point |
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
|     1 | user001 | password123 | 홍길동  | hong1@example.com | 010-1111-2222 | 서울시 강남구   | 1990-05-12 | M      |        1 |     3000 |
|     3 | user002 | password456 | 김영희  | kim2@example.com  | 010-2233-4455 | 경기도 수원시   | 1992-07-23 | F      |        2 |     5000 |
|     4 | user003 | password789 | 이철수  | lee3@example.com  | 010-3344-5566 | 부산시 해운대구 | 1985-10-30 | M      |        3 |     5000 |
|    10 | user004 | password012 | 박지영  | park4@example.com | 010-4455-6677 | 인천시 부평구   | 1995-03-15 | F      |        1 |     5000 |
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
4 rows in set (0.00 sec)

mysql> SET @idx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `member`
    -> SET `mb_no` = (@idx := @idx + 1);
Query OK, 3 rows affected, 1 warning (0.00 sec)
Rows matched: 4  Changed: 3  Warnings: 1

mysql> select * from member;
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
| mb_no | mb_id   | mb_password | mb_name | mb_email          | mb_hp         | mb_addr         | mb_birth   | mb_sex | mb_level | mb_point |
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
|     1 | user001 | password123 | 홍길동  | hong1@example.com | 010-1111-2222 | 서울시 강남구   | 1990-05-12 | M      |        1 |     3000 |
|     2 | user002 | password456 | 김영희  | kim2@example.com  | 010-2233-4455 | 경기도 수원시   | 1992-07-23 | F      |        2 |     5000 |
|     3 | user003 | password789 | 이철수  | lee3@example.com  | 010-3344-5566 | 부산시 해운대구 | 1985-10-30 | M      |        3 |     5000 |
|     4 | user004 | password012 | 박지영  | park4@example.com | 010-4455-6677 | 인천시 부평구   | 1995-03-15 | F      |        1 |     5000 |
+-------+---------+-------------+---------+-------------------+---------------+-----------------+------------+--------+----------+----------+
4 rows in set (0.00 sec)

Error Code : 1175 발생시

간혹 DBMS툴에서 에러코드 1175가 발생할 수 있다

11:59:47    UPDATE `member`  SET `mb_no` = (@idx := @idx + 1) ORDER BY `mb_no`    Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.    0.000 sec

Safe Update Mode가 활성화 중인지 확인 (0 : 비활성화 / 1 : 활성화)

SELECT @@SQL_SAFE_UPDATES;

Safe Update Mode 비활성화(현재 세션)

SET SQL_SAFE_UPDATES = 0;

Safe Update Mode 다시 활성화

SET SQL_SAFE_UPDATES = 1;
반응형
저작자표시 비영리 변경금지

'MYSQL' 카테고리의 다른 글

[MYSQL][06] 외래키(Foreign Key, FK)의 참조 무결성  (0) 2024.11.25
[MYSQL][05] KEY  (0) 2024.11.24
[MYSQL][04] SELECT  (0) 2024.11.22
[MYSQL][02] 데이터베이스 및 테이블 생성 예제  (1) 2024.11.20
[MYSQL][01] 데이터베이스(DataBase, DB)-1  (1) 2024.11.19

데이터베이스 및 테이블 생성 예제

데이터베이스명은 std이고 테이블명 student으로 생성하기로 정하고
create, insert, select, update, delete를 진행해본다.

1. 학생 디비 생성

mysql> create database stu;
Query OK, 1 row affected (0.01 sec)

2. 학생 테이블 생성

테이블명 : student

필드

컬럼명 타입 용도
std_id int primary key 학번 인덱스
std_name char(10) 학생 이름
std_dpt varchar(20) 학과
std_tel varchar(20) 연락처
std_addr varchar(20) 주소 
mysql> create table student (std_id int primary key, std_name char(10), std_dpt varchar(20), std_tel varchar(20), std_addr varchar(50));
Query OK, 0 rows affected (0.02 sec)

3. 학생 데이터 입력

mysql> insert into student(std_id, std_name, std_dpt, std_tel, std_addr) values (1, "박명수", "영어영문학과", "010-5555-7878", "제주특별자치도시");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(std_id, std_name, std_tel) value (3, "한길동", "010-3333-3333");
Query OK, 1 row affected (0.00 sec)

4. 학생 데이터 여러 개 입력

INSERT INTO 테이브블명 (컬럼1, 컬럼2, ...)
VALUES (값1, 값2, ...), (값1, 값2, ...);
mysql> insert into student(std_id, std_name, std_dpt, std_tel, std_addr) values (4, "정형돈", "컴퓨터공학과", "010-1313-1414", "광주광역시"), (5, "노홍철", " 신문방송학과", "010-2222-7114", "경기도");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

4. 학생 데이터 조회

mysql> select * from student;
+--------+----------+--------------+---------------+------------------+
| std_id | std_name | std_dpt      | std_tel       | std_addr         |
+--------+----------+--------------+---------------+------------------+
|      1 | 박명수   | 영어영문학과  | 010-5555-7878  | 제주특별자치도시  |
|      3 | 한길동   | NULL         | 010-3333-3333  | NULL             |
|      4 | 정형돈   | 컴퓨터공학과  | 010-1313-1414  | 광주광역시        |
|      5 | 노홍철   | 신문방송학과  | 010-2222-7114  | 경기도            |
+--------+----------+--------------+---------------+------------------+
4 rows in set (0.00 sec)
2 rows in set (0.00 sec)

5. 학생 테이터 수정


UPDATE 테이블명
SET 컬럼1 = 값1, 컬럼2 = 값2, ...
WHERE 조건;


mysql> update student set std_dpt = "AI학과" where std_id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+--------+----------+--------------+---------------+------------------+
| std_id | std_name | std_dpt      | std_tel       | std_addr         |
+--------+----------+--------------+---------------+------------------+
|      1 | 박명수   | 영어영문학과   | 010-5555-7878 | 제주특별자치도시 |
|      3 | 한길동   | AI학과        | 010-3333-3333 | NULL             |
|      4 | 정형돈   | 컴퓨터공학과   | 010-1313-1414 | 광주광역시       |
|      5 | 노홍철   | 신문방송학과   | 010-2222-7114 | 경기도           |
+--------+----------+--------------+---------------+------------------+
4 rows in set (0.00 sec)

6. 학생 데이터 삭제

DELETE FROM 테이블명 WHERE 조건;

mysql> delete from student where std_id = 5;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+--------+----------+--------------+---------------+------------------+
| std_id | std_name | std_dpt      | std_tel       | std_addr         |
+--------+----------+--------------+---------------+------------------+
|      1 | 박명수   | 영어영문학과 | 010-5555-7878 | 제주특별자치도시 |
|      3 | 한길동   | AI학과       | 010-3333-3333 | NULL             |
|      4 | 정형돈   | 컴퓨터공학과 | 010-1313-1414 | 광주광역시       |
+--------+----------+--------------+---------------+------------------+
3 rows in set (0.00 sec)

7. 학생 데이터 모두 삭제

DELETE FROM 테이블명

※ 쿼리문 작성 시 where은 필수로 넣고 최소 테이블이나 디비 백업을 해두고 작업하는 편이 좋다.

mysql> delete from student;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)
반응형
저작자표시 비영리 변경금지

'MYSQL' 카테고리의 다른 글

[MYSQL][06] 외래키(Foreign Key, FK)의 참조 무결성  (0) 2024.11.25
[MYSQL][05] KEY  (0) 2024.11.24
[MYSQL][04] SELECT  (0) 2024.11.22
[MYSQL][03] CREATE : 회원 테이블 생성  (0) 2024.11.21
[MYSQL][01] 데이터베이스(DataBase, DB)-1  (1) 2024.11.19

[MYSQL][01] 데이터베이스(DataBase, DB)-1

목차

1. 데이터베이스란?

2. 데이터베이스의 역할

3. 데이터베이스의 장단점

4. 데이터베이스 관리 시스템

5. 데이터베이스의 종류

6. SQL(Structured Query Language, SQL)이란?

7. SQL문법 종류

8. 데이터베이스 생성 및 삭제

9. 테이블 생성 및 삭제

10. 데이터 삽입, 조회, 수정, 삭제

1. 데이터베이스란?

공유(Share)할 목적으로 통합(Integrate) 관리를 위해 일정한 형태로 저장(Stored)한 데이터를 데이터베이스라고 한다.

데이터베이스의 정의 : ISOS

Integrated Data(통합된 데이터) : 중복을 최소화 한 데이터
Stored Data(저장된 데이터) : 컴퓨터가 접근가능한 위치에 저장된 데이터
Operational Data(운영 데이터) : 업무수행을 위해 반드시 필요한 데이터
Shared Data(공용 데이터) : 여러 응용시스템이 공동 소유 및 유지하는 데이터

데이터베이스 시스템의 특징 : R1C3

Real Time Accessibility(실시간 접근성) : 질의에 실시간 응답 처리
Continuous Evolution(지속적인 변화) : 최신 데이터를 동적으로 유지(삽입, 삭제, 수정)
Concurrent Sharing(동시 공유) : 목적이 다른 사용자가 동시에 원하는 데이터 공유
Content Reference(내용에 의한 참조) : 주소에 의한 참조가 아닌 사용자가 요구하는(질의에 따른) 데이터 내용을 참조

2. 데이터베이스의 역할

중복데이터의 배제가 아닌 최소화하여 목적에 맞게 관리

3. 데이터베이스의 장단점

장점

  • 데이터 중복 방지
    • 데이터를 중앙에서 관리하여 중복을 최소화, 일관성 있는 데이터를 유지
  • 효율적인 저장공간 사용
    • 중복을 제거하고, 데이터를 정규화하여 저장 공간을 절약
  • 빠른 데이터 접근
    • 인덱싱, 쿼리 최적화 등을 통해 빠르고 효율적인 데이터 검색이 가능
  • 데이터 공유 용이
    • 여러 사용자나 애플리케이션이 동일한 데이터를 접근하고 수정할 수 있어 협업 및 데이터 공유가 용이
  • 데이터 무결성 보장
    • 데이터베이스 관리 시스템(DBMS)은 무결성 제약조건을 통해 데이터의 정확성, 일관성을 보장
  • 보안 강화
    • DBMS는 사용자 권한, 인증, 암호화 등의 보안 기능을 제공하여 데이터 접근을 제어하고 보호
  • 백업 및 복구 기능
    • 데이터 손실을 방지하기 위한 백업 및 복구 기능이 제공되어 안전하게 데이터를 보호

단점

  • 시스템 복잡성
    • 설계, 구축 및 관리가 복잡하여 운영을 위해서 기술의 전문성이 필요
  • 전문 인력 필요
    • 관리 및 최적화, 문제 해결 등을 위해 전문가가 필요
  • 운영 비용 증가
    • 관리 및 유지보수, 라이센스 비용 등이 발생하여 운영 비용이 증가
  • 백업 및 복구 시간
    • 대규모 데이터베이스의 백업 및 복구는 시간이 많이 걸리고, 대량의 데이터 처리로 시스템에 부담
  • 성능 문제
    • 데이터베이스가 매우 크거나 복잡해질 경우, 쿼리 성능이 저하되거나 시스템 과부하 야기
  • 보안 위험성
    • 보안이 제대로 구성되지 않으면 해킹, 데이터 유출 등의 관리 위험성

4. 데이터베이스 관리 시스템 (DataBase Management System, DBMS)

데이터의 입력, 수정, 삭제, 조회 등을 제공하는 소프트웨어

5. 데이터베이스의 종류

5-1. 계층형 데이터베이스

부모 자식관계인 트리구조

5-2. 네트워크형 데이터베이스

  • 데이터를 노드로 표현한 모델

5-3. Key-Value(키-값) 데이터베이스

  • NoSQL의 한 종류
  • 키 값을 일대일 대응
  • 데이터 중복이 발생하며 비정형데이터 저장에 유리

5-4. 관계형 데이터베이스

실무에서 가장 많이 사용하는 데이터베이스 종류

관계형 데이터베이스의 구조

  • 테이블(Table) = 릴레이션(Relation) : 행과 열의 값들의 모음, 도메인 특성에 따라 데이터를 논리적으로 그룹화
    • 행(Row), 튜플(Tuple), 레코드(Record) : 관계된 데이터의 묶음, 각 행은 기본키(Primary Key, PK)로 구분 및 식별
    • 열(Column), 필드(Field), 어트리뷰트(Attribute, 속성) : 고유한 이름과 타입이 있다.

※ 도메인(Domain) : 특정 속성(필드, 컬럼)의 허용 값의 타입과 범위

※ 도메인의 주요 특성

  • 유형제한 : 특정 데이터 타입으로 제한(숫자, 문자열, 날짜 등)
  • 범위제한 : 값의 범위를 정의
  • 형식제한 : 값의 형식을 정의(이메일, 주소, 전화번호 등)
  • 일관성보장

※ 도메인의 예시

  • 성별 필드의 도메인 : 남, 여
  • 학년 필드의 도메인 : 1,2,3,4

ERD(Entity Relationship Diagram)

테이블 간의 관계를 표현한 다이어그램

  • 논리모델 : 요구사항을 수집 및 분석해 데이터베이스의 모양을 구성
  • 물리모델 : 논리모델을 기반으로 실제 데이터를 저장할 수 있는 모델을 표현

대표적 DBMS

  • SQL Server
  • Oracle Database
  • DB2
  • PostregSQL
  • MySQL
  • SQLite

6. SQL(Structured Query Language, SQL)이란?

관계형 데이터베이스 관리시스템의 데이터를 관리하는 프로그래밍 언어

※ 구조(Structured) -> 정형화 되어 있다. -> 정형화된 데이터를 질의해서 다룬다.
※ NoSQL은 비정형 데이터를 다룬다.
※ 관계형 데이터베이스(Relational DataBase Management System, RDBMS)

7. SQL 문법의 종류

데이터 정의 언어(DDL), 데이터 조작 언어(DML), 데이터 제어 언어(DCL)

7-1. 데이터 정의 언어(Data Define Language, DDL)

데이터베이스를 정의하는 언어

  • Create : 테이블 생성
  • Alter : 테이블 수정
  • Drop : 디비 또는 테이블 삭제
  • Truncate : 테이블 초기화

7-2. 데이터 조작 언어(Data Manipulation Language, DML)

  • Select : 데이터 조회
  • Insert : 데이터 입력
  • Update : 데이터 수정
  • Delete : 데이터 삭제

7-3. 데이터 제어 언어(Data Control Language, DCL)

  • Grant : 특정 디비 사용자에게 특정 권한 부여
  • Revote : 권한 삭제
  • Commit : 트랜잭션 작업을 완료
  • Rollback : 트랜잭션 작업을 취소 및 이전상태로 복구

8. 데이터베이스 생성 및 삭제

8-1. mysql 접속

mysql -u root -p
Enter password : ******
mysql>

8-2. DDL - create : 데이터베이스 생성

mysql> create database myDatabase;
Query OK, 1 row affected(0.01 SEC)

8-3. 데이터베이스 목록 확인

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| mydatabase         |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

8-4. 데이터베이스 사용(선택)

mysql> use myDatabase;
Database changed

8-5. 테이블 조회

mysql> show tables;
Empty set (0.00 sec)

8-6. DDL - drop : 데이터베이스 삭제

mysql> drop database myDatabase;
Query OK, 0 rows affected (0.02 sec)

9. 테이블 생성 및 삭제

9-1. 테이블 생성

mysql> create table member (col1 int, col2 VARCHAR(50), col3 DATETIME);
Query OK, 0 rows affected (0.04 sec)

9-2. 테이블 생성 확인

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| member               |
+----------------------+
1 row in set (0.00 sec)

9-3. 테이블 구조 확인

mysql> desc member;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1  | int         | YES  |     | NULL    |       |
| col2  | varchar(50) | YES  |     | NULL    |       |
| col3  | datetime    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

9-4. 테이블 삭제

mysql> drop table member;
Query OK, 0 rows affected (0.01 sec)

9-5. 테이블 삭제 확인

mysql> show tables;
Empty set (0.00 sec)

10. 데이터 삽입, 조회, 수정, 삭제

10-1. 데이터 삽입

DML - insert(삽입) 기본형식

INSERT INTO 테이블이름 (열1, 열2, ...) VALUES (값1, 값2, ...);
mysql> INSERT INTO member(col1, col2, col3) VALUES (1,"col2 text", "2024-01-01");
Query OK, 1 row affected (0.01 sec)

10-2. 데이터 조회

DML - select(조회) 기본형식

mysql> select [열1, 열2, ... | * ] from 테이블명
※ 열을 콤마로 구분하여 직접 선택하거나 *를 사용하여 전체를 선택
mysql> select * from member;
+------+-----------+---------------------+
| col1 | col2      | col3                |
+------+-----------+---------------------+
|    1 | col2 text | 2024-01-01 00:00:00 |
+------+-----------+---------------------+
1 row in set (0.00 sec)

10-3. 데이터 삭제

DML - delete(삭졔) 기본 형식

DELETE FROM 테이블명 WHERE 조건;
mysql> delete from member where col1 = 1;
Query OK, 1 row affected (0.00 sec)

10-4. 데이터 조회

mysql> select * from member;
Empty set (0.00 sec)
 
반응형
저작자표시 비영리 변경금지

'MYSQL' 카테고리의 다른 글

[MYSQL][06] 외래키(Foreign Key, FK)의 참조 무결성  (0) 2024.11.25
[MYSQL][05] KEY  (0) 2024.11.24
[MYSQL][04] SELECT  (0) 2024.11.22
[MYSQL][03] CREATE : 회원 테이블 생성  (0) 2024.11.21
[MYSQL][02] 데이터베이스 및 테이블 생성 예제  (1) 2024.11.20

+ Recent posts

티스토리툴바