To create the Northwind database in MySQL, use the CREATE DATABASE statement with the following syntax:

/*!40101 SET NAMES utf8 */;
 
/*!40101 SET SQL_MODE=''*/;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/`northwind` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
 
/********************
Plase some SQL CREATE TABLE and INSERT INTO statements here to create tables and populate with data.
*********************/
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

There are quite a few wired but useful MySQL syntaxes above.

1. SQL comments

   A /* sequence to the following */ sequence encloses comments, just like in the C programming language. This syntax enables a comment to extend over multiple lines because the beginning and closing sequences need not to be on the same line.

   There are 2 other characters which also mark the start of comments.

   (1) From a -- (double-dash) sequence to the end of the line encloses a line of comment.

   In MySQL, the -- comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on).

   (2) From a # character to the end of the line encloses a line of comment. The comment can start straight away after the # character, there is no need to be followed by at least one whitespace or control character.

2. Executable comments

   If you see /*! is used in SQL comment, it is code-containing comments. It enables you to write code that is executable. In this case, MySQL Server parses and executes the code within the comment.

3. Version awareness comments

   If you add a version number after the /*! characters, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number.

   For example, the IF NOT EXISTS keyword in the following comment can only be executed by servers from MySQL 3.23.12 or higher.

   CREATE DATABASE /*!32312 IF NOT EXISTS*/`northwind`;

4. SET syntax for variable assignment

   SET syntax for variable assignment enables you to assign values to different types of variables that affect the operation of the server or clients.

5. @@ modifiers apply only to system variables.

   You can run the following SQL statement and it returns 1, which indicates foreign check is enabled when insert statement is executed.

   select @@FOREIGN_KEY_CHECKS;

6. FOREIGN_KEY_CHECKS=0 and UNIQUE_CHECKS=0

   FOREIGN_KEY_CHECKS is an option that can be set to specify whether or not to foreign key constraints are checked for InnoDB tables.

   UNIQUE_CHECKS is an option that can be set to specify whether or not to unique key constraints are checked for a table.

   Disable check for foreign key constraints.

   SET FOREIGN_KEY_CHECKS = 0;

   Enable check for foreign key constraints.

   SET FOREIGN_KEY_CHECKS = 1;

7. User-defined variable

   A user-defined variable is written as @var_name and is assigned an expression value as follows:

   SET @var_name = expr;

   Examples:

   SET @name = 50;

   Set the value of system variable @@FOREIGN_KEY_CHECKS to a user-defined variable @OLD_FOREIGN_KEY_CHECKS.

   SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS

8. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS

   At the end of the Northwind database script, the server variables are then reset back to their original/default values by using the following statements.

   /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
   /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
   /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

9. IF NOT EXISTS

   Because the database name must be unique within the MySQL server instance, so to avoid an error when creating the database, we used the IF NOT EXISTS option. This way, MySQL does not issue an error but instead it terminates the CREATE DATABASE statement with a warning like this "Can't create database 'northwind'; database exists".

10. CHARACTER SET and COLLATE

    You can specify the CHARACTER SET and COLLATE for the database at creation time, but if you omit the CHARACTER SET and COLLATE clauses, MySQL uses the default character set and collation for the new database. The default character set and collation are contained in MySQL's my.ini configuration file.

Happy Coding!

Other tutorials in this category

1. What is Northwind database in MySQL

2. The Categories table in MySQL Northwind database

3. The Suppliers table in MySQL Northwind database

4. The Products table in MySQL Northwind database

5. The Customers table in MySQL Northwind database

6. The Employees table in MySQL Northwind database

7. The Shippers table in MySQL Northwind database

8. The Orders table in MySQL Northwind database

9. The Order Details table in MySQL Northwind database

10. SQL Views in MySQL Northwind database

Back to Tutorial Index Page