Previously, we have looked at how you can extract information from a database using queries, but there may be a time when you will want to create a new database, add a new table or simply edit the ones that already exist.
To create an SQL database, we need to enter the following command:
This command creates a new database called my_first_db. Inside this database you can now start creating tables or insert data in said tables. Keep in mind that each database must hold a unique name. Having done this we can now follow up with creating tables or altering already existing tables.
Note that if the name is not unique, an error is usually thrown. To suppress the error and skip creating a duplicate database, you can use the IF NOT EXISTS:
On the rare occasion where you might want to delete your database you can use the DROP DATABASE command followed by the name of the database you want deleted.
Keep in mind that all the data within the database (including tables and data inside them) will be deleted.
Here is how you would delete the database that was created earlier:
Tables
When creating a new table there are some essential rules to follow to ensure good performance:
- each table column has a fixed name
- all data in a column must be of the same type
On top of the rules mentioned above, you must also decide what data you want to include in your table, what name do you assign to the data (column) and what type it is going to be¹.
The process of creating a new table contains two steps. First, you tell the database that you want to create a new table using the CREATE TABLE statement followed by the table name. Next, inside the CREATE TABLE statement, you start defining your columns together with their types, and every subsequent pair of column-type must be preceded by a comma.
To gain a better understanding of this concept let’s look at an example. We want to create a simple table called employees which takes two columns:
- full_name with VARCHAR type²
- age with INTEGER type³
Note that if the name is not unique, an error is usually thrown. To suppress the error and skip creating a duplicate table, you can utilize the IF NOT EXISTS clause which is used as such:
To delete a table from a database, you can use the DROP TABLE command followed by the name of the table you want deleted.
Keep in mind that the table and all the information inside it will be deleted. Here is how you would delete the employees table:
Footnotes
[1] Each object in a table holds an attribute (*data type*) that specifies the type of data that it can hold: numbers, text, date & time, monetary data and others.
[2] This type is used for columns which will store a string of characters (can be letters, digits and so on).
[3] An integer represents a whole number or a number which is not a fraction.
Learn More
