Creating Tables

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 customers table:

CREATE TABLE customers (
  id BIGINT(20),
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(255),
  phone VARCHAR(15)
);

In this statement:

• CREATE TABLE is the command used to create a new table.
• customers is the name of the table.
• Each line inside the parentheses defines a column in the table with a data type.
• A ; 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.

Data Types

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. n represents the maximum length.
• DECIMAL(M,N): For exact numerical values. M is the total number of digits, and N is the number of digits after the decimal point.
• VARCHAR(n): For strings of variable length. n represents the maximum length.
• TEXT: For longer text strings.
• DATE: For date values in YYYY-MM-DD format.
• DATETIME: For date and time values in YYYY-MM-DD HH:MM:SS format.
• BOOLEAN or TINYINT(1): For boolean values (TRUE or FALSE).
• FLOAT(M,D): For floating point numbers. M is the total number of digits, and D is 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:

column_name data_type

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/

Dropping Tables

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 DROP TABLE.

CRUD Operations

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 customers table.
• Read: This is about getting or retrieving data from your database. For example, you might want to get all customers from the customers table.
• 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.

Key Takeaways

• 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 TABLE command. 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 INT for integers, VARCHAR(n) for variable-length strings, DATE for 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 TABLE command. This command should be used cautiously, as it permanently removes the table and its data from the database.