|
|
data, 212, 239
Data Definition Language (DDL), 24
data directory, 452
data integrity, 214
Data Manipulation Language (DML), 24
data redundancy, 209
data sources and ODBC (Open Database Connectivity), 663–665
connection options, 665
setup, 664
data types, 10, 213. See column types
DATABASE() function, 561
database lifecycle, 239–250
analysis phase, 240–241
design phase, 241–248
common errors, 246–248
concept, 242–245
logical and physical, 245–248
example, 250–256
analysis phase, 250–251
design phase, 251–254
implementation phase, 254– 255
testing, operation and maintenance, 255–256
implementation phase, 248
maintenance phase, 249–250
operation phase, 249
testing phase, 248
database maintenance
analyzing tables, 282–284
with ANALYZE TABLE statement, 282–283
with myisamchk utility, 283–284
with mysqlcheck utility, 283
checking tables, 284–288
with CHECK TABLE statement, 285–286
with myisamchk utility, 287–288
with mysqlcheck utility, 286–287
with myisamchk utility, 294–298
check options, 296–297
general options, 295–296, 298
repair options, 297–298
with mysqlcheck utility, 292–294
optimizing tables, 280–282
with myisamchk utility, 281–282
with mysqlcheck utility, 281
with OPTIMIZE statement, 280
repairing tables, 288–298
with myisamchk utility, 291– 292
with mysqlcheck utility, 290–291
with REPAIR TABLE, 289– 290
database management system, 4
database programming
development stages, 185–189
application design, 187
coding, 187–188
requirements analysis, 186– 187
testing and implementation, 189
techniques, 176–185
code portability and maintenance, 177–181
persistent connections, 176
work division between database server and application, 181–185
databases
connecting to, 9. See also connection to MySQL server
creating, 7–38
experimenting, 8–9
following MySQL install, 7–8
need for permission from administrator, 8
PHP function for, 574
date functions, 26–29
dropping tables and databases, 24– 25
PHP function for, 575–576
early models, 209–211
hierarchical, 210, 210–211
network, 211, 211
information retrieval, 13–23
average, minimum and total values, 22–23
calculations, 23
counting, 21–22
distinct records, 21
limiting number of results, 18–20
order of processing conditions, 15–16
pattern matching, 16–17
returning maximum value, 20– 21
sorting, 17–18
mysql command option to use, 52
names for, PHP function to return, 575
normalization, 219–236
1st normal form, 226–227
2nd normal form, 227–228
3rd normal form, 228–229
4th normal form, 233–234
5th normal form and beyond, 234–236
Boyce-Codd normal form, 229–232
and denormalization, 236– 237
PHP function to change, 592
PHP function to return resource to list, 586–587
symbolic linking, 473–476
term definition, 212
terminology, 5
what it is, 4
datadir variable, 380
data_sources method (Perl DBI), 597
date and time column types, 50–52
date and time functions, 26–29, 511–524
ADDDATE, 512
CURDATE, 512
current date and time, 28–29
CURRENT_DATE, 512
CURRENT_TIME, 512–513
CURRENT_TIMESTAMP, 513
CURTIME, 513
DATE_ADD, 513
DATE_FORMAT, 513–515
DATE_SUB, 515
DAYNAME, 515
DAYOFMONTH, 516
DAYOFWEEK, 516
DAYOFYEAR, 516
EXTRACT, 516–517
FROM_DAYS, 517
FROM_UNIXTIME, 517–518
HOUR, 518
MINUTE, 518
MONTH, 518
MONTHNAME, 519
NOW, 519
PERIOD_ADD, 519
PERIOD_DIFF, 520
QUARTER, 520
SECOND, 521
SEC_TO_TIME, 520–521
specifying format, 28
SUBDATE, 521
SYSDATE, 521
TIME_FORMAT, 521
TIME_TO_SEC, 521
TO_DAYS, 522
UNIX_TIMESTAMP, 522
WEEK, 522–523
WEEKDAY, 523
YEAR, 523–524
YEARWEEK, 524
date calculations, 35–37
DATE column type, 51
DATE_ADD() function, 513
DATE_FORMAT() function, 513–515
DATE_SUB() function, 515
DATETIME date column type, 51
DAYNAME() function, 515
DAYOFMONTH() function, 29, 516
DAYOFWEEK() function, 516
DAYOFYEAR() function, 30, 516
db table
fields, 418
and user permissions, 421, 422
db_pconnect() function (PHP), 178
DDL (Data Definition Language), 24
debug information, mysql command option for, 54
debug log, mysql command option for, 52
debugging queries, 188
DEC column type, 44
DECIMAL column type, 44
decimal number system, 80
DECODE() function, 561
default character set, mysql command option for, 52
DEGREES() function, 546
deinit function
for aggregate user-defined functions, 201
for standard user-defined functions, 198
Delayed_errors variable, 388
delayed_insert_limit variable, 380
Delayed_insert_threads variable, 388
delayed_insert_timeout variable, 380
delayed_queue_size variable, 373, 380
Delayed_writes variable, 388
delay_key_write variable, 380
DELETE privilege, 435, 488
DELETE statement, 23–24, 486
optimizing, 172
deleteRow() method (Java), 634
deleting records in table, 23–24, 98
denormalization, 236–237
dependencies, multivalued, 234
DESC keyword, 18
descending sort, 18
DESCRIBE statement, 11, 486
DESCRIPTION attribute (Python), 620
DES_DECRYPT() function, 561
DES_ENCRYPT() function, 561–562
design phase of database lifecycle, 241–248
concept, 242–245
example, 251–254
logical and physical, 245–248
determinant, 232
directories
for binary installation, 452
for data storage, 262, 263
for program code, 188
dirty read, 124
disconnect method (Perl DBI), 600
distclean utility, 465
DISTINCT keyword, in SELECT statement, 92
DML (Data Manipulation Language), 24
do method (Perl DBI), 600–601
DO statement, 487
documentation of code, 187
domain, 213
domain key normal form, 236
dotted notation for table fieldnames, 33–34
DOUBLE column type, 43
DOUBLE PRECISION column type, 43
Driver attribute (Perl DBI), 613–614
DROP clause, for ALTER TABLE statement, 26
DROP DATABASE statement, and transaction completion, 117
DROP INDEX statement, 152
DROP privilege, 435, 446, 488
DROP statement, 487
DROP TABLE statement, and transaction completion, 117
dump_results method (Perl DBI), 607
duplication of data, and normalization, 223
durability of transactions, 257
dynamic tables, 57–58
|
|