{"id":11562,"date":"2019-03-14T12:39:16","date_gmt":"2019-03-14T12:39:16","guid":{"rendered":"http:\/\/www.appservgrid.com\/paw92\/?p=11562"},"modified":"2019-03-14T12:39:16","modified_gmt":"2019-03-14T12:39:16","slug":"learn-mysql-mariadb-for-beginners-part-1","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/03\/14\/learn-mysql-mariadb-for-beginners-part-1\/","title":{"rendered":"Learn MySQL \/ MariaDB for Beginners \u2013 Part 1"},"content":{"rendered":"

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\u00a0Data Manipulation Language<\/strong>\u00a0(DML<\/strong>) operations with data on a\u00a0MySQL<\/strong>\u00a0\/\u00a0MariaDB<\/strong>\u00a0server.<\/p>\n

It is assumed that you have previously\u00a01)<\/strong>\u00a0installed the necessary packages on your Linux system, and\u00a02)<\/strong>executed\u00a0mysql_secure_installation<\/strong>\u00a0to improve the database server\u2019s security. If not, follow below guides to install MySQL\/MariaDB server.<\/p>\n

    \n
  1. Install Latest MySQL Database in Linux Systems<\/a><\/li>\n
  2. Install Latest MariaDB Database in Linux Systems<\/a><\/li>\n<\/ol>\n

    For brevity, we will refer to\u00a0MariaDB<\/strong>\u00a0exclusively throughout this article, but the concepts and commands outlined here apply to\u00a0MySQL<\/strong>\u00a0as well.<\/p>\n

    Part 1<\/b>:\u00a0Learn MySQL \/ MariaDB for Beginners<\/b><\/div>\n
    Part 2<\/b>:\u00a0Learn How to Use Several Functions of MySQL and MariaDB<\/a><\/div>\n

    Creating Databases, Tables, and Authorized Users<\/h3>\n

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

    To store persistent information in a database, we will use\u00a0tables<\/strong>\u00a0that 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.<\/center><\/p>\n

    Creating a New Database<\/h4>\n

    To create a new database named\u00a0BooksDB<\/code>, enter the MariaDB prompt with the following command (you\u2019ll be prompted to enter the password for the root MariaDB user):<\/p>\n

    [root@TecMint ~]# 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)]> CREATE DATABASE BookstoreDB;<\/strong>\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nMariaDB [(none)]> \r\n<\/pre>\n
    Once the database has been created, we need to create at least two tables on it. But first let\u2019s explore the concept of data types.<\/p>\n
    Introducing MariaDB data Types<\/h3>\n
    As we explained earlier,\u00a0tables<\/strong>\u00a0are database objects where we will keep persistent information. Each table consists of two or more fields (also known as\u00a0columns<\/strong>) of a given data type (the type of information) that such field can store.<\/p>\n
    The most common data types in MariaDB are the following (you can consult the complete list in the\u00a0official MariaDB online documentation<\/a>):<\/p>\n
    Numeric:<\/h5>\n
      \n
    1. BOOLEAN<\/strong>\u00a0considers 0 as false and any other values as true.<\/li>\n
    2. TINYINT<\/strong>, if used with SIGNED, covers the range from -128 to 127, whereas the UNSIGNED range is 0 to 255.<\/li>\n
    3. SMALLINT<\/strong>, if used with SIGNED, covers the range from -32768 to 32767. The UNSIGNED range is 0 to 65535.<\/li>\n
    4. INT<\/strong>, if used with UNSIGNED, covers the range from 0 to 4294967295, and -2147483648 to 2147483647 otherwise.<\/li>\n<\/ol>\n
      Note<\/strong>: In TINYINT, SMALLINT, and INT, the default SIGNED is assumed.<\/p>\n
      DOUBLE(M, D)<\/strong>, where\u00a0M<\/strong>\u00a0is the total number of digits and\u00a0D<\/strong>\u00a0is 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.<\/p>\n
      String:<\/h5>\n
        \n
      1. VARCHAR(M)<\/strong>\u00a0represents a string of variable length where\u00a0M<\/strong>\u00a0is 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 \u00f1 represents one character but takes up 2 bytes.<\/li>\n
      2. TEXT(M)<\/strong>\u00a0represents a column with a maximum length of 65,535 characters. However, as it happens with\u00a0VARCHAR(M)<\/strong>, the actual maximum length is reduced if multi-byte characters are stored. If\u00a0M<\/strong>\u00a0is specified, the column is created as the smallest type that can store such number of characters.<\/li>\n
      3. MEDIUMTEXT(M)<\/strong>\u00a0and\u00a0LONGTEXT(M)<\/strong>\u00a0are similar to\u00a0TEXT(M)<\/strong>, only that the maximum allowed lengths are 16,777,215 and 4,294,967,295 characters, respectively.<\/li>\n<\/ol>\n
        Date and Time:<\/h5>\n
          \n
        1. DATE<\/strong>\u00a0represents the date in\u00a0YYYY-MM-DD<\/strong>\u00a0format.<\/li>\n
        2. TIME<\/strong>\u00a0represents the time in\u00a0HH:MM:SS.sss<\/strong>\u00a0format (hour, minutes, seconds, and milliseconds).<\/li>\n
        3. DATETIME<\/strong>\u00a0is the combination of\u00a0DATE<\/strong>\u00a0and\u00a0TIME<\/strong>\u00a0in\u00a0YYYY-MM-DD HH:MM:SS<\/strong>\u00a0format.<\/li>\n
        4. TIMESTAMP<\/strong>\u00a0is used to define the moment a row was added or updated.<\/li>\n<\/ol>\n
          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.<\/p>\n
          For example, a person\u2019s name can easily fit into a\u00a0VARCHAR(50)<\/strong>, whereas a blog post will need a\u00a0TEXT<\/strong>\u00a0type (choose\u00a0M<\/strong>\u00a0as per your specific needs).<\/p>\n
          Creating Tables with Primary and Foreign Keys<\/h4>\n
          Before we dive into creating tables, there are two fundamental concepts about relational databases that we need to review:\u00a0primary<\/strong>\u00a0and\u00a0foreign<\/strong>\u00a0keys.<\/p>\n
          A\u00a0primary key<\/strong>\u00a0contains a value that uniquely identifies each row, or record, in the table. On the other hand, a\u00a0foreign key<\/strong>\u00a0is 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.<\/p>\n
          To illustrate, let\u2019s use the\u00a0BookstoreDB<\/code>\u00a0and create two tables named\u00a0AuthorsTBL<\/code>\u00a0and\u00a0BooksTBL<\/code>\u00a0as follows. The\u00a0NOT NULL<\/strong>\u00a0constraint indicates that the associated field requires a value other than\u00a0NULL<\/strong>.<\/p>\n
          Also,\u00a0AUTO_INCREMENT<\/strong>\u00a0is used to increase by one the value of\u00a0INT<\/strong>\u00a0primary key columns when a new record is inserted into the table.<\/p>\n
          MariaDB [(none)]><\/strong> USE BookstoreDB;\r\n\r\nMariaDB [(none)]><\/strong> CREATE TABLE AuthorsTBL (\r\nAuthorID INT NOT NULL AUTO_INCREMENT,\r\nAuthorName VARCHAR(100),\r\nPRIMARY KEY(AuthorID)\r\n);\r\n\r\nMariaDB [(none)]><\/strong> CREATE TABLE BooksTBL (\r\nBookID INT NOT NULL AUTO_INCREMENT,\r\nBookName VARCHAR(100) NOT NULL,\r\nAuthorID INT NOT NULL,\r\nBookPrice DECIMAL(6,2) NOT NULL,\r\nBookLastUpdated TIMESTAMP,\r\nBookIsAvailable BOOLEAN,\r\nPRIMARY KEY(BookID),\r\nFOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)\r\n);\r\n<\/pre>\n
          Create MySQL Tables with Primary and Foreign Key<\/div>\n
          MariaDB [(none)]> USE BookstoreDB;<\/strong>\r\nDatabase changed\r\nMariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (\r\n    -> AuthorID INT NOT NULL AUTO_INCREMENT,\r\n    -> AuthorName VARCHAR(100),\r\n    -> PRIMARY KEY(AuthorID)\r\n    -> );<\/strong>\r\nQuery OK, 0 rows affected (0.05 sec)\r\n\r\nMariaDB [BookstoreDB]> CREATE TABLE BooksTBL (\r\n    -> BookID INT NOT NULL AUTO_INCREMENT,\r\n    -> BookName VARCHAR(100) NOT NULL,\r\n    -> AuthorID INT NOT NULL,\r\n    -> BookPrice DECIMAL(6,2) NOT NULL,\r\n    -> BookLastUpdated TIMESTAMP,\r\n    -> BookIsAvailable BOOLEAN,\r\n    -> PRIMARY KEY(BookID),\r\n    -> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)\r\n    -> );<\/strong>\r\nQuery OK, 0 rows affected (0.05 sec)\r\n\r\nMariaDB [BookstoreDB]> \r\n<\/pre>\n
          Now we can go ahead and start inserting records into\u00a0AuthorsTBL<\/code>\u00a0and\u00a0BooksTBL<\/code>.<\/p>\n
          Selecting, Inserting, Updating, and Deleting Rows<\/h4>\n
          We will first populate the\u00a0AuthorsTBL<\/code>\u00a0table. Why? Because we need to have values for\u00a0AuthorID<\/code>\u00a0before inserting records into the\u00a0BooksTBL<\/strong>.<\/p>\n
          Execute the following query from your MariaDB prompt:<\/p>\n
          MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');\r\n<\/pre>\n
          After that, we will select all records from\u00a0AuthorsTBL<\/strong>. Remember we will need the\u00a0AuthorID<\/strong>\u00a0for each record to create the\u00a0INSERT<\/strong>\u00a0query for\u00a0BooksTBL<\/strong>.<\/p>\n
          If you want to retrieve one record at a time, you can use a\u00a0WHERE<\/strong>\u00a0clause to indicate a condition that a row must meet to be returned. For example,<\/p>\n
          MariaDB [BookstoreDB]><\/strong> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';\r\n<\/pre>\n
          Alternatively, you can select all the records simultaneously:<\/p>\n
          MariaDB [BookstoreDB]><\/strong> SELECT * FROM AuthorsTBL;\r\n<\/pre>\n
          Select and Query Record in MySQL<\/div>\n
          MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';<\/strong>\r\n+----------+-----------------+\r\n| AuthorID | AuthorName      |\r\n+----------+-----------------+\r\n|        1 | Agatha Christie |\r\n+----------+-----------------+\r\n1 row in set (0.00 sec)\r\n\r\nMariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;<\/strong>\r\n+----------+-----------------+\r\n| AuthorID | AuthorName      |\r\n+----------+-----------------+\r\n|        1 | Agatha Christie |\r\n|        2 | Stephen King    |\r\n|        3 | Paulo Coelho    |\r\n+----------+-----------------+\r\n3 rows in set (0.00 sec)\r\n\r\nMariaDB [BookstoreDB]>\r\n<\/pre>\n
          \n
          \"Select<\/a><\/p>\n
          Select and Query a String in MySQL Database<\/p>\n<\/div>\n
          Now let\u2019s create the\u00a0INSERT<\/strong>\u00a0query for\u00a0BooksTBL<\/strong>, using the corresponding\u00a0AuthorID<\/strong>\u00a0to match the author of each book. A value of\u00a01<\/strong>\u00a0in\u00a0BookIsAvailable<\/strong>\u00a0indicates the book is in stock,\u00a00<\/strong>\u00a0otherwise:<\/p>\n
          MariaDB [BookstoreDB]><\/strong> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)\r\nVALUES ('And Then There Were None', 1, 14.95, 1),\r\n('The Man in the Brown Suit', 1, 23.99, 1),\r\n('The Stand', 2, 35.99, 1),\r\n('Pet Sematary', 2, 17.95, 0),\r\n('The Green Mile', 2, 29.99, 1),\r\n('The Alchemist', 3, 25, 1),\r\n('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);\r\n<\/pre>\n
          Insert Query in MySQL Table<\/div>\n
          MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)\r\n    -> VALUES ('And Then There Were None', 1, 14.95, 1),\r\n    -> ('The Man in the Brown Suit', 1, 23.99, 1),\r\n    -> ('The Stand', 2, 35.99, 1),\r\n    -> ('Pet Sematary', 2, 17.95, 0),\r\n    -> ('The Green Mile', 2, 29.99, 1),\r\n    -> ('The Alchemist', 3, 25, 1),\r\n    -> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);<\/strong>\r\nQuery OK, 7 rows affected (0.03 sec)\r\nRecords: 7  Duplicates: 0  Warnings: 0\r\n<\/pre>\n
          At this point we will do a\u00a0SELECT<\/strong>\u00a0to see the records in\u00a0BooksTBL<\/strong>. Then let\u2019s\u00a0UPDATE<\/strong>\u00a0the price of \u201cThe Alchemist<\/strong>\u201d by\u00a0Paulo Coelho<\/strong>\u00a0and\u00a0SELECT<\/strong>\u00a0that specific record again.<\/p>\n
          Note how the\u00a0BookLastUpdated<\/strong>\u00a0field now shows a different value. As we explained earlier, a\u00a0TIMESTAMP<\/strong>\u00a0field shows the value when the record was inserted or last modified.<\/p>\n
          MariaDB [BookstoreDB]><\/strong> SELECT * FROM BooksTBL;\r\nMariaDB [BookstoreDB]><\/strong> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;\r\nMariaDB [BookstoreDB]><\/strong> SELECT * FROM BooksTBL WHERE BookID=6;\r\n<\/pre>\n
          Insert Query and update Table in MySQL Database<\/div>\n
          MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;<\/strong>\r\n+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+\r\n| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |\r\n+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+\r\n|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |\r\n|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |\r\n|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |\r\n|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |\r\n|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |\r\n|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41<\/strong> |               1 |\r\n|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |\r\n+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+\r\n7 rows in set (0.00 sec)\r\n\r\nMariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;<\/strong>\r\nQuery OK, 1 row affected (0.04 sec)\r\nRows matched: 1  Changed: 1  Warnings: 0\r\n\r\nMariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;<\/strong>\r\n+--------+---------------+----------+-----------+---------------------+-----------------+\r\n| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |\r\n+--------+---------------+----------+-----------+---------------------+-----------------+\r\n|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |\r\n+--------+---------------+----------+-----------+---------------------+-----------------+\r\n1 row in set (0.00 sec)\r\n\r\nMariaDB [BookstoreDB]> \r\n<\/pre>\n
          \n
          \"Insert<\/a><\/p>\n
          Insert and Update Database Table Record<\/p>\n<\/div>\n
          Although we won\u2019t do it here, you can also delete a record if not used anymore. For example, suppose we want to delete \u201cThe Alchemist<\/strong>\u201d from\u00a0BooksTBL<\/strong>.<\/p>\n
          To do so, we will use the\u00a0DELETE<\/strong>\u00a0statement as follows:<\/p>\n
          MariaDB [BookstoreDB]><\/strong> DELETE FROM BooksTBL WHERE BookID=6;\r\n<\/pre>\n
          As in the case of\u00a0UPDATE<\/strong>, it is a good idea to do a\u00a0SELECT<\/strong>\u00a0first in order to view the record(s) that may potentially be impacted by the\u00a0DELETE<\/strong>.<\/p>\n
          Also, don\u2019t forget to add the\u00a0WHERE<\/strong>\u00a0clause and a condition\u00a0(BookID=6)<\/strong>\u00a0to select the specific record to be removed. Otherwise, you run the risk of deleting all the rows in the table!<\/p>\n
          If you desire to concatenate two (or more) fields, you can use the\u00a0CONCAT<\/strong>\u00a0statement. For example, let\u2019s say we want to return a result set that consist of one field with the book name and author in the form of \u201cThe Alchemist (Paulo Coelho)<\/strong>\u201d and another column with the price.<\/p>\n
          This will require a\u00a0JOIN<\/strong>\u00a0between\u00a0AuthorsTBL<\/strong>\u00a0and\u00a0BooksTBL<\/strong>\u00a0on the common field shared by both tables (AuthorID<\/strong>):<\/p>\n
          MariaDB [BookstoreDB]><\/strong> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;\r\n<\/pre>\n
          As we can see,\u00a0CONCAT<\/strong>\u00a0allows us to join multiple string expressions separated by commas. You\u2019ll also noticed that we chose the alias\u00a0Description<\/strong>\u00a0to represent the result set of the concatenation.<\/p>\n
          The output of the above query is shown in the below image:<\/p>\n
          Query Multiple Fields in MySQL Table<\/div>\n
          MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;<\/strong>\r\n+--------------------------------------------------------+-----------+\r\n| Description                                            | BookPrice |\r\n+--------------------------------------------------------+-----------+\r\n| And Then There Were None (Agatha Christie)             |     14.95 |\r\n| The Man in the Brown Suit (Agatha Christie)            |     23.99 |\r\n| The Stand (Stephen King)                               |     35.99 |\r\n| Pet Sematary (Stephen King)                            |     17.95 |\r\n| The Green Mile (Stephen King)                          |     29.99 |\r\n| The Alchemist (Paulo Coelho)                           |     25.00 |\r\n| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |\r\n+--------------------------------------------------------+-----------+\r\n7 rows in set (0.00 sec)\r\n<\/pre>\n
          \n
          \"Query<\/a><\/p>\n
          Query Multiple Fields in Database Table<\/p>\n<\/div>\n
          Create User to Access the BookstoreDB Database<\/h4>\n
          Using\u00a0root<\/strong>\u00a0to perform all\u00a0DML<\/strong>\u00a0operations in a database is a bad idea. To avoid this, we can create a new\u00a0MariaDB<\/strong>\u00a0user account (we\u2019ll name it\u00a0bookstoreuser<\/strong>) and assign all necessary permissions for\u00a0BookstoreDB<\/strong>:<\/p>\n
          MariaDB [BookstoreDB]><\/strong> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';\r\nMariaDB [BookstoreDB]><\/strong> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;\r\nMariaDB [BookstoreDB]><\/strong> FLUSH PRIVILEGES;\r\n<\/pre>\n
          Create New Database User with Privileges<\/div>\n
          MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';<\/strong>\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nMariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;<\/strong>\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nMariaDB [BookstoreDB]> FLUSH PRIVILEGES;<\/strong>\r\nQuery OK, 0 rows affected (0.00 sec)\r\n<\/pre>\n
          Having a dedicated, separate user for each database will prevent damages to the entire database should a single account become compromised.<\/p>\n
          Extra MySQL Tips<\/h4>\n
          To clear up the\u00a0MariaDB<\/strong>\u00a0prompt, type the following command and press\u00a0Enter<\/strong>:<\/p>\n
          MariaDB [BookstoreDB]><\/strong> \\! clear\r\n<\/pre>\n
          To inspect the configuration of a given table, do:<\/p>\n
          MariaDB [BookstoreDB]><\/strong> SELECT COLUMNS IN [TABLE NAME HERE ];\r\n<\/pre>\n
          For example,<\/p>\n
          MariaDB [BookstoreDB]><\/strong> SHOW COLUMNS IN BooksTBL;\r\n<\/pre>\n
          List Columns in Database Table<\/div>\n
          MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;<\/strong>\r\n+-----------------+--------------+------+-----+-------------------+-----------------------------+\r\n| Field           | Type         | Null | Key | Default           | Extra                       |\r\n+-----------------+--------------+------+-----+-------------------+-----------------------------+\r\n| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |\r\n| BookName        | varchar(100) | NO   |     | NULL              |                             |\r\n| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |\r\n| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |\r\n| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |\r\n| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |\r\n+-----------------+--------------+------+-----+-------------------+-----------------------------+\r\n6 rows in set (0.02 sec)\r\n<\/pre>\n
          \n
          \"List<\/a><\/p>\n
          List Columns in Database Table<\/p>\n<\/div>\n
          A quick inspection reveals that the\u00a0BookIsAvailable<\/strong>\u00a0field admits\u00a0NULL<\/strong>\u00a0values. Since we don\u2019t want to allow that, we\u2019ll\u00a0ALTER<\/strong>\u00a0the table as follows:<\/p>\n
          MariaDB [BookstoreDB]><\/strong> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;\r\n<\/pre>\n
          (Feel free to show the columns again \u2013 the highlighted\u00a0YES<\/strong>\u00a0in the above image should now be a\u00a0NO<\/strong>).<\/p>\n
          Finally, to view all the databases on your server, do:<\/p>\n
          MariaDB [BookstoreDB]><\/strong> SHOW DATABASES;\r\nOR\r\nMariaDB [BookstoreDB]><\/strong> SHOW SCHEMAS;\r\n<\/pre>\n
          List All MySQL Databases<\/div>\n
          [root@TecMint ~]# mysql -u bookstoreuser -p<\/strong>\r\nEnter password: \r\nWelcome to the MariaDB monitor.  Commands end with ; or \\g.\r\nYour MariaDB connection id is 3\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 [BookstoreDB]> SHOW DATABASES;<\/strong>\r\n+--------------------+\r\n| Database           |\r\n+--------------------+\r\n| BookstoreDB        |\r\n| information_schema |\r\n+--------------------+\r\n2 rows in set (0.00 sec)\r\n\r\nMariaDB [BookstoreDB]> SHOW SCHEMAS;<\/strong>\r\n+--------------------+\r\n| Database           |\r\n+--------------------+\r\n| BookstoreDB        |\r\n| information_schema |\r\n+--------------------+\r\n2 rows in set (0.00 sec)\r\n<\/pre>\n
          The following image shows the result of the above command after accessing the MariaDB prompt as the\u00a0bookstoreuser<\/strong>\u00a0(note how this account can\u2019t \u201csee\u201d any databases other than\u00a0BookstoreDB<\/strong>\u00a0and\u00a0information_schema<\/strong>\u00a0(available for all users):<\/p>\n
          \n
          \"List<\/a><\/p>\n
          List All MySQL Databases<\/p>\n<\/div>\n
          Summary<\/h3>\n
          In this article we have explained how to run\u00a0DML<\/strong>\u00a0operations 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.<\/p>\n
            \n
          1. MySQL Database Administration Part \u2013 1<\/a><\/li>\n
          2. MySQL Database Administration Part \u2013 2<\/a><\/li>\n
          3. MySQL Performance Tunning and Optimization \u2013 Part 3<\/a><\/li>\n<\/ol>\n
            If you have any questions about this article, don\u2019t hesitate to let us know! Feel free to use the comment form below to reach us.<\/p>\n
            Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
            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\u00a0Data Manipulation Language\u00a0(DML) operations with data on a\u00a0MySQL\u00a0\/\u00a0MariaDB\u00a0server. It is assumed that you have previously\u00a01)\u00a0installed the necessary packages on your Linux system, and\u00a02)executed\u00a0mysql_secure_installation\u00a0to improve the database server\u2019s security. If not, follow … <\/p>\n
            Continue reading “Learn MySQL \/ MariaDB for Beginners \u2013 Part 1”<\/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-11562","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\/11562","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=11562"}],"version-history":[{"count":1,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11562\/revisions"}],"predecessor-version":[{"id":11563,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11562\/revisions\/11563"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=11562"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=11562"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=11562"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}