Mysql phpmyadmin table fields attributes unsigned. MySQL foreign keys and their configuration in phpMyAdmin
Good day to all! Today I will tell you about how to create a database inphpmyadmin. And so, first of all, we start the server and go to phpMyAdmin. If you the server is installed on the local computer, That in Danwer phpMyAdmin is located at http://localhost/tools/phpmyadmin/, and in WAMP and on other servers, phpMyAdmin is available at http://localhost/phpmyadmin/
On real hosting, phpMyadmin is available at a different address. Which one you need to find out from your hosting provider.
In version 5.5 it looks like this:
Next, in the new database name field, enter the name of our database, for example test. In the comparison field, select the database encoding, utf8_general_ci. If we do not specify the encoding, that is, we leave the “comparison” as is, then this encoding is selected by default. After filling out these fields, click on the button create.
A message will appear in the middle of the screen that the test database has been created. It will appear in the list with the created databases. We find it in this list, click on its name and find ourselves inside the created database. Initially it is empty, so let's create a table.
There are two ways to create tables:
First way: In the left column click on create table
And the following window will appear:
Set a name for the table and a title for each field. For example, we want to create a table with website categories. Therefore, we set the table name categories. For this table we only need 2 fields, these are id and category names ( name) According to the rules in any table, the first field always has the name id, that is, an identifier. For this field, be sure to select the INT type and set the length of the value in numbers, for example 2. We select the index PRIMARY, so we give it a primary key and insert a checkbox for A_I (Auto Increment) so that its value is automatically increased by one.
For the category name field, select the VARCHAR type and set the maximum length to 255 characters.
If you want to add one more or more fields, then in the field add, enter the number that corresponds to the number of fields you want to add and click ok.
We don’t need four fields, but only two, so we filled in only two fields and left the rest empty. Only filled fields will be added to the table. After you have filled out all the required fields, move down the page a little lower and click on Save.
That's it, a table with a name categories created. It will appear in the list of tables on the left side of the screen.
Second way:
Immediately after entering the database we see such a block Create table. This block has 2 fields where we can enter the name of the table and the number of columns in the table. Let's create, for example, a table with registered users, let's call this table users. This table will have the following fields: id, login, password, e-mail, f_name, s_name, In the number of columns field, enter 6 and click OK.
The same window will appear as in the first method.
After filling out all the fields, go down a little lower and click on the button save.
All table users created. It was also added to the left list with the created databases.
That's all for today. Now you know, how to create MySQL database and tables in phpmyadmin without difficulties and puzzles. I wish everyone good luck!
Did you like the article?
Laboratory report No. 11
Discipline:"Web programming"
Topic: “MySQL DBMS. Working with the phpMyAdmin utility »
Option No. 14
Theoretical part
The phpMyAdmin utility is implemented in PHP language and is available for free download from the website www.phpmyadmin.net. This tool has a user-friendly interface and allows you to rip out the language of communication.
Creating tables using phpMyAdmin. The phpMyAdmin page has a special field for creating a new table in this database.
Enter the name of the new table (for example, book_new) and indicate in the adjacent field what should be in this table, for example, four columns – 4 (id - number, title - title of the book, author - author, price - price).
Click the button OK This will create a four-column table book_new in database db_1408057. A window will appear on the screen with fields for setting the properties of the new table (each line contains attribute fields of one column of the new table). Add the necessary attributes to it.
To make changes to the column parameters, you must click on the “pencil” icon in the corresponding line on this page.
To view the general structure of the database, click on the name of the selected database in the left field.
To enter data into a new table, you need to click on the button in the row of this table in the general list Insert.
After filling out these fields, click the button OK.
Having completed filling out the table, return to the previous page with summary data for all tables in the database - and in the book_new table row, click on the button Review. As a result, the contents of the table will be displayed, that is, the data just entered into it (see the table below).
If there are many rows in the table and all of them are not immediately visible, then the button Show allow you to specify starting from which line and how many lines should be displayed in the window.
To make changes to an individual entry, click on the “pencil” icon in the line displaying the contents of this entry.
This way you can fill in all the necessary tables.
Executing queries against database tables. To execute an SQL query, click on the button SQL. A window will open in which you can type an SQL query.
The structure of the database in graphical form (according to the topic of the course work “Kindergarten”).
Figure 1. Database logical model
Figure 2. Physical model of the database
The procedure for working with the phpMyAdmin utility to create tables, enter, select, change and delete data.
Figure 3. Creating a database named “g141003_nalivko”
Figure 4. Creating a Deti database table, where the number of fields is 5
Figure 5. Displaying a window with fields for setting properties of the new Deti table
Figure 6. Displaying the structure of the Deti table.
Figure 7. Making changes to the parameters of the Deti table column by clicking on the “pencil” icon in the corresponding row on this page.
Figure 8. Entering data into the Deti table by clicking on the Insert button in a row of this table in the general list
Figure 9. Filling in the fields in the Deti table
Figure 10. Adding one row to the Deti table
Figure 11. Displaying the contents of the table Deti, that is, the data just entered into it when you click on the Review button.
Figure 12. Displaying the contents of the Deti table.
Figure 13. Deleting a record from the Deti table.
Figure 14. Display of the Groupy table structure.
Figure 15. Displaying the contents of the Groupy table.
Figure 16. Display of the structure of the Vospitateli table.
Figure 17. Displaying the contents of the Vospitateli table.
Figure 18. Display of the g141003_nalivko database structure.
Figure 20. Result of a select query on the Deti table
Figure 22. Result of a query to select data from the Deti table, the group number of which is 13
Figure 24. Result of a query to select data from the fields id_g, id_v, id_k of the Gruppy table
Figure 26. Result of a query to select data from the fields id_v, fio_v, telefon of the Vospitateli table
Figure 27. Sorting alphabetically by fio_v field in the Vospitateli table
Figure 28. Result of sorting in alphabetical order by the fio_v field in the Vospitateli table
Figure 29. Sorting alphabetically by the id_k field in the Gruppy table
Figure 30. Result of sorting in alphabetical order by the id_k field in the Groupy table
Figure 31. Sort alphabetically by fio_d field in the Deti table
Figure 32. Result of sorting in alphabetical order by the fio_d field in the Deti table
Figure 33. Grouping data in the Deti table by the id_g field
Figure 34. The result of grouping data in the Deti table by the id_g field
Figure 35. Grouping data in the Groupy table by the id_k field
Figure 36. Result of grouping data in the Groupy table by field id_k
Figure 37. Grouping data in the Vospitateli table by the id_g field
Figure 38. Result of grouping data in the Vospitateli table by the id_g field
Figure 39. Using the aggregate function COUNT() – counting the number of records in the Vospitateli table by the fio_v field
Figure 40. The result of using the aggregate function COUNT() - counting the number of records in the Vospitateli table by the fio_v field
Figure 41. Using the aggregate function MAX() – maximum value in the Groupy table by field id_k
Figure 42. The result of using the aggregate function MAX() - the maximum value in the Groupy table by field id_k
Figure 43. Using the aggregate function SUM() - calculates the sum in the Deti table by the id_g field
Figure 44. Result of using the aggregate function SUM() - calculates the sum in the Deti table by the id_g field
To connect to phpMyAdmin you can use the already created database u1234567_default(Where u1234567— your hosting login), which is automatically created when you order hosting. Access details for this database are given in the article.
Attention Database u1234567_default is created only when ordering tariff plans from “Host-0” and higher. If you ordered the “Host-Lite” tariff plan and then upgraded it to a higher one, this database will not be created. You need to create the database yourself according to the instructions:
I can't login to phpMyAdmin
To log into phpMyAdmin you need to use database user login and password. Do not confuse the database user login and password with the hosting service login and password (u1234567).
If the database password is not suitable, you can change it to a new one using the instructions:
How to create, delete or modify a table in the phpMyAdmin database?
Attention! Editing the database may cause your site to not work correctly. Before making changes, create a backup of your site or contact the developers.
How to add a new table to a database?
Last update: 12/22/2017
Typically, databases are used as data storage. PHP allows you to use various database management systems, but the most popular today in conjunction with PHP is MySQL. MySQL is free software that allows you to interact with databases using SQL commands. The process of installing and configuring MySQL has already been discussed.
To make it easier for us to work with MySQL databases, let's install a special set of phpMyAdmin scripts. phpMyAdmin provides an intuitive web interface for managing MySQL databases. Using this tool, it is much easier to work with databases than to manage MySQL through the console.
To install phpMyAdmin, download the archive and unpack it into a folder C:\localhost where are the docs for php. Let's rename the unpacked folder to phpMyAdmin for brevity.
In the unpacked phpMyAdmin directory, create a file config.inc.php with the following content:
And to make sure that everything is configured correctly, in the browser let's go to phpMyAdmin, for example, http://localhost:8080/phpmyadmin:
In the left column you can see all the available databases on the MySQL server. Even if you haven't created any databases yet, the server already has a set of default databases.
The right side of the phpMyAdmin interface contains basic database management tools, as well as various configuration information.
Creating a MySQL Database in phpMyAdmin
To exchange data with the MySQL server (save, change, delete, retrieve data), we naturally need a database. We can create a database from the MySQL console, as well as from the phpMyAdmin visual interface.
Let's open the phpMyAdmin interface. Let's go to the Databases tab. Under the label Create a database Let's enter some name for the new database, for example, compstore and click on the "Create" button.
And after that we will receive a message about the successful creation of a new database, and it will be added to the database lists.
The new database is still empty and contains nothing. Let's add a table to it that will store the data. To do this, click on the name of the database and we will be taken to the “Structure” tab, where we will be offered options for the new table. In the "Name" field, enter the name of the new table. Let the table store data about smartphone models, so let's enter the name "phones", and enter the number 3 as the number of columns:
To create a table, click on the “Forward” button. After this, we will have a set of cells for setting column parameters. Let us indicate the following sequentially for the column names: id, name, company. As a type, we will specify the INT type for the id columns, and the VARCHAR type for the name and company columns. For the name and company columns, in the "Length/Values" field, enter the number 200 - it will indicate the maximum line length in characters. Also, for the id column, indicate PRIMARY in the “Index” field and put a checkmark in the “A_I” (AutoIncrement) field:
So the table will have columns for Unique ID, Phone Name and Manufacturer Name. And then click on the “Save” button below.
After creating the table, we can see the table and its columns in the database column:
This is not the only way to create tables in phpMyAdmin, as here we can manipulate the database using SQL queries. So, select our database in the list of databases and go to the “SQL” tab. It displays a field for entering a command in the SQL query language. Let's enter the following command into it:
CREATE Table phones1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, company VARCHAR(200) NOT NULL)
This is a standard SQL table creation command. After the CREATE Table keywords comes the name of the table being created, and then the column definitions in parentheses, separated by commas.
Each column definition contains the column name, its type, and a number of additional values. For example, the definition of the id column (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) indicates the name of the column, the type is INT, and also that the column must have a value - NOT NULL, that its value will automatically increase by one with the addition of a new object - AUTO_INCREMENT, and that it plays the role of a primary key - PRIMARY KEY.
In general, the table created in this way will be equivalent to the one created earlier. Click the Forward button and a second table, phones1, will be created.
In this post we will learn how to create communications between tables in a database MySQL by using phpmyadmin. If for some reason you do not wish to use phpmyadmin, see the SQL queries below.
Why is it convenient to keep connections? in the database itself? After all, this task is usually solved by the application itself?It's all about the restrictions and change actions that can be placed on the connections.
For example, you can prohibit deleting a category if at least one note is associated with it. Or delete all notes if the category is deleted. Or set NULL to the linking field. In any case, with the help of connections, the fault tolerance and reliability of the application increases.
To begin with, the table engine must beInnoDB. Only it supports foreign keys (foreign key). If you have tablesMyISAM, read how to convert them toInnoDB .
In order to link tables by fields, you must first add to index linked fields:
IN phpmyadmin select the table, select the structure mode, select the field for which we will make an external link and click Index.
Note the difference between "Index" and "Unique". A unique index can be used, for example, before the id field, that is, where the values are not repeated.
The same action can be done using SQL-query:
ALTER TABLE `table_name` ADD INDEX (`field_name`) ;
Similarly, we add an index (only in my case, now unique or primary) for the table we are referring to, for the id field. Since the id field is an identifier, we create a primary key for it. A unique key might be needed for other unique fields.
By using SQL-query:
ALTER TABLE `table_name` ADD UNIQUE (`field_name`);
Now all that's left is link tables. To do this, click on the Contacts item below:
Now, for the available fields (and only indexed fields are available), we select the connection with external tables and actions when changing records in the tables:
Through SQL-request:
ALTER TABLE `table_name` ADD FOREIGN KEY (`field_in_table_name_which_need_connect`) REFERENCES `outer_table_to_connect` (`outer_field`) ON DELETE RESTRICT ON UPDATE RESTRICT ;
That's all, the tables are connected via foreign key.