1

Introduction to MySQL 

FHWA Resource Center

EPA Office of Transportation and Air Quality

EPA-420-B-09-023
2

2

Why Learn About MySQL?

z Custom inputs

– MOVES has importers for most common types of inputs, but 

some MySQL expertise would be helpful for using specialized 

inputs

z Working with output

– While MOVES can produce summary reports from the GUI, 

MySQL can be used to produce customized output
3

3

MySQL

z Relational Database Management System 

(RDBMS)

z What is a “relational database”?
4

4

Relational Databases 101

z A relational database provides a way of storing, 

querying, and manipulating large amounts of data

z A database contains multiple tables, in which data 

are stored. Each table contains records (rows) and 

fields (columns)

z These tables relate to each other based on values 

in a particular column
5

5

Relational Databases 101

z Example: Excel spreadsheet

– Each “spreadsheet” is like a database

– Each “worksheet” within the spreadsheet is like a table

– However, the rules for database tables are more rigorous
6

6

Relational Database Rules

z Each table is a set of columns and rows that 

describe multiple instances of something

z Each field (column) must contain a unique type of 

data in a specific format 

z Each row represents one unique record (tables 

cannot contain duplicate records)
7

7

Relational Database Rules

z Each table must contain at least one field with 

completely unique values to identify a record (row), 

and so that this record can be related to data in 

other tables (a key)

– Keys are usually numeric for flexibility and speed.

z NULL values are allowed when data are not known 

or not relevant. Zeros are not the same as NULL.

– Keys cannot be NULL
8

8

MOVES Databases

z The MOVES default database has over 100 different 

tables that store

– Lookup/reference information

– Conversion/adjustment factors

– Emissions data

– Activity data

z MOVES also uses databases to store user inputs, 

intermediate results and final output
9

9

Entity-Relationship Diagrams

The symbols on the connecting lines indicate the type of relationship. The 

top fields (above the line) in the boxes are the key fields used in joining 

tables.
10

10

Types of Relationships

z One to one

– Example: for each month there is only one month code, and 

for each month code there is only one month

z One to many

– Example: one model year car can be sold in any of 12 

months, but during any given month only one model year car is 

being sold

z Many to many

– Example: any vehicle type can be of any age (any of the 30 

age bins)
11

11

Structured Query Language

(SQL)

z SQL is the language used to work with modern 

relational databases

– It provides the programming syntax (language) to 

communicate with (query) the database and its tables

z Like English, an SQL statement is made up of 

subjects, verbs, and predicates

– “SELECT * FROM state WHERE stateid = 32” means “Show 

me all the data from the “state” table where the state code in 

the table is 32.”
12

12

MOVES and MySQL

z Draft MOVES2009 uses only MySQL version 5.0.27

– Newer or older versions will not work.

z Support for MySQL is available in the help files, the 

reference manual (C:\MySQL\docs\manual.chm), 

and the MySQL web site (http://www.mysql.com)

z Entity/relationship diagrams for the MOVES default 

database are located at C:\MySQL\data\(MOVES 

default database folder)\readme
13

13

MOVES and MySQL

z Do you need expertise in relational databases and 

MYSQL?

– Not needed for simple runs

– Some basic knowledge gives users flexibility to customize 

outputs and view input databases

– In-house expert would be helpful for more advanced analysis

z MySQL training is available from various 

commercial vendors.
14

14

MySQL Query Browser

z Provided with the MOVES installation package.

z Windows tool for viewing databases, executing 

queries, and editing tables.

z Resultsets can be exported as csv or Excel files.

z Built-in Help files.

z Records query history, so you can repeat queries 

without retyping them.

z Tables can be edited directly, rather than using 

MySQL commands.

– Table must have a key to be edited directly
15

15

MySQL Query Browser

The Browser can be used to display the contents of the tables in a 

spreadsheet-like form.
16

16

Exploring MOVES Databases with 

Query Browser

z We’ll demonstrate some simple MySQL commands 

in Query Browser.

– Commands are shown in the slide headings and screenshots.

– Note that some screenshots show older versions of MOVES 

and/or output databases you won’t have on your computer.

z To Open Query Browser:

– Start/Programs/MySQL/MySQL Query Browser.

– Make sure “localhost” is specified (might not be after initial 

installation) and click OK.

z Click “ignore” on warning message about schema.

z You can suppress this message for future use.
17

17

MySQL Query Browser opening view 

with available databases (example)
18

18

Double-clicking on the MOVES 

default database opens up tables
19

19

DESCRIBE state; 

(or, show columns from state;)

Describe command is used to show you the structure of a table.

You can also right click and edit a table, which will display it’s structure as 

well.
20

20

Highlighting Table + F2 

will also show the table structure.

The F2 function key will show the structure of the table.

You can also right click and edit a table, which will display it’s structure as 

well.
21

21

z INT (± 8388608)

z SMALLINT (± 32767)

z FLOAT(M,D), (default is (10,2))

z VARCHAR (variable length string between 1 & 

255 characters)

z CHAR (fixed length string between 1 & 255 

characters)

Commonly Used 

Field Types in MOVES Databases
22

22

“SELECT” statements in Query 

Browser

z Double-clicking on table names generates a 

“SELECT *” (Select All) statement

z Can also drag and drop from the table listing into 

the results window

z You can include conditions in Select statements 

to filter data or locate individual data elements
23

23

SELECT * FROM state;
24

24

SELECT stateName FROM state;
25

25

SELECT * FROM state where stateAbbr = "KS";
26

26

SELECT COUNT(stateAbbr) FROM state;
27

27

Working with MOVES output

z MySQL commands can be used to sort or sum 

MOVES output by year, month, pollutant, road type, 

etc.

– Can filter results in MySQL and export to Excel

z The Output Emissions Detail options can also be 

used to perform some output aggregation, but 

once applied, the underlying detail is not saved

– MySQL or Excel can be used to aggregate the output after the 

MOVES run, so that the detail in the native MOVES output is 

still available if it’s needed in the future

– In some cases, you need aggregation factors (e.g., travel time 

fractions)—total emissions of a given pollutant are additive, but 

emissions rates need to be weighted
28

28

Working with output: examples

1) Summing emissions output across roadtypes

2) Filtering MOVES lookup output for desired 

emissions rates
29

29

Specifying Detail in Output 

Emissions Detail screen

There is only 1 emission process in Indiana.mrs (running), but emission process is 

selected so that it is identified in the output database rather than being NULL.
30

30

MOVES output tables
31

31

Emissions output table, filtered for 

pollutant, roadtype, emissions

SELECT pollutantid, 

roadtypeid, 

emissionquant

FROM movesoutput;
32

32

Summing emissions across 

road types

SELECT pollutantid, 

sum(emissionquant) FROM 

movesoutput group by 

pollutantid;
33

33

MOVES summary report for the 

same run:
34

34

Other example scripts for summing 

output

z SELECT MOVESRunID, count(*), sum(distance) 

FROM MOVESActivityOutput GROUP BY 

MOVESRunID; 

– (count(*) returns the total number of records summed)

z SELECT MOVESRunID, pollutantID, processID, 

count(*), sum(emissionQuant) FROM MOVESOutput

GROUP BY MOVESRunID, pollutantID, processID; 
35

35

Working with MOVES Lookup 

Output Tables

Participants do not have the database used in the following slides.
36

36

SELECT * FROM moveslookupoutput; 

(returns 128 rows in this example)
37

37

SELECT * FROM moveslookupoutput

where roadtypeID = 2; 

(returns 32 rows)
38

38

SELECT * FROM moveslookupoutput

where roadtypeid=2 and pollutantid=90;

(Returns 16 rows)
39

39

SELECT * FROM moveslookupoutput

where roadtypeID = 2 and pollutantID = 90 

group by avgspeedbinID; 

(now ordered by speed bin)
40

40

SELECT pollutantID, avgspeedbinID, 

emissionrate FROM moveslookupoutput

where roadtypeID = 2 and pollutantID = 90 

group by avgspeedbinID;
41

41

Other handy things Query Browser 

can do

z Export result tables.

z Import data.

z Edit data.

z History function.
42

42

Export tables using the Browser

z One useful way to build input databases is to first 

copy the relevant information from the MOVES 

default database

z Use SELECT and WHERE statements as necessary 

to filter the information and get the values that you 

want

z In Query Browser file menu, select “Export 

Resultset”—can save as .csv, .xls, or other file 

formats

z Then you can work with these values in Excel or 

other programs
43

43

Export resultsets from Browser
44

44

Tip: Copy query statements into exported 

spreadsheets for future reference
45

45

Load data infile

z This statement can be used to import information 

from .csv or .txt (not .xls) files into MySQL tables

z Example script to update age values:

– load data infile 'c:/MOVES/Change/NewAgeDist.csv' 

– Replace into table SourceTypeAgeDistribution

– fields terminated by ','

– ignore 1 lines

– (sourceTypeId,yearId,ageId,ageFraction);
46

46

Editing data from within Query 

Browser

z Cell values in tables with primary keys can also be 

edited from within Query Browser itself, rather than 

importing alternative data:

– Highlight a cell

– Click “Edit” button

– Make any necessary changes

– Click “Apply Changes”

– Click “Edit” button again to close
47

47

Editing data from within Query 

Browser

z MOVES output tables cannot be readily edited this 

way, since they do not have primary keys.

z Don’t edit the default database!!!

– Check the title bar at the top of the Query Browser window to 

make sure that the database you want to edit is active
48

48

Editing data from within Query 

Browser
49

49

Copy default information into new 

database

z You can also copy information from the default database 

directly into another database without first exporting and 

then importing

z This is useful when you want to edit data within Query 

Browser, and don’t have many values to change

z Example: insert into myrun.fuelformulation SELECT * FROM 

MOVESDBDRAFT2009.fuelformulation where 

fuelformulationid = 1061;

In this example, “myrun” is the user input database and 

“MOVESDBDRAFT2009” is the default database (syntax is 

“databasename.tablename”)
50

50

Using Query Browser’s “history”

function

z Query Browser has a history function, so that 

previous statements can be selected and applied, 

rather than retyping them

z Can also pull up a previous query statement and 

then modify it to select the desired data
51

51

Query Browser history function
52

52

Generating local input databases for 

MOVES when GUI options don’t exist

z MOVES has several GUI options for importing 

common types of local inputs (County Data 

Manager, I/M Coverage Editor, etc.)

– These are covered in other parts of the course

z In some cases, non-default local data would need 

to be supplied through additional input databases 

(as opposed to being entered through the GUI)

z Since the tables in these databases must adhere to 

the MOVES default names and formats, it is safer 

(and easier) to base input databases on existing 

tables, rather than creating them from scratch.
53

53

One way: Copying default tables

z Create a new database in MySQL

z In Explorer, locate the files associated with each 

necessary table in the MOVES default database, 

and COPY (don’t MOVE) them into the new 

database folder

z Each table has three associated files (*.frm, *.MYD, 

and *.MYI), all of which must be copied

z These can then be edited using MySQL commands
54

54

Note that all tables have three parts (frm, myd and myi). All three parts must 

be copied.

Use the “flush tables;” command to unlock tables that were in use.
55

55

Creating a Template Database in 

MOVES

z A blank database can be created in MOVES from 

the Manage Input Data Sets screen—this database 

contains all the tables that the default database 

does, with the same format, but no data

z This database can then be populated with 

necessary data
56

56

Creating input databases
57

57

Never, Ever, EVER Modify the 

MOVES Default Database!!!

z To be safe, ALWAYS create a new database in 

MOVES, or copy tables from the MOVES default 

database into a new database, and THEN work on 

the tables

– MOVES will not alert you if you accidentally modify the default 

database

– Any changes you make will be applied to all future MOVES 

runs using this database

– If you modify the default database, it will also make it 

extremely difficult to troubleshoot puzzling model results

z Make a backup copy of the MOVES default 

database, just in case . . .