480 likes | 677 Views
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
E N D
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 A Guide to MySQL
Objectives (continued) • Optimize queries • Understand and use the MySQL Query Browser • Understand and use the MySQL Administrator A Guide to MySQL
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
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
Importing Data into a Database (continued) A Guide to MySQL
Importing Data into a Database (continued) A Guide to MySQL
Importing Data into a Database (continued) A Guide to MySQL
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
Exporting Data from a Table (continued) A Guide to MySQL
Database Performance Issues • Database performance: speed or rate with which the DBMS supplies information • Optimizer: built-in program or routine A Guide to MySQL
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
Optimizing the Tables in a Database (continued) A Guide to MySQL
Optimizing the Tables in a Database (continued) A Guide to MySQL
Optimizing the Tables in a Database (continued) A Guide to MySQL
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
Optimizing the Queries in a Database (continued) A Guide to MySQL
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
Optimizing the Queries in a Database (continued) A Guide to MySQL
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
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
Starting and Using the Query Browser (continued) A Guide to MySQL
Query Browser Window • Query Area • Result Area • Object Browser: Schemata, Bookmarks, History • Information Browser: Syntax, Functions, Params, Trx A Guide to MySQL
Starting and Using the Query Browser (continued) A Guide to MySQL
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
Using the Database Browser • Can set default database • Select tables and columns for query • Edit tables A Guide to MySQL
Using the Database Browser (continued) A Guide to MySQL
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
Using the Syntax Browser (continued) A Guide to MySQL
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
Creating and Executing Queries (continued) A Guide to MySQL
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
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
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
Using the MySQL Table Editor (continued) A Guide to MySQL
Analyzing Query Performance A Guide to MySQL
Using the MySQL Administrator • Tool for performing administrative operations • Separate program • Download from http://dev.mysql.com/downloads/ A Guide to MySQL
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
Viewing the Administrator Window • Sidebar has 11 sections • Configure and manage MySQL Server • Manage user privileges • Change startup options A Guide to MySQL
Viewing the Administrator Window (continued) • Monitor database performance • Backup and restore databases • Replicate databases • View catalogs, databases, and tables A Guide to MySQL
Viewing the Administrator Window (continued) A Guide to MySQL
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
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
Backing Up a Database (continued) A Guide to MySQL
Restoring a Database A Guide to MySQL
Maintaining a Database • Use Catalogs section • Create, edit, and maintain tables and indexes • Optimize tables, check tables, and repair tables A Guide to MySQL
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
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