Transcript Database and SQL

CSC 2720
Building Web Applications
Database and SQL
Database and DBMS
 Database – Structured collection of records or data
 Database Management System (DBMS) –
Specialized software for managing databases
Advantages of using DBMS
 Efficient Data Access
 Support multiple users and Data Security
 Protect access to data in which only authorized users
can access the data
 Concurrent Access
 Support mechanisms to allow multiple users to safely
read/write the data. (e.g., record locking)
 Transaction
 Comprises of multiple indivisible operations
 A transaction must succeed or fail as a complete unit; it
cannot remain in an intermediate state.
Advantages of using DBMS
Web App
Web App
(Java Servlet)
SQL
SQL
(PHP)
DBMS
SQL
Stand alone App
SQL
SQL
(Java / C++ / C )
Web App
(ASP.NET)
Database
Management Tools
 Standardized query language for defining and
manipulating data
 Network accessible
Introduction to Relational Database
 Relational Database – A type of database in which
data are organized into related tables (a.k.a.
relations).
Column / Field / Attribute
Row /
Record
ItemID
Name
Price
Quantity
0123456
Coffee
4.50
100
0222222
Soy Milk
4.40
50
0142562
Tea
5.00
40
 A table has a name.
 A field has a name and a type.
Designing a Table –
Deciding column names and types
 Name





Should contain only letters, digits and underscore
Meaningful
Should not be a reserve word
Should be less than 64 characters
Should assume the name is case sensitive
 Three Primary Types
 Text (or Strings) – CHAR, VARCHAR, etc.
 Numbers – INT, DOUBLE, DECIMAL, etc.
 Dates and Times – TIME, DATE, DATETIME, etc.
Some Common MySQL Types
Type
Size (bytes)
Description
CHAR[Length]
Length
A fixed-length field from 0 to 255
characters long
VARCHAR[Length]
Length + 1 or 2
A variable-length field from 0 to 65535
characters long
TINYINT / SMALLINT /
MEDIUMINT / INT / BIGINT
1/2/
3/4/8
Signed integers
FLOAT / DOUBLE
4/8
Single and Double precision floating point
numbers
DECIMAL[Length, Decimals]
Length + 1 or 2
A DOUBLE stored as a string, allowing for
a fixed decimal point (Length: # of digits,
Decimals: # of decimal places)
DATE
3
In the format YYYY-MM-DD
TIME
3
In the format of HH:MM:SS
DATETIME
8
In the format of
YYYY-MM-DD HH:MM:SS
TIMESTAMP
4
In the format of YYYYMMDDHHMMSS;
acceptable range ends in the year 2037
Additional Field Properties
 NOT NULL
 Every field must be assigned a value or else an error will occur.
 DEFAULT default_value
 If a field is not assigned any value, default_value is assumed.
 AUTO_INCREMENT
 Use an integer that is one more than the current largest integer as
the default value
 UNSIGNED
 A field can only hold non-negative integer.
 The range of positive integers is doubled.
SQL query to create a "Users" table
CREATE TABLE users (
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
pass CHAR(40) NOT NULL,
reg_date DATETIME NOT NULL,
PRIMARY KEY (user_id)
)
 PRIMARY KEY (user_id)
 Indicates that the field user_id is to be used as the
primary key to identify the records in the table
Characteristics of a Table
 A NULL value means no value.
 When defining a table, we can set whether a field can
contain a null value or not.
 Keys are used to identify records in a table.
 A Primary key is a field (or combination of fields) that
uniquely identifies a record within a table.
 A Foreign key is a field (or combination of fields) that
serves as a primary key in another table. It is used to
establish a relationship between a pair of tables.
 Records are not ordered in a table.
Structured Query Language (SQL)
 A language that can be used to build, modify, query,
and manipulate a database
 SQL supported by different DBMS may vary slightly.
 With SQL, you can






Create/delete a database
Create/delete tables in a database
Retrieve data from a database
Insert new records in a database
Delete records from a database
Update records in a database
SQL (con't)
 SQL is not case sensitive
 Multiple statements are separated by semicolon
 Important commands for manipulating data




SELECT - extracts data from a database table
UPDATE - updates data in a database table
DELETE - deletes data from a database table
INSERT INTO - inserts new data into a database table
 Important commands for manipulating table
 CREATE TABLE - creates a new database table
 ALTER TABLE - alters (changes) a database table
 DROP TABLE - deletes a database table
 Examples: SQL Tutorial at W3Schools
 http://www.w3schools.com/sql/default.asp