Transcript ECA 236 - Just Us Two Photography

ECA 236
Open Source Server Side Scripting
MySQL – Inserting Data
Open Source Server Side Scripting
sitename table
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
ECA 236
Open Source Server Side Scripting
2
INSERT
1st
way to INSERT data:
specify
the columns to be used
syntax:
INSERT INTO tablename (column1, column2, column5 )
VALUES( ‘value1’, ‘value2’, ‘value5’ );
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
ECA 236
Open Source Server Side Scripting
3
INSERT
2nd
cont …
way to INSERT data:
do
not specify columns
syntax:
INSERT INTO tablename VALUES ( ‘value1’, NULL, ‘value3’ );
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
ECA 236
Open Source Server Side Scripting
4
INSERT
2nd
cont …
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’ ) ;
ECA 236
Open Source Server Side Scripting
5
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
ECA 236
Open Source Server Side Scripting
6
inserting data into users
to
insert a new row into the users table:
INSERT INTO users (first_name, last_name, email, password,
registration_date) VALUES ('John', 'Lennon',
'[email protected]', PASSWORD('Happin3ss'), NOW( ));
use
the NOW( ) function to insert the current date
information
notice that NOW( ) is not enclosed in quotes
ECA 236
Open Source Server Side Scripting
7
inserting data into users
cont …
INSERT INTO users (first_name, last_name, email, password,
registration_date) VALUES ('John', 'Lennon',
'[email protected]', 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
ECA 236
Open Source Server Side Scripting
8
inserting data into users
to
INSERT several more records:
INSERT INTO users (first_name, last_name, email, password,
registration_date) VALUES ('Paul', 'McCartney',
'[email protected]', PASSWORD('letITbe'), NOW( )),
('George', 'Harrison', '[email protected] ',
PASSWORD('something'), NOW( )), ('Ringo', 'Starr',
'[email protected]', PASSWORD('thisboy'), NOW( ));
ECA 236
Open Source Server Side Scripting
9
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
ECA 236
Open Source Server Side Scripting
10
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-variable=local-infile=0
ECA 236
Open Source Server Side Scripting
11
loading text files into tables
text files should
cont …
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
Spike
email
last_name
first_name
Ivy
[email protected]
password
scraTchY
tabs
ECA 236
Open Source Server Side Scripting
12
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;
ECA 236
Open Source Server Side Scripting
13
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:
LOAD DATA INFILE “c:/data/users_data.txt” INTO TABLE users;
view
table after data has loaded
SELECT * FROM users;
ECA 236
Open Source Server Side Scripting
14
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;
ECA 236
Open Source Server Side Scripting
15
UPDATE
UPDATE
allows you to modify existing records
syntax:
UPDATE table_name SET column = ‘value’;
UPDATE
multiple columns by separating with comma
UPDATE table_name SET column1 = ‘value1’, column2 = ‘value2’;
use
a WHERE clause to identify rows to UPDATE
UPDATE table_name SET column3 = ‘value’ WHERE column1 = ‘value1’;
ECA 236
Open Source Server Side Scripting
16
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 >= 5;
ECA 236
Open Source Server Side Scripting
17
UPDATE
registration_date has been
cont …
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;
ECA 236
Open Source Server Side Scripting
18
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
ECA 236
Open Source Server Side Scripting
19
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
ECA 236
Open Source Server Side Scripting
20
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 > c:/data/mydump.sql
ECA 236
Open Source Server Side Scripting
21
backing up a database
cont …
mysqldump
read
the file back into MySQL using the syntax:
mysql -u root -p sitename < c:/data/mydump.sql
review
MySQL Manual for a long list of options to
use with mysqldump
ECA 236
Open Source Server Side Scripting
22
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
ECA 236
Open Source Server Side Scripting
23