MariaDB Crash Course - Ben Forta [96]
COMMIT statement, 228-229
ROLLBACK statement, 227-228
ROLLBACK statement, 227-228
rows
cursors, 209
explained, 8
inserting
multiple rows, 165-166
single rows, 161-164
NULL, 8
retrieving, 31-32
RTrim() function, 83-84, 91
S
safe-mode option (mysqld), 245
Sams Teach Yourself Regular Expressions in 10 Minutes, 68
savepoints, 227-230
SAVEPOINT statement, 229
scalability, 124
scripts
create.sql, 262
executing, 17
populate.sql, 261-262
search criteria. See filtering data
searching
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
proximity searching, 159
with regular expressions. See regular expressions
search pattern, 61
security
access control, 235-236
user accounts
access rights, 238-241
creating, 237-238
deleting, 238
obtaining list of, 236-237
passwords, 241-242
SELECT statement, 27
AS keyword, 84-85
clause ordering, 113-114
concatenating fields, 83
DISTINCT keyword, 31-32
FROM keyword, 27
fully qualified table names, 34-35
GROUP BY clause, 108-109
IN operator, 57-58
INSERT SELECT, 166-168
IS NULL clause, 50
LIMIT clause, 32-34
limiting results, 32-34
NOT operator, 58-59
ORDER BY clause, 38-43, 112-113
retrieving, 31-32
retrieving all columns, 30
retrieving individual columns, 27-28
retrieving multiple columns, 29
retrieving unknown columns, 31
SELECT INTO OUTFILE, 243
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
selecting databases, 22
self joins, 134-136
semicolons (;), 28
server-based results fomatting, 82
servers, database servers, 14
SET PASSWORD statement, 241-242
SET statement, 170
SHOW CHARACTER SET statement, 232
SHOW COLLATION statement, 232
SHOW COLUMNS FROM statement, 25
SHOW CREATE DATABASE statement, 25
SHOW CREATE TABLE statement, 25
SHOW DATABASES statement, 23
SHOW ERRORS statement, 25
SHOW GRANTS statement, 25, 238
SHOW PROCESSLIST statement, 248
SHOW STATUS statement, 25
SHOW TABLES statement, 23-24
showing
columns, 24
databases, 23
tables, 23-24
Sin() function, 96
single value, checking against, 47
SMALLINT datatype, 265-266
sorting query results, 37-38, 112-113
ascending/desccending order, 40-43
case sensitivity, 42
by multiple columns, 39-40
nonselected columns and, 39
Soundex() function, 91
spaces, removing, 83-84
special characters, matching, 73-74
SQL statements. See specific statements
Sqrt() function, 96
standard deviation aggregate functions, 98
startup problems, diagnosing, 245
statements. See specific statements
stopwords, 158
stored procedures
advantages of, 198
building intelligent stored procedures, 205-207
creating, 200-201
disadvantages of, 198
dropping, 201
executing, 199
explained, 197-198
inspecting, 208
parameters, 202-205
productpricing(), 200-201
storing
date and time values, 266
numeric values, 265
strings, 263
string datatypes, 263-264
strings. See text functions
empty, 179
fixed length, 264
quotes, 264
variable-length, 264
wildcard searching and, 62
subqueries
as calculated fields, 119-121
combining, 117
correlated subqueries, 120
defined, 115
explained, 115
filtering by, 115-118
formatting, 117
performance and, 119
UPDATE statement, 170
WHERE clause, 118
SubString() function, 91
SUM() functions, 98, 102-103
T
tables
aliases, 133-134
AUTO_INCREMENT, 180-181
calculated fields
aliases, 84-85
concatenating fields, 82-83
explained, 81-82
mathematical calculations, 85-86
Cartesian products, 127
columns
aliases, 84-85
explained, 7-8
NULL, 8
primary keys, 9-10
retrieving all columns, 30
retrieving individual columns, 27-28
retrieving multiple columns, 29
retrieving unknown columns, 31
creating, 259
CREATE TABLE statement, 176-177
explained, 175
with mysql, 260-261