Transcript SQL : a very, very, very short introduction.

Using Database: A very, very
short introduction.
No more play


This camp is not a game-programming camp!
We should try for more serious programming.
With database engine, we can:



Store lots of data
Do complex search over data
And... most important:

Write better games!
Reference

Most part of this note is from Wikipedia.
http://en.wikipedia.org/wiki/SQL
SQL



SQL stands for "Structured Query
Language."
It operates on databases with table
structures.
A few notable commands:




SELECT
INSERT
UPDATE
DELETE
Plan for the day

Morning:


Using SELECT to query data
Afternoon:

Manipulating data with INSERT, UPDATE,
DELETE
Database ~ Tables


A table is a collection of rows.
Each row contains a set of columns, called
fields.
Table structure

Each row (or record) contains a set of fields,
each of which is of a specific data type.
A very simple database


We will use only one table!
It is a movie show time database, from
movieseer. (thanks a lot!)
SELECT

Syntax:


SELECT [ DISTINCT ] <field-list>
FROM <table-list>
[ WHERE <condition> ]
[ ORDER BY <field> ]
What's that mean?




SELECT <field-list> FROM <table-list>;
SELECT <field-list> FROM <table-list> WHERE <condition>;
SELECT DISTINCT <field-list> FROM <table-list> WHERE
<condition>;
SELECT <field-list> FROM <table-list> WHERE <condition>
ORDER BY <field>;
How could we use this fancy SQL
command in C#?

Database Engine with SQL capability


MySQL, Access, SQL Server, etc.
A bunch of objects:



xxxDbConnection
xxxDbCommand
xxxDbDataReader
Connecting to Access


We need to use Microsoft JET
OLEDB.
We use


System.Data.OleDb.OleDbConnection
With the following connection string:

Provider=Microsoft.JET.OLEDB.4.0;
Data source=<filename.mdb>
OleDbCommand

Properties:


CommandText
Example:
cmd.CommandText = "SELECT DISTINCT theater FROM showtime";
OleDbDataReader reader = cmd.ExecuteReader();
while(reader.Read()) {
// your data is in reader["theater"]
}
reader.Close();
More SQL Commands



INSERT
UPDATE
DELETE
INSERT

Syntax:


INSERT INTO
<table> (<field-list>)
VALUES (<value-list>)
Example:

INSERT INTO showtime (movie,theater,time)
VALUES ('The Programmer',
'KAMPANGSAN-SF', #20:00:0#)
UPDATE

Syntax:


UPDATE <table>
SET <field1>=<value1>, <field2>=<value2>,...
WHERE <condition>
Example:

UPDATE showtime
SET movie="Sad City"
WHERE movie = "Sin City";
DELETE

Syntax:


DELETE FROM <table>
WHERE <condition>;
Example:

DELETE FROM showtime
WHERE movie like "Star%";