MYSQL 기본

MYSQL 기본

---

1. CREATE

CREATE TABLE tb_test01(
	col1 varchar(10),
	col2 varchar(20),
    col3 varchar(30)
);
-- 구분선 --
create table tb_test02(
	id int,
    name varchar(30),
    height decimal(3, 1),
    birth date
);
-- 구분선 --
-- table copy : 데이터를 포함
create table tb_test03
as select * from jobs;
-- 구분선 --
-- table copy : 위 쿼리보다 더 안전
create table if not exists tb_test03
select * from jobs;
-- 구분선 --
create table tb_test03
as
select job_id as jobid, max_salary as maxsal
from jobs;
-- 구분선 --
drop table tb_test03;
-- 구분선 --
-- 데이터 미포함(mysql)
create table if not exists tb_test04
like jobs;
-- 구분선 --
create table tb_test05
select department_id as dnum, sum(salary) as "sum", avg(salary) as "avg" 
from employees
group by department_id;

2. Alter

alter table tb_test04
rename 
to tb_test99;
-- 구분선 --
-- 컬럼 추가
alter table tb_test99
add newcol1 varchar(30);
-- 구분선 --
alter table tb_test99
add (col2_new decimal(5,1), col3_new date);
-- 구분선 --
-- 컬럼 형태 확인
desc tb_test99;
-- 구분선 --
-- 컬럼 데이터형 수정
alter table tb_test99
modify
newcol1 int;
-- 구분선 --
-- 컬럼명 변경
alter table tb_test99
change col2_new new_col2 varchar(20);
-- 구분선 --
alter table tb_test99
drop column newcol1;

3. 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 values(40, '개발부', 130, 103);

4. Delete & Update

-- 삭제
delete from dept
where department_name = '개발부';
-- 구분선 --
delete from dept
where manager_id is null;
-- 구분선 --
-- 수정
update dept
set manager_id = 300
where department_id = 40;
-- 구분선 --
update dept
set manager_id = 500, location_id = 333
where department_name = '개발부';

5. 무결성, 제약조건

• 무결성 : 데이터베이스에 저장된 값들이 정확하고 일관성 있는 데이터임을 의미. 무결성이 보장되도록 여러 개체에 규정을 부여하여 운영
• 제약조건 : 정확하고 유효한 데이터가 데이터베이스에 저장될 수 있도록 하기 위하여 데이터를 조작하는데 한계를 규정한 것
• constraint 뒤에 제약조건의 id 부여
  1. primary key : 기본키(null 허용하지 않는다. 중복을 허용하지 않는다. 예시 : id, 주민번호)
  2. unique key : 고유키(null 허용한다. 중복을 허용하지 않는다. 예시 : e-maill)
  3. foreign key : 외래키, join(테이블과 테이블을 연결), null 허용
  외래키로 지정된 컬럼은 연결된 테이블에서 PK나 UK로 설정되어 있어야 한다.
  4. check : 범위를 지정, 값을 지정, 지정된 값외에 사용할 수 없다. null을 허용
  5. not null : null값을 허용하지 않는다. 중복을 허용한다.

-- not null
create table tb_test06(
	col1 varchar(10) not null,
    col2 varchar(20)
);
insert into tb_test06 (col1, col2) values ('AAA','111');
insert into tb_test06 (col1, col2) values ('BBB','');
insert into tb_test06 (col1, col2) values (' ','111');
insert into tb_test06 (col2) values ('111'); -- 불가능
-- 구분선 --
-- primary key = unique + not null
create table tb_test06(
	pk_col varchar(10) primary key,
    col1 varchar(20),
    col2 varchar(30)
);
-- 구분선 --
insert into tb_test06(pk_col)
values('BBB');
-- 불가능
insert into tb_test06(pk_col)
values('bbb');
drop table if exists tb_test06;
-- 구분선 --
create table tb_test06(
	pk_col varchar(10),
    col1 varchar(20),
    col2 varchar(30)
);
alter table tb_test06 add primary key(pk_col);
-- 구분선 --
-- unique : 고유키, 중복된 값은 불가, null허용
CREATE TABLE TB_TEST07(
	uk_col varchar(10) unique,
    col1 varchar(20),
    col2 varchar(30)
);
insert into tb_test07(uk_col, col1, col2)
values ('AAA', 'aaa', '111');
-- 소문자지만 같은 값이므로 안된다.
insert into tb_test07(uk_col, col1, col2)
values ('aaa', 'aaa', '111');
alter table tb_test07 add primary key(col2);
drop table if exists tb_table07;
-- 구분선 --
-- 외래키
-- 기본(부모)테이블(departments)에서 PK, UK로 설정되어 있어야 한다.
-- NULL을 허용
-- parent table : departments
create table tb_dept(
	department_id varchar(10),
    dpartment_name varchar(20),
    location_id decimal(3),
    primary key(department_id)
);
insert into tb_dept(department_id, dpartment_name, location_id)
values('10', '기획부', 120);
insert into tb_dept(department_id, dpartment_name, location_id)
values('20', '관리부', 120);
insert into tb_dept(department_id, dpartment_name, location_id)
values('30', '개발부', 120);
-- child table : employees
create table tb_emp(
	empno int,
    ename varchar(30),
    department_id varchar(10),
    foreign key(department_id) references tb_dept(department_id),
    primary key(empno)
);
-- 컬럼의 정보를 볼 수 있도록 해준다.
desc tb_emp;
insert into tb_emp(empno, ename, department_id)
values(100, '홍길동', '20');
insert into tb_emp(empno, ename, department_id)
values(101, '홍길동', '10');
insert into tb_emp(empno, ename, department_id)
values(102, '일지매', '30');
-- 참조하는 테이블이 비어있어서 error
insert into tb_emp(empno, ename, department_id)
values(103, '정수동', '40');
insert into tb_emp(empno, ename)
values(104, '홍두께');
insert into tb_emp(empno, ename, department_id)
values(105, '임꺽정', null);
select empno, ename,
		e.department_id, d.department_id,
        d.dpartment_name, location_id
from tb_emp e, tb_dept d
where e.department_id = d.department_id;
-- check
-- 지정된 값만 넣을 수 있다.null 허용.중복된 값은 당연히 허용.
create table tb_check(
	col1 varchar(10),
    col2 int,
    constraint chk_01 check( col1 in ('사과','배','바나나')),
    constraint chk_02 check( col2 > 0 AND col2 <= 10 )
);
-- 구분선 --
insert into tb_check(col1, col2)
values('사과','5');
insert into tb_check(col1, col2)
values('배','5');
-- null 가능
insert into tb_check(col1, col2)
values(null,'5');
-- error 범위 안 맞음
insert into tb_check(col1, col2)
values('바나나','0');

6. view

• 뷰는 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 생성되는 가상테이블이다.
• 뷰는 저장장치 내에 물리적으로 존재하지 않지만 사용자에게 있는 것처럼 간주된다.
• 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단하다.
• 뷰가 정의된 기본테이블이나 뷰를 삭제하면 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동으로 삭제된다.

/*
	view : 가상 테이블, 실체가 없는 테이블 != dual, 다른 테이블에 접근하기 위한 테이블
	table <----- view <----- user
    속도가 빠르다
    한개의 view로 여러개의 테이블을 검색할 수 있다. (join문에 유리)
    제한설정이 가능하다. ---> readOnly
*/
-- **구분선** --
-- or replace 생성을 하거나 수정을 한다.
CREATE OR REPLACE VIEW UB_TEST01
AS
SELECT JOB_ID, JOB_TITLE, MIN_SALARY
FROM JOBS;
-- **구분선** --
-- 실제테이블, 임시테이블 모두 삽입된다.
insert into ub_test01(job_id, job_title, min_salary)
values ('DEVELOPER0', '개발자', 10000);
-- **구분선** --
CREATE TABLE EMP
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES;
-- VIEW를 생성
CREATE OR REPLACE VIEW ub_view(empno, ename, sal)
AS 
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMP;
-- 조인해서 만든 데이터들을 뷰로 만들어놔서 테이블 1개로 관리한다.
CREATE OR REPLACE VIEW DEPT_EMP_LOCATION_VIEW
AS 
SELECT EMPLOYEE_ID, FIRST_NAME, D.DEPARTMENT_ID, DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D. DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
;
SELECT * 
FROM DEPT_EMP_LOCATION_VIEW
WHERE 1=1
;
DROP VIEW DEPT_EMP_LOCATION_VIEW;