MariaDB Crash Course - Ben Forta [92]
* * *
Tip: Using Quotes
Regardless of the form of string datatype being used, string values must always be surrounded by quotes (single quotes are often preferred).
* * *
* * *
Caution: When Numeric Values Are Not Numeric Values
You might think that phone numbers and Zip Codes should be stored in numeric fields (after all, they store only numeric data), but doing so would not be advisable. If you store the Zip Code 01234 in a numeric field, the number 1234 would be saved. You’d actually lose a digit.
The basic rule to follow is: If the number is a number used in calculations (sums, averages, and so on), it belongs in a numeric datatype column. If it is used as a literal string (that happens to contain only digits), it belongs in a string datatype column.
* * *
Numeric Datatypes
Numeric datatypes store numbers. MariaDB supports several numeric datatypes, each with a different range of numbers that can be stored in it. Obviously, the larger the supported range, the more storage space needed. In addition, some numeric datatypes support the use of decimal points (and fractional numbers), whereas others support only whole numbers. Table D.2 lists the frequently used MariaDB numeric datatypes.
Table D.2 Numeric Datatypes
* * *
Note: Signed Or UNSIGNED?
All numeric datatypes (with the exception of BIT and BOOLEAN) can be signed or unsigned. Signed numeric columns can store both positive and negative numbers; unsigned numeric columns store only positive numbers. Signed is the default, but if you know that you’ll not need to store negative values you can use the UNSIGNED keyword. Doing so allows you to store values twice as large.
* * *
* * *
Tip: Not Using Quotes
Unlike strings, numeric values should never be enclosed within quotes.
* * *
* * *
Tip: Storing Currency
There is no special MariaDB datatype for currency values, use DECIMAL(8,2) instead.
* * *
Date and Time Datatypes
MariaDB uses special datatypes for the storage of date and time values as listed in Table D.3.
Table D.3 Date and Time Datatypes
Binary Datatypes
Binary datatypes are used to store all sorts of data (even binary information), such as graphic images, multimedia, and word processor documents (see Table D.4).
Table D.4 Binary Datatypes
* * *
Note: Datatypes in Use
If you want to see a real-world example of how different databases are used, see the sample table creation scripts (described in Appendix B, “The Example Tables”).
* * *
Appendix D. MariaDB Reserved Words
The MariaDB implementation of SQL is made up of keywords—special words used in performing SQL operations. Special care must be taken to not use these keywords when naming databases, tables, columns, and any other database objects. Thus, these keywords are considered reserved. This appendix lists all of the MariaDB reserved words.
Index
Symbols
* (asterisk), 30
\ (backslash), 74
/* */ comment syntax, 36
% (percent sign) wildcard, 62-63
# (pound sign), 36
; (semicolon), 28
--(two hyphens), 35
_ (underscore) wildcard, 64
A
Abs() function, 96
access control, 235-236
access rights, 238-241
accounts. See user accounts
advantages of MySQL, 13-14
Against() function, 149-152
aggregate functions
ALL argument, 103
AVG(), 98-99
combining, 104-105
COUNT(), 99-100
defined, 97
DISTINCT argument, 103-104
explained, 97
joins and, 139-140
MAX(), 100-101
MIN(), 101-102
naming aliases, 105
SUM(), 102-103
aliases, 84-85, 133-134
ALL argument, 103
alphabetical sort order, 40-43
ALTER TABLE statement, 183-185
ANALYZE TABLE statement, 244
anchor metacharacters, 77
anchors, 77-79
AND keyword, 50
AND operator, 53-54
application filtering, 46
AS keyword, 84-85
asterisk (*), 30
auto increment, 25
AUTO_INCREMENT, 180-181
AVG() function, 98-99
B
backing up data, 243
backslash (\), 74
BACKUP TABLE statement, 243
BETWEEN operator (WHERE clause), 49
BINARY datatype, 266-267
BIT datatype, 265
boolean text searches, 154-158
C
calculated fields
aliases, 84-85
concatenating