Aggregate Functions
mysql
group by - aggregate functions
After you have mastered the basics of MySQL, it's time to take the
next step and take on Aggregate Functions. Before we talk about what they are,
let's review the definition of aggregate, as it relates to MySQL:
- Aggregate - Constituting or amounting to a whole; total. ~American Heritage Dictionary
With this type of wording, we can assume that MySQL's aggregate
functions are something that will be very top-level, or in other words, the
opposite of detailed.
The most common types of aggregate functions let you find out
things like the minimum, maximum and even the average of a "grouped"
set of data. The trick to understanding aggregate functions is often
understanding what kind of data is being grouped and analyzed.
mysql
group by - the data
Before we can start throwing around these fancy functions, let's
build an appropriate table that has enough data in it to be meaningful to us.
Below is the SQL for our "products" table. You can either run this
SQL statement in your MySQL administrator software or use MySQL to execute the
queries (i.e. create table, then each of the records).
You can download the products.sql file from our website. If you are new to MySQL
you will need to know how to Create a MySQL
Table and Insert a MySQL
Row.
group by - creating your first "group"
Imagine that our store was
running an advertisement in the newspaper and we wanted to have a "bargain
basement" section that listed the lowest price of each product type. In
this case we would be "grouping" by the product type and finding the
minimum price of each group.
Our query needs to return two
columns: product type and minimum price. Additionally, we want to use the type column as our group. The SELECT
statement we are about to use will look different because it includes an
aggregate function, MIN, and the GROUP BY statement, but otherwise it isn't any
different than a normal SELECT statement.
PHP and MySQL Code:
// Make a MySQL Connection
$query = "SELECT type, MIN(price) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo $row['type']. " - $". $row['MIN(price)'];
echo "
";
}
?>
mysql
aggregate functions - count()
This lesson will teach you how to use the aggregate function
COUNT(). If you missed the Aggregate
Introduction Lesson, please check it out now, as it explains many
concepts used in this lesson!
mysql count - counting records
The COUNT function is an
aggregate function that simply counts all the items that are in a group. The
"products" table that is displayed above has several products of
various types. One use of COUNT might be to find out how many items of each
type there are in the table.
Just as we did in the aggregate introduction
lesson, we are going to GROUP BY type to create four groups: Music, Toy,
Clothing and Food. For a slight change of pace, let's count the name column to find how many products there
are per type.
PHP and MySQL Code:
// Make a MySQL Connection
$query = "SELECT type, COUNT(name) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(name)'] ." ". $row['type'] ." items.";
echo "
";
}
?>
mysql
aggregate functions - sum()
mysql sum - totaling groups
SUM is an aggregate function that
totals a specific column for a group. The "products" table that is
displayed above has several products of various types. One use of SUM might be
to find the total of all the items' price for each product type.
Just as we did in the aggregate
introduction lesson, we are going to apply the aggregate function to price and GROUP BY type to create four groups: Music, Toy,
Clothing and Food.
PHP and MySQL Code:
// Make a MySQL Connection
$query = "SELECT type, SUM(price) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "Total ". $row['type']. " = $". $row['SUM(price)'];
echo "
";
}
?>
mysql
aggregate functions - avg()
This lesson will teach you how to use the aggregate function
AVG(). If you missed the Aggregate
Introduction Lesson, please check it out now. It explains the
meaning of aggregates and describes the GROUP BY statement.
mysql average - finding a middle ground
The AVG function returns the
average value for the specified column of a group.
Our imaginary customers have been
complaining recently that our prices are too high, so we would like to find out
the average price of each product type to see if this is in fact the truth.
To find out this metric we are
going to apply the aggregate function to theprice and GROUP BY type to create four price groups: Music,
Toy, Clothing and Food.
PHP and MySQL Code:
// Make a MySQL Connection
$query = "SELECT type, AVG(price) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "The average price of ". $row['type']. " is $".$row['AVG(price)'];
echo "
";
}
?>
mysql
aggregate functions - min()
This lesson will teach you how to use the aggregate function MIN().
If you missed the Aggregate
Introduction Lesson, you might want to check it out to learn about
the GROUP BY statement and its use with MySQL aggregate functions.
mysql min
The MIN function is an aggregate
function that finds the smallest value in a group. The products table that is
displayed above has several products of various types. One use of MIN might be
to find out the cheapest item in each group.
Just as we did in the Aggregate Introduction Lesson,
we are going to GROUP BY type to create four groups: Music, Toy,
Clothing and Food. The column that will have the MIN function applied to it is,
of course, price.
PHP and MySQL Code:
// Make a MySQL Connection
$query = "SELECT type, MIN(price) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "The cheapest ". $row['type']. " is $" .$row['MIN(price)'];
echo "
";
}
?>
mysql
aggregate functions - max()
This lesson will teach you how to use the MAX() aggregate function
. If you missed the Aggregate
Introduction Lesson, please check it out now, as it explains many
concepts used in this lesson!
mysql max - finding the big one
MySQL's MAX aggregate function
will find the largest value in a group. The "products" table that is
displayed above has several products of various types. We could use the MAX
function to find the most expensive item for each type of product.
Just as we did in the aggregate
introduction lesson, we are going to GROUP BY type to create four groups: Music, Toy,
Clothing and Food. We will also be applying the aggregate function to the price column.
PHP and MySQL Code:
// Make a MySQL Connection
$query = "SELECT type, MAX(price) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "The most expensive ". $row['type']. " is $" .$row['MAX(price)'];
echo "
";
}
?>
mysql
- sql injection prevention
If you have ever taken raw user input and inserted it into a MySQL
database there's a chance that you have left yourself wide open for a security
issue known as SQL Injection. This lesson will teach you how to help prevent this from
happening and help you secure your scripts and MySQL statements.
what is sql injection
SQL injection refers to the act
of someone inserting a MySQL statement to be run on your database without your
knowledge. Injection usually occurs when you ask a user for input, like their
name, and instead of a name they give you a MySQL statement that you will
unknowingly run on your database.
sql injection example
Below is a sample string that has
been gathered from a normal user and a bad user trying to use SQL Injection. We
asked the users for their login, which will be used to run a SELECT statement
to get their information.
MySQL & PHP Code:
// a good user's name
$name = "timmy";
$query = "SELECT * FROM customers WHERE username = '$name'";
echo "Normal: " . $query . "
";
// user input that uses SQL Injection
$name_bad = "' OR 1'";
// our MySQL query builder, however, not a very safe one
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
// display what the new query will look like, with injection
echo "Injection: " . $query_bad;
The normal query is no problem,
as our MySQL statement will just select everything from customers that has a
username equal to timmy.
However,
the injection attack has actually made our query behave differently than we
intended. By using a single quote (') they have ended the string part of our
MySQL query
- username = ' '
and then added on to our WHERE
statement with an OR clause of 1 (always true).
- username = ' ' OR 1
This OR clause of 1 will always
be true and so every single entry in the "customers" table
would be selected by this statement!
more serious sql injection attacks
Although the above example
displayed a situation where an attacker could possibly get access to a lot of
information they shouldn't have, the attacks can be a lot worse. For example an
attacker could empty out a table by executing aDELETE statement.
MySQL & PHP Code:
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";
// our MySQL query builder really should check for injection
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
// the new evil injection query would include a DELETE statement
echo "Injection: " . $query_evil;
injection prevention - mysql_real_escape_string()
Lucky for you, this problem has
been known for a while and PHP has a specially-made function to prevent these
attacks. All you need to do is use the mouthful of a function mysql_real_escape_string.
What mysql_real_escape_string does is take a string that is going to
be used in a MySQL query and return the same string with all SQL Injection
attempts safely escaped. Basically, it will replace those troublesome quotes(')
a user might enter with a MySQL-safe substitute, an escaped quote \'.
Lets try out this function on our
two previous injection attacks and see how it works.
MySQL & PHP Code:
//NOTE: you must be connected to the database to use this function!
// connect to MySQL
$name_bad = "' OR 1'";
$name_bad = mysql_real_escape_string($name_bad);
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection:
" . $query_bad . "
";
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";
$name_evil = mysql_real_escape_string($name_evil);
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection:
" . $query_evil;
Notice that those evil quotes have been escaped with a backslash
\, preventing the injection attack. Now all these queries will do is try to
find a username that is just completely ridiculous:
- Bad: \' OR 1\'
- Evil: \'; DELETE FROM customers WHERE 1 or username = \'
And I don't think we have to worry about those silly usernames
getting access to our MySQL database. So please do use the handy mysql_real_escape_string()function to help prevent SQL Injection attacks
on your websites. You have no excuse not to use it after reading this lesson!
mysql
date - formats
MySQL comes with several data types for storing a date in its
database system: DATETIME, DATE, TIMESTAMP, and YEAR. This lesson will show you
the proper formats of each type, show their related MySQL functions, and give
anINSERT example of each.
These date types are chosen for a
column when you create a new table in MySQL.
Often the most difficult part of using dates in MySQL is to be sure the format
of the date you are trying to store matches the format of your table's date
column. If you haven't already, try to create a new MySQL table with the date
types we mentioned above.
We have assembled a "date
playground" MySQL table that can be used to follow along with this lesson. dateplayground.sql. Also, the
following acronyms are used in this lesson:
- Y - year segment
- M - month segment
- D - day segment
- H - hour segment
- m - minute segment, note the lower case
- S - sec segment
mysql date - date
The default way to store a date
in MySQL is with the type DATE. Below is the proper format of a DATE.
- YYYY-MM-DD
- Date Range: 1000-01-01 to 9999-12-31
If you try to enter a date in a
format other than the Year-Month-Day format then it might work, but it won't be
storing them as you expect.
To insert the current date into
your table you can use MySQL's built-in function CURDATE() in your query. Below
we have created 2 dates, one manually and one using CURDATE().
PHP & MySQL Code:
//This assumes you have already created the 'dateplayground' table
//Connect to DB
$query_manual = "INSERT INTO dateplayground (dp_name, dp_date)
VALUES ('DATE: Manual Date', '2020-2-14')";
$query_auto = "INSERT INTO dateplayground (dp_name, dp_date)
VALUE ('DATE: Auto CURDATE()', CURDATE() )";
mysql_query($query_manual) or die(mysql_error());
mysql_query($query_auto) or die(mysql_error());
?>
mysql date - year
If you just need to store the
year of an event, MySQL also has a date type just for that. YEAR's format is
simply:
- YYYY
- Date Range: 1901 to 2155
It should be noted that the range
of years that can be stored are from 1901 to 2155. If you need to store years
outside that range then use DATE instead of YEAR.
Below we have created another
manual and automatic example to show off YEAR's use. We have used CURDATE()
again, even though it provides a lot more information than YEAR requires. All
the date information, besides the year, is just ignored by YEAR.
PHP & MySQL Code:
$query_manual = "INSERT INTO dateplayground (dp_name, dp_year)
VALUES ('YEAR: Manual Year', '2011')";
$query_auto = "INSERT INTO dateplayground (dp_name, dp_year)
VALUE ('YEAR: Auto CURDATE()', CURDATE() )";
mysql_query($query_manual) or die(mysql_error());
mysql_query($query_auto) or die(mysql_error());
?>
mysql date - datetime
DATETIME actually stores both the
current date and time, meaning it has the ability to store the year, month,
day, hour, minute, and second inside it. DATETIME's format is:
- YYYY-MM-DD HH:mm:SS
- Date Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59
The hyphen and the colon are the
standard character to separate a date and time respectively, but MySQL allows
for you to choose your own delimiters if you wish.
With DATETIME you can choose to
store the date or the time and date together, but you cannot store just the time.
In our example below we have
manually stored a complete DATETIME and also used three different MySQL
functions: CURDATE(), CURTIME(), and NOW().
PHP & MySQL Code:
$query_manual = "INSERT INTO dateplayground (dp_name, dp_datetime)
VALUES ('DATETIME: Manual DateTime', '1776-7-4 04:13:54')";
$query_autodate = "INSERT INTO dateplayground (dp_name, dp_datetime)
VALUE ('DATETIME: Auto CURDATE()', CURDATE() )";
$query_autotime = "INSERT INTO dateplayground (dp_name, dp_datetime)
VALUE ('DATETIME: Auto CURTIME()', CURTIME() )"; //This will fail
$query_autonow = "INSERT INTO dateplayground (dp_name, dp_datetime)
VALUE ('DATETIME: Auto NOW()', NOW() )";
mysql_query($query_manual) or die(mysql_error());
mysql_query($query_autodate) or die(mysql_error());
mysql_query($query_autotime) or die(mysql_error());
mysql_query($query_autonow) or die(mysql_error());
?>
mysql date - timestamp
TIMESTAMP is a format popularized
by the *NIX operating systems that stores the amount of time that has passed
since January 1, 1970. If you want more versatility than this date type
provides, remember to try DATETIME.
- YYYY-MM-DD HH:mm:SS
- Date Range: 1970-01-01 00:00:00 to 2037-12-31 23:59:59
The big difference between
DATETIME and TIMESTAMP is the date ranges that can be stored. Below we have
purposely entered an erroneous date, manually, so you can see what happens when
you enter a date that is outside the boundaries of a this type.
PHP & MySQL Code:
//This will fail
$query_manual = "INSERT INTO dateplayground (dp_name, dp_timestamp)
VALUES ('TIMESTAMP: Manual Timestamp', '1776-7-4 04:13:54')";
$query_autodate = "INSERT INTO dateplayground (dp_name, dp_timestamp)
VALUE ('TIMESTAMP: Auto CURDATE()', CURDATE() )";
//This will fail
$query_autotime = "INSERT INTO dateplayground (dp_name, dp_timestamp)
VALUE ('TIMESTAMP: Auto CURTIME()', CURTIME() )";
$query_autonow = "INSERT INTO dateplayground (dp_name, dp_timestamp)
VALUE ('TIMESTAMP: Auto NOW()', NOW() )";
mysql_query($query_manual) or die(mysql_error());
mysql_query($query_autodate) or die(mysql_error());
mysql_query($query_autotime) or die(mysql_error());
mysql_query($query_autonow) or die(mysql_error());
?>
viewing dateplayground in php
Below is a quick script that will
spit out the MySQL table dateplayground in HTML.
PHP & MySQL Code:
$query = "SELECT * FROM dateplayground";
$result = mysql_query($query) or die(mysql_error());
echo "
";
for($i = 0; $i < mysql_num_fields($result); $i++){
echo "".mysql_field_name($result, $i)." ";
}
echo "
while($row = mysql_fetch_array($result)){
echo "
";
for($i = 0; $i < mysql_num_fields($result); $i++){
echo "". $row[$i] ." ";
}
echo "
";}
echo "
";
?>
mysql
time - formats
There are three different types of time data types in MySQL: TIME,
DATETIME, and TIMESTAMP. If you would like to learn more about DATETIME and
TIMESTAMP, then check out our MySQL Date section, as we've covered them there. This
lesson will just be covering the basics of using TIME.
mysql time - time
First you need to create a MySQL
table with a TIME type. We have one already created if you want to use it: timeplayground.sql.
The TIME data type can be used to
store actual times as well as the amount of time between two points in time
(like the time between now and the weekend) that may sometimes be larger than
23 hours. H - Hour; M - Minute; S - Second.
- Standard format: HH:MM:SS
- Extended hour format: HHH:MM:SS
- Time Range: -838:59:50 to 838:59:59
When manually entering a time
into MySQL it is highly recommended that you use the exact format show above.
MySQL allows for many different ways to enter a time, but they don't always
behave as you would expect. Using the standard/extended format we have shown
above will help you avoid annoying problems.
Below we have entered 3 manual
times into MySQL. The first is done in the recommended format, the second is a
shorthand version of the first and the final example is outside the allowed
time range.
PHP & MySQL Code:
//This assumes you have already created the 'dateplayground' table
//Connect to DB
$query_manual1 = "INSERT INTO timeplayground (dp_name, dp_time)
VALUES ('TIME: Manual Time', '12:10:00')"; //perfectly done
$query_manual2 = "INSERT INTO timeplayground (dp_name, dp_time)
VALUES ('TIME: Manual Time', '1210')"; // will this shorthand work?
$query_manual3 = "INSERT INTO timeplayground (dp_name, dp_time)
VALUES ('TIME: Manual Time', '978:31:12')"; //how about this?
mysql_query($query_manual1) or die(mysql_error());
mysql_query($query_manual2) or die(mysql_error());
mysql_query($query_manual3) or die(mysql_error());
?>
mysql time - now()
To get the current time, use
MySQL's built in function NOW(). NOW() contains both the date and time
information, but MySQL is smart enough to just use the data needed for TIME.
PHP & MySQL Code:
$query_auto = "INSERT INTO timeplayground (dp_name, dp_time)
VALUE ('TIME: Auto NOW()', NOW() )";
mysql_query($query_auto) or die(mysql_error());
?>
mysql timeplayground.sql displayed
Below is a small PHP script to
spit out a rough version of our timeplayground.sql table.
PHP & MySQL Code:
$query = "SELECT * FROM timeplayground";
$result = mysql_query($query) or die(mysql_error());
echo "
";
for($i = 0; $i < mysql_num_fields($result); $i++){
echo "".mysql_field_name($result, $i)." ";
}
echo "
while($row = mysql_fetch_array($result)){
echo "
";
for($i = 0; $i < mysql_num_fields($result); $i++){
echo "". $row[$i] ." ";
}
echo "
";}
echo "
";?>
mysql
index - overclock your tables
Hardware enthusiasts have been overclocking their PCs for years
now, trying to push the limits of their hardware for maximum performance.
Sometimes they are successful and their applications run speedy fast, while
other times they push a little too hard and end up damaging the computer!
mysql index - speed and extra overhead
Indexes are created on a per
column basis. If you have a table with the columns: name, age, birthday and
employeeID and want to create an index to speed up how long it takes to find employeeID values in your queries, then you would
need to create an index for employeeID.
When you create this index, MySQL will build a lookup index where employeeID specific queries can be run quickly.
However, the name, age and birthday queries would not be any faster.
Indexes are something extra that
you can enable on your MySQL tables to increase performance,cbut they do have
some downsides. When you create a new index MySQL builds a separate block of
information that needs to be updated every time there are changes made to the
table. This means that if you are constantly updating, inserting and removing
entries in your table this could have a negative impact on performance.
creating a mysql index - new table
If you are creating a new MySQL
table you can specify a column to index by using the INDEX term as we have
below. We have created two fields: name and employeeID (index).
MySQL Code:
CREATE TABLE employee_records (
name VARCHAR(50),
employeeID INT, INDEX (employeeID)
)
creating a mysql index - existing table
You can also add an index to an
older table that you think would benefit from some indexing. The syntax is very
similar to creating an index in a new table. First, let's create the table.
MySQL Code:
CREATE TABLE employee_records2 (name VARCHAR(50), employeeID INT)
With our newly created table we
are going to update the "employee_records2" table to include an
index.
MySQL Code:
CREATE INDEX id_index ON employee_records2(employeeID)
We keep our existing employeeID field and create a new index id_index that is made up of employeeID data.
Comments
Post a Comment
Thank you for your Comment