ECA 236

1
ECA 236

• Open Source Server Side Scripting
• MySQL Inserting Data

2
sitename table
users users
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
first_name VARCHAR(15) NOT NULL
last_name VARCHAR(30) NOT NULL
email VARCHAR(40)
password CHAR(16) NOT NULL
registration_date DATETIME NOT NULL
3
INSERT

• 1st way to INSERT data
• specify the columns to be used
• syntax
• using this way, you can add rows of records, but
  populate only the fields you want
• unspecified columns will be treated as NULL or
  given a default value

INSERT INTO tablename (column1, column2, column5
) VALUES( value1, value2,
value5 )
4
INSERT cont

• 2nd way to INSERT data
• do not specify columns
• syntax
• using this way, you include a value for every
  column, even if NULL
• failure to match number of values to number of
  columns will generate an error

INSERT INTO tablename VALUES ( value1, NULL,
value3 )
5
INSERT cont

• 2nd way to INSERT data
• to insert multiple rows, separate each record
  with a comma
• syntax

INSERT INTO tablename VALUES ( value1, NULL,
value3 ), ( value4, NULL, value6 ), (
value7, NULL, value9 )
6
NOW( )

• NOW( ) is a MySQL function
• use NOW( ) with date data types
• NOW( ) inserts the current date and time into a
  column
• do not put a space between the function name and
  the parentheses

7
inserting data into users

• to insert a new row into the users table
• use the NOW( ) function to insert the current
  date information
• notice that NOW( ) is not enclosed in quotes

INSERT INTO users (first_name, last_name, email,
password, registration_date) VALUES
('John', 'Lennon', 'john_at_beatles.com',
PASSWORD('Happin3ss'), NOW( ))
8
inserting data into users cont
INSERT INTO users (first_name, last_name, email,
password, registration_date) VALUES
('John', 'Lennon', 'john_at_beatles.com',
PASSWORD('Happin3ss'), NOW( ))

• we left out the user_id column
• user_id will be set to NULL
• because user_id is AUTO_INCREMENT, MySQL will set
  the value to the next logical number

9
inserting data into users

• to INSERT several more records

INSERT INTO users (first_name, last_name, email,
password, registration_date) VALUES ('Paul',
'McCartney', 'paul_at_beatles.com',
PASSWORD('letITbe'), NOW( )), ('George',
'Harrison', 'george_at_beatles.com ',
PASSWORD('something'), NOW( )), ('Ringo',
'Starr', 'ringo_at_beatles.com', PASSWORD('thisboy'),
NOW( ))
10
inserting data into users

• when using INSERT enclose strings with single
  quotes
• do not quote numbers or function calls
• to INSERT a value that contains a single quote,
  escape the quote with a backslash

11
loading text files into tables

• in some situations you may have to change the
  configuration of the my.ini file
• modify my.ini file, usually located in Windows or
  WINNT directory
• add the following code to the mysqld section

mysqld set-variablelocal-infile0
12
loading text files into tables cont

• text files should contain
• one row for each record to be loaded
• each column separated with a delimiter
• you can specify delimiter and end of line marker
• by default, MySQL uses a tab and linefeed
• NULL values may be represented with \N

email
last_name
password
first_name
Spike Ivy poison_at_ivy.com scraTchY
tabs
13
loading text files into tables cont

• LOAD DATA INFILE
• loads a text file into a table
• for more information visit the MySQL Manual
• 3.3.3 Loading Data into a Table
• 4.2.4 Security issues with LOAD DATA LOCAL
• 6.4.8 LOAD DATA INFILE Syntax

LOAD DATA INFILE path_to/file_name.txt INTO
TABLE table_name
14
loading text files into tables cont

• EXERCISE
• download users_data.txt from web site
• create a folder one level down from C named data
• save users_data.txt
• in mysql monitor
• view table after data has loaded

LOAD DATA INFILE c/data/users_data.txt INTO
TABLE users
SELECT FROM users
15
SELECT INTO OUTFILE

• complements LOAD DATA INFILE
• write data from a database to a file
• defaults
• writes tabs between fields
• writes newlines at end of lines
• when working on Windows, escape backslash

SELECT INTO OUTFILE c\\data\\into_outfile.txt
FROM users
16
UPDATE

• UPDATE allows you to modify existing records
• syntax
• UPDATE multiple columns by separating with comma
• use a WHERE clause to identify rows to UPDATE

UPDATE table_name SET column value
UPDATE table_name SET column1 value1, column2
value2
UPDATE table_name SET column3 value WHERE
column1 value1
17
UPDATE cont

• the users table contains unencrypted passwords
• use PASSWORD( ) function to encrypt current
  unencrypted values in password column
• notice there are not quotes around password being
  passed to PASSWORD( )

UPDATE users SET password PASSWORD(password)
WHERE user_id gt 5
18
UPDATE cont

• registration_date has been set to zero for all
  new entries
• use the WHERE clause to affect only those rows
  with a registration_date set to zero

UPDATE users SET registration_date NOW( ) WHERE
registration_date 0
19
LOCAL

• LOAD DATA LOCAL INFILE
• if the LOCAL keyword is included the file is read
  by the client program on the client machine, and
  sent to the server
• if the LOCAL keyword is not included the file
  must be located on the server
• files to be loaded must be readable by all

20
backing up a database

• mysqldump
• will back up tables and their structure
• run directly from command line
• dump file to the screen

mysqldump -u root -p sitename
21
backing up a database cont

• mysqldump
• create an output file
• contains SQL commands to create the table
• contains data to populate the table

mysqldump -u root -p sitename gt
c/data/mydump.sql
22
backing up a database cont

• mysqldump
• read the file back into MySQL using the syntax
• review MySQL Manual for a long list of options to
  use with mysqldump

mysql -u root -p sitename lt c/data/mydump.sql
23
source

• to run MySQL statements stored in a text file
• use source or \.
• takes the filename as an argument
• do not use quotes or semicolon

source path_to/file_name.txt
\. path_to/file_name.txt
\. C/data/insert_record.sql