A Guide to MySQL

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

3
Objectives (continued)

• Optimize queries
• Understand and use the MySQL Query Browser
• Understand and use the MySQL Administrator

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

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)

6
Importing Data into a Database (continued)
7
Importing Data into a Database (continued)
8
Importing Data into a Database (continued)
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

10
Exporting Data from a Table (continued)
11
Database Performance Issues

• Database performance speed or rate with which
  the DBMS supplies information
• Optimizer built-in program or routine

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

13
Optimizing the Tables in a Database (continued)
14
Optimizing the Tables in a Database (continued)
15
Optimizing the Tables in a Database (continued)
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

17
Optimizing the Queries in a Database (continued)
18
Optimizing the Queries in a Database (continued)

• PROCEDURE ANALYSE()
• Analyzes query results
• Suggests optimal data type
• Place at the end of SELECT statement

19
Optimizing the Queries in a Database (continued)
20
Using the MySQL Query Browser

• Separate program
• Create and execute queries using graphical user
  interface
• Download from http//dev.mysql.com/downloads/

21
Starting and Using the Query Browser

• Depends on operating environment
• Must enter
• Server host information
• Username and password
• Default database (schema)

22
Starting and Using the Query Browser (continued)
23
Query Browser Window

• Query Area
• Result Area
• Object Browser Schemata, Bookmarks, History
• Information Browser Syntax, Functions, Params,
  Trx

24
Starting and Using the Query Browser (continued)
25
Getting Help in the Query Browser

• Help command on Help menu
• Displays window with list of topics
• Click topics to learn more

26
Using the Database Browser

• Can set default database
• Select tables and columns for query
• Edit tables

27
Using the Database Browser (continued)
28
Using the Syntax Browser

• Easy way to learn syntax
• Four categories of commands
• Data Manipulation
• Data Definition
• MySQL Utility
• Transactional and Locking

29
Using the Syntax Browser (continued)
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

31
Creating and Executing Queries (continued)
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

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

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

35
Using the MySQL Table Editor (continued)
36
Analyzing Query Performance
37
Using the MySQL Administrator

• Tool for performing administrative operations
• Separate program
• Download from http//dev.mysql.com/downloads/

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

39
Viewing the Administrator Window

• Sidebar has 11 sections
• Configure and manage MySQL Server
• Manage user privileges
• Change startup options

40
Viewing the Administrator Window (continued)

• Monitor database performance
• Backup and restore databases
• Replicate databases
• View catalogs, databases, and tables

41
Viewing the Administrator Window (continued)
42
Getting Help in the Administrator

• Help command on Help menu
• Displays window with list of topics
• Click topics to learn more

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

44
Backing Up a Database (continued)
45
Restoring a Database
46
Maintaining a Database

• Use Catalogs section
• Create, edit, and maintain tables and indexes
• Optimize tables, check tables, and repair tables

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

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