MySQL Command Line /Database Setup

MySQL is an open source database built for speed in handling web applications.  It is the most common database chosen for PHP applications.

MySQL has no built in "window" style interface.  You are either required to find a program to access it, download and install a web based interface for MySQL like phpMyAdmin, or type commands to MySQL directly on a command line.  The advantage is that MySQL is streamlined for quickly processing web data.  There is no overhead for a "window" application, as there is in Microsoft's SQL Server.

The command line in MySQL is called the MySQL monitor. We can make changes to the data, or the structure of the tables or database while customers are hitting our database, through the command line.

Login to UNIX

Since MySQL is running on a Linux/UNIX server (Zephir) we will need a secure command line program, like puTTY to access the UNIX command line.  We will first be logging in to the UNIX server itself (Zephir) then we will login to MySQL.

When we run puTTY, we will provide the name of the server we are trying to reach. puTTY will attempt to connect to the server, which will ask for your login info:

login as:

You would enter your "school" login such as "horsey01", and the same password to access the school computers.

Once you are logged in, you will usually be presented with a dollar sign prompt, such as:

login as: horsey01
horsey01@zephir.seattlecentral.edu's password:
Last login: Sun Apr 15 20:04:48 2007 from 22-150-55-92.sttl.fake-myserver.com
[horsey01@zephir ~]$

NOTE: When you pay a third party to host MySQL, you should be provided login info.  Email your hosting company if information is not readily available. Command line access is not guaranteed, as it presents security challenges for a hosting company.

Login To MySQL

Once you have logged into UNIX, you must next login to MySQL:

$ mysql -u horsey01 -p;

Where horsey01 is your MySQL username, and in our case, your school login again. Once you have entered the above command, you will be promted for your password. To access MySQL on Zephir, you will need to set your password, prior to accessing MySQL: Set Zephir MySQL Password

If you are successfully logged into MySQL, you will see something like this:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 86662 to server version: 5.0.27

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

mysql>

NOTE: If MySQL is NOT hosted from the same machine as the web server ( in most cases, and it is for us) the hostname of the server where MySQL resides may be required:

$ mysql -h hostname -u username -p;

When the database server and the web server are the same machine, you can refer to this "local" machine with the alias localhost.  We'll see how this is used in code later.

MySQL Commands

All MySQL commands end in a semi-colon and are activated by the ENTER key.  You can enter multiple lines of code, but if you hit enter with the semi-colon at the end of a line, MySQL will try to process your command.

On Zephir, we have only one database for our use, so it may seem obvious which database we would like to use.  However, since we may find ourselves working with more than one database, we need to type a command to "select" a database to be able to view it, and/or make changes to data:

mysql> use dbname;

Where "dbname" is the actual name of our assigned database.  Again, the convention at school gives us an easy way to rememeber our database name.  It is our school login again, for example: "horsey01":

mysql> use horsey01;

You will get a message "database changed", for many successful commands, but it does not necessarily make a significant change to the database.

To view the all the databases on the server:

> show databases;

You may not have adminstrative access to other databases, but you may be allowed to see their names.

To view the tables in your database:

> show tables;

You will see a list of the tables in your database, but not what they include. To see details of the data types of your database:

>describe tablename;

Where "tablename" is a valid name for one of your tables.  You will see field names, data types, primary keys, whether nulls are allowed, etc. You can keep the command line open while you write your PHP code as a reference to be sure you are accessing your data correctly.

On Zephir, we have only one database for our use, but if we could create a new database, you would use the following command:

mysql> create database dbname;

An empty database would then be created. You would then need to create tables and add data.

MySQL Database Structure/Data Types

Data types in MySQL are usually strings, numerics and dates. Any number that is not added should be stored as a string. SQL expects a single quote around data that is a string type, but insists on NO quote around a numeric value. Below are some of the most comon data types:

STRING TYPES:
CHAR(number): A fixed length string
VARCHAR(number): a variable length str with a max length
TEXT: A field designed to accept up to 65,000 chars
BLOB: A field designed to accept large fields or image data, etc.

CHAR is faster to access info than VARCHAR but less size efficient. VARCHAR is better when you have a wide variety of data sizes. Use CHAR for states, (2 characters) passwords, etc.

NUMERIC TYPES:
TINYINT (-127 TO 255)
MEDIUMINT (-8 million to +8 million)
INT(same as INTEGER) (2 TO 31st power positive and negative)
BIGINT( 2 to 63rd, positive and negative)
FLOAT (width,decimals): for currency, etc.

Below are some of the special data types:

OTHER TYPES:
DATE (YYYY-MM-DD)
DATETIME (YYYY-MM-DD HH:MM:SS)
TIMESTAMP (YYYYMMDDHHMMSS)

Dates are entered into the database without the punctuation "-", etc. Timestamp is a way to compare two dates easily. It represents the number of seconds that have elapsed since the first of January, 1970, the beginning of the UNIX epoch.

Field Attributes

Besides the data type, we indicate other information to help us define the data allowed in each field. The most commonly used field types are listed below:

FIELD ATTRIBUTES:
NOT NULL: All rows must have a value (unless you require entry, do not add this)
AUTO_INCREMENT: MySQL will autogenerate an integer (for a primary key)
PRIMARY KEY: Must use this also to designate primary key, will be automatically indexed
UNSIGNED: For integer must have zero or positive value

Creating Database Tables

You can create your tables via the command line. You will enter data type and attribute information into the command line, and can use multiple lines, as long as you remember to end your command with the semi-colon.

IMPORTANT: Remember the technique we learned in class, where you create your tables in notepad, then copy the command, and right click on the command line in puTTY, and copy the code to the command line, and press enter! Here is a sample table:

create table tblCustomers
( CustomerID int unsigned not null auto_increment primary key,
LastName varchar(50),
FirstName varchar(50),
Email varchar(80)
);

This is a good demonstration of  the additional attributes you should use with a primary key, (CustomerID). The remainder of the fields are rather standard string type fields. We can then enter data into our new table:

insert into tblCustomers values
(NULL,"Smith","Bob","bob@fake.com"),
(NULL,"Jones","Bill","bill@fake.com"),
(NULL,"Doe","John","john@fake.com"),
(NULL,"Rules","Ann","ann@fake.com");

Here we have entered data, but were careful to enter NULL into the primary key, because it will automatically increment. If you have command line UNIX access to your server (we do not) you can redirect the contents of file you have built to automatically create all your tables at one time. If you have a file named "filename.ext" this is how the redirection might look:

>mysql -h hostname -u username dbname -p < filename.ext

Usually you will want to name the extension with .sql to indicate to you it is an SQL statement. NOTE: You can use the 'up' and 'down' arrow keys on the keypad to call up, and then backspace and edit and re-use MySQL commands!

We can see in retrospect how an existing table was created with the show create command:

show create table tblCustomers \G

Note the use of \G which positions all data vertically, rather than horizontally in the command window.

SQL statements

Every DBMS adheres to one degree or another to the SQL standards regarding the creation of SQL statements to query the database for information, or to make changes to the database structure. In general, keywords such as 'where' and 'select' are not case sensitive, however the objects (like table names) can be case sensitive. It is suggested you try your SQL statements out on the DBMS you are currently using, to determine case sensitivity for column names, etc. If you assume case sensitivity, you are usually safer!

Once we have created a table, and entered data, we can view data by typing a 'select' SQL statement at the command line:

> select * from tblCustomers;

We can also use the \G to see our data vertically:

> select * from tblCustomers \G

Next, update one of the records, using an 'update' SQL statement. Lets change "John" to "Jonathan"

update tblCustomers set FirstName='Jonathan' where CustomerID = 3;

Then use the UP button to scroll through your SQL statements, and pull back up the select statement to view the change:

select * from tblCustomers;

Note that the fields of 'text' type of data (char, varchar, text) need the data to 'match' set in single quotes (above, FirstName='Jonathan'). Numeric data, such as integers and floats must not be quoted (above CustomerID=3).

You can elect to show only certain fields:

select FirstName from tblCustomers;

You can use the optional "order by" clause to sort the records in a particular order. Order by accepts "asc" as ascending, and "desc" as descending. "asc" is the default sort order. Here is ordering the fields by first name:

select FirstName, LastName from tblCustomers order by LastName asc;

Notice that the fields we are shown in the proper order, and appear in the order we typed them into the SQL statement. You can also use the select statement with "where" clauses at the end to filter (limit) the data:

select LastName, FirstName from tblCustomers where FirstName like 'b%';

The "like" clause allows us to use a "wildcard" approach, and pulls up all records where the first name starts with 'b'. We can also pull up records with ANY letter b thus:

select LastName, FirstName from tblCustomers where LastName like '%b%';

We can use the 'limit' statement to page through records, if we are showing too many per page:

select * from tblCustomers limit 1,1;

This will return records starting with the first number passed, and return the number of records specified by the second number. Since we have so few records, this is a limited example.

There are other very useful keywords in SQL, one of which is 'distinct', which pulls up only unique records in a database:

select distinct City from tblCustomers;

The above example could pull up all cities in which we have customers, to help us determine where we want to advertise. (Note: given the current structure of our examples, this statement would not run, as we have not created a 'City' field).

Altering A Table

We can choose to alter the structure of a table after it has been created. let's add a field to the table. In this case, we'll add a field to store the state the user lives in, and we'll enter it specifically to appear by default after the first name:

Alter table tblCustomers add State char(2) after FirstName;

Do a select statement and notice that all the values are NULL. This means that there is no value at all, not even an empty string. We decided that we don't want to add this field without entering some sort of data, so we'll delete this field with all it's contents:

Alter table tblCustomers drop State;

Now Lets put a default value of "WA" for the state, to help us from having to update so much data:

Alter table tblCustomers add State char(2) default "WA" after FirstName;

Note that now each customer is conveniently in the state of washington. Imagine I want to change ALL the records now to the state of oregon. I could try this with the following statement:

update tblCustomers set State='OR';

Note that this changed EVERY record. It is VERY dangerous to use the update statement without a "where" clause. We could perform the same action above, only using a "where" clause to specify "all" of the records by indicating where 1:

update tblCustomers set State='WA' where 1;

MySQL can be set to STOP you without a where clause. Our version is not set up this way.

Let's imagine we want to allow a larger space for the last name field. We don't want to delete the data, so we need to alter the field, but not destroy it in the process:

Alter table tblCustomers modify LastName varchar(100);

Altering a table can have a negative effect as well. Lets change the type and size of the first name field:

Alter table tblCustomers modify FirstName char(3);

Now run your select statement. Note that all characters are chopped off at 3 characters. Be careful when modifying data. We can also change the name of our table, if we wish:

Alter table tblCustomers rename to bnCustomers;

Be careful with this, if you in fact are referring to a table with your PHP code, it will no longer work! Now lets delete a user from our table:

Delete from tblCustomers where CustomerID = 2;

Do your select statement, and note that the number 2 user is gone, and since the Customer ID is a unique field, it is gone forever. Now lets practice deleting the table in it's entirety:

Drop table tblCustomers;

If we're not sure what to do, we type "help" to see a list of MySQL commands, and then we can type "help command" to get more specific info.

To logout of MySQL when you are done, use the QUIT command.

> QUIT;

MySQL Users

Although we may not have the capability to create additional MySQL "users" on Zephir, it is wise to do so for security purposes on a commercial server.  Therefore, lets cover some MySQL user basics.

There are 4 levels of users for MySQL. We need to be aware of these, as it is possible to create users who have different capabilities, to meet our needs. Each user is limited (or not) specifically based on the permissions that you give the user. The reason why we might want to create a new user (for example) is to allow the new user created be limited in ability (add/edit/delete capability in a database for example). We would then use THIS user for all access from web pages!

If we had too powerful a user access our database from the web, a clever hacker could use this "user" to change our table, or drop tables, etc. and wreak havoc on our site and our data.

The four levels are: global, database, table and column. We will routinely work with "database" access for our administrative user, but we will create a far more limited user with database access for our "web" user access. Here is the common syntax for creating our new user, and granting privileges to the user:

GRANT privileges [columns]
on item (dbname.*)
to username [identified by 'password']
[with grant option]

To create an adminstrative user for our database, we could use the following command:

> grant select, insert, delete, update, index, alter, create, drop on dbname.* to username identified by 'horsey123' with grant option;

Note there are some powerful capabilities there. Drop can remove a table and all data in it's entirety, for example. "With Grant Option" allows the new user to create further (equally powerful) users. If we wish to create a more limited user for our web access it might look like:

grant select, insert, delete, update on dbname.* to username identified by 'horsey123';

This creates less "godlike" powers! The user can then change the data from the web, but not the structure of the website. For our purposes in class, we will be using the same "godlike" username, and will not create a new user, but it is highly advised if you work on your own server.

Here is one last version.  The above "web user" may be able to insert, update and delete data, if they are logging into our website.  However, what if the PHP page involved is open to the public, such as a textbox for searching?  Lets create a "select only" user specifically for searchers:

grant select on dbname.* to username identified by 'horsey123';

Later we'll cover a way to create multiple MySQL users to limit access to just what is needed to get the job done. We can limit damage by SQL Injection Attacks in this way!

Interfacing with MySQL

While using the command line is one way of working with MySQL, there are other means to get your job done.  In general, you will be building the "interface" for your customers, both administrative users and the general public.  This is because direct access to MySQL is both dangerous and complicated.

As the developer of a website (and/or database administrator) we may wish to access MySQL through a web based interface, such as phpMyAdmin or a desktop application.  Most hosting companies provide some such access to your databases.

MySQL provides a desktop application to access MySQL, MySQL Administrator.  While this is a powerful and useful product, I also recommend another quick and easy desktop tool, MySQL Control Center.

Another excellent product is Navicat which provides a 'query analyzer' to optimize your SQL statements and remote backup capabilities. They provide both a commercial and a free 'lite' version of their software.