MariaDB Crash Course - Ben Forta [11]
• The hostname (the name of the computer)—this is localhost if connecting to a local MariaDB server
• The port (if a port other than the default 3306 is used)
• A valid user name
• The user password (if required)
As explained in Chapter 2, “Introducing MariaDB,” all this information can be passed to the mysql command line utility, or entered into the server connection screen in MySQL Workbench.
* * *
Note: Using Other Clients
If you are using a client other than the ones mentioned here, you still need to provide this information to connect to MariaDB.
* * *
After you are connected, you have access to whatever databases and tables your login name has access to. (Logins, access control, and security are revisited in Chapter 28, “Managing Security.”)
Selecting a Database
When you first connect to MariaDB, you do not have any databases open for use. Before you can perform any database operations, you need to select a database. To do this you use the USE keyword.
* * *
New Term: Keyword
A reserved word that is part of the MariaDB SQL language. Never name a table or column using a keyword. Appendix D, “MariaDB Reserved Words,” lists the MariaDB keywords.
* * *
For example, to use the crashcourse database you would enter the following:
Input
USE crashcourse;
Output
Database changed
Analysis
The USE statement does not return any results. Depending on the client used, some form of notification might be displayed. For example, the Database changed message shown here is displayed by the mysql command line utility upon successful database selection.
* * *
Tip: Preselecting a Database
If you are using the mysql command line tool, you can preselect a database by typing its name after mysql when running the tool.
* * *
Remember, you must always USE a database before you can access any data in it.
Learning About Databases and Tables
But what if you don’t know the names of the available databases? And for that matter, how are clients like MySQL Workbench able to display a list of available databases?
Information about databases, tables, columns, users, privileges, and more is stored within databases and tables themselves (yes, MariaDB uses MariaDB to store this information). But these internal tables are generally not accessed directly. Instead, the MariaDB SHOW command can be used to display this information (information that MariaDB then extracts from those internal tables). Look at the following example:
Input
SHOW DATABASES;
Output
+--------------------+
| Database |
+--------------------+
| information_schema |
| crashcourse |
| mysql |
| forta |
| coldfusion |
| flex |
| test |
+--------------------+
Analysis
SHOW DATABASES; returns a list of available databases. Included in this list might be databases used by MariaDB internally (such as mysql and information_schema in this example). Of course, your own list of databases might not look like those shown here.
To obtain a list of tables within a database, use SHOW TABLES;, as seen here:
Input
SHOW TABLES;
Output
+-----------------------+
| Tables_in_crashcourse |
+-----------------------+
| customers |
| orderitems |
| orders |
| products |
| productnotes |
| vendors |
+-----------------------+
Analysis
SHOW TABLES; returns a list of available tables in the currently selected database.
To show a table’s columns, you can use DESCRIBE:
Input
DESCRIBE customers;
Output
+--------------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | | |
| cust_address | char(50) | YES | | NULL | |
| cust_city | char(50) | YES | | NULL | |
| cust_state | char(5) | YES | | NULL | |
| cust_zip | char(10) | YES | | NULL | |
| cust_country | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
| cust_email | char(255) | YES | | NULL | |
+--------------+-----------+------+-----+---------+----------------+