SQL · Theory

Copy Database

Theory

12/439

Copy Database

MySQL has no COPY DATABASE keyword. You dump and restore: mysqldump college > college.sql then mysql college2 < college.sql after CREATE DATABASE college2.

For one table: CREATE TABLE students_bak AS SELECT * FROM students; That copies data, not always indexes/triggers. For a true clone, dump is safer.

Trap — thinking CREATE DATABASE college LIKE other; exists like CREATE TABLE LIKE. It does not.

On the example next to this theory: Copy Database: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

mysqldump then mysql < file. Or CREATE TABLE new AS SELECT * FROM old.

Copy Database — sample query

-- Copy Database
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Copy Database: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefNo COPY DATABASE. Dump + restore, or CREATE TABLE AS SELECT.
Rulemysqldump for a real copy.
TrapInventing COPY DATABASE syntax.

SQL · Theory

Copy Database

← All stacks

Theory

12/439

Copy Database

MySQL has no COPY DATABASE keyword. You dump and restore: mysqldump college > college.sql then mysql college2 < college.sql after CREATE DATABASE college2.

For one table: CREATE TABLE students_bak AS SELECT * FROM students; That copies data, not always indexes/triggers. For a true clone, dump is safer.

Trap — thinking CREATE DATABASE college LIKE other; exists like CREATE TABLE LIKE. It does not.

On the example next to this theory: Copy Database: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

mysqldump then mysql < file. Or CREATE TABLE new AS SELECT * FROM old.

Copy Database — sample query

-- Copy Database
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Copy Database: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefNo COPY DATABASE. Dump + restore, or CREATE TABLE AS SELECT.
Rulemysqldump for a real copy.
TrapInventing COPY DATABASE syntax.

Copy Database

Copy Database — sample query

Short notes

Questions

Copy Database

Copy Database — sample query

Short notes

Questions