1 / 48

A Guide to MySQL

6. 8. MySQL Special Topics. A Guide to MySQL. Objectives. Import data into a MySQL table Export data from a MySQL table Understand issues that affect data performance Analyze tables. Objectives (continued). Optimize queries Understand and use the MySQL Query Browser

brandic
Download Presentation

A Guide to MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 6 8 MySQL Special Topics A Guide to MySQL

  2. Objectives • Import data into a MySQL table • Export data from a MySQL table • Understand issues that affect data performance • Analyze tables A Guide to MySQL

  3. Objectives (continued) • Optimize queries • Understand and use the MySQL Query Browser • Understand and use the MySQL Administrator A Guide to MySQL

  4. Importing and Exporting Data • Importing: converting data to a MySQL database • Exporting: converting the data in a MySQL database to a file format for use in another program A Guide to MySQL

  5. Importing Data into a Database • Text file: • Contains unformatted data • Comma-delimited • Tab-delimited • LOAD DATA INFILE: • Name of file to import • INTO TABLE (table) A Guide to MySQL

  6. Importing Data into a Database (continued) A Guide to MySQL

  7. Importing Data into a Database (continued) A Guide to MySQL

  8. Importing Data into a Database (continued) A Guide to MySQL

  9. Exporting Data from a Table • SELECT INTO OUTFILE: • SELECT (name of columns to export) • INTO OUTFILE (name of text file) • FROM (table) • Text file: can open in any word-processing program or text editor A Guide to MySQL

  10. Exporting Data from a Table (continued) A Guide to MySQL

  11. Database Performance Issues • Database performance: speed or rate with which the DBMS supplies information • Optimizer: built-in program or routine A Guide to MySQL

  12. Optimizing the Tables in a Database • Efficient table designs: • Smallest possible size for columns • Eliminate unnecessary columns • Eliminate unnecessary tables • ANALYZE TABLE command: creates statistics for optimizer A Guide to MySQL

  13. Optimizing the Tables in a Database (continued) A Guide to MySQL

  14. Optimizing the Tables in a Database (continued) A Guide to MySQL

  15. Optimizing the Tables in a Database (continued) A Guide to MySQL

  16. Optimizing the Queries in a Database • EXPLAIN command: • Evaluates performance before query is executed • Precedes normal SELECT statement • Use output to determine if indexes needed A Guide to MySQL

  17. Optimizing the Queries in a Database (continued) A Guide to MySQL

  18. Optimizing the Queries in a Database (continued) • PROCEDURE ANALYSE(): • Analyzes query results • Suggests optimal data type • Place at the end of SELECT statement A Guide to MySQL

  19. Optimizing the Queries in a Database (continued) A Guide to MySQL

  20. Using the MySQL Query Browser • Separate program: • Create and execute queries using graphical user interface • Download from http://dev.mysql.com/downloads/ A Guide to MySQL

  21. Starting and Using the Query Browser • Depends on operating environment • Must enter: • Server host information • Username and password • Default database (schema) A Guide to MySQL

  22. Starting and Using the Query Browser (continued) A Guide to MySQL

  23. Query Browser Window • Query Area • Result Area • Object Browser: Schemata, Bookmarks, History • Information Browser: Syntax, Functions, Params, Trx A Guide to MySQL

  24. Starting and Using the Query Browser (continued) A Guide to MySQL

  25. Getting Help in the Query Browser • Help command on Help menu • Displays window with list of topics • Click topics to learn more A Guide to MySQL

  26. Using the Database Browser • Can set default database • Select tables and columns for query • Edit tables A Guide to MySQL

  27. Using the Database Browser (continued) A Guide to MySQL

  28. Using the Syntax Browser • Easy way to learn syntax • Four categories of commands: • Data Manipulation • Data Definition • MySQL Utility • Transactional and Locking A Guide to MySQL

  29. Using the Syntax Browser (continued) A Guide to MySQL

  30. Creating and Executing Queries • Type query in Query Area • Do not need semi-colon • Execute by: • Using Execute command on Query menu • Pressing Ctrl + Enter • Clicking Execute button on Query toolbar • Commands are identical to those in Command Line Client window A Guide to MySQL

  31. Creating and Executing Queries (continued) A Guide to MySQL

  32. Exporting a Resultset • Four file formats: • CSV (comma-separated values) • HTML • XML • Microsoft Excel • Create and execute SELECT query • Use Export Resultset command on File menu A Guide to MySQL

  33. Using the Script Editor • Can create and edit scripts • Use Open Script command on File menu to open a script • Can debug and execute script • Use New Script Tab command on File menu to create a new script A Guide to MySQL

  34. Using the MySQL Table Editor • Can change the structure of tables • Can add foreign keys • Change name, data type, default value, or comment for a column A Guide to MySQL

  35. Using the MySQL Table Editor (continued) A Guide to MySQL

  36. Analyzing Query Performance A Guide to MySQL

  37. Using the MySQL Administrator • Tool for performing administrative operations • Separate program • Download from http://dev.mysql.com/downloads/ A Guide to MySQL

  38. Starting the Administrator • Similar to Query Browser • Can also start from Tools menu in Query Browser • Dialog box requests: • Server host • Username and password A Guide to MySQL

  39. Viewing the Administrator Window • Sidebar has 11 sections • Configure and manage MySQL Server • Manage user privileges • Change startup options A Guide to MySQL

  40. Viewing the Administrator Window (continued) • Monitor database performance • Backup and restore databases • Replicate databases • View catalogs, databases, and tables A Guide to MySQL

  41. Viewing the Administrator Window (continued) A Guide to MySQL

  42. Getting Help in the Administrator • Help command on Help menu • Displays window with list of topics • Click topics to learn more A Guide to MySQL

  43. Backing Up a Database • Database can be damaged or destroyed • Recovery: returns the database to its correct state • Backup copy: periodically making a copy of the database A Guide to MySQL

  44. Backing Up a Database (continued) A Guide to MySQL

  45. Restoring a Database A Guide to MySQL

  46. Maintaining a Database • Use Catalogs section • Create, edit, and maintain tables and indexes • Optimize tables, check tables, and repair tables A Guide to MySQL

  47. Summary • Use LOAD DATA INFILE to import data • Use SELECT INTO OUTFILE to export data • Use ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE to evaluate table performance • Use REPAIR TABLE to repair a damaged or corrupted table A Guide to MySQL

  48. Summary (continued) • Use EXPLAIN and PROCEDURE ANALYSE() to evaluate queries • MySQL Query Browser: • Graphical user interface • Create and execute queries • MySQL Administrator: • Graphical user interface • Perform administrative operations A Guide to MySQL

More Related