MySQL 기초 공부

create database iot;    -- db생성
show databases;    -- db 보여주기
use iot;    -- 사용할 db 선택
show tables;    -- 현재 선택된 db의 테이블 보여줌
 
create table dept (    
deptno int(2) primary key,    -- 칼럼명/type/제약조건
dname varchar(20) not null,
loc varchar(20)
) ENGINE = InnoDB;
desc dept; -- table 스키마 보기
 
create table emp(
empno int(4) primary key,
ename varchar(10) not null,
job varchar(20) not null,
mgr int (4),
hiredate date, -- 입사일
sal int(8),    -- 연봉
comm int(8),    -- 커미션
deptno int(2),    -- 부서 번호
FOREIGN KEY fk_dept(deptno) 
REFERENCES dept(deptno)
) ENGINE = InnoDB; -- dept테이블의 deptno칼럼 참조
 
desc emp;
 
-- 관계된 테이블의 데이터 없이 참조하려고 하면 error
-- insert into emp values(1111, 'aaa', 'aaa', 1111, '2017/05/30', 3000, null, 10); 
 
insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'SALES', 'CHICAGO');
insert into dept values(40, 'OPERATING', 'BOSTON');
 
select * from dept;
desc emp;
 
insert into emp values(7369, 'SMITH', 'CLERK', 7902, 1980/12/17, 800, null, 20);
insert into emp values(7499, 'ALLEN', 'SALESMAN', 7698, '1981/02/20', 1600, 300, 30);
insert into emp values(7521, 'WARD', 'SALESMAN', 7698, '1981/02/22', 1250, 500, 30);
insert into emp values(7566, 'JONES', 'MANAGER', 7839, '1981/04/02', 2975, null, 20);
insert into emp values(7654, 'MARTIN', 'SALESMAN', 7698, '1981/09/28', 1250, 1400, 30);
insert into emp values(7698, 'BLAKE', 'MANAGER', 7839, '1981/05/01', 2850, null, 30);
insert into emp values(7788, 'SCOTT', 'ANALYST', 7566, '1987/04/19', 3000, null, 20);
insert into emp values(7839, 'KING', 'PRESIDENT', null, '1981/11/17', 5000, null, 10);
insert into emp values(7844, 'TURNER', 'SALESMAN', 7698, '1981/09/08', 1500, 0, 30);
insert into emp values(7876, 'ADAMS', 'CLERK', 7788, '1987/05/23', 1100, null, 20);
insert into emp values(7900, 'JAMES', 'CLERK', 7698, '1981/12/03', 950, null, 30);
insert into emp values(7902, 'FORD', 'ANALYST', 7566, '1981/12/03', 3000, null, 20);
insert into emp values(7934, 'MILLER', 'CLERK', 7782, '1982/01/23', 1300, null, 10);
insert into emp values(7782, 'CLARK', 'MANAGER', 7839, '1981/06/09', 2450, null, 10);
 
select * from emp;
 
insert into dept values(10, 'aaa', 'aaa'); -- 기본키 중복 error
insert into dept values(50, null, 'aaa'); -- dname은 not null이므로 error
insert into dept values(50, 'aaa', 'aaa'); -- error없이 정상 삽입
 
select * from dept where loc='NEW YORK';
select deptno from dept;
 
SELECT @@AUTOCOMMIT;  -- 오토커밋 여부를 조회
set autocommit=FALSE; -- 0으로 설정(0, FALSE: 해제 / 1,TRUE : 활성화)
 
delete from dept where deptno=50;
-- commit; 
 
select * from dept;
rollback;