MariaDB Crash Course - Ben Forta [94]
DESC keyword, 40-43
DESCRIBE statement, 24-25
diagnosing startup problems, 245
dictionary sort order, 42
DISTINCT keyword, 31-32, 103-104
downloading
MariaDB, 252
MySQL Workbench, 252
DROP PROCEDURE statement, 201
DROP TABLE statement, 185
DROP TRIGGER statement, 219
DROP USER statement, 238
dropping
stored procedures, 201
triggers, 219
E
empty strings, 179
enabling full-text searching, 148-149
encodings, 231
engine types, 182-183
equijoins. See inner joins
escaping, 74
evaluation, order of, 55-56
EXECUTE statement, 199
executing
scripts, 17
stored procedures, 199
Exp() function, 96
EXPLAIN statement, 248
explicit commits, 228-229
expressions. See regular expressions
F
FETCH statement, 212, 215-216
fields, calculated. See also columns
aliases, 84-85
concatenating, 82-83
explained, 81-82
mathematical calculations, 85-86
subqueries as, 119-121
views, 193-194
files, log
flushing, 246
reviewing, 245-246
filtering data
application filtering, 46
by subqueries, 115-118
groups, 109-111
WHERE clause, 45-46
checking against single value, 47
checking for nonmatches, 48-49
checking for NULL value, 50-51
checking for range of values, 49-50
combining clauses, 53-56
conditional operators, 46
wildcard filtering
LIKE operator, 61
tips, 65
% (percent sign) wildcard, 62-63
_ (underscore) wildcard, 64
with views, 192
fixed length strings, 264
FLOAT datatype, 265
flushing log files, 246
FLUSH LOGS statement, 246
FLUSH TABLES statement, 243
foreign keys, 124
formatting
retrieved data with views, 191
server-based compared to client-based, 82
statements, 177
subqueries, 117
FROM keyword, 27
FULLTEXT clause, 148-149
full-text searching
boolean text searches, 154-158
enabling, 148-149
explained, 147-148
performing searches, 149-152
query expansion, 152-154
support for, 147
tips and guidelines, 158
fully qualified table names, 34-35
functions
Against(), 149-152
aggregate functions, 97
ALL argument, 103
AVG(), 98-99
combining, 104-105
COUNT(), 99-100
DISTINCT argument, 103-104
joins and, 139-140
MAX(), 100-101
MIN(), 101-102
SUM(), 102-103
Concat(), 82
date and time functions, 92-95
defined, 89
LTrim(), 84
Match(), 149-152
numeric functions, 96
portability, 89
RTrim(), 83-84
system, 90
text functions, 90-92
Trim(), 84
G
globalization, 232-234
granting access rights, 239
GRANT statement, 239
GROUP BY clause, 108-109
grouping data
explained, 107
filtering groups, 109-111
GROUP BY clause, 108-109
grouping and sorting, 112-113
nested groups, 108
groups, filtering, 109-111
H
HAVING clause, 109
help option (mysqld), 245
HELP SHOW statement, 25
I
IGNORE keyword, 171
implicit commits, 228
improving performance, 247-249
IN BOOLEAN MODE keywords, 155-156
IN operator, 57-58
INFORMATION_SCHEMA statement, 25
inline comments, 35
inner joins, 129-130
INSERT statement
explained, 161
inserting multiple rows, 165-166
inserting retrieved data, 166-168
inserting single rows, 161-164
INSERT SELECT, 166-168
omitting columns, 164
performance, 164
security privileges, 161
triggers, 219-221
VALUES, 164
inserting
retrieved data, 166-168
rows
multiple rows, 165-166
single rows, 161-164
inspecting stored procedures, 208
installing MariaDB, 252
INT datatype, 265
J-K
joins
advantages of, 125
aggregate functions, 139-140
creating, 125-129
cross joins, 129
explained, 123
inner joins, 129-130
join conditions, 140
joining multiple tables, 130-132
natural joins, 136-137
outer joins, 137-138
self joins, 134-136
simplifying with views, 189-190
keys. See foreign keys; primary keys
keywords, 22, 269-274. See also specific keywords
KILL statement, 248
L
Left() function, 91
Length() function, 91
less than operator (WHERE clause), 156
LIKE keyword, 147
LIKE operator, 61
LIMIT clause, 32-34
Locate() function, 91
log-bin command line option, 245
log command line option, 245
log-error command line option, 245
log files
flushing, 246
reviewing, 245-246
logging