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 );