[웹 서비스 개발] MySQL Command Exercise

24-2 15주차 1차시

[1] database 생성

create database inventory;

[2] table 생성

use inventory;

create table items(
	IDX int not null,
    NAME varchar(20) not null,
    LOCATION varchar(20) not null,
    QUANTITY int,
    PRIMARY KEY (IDX)
);

1) Table 복사하여 생성하기

특정 테이블의 일부 열을 선택하여 데이터를 복사하면서 새로운 테이블을 생성할 수 있다. 이 과정에서 새 테이블은 원본 테이블의 선택된 열과 동일한 데이터 형식을 가지며, 기본적으로 제약 조건(Primary Key, Foreign Key 등)은 복사되지 않는다.

CREATE TABLE TestTable AS 
SELECT customername, contactname 
FROM customers;

• 사용 예시
  TestTable이라는 새로운 테이블을 생성하고, customers 테이블에서 customername과 contactname 열의 데이터를 복사한다.
• 조건 추가 가능
  복사 시 특정 조건을 추가하려면 WHERE 절을 사용할 수 있다.

  CREATE TABLE TestTable AS 
  SELECT customername, contactname 
  FROM customers
  WHERE customername LIKE 'A%';

2) Table 삭제하기

테이블 자체를 삭제할 때 사용된다. 테이블에 저장된 데이터뿐 아니라 테이블의 구조와 관련된 모든 메타데이터(인덱스, 제약 조건 등)도 제거된다. 삭제 후에는 복구가 불가능하므로 주의가 필요하다.

DROP TABLE Shippers;

• 사용 예시
  Shippers라는 테이블을 삭제한다.
• 주의사항
  이 명령어는 삭제된 테이블을 되돌릴 수 없으므로 신중히 사용해야 한다.

3) Table 데이터만 삭제하기 (with TRUNCATE)

테이블의 데이터를 빠르고 효율적으로 삭제한다. 테이블의 구조와 제약 조건은 유지되며, AUTO_INCREMENT가 설정된 경우 초기화된다.

TRUNCATE TABLE table_name;

• 사용 예시
  table_name 테이블의 모든 데이터를 삭제한다.
• 특징
  • DELETE 명령어와 달리 TRUNCATE는 조건을 지정할 수 없으며, 테이블의 모든 데이터를 삭제한다.
  • 속도가 빠르고, 로그 기록이 최소화된다.
• 주의사항
  외래 키 제약 조건이 설정된 테이블에서는 사용할 수 없으며, ROLLBACK이 지원되지 않는 경우가 많다.

[3-1] 데이터 추가하기

insert into items(IDX, NAME, LOCATION, QUANTITY) VALUES (2, 'Kevin', 'Seoul', 5);

1) 한 문장으로 여러 데이터 추가하기

INSERT INTO `itmes`(`IDX`, `NAME`, `LOCATION`, `QUANTITY`) VALUES (1, 'Ha', 'Siheung', 9),  (13, 'Min', 'Mapo', 9);

[3-2] 데이터 확인하기

select * from items;

1) 특정 컬럼만 확인하기

SELECT NAME from items WHERE IDX = 2;

[3-3] 데이터 수정하기

UPDATE items SET QUANTITY = 6 WHERE IDX = 2;

[3-4] 데이터 삭제하기

delete from items where name = "park";

[4] Table 속성 출력하기

DESCRIBE items;

[5] 테이블 수정 (Alter Table)

• ALTER TABLE 문은 기존 테이블에 열을 추가하거나 삭제하거나 수정할 때 사용된다.
• 또한, 기존 테이블에 다양한 제약 조건을 추가하거나 삭제하는 데도 사용된다.

1) ADD

ALTER TABLE {테이블명} ADD {컬럼명} {데이터타입}

ALTER TABLE items ADD CREATEDAD DATE;

2) MODIFY

ALTER TABLE {테이블명} MODIFY COLUMN {컬럼명} {데이터타입}

ALTER TABLE items MODIFY COLUMN CREATEDAD VARCHAR(20);

3) DROP

ALTER TABLE {테이블명} DROP COLUMN {컬럼명}

ALTER TABLE items DROP COLUMN CREATEDAD;

4) CHANGE

ALTER TABLE {테이블명} change {컬럼명} {새로운 컬럼명} {데이터타입} {또 다른 컬럼 속성 추가 가능};

ALTER TABLE items change IDX IDX int AUTO_INCREMENT;

insert into items(NAME, LOCATION, QUANTITY) VALUES ('Kevin', 'Seoul', 5);

: auto_increment로 되었으므로 IDX는 insert 시 제외된다.

5) MODIFY와 CHANGE의 차이점

기능	CHANGE	MODIFY
열 이름 변경	가능 (old_column_name → new_column_name)	불가능 (열 이름은 유지됨)
데이터 타입 변경	가능	가능
주요 사용 목적	열 이름과 데이터 타입을 동시에 변경할 때	데이터 타입이나 속성만 수정할 때

• CHANGE: 열 이름과 속성을 모두 변경.
• MODIFY: 열 이름은 유지하고 속성만 변경.

[6] replace

: 지정된 테이블에 데이터를 삽입하거나, 동일한 기본 키 또는 고유 키를 가진 레코드가 이미 존재하면 해당 레코드를 삭제하고 새 레코드를 삽입한다.

• 이는 INSERT INTO와 비슷하지만, 중복된 키가 있을 때 삭제 후 삽입이 이루어진다.

replace into {테이블명} VALUES (값, 값, 값, 값);

replace into items VALUES (2, 'Kevin', 'Incheon', 5);

[7] Primary key

1) Table 생성 시 지정하기

CREATE TABLE Users(
	ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255), 
    Age int,
    PRIMARY KEY (ID)
);

2) Primary Key 삭제하기

ALTER TABLE Users DROP PRIMARY KEY;

3) Constraint

: CONSTRAINT는 데이터베이스에서 특정 규칙이나 조건을 정의하여 데이터의 무결성을 유지하는 데 사용되는 SQL 명령어이다.

CREATE TABLE Users2(
	ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255), 
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY(ID, LastName)
);

특징	Users 테이블	Users2 테이블
기본 키 구성	단일 열 (ID)	복합 열 (ID, LastName)
유일성 보장	ID로만 유일성 보장	ID와 LastName의 조합으로 유일성 보장
중복 가능성	ID가 동일하면 중복 불가	같은 ID로 다른 LastName을 가질 수 있음

[8] FOREIGN key

0) 사전 세팅

1. Persons Table 만들기

CREATE TABLE Persons(
    PersonID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstNAme varchar(255) NOT NULL,
    Age int NOT NULL,
    PRIMARY KEY (PersonID)
);	

2. Persons Table 데이터 추가하기

INSERT INTO Persons (LastName, FirstName, Age) VALUES 
('Smith', 'John', 30),
('Doe', 'Jane', 25),
('Johnson', 'Emily', 22),
('Williams', 'Michael', 35),
('Brown', 'Linda', 28);

1) Table 생성 시 지정하기

CREATE TABLE Orders(
	OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY(PersonID) REFERENCES Persons(PersonID)
);

2) ALTER로 지정하기

ALTER DROP Orders DROP FOREGIN KEY {FK명}

ALTER TABLE Orders ADD FOREIGN KEY PK_PersonID(PersonID) REFERENCES Persons(PersonID);

---