IT 공부/MySQL

테이블 수정, 인덱스

toraa 2022. 7. 12. 16:35

테이블 수정

ALTER TABLE 테이블명
  ADD COLUMN 컬럼명 데이터타입 [NOT NULL|NULL][DAFAULT값] AFTER 컬럼명,
  DROP COLUMN 컬럼명,
  CHANGE COLUMN 수정전컬럼명 수정후컬럼명 데이터타입 [NOT NULL|NULL][DEFAULT값]

# 강의 테이블에 깁본값을 3으로 갖는 int 데이터타입의 '이수학점' 칼럼을 추가하시오. 강의번호 뒤에 추가하시오.
use test;
	alter table 강의 
    add column 이수학점 int NULL default 3 after 강의번호 ;
    
# 강의 테이블의 '이수학점' 컬럼의 이름을 '수강학점'으로 변경하시오.
	alter table 강의 
	change column 이수학점 수강학점 char(4);
    
# '수강학점' 컬럼을 삭제하시오
	alter table 강의 
    DROP COLUMN 수강학점;
    
# '학생' 테이블에서 '전화번호' 컬럼명을 '연락처'로 변경
	alter table 학생 
    change column 전화번호 연락처 varchar(10);
    
# '학생' 테이블에서 '학생이름' 컬럼이 '강의' 테이블의 '강의번호'를 참조하도록 외래키를 추가하시오

-- 1) 외래키 일반적으로 추가 경우
	alter table 학생 
    add foreign key(학생이름) references 강의(강의번호); 
    
-- 2) 외래키를 제약조건 이름으로 추가한 경우
	alter table 학생 
    add constraint t foreign key(학생이름) references 강의(강의번호); 
  
 -- 1)방법으로 추가했을 경우, 자동부여된 제약조건이름을 찾아서 외래키를 삭제하고 그다음 열을 삭제
	alter table 학생
	drop foreign key 학생_ibfk_2;
    
 -- 2)방법으로 추가했을 경우, 명시한 제약조건 이름 t로 외래키를 삭제하고 그 다음 열을 삭제
	alter table 학생
	drop foreign key t;
   
   alter table 학생
   drop column 학생이름;
 
    desc 강의;
    
#강의1 테이블을 생성하시오.
/*
	. 모든 컬럼은 null을 허용하지 않는다.
    . 강의번호, 강의이름, 강의시간, 인원제한, 교수번호의 컬럼을 갖는다.
    . 강의번호는 기본키이고, 입력하지 않아도 자동으로 1씩 증가된다.
    . 강의이름은 반드시 값을 가져야하고, 중복된 이름을 가져서는 안된다.
    . 교수번호는 길이가 5인 고정문자열을 갖는다.
    . 강의시간과 강의이름은 최대 문자열이 20인 크기를 갖는다.
*/    
create table 강의1(
	강의번호 int(5) auto_increment primary key,
    강의이름 varchar(20) not null unique,
    강의시간 varchar(20) not null,
    인원제한 int default 50, 
    교수번호 char(5) not null
);
desc 강의1;
insert into 강의1(강의번호,강의이름,강의시간,인원제한,교수번호)
values (019,'데이터베이스','수요일 2~3교시',80,'c12');
insert into 강의1(강의번호,강의이름,강의시간,인원제한,교수번호)
values (041,'컴파일러','수요일 4~6교시',50,'c29');

인덱스(Index)
    . 찾고자 하는 데이터를 검색할 때, 모든 레코드를 검색한다. 이것을 특정 범위내에서 검색하도록 색인처리 하는 것.
    . 데이터베이스 분야에서 테이블에 대한 검색 속도를 높이기 위해 사용되는 자료구조이다.
    . 관계형 데이터베이스에서 인덱스는 테이블 부분에 대한 또 하나의 사본이다. 

      인덱스가 많아지면 오히려 오버헤드가 발생된다.
    .장점
        - SQL문을 변경하지 않아도 성능 개선을 할 수 있다.
        - 그러면서도 테이블의 데이터에 영향을 주지 않는다.alter

    . 인덱스 생성
        - 자동생성 : 기본키,외래키,unique는 자동 생성된다.
        - 명시적 지정

           create index 인덱스이름 on 테이블(컬럼,컬럼,..);


. 인덱스 조회
   show index from 테이블 
. 인덱스 삭제
   drop index 인덱스명 on 테이블
            
. 인덱스 대상선정 참조
            . 크기가 큰 테이블
            . Cardinarlity가 높은 열을 지정한다. - 주민번호 > 남녀미상

#인덱스 조회
show index from 강의1;

#인덱스 생성
create index 강의명조회 on 강의1(강의이름);

explain select * from 강의1 where 교수번호='c29';	-- 일반컬럼은 전체검색
explain select * from 강의1 where 강의이름='컴파일러'; -- 인덱스로 검색

#인댁스 삭제
drop index 강의명조회 on 강의1;    

/*	1) 생성 : '회원'테이블
		. 아이디, 가변문자열 20글자, 기본키
        . 이름, 가변문자열 20글자, 필수입력
        . 암호, 가변문자열 20글자, 필수입력
        . 전화번호, 가변문자열 20글자
        . 주소, 가변문자열 20글자, 필수입력
    
    2) 테이블에 포인트 int 디폴트 20인 칼럼추가
    3) 이름 칼럼을 testindex로 인덱스를 생성
    4) 테이블에 생성되어 있는 인덱스 조회
    5) testindex제거
    6) 외래키부여 - 회원의 이름이 강의1의 이름을 참조
    7) 회원이름열 삭제
    8) 회원테이블 삭제       
*/
create table 회원(
아이디 varchar(20) primary key,
이름 varchar(20) not null,
암호 varchar(20) not null,
전화번호 varchar(20),
주소 varchar(20) not null
);

create index testindex on 회원(이름);

show index from 회원;

drop index testindex on 회원;

alter table 회원 
    add foreign key(이름) references 강의1(강의이름); 
    
alter table 회원 
drop column 이름;

drop table 회원;

DML - 데이터 조작언어
  . insert 레코드 삽입
      INSERT INTO 테이블(컬럼명, 컬럼명,..); 전체컬럼 입력
      INSERT INTO 테이블(컬럼명,컬럼명,..) VALUES(값,값,..); 선택적 입력
  . update 수정
       UPDATE 테이블 SET 컬럼명 = 값, 컬럼명 = 값,.. ; where 조건;
       UPDATE 테이블 SET 컬럼명 = 수식, 컬럼명 = 값,.. ; where 조건;
       UPDATE 테이블 SET 컬럼명 = (select 컬럼명 from 테이블) ; where 조건;
            
   . select 검색
   . delete 삭제
         delete from 테이블명;

use myschool;
show tables;

desc 학생;
insert into 학생(학생번호,소속학과,학생이름,학년,성별,나이,전화번호,주소)
	values('201258043','국어국문학과','김건부',3,'남',24,'010-1424-7473','부산시');
    insert into 학생(학생번호,소속학과,학생이름,학년,성별,나이,전화번호,주소)
	values('201311001','컴퓨터과학과','서대길',1,'남',21,'010-4293-7473','광주시');
    insert into 학생(학생번호,소속학과,학생이름,학년,성별,나이,전화번호,주소)
	values('201323004','법학과','배수지',2,'여',24,'010-2345-5462','서울시');
    insert into 학생(학생번호,소속학과,학생이름,학년,성별,나이,전화번호,주소)
	values('201409081','경영학과','서강준',4,'남',35,'010-9174-4126','대전시');
    insert into 학생(학생번호,소속학과,학생이름,학년,성별,나이,전화번호,주소)
	values('201426005','컴퓨터과학과','장희령',1,'여',31,'010-8691-9520','부산시');
        insert into 학생(학생번호,소속학과,학생이름,학년,성별,나이,전화번호,주소)
	values('201426007','컴퓨터과학과','장하권',2,'남',27,'010-5486-1354','서울시');
    insert into 학생(학생번호,소속학과,학생이름,학년,성별,나이,전화번호,주소)
	values('201434027','법학과','이지은',2,'여',29,'010-7812-4261','서울시');
    insert into 학생(학생번호,소속학과,학생이름,학년,성별,나이,전화번호,주소)
	values('201481096','통계학과','김광희',3,'남',28,'010-9284-4566','서울시');   
    
select * from 학생;

/*
	'학생' 테이블에 학번 201483327, 소속학과 유아교육과, 이름 부승관,
    1학년, 남학생, 25세, 전화번호는 010-9927-4412, 주소는 제주시
    학생정보를 추가하여 입력하시오.
*/

insert into 학생(학생번호,소속학과,학생이름,학년,성별,나이,전화번호,주소)
 values('201483327','유아교육과','부승관',1,'남',28,'010-9927-4412','제주시');   
 
insert into 교수(교수번호,소속학과,교수이름,직위,전화번호,연구실위치,주소,입사일,퇴사일)
	values('C012','컴퓨터과학과','강동원','부교수','010-8561-1354','서울종로대학','서울시종로구','2004-03-21',null);
    insert into 교수(교수번호,소속학과,교수이름,직위,전화번호,연구실위치,주소,입사일,퇴사일)
	values('C017','컴퓨터과학과','주지훈','정교수','010-5461-5846','서울강남대학','서울시강남구','1984-02-28','2014-08-30');
    insert into 교수(교수번호,소속학과,교수이름,직위,전화번호,연구실위치,주소,입사일,퇴사일)
	values('C029','컴퓨터과학과','이도현','정교수','010-6487-9134','전북대학','전주시완산구','1994-06-30',null);
    insert into 교수(교수번호,소속학과,교수이름,직위,전화번호,연구실위치,주소,입사일,퇴사일)
	values('K041','국문학과','차은우','정교수','010-6851-5149','서울구로대학','서울시구로구','1991-01-24',null);
    insert into 교수(교수번호,소속학과,교수이름,직위,전화번호,연구실위치,주소,입사일,퇴사일)
	values('LA17','법학과','이주연','부교수','010-1658-5433','인천대학','인천시남동구','2006-08-30',null);
    insert into 교수(교수번호,소속학과,교수이름,직위,전화번호,연구실위치,주소,입사일,퇴사일)
	values('MN23','경영학과','최수빈','조교수','010-3548-4489','강원대학','춘천시효자구','2011-02-03',null);
    insert into 교수(교수번호,소속학과,교수이름,직위,전화번호,연구실위치,주소,입사일,퇴사일)
	values('SS07','통계학과','전정국','조교수','010-1905-9994','제주대학','제주시노형동','2011-03-01',null);
    
select * from 교수;

  insert into 교수(교수번호,소속학과,교수이름,직위,전화번호,연구실위치,주소,입사일,퇴사일)
	values('C007','컴퓨터과학과','조슈아','부교수','010-1905-9994', '서울강남대학','서울시강남구','2013-05-06',null);    
        
    insert into 강의(강의번호,강의이름,강의실,강의시간,인원제한,이수구분,교수번호,선수과목)
	values('C019','데이터베이스','본관 123호','수요일 2~3교시','80', '전공필수','C012','C021');
    insert into 강의(강의번호,강의이름,강의실,강의시간,인원제한,이수구분,교수번호,선수과목)
	values('C021','자료구조','신관 492호','월요일 1~2교시', 60, '전공필수','C012',null);
    insert into 강의(강의번호,강의이름,강의실,강의시간,인원제한,이수구분,교수번호,선수과목)
	values('C041','컴파일러','본관 232호','수요일 4~6교시','50', '전공선택','C029','C021');
    insert into 강의(강의번호,강의이름,강의실,강의시간,인원제한,이수구분,교수번호,선수과목)
	values('K014','현대시론','구관 009호','화요일 6교시','100', '교양필수','K041',null);
    insert into 강의(강의번호,강의이름,강의실,강의시간,인원제한,이수구분,교수번호,선수과목)
	values('K031','한국어 의미의 이해','강당','목요일 6~7교시','200', '교양필수','K041','K014');
    insert into 강의(강의번호,강의이름,강의실,강의시간,인원제한,이수구분,교수번호,선수과목)
	values('LA01','형법','신관 333호','월요일 7~9교시','90', '전공필수','LA17',null);
    insert into 강의(강의번호,강의이름,강의실,강의시간,인원제한,이수구분,교수번호,선수과목)
	values('MN09','마케팅원론','본관 042호','금요일 4~5교시','80', '전공필수','MN23',null);
	
    select * from 강의;

desc 강의;
desc 교수;

alter table 강의 drop foreign key 강의_ibfk_1;
alter table 강의 drop 선수과목;
alter table 강의 add column 선수과목 char(4);
alter table 강의 add foreign key(선수과목) references 강의(강의번호);
alter table 교수 add column 퇴사일 datetime;
alter table 수강신청 drop foreign key fk_강의_has_학생_학생1;
alter table 수강신청 drop foreign key fk_강의_has_학생_강의1;
# 수강신청 테이블에 강의번호 C019 학생번호 201426005, 신청시각 2014-03-02 10:50:00
# 			   강의번호 K014 학생번호 20140981 , 신청시각 2014-03-02 9:50:00


insert into 수강신청(강의번호,학생번호,신청시각)
values('C0109','201426005','2014-03-02 10:50:00');
insert into 수강신청(강의번호,학생번호,신청시각)
values('K014','20140981','2014-03-02 9:50:00');
desc 수강신청;

create table 졸업예정자(
	학생번호 char(9) not null primary key,
    소속학과 varchar(30),
    학생이름 char(10) not null,
    학년 int,
    성별 char(4),
    나이 int,
    전화번호 char(13) not null,
    주소 varchar(50)
);

insert into 졸업예정자 select * from 학생 where 학년=4;
select * from 졸업예정자;

desc 학생;

select * from 강의;
update 강의 set 인원제한 = 100;

#학생테이블에서 학생번호 201434027인 학생의 주소를 서울시 영등포구로 변경하시오.
#학생, 통계학과의 김광희님을 남자에서 여자로 변경하시오.
#학생, 박원장님의 소속학과를 유아교육학과로 변경하시오.
#강의, 자료구조(C021)의 강의번호를 C020으로 변경하시오. <- 수강신청
# 참조하고 있을 경우, 새로운 C020을 생성해서 내용까지 복사하고, 참조하는데서 참조필드도 변경하고,
#				 기존에 있는 C021을 삭제

select * from 학생;
select * from 강의;
select * from 수강신청;

update 학생 set 주소 ='서울시 영등포구' where 학생번호='201434027';
update 학생 set 성별 ='여자' where 학생이름='김광희';
update 학생 set 소속학과='유아교육학과' where 학생이름='박원장';
update 강의 set 강의번호='C020' where 강의이름='자료구조';

insert 강의(강의번호,강의이름,강의실,강의시간,인원제한,이수구분,교수번호,선수과목)
values('C022','자료구조','신관 492호','월요일 1~2교시', 60,'전공필수','C012',null);
update 강의 set 선수과목 = 'C022'where 강의번호 = 'C0129';
alter table 강의 drop foreign key 강의_ibfk_1;
delete from 강의 where 강의번호='C022';


insert into 수강신청(강의번호,학생번호,신청시각) values('K014','201258047','2014-02-28 05:32:35');
insert into 수강신청(강의번호,학생번호,신청시각) values('C021','201311001','2014-02-27 09:41:37');
insert into 수강신청(강의번호,학생번호,신청시각) values('K031','201323004','2014-02-26 12:14:39');
insert into 수강신청(강의번호,학생번호,신청시각) values('LA01','201409081','2014-02-27 16:34:11');
insert into 수강신청(강의번호,학생번호,신청시각) values('MN09','201426005','2014-03-01 08:51:55');
insert into 수강신청(강의번호,학생번호,신청시각) values('C019','201426005','2014-03-02 19:44:32');
insert into 수강신청(강의번호,학생번호,신청시각) values('K014','201434027','2014-02-28 09:37:22');
insert into 수강신청(강의번호,학생번호,신청시각) values('C021','201434027','2014-03-03 21:15:53');
insert into 수강신청(강의번호,학생번호,신청시각) values('LA01','201481096','2014-02-28 20:59:12');
insert into 수강신청(강의번호,학생번호,신청시각) values('C021','201481096','2014-03-01 18:24:56');

# 수강신청 시각이 2014년 2월 28일 이후인 학생은 삭제하시오.
delete from 수강신청 where 신청시각 > '2014-02-28';