MariaDB Crash Course - Ben Forta [90]
• create.sql contains the MariaDB SQL statements to create the six database tables (including defining all primary keys and foreign key constraints).
• populate.sql contains the SQL INSERT statements used to populate these tables.
* * *
Note: For MariaDB Only
The SQL statements in the downloadable .sql files are DBMS specific and are designed to be used only with MariaDB.
* * *
After you have downloaded the scripts, you can use them to create and populate the tables needed to follow along with the chapters in this book. You can do this using the mysql command line utility or MySQL Workbench.
* * *
Note: Create, Then Populate
You must run the table creation scripts before the table population scripts. Be sure to check for any error messages returned by these scripts. If the creation scripts fail, you need to remedy whatever problem might exist before continuing with table population.
* * *
* * *
Caution: One Or the Other, Not Both
Both of the following sets of instructions do the exact same thing, so pick one and use it, but don’t try to use both. (You’ll not be able to create the same database and tables twice.)
* * *
Using mysql
To create the example data using the mysql command line utility, do the following:
1. Make sure MariaDB is running.
2. Open a command prompt window, and go to the bin folder under the MariaDB installation folder.
3. Connect to MariaDB as the root user (so that you have the security access needed to create a new database). Type mysql –u root and press Enter. If you specified a root password at installation time, use mysql –u root –p and press Enter, and then type the password when prompted to do so.
4. You should see a prompt like MariaDB [(none)]>. The name of the currently selected database is displayed inside the square brackets, and (none) simply means that no database has been selected.
5. To keep the tables used in this book separate from any other work or data, we create a new database and use that exclusively for all chapters. Type create database crashcourse; (don’t forget the ;) and press Enter to create a new database named crashcourse. You should see a message saying OK.
6. Next you need to select the new database (so that when you create the tables they are created inside it). Type USE crashcourse; and press Enter. The prompt should now indicate that the crashcourse database has been selected.
7. To create the tables, you need to run the create.sql script. Make sure you know the full path to the file and type \. /path/create.sql and press Enter, replacing path with the actual path. So, if create.sql is in /downloads/, type \. /downloads/create.sql. You should see a series of OK messages.
* * *
Caution: No ; When Using \.
Unlike the CREATE and USE statements (and indeed just about every MariaDB SQL statement), do not type a trailing ; when using the \. command to execute an external script file.
* * *
8. Repeat step 7, this time using the populate.sql script. This populates the newly created tables with the sample data. Again, you should see a series of OK messages indicating success.
9. When you are done, type exit or quit to exit mysql.
You can now return to Chapter 3, “Working with MariaDB.”
Using MySQL Workbench
To create the example data using MySQL Workbench, do the following:
1. Make sure MariaDB is running.
2. Launch MySQL Workbench.
3. Click on Open Connection to Start Querying (it’s the top option in the left column) to display the Connect to Database dialog.
4. Make sure the hostname is correct, the user name should be root, and you should enter the root password if one was specified at installation time. Click OK and you should see the SQL Editor window. Existing databases are listed in the Overview tab at the top of the lower half of the screen.
5. To keep the tables used in this book separate from any other work or data, we create a new database and use that exclusively for all chapters. Click the + button on the right above the listed databases to display the new_schema dialog (schema is another