Part 4: Advanced MySQL Commands - MySQL

What we have covered so far is but a small part of what MySQL is capable of. Let's delve a little deeper into the language, exploring some of the more advanced commands of the language.

Logical Operations

MySQL includes full support of all basic logical operations.

AND (&&)

mysql> SELECT * FROM test WHERE
mysql> (name = "Bugs Bunny") AND
mysql> (phone_number = 5554321);

Result:
All records containing the name "Bugs Bunny" AND the phone number '5554321' will be displayed to the screen.

OR ( || )

mysql> SELECT * FROM test WHERE
mysql> (name = "Bugs Bunny") OR
mysql> (phone_number = 5554321);

Result:
All records containing the name "Bugs Bunny" OR the phone number '5554321' will be displayed to the screen.

NOT ( ! )

mysql> SELECT * FROM test WHERE
mysql> (name != "Bugs Bunny");

Result:
All records NOT containing the name "Bugs Bunny" will be displayed to the screen.

Order By

mysql> SELECT * FROM test WHERE
mysql> (name = "Bugs Bunny") ORDER BY
mysql> phone_number;

Result:
All records containing the name "Bugs Bunny" will be displayed to the screen, ordered in respect to the phone_number.

Search functions

MySQL offers the user the ability to perform both general and specific searches on data.

mysql> SELECT * FROM test WHERE
mysql> (name LIKE "%gs Bunny");

Result:
All records containing the partial string "gs Bunny" will be displayed to the screen. This would include such names as: "Bugs Bunny", "ags Bunny", "gs Bunny", and "234rtgs Bunny".

Notice that "LIKE" has been used instead of the equals sign (=). "LIKE" signifies that one is searching for an estimate of the data requested, and not necessarily an exact copy.

The '%' sign could be placed anywhere within the string. The method in which the server searches for a string is dependent upon where one places the '%' sign.

mysql> SELECT * FROM test WHERE
mysql> (name LIKE "Bugs Bunny%");

Result:
All records containing the partial string "Bugs Bunny" will be displayed to the screen. This would include such names as: "Bugs Bunnys", "Bugs Bunnyyyy453", "Bugs Bunnytrtrtrtrtr", but not "gs Bunny".

Focused Search Results

One can also perform searches and display only certain columns.

mysql> SELECT name FROM test WHERE
mysql> (name = "Bugs Bunny");

Result:

name
Bugs Bunny

Alter table

Another very important function of MySQL is the ability to modify previously created tables. This is accomplished via the ALTER statement. This function allows one to add, modify, and delete columns, as well as rename the table, among other functions.

Example: Rename the table

mysql> ALTER table test RENAME mytest;

Example: Add a column

mysql> ALTER table mytest ADD birthday DATE;

Example: Modify a column

mysql> ALTER table mytest CHANGE
mysql> name newname VARCHAR (25);

Example: Delete a column

mysql> ALTER table mytest DROP newname;

Executing the above four functions would modify test, creating the following table:

mysql> TABLE mytest (
> email VARCHAR (25),
> phone_number INT,
> ID INT AUTO_INCREMENT,
> birthday DATE );

The topics covered within this article are but a short introduction of the capabilities of MySQL. However, these functions form the basis of almost all advanced commands to be found in the language. Above all, the most important lesson that one can remember is to practice, study the documentation, and become an active member of the mailing list archives (And obviously read DevShed frequently!). Only by taking an enthusiastic, even "aggressive" approach to the language can one successfully master it.

>>> More MySQL Articles          >>> More By W.J. Gilmore

blog comments powered by Disqus