Table of Contents for
MySQL in a Nutshell, 2nd Edition

Version ebook / Retour

Cover image for bash Cookbook, 2nd Edition MySQL in a Nutshell, 2nd Edition by Russell J.T. Dyer Published by O'Reilly Media, Inc., 2008
  1. Cover
  2. MySQL in a Nutshell
  3. MySQL in a Nutshell
  4. Dedication
  5. Preface
  6. The Purpose of This Book
  7. How This Book Is Organized
  8. Conventions Used in This Book
  9. Using Code Examples
  10. Request for Comments
  11. Safari® Enabled
  12. Acknowledgments
  13. I. Introduction and Tutorials
  14. 1. Introduction to MySQL
  15. The Value of MySQL
  16. The MySQL Package
  17. Licensing
  18. Mailing Lists
  19. Books and Other Publications
  20. 2. Installing MySQL
  21. Choosing a Distribution
  22. Unix Source Distributions
  23. Unix Binary Distributions
  24. Linux RPM Distributions
  25. Macintosh OS X Distributions
  26. Novell NetWare Distributions
  27. Windows Distributions
  28. Postinstallation
  29. 3. MySQL Basics
  30. The mysql Client
  31. Creating a Database and Tables
  32. Show Me
  33. Inserting Data
  34. Selecting Data
  35. Ordering, Limiting, and Grouping
  36. Analyzing and Manipulating Data
  37. Changing Data
  38. Deleting Data
  39. Searching Data
  40. Importing Data in Bulk
  41. Command-Line Interface
  42. Conclusion
  43. II. SQL Statements and Functions
  44. 4. Security and User Statements and Functions
  45. Statements and Functions
  46. SQL Statements in Alphabetical Order
  47. CREATE USER
  48. DROP USER
  49. FLUSH
  50. GRANT
  51. RENAME USER
  52. RESET
  53. REVOKE
  54. SET PASSWORD
  55. SHOW GRANTS
  56. SHOW PRIVILEGES
  57. Functions in Alphabetical Order
  58. AES_DECRYPT()
  59. AES_ENCRYPT()
  60. CURRENT_USER()
  61. DECODE()
  62. DES_DECRYPT()
  63. DES_ENCRYPT()
  64. ENCODE()
  65. ENCRYPT()
  66. MD5()
  67. OLD_PASSWORD()
  68. PASSWORD()
  69. SESSION_USER()
  70. SHA()
  71. SHA1()
  72. SYSTEM_USER()
  73. USER()
  74. 5. Database and Table Schema Statements
  75. Statements and Clauses in Alphabetical Order
  76. ALTER DATABASE
  77. ALTER SCHEMA
  78. ALTER SERVER
  79. ALTER TABLE
  80. ALTER VIEW
  81. CREATE DATABASE
  82. CREATE INDEX
  83. CREATE SCHEMA
  84. CREATE SERVER
  85. CREATE TABLE
  86. CREATE VIEW
  87. DESCRIBE
  88. DROP DATABASE
  89. DROP INDEX
  90. DROP SERVER
  91. DROP TABLE
  92. DROP VIEW
  93. RENAME DATABASE
  94. RENAME TABLE
  95. SHOW CHARACTER SET
  96. SHOW COLLATION
  97. SHOW COLUMNS
  98. SHOW CREATE DATABASE
  99. SHOW CREATE TABLE
  100. SHOW CREATE VIEW
  101. SHOW DATABASES
  102. SHOW INDEXES
  103. SHOW SCHEMAS
  104. SHOW TABLE STATUS
  105. SHOW TABLES
  106. SHOW VIEWS
  107. 6. Data Manipulation Statements and Functions
  108. Statements and Functions Grouped by Characteristics
  109. Statements and Clauses in Alphabetical Order
  110. BEGIN
  111. COMMIT
  112. DELETE
  113. DO
  114. EXPLAIN
  115. HANDLER
  116. HELP
  117. INSERT
  118. JOIN
  119. LIMIT
  120. LOAD DATA INFILE
  121. RELEASE SAVEPOINT
  122. REPLACE
  123. ROLLBACK
  124. ROLLBACK TO SAVEPOINT
  125. SAVEPOINT
  126. SELECT
  127. SET
  128. SET TRANSACTION
  129. SHOW ERRORS
  130. SHOW WARNINGS
  131. START TRANSACTION
  132. TRUNCATE
  133. UNION
  134. UPDATE
  135. USE
  136. XA
  137. Functions in Alphabetical Order
  138. ANALYSE()
  139. BENCHMARK()
  140. DATABASE()
  141. FOUND_ROWS()
  142. LAST_INSERT_ID()
  143. ROW_COUNT()
  144. SCHEMA()
  145. 7. Table and Server Administration Statements and Functions
  146. Statements and Clauses in Alphabetical Order
  147. ALTER SERVER
  148. ANALYZE TABLE
  149. BACKUP TABLE
  150. CACHE INDEX
  151. CHECK TABLE
  152. CHECKSUM TABLE
  153. CREATE SERVER
  154. FLUSH
  155. KILL
  156. LOAD INDEX INTO CACHE
  157. LOCK TABLES
  158. OPTIMIZE TABLE
  159. REPAIR TABLE
  160. RESET
  161. RESTORE TABLE
  162. SET
  163. SHOW ENGINE
  164. SHOW ENGINES
  165. SHOW OPEN TABLES
  166. SHOW PLUGINS
  167. SHOW PROCESSLIST
  168. SHOW STATUS
  169. SHOW TABLE STATUS
  170. SHOW VARIABLES
  171. UNLOCK TABLES
  172. Functions in Alphabetical Order
  173. CONNECTION_ID()
  174. GET_LOCK()
  175. IS_FREE_LOCK()
  176. IS_USED_LOCK()
  177. RELEASE_LOCK()
  178. UUID()
  179. VERSION()
  180. 8. Replication Statements and Functions
  181. Merits of Replication
  182. Replication Process
  183. The Replication User Account
  184. Configuring the Servers
  185. Copying Databases and Starting Replication
  186. Starting Replication
  187. Backups with Replication
  188. SQL Statements and Functions in Alphabetical Order
  189. CHANGE MASTER TO
  190. LOAD DATA FROM MASTER
  191. LOAD TABLE...FROM MASTER
  192. MASTER_POS_WAIT()
  193. PURGE MASTER LOGS
  194. RESET MASTER
  195. RESET SLAVE
  196. SET GLOBAL SQL_SLAVE_SKIP_COUNTER
  197. SET SQL_LOG_BIN
  198. SHOW BINLOG EVENTS
  199. SHOW BINARY LOGS
  200. SHOW MASTER LOGS
  201. SHOW MASTER STATUS
  202. SHOW SLAVE HOSTS
  203. SHOW SLAVE STATUS
  204. START SLAVE
  205. STOP SLAVE
  206. Replication States
  207. 9. Stored Routines Statements
  208. Statements in Alphabetical Order
  209. ALTER EVENT
  210. ALTER FUNCTION
  211. ALTER PROCEDURE
  212. ALTER TRIGGER
  213. BEGIN...END
  214. CALL
  215. CLOSE
  216. CREATE EVENT
  217. CREATE FUNCTION
  218. CREATE PROCEDURE
  219. CREATE TRIGGER
  220. DECLARE
  221. DELIMITER
  222. DROP EVENT
  223. DROP FUNCTION
  224. DROP PREPARE
  225. DROP PROCEDURE
  226. DROP TRIGGER
  227. EXECUTE
  228. FETCH
  229. OPEN
  230. PREPARE
  231. SHOW CREATE EVENT
  232. SHOW CREATE FUNCTION
  233. SHOW CREATE PROCEDURE
  234. SHOW EVENTS
  235. SHOW FUNCTION CODE
  236. SHOW FUNCTION STATUS
  237. SHOW PROCEDURE CODE
  238. SHOW PROCEDURE STATUS
  239. SHOW TRIGGERS
  240. 10. Aggregate Clauses, Aggregate Functions, and Subqueries
  241. Aggregate Functions in Alphabetical Order
  242. AVG()
  243. BIT_AND()
  244. BIT_OR()
  245. BIT_XOR()
  246. COUNT()
  247. GROUP_CONCAT()
  248. MAX()
  249. MIN()
  250. STD()
  251. STDDEV()
  252. STDDEV_POP()
  253. STDDEV_SAMP()
  254. SUM()
  255. VAR_POP()
  256. VAR_SAMP()
  257. VARIANCE()
  258. Subqueries
  259. 11. String Functions
  260. String Functions Grouped by Type
  261. String Functions in Alphabetical Order
  262. ASCII()
  263. BIN()
  264. BINARY
  265. BIT_LENGTH()
  266. CAST()
  267. CHAR()
  268. CHAR_LENGTH()
  269. CHARACTER_LENGTH()
  270. CHARSET()
  271. COALESCE()
  272. COERCIBILITY()
  273. COLLATION()
  274. COMPRESS()
  275. CONCAT()
  276. CONCAT_WS()
  277. CONVERT()
  278. CRC32()
  279. ELT()
  280. EXPORT_SET()
  281. FIELD()
  282. FIND_IN_SET()
  283. HEX()
  284. INSERT()
  285. INSTR()
  286. INTERVAL()
  287. LCASE()
  288. LEFT()
  289. LENGTH()
  290. LOAD_FILE()
  291. LOCATE()
  292. LOWER()
  293. LPAD()
  294. LTRIM()
  295. MAKE_SET()
  296. MATCH() AGAINST()
  297. MID()
  298. OCTET_LENGTH()
  299. ORD()
  300. POSITION()
  301. QUOTE()
  302. REPEAT()
  303. REPLACE()
  304. REVERSE()
  305. RIGHT()
  306. RPAD()
  307. RTRIM()
  308. SOUNDEX()
  309. SPACE()
  310. STRCMP()
  311. SUBSTR()
  312. SUBSTRING()
  313. SUBSTRING_INDEX()
  314. TRIM()
  315. UCASE()
  316. UNCOMPRESS()
  317. UNCOMPRESSED_LENGTH()
  318. UNHEX()
  319. UPPER()
  320. 12. Date and Time Functions
  321. Date and Time Functions Grouped by Type
  322. Date and Time Functions in Alphabetical Order
  323. ADDDATE()
  324. ADDTIME()
  325. CONVERT_TZ()
  326. CURDATE()
  327. CURRENT_DATE()
  328. CURRENT_TIME()
  329. CURRENT_TIMESTAMP()
  330. CURTIME()
  331. DATE()
  332. DATE_ADD()
  333. DATE_FORMAT()
  334. DATE_SUB()
  335. DATEDIFF()
  336. DAY()
  337. DAYNAME()
  338. DAYOFMONTH()
  339. DAYOFWEEK()
  340. DAYOFYEAR()
  341. EXTRACT()
  342. FROM_DAYS()
  343. FROM_UNIXTIME()
  344. GET_FORMAT()
  345. HOUR()
  346. LAST_DAY()
  347. LOCALTIME()
  348. LOCALTIMESTAMP()
  349. MAKEDATE()
  350. MAKETIME()
  351. MICROSECOND()
  352. MINUTE()
  353. MONTH()
  354. MONTHNAME()
  355. NOW()
  356. PERIOD_ADD()
  357. PERIOD_DIFF()
  358. QUARTER()
  359. SEC_TO_TIME()
  360. SECOND()
  361. SLEEP()
  362. STR_TO_DATE()
  363. SUBDATE()
  364. SUBTIME()
  365. SYSDATE()
  366. TIME()
  367. TIME_FORMAT()
  368. TIME_TO_SEC()
  369. TIMEDIFF()
  370. TIMESTAMP()
  371. TIMESTAMPADD()
  372. TIMESTAMPDIFF()
  373. TO_DAYS()
  374. UNIX_TIMESTAMP()
  375. UTC_DATE()
  376. UTC_TIME()
  377. UTC_TIMESTAMP()
  378. WEEK()
  379. WEEKDAY()
  380. WEEKOFYEAR()
  381. YEAR()
  382. YEARWEEK()
  383. 13. Mathematical Functions
  384. Functions in Alphabetical Order
  385. ABS()
  386. ACOS()
  387. ASIN()
  388. ATAN()
  389. ATAN2()
  390. BIT_COUNT()
  391. CEIL()
  392. CEILING()
  393. CONV()
  394. COS()
  395. COT()
  396. DEGREES()
  397. EXP()
  398. FLOOR()
  399. FORMAT()
  400. GREATEST()
  401. INET_ATON()
  402. INET_NTOA()
  403. LEAST()
  404. LN()
  405. LOG()
  406. LOG2()
  407. LOG10()
  408. MOD()
  409. OCT()
  410. PI()
  411. POW()
  412. POWER()
  413. RADIANS()
  414. RAND()
  415. ROUND()
  416. SIGN()
  417. SIN()
  418. SQRT()
  419. TAN()
  420. TRUNCATE()
  421. 14. Flow Control Functions
  422. Functions in Alphabetical Order
  423. CASE
  424. IF()
  425. IFNULL()
  426. ISNULL()
  427. NULLIF()
  428. III. MySQL Server and Client Tools
  429. 15. MySQL Server and Client
  430. mysql Client
  431. mysql
  432. mysqld Server
  433. mysqld
  434. mysqld_multi
  435. mysqld_multi
  436. mysqld_safe
  437. mysqld_safe
  438. 16. Command-Line Utilities
  439. comp_err
  440. make_binary_distribution
  441. msql2mysql
  442. my_print_defaults
  443. myisam_ftdump
  444. myisamchk
  445. myisamlog
  446. myisampack
  447. mysql_convert_table_format
  448. mysql_find_rows
  449. mysql_fix_extensions
  450. mysql_fix_privilege_tables
  451. mysql_setpermission
  452. mysql_tableinfo
  453. mysql_upgrade
  454. mysql_waitpid
  455. mysql_zap
  456. mysqlaccess
  457. mysqladmin
  458. mysqlbinlog
  459. mysqlbug
  460. mysqlcheck
  461. mysqldump
  462. mysqldumpslow
  463. mysqlhotcopy
  464. mysqlimport
  465. mysqlshow
  466. mysqlslap
  467. perror
  468. replace
  469. resolveip
  470. resolve_stack_dump
  471. IV. APIs and Connectors
  472. 17. C API
  473. Using C with MySQL
  474. Functions in Alphabetical Order
  475. mysql_affected_rows()
  476. mysql_autocommit()
  477. mysql_change_user()
  478. mysql_character_set_name()
  479. mysql_close()
  480. mysql_commit()
  481. mysql_connect()
  482. mysql_create_db()
  483. mysql_data_seek()
  484. mysql_debug()
  485. mysql_drop_db()
  486. mysql_dump_debug_info()
  487. mysql_eof()
  488. mysql_errno()
  489. mysql_error()
  490. mysql_escape_string()
  491. mysql_fetch_field()
  492. mysql_fetch_field_direct()
  493. mysql_fetch_fields()
  494. mysql_fetch_lengths()
  495. mysql_fetch_row()
  496. mysql_field_count()
  497. mysql_field_seek()
  498. mysql_field_tell()
  499. mysql_free_result()
  500. mysql_get_client_info()
  501. mysql_get_character_set_info()
  502. mysql_get_client_version()
  503. mysql_get_host_info()
  504. mysql_get_proto_info()
  505. mysql_get_server_info()
  506. mysql_get_server_version()
  507. mysql_get_ssl_cipher()
  508. mysql_hex_string()
  509. mysql_info()
  510. mysql_init()
  511. mysql_insert_id()
  512. mysql_kill()
  513. mysql_library_end()
  514. mysql_library_init()
  515. mysql_list_dbs()
  516. mysql_list_fields()
  517. mysql_list_processes()
  518. mysql_list_tables()
  519. mysql_more_results()
  520. mysql_next_result()
  521. mysql_num_fields()
  522. mysql_num_rows()
  523. mysql_options()
  524. mysql_ping()
  525. mysql_query()
  526. mysql_real_connect()
  527. mysql_real_escape_string()
  528. mysql_real_query()
  529. mysql_reload()
  530. mysql_refresh()
  531. mysql_rollback()
  532. mysql_row_seek()
  533. mysql_row_tell()
  534. mysql_select_db()
  535. mysql_set_character_set()
  536. mysql_set_local_infile_default()
  537. mysql_set_local_infile_handler()
  538. mysql_set_server_option()
  539. mysql_shutdown()
  540. mysql_sqlstate()
  541. mysql_ssl_set()
  542. mysql_stat()
  543. mysql_store_result()
  544. mysql_thread_end()
  545. mysql_thread_id()
  546. mysql_thread_init()
  547. mysql_thread_safe()
  548. mysql_use_result()
  549. mysql_warning_count()
  550. C API Datatypes
  551. 18. Perl API
  552. Using Perl DBI with MySQL
  553. Perl DBI Reference
  554. available_drivers()
  555. begin_work()
  556. bind_col()
  557. bind_columns()
  558. bind_param()
  559. bind_param_array()
  560. bind_param_inout()
  561. can()
  562. clone()
  563. column_info()
  564. commit()
  565. connect()
  566. connect_cached()
  567. data_diff()
  568. data_sources()
  569. data_string_desc()
  570. data_string_diff()
  571. disconnect()
  572. do()
  573. dump_results()
  574. err()
  575. errstr()
  576. execute()
  577. execute_array()
  578. execute_for_fetch()
  579. fetch()
  580. fetchall_arrayref()
  581. fetchall_hashref()
  582. fetchrow_array()
  583. fetchrow_arrayref()
  584. fetchrow_hashref()
  585. finish()
  586. foreign_key_info()
  587. func()
  588. get_info()
  589. installed_drivers()
  590. installed_versions()
  591. last_insert_id()
  592. looks_like_number()
  593. neat()
  594. neat_list()
  595. parse_dsn()
  596. parse_trace_flag()
  597. parse_trace_flags()
  598. ping()
  599. prepare()
  600. prepare_cached()
  601. primary_key()
  602. primary_key_info()
  603. private_attribute_info()
  604. quote()
  605. quote_identifier()
  606. rollback()
  607. rows()
  608. selectall_arrayref()
  609. selectall_hashref()
  610. selectcol_arrayref()
  611. selectrow_array()
  612. selectrow_arrayref()
  613. selectrow_hashref()
  614. set_err()
  615. state()
  616. statistics_info()
  617. swap_inner_handle()
  618. table_info()
  619. tables()
  620. take_imp_data()
  621. trace()
  622. trace_msg()
  623. type_info()
  624. type_info_all()
  625. Attributes for Handles
  626. 19. PHP API
  627. Using PHP with MySQL
  628. PHP MySQL Functions in Alphabetical Order
  629. mysql_affected_rows()
  630. mysql_change_user()
  631. mysql_client_encoding()
  632. mysql_close()
  633. mysql_connect()
  634. mysql_create_db()
  635. mysql_data_seek()
  636. mysql_db_name()
  637. mysql_db_query()
  638. mysql_drop_db()
  639. mysql_errno()
  640. mysql_error()
  641. mysql_escape_string()
  642. mysql_fetch_array()
  643. mysql_fetch_assoc()
  644. mysql_fetch_field()
  645. mysql_fetch_lengths()
  646. mysql_fetch_object()
  647. mysql_fetch_row()
  648. mysql_field_flags()
  649. mysql_field_len()
  650. mysql_field_name()
  651. mysql_field_seek()
  652. mysql_field_table()
  653. mysql_field_type()
  654. mysql_free_result()
  655. mysql_get_client_info()
  656. mysql_get_host_info()
  657. mysql_get_proto_info()
  658. mysql_get_server_info()
  659. mysql_info()
  660. mysql_insert_id()
  661. mysql_list_dbs()
  662. mysql_list_fields()
  663. mysql_list_processes()
  664. mysql_list_tables()
  665. mysql_num_fields()
  666. mysql_num_rows()
  667. mysql_pconnect()
  668. mysql_ping()
  669. mysql_query()
  670. mysql_real_escape_string()
  671. mysql_result()
  672. mysql_select_db()
  673. mysql_set_charset()
  674. mysql_stat()
  675. mysql_tablename()
  676. mysql_thread_id()
  677. mysql_unbuffered_query()
  678. V. Appendixes
  679. A. Data Types
  680. Numeric Data Types
  681. BIT
  682. TINYINT
  683. BOOL, BOOLEAN
  684. SMALLINT
  685. MEDIUMINT
  686. INT, INTEGER
  687. BIGINT, SERIAL
  688. FLOAT
  689. DOUBLE, DOUBLE PRECISION
  690. DEC, DECIMAL, FIXED, NUMERIC
  691. Date and Time Data Types
  692. String Data Types
  693. CHAR
  694. VARCHAR
  695. BINARY
  696. VARBINARY
  697. TINYBLOB
  698. TINYTEXT
  699. BLOB
  700. TEXT
  701. MEDIUMBLOB
  702. MEDIUMTEXT
  703. LONGBLOB
  704. LONGTEXT
  705. ENUM
  706. SET
  707. B. Operators
  708. Arithmetic Operators
  709. Relational Operators
  710. Logical Operators
  711. Bitwise Operators
  712. Regular Expressions
  713. C. Server and Environment Variables
  714. Index
  715. About the Author
  716. Colophon
  717. Copyright

Chapter 10. Aggregate Clauses, Aggregate Functions, and Subqueries

MySQL has many built-in functions that you can use in SQL statements for performing calculations on combinations of values in databases; these are called aggregate functions. They include such types of basic statistical analysis as counting rows, determining the average of a given column’s value, finding the standard deviation, and so forth. The first section of this chapter describes MySQL aggregate functions and includes examples of most of them. The second section provides a tutorial about subqueries. It includes several examples of subqueries in addition to the ones shown in the first section and in various examples throughout this book. Subqueries are included in this chapter because they are often used with GROUP BY and aggregate functions and because they’re another method for grouping selected data.

The following functions are covered in this chapter:

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