To create a database from a script in MySQL:

1. Open a Query tab in MySQL Workbench
2. Run a CREATE DATABASE or CREATE SCHEMA statement to create the database (example below)

This will create a new database.

The script can be as simple as CREATE DATABASE myDatabase; but this will create a blank database. It won’t create any tables or other database objects. To create those, you need to include CREATE TABLE and other statements in your script.

Sample Scripts

Below are examples of scripts that create a database.

Create a Blank Database

The following statement creates a blank database called “Music”. No tables are created.

CREATE DATABASE Music;

The following statement does exactly the same thing (CREATE SCHEMA is a synonym for CREATE DATABASE). You can choose which one you prefer to use.

CREATE SCHEMA Music;

But NOT if it Already Exists…

Adding IF NOT EXISTS will only create the database if it doesn’t already exist.

CREATE DATABASE IF NOT EXISTS Music;

Or DROP it if it Already Exists…

Starting with DROP DATABASE IF EXISTS; will drop the database if it already exists. Then the CREATE DATABASE statement can go ahead and create the new database as specified.

DROP DATABASE IF EXISTS Music;
CREATE DATABASE Music;

Simple Database with a Table

The following script creates a database (if it doesn’t already exist), then creates a table with two columns. It sets the columns’ data types, and sets the ActorId column as the primary key.

CREATE DATABASE Movies;
USE Movies ;

CREATE TABLE Actors (
 ActorId INT NOT NULL AUTO_INCREMENT,
 ActorName VARCHAR(255) NOT NULL,
 PRIMARY KEY (ActorId));

Database with 3 Tables

The following script was generated from a diagram using MySQL Workbench and is more complete than the above example.

This one drops any existing database of this name, then creates the database, creates the tables, columns, and their data types. It also sets up a relationship between the tables using primary key and foreign key constraints.

Also, at the top of the script, there’s a bunch of SET statements that disable unique and foreign key checks. These are then reenabled again at the bottom, after the script has run.

This technique is typically used for when inserting data into tables with relationships. Without doing this, you could run into trivial errors simply because all the data hasn’t been inserted yet (for example, if populating a foreign key field before the associated primary key field has been populated).

-- MySQL Script generated by MySQL Workbench
-- Mon May 30 11:25:32 2016
-- Model: New Model Version: 1.0
-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema Music
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `Music` ;

-- -----------------------------------------------------
-- Schema Music
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Music` DEFAULT CHARACTER SET utf8 ;
USE `Music` ;

-- -----------------------------------------------------
-- Table `Music`.`Artists`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Music`.`Artists` ;

CREATE TABLE IF NOT EXISTS `Music`.`Artists` (
 `ArtistId` INT NOT NULL AUTO_INCREMENT,
 `ArtistName` VARCHAR(255) NOT NULL,
 PRIMARY KEY (`ArtistId`));


-- -----------------------------------------------------
-- Table `Music`.`Genres`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Music`.`Genres` ;

CREATE TABLE IF NOT EXISTS `Music`.`Genres` (
 `GenreId` INT NOT NULL AUTO_INCREMENT,
 `Genre` VARCHAR(255) NOT NULL,
 PRIMARY KEY (`GenreId`));


-- -----------------------------------------------------
-- Table `Music`.`Albums`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Music`.`Albums` ;

CREATE TABLE IF NOT EXISTS `Music`.`Albums` (
 `AlbumId` INT NOT NULL AUTO_INCREMENT,
 `AlbumName` VARCHAR(255) NOT NULL,
 `DateReleased` DATETIME NOT NULL,
 `ArtistId` INT NOT NULL,
 `GenreId` INT NOT NULL,
 PRIMARY KEY (`AlbumId`),
 INDEX `ArtistId_idx` (`ArtistId` ASC),
 INDEX `GenreId_idx` (`GenreId` ASC),
 CONSTRAINT `ArtistId`
 FOREIGN KEY (`ArtistId`)
 REFERENCES `Music`.`Artists` (`ArtistId`)
 ON DELETE RESTRICT
 ON UPDATE RESTRICT,
 CONSTRAINT `GenreId`
 FOREIGN KEY (`GenreId`)
 REFERENCES `Music`.`Genres` (`GenreId`)
 ON DELETE RESTRICT
 ON UPDATE RESTRICT);


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