Index_S

S

safe_mysqld script, 263

safe_show_database variable, 386

SCALE attribute (Perl DBI), 616

scope creep, 186

scope, for transaction level, 125

scripts directory, 452

SECOND() function, 521

2nd normal form, 227–228

SEC_TO_TIME() function, 520–521

Secure Socket Layer, connections, 447–448

security. See also users and permissions management

applications, 448–449

and LOAD DATA LOCAL, 449

LOAD DATA LOCAL statement and, 325

when connecting, 415–416

SELECT INTO statement for backups, 315–318

restoring database with LOAD DATA, 318–325

restoring database with mysqlimport, 325–326

SELECT privilege, 436, 489

SELECT statement, 13–23, 493–495

calculations during, 161–163

calculations in, 23

counting rows returned, 21–22

DISTINCT keyword in, 92

EXPLAIN to analyze index use with, 157–164

GROUP BY clause, 37–38

IGNORE INDEX, 158

AS keyword, 30

LIMIT clause, 18–20

optimizing, 164–169

ORDER BY clause, 17–18

index use for, 154

pattern matching, 16–17

to return average, minimum and total values, 22–23

returning distinct records, 21

sub-selects, 94–95

as joins, 95–96

USE INDEX, 158

WHERE clause, 14

index use to match condition, 153

order of processing conditions, 15–16

setting variable, 101

wildcard (*), 14

selectall_arrayref method (Perl DBI), 602–603

selectall_hashref method (Perl DBI), 603

selectcol_arrayref method (Perl DBI), 603

Select_full_join variable, 390

Select_full_range_join variable, 390

Select_range variable, 390

Select_range_check variable, 390

selectrow_array method (Perl DBI), 603

selectrow_arrayref method (Perl DBI), 603

selectrow_hashref method (Perl DBI), 604

Select_scan variable, 390

; (semicolon), for MySQL commands, 10

semicolon (;), for MySQL commands, 10

SERIALIZABLE transaction level, 124

server_id variable, 386

service on Windows NT/2000/XP, installing MySQL as, 454–455

SESSION keyword, for SET statement, 391

SESSION_USER() function, 569

SET column type, 46, 49–50

SET GLOBAL SQL_SLAVE_SKIP_ COUNTER statement, 343, 358

SET PASSWORD statement, 7

SET SQL_LOG_BIN statement, 343

SET statement, 99, 495–497

to change variable values, 391–393

GLOBAL keyword, 391

SESSION keyword, 391

for user passwords, 438

SET TRANSACTION statement, 497

setAutoCommit() method (Java), 628

setCursorName() method (Java), 632

set_err method (Perl DBI), 598

setEscapeProcessing() method (Java), 632–633

setFetchSize() method (Java), 633, 638

setinputsizes() method (Python), 623

setMaxFields() method (Java), 633

setMaxFieldSize() method (Java), 633

setNextException() method (Java), 642

setNextWarning() method (Java), 642

setoutputsizes() method (Python), 623

setQueryTimeout() method (Java), 633

setReadOnly() method (Java), 628

setTransactionIsolation() method (Java), 628

setTypeMap() method (Java), 629

setXXX() method (Java), 631–632

SHA() function, 569

SHA1() function, 569

share/mysql directory, 452, 463

shared libraries, avoiding common problems, 465

SHOW BINLOG EVENTS statement, 343

SHOW DATABASES privilege, 436, 489

SHOW INDEX statement, 169–170

SHOW MASTER LOGS statement, 343

SHOW MASTER STATUS command, 348–349

SHOW SLAVE STATUS command, 342, 345, 348

SHOW statement, 497

SHOW STATUS statement, 365–366

SHOW TABLES statement, 11

SHOW VARIABLES statement, 361

ShowErrorStatement attribute (Perl DBI), 613

SHUTDOWN privilege, 436, 447, 489

shutting down MySQL

in Unix, 263–265

in Windows, 265–269

SIGN() function, 553

SIN() function, 553

skip_external_locking variable, 386

skip_networking variable, 386

skip_show_database variable, 386

slave for replication, 337

commands for, 342–343

configuration file options, 340–342

SLAVE START command, 342

SLAVE STOP command, 342

slave_net_timeout variable, 386

Slave_open_temp_tables variable, 390

Slave_running variable, 390

slow query log, 272, 276–277

Slow_launch_threads variable, 390

slow_launch_time variable, 386

slow_queries variable, 373

Slow_queries variable, 390

SMALLINT column type, 43

socket file, mysql command option for, 54

socket variable, 386

software, open source vs. proprietary, 3

software RAID, 473

sort_buffer variable, 373, 386

sorting

character set and, 409

by MySQL server vs. by application, 181–185

SET column type, 50

Sort_merge_passes variable, 390

Sort_range variable, 390

Sort_rows variable, 390

Sort_scan variable, 390

SOUNDEX() function, 540–541

source code for MySQL availability, 3

vs. binary distribution, 451–453

directories, 452–453

SOURCE command, 104–105

SPACE() function, 541

sql-bench directory, 452, 463

SQL statements

running those stored in files, 102–105

from command line, 104–105

SQL (Structured Query Language), 4, 5

operators, 67–84

arithmetic operators, 69–70

bit operators, 79–84, 509–511

comparison operators, 70–79

LIKE clause, 74–75

logical operators, 68

regular expressions, 75–79

SQLAllocConnect function (MyODBC), 672

SQLAllocEnv function (MyODBC), 672

SQLAllocHandle function (MyODBC), 673

SQLAllocStmt function (MyODBC), 673

SQL_AUTO_IS_NULL variable, 392, 496

SQL_BIG_SELECTS variable, 393, 496

SQLBindParameter function (MyODBC), 673

SQL_BUFFER_RESULT variable, 393, 496

SQLBulkOperations function (MyODBC), 673

SQLCancel function (MyODBC), 673

SQLCloseCursor function (MyODBC), 674

SQLColAttribute function (MyODBC), 674

SQLColAttributes function (MyODBC), 674

SQLColumnPrivileges function (MyODBC), 674

SQLColumns function (MyODBC), 675

SQLConnect function (MyODBC), 675

SQLDataSources function (MyODBC), 675

SQLDescribeCol function (MyODBC), 675

SQLDescribeParam function (MyODBC), 675–676

SQLDisconnect function (MyODBC), 676

SQLDriverConnect function (MyODBC), 676

SQLDrivers function (MyODBC), 676

SQLEndTran function (MyODBC), 676–677

SQLError function (MyODBC), 677

SQLExecDirect function (MyODBC), 677

SQLExecute function (MyODBC), 677

SQLExtendedFetch function (MyODBC), 677

SQLFetch function (MyODBC), 677

SQLFetchScroll function (MyODBC), 677

SQLForeignKeys function (MyODBC), 678

SQLFreeConnect function (MyODBC), 677

SQLFreeEnv function (MyODBC), 677

SQLFreeHandle function (MyODBC), 678

SQLFreeStmt function (MyODBC), 678

SQLGeStmtAttr function (MyODBC), 683–686

SQLGetConnectAttr function (MyODBC), 678–679

SQLGetConnectOption function (MyODBC), 679

SQLGetCursorName function (MyODBC), 680

SQLGetDiagField function (MyODBC), 680–681

SQLGetDiagRec function (MyODBC), 681

SQLGetEnvAttr function (MyODBC), 682

SQLGetFunctions function (MyODBC), 682–683

SQLGetInfo function (MyODBC), 683

SQLGetStmtOption function (MyODBC), 686

SQLGetTypeInfo function (MyODBC), 686

SQL_LOG_OFF variable, 393, 496

SQL_LOG_UPDATE variable, 393, 496

SQLNativeSql function (MyODBC), 686

SQLNumParams function (MyODBC), 686

SQLNumResultCols function (MyODBC), 686

SQLParamData function (MyODBC), 686

SQLPrepare function (MyODBC), 687

SQLPrimaryKeys function (MyODBC), 687

SQLPutData function (MyODBC), 687

SQL_QUOTE_SHOW_CREATE variable, 393, 496

SQLRowCount function (MyODBC), 687

SQL_SAFE_UPDATES variable, 393, 496

SQL_SELECT_LIMIT variable, 393, 496

SQLSetConnectAttr function (MyODBC), 687

SQLSetConnectOption function (MyODBC), 688

SQLSetCursorName function (MyODBC), 688

SQLSetEnvAttr function (MyODBC), 688

SQLSetPos function (MyODBC), 688

SQLSetScrollOptions function (MyODBC), 688

SQLSetStmtAttr function (MyODBC), 689

SQLSetStmtOption function (MyODBC), 689

SQLSpecialColumns function (MyODBC), 689

SQLStatistics function (MyODBC), 689–690

SQLTablePrivileges function (MyODBC), 690

SQLTables function (MyODBC), 690

SQLTransact function (MyODBC), 690

SQRT() function, 553

ssl_[variables] variable, 390

stakeholders, and database design, 244

standard user-defined functions, 191, 195–198

creating, 198–200

deinit function, 198

init function, 195–197

args parameter, 196

initd parameter, 196

message parameter, 197

main function, 197–198

args parameter, 197–198

initd parameter, 197–198

starting MySQL

avoiding common problems, 464–465

in Unix, 263–265

in Windows, 265–269

state attribute (Perl DBI), 617

state method (Perl DBI), 598–599

Statement attribute (Perl DBI), 614, 616

static tables, 57

status variables, 387–391

STD() function, 557

STDDEV() function, 557

string column types, 45–50

string functions, 524–543

ASCII, 524–525

BIN, 525

BIT_LENGTH, 526

CHAR, 526

CHARACTER_LENGTH, 526

CHAR_LENGTH, 526

CONCAT, 526–527

CONCAT_WS, 527–528

CONV, 528–529

ELT, 529

EXPORT_SET, 529

FIELD, 530

FIND_IN_SET, 530

HEX, 530–532

INSERT, 532

INSTR, 532–533

LCASE, 533

LEFT, 533

LENGTH, 533–534

LOAD_FILE, 534

LOCATE, 534

LOWER, 535

LPAD, 535

LTRIM, 535–536

MAKE_SET, 536

OCT, 536–537

OCT_LENGTH, 537

ORD, 537

POSITION, 537–538

QUOTE, 538

REPEAT, 538

REPLACE, 539

REVERSE, 539

RIGHT, 539

RPAD, 539–540

RTRIM, 540

SOUNDEX, 540–541

SPACE, 541

SUBSTRING, 541–542

SUBSTRING_INDEX, 542

TRIM, 542–543

UCASE, 543

UPPER, 543

strings, comparing, 73–74

striping, 469–470

striping data across disks, 394

Structured Query Language (SQL), 4

SUBDATE() function, 521

SUBSTRING() function, 541–542

SUBSTRING_INDEX() function, 542

SUM() function, 22–23, 37, 557

grouping in query with, 37–38

Sun version of tar distribution, 456

SUPER privilege, 436, 489

super-smack, 399

.sym file extension, 475

symbolic links, 473–477

symlinking, 394, 473–477

SYSDATE() function, 521

system security, 449

system testing, 189

SYSTEM_USER() function, 569