[JAVA 웹 개발 공부] 국비지원 40일차 - MySQL 테이블 생성, 외래키, 테이블 조회

MySQL 테이블 생성

 

*PK : primary key

*NN : not null

*AI : auto_increment

 

 

table person, book

 

create database if not exists my_db;
show databases;
use my_db;
show tables;

-- 이름 / 나이
-- 고정길이 문자열 / 가변길이 문자열(사이즈에 맞게 조절됨. 불필요한 공간 할당 줄일수 있음. 용량 줄일 수 있음.)
create table person (
	name varchar(10), -- 이름 가변길이문자열(길이제한10)
    age int -- 나이 정수형
);
select * from person;
desc person;

-- 행 정보 저장
insert into person (name, age) values ("홍길동", 22);
insert into person (name, age) values ("브루스 리", 33);
insert into person (age, name) values (44, "스티븐 킹");
insert into person values ("톰 크루즈", 55);
insert into person (name) values ("이름만");
insert into person (age) values (66);

-- 책 테이블을 생성합니다.
-- 컬럼 구성
-- 숫자 : 정수 (각 행을 고유하게 식별할 수 있는 특별한 컬럼 값)
-- 제목 : 가변길이 문자열 (25자 제한)
-- 가격 : 정수
create table if not exists book (
	no int primary key -- null 값 허용안함. 무조건 값이 있어야함.
	, name varchar(25)
    , price int
);
desc book;
select * from book;
-- 테이블 지우기
drop table book; 

-- 책 테이블에 3행을 추가합니다.
-- 파워자바 25000원 
-- 구의 증명 6480원
-- 모든 것은 기본에서 시작한다 11200원
insert into book (no, name, price) values (1, "파워자바", 25000);
insert into book (no, name, price) values (2, "구의 증명", 6480);
insert into book (no, name, price) values (3, "모든 것은 기본에서 시작한다", 11200);
insert into book (no, name, price) values (4, "파워자바", 25000);
insert into book (no, name, price) values (5, "파워자바", 25000);

select * from book;
select * from book where no = 4;

-- 행 지우기
delete from book where no = 4;
delete from book where no = 5;

 

delete from person; -- person 테이블의 행 삭제
drop table if exists person; -- person 테이블 삭제

create table if not exists person (
	id int primary key auto_increment -- 값이 1부터 시작해서 행 추가할때 1씩 증가
    , name varchar(10) -- 이름 가변길이문자열(길이제한10)
    , age int -- 나이 정수형
);
select * from person;
desc person;

insert into person (id, name, age) values (100, "홍길동", 22);
insert into person (name, age) values ("다른사람", 23);

delete from person where id = 106;

update person
set name = '도우너'
where id = 105;

update person
set age = 44
where id = 105;

update person
set id = 103
where id = 105;

-- 104번의 사람의 이름을 '둘리' 나이를 50으로 수정하기
update person
set name = '둘리' , age = 50 , id = 104
where id = 107;

 

 

table restaurant, menu

 

-- 서면 식당(restaurant)
-- id int pk auto_increment
-- 상호명(name : varchar(15))
-- 전화번호(phoneNumber : varchar(15))
-- 주소(address : varchar(100))
create table if not exists restaurant (
	id int primary key auto_increment
    , name varchar(15)
    , phoneNumber varchar(15)
    , address varchar(100)
);
desc restaurant;
select * from restaurant;

drop table if exists restaurant;

insert into restaurant (name, phoneNumber, address)
	values ('영자면옥', '234-2344', '부전동444-2222')
    , ('칸다소바', '123-6766', '부전동 234-523')
    , ('서브웨이', '277-9847', '부전동 538-2830');
    
insert into restaurant (name, phoneNumber, address) values (null, null, null);

-- 테이블 컬럼 수정
-- 제약사항(NULL 허용 X) 추가
-- table 구성 변경 alter table
alter table restaurant change column phoneNumber phoneNumber varchar(15) not null;

desc restaurant;
-- 컬럼 추가
alter table restaurant add column score double;
-- 컬럼 삭제
alter table restaurant drop column score;

-- 상호명 메뉴명
-- 영자면옥 칼국수
-- 영자면옥 제육
-- 영자면옥 짜장면
-- 칸다소바 마제소바, 파마제소바
-- 서브웨이 에그마요, 미트볼, 참치, 새우

alter table restaurant add column menu varchar(30);
select * from restaurant;

insert into restaurant (name, phoneNumber, address, score, menu)
	values ('영자면옥', '234-2344', '부전동 444-2222', 5.0, '칼국수')
		, ('영자면옥', '234-2344', '부전동 444-2222', 5.0, '짜장면')
        , ('영자면옥', '234-2344', '부전동 444-2222', 5.0, '제육');
-- 메뉴라는 컬럼을 생성 후 추가하였으나, 정보가 다르게 입력될 경우의 정보상 오류와 결함의 문제 
-- 메뉴라는 테이블을 만들어서 참조하자!
alter table restaurant drop column menu;

-- 메뉴를 참조
create table if not exists menu (
	menuId int primary key auto_increment
    , name varchar(30) not null
    , price int not null
);
desc menu;
select * from menu;
insert into menu (name, price) values ('칼국수', 5000), ('제육', 6000), ('짜장면', 5500);

-- 외래키(Foreign Key) 참조
alter table menu add constraint foreign key (restID) references restaurant (id);

desc menu;

delete from restaurant where id = 1; -- 참조 무결성

 

 

외래키(Foreign Key) 참조

 

*관계형 DBMS

*중복제거

*결함이 생기지 않음

*조직화된 구조

*데이터들끼리 연관관계

*참조무결성

 

 

table class, students

 

create table if not exists class (
	id int primary key auto_increment
    , name varchar(10) 
);
desc class;
select * from class;
drop table if exists class;

insert into class (name) values ('돌고래'), ('코끼리');

create table if not exists students (
	id int primary key auto_increment
    , name varchar(10)
    , kor int
    , eng int
    , math int
    , classId int
    , constraint foreign key (classId) references class (id)
);

desc students;
select * from students;
drop table if exists students;

insert into students (name, kor, eng, math, classId) 
	values ('돌피', 90, 66, 80, 1)
		, ('블루홀', 88, 70, 100, 1)
		, ('네드', 80, 80, 80, 2)
		, ('가네샤', 90, 75, 77, 2);

 

 

table department, employee

 

create table if not exists department (
    name varchar(5) primary key not null
    , phone char(8) not null
);        
desc department; 
select * from department; -- 테이블보기
drop table if exists department; -- 삭제하기

insert into department (name, phone) 
	values ('IT', '333-4444')
    , ('물류', '333-8282')
    , ('회계', '333-7942');

create table if not exists employee (
	id int primary key auto_increment 
    , firstName varchar(10) not null
    , lastName varchar(10) not null
    , email varchar(60)
    , departName char(8)  
    , constraint foreign key (departName) references department(name)
);
desc employee;
select * from employee;
drop table if exists employee; 

insert into employee (firstName, lastName, email, departName) 
	values ('브루스', '웨인', 'batman@com.com', 'IT')
    , ('클락', '켄트', 'super@com.com', 'IT')
    , ('브루스', '리', 'lee@com.com', '물류')
    , ('폴', '피닉스', 'paul@com.com', '회계');

 

 

table 조회 연습

 

select count(*) from employees; -- count는 개수를 셀수 있음
select count(first_name) from employees;

-- 전화번호가 515~~ 로 시작하는 직원 조회하기
select * from employees where PHONE_NUMBER like '515%';

-- 전화번호가 515로 시작하는 직원의 수는?
select count(*) from employees where PHONE_NUMBER like '515%';

-- 모든 직원의 연봉의 합
select sum(salary) from employees;

-- 모든 직원의 연봉의 평균
select avg(salary) from employees;

-- 연봉의 최대값
select max(salary) from employees;

-- 연봉의 최소값
select min(salary) from employees;

-- 연봉의 최대값 - 최소값
select max(salary) - min(salary) from employees;

 

use hr;
select first_name, last_name, department_id from hr.employees;

-- sub query
-- 직원 테이블 finance
select * from departments where department_name = 'finance';
select * from employees where department_id = 100;
select * from employees 
	where department_id = (select department_id from departments 
    				where department_name = 'finance');

select '임의의 행정보', (select 500);
select 'asdf', (select department_id from departments where department_name = 'finance') as a;

-- 부서명이 IT로 시작하는 부서를 조회
select * from departments where department_name like 'IT%';
select department_id from departments where department_name like 'it%';

-- 위 부서에서 일하는 직원 조회
select * from employees where department_id in(60, 210, 230);
select * from employees 
	where department_id in(select department_id from departments where department_name like 'it%');

-- John Seo가 받는 연봉과 같은 직원들 조회하기
select * from employees where first_name = 'John' and last_name = 'Seo';
select * from employees 
	where salary = (select salary from employees 
    			where first_name = 'John' and last_name = 'Seo');

-- TJ Olson과 같은 부서에서 일하는 직원들 조회하기
select * from employees 
	where department_id = (select department_id from employees 
    				where first_name = 'TJ' and last_name = 'Olson');
	
-- 연봉 상위 10명
select * from employees order by salary desc limit 10;
-- 연봉 상위 10명 중에서 salary 오름차순으로 조회
select * from (select * from employees order by salary desc limit 10) as a order by a.salary asc;

 

 

연습문제

 

-- 평균 연봉보다 높은 연봉의 직원 조회하기
-- 평균 연봉보다 높은 연봉의 직원 수 조회하기 : 51명
SELECT AVG(SALARY) FROM employees;
SELECT * FROM employees WHERE SALARY > 6461.682243;
SELECT * FROM employees WHERE SALARY > (SELECT AVG(SALARY) FROM employees);
SELECT COUNT(*) FROM employees WHERE SALARY > (SELECT AVG(SALARY) FROM employees);

-- 최고연봉자와 최저연봉자의 이름 조회하기 : 2명
SELECT MAX(SALARY) FROM employees;
SELECT MIN(SALARY) FROM employees;
SELECT FIRST_NAME, LAST_NAME FROM employees
 WHERE SALARY IN ((SELECT MAX(SALARY) FROM employees), (SELECT MIN(SALARY) FROM employees));

-- 각 직원들의 이름, 연봉, 평균연봉과의 차(직원개인연봉 - 평균연봉) 조회하기 : 107명
SELECT FIRST_NAME, LAST_NAME, SALARY
	, ROUND(SALARY - (SELECT AVG(SALARY) FROM employees)) AS 'Gap'
 FROM employees;

-- Marketing 부서에서 일하는 직원 조회하기
-- Marketing 부서에서 일하는 직원 수, 연봉 합 조회하기 : 2명
SELECT DEPARTMENT_ID FROM departments WHERE DEPARTMENT_NAME = 'Marketing';
SELECT COUNT(*), SUM(SALARY) FROM employees
 WHERE DEPARTMENT_ID
	= (SELECT DEPARTMENT_ID FROM departments WHERE DEPARTMENT_NAME = 'Marketing');

-- Ellen Abel과 같은 부서에서 일하는 직원들을 연봉기준 내림차순으로 조회하기 : 34명
SELECT * FROM employees WHERE FIRST_NAME = 'Ellen' AND LAST_NAME = 'Abel';
SELECT * FROM employees
 WHERE DEPARTMENT_ID
	= (SELECT DEPARTMENT_ID FROM employees WHERE FIRST_NAME = 'Ellen' AND LAST_NAME = 'Abel')
 ORDER BY SALARY DESC;

-- 부서명이 IT로 시작하는 부서 직원들 중 최고연봉자 이름 조회하기
-- 위의 직원보다 높은 연봉을 받는 직원들을 연봉기준 오름차순으로 조회하기 : 23명
SELECT FIRST_NAME, LAST_NAME FROM employees WHERE DEPARTMENT_ID IN 
	(SELECT DEPARTMENT_ID FROM departments WHERE DEPARTMENT_NAME LIKE 'IT%')
 ORDER BY SALARY DESC LIMIT 1;
 
SELECT * FROM employees
 WHERE SALARY > 
		 (SELECT SALARY FROM employees WHERE DEPARTMENT_ID IN 
			(SELECT DEPARTMENT_ID FROM departments WHERE DEPARTMENT_NAME LIKE 'IT%')
				ORDER BY SALARY DESC LIMIT 1)
 ORDER BY SALARY ASC;

-- 자기가 속한 부서의 평균연봉보다 높은 연봉을 받는 직원
SELECT FIRST_NAME, LAST_NAME, SALARY, A.DEPARTMENT_ID
 FROM employees AS A
 WHERE A.SALARY > (SELECT AVG(SALARY) FROM employees WHERE DEPARTMENT_ID = A.DEPARTMENT_ID);