MySQL

Learn MySQL / MariaDB for Beginners – Part 1

Learn MySQL / MariaDB for Beginners &-8211; Part 1 &-8211; this Article or News was published on this date:2019-05-28 17:13:06 kindly share it with friends if you find it helpful

In this article we will show how to create a database (also known as a schema), tables (with data types), and explain how to perform Data Manipulation Language (DML) operations with data on a MySQL / MariaDB server.

It is assumed that you have previously 1) installed the necessary packages on your Linux system, and 2) executed mysql_secure_installation to improve the database server’s security. If not, follow below guides to install MySQL/MariaDB server.

  1. Install Latest MySQL Database in Linux Systems
  2. Install Latest MariaDB Database in Linux Systems

For brevity, we will refer to MariaDB exclusively throughout this article, but the concepts and commands outlined here apply to MySQL as well.

Part 1: Learn MySQL / MariaDB for Beginners

Creating Databases, Tables, and Authorized Users

As you know, a database can be defined in simple terms as an organized collection of information. Particularly, MariaDB is a relational database management system (RDBMS) and uses the Structure Query Language to perform operations on databases. Additionally, keep in mind that MariaDB uses the terms database and schema interchangeably.

To store persistent information in a database, we will use tables that store rows of data. Often, two or more tables will be related to each other in some way. That is part of the organization that characterizes the use of relational databases.

Creating a New Database

To create a new database named BooksDB, enter the MariaDB prompt with the following command (you’ll be prompted to enter the password for the root MariaDB user):

[[email protected] ~]- mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> 

Once the database has been created, we need to create at least two tables on it. But first let’s explore the concept of data types.

Introducing MariaDB data Types

As we explained earlier, tables are database objects where we will keep persistent information. Each table consists of two or more fields (also known as columns) of a given data type (the type of information) that such field can store.

The most common data types in MariaDB are the following (you can consult the complete list in the official MariaDB online documentation):

Numeric:
  1. BOOLEAN considers 0 as false and any other values as true.
  2. TINYINT, if used with SIGNED, covers the range from -128 to 127, whereas the UNSIGNED range is 0 to 255.
  3. SMALLINT, if used with SIGNED, covers the range from -32768 to 32767. The UNSIGNED range is 0 to 65535.
  4. INT, if used with UNSIGNED, covers the range from 0 to 4294967295, and -2147483648 to 2147483647 otherwise.

Note: In TINYINT, SMALLINT, and INT, the default SIGNED is assumed.

DOUBLE(M, D), where M is the total number of digits and D is the number of digits after the decimal point, represents a double-precision floating-point number. If UNSIGNED is specified, negative values are not be allowed.

String:
  1. VARCHAR(M) represents a string of variable length where M is the maximum allowed column length in bytes (65,535 in theory). In most cases, the number of bytes is identical to the number of characters, except for some characters that can take up as much as 3 bytes. For example, the Spanish letter ñ represents one character but takes up 2 bytes.
  2. TEXT(M) represents a column with a maximum length of 65,535 characters. However, as it happens with VARCHAR(M), the actual maximum length is reduced if multi-byte characters are stored. If M is specified, the column is created as the smallest type that can store such number of characters.
  3. MEDIUMTEXT(M) and LONGTEXT(M) are similar to TEXT(M), only that the maximum allowed lengths are 16,777,215 and 4,294,967,295 characters, respectively.
Date and Time:
  1. DATE represents the date in YYYY-MM-DD format.
  2. TIME represents the time in HH:MM:SS.sss format (hour, minutes, seconds, and milliseconds).
  3. DATETIME is the combination of DATE and TIME in YYYY-MM-DD HH:MM:SS format.
  4. TIMESTAMP is used to define the moment a row was added or updated.

After having reviewed these data types, you will be in a better position to determine which data type you need to assign to a given column in a table.

For example, a person’s name can easily fit into a VARCHAR(50), whereas a blog post will need a TEXT type (choose M as per your specific needs).

Creating Tables with Primary and Foreign Keys

Before we dive into creating tables, there are two fundamental concepts about relational databases that we need to review: primary and foreign keys.

A primary key contains a value that uniquely identifies each row, or record, in the table. On the other hand, a foreign key is used to create a link between the data in two tables, and to control the data that can be stored in the table where the foreign key is located. Both primary and foreign keys are generally INTs.

To illustrate, let’s use the BookstoreDB and create two tables named AuthorsTBL and BooksTBL as follows. The NOT NULL constraint indicates that the associated field requires a value other than NULL.

Also, AUTO_INCREMENT is used to increase by one the value of INT primary key columns when a new record is inserted into the table.

MariaDB [(none)]> USE BookstoreDB;

MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);

MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);
Create MySQL Tables with Primary and Foreign Key
MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
    -> AuthorID INT NOT NULL AUTO_INCREMENT,
    -> AuthorName VARCHAR(100),
    -> PRIMARY KEY(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
    -> BookID INT NOT NULL AUTO_INCREMENT,
    -> BookName VARCHAR(100) NOT NULL,
    -> AuthorID INT NOT NULL,
    -> BookPrice DECIMAL(6,2) NOT NULL,
    -> BookLastUpdated TIMESTAMP,
    -> BookIsAvailable BOOLEAN,
    -> PRIMARY KEY(BookID),
    -> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> 

Now we can go ahead and start inserting records into AuthorsTBL and BooksTBL.

Selecting, Inserting, Updating, and Deleting Rows

We will first populate the AuthorsTBL table. Why? Because we need to have values for AuthorID before inserting records into the BooksTBL.

Execute the following query from your MariaDB prompt:

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

After that, we will select all records from AuthorsTBL. Remember we will need the AuthorID for each record to create the INSERT query for BooksTBL.

If you want to retrieve one record at a time, you can use a WHERE clause to indicate a condition that a row must meet to be returned. For example,

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Alternatively, you can select all the records simultaneously:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
Select and Query Record in MySQL
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
|        3 | Paulo Coelho    |
+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [BookstoreDB]>
Select and Query a String in MySQL DatabaseSelect and Query a String in MySQL Database

Select and Query a String in MySQL Database

Now let’s create the INSERT query for BooksTBL, using the corresponding AuthorID to match the author of each book. A value of 1 in BookIsAvailable indicates the book is in stock, 0 otherwise:

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Insert Query in MySQL Table
MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
    -> VALUES ('And Then There Were None', 1, 14.95, 1),
    -> ('The Man in the Brown Suit', 1, 23.99, 1),
    -> ('The Stand', 2, 35.99, 1),
    -> ('Pet Sematary', 2, 17.95, 0),
    -> ('The Green Mile', 2, 29.99, 1),
    -> ('The Alchemist', 3, 25, 1),
    -> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

At this point we will do a SELECT to see the records in BooksTBL. Then let’s UPDATE the price of “The Alchemist” by Paulo Coelho and SELECT that specific record again.

Note how the BookLastUpdated field now shows a different value. As we explained earlier, a TIMESTAMP field shows the value when the record was inserted or last modified.

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
Insert Query and update Table in MySQL Database
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |
|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |
|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |
|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |
|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |
|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41 |               1 |
|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)

MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> 
Select and Query a String in MySQL DatabaseInsert and Update Database Table Record

Insert and Update Database Table Record

Although we won’t do it here, you can also delete a record if not used anymore. For example, suppose we want to delete “The Alchemist” from BooksTBL.

To do so, we will use the DELETE statement as follows:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

As in the case of UPDATE, it is a good idea to do a SELECT first in order to view the record(s) that may potentially be impacted by the DELETE.

Also, don’t forget to add the WHERE clause and a condition (BookID=6) to select the specific record to be removed. Otherwise, you run the risk of deleting all the rows in the table!

If you desire to concatenate two (or more) fields, you can use the CONCAT statement. For example, let’s say we want to return a result set that consist of one field with the book name and author in the form of “The Alchemist (Paulo Coelho)” and another column with the price.

This will require a JOIN between AuthorsTBL and BooksTBL on the common field shared by both tables (AuthorID):

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

As we can see, CONCAT allows us to join multiple string expressions separated by commas. You’ll also noticed that we chose the alias Description to represent the result set of the concatenation.

The output of the above query is shown in the below image:

Query Multiple Fields in MySQL Table
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description                                            | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie)             |     14.95 |
| The Man in the Brown Suit (Agatha Christie)            |     23.99 |
| The Stand (Stephen King)                               |     35.99 |
| Pet Sematary (Stephen King)                            |     17.95 |
| The Green Mile (Stephen King)                          |     29.99 |
| The Alchemist (Paulo Coelho)                           |     25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)
Select and Query a String in MySQL DatabaseQuery Multiple Fields in Database Table

Query Multiple Fields in Database Table

Create User to Access the BookstoreDB Database

Using root to perform all DML operations in a database is a bad idea. To avoid this, we can create a new MariaDB user account (we’ll name it bookstoreuser) and assign all necessary permissions for BookstoreDB:

MariaDB [BookstoreDB]> CREATE USER [email protected] IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to [email protected];
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Create New Database User with Privileges
MariaDB [BookstoreDB]> CREATE USER [email protected] IDENTIFIED BY 'sfnews';
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to [email protected];
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Having a dedicated, separate user for each database will prevent damages to the entire database should a single account become compromised.

Extra MySQL Tips

To clear up the MariaDB prompt, type the following command and press Enter:

MariaDB [BookstoreDB]> ! clear

To inspect the configuration of a given table, do:

MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];

For example,

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
List Columns in Database Table
MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| BookName        | varchar(100) | NO   |     | NULL              |                             |
| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |
| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |
| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)
Select and Query a String in MySQL DatabaseList Columns in Database Table

List Columns in Database Table

A quick inspection reveals that the BookIsAvailable field admits NULL values. Since we don’t want to allow that, we’ll ALTER the table as follows:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(Feel free to show the columns again – the highlighted YES in the above image should now be a NO).

Finally, to view all the databases on your server, do:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;
List All MySQL Databases
[[email protected] ~]- mysql -u bookstoreuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

The following image shows the result of the above command after accessing the MariaDB prompt as the bookstoreuser (note how this account can’t “see” any databases other than BookstoreDB and information_schema (available for all users):

Select and Query a String in MySQL DatabaseList All MySQL Databases

List All MySQL Databases

Summary

In this article we have explained how to run DML operations and how to create a database, tables, and dedicated users on a MariaDB database. Additionally, we shared a few tips that may make your life as a system / database administrator easier.

  1. MySQL Database Administration Part – 1
  2. MySQL Database Administration Part – 2
  3. MySQL Performance Tunning and Optimization – Part 3

If you have any questions about this article, don’t hesitate to let us know! Feel free to use the comment form below to reach us.

Learn How to Use Several Functions of MySQL and MariaDB – Part 2

Learn How to Use Several Functions of MySQL and MariaDB &-8211; Part 2 &-8211; this Article or News was published on this date:2019-05-28 17:12:26 kindly share it with friends if you find it helpful

This is the second part of a 2-article series about the essentials of MariaDB / MySQL commands. Please refer to our previous article on this topic before proceeding.

  1. Learn MySQL/MariaDB Basics for Beginners – Part 1

In this second part of MySQL/MariaDB beginner series, we will explain how to limit the number of rows returned by a SELECT query, and how to order the result set based on a given condition.

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.

Prerequisites

To begin, please follow these steps:

1. Download the employees sample database, which includes six tables consisting of 4 million records in total.

- wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
- tar xjf employees_db-full-1.0.6.tar.bz2
- cd employees_db

2. Enter the MariaDB prompt and create a database named employees:

- mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE employees;
Query OK, 1 row affected (0.00 sec)

3. Import it into your MariaDB server as follows:

MariaDB [(none)]> source employees.sql

Wait 1-2 minutes until the sample database is loaded (keep in mind we’re talking about 4M records here!).

4. Verify that the database was imported correctly by listing its tables:

MariaDB [employees]> USE employees;
Database changed
MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.02 sec)

5. Create a special account to use with the employees database (feel free to choose another account name and password):

MariaDB [employees]> CREATE USER [email protected] IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)

MariaDB [employees]> GRANT ALL PRIVILEGES ON  employees.* to [email protected];
Query OK, 0 rows affected (0.02 sec)

MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> exit
Bye

Now login as empadmin user into Mariadb prompt.

- mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Learn Basic MySQL Commands for BeginnersLearn Basic MySQL Commands for Beginners

Learn Basic MySQL Commands for Beginners

Make sure all of the steps outlined in the above image have been completed before proceeding.

Ordering and Limiting the Number of Rows in the Result Set

The salaries table contains all the incomes of each employee with start and end dates. We may wish to view the salaries of emp_no=10001 over time. This will help answer the following questions:

  1. Did he / she get any raises?
  2. If so, when?

Execute the following query to find out:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
+--------+--------+------------+------------+
17 rows in set (0.03 sec)

Now what if we need to view the latest 5 raises? We can do ORDER BY from_date DESC. The DESC keyword indicates that we want to sort the result set in descending order.

Additionally, LIMIT 5 allows us to return only the top 5 rows in the result set:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)
Learn Basic MySQL Commands for BeginnersQuery MySQL Table by Date Order

Query MySQL Table by Date Order

You can also use ORDER BY with multiple fields. For example, the following query will order the result set based on the employee’s birth date in ascending form (the default) and then by the last names in alphabetical descending form:

MariaDB [employees]> 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;
+--------------------+--------+------------+
| Name               | Gender | Hire date  |
+--------------------+--------+------------+
| Whitcomb, Kiyokazu | M      | 1988-07-26 |
| Schaad, Ronghao    | M      | 1988-07-10 |
| Remmele, Supot     | M      | 1989-01-27 |
| Pocchiola, Jouni   | M      | 1985-03-10 |
| Kuzuoka, Eishiro   | M      | 1992-02-12 |
| Decaestecker, Moni | M      | 1986-10-06 |
| Wiegley, Mircea    | M      | 1985-07-18 |
| Vendrig, Sachar    | M      | 1985-11-04 |
| Tsukuda, Cedric    | F      | 1993-12-12 |
| Tischendorf, Percy | M      | 1986-11-10 |
+--------------------+--------+------------+
10 rows in set (0.31 sec)
Learn Basic MySQL Commands for BeginnersQuery MySQL Table by Birth Date

Query MySQL Table by Birth Date

You can view more information about LIMIT here.

Grouping Records / MAX, MIN, AVG, and ROUND

As we mentioned earlier, the salaries table contains the incomes of each employee over time. Besides LIMIT, we can use the MAX and MIN keywords to determine when maximum and minimum number of employees were hired:

MariaDB [employees]> 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;
+-----------------+-------------+
| Name            | Max. salary |
+-----------------+-------------+
| Facello, Georgi |       88958 |
| Simmel, Bezalel |       72527 |
| Bamford, Parto  |       43699 |
+-----------------+-------------+
3 rows in set (0.02 sec)

MariaDB [employees]> 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;
+-----------------+-------------+
| Name            | Min. salary |
+-----------------+-------------+
| Facello, Georgi |       60117 |
| Simmel, Bezalel |       65828 |
| Bamford, Parto  |       40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)
Learn Basic MySQL Commands for BeginnersGrouping MySQL Records Using MAX and MIN Keywords

Grouping MySQL Records Using MAX and MIN Keywords

Based on the above result sets, can you guess what the below query will return?

MariaDB [employees]> 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;
+-----------------+-------------+
| Name            | Avg. salary |
+-----------------+-------------+
| Facello, Georgi |    75388.94 |
| Simmel, Bezalel |    68854.50 |
| Bamford, Parto  |    43030.29 |
+-----------------+-------------+
3 rows in set (0.01 sec)

If you agree that it will return the average (as specified by AVG) salary over time rounded to 2 decimals (as indicated by ROUND), you’re right.

If we want to view the sum of the salaries grouped by employee and return the top 5, we can use the following query:

MariaDB [employees]> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;
+--------+---------+
| emp_no | Salary  |
+--------+---------+
| 109334 | 2553036 |
|  43624 | 2492873 |
|  66793 | 2383923 |
| 237542 | 2381119 |
|  47978 | 2374024 |
+--------+---------+
5 rows in set (2.22 sec)

In the above query, salaries are grouped by employee and then the sum is performed.

Bringing it all Together

Fortunately, we don’t 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.

Once we execute the script, it will return the required information without further intervention on our part. For example, let’s create a file named maxminavg.sql in the current working directory with the following contents:

--Select database
USE employees;
--Calculate maximum salaries
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;
--Calculate minimum salaries
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;
--Calculate averages, round to 2 decimal places
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;

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:

- mysql -u empadmin -p  maxminavg.sql
Enter password: 
Name	Max. salary
Facello, Georgi	88958
Simmel, Bezalel	72527
Bamford, Parto	43699
Name	Min. salary
Facello, Georgi	60117
Simmel, Bezalel	65828
Bamford, Parto	40006
Name	Avg. salary
Facello, Georgi	75388.94
Simmel, Bezalel	68854.50
Bamford, Parto	43030.29

or from the MariaDB prompt:

- mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> source maxminavg.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Learn Basic MySQL Commands for BeginnersMySQL Script to Run SQL Commands

MySQL Script to Run SQL Commands

Summary

In this article we have explained how to use several MariaDB functions in order to refine result sets returned by SELECT statements. 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.

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!

How to Reset MySQL or MariaDB Root Password in Linux

How to Reset MySQL or MariaDB Root Password in Linux &-8211; this Article or News was published on this date:2019-05-28 16:59:18 kindly share it with friends if you find it helpful

If you are setting up a MySQL or MariaDB database server for the first time, chances are you will be running mysql_secure_installation soon afterwards to implement basic security settings.

One of these settings is the password for the database root account – which you must keep private and use only when strictly required. If you forget the password or need to reset it (for example, when a database administrator changes roles – or is laid off!).

Suggested Read: Change MySQL or MariaDB Root Password

This article will come in handy. We will explain how to reset or recover forgottent MySQL or MariaDB root password in Linux.

Although we will use a MariaDB server in this article, the instructions should work for MySQL as well.

Recover MySQL or MariaDB root Password

To begin, stop the database service and check the service status, we should see the environment variable we set previously:

------------- SystemD ------------- 
- systemctl stop mariadb

------------- SysVinit -------------
- /etc/init.d/mysqld stop

Next, start the service with --skip-grant-tables:

------------- SystemD ------------- 
- systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
- systemctl start mariadb
- systemctl status mariadb

------------- SysVinit -------------
- mysqld_safe --skip-grant-tables &
Start MySQL/MariaDB with Skip TablesStart MySQL/MariaDB with Skip Tables

Start MySQL/MariaDB with Skip Tables

This will allow you to connect to the database server as root without a password (you may need to switch to a different terminal to do so):

- mysql -u root

From then on, follow the steps outlined below.

MariaDB [(none)]> USE mysql;
MariaDB [(none)]> UPDATE user SET password=PASSWORD('YourNewPasswordHere') WHERE User='root' AND Host = 'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;

Finally, stop the service, unset the environment variable and start the service once again:

------------- SystemD ------------- 
- systemctl stop mariadb
- systemctl unset-environment MYSQLD_OPTS
- systemctl start mariadb

------------- SysVinit -------------
- /etc/init.d/mysql stop
- /etc/init.d/mysql start

This will cause the previous changes to take effect, allowing you to connect to the database server using the new password.

Summary

In this article we have discussed how to reset the MariaDB / MySQL root password. As always, feel free to use the comment form below to drop us a note if you have any questions or feedback. We look forward to hearing from you!

How to Change Root Password of MySQL or MariaDB in Linux

How to Change Root Password of MySQL or MariaDB in Linux &-8211; this Article or News was published on this date:2019-05-28 16:59:14 kindly share it with friends if you find it helpful

If you’re installing MySQL or MariaDB in Linux for the first time, chances are you will be executing mysql_secure_installation script to secure your MySQL installation with basic settings.

One of these settings is, database root password – which you must keep secret and use only when it is required. If you need to change it (for example, when a database administrator changes roles – or is laid off!).

Suggested Read: Recover MySQL or MariaDB Root Password in Linux

This article will come in handy. We will explain how to change a root password of MySQL or MariaDB database server in Linux.

Although we will use a MariaDB server in this article, the instructions should work for MySQL as well.

Change MySQL or MariaDB Root Password

You know the root password and want to reset it, in this case, let’s make sure MariaDB is running:

------------- CentOS/RHEL 7 and Fedora 22+ ------------- 
- systemctl is-active mariadb

------------- CentOS/RHEL 6 and Fedora -------------
- /etc/init.d/mysqld status
Check MySQL StatusCheck MySQL Status

Check MySQL Status

If the above command does not return the word active as output or its stopped, you will need to start the database service before proceeding:

------------- CentOS/RHEL 7 and Fedora 22+ ------------- 
- systemctl start mariadb

------------- CentOS/RHEL 6 and Fedora -------------
- /etc/init.d/mysqld start

Next, we will login to the database server as root:

- mysql -u root -p

For compatibility across versions, we will use the following statement to update the user table in the mysql database. Note that you need to replace YourPasswordHere with the new password you have chosen for root.

MariaDB [(none)]> USE mysql;
MariaDB [(none)]> UPDATE user SET password=PASSWORD('YourPasswordHere') WHERE User='root' AND Host = 'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;

To validate, exit your current MariaDB session by typing.

MariaDB [(none)]> exit;

and then press Enter. You should now be able to connect to the server using the new password.

Check MySQL StatusChange MySQL/MariaDB Root Password

Change MySQL/MariaDB Root Password

Summary

In this article we have explained how to change the MariaDB / MySQL root password – whether you know the current one or not.

As always, feel free to drop us a note if you have any questions or feedback using our comment form below. We look forward to hearing from you!

How to Change a Default MySQL/MariaDB Data Directory in Linux

How to Change a Default MySQL/MariaDB Data Directory in Linux &-8211; this Article or News was published on this date:2019-05-28 16:58:06 kindly share it with friends if you find it helpful

After installing the components of a LAMP stack on a CentOS/RHEL 7 server, there are a couple of things you may want to do.

Some of them have to do with increasing the security of the Apache and MySQL / MariaDB, while others may be applicable or not according to our setup or needs.

For example, based on the expected use of the database server, we may want to change the default data directory (/var/lib/mysql) to a different location. This is the case when such directory is expected to grow due to high usage.

Otherwise, the filesystem where /var is stored may collapse at one point causing the entire system to fail. Another scenario where changing the default directory is when we have a dedicated network share that we want to use to store our actual data.

For this reason, in this article we will explain how to change the default MySQL / MariaDB data directory to a different path on a CentOS/RHEL 7 server and Ubuntu/Debian distributions.

Although we will use MariaDB, the concepts explained and the steps taken in this article apply both to MySQL and to MariaDB, unless noted otherwise.

Changing the default MySQL/MariaDB Data Directory

Note: We are going to assume that our new data directory is /mnt/mysql-data. It is important to note that this directory should be owned by mysql:mysql.

- mkdir /mnt/mysql-data
- chown -R mysql:mysql /mnt/mysql-data

For your convenience, we’ve divided the process into 5 easy-to-follow steps:

Step 1: Identify Current MySQL Data Directory

To begin, it is worthy and well to identify the current data directory using the following command. Do not just assume it is still /var/lib/mysql since it could have been changed in the past.

- mysql -u root -p -e "SELECT @@datadir;"

After you enter the MySQL password, the output should be similar to.

Identify MySQL Data DirectoryIdentify MySQL Data Directory

Identify MySQL Data Directory

Step 2: Copy MySQL Data Directory to a New Location

To avoid data corruption, stop the service if it is currently running before proceeding. Use the systemd well-known commands to do so:

------------- On SystemD ------------- 
- systemctl stop mariadb
- systemctl is-active mariadb

------------- On SysVInit ------------- 
- service mysqld stop
- service mysqld status

OR

- service mysql stop
- service mysql status

If the service has been brought down, the output of the last command should be as follows:

Identify MySQL Data DirectoryStop MySQL Service

Stop MySQL Service

Then copy recursively the contents of /var/lib/mysql to /mnt/mysql-data preserving original permissions and timestamps:

- cp -R -p /var/lib/mysql/* /mnt/mysql-data
Identify MySQL Data DirectoryCopy MySQL Data Directory to New Location

Copy MySQL Data Directory to New Location

Step 3: Configure a New MySQL Data Directory

Edit the configuration file (my.cnf) to indicate the new data directory (/mnt/mysql-data in this case).

- vi /etc/my.conf
OR
- vi /etc/mysql/my.conf

Locate the [mysqld] and [client] sections and make the following changes:

Under [mysqld]:
datadir=/mnt/mysql-data
socket=/mnt/mysql-data/mysql.sock

Under [client]:
port=3306
socket=/mnt/mysql-data/mysql.sock

Save the changes and then proceed with the next step.

Identify MySQL Data DirectoryConfigure New MySQL Data Directory

Configure New MySQL Data Directory

Step 4: Set SELinux Security Context to Data Directory

This step is only applicable to RHEL/CentOS and its derivatives.

Add the SELinux security context to /mnt/mysql-data before restarting MariaDB.

- semanage fcontext -a -t mysqld_db_t "/mnt/mysql-data(/.*)?"
- restorecon -R /mnt/mysql-data

Next restart the MySQL service.

------------- On SystemD ------------- 
- systemctl stop mariadb
- systemctl is-active mariadb

------------- On SysVInit ------------- 
- service mysqld stop
- service mysqld status

OR

- service mysql stop
- service mysql status

Now, use the same command as in Step 1 to verify the location of the new data directory:

- mysql -u root -p -e "SELECT @@datadir;"
Identify MySQL Data DirectoryVerify MySQL New Data Directory

Verify MySQL New Data Directory

Step 5: Create MySQL Database to Confirm Data Directory

Login to MariaDB, create a new database, and then check /mnt/mysql-data:

- mysql -u root -p -e "CREATE DATABASE sfnews;"
Identify MySQL Data DirectoryCheck MySQL New Data Directory

Check MySQL New Data Directory

Congratulations! You have successfully changed the data directory for MySQL or MariaDB.

Summary

In this post we have discussed how to change the data directory in a MySQL or MariaDB server running on CentOS/RHEL 7 and Ubuntu/Debian distributions.

Do you have any questions or comments about this article? Feel free to let us know using the form below – we are always glad to hear from you!