MYSQL DATABASE ENVIRONMENT BASICS

When you open the client, you will be prompted to enter the password for the root account. This is the password that was previously configured. Type the password and hit Enter.

You should see that you have entered the MySQL environment:

To create a database, type the following command:

CREATE DATABASE uestudio_sample;

where "uestudio_sample" is the name of the database. Note: be sure to include the semi-colon at the end of the command.

If the command is successful you will see something similar to:

Query OK, 1 row affected (0.00 sec)

You may verify that the databases have been created by using the "Show Databases" command. If you type, "Show Databases;" and hit enter, you should see something similar to:

Create a Table
For the purposes of this Power Tip, we will create a table called "names" that includes the following fields: key, first, last. You may create this table using the following steps:

Type:

USE uestudio_sample;

This will tell MySQL which database to use, then type:

CREATE TABLE names (id INT NOT NULL AUTO_INCREMENT, first VARCHAR(48), last VARCHAR(48), PRIMARY KEY(id));

We will not cover the syntax of the command (above) in the scope of this Power Tip.

If you wish to see the structure of the table, and confirm it was created correctly, type:

Describe Names;

You should see something similar to:

Now, we need to insert sample data into our table. For example, if we want to create an entry to insert the name "John" (first) "Smith" (last) into the table "names", we would do so using the following command:

INSERT INTO names (first, last) VALUES ('John', 'Smith');

You may insert additional data by modifying the VALUES. Because the id is an "auto increment", we do not need to specify a value for this field.

To display all the data in the table, simply type:

SELECT * FROM names;

Because we inserted a few other names into our table, the query produced the following results:

Create a User
Now that you have a database, and data, you will want to create a user (account) that will be used by the server (php) to connect to the database. You may access the database with the "root" account, however this is not typically recommended.

To create a user account you will need to use the following command line:

GRANT ALL PRIVILEGES on uestudio_sample.* to idm@'localhost' identified by 'helloworld';

"idm" is the Username and "helloworld" is the password for the user.