Home-How to open a file-SQL - what it is, what the language is needed for, and basic functions for beginners. SQL "for dummies": what beginners need to know

SQL - what it is, what the language is needed for, and basic functions for beginners. SQL "for dummies": what beginners need to know

    Which field of the Customers table is the primary key?

    What is column 4 of the Customers table?

    What is another name for a line? Column?

    Why can't you query the first five rows of a table for viewing?

(See Appendix A for answers.)

Sql: overview

This chapter will introduce you to the structure of the SQL language, as well as certain general implications, such as the type of data these fields can contain and some areas of ambiguity that exist in SQL. It is intended to provide a link to more specific information in subsequent chapters. You don't have to remember every detail mentioned in this chapter. Brief overview presented here in one place, many of the details you can refer to later as you master the language. We put all of this at the beginning of the book to orient you to the world of SQL without taking a simplistic approach to its problems, while at the same time giving you a familiar place to refer to it in the future when you have questions. This material may become clearer as we move on to specific SQL commands, starting in Chapter 3.

How does sql work?

SQL is a language aimed specifically at relational databases. It eliminates a lot of work that you would have to do if you used universal language programming, for example C. To form relational base data on C, you would need to start from the beginning. You would have to define an object called table , which could grow to have any number of rows, and then gradually create procedures to put values ​​into and retrieve from them. If you wanted to find some specific strings, you would need to do step by step procedure, similar to the following:

1. Consider a table row.

2. Check to see if this string is one of the strings you need.

3. If so, save it somewhere until the entire table has been checked.

4. Check if there are other rows in the table.

5. If yes, return to step 1.

6. If there are no more rows, print all the values ​​stored in step 3.

(Of course this is not an actual set C commands, but only the logic of steps that would be included in a real program.)

SQL will save you the time it takes to do all this. Commands in SQL can operate on all groups of tables as a single entity and can process any amount of information extracted or derived from them as a single unit.

What does ansi do?

As we discussed in the Introduction, the SQL standard is defined using ANSI code ( American National Standards Institute). SQL was not invented by ANSI. This is essentially an IBM invention. But other companies picked up SQL right away, at least, one company (Oracle) took away IBM's right to market SQL products.

After a number of competing SQL programs appeared on the market, ANSI defined the standard to which they should be conformed (defining such standards is the function of ANSI). However, after this some problems appeared. They arose as a result of ANSI standardization in the form of certain restrictions. Since ANSI does not always define what is most useful, programs try to conform to the ANSI standard without allowing it to limit them too much. This, in turn, leads to random inconsistencies. Database programs usually provide ANSI SQL additional features and often loosen many of the restrictions on most of them. Therefore, the common ANSI variations will also be covered. Although we obviously cannot cover every exception or variation, good ideas tend to be introduced and used in various programs even when they are not defined by the ANSI standard. ANSI is a type of minimum standard and you can do more than it allows, although you must follow its guidelines when performing the tasks it specifies.

1 vote

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 is it

Sql - language structured queries. 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 by sql help requests.

What you need to know


Key Points to Learn Sql

As noted above, queries are used to process and input new information in a database consisting of tables. Each line is 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 in this example in the table, select all available fields. 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.

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 instructions

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.

Last update: 06/26/2017

A database is often identified as a set of tables that store data. But this is not entirely true. It's better to say that a database represents a store of objects. The main ones:

    Tables: store the actual data

    Views: SQL expressions that return a set of data as a table

    Stored procedures: execute code on SQL language in relation to data in the database (for example, receives data or changes it)

    Functions: also SQL code that performs a specific task

IN SQL Server two types of databases are used: system and user. System databases are required SQL server for correct operation. A user bases data are created by server users and can store any arbitrary information. They can be changed and deleted, or created again. Actually, these are the databases that we will create and with which we will work.

System Databases

In MS SQL Server, four are created by default system databases data:

    master: This is the main database of the server, if it is missing or damaged, the server will not be able to work. It stores all used server user logins, their roles, various configuration settings, names and information about databases stored on the server, as well as a number of other information.

    model: This database represents the template from which other databases are created. That is, when we create our own database through SSMS, it is created as a copy of the model database.

    msdb: stores information about the work performed by a component such as the SQL scheduler. It also stores information about database backups.

    tempdb: This database is used as storage for temporary objects. It is recreated every time the server starts.

All these databases can be seen through SQL Server Management Studio in the Databases -> System Databases node:

These databases should not be modified, with the exception of the model database.

If during the server installation stage the PolyBase component was selected and installed, then the default server will also have three more databases that are used by this component: DWConfiguration, DWDiagnostics, DWQueue.

Creating a Database in SQL Management Studio

Now let's create our database. To do this, we can use a script in SQL language, or do everything using graphic tools in SQL Management Studio. IN in this case we will choose the second method. To do this, open SQL Server Management Studio and click right click mouse to the Databases node. Then in the appeared context menu select New Database:

After this, a window opens for us to create a database:

In the Database field you must enter the name of the new database. Let our database be called university.

The next field, Owner, specifies the owner of the database. By default it has the value , that is, the owner will be the one who creates this database. Let's leave this field unchanged.

The following is a table for installation general settings databases. It contains two lines - the first to set the settings for the main file where the data will be stored, and the second line to configure the logging file. In particular, we can set the following settings:

    Logical Name: The logical name that is assigned to the database file.

    File Type: There are several types of files, but, as a rule, the main work is done with data files (ROWS Data) and a log file (LOG)

    Filegroup: Denotes a group of files. A file group can store many files and can be used to break a database into pieces to be placed in different locations.

    Initial Size (MB): Sets the initial size of files when created (actual size may differ from this value).

    Autogrowth/Maxsize : when the database reaches starting size SQL Server uses this value to grow the file.

    Path : The directory where the databases will be stored.

    File Name : The immediate name of the physical file. If not specified, the logical name is used.

After entering the name of the database, click on the OK button and the database will be created.

After that, it will appear among the server databases. If this database is not required later, it can be deleted by right-clicking on it and selecting Delete in the context menu.

“We say Clipper – we mean...”

The term xBase technologies should be understood as those DBMSs that were widely used several years ago and were predominantly single-user, file-oriented systems. These are not only DBF-compatible systems like dBase IV, Clipper, FoxPro, but also systems like Paradox, Clarion and others. The latter, although technically different from the former, were very similar ideologically. Therefore, if somewhere in the text there is a mention of xBase, then by it we mean all the above-mentioned DBMSs.

The operating principles of xBase systems will not be covered here, since they are quite well known.

The golden age of xBase systems.

10 years ago, when the word “hacker” was not yet a dirty word, and every normal programmer easily answered the question of what INT21 is and why it is necessary to place “20 in the twentieth port”, among “normal” programmers it was considered that programming You can use Clipper and others like it only if you really need money or have nothing else to do.

What was programming on xBase systems? Dull and boring “Accounting” material resources", "Calculation wages” or something else like that. This, of course, brought money, but did not bring moral satisfaction. A “real” programmer could restore any binary code, be it a library from the C compiler or device drivers. And it’s free all night long. This brought great moral satisfaction, but did not bring money. Maybe they paid money for it, but not in this country.

In principle, xBase systems coped with their tasks using the classic configuration of those years: i286/40 MB HDD /1 MB RAM, but it all worked slowly, even on small databases.

With the advent of 386 and 486 processors, xBase technologies took heart - DBMSs moved faster. The mass appearance spoiled the joyful picture a little local networks, as customers demanded collaboration workstations on the network (not always giving themselves an idea of ​​what they really want), but writing network programs No one has really done it yet. The first one appeared here serious problem sharing data. The disadvantage of xBase technologies is that all installations and removal of locks on records and files are done by the programmer, writing a program, and if a mistake is made, then big troubles cannot be avoided.

Problems due to the growth in the volume of processed data also did not loom soon. This took time. In the meantime, everything looked great, especially since most of the problems were caused by faulty indexes, which were successfully repaired.

Damaged files with databases, because their structure was known and could also be corrected without much hassle.

In a word, this was the golden age of xBase.

“And in the damned bourgeoisie...”

Corporate DBMS Oracle, IBM DB/2 and others existed even then. These systems occupied their narrow sector on mainframes (or at least miniframes) and were very expensive. The transfer of these DBMSs to the PC platform, due to its weakness, was not envisaged.

In addition, these systems were not designed for an amateur programmer and the ideas embedded in them sometimes required a fundamentally different approach and way of thinking, thoughtful reading of documentation, and rejection of some traditional approaches. Such systems are not taken using sleight-of-hand methods, as suggested by most publications such as “Study…….. in 21 days.”

The very fact of centralized processing and storage of information that the SQL server offered required either the presence of a separate DBMS administrator and application programmer, or their combination in a single person, which was problematic to do due to the complexity of the software.

There were difficulties of a different kind, for example, application development on Oracle was radically different from Clipper, Fox, Paradox, etc. THIS IS DEVELOPMENT, because it’s hard to call IT PROGRAMMING. xBase systems are still more or less similar to procedural languages, in contrast to the same Oracle CDE. A common opinion of the late 80s was given in PC Magazine, Russian Edition. “Oracle is a system in which you feel the power of a tank, but you don’t know which lever to pull.”

SQL technologies were still “terribly far from the people.” But there was no one to wake up these “sleeping Decembrists” yet. Because it's too expensive :-(.

“People's techno - the rumble of servers...”

The general situation began to change when the cost of a megabyte began to decrease at an increasingly rapid pace. hard drives and tapes, memory became cheaper, and Intel threw Pentium onto the market. Building a large enterprise management system on a single server costing less than $4,000 (hardware only) using more efficient technologies has finally become affordable for a wider range of customers.

But iron is still half the battle, even less - one third. He needs more operating systems, DBMS and application software based on these DBMS. And this means time and money.

One would hardly expect a mass exodus from xBase-compatible DBMSs under such conditions.

“Mine is yours, don’t understand” or “oPHBER-gDPYUBYARBSI”

There is at least one problem that the American programmer does not have: he does not know what a bilingual keyboard layout is and why getty must necessarily skip 8 bits.

Russian language support has been a traditional sore point for all imported DBMSs. If there were methods for xBase-compatible DBMS proper organization sorting and processing data in Russian, which, although done “on the knee”, gave good results, then for large DBMSs adaptation to Russian code tables, the sorting order and reworking of functional libraries required very significant effort. It was difficult for single people, even talented ones, to do this, and the manufacturing companies of these DBMSs had only just begun to perceive Russia as a market for their products. And, accordingly, understand how it is done correct localization products for Russia.

Russian code pages are another story. In terms of encodings, Russia was “lucky”.

The small Russian-speaking Unix world steadfastly clung to KOI8, which had several variations, since it was the only encoding that kept letters readable when passing through clumsy American mail gates, which believed that mail could only be 7-bit.

The PC platform fully used the 866 encoding; in addition, the commercial genius BG invented Windows, with which the 1251 encoding appeared.

IBM also considered it necessary to show a kind gesture and developed the 855 encoding for the Slavs, represented by Russia, which, by the way, was called not Russian, but Cyrillic. To be honest, I didn’t come across it anywhere except Oracle. It is not known what Oracle Corporation was guided by when choosing this encoding as the base one for the Russian language, most likely due to the fact that Russians write in Cyrillic J. Although on the screen it’s more like some kind of methodological show.

Alternative platforms like Apple used their own version of Russian encoding. It still creaked in places hard drives inheritance from the CMEA brothers in the form of “Pravtsov” and “Robotrons”, who also used their vision of the Russian language, but fortunately their fleet was relatively small.

The International Organization for Standardization ISO, naturally, could not stand aside either. With its help, ISO 8859-5 was born, which was intended as a final solution to the problem of the Russian language in computer platform. Regarding the latter, it is worth saying that it is most well suited for sorting, the letters of the Russian language in it appear in a row and without breaks, but since it is difficult for the de jure standard to outlive the de facto standards, it was received rather coolly by the computer community. Although it is now quite common on Unix platforms, it is sometimes referred to as Russian Unix.

In fairness, it is also worth mentioning Unicode - a universal character encoding system.

An ordinary user is far from this problem and stumbles upon it if the body of a letter coming to him via the Internet contains abracadabra (see the title). But for DBMS programmers this is a problem, especially if the network uses clients on different platforms.

“Give me an introduction!”

But the real breakthrough in enterprise-level DBMS occurred after Windows NT came onto the scene. This OS was conceived as a platform displacing Unix, aimed at the enterprise applications market. And although NT failed to seriously advance Unix (the blow rather fell on Novell) and only highlighted its advantages, nevertheless, with its appearance it caused a certain ferment among manufacturers of Unix systems.

When the money-hungry shadow of BG loomed on the horizon, Unix manufacturers finally began to think that the stereotype that had been developing for years “Unix is ​​a system for the elite” had become obsolete and its continued existence threatened to result in a significant decrease in profits. It followed from this that systems should be made not only for highly qualified programmers, but also for ordinary users. Menu-driven administrator interfaces began to be added to Unix systems. Some systems now have DOS emulation and support for Win32, and later Windows95-98. Unix has become closer and more understandable to the people. Ultimately, it was all for the good end user who had to decide daily tasks, and not delve into the next strategic directions of information technology development.

Omitting the virtues and Windows disadvantages NT, it should be noted that Windows pair NT + MS SQL Server had appearance“real SQL server”, integration via ODBC with other Microsoft means, and what is important - a low price.

Microsoft, professing the “whatever I eat, I’ll bite” policy, was in such a hurry to fill a place in the corporate DBMS sector that it did not develop the server itself, but bought a ready-made one from Sybase and adapted it to NT. Willy-nilly, other SQL server manufacturers also had to reduce the price of their systems to maintain cost attractiveness. And it was good!

But Linux added even more fuel to the fire. Hardly Linus Torvalds assumed that something significantly greater would emerge from his experimental system, and yet it happened. Linux is free and has human face, is well documented and quite easy to install, in addition, the user receives in one bottle both workstation, so server applications. Moreover, install Linux on home computer has become fashionable. And this despite the fact that Linux developers and contributors do not spend millions of dollars on the pompous release of the next version, as Microsoft does.

Not long ago, Microsoft conducted comparative testing of NT and Linux. Windows NT, of course, won. But, as someone joked in RU.LINUX: “If the cockroaches ran in, it means that dichlorvos worked.”

In addition, it is easier to list those equipment manufacturers, system and application software that are not related to Linux. While browsing ComputerWorld recently, I noticed that Linux was mentioned in one way or another on almost every page, which is not bad for a free product.

Returning to the topic of DBMS, it should be said that many DBMS manufacturers have released servers for Linux, and although they are somewhat limited in functionality, they are very suitable as a platform for evaluating the capabilities of SQL servers or developing compact systems. Limitations in functionality are sometimes associated with the capabilities of Linux, for example, Linux does not support raw devices, therefore DBMSs cannot work independently with “raw” partitions. Or is such a limitation the will of the DBMS manufacturer - why put the same thing into a shareware release as a commercial release, especially if the latter costs a lot of $$$?

From SQL DBMS on Linux there are Oracle versions, Informix (both SE and DS), IBM DB/2, InterBase, Sybase. These systems apply to different conditions, but as a rule they are free either for development or for non-commercial use. Informix Dynamic Server, for example, sells for a purely symbolic price of $99. IBM DB2 Developer Edition is free for developers. Oracle, which traditionally distributes its versions in the form of trial systems, has released its Oracle8 Enterprise Edition for Linux.

There are also non-commercial systems such as Postgres SQL (supplied with Linux) and mySQL. I have never worked with Postgres, so it’s difficult for me to say anything about it.

One thing to mention about MySQL is that it does not have triggers or stored procedures, i.e. this is a simple data warehouse in which data integrity control lies entirely with client program. MySQL is often used to work as part of a Web server, since this DBMS is simple and does not require large resources.

There's even Clipper for Linux! Only it is now called Flagship, and it was developed not by CA, but by the German company Multisoft Datentechnik Gmbh. Like any Unix system, you can access it using terminals. A license for assessing opportunities for two sessions is free. Code modifications from Clipper for DOS are minimal, the Germans claim that it is fully code compatible with version 5.0. There is even a DBU, with an identical interface, that reports “Exit to Unix?” when exiting, instead of the usual “Exit to DOS?” J. By the way, the Germans claim that it exists under 50(sic!) different platforms. But if we consider Flagship as a corporate DBMS, then this is still a half-measure... and by no means free for use in an organization. Although, if there are people interested, then Flagship is in their hands. “And we will go the other way.”

SQL technologies. What they can do and how they do it.

Client-server interaction.

Language

Communication with the DBMS server occurs in the structured query language SQL (Structured Query Language). Basic set language standardized by ANSI. The current edition is ANSI SQL92. This is a non-procedural language. It is designed specifically for building queries and manipulating data and data structures. It has no variables, no labels, no loops, or anything else that a normal programmer is used to working with. It is necessary to clearly understand that SQL stipulates the method of transferring data to the client program, but does not stipulate in any way how this data should be processed and presented to the user in the client program.

Naturally, the basic standard cannot provide for all user needs, so many DBMS manufacturers offer their own and often non-portable SQL extensions. For example, Oracle and IBM have their own extensions SELECT statement, which allows you to effectively expand hierarchically ordered data into a horizontal tree (In Oracle this is START WITH / CONNECT BY). There is no such operator in the Informix SQL dialect, so you have to write stored procedures for these purposes. The number of extensions can be in the dozens for a DBMS server from one company. However, no one said that it would be easy...

There are also special procedural extensions to SQL dialects. They are similar to regular procedural languages, i.e. they have normal variables and labels and loops and everything else, and also fully support SQL syntax. Strict standard to procedural no extension, therefore, DBMS manufacturers define the syntax as they see fit. Again there is large number proprietary extensions, in particular Informix supports cursors with arbitrary positioning.



Related publications: