8.1.1 INSERT 예제

인서트 예제는 다음과 같습니다.

-- 데이터변경 전후 관련 튜토리얼 (저장 테스트)
-- https://www.javatpoint.com/mysql-insert
CREATE TABLE People(
id int NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
age int,
PRIMARY KEY (id)
);

SELECT id, name, occupation, age
FROM history_hub.People;

-- case 1
INSERT INTO People (id, name, occupation, age)
VALUES (101, 'Peter', 'Engineer', 32);

-- case 2
INSERT INTO People VALUES
(102, 'Joseph', 'Developer', 30),
(103, 'Mike', 'Leader', 28),
(104, 'Stephen', 'Scientist', 45);

-- case 3
INSERT INTO People (name, occupation)
VALUES ('Stephen', 'Scientist'), ('Bob', 'Actor');

-- https://www.mysqltutorial.org/mysql-insert-statement.aspx
CREATE TABLE IF NOT EXISTS tasks (
task_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
start_date DATE,
due_date DATE,
priority TINYINT NOT NULL DEFAULT 3,
description TEXT,
PRIMARY KEY (task_id)
);

SELECT * FROM tasks;

-- case 4
INSERT INTO tasks(title,priority)
VALUES('Learn MySQL INSERT Statement',1);

-- CASE 5
INSERT INTO tasks(title,priority)
VALUES('Understanding DEFAULT keyword in INSERT statement',DEFAULT);

-- CASE 6
INSERT INTO tasks(title, start_date, due_date)
VALUES('Insert date into table','2018-01-09','2018-09-15');

-- CASE 7
INSERT INTO tasks(title,start_date,due_date)
VALUES('Use current date for the task',CURRENT_DATE(),CURRENT_DATE());

-- CASE 8
INSERT INTO tasks(title, priority)
VALUES
('My first task', 1),
('It is the second task',2),
('This is the third task of the week',3);

-- CASE 9
CREATE TABLE IF NOT EXISTS tasks_temp (
task_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
start_date DATE,
due_date DATE,
priority TINYINT NOT NULL DEFAULT 3,
description TEXT,
PRIMARY KEY (task_id)
);
INSERT INTO tasks_temp(task_id, title, start_date, due_date, priority, description )
SELECT * FROM tasks limit 5;

--case 10
INSERT INTO tasks( title, start_date, due_date, priority, description )
SELECT title, start_date, due_date, priority, description
FROM tasks limit 5;

-- 데이터변경 전후 관련 튜토리얼 (저장 테스트)
-- https://www.javatpoint.com/mysql-insert
CREATE TABLE People(
id int NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
age int,
PRIMARY KEY (id)
);

SELECT id, name, occupation, age
FROM history_hub.People;

-- case 1
INSERT INTO People (id, name, occupation, age)
VALUES (101, 'Peter', 'Engineer', 32);

-- case 2
INSERT INTO People VALUES
(102, 'Joseph', 'Developer', 30),
(103, 'Mike', 'Leader', 28),
(104, 'Stephen', 'Scientist', 45);

-- case 3
INSERT INTO People (name, occupation)
VALUES ('Stephen', 'Scientist'), ('Bob', 'Actor');

-- https://www.mysqltutorial.org/mysql-insert-statement.aspx
CREATE TABLE IF NOT EXISTS tasks (
task_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
start_date DATE,
due_date DATE,
priority TINYINT NOT NULL DEFAULT 3,
description TEXT,
PRIMARY KEY (task_id)
);

SELECT * FROM tasks;

-- case 4
INSERT INTO tasks(title,priority)
VALUES('Learn MySQL INSERT Statement',1);

-- CASE 5
INSERT INTO tasks(title,priority)
VALUES('Understanding DEFAULT keyword in INSERT statement',DEFAULT);

-- CASE 6
INSERT INTO tasks(title, start_date, due_date)
VALUES('Insert date into table','2018-01-09','2018-09-15');

-- CASE 7
INSERT INTO tasks(title,start_date,due_date)
VALUES('Use current date for the task',CURRENT_DATE(),CURRENT_DATE());

-- CASE 8
INSERT INTO tasks(title, priority)
VALUES
('My first task', 1),
('It is the second task',2),
('This is the third task of the week',3);

-- CASE 9
CREATE TABLE IF NOT EXISTS tasks_temp (
task_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
start_date DATE,
due_date DATE,
priority TINYINT NOT NULL DEFAULT 3,
description TEXT,
PRIMARY KEY (task_id)
);
INSERT INTO tasks_temp(task_id, title, start_date, due_date, priority, description )
SELECT * FROM tasks limit 5;

--case 10
INSERT INTO tasks( title, start_date, due_date, priority, description )
SELECT title, start_date, due_date, priority, description
FROM tasks limit 5;

CREATE TABLE devices (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);

INSERT INTO devices(name)
VALUES('Router F1'),('Switch 1'),('Switch 2');

-- case 12
INSERT INTO
devices(name)
VALUES
('Printer')
ON DUPLICATE KEY UPDATE name = 'Printer';

-- case 13
INSERT INTO devices(id,name)
VALUES
(4,'Printer')
ON DUPLICATE KEY UPDATE name = 'Central Printer';

-- case 14
INSERT INTO tasks(title,priority) VALUES('Learn MySQL INSERT Statement',1);
INSERT INTO tasks(title,priority) VALUES('Understanding DEFAULT keyword in INSERT statement',DEFAULT);
INSERT INTO tasks(title, start_date, due_date) VALUES('Insert date into table','2018-01-09','2018-09-15');
INSERT INTO tasks(title,start_date,due_date) VALUES('Use current date for the task',CURRENT_DATE(),CURRENT_DATE());
SELECT employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo, jobTitle
FROM history_hub.employees;