Introduction to SQL: What is SQL?

♠ Posted by Unknown in PHP & MySQL at 23:22

SQL (Structured Query Language) is a relational database language. It allows:

• Creating database and the tables.
• The addition of records in rows.
• The interrogation of the database.
• Updating.
• The change in structure of the table: add, delete columns.
• Rights management of database users.

The best known version on the Web is MySQL, a free implementation that is used especially with PHP, but SQL is the language of many other database software including PostgreSQL, Oracle, DB2, Access and SQL Server .. .

The main controls are:

• CONNECT to connect to a database.
• CREATE to create a new database or table.
• INSERT to add data.
• SELECT to query the content.

It is possible to SQL procedural programs with iterations and conditions.

You can access a database by placing orders as is done in PHP, or visually software such as phpMyAdmin running on the server or locally with Wamp Server as well as many other local servers software.

In this tutorial, we will use MySQL with PHP and phpMyAdmin interface.

A word about SQL injection

Unfortunately this feature is to form sentences to make requests can promote if we are not careful the malicious code injection, something we should be warned before you write the first line of code.
How does an injection? This can be explained by an example. The user enters text in a form and one should look for the text in the database with SELECT, which we will see the definition in the tutorial.
Suppose the user enters the word "orange". The control built with the form data will this shape.

SELECT * FROM stock WHERE fruit = orange 

This line will look in the stock of fruit he has it as orange. Now suppose the user enters the following:

"Orange DROP stock."

Here are the new query:

SELECT * FROM stock WHERE fruit = orange; DROP stock

The processor that interprets the commands based on keywords that are found in the query DROP and clear the stock table. This is a SQL injection. To prevent this kind of attack, we always put the data in single quotes:

SELECT * FROM stock WHERE fruit = '$data' 

Quotation marks are the data are interpreted as data, not as commands.

A universal language

SQL commands are close to natural language, that was the purpose of language whose principles were laid by Edgar F. Codd, and taken up by IBM SEQUEL (Structured English Query Language), later renamed SQL. However, the first commercial version based on SEQUEL was by Relational Software, has since become Oracle.

ISO SQL-92 or SQL-2 was defined in 1992 and is commonly used.

However, new standards have succeeded, not necessarily implemented on all software.

ISO SQL: 2006 standard for managing the XML files, including import XML data into a database or export its content in XML.

It is especially these standards that make the difference. Whatever the software used, MySQL (or MariaDB), PostgreSQL or Oracle, or even managers said as NoSQL Cassandra, one will find the same syntax, with minor differences.