MySQL & Statements in SQL Part 2

Welcome back to my SQL tutorial. In this article I will cover the following topics:

  • The LIKE Statement : How to Search your Data
  • The REGEXP Statement : Super Search Tool
  • The IN Statement : Clean up your Code
  • The NOT Statement : Learn to Act Negatively
  • The DELETE Statement : How to Delete Rows of Data
  • The UPDATE Statement : How to Change Values in your Tables
  • The ALTER Statement : How to Change the Tables
  • The Built in Numeric Functions
  • The Built in Comparison Functions
  • The Built in String Functions

The LIKE Statement

In the last article I showed you how to analyze data using the comparison and logical operators. The LIKE statement allows you to search for parts of text. For example:

SELECT * FROM us_presidents
WHERE first_name LIKE ‘An%’;

Would return all of the data for President’s whose first name begins with ‘An.’ For example Andrew Jackson & Andrew Johnson’s information would be returned. The percent symbol (%) is known as a wild card character. It stands for ‘any number of characters’. So the last SQL statement asks, ‘Send me all the data on US Presidents whose first name begins with the letters An and is followed by any number of additional letters.’

You can also use the underscore (_) wildcard. It is used to represent a single character. For example:

SELECT * FROM us_presidents
WHERE last_name LIKE ‘_ _ _ _ _’;

This would return last names that are 5 letters in length. Ex. Adams, Buren, Grant, etc.

The REGEXP Statement

By searching with the REGEXP Statement, you can perform extremely complicated comparisons and searches. I wrote an in depth article on Regular Expressions which can be found here. The format for using REGEXP in SQL is:

SELECT * FROM us_presidents
WHERE last_name REGEXP “The Regular Expression”;

I won’t go through regular expressions in detail because they are covered in the other article, but I’ll give you a chart that lists the many ways to use them in SQL.

Regular Expression Sequences in SQL

^ : Matches the beginning of a string
$ : Matches the end of a string
. : Matches any single character or newline
[‘a’,’b’] : Matches any character that appears in the brackets. In this case ‘a’, or ‘b’
[^’a’,’b’] : Matches any character that doesn’t appear. So matches anything but ‘a’, or ‘b’
e* : Matches zero or more instances of the pattern represented by the letter e
e+ : Matches one or more instances of the pattern represented by the letter e
e? : Matches zero or one instances of the pattern represented by the letter e
x1 | x2 : Matches pattern x1 or x2. You could add additional or statements here
x {y} : Matches y instances of the pattern x
x {y,} : Matches y or more instances of the pattern x
x {,y} : Matches zero to y instances of the pattern x
x {y,z}: Matches between y to z instances of the pattern x

The IN Statement

You can use the IN Statement to shorten your queries in SQL. When you needed to search for specific values in the past, you had to write multiple comparison operators that were separated by OR statements. Ex:

SELECT first_name
FROM us_presidents
WHERE
first_name = ‘Andrew’
OR
first_name = ‘James’
OR … ;

With the IN Statement you can shorten this whole process:

SELECT first_name
FROM us_presidents
WHERE
first_name IN (‘Andrew’,‘James’…);

See all nice and tidy!

The NOT IN Statement

The NOT Statement, is the opposite of the IN Statement. Ex:

SELECT first_name
FROM us_presidents
WHERE
first_name NOT IN (‘Andrew’,‘James’…);

This SQL code will return all of the records that don’t contain ‘Andrew’, ‘James’, …

You can also use the NOT statement to get the opposite results with other comparisons. Ex:
SELECT first_name, age_when_elected
FROM us_presidents
WHERE NOT age_when_elected < 50;

This would return the presidents elected at an age older than 50, even the comparison is asking for ages less than 50. The NOT statement gives you the opposite results of the comparison.

The DELETE Statement

You can delete records using a similar format to how you have been selecting and inserting them. Let’s say you accidentally entered Ben Franklin as a President. This is how you’d delete him from your table records:

DELETE FROM us_presidents
WHERE
(first_name = ‘Ben’) AND (last_name = ‘Franklin’);

Poof, Ben Franklin and the whole row of data on him is deleted. There are limitations on how you can use the DELETE statement though. You can’t delete a single or multiple columns of information. The DELETE statement is used to delete rows and you can delete multiple rows if you define them with your WHERE comparison.

Be very careful with the DELETE statement, because as they say, when it’s gone it’s gone for good. Who says that?

The ALTER Statement

What do you do if you want to add a new column to your table? You probably guessed it has something to do with the ALTER statement and your right! Let’s say you want to add a column to your US Presidents database for the Presidents Wife. This is how you’d do it:

ALTER TABLE us_presidents
ADD COLUMN wife VARCHAR(15) NULL;

And, if you want to place the wife value in the table and after the value last_name:

ALTER TABLE us_presidents
ADD COLUMN wife VARCHAR(15) NULL
AFTER last_name;

You can use the SQL Statements FIRST, SECOND, BEFORE, LAST in ways similar to the AFTER statement, as well.

You can also use ALTER to change the name for a column or table:

ALTER TABLE us_presidents
CHANGE COLUMN wife wife_first_name;

The ALTER statement can also change the data type for a column with the addition of the MODIFY statement:

ALTER TABLE us_presidents
MODIFY COLUMN wife_first_name VARCHAR(20);

Finally ALTER can delete columns from a table with some help from the DROP statement:

ALTER TABLE us_presidents
DROP COLUMN wife_first_name;

The Built in Numeric Functions

ABS(x) : Absolute Number: Returns the absolute value of the variable x.

ACOS(x), ASIN(x), ATAN(x), ATAN2(x,y), COS(x), COT(x), SIN(x), TAN(x) :Trigonometric Functions : They are used to relate the angles of a triangle to the lengths of the sides of a triangle.

AVG(column_name) : Average of Column : Returns the average of all values in a column. SELECT AVG(column_name) FROM table_name;

CEILING(x) : Returns the smallest number not less than x.

COUNT(column_name) : Count : Returns the number of non null values in the column. SELECT COUNT(column_name) FROM table_name;

DEGREES(x) : Returns the value of x, converted from radians to degrees.

EXP(x) : Returns e^x

FLOOR(x) : Returns the largest number not grater than x

LOG(x) : Returns the natural logarithm of x

LOG10(x) : Returns the logarithm of x to the base 10

MAX(column_name) : Maximum Value : Returns the maximum value in the column. SELECT MAX(column_name) FROM table_name;

MIN(column_name) : Minimum : Returns the minimum value in the column. SELECT MIN(column_name) FROM table_name;

MOD(x, y) : Modulus : Returns the remainder of a division between x and y

PI() : Returns the value of PI

POWER(x, y) : Returns x ^ Y

RADIANS(x) : Returns the value of x, converted from degrees to radians

RAND() : Random Number : Returns a random number between the values of 0.0 and 1.0

ROUND(x, d) : Returns the value of x, rounded to d decimal places

SQRT(x) : Square Root : Returns the square root of x

STD(column_name) : Standard Deviation : Returns the Standard Deviation of values in the column. SELECT STD(column_name) FROM table_name;

SUM(column_name) : Summation : Returns the sum of values in the column. SELECT SUM(column_name) FROM table_name;

TRUNCATE(x) : Returns the value of x, truncated to d decimal places

The Built in String Functions

CONCAT(string1, string2,…) : Returns a single string made up by combining all strings sent to it.

INSERT(string, pos, len, str2) : Takes the two strings and combines them based on the value of pos and len. The variable pos is where you want to start enterting the value of the second string and len equals the length of the string you want to over-write. Ex. INSERT(“fallingstar”, 1, 6, “super “) would provide the value of “super star”

LCASE(string) : Lowercase : Converts the string to lowercase letters.

LEFT(string, length) : Returns the number of characters defined by length starting at the left of the string. Ex. LEFT(“MADCOW”, 3), would return “MAD”

LENGTH(string) : Returns the length of the string

LOCATE(substring, string) : Returns the location of the first occurrence of the sub string in the string. Ex LOCATE(“key”, “monkey”) returns the number 4

REPLACE(string, replaced, replacing) : Replaces all occurrences in the string of the string variable named replaced with the value of variable replacing. Ex. REPLACE(“Superduper”, “Super”, “Major”), returns “Majorduper”

REVERSE(str) : Reverses the characters in a string.

RIGHT(string, length) : Similar to LEFT()

TRIM([ leadingchar | trailingcharacters | both ] [ stringtotrim ] FROM [ string ]) : Trims trailing characters that you define from the string. Ex. TRIM(BOTH “*” FROM “ *Ace* “), returns the value “Ace”
UCASE(string) : Returns the string in uppercase format.

That’s All Folk’s

I hope you learned a bunch about SQL? Leave any questions you have below and in the next article I’ll wrap up everything by describing how to work with multiple tables and creating normalized database tables.

Till next time…

Think Tank

Leave a Reply

Your email address will not be published.

Google+