SQL statements to create database table SQL Server and MySQL

In this article, I will show you some simple SQL statements for creating a database and table for SQL Server and MySQL database engine.

I took the example from my Thailand administrative repository https://github.com/aaronamm/thai-administrative-division-province-district-subdistrict-sql

We start with creating a database and then creating a province table.

Create a database and table statements for SQL Server and MySQL

Create a database statement

SQL Server >

CREATE DATABASE Administrative;

MySQL >

CREATE DATABASE administrative;

Explain a statement

Create a database name administrative with default configuration.

Create a table statement

We create table with syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

SQL Server

CREATE TABLE [dbo].[Provinces] (
    [Id] int NOT NULL IDENTITY(1,1) ,
    [Code] int NOT NULL ,
    [NameInThai] nvarchar(150) NOT NULL ,
    [NameInEnglish] nvarchar(150) NOT NULL,
    CONSTRAINT [PK_Provinces_Id] PRIMARY KEY CLUSTERED
    ([ID] ASC)
);

Optional, we can also specify create index statement during after creating table.

CREATE UNIQUE INDEX [UX_Provinces_Code] ON [dbo].[Provinces]
([Code] ASC);

MySQL

CREATE TABLE `provinces` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` int NOT NULL,
  `name_in_thai` varchar(150) NOT NULL,
  `name_in_english` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_provinces_code` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Explain a statement

• We create table provinces.
• Make id an primary key, integer type and auto increment
• Make ProvinceCode as a unique key and it becomes indexing by default
• For MySQL, we specify database engine to InnoDB
• For SQL Server, Id is clustered index and ProvinceCode is unique non-clustered index