Note: This is an RHCE 7 exam objective.
Presentation of MariaDB Database Schema
A database schema consists of:
- a database name that groups all the objects together,
- one or several users with their associated access rights,
- a list of tables storing records,
- other objects like indexes, views, triggers, etc.
Note: A single MariaDB instance can host several databases.
Prerequisites
First, you need to install a MariaDB database.
Procedure
Then, you have to connect to the server with the password you created previously:
# mysql -u root -p Enter password:your passwordWelcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Then, you can create a database (here called test):
MariaDB [(none)]> create database test; Query OK, 1 row affected (0.00 sec)
Note: Use the drop database command to remove a database.
Give permissions to the user called user:
MariaDB [(none)]> grant all on test.* to user@localhost identified by 'your password'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec)
Note: There is a password associated with the root account required during the server installation process. Then, there is another password linked to the database owner (here user). It’s easier if both are the same but they don’t need to.
Exit the MariaDB command line:
MariaDB [(none)]> quit Bye
Now, you can connect to your own database directly:
# mysql -u user -p test Enter password:your passwordWelcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [test]>
To get the list of all the available databases, type:
MariaDB [test]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec)
Note: You can go inside a database with the use command. For example, type use test; to go to your new database.
To create a table called addresses, type:
MariaDB [test]> create table addresses(id int(10) unsigned, name varchar(20), address varchar(40)); Query OK, 0 rows affected (0.14 sec)
Note: Use the drop table command to remove a table.
To get the list of all the tables created in your database, type:
MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | addresses | +----------------+ 1 row in set (0.00 sec)
To get a description of a particular table (here addresses), type:
MariaDB [test]> desc addresses; +---------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | address | varchar(40) | YES | | NULL | | +---------+------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
To get the create table statement associated with the addresses table, type:
MariaDB [test]> show create table addresses; +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | addresses | CREATE TABLE `addresses` ( `id` int(10) unsigned DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `address` varchar(40) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Useful Tip
At the MariaDB prompt, you can get some information about the syntax by using the help command:
MariaDB [(none)]> help drop table Name: 'DROP TABLE' Description: Syntax: DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] DROP TABLE removes one or more tables. You must have the DROP privilege for each table. All table data and the table definition are removed, so be careful with this statement! If any of the tables named in the argument list do not exist, MySQL returns an error indicating by name which nonexisting tables it was unable to drop, but it also drops all of the tables in the list that do exist. ...
Additional Resources
You can also watch Andrew Mallett‘s video about Creating a MariaDB Database (6min/2015).
After installing group mariadb and mariadb-client and updating man with mandb and check documentation on /usr/share/docs/ I cannot find anyplace with SQL syntax examples.
Any advice where to find it during the exam some examples just in case I don’t remember some details of the syntax?
You are perfectly right. I haven’t seen any such example and I don’t know where to find!
Sorry.
The simplest way to get the syntax is well the simplest, simply type help at the MariaDB or MySql command line.
The syntax is not pretty but with a little practice easy to used.
Example
MariaDB : help table;
Note: This is done from memory! It is easy to get confused using this method!
I wasn’t aware of this feature.
Thank you for your useful comment.
Thanks Sam, I was not aware neither. This is great help, because apart from the syntax, some commands have even examples.
MariaDB [inventory]> help grant;
Name: ‘GRANT’
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] …
ON [object_type] priv_level
TO user_specification [, user_specification] …
[REQUIRE {NONE | ssl_option [[AND] ssl_option] …}]
[WITH with_option …]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] …
[WITH GRANT OPTION]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
user_specification:
user
[
IDENTIFIED BY [PASSWORD] ‘password’
| IDENTIFIED WITH auth_plugin [AS ‘auth_string’]
]
ssl_option:
SSL
| X509
| CIPHER ‘cipher’
| ISSUER ‘issuer’
| SUBJECT ‘subject’
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
The GRANT statement grants privileges to MySQL user accounts. GRANT
also serves to specify other account characteristics such as use of
secure connections and limits on access to server resources. To use
GRANT, you must have the GRANT OPTION privilege, and you must have the
privileges that you are granting.
Normally, a database administrator first uses CREATE USER to create an
account, then GRANT to define its privileges and characteristics. For
example:
CREATE USER ‘jeffrey’@’localhost’ IDENTIFIED BY ‘mypass’;
GRANT ALL ON db1.* TO ‘jeffrey’@’localhost’;
GRANT SELECT ON db2.invoice TO ‘jeffrey’@’localhost’;
GRANT USAGE ON *.* TO ‘jeffrey’@’localhost’ WITH MAX_QUERIES_PER_HOUR 90;
However, if an account named in a GRANT statement does not already
exist, GRANT may create it under the conditions described later in the
discussion of the NO_AUTO_CREATE_USER SQL mode.
The REVOKE statement is related to GRANT and enables administrators to
remove account privileges. See [HELP REVOKE].
When successfully executed from the mysql program, GRANT responds with
Query OK, 0 rows affected. To determine what privileges result from the
operation, use SHOW GRANTS. See [HELP SHOW GRANTS].
URL: http://dev.mysql.com/doc/refman/5.5/en/grant.html
Very interesting. Thanks.
Guys, you are doing a tremendous work here. Thanks
Thanks.
I am facing a issue.
===============================================================
MariaDB [(none)]> use tom;
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
MariaDB [tom]> show tables;
+—————+
| Tables_in_tom |
+—————+
| addresses |
+—————+
1 row in set (0.00 sec)
MariaDB [tom]> show
->
-> clear
-> DON’T KNOW HOW TO GO BACK TO MariaDB [tom]> ……menu.
‘> help!!!!
================================================================
Whenever I make any syntax mistake in MariaDB prompt, it takes me in ” -> ” prompt and I become unable to come out.
One option that I try is to exit MariaDB prompt by pressing “ctrl+c” but it’s time consuming.
I can’t find a solution in the article either.
Pls help, if there is any other way to get out of this mess.
All commands must finish by a ‘;’.
I’ve been seeking some further sources for mariadb to help me get more comfortable with the syntax.
I have found: A document in the /usr/share/doc/mariadb area (can’t find exact location) which confirms that the mysql manual couldn’t be distributed with mariadb for licencing reasons,
but more on the bright side, another way to get a little help with syntax: the command “show create table tablename” will show you the exact command that would create one of the tables that is already present.
That should hopefully be enough to get someone started 🙂 )
https://www.safaribooksonline.com/library/view/learning-mysql-and/9781449362898/ch04.html
Interesting. Thanks.
For some help with SQL syntax, there are some example sql files installed by default under /usr/share/mysql which show basic syntax for create, insert drop and select statements.
Interesting. Thanks.