무엇을 사용하건 특별한 정답은 없음.
관계형 데이터베이스와 소통하는 언어.
이번 실습에서는 MySQL 8.0을 사용.
툴은 MySQL
CREATE DATABASE restaurant_finder;
CREATE TABLE `restaurant_finder`.`restaurants` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`type` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO restaurants (name, type) VALUES ('Web Dev Mealery', 'German');
INSERT INTO restaurants (name, type) VALUES ('Pizza House', 'Italian');
INSERT INTO restaurants (name, type) VALUES ('Beergarden', 'German');
SELECT * FROM restaurant_finder.restaurants;
UPDATE restaurants SET name = 'Web Dev Meals' WHERE id = 1;
DELETE FROM restaurants WHERE id = 1;
명령어 키워드 create database
는 구분하기 쉽게 대문자를 권장하되 의무는 아니며 sql실행에도 영향을 주지 않음.
MySQL Workbench 단축키
ctrl
+Enter
: 쿼리 전체 실행
ctrl
+shift
+Enter
: 드래그한 쿼리만 실행
테이블의 column명 설정시 streetNunber
가 아닌 street_number
처럼 만든다.
단어 구분은 언더바로, 단어는 모두 소문자로 작명.
CREATE TABLE `restaurant_finder`.`addresses` (
`id` INT NOT NULL AUTO_INCREMENT,
`street` VARCHAR(255) NOT NULL,
`street_number` VARCHAR(45) NOT NULL,
`city` VARCHAR(255) NOT NULL,
`postal_code` INT NOT NULL,
`country` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`));
CREATE TABLE `restaurant_finder`.`types` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`typecol` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
// 테이블 관계설정을 위해 restaurants테이블 삭제 후 새로생성
CREATE TABLE `restaurant_finder`.`restaurants` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`address_id` INT NOT NULL,
`type_id` INT NOT NULL,
PRIMARY KEY (`id`));
CREATE TABLE `restaurant_finder`.`reviews` (
`id` INT NOT NULL AUTO_INCREMENT,
`reviewer_name` VARCHAR(255) NOT NULL,
`rating` INT NOT NULL,
`text` TEXT NULL,
`date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`restaurant_id` INT NOT NULL,
PRIMARY KEY (`id`));
insert into types (name) values ('Italian');
insert into types (name) values ('American');
insert into types (name) values ('German');
insert into types (name) values ('Austrian');
insert into reviews (reviewer_name, rating, text, restaurant_id) values(
'lee ee',
2,
'맛집.',
3
);
둘 이상의 테이블을 결합시켜 하나의 결과 테이블로 조회하고싶을 때 사용.
select restaurants.id, restaurants.name, addresses.* from restaurants inner join addresses on (restaurants.address_id = addresses.id);
select restaurants.id, restaurants.name, addresses.*, types.name AS type_name from restaurants
inner join addresses on (restaurants.address_id = addresses.id)
inner join types on restaurants.type_id = types.id;
reviews테이블의 모든 컬럼과 평점(rating)이 3점이상인 레스토랑의 정보를 표시하시오.
SELECT reviews.*, restaurants.name AS restaurants_name, types.name AS restaurants_type FROM restaurants
INNER JOIN addresses ON (restaurants.address_id = addresses.id)
INNER JOIN types ON (restaurants.type_id = types.id)
INNER JOIN reviews ON (restaurants.id = reviews.restaurant_id)
WHERE reviews.rating >3;