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
Be the first comment