SQL Basic Commands

Introduction

SQL (Structured Query Language) is a domain-specific language used to manage data, especially in a relational database management system (RDBMS)

---

Let’s take a sample table named friends to illustrate each command. This table represents a list of friends including the following attributes: id, name, age and city.

CREATE DATABASE

Caution

These scripts assume that you have been able to install a MySQL database locally or establish a remote connection to one.

Most of the code blocks below must run within MySQL command line interface. It usually has mysql> string beforehand each command.

First of all, you must create the database.

CREATE DATABASE test;

USE test; # tells to use the newly created test DB

CREATE TABLE

Then, create the table you’ll work on.

CREATE TABLE command is used to create a new table. You must provide data type definition foreach column of the table.

CREATE TABLE IF NOT EXISTS friends (
  id   INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL COMMENT 'Name of the person',
  age  INT NOT NULL COMMENT 'Age of the person',
  city VARCHAR(25) NOT NULL COMMENT 'City where the person lives'
);

You can then take a look at the structure of the table with:

DESCRIBE friends;

It will output this:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
| age   | int         | NO   |     | NULL    |                |
| city  | varchar(25) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

INSERT

Now that you have an empty skeleton that describes the columns of the table, let’s add some values.

INSERT command is used to add new rows into a table. It lets specify values for each column, either partially or completely, depending on the table’s structure.

INSERT INTO friends (name, age, city) VALUES ('Pit', 25, 'Trento');

Let’s insert some more data and then visualize it:

INSERT INTO friends (name, age, city) VALUES ('Lisa', 20, 'Milano');
INSERT INTO friends (name, age, city) VALUES ('Luca', 18, 'Milano');
INSERT INTO friends (name, age, city) VALUES ('Adele', 22, 'Bologna');

SELECT

The SELECT command is used to retrieve specific data from one or more tables in a database.

You can also decide to select all the columns by using * (star sign) right after the SELECT command.

SELECT * FROM friends;

The output is:

+----+-------+-----+---------+
| id | name  | age | city    |
+----+-------+-----+---------+
|  1 | Pit   |  25 | Trento  |
|  2 | Lisa  |  20 | Milano  |
|  3 | Luca  |  18 | Milano  |
|  4 | Adele |  22 | Bologna |
+----+-------+-----+---------+

Let’s extract the data and show the list of friends using a little bit of HTML:

id	name	age	city
1	Pit	25	Trento
2	Lisa	20	Milano
3	Luca	18	Milano
4	Adele	22	Bologna

---

SELECT command allows you to choose particular columns, apply filters, and sort results as needed.

SELECT name, age FROM friends;

The output is:

+-------+-----+
| name  | age |
+-------+-----+
| Pit   |  25 |
| Lisa  |  20 |
| Luca  |  18 |
| Adele |  22 |
+-------+-----+

SELECT WHERE

The WHERE clause is used to:

1. filter records,
2. extract only those records that fulfill a specific condition.

SELECT name, age FROM friends WHERE city = 'Milano';

The output is:

+------+-----+
| name | age |
+------+-----+
| Lisa |  20 |
| Luca |  18 |
+------+-----+

---

The following operators can be used in the WHERE clause:

operator	description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

UPDATE

UPDATE command modifies existing data in a table. By specifying a condition, you can update one or multiple rows.

It is commonly used for correcting or altering record.

The following command will update Pit’s age:

UPDATE friends SET age = 26 WHERE name = 'Pit';

Once again, the WHERE clause is being used.

DELETE

DELETE command removes rows from a table based on a specified condition.

Unlike the DROP command, it does not delete the table structure: only the data within it.

If Adele is not your friend anymore, you can decide to delete her from your database:

DELETE FROM friends WHERE name = 'Adele';

If you run:

SELECT * FROM friends;

The output will now be:

+----+-------+-----+---------+
| id | name  | age | city    |
+----+-------+-----+---------+
|  1 | Pit   |  26 | Trento  |
|  2 | Lisa  |  20 | Milano  |
|  3 | Luca  |  18 | Milano  |
+----+-------+-----+---------+

Three friends left, seems you are an adult now, isn’t it?

---

It is possible to delete all records in a table, without deleting the table. This means that the table structure, attributes, and indexes will be intact.

DELETE FROM friends;

DROP TABLE

DROP TABLE statement is used to permanently delete an existing table in a database.

Danger

Be careful before dropping a table. It will delete the entire table and all its content!

DROP TABLE IF EXISTS friends;

However, in most databases you cannot drop a table that is referenced by a foreign key constraint in another table. To solve this, you must remove the foreign key constraint or drop the dependent table.

DROP DATABASE

DROP DATABASE statement is used to permanently delete an existing SQL database.

Danger

Be careful before dropping a database. It will delete the the database and all its content (tables, views, stored procedures, and data)!

DROP DATABASE test;

ALTER TABLE

ALTER TABLE statement is used to add, delete or modify columns in an existing table. It is also used to add or drop various constraints on an existing table.

Common ALTER TABLE operations are:

• add column,
• drop column
• rename column,
• modify column: changes the data type, size, or constraints of a column
• add constraint: adds a new constraint
• rename table: renames a table

ADD COLUMN

Let’s add a column named has_pet of type BOOL.

ALTER TABLE friends ADD has_pet BOOL;

If you run the usual SELECT * query, you’ll get all the values set to NULL:

+----+------+-----+--------+---------+
| id | name | age | city   | has_pet |
+----+------+-----+--------+---------+
|  1 | Pit  |  26 | Trento |    NULL |
|  2 | Lisa |  20 | Milano |    NULL |
|  3 | Luca |  18 | Milano |    NULL |
+----+------+-----+--------+---------+

You can now use UPDATE to fulfill has_pet column.

UPDATE friends SET has_pet = TRUE WHERE name = 'Pit';
UPDATE friends SET has_pet = 1 WHERE name = 'Lisa';
UPDATE friends SET has_pet = 0 WHERE name = 'Luca';

DROP COLUMN

Let’s drop a column:

ALTER TABLE friends DROP COLUMN has_pet;

RENAME COLUMN

Let’s rename a column:

ALTER TABLE friends RENAME COLUMN has_pet TO hasPet;

ORDER BY

ORDER BY clause sorts the result set of a query in ascending or descending order based on one or more columns.

This helps in organizing the output data effectively.

SELECT name, age FROM friends ORDER BY age DESC;

The output is:

+------+-----+
| name | age |
+------+-----+
| Pit  |  26 |
| Lisa |  20 |
| Luca |  18 |
+------+-----+

GROUP BY

GROUP BY clause groups rows that have the same values in specified columns into summary rows. It is often used with aggregate functions like COUNT, SUM, AVG, etc.