Create a table in MySQL

To create a table in MySQL, start by connecting to the MySQL server from PHPMyAdmin. Indeed phpMyAdmin (PMA) is the central interface to manage MySQL databases and tables.

MySQL is an Open Source database, which means that it is free software. To start, connect to the MySQL server and database from a web browser like Google Chrome or Firefox for example.

How to create a table in MySQL ?

If your development server is your machine, then the URL is: https://localhost/pma/. Where localhost can be replaced by your machine name. The default port for PMA is 3306.

To sum up you need two information’s:

  1. The server URL: https://localhost/pma/ where localhost is replaced by your servername
  2. The server port, by default it is 3306

Simply copy and paste the script into the phpMyAdmin interface for example.

This MySQL example table contains three columns:

  • The CLIENTID column which represents the client number of type auto incremented integer
  • A NAME column for the customer name of type varchar(20) with the UNIQUE attribute
  • A CITY column to store the customer’s city varchar(20)

At the end of the script, the ClientID column is explicitly defined a primary key.

MySQL code to add a customer’s table then insert and select data

To start, test if the table exists in our database with the exists() function. If it does, then the table is deleted with the T-SQL drop table command. Then create the SALES table which simply contains three columns.

To execute the code and create a table in a MySQL database, go to the SQL tab, copy, and adapt the SQL code as needed.

-- Create the CUSTOMERS MySQL table with the column NAME declared as UNIQUE
-- The UNIQUE keyword defines the column with a unique value
CREATE TABLE Customers (
	CLIENTID int AUTO_INCREMENT,	
	NAME varchar(20) UNIQUE,	
	CITY varchar(20),
    PRIMARY KEY (CLIENTID)
);

-- Insert data for manipulation examples
INSERT INTO Customers (NAME, CITY) 
VALUES 	('MAMMADOU', 'Lyon'),
		('SERGEI', 'Lyon'),
		('CHRISTOPHE', 'Paris');

-- Check inserted rows
SELECT	*
FROM    Customers;

This short MySQL tutorial explains how to create a table with the Create Table command. And also, how to insert some rows with the SQL Insert Into command. Finally, how to display the contents of the table with a Select query.

To go further, here is an article to learn how to create a SQL Server table with a T-SQL script.

Be the first to comment

Leave a Reply

Your email address will not be published.


*