Basic SQL Commands Every Programmer Should Know

Note: MySQL commands are used for the examples. Thus, all the statements are terminated with semicolons.

Helps to view all the databases available

Allows creating a new database

Type USE <database_name> to chose a database to work with

Use SOURCE <file_name.sql> to import and execute several SQL commands from a .sql file

Use DROP DATABASE <database_name>to remove a complete database

SHOW TABLES

Allows viewing all tables available in a database

To create a new table, use:

1.CREATE TABLE <table_name1> (
2.<col_name1><col_type1>,
3.<col_name2><col_type2>,
4.<col_name3><col_type3>
5.PRIMARY KEY(<col_name1>),
6.FOREIGN KEY(<col_name2>)REFERENCES <table_name2>(<col_name2>)
7.);

In case you need to create constraints for specific columns in the table, set the following restrictions when creating the table:

1.a table cell cannot be NULL;

2.primary key – PRIMARY KEY (col_name1, col_name2, …);

3.foreign key – FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn).

More than one primary key can be specified. In this case, you get a composite primary key.

You can view various information (value type, key, or not) about the table columns using the DESCRIBE <table_name> command

To add data to a table, use:

1.INSERT INTO <table_name> (<col_name1>, <col_name2>, <col_name3>, …)
 2.VALUES (<value1>, <value2>, <value3>, …);

It is not necessary to specify the columns’ names when adding data to each column in the table:

1.INSERT INTO <table_name>
  2.VALUES (<value1>, <value2>, <value3>, …);

To update the table data, use:

1.UPDATE <table_name>
  2.SET <col_name1> = <value1>, <col_name2> = <value2>, ...
  3.WHERE <condition>;

Use DELETE FROM <table_name>; to delete data from the table

Using DROP TABLE <table_name>; deletes the entire table

Use SELECT to retrieve data from a specific table:

1.SELECT <col_name1>, <col_name2>, …
  2.FROM <table_name>;

Use this command to retrieve non-duplicate data only:

1.SELECT DISTINCT <col_name1>, <col_name2>, …
  2.FROM <table_name>;

Use WHERE <condition> to specify conditions in command:

1.SELECT <col_name1>, <col_name2>, …
  2.FROM <table_name>
  3.WHERE <condition>;

You can specify the following conditions in the command:

1. text or numerical values comparison
2. logical operations AND (and), OR (or), and NOT (negation)

This operator is often used with aggregate functions, such as COUNT, MAX, MIN, SUMand AVG, for groups of output values

1.SELECT <col_name1>, <col_name2>, …
  2.FROM <table_name>
  3.GROUP BY <col_namex>;

This keyword works exactly as WHERE does, except it is used with aggregate functions.

1.SELECT <col_name1>, <col_name2>, ...
  2.FROM <table_name>
  3.GROUP BY <column_namex>
  4.HAVING <condition>

This command is used for query results sorting by descending or ascending order. In case no sorting order is specified, ORDER BY sorts results in ascending order.

1.SELECT <col_name1>, <col_name2>, …
  2.FROM <table_name>
  3.ORDER BY <col_name1>, <col_name2>, … ASC|DESC;

Use BETWEEN to select data values from a specified range:

1.SELECT <col_name1>, <col_name2>, …
  2.FROM <table_name>
  3.WHERE <col_namex> BETWEEN <value1> AND <value2>;

Numeric, text, and dates values can be used here.

LIKE is used in WHERE to specify a search for a similar value template:

1.SELECT <col_name1>, <col_name2>, …
  2.FROM <table_name>
  3.WHERE <col_namex> LIKE <pattern>;

Multiple values ​​for the operator WHERE can be specified using IN:

1.SELECT <col_name1>, <col_name2>, …
  2.FROM <table_name>
  3.WHERE <col_namen> IN (<value1>, <value2>, …);

Use JOIN to link two or more tables with common attributes they have:

1.ELECT <col_name1>, <col_name2>, …
  2.FROM <table_name1>
  3.JOIN <table_name2>
  4.ON <table_name1.col_namex> = <table2.col_namex>;

VIEW provides the latest information that is listed in the database. It consists of rows and columns.

1.CREATE VIEW <view_name> AS
  2.SELECT <col_name1>, <col_name2>, …
  3.FROM <table_name>
  4.WHERE <condition>;

The following functions are commonly used aggregate ones:

1. COUNT(col_name) — reveals the current number of rows;
2. SUM(col_name) — shows the sum of the values ​​in a specified column;
3. AVG(col_name) — shows the average value in a specified column;
4. MIN(col_name) — shows the minimum value in a specified column;
5. MAX(col_name) — shows the maximum value in a specified column.

Nested subqueries are SQL queries that include expressions SELECT, FROM, and WHEREare nested within another query.