{"id":11564,"date":"2019-03-14T12:43:02","date_gmt":"2019-03-14T12:43:02","guid":{"rendered":"http:\/\/www.appservgrid.com\/paw92\/?p=11564"},"modified":"2019-03-14T12:43:02","modified_gmt":"2019-03-14T12:43:02","slug":"learn-how-to-use-several-functions-of-mysql-and-mariadb-part-2","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/03\/14\/learn-how-to-use-several-functions-of-mysql-and-mariadb-part-2\/","title":{"rendered":"Learn How to Use Several Functions of MySQL and MariaDB \u2013 Part 2"},"content":{"rendered":"

This is the second part of a 2-article series about the essentials of\u00a0MariaDB \/ MySQL<\/strong>\u00a0commands. Please refer to our previous article on this topic before proceeding.<\/p>\n

    \n
  1. Learn MySQL\/MariaDB Basics for Beginners \u2013 Part 1<\/a><\/li>\n<\/ol>\n

    In this second part of\u00a0MySQL\/MariaDB beginner series<\/a>, we will explain how to limit the number of rows returned by a\u00a0SELECT<\/strong>\u00a0query, and how to order the result set based on a given condition.<\/p>\n

    Additionally, we will learn how to group the records and perform basic mathematical manipulation on numeric fields. All of this will help us to create a SQL script that we can use to produce useful reports.<\/p>\n

    Prerequisites<\/h4>\n

    To begin, please follow these steps:<\/p>\n

    1.<\/strong>\u00a0Download the\u00a0employees<\/code>\u00a0sample database, which includes six tables consisting of\u00a04<\/strong>\u00a0million records in total.<\/p>\n

    # wget https:\/\/launchpad.net\/test-db\/employees-db-1\/1.0.6\/+download\/employees_db-full-1.0.6.tar.bz2\r\n# tar xjf employees_db-full-1.0.6.tar.bz2\r\n# cd employees_db\r\n<\/pre>\n
    2.<\/strong>\u00a0Enter the\u00a0MariaDB<\/strong>\u00a0prompt and create a database named\u00a0employees<\/strong>:<\/p>\n
    # mysql -u root -p<\/strong>\r\nEnter password: \r\nWelcome to the MariaDB monitor.  Commands end with ; or \\g.\r\nYour MariaDB connection id is 2\r\nServer version: 10.1.14-MariaDB MariaDB Server\r\n\r\nCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nMariaDB [(none)]><\/strong> CREATE DATABASE employees;<\/strong>\r\nQuery OK, 1 row affected (0.00 sec)\r\n<\/pre>\n
    3.<\/strong>\u00a0Import it into your MariaDB server as follows:<\/p>\n
    MariaDB [(none)]><\/strong> source employees.sql<\/strong>\r\n<\/pre>\n
    Wait 1-2 minutes until the sample database is loaded (keep in mind we\u2019re talking about\u00a04M<\/strong>\u00a0records here!).<\/p>\n
    4.<\/strong>\u00a0Verify that the database was imported correctly by listing its tables:<\/p>\n
    MariaDB [employees]><\/strong> USE employees;<\/strong>\r\nDatabase changed\r\nMariaDB [employees]><\/strong> SHOW TABLES;<\/strong>\r\n+---------------------+\r\n| Tables_in_employees |\r\n+---------------------+\r\n| departments         |\r\n| dept_emp            |\r\n| dept_manager        |\r\n| employees           |\r\n| salaries            |\r\n| titles              |\r\n+---------------------+\r\n6 rows in set (0.02 sec)\r\n<\/pre>\n
    5.<\/strong>\u00a0Create a special account to use with the\u00a0employees<\/strong>\u00a0database (feel free to choose another account name and password):<\/p>\n
    MariaDB [employees]><\/strong> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';<\/strong>\r\nQuery OK, 0 rows affected (0.03 sec)\r\n\r\nMariaDB [employees]><\/strong> GRANT ALL PRIVILEGES ON  employees.* to empadmin@localhost;<\/strong>\r\nQuery OK, 0 rows affected (0.02 sec)\r\n\r\nMariaDB [employees]><\/strong> FLUSH PRIVILEGES;<\/strong>\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nMariaDB [employees]><\/strong> exit<\/strong>\r\nBye\r\n<\/pre>\n
    Now login as\u00a0empadmin<\/strong>\u00a0user into Mariadb prompt.<\/p>\n
    # mysql -u empadmin -p<\/strong>\r\nEnter password: \r\nWelcome to the MariaDB monitor.  Commands end with ; or \\g.\r\nYour MariaDB connection id is 4\r\nServer version: 10.1.14-MariaDB MariaDB Server\r\n\r\nCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nMariaDB [(none)]><\/strong> USE employees;<\/strong>\r\nReading table information for completion of table and column names\r\nYou can turn off this feature to get a quicker startup with -A\r\n\r\nDatabase changed\r\n<\/pre>\n
    \n
    \"Learn<\/a><\/p>\n
    Learn Basic MySQL Commands for Beginners<\/p>\n<\/div>\n
    Make sure all of the steps outlined in the above image have been completed before proceeding.<\/p>\n
    Ordering and Limiting the Number of Rows in the Result Set<\/h4>\n
    The salaries table contains all the incomes of each employee with start and end dates. We may wish to view the salaries of\u00a0emp_no=10001<\/code>\u00a0over time. This will help answer the following questions:<\/p>\n
      \n
    1. Did he \/ she get any raises?<\/li>\n
    2. If so, when?<\/li>\n<\/ol>\n
      Execute the following query to find out:<\/p>\n
      MariaDB [employees]><\/strong> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;<\/strong>\r\n+--------+--------+------------+------------+\r\n| emp_no | salary | from_date  | to_date    |\r\n+--------+--------+------------+------------+\r\n|  10001 |  60117 | 1986-06-26 | 1987-06-26 |\r\n|  10001 |  62102 | 1987-06-26 | 1988-06-25 |\r\n|  10001 |  66074 | 1988-06-25 | 1989-06-25 |\r\n|  10001 |  66596 | 1989-06-25 | 1990-06-25 |\r\n|  10001 |  66961 | 1990-06-25 | 1991-06-25 |\r\n|  10001 |  71046 | 1991-06-25 | 1992-06-24 |\r\n|  10001 |  74333 | 1992-06-24 | 1993-06-24 |\r\n|  10001 |  75286 | 1993-06-24 | 1994-06-24 |\r\n|  10001 |  75994 | 1994-06-24 | 1995-06-24 |\r\n|  10001 |  76884 | 1995-06-24 | 1996-06-23 |\r\n|  10001 |  80013 | 1996-06-23 | 1997-06-23 |\r\n|  10001 |  81025 | 1997-06-23 | 1998-06-23 |\r\n|  10001 |  81097 | 1998-06-23 | 1999-06-23 |\r\n|  10001 |  84917 | 1999-06-23 | 2000-06-22 |\r\n|  10001 |  85112 | 2000-06-22 | 2001-06-22 |\r\n|  10001 |  85097 | 2001-06-22 | 2002-06-22 |\r\n|  10001 |  88958 | 2002-06-22 | 9999-01-01 |\r\n+--------+--------+------------+------------+\r\n17 rows in set (0.03 sec)\r\n<\/pre>\n
      Now what if we need to view the latest 5 raises? We can do\u00a0ORDER BY from_date DESC<\/strong>. The\u00a0DESC<\/strong>\u00a0keyword indicates that we want to sort the result set in descending order.<\/p>\n
      Additionally,\u00a0LIMIT 5<\/strong>\u00a0allows us to return only the top\u00a05<\/strong>\u00a0rows in the result set:<\/p>\n
      MariaDB [employees]><\/strong> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;<\/strong>\r\n+--------+--------+------------+------------+\r\n| emp_no | salary | from_date  | to_date    |\r\n+--------+--------+------------+------------+\r\n|  10001 |  88958 | 2002-06-22 | 9999-01-01 |\r\n|  10001 |  85097 | 2001-06-22 | 2002-06-22 |\r\n|  10001 |  85112 | 2000-06-22 | 2001-06-22 |\r\n|  10001 |  84917 | 1999-06-23 | 2000-06-22 |\r\n|  10001 |  81097 | 1998-06-23 | 1999-06-23 |\r\n+--------+--------+------------+------------+\r\n5 rows in set (0.00 sec)\r\n<\/pre>\n
      \n
      \"Query<\/a><\/p>\n
      Query MySQL Table by Date Order<\/p>\n<\/div>\n
      You can also use\u00a0ORDER BY<\/strong>\u00a0with multiple fields. For example, the following query will order the result set based on the employee\u2019s birth date in ascending form (the default) and then by the last names in alphabetical descending form:<\/p>\n
      MariaDB [employees]><\/strong> SELECT CONCAT(last_name, ', ', first_name) AS Name, gender AS Gender,  hire_date AS \"Hire date\" FROM employees ORDER BY birth_date, last_name DESC LIMIT 10;<\/strong>\r\n+--------------------+--------+------------+\r\n| Name               | Gender | Hire date  |\r\n+--------------------+--------+------------+\r\n| Whitcomb, Kiyokazu | M      | 1988-07-26 |\r\n| Schaad, Ronghao    | M      | 1988-07-10 |\r\n| Remmele, Supot     | M      | 1989-01-27 |\r\n| Pocchiola, Jouni   | M      | 1985-03-10 |\r\n| Kuzuoka, Eishiro   | M      | 1992-02-12 |\r\n| Decaestecker, Moni | M      | 1986-10-06 |\r\n| Wiegley, Mircea    | M      | 1985-07-18 |\r\n| Vendrig, Sachar    | M      | 1985-11-04 |\r\n| Tsukuda, Cedric    | F      | 1993-12-12 |\r\n| Tischendorf, Percy | M      | 1986-11-10 |\r\n+--------------------+--------+------------+\r\n10 rows in set (0.31 sec)\r\n<\/pre>\n
      \n
      \"Query<\/a><\/p>\n
      Query MySQL Table by Birth Date<\/p>\n<\/div>\n
      You can view more information about\u00a0LIMIT<\/strong>\u00a0here<\/a>.<\/p>\n
      Grouping Records \/ MAX, MIN, AVG, and ROUND<\/h4>\n
      As we mentioned earlier, the\u00a0salaries<\/code>\u00a0table contains the incomes of each employee over time. Besides\u00a0LIMIT<\/strong>, we can use the\u00a0MAX<\/strong>\u00a0and\u00a0MIN<\/strong>\u00a0keywords to determine when maximum and minimum number of employees were hired:<\/p>\n
      MariaDB [employees]><\/strong> SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS \"Max. salary\" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;<\/strong>\r\n+-----------------+-------------+\r\n| Name            | Max. salary |\r\n+-----------------+-------------+\r\n| Facello, Georgi |       88958 |\r\n| Simmel, Bezalel |       72527 |\r\n| Bamford, Parto  |       43699 |\r\n+-----------------+-------------+\r\n3 rows in set (0.02 sec)\r\n\r\nMariaDB [employees]><\/strong> SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS \"Min. salary\" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;<\/strong>\r\n+-----------------+-------------+\r\n| Name            | Min. salary |\r\n+-----------------+-------------+\r\n| Facello, Georgi |       60117 |\r\n| Simmel, Bezalel |       65828 |\r\n| Bamford, Parto  |       40006 |\r\n+-----------------+-------------+\r\n3 rows in set (0.00 sec)\r\n<\/pre>\n
      \n
      \"Grouping<\/a><\/p>\n
      Grouping MySQL Records Using MAX and MIN Keywords<\/p>\n<\/div>\n
      Based on the above result sets, can you guess what the below query will return?<\/p>\n
      MariaDB [employees]><\/strong> SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS \"Avg. salary\" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;<\/strong>\r\n+-----------------+-------------+\r\n| Name            | Avg. salary |\r\n+-----------------+-------------+\r\n| Facello, Georgi |    75388.94 |\r\n| Simmel, Bezalel |    68854.50 |\r\n| Bamford, Parto  |    43030.29 |\r\n+-----------------+-------------+\r\n3 rows in set (0.01 sec)\r\n<\/pre>\n
      If you agree that it will return the average (as specified by\u00a0AVG<\/strong>) salary over time rounded to 2 decimals (as indicated by\u00a0ROUND<\/strong>), you\u2019re right.<\/p>\n
      If we want to view the sum of the salaries grouped by employee and return the top\u00a05<\/strong>, we can use the following query:<\/p>\n
      MariaDB [employees]><\/strong> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;<\/strong>\r\n+--------+---------+\r\n| emp_no | Salary  |\r\n+--------+---------+\r\n| 109334 | 2553036 |\r\n|  43624 | 2492873 |\r\n|  66793 | 2383923 |\r\n| 237542 | 2381119 |\r\n|  47978 | 2374024 |\r\n+--------+---------+\r\n5 rows in set (2.22 sec)\r\n<\/pre>\n
      In the above query, salaries are grouped by employee and then the sum is performed.<\/p>\n
      Bringing it all Together<\/h4>\n
      Fortunately, we don\u2019t need to run query after query to produce a report. Instead, we can create a script with a series of SQL commands to return all the necessary result sets.<\/p>\n
      Once we execute the script, it will return the required information without further intervention on our part. For example, let\u2019s create a file named\u00a0maxminavg.sql<\/strong>\u00a0in the current working directory with the following contents:<\/p>\n
      --Select database\r\nUSE employees;\r\n--Calculate maximum salaries\r\nSELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS \"Max. salary\" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;\r\n--Calculate minimum salaries\r\nSELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS \"Min. salary\" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;\r\n--Calculate averages, round to 2 decimal places\r\nSELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS \"Avg. salary\" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;\r\n<\/pre>\n
      Lines beginning with two dashes are ignored, and the individual queries are executed one after another. We can execute this script either from the Linux command line:<\/p>\n
      # mysql -u empadmin -p < maxminavg.sql<\/strong>\r\nEnter password: <\/strong>\r\nName\tMax. salary\r\nFacello, Georgi\t88958\r\nSimmel, Bezalel\t72527\r\nBamford, Parto\t43699\r\nName\tMin. salary\r\nFacello, Georgi\t60117\r\nSimmel, Bezalel\t65828\r\nBamford, Parto\t40006\r\nName\tAvg. salary\r\nFacello, Georgi\t75388.94\r\nSimmel, Bezalel\t68854.50\r\nBamford, Parto\t43030.29\r\n<\/pre>\n
      or from the MariaDB prompt:<\/p>\n
      # mysql -u empadmin -p<\/strong>\r\nEnter password: \r\nWelcome to the MariaDB monitor.  Commands end with ; or \\g.\r\nYour MariaDB connection id is 4\r\nServer version: 10.1.14-MariaDB MariaDB Server\r\n\r\nCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nMariaDB [(none)]><\/strong> source maxminavg.sql<\/strong>\r\nReading table information for completion of table and column names\r\nYou can turn off this feature to get a quicker startup with -A\r\n\r\nDatabase changed\r\n<\/pre>\n
      \n
      \"MySQL<\/a><\/p>\n
      MySQL Script to Run SQL Commands<\/p>\n<\/div>\n
      Summary<\/h3>\n
      In this article we have explained how to use several MariaDB functions in order to refine result sets returned by\u00a0SELECT<\/strong>\u00a0statements. Once they have defined, multiple individual queries can be inserted in a script to execute it more easily and to reduce the risk of human error.<\/p>\n
      Do you have any questions or suggestions about this article? Feel free to drop us a note using the comment form below. We look forward to hearing from you!<\/p>\n
      Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
      This is the second part of a 2-article series about the essentials of\u00a0MariaDB \/ MySQL\u00a0commands. Please refer to our previous article on this topic before proceeding. Learn MySQL\/MariaDB Basics for Beginners \u2013 Part 1 In this second part of\u00a0MySQL\/MariaDB beginner series, we will explain how to limit the number of rows returned by a\u00a0SELECT\u00a0query, and … <\/p>\n
      Continue reading “Learn How to Use Several Functions of MySQL and MariaDB \u2013 Part 2”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-11564","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11564","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/comments?post=11564"}],"version-history":[{"count":1,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11564\/revisions"}],"predecessor-version":[{"id":11565,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11564\/revisions\/11565"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=11564"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=11564"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=11564"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}