Top

A Linux User Reference

Search tips
  • search ignores words that are less than 4 characters in length
  • searches are case insensitve
  • if a search does not return anything try it in Boolean mode then Query expansion mode by checking the appropriate radio button e.g. searching for 'cron' in just the Administration category returns nothing - presumably because the 50% threshold is reached. Boolean mode ignores this threshold so a search for 'cron' returns several hits
  • in Boolean mode preceding a word with a '+' means the result must include that word, a '-' means it must not
  • in Boolean mode '+crontab -anacron' means match articles about crontab that DO NOT mention anacron
  • to match a phrase e.g. 'manage system' check the Boolean mode radio button and enclose the phrase in quotes "some phrase ..."
  • in Query expansion mode the search context is expanded beyond the keywords you entered - relevancy of hits may well be degraded

DATABASE

MySql queries

  • Select all, multiple selects and column aliasing

    The select statement is used to retrieve rows selected from one or more tables and can include unions and sub-queries. One or more union keywords can be used to run multiple select statements. Each statement being separated by the union keyword.

    Select all rows and columns in table products

    mysql> select * from products;
    +---------+----------------+----------+-----------------+
    | prod_id | prod_name      | prod_vat | prod_sale_price |
    +---------+----------------+----------+-----------------+
    | p00001  | Super Coffee   |    17.50 |            2.49 |
    | p00002  | Super Tea      |    17.50 |            1.49 |
    | p00003  | Baked Beans    |    17.50 |            0.65 |
    | p00004  | Special Lager  |    17.50 |            0.99 |
    | p00005  | Whacko Brandy  |    17.50 |            2.12 |
    | p00006  | Finest Tobacco |    17.50 |            7.50 |
    +---------+----------------+----------+-----------------+
    

    Select with 'union'

    mysql> select category from contents union select title from articles;
    +---------------------------------------------------------------------------------------+
    | category                                                                              |
    +---------------------------------------------------------------------------------------+
    | Administration                                                                        |
    | Boot                                                                                  |
    | Database                                                                              |
    | CRON or ANACRON?                                                                      |
    | CRON files                                                                            |
    .....
    

    'union' simply runs run 'select' commands one after another. Each 'select' should result in the same number of columns.

    The first 3 rows above are from the contents table, the rest from articles.

    Select only some columns, rename them using aliasing

    mysql> select prod_id as Code, prod_name as Product, prod_sale_price as Price from products;
    +--------+----------------+-------+
    | Code   | Product        | Price |
    +--------+----------------+-------+
    | p00001 | Super Coffee   |  2.49 |
    | p00002 | Super Tea      |  1.49 |
    | p00003 | Baked Beans    |  0.65 |
    | p00004 | Special Lager  |  0.99 |
    | p00005 | Whacko Brandy  |  2.12 |
    | p00006 | Finest Tobacco |  7.50 |
    +--------+----------------+-------+
    
  • Limit, group by and order

    Limit results to 3, sort by highest priced first

    mysql> select prod_id as Code, prod_name as Product, prod_sale_price as Price
        -> from products order by prod_sale_price desc limit 3;
    +--------+----------------+-------+
    | Code   | Product        | Price |
    +--------+----------------+-------+
    | p00006 | Finest Tobacco |  7.50 |
    | p00001 | Super Coffee   |  2.49 |
    | p00005 | Whacko Brandy  |  2.12 |
    +--------+----------------+-------+
    

    ASC (ascending) is the default sort order

    Grouping allows aggregate functions to be run against groups or sets of data as opposed to all entries in a column. So if you had a set of monthly figures, grouping by the month allows for monthly calculations i.e. top earners for the month of April.

    Group by categories

    mysql> select category  from contents;
    +----------------+
    | category       |
    +----------------+
    | Administration |
    | Administration |
    | Administration |
    | Administration |
    | Administration |
    | Administration |
    | Administration |
    | Administration |
    | Administration |
    | Administration |
    | Boot           |
    | Boot           |
    | Boot           |
    | Boot           |
    | Boot           |
    | Database       |
    | Database       |
    | Database       |
    | Database       |
    +----------------+
    19 rows in set (0.00 sec)
    
    mysql> select category  from contents group by category;
    +----------------+
    | category       |
    +----------------+
    | Administration |
    | Boot           |
    | Database       |
    +----------------+
    3 rows in set (0.00 sec)
    
  • Performing arithmetic operations
    sum()

    Display the gross profit margin for all products to date

    mysql> select (sum(Sales*Unit_sale)-sum((Opening_stock+Purchases-Closing_stock)* \
           Unit_cost))/(sum(Sales*Unit_sale))
        -> as 'Gross Profit Margin' from Product_history;
    +---------------------+
    | Gross Profit Margin |
    +---------------------+
    |            0.606006 |
    +---------------------+
    
  • Combining aliasing, grouping, ordering and arithmetic

    Display sales, gross and monthly margins per product to date. Top gross earners first

    mysql> select prod_id as ID, prod_name as Product,
        -> group_concat(Unit_sale-Unit_cost) as 'Monthly Margins', group_concat(Sales) \
           as 'Monthly Sales',
        -> sum((Unit_sale-Unit_cost)*Sales) as 'Gross Profit'
        -> from products, Product_history where products.prod_id=Product_history.Product_id
        -> group by prod_id order by sum((Unit_sale-Unit_cost)*Sales) desc;
    +--------+----------------+---------------------+-----------------+--------------+
    | ID     | Product        | Monthly Margins     | Monthly Sales   | Gross Profit |
    +--------+----------------+---------------------+-----------------+--------------+
    | p00006 | Finest Tobacco | 4.60,4.65,4.60,4.80 | 200,310,400,390 |      6073.50 |
    | p00004 | Special Lager  | 0.34,0.34,0.40,0.40 | 52,68,85,83     |       108.00 |
    | p00002 | Super Tea      | 0.64,0.64,0.59,0.64 | 11,17,15,19     |        38.93 |
    | p00003 | Baked Beans    | 0.43,0.43,0.43,0.46 | 14,18,22,21     |        32.88 |
    | p00001 | Super Coffee   | 0.59,0.64,0.59,0.59 | 8,12,12,14      |        27.74 |
    | p00005 | Whacko Brandy  | 0.57,0.57,0.55,0.65 | 8,10,12,15      |        26.61 |
    +--------+----------------+---------------------+-----------------+--------------+
    

    Calculate gross profit on a monthly basis

    mysql> select monthname(date) as Month,
        -> sum(Sales*Unit_sale),
        -> sum((Opening_stock+Purchases)*Unit_cost) as Costs,
        -> sum((Sales*Unit_sale)-((Opening_stock+Purchases)*Unit_cost)) as Gross_Profit
        -> from Product_history group by month(date);
    +-------+----------------------+---------+--------------+
    | Month | sum(Sales*Unit_sale) | Costs   | Gross_Profit |
    +-------+----------------------+---------+--------------+
    | April |              1619.35 |  689.68 |       929.67 |
    | May   |              2488.93 | 1292.79 |      1196.14 |
    | June  |              3179.38 | 1433.56 |      1745.82 |
    | July  |              3120.92 | 1632.78 |      1488.14 |
    +-------+----------------------+---------+--------------+
    

    Calculate each product's gross profit on a monthly basis

    mysql> select monthname(date) as Month, Product_Code, Product_name, Sales*Unit_sale,
        -> (Opening_stock+Purchases)*Unit_cost as Costs,
        -> (Sales*Unit_sale)-((Opening_stock+Purchases)*Unit_cost) as Gross_Profit
        -> from Products, Product_history
        -> where Products.Product_Code=Product_history.Product_id order by date, Product_Code;
    +-------+--------------+----------------+-----------------+---------+--------------+
    | Month | Product_Code | Product_name   | Sales*Unit_sale | Costs   | Gross_Profit |
    +-------+--------------+----------------+-----------------+---------+--------------+
    | April | p00001       | Super Coffee   |           19.92 |   22.80 |        -2.88 |
    | April | p00002       | Super Tea      |           21.89 |   16.20 |         5.69 |
    | April | p00003       | Baked Beans    |            9.10 |    5.28 |         3.82 |
    | April | p00004       | Special Lager  |           51.48 |   46.80 |         4.68 |
    | April | p00005       | Whacko Brandy  |           16.96 |   18.60 |        -1.64 |
    | April | p00006       | Finest Tobacco |         1500.00 |  580.00 |       920.00 |
    | May   | p00001       | Super Coffee   |           29.88 |   29.60 |         0.28 |
    .....
    | June  | p00006       | Finest Tobacco |         3000.00 | 1276.00 |      1724.00 |
    | July  | p00001       | Super Coffee   |           34.86 |   30.40 |         4.46 |
    | July  | p00002       | Super Tea      |           37.81 |   39.15 |        -1.34 |
    | July  | p00003       | Baked Beans    |           13.65 |    7.98 |         5.67 |
    | July  | p00004       | Special Lager  |           78.85 |   72.05 |         6.80 |
    | July  | p00005       | Whacko Brandy  |           30.75 |   25.20 |         5.55 |
    | July  | p00006       | Finest Tobacco |         2925.00 | 1458.00 |      1467.00 |
    +-------+--------------+----------------+-----------------+---------+--------------+
    

    Display the top three product performers to date

    mysql> select Product_code, Product_name,sum(Unit_sale-Unit_cost)*Sales from Products, \
           Product_history where Products.Product_code=Product_history.Product_id \
           group by Product_code order by sum(Unit_sale-Unit_cost)*Sales desc limit 3;
    +--------------+----------------+--------------------------------+
    | Product_code | Product_name   | sum(Unit_sale-Unit_cost)*Sales |
    +--------------+----------------+--------------------------------+
    | p00006       | Finest Tobacco |                        3730.00 |
    | p00004       | Special Lager  |                          76.96 |
    | p00002       | Super Tea      |                          27.61 |
    +--------------+----------------+--------------------------------+
    

    Display the top three sellers for the month of April

    mysql> select Monthname(date),prod_id, prod_name,sum(Sales) from products, Product_history \
           where products.prod_id=Product_history.Product_id and Monthname(date)="April" \
           group by prod_id order by Sales desc limit 3;
    +-----------------+---------+----------------+------------+
    | Monthname(date) | prod_id | prod_name      | sum(Sales) |
    +-----------------+---------+----------------+------------+
    | April           | p00006  | Finest Tobacco |        200 |
    | April           | p00004  | Special Lager  |         52 |
    | April           | p00003  | Baked Beans    |         14 |
    +-----------------+---------+----------------+------------+
    
  • Using a temporary table

    Display the gross profit to date - a total of gross profit for each month

    mysql> select sum(Sales*Unit_sale) as 'Total Sales', sum((Opening_stock+Purchases)* \
           Unit_cost) as 'Total Costs', sum((Sales*Unit_sale)-((Opening_stock+Purchases)* \
           Unit_cost)) as Gross_Profit from Product_history into @Total_sales, 
           @Total_purchases, @Gross_profit; \
           select @Total_sales, @Total_purchases, @Gross_profit;
    Query OK, 1 row affected (0.00 sec)
    +--------------+------------------+---------------+
    | @Total_sales | @Total_purchases | @Gross_profit |
    +--------------+------------------+---------------+
    | 10408.58     | 5048.81          | 5359.77       |
    +--------------+------------------+---------------+
    

    The temporary table is a result of defining temporary columns. Each temporary column name starts with an '@'.

  • Pattern Matching with 'like' and 'regexp'

    Pattern matching using SQL simple regular expression comparison. Returns 1 for true, 0 for false.

    Select products that have 'Tea' or startwith 'B and have another B' in their name

    mysql> select * from products where prod_name like 'B%B%' or prod_name like '%Tea%';
    +---------+-------------+----------+-----------------+
    | prod_id | prod_name   | prod_vat | prod_sale_price |
    +---------+-------------+----------+-----------------+
    | p00002  | Super Tea   |    17.50 |            1.49 |
    | p00003  | Baked Beans |    17.50 |            0.65 |
    +---------+-------------+----------+-----------------+
    

    Select all suppliers from a county begining with 'Lanc'

    mysql> select supp_name, supp_county from suppliers where supp_county like 'Lanc%';
    +------------------+-------------+
    | supp_name        | supp_county |
    +------------------+-------------+
    | The Bean Factory | Lancashire  |
    | Cheapo's         | Lancashire  |
    +------------------+-------------+
    

    The pattern can be an extended regular expression.

    Select all suppliers whose county startswith 'L' followed by one or more characters and ends in 're'

    mysql> select supp_name, supp_county from suppliers where supp_county regexp '^L.*re$';
    +------------------+-------------+
    | supp_name        | supp_county |
    +------------------+-------------+
    | The Bean Factory | Lancashire  |
    | Cheapo's         | Lancashire  |
    +------------------+-------------+
    
  • Joins

    Joins are used to associate tables within a single select statement. This allows data to be represented by multiple tables providing more efficient storage, easier manipulation and greater scalability. The following two tables used in join examples:

    products

    prod_id prod_name prod_vat prod_sale_price
    p00001 Super Coffee 17.50 2.49
    p00002 Super Tea 17.50 1.49
    p00003 Baked Beans 17.50 0.65
    p00004 Special Lager 17.50 0.99
    p00005 Whacko Brandy 17.50 2.12
    p00006 Finest Tobacco 17.50 7.50

    history

    hist_ref prod_id hist_new_price hist_new_vat hist_date
    7 p00001 2.49 17.50 2009-12-21
    8 p00002 1.49 17.50 2009-12-21
    9 p00003 0.65 17.50 2009-12-21
    10 p00004 0.99 17.50 2009-12-21
    11 p00005 2.12 17.50 2009-12-21
    12 p00006 7.50 17.50 2009-12-21

    There are often subtle differences in the syntax depending on the DB being used. There are several types of joins - the two most common are:

    Inner or equi join examples

    • Contains only the rows that exist in each table specified.
    • If the column name is the same in each table prefix it with the table name thereby removing any ambiguity. An error will occur if you do not.

      select col1, col2, ... from tab1, tab2, ...  where tab1.colname=tab2.colname  
      

    An inner join

    mysql> select products.prod_id,prod_name,prod_sale_price,hist_ref,hist_new_price, hist_date \
           from products, pricehistory where products.prod_id=pricehistory.prod_id;
    +---------+----------------+-----------------+----------+----------------+------------+
    | prod_id | prod_name      | prod_sale_price | hist_ref | hist_new_price | hist_date  |
    +---------+----------------+-----------------+----------+----------------+------------+
    | p00001  | Super Coffee   |            2.49 |        7 |           2.49 | 2009-12-21 |
    | p00002  | Super Tea      |            1.49 |        8 |           1.49 | 2009-12-21 |
    | p00003  | Baked Beans    |            0.65 |        9 |           0.65 | 2009-12-21 |
    | p00004  | Special Lager  |            0.99 |       10 |           0.99 | 2009-12-21 |
    | p00005  | Whacko Brandy  |            2.12 |       11 |           2.12 | 2009-12-21 |
    | p00006  | Finest Tobacco |            7.50 |       12 |           7.50 | 2009-12-21 |
    +---------+----------------+-----------------+----------+----------------+------------+
    

    An inner join defining the join type

    mysql> select products.prod_id,prod_name,prod_sale_price,hist_ref,hist_new_price, hist_date \
           from products inner join pricehistory on products.prod_id=pricehistory.prod_id;
    +---------+----------------+-----------------+----------+----------------+------------+
    | prod_id | prod_name      | prod_sale_price | hist_ref | hist_new_price | hist_date  |
    +---------+----------------+-----------------+----------+----------------+------------+
    | p00001  | Super Coffee   |            2.49 |        7 |           2.49 | 2009-12-21 |
    | p00002  | Super Tea      |            1.49 |        8 |           1.49 | 2009-12-21 |
    | p00003  | Baked Beans    |            0.65 |        9 |           0.65 | 2009-12-21 |
    | p00004  | Special Lager  |            0.99 |       10 |           0.99 | 2009-12-21 |
    | p00005  | Whacko Brandy  |            2.12 |       11 |           2.12 | 2009-12-21 |
    | p00006  | Finest Tobacco |            7.50 |       12 |           7.50 | 2009-12-21 |
    +---------+----------------+-----------------+----------+----------------+------------+
    

    Clearly defining the join type is otpional.

    Outer join

    • Contains a combined table with all rows in one table (left or right) and blanks in columns where a column does not exist in the other table(s). The resulting table will therefore differ depending on which table is chosen to provide all of the rows.
    • Left or right defines which table defines all of the columns.
    • Full join contains all rows in all tables regardless of matching rows

      select col1, col2, ... from tab1 [full] [direction - left or right]  \
      join tab2 on tab1.colname=tab2.colname
      

    Left and Right outer join matching on 'prod_id'

    mysql> select * from products right join pricehistory using (prod_id);
    +---------+----- / --------+------------+----------------+----------+-----------------+
    | prod_id | hist / new_vat | hist_date  | prod_name      | prod_vat | prod_sale_price |
    +---------+----- / --------+------------+----------------+----------+-----------------+
    | p00001  |      /   17.50 | 2009-12-21 | Super Coffee   |    17.50 |            2.49 |
    | p00002  |      /   17.50 | 2009-12-21 | Super Tea      |    17.50 |            1.49 |
    | p00003  |      /   17.50 | 2009-12-21 | Baked Beans    |    17.50 |            0.65 |
    | p00004  |      /   17.50 | 2009-12-21 | Special Lager  |    17.50 |            0.99 |
    | p00005  |      /   17.50 | 2009-12-21 | Whacko Brandy  |    17.50 |            2.12 |
    | p00006  |      /   17.50 | 2009-12-21 | Finest Tobacco |    17.50 |            7.50 |
    +---------+----- / --------+------------+----------------+----------+-----------------+
    
    mysql> select * from products left join pricehistory using (prod_id);
    +---------+----------------+----------+-----------------+----- / --------+------------+
    | prod_id | prod_name      | prod_vat | prod_sale_price | hist / new_vat | hist_date  |
    +---------+----------------+----------+-----------------+----- / --------+------------+
    | p00001  | Super Coffee   |    17.50 |            2.49 |      /   17.50 | 2009-12-21 |
    | p00002  | Super Tea      |    17.50 |            1.49 |      /   17.50 | 2009-12-21 |
    | p00003  | Baked Beans    |    17.50 |            0.65 |      /   17.50 | 2009-12-21 |
    | p00004  | Special Lager  |    17.50 |            0.99 |      /   17.50 | 2009-12-21 |
    | p00005  | Whacko Brandy  |    17.50 |            2.12 |      /   17.50 | 2009-12-21 |
    | p00006  | Finest Tobacco |    17.50 |            7.50 |      /   17.50 | 2009-12-21 |
    +---------+----------------+----------+-----------------+----- / --------+------------+
    

    Selective outer join

    mysql> select invoiceitems.inv_ref, invoices.inv_date, item_number, prod_id, item_unit_cost, \
           item_qty from invoiceitems join invoices on invoiceitems.inv_ref = invoices.inv_ref \
           where invoiceitems.inv_ref IN ("inv00001", "inv00004");
    +----------+------------+-------------+---------+----------------+----------+
    | inv_ref  | inv_date   | item_number | prod_id | item_unit_cost | item_qty |
    +----------+------------+-------------+---------+----------------+----------+
    | inv00001 | 2008-04-01 |           1 | p00001  |           2.12 |       12 |
    | inv00004 | 2008-04-03 |           1 | p00004  |           0.45 |       24 |
    | inv00004 | 2008-04-03 |           2 | p00005  |           1.65 |       12 |
    +----------+------------+-------------+---------+----------------+----------+
    

    Displays invoices 'inv00001' and 'inv00004' along with there respective invoice items.

    The same as above but using an inner join

    mysql> select invoiceitems.inv_ref, invoices.inv_date, item_number, prod_id, item_unit_cost, \
           item_qty from invoiceitems, invoices where invoiceitems.inv_ref=invoices.inv_ref \
           and invoiceitems.inv_ref IN ("inv00001", "inv00004");
    +----------+------------+-------------+---------+----------------+----------+
    | inv_ref  | inv_date   | item_number | prod_id | item_unit_cost | item_qty |
    +----------+------------+-------------+---------+----------------+----------+
    | inv00001 | 2008-04-01 |           1 | p00001  |           2.12 |       12 |
    | inv00004 | 2008-04-03 |           1 | p00004  |           0.45 |       24 |
    | inv00004 | 2008-04-03 |           2 | p00005  |           1.65 |       12 |
    +----------+------------+-------------+---------+----------------+----------+
    

    Same columns, different statements, same results - bit like linux really.