SELECT

mysql

Basic usages / examples of using the SELECT statement:

SELECT colName1, colName2 FROM tableName;
SELECT * FROM tableName;

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 BuyerID, MAX(price) FROM Antiques GROUP BY BuyerID HAVING price > 100;

SELECT CONCAT(firstName, " ", lastName) FROM tableName WHERE income / dependent > 1000 AND age > 30;

What is the default sort order?

The default sort order is ASC. For example:

SELECT Concat(name, ' (', location, ') ') FROM vendor ORDER BY name;

If we specify the ORDER BY clause but do not specify either ASC, or DESC, MySQL order the result using ASC.

How can we use 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

How can we do wildcard search?

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.

How can we use aggregate functions?

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;
  1. 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.
  1. 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.
  2. MIN(column_name)
  3. SUM(column_name)
  4. 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;

What is the difference between COUNT(*) and COUNT(columnName)?

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.

Can we use DISTINCT with COUNT()?

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.

Can we use multiple aggregate functions in a single statement?

Yes. 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;

How can we use the GROUP BY clause?

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:

  1. 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.
  2. 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.
  3. 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.
  4. Aside from the aggregate calculation statements, every column in your SELECT should be present in the GROUP BY clause.
  5. 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.
  6. 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;

How can we select distinct rows?

Use the DISTINCT keyword:

SELECT DISTINCT columnName1, columnName2 FROM tableName;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License