MariaDB Crash Course - Ben Forta [91]
6. Next you need to select the new database (so that when you create the tables they are created inside it). Type USE crashcourse; at the top of the screen and click the Execute button (the one with the yellow lightning bolt). The output tab indicates success or failure with an icon for each statement—red X for failure, blue exclamation mark for success.
7. To create the tables, you need to run the create.sql script. Select File, Open SQL Script, and locate the create.sql file. When you see the contents in the editor window, click the Execute button. The output tab below indicates success or failure with an icon for each statement (multiple statements will be executed).
8. Repeat step 7, this time using the populate.sql script. This populates the newly created tables with the sample data. Again, the output tab below indicates success or failure with an icon for each statement.
9. When you are done, you can quit MySQL Workbench.
You can now return to Chapter 3.
Appendix C. MariaDB Datatypes
As explained in Chapter 1, “Understanding SQL,” datatypes are basically rules that define what data may be stored in a column and how that data is actually stored.
Datatypes are used for several reasons:
• Datatypes enable you to restrict the type of data that can be stored in a column. For example, a numeric datatype column only accepts numeric values.
• Datatypes allow for more efficient storage, internally. Numbers and date time values can be stored in a more condensed format than text strings.
• Datatypes allow for alternate sorting orders. If everything is treated as strings, 1 comes before 10, which comes before 2. (Strings are sorted in dictionary sequence, one character at a time starting from the left.) As numeric datatypes, the numbers would be sorted correctly.
When designing tables, pay careful attention to the datatypes being used. Using the wrong datatype can seriously impact your application. Changing the datatypes of existing populated columns is not a trivial task. (In addition, doing so can result in data loss.)
Although this appendix is by no means a complete tutorial on datatypes and how they are to be used, it explains the major MariaDB datatype types, and what they are used for.
String Datatypes
The most commonly used datatypes are string datatypes. These store strings: for example, names, addresses, phone numbers, and Zip Codes. As listed in Table D.1, there are basically two types of string datatype that you can use—fixed-length strings and variable-length strings.
Table D.1 String Datatypes
Fixed-length strings are datatypes that are defined to accept a fixed number of characters, and that number is specified when the table is created. For example, you might allow 30 characters in a first-name column or 11 characters in a Social-Security-number column (the exact number needed allowing for the two dashes). Fixed-length columns do not allow more than the specified number of characters. They also allocate storage space for as many characters as specified. So, if the string Ben is stored in a 30-character first-name field, a full 30 bytes are stored. CHAR is an example of a fixed-length string type.
Variable-length strings store text of variable length. Some variable-length datatypes have a defined maximum size. Others are entirely variable. Either way, only the data specified is saved (and no extra data is stored). TEXT is an example of a variable-length string type.
If variable-length datatypes are so flexible, why would you ever want to use fixed-length datatypes? The answer is performance. MariaDB can sort and manipulate fixed-length columns far more quickly than it can sort variable-length columns. In addition, MariaDB does not allow you to index variable-length columns (or the variable portion of a column). This also dramatically affects performance.