How to create a mysql database. Creating a PhpMyAdmin database and adding a user to it


Many users actively use Excel to generate reports and their subsequent editing. For convenient viewing information and receiving full control when managing data while working with the program.

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

That is, everything that is necessary to work with databases. The only caveat: Excel is a universal analytical tool that is more suitable for complex calculations, calculations, sorting and even for storing 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 collection of data distributed in rows and columns for convenient search, systematization and editing. How to make a database in Excel?

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

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

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

Database records and fields correspond to the rows and columns of the standard Microsoft tables Excel.

If you know how to do simple tables, then creating a database will not be difficult.



Creating a database in Excel: step-by-step instructions

Step-by-step creation of a database in Excel. Our task is to create a client database. Over the course of several years of operation, the company has acquired several dozen regular customers. It is necessary to monitor the terms of contracts and areas of cooperation. Know contact persons, communication 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, you need to select what you need, filter, and sort the data.

How to maintain a customer database in Excel

To make it easier to find data in the database, let's organize it. The Sort tool is suitable for this purpose.


The data in the table is distributed according to the period of conclusion of the contract.


Now the manager sees with whom it is time to renew the contract. And what companies do we continue to cooperate with?

During the course of the company's activities, the database grows to incredible sizes. Find necessary information It's getting more and more difficult. To find 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 invisible. They can be restored at any time.

IN Excel program 2 filters are most often used:

  • Autofilter;
  • filter by selected range.

Autofilter prompts the user to select a filtering option from a ready-made list.


Let's experiment with filtering data by selected cells. Let's say 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 using various parameters:

  • sum (sum data);
  • count (count the number of cells with numeric 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:

Tools on the Data tab allow you to segment your database. Group information in terms of relevance to the company's goals. Identifying groups of buyers of services and goods will help the marketing promotion of the product.

Ready-made sample templates for maintaining a customer base by segment.


Templates can be customized, shortened, expanded and edited.

Annotation: The process of creating a database is defined. Operators for creating and changing a database are described. The possibility of specifying the name of a file or several files to store data, the size and location of the files is being considered. Operators for creating, changing, and deleting user tables are analyzed. A description of the parameters for declaring table columns is provided. The concept and characteristics of indices are given. Operators for creating and changing indexes are considered. The role of indexes in improving the efficiency of SQL statement execution is determined.

Database

Database creation

In various DBMSs, the procedure for creating databases is usually assigned only to the database administrator. In single-user systems, the default database can be created directly during the installation and configuration of the DBMS itself. The SQL standard does not define how databases should be created, so in each of the dialects SQL language Usually a different approach is used. According to the SQL standard, tables and other database objects exist in some environment. Among other things, each environment consists of one or more directories, and each directory consists of a set of schemas. A schema is a named collection of database objects that are related to each other in some way (all objects in a database must be described in one schema or another). Schema objects can be tables, views, domains, statements, mappings, interpretations, and character sets. They all have the same owner and many general values, accepted by default.

The SQL standard leaves DBMS developers the right to choose a specific mechanism for creating and destroying directories, but the mechanism for creating and deleting schemas is regulated through the CREATE SCHEMA and DROP SCHEMA statements. The standard also specifies that within the schema creation statement it must be possible to define a range of privileges, available to users the created scheme. However specific ways The definitions of such privileges vary across DBMSs.

Currently, the CREATE SCHEMA and DROP SCHEMA statements are implemented in very few DBMSs. In other implementations, for example, in the MS SQL Server DBMS, the CREATE DATABASE operator is used.

Creating a database in MS SQL Server environment

The process of creating a database in a SQL server system consists of two stages: first, the database itself is organized, and then the database that belongs to it transaction log. The information is placed in appropriate files with extensions *.mdf (for the database) and *.ldf. (For transaction log). The database file records information about the main objects (tables, indexes, views, etc.), and the file transaction log– about the process of working with transactions (monitoring data integrity, the state of the database before and after executing transactions).

Creating a database in the SQL server system is carried out with the CREATE DATABASE command. It should be noted that the procedure for creating a database in a SQL server requires server administrator rights.

<определение_базы_данных>::= CREATE DATABASE database_name [<определение_файла>[,...n] ] [,<определение_группы>[,...n] ] ] [ LOG ON (<определение_файла>[,...n] ) ] [ FOR LOAD | FOR ATTACH ]

Let's consider the main parameters of the presented operator.

When choosing a database name, you should follow the general rules for naming objects. If the database name contains spaces or any other illegal characters, it is enclosed in delimiters (double quotes or square brackets). The database name must be unique within the server and cannot exceed 128 characters.

When creating or editing a database, you can specify the name of the file that will be created for it, change the name, path, and original size this file. If, while using the database, you plan to place it on several disks, you can create so-called secondary database files with the *.ndf extension. In this case, the main information about the database is located in the primary (PRIMARY) file, and if there is not enough free space for it, the added information will be placed in the secondary file. The approach used in SQL Server allows the contents of the database to be distributed across multiple disk volumes.

The ON parameter specifies a list of files on disk for storing information stored in the database.

The PRIMARY parameter specifies primary file. If omitted, the first file in the list is primary.

The LOG ON parameter specifies the list of files on the disk to be placed transaction log. File name for transaction log is generated based on the database name and is appended with _log characters at the end.

When creating a database, you can define a set of files that it will consist of. The file is defined using the following construct:

<определение_файла>::= ([ NAME=logical_file_name,] FILENAME="physical_file_name" [,SIZE=file_size ] [,MAXSIZE=(max_file_size |UNLIMITED ) ] [, FILEGROWTH=growth_size ])[,...n]

Here logical file name– this is the file name under which it will be recognized when executing various SQL commands.

Physical file name is intended to indicate the full path and name of the corresponding physical file that will be created on the hard drive. This name will remain with the file at the operating system level.

The SIZE parameter determines the initial file size; The minimum parameter size is 512 KB; if it is not specified, the default is 1 MB.

The MAXSIZE parameter specifies the maximum size of the database file. When the UNLIMITED parameter is set, the maximum database size is limited free space on disk.

When creating a database, you can enable or disable automatic growth of its size (this is determined by the FILEGROWTH parameter) and specify the increment using an absolute value in MB or a percentage. The value can be specified in kilobytes, megabytes, gigabytes, terabytes or percentage (%). If a number is specified without the MB, KB, or % suffix, the default value is MB. If the growth step size is specified as a percentage (%), the size is increased by the specified percentage of the file size. The size shown is rounded to the nearest 64 KB.

Additional files may be included in the group:

<определение_группы>::=FILEGROUP file_group_name<определение_файла>[,...n]

Example 3.1. Create a database, and for the data, define three files on drive C, for transaction log– two files on drive C.

CREATE DATABASE Archive ON PRIMARY (NAME=Arch1, FILENAME='c:\user\data\archdat1.mdf', SIZE=100MB, MAXSIZE=200, FILEGROWTH=20), (NAME=Arch2, FILENAME='c:\user \data\archdat2.mdf', SIZE=100MB, MAXSIZE=200, FILEGROWTH=20), (NAME=Arch3, FILENAME='c:\user\data\archdat3.mdf', SIZE=100MB, MAXSIZE=200, FILEGROWTH =20) LOG ON (NAME=Archlog1, FILENAME='c:\user\data\archlog1.ldf', SIZE=100MB, MAXSIZE=200, FILEGROWTH=20), (NAME=Archlog2, FILENAME='c:\user \data\archlog2.ldf', SIZE=100MB, MAXSIZE=200, FILEGROWTH=20) Example 3.1. Database creation.

Mysql, it is much more important whether you have basic set knowledge without which to talk about successful implementation it doesn't work as planned.

In this material I will tell you what actions any PC owner needs to take if for some purpose he needs to install mysql, create a database, and try his hand at managing a modern database. It will also be of interest to those who want to know how to make it work modern DBMS.

Because, as I said, in order for you to succeed, you need basic knowledge, without which it will not be possible to create a mysql database, accordingly we will assume that you know what a database, table, database queries are and you are not confused by the abbreviation SQL. We will also assume that you have installed and configured Apache server.

So, if you have everything you need, and the desire to learn how to create a mysql database has not yet disappeared, then let’s start by launching Apache. To run it, go to the path C:\WebServers\etc and run the Run file. If Apache starts correctly, without errors, then a red pen will be added to the existing icons in the lower right corner.

Now you need to make sure that the web server is working, enter the address http://localhost/ in the browser. In response you should receive the text “Hurray, it’s working!” If this is what you see on the monitor, then Apache is in complete order.

Now go to Utilities and select phpMyAdmin from the list, you have graphical shell, which allows you to manage mySQL DBMS. With its help, you can perform all operations without exception that are provided by the creators of this database, and specifically:

Database creation

In order to create a database, you must enter a name in the “Create a new database” field, for example, MyBase. Now click the “Create” button and phpMyAdmin will create a new database.

Creating tables

Creating tables is quite simple, you should select a base (MyBase). In the main window you will be prompted to enter a name for the table (try entering the name DataStudent), and you will also need to enter the number of fields (put the number 5). When choosing how many fields you will have, do not forget that one field goes under the key (ID). If you entered everything correctly, then press the “Enter” button.

Now you will see an additional form for creating tables. In it you can set fields, assign for each of them, name the columns, define them maximum dimensions. As a rule, the first field is the key one. We enter the name “ID” into it; now we need to decide on the type of data that will be entered for this column. Since there is no such type as a counter in MySQL, you should select Int and check the unsigned box in the attributes. Then we go to additional settings and set Auto-increment there, so that every time you enter a new value, this field independently increases its value by one.

Thus, you have a typical counter. It's time to move on to the next group of settings - RadioButton. Here we select the value “primary”; if it is activated, then our field becomes the primary key.

If you did everything correctly, then your main field is configured correctly; when you add rows in it, automatic increase ID values. And you are one step closer to understanding how to create a database MySQL data.

Moving on, now we need to define the values ​​and give names to the remaining columns of our table. Let it be Last name, first name, patronymic and, say, rating: “Fam”, “Name”, “Otch”, “Evaluation”, it remains to assign a data type - varChar, because these fields will store information in the form of a string. Don't forget to set and maximum length fields, it would be logical to limit it to 30 characters. Check all the entered data and admire the table you created. It should contain a set of fields with the names “ID”, “Fam”, “Name”, “Otch”, “Evaluation”.

I hope that the information in this article was useful to you and that you found the answer to the question of how to create a MySQL database.

How to create a database?




A database is one of the most convenient options for storing and working with data. Today, there are many ready-made shells for creating your own unique database, which makes the work of many enterprises easier. Such programs have user-friendly interface And big choice data types. Below we will look at how to create a database in the most popular programs.

How to create a sql database

Before you start creating the database itself, it is best to create its model. What is a database model? This is a detailed logical connection of data, a set of tables in which its name and what it is characterized by are recorded. For example, the “Clients” table will have the following fields: client name, client id, client address, contact information. In the same way, it is worth thinking through and listing in tables all the areas of data with which the database will work (customers, intermediaries, goods, etc.). After all the tables are ready, they need to be connected to each other. There are several main types of connections:

  • one to one,
  • one to many,
  • many to many.

For example, one customer can buy many products, so the relationship between the Customers and Products tables should be one to many. So, by analogy, we connect those tables that are logically related to each other. The database model can be simply drawn on paper by hand. Can also be used for this special programs(eg Rwin, BpWin).

Procedure:

  1. Install on your computer Visual Studio. It is worth installing an extended kit, since standard sql Excluded.
  2. Open Visual Studio, select the “Tools” → “Connect to Database” tab.
  3. In the window that appears, select the database type " Microsoft Sql Server Database file" → "Continue".
  4. In the window that appears, select the location for storing the database on your computer. Click "Ok". After this, the created database file will appear in the list of files on the right side of the screen.
  5. Double click on the database file. In the list that opens right click mouse click on “Tables” → “New”. An empty table field appears.
  6. Let's fill out the table. The tables are filled in accordance with the previously created model. In fact, we transfer the model to sql.
    For creating new table repeat point 5. One of the table fields must be set as key. To do this, select the required field (most often the key fields are fields containing id) and click on the key sign on the toolbar.
  7. For each line, you must select a data type. The data type determines in what form an object can store information in a given field. Once a specific data type has been established, entering data of another type will not be possible.
    If our string contains text value, then these can be the types CHAR(M), VARCHAR(M), TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT - depending on the expected amount of memory that the string will store.
    If the data type is number, then BOOLEAN, INTEGER, DECIMAL, FLOAT, REAL, DOUBLE, PRECISION are suitable. If the string will store time and date data, then we use DATE, TIME, TIMESTAMP, DATETIME.
    • Binary data can have the types "Binary", "image", "varbinary".
    • Other data types: "cursor", "hierarchyid", "sql_variant", "table", "timestamp", "uniqueidentifier", "xml", "Spatial types".

After filling out all the tables, your database will be created and brought into working form.

How to create a 1C database

A new database is created quite often in enterprises. This does not require special knowledge. A new database is created in 10 minutes. If you use 1c, then to create it in this shell you do not need to install anything additional. Before creation new base data must be backed up information base. Well, let's get started.

Dolphins have always inspired people's trust. We associate them with kindness and joy. Although the dolphin is a symbol of MySQL, this in no way explains the popularity it enjoys all over the world:

According to current statistics, MySQL ranks first in terms of prevalence on the Internet among all relational database systems.

MySQL Features

The procedure for creating a MySQL database is no different from other DBMSs. And its free nature is also hardly the main reason for the popularity of this system. For example, SQL Server from Microsoft. In every version of this product a free edition is being released, and with pretty good technical characteristics.

Features of the MySQL DBMS:

  • Most often used as a remote server;
  • Includes a large number of table types;
  • Comes with a special EXAMPLE type that shows how new tables can be created;
  • High degree of scalability due to support for most popular platforms;
  • Open source- thereby this DBMS is constantly improved and modernized by many developers around the world;
  • A large number of APIs have been created that provide interconnection between MySQL and the main part of all programming languages;
  • The maximum size of a database table file is limited only by the capabilities of the operating system used.

From the closest competitor MySQL systems MS SQL Server free edition Express limitation The database size is 10 GB.


  • The latest version of the DBMS 5.7.5m15 (test) was released in September 2014.

Creating a MySQL Database

The main logical and structural unit of data division in any relational model presentation of information is the basis. Above it is only the server. Any database consists of tables, each of which is divided into columns. Let's look at all the ways to create a database in MySQL.

The PHPMyAdmin environment is one of the most popular shells for working with MySQL. Its interface greatly facilitates database administration.

To create a MySQL database via PHPMyAdmin, do the following:

  • We go into the shell;
  • Go to the “Databases” tab;
  • Enter the name in the first field created base data, and from the drop-down list select the required encoding. In our case it is utf8_genegal_ci.

The database name must not exceed 64 characters.

  • Then click on the “Create” button:


  • After this, the name of the created MySQL database should appear in the lists on the left and below:


Now let's create the first table in our database. We do the following:

  • In the list on the left, find the name of our database and click on it:


  • In the next window, enter the name of the table and set the number of columns;
  • Then click on the “Ok” button.

The same can be done by clicking on the “Create table” link immediately below the list of databases on the left, but then the next window will display a template without a name and with four columns.

  • The next step is to set the structure of our table. We specify the names and types of data that will be stored in the columns of the table;
  • After that, click on the “Save” button:


  • Our MySQL database table has been created and is ready to be filled with data:


But it is not the only way how can you create a database in PHPMyAdmin. A similar effect can be obtained if you use an SQL query. To do this, use the CREATE command. Its syntax is:

CREATE DATABASE db_name ;

Arguments:

  • IF NOT EXISTS – used to track the uniqueness of the database name. If you do not specify this parameter, then if a database with the same name is created, a query execution error may occur;
  • db_name – indicates the name of the database being created;
  • CHARACTER SET charset – sets the database encoding. If not specified, the default value is used;
  • COLLATE collation – sets the data sorting order. Optional parameter.

Now let's create a database using an SQL query through the PHPMyAdmin shell:

  • Go to the “SQL” tab;
  • In the editor window that appears, enter a request to create a database;
  • Or click on the “Request window” icon. It is located to the left above the list of databases:


  • Enter the request text:

CREATE DATABASE `my_db`;

  • Click on “Ok” below:


  • After this, the name of our database will appear in the list on the left:


For delete sql database, use the DROP DATABASE "my_db" command.

CREATE DATABASE `my_db` CHARACTER SET utf8 COLLATE utf8_general_ci;


Setting up database backup

The ability to restore a database is very important. In case of unforeseen situations, restoration will help to recover lost information and quickly resume the operation of the resource.

Setting it up backup databases in PHPMyAdmin. Procedure:

  • In the list on the left, select the database we need;
  • Click on the “Export” tab;
  • Click "Ok".


If you select “Normal” in the “Export method” section, a large window will open in front of you with many parameters for customization:


Now let's try to restore the saved copy of the database. But first, let's delete the existing instance. Go to the “Operations” tab and click the link “ Delete database»:


Recovery MySQL databases In PHPMyAdmin, go to the “Import” tab. In chapter " Imported file"Depending on the location where you saved the copy of the database, select the source. After that, click on the “OK” button located at the bottom of the screen:


Sometimes, after making some changes, you need to restore not the entire database, but only a specific table. This feature is also implemented in PHPMyAdmin. To do this, on the page of the desired table at the bottom of its structure, select the appropriate item from the drop-down list and click on “Ok” at the bottom:


Database compression in MySQL

Unfortunately, MySQL does not support changing or limiting the size of databases. Of the tools built into PHPMyAdmin, you can only use table optimization for this. This operation is another option from the drop-down list shown above.

Also, to reduce the size of the database, it is recommended to save its backup copies in the form of archives. Compression (compression) backup copies is configured in the item of the same name on the “Export” tab in the “Output” section:


Another way to reduce the size of a MySQL database is the following set of actions:

  • Creating a dump (copy) via command line using the mysqldump command;
  • Delete all databases;
  • Stop all MySQL services;


2024 gtavrl.ru.