Brown PHP

MySQL is a very powerful open source database which means that it is free to use. One of the many tools that come with MySQL is their MySQL Command Line that will allow you to do pretty much everything from create a database to add and edit entries in the database. This article will help you to begin using MySQL Command Line Successfully.

When you install MySQL you will receive a command line application where you can create new databases, modify current databases, and delete databases.

Once you launch the MySQL Command Line Client you will be prompted for a password assuming you set one up when you installed MySQL. From here you will be given a mysql> prompt and you will be able to enters your commands.

Lets start with creating a database in our system:

mysql> CREATE DATABASE temp;
Query OK, 1 row affected (0.00 sec)

Be sure that when creating databases, tables, and fields that the names are case sensitive and cannot include spaces.
All commands issued through the command line must end with a semi-colon (;) or else you will be promtped for more commands.

In order to edit this new database we have created we need to switch to it and that is where the USE command comes into play.

mysql> USE temp;
Database changed

Now that we have a working database lets create a table in the database to store our relatives

mysql>CREATE TABLE family (name char(25), age integer, relation char(50));
Query OK, 0 rows affected (0.05 sec)

We just created a table that contains three fields so lets go into a little more detail about that command we just ran.

First we used the CREATE TABLE command which does exactly what it says does and it creates the table. Where we typed in family is the name of the table that we just created. Everything else within the parenthasis are fields in the table and what type of information is going to be stored in that field. For example that name char(25) tells SQL to create a field within the table called “name” and the char(25) tells it that it will be a character field meaning you can type any character you want in it and that it is 25 characters long. The age integer tells SQL to create a field in the table called age and that it will be a integer meaning that only whole numbers are stored in this field. You can add a size to the integer field like when using char but it is not nessecary as the default length of an interger field is 11. The last one is just like the first one realtion char(50) except for the fact that we made this field able to hold 50 characters instead of 25.

Now that we have created a database and a table lets look at a coupld show commands to see what we have. The show commands are pretty straight forward in terms of what they do. Lets start with SHOW DATABASES;

mysql> SHOW DATABASES;

+—————-+
| Database   |
+—————-+
| temp          |
| mysql        |
| mikenetpc  |
+—————+

3 rows in set (0.01 sec)

You can see that the SHOW DATABASES command will print you a list of all the databases on the system so can you guess what the SHOW TABLES command will do once you have used the USE temp command. Yep you guessed it, it will show you a list of all the tables in the database temp.

mysql> SHOW TABLES;

+———————-+
| Tables in temp  |
+———————-+
| family               |
+———————-+
1 row in set (0.01 sec)

Lets keep going deeper with the SHOW commands and lets look at the table itself and see all of the fields we have. We will simply use the SHOW COLUMNS FROM family command.

mysql> SHOW COLUMNS FROM family;

+———-+————-+——-+——–+————+———+
| Field    | Type      | Null  | Key   | Default  | Extra |
+———-+————-+——-+——–+————+———+
| name   | char(25) | YES |         | NULL    |           |
| age      | int(11)    | YES |         | NULL   |           |
| relation | char(50) | YES |         | NULL   |           |
+———-+————-+——-+——–+———–+———-+
3 rows in set (0.07 sec)

Now that we have create a database and a table and confirmed that they are there and setup the way we would like, lets add some information to the table because a database is not good without information. If you have every done any MySQL and PHP programming these next 2 commands should look very familiar. Lets start by adding in some people into the table by using the INSERT INTO table command. We are going to add Derek who is 22 and my cousin, Chelsie who is 21 and my cousin, and Leslie who is 27 and my sister.

mysql>INSERT INTO family (name, age, relation) VALUES (‘Derek’, 22, ‘Cousin’);
Query OK, 1 row affected (0.03 sec)

mysql>INSERT INTO family (name, age, relation) VALUES (‘Chelsie’, 21, ‘Cousin’);
Query OK, 1 row affected (0.03 sec)

mysql>INSERT INTO family (name, age, relation) VALUES (‘Leslie’, 27, ‘Sister’);
Query OK, 1 row affected (0.03 sec)

Now that we have added our information lets take a look at it and confirm it is all there. We will be using the SELECT command to view content in the table.

mysql> SELECT * FROM family;

+———–+——+————-+
| Name   | Age | Relation |
+———–+——+————-+
| Derek   | 22   | Cousin   |
| Chelsie | 21   | Cousin   |
| Leslie   | 27   | Sister     |
+———-+——-+————-+
3 rows in set (0.01 sec)

We can also use the SELECT command to order our list or to only pick a specific person.

mysql> SELECT * FROM family ORDER BY name;

+———–+——+————-+
| Name   | Age | Relation |
+———–+——+————-+
| Chelsie | 21 | Cousin     |
| Derek   | 22 | Cousin     |
| Leslie   | 27 | Sister       |
+———+——+—————+
3 rows in set (0.01 sec)

As you can see by simple adding ORDER BY name we were able to resort the information in the table by name. Now lets pull a specific person from the table

mysql> SELECT * FROM family WHERE name=’Leslie’;

+———+——+————-+
| Name | Age | Relation |
+———+——+————-+
| Leslie | 27   | Sister     |
+———+——+————-+
1 rows in set (0.01 sec)

By qualifying what we want name to be equal to we can pull specific records out of the table.

US $10.62 End Date: Friday Jul-30-2010 3:47:43 PDT Buy It Now for only: US $10.62 Buy it now | Add to watch list