Each of us regularly faces and usesvarious databases. When we select an email address, we work with the database. Databases use search services, banks to store customer data, etc.
But, despite the constant use of databasesdata, even for many developers of software systems, there are many "white spots" because of the different interpretation of the same terms. We will give a brief definition of the main terms of databases before examining the SQL language. So.
Database - file or set of files to store ordereddata structures and their interrelationships. Very often a database is called a database management system (DBMS). The database is only a repository of information in a specific format and can work with different DBMSs.
Table - imagine the folder in which you storedocuments grouped by a specific feature, for example, a list of orders for the last month. This is the table in the computer database. A separate table has a unique name.
Data type - The type of information that can be stored in a separate column or row. It can be numbers or text of a certain format.
Column and String - we all worked with spreadsheets, which also contain rows and columns. Any relational database works with tables in a similar way. Rows are sometimes called records.
Primary key - each row of the table can have one or more columns for its unique identification. Without a primary key, it is very difficult to update, modify, or delete the required rows.
SQL query language (Eng. Structured Query Language) was designed only for working with databases and is currently the standard for all popular DBMSs. The syntax of the language consists of a small number of operators and is easy to learn. But, despite its external simplicity, it allows the creation of sql queries for complex operations with databases of any size.
Since 1992 there is a generally accepted standard called ANSI SQL. It defines the basic syntax and functions of operators and is supported by all the leaders of the database market, such as ORACLE Microsoft SQL Server. It is impossible to consider all the features of the language in one small article, so we will briefly consider only the basic SQL queries. Examples illustrate the simplicity and capabilities of the language:
All the columns in the database table store one data type. Data types in SQL are the same as in other programming languages.
| Data type | Description |
| INT | Whole numbers |
| REAL | Floating point numbers |
| TEXT | Character string with variable length |
| On DATE | sql query "date" in various formats |
| TIME | Time |
| CHAR | Text strings of fixed length |
You can create new databases, tables and other queries in SQL in two ways:
A new database is created by the operator CREATE DATABASE <name of the database>;. As you can see, the syntax is simple and concise.
We create the tables inside the database with the CREATE TABLE statement with the following parameters:
As an example, create a Commodity table with the following columns:
| Column | Description |
| commodity_id | Product ID |
| vendor_id | Vendor ID (external table key Vendors) |
| commodity_name | Product name |
| commodity_price | Cost |
| commodity_desc | Description |
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);
The table consists of five columns. After the name is a data type, the columns are separated by commas. The value of the column can be null (NULL) or must be filled (NOT NULL), and this is determined when the table is created.
The data fetch operator is the most commonly used SQL query. To obtain information, you need to specify what we want to select from such a table. First a simple example:
SELECT commodity_name FROM Commodity
After the SELECT statement, we specify the name of the column to retrieve the information, and FROM specifies the table.
The result of the query will be all linestables with the values of Commodity_name in the order in which they were entered in the database ie. without any sorting. To order the result, use the optional ORDER BY clause.
To query multiple fields, we list them with a comma, as in the following example:
SELECT commodity_id, commodity_name, commodity_price FROM Commodity
It is possible to get the value of all the columns of the string as a result of the query. To do this, use the "*" sign:
SELECT * FROM Commodity
To add a row to the table, use SQL queries with the INSERT statement. Adding can be done in three ways:
To add a complete line, you must specify the table name and column (s) of the new row. Let's give an example:
INSERT INTO Commodity VALUES ("106", "50", "Coca-Cola", "1.68", "No Alcogol,)
The example adds a new product to the table. Values are specified after VALUES for each column. If there is no corresponding value for the column, then you must specify NULL. Columns are filled with values in the order specified when creating the table.
In the case of adding only a portion of the string, you must explicitly specify the column names, as in the example:
INSERT INTO Commodity (commodity_id, vendor_id, commodity_name)
VALUES ("106", '50 "," Coca-Cola ",)
We entered only the identifiers of the goods, the supplier and its name, and the remaining fields were left blank.
Basically, INSERT is used to add strings, but can also be used to add SELECT statement results.
To change the information in the fields of the database table, you must use the UPDATE statement. The operator can be used in two ways:
UPDATE consists of three main elements:
Let's consider an example. Let's admit, at the goods with ID = 106 cost has changed, therefore this line needs to be updated. Write the following statement:
UPDATE Commodity SET commodity_price = "3.2" WHERE commodity_id = "106"
We specified the name of the table, in our case the Commodity, where the update will be made, then after SET, the new value of the column and find the desired record, indicating the WHERE value in the WHERE clause.
To change multiple columns after the SET statement, specify several column-value pairs, separated by commas. We are looking at an example in which the name and price of a product are updated:
UPDATE Commodity SET commodity_name = 'Fanta', commodity_price = "3.2" WHERE commodity_id = "106"
To delete information in a column, you can assignit is NULL if the table structure allows it. It must be remembered that NULL is precisely "no" value, and not zero in the form of text or number. Delete the product description:
UPDATE Commodity SET commodity_desc = NULL WHERE commodity_id = "106"
SQL requests to delete rows in the table are performed by the DELETE statement. There are two uses:
Example of deleting one row from a table:
DELETE FROM Commodity WHERE commodity_id = "106"
After DELETE FROM, specify the name of the table, in thewhich will be deleted rows. The WHERE clause contains a condition by which to select the rows to be deleted. In the example, we delete the item line with ID = 106. It is very important to specify WHERE. skipping this operator will result in the removal of all rows in the table. This also applies to changing the value of the fields.
The DELETE statement does not specify the column names and metacharacters. It completely removes lines, and it can not delete a single column.
Microsoft Access is commonly used ininteractive mode for creating tables, databases, for managing, modifying, analyzing data in the database and for implementing SQL Access queries through a convenient interactive Query Designer, using which you can build and immediately execute SQL statements of any complexity.
The server access mode is also supported, withwhich DBMS Access can be used as a SQL query generator to any ODBC data source. This feature allows Access applications to interact with databases of any format.
Because SQL queries do not have all the capabilitiesprocedural programming languages such as loops, branchings, etc., DBMS vendors are developing their own version of SQL with advanced capabilities. First of all, this is support for stored procedures and standard procedural language operators.
The most common dialects of the language are:
The MySQL database is distributed under freethe GNU General Public License. There is a commercial license with the possibility of developing custom modules. As an integral part of the most popular assemblies of Internet servers, such as XAMPP, WAMP and LAMP, and is the most popular database for developing applications on the Internet.
It was developed by Sun Microsystems andcurrently supported by Oracle. It supports databases of up to 64 terabytes, the SQL standard syntax: 2003, database replication and cloud services.
</ p>
