MariaDB Crash Course - Ben Forta [93]
explained, 81-82
mathematical calculations, 85-86
subqueries as, 119-121
views, 193-194
calculated values, totaling, 102
calling stored procedures, 199
Cartesian products, 127
case sensitivity, 28, 42
changing passwords, 241-242
character classes, matching, 75
character matching
anchors, 77-79
basic character matching, 68-70
character classes, 75
multiple instances, 75-77
one of several characters, 71-72
OR matches, 70
ranges, 72-73
special characters, 73-74
character sets, 232-234
checking
for nonmatches, 48-49
for NULL value, 50-51
for range of values, 49-50
against single value, 47
CHECK TABLE statement, 244
clauses. See also specific clauses
definition of, 38
positioning, 46, 51
SELECT clause ordering, 113-114
client-based results formatting, 82
client-server software, 14-15
CLOSE statement, 211
closing cursors, 211
collation sequences, 232-234
columns. See also fields
aliases, 84-85
derived, 85
explained, 7-8
fully qualified names, 126
GROUP BY clause, 109
multiple, sorting query results by, 39-40
NULL, 8, 177-178
omitting, 164
padded spaces
RTrim() function, 83-84
primary keys, 9-10
retrieving
all columns, 30
individual columns, 27-28
multiple columns, 29
unknown columns, 31
subquery result restrictions, 118
updating multiple, 170
values, deleting, 171
viewing, 24
combined queries
creating, 141-144
duplicate rows and, 144-145
explained, 141
sorting results, 145-146
combining
aggregate functions, 104-105
WHERE clauses
AND operator, 53-54
OR operator, 54-55
order of evaluation, 55-56
comments, 35-36
commits, 227
default commit behavior, 230
explicit commits, 228-229
implicit commits, 228
COMMIT statement, 228-229
compatibility with MySQL, 15
compound queries. See combined queries
concatenating fields, 82-83
Concat() function, 82
conditional operators, 46
correlated subqueries, 120
Cos() function, 96
COUNT() function, 98-100, 139
COUNT* subquery, 119-121
create.sql script, 262
CREATE FULLTEXT statement, 148-149
CREATE PROCEDURE statement, 200-201
CREATE TABLE statement, 175-177
DEFAULT keyword, 181-182
engine types, 182-183
CREATE TRIGGER statement, 218
CREATE USER statement, 237
CREATE VIEW statement, 189-191
currency datatypes, 266
cursors
closing, 211
creating, 210
explained, 209
opening, 210-211
retrieving data with, 212-216
customers table, 257
D
data
breaking correctly (columns), 7
deleting
guidelines, 172-173
TRUNCATE TABLE statement, 172
filtering. See data filtering
grouping
explained, 107
filtering groups, 109-111
GROUP BY clause, 108-109
grouping and sorting, 112-113
nested groups, 108
updating, 172-173
databases. See also tables
explained, 5-6
maintenance
backing up data, 243
diagnosing startup problems, 245
performing, 243-244
reviewing log files, 245-246
schemas, 7
selecting, 22
viewing available databases, 23
database servers, 14
data filtering
groups, 109-111
by subqueries, 115-118
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
IN operator, 57-58
NOT operator, 58-59
wildcard filtering
LIKE operator, 61
% (percent sign) wildcard, 62-63
tips, 65
_ (underscore) wildcard, 64
with views, 192
datatypes, 8
binary, 266-267
currency, 266
date and time, 266
numeric, 265-266
string, 263-264
usefulness of, 263
date and time datatypes, 266
date and time functions, 90-95
DATE dataype, 266
DATETIME datatype, 266
DBMS (Database Management System), 6, 61
DECIMAL datatype, 265
DECLARE statement, 210, 213-214
default commit behavior, 230
default values, 181-182
DELAYED keyword, 248
DELETE statement, 171-172
FROM clause, 172
guidelines, 172-173
triggers, 221-222
WHERE clause, 171
deleting
column values, 171
data
guidelines, 172-173
table data, 171-172
TRUNCATE TABLE statement, 172
tables, 185
user accounts, 238
derived