Video Tutorial 4 SQL. DDL AND DML. SELECT FROM WHERE AND ORDER BY STATEMENT with Mysql Workbench.

DDL AND DML

SQL statements are divided into two major categories: data definition language (DDL) and data manipulation language (DML).

Data definition language (DDL)

DDL statements are used to build and modify the structure of your tables and other objects in the database.

• CREATE - to create objects in the database

• ALTER - alters the structure of the database

• DROP - delete objects from the database

• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

Data Manipulation Language (DML)

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database

• INSERT - insert data into a table

• UPDATE - updates existing data within a table

• DELETE - deletes all records from a table, the space for the records remain

• MERGE - UPSERT operation (insert or update)

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SQL SELECT Syntax 1

 SELECT column_name,column_name
FROM table_name;
 

SELECT Example

In the following example we ask for the "title", "author" and "isbn" of all the registers of the table "books". Notice that SQL is not case sensitive. We can write SQL both in upper case or lower case.

SQL SELECT Syntax 2

and

SELECT * FROM table_name;

The * means we are asking for all the columns of the table.

SELECT * FROM Clause Example

Below we can see the result of the SELECT * FROM eli.publisher; all the information of the table "publisher";

Notice we are using indistinctly the command line interface and the workbench. It is important you get familiar with the command line interface, so that you can use it if you need to.

The SQL WHERE Clause 

The WHERE clause is used to extract only those records that fulfill a specified criterion. It works as a filter.

SQL WHERE Syntax

  SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

WHERE Clause Example

In this example we are asking for all the registers of the table "books" which have as an "idpublisher"=1. Only if this condition is true we are getting a result;

The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by one or more columns.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

  SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;
  

ORDER BY Clause Example

In this example we are organizing the table "books" by the "title" in alphabetical order;

<< Previous

Next >>