The Microsoft Office package has a special program for creating a database and working with them - Access. However, many users prefer to use the more familiar application, Excel, for this purpose. It should be noted that this program has all the tools to create a full-fledged database (DB). Let's find out how to do it.

A database in Excel is a structured set of information distributed over the columns and rows of a sheet.

According to special terminology, database rows are named "records". Each entry contains information about a separate object.

The columns are called "fields". Each field contains a separate parameter of all records.

That is, the framework of any database in Excel is a regular table.

Create a table

So, first of all we need to create a table.

This completes the creation of the database framework.

Assigning Database Attributes

In order for Excel to perceive a table not just as a range of cells, but as a database, it needs to be assigned the appropriate attributes.

We can say that after that we already have a ready-made database. You can work with it in the state as it is presented now, but many possibilities will be curtailed. Below we will analyze how to make the database more functional.

Sort and filter

Working with databases, first of all, provides for the possibility of ordering, selecting and sorting records. Let's connect these functions to our database.

Search

If you have a large database, it is convenient to search through it using a special tool.

Pinning areas

It is convenient when creating a database to fix cells with the names of records and fields. When working with a large database, this is simply a necessary condition. Otherwise, you will constantly have to spend time scrolling through the sheet to see which row or column corresponds to a certain value.

Now the names of the fields and records will always be in front of your eyes, no matter how far you scroll the data sheet.

Drop-down list

For some fields of the table, it would be optimal to organize a drop-down list so that users, when adding new records, can specify only certain parameters. This is true, for example, for the field "Floor". After all, there are only two options: male and female.

Now, when you try to enter data in the range where the limit was set, a list will appear in which you can choose between clearly defined values.

If you try to write arbitrary characters in these cells, an error message will appear. You will have to go back and make the correct entry.

Of course, Excel is inferior in its capabilities to specialized programs for creating databases. However, it has a toolkit that will in most cases satisfy the needs of users who want to create a database. Given the fact that the capabilities of Excel, in comparison with specialized applications, are known to ordinary users much better, then in this regard, Microsoft's development even has some advantages.

2017-06-21

Creating a MySQL database

Hello dear visitor!

Before proceeding to the next topic of creating a site, I want to apologize for the delay in writing articles. But, as they say, the reason is good, since it was connected with work on other important projects. I hope that in the future, urgent matters will allow me to consider all the main issues raised regarding the development of the site in the normal mode, with a frequency of 1-2 articles per week.

This article begins the next part of the collection "Make a site from scratch with your own hands", which will mainly be aimed at developing and optimizing the site, filling it with content and the necessary functionality.

And in order to be able to fully develop the site in the future, we will not be able to do without considering such an important issue as working with the MySQL database (hereinafter, the abbreviation "DB" will also be used to denote the MySQL database).

In this article, we will create a database on the local Denwer web server and on the hosting hosting our site.

• Why do you need a database
• What is a MySQL database
• Create a database on the local Denwer web server
• Create a hosted database

Why do you need a database

In previous articles, we have been creating the basis of the site, which includes the following main steps:

• design layout development;
• building web pages with HTML and CSS;
• creating a dynamic website using PHP;
• adapting the site for mobile devices using media queries;
• placement of the site on the Internet;

At the same time, at all these stages it was not required to use any database.

And now, as mentioned above, we will be developing the site, which will include questions such as:

• Filling content;
• work with forms;
• keeping track of customer information;
• accounting information about orders;
• recording information on commissions received;
• recording information about sent and received e-mails;
• optimization;

As you can see, even from the title of the above points, we will need to store and process the necessary information somewhere.

But, probably, the question may arise, why do we need a database at all? After all, you can save information in regular files?

Of course, you can solve all these issues without a database, using the contents of the files for this. But, in this case, the extraction and sorting operations will be much more laborious. And the speed of obtaining information from the database is much faster than from files. And if you add to this the amount of code that will be required to process the data placed in the files, then the question of whether to use a database disappears by itself.

What is a MySQL database

A lot of various reference literature has been written about the MySQL software server, which can be found on specialized Internet resources. Therefore, if necessary, it will not be difficult to get acquainted with the theoretical issues on this information system in more detail. Here we will focus on the practical application of this tool for our site.

And for this, in my opinion, it is quite enough to understand only that the MySQL database, in fact, is a set of ordinary tables that we all had to meet once. And, therefore, for those who get acquainted with this for the first time, you should not be afraid of such a term as "database".

These tables may have a different number of rows (records) and columns (fields) depending on the volume. The first column usually defines the record's unique identifier (commonly referred to as "id"), and the remaining fields are assigned based on their number.

Thus, by selecting a row using special SQL queries, we can perform all the necessary actions with it, such as writing data to it, extracting, sorting, or updating it.

In order to clearly explain this, below is a screenshot of a fragment of a table from one of my existing sites, which keeps a constant record of all visits, including various services and search robots.

Such an application tool makes it possible to collect information about all site visits in a given period, and, if necessary, analyze this data. Which is especially useful for emergency situations. If someone is interested in this, let me know, in one of the following articles I can post the corresponding code and tell you how it works.

Here you can see that the table consists of 6 columns and a certain number of rows, depending on the size of the specified period (in this case, the table has 35539 recent entries). Each line contains data on date, time, number of visits per day, user ip-address and host name.

At the same time, in the first column you can see unique id for each row of the table, which allows them to be identified by this value.

For example, you can make an SQL query that will iterate through all the id values ​​in order, and thus allow you to extract data from the entire table. And you can, by making a request to a specific ip-address, sort and analyze the visits that were made from it.

In general, having any data in a MySQL table, you can extract the necessary information depending on the required task.

Similarly, using queries, you can both write and update the contents of tables.

As you can see, there is nothing complicated here. The main thing when working with MySQL is to know how to correctly compose queries. But, and this shouldn’t be a big problem, which can be seen when we later record and retrieve the data necessary when adding site functionality.

And now, in order to be able to create MySQL tables and work with them, let's create a database in the local Denwer web server and on the hosting where the site is hosted.

Create a database on the local Denwer web server

To work with MySQL, there is a special phpMyAdmin application, which is a web interface for administering a database management system (DBMS). This tool allows you to administer the MySQL server through a browser, including creating tables and viewing their contents.

Thus, using the specified application, we will create a MySQL database.

To do this, at the beginning, by typing in the address bar of the browser "http://localhost/Tools/phpMyAdmin/" Let's open the main page of phpMyAdmin.

It should be noted that this interface can also be opened in another way - through a link to the Denver main page, as shown in the following screenshot, after typing "http://localhost/denwer/" in the browser.

And then, going to the appropriate section, we will create a database. To do this, just enter its name (let's call it, for example, "avtobezugona") and the required encoding, in our case, it will be "ult8_general_ci"

That's all, our database with the name "avtobezugona" has been created, as evidenced by the corresponding fields in the list of databases in the "Databases" section and in the main menu of phpMyAdmin.

Create a hosted database

In order to create a database, you first need to enter the hosting control panel, as shown in the article Buying hosting for a site.

Successful creation of the database is confirmed by a corresponding informational message displaying the necessary data for connection. They should definitely be written down somewhere, since in the future they will be required for work.

Due to the fact that this database is intended for an active site, for obvious reasons, some fields in the screenshots are made illegible.

Now you can go directly to the PhpMyAdmin editor and set the required database encoding.

But, in order to enter the PhpMyAdmin application, you should enter in the appropriate fields the data that was determined when creating the database and confirmed at the final stage (Fig. 7).

As a result, we got the desired result, created a database in Denver and hosted. And now we are ready to create tables in them in the following articles and work with their content.

• Next article:

Many users actively use Excel to generate reports and their subsequent editing. For easy viewing of information and gaining full control when managing data in the process of working with the program.

The appearance of the program's workspace is a table. A relational database structures information into rows and columns. Despite the fact that the standard MS Office package has a separate application for creating and maintaining databases - Microsoft Access, users actively use Microsoft Excel for the same purposes. After all, the capabilities of the program allow you to: sort; format; filter; edit; organize and structure information.

That is, everything that is necessary to work with databases. The only caveat: the Excel program is a universal analytical tool that is more suitable for complex calculations, calculations, sorting, and even for saving structured data, but in small volumes (no more than a million records in one table, for the 2010 version).

Database Structure - Excel Table

Database - a set of data distributed in rows and columns for easy searching, organizing and editing. How to make a database in Excel?

All information in a database is contained in records and fields.

A record is a row in a database (DB) that includes information about one object.

Field - a column in the database containing the same type of data about all objects.

The records and fields of the database correspond to the rows and columns of a standard Microsoft Excel spreadsheet.

If you know how to make simple tables, then creating a database is not difficult.

Creating a database in Excel: step by step instructions

Step by step database creation in Excel. Our task is to form a client database. For several years of work, the company has gained several dozen regular customers. It is necessary to track the terms of contracts, areas of cooperation. Know contact persons, contact details, etc.

How to create a customer database in Excel:

The main work - entering information into the database - has been completed. To make this information convenient to use, it is necessary to select the necessary, filter, sort the data.

How to maintain a customer database in Excel

To simplify the search for data in the database, let's arrange them. For this purpose, the "Sort" tool is suitable.

The data in the table were distributed according to the term of the conclusion of the contract.

Now the manager sees with whom it is time to renew the contract. Which companies do we continue to work with?

The database in the course of the company's activities grows to incredible sizes. Finding the information you need is getting harder and harder. To search for specific text or numbers, you can use one of the following methods:

Through data filtering the program hides all information that is not of interest to the user. The data remains in the table but is not visible. You can restore them at any time.

In Excel, 2 filters are most often used:

• Autofilter;
• filter by selected range.

Autofilter prompts the user to select a filtering option from a predefined list.

Let's experiment with filtering data by selected cells. Suppose we need to leave in the table only those companies that operate in Belarus.

If the database contains financial information, you can find the amount by different parameters:

• sum (summarize data);
• count (count the number of cells with numerical data);
• average value (calculate the arithmetic mean);
• maximum and minimum values ​​in the selected range;
• product (the result of data multiplication);
• standard deviation and sample variance.

The procedure for working with financial information in the database:

The tools on the Data tab allow you to shard the database. Group information in terms of relevance to the company's goals. The selection of groups of buyers of services and goods will help the marketing promotion of the product.

Ready-made sample templates for maintaining a client base by segments.

Templates can be customized, reduced, expanded and edited.

In the modern world, tools are needed that would allow storing, organizing and processing large amounts of information that are difficult to work with in Excel or Word.

Such repositories are used to develop information sites, online stores and accounting add-ons. The main tools that implement this approach are MS SQL and MySQL.

The product from Microsoft Office is a simplified version in terms of functionality and is more understandable for inexperienced users. Let's walk through creating a database in Access 2007 step by step.

Description of MS Access

Microsoft Access 2007 is a database management system (DBMS) that implements a full-fledged graphical user interface, the principle of creating entities and relationships between them, as well as the structural SQL query language. The only disadvantage of this DBMS is the inability to work on an industrial scale. It is not designed to store huge amounts of data. Therefore, MS Access 2007 is used for small projects and personal non-commercial purposes.

But before showing the creation of a database step by step, you need to familiarize yourself with the basic concepts from database theory.

Definitions of basic concepts

Without basic knowledge about the controls and objects used to create and configure the database, it is impossible to successfully understand the principle and features of customizing the subject area. Therefore, now I will try to explain in simple terms the essence of all important elements. So, let's begin:

1. A subject area is a set of created tables in a database that are linked to each other using primary and secondary keys.
2. An entity is a separate database table.
3. Attribute - the heading of a separate column in the table.
4. A tuple is a string that takes the value of all attributes.
5. The primary key is a unique value (id) that is assigned to each tuple.
6. The secondary key of table "B" is the unique value of table "A" used in table "B".
7. SQL query is a special expression that performs a specific action with the database: adding, editing, deleting fields, creating selections.

Now that we have a general idea of ​​what we will be working with, we can start creating a database.

Database creation

For clarity of the whole theory, let's create a training database "Students-Exams", which will contain 2 tables: "Students" and "Exams". The main key will be the "Record number" field, because. this parameter is unique for each student. The remaining fields are intended for more complete information about students.

So, do the following:

Everything, now it remains only to create, fill in and link tables. Move on to the next item.

Creating and populating tables

After successfully creating the database, an empty table will appear on the screen. To form its structure and fill it, do the following:

Advice! To fine-tune the data format, go to the "Table Mode" tab on the ribbon and pay attention to the "Formatting and Data Type" block. There you can customize the format of the displayed data.

Creating and editing data schemas

Before you start linking two entities, by analogy with the previous paragraph, you need to create and fill out the "Exams" table. It has the following attributes: "Record number", "Exam1", "Exam2", "Exam3".

To execute queries, we need to link our tables. In other words, this is a kind of dependency that is implemented using key fields. For this you need:

The constructor should automatically create the relationship, depending on the context. If this did not happen, then:

Executing queries

What to do if we need students who study only in Moscow? Yes, there are only 6 people in our database, but what if there are 6000 of them? Without additional tools, it will be difficult to find out.

It is in this situation that SQL queries come to our aid, which help to remove only the necessary information.

Request types

SQL syntax implements the CRUD principle (abbreviated from the English create, read, update, delete - “create, read, update, delete”). Those. With requests, you can implement all of these features.

per sample

In this case, the “read” principle comes into play. For example, we need to find all students who study in Kharkiv. For this you need:

But what if we are interested in students from Kharkiv who have more than 1000 scholarships? Then our request will look like this:

SELECT * FROM Students WHERE Address = Kharkiv AND Scholarship > 1000;

and the resulting table will look like this:

To create an entity

In addition to adding a table using the built-in constructor, sometimes you may need to perform this operation using an SQL query. In most cases, this is necessary during the performance of laboratory or term papers as part of a university course, because in real life there is no need for this. Unless, of course, you are a professional app developer. So, to create a request, you need:

1. Go to the "Create" tab.
2. Click the "Query Builder" button in the "Others" block.
3. In the new window, click on the SQL button, then enter the command in the text field:

CREATE TABLE Teachers
(TeacherCode INT PRIMARY KEY,
Surname CHAR(20),
Name CHAR (15),
Middle name CHAR (15),
Gender CHAR (1),
date of birth DATE,
main_subject CHAR(200));

where "CREATE TABLE" means to create the "Teachers" table, and "CHAR", "DATE" and "INT" are the data types for the corresponding values.

Attention! At the end of each request, there must be a ";" character. Without it, script execution will result in an error.

To add, delete, edit

Everything is much simpler here. Again go to the field to create a request and enter the following commands:

Form creation

With a huge number of fields in the table, it becomes difficult to fill the database. You can accidentally omit a value, enter the wrong value, or enter a different type. In this situation, forms come to the rescue, with the help of which you can quickly fill in entities, and the likelihood of making a mistake is minimized. This will require the following steps:

We have already considered all the basic functions of MS Access 2007. The last important component remains - report generation.

Report generation

A report is a special function of MS Access that allows you to format and prepare data from a database for printing. This is mainly used to create invoices, accounting reports and other office documentation.

If you have never encountered such a function, it is recommended to use the built-in "Report Wizard". To do this, do the following:

1. Go to the "Create" tab.
2. Click the "Report Wizard" button in the "Reports" block.
   
   
3. Select the table of interest and the fields you want to print.
   
   
4. Add the required level of grouping.
   
   
5. Select the sort type for each field.
   
   
6. Customize the layout view for the report.

Nowadays, databases are very widely used in various fields: at enterprises, in companies, educational institutions, and so on. And to work with them allows a program from the Office package - Microsoft Access, which gives users very wide opportunities for interacting with tables. The skills of working in Microsoft Access will be useful to every modern user. This article will take a detailed look at how to create a database in Access. Let's figure it out. Go!

The database is a collection of related information, combined in the form of a table. Having dealt with the fact that it is generally such, you can proceed directly to the creation.

An example of creating a database with a multilevel list

With Microsoft Access running, choose New from the File menu. Then click the "New Database" button. In the window on the right, specify the name of the file and the location where it will be stored. After that, click the "Create" button. You can also use one of the list of templates offered by the utility. In some cases, it will be much more convenient to simply edit the template than to create it all over again.

How to start creating a database

You will see a table with one single field. Hover over the tab and select Design. A window will appear asking you to change the name. Now you can start filling in the cells in the Field Name column. The next column "Data type" will be filled in automatically, depending on whether you entered words or numbers. The data type in each cell can be changed. There are such data types:

• text;
• MEMO field (used for descriptions of objects, as it allows you to enter a large number of characters,> 255);
• numerical;
• date Time;
• monetary;
• counter;
• boolean (useful for fields containing "yes" or "no");
• OLE object field (allows you to insert multimedia files: photo, video);
• hyperlink;
• attachment (used to store multiple files in one box);
• calculated (allows you to calculate the sum, difference, product based on other data from the table);
• substitution master.

In most cases, you will only need "numeric", "text", "money", "calculated" and "Date / time", these are the data types that are used most often. Below, in the "Field Properties" section, you can see the size of the field. If you have a text data type, there will be 255. This means that you can enter 255 characters in this cell. Of course, this is too much, so you should reduce the size of the specified field to 30 (you can put any other number, depending on the tasks at hand). This is done so that the database takes up less disk space.

For a different attribute, a different data type, an example in the screenshot.

By choosing a numeric data type, you can set the so-called input mask. It is needed in order to display the entered numerical values ​​in a certain way. The mask is set using spaces and bars. For example, if your table contains passport numbers, then to display these numbers in the appropriate format, set the mask "####_######". That is 4 characters, then a space and 6 more characters. For phone numbers, you can specify the mask "8(###)-###-##-##".

For the Date/Time type, you can select different display formats in the Field Properties section.

Don't forget to save the created table. This can be done using the key combination Ctrl+S. If, when saving, a window appears with a warning that the key fields are not set, then click "No", as they can be created later.

The save key is in the top left corner.

As you can see, creating databases is a simple process that even a novice user can handle. Write in the comments if the article helped you understand the issue, and ask if any of the material presented is not clear to you.