CPE 595 Web Application Development MySQL I

1
CPE 595Web Application DevelopmentMySQL I
2
Today

• Lecture PHP II MySQL I lecture (was scheduled to
  be PHP III, but weve made good progress so lets
  move on ahead)

3
MySQL I Introduction, Setup, Syntax Lecture
4
Assumptions

• Everyone in the class has had exposure to
• SQL-style databases in the class prereqs
• Terms like table, primary key, relational,
  and row are all familiar if not, review your
  notes from previous classes and pay special
  attention to the database-oriented chapters in
  the reading

5
SQL?

• SQL stands for structured query language
• Many different implementations exist SQL is
• the standard that these implementations are based
  on
• Because SQL is an open standard, the core syntax
  is very similar from implementation to
  implementation
• Variations between implementations typically
  exist in the more advanced functions

6
Properties of SQL

• Is a baseline ANSI standard for databases
• Applies to relational databases
• Defines general parameters for
• functionality basic SQL statements in one
  environment should work in others, so if you
  learned Microsoft SQL in Access most of what you
  know applies here

7
Properties of MySQL

• Open source database, distributed for free,
  controlled and maintained by MySQL AG
• Robust and reliable database platform
• Runs on all major server platforms can be
  installed on Windows, already installed on OS X,
  most Linux distributions
• Is powerful and easy to use
• Is arguably not really enterprise level in terms
  of scalability, advanced functions, manageability

8
MySQL on our server

• Is running as a separate process (application) on
  the server
• Other applications talk to the database via
  built-in or added-in APIs youll rarely work
  with the application directly
• PHP has built-in commands and functionality to
  interact with MySQL

9
SQL in the LAMP stack

• MySQL is not the only SQL-compliant database
  available for use in the LAMP stack, theres also
  SQLite, mSQL, PostgreSQL, and probably others
• Proprietary databases (such as Oracle) can be
  used too

10
Administering MySQL
http//www.isqa419.sba.pdx.edu/phpMyAdmin-2.6.1/
We have a web app called phpMyAdmin already
installed on the server you have an account on
it already. This is how you can interact
directly with the database when building your
PHP-based apps
11
Core MySQL syntax

• INSERT adds row(s) to a table
• UPDATE modifies row(s) in a table
• DELETE removes row(s) in a table
• SELECT returns row(s) in a table
• This should all be familiar!

12
Examples
SELECT statement SELECT FROM tblUsers WHERE
isAdminFld 1
13
Examples
INSERT statement INSERT INTO tblUsers VALUES
(, Thao, secret_password, 1) Alternate
(and my preferred) syntax INSERT INTO
tblUsers (usrName, usrPasswd,isAdminFld)
VALUES (thao, secret_password, 1)
14
Examples
UPDATE statement UPDATE tblUsers SET isAdminFld
1 WHERE usrName Thao
15
Examples
DELETE statement DELETE FROM tblUsers WHERE
usrName Thao
16
But watch what you do
Syntax is OK so whats wrong with this
statement? DELETE FROM tblUsers WHERE usrName
Thao
17
But watch what you do

• Syntax is OK so whats wrong with this
  statement?
• DELETE FROM tblUsers WHERE usrName Thao
• MySQL might return 45 rows deleted. I would
  suggest the use of LIMIT clauses, and basing
  DELETES off explicit matches with unique keys.

18
This should all lookfamiliar

• Anything that isnt familiar?
• Note that a lot of the more complex SQL syntax
  that you may have learned before (LEFT INNER
  JOIN and so on) is usually supported, but for
  this class were using a very simple subset of
  standard SQL syntax

19
Similarly

• You may be used to working with complex
  multi-table database applications its not
  unusual to have dozens of tables in a database.
  MySQL completely supports this.
• For this class, most (if not all) of your
  applications will be driven by a single table

20
Normalization?

• Should be a familiar concept from your previous
  database classes just defines some benchmarks
  for proper database design
• Check out the Wikipedia article on database
  normalization for further explanation
• Stuff like this is why DBAs have job security

21
Using phpMyAdmin
22
phpMyAdmin intro

• On-screen demo accessing, creating tables,
  running various queries
• Theres a lot of advanced functionality in there
  just remember that you cant (easily) break
  anything on the server, but you CAN easily delete
  all your data, so do watch what youre doing.

23
using phpMyAdmin

• Notice that any time you use phpMyAdmin it will
  display the queries it runs against the
  databases. USE THIS! Its a great way to figure
  out the query syntax youll need to call from PHP
  later on.

24
Tying it all together

• Database lives on the server/back end and is
  controlled (via SQL queries) sent by PHP
• PHP is where the application logic lives its
  where youll build queries (concatenation), send
  them to MySQL, and handle result sets (such as
  displaying table rows)
• Again, were doing this in a very simple way.
  More complex applications would probably need a
  more sophisticated implementation.

25
Similarly

• You may be used to working with complex
  multi-table database applications its not
  unusual to have dozens of tables in a database.
  MySQL completely supports this.
• For this class, most (if not all) of your
  applications will be driven by a single table

26
Questions?