Home arrow MySQL arrow Page 5 - Beginning MySQL Tutorial

Part 4: Advanced MySQL Commands - MySQL

One of the fastest SQL (Structured Query Language) database servers currently on the market is the MySQL server, developed by T.c.X. DataKonsultAB. MySQL, available for download at http://www.mysql.com, offers the data base programmer with an array of options and capabilities rarely seen in other database servers. What's more, MySQL is free of charge for those wishing to use it for private and commercial use.

TABLE OF CONTENTS:
  1. Beginning MySQL Tutorial
  2. Part 1: At First Glance
  3. Part 2: Datatypes and Tables
  4. Part 3: Manipulating the Database
  5. Part 4: Advanced MySQL Commands
By: W.J. Gilmore
Rating: starstarstarstarstar / 293
April 03, 1999

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: