www.PunjabColleges.com

Ultimate website on educational resources in Punjab

Date : May 9, 2008

Others

Contact Us
(Tel. 98145-74565)

Image Gallery
List of Universities in India
Education in Punjab
GJU, Hisar
Right to Information Act
Useful Telephone Nos.
Computer Tutorials
Articles
Vacancies
Journals

Using MySQL

Creating a MySQL Database

Remember that ALL MySQL commands end with a semicolon ";".

Anyway, lets make a database called info. To make it, you would type

mysql> create database info;

If you did this successfully, you will get something like this result;

Query OK, 1 row affected (0.03 sec)

To start using this database, type in

mysql> use info;

You will get the result;

Database changed.

Great, you now have the info database working, and are now using it.

Creating Your Tables

This is where the previously mentioned MySQL data types come in handy. When you create a table, you specify all of the columns and their data types. It is possible to add a column, or manipulate current columns after they are made, which I will show you later on in the tutorial.

Ok, this example Table will be called users, and it will have 3 total columns. 1, the ID Number, 2, the Person's Name, and 3, the Persons E-Mail address.

mysql> CREATE TABLE users (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR (50),
-> email VARCHAR (50),
-> PRIMARY KEY (id));

Asking what the !@#$ does that mean? Well, I got some explaining to do.

The first column is probably the most cryptic one. Basically, it tells MySQL that this column will be called id, it is an integer, its value cannot be null, and it automatically increments the number from the prior entry. You will see how it is used in the next section, adding data.

The name column is a Variable Length String, which I explained last week. Its length is a maximum of 50 characters. The email column is pretty much the same thing as well.

What's this about the Primary Key part though?

The Primary Key is a type of index MySQL uses. This index can do such things as;

1. Quickly find the rows that match a WHERE clause.
2. Retrieve rows from other tables when performing joins. 3. Sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key

This can definitely help boost the speeds of your queries as well.

Adding Data to a Table

Adding your Data to a table is not that hard of a process at all. Basically you specify what table you are inserting the values into, then you go ahead and do so. The syntax is as follows;

mysql> INSERT INTO users VALUES ("NULL","Blair Ireland","bireland@thescripts.com");

If successful, you should get something like the following for a response;

Query Ok, 1 row affected (0.05 sec)

*Note: When you add data, and you do not have any values to go into a column, you must still have it listed in your entry. For example, if I didn't have my e-mail address, the syntax would look like:

mysql> INSERT INTO users VALUES ("NULL","Blair Ireland","");

Viewing Data

After you add data to your table, you probably want to check it out to make sure everything went as planned. To do so, you would utilize the SELECT command.

To view all data in the table, you would use something like this;

mysql> SELECT * FROM users;

This will give you an output like this

+----+---------------+---------------------------------+
| id | name          | email                           |
+----+---------------+---------------------------------+
| 1  | Blair Ireland | bireland@thescripts.com         |
| 2  | Mark Hardy    | mhardy@thescripts.com           |
+----+---------------+---------------------------------+
2 rows in set (0.00 sec)

Say you want to select a particular row in this database though, you would use this sort of command;

mysql> SELECT * FROM users WHERE (name="Blair Ireland");

This would give you

+----+---------------+---------------------------------+
| id | name          | email                           |
+----+---------------+---------------------------------+
| 1  | Blair Ireland | bireland@thescripts.com         |
+----+---------------+---------------------------------+

You can also select specific columns, like this; mysql> select name from users;

+----------------+
| name           |
+----------------+
| Blair Ireland  |
| Mark Hardy     |
+----------------+
2 rows in set (0.06 sec)

Modifying Database Data

If you have data already in the database that needs some modifying, you would change it by utilizing the UPDATE command in mysql.

Its use is something like this;

mysql> UPDATE users SET email = 'webmaster@thescripts.com'
-> WHERE email = "bireland@thescripts.com";

This would just change all rows with email set to bireland@thescripts.com and change them to webmaster@thescripts.com. In this case though, only one entry has bireland@thescripts.com as its email, so only one entry would be changed.

Deleting Database Data

If you want to remove data in the database, you would use MySQL's DELETE command. Its use would be as follows

mysql> DELETE FROM users WHERE (name="Mark Hardy");

This would delete Mark Hardy's entry in the database, leaving only Blair Ireland's entry in it.

So far, a very small portion of MySQL's commands have been covered. There are quite a few more advanced commands you can integrate within your SQL queries.

First off, I will use the users table that has been used throughout these tutorials.

mysql> CREATE TABLE users (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR (50),
-> email VARCHAR (50),
-> PRIMARY KEY (id));

Search Functions

As you have seen in previous examples, MySQL most definitely has the ability to find specific search data. I have never covered general searches though, so here you go;

The % character in MySQL is the wildcard character. That is, it can represent anything, literally.

To do a general search, you would use the following syntax;

mysql> SELECT * FROM test WHERE
-> (name LIKE "%B%");

This will result in finding anything with the capital letter B in the column name. Notice the two %'s used. This checks for anything before or after that letter. You can use just one if you like though.

You can place that % sign anywhere within the query though, as the search is based upon the placement of this character.

Another wildcard character is the _ character. It will match exactly one character.

To use a literal wildcard character in your searches, you Order By

mysql> SELECT * FROM users WHERE
-> (name = "Joe%") ORDER BY id DESC;

This will return all the records containing someone with the first name of Joe, and will output it from the greatest ID Number, descend until the lowest ID number is reached.

The default for ORDER BY is ascending, so if you want it to go by the lowest ID number first, you would just type in ORDER BY id, or you could plug in the ASC keyword where DESC is currently. Both would give you the same result.

MySQL Logical Operators

By Blair Ireland
Senior Editor, TheScripts.com

Logical Operators

One of the great features within MySQL is its full support for logical operations. I will name off, and show examples of them below

mysql> CREATE TABLE users (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR (50),
-> email VARCHAR (50),
-> PRIMARY KEY (id));

NOT (or) !

mysql> SELECT * FROM users WHERE
-> (name != "Blair Ireland");

or

mysql> SELECT * FROM users WHERE
-> (name NOT = "Blair Ireland");

This query would return all records without Blair Ireland present as the name.

AND (or) &&

mysql> SELECT * FROM users WHERE
mysql> (name = "Blair Ireland") AND mysql> (email = "bireland@domainname.com");

or

mysql> SELECT * FROM users WHERE
-> (name = "Blair Ireland") &&
-> (email = "bireland@domainname.com");

This query would return all records with Blair Ireland present as the name, and bireland@domainname.com as the email.

OR ( or ) ||

mysql> SELECT * FROM test WHERE
-> (name = "Blair Ireland") OR
-> (email = "bireland@domainname.com");

or

mysql> SELECT * FROM test WHERE
-> (name = "Blair Ireland") ||
-> (email = "bireland@domainname.com");

This query would return all records with Blair Ireland present as the name, or records with bireland@domainname.com as the email.

Manipulating MySQL Tables

By Blair Ireland
Senior Editor, TheScripts.com

Table Manipulation

Lets say you made your table, and all the data has been added to it. Now you have come across a problem... your limit for characters in that particular column is too small for what you now need. You don't want to have to delete all of this data, yet, you have to change your table some how.

Fret no more everyone, you can manipulate your tables that have already been created.

The command for this task is known as ALTER TABLE. Just a note, it is possible to mix and match these commands, usually just separate them with a comma (,), or just place them all in the same line. Play around with them to get a feel for what I am talking about.

Renaming a Table

mysql> ALTER TABLE users RENAME public;

Changing a columns datatype

mysql> ALTER TABLE public MODIFY name CHAR(150);

Renaming a Table and Changing its datatype at once

mysql> ALTER table users CHANGE
-> email emailaddy CHAR (100);

Adding a Column

mysql> ALTER TABLE public ADD time TIMESTAMP;

Remove a Column

mysql> ALTER TABLE public DROP COLUMN time;

After you make these changes to the table, you may want to optimize the table afterwards (especially if you are using VARCHAR's, TEXT's or BLOB's, as this will optimize its memory allocation. You will also want to do it if you have deleted a large part of a table.

During a table optimization, the original table is available to clients, however, modifying and adding to the table is stalled until optimization is complete.

The syntax is:

OPTIMIZE TABLE table_name_goes_here

Deleting an entire table

To delete (or drop) an entire table, you would use the following syntax;

mysql> DROP TABLE public;

If you would like to drop more tables at once though, you would do this;

mysql> DROP TABLE public, tests;

Though this does not even remotely cover all of the available features found within MySQL, it does scratch the surface enough to catch your interest. Look for more tutorials coming soon on TheScripts.com covering MySQL's features.