sql (4) 테이블 및 자료형

1. 테이블

• 테이블 생성 및 삭제

  CREATE TABLE people (
    person_id INT,
    person_name VARCHAR(10),
    age TINYINT,
    birthday DATE
  );
    
  DROP TABLE people;
  

• 테이블 생성 시 제약

  CREATE TABLE people (
    person_id INT AUTO_INCREMENT PRIMARY KEY,
    person_name VARCHAR(10) NOT NULL,
    nickname VARCHAR(10) UNIQUE NOT NULL,
    age TINYINT UNSIGNED,
    is_married TINYINT DEFAULT 0
  );
  

  AUTO_INCREMENT : 새 행 생성시마다 자동으로 1씩 증가

  PRIMARY KEY : 중복 입력 불가, NULL 불가. -> 테이블마다 하나씩만 가능하며 클러스터 인덱스 적용됨. 보통 행을 식별하기 위해 사용하기 때문에 id의 AUTO_INCREMENT와 함께 사용하는 경우가 많음

  UNIQUE : 중복 입력 불가. -> 논클러스터 인덱스 적용

  NOT NULL : NULL 불가

  UNSIGNED : 양수만

  DEFAULT 기본값 값 입력이 없을 시 기본값 사용

• 테이블 속성 변경

  -- 테이블명 변경
  ALTER TABLE people RENAME TO friends,
  -- 컬럼 자료형 변경
  CHANGE COLUMN person_id person_id TINYINT,
  -- 컬럼명 변경
  CHANGE COLUMN person_name person_nickname VARCHAR(10), 
  -- 컬럼 삭제
  DROP COLUMN birthday,
  -- 컬럼 추가
  ADD COLUMN is_married TINYINT AFTER age;
  

• 테이블 데이터 입력

  INSERT INTO people
    (person_id, person_name, age, birthday)
    VALUES (1, '홍길동', 21, '2000-01-31');
      
  -- 모든 컬럼에 값 넣을 때는 컬럼명들 생략 가능
  INSERT INTO people
    VALUES (2, '전우치', 18, '2003-05-12');
      
  -- 일부 컬럼에만 값 넣기 가능 (NOT NULL은 생략 불가)
  INSERT INTO people
    (person_id, person_name, birthday)
    VALUES (3, '임꺽정', '1995-11-04');
      
  -- 여러 행을 한 번에 입력 가능
  INSERT INTO people
    (person_id, person_name, age, birthday)
    VALUES 
      (4, '존 스미스', 30, '1991-03-01'),
      (5, '루피 D. 몽키', 15, '2006-12-07'),
      (6, '황비홍', 24, '1997-10-30');
  

2. 자료형

1) ## 숫자 자료형

• 정수

자료형	바이트	SIGNED	UNSIGNED
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32,768 ~ 32,767	0 ~ 65,535
MEDIUMINT	3	-8,388,608 ~ 8,388,607	0 ~ 16,777,215
INT	4	-2,147,483,648 ~ 2,147,483,647	0 ~ 4,294,967,295
BIGINT	8	-2^63 ~ 2^63 - 1	0 ~ 2^64 - 1

• 고정 소수점 - 부동 소수점 보다 좁은 범위지만, 정확한 수

자료형	설명	범위
DECIMAL( s, d )	실수 부분 총 자릿수( s ) & 소수 부분 자릿수 ( d )	s 최대 65

• 부동 소수점 - 넓은 범위, 고정 소수점 보다 오차가 생길 수 있음

자료형	표현 범위
FLOAT	-3.402…E+38 ~ -1.175…E-38 , 0 , 1.175…E-38 ~ 3.402…E+38
DOUBLE	-1.797…E+308 ~ -2.225E-308 , 0 , 2.225…E-308 ~ 1.797…E+308

1. 문자 자료형

   • 문자열 - 검색 시 CHAR 형태가 더 빠르기 때문에 적합한 자료형 선택이 필요할 수 있음

   자료형	설명	차지하는 바이트	최대 바이트
   CHAR( s )	고정 사이즈 (남는 글자 스페이스로 채움)	s (고정값)	255
   VARCHAR ( s )	가변 사이즈	실제 글자 수[최대 s] + 1 [글자수 정보]	65,535

   • 텍스트

   자료형	최대 바이트 크기
   TINYTEXT	255
   TEXT	65,535
   MEDIUMTEXT	16,777,215
   LONGTEXT	4,294,967,295

2. 시간 자료형

   자료형	설명	비고
   DATE	YYYY-MM-DD
   TIME	HHH:MI:SS	HHH: -838 ~ 838까지의 시간
   DATETIME	YYYY-MM-DD HH:MI:SS	입력된 시간을 그 값 자체로 저장
   TIMESTAMP	YYYY-MM-DD HH:MI:SS	MySQL이 설치된 컴퓨터의 시간대를 기준으로 저장

   • 시간 데이터를 가감없이 기록할 때 DATETIME
   • 시간 자동기록, 국제적인 서비스를 할 경우 TIMESTAMP 사용

3. 데이터 편집

• 데이터 삭제

-- 보통 조건을 달아서 삭제함. pk값을 많이 사용
DELETE FROM businesses
WHERE status = 'CLS';

-- 이렇게 조건을 안쓰면 테이블 데이터가 다 날아감.. 조심 조심
DELETE FROM businesses;

-- 이렇게 Delete로 삭제하면 테이블 초기화가 아니라 id가 어디까지 증가했는지는 테이블에 남아 있음

-- 테이블 초기화해서 auto_increment까지 초기화시키려면
TRUNCATE businesses;

• 데이터 변경

-- 조건에 해당하는 데이터 변경
UPDATE menus
SET menu_name = '삼선짜장'
WHERE menu_id = 12;

-- 여러 컬럼도 가능
UPDATE menus
SET 
  menu_name = '열정떡볶이',
  kilocalories = 492.78,
  price = 5000
WHERE 
  fk_business_id = 4
  AND menu_name = '국물떡볶이';

-- 마찬가지로 조건이 없으면 전체 데이터가 변경됨.. 조심 조심
UPDATE menus
SET menu_name = '획일화';

---

마지막 수정일시: 2022-12-14 14:44