Linux Newbie Administrator Guide

3.23.56

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

The last "mysql>" represents the mysql prompt.

Step four. Create a user that can read, write and create tables in "my_database". To do this I used the GRANT command while logged on to MySQL with root privileges.

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER

-> ON my_database.* TO peter@localhost

-> IDENTIFIED BY 'peters_password';

Note, MySQL only processes the command when ended with a semicolon. Once entered, you should get an output similar to this,

Query OK, 0 rows affected (0.00 sec)

mysql>

To log out of MySQL just type:

mysql> exit

Now you can log on to MySQL with your new user account and start building your new database.

mysql --user=peter --password='peters_password' my_database

Step five. Create a table in my_database. To add a table to your database use the CREATE TABLE command. The syntax for the command is as follows:

CREATE TABLE ([column_name] [column_type] [DEFAULT value],...)

My command looked like this:

mysql> CREATE TABLE my_table (person TEXT DEFAULT "", age INTEGER DEFAULT 0) ;

To alter the table once you have created it, you can use the ALTER TABLE command. To add another column to the table my_table with the name "height" of the type REAL and with a DEFAULT value of NULL, I would use the following command:

mysql> ALTER TABLE my_table ADD COLUMN (height REAL DEFAULT NULL) ;

Step six. Populate the table with values. Note, when you enter the values, they must be in the same order as the columns in the table. Use "\N" for a NULL value.

mysql> INSERT INTO my_table VALUES('Bill',28,184) ;

You can also populate the table from an ASCII text tab delimited file. To do this you must pass the option "--local-infile" to MySQL when logging in and then call the LOAD DATA command.

mysql --local-infile --user=peter --password='peters_password' my_database

mysql> LOAD DATA LOCAL INFILE "my_data_filename" INTO TABLE my_table;

8.5.3 Querying the MySQL database

There are many ways to query the MySQL database using the SELECT command. A basic syntax for SELECT would look like this:

SELECT [columns_to_display] FROM

WHERE [column] [LIKE] [match_sequence]

For example, using the following command I can search the "age" column and output the information in the "columns_to_display" for the people in their 20's. Note, the "*" selects all the available columns in the table.

mysql> SELECT * FROM my_table WHERE age LIKE "2%";

MySQL databases can also be queried remotely by other applications (for example MS Access). To allow access for other systems to query your database, you can use the GRANT command. To give yourself permission to access the database from multiple domains, alter the DOMAIN_I_WILL_BE_ACCESSING_FROM and run the command again.

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER

-> ON my_database.* TO peter@DOMAIN_I_WILL_BE_ACCESSING_FROM

-> IDENTIFIED BY 'peters_password';