SELECT name FROM products LIMIT num_rows_return; SELECT name FROM products LIMIT start_row, num_rows_return; SELECT name FROM products WHERE price BETWEEN 5 AND 10; SELECT name FROM products WHERE price IS NULL; SELECT name FROM products WHERE vendorID IN (1002,1003); SELECT Concat(name, ' (', location, ') ') FROM vendor ORDER BY name [DESC | ASC]; SELECT colName1, colName2 FROM tableName; SELECT * FROM tableName; SELECT BuyerID, MAX(price) FROM Antiques GROUP BY BuyerID HAVING price > 100; SELECT CONCAT(firstName, " ", lastName) FROM tableName WHERE income / dependent > 1000 AND age > 30;
The default sort order is ASC.
By default, MySQL is not case sensitive when performing match (comparision).
The LIMIT clause
LIMIT clause with one value specified always starts from the first row, and the specified number is the number of rows to return. LIMIT clause with two values specified starts from wherever the first value tells it to, and the second value is the number of rows to return.
LIMIT 4 OFFSET 3 is the same as LIMIT 3,4
SELECT name FROM products WHERE name LIKE 'jet%'; SELECT name FROM products WHERE name REGEXP '1000' ORDER BY name;
Wildcard search typically take far longer than any other search types. The % matches zero or more occurrences of any characters. The underscore matches any single character.
SELECT id FROM orders WHERE Date(date) = '2005-09-01';
It is good practice to use Date() if what you want is just the date, even if you know that the column only contain dates. This way, if somehow a date time value ends up in the table in the future, your SQL will continue to work. The same applies to Time().
SELECT id FROM orders WHERE Date(date) BETWEEN '2005-09-01' AND '2005-09-30'; SELECT id FROM orders WHERE Year(date) = 2005 AND Month(date) = 9;
Year(), Month(), Day(), Hour(), Minute(), Second(), DayOfWeek(), Date(), Time(), Now(), AddDate(), AddTime(), CurDate(), CurTime(), DateDiff(), Date_Add(), Date_Format().
SELECT AVG(price) AS averagePrice FROM products WHERE vendorID=1003; SELECT COUNT(*) FROM customer; SELECT COUNT(column_name) FROM customer; SELECT MAX(id) FROM customer;
COUNT(*) counts the number of rows in a table. COUNT(column_name) counts the number of rows that have value in specific column ignoring NULL.
MAX(column_name) is usually used to find the highest numeric or date values. MySQL allows it to be used to return the highest value in any column, including textual columns. When used with textual columns, MAX() return the row that would be the last if the data was sorted by that column.
SUM(price * quantity)
The following example use AVG() function to return the average product price offered by a specific vendor. The DISTINCT argument is used so the average only takes into account unique prices:
SELECT AVG( DISTINCT(price) ) AS avgPrice FROM product WHERE vendorID=1003;
DISTINCT may only be used with COUNT() if a column name is specified. DISTINCT may not be used with COUNT(*) and so COUNT(DISTINCT(*)) is not allowed. DISTINCT must be used with a column name, and not with a calculation or expression.
Multiple aggregate functions can be used in single statement:
SELECT COUNT(*) AS num_items, MIN(price) AS min_price, MAX(price) AS max_price, AVG(price) AS avg_price FROM products;
SELECT vendorID, COUNT(*) AS num_prods FROM products GROUP BY(vendorID);
The GROUP BY instructs MySQL to group the data, and then perform the aggregate function on each group, rather than on the entire result set. Before you use GROUP BY, here are some important rules:
- GROUP BY clause can contain as many columns as you want. This enable you to nest groups, providing you with more granular control over how data is grouped.
- If you have nested groups in your GROUP BY clause, data is summarized at the last specified group. In other words, all the columns specified are evaluated together when grouping is established.
- Every column listed in GROUP BY clause must be a retrieved column or a valid expression, but not an aggregate function. If an expression is used in the SELECT, that same expression must be specified in the GROUP BY clause. Aliases can not be used.
- Aside from the aggregate calculation statements, every column in your SELECT should be present in the GROUP BY clause.
- If the grouping column contain a row with NULL, NULL will be returned as a group. If there are multiple rows with NULL, they will be grouped together.
- GROUP BY clause must come after any WHERE clause, and before any ORDER BY clause.
To obtain values at each group and at summary level for each group, use the WITH ROLLUP keyword:
SELECT vendorID, COUNT(*) AS num_prod FROM products GROUP BY vendorID WITH ROLLUP;
HAVING vs WHERE
In addition to being able to group data using GROUP BY, MySQL also allows you to filter which groups to include and which groups to exclude. For example, you might want to list all customers who have made at least two orders. To obtain this data you must filter based on the complete group, not on individual rows.
WHERE filters rows, not group. WHERE has no idea what a group is. HAVING is similar to WHERE. In fact, HAVING can be used in place of WHERE. The only difference: WHERE filter rows, HAVING filter groups.
SELECT id, COUNT(*) AS orders FROM order GROUP BY id HAVING COUNT(*) >= 2;
WHERE filters before data is grouped, and HAVING filters after data is grouped. Rows that were eliminated by WHERE clause are not included in the group.
SELECT order, SUM(quantity * price) AS order_total FROM orderitems GROUP BY order HAVING SUM(quantity * price) >= 50 ORDER BY order_total;
How can we select distinct rows?
Use the DISTINCT keyword:
SELECT DISTINCT columnName1, columnName2 FROM tableName;