In this section, I will try to go over everything there is to know about tables in SQL.
Disclaimer: I will be writing queries for MySQL since that is one of the most popular open-source databases out there. Let me know if you would like to see a similar post about all the other RMDBS.
Creating a simple table
The general syntax for creating a table is:
CREATE TABLE table_name(
column_name DATA_TYPE CONSTRAINT
);
Let’s create a table my_table with 3 columns. We need to specify the datatype associated with that column. I will be discussing other data types in a future article. If needed, we can also provide constraints on the individual column here. Currently, we don’t have any constraints
CREATE TABLE my_table (
id INT,
name VARCHAR(15),
alias VARCHAR(25)
);
Inserting data into the table
The general syntax for inserting data into a table is as follows:
INSERT INTO table_name (column...)
VALUES (value...);
Specifying columns is optional, if columns aren’t specified then by default all the columns are selected. The sequence in which values are specified is very important, it should match the columns specified or the columns of the table.
Let’s add a single row item into our table
INSERT INTO my_table
VALUES (1, "Bruce", "Batman");
Here we don’t specify the columns in our query, so by default, it selects all the columns. If we only want to add data into specific columns then we can specify them in our query.
We can also add multiple rows in one query, as follows:
INSERT INTO my_table
VALUES
(2, "Clark", "Superman"),
(3, "Barry", "Flash");
renaming table
We can still rename a table and the columns of the table after creation.
NOTE: Once modified, the table will be permanently changed. There is no undo unless there has been a backup created.
The general syntax is:
-- Altering table name
ALTER TABLE old_table_name
RENAME TO new_table_name;
-- Altering column name
ALTER TABLE table_name
RENAME COLUMN old_column_name
TO new_column_name;
Let’s change our table name to “superheroes”, and the “name” column to “real_name”.
ALTER TABLE my_table
RENAME TO superheroes;
ALTER TABLE superheroes
RENAME COLUMN name
TO real_name;
Please note, I am using “superheroes” as the table name in my 2nd part since I have already changed the table name in the 1st part.
Display, add, and delete columns
Let’s look at some basic queries for column manipulation
Display columns
We can use: DESCRIBE table_name
to get the details about the columns.
It returns details with the field, type, null, key, default, and extra columns.
Adding columns
The general syntax for adding a column is:ALTER TABLE table_name
ADD column_name datatype constraints
Now let’s add a new column “has_super_powers” and constraint that to only have either Y or N
ALTER TABLE superheroes
ADD has_super_powers CHAR(1)
CONSTRAINT super_power_check
CHECK (has_super_powers IN ('Y', 'N'));
There are multiple ways to add a constraint, here I am going with the above method, this way I can provide a name to my constraint.
Deleting columns
The general syntax for deleting a column is:
ALTER TABLE table_name
DROP column_name
If we want to delete the newly created column, then we can do so by using the following query
ALTER TABLE superheroes
DROP has_super_powers;
Display, add, and delete ROws
Let’s look at some basic queries for rows manipulation
Display rows
We have already gone through this section in the “SELECT” portion of this article: SQL Simplified: Let’s go back to the basics
But to quickly go over it, we can use the following general syntax to display rows in a tableSELECT * FROM table_name;
Adding rows
We can use the INSERT statement to add row(s) to our table.
Deleting rows
In order to delete rows, make sure to use the WHERE clause in your statement, otherwise, we will end up deleting all the rows from the table. The general syntax is:DELETE FROM table_name
WHERE condition;
Let’s say we want to delete our 3rd row with id = 3, then we can use the following query:
NOTE: We may need to set the SQL_SAFE_UPDATES to 0, in order to delete the row without providing a primary key
SET SQL_SAFE_UPDATES = 0;
DELETE FROM superheroes
WHERE id = 3;
SET SQL_SAFE_UPDATES = 1;
Updating data in a table
Now let’s look at how we can update the data within a table.
Updating a column of data
Here, we want to update the “has_super_powers” to have a value of Y for all the rows, so we can use the following query to do so:
UPDATE superheroes
SET has_super_powers = 'Y';
Setting the column to anything other than ‘Y’ or ‘N’ will result in an error since we have added a constraint to it which forces the values to be either ‘Y’ or ‘N’
Updating a row of data
Looking at the above data, we know that Batman doesn’t have any superpowers so let’s update the value for that particular row.
NOTE: It’s very important to include WHERE clause along with SET clause when updating a specific row. Without WHERE the entire table would be updated
UPDATE superheroes
SET has_super_powers = 'N'
WHERE alias = "Batman";
deleting table
In order to delete the table, we can simply call DELETE TABLE table_name
I will be adding more details to this article as I come across other topics related to TABLES in SQL.