You’re new to programming or just avoided learning SQL before, then you have come to the right place, this is something the every new developer faces eventually. I hope this brief overview of the basic syntax of SQL-queries will help interested developers, and anyone who need it.
SQL Database, What is it?
Structured Query Language (SQL) – Standard communication with the database, which is basically supported by ANSI. The most recent version – SQL-99, although a new standard for SQL-200n is in the works. Most databases are firmly adheres to the ANSI-92. It was a lot of discussion about the introduction of more modern standards, but manufacturers of commercial databases deviate from this by developing its new concept manipulation stored data. Almost every single database uses a unique set of syntax, though very much like standard ANSI. In most cases, this syntax is an extension of a basic standard, although there are cases where the syntax leads to different results for different databases. Always a good idea to browse the documentation for the database, especially if unexpected results are obtained.
If you meet with SQL, you should be familiar with basic concepts that must be understood.
In general terms, «SQL database»” is the common name for a relational database management system (RDBMS). For some systems, “database” also refers to a group of tables, data, configuration data, which are an integral part of the individual from other, similar designs. In this case, each installation of SQL database can consist of several databases. In other systems, they are referred to as a table.
Table – the design of a database that consists of columns that contain string data. Usually tables are created to contain relevant information. Within the same database can be created several tables.
Each column represents an attribute or set of attributes of objects, such as employee identification numbers, height, color machines, etc. Often on the column uses the term field, including name, eg “in the «Name»”. Field line is a minimal element table. Each column in the table has a certain name, data type and size. Column names must be unique within a table.
Each row (or record) represents a set of attributes of a particular object, for example, the line may contain an identification number of the employee, his remuneration, his year of birth, etc. Rows of tables do not have names. To contact a specific line, the user must specify some attribute (or set of attributes) that uniquely identifies it.
One of the major operations that are performed on the data, the sample is stored in a database of information. To this end, the user must perform a query (query).
Now let’s consider the basic types of database queries that are focused on the manipulation of data within a database. For our purposes, all examples are in standard SQL, in order to meet any environment.
Types of data queries in SQL
There are four main types of data queries in SQL, which belong to the so-called data manipulation language (Data Manipulation Language or DML):
- SELECT – to select rows from the tables;
- INSERT – add row to the table;
- UPDATE – to change the line in the table;
- DELETE – delete rows in the table;
Each of these requests has various operators and functions that are used to produce some of the data. SELECT query has the largest number of options. There are also additional types of queries, used in conjunction with SELECT, such as JOIN and UNION. But until then, we’ll just focus on the main query.
Using a SELECT query to retrieve the necessary data
To obtain information stored in the database use the query SELECT. Basic steps of this query is limited to one table, although there are designs that provide a sample from several tables at once. In order to get all the rows of data for specific columns, use the query like this:
SELECT column1, column2 FROM table_name;
Also, you can get all the columns from a table using a wildcard character “*”:
SELECT * FROM table_name;
This can be useful in the case where you are going to choose the data with a certain condition WHERE. The following query returns all columns from all rows, where «column1» contains the value “3”:
SELECT * FROM table_name WHERE column1 = 3;
In addition to “=” (equal), the following conditional statements:
Conditional operators :
<> Not equal
= Greater or equal
<= Less than or equal to
Additionally, you can use the LIKE condition BETWEEN and for comparison with the condition WHERE, as well as combinations of AND and OR.
SELECT * FROM table_name WHERE ((Age> = 18) AND (LastName BETWEEN ‘Mac’ AND ‘Dale’)) OR Company LIKE ‘%eUKHost%’;
Which means: select all columns from a table table_name, where the column value is greater than or equal to age 18 as well as the value of the LastName column is in alphabetical range from Shane to Phillips, inclusive, or the value of the Company column is eUKHost.
Using the INSERT query to insert new data
INSERT query is used to create a new row of data. To update the existing data or blank fields should use the query string UPDATE.
Approximate syntax query INSERT:
INSERT INTO table_name (column1, column2, column3) VALUES (‘data1’, ‘data2’, ‘data3’);
If you’re going to insert all the values in the order in which there are columns in the table, we can not specify column names, although it is preferable for readability. In addition, if you list the columns do not necessarily indicate their order of being in the database until the values you enter correspond to this order. You do not need to list the columns in which no information is entered.
Modify existing information in the database are very similar.
UPDATE query and a WHERE clause
UPDATE is used to modify the existing value or free field in a row, so the new values must match an existing data type and provide acceptable values. If you do not want to change the values in all rows, then you need to use the condition WHERE.
UPDATE table_name SET column1 = ‘data1’, column2 = ‘data2’ WHERE column3 = ‘data3’;
You can use the WHERE clause for each column, including the one you want to change. It is used when it is necessary to replace one specific value to another.
UPDATE table_name SET FirstName = ‘Cannon’ WHERE FirstName = ‘Cannon’ AND LastName = ‘Bell’;
Note: The DELETE statement removes entire rows
The DELETE statement removes a row from the database. If you want to delete one single field, you must use the UPDATE query and set this field value, which would be the analogue of NULL in your program. Be careful and limit your request to DELETE condition WHERE, or you could lose all the contents of the table.
DELETE FROM table_name WHERE column1 = ‘data1’;
Once the line has been removed from your database, it can not be restored, it is desirable to have a column called «IsActive», or something like that, you can change it to zero, which would indicate a block of data from this line.
Now you know the basics of SQL database queries, and we’ve reviewed the most important and basic commands used in data queries. A set of basic concepts were not affected (SUM and COUNT for example), but the few commands mentioned above should lead you to action and a better understanding of the remarkable query language named SQL – the database language.