SQL command for creating and deleting tables, adding data

MySQL Dec 28, 2022

Data in a database is stored in tables. To write data to the database, you must first create a table, add columns to it. And only then add data to these columns.

Create a table

Let's assume there is a database. And we want to write the data of site users into it. The data will be quite primitive:

Unique serial number of the user (identifier - id). Data type: integer
Username. Data type: string
Date added to the database. Data type: date and time

The ID column appears in almost every table in the database. It has a unique number, so it is often used to uniquely identify a row in a table. Anything can have an ID: user, news, product, publication.Let's try to create a table called USERS and these fields.

CREATE TABLE USERS (
   ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR(200),
   DATE DATETIME DEFAULT CURRENT_TIMESTAMP
);

Let's take an example line by line. On the first line, you can see the command with the telling name CREATE TABLE . It makes a request to create a table. It is followed by the name of the USERS table . The table name should contain only Latin letters (lowercase and uppercase) and underscores, but spaces and other characters should not be used.

After the name of the USERS table , parentheses are opened and the names of the columns with the data types in them and other options, separated by commas, follow:

ID - field of type INT (integer - INTEGER). After the data type comes "NOT NULL" - means that this column cannot be empty when adding a new row to the table, otherwise an error will appear. Then comes "PRIMARY KEY" - this inscription means that this column is a primary key, which means that its values are unique. This column has the AUTO_INCREMENT property , which means that when data is added to the table, the value in this column will automatically be set to one greater than the largest.
NAME - a field of type VARCHAR(200) - is plain text with a maximum of 200 characters. You can set any length in numbers (up to 255). The excess will be cut off if you try to add too long a line. If you set the field type to VARCHAR with a length of more than 255, then on older versions of MySQL an error may occur when creating a table, and new versions will simply treat this field as a TEXT type.
DATE is a field of DATETIME type. Contains date and time in base format, for example, "12/29/2022 01:22:31". The phrase "DEFAULT CURRENT_TIMESTAMP" means that when a new row is added to the table, the DATE column will take on the value of the current date - no need to pass the value in the query to the database. The "DEFAULT" parameter can be set for any data type, for example, for INT you can write "DEFAULT 5", then if you add a row to the database and do not specify a column value, then it will be assigned the value "5".

A semicolon is always placed at the end of an SQL query.

So, let's execute the SQL query from the example. Now you need to make sure the table is really created. To do this, we will make the following query to the database:

SHOW COLUMNS FROM `USERS`;

This command can be translated literally from English: "SHOW Columns FROM". This command is followed by the name of the table we just created. If the table was created successfully, then as a result of the query we will see a list of columns:

+-------+------+------+-----+------------- ------+----------------+
| field | type | Null | key | default | extra |
+-------+------+------+-----+------------- ------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(200) | YES | | NULL | |
| DATE | datetime | YES | | CURRENT_TIMESTAMP | |
+-------+------+------+-----+------------- ------+----------------+

From this result, we can see that all columns were created successfully.

Adding data to a table

After successfully creating the table, you can try to add the first data. Let's not waste time on trifles and add two lines of data at once:

INSERT INTO `USERS` SET NAME='Anurag';
INSERT INTO `USERS` SET NAME='Deep', DATE='2022-12-29 01:22:09';

The "INSERT INTO" command inserts data into the table whose name comes after it. In our case it is "USERS". Then comes the word "SET", after which the names of the columns in the table and their values to be inserted are listed, separated by commas.

Note that the order of the column names and values in the parentheses is important.

Clearing and deleting a table

Let's try to clear the table completely. Erase all the lines in it. To do this, we use the following SQL query:

TRUNCATE `USERS`;

This short query contains the "TRUNCATE" command and the name of our table "USERS". When such a command is executed, all rows in the table will be deleted. We can verify that the data was deleted correctly by calling the command:

SELECT * FROM `USERS`;

If there is not a single row in the table, then the deletion was successful. Now let's try to delete the table itself. Deleting a table is done with the following query: