STUDY AND LESSON

MySQL, MariaDB 이너 조인

실습을 위한 부서(dept)와 사원(emp) 테이블을 생성한다.

create table dept(
	id int(20) unsigned primary key auto_increment,
	regDate datetime not null default now(),
	deptName char(30) unique
);


create table emp(
	id int(20) unsigned primary key auto_increment,
	regDate datetime not null default now(),
	name char(30) not null,
    deptId int(20) not null
);

키워드 설명

1) unsinged : 표현할 숫자의 범위에서 음수를 제외하고 음수를 제외한 만큼 양수에서 더 표현

2) primary key(개체 무결성 제약조건) : 기본키

3) auto_increment : 자동 증가

4) defalut : 해당 컬럼 기본값 설정

5) now() : 현재 시스템 시간을 가져오는 함수

다음으로 실습을 위해 각 테이블에 데이터를 추가한다.

-- dept 테이블에 '홍보부서'와 '기획부서' 추가

insert into dept(deptName) values('홍보');
insert into dept(deptName) values('기획');


-- emp 테이블에 사원 추가

insert into emp(name, deptId) value('루니', 1);
insert into emp(name, deptId) value('램파드', 2);

Inner Join을 통한 사원의 소속 부서 출력

사원 테이블에는 사원이 속한 부서의 이름이 아닌 부서의 번호가 입력되어 있다.

우선 부서 테이블과 사원 테이블에 있는 모든 데이터를 가져오는 다음 쿼리문을 실행하여 본다.

select * from dept, emp;

실행하면 사원의 이름도 중복되고 사원의 부서도 알 수 없게 출력된다.

따라서 Inner Join을 이용하여 사원의 소속 부서를 알아내야 한다.

select * from [테이블명] inner join [조인할 테이블명] on [조인 조건]

select * from emp inner join dept on emp.deptId = dept.id;

해당 쿼리문은 사원 테이블과 부서 테이블을 Innter Join한 결과이고 조건은 사원 테이블의 부서 번호와 부서 테이블의 부서 번호가 일치하는 데이터만 출력하였다.

select emp.id, emp.regDate, emp.name, dept.deptName from emp inner join dept on emp.deptId = dept.id;

필요한 데이터는 사원 정보와 사원의 소속 부서이기 때문에 나머지 필요하지 않은 정보를 제외하였다.

컬럼 이름 변경

alter table [테이블명] change [변경전 컬럼] [변경후 컬럼] [컬럼 타입];

alter table article change user nickname varchar(30);

컬럼 제약 조건 추가

1) not null 추가 : alter table [테이블명] modify [컬럼명] [타입] [제약조건];

2) primary key(기본키) : alter table [테이블명] add primary key(컬럼명);

3) foreign key(외래키) : alter table [테이블명] add constraint [제약조건 이름] foreign key(컬럼명) reference [참조할 테이블](참조 테이블의 참조 컬럼);

// not null
alter table article modify id INT not null;


// 기본키
alter table article add primary key(id);

// 외래키 -> user 테이블의 기본키를 article 테이블 id 컬럼의 외래키로 추가
alter table article add constraint userid_fk foreign key(id) reference user(id);

컬럼 추가

alter table [테이블명] add column [컬럼명] [컬럼 타입];

alter table article add column username varchar(30);

컬럼 수정

alter table [테이블명] modify [컬럼명] [타입];

alter table article modify id INT;

컬럼 삭제

alter table [테이블명] drop column [컬럼명];

alter table article drop column id;

컬럼 순서 변경

alter table [테이블명] modify [컬럼명] [컬럼타입] after [앞 컬럼];

# username 뒤에 nickname 컬럼 추가

alter table article modify nickname varchar(30) after username;

MySQL, MariaDB 어드민 페이지 생성을 위한 테이블 생성 쿼리문

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema admin
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `admin` ;

-- -----------------------------------------------------
-- Schema admin
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `admin` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ;
USE `admin` ;

-- -----------------------------------------------------
-- Table `admin`.`category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `admin`.`category` ;

CREATE TABLE IF NOT EXISTS `admin`.`category` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(50) NOT NULL,
  `title` VARCHAR(100) NULL,
  `created_at` DATETIME NOT NULL,
  `created_by` VARCHAR(20) NOT NULL,
  `update_at` DATETIME NULL,
  `update_by` VARCHAR(20) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `admin`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `admin`.`user` ;

CREATE TABLE IF NOT EXISTS `admin`.`user` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `account` VARCHAR(12) NOT NULL,
  `password` VARCHAR(100) NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NULL,
  `phone_number` VARCHAR(13) NOT NULL,
  `registered_at` DATETIME NULL,
  `unregistered_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `created_by` VARCHAR(20) NOT NULL,
  `updated_at` DATETIME NULL,
  `updated_by` VARCHAR(20) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `admin`.`partner`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `admin`.`partner` ;

CREATE TABLE IF NOT EXISTS `admin`.`partner` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  `address` VARCHAR(100) NOT NULL,
  `call_center` VARCHAR(13) NULL,
  `partner_number` VARCHAR(13) NULL,
  `business_number` VARCHAR(16) NULL,
  `ceo_name` VARCHAR(20) NULL,
  `registered_at` DATETIME NULL,
  `unregistered_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `created_by` VARCHAR(20) NOT NULL,
  `updated_at` DATETIME NULL,
  `updated_by` VARCHAR(20) NULL,
  `category_id` BIGINT(20) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `admin`.`item`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `admin`.`item` ;

CREATE TABLE IF NOT EXISTS `admin`.`item` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(50) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  `content` TEXT NULL,
  `price` DECIMAL(12,4) NOT NULL,
  `brand_name` VARCHAR(50) NULL,
  `registered_at` DATETIME NULL,
  `unregistered_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `created_by` VARCHAR(20) NOT NULL,
  `updated_at` DATETIME NULL,
  `updated_by` VARCHAR(20) NULL,
  `partner_id` BIGINT(20) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `admin`.`order_group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `admin`.`order_group` ;

CREATE TABLE IF NOT EXISTS `admin`.`order_group` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(50) NOT NULL,
  `order_type` VARCHAR(50) NOT NULL,
  `rev_address` TEXT NOT NULL,
  `rev_name` VARCHAR(50) NULL,
  `payment_type` VARCHAR(50) NOT NULL,
  `total_price` DECIMAL(12,4) NOT NULL,
  `total_quantity` INT NOT NULL,
  `order_at` DATETIME NULL,
  `arrival_date` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `created_by` VARCHAR(20) NOT NULL,
  `updated_at` DATETIME NULL,
  `updated_by` VARCHAR(20) NULL,
  `user_id` BIGINT(20) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `admin`.`order_detail`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `admin`.`order_detail` ;

CREATE TABLE IF NOT EXISTS `admin`.`order_detail` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(50) NOT NULL,
  `arrival_date` DATETIME NULL,
  `quantity` INT NOT NULL,
  `total_price` DECIMAL(12,4) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `created_by` VARCHAR(20) NOT NULL,
  `updated_at` DATETIME NULL,
  `updated_by` VARCHAR(20) NULL,
  `order_group_id` BIGINT(20) NOT NULL,
  `item_id` BIGINT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `admin`.`admin_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `admin`.`admin_user` ;

CREATE TABLE IF NOT EXISTS `admin`.`admin_user` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `account` VARCHAR(12) NOT NULL,
  `password` VARCHAR(100) NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  `role` VARCHAR(50) NOT NULL,
  `last_login_at` DATETIME NULL,
  `password_updated_at` DATETIME NULL,
  `login_fail_count` INT NULL,
  `registered_at` DATETIME NULL,
  `unregistered_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `created_by` VARCHAR(20) NOT NULL,
  `updated_at` DATETIME NULL,
  `updated_by` VARCHAR(20) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;