MySQL Manager

From wikihelp
(Redirected from Mysqldbmanager)
Jump to: navigation, search

Contents

Introduction to MySQL Database Manager

Database Manager is a web based MySQL client that allows you to create and manipulate a maximum of two MySQL databases.

Database Manager is designed for advanced users. Knowledge of relational databases and SQL is required in order to use this Database Manager efficiently.

Database Manager is best used for creating databases and tables. For populating a database, you should consider using a script(PHP, ColdFusion) or CGI.

Database Manager builds SQL statements from the various options that you select. This is the reason for the "Build SQL Query" button. Viewing the Query gives you an opportunity to review the query before running it. Clicking the "Run SQL Query" button submits the query to the SQL server.

You can find more information about MySQL database at:

http://www.mysql.com/

Setting your Database Manager password

The first time you access Database Manager, you are prompted to set a password. Enter a password in the "Enter password" textbox and the “Confirm password” textbox, and click the "Create user" button to complete the operation.

Note that this password is only for database administration.

Click the "Databases" tab to start using Database Manager. You will be prompted to enter this password in order to log into Database Manager. Enter your Database Manager password in the "Enter Password" textbox and click the "Login" button to complete the operation.

Changing your Database Manager password

To change your Database Manager password at any time, click the "Users" tab from the "Database Manager" main page. Enter your new password in the "Enter New Password" textbox and check the box labeled “Yes, I want to change the password for the selected user.” Click “OK.” The result of operation will appear at bottom of the page.

Creating a database

To create a database, click on the Databases tab from the "Database Manager" page. Select the "Create" option from the "Select" list and choose Database from the second list. Enter the name of the database you want to create and click the "Build SQL Query" button. The detail of SQL query will be displayed at the bottom of the page. After reviewing the detail of SQL query click the "Run SQL Query" button to complete the operation. The results of operation will be displayed at the bottom of the page.

Your user name is appended to the end of every database name. The Total maximum length is 64 characters long and any "." (dot) or "-" (dash) in the database name is automatically converted to "_" (underscore).

For example if you choose "customers" as your database name and your domain is mycar.com, the complete database name will be:

customers_mycar_com

Connecting to your database

In order to connect to the database you have created, you need the following arguments to include in your application/script:

Database user name: [first 10 characters of domain name] + [random 6-digit number]

example: if the domain name was "bob.the-mechanic.com", then the databse username would be something like bobthemech386296. Note that all non-alpha characters were removed ("." and "-").

Password: The password will be the same as your Database Manager password.

Database name: It will be the complete name of database as outlined in Creating a database.

Please contact technical support for your MySQL server name (host name).

Creating a table

Using this option, you can create tables within an existing database.

To create a table, choose the "Create" option from the "Select" list. Select "Table" from the next list. Enter the name of table you want to create in the "Table Name" textbox.

In order to create a table, you need to define at least one column for your table. Enter the column name in the "Column Name" textbox. Choose the data type for the column from the drop-down menu. Assign the attributes for the selected data type by clicking the appropriate check boxes next to data type drop-down menu. You can enter the maximum length of data for the column in the "length" textbox. Enter the default value for the column in the "Default" textbox if necessary. Click the "Build SQL Query" button. After reviewing the SQL Query click the "Run SQL Query" button to complete the operation. The result of operation will appear at the bottom of the page.

Creating an index

"Create Index" allows you to add an index to an existing table.

To create an index, select "Create" from the "Select" list. Choose "Index" from the next list and then select "ON". A list of tables within your database will be displayed, choose the table you want to create the index for. Enter the name of index in the "Index Name" textbox and select the column name you want to be used for indexing. If you want the entries for the indexed column to be unique click the "Unique" checkbox. Select the order in which you want to index your column, from the "Column Order" drop-down menu. Click the "Build SQL Query" button and after reviewing the SQL Query click on the "Run SQL Query" to complete the operation. The result of operation will appear at the bottom of the page.

Dropping a database

This option allows you to remove permanently a database with all of its associated files.

Note that if you drop a database, it can not be restored.

To drop a database, choose "Drop" from the "Select" list, choose the Database option from the next list and select the database you want to drop. The SQL query details will appear at the bottom of the page. Review the query and click the "Run SQL Query" button to complete the operation. The result of operation will appear at the bottom of the page.

Using a database

The "Use" option allows you to select a default database, to be used for subsequent queries.

Every time you login to the Database Manager, you have to use this option to access an existing database.

To use a database select the "Use" option from the "Select" list and choose the specific database you want to work with. Click the "Run SQL Query" to complete the operation.

After performing "Use Database", you will have several options in the "Select" list to choose from in order to make changes to the database. These options include:

  • Alter
  • Create
  • Delete
  • Drop
  • Desc
  • Insert
  • Select
  • Show
  • Update

Altering a table

Introduction to altering a table

To alter a table, choose "Alter" from the "Select" list. Select the table you want to alter. A list with the following actions will be displayed in the "Database Manager" page(we will call this list the "Alter list" ). You can use these actions in order to modify the structure of a table:

  • Alter
  • Add
  • Change
  • Drop
  • Modify
  • Rename

Altering a column

To alter a column, select "Alter" from the "Select" list. Choose "Alter" from the Alter list. You will have two options at this point : Set and Drop.

To set the default value for a column, select the column that you want to set the default value for and enter the default value in the "Column Name" textbox and click the "Build SQL Query" button. After reviewing the SQL query click the "Run SQL Query" button to complete the operation.

To drop the existing default value of a column, choose the "Drop" option. Select the column you want to drop the default value for and click on the "Build SQL Query" button. After reviewing the SQL query click the "Run SQL Query" to complete the operation. The result of the operation will be displayed at the bottom of the page.

Adding a column to the table

To add a column to the table, choose the "Alter" option from the "Select" list. Select the "Add" option from the "Alter" list, then select "Column" from the next list. Enter the new column name in the "Column Name" textbox and select the column type from the "Column Type" drop-down menu. Specify the location of the new column in the table by clicking one of the "Insert Column" radio buttons. If you click "After" radio button, you have to select a column from the "Column Name" list.

  • First: will insert the new column before the first existing column.
  • Last: will insert the new column after the last existing column.
  • After: will insert the new column after the column selected from the "Column Name" menu.

Choose the data type for the column from the drop-down menu. Assign the attributes for the selected data type by clicking the appropriate check boxes next to the data type drop-down menu. You can enter the maximum length of data for the column in the "length" textbox. Enter the default value for the column in the "Default" textbox if necessary. Click the "Build SQL Query" button. After reviewing the SQL Query click the "Run SQL Query" button to complete the operation. The result of operation will appear at the bottom of the page.

Adding an index to the table

Normally, you create all indexes on a table at the time the table itself is created with "Create Table". "Add Index" allows you to add indexes to the existing tables.

To add an index to your table, choose the "Alter" option from the "Select" list. Select "Add" from the "Alter" list, then select "Index" from the next list. Enter a name for the index in the "Index Name" textbox. Select the column that you want to index, from the "Column Name" list. Select the order in which you want to index from the "Column Order" drop-down menu. Click the "Build SQL Query" button and after reviewing the SQL Query click on the "Run SQL Query" to complete the operation. The result of operation will appear at the bottom of the page.

Creating a unique index for the table

This option is identical to "Add Index" except that the values of the indexed column are guaranteed to be unique. It means if a user attempts to add a value that already exists to a unique index, it will return an error.

To create a unique index, choose the "Alter" option from the "Select" list. Select the "Add" option from the "Alter" list and select "Unique" from the next list. Enter a name for the unique index in the "Unique Name" textbox. Choose the column, you want to index, from the "Column Name" list. Click the "Build SQL Query" button and after reviewing the SQL Query click on the "Run SQL Query" to complete the operation. The result of operation will appear at the bottom of the page.

Making changes to a column

This option allows you to change the name, type and attributes of an existing column. You can use this option for renaming the column without having to create a new column and losing the data as a result.

Note that when you want to rename a column, you still need to set the data type and attribute fields for the query return your desired result.

To change a column, choose the "Alter" from the "Select" list. Select the "Change" option from the "Alter" list. Choose the column you want to make changes to from the "change Column Name" list . Enter the new name in the "to new Column Name" textbox . Choose the new attributes for the column.

After making the necessary changes click the "Build SQL Query" button and after reviewing the SQL query click the "Run SQL Query" button to complete the operation. The result of the operation will be displayed at the bottom of the page.

Dropping a column

Using this option, you can delete a column from a table. Note that this will remove a column and all of its data from a table permanently. The data destroyed in this manner can't be recovered.

To delete a column from the table, select "Alter" from the "Select" list . Choose the "Drop" option of the "Alter" list, then select "Column" from the next list. Choose the column you want to drop from the "Column Name" list and click the "Build SQL Query" . After reviewing the SQL query click the "Run SQL Query" to complete the operation. The result of operation will be displayed at the bottom of the page.

Modifying a column

The modify option is the same as change except that it does not allow you to change the column name. You can change the attributes of a column using the modify option.

To modify a column, choose "Alter" from the "Select" list. Select the "Modify" option from the "Alter" list. Choose the column you want to make changes to from the "change Column Name" list . Set the desired attributes for the column and click the "Build SQL Query" button. After reviewing the SQL query click the "Run SQL Query" button to complete the operation. The result of the operation will be displayed at the bottom of the page.

Changing the table name

The "Rename" option allows you to change the name of a table. This operation does not affect any of the data or indices within a table, but the table's name.

To change the name of a table, choose "Alter" from the "Select" list. Select the "Alter" option from the "Select" list. Choose the table you want to rename and choose the "Rename" option from the "Alter" list. Enter the new name for the table in the "to New Table Name" textbox and click the "Build SQL Query" button. After reviewing the SQL Query click the "Run SQL Query" button to complete the operation. The result of operation will appear at the bottom of the page.

Deleting rows from a table

This option allows you to delete rows from a table. Note that if you do not set a condition for deleting rows, this will erase the entire table and recreate it as an empty table. The deleted data can not be recovered.

To delete rows from a table, select the "Delete" option from the "Select" list. Then choose the table from the tables list. Set the condition for deleting the rows using the "optional where" panel fields. Choose the column name based on which you want to set the condition from the "Column Name" list. Select the "Equals" or "Like" operators from the drop-down menu. Enter the value of the selected column for the rows you want to delete. Click the "Build SQL Query" button. After reviewing the SQL Query click the "Run SQL Query" button to complete the operation. The result of operation will appear at the bottom of the page.

Dropping a table

This option will erase an entire table permanently and deletes the data associated with the table.

To drop a table, choose the "Drop" option from the "Select" list. Select "Table" from the next list. A list of tables inside your database will appear. Select the table you want to delete and click the "Run SQL Query" button. The result of the operation will be displayed at the bottom of the page.

Getting information about a table or column

The "Desc" option will allow you to get information about a table or column.

To describe a table, choose the "Desc" option from the "Select" list. Select the table you want to get information for. If you want the information about a specific column, you should select a column from the "Column Name" list from the "Optional" panel. Click the "Build SQL Query" button and after reviewing the SQL query click the "Run SQL Query" button to complete the operation. The complete information about all columns or the specified column will be displayed at the bottom of the page.

Inserting data into a table

This option allows you to insert data into a table. Using this option, you can insert a given value to a given column. To complete the other columns of the same row, you should use the "Update" option from the "Select" list. Columns in the table that are not assigned any value are set to their default value or NULL.

To insert data into a table, choose "Insert" from the "Select" list and select the table you want to add the data to. From the "Column Name" list, choose the column you want to add a value to. Enter the value into the "Column Value" textbox and click the "Build SQL Query" button. After reviewing the SQL Query, click the "Run SQL Query" button to complete the operation. The result of the operation will appear at the bottom of the page.

Updating data within a table

This option allows you to alter data within a table. It is used to change actual data within a table. After creating a row using the "Insert" option and inserting a given value to a given column, you should use this option to populate the other fields of the same row.

To update a row in the table, choose "Update" from the "Select" list and select the table you want to update. From the "Column Name" list, choose the column you want to add a value to. Enter the value into the "Column Value" textbox.

You should use "Where clause" in order to enter data into a specific row in a table by specifying a value that must be matched by the column in question.

In the "optional where" panel, choose the column name from the "Column Name" list. Select "equal" or "like" from the drop-down menu and enter the matching value in the "Column Value" textbox.

Click the "Build SQL Query" button. After reviewing the SQL Query click the "Run SQL Query" button to complete the operation. The result of operation will appear at the bottom of the page.

Selecting data from the database

This option allows you to retrieve data from database tables.

To retrieve data from a database, choose the "Select " option from the "Select" list. Select the table you want to retrieve data from. Now you have three option to choose from :

  1. To display all columns from the table, click the "All Columns" checkbox. After reviewing the SQL Query, click the "Run SQL Query" button to complete the operation. The result of the query will be displayed at the bottom of the page.
  2. To display an individual column, choose the column you want to display from the "Individual Column Name" list. Ensure that option 1, "All Columns" is not highlighted. Click the "Build SQL Query" button. After reviewing the SQL Query click the "Run SQL Query" to complete the operation. The result of the query will be displayed at the bottom of the page.
  3. To display data from the database using the aggregate functions, select your desired aggregate function from the "aggregate function" list. Choose the column from the "Column Name" list. Ensure that option 1, "All Columns" or option 2, Individual Column" are not highlighted. Click the "Build SQL Query" button. After reviewing the SQL Query click the "Run SQL Query" to complete the operation. The result of the query will be displayed at the bottom of the page.

You can use the "where clause" in order to pick out specific rows in a table by specifying a value that must be matched by the column in question. You should combine this by any one of the above options.

In the "optional where" panel, choose the column name from the "Column Name" list. Select "equal" or "like" from the drop-down menu and enter the matching value in the "Column Value" textbox.

Click the “Back” button to return to the query building page after you have generated a result set.

Display information about the database

Display information about columns

This option allows you to display information about the columns within a table.

Choose "Show" from the "Select" list and select "Fields" from the next list. Highlight "from", the list of tables within the database will be displayed. Select a table and click the "Run SQL Query" button to complete the operation. The complete information about the columns will be displayed at the bottom of the page.

Display information about table indexes

This option allows you to get information about indexes on a table. This is synonymous to the "Show Keys" option.

Choose "Show" from the "Select" list and select "Index" from the next list. Highlight "from", the list of tables within the database will be displayed. Select a table and click the "Run SQL Query" button to complete the operation. The complete information about the table indexes will be displayed at the bottom of the page.

Display information about table keys

This option allows you to get information about indexes on a table. This is synonymous to the "Show Index" option.

Choose "Show" from the "Select" list and select "Keys" from the next list. Highlight "from", the list of tables within the database will be displayed. Select a table and click the "Run SQL Query" button to complete the operation. The complete information about the table indexes will be displayed at the bottom of the page.

Display the list of tables in a database

This option allows you to display a list of tables within a data base.

Choose "Show" from the "Select" list and select "Tables" from the next list. Click the "Run SQL Query" button to complete the operation. A list of tables will be displayed at the bottom of the page.

Personal tools
Namespaces

Variants
Actions
NAVIGATION
Design
Website Tools
Ecommerce
E-mail
Domains
SEO and Analytics
Advanced Tools
Website Add-Ons
Online Marketing
Social
Webmail
Applications