DB/MySQL (관계형DB)
조인을 위한 실습
블루빔
2020. 11. 24. 13:59
DB 서버 접근
▶ mysql -uroot -p
데이터베이스 생성 & 확인
▶CREATE DATABASE Acompany;
▶ SHOW DATABASES;
데이터베이스에 접근
▶USE Acompany;
employee Table 생성하기
▶CREATE TABLE employee (
id INT(10) NOT NULL AUTO_INCREMENT,
name CHAR(20) NOT NULL,
gender CHAR(1) NOT NULL,
hire_date INT(10)NOT NULL,
department_id INT(10) NULL,
PRIMARY KEY(id));
department Table 생성하기
▶CREATE TABLE department (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
location INT(1) NULL,
PRIMARY KEY(id));
각 테이블에 데이터 삽입하기
INSERT INTO department (id,name,location) VALUES (1,'HR',3);
INSERT INTO department (id,name,location) VALUES (2,'Finance',5);
INSERT INTO department (id,name,location) VALUES (3,'Marketing',4);
INSERT INTO employee (id,name,gender,hire_date,department_id) VALUES (1,'KimA','m',20170102,1);
INSERT INTO employee (id,name,gender,hire_date,department_id) VALUES (2,'LeeA','w',20200602,2);
INSERT INTO employee (id,name,gender,hire_date,department_id) VALUES (3,'KangA','w',20190802,1);
INSERT INTO employee (id,name,gender,hire_date,department_id) VALUES (4,'JangA','m',20190802,3);
조인 JOIN
SELECT * FROM employee LEFT JOIN department ON department_id=department.id;
----------------------------
문제 : 두 테이블이 같은 컬럼 'name'을 사용하기에 조인할때 에러남
해결 : 두 테이블의 이름을 변경한다.
▶ ALTER TABLE 테이블명 RENAME COLUMN 원래컬럼명 TO 변경할컬럼명;
ALTER TABLE employee RENAME COLUMN name TO emp_name;
ALTER TABLE department RENAME COLUMN name TO dep_name;
----------------------------
두테이블에서 보고 싶은 컬럼만 선택해서 출력
▶SELECT employee.id,emp_name,gender,hire_date,dep_name,location FROM employee LEFT JOIN department ON department_id=department.id;
두테이블에서 보고 싶은 컬럼만 선택해서 출력하고 id를 employee_id로 설정함
▶SELECT employee.id AS employee_id,emp_name,gender,hire_date,dep_name,location FROM employee LEFT JOIN department ON department_id=department.id;