[필기정리]Day55 - zerofill, DDL , primary key, view 등

Q1. 사용자 ID를 "SKY", 암호(PASSWORD) "SKY1234"로 생성하시오.
A.

create user sky identified by 'sky1234';

Q2. 사용자 "SKY"의 암호를 "SKY5678"로 변경하시오.

A.

alter user 'sky' identified with mysql_native_password by 'SKY5678';

# MySQL 5.6 이하 
set password for sky = password('sky5678');

 

Q3. 사용자 "SKY"에게 모든 데이터베이스를 관리할 수 있는 모든 권한을 부여하시오. (슈퍼 유저를 만드는 것!)
A.

grant all privileges on *. * to sky;

 

Q4. 다음과 같은 학생신상테이블(SINSANG)이 있다. 

조건

학번 : 정수2자리

이름 : 영수치 10자리

출생년도 : 영수치 4자리

성별 : 영수치 1자리

도시명 : 영수치 10자리

입학일자 : 날짜형

우편번호 : 영수치 7자리

전화번호 : 영수치 14자리

PRIMARY KEY : 학번

① 학생신상테이블을 생성하시오.

A.

create table sinsang 
( 
stu_no int(2), // 제로필 속성이 없기 때문에 2로 줘도 의미가 없다 
stu_name varchar(10), 
birth_year char(4), 
sex char(1), 
city varchar(10), 
ent_date date, 
postcode char(7), 
phone char(14), 
primary key (stu_no) 
); 

 

② 학생신상 테이블(SINSANG)로부터 도시명이 "서울"인 학생의 학번, 이름, 성별을

    학번 순으로 출력하는 SQL문을 쓰시오.

A.

select stu_no, stu_name, sex from sinsang where city='서울' order by stu_no;

③ 학생신상 테이블로부터 우편번호가 '546-121'인

   모든 학생의 우편번호를 '540-100'으로 변경하는 SQL문을 쓰시오.
A.

update sinsang set postcode ='540-100' where postcode = '546-121';

④ 학생신상 테이블의 형식에 맞추어 본인의 데이터를 생성하여 보시오.(INSERT 명령문 사용)
A.

insert into sinsang values(1, '홍길동', '1980', 'm', '한산도', now(), '546-121', '015');

// 참고
- 현재 사용자 확인

select user();

 

- 현재 선택 중인 Database 확인

select database();

 

- 화면 지우기

system cls // clear screen의 약자

 

// 참고 

#         // 한 줄 주석 
/* */    // 여러 줄 주석 

# zerofill

  테이블 생성 시 int(3) 등으로 괄호 옵션을 주는 경우, 
  실제 의미를 위해서는 zerofill이라는 옵션과 함께 사용되어야 한다. 
  그렇지 않은 경우에는 무의미하다.

  zerofill은 말 그대로 0을 채우라는 의미고,

  즉 괄호 안의 숫자만큼 빈 칸을 0으로 채우라는 의미다.

  zerofill은 보여지는 형식을 정의 하는 것이지 실제 저장되는 방식과는 무관하다.

  // 보여질 때 보기 좋게 0이 채워지는 것

 

  int형을 입력하면 자동적으로 int(11)이 된다. 
  기본값이 11인 이유는 int형의 최대값이 10자리이기 때문으로 보인다. 
  11자리로 해놓으면 최대값에 도달한다고 하더라도

  자릿수가 보기좋게 0부터 시작해서 정렬되기 때문으로 보인다.
  
ex) 테스트를 해서 한번 보자. 

create table test(no int(3) zerofill); 
insert into test values(1); 
select * from test;

 

# char와 varchar의 차이점

  문자열 char는 고정형 문자열이기 때문에

  char(20)일 때 2byte의 문자만 넣어도 20byte 만큼의 데이터를 잡게된다. 

 

  반면 varchar(variable + char)는 가변형 문자열이기 때문에

  데이터의 길이에 따라서 가변적으로 길이가 정해진다. 
  varchar(20)일 경우 입력한 크기만큼의 공간만 잡히게 되는데,

  2byte의 문자를 넣으면 2byte 만큼의 데이터만 잡게 된다.

  고정 사이즈인 char 타입은 추후에 연산할 필요가 없기 때문에

  검색속도 및 읽히는 속도가 다른 타입보다 월등히 빠르다. 
  사이즈가 고정되어 있을 때 char 타입을 사용하면 더 효율적으로 데이터를 관리할 수 있다. ex) 주민등록번호 

  예를 들어 아이디를 만드는데 데이터 베이스에 자료형이 char(100)으로 잡혀 있다면, 
  'sogood'(6byte)이라는 아이디를 만들 때 6byte를 잡는데 94byte의 데이터가 낭비된다. 
  따라서 상황에 따라 맞는 타입을 사용하면서 공간복잡도와 시간복잡도를 모두 고려해서 타입을 설정해야 한다.

# 테이블을 관리하는 데이터 정의언어(DDL : Data Definition Language) 명령문의 종류

명령문	설명
create table	테이블 생성
alter table	테이블 및 열(column) 변경
drop table	테이블 삭제
rename	테이블 이름 변경

다음은 학사 데이터베이스에서 POST 테이블을 생성하는 CREATE TABLE 명령문의 예제이다.

create table post( 
post_no varchar(6) Not null,         // 우편번호 
post_dong char(30) Not null,         // 동이름 
post_address char(60) Not null,      // 주소 
ddd char(4),                         // DDD 전화 지역번호 
primary key (post_no)                // 유일해야 한다. 
); 

기본 키(primary key)는 값이 항상 유일한 열 또는 열의 집합으로 알려져 있다.
따라서 기본키로 사용되는 열에는 NULL 값이 허용되지 않는다.

 
위의 구문에서는 post_no열이 post 테이블의 기본 키이다. 
기본 키로 정의된 열은 not null로 정의되어야 한다.

 
그리고 마지막 열에 있는 열의 정의와 primary key 단어 사이에는 구분을 위한 콤마를 사용한다.

 

테이블에서 여러 개의 열을 사용하여 기본 키를 정의할 수 있다.

ex) 하나의 필드로 유일한 값이 될 수 없는 경우
이러한 기본 키를 조합 기본 키(composite primary key)라 한다.

#primary key (기본 키)

Q1. 과목명, 교수, 과목인원, 과목 개설 일자를 기록하기 위한 diplomas 테이블을 생성하라.

      course, professor, end_date 열을 사용하여 조합 기본 키를 생성한다.
A.

create table diplomas 
( 
course varchar(20) not null, 
professor varchar(10) not null, 
cou_num int(2), 
end_date date not null, 
primary key(course, professor, end_date) 
);

 

 

3개의 열을 사용하여 기본 키를 정의함으로써 학생은 지정된 일자에 한 과목에 대하여 취득할 수 있다.
그리고 not null 무결성 규칙은 모든 열에 관련되어 정의되어 있다.

어떤 열 또는 열의 그룹을 기본키로 사용할 수 있지만 지켜야 할 많은 규칙이 있다. 
이러한 규칙 중 몇 가지는 관계 모델의 이론을 근거로 하고 있으나, 그 외의 규칙들은 SQL에 근거를 두고 있다. 

 

primary key 정의 규칙

각 테이블에는 오직 하나의 기본 키만 정의할 수 있다.

관계형 모델에서는 각 테이블에 하나의 기본 키를 정의하도록 하고 있다.

sql에서는 이러한 것을 따르도록 하고 있지 않기 때문에 기본 키 없이 테이블을 생성할 수 있으나,  

각 기본 테이블에 대하여 하나의 기본키를 지정하는 것이 바람직하다.  // 문법적 강제성은 없음

기본키로 테이블을 갱신할 때마다 유일성이 반영되기 때문에 중복이 일어나지 않는다.

반대로 기본키를 사용하지 않는 이유는 테이블에 동일한 테이터를 갖는 행을 저장할 수 있도록 하기 위해서지만,
이 경우 아래와 같은 문제점이 발생할 수 있다.

 
2개의 행을 서로 구분할 수 없으므로 SELECT 명령문에서 동일한 조건을 만족하게 되며, 
UPDATE 명령문에서 2개의 행은 함께 갱신되기 때문에 문제가 발생한다. 
이러한 상황에서는 데이터가 잘못 될 가능성이 높다.

- 유일성 규칙(uniqueness rule)

  테이블에서 서로 다른 행은 동일한 값을 기본 키로 가질 수 없다. 

  ex) student 테이블에서 우편번호코드는 많은 학생이 같은 도시의 동에 살고 있기 때문에 후보 키로 지정할 수 없다.

- 최소화 규칙(minimal rule)

  기본 키로 사용된 열의 일부가 삭제될 가능성이 있고, 

  기본 키가 유일성 규칙을 아직 만족해야 한다면 기본 키는 부정확하게 된다. 
  다시 말하면 기본 키는 불필요하게 많은 열로 구성하지 않아야 한다는 것이다. 

  ex) stu_no과 stu_name을 기본 키로 정의한 경우
  우리는 학생의 학번이 유일하다는 것을 이미 알고 있다.
  따라서 이러한 경우에 기본 키는 필요 이상의 열을 가지고 있으므로 최소화 규칙을 만족하지 않는다. 

  //  굳이 stu_name까지 기본키일 필요 없다는 것

- 기본 키 열 목록에서 열의 이름은 한 번만 나타나야 한다.
- 기본 키에 포함되는 열의 집단은 null 값을 가지지 않아야 한다. (not null) 

Q2. diplomas 테이블이 다음과 같은 구조와 내용을 가지고 있을 때, sex 열을 추가 후 확인하시오.

insert into diplomas values('웹프로그래밍', '공자', 2, now()); 
insert into diplomas values('웹프로그래밍', '맹자', 3, now());

A.
새로운 열인 성별(sex)을 테이블에 포함할 수 있도록 확장시켜보면 다음과 같다.

alter table diplomas add sex char(2);
select * from diplomas;

 

그러면 diplomas 테이블은 sex라는 열을 char(2) 데이터형의 크기로 추가되고,

sql에서 열에 값을 채울 때 유일하게 가능한 값은 null 이다.

 // 기존에 없는 내용을 추가하는 것이기 때문에 null 값만 가질 수 있다. 

# 테이블 구조의 변경 

alter table 명령으로 수행되는 기능

테이블에 새로운 열을 추가한다.

기존에 존재하는 열을 삭제한다.

자료형의 길이를 변경한다.

기존에 존재하는 열의 자료형을 특별한 조건하에서 변경할 수 있다.

기본 키와 같은 새로운 무결성 규칙을 추가할 수 있다.

기존에 존재하는 무결성 규칙을 삭제할 수 있다.

자료형의 길이는 더 커지거나 줄어들 수 있다. 
길이의 감소는 테이블이 공백이거나 열이 null 값만을 보유하고 있는 경우에만 허용된다.

Q3. diplomas 테이블의 sex 열의 길이를 2에서 4로 증가시켜라.

A.

alter table diplomas modify sex char(4);

열에 있는 모든 값이 새로운 자료형의 규칙을 만족한다면 열은 새로운 자료형을 받아들인다.

Q4. diplomas 테이블에서 sex의 자료형을 char에서 int로 변경하라.

A. 

alter table diplomas modify sex int;

★자료형의 변경에 있어서 일반적인 규칙은 열에 있는 값이 새로운 자료형으로 변경할 수 있어야 한다. 
그래서 위의 예제는 각 char 값을 int 값으로 변경할 수 있다.

테이블에 기본 키로 정의되는 열이 중복된 값을 가지고 있지 않을 때 
즉, 기본 키가 정의되어 있지 않으면 기본 키의 추가가 허용된다. 

alter table 테이블 이름 add primary key(professor);

# 테이블 복사
   create table 문과 as 키워드를 이용하여 sub query로 새로운 테이블을 복사할 수 있다. 
  테이블 복사의 형식은 다음과 같다.

create table new_table_name [column_name, ...] as select_statement

동일한 사용자인 경우에는 새롭게 작성될 테이블명(new_table_name)을 입력하고, 
select_statement는 sub query로 유효한 select 문이다. 
as 키워드는 column_name이 명시될 경우 

이 칼럼들의 수는 as 키워드 다음의 sub query가 반환하는 컬럼의 수와 같아야 한다.

Q1. student 테이블과 동일한 테이블 구조와 데이터를 가지고 있는 student1 테이블을 복사하라.

A.

create table student1 as select * from student;

// student 테이블의 구조와 데이터가 동일하게 복사됨

Q2. post 테이블에서 post_no와 post_address로 구성된 신규 post1 테이블을 생성하라.

A. 

create table post1 as select post_no, post_address from post;

// post 테이블에서 post_no, post_adress만 가지고 만드는 것

Q3. post1 테이블의 모든 열을 조회하라.

A.

select * from post1;

Q4. 새롭게 생성된 "post1" 테이블의 데이터 유형을 알아보라.

A.

desc post1;

# 테이블 이름 변경 (rename)
   테이블뿐만 아니라 뷰, 시노님, 시퀀스 등의 mysql 객체의 이름을 바꿀 수 있다. 
   rename 명령문의 형식은 다음과 같다.

rename oldname to newname;

Q. "post1" 테이블을 "test_post" 테이블명으로 변경하라.

A.

alter table post1 rename test_post;

# 테이블과 데이터 사전
   MySQL은 데이터베이스와 작업에 대한 정보를 데이터 사전이라는 테이블 집합에 모아두었고, 
   이러한 테이블의 소유자는 "root" 사용자이다.

- 테이블 및 데이터 사전 정보 보기

use information_schema; 
show tables; 

use mysql; 
show tables; 

desc columns; 

desc user;

Q. 사용자 "root" user, password, 파일권한(file_priv)을 출력하라.

A.

# MySQL 5.7 이상 
select user, authentication_string, file_priv from user where user = 'root';

# MySQL 5.6이하
select user, password, file_priv from user where user = 'root';

# view 
  view는 뷰의 질의에 대한 텍스트를 조회할 수 있다.

뷰(View)란 무엇인가?

1. 뷰는 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해      하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블이다.

2. 뷰는 저장장치 내에 물리적으로 존재하지 않지만 사용자에게 있는 것처럼 간주된다.

3. 뷰는 데이터 보정작업, 처리과정 시험 등 임시적인 작업을 위한 용도로 활용된다.

4. 뷰는 조인문의 사용 최소화로 사용상의 편의성을 최대화 한다.

// Tip : view vs create table ~ as
- view : 실제로 존재하지 않지만 해당 테이블과 동일한 내용
- create table ~ as : 실제로 복사를 하여 존재하는 테이블
 

뷰(View)의 특징

1. 뷰는 기본테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용하며,      조작도 기본 테이블과 거의 같다.

2. 뷰는 가상 테이블이기 때문에 물리적으로 구현되어 있지 않다.

3. 데이터의 논리적 독립성을 제공할 수 있다.

4. 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해진다.

5. 뷰를 통해서만 데이터에 접근하게 하면,     뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있다. (보안성)

6. 기본 테이블의 기본키를 포함한 속성(열) 집합으로 뷰를 구성해야지만 삽입, 삭제, 갱신, 연산이 가능하다.

7. 일단 정의된 뷰는 다른 뷰의 정의에 기초가 될 수 있다.

8. 뷰가 정의된 기본 테이블이나 뷰를 삭제하면 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동으로 삭제된다.

뷰(View) 사용 시 장점

1. 논리적 데이터 독립성을 제공한다.

2. 동일 데이터에 대해 동시에 여러사용자의 상이한 응용이나 요구를 지원해 준다.

3. 사용자의 데이터관리를 간단하게 해준다.

4. 접근 제어를 통한 자동 보안이 제공된다.

뷰(View) 사용 시 단점

1. 독립적인 인덱스를 가질 수 없다.

2. ALTER VIEW문을 사용할 수 없다.      즉, 뷰의 정의를 변경할 수 없다.

3. 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신, 연산에 제약이 따른다.

- 뷰 정의문

CREATE VIEW 뷰이름[(속성이름[,속성이름])]AS SELECT문;

ex) 고객 테이블에서 주소가 서울시인 고객들의 성명과 전화번호를 서울고객이라는 뷰로 만들어라.

CREATE VIEW 서울고객(성명, 전화번호) 
AS SELECT 성명, 전화번호 
FROM 고객 
WHERE 주소 = '서울시';

- 뷰 삭제문

DROP VIEW 뷰이름 RESTRICT or CASCADE;

 

※ 뷰는 ALTER문을 사용하여 변경할 수 없으므로 필요한 경우는 삭제한 후 재생성한다.
ex) 서울고객이라는 뷰를 삭제하라. 

DROP VIEW 서울고객 RESTRICT;

// RESTRICT : 뷰를 다른곳에서 참조하고 있으면 삭제가 취소된다.
// CASCADE : 뷰를 참조하는 다른 뷰나 제약 조건까지 모두 삭제된다.

- view 문제

Q1. student 테이블에 있는 모든 데이터를 가지고 studentInfo1이라는 view를 만든다.
A.

CREATE VIEW studentInfo1 AS SELECT * FROM student;

Q2. studentInfo1 view에 있는 모든 데이터를 조회한다.
A.

select * from studentInfo1;

Q3. student 테이블에 있는 stu_no, stu_name을 가지고 studentInfo2라는 view를 만든다.
A.

CREATE VIEW studentInfo2 AS SELECT stu_no, stu_name from student;

Q4. studentInfo2 view에 있는 모든 데이터를 조회한다.
A.

select * from studentInfo2;

Q5. student 테이블에 있는 stu_no, stu_name을

     각각 '학번', '이름' 이라는 컬럼명으로 studentInfo3라는 view를 만든다.
A.

CREATE VIEW studentInfo3 (학번, 이름) AS SELECT stu_no, stu_name from student;

Q6. studentInfo3 view에 있는 모든 데이터를 조회한다.
A.

select * from studentInfo3;

Q7. studentInfo1 view를 삭제한다.

A.

DROP VIEW studentInfo1;

Q8. studentInfo2, studentInfo3 view를 삭제한다.
A.

DROP VIEW studentInfo2, studentInfo3;

 

- 뷰 목록 조회

방법①

SHOW FULL TABLES IN 디비명 WHERE TABLE_TYPE LIKE 'VIEW';

// 모든 테이블에서 view가 있으면 보여줘라!(검색)
ex)

SHOW FULL TABLES IN haksa_database WHERE TABLE_TYPE LIKE 'VIEW';

방법②

SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'haksa_database';

- 쿼리결과가 길때

SELECT * FROM TABLE\G // 데이터를 줄 단위로 볼 수 있음

ex)

SELECT * FROM STUDENT\G;