MySQL in a Nutshell by Dyer, Russell -- Read -- Imperial Library of Trantor

Index

MySQL in a Nutshell

Preface

The Purpose of This Book How This Book Is Organized

Part I, Introduction and Tutorials Part II, SQL Statements and Functions Part III, MySQL Server and Client Tools Part IV, MySQL API Appendixes

Conventions Used in This Book Using Code Examples Request for Comments Safari® Enabled Acknowledgments

I. Introduction and Tutorials

1. Introduction to MySQL

The Value of MySQL The MySQL Package Licensing Mailing Lists Books and Other Publications

2. Installing MySQL

Choosing a Distribution Unix Source Distributions Unix Binary Distributions Linux RPM Distributions Macintosh OS X Distributions Novell NetWare Distributions Windows Distributions Postinstallation

3. MySQL Basics

The mysql Client Creating a Database and Tables Show Me Inserting Data Selecting Data Ordering, Limiting, and Grouping Analyzing and Manipulating Data Changing Data Deleting Data Searching Data Importing Data in Bulk Command-Line Interface Conclusion

II. SQL Statements and Functions

4. Security and User Statements and Functions

Statements and Functions SQL Statements in Alphabetical Order

CREATE USER DROP USER FLUSH GRANT RENAME USER RESET REVOKE SET PASSWORD SHOW GRANTS SHOW PRIVILEGES

Functions in Alphabetical Order

AES_DECRYPT() AES_ENCRYPT() CURRENT_USER() DECODE() DES_DECRYPT() DES_ENCRYPT() ENCODE() ENCRYPT() MD5() OLD_PASSWORD() PASSWORD() SESSION_USER() SHA() SHA1() SYSTEM_USER() USER()

5. Database and Table Schema Statements

Statements and Clauses in Alphabetical Order

ALTER DATABASE ALTER SCHEMA ALTER SERVER ALTER TABLE ALTER VIEW CREATE DATABASE CREATE INDEX CREATE SCHEMA CREATE SERVER CREATE TABLE CREATE VIEW DESCRIBE DROP DATABASE DROP INDEX DROP SERVER DROP TABLE DROP VIEW RENAME DATABASE RENAME TABLE SHOW CHARACTER SET SHOW COLLATION SHOW COLUMNS SHOW CREATE DATABASE SHOW CREATE TABLE SHOW CREATE VIEW SHOW DATABASES SHOW INDEXES SHOW SCHEMAS SHOW TABLE STATUS SHOW TABLES SHOW VIEWS

6. Data Manipulation Statements and Functions

Statements and Functions Grouped by Characteristics

Data Manipulation Statements Transaction Statements Related Functions

Statements and Clauses in Alphabetical Order

BEGIN COMMIT DELETE DO EXPLAIN HANDLER HELP INSERT JOIN LIMIT LOAD DATA INFILE RELEASE SAVEPOINT REPLACE ROLLBACK ROLLBACK TO SAVEPOINT SAVEPOINT SELECT SET SET TRANSACTION SHOW ERRORS SHOW WARNINGS START TRANSACTION TRUNCATE UNION UPDATE USE XA

Functions in Alphabetical Order

ANALYSE() BENCHMARK() DATABASE() FOUND_ROWS() LAST_INSERT_ID() ROW_COUNT() SCHEMA()

7. Table and Server Administration Statements and Functions

Statements and Clauses in Alphabetical Order

ALTER SERVER ANALYZE TABLE BACKUP TABLE CACHE INDEX CHECK TABLE CHECKSUM TABLE CREATE SERVER FLUSH KILL LOAD INDEX INTO CACHE LOCK TABLES OPTIMIZE TABLE REPAIR TABLE RESET RESTORE TABLE SET SHOW ENGINE SHOW ENGINES SHOW OPEN TABLES SHOW PLUGINS SHOW PROCESSLIST SHOW STATUS SHOW TABLE STATUS SHOW VARIABLES UNLOCK TABLES

Functions in Alphabetical Order

CONNECTION_ID() GET_LOCK() IS_FREE_LOCK() IS_USED_LOCK() RELEASE_LOCK() UUID() VERSION()

8. Replication Statements and Functions

Merits of Replication Replication Process The Replication User Account Configuring the Servers Copying Databases and Starting Replication

Using mysqldump Alternative Methods for Making Copies

Starting Replication Backups with Replication SQL Statements and Functions in Alphabetical Order

CHANGE MASTER TO LOAD DATA FROM MASTER LOAD TABLE...FROM MASTER MASTER_POS_WAIT() PURGE MASTER LOGS RESET MASTER RESET SLAVE SET GLOBAL SQL_SLAVE_SKIP_COUNTER SET SQL_LOG_BIN SHOW BINLOG EVENTS SHOW BINARY LOGS SHOW MASTER LOGS SHOW MASTER STATUS SHOW SLAVE HOSTS SHOW SLAVE STATUS START SLAVE STOP SLAVE

Replication States

Master BinLog Dump Thread States Slave I/O Thread States Slave SQL Thread States

9. Stored Routines Statements

Statements in Alphabetical Order

ALTER EVENT ALTER FUNCTION ALTER PROCEDURE ALTER TRIGGER BEGIN...END CALL CLOSE CREATE EVENT CREATE FUNCTION CREATE PROCEDURE CREATE TRIGGER DECLARE DELIMITER DROP EVENT DROP FUNCTION DROP PREPARE DROP PROCEDURE DROP TRIGGER EXECUTE FETCH OPEN PREPARE SHOW CREATE EVENT SHOW CREATE FUNCTION SHOW CREATE PROCEDURE SHOW EVENTS SHOW FUNCTION CODE SHOW FUNCTION STATUS SHOW PROCEDURE CODE SHOW PROCEDURE STATUS SHOW TRIGGERS

10. Aggregate Clauses, Aggregate Functions, and Subqueries

Aggregate Functions in Alphabetical Order

AVG() BIT_AND() BIT_OR() BIT_XOR() COUNT() GROUP_CONCAT() MAX() MIN() STD() STDDEV() STDDEV_POP() STDDEV_SAMP() SUM() VAR_POP() VAR_SAMP() VARIANCE()

Subqueries

Single Field Subqueries Multiple Fields Subqueries Results Set Subqueries

11. String Functions

String Functions Grouped by Type

Character Sets and Collation Converting Formatting Expressions Extracting Manipulating

String Functions in Alphabetical Order

ASCII() BIN() BINARY BIT_LENGTH() CAST() CHAR() CHAR_LENGTH() CHARACTER_LENGTH() CHARSET() COALESCE() COERCIBILITY() COLLATION() COMPRESS() CONCAT() CONCAT_WS() CONVERT() CRC32() ELT() EXPORT_SET() FIELD() FIND_IN_SET() HEX() INSERT() INSTR() INTERVAL() LCASE() LEFT() LENGTH() LOAD_FILE() LOCATE() LOWER() LPAD() LTRIM() MAKE_SET() MATCH() AGAINST() MID() OCTET_LENGTH() ORD() POSITION() QUOTE() REPEAT() REPLACE() REVERSE() RIGHT() RPAD() RTRIM() SOUNDEX() SPACE() STRCMP() SUBSTR() SUBSTRING() SUBSTRING_INDEX() TRIM() UCASE() UNCOMPRESS() UNCOMPRESSED_LENGTH() UNHEX() UPPER()

12. Date and Time Functions

Date and Time Functions Grouped by Type

Determining the Date or Time Extracting and Formatting the Date or Time Calculating and Modifying the Date or Time

Date and Time Functions in Alphabetical Order

ADDDATE() ADDTIME() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME() DATE() DATE_ADD() DATE_FORMAT() DATE_SUB() DATEDIFF() DAY() DAYNAME() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() EXTRACT() FROM_DAYS() FROM_UNIXTIME() GET_FORMAT() HOUR() LAST_DAY() LOCALTIME() LOCALTIMESTAMP() MAKEDATE() MAKETIME() MICROSECOND() MINUTE() MONTH() MONTHNAME() NOW() PERIOD_ADD() PERIOD_DIFF() QUARTER() SEC_TO_TIME() SECOND() SLEEP() STR_TO_DATE() SUBDATE() SUBTIME() SYSDATE() TIME() TIME_FORMAT() TIME_TO_SEC() TIMEDIFF() TIMESTAMP() TIMESTAMPADD() TIMESTAMPDIFF() TO_DAYS() UNIX_TIMESTAMP() UTC_DATE() UTC_TIME() UTC_TIMESTAMP() WEEK() WEEKDAY() WEEKOFYEAR() YEAR() YEARWEEK()

13. Mathematical Functions

Functions in Alphabetical Order

ABS() ACOS() ASIN() ATAN() ATAN2() BIT_COUNT() CEIL() CEILING() CONV() COS() COT() DEGREES() EXP() FLOOR() FORMAT() GREATEST() INET_ATON() INET_NTOA() LEAST() LN() LOG() LOG2() LOG10() MOD() OCT() PI() POW() POWER() RADIANS() RAND() ROUND() SIGN() SIN() SQRT() TAN() TRUNCATE()

14. Flow Control Functions

Functions in Alphabetical Order

CASE IF() IFNULL() ISNULL() NULLIF()

III. MySQL Server and Client Tools

15. MySQL Server and Client

mysql Client

mysql

mysqld Server

mysqld

mysqld_multi

mysqld_safe

16. Command-Line Utilities

comp_err make_binary_distribution msql2mysql my_print_defaults myisam_ftdump myisamchk myisamlog myisampack mysql_convert_table_format mysql_find_rows mysql_fix_extensions mysql_fix_privilege_tables mysql_setpermission mysql_tableinfo mysql_upgrade mysql_waitpid mysql_zap mysqlaccess mysqladmin mysqlbinlog mysqlbug mysqlcheck mysqldump mysqldumpslow mysqlhotcopy mysqlimport mysqlshow mysqlslap perror replace resolveip resolve_stack_dump

IV. APIs and Connectors

17. C API

Using C with MySQL

Connecting to MySQL Querying MySQL

Functions in Alphabetical Order

mysql_affected_rows() mysql_autocommit() mysql_change_user() mysql_character_set_name() mysql_close() mysql_commit() mysql_connect() mysql_create_db() mysql_data_seek() mysql_debug() mysql_drop_db() mysql_dump_debug_info() mysql_eof() mysql_errno() mysql_error() mysql_escape_string() mysql_fetch_field() mysql_fetch_field_direct() mysql_fetch_fields() mysql_fetch_lengths() mysql_fetch_row() mysql_field_count() mysql_field_seek() mysql_field_tell() mysql_free_result() mysql_get_client_info() mysql_get_character_set_info() mysql_get_client_version() mysql_get_host_info() mysql_get_proto_info() mysql_get_server_info() mysql_get_server_version() mysql_get_ssl_cipher() mysql_hex_string() mysql_info() mysql_init() mysql_insert_id() mysql_kill() mysql_library_end() mysql_library_init() mysql_list_dbs() mysql_list_fields() mysql_list_processes() mysql_list_tables() mysql_more_results() mysql_next_result() mysql_num_fields() mysql_num_rows() mysql_options() mysql_ping() mysql_query() mysql_real_connect() mysql_real_escape_string() mysql_real_query() mysql_reload() mysql_refresh() mysql_rollback() mysql_row_seek() mysql_row_tell() mysql_select_db() mysql_set_character_set() mysql_set_local_infile_default() mysql_set_local_infile_handler() mysql_set_server_option() mysql_shutdown() mysql_sqlstate() mysql_ssl_set() mysql_stat() mysql_store_result() mysql_thread_end() mysql_thread_id() mysql_thread_init() mysql_thread_safe() mysql_use_result() mysql_warning_count()

C API Datatypes

18. Perl API

Using Perl DBI with MySQL

Connecting to MySQL Executing an SQL Statement Capturing Data Disconnecting from MySQL Temporarily Storing Results

Perl DBI Reference

available_drivers() begin_work() bind_col() bind_columns() bind_param() bind_param_array() bind_param_inout() can() clone() column_info() commit() connect() connect_cached() data_diff() data_sources() data_string_desc() data_string_diff() disconnect() do() dump_results() err() errstr() execute() execute_array() execute_for_fetch() fetch() fetchall_arrayref() fetchall_hashref() fetchrow_array() fetchrow_arrayref() fetchrow_hashref() finish() foreign_key_info() func() get_info() installed_drivers() installed_versions() last_insert_id() looks_like_number() neat() neat_list() parse_dsn() parse_trace_flag() parse_trace_flags() ping() prepare() prepare_cached() primary_key() primary_key_info() private_attribute_info() quote() quote_identifier() rollback() rows() selectall_arrayref() selectall_hashref() selectcol_arrayref() selectrow_array() selectrow_arrayref() selectrow_hashref() set_err() state() statistics_info() swap_inner_handle() table_info() tables() take_imp_data() trace() trace_msg() type_info() type_info_all()

Attributes for Handles

Attributes for All Handles Attributes Only for Database Handles Attributes Only for Statement Handles DBI Dynamic Attributes

19. PHP API

Using PHP with MySQL

Connecting to MySQL Querying MySQL

PHP MySQL Functions in Alphabetical Order

mysql_affected_rows() mysql_change_user() mysql_client_encoding() mysql_close() mysql_connect() mysql_create_db() mysql_data_seek() mysql_db_name() mysql_db_query() mysql_drop_db() mysql_errno() mysql_error() mysql_escape_string() mysql_fetch_array() mysql_fetch_assoc() mysql_fetch_field() mysql_fetch_lengths() mysql_fetch_object() mysql_fetch_row() mysql_field_flags() mysql_field_len() mysql_field_name() mysql_field_seek() mysql_field_table() mysql_field_type() mysql_free_result() mysql_get_client_info() mysql_get_host_info() mysql_get_proto_info() mysql_get_server_info() mysql_info() mysql_insert_id() mysql_list_dbs() mysql_list_fields() mysql_list_processes() mysql_list_tables() mysql_num_fields() mysql_num_rows() mysql_pconnect() mysql_ping() mysql_query() mysql_real_escape_string() mysql_result() mysql_select_db() mysql_set_charset() mysql_stat() mysql_tablename() mysql_thread_id() mysql_unbuffered_query()

V. Appendixes

A. Data Types

Numeric Data Types

BIT TINYINT BOOL, BOOLEAN SMALLINT MEDIUMINT INT, INTEGER BIGINT, SERIAL FLOAT DOUBLE, DOUBLE PRECISION DEC, DECIMAL, FIXED, NUMERIC

Date and Time Data Types String Data Types

CHAR VARCHAR BINARY VARBINARY TINYBLOB TINYTEXT BLOB TEXT MEDIUMBLOB MEDIUMTEXT LONGBLOB LONGTEXT ENUM SET

B. Operators

Arithmetic Operators Relational Operators Logical Operators Bitwise Operators Regular Expressions

C. Server and Environment Variables

Index About the Author Colophon

← Prev
Back
Next →

← Prev
Back
Next →