How to use MySQL to work with a database

indexadjustmentInternet και Εφαρμογές Web

13 Νοε 2013 (πριν από 3 χρόνια και 11 μήνες)

99 εμφανίσεις

Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
1

Chapter 10
How to use
MySQL to
work with a database
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
2

Objectives
Applied


Use the
mysql program to run commands, scripts, and DDL
statements.


Code simple SELECT, INSERT, UPDATE, and DELETE
statements, and use the
mysql program to run them.
Knowledge


Distinguish between SQL’s Data Definition Language and Data
Manipulation Language.


Describe the capabilities of a SELECT statement.


Describe the capabilities of INSERT, UPDATE, and DELETE
statements.


Describe the use of a SQL script.
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
3

MySQL is…


Inexpensive.

MySQL is free for most uses and relatively
inexpensive for other uses.


Fast.
By many accounts,
MySQL is one of the fastest relational
databases
that’s currently available.


Easy to use.
Compared to other database systems,
MySQL is easy
to install and use.


Portable.

MySQL runs on most modern operating systems
including Windows,
Unix,
Solaris, and OS/2.
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
4

MySQL provides…


Support for SQL.
Like any modern database product,
MySQL
supports SQL, which is the standard language for working with
data that’s stored in relational databases.


Support for multiple clients.

MySQL supports access from
multiple clients from a variety of interfaces and programming
languages including Java,
Perl, PHP, Python, and C.


Connectivity.

MySQL can provide access to data via an
intranet
or the Internet.


Security.

MySQL can protect access to your data so only
authorized users can view the data.
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
5

MySQL doesn’t provide…


Referential integrity.
Unlike Oracle or Microsoft SQL Server,
standard
MySQL tables don’t support declarative referential
integrity (DRI). However, referential integrity can be maintained
by using SQL triggers or by the applications that maintain the
data in the database.


Subqueries.
Although
MySQL doesn’t support SQL
subqueries,
the same results can often be achieved by writing a SQL
statement in a different way or by using a combination of Java
code and SQL code.


Transaction processing.
Unlike Oracle or Microsoft SQL
Server,
MySQL doesn’t support transaction processing, which
can prevent corruption of the database in the event of a system
failure. For most web applications, however, this lack of support
isn’t necessary or can be duplicated by other means.
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
6

A command-line interface
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
7

An HTML interface
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
8

The icon for the MySQL server
MySQL icon

Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
9

How to start the
MySQL server and display the
MySQL icon


On most systems, the
MySQL server starts every time you start
your computer.


If the
MySQL server doesn’t start when you start your computer,
you can start the
MySQL server by running the
winmysqladmin.exe file in
MySQL’s bin directory.


On some systems, the
MySQL icon is displayed in the tray as
shown above. On other systems, you must run the
winmysqladmin.exe file to display the icon.
How to stop the
MySQL server


To stop the server, you can click on the
MySQL icon. Then, you
can select the “Win 9x” and “
ShutDown this Server” commands,
or you can select the “Win NT” and “Stop this Service”
commands.
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
10

The
mysql program
How to start the
mysql program
The syntax
mysql –h
hostname
–u
username
–p
Examples
c:\mysql\bin>mysql –h murach.com –u
jmurach –p
c:\mysql\bin>mysql –u
jmurach -p
c:\mysql\bin>mysql
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
11

How to exit the
mysql program
The syntax
exit | quit
Example
mysql>exit
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
12

How to create a database
mysql> create database
murach;
Query OK, 1 row affected (0.06 sec)
How to list the names of all databases
mysql> show databases;
+------------+
| Database |
+------------+
|
murach |
|
msql |
+------------+
3 rows in set (0.00 sec)
How to select a database for use
mysql> use
murach;
Database changed
How to delete a database
mysql> drop database
murach;
Query OK, 3 rows affected (0.00 sec)
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
13

How to create a table using the
mysql program
mysql> create table User (
->
UserID
int not null
auto_increment,
->
FirstName
varchar
(50),
->
LastName
varchar
(50),
->
EmailAddress
varchar
(50),
->
primary key(
UserID)

-> );
Query OK, 0 rows affected (0.05 sec)
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
14

How to create a table using a SQL script
A
mysql command that runs a script
C:\mysql\bin>mysql -u
jmurach -p
murach <
c:\murach\scripts\UserCreate.sql
Enter password: ******
The SQL script that’s stored in a file named
UserCreate.sql
CREATE TABLE User (

UserID INT NOT NULL AUTO_INCREMENT,

FirstName
VARCHAR(50),

LastName
VARCHAR(50),

EmailAddress
VARCHAR(50),
PRIMARY
KEY(
UserID)
)
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
15

How to list all of the tables in a database
mysql> show tables;
+------------------+
|
Tables_in_murach |
+------------------+
|
download |
|
user |
+------------------+
2 rows in set (0.00 sec)
How to delete a table
mysql> drop table User;
Query OK, 0 rows affected (0.00 sec)
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
16

How to work with databases and tables


A
relational database
consists of one or more
tables
that consist
of
rows
(
records
) and
columns
(
fields
).
These tables are related by
keys.


The
primary key
in a table is the one that uniquely identifies each
of the rows in the table.


A
foreign key
is used to relate the rows in one table to the rows in
another table.


When you create a table, you define each of its columns and you
identify its primary key.


To define a column, you must supply the name and the data type,
and you can also indicate whether a column accepts default values,
whether it’s automatically generated for new rows, and so on.
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
17

How to use the INSERT statement
With the
mysql program
mysql> insert into User
-> (
FirstName,
LastName,
EmailAddress)
->
values
-> ('John', 'Smith', 'jsmith@hotmail.com'),
-> ('Andrea', '
Steelman', 'andi@murach.com'),
-> ('Joel', 'Murach', 'joelmurach@yahoo.com');
Query OK, 3 rows affected (0.06 sec)
In a SQL script stored in a file named
UserInsert.sql
INSERT INTO User
(
FirstName,
LastName,
EmailAddress)
VALUES
('John', 'Smith', 'jsmith@hotmail.com'),
('Andrea', '
Steelman', 'andi@murach.com'),
('Joel', 'Murach', 'joelmurach@yahoo.com')
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
18

How to use the INSERT statement (continued)
A
Mysql command that runs the SQL script
C:\mysql\bin>mysql -u
jmurach -p
murach <
c:\murach\scripts\UserInsert.sql
Enter password: ******
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
19

How to use the Load command
A tab-delimited text file that’s stored in Users.txt
1
John
Smith
jsmith@hotmail.com
2
Andi
Lewis
andi@murach.com
3
Joel
Murach
joelmurach@yahoo.com
A Load command that loads the data into a local table
mysql> load data local
infile
"
c:/
murach/scripts/Users.txt" into table User;
Query OK, 3 rows affected (0.00 sec)
Records:
3 Deleted: 0 Skipped: 0 Warnings: 0
A Load command that loads the data into a table on a
remote
Unix server
load data
infile
"/
usr/local/
etc/
httpd/sites/murach.com/htdocs/Users.txt"
into table User
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
20

A configuration file


If you create a configuration file for
MySQL, you don’t have to
enter the user, password, and host parameters each time you start
the
mysql program.


On a Windows system, the
my.cnf file should be in the c:\
directory.
A c:\my.cnf configuration file
[
client]
user
=jsmith
password
=sesame
host
=localhost
How to start the
mysql program with a
my.cnf file
mysql
databasename <
sqlscriptpath
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
21

Three ways to start the
mysql program using the
my.cnf file
Start the
mysql program only
c:\mysql\bin>mysql
Start the
mysql program and select the
murach database
c:\mysql\bin>mysql
murach
Start the
mysql program, select the
murach database, and
run a script
c:\mysql\bin>mysql
murach <
c:\murach\scripts\UserCreate.sql
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
22

The syntax for a SELECT statement that gets all
columns
SELECT *
FROM table-1
[WHERE selection-criteria]
[ORDER BY field-1 [ASC|DESC] [, field-2 [ASC|DESC
] ...]]
Example
SELECT * FROM User
Result set
+--------+-----------+----------+----------------------+
|
UserID |
FirstName |
LastName |
EmailAddress |
+--------+-----------+----------+----------------------+
| 1 | John | Smith | jsmith@hotmail.com |
| 2 | Andrea |
Steelman | andi@murach.com |
| 3 | Joel | Murach | joelmurach@yahoo.com |
+--------+-----------+----------+----------------------+
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
23

The syntax for a SELECT statement that gets
selected columns
SELECT field-1 [, field-2] ...
FROM table-1
[WHERE selection-criteria]
[ORDER BY field-1 [ASC|DESC] [, field-2 [ASC|DESC
] ...]]
Example
SELECT
FirstName,
LastName
FROM User
WHERE
UserID < 3
ORDER BY
LastName ASC
Result set
+-----------+----------+
|
FirstName |
LastName |
+-----------+----------+
| John | Smith |
| Andrea |
Steelman |
+-----------+----------+
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
24

The syntax for a SELECT statement that joins two
tables
SELECT field-1 [, field-2] ...
FROM table-1
{INNER | LEFT OUTER | RIGHT OUTER} JOIN table-2
ON table-1.field-1 {=|<|>|<=|>=|<>} table-2.field-2
[WHERE selection-criteria]
[ORDER BY field-1 [ASC|DESC] [, field-2 [ASC|DESC] ...]]
A statement that gets data from related User and
Download tables
SELECT
EmailAddress,
DownloadFilename,
DownloadDate
FROM User
INNER JOIN Download
ON
User.UserID =
Download.UserID
WHERE
DownloadDate > '2002-08-01'
ORDER BY
EmailAddress ASC
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
25

Result set
+-----------------+--------------+----------------------+
|
EmailAddress |
DownloadFile |
DownloadDate |
+-----------------+--------------+----------------------+
|
andi@murach.com |
filter.rm | 2002-08-02 18:31:46 |
|
andi@murach.com |
so_long.rm | 2002-08-02 18:31:46 |
|
andi@murach.com |
corvair.rm | 2002-08-02 18:31:46 |
|
joel@yahoo.com | filter.mp3 | 2002-08-02 18:31:46 |
+-----------------+--------------+----------------------+
4 rows in set (0.05 sec)
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
26

How to select data from multiple tables


To return a result set that contains data from two tables, you
join
the tables. To do that, you can use a JOIN clause.


Most of the time, you’ll want to code an
inner join
so that rows are
only included when the key of a row in the first table matches the
key of a row in the second table.


In a
left outer join
, the data for all of the rows in the first table (the
one on the left) are included in the table, but only the data for
matching rows in the second table are included. In a
right outer
join
, the reverse is true.
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
27

The syntax for the INSERT statement
INSERT INTO table-name [(field-list)]
VALUES (value-list)
A statement that adds one row to the Download table
INSERT INTO Download (
UserID,
DownloadDate,

DownloadFilename,
ProductCode)
VALUES (1, '2002-12-01', 'jr01-01.mp3', 'jr01')
A statement that uses the
MySQL
Now function
INSERT INTO Download (
UserID,
DownloadDate,

DownloadFilename,
ProductCode)
VALUES (1,
NOW(), 'jr01-01.mp3', 'jr01')
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
28

The syntax for the UPDATE statement
UPDATE table-name
SET expression-1 [, expression-2] ...
WHERE selection-criteria
A statement that updates the
FirstName column in one
row
UPDATE User
SET
FirstName = 'Jack',
WHERE
EmailAddress = 'jsmith@hotmail.com'
A statement that updates the
ProductPrice column in
selected rows
UPDATE Products
SET
ProductPrice = 36.95
WHERE
ProductPrice = 36.50
Java Servlets and JSPCH10

© 2003, Mike Murach & Associates, Inc.


Slide
29

The syntax for the DELETE statement
DELETE FROM table-name
WHERE selection-criteria
A statement that deletes one row
DELETE FROM User WHERE
EmailAddress =
'
jsmith@hotmail.com'
A statement that deletes selected rows
DELETE FROM Download WHERE
DownloadDate < '2002-08-02'