Some of the main things you can do with id: create/drop a database, create users with different privileges for databases, create/empty/drop tables, insert/update/delete tables' information, run SQL queries in databases, import/export databases/tables.
1. Set the language for PhpMyAdmin
In the login window you have the option for language. Choose the one you want to work in, insert the username and password for th application and login to it.
2. Create a database
After you successfully login to PhpMyAdmin you can create a new database. One of the details is the Collation. If you plan to store in database data that will contain diacritics, than choose at least utf8_general_ci as collation. After you create the database, you can create tables (which are related to that database)
3. Create a table
4. Define the column types of a table
More about MySQL types here.
For data kept in a certain column you can set the default value as NULL (the information kept in that table does not need to be initially defined, it can be "inserted" in that table after the reference to it is defined - in SQL representation it means that data can be added in the table at UPDATE), or you can define a default value (as shown in the picture below). This is used especially for columns which keep flag values.
When creating a table another important issue is the INDEX - in SQL a primary key. It usually is set as auto-increment. The options to do that are shown in the picture below:
Also, for table's columns which might contain diacritics (such as title and content for an article) you can define the needed collation.
If you find out you need more columns, you can write the number of extra needed columns in the text field in right bottom of the columns definition page and click Add, or if you finished defining the description of table's columns click Save. Now you can see the structure of the defined table as shown in the picture below. The actions that can be taken to a column(from left to right) are: Browse (view the values of that column), Edit, Delete, Make Primary key, Make Unique key, Fulltext (used in fulltext searches, available in MyISAM tables)
A detail regarding DATE fields. Per table can be defined one TIMESTAMP column. There also can be used INT(11) to manipulate fields containing date. In the picture below is edited the created_at column to be timestamp and the default value to be the current timestamp.
4. Insert data into table
When you click on Insert in the main tabs on the page (between Search and Export), you can put down the values for each column. The default null columns can contain no data, also the primary key or the columns which have defined a default value. After you insert the needed information, click Go. As you will see in the first image below, at the bottom of the columns editing there are some more actions you can take:
- what action to be taken after insertion, Default is: "Go back to previous page". Another option here is "Insert another new row". You can choose this if you want to continue to insert values into the table.
- Also, you can insert in one action more than 2 rows. "Restart insertion with n rows", The n can be selected from a list.
5. Writing interrogation for data in tables
This is not so a "beginners" field, but for those who want to learn more about SQL queries, can read for a start here. To write SQL queries go to SQL tab and after you write the query, click on GO to run it.
Below are 2 examples:
1. How select only data which has flag is_published set on 1 (first picture, the query, second the select result)
2. for all rows which are not published (flag is_published is 0), make the content NULL
6. Export a database
Before you start "playing" with tables and queries a wise think to do is to Export the database, so you make sure you can recover the data if you mess up something. For that go to Export tab (between Insert and Import). You can backup data on the database's server or save them in a file (which can be used to import the database to another location).
7. Delete a database
On the top of the PhpMyAdmin page you can see some links which are a breadcrumb. Click on Server:your_server_name and now you have in the main tabs the option of databases. When click a list of all databases defined on server will be displayed. You can select the checkbox(es) for the database(s) you want to delete and click the red X button below the list (if you mouse over that button it will show you the text "Drop").
Note: to be able to delete a database the user you are logged in must have the privileges to do that action.
8. Create users and give them privileges for your databases
As mentioned above (section 7.) an user can have different privileges on a database. To define an user and set his rights on a database, go to Server link and choose Privileges. Here you can Add a new user or edit the already existing ones.
Note: be careful with what you set in this section. Don't give everyone the right to delete your databases:)
9. Import a database
If you exported a database and save it in a file, now you can go to another location (MySQL server) and import it there. In my example, I have dropped the for_example database and created a new one called second_example. Now I want to bring all I had in the first database into the second one with only 2 clicks (metaphorically speaking...or not!). Go to the new database where you want to do the import. First click would be on the Import tab (between Export and Designer). Select the file you created on the export of the first database. The second click should be on Go.
No comments:
Post a Comment