What is sql language used for? SQL programming language

Welcome to my blog site. Today we’ll talk about sql queries for beginners. Some webmasters may have a question. Why learn sql? Isn't it possible to get by?

It turns out that this will not be enough to create a professional Internet project. Sql is used to work with databases and create applications for WordPress. Let's look at how to use queries in more detail.

What it is

Sql is a structured query language. Designed to determine the type of data, provide access to it and process information in short periods of time. It describes the components or some results that you want to see on the Internet project.

To put it simply, this programming language allows you to add, change, search and display information in the database. The popularity of mysql is due to the fact that it is used to create dynamic Internet projects that are based on a database. Therefore, to develop a functional blog, you need to learn this language.

What can it do

The sql language allows you to:

create tables;
change to receive and store various data;
combine information into blocks;
protect data;
create requests in access.

Important! Once you understand sql, you can write applications for WordPress of any complexity.

What structure

The database consists of tables that can be presented as an Excel file.

It has a name, columns and a row with some information. You can create such tables using sql queries.

What you need to know

Key Points to Learn Sql

As noted above, queries are used to process and enter new information into a database consisting of tables. Each line is a separate entry. So, let's create a database. To do this, write the command:

Create database 'bazaname'

We write the database name in Latin in quotes. Try to come up with a clear name for it. Do not create a database like “111”, “www” and the like.

After creating the database, install:

SET NAMES 'utf-8'

This is necessary for the content on the site to be displayed correctly.

Now let's create a table:

CREATE TABLE 'bazaname' . 'table' (

id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,

log VARCHAR(10),

pass VARCHAR(10),

date DATE

In the second line we wrote three attributes. Let's see what they mean:

The NOT NULL attribute means that the cell will not be empty (the field is required);
The AUTO_INCREMENT value is auto-completion;
PRIMARY KEY - primary key.

How to add information

To fill the fields of the created table with values, the INSERT statement is used. We write the following lines of code:

INSERT INTO 'table'

(login, pass, date) VALUES

('Vasa', '87654321', '2017-06-21 18:38:44');

In brackets we indicate the names of the columns, and in the next - the values.

Important! Maintain consistency in column names and values.

How to update information

To do this, use the UPDATE command. Let's see how to change the password for a specific user. We write the following lines of code:

UPDATE 'table' SET pass = '12345678' WHERE id = '1'

Now change the password ‘12345678’. Changes occur in the line with “id”=1. If you do not write the WHERE command, all lines will change, not a specific one.

I recommend that you purchase the book " SQL for dummies " With its help, you can work professionally with the database step by step. All information is structured according to the principle from simple to complex, and will be well perceived.

How to delete an entry

If you wrote something wrong, correct it using the DELETE command. Works the same as UPDATE. We write the following code:

DELETE FROM 'table' WHERE id = '1'

Sampling information

To retrieve values from the database, use the SELECT command. We write the following code:

SELECT * FROM 'table' WHERE id = '1'

In this example, we select all available fields in the table. This happens if you enter an asterisk “*” in the command. If you need to select some sample value, write this:

SELECT log , pass FROM table WHERE id = '1'

It should be noted that the ability to work with databases will not be enough. To create a professional Internet project, you will have to learn how to add data from a database to pages. To do this, familiarize yourself with the PHP web programming language. It will help you with this cool course by Mikhail Rusakov .

Deleting a table

Occurs using a DROP request. To do this, we will write the following lines:

DROP TABLE table;

Displaying a record from a table based on a specific condition

Consider this code:

SELECT id, countri, city FROM table WHERE people>150000000

It will display records of countries with a population of more than one hundred and fifty million.

An association

It is possible to link several tables together using Join. See how it works in more detail in this video:

PHP and MySQL

Once again I want to emphasize that requests when creating an Internet project are commonplace. To use them in PHP documents, follow the following algorithm:

Connect to the database using the mysql_connect() command;
Using mysql_select_db() we select the desired database;
We process the request using mysql_fetch_array();
Close the connection with the mysql_close() command.

Important! Working with a database is not difficult. The main thing is to write the request correctly.

Beginner webmasters will think about it. What should you read on this topic? I would like to recommend Martin Graber's book " SQL for mere mortals " It is written in such a way that beginners will understand everything. Use it as a reference book.

But this is a theory. How does this work in practice? In reality, an Internet project must not only be created, but also brought to the TOP of Google and Yandex. The video course will help you with this “ Website creation and promotion ».

Video instruction

Still have questions? Watch the online video for more details.

Conclusion

So, figuring out how to write sql queries is not as difficult as it seems, but any webmaster needs to do this. The video courses described above will help with this. Subscribe to my VKontakte group to be the first to know when new interesting information appears.

Today, at almost every step you can find the use of databases: online job search, ticket booking, accounting systems. At the beginning of the use of relational databases, users needed to know in detail the organization of data storage, as well as step-by-step instructions not only on what data was needed, but also how to obtain it.

For example, you came to the accounting department for a certificate of income. In the first case, you yourself indicate which salary books you need to look at, where they are stored, how to issue a certificate, and so on. In another case, you indicate your last name and the period for which you need a certificate; the accountant knows how to do the rest.

To achieve such “ease” of using databases, specialists needed to create a universal data manipulation language. It became, de facto, the non-procedural language SQL - Structured Query Language (which means “Structured Query Language”). The main advantage of SQL statements is that they focus more on the result (final) of data processing than on the procedure of this processing itself.

Of course, behind the desire to do something good there was also a commercial interest. It was a time (the seventies) of a sharp expansion in the range of users, possible markets for computers and a variety of software, provoked by a decrease in energy consumption, as well as the size and cost of the computers themselves.

What is SQL?

In 1974, IBM developed a special language SEQUEL (Structured English Query Language) for the experimental relational DBMS System R. Later, when it came to entering the global market, it was renamed SQL and went through a standardization procedure (1987). Today SQL is the most common, universal computer language for managing, creating, and modifying data in client-server databases.

SQL is just a language (a set of operators, calculated functions and instructions) used to “communicate” with a DBMS; in itself it is neither a database nor a separate product, but at the same time they are inseparable.

Advantages and Disadvantages of SQL

SQL has existed for more than 30 years, continues to develop, strengthens its position, thanks to its main advantages: independence from a specific DBMS, the presence of standards, declarativeness, the ability to create interactive queries, support for client-server architecture.

Based on its advantages, the opinion is formed: “Since most of the queries are written in SQL, then it doesn’t matter what kind of DBMS it is - it would be SQL.”

We must not forget about the disadvantages, among which are:

complexity- from its creation to today, SQL has gone from a “non-procedural language” to a “programmer’s tool”;
deviation from standards- the presence of SQL dialects with the lack of a complete (one manual) description of the language;
inconsistency with the relational data model- columns without a name and duplicate column names, duplicate lines, high redundancy.

Therefore, researchers are working not only to correct errors, but also to consider alternative options for creating a new language that will be “truly relational.”

The birth of SQL was sparked by a new generation of databases based on relational algebra. There is a lot of money invested in it by investors and users, so if there is a need, it needs to be taught. In this section, we will try to cover the main points of using SQL, show you how to correctly create queries, and demonstrate specific examples.

Section: SQL programming › List of articles

An article about the history of the emergence and implementation of the SQL query language, as well as who was the progenitor of this language

Listed are 12 basic rules for building databases, according to which databases should be built

Description of the basics of data representation in databases, methods of recording access to data in various database systems

An article about the history of the SQL query language, the history of standardization and the emergence of various versions of the standard

Describes what levels of compliance there are with SQL standards, as well as the requirements that must be met to comply with the standard

What is additional functionality, its compliance with the standard, who approved the additional functionality

A description of what is included in the various parts of the SQL standard description, as well as a breakdown of the component parts

Describes the basis for describing metadata that is stored in a special schema, the interaction of programming languages for using metadata

Describes what groups, i.e. classes, the basic instructions for processing data in databases are divided into

Describes the main differences in writing databases (dialects), their differences, advantages and disadvantages

The article describes the basic rules for creating identifiers, as well as the reasons for the appearance of these rules

What names can be given to tables or databases, and which cannot, as well as the rationale for such principles

Rules for using various operators, the importance of various operators, how to avoid mistakes

List and detailed description of statements that are supported by various platforms and the SQL standard

What clauses can be used in the CREATE FUNCTION statement for OLE-DB external table user functions

Features of using ALTER FUNCTION and CREATE FUNCTION instructions in databases on the Oracle platform

Instructions and suggestions used in databases on the Oracle platform, features of use

Other commands written in database procedures on the SQL Server platform, limited use

Features of indexing when using CREATE INDEX, ALTER INDEX instructions in Oracle databases

What is the CREATE ROLE statement, and the syntax features when using it and keywords

Some features of using the CREATE/ALTER TRIGGER statement in databases on different platforms

Ways and methods of using the CREATE/ALTER TRIGGER statement in databases on the SQL Server platform

Ways and methods of using the CREATE/ALTER TYPE statement in databases on various platforms

Some considerations when using the CREATE/ALTER TYPE statement in databases

Ways and methods of using the CREATE/ALTER TYPE statement in databases on the PostgreSQL platform

Ways and methods of using the CREATE/ALTER VIEW statement in databases on various platforms

Rules and Practices for Using the CREATE/ALTER VIEW Statement in Databases on Various Platforms

Some features of using the CREATE/ALTER VIEW statement in databases on various platforms

Ways and methods of using the CREATE/ALTER VIEW statement in databases on the SQL Server platform

Some features of using the DECLARE CURSOR command in databases on various platforms

Ways and methods (syntax elements) of using the DROP statement in databases on the MySQL platform

Rules and Practices for Using the RELEASE SAVEPOINT Statement in Databases on Various Platforms

SQL (Structured Query Language) is a database management language for relational databases. SQL itself is not considered a Turing-complete programming language, but its stereotype allows for procedural extensions to be made for it that expand its functionality into a full-fledged programming language.

The language was created in the 1970s under the name “SEQUEL” for the System R database management system (DBMS). It was later renamed “SQL” to avoid trademark incidents. In 1979, SQL was first released as a paid product, Oracle V2.

The first official language stereotype was adopted by ANSI in 1986 and ISO in 1987. Since then, several more versions of the standard have been made, some of them repeating the previous ones with minor variations, others taking on new important features.

Despite the existence of stereotypes, the bulk of popular SQL implementations stand out, for example, because the code can rarely be transferred from one DBMS to another without making significant changes. This is explained by the enormous size and complexity of the standard, as well as its lack of specifications in some significant areas of implementation.

SQL originated as a simple, standardized method for retrieving and managing data contained in a relational data foundation. Later it became more difficult than he thought, and turned into a tool of the creator, rather than the end user. In real time, SQL (mostly implemented by Oracle) remains the most well-known database management language, but there are a number of alternatives.

SQL is made up of four distinct parts:

Data Definition Language (DDL) is used to define the data structures stored in the data foundation. DDL statements provide the ability to make, change, and delete individual objects in the database. Allowed object types depend on the DBMS used and typically include databases, users, tables, and a number of smaller backup objects, for example, roles and indexes.
Data manipulation language (DML) is used to retrieve and configure data in a database. DML statements provide the ability to retrieve, insert, update, and delete data in tables. There are times when data retrieval select statements are not considered part of DML because they do not change the position of the data. All DML statements are declarative.
The information definition language (DCL) is used to control access to information in the database. DCL statements are used for privileges and make it possible to grant and revoke rights to use specific DDL and DML statements on specific database objects.
Transaction Control Language (TCL) is used to control the processing of transactions in the database. Typically, TCL statements include commit to confirm the changes made during the transaction, rollback to undo them, and savepoint to break the transaction into a number of smallest parts.

It follows in the footsteps to indicate that SQL will sell a declarative programming paradigm: any statement outlines only the important effect, and the DBMS accepts the conclusion on how to execute it, i.e. conceives simple operations necessary to perform the impact and carries them out. Last but not least, to effectively apply SQL probabilities, the creator needs to understand how the DBMS analyzes any statement and makes its execution design.

Structured Query Language or SQL is a declarative programming language for use in quasi-relational databases. Many of the original features of SQL were taken from tuple calculus, but recent extensions to SQL include more and more relational algebra.
SQL was originally created by IBM, but many vendors have developed their own dialects. It was adopted as a standard by the American National Standards Institute (ANSI) in 1986 and ISO in 1987. In the SQL programming language standard, ANSI stated that the official pronunciation of SQL is "es q el". However, many database specialists used the "slang" pronunciation "Sequel", which reflects the original name of the language, Sequel, which was later changed due to a trademark and name conflict with IBM. Programming for beginners.
SQL programming language was revised in 1992 and this version is known as SQL-92. 1999 was then revised again to become SQL:1999 (AKA SQL3). Programming for dummies. SQL 1999 supports objects that were not previously supported in other versions, but as of late 2001, only a few database management systems supported SQL implementations: SQL 1999.
SQL, although defined as ANSI and ISO, has many variations and extensions, most of which have their own characteristics, such as Oracle Corporation's "PL/SQL" implementation or Sybase and Microsoft's implementation called "Transact-SQL", which can be confusing to those familiar with programming basics. It is also not uncommon for commercial implementations to omit support for the main features of the standard, such data types as date and time, preferring some of their own variant. As a result, unlike ANSI C or ANSI Fortran which can usually be ported from platform to platform without major structural changes, SQL programming language queries can rarely be ported between different database systems without significant modifications. Most people in the database industry believe that this lack of compatibility is intentional, in order to provide each developer with their own database management system and tie the buyer to a specific database.
As its name suggests, the SQL programming language is designed for specific, limited purposes - querying data contained in a relational database. As such, it is a set of programming language instructions for creating data samples, rather than a procedural language such as C or BASIC, which are designed to solve a much wider range of problems. Language extensions such as "PL/SQL" are designed to solve this limitation by adding procedural elements to SQL while maintaining the benefits of SQL. Another approach is to embed procedural programming language commands into SQL queries and interact with the database. For example, Oracle and others support Java in the database, while PostgreSQL allows functions to be written in Perl, Tcl, or C.
One joke about SQL: "SQL is neither structured nor a language." The point of the joke is that SQL is not a Turing language. .

Select * from T

C1	C2
1	a
2	b

C1	C2
1	a
2	b

Select C1 from T

C1
1
2

C1	C2
1	a
2	b

Select * from T where C1=1

C1	C2
1	a

Given a table T, the Select * from T query will display all the elements of all rows in the table.
From the same table, the query Select C1 from T will display the elements from column C1 of all rows in the table.
From the same table, the query Select * from T where C1=1 will display all elements of all rows where the value of column C1 is "1".

SQL keywords

SQL words are divided into a number of groups.

The first one is Data Manipulation Language or DML(data management language). DML is a subset of the language used to query databases and add, update, and delete data.

SELECT is one of the most commonly used DML commands and allows the user to specify a query as a set description of the desired result. The query does not specify how the results should be arranged - translating the query into a form that can be executed in the database is the job of the database system, more specifically the query optimizer.
INSERT is used to add rows (formal set) to an existing table.
UPDATE is used to change data values in an existing table row.
DELETE specifies existing rows that will be deleted from the table.

Three other keywords can be said to fall into the DML group:

BEGIN WORK (or START TRANSACTION, depending on the SQL dialect) can be used to mark the start of a database transaction that will either complete all or not execute at all.
COMMIT states that all data changes made after operations are performed are saved.
ROLLBACK specifies that all data changes after the last commit or rollback must be destroyed, up to the point that was recorded in the database as a “rollback”.

COMMIT and ROLLBACK are used in areas such as transaction control and locking. Both instructions complete all current transactions (sets of operations on the database) and remove all locks on changing data in tables. The presence or absence of a BEGIN WORK or similar statement depends on the particular SQL implementation.

The second group of keywords belongs to the group Data Definition Language or DDL (Data Definition Language). DDL allows the user to define new tables and their associated elements. Most commercial SQL databases have their own DDL extensions that allow control over non-standard, but usually vital, elements of a particular system.
The main points of the DDL are the create and delete commands.

CREATE specifies the objects (such as tables) that will be created in the database.
DROP specifies which existing objects in the database will be deleted, usually permanently.
Some database systems also support the ALTER command, which allows the user to modify an existing object in various ways, such as adding columns to an existing table.

The third group of SQL keywords is Data Control Language or DCL (Data Control Language). DCL is responsible for data access rights and allows the user to control who has access to view or manipulate data in the database. There are two main keywords here.

sql is often called the Esperanto language for database management systems (DBMS). Indeed, there is no other language in the world for working with databases that would be so widely used in programs. The first sol standard appeared in 1986 and has now gained universal recognition. It can be used even when working with non-relational DBMSs. Unlike other software tools, such as the C and Cobol languages, which are the prerogative of professional programmers, sql is used by specialists from a variety of fields. Programmers, DBMS administrators, business analysts - they all successfully process data using sql. Knowledge of this language is useful for everyone who has to deal with the database.

In this article we will look at the basic concepts of sql. Let's tell his backstory (and dispel a few myths along the way). You will become familiar with the relational model and will be able to acquire first skills in working with sql, which will help in further mastering the language.

Is it difficult to learn sql? It depends on how deep you're going to go. In order to become a professional, you have to study a lot. The sql language began life in 1974 as the subject of a short 23-page research paper and has come a long way since then. The text of the current standard - the official document "the international standard database language sql" (commonly called sql-92) - contains over six hundred pages, but it does not say anything about the specific features of the versions of sol implemented in DBMSs from Microsoft, Oracle, Sybase etc. The language is so developed and diverse that just listing its capabilities would require several journal articles, and if you collect everything that has been written on the topic sol, you will get a multi-volume library.

However, for the average user it is not at all necessary to know sql completely. Just as a tourist who finds himself in a country where they speak an incomprehensible language only needs to learn a few common expressions and grammar rules, so in SQL - knowing a little, you can get many useful results. In this article we will look at the basic sql commands, the rules for setting criteria for selecting data, and show how to get results. As a result, you will be able to create tables yourself and enter information into them, create queries and work with reports. This knowledge can become the basis for further independent development of sql.

What is sql?

sql is a specialized non-procedural language that allows you to describe data, retrieve and process information from relational DBMSs. Specialization means that sol is intended only for working with the database; You cannot create a full-fledged application system using only this language - this will require the use of other languages in which you can embed SQL commands. Therefore, sql is also called an auxiliary language tool for data processing. An auxiliary language is used only in conjunction with other languages.

A general-purpose application language usually has facilities for creating procedures, but SQL does not. With its help, you cannot specify how a certain task should be performed, but you can only determine what exactly it is. In other words, when working with sql, we are interested in the results, not the procedures for obtaining them.

The most significant property of sql is the ability to access relational databases. Many even believe that the expressions “database processed using sql” and “relational database” are synonymous. However, you will soon see that there is a difference between them. The sql-92 standard doesn't even have the term relation.

What is a relational DBMS?

Without going into details, a relational DBMS is a system based on a relational data management model.

The concept of a relational model was first proposed in the work of Dr. E. F. Codd, published in 1970. It described a mathematical apparatus for structuring and manipulating data, and also proposed an abstract model for representing any real-world information. Previously, when using a database, it was necessary to take into account the specific features of storing information in it. If the internal structure of the database changed (for example, to improve performance), it was necessary to rework application programs, even if no changes occurred at the logical level. The relational model made it possible to separate the private features of data storage from the application program level. In fact, the model does not describe in any way how information is stored and accessed. What is taken into account is how this information is perceived by the user. Thanks to the emergence of the relational model, the approach to data management has qualitatively changed: from an art it turned into a science, which led to the revolutionary development of the industry.

Basic concepts of the relational model

According to the relational model, a relation is a table with data. A relation may have one or more attributes (features) corresponding to the columns of this table, and some set (possibly empty) of data, which are sets of these attributes (they are called n-ary tuples, or records) and corresponding to the rows of the table.

For any tuple, attribute values must belong to so-called domains. In fact, a domain is a certain set of data that defines the set of all valid values.

Let's look at an example. Let there be a Days of Week domain containing values from Monday to Sunday. If a relation has a WeekDay attribute corresponding to this domain, then any tuple in the relation must have one of the listed values in the WeekDay column. The values January or Cat are not allowed to appear.

Please note: the attribute must have one of the valid values. Specifying multiple values at once is prohibited. Thus, in addition to the requirement that attribute values belong to a certain domain, the condition of its atomicity must be met. This means that these meanings cannot be decomposed, i.e., they cannot be broken down into smaller parts without losing the underlying meaning. For example, if the attribute value simultaneously contained Monday and Tuesday, then two parts could be distinguished, preserving the original meaning - Day of the Week; therefore, this attribute value is not atomic. However, if you try to break the meaning “Monday” into parts, you will get a set of individual letters - from “P” to “K”; the original meaning is lost, so the meaning of "Monday" is atomic.

Relationships also have other properties. The most significant of them is the mathematical property of closed operations. This means that as a result of performing any operation on a relation, a new relation must appear. This property allows you to obtain predictable results when performing mathematical operations on relationships. In addition, it becomes possible to represent operations in the form of abstract expressions with different levels of nesting.

In his original work, Dr. Codd defined a set of eight operators called relational algebra. Four operators—union, logical multiplication, difference, and Cartesian product—were carried over from traditional set theory; the remaining operators were created specifically to handle relationships. Subsequent work by Dr. Codd, Chris Date, and other researchers proposed additional operators. Later in this article, we'll look at three relational operators—project, select, or restrict, and join.

sql and relational model

Now that you're familiar with the relational model, let's forget about it. Of course, not forever, but only to explain the following: although it was the relational model proposed by Dr. Codd that was used in the development of sql, there is no complete or literal correspondence between the two (this is one of the reasons why the sql-92 standard does not have the term attitude). For example, the concepts of an sql table and a relation are not equivalent, because tables can have several identical rows at once, while identical tuples are not allowed to appear in relations. In addition, SQL does not provide for the use of relational domains, although data types play a role to some extent (some influential supporters of the relational model are now attempting to push for the inclusion of relational domains in the future SQL standard).

Unfortunately, the inconsistency between sql and the relational model has given rise to many misunderstandings and disputes over the years. But since the main topic of the article is studying sql, and not the relational model, these problems are not discussed here. Just remember that there are differences between the terms used in sql and the relational model. Further in the article only terms accepted in sql will be used. Instead of relationships, attributes and tuples, we will use their sql analogues: tables, columns and rows.

Static and dynamic sql

You may already be familiar with terms such as static and dynamic sql. An sql query is static if it is compiled and optimized at a stage prior to program execution. We have already mentioned one form of static sql when we talked about embedding sql commands in C or Cobol programs (there is another name for such expressions - embedded sql). As you probably guess, a dynamic SQL query is compiled and optimized during program execution. As a rule, ordinary users use dynamic sql, which allows them to create queries in accordance with their immediate needs. One of the options for using dynamic SQL queries is their interactive or direct call (there is even a special term - directsql), when the queries sent for processing are entered interactively from the terminal. There are certain differences between static and dynamic SQL in the syntax of the constructs used and execution features, but these issues are beyond the scope of the article. We only note that for clarity of understanding, examples are given in the form of direct sql queries, since this allows not only programmers, but also most end users, to learn how to use sql.

How to learn sql

Now you are ready to write your first sql queries. If you have access to the database via sql and you want to use our examples in practice, then consider the following: you must log in as a user with unlimited rights and you will need software tools for interactive processing of sql queries (if we are talking about a network database, You should talk to your database administrator about granting you the appropriate rights). If you don’t have access to the database via sql, don’t worry: all the examples are very simple and you can figure them out “dry”, without going to the machine.

In order to perform any actions in sql, you must execute an expression in sql language. There are several types of expressions, but among them three main groups can be distinguished: ddl commands (data definition language), dml commands (data manipulation language) and data control tools. Thus, in a sense, sql combines three different languages.

Data Description Language Commands

Let's start with one of the main ddl commands - create table. There are several types of tables in sql, the main ones are two types: basic (base) and selective (views). Basic tables are those related to real-life data; selective are “virtual” tables that are created based on information obtained from base tables; but to users the forms look like regular tables. The create table command is designed to create base tables.

In the create table command, you must specify the name of the table, specify the list of columns and the types of data they contain. Other optional elements may also be present as parameters, but first let's look at only the basic parameters. Let's show the simplest syntactic form for this command:

create tableTableName(ColumnDataType) ;

create and table are sql keywords; TableName, Column and DataType are formal parameters, instead of which the user enters actual values each time. The Column and DataType parameters are enclosed in parentheses. In sql, parentheses are commonly used to group individual elements. In this case, they allow you to combine definitions for a column. The ending semicolon is a command separator. It must terminate any expression in sql language.

Let's look at an example. Let's say you need to create a table to store data about all appointments. To do this, enter the command in sql:

create table appointments (appointment_date date) ;

After executing this command, a table named appointments will be created, where there is one column, appointment_date, in which data of type date can be written. Since no data has been entered yet, the number of rows in the table is zero (the create table command only defines the table; the actual values are entered with the insert command, which is discussed later).

The appointments and appointment_date parameters are called identifiers because they specify names for specific database objects, in this case names for a table and a column, respectively. There are two types of identifiers in sql: regular and delimited. Highlighted identifiers are enclosed in double quotes and are case-sensitive. Regular identifiers are not distinguished by any limited characters, and their writing is not case sensitive. This article uses only regular identifiers.

The characters used to construct identifiers must satisfy certain rules. Regular identifiers can only use letters (not necessarily Latin, but also other alphabets), numbers and the underscore character. The identifier must not contain punctuation, spaces or special characters (#, @, % or!); in addition, it cannot begin with a number or an underscore. You can use separate sql keywords for identifiers, but this is not recommended. An identifier is intended to designate some object, so it must have a unique (within a certain context) name: you cannot create a table with a name that is already found in the database; You cannot have columns with the same names in the same table. By the way, keep in mind that appointments and appointments are the same names for sql. Changing the case of letters alone cannot create a new identifier.

Although a table can have just one column, in practice tables with multiple columns are usually required. The command to create such a table in general looks like this:

create tableTableName(ColumnDataType[ ( , ColumnDataType )]) ;

Square brackets are used to denote optional elements, curly brackets contain elements that can represent a list of single-path constructs (when entering a real SQL command, neither one nor the other brackets are placed). This syntax allows you to specify any number of columns. Note that the second element is preceded by a comma. If there are several parameters in the list, they are separated from each other by commas.

create table appointments2 (appointment_date date, appointment_time time, description varchar (256)) ;

This command creates the appointments2 table (the new table must have a different name, since the appointments table is already present in the database). Like the first table, it has an appointment_date column to record the dates of appointments; In addition, an appointment_time column has appeared to record the time of these meetings. The description parameter is a text string that can contain up to 256 characters. This parameter is specified as varchar (short for character varying) because it is not known in advance how much space will be required for the entry, but it is clear that the description will take no more than 256 characters. When describing a parameter of the character string type (and some other types), the length of the parameter is indicated. Its value is specified in parentheses to the right of the type name.

You may have noticed that in the two examples discussed, the command entry is formatted differently. If in the first case the command is completely placed on one line, then in the second after the first open parenthesis the entry is continued on a new line, and the definition of each subsequent column begins on a new line. There are no special requirements for record formatting in sql. Breaking a record into lines makes it easier to read. When writing commands, the sql language allows you not only to break the command into lines, but also to insert indents at the beginning of lines and spaces between record elements.

Now that you know the basic rules, let's look at a more complex example of creating a table with multiple columns. At the beginning of the article, the employees table was shown. It contains the following columns: last name, first name, hire date, department, category and salary for the year. The following sql command is used to define this table:

create table employees (last_name character (13) not null, first_name character (10) not null, hire_date date, branch_office character (15), grade_level smallint, salary decimal (9, 2));

The team meets several new elements. First of all, there is the not null expression at the end of the definition of the last_name and first_name columns. With the help of such structures, requirements are set that must be complied with. In this case, it is indicated that the last_name and first_name fields must be filled in when entering; You cannot leave these columns empty (this is quite logical: how can you identify an employee without knowing his name?).

In addition, the example contains three new data types: character, smallint and decimal. So far we haven't talked much about types. Although sql does not have relational domains, it does have a set of basic data types. This information is used when allocating memory and comparing values; to a certain extent narrows the list of possible input values, but type control in sql is less strict than in other languages.

All data types available in sql can be divided into six groups: character strings, exact numeric values, approximate numeric values, bit strings, datetimes and intervals. We have listed all the varieties, but this article will discuss only some of them in detail (bit strings, for example, are not of particular interest to ordinary users).

By the way, if you thought that the date and time was a typo, you were mistaken. This group (datetime) includes most of the time-related data types used in sql (parameters such as time intervals are separated into a separate group). In the previous example, we already encountered two data types from the date-time group - date and time.

The next data type you're already familiar with is character varying (or just varchar); it belongs to the group of character strings. If varchar is used to store strings of variable length, then the char type encountered in the third example is intended to store strings with a fixed number of characters. For example, the last_name column will contain strings of 13 characters, regardless of the actual last names entered, be it poe or penworth-chickering (in the case of poe, the remaining 10 characters will be filled with spaces).

From the user's point of view, varchar and char have the same meaning. Why was it necessary to introduce two types? The fact is that in practice you usually have to look for a compromise between performance and saving disk space. As a rule, using strings with a fixed length gives some benefit in access speed, but if the string length is too long, disk space is wasted. If in appointments2 you reserve 256 characters for each comment line, then this may turn out to be irrational; most often the lines will be significantly shorter. On the other hand, last names also vary in length, but they generally require around 13 characters; in this case, losses will be minimal. A good rule of thumb is: if you know that the length of the string varies little or is relatively small, then use char; in other cases - varchar.

The next two new data types, smallint and decimal, belong to the group of exact numeric values. smallint is short for small integer. SQL also provides an integer data type. The presence of two similar types in this case is explained by space saving considerations. In our example, the values of the grade_level parameter can be represented using a two-digit number, so the smallint type is used; however, in practice it is not always known what maximum values the parameters can have. If there is no such information, then use integer. The actual amount of space allocated for storing smallint and integer parameters, and the corresponding range of values for these parameters, is specific to each platform.

The decimal data type, commonly used for financial accounting, allows you to specify a pattern with the required number of decimal places. Because this type is used for precise numeric notation, it guarantees precision when performing mathematical operations on decimal data. If you use data types from the approximate numeric notation group for decimal values, for example float (floating point number), this will lead to rounding errors, so this option is not suitable for financial calculations. To define parameters of the decimal type, the following notation is used:

where p is the number of decimal places, d is the number of decimal places. Instead of p, write the total number of significant figures in the values used, and instead of d, write the number of decimal places.

The "Creating a Table" sidebar shows a complete summary of the create table command. It contains new elements and shows the format for all the considered data types (In principle, there are other data types, but we are not considering them yet).

At first, it may seem that the syntax of sql commands is too complicated. But you can easily understand it if you carefully study the above examples. An additional element appeared on the diagram - a vertical line; it serves to distinguish between alternative designs. In other words, when defining each column, you need to select the appropriate data type (as you remember, optional parameters are enclosed in square brackets, and constructs that can be repeated many times are enclosed in curly braces; these special characters are not written in real sql commands). The first part of the diagram shows the full names for the data types, the second contains their abbreviated names; in practice, any of them can be used.

The first part of the article is completed. The second will be devoted to studying the DML commands insert, select, update and delete. Data sampling conditions, comparison operators and logical operators, the use of null values and ternary logic will also be covered.

Creating a table. The syntax of the create table command is as follows: optional parameters are indicated in square brackets, and repeating structures are indicated in curly brackets.

The secret of the sql name

In the early 1970s. ibm began to implement the relational database model proposed by Dr. Codd. Donald Chamberlin and a group of others at the Advanced Research Unit created a prototype language called structured English query language, or simply sequel. Subsequently it was expanded and refined. The new version proposed by ibm was called sequel/2. It was used as a programming interface (api) for the design of ibm's first relational database system, system/r. For reasons related to legal nuances, ibm decided to change the name: instead of sequel/2, use sql (structured query language). This abbreviation is often pronounced "see-ku-el."