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 |