In a SQL database, tables are the fundamental structures used to store related data. Think of an SQL database as a huge digital filing cabinet. In this filing cabinet, tables are like individual drawers. Each drawer (or table) holds information about a specific topic. For example, if you were running an online store, you might have one drawer for 'Customers', another for 'Products', and another for 'Orders'.
So, the role of tables in SQL databases is to hold related pieces of information in a way that's organized and easy to find. It allows us to add, remove, and change pieces of information (rows) in a way that's easy to manage. And we can also make connections between drawers (tables) so that we can find out, for example, which customers have placed which orders.
Rows and Columns
A table in a SQL database consists of rows and columns, much like a table in a spreadsheet.
- Rows: Each row in a table represents a single record. It's like one entry in your data set. For example, if you have a table named 'Customers', a row in that table might represent one individual customer, with information about that customer like their name, contact information, etc.
- Columns: Columns in a table are the different categories of information that you're keeping track of. Each column represents a specific attribute or field of the data record. In the 'Customers' table example, the columns might include 'CustomerID', 'FirstName', 'LastName', 'Email', etc. Each one of these columns stores a specific type of data, and each row will have a piece of data for each column.
Here's an example of what a customer's table could look like.
In essence, rows are the individual records in a table, and columns are the types of information being stored about each record.
CREATE TABLE Keyword
We can use SQL to create tables. The keyword used to create a table in SQL is
CREATE TABLE. Here's a basic example of how you would use it to create a
CREATE TABLE customers (
In this statement:
CREATE TABLEis the command used to create a new table.
customersis the name of the table.
- Each line inside the parentheses defines a column in the table with a data type.
;character ends a statement.
Note that SQL commands are not case-sensitive. However, it's common practice to write SQL keywords in uppercase to distinguish them from table and column names, which are often written in lowercase or mixed case.
You may have noticed, but we're using snake casing for our column names. It's a common naming convention in SQL that deviates from PHP's naming convention of using camel casing. Since we're writing code for databases, you should follow the naming convention adopted by SQL developers.
Similar to PHP, SQL supports data types. All columns must have a data type. Data types in SQL tables specify the kind of data that can be stored in each column of a table. It's important to select the most suitable data type for each column to ensure efficient use of storage and also because the data type influences the kind of operations you can perform on the data.
MariaDB/MySQL supports a wide range of data types. Here are some of the most commonly used:
BIGINT(n): For integer values.
nrepresents the maximum length.
DECIMAL(M,N): For exact numerical values.
Mis the total number of digits, and
Nis the number of digits after the decimal point.
VARCHAR(n): For strings of variable length.
nrepresents the maximum length.
TEXT: For longer text strings.
DATE: For date values in
DATETIME: For date and time values in
TINYINT(1): For boolean values (
FLOAT(M,D): For floating point numbers.
Mis the total number of digits, and
Dis the number of digits after the decimal point.
When you are creating a table, the format for defining a column with its data type is:
For a complete list of data types, check out these links:
- MySQL: https://dev.mysql.com/doc/refman/8.0/en/data-types.html
- MariaDB: https://mariadb.com/kb/en/data-types/
Sometimes, you may want to delete a table after it's been created or don't need it anymore. This is known as "dropping."
"Dropping" a table in SQL essentially means to delete the entire table from the database. It removes the table structure, data, constraints, indexes, triggers, and any associated permissions for the table. Once a table is dropped, it cannot be recovered, so you should be very careful when using the
DROP TABLE command.
To drop a table in SQL, you would use the
DROP TABLE command, followed by the name of the table you want to drop. Here's an example:
DROP TABLE customers;
This command would delete the entire 'Customers' table from your database. You should only run this command if you're absolutely sure you want to delete the entire table and all its data. Always make sure you have a recent backup of your data before running destructive commands like
After creating a table, the next step is to begin interacting with it. SQL developers refer to these interactions as CRUD. CRUD stands for: Create, Read, Update, Delete.
These are the four basic operations you can perform on data in a database.
- Create: This is about adding new data to your database. For example, you might want to add a new customer to your
- Read: This is about getting or retrieving data from your database. For example, you might want to get all customers from the
- Update: This is about changing or modifying existing data in your database. For example, updating a customer's email address.
- Delete: This is about removing data from your database. For example, deleting a customer after they've decided to deactivate their account.
In summary, these CRUD operations form the basic actions you can perform on any data in a SQL database. It's like having a file cabinet where you can add new files, read the contents of files, change the contents of files, and throw away files you no longer need.
- In SQL databases, tables are structures used to organize and store related data. They can be thought of as individual drawers in a digital filing cabinet. Each table holds information on a specific topic and consists of rows and columns.
- Rows in a table represent individual records or entries. Columns represent different categories of data or attributes that you're storing. So, rows are the individual records, and columns are the types of data about each record.
- To create a table in SQL, you use the
CREATE TABLEcommand. This is followed by the table name and a list of column names with their data types.
- Each column in a table has a data type that determines the kind of data it can store, such as
VARCHAR(n)for variable-length strings,
DATEfor dates, etc.
- The basic operations you can perform on data in SQL are CRUD operations - Create, Read, Update, and Delete.
- To delete a table and all its data from a database, you use the
DROP TABLEcommand. This command should be used cautiously, as it permanently removes the table and its data from the database.