ComputersProgramming

Basic SQL statements

The SQL standard was adopted in 1992 and is still in use. It was he who became the benchmark for many database management systems. Of course, some manufacturers use their interpretation of the standard. But in any system, there are the main components - SQL statements.

Introduction

With the help of SQL statements in databases, the values, tables and data are managed and received for further analysis and display. They are a set of keywords by which the system understands what to do with the data.

Define several categories of SQL statements:

  • Definition of database objects;
  • Value manipulation;
  • Protection and management;
  • Session settings;
  • Information about the base;
  • Static SQL;
  • Dynamic SQL.

SQL statements for manipulating data

This category includes keywords, with which you can manage the placement of values in the database.

INSERT. Inserts rows into an existing table. It can be used for either one value or several, defined by a certain condition. For example:

INSERT INTO

Table name (column name 1, column name 2)

VALUES (value 1, value 2).

To use the SQL query operator INSERT for several values, the syntax is:

INSERT INTO

Table name 1 (column name 1, column name 2)

SELECT column name 1, column name 2

FROM table name 2

WHERE table name 2. column name 1> 2

This query will select all the data from Table 2 that is greater than 2 by column 1 and inserts them into the first.

UPDATE. As the name implies, this SQL query operator updates the data in an existing table for a specific characteristic.

Example:

UPDATE table name 1

SET column name 2 = "Basil"

WHERE table name 1. column name 1 = 1

This construction will fill the Basil value with all the lines in which it encounters the number 1 in the first column.

DELETE. Removes data from the table. You can specify a condition or remove all rows.

DELETE FROM table name

WHERE table name.column name 1 = 1

The above query will remove all data from the database with a value of one in the first column. And here's how you can clear the entire table:

DELETE FROM the name of the table.

Further it is necessary to tell about operator SELECT. He is one of the most important, so he will have to devote a separate chapter.

SELECT statement

The main purpose of SELECT is to retrieve data for certain conditions. The result of his work is always a new table with the selected data. The MS SQL SELECT statement can be used in a multitude of different queries. Therefore, along with it, you can consider other related keywords.

To select all the data from a certain table, use the "*" sign.

SELECT *

FROM table name 1

The result of this query will be an exact copy of Table 1.

And here is a sample by the condition WHERE, which extracts from Table 1 all values greater than 2 in column 1.

SELECT *

FROM table name 1

WHERE table name 1. column name 1> 2

You can also specify in the selection that only certain columns are needed.

SELECT table name 1. column name 1

FROM table name 1

The result of this query will be all rows, with values from column 1. Using MS SQL statements, you can create your own table, replacing, calculating and substituting certain values on the move.

SELECT

Table name 1. column name 1

Table name 1. column name 2

Table name 1. column name 3

«=» AS EQ

Table name 1. column name 2 * table name 1. column name 3 AS SUMMA

FROM table name 1

This seemingly complex query selects all the values from Table 1, then creates new columns of EQ and SUMMA. The first one puts the sign "+", in the second product of the data from columns 2 and 3. The result can be presented in the form of a table to understand how it works:

Column 1

Column 2

Column 3

EQ

SUMMA

Product 1 Name

10

50

+

500

Product name 2

15

100

+

1500

When using the SELECT statement, you can immediately organize the ordering of data by some kind of characteristic. To do this, use the word ORDER BY.

SELECT

Table name 1. column name 1

Table name 1. column name 2

Table name 1. column name 3

FROM table name 1

ORDER BY column name 2

The resulting table will look like this:

Column 1

Column 2

Column 3

1

1

54

3

2

12

7th

3

100

2

5

1

That is, all rows have been set in the order that in column 2 the values go in ascending order.

Data can also be obtained from several tables. For clarity, you first need to imagine that there are two of them in the database, roughly the following:

Employees table

room

Name

Surname

1

Vasya

Vasin

2

Peter

Petin

Table "Salary"

room

Rate

Charged

1

1

10000

2

0.5

3500

Now you need to somehow link these two tables to get common values. Using the basic SQL statements, you can do this:

SELECT

Employees.The number

Employees.The name

Salary.Stavka

Salary.No

FROM Employees, Salary

WHERE Employees.Number = Salary.Number

Here, a selection is made of two different value tables, grouped by number. The result is the following data set:

room

Name

Rate

Charged

1

Vasya

1

10000

2

Peter

0.5

3500

A little more about SELECT. Using aggregate functions

One of the main SQL SELECT statements can perform some computation in the sample. To do this, he uses certain functions and formulas.

For example, to get the number of entries from the Employees table, you need to use the query:

SELECT COUNT (*) AS N

FROM Employees

The result is a table with one value and a column.

N

2

In queries, you can use functions that calculate the sum, the maximum and minimum values, and the mean. For this, the keywords SUM, MAX, MIN, AVG are used.

For example, you need to sample from the already known table "Salary":

room

Rate

Charged

1

1

10000

2

0.5

3500

You can apply this query and see what happens:

SELECT

SUM (Salary.Specified) AS SUMMA

MAX (Salary.No) AS MAX

MIN (Salary.Specified) AS MIN

AVG (Salary.No) AS SRED

FROM Salary

The final table will be as follows:

SUMMA

MAX

MIN

SRED

13500

10000

3500

6750

In this way, you can select the required values from the database, on the fly performing the calculation of various functions.

Unification, intersection and differences

Merge multiple queries into SQL

SELECT Employees.Name

FROM Employees

WHERE Employees.Number = 1

UNION

SELECT Employees.Name

FROM Employees, Salary

WHERE Salary.Number = 1

It should be taken into account that with such a combination, the tables must be compatible. That is, have the same number of columns.

Syntax of the SELECT statement and the order of its processing

The first thing SELECT determines the area from which it will take data. The FROM keyword is used for this. If not specified, what exactly to choose.

Then there may be a SQL WHERE clause. With its help, SELECT runs through all rows of the table and checks the data for compliance with the condition.

If the query has GROUP BY, then the values are grouped according to the specified parameters.

Operators for comparing data

There are several types. In SQL, comparison operators can check different types of values.

  • «=». Denotes, as you might guess, the equality of two expressions. For example, it was already used in the examples above - WHERE Salary. Number = 1.

  • «>». The sign is bigger. If the value of the left side of the expression is greater, then the logical TRUE is returned and the condition is satisfied.

  • «<». The sign is smaller. Reverse the previous statement.

  • The signs "<=" and "> =". It differs from simple operators more and less, in that when the operands are equal, the condition will also be true.

  • «<>». Not equal. The condition will be considered TRUE only if one operand is not equal to another. He has one more interpretation - "! =".

LIKE

Translate this keyword as "similar". The LIKE statement in SQL is used approximately the same way - it performs a query on a template. That is, it allows you to extend the selection of data from the database using regular expressions.

For example, the following task has been set: from the already known "Employees" base, to get all people whose name ends with "I". Then the query can be written as follows:

SELECT *

FROM Employees

WHERE Name LIKE `% I`

The percent sign in this case means a mask, that is, any symbol and their number. And by the letter "I" SQL will determine that the last character should be exactly this.

CASE

This SQL Server statement is a multiple-choice implementation. It resembles the switch design in many programming languages. The CASE statement in SQL performs an action on several conditions.

For example, you need to select the maximum and minimum values from the "Salary" table.

room

Rate

Charged

1

1

10000

2

0.5

3500

Then the query can be written as follows:

SELECT *

FROM Salary

WHERE CASE WHEN SELECT MAX (Accrued) THEN Maximum

WHEN SELECT MIN (Accrued) THEN Minimum

END total

In this context, the system looks for the maximum and minimum value in the "Accrued" column. Then, using END, the "total" field is created, to which the "Maximum" or "Minimum" will be recorded, depending on the outcome of the condition.

By the way, in SQL there is also a more compact form CASE - COALESCE.

Data Definition Operators

This view allows you to make various changes to tables - creating, deleting, modifying and working with indexes.

The first one to consider is CREATE TABLE. It does nothing but create a table. If you just type CREATE TABLE, nothing happens, since you still need to specify a few parameters.

For example, to create an already familiar "Employees" table, you would use the following commands:

CREATE TABLE Employees

(Number number (10) NOT NULL

The name varchar (50) NOT NULL

Surname varchar (50) NOT NULL)

In this query, the field names and their types are immediately defined in parentheses, and also whether it can be NULL.

DROP TABLE

Performs one simple task - deleting the specified table. Has an additional parameter IF EXISTS. It absorbs an error when it is deleted, if the table does not exist. Example of use:

DROP TABLE Employees of IF EXISTS.

CREATE INDEX

SQL has an index system that allows you to speed up data access. In general, it is a reference that points to a specific column. Create an index can be a simple query:

CREATE INDEX index_name

ON tbl_name (column_name)

This operator is used in T-SQL, Oracle, PL SQL and many other interpretations of technologies.

ALTER TABLE

A very functional operator with many options. In general, it makes a change in the structure, definition and placement of tables. The operator is used in Oracle SQL, Postgres and many others.

Below you will find various options for using ALTER TABLE.

  • ADD. Adds a column to the table. Its syntax is as follows: ALTER TABLE tbl_name ADD column_name data_type_type. Can have the parameter IF NOT EXISTS, which suppress the error if the column being created already exists;

  • DROP. Deletes the column. It also has an IF EXISTS key, without which an error is generated indicating that the required column is missing;

  • CHANGE. Click to rename the field name to the specified field. Example usage: ALTER TABLE tbl_name CHANGE old_name new_name;

  • MODIFY. This command will help to change the type and additional attributes of a particular column. And it is used like this: ALTER TABLE tbl_name MODIFY column_name data_type attributes;

CREATE VIEW

In SQL, there is such a thing as representation. In short, this is a kind of virtual table with data. It is formed as a result of sampling using the SQL SELECT statement. Views can restrict access to the database, hide them, replace the actual column names.

The process of creating is done with a simple query:

CREATE VIEW view name AS SELECT FROM * table name

The sample can occur both the entire database as a whole, and for some condition.

A bit about functions

SQL queries often use various built-in functions that allow you to interact with data and convert them on the fly. It is worth considering them, as they form an integral part of the structured language.

  • COUNT. Counts records or rows in a specific table. As a parameter, you can specify a column name, then the data will be taken from it. SELECT COUNT * FROM Employees;

  • AVG. This function only applies to columns with numeric data. Its result is the determination of the arithmetic mean of all values;

  • MIN and MAX. These functions have already been used in this article. They determine the maximum and minimum values from the specified column;

  • SUM. It's simple - the function calculates the sum of the column values. It is used exclusively for numeric data type. By adding DISTINCT to the query, only unique values will be added;

  • ROUND. Function of rounding of decimal fractional numbers. The syntax uses the column name and the number of decimal places;

  • LEN. A simple function that calculates the length of the column values. The result is a new table with the number of characters;

  • NOW. This keyword is used to calculate the current date and time.

Additional operators

Many examples with SQL statements have keywords that perform small tasks, but nevertheless greatly simplify the sampling or actions with databases.

  • AS. It is used when you need to visualize the result visually, assigning the specified name to the resulting table.

  • BETWEEN. Very convenient tool for sampling. It indicates the range of values among which you want to retrieve data. The input accepts a parameter from and to which number the range is used;

  • NOT. The operator gives the opposite of the expression.

  • TRUNCATE. Removes data from the specified database portion. It differs from similar operators in that it is impossible to recover data after its use. It should be noted that the implementation of this keyword in different interpretations of SQL may be different. Therefore, before trying to use TRUNCATE, it is better to get acquainted with the reference information.

  • LIMIT. Sets the number of lines to output. The peculiarity of the operator is that he is always located at the end. It takes one mandatory parameter and one optional parameter. The first one specifies how many rows with the selected data should be shown. And if the second is used, then the operator is triggered both for the range of values.

  • UNION. A very convenient operator for combining multiple requests. He already met among the examples of this in this article. You can display the necessary rows from several tables, combining their UNION for more convenient use. The syntax is: SELECT column_name FROM tbl_name UNION SELECT name_of_other_column FROM_name of the other table. The result is a summary table with merged queries.

  • PRIMARY KEY. Translated as "primary key". Actually, such terminology is used in reference materials. It means a unique identifier for the string. It is used, as a rule, when creating a table to specify a field, which will contain it.

  • DEFAULT. Just like the previous statement, it is used during the execution of the creating query. It specifies the default value that the field will fill when creating it.

A few tips for developing a platform for working with SQL

  1. NULL. Beginners and not only programmers often forget about the possibility of getting a NULL value when creating queries. As a result, an error creeps in the code, which is difficult to track during debugging. Therefore, when creating tables, selecting or recalculating values, you need to stop and think, and whether the occurrence of NULL in this part of the query is taken into account.

  2. Memory. This article showed several functions that can perform some tasks. When developing a shell to work with the database, you can "outweigh" the calculation of simple expressions on the database management system. In some cases, this gives a significant increase in performance.

  3. Restrictions. If you need to get just two from a database with thousands of lines, then you should use operators like LIMIT or TOP. You do not need to extract the data using the shell development language.

  4. Compound. After receiving data from several tables, many programmers begin to bring them together by means of shell memory. But why? After all, you can make one request in which it will all be present. You do not have to write extra code and reserve additional memory in the system.

  5. Sorting. If there is an opportunity to apply ordering in the query, that is, the database engine, then you need to use it. This will significantly save on resources when running a program or service.

  6. Many requests. If you have to insert a lot of records sequentially, then for optimization, you should think about packet data insertion with one query. This will also increase the performance of the entire system.

  7. Thoughtful placement of data. Before drawing up the structure of the database you need to think about whether it is necessary to have so many tables and fields. Maybe there is a way to unite them or give up some. Very often, programmers use an excessive amount of data that will never be used anywhere.

  8. Types. To save space and resources, you need to be sensitive to the types of data used. If it is possible to use a less "heavy" memory type, then it is necessary to apply it. For example, if you know that in this field the numeric value will not exceed 255, then why use a 4-byte INT if there is TINYINT in 1 byte.

Conclusion

In conclusion, it should be noted that the language of structured SQL queries is now used almost everywhere - sites, web services, PC programs, applications for mobile devices. Therefore, knowledge of SQL will help all development industries.

At the same time, the modifications of the original standard of language sometimes differ from each other. For example, PL SQL statements may have a different syntax than in SQL Server. Therefore, before you start developing with this technology, it is worth reading the manuals on it.

In the future, analogs that could outperform the functionality and performance of SQL are unlikely to emerge, so this area is a pretty promising niche for any programmer.

Similar articles

 

 

 

 

Trending Now

 

 

 

 

Newest

Copyright © 2018 en.birmiss.com. Theme powered by WordPress.