MariaDB Crash Course - Ben Forta [95]
logical operators. See operators
login names, 21-22
log-slow-queries command line option, 246
Lower() function, 91
LTrim() function, 84, 91
M
maintenance (database)
backing up data, 243
diagnosing startup problems, 245
performing, 243-244
reviewing log files, 245-246
MariaDB
downloading, 252
installing, 252
requirements, 251
Match() function, 149-152
matching. See character matching
mathematical calculations, 85-86
mathematical operators, 86
MAX() functions, 98-101
metacharacters
anchor metacharacters, 77
repetition metacharacters, 76
whitespace metacharacters, 74
MIN() functions, 98-102
Mod() function, 96
multiple columns, retrieving, 29
multiple instances, matching, 75-77
multiple rows, inserting, 165-166
multiple tables, joining, 130-132
MySQL
advantages of, 13-14
MariaDB compatibility, 15
mysql utility, 16-17, 252, 260-261
MySQL Workbench, 17-19
creating tables with, 261-262
downloading, 252
mysqld utility, 245
mysqldump utility, 243
mysqlhotcopy utility, 243
N
names, login, 21-22
natural joins, 136-137
navigating tables, 209
nonmatches, checking for, 48-49
NOT operator, 58-59
NULL keyword, 171
NULL values
checking for, 50-51
compared to empty strings, 179
COUNT() function, 100
NULL columns, 8
primary keys, 180
table columns, 177-178
numeric datatypes, 265-266
numeric functions, 90, 96
numeric values, storing, 265
O
OPEN CURSOR statement, 210-211
opening cursors, 210-211
operators
AND, 53-54
boolean operators, 156
conditional operators, 46
definition of, 53
IN, 57-58
LIKE, 61
mathematical, 86
NOT, 58-59
OR, 54-55
OPTIMIZE TABLE statement, 244
optimizing performance, 24-29
OR operator, 54-55, 70
ORDER BY clause, 38-43, 54, 112-113
positioning, 43
sorting by multiple columns, 40
orderitems table, 258
order of evaluation, 55-56
orders table, 257-258
outer joins, 137-138
overriding AUTO_INCREMENT, 181
overwriting tables, 177
P
parameters for stored procedures, 202-205
passwords, 241-242
percent sign (%) wildcard, 62-63
performance
deleting data, 172
improving, 247-249
subqueries, 119
views, 188
phone numbers, 265
Pi() function, 96
placeholders. See savepoints
populate.sql script, 261-262
portability, 89
pound sign (#), 36
predicates, 62
primary keys, 124, 179-180
customer table, 257
explained, 9-10
importance, 9
orders table, 258
products, 259
products table, 257
vendors table, 256
privileges. See access rights
procedures, stored. See stored procedures
processing transactions. See transaction processing
productnotes table, 259
productpricing() stored procedure, 200-201
products table, 256-257
proximity searching, 159
Q
queries
calculated fields
aliases, 84-85
concatenating fields, 82-83
explained, 81-82
mathematical calculations, 85-86
data formatting, 30
defined, 115
sorting results, 37-38
ascending/desccending order, 40-43
by multiple columns, 39-40
case sensitivity, 42
nonselected columns and, 39
views, 187
query expansion, 152-154
quotes
numeric values, 266
string values, 264
R
Rand() function, 96
range of values, checking for, 49-50
ranges, matching, 72-73
REAL datatype, 265
records, 8
referential integrity, 125
REGEXP keyword, 69-70
regular expressions, 67-68
anchors, 77-79
basic character matching, 68-70
matching
character classes, 75
multiple instances, 75-77
one of several characters, 71-72
ranges, 72-73
special characters, 73-74
OR matches, 70
relational tables, 123-124
RENAME TABLE statement, 185
renaming tables, 185
REPAIR TABLE statement, 244
repetition metacharacters, 76
replacing tables, 177
reserved words, 22, 269-274
RESTORE TABLE statement, 243
retrieved data, inserting, 166-168
retrieving
columns
all columns, 30
individual columns, 27-28
multiple columns, 29
unknown columns, 31
rows, 31-32
reusable views, creating, 190
reviewing log files, 245-246
REVOKE statement, 239-240
revoking access rights, 239-240
Right() function, 91
RIGHT keyword (outer joins), 138
rights. See access rights
rollbacks