Knowing how to create an SQL table properly is perhaps one of the most essential skills a budding database designer should have.
If you’re new to database management or simply need a refresher on SQL tables, this tutorial is just the one for you.
Getting Started With Your SQL Table
Before we create a table, make sure that you have a schema set up in an SQL server. For this example, we will be using a MySQL server along with MySQL Workbench to create a table.
The first thing to do is to set up a connection.
To do this, open MySQL Workbench, and click on the + icon to add a connection.
This opens up a dialog box where you can control the properties of the new connection. Add a new Connection Name and click OK.
Clicking on the connection takes you to the editor where you can input queries to create and manipulate schemas.
To test out our code for creating a table, let’s create a new schema.
CREATE schema mySchema;
USE mySchema
This creates an SQL schema that stores tables and their relationships. Now, onto the table.
Create an SQL Table
In SQL, a table can be created using the CREATE keyword. While creating the table, you need to specify its column names, column data types, and the primary key column.
The general syntax for doing so is:
CREATE TABLE table_name(
column1 datatype
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( columnName )
);
Let’s use this syntax to create a table that stores employee data in a company.
use mySchema;
CREATE TABLE employee(
empID int not null,
empName varchar(25) not null,
emailID varchar(25) not null,
PRIMARY KEY (empID)
);
Note the not null key phrase here. This ensures that whenever a new employee is added, none of the fields can be left empty while adding their information.
Now, let’s test whether our table was successfully created and stored in the schema. One way to do so is to add some values to the table, and output them into the ‘Result Grid’ panel.
Adding Values in an SQL Table
To add values to the table, use the following command and arguments:
INSERT INTO employee
VALUES (1, ‘John Matthews’, ‘john_matthews@muo.com’);
Displaying Values From an SQL Table
To display values from the employee table, we can make use of the SELECT command.
To do so, use the following:
SELECT * from employee;
The * here is a wildcard operator which selects everything by default. In this case, all rows of the employee table are selected to be displayed.
If everything goes smoothly, this is what you should see:
Exploring SQL Further
There’s a lot more to databases than simply building table-upon-table. You can play around with some handy features such as queries and subqueries or, if you’re feeling adventurous, even try your hand at writing a procedure or trigger.
At the end of the day, however, the effectiveness of your SQL program comes down to how well you build and structure your tables. So make sure to keep this guide bookmarked until you know how to build SQL tables like the back of your hand!
