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.
For the latest version of MySql Workbench Click on the Server menu in the top menu bar Select 'Data Export' submenu It will open up the Object Selection window where you can select the appropriate database which has the desired tables. Executing a SELECT query will display the associated result set in the SQL View panel, directly below the SQL Query panel. These cells are editable if MySQL Workbench is able to determine how, as for example they are editable if a Primary or Unique key exists within the result set. The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected. INSERT INTO/ INSERT INTO SELECT with MySql Workbench. To select top 10 records, use LIMIT in MySQL. Let us first create a table − mysql create table DemoTable - ( - PageNumber text - ); Query OK, 0 rows affected (2.50 sec).
- 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 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
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
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
Mysql Workbench Select Statements
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
ORDER BY Clause Example
Mysql Workbench Select Schema
In this example we are organizing the table 'books' by the 'title' in alphabetical order;
| << Previous | Next >> |
