[MySQL] create, drop, alter / insert, delete, select, update

Object : create, drop, alter 
Data : insert, delete, select, update

Table Copy

• 데이터 포함

create table tb_test03 as select * from jobs;
create table tb_test03 as select job_id, max_salary as maxsal from jobs;

-- more safe
create table if not exists tb_test03
select * from jobs;

• 데이터 미포함

create table if not exists tb_test04 like jobs;

-- oracle에서도 쓰는 방법
create table dept as select * from departments where 1=2;

 

Object

• create

create table tb_test01(
	col1 varchar(10),
    col2 varchar(20),
    col3 varchar(30)
);

• drop

drop table tb_test03;

• alter

-- column 추가
alter table tb_test99 add newcol1 varchar(30);
alter table tb_test99 add (col2_new decimal(5,1), col3_new date);

-- column 데이터 수정
alter table tb_test99 modify newcol1 int;

-- column명 변경
alter table tb_test99 change col2_new new_col2 varchar(20);

-- column 삭제
alter table tb_test99 drop column newcol1;

 

Data

• insert

-- insert
insert into dept(department_id, department_name, manager_id, location_id)
values(10, '기획부', 100, 101);

insert into dept(department_id, department_name)
values(20, '관리부');

insert into dept(manager_id,  location_id, department_id, department_name)
values(110, 102, 30, '경리부');

insert into dept
values(40, '개발부', 130, 103);

• delete

-- delete
set sql_safe_updates = 0; -- safe mode 해제

delete from dept where department_name = '관리부부';
delete from dept where manager_id is null;

- update와 delete 에는 safe mode가 default로 걸려있는데 이 때 바로 값을 변경하려 하면 1175 error(보안 에러) 가 나타난다. 따라서 sql_safe_updates = 1 이 기본인 설정에서 sql_sate_updates = 0 으로 변경해야 값에 접근이 가능하다.

• select

• update

-- update
update dept set manager_id = 300 where department_id = 40;
update dept set manager_id = 500, location_id = 333 where department_name = '개발부';