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';