How to write SQL-queries - detailed examples

Each of us faces on a regular basis and uses different databases.When we choose e-mail, we are working with the database.Databases used search services, banks for the storage of customer data, etc.

But despite the constant use of the database, even for many developers of software systems is a lot of "white spots" because of different interpretations of the same terms.We give a brief definition of key terms database before consideration of SQL.So.

database - file or set of files for storing ordered data structures and their relationships.Very often, the database is called database management system (DBMS).Database - this is just a repository of information in a specific format and can work with different databases.

Table - imagine the folder in which documents are stored, grouped by certain attributes such as a list of orders in the last month.This is a table in a computer database.A separate table has its own unique name.

data type - kind of information allowed to be stored in a single column or row.It may be specific numbers or text format.

column and row - we all worked with spreadsheets, which are also present in the rows and columns.Any relational database tables works similarly.The lines are sometimes called records.

primary key - each row may have one or more columns for its unique identification.No primary key is very difficult to perform the update, modify, and delete the required rows.

What is SQL?

query language SQL (Eng. Structured Query Language - Structured Query Language) was designed only to work with databases and is currently the standard for all popular databases.Syntax consists of a small number of operators and easy to learn.But despite the apparent simplicity, it allows the creation of sql queries for complex database operations of any size.

Since 1992, there is a common standard, called ANSI SQL.It defines the basic syntax and functions of operators and supported by all DBMS market leaders such as ORACLE Microsoft SQL Server.Consider all the possibilities of language in a short article is impossible, so we will briefly consider only the basic SQL statements.Examples demonstrate the simplicity and features of the language:

• creating databases and tables;
• sample data;
• adding records;
• modification and deletion of information.

Data Types SQL

All columns in a database table store the same data type.SQL data types are the same as in other programming languages.

create spreadsheets and databases

create new databases, tables, and other requestsin SQL There are two ways:

• SQL statement in the console database
• Using interactive administration tools that make up the database server.

create a new database operator CREATE DATABASE & lt; name of the database & gt ;;.As you can see, the syntax is simple and laconic.

tables within the database create --CREATE TABLE statement with the following parameters:

• table name
• names and column data types

As an example, create a table Commodity with the following columns:

create the table:

--CREATE TABLE Commodity

(commodity_id CHAR (15) NOT NULL,

vendor_id CHAR (15) NOT NULL,

commodity_name CHAR (254) NULL,

commodity_price DECIMAL (8,2) NULL,

commodity_desc VARCHAR (1000) NULL);

table consists of five columns.After the name is the type of data columns separated by commas.The value of the column can accept null values ​​(NULL), or must be filled in (NOT NULL), and it is determined to create the table.

sample data from the table

operator data sampling - the most commonly used SQL statements.For information you need to specify that we want to select from a table.First, a simple example:

SELECT commodity_name FROM Commodity

After the SELECT statement to specify the name of the column to get information, and determines FROM table.

result of the query will be all rows with values ​​Commodity_name in the order in which they were entered into the database that iswithout any sorting.To organize the results using an additional operator ORDER BY.

To query on multiple fields list them separated by commas, as in the following example:

SELECT commodity_id, commodity_name, commodity_price FROM Commodity

is possible to obtain as a result of the request of all the columns line.For this purpose, the sign «*»:

SELECT * FROM Commodity

• Additionally SELECT supports:
• sort data (operator ORDER BY)
• Choice according to the conditions (WHERE)
• grouping term (GROUP BY)

add rows

To add rows to a table using SQL statements with the operator INSERT.The addition can be done in three ways:

• add a new whole line;
• of the line;
• query results.

to add the complete line must specify the name of the table and the values ​​of columns (fields) of the new line.Here is an example:

INSERT INTO Commodity VALUES ('106', '50', 'Coca-Cola', '1.68', 'No Alcogol,)

example adds a table of new goods.The values ​​are specified after VALUES statement for each column.If there is no corresponding value for a column, you must specify NULL.The columns are filled with the values ​​in the order specified when creating a table.

If you add only a portion of the line, you must explicitly specify the name of the column, as in the example:

INSERT INTO Commodity (commodity_id, vendor_id, commodity_name)

VALUES ('106', '50', 'Coca-Cola',)

We have introduced only product identifiers, supplier and its name, and put aside the rest of the field blank.

Adding query results

mainly INSERT is used to add strings, but can be used to add the results of the operator SELECT.

Changing Data

To change the information in the fields of the database table you want to use the operator UPDATE.The operator can be used in two ways:

• updates all rows in the table.
• only a certain line.

UPDATE consists of three main elements:

• table in which you want to make changes;
• field names and their new values;
• conditions to select rows to change.

Consider an example.Suppose goods with ID = 106 has changed the price, so this line to update.We write the following statement:

UPDATE Commodity SET commodity_price = '3.2' WHERE commodity_id = '106'

We specify the table name, in our case, Commodity, which will be updated, and then after the SET - the new value of the column and find the desired entryby specifying the desired value in WHERE ID.

To change the number of columns after the SET indicated a few pairs of the column value, separated by commas.See an example in which updated the name and price of the goods:

UPDATE Commodity SET commodity_name = 'Fanta', commodity_price = '3.2' WHERE commodity_id = '106'

To delete the information in the column, you can set it to NULL, ifIt allows the structure of the table.Keep in mind that NULL - this is "no" value, not zero as text or numbers.Delete the product description:

UPDATE Commodity SET commodity_desc = NULL WHERE commodity_id = '106'

Deleting rows

SQL queries to delete rows in the table are carried out by the operator DELETE.There are two ways to use:

• table deleted certain lines;
• deletes all rows in the table.

Example of deleting a row from the table:

DELETE FROM Commodity WHERE commodity_id = '106'

After DELETE FROM specify the name of the table where rows will be deleted.The WHERE clause contains a condition on which the line will be selected for deletion.In the example we remove the line item with ID = 106.Specify WHERE very important becauseprivedt pass this statement to delete all rows in a table.This applies to change the field values.

The DELETE statement does not specify column names, and meta-characters.It completely removes the row and column to delete an individual he can not.

Using SQL in Microsoft Access

Microsoft Access is usually used interactively to create a spreadsheet, database, management changes, the analysis of data in the database and in order to implement the queries SQL Access via a convenient interactive query builder (Query Designer), using which you can build and immediately execute SQL statements of any complexity.

also supported regime of access to the server, where the Access database can be used as a generator of SQL-queries to any ODBC data sources.This feature allows the Access applications interact with databases of any size.

Extensions SQL

Because SQL queries do not have all the features of procedural programming languages, such as loops, branching, etc., manufacturers are developing a database version of the SQL enhanced.The first is support for stored procedures and standard operators procedural languages.

most common dialects:

• Oracle Database - PL / SQL
• Interbase, Firebird - PSQL
• Microsoft SQL Server - Transact-SQL
• PostgreSQL - PL / pgSQL.

SQL Internet

MySQL database is released under a free license GNU General Public License.There is a commercial license with the ability to develop custom modules.As part of the most popular part of the build Internet servers such as XAMPP, WAMP and LAMP, and is the most popular database application development on the Internet.

was developed by Sun Microsystems and is currently supported by Oracle.Supports database size up to 64 terabytes, the standard SQL: 2003 syntax, database replication, and cloud services.