“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 “Material Accounting”, “Payroll” or something else like that. This, of course, brought money, but did not bring moral satisfaction. A “real” programmer could restore any binary code to its source text, be it a library from a 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. This is where the first serious problem appeared 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 Oracle DBMS, IBM DB/2 and others existed even then. These systems occupied their narrow sector on mainframes (miniframes in extreme cases) 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, developing applications on Oracle was radically different from Clipper, Fox, Paradox, etc. THIS IS DEVELOPMENT, because it’s hard to call THIS 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 on hard drives and tapes began to decrease at an increasingly rapid pace, memory became cheaper, and Intel launched 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 OS, 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”

Yes, according to 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 for xBase-compatible DBMSs there were methods for correctly organizing sorting and processing data in Russian, which, although they were done “on the knee”, gave good results, then for large DBMSs adaptation to Russian code tables, sorting order and reworking of functional libraries required very significant forces. 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 the correct localization of products for Russia is done.

Russian code pages are a different 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. In some places, the hard drives were still creaking from the legacy 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 the final solution to the problem of the Russian language on the 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, this all went to the benefit of the end user, who had to solve everyday problems, 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 a server on its own, 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. It is unlikely that Linus Torvalds imagined that something significantly greater would come out of 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, and 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 versions of Oracle, Informix (both SE and DS), IBM DB/2, InterBase, Sybase. These systems are distributed under varying conditions, but are generally free for either development or 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 storage in which data integrity control lies entirely with the 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!) various 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). The basic set of the language is 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 must be clearly understood 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.

It is natural that 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 to the 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 are also fully supported SQL syntax. There is no strict standard for procedural extensions, so DBMS manufacturers define the syntax as they see fit. Again there is a large number of proprietary extensions, in particular Informix supports free-positioning cursors.

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 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. Short review 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 were using a generic programming language, For example, C. To create a relational database 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 certain strings, you would need to follow a 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 jumped on the SQL bandwagon, with at least one company (Oracle) wresting the right from IBM 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 capture every exception or variation, successful ideas tend to be adopted 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.

Most modern web applications interact with databases, usually using a language called SQL. Luckily for us, this language is very easy to learn. In this article we will look at simple SQL queries and learn how to use them to interact with MySQL database.

What will you need?

SQL (Structured Query Language) a language specifically designed to interface with database management systems such as MySQL, Oracle, Sqlite and others... To complete SQL requests in this article, I advise you to install MySQL to your local computer. I also recommend using phpMyAdmin as a visual interface.

All this is available in everyone's favorite Denver. I think everyone should know what it is and where to get it :). Can also use WAMP or MAMP.

Denver has a built-in MySQL console. This is what we will use.

CREATE DATABASE:database creation

Here is our first request. We will create our first database for further work.

To begin, open MySQL console and log in. For WAMP The default password is empty. That is, nothing :). For MAMP - "root". For Denver, we need to clarify.

After login, enter the following line and click Enter:

CREATE DATABASE my_first_db;

Note that a semicolon (;) is added at the end of the query, just like in other languages.

Also commands in SQL case sensitive. We write them in capital letters.

Options formally: Character SetAnd Collation

If you want to install character set (character set) and collation (comparison) can be write next command:

CREATE DATABASE my_first_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Finds a list of character sets that are supported in MySQL.

SHOW DATABASES:displays a list of all databases

This command is used to list all available databases.

DROP DATABASE:deleting a database

You can delete an existing DB using this query.

Be careful with this command as it runs without warning. If there is data in your database, it will all be deleted.

USE:Database selection

Technically this is not a query, but a statement and does not require a semicolon at the end.

It tells MySQL select the default database for the current session. Now we are ready to create tables and do other things with the database.

What is a table in a database?

You can represent the table in the database as Excel file.

Just like in the picture, tables have column names, rows and information. By using SQL queries we can create such tables. We may also add, read, update and delete information.

CREATE TABLE: Creating a table

C Using this query we can create tables in the database. Unfortunately, the documentation MySQL not very clear for beginners on this issue. The structure of this type of query can be very complex, but we'll start with something easy.

The following query will create a table with 2 columns.

CREATE TABLE users (username VARCHAR(20), create_date DATE);

Please note that we can write our queries on multiple lines and with tabs for indentation.

The first line is simple. We simply create a table called "users". Next, in parentheses, separated by commas, is a list of all columns. After each column name we have information types, such as VARCHAR or DATE.

VARCHAR(20) means that the column is of type string and can be a maximum of 20 characters in length. DATE is also an information type that is used to store dates in the following format: "YYYY - MM-DD".

PRIMARY KEY ( primary keyh)

Before we run the next query, we must also include a column for "user_id", which will be our primary key. You can think of PRIMARY KEY as information that is used to identify each row in a table.

CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20), create_date DATE);

INT makes it 32 bit whole type(for example, numbers). AUTO_INCREMENT automatically generates a new value ID every time we add new series of information. This is not necessary, but it makes the whole process easier.

This column does not have to be an integer value, but it is most often used. Having a Primary Key is also optional, but is recommended for database architecture and performance.

Let's run the query:

SHOW TABLES:show all tables

This query allows you to get a list of tables that are in the database.

EXPLAIN:Show table structure

To show the structure of an existing table, you can use this query.

Columns are displayed with all properties.

DROP TABLE:delete table

Same as DROP DATABASES, this query deletes the table and its contents without warning.

ALTER TABLE: change table

This query can also contain complex structure due to the greater number of changes it can make to the table. Let's look at examples.

(if you deleted the table in the previous step, create it again for tests)

ADDING A COLUMN

ALTER TABLE users ADD email VARCHAR(100) AFTER username;

Due to the good readability of SQL, I think there is no point in explaining it in detail. We are adding new column"email" after "username".

REMOVING A COLUMN

It was also very easy. Use this request with caution because your data may be deleted without warning.

Restore the column you just deleted for further experiments.

MAKING CHANGES IN A COLUMN

Sometimes you may want to make changes to the properties of a column, and you don't have to completely delete it to do this.

This query renamed the user column to "user_name" and changed its type from VARCHAR(20) to VARCHAR(30). This change should not change the data in the table.

INSERT: Adding Information to a Table

Let's add some information to the table using the following query.

As you can see, VALUES() contains a list of values ​​separated by commas. All values ​​are enclosed in single columns. And the values ​​must be in the order of the columns that were defined when the table was created.

Notice that the first value is NULL for the PRIMARY KEY field called "user_id". We do this so that the ID is generated automatically, since the column has the AUTO_INCREMENT property. When information is added for the first time the ID will be 1. The next row will be 2, and so on...

ALTERNATIVE OPTION

There is another query option for adding rows.

This time we use keyword SET instead of VALUES, and it doesn't have parentheses. There are several nuances:

You can skip the column. For example, we didn't assign a value to "user_id", which would default to its AUTO_INCREMENT value. If you omit a column with a VARCHAR type, then an empty row will be added.

Each column must be referred to by name. Because of this, they can be mentioned in any order, unlike the previous version.

ALTERNATIVE OPTION 2

Here's another option.

Again, since there are references to the column name, you can set the values ​​in any order.

LAST_INSERT_ID()

You can use this query to get the ID that was AUTO_INCREMENT for the last row of the current session.

NOW()

Now it's time to show how you can use the MySQL function in queries.

The NOW() function outputs current date. So you can use it for automatic establishment column dates to the current one when inserting a new row.

Please note that we received 1 warning, but please ignore it. The reason for this is that NOW() also serves to output temporary information.

SELECT: Reading data from a table

If we add information to a table, then it would be logical to learn how to read it from there. This is where the SELECT query will help us.

Below is the simplest one possible request SELECT to read the table.

In this case, the asterisk (*) means that we have requested all fields from the table. If you only want certain columns, the query would look like this.

ConditionWHERE

Most often, we are not interested in all columns, but only in some. For example, let's assume that we only need email address for user "nettuts".

WHERE allows you to set conditions in a query and make detailed selections.

Note that for equality, one equal sign (=) is used, not two, as in programming.

You can also use comparisons.

AND or OR can be used to combine conditions:

Please note that numeric values must not be in quotes.

IN()

This is useful for sampling on multiple values

LIKE

Allows you to make "wildcard" requests

The % icon is used as a "wildcard". That is, anything could be in its place.

ConditionORDER BY

If you want to get the result in an ordered form according to any criterion

The default order is ASC (smallest to largest). For the opposite, DESC is used.

LIMIT ... OFFSET ...

You can limit the number of results received.

LIMIT 2 takes only the first 2 results. LIMIT 1 OFFSET 2 gets 1 result after the first 2. LIMIT 2, 1 means the same thing (just note offset comes first and then limit ).

UPDATE: Make changes to the information in the table

This query is used to change information in a table.

In most cases, it is used in conjunction with a WHERE clause, since you will most likely want to make changes to certain columns. If there is no WHERE clause, the changes will affect all rows.

You can also use LIMIT to limit the number of rows to which changes need to be made.

DELETE: Removing information from a table

Just like UPDATE, this query is used with WHERE:

To delete the contents of a table, you can simply do this:

DELETE FROM users;

But it's better to use TRUNCATE

In addition to deleting, this request also resets values AUTO_INCREMENT and when adding rows again, the countdown will start from zero. DELETE does not do this and the countdown continues.

Disabling Lowercase Values ​​and Special Words

String values

Some characters need to be disabled ( escape ), or there may be problems.

A backslash is used for this.(\).

Special words

Because in MySQL there are many special words ( SELECT or UPDATE ), to avoid errors when using them, you must use quotes. But not ordinary quotes, but like this(`).

That is, you will need to add a column named " delete ", you need to do it like this:

Conclusion

Thank you for reading to the end. I hope you found this article helpful. It's not over yet! To be continued:).

NO:user READ:@ALL EDIT:@ALL

Learn the basic constructs of the structured query language SQL.

Studying the MS SQL Server Management Studio environment.

Acquiring data structure design skills.

Databases form the basis for building information systems of any scale and purpose. In database theory, one of the main issues is related to the analysis of the subject area and modeling of data structure, data management and analysis.

The basis of any database is the data model implemented in it, which is a set of data structures, integrity constraints and data manipulation operations. Using a data model, domain objects and the relationships existing between them can be represented.

The result laboratory work will create a relational database based on MS SQL Server 2005.

In a relational database, data is presented as a collection of tables. The table consists of a certain number columns (fields) and an arbitrary number of rows (records).

The planned database will be an information repository of data on student performance and will consist of the following tables:

- Specialty(speciality)
- Course(well)
- Group(group)
- Discipline(discipline)
- Account(reporting type)
- Mark(mark)
- Status
- Position(job title)
- People(People)
- Student(student)
- Teacher(teacher)
- SemesterResults(results of the session, semester)

The structure of these tables is given in the Appendix.

1. Getting started in Microsoft SQL Server Management Studio

To create databases we use the Microsoft SQL Server Management Studio environment. When requesting a connection to the server, select (Fig. 1):

Server type: Database Engine Component

Server name: SQL-MS.

Under this name in the domain fizmat.vspu.ru. A machine is available on which MS SQL Server 2005 server components are installed. You can try selecting a server from the drop-down list of servers. You can also access this machine at IP address 192.168.10.152 from the local network.

Authentication: SQL Server Authentication.

This setting allows you to create users for a given instance of SQL Server, regardless of the computer from which you log on.

Login: studentMBS21.

Password: student.

Figure 1. Microsoft SQL Server Management Studio 2005 login window

Note. The user studentMBS21 has great authority on this server, so it must be used very carefully. We will create a database under this user, and we will fill it out and search through it under other users. It is best to use your own account in the domain fizmat.vspu.ru. In this case, you must select Windows Authentication.

Now click the “Options” button and select (Fig. 2):

Connect to a database → Server overview… → User databases → trial_base.

Network Protocol → TCP/IP

Click the “Connect” button.

Figure 2. Microsoft SQL Server Management Studio 2005 login window (Options tab)

Note. The trial_base database is the default database for the user studentMBS21 and was created when this user registered. In the case when the user's access rights are not limited (as in the case under consideration), the Settings tab does not need to be opened. If the user has access only to certain databases, one of these databases must be specified when connecting to the server.

After a successful connection to the database, we see the following picture on the screen (Fig. 3):

Figure 3. Connection to SQL server established

The MS SQL Management Studio environment provides convenient tools for creating, editing, and populating databases. But real professionals rarely use this environment in their work, and use SQL queries to complete their tasks. We will use the graphical mode when it is convenient and clear, but our main emphasis will be on mastering the SQL language base.

2. Creating a database in Microsoft SQL Server Management Studio

In the “Databases” section, right-click and select “Create a database...” (Fig. 4). Let's name the database by group index - mbs21. We will assign the database owner to the user under whose name the login was made – studentMBS21. In the “Options” section, select the sort type Cyrillic_General_BIN (for example), click OK.

Figure 4. Creating a database

The newly created mbs21 appears in the “Databases” section of the Object Browser (check it out!):

Figure 5. Object Browser

3. Creating database tables in Microsoft SQL Server Management Studio

Let's start by creating the Specialty table. The table structure is shown below:

IN relational databases data, the primary key is used as a unique identifier for a record. This field is required; it is used to link tables using foreign keys (examples of such linking will be discussed below). The primary key must be an integer type (in in this case - int). The second field will store the name of the specialty - some string, so we select the type for this field varchar(60). The number in brackets indicates the maximum number of characters per line. Detailed information You can find information about these types in the help.

In the simplest way, you can create tables using MS SQL Server Management Studio (right mouse button on the “Tables” header > Create table.). We get the following:

Figure 6. Creating a table

Enter the name of the first column Num (primary key - the record number is stored in that column), select the type from the drop-down list data int. The primary key cannot be empty, so we leave the “Allow null values” field unchecked. Then, in the same way, enter the name of the second column, set the type, and prohibit the field from having null value. The table takes the following form:

Figure 7.

Now you need to specify that the field Num will be the primary key. Right click Click on this field and select “Set primary key”:

Figure 8.

Save the table under the name Specialty (the table should then appear in the Object Browser). Now you can proceed to filling out this table (to do this, select this table in the object browser and context menu click “Open table”):

Figure 9.

When filling out, you will find that each time you have to enter not only useful information (name of specialty), but also the record number. To automatically enter a record number, you must specify an identifying column specification. To do this, you need to specify in the column properties that this column is identifying (Fig. 10):

Figure 10. Defining properties of an identifying column

4. Create database tables using SQL query

Creating tables in graphic mode, certainly convenient, but not universal. If you use other database development tools (for example, IBM DB2), you will have to get used to new working techniques. The use of SQL language constructs allows you to work with databases based on a unified approach in any database management environment.

Select “Create Query” from the toolbar:

Figure 11.

Let's create a new database with a query. Let's write

CREATE DATABASE mbs21_query

and press F5. It should appear in the Object Explorer new base(if it does not appear immediately, then you need to select the “Databases” section with the mouse and select “Update” in the context menu).

Now let's create the Specialty table. The simplified syntax for creating tables is as follows:

CREATE TABLE< имя таблицы> ( < имя столбца 1 > < тип данных>[ NOT NULL ] [ DEFAULT< значение по умолчанию> ] , < имя столбца 2 > < тип данных>[ NOT NULL ] [ DEFAULT< значение по умолчанию> ] , ... )

Let's introduce new request:

/* creating a table Specialty */ USE mbs21_query CREATE TABLE Specialty( Num INT IDENTITY (1, 1) PRIMARY KEY NOT NULL, -- primary key NameSpec VARCHAR (60 ) -- name of specialty )

In the Object Explorer we see that the table has indeed been created. We save the file with the SQL query in our own folder (at the end of the work you need to show the queries that were executed to the teacher). The word IDENTITY(1,1) is added so that the primary key field Num is automatically numbered starting from one (in fact, this construct defines the specification of the identifying column).

You need to create the remaining tables in the same way. Let's look at the Course table.

Table Course(well)

This table contains the field Specialty, which references the primary key of the Specialty table. To create such a table, you need to run the following query:

/* creating the Course table */ USE mbs21_query -- determine the database that the table belongs to CREATE TABLE Course( Num INT IDENTITY (1 , 1 ) PRIMARY KEY NOT NULL , -- primary key YearEntry INT NOT NULL , -- year of admission YearFinal INT , -- year of graduation Specialty INT FOREIGN KEY REFERENCES Specialty(Num) -- speciality, -- foreign key reference to the Num field of the Specialty table )

Exercise. Create all other tables specified in the Appendix using SQL queries.

Application. Data structure

Table Specialty(speciality)

Table Course(well)

Table Group(group)

Table Discipline(discipline)

Table Account(reporting type)

Table Mark(mark)

Table Status(student's academic status)

Table Position(job title)

Table People(People)

Field (column) name	Content	Data type	Ability to contain NULL
Num	Primary key	int	No
LastName	Surname	varchar(30)	No
FirstName	Name	varchar(30)	No
MiddleName	Surname	varchar(30)	Yes
Male	Floor	char(1)	No
BrthDate	Birthday	datetime	Yes
Addr	Address	varchar(100)	Yes

Table Student(student)

Field (column) name	Content	Data type	Ability to contain NULL
Num	Primary key	int	No
People	Human ( external key refers to the primary key of the People table)	int	No
Group	Group (foreign key refers to the primary key of the Group table)	int	No
StudNum	Student ID number	varchar(30)	No
Status	Student academic status (foreign key references the primary key of the Status table)	int	No

Table Teacher(teacher)

Table SemesterResults(session results)

Field (column) name	Content	Data type	Ability to contain NULL
Num	Primary key	int	No
Student	Student (foreign key refers to the primary key of the Student table)	int	No
Semester	Serial number semester	int	No
Account	Reporting type (foreign key refers to the primary key of the Account table)	int	No
Discipline	Discipline (foreign key refers to the primary key of the Discipline table)	int	No
Teacher	Teacher (foreign key refers to the primary key of the Teacher table)	int	No
Mark	Mark (the foreign key references the primary key of the Mark table)	int	No
Date	Report submission date	DateTime	No

Today, SQL courses “for dummies” are becoming increasingly popular. This can be explained very simply, because in modern world More and more often you can find so-called “dynamic” web services. They are distinguished by a fairly flexible shell and are based on All novice programmers who decide to dedicate websites, first of all enroll in SQL courses “for dummies”.

Why learn this language?

First of all, SQL is taught in order to further create a wide variety of applications for one of the most popular blog engines today - WordPress. After passing several simple lessons you can already create queries of any complexity, which only confirms the simplicity of this language.

What is SQL?

Or a structured query language, was created for one single purpose: to determine, provide access to and process them in fairly short periods of time. If you know the SQL meaning, then you will understand that this server is classified as a so-called “non-procedural” language. That is, its capabilities only include a description of any components or results that you want to see in the future on the site. But when does not indicate exactly what results are going to be obtained. Each new request in this language is like an additional “superstructure”. It is in the order in which they are entered into the database that the queries will be executed.

What procedures can be performed using this language?

Despite its simplicity, the SQL database allows you to create a wide variety of queries. So what can you do if you learn this important programming language?

• create a wide variety of tables;
• receive, store and modify received data;
• change table structures at your discretion;
• combine the received information into single blocks;
• calculate the received data;
• ensure complete protection of information.

What commands are the most popular in this language?

If you decide to take a SQL for Dummies course, then you will receive detailed information about the commands that are used in creating queries using it. The most common today are:

1. DDL is a command that defines data. It is used to create, modify and delete a wide variety of objects in the database.
2. DCL is a command that manipulates data. It is used to provide different users with access to information in the database, as well as to use tables or views.
3. TCL is a team that manages a variety of transactions. Its main purpose is to determine the progress of a transaction.
4. DML - manipulates the received data. Its purpose is to allow the user to move various information from the database or enter it there.

Types of privileges that exist in this server

Privileges refer to those actions that a particular user can perform in accordance with his status. The most minimal, of course, is a regular login. Of course, privileges may change over time. Old ones will be deleted and new ones will be added. Today, all those who take SQL Server "for dummies" courses know that there are several types of permitted actions:

1. Object type - the user is allowed to execute any command only in relation to a specific object that is located in the database. At the same time, privileges differ for different objects. They are also tied not only to a particular user, but also to tables. If someone, using his capabilities, created a table, then he is considered its owner. Therefore, he has the right to assign new privileges to other users related to the information in it.
2. The system type is the so-called data copyright. Users who have received such privileges can create various objects in the database.

History of SQL

This language was created by IBM Research Laboratory in 1970. At that time, its name was slightly different (SEQUEL), but after a few years of use it was changed, shortening it a little. Despite this, even today many world-famous programming experts still pronounce the name the old-fashioned way. SQL was created with one single purpose - to invent a language that would be so simple that even ordinary users Internet. An interesting fact is that SQL time was not the only such language. In California, another group of specialists developed a similar Ingres, but it never became widespread. Before 1980, there were several variations of SQL that were only slightly different from each other. To prevent confusion, a standard version was created in 1983, which is still popular today. SQL courses "for dummies" allow you to learn much more about the service and fully study it in a few weeks.