From Notes from John M for Class #3. (Thanks John!)




# Create our practice database
CREATE DATABASE foo;
# Switch to our practice database
USE foo;

# Create a default table
CREATE TABLE bar (bar_id INT);
# Create an INNODB table
CREATE TABLE bar_innodb (bar_id INT) TYPE=INNODB;
# Create a HEAP table
CREATE TABLE bar_heap (bar_id INT) TYPE=HEAP;

# Create two identical tables for merging
CREATE TABLE t_even (t_id INT);
CREATE TABLE t_odd (t_id INT);

# Insert some data
INSERT INTO TABLE t_even VALUES (2), (4);
INSERT INTO TABLE t_odd VALUES (1), (3);

# Create/Merge the two tables
CREATE TABLE t_merge (t_id INT) TYPE=MERGE UNION=(t_even, t_odd);

# Pull data from the merged table
SELECT * FROM t_merge;
# Switch to another database
USE world;
# Pull data from the merged table from anywhere using the dot notation method
SELECT * FROM foo.t_merge;

# Switch to our practice database
USE foo;
# Create a table with a reserved system command as the name (this fails)
CREATE TABLE select (t_id INT);
# Use backticks to solve this issue
CREATE TABLE `select` (t_id INT);
# Remove the table
DROP TABLE `select`;