[MYSQL/Database] 실습 alter, rename, view 생성

view생성하기

create veiw [view name]  as  조건

 

create database testdb;

use testdb;

create table t_user(
	c_ID int primary key auto_increment comment '유저 고유 아이디',
    c_name char(20) not null comment '유저이름'
    )comment '유저 테이블';
    
SELECT table_name, table_comment
	FROM  information_schema.tables
	WHERE table_schema = 'testdb' AND table_name = 't_user';
    
    
SELECT table_name, column_name, column_comment
	FROM information_schema.columns
	WHERE table_schema = 'testdb' AND table_name = 't_user';

 

<조건 >

create table t1 (id int, name varchar(30));

insert into t1 values(1,'홍길동');
insert into t1 values(2,'서길동');
insert into t1 values(3,'남길동');
insert into t1 values(4,'북길동');

select * from t1;

뷰생성 

create view v1 as select id from t1;
create view v2 as select name from t1;
create view v3 as select id, name from t1 where id<3;

select * from v1;
select * from v2;
select * from v3;

 

# 테이블 컬럼 추가

alter table t_user
  add column test_column int not null;

desc t_user;

# 열이름 및 테이터 형식 변경

alter table t_user
  change column c_name n_name varchar(30) null;

desc t_user;

#  테이블 컬럼 삭제

alter table t_user drop column test_column;

desc t_user;

#  RENAME

create table t1(id int);
create table t2(id int);
create table t3(id int);

rename table t1 to t11;

rename table t2 to t22,
                  t3 to t33;

 

 

컬럼 추가
alter table t_user add column test_column int not null;

컬럼 이름 바꾸기
alter table t_user change column c_name n_name varchar(30) null;

컬럼 지우기
alter table t_user drop column test_column;

컬럼 추가하기
alter table t_user add column addr varchar(20);

컬럼 데이터타입 변경하기
alter table t_user change column n_name n_name varchar(50);

컬럼 지우기 
alter table t_user drop column c_ID;