Transcript IMS1907 Database Systems

IMS1907 Database Systems
Summer Semester 2004/2005
Lecture 9
Structured Query Language – SQL
Data Definition Language - DDL
Structured Query Language - SQL
Has become de facto language for creating and querying
relational databases
– mainframe and personal database environments
Accepted as a standard
– ANSI, FIPS, ISO
First published in 1986
– updated in 1989, 1992 (SQL-92), 1999 (SQL-99) and
2003 (SQL:2003)
Monash University 2004
2
Structured Query Language - SQL
SQL-99 was a significant extension on SQL-92
– added regular expression matching, recursive queries,
triggers, non-scalar types and some object-oriented
features (the last two are somewhat controversial and not
yet widely supported)
SQL:2003
– introduced XML-related features, standardized
sequences and columns with auto-generated values
(including identity-columns)
Monash University 2004
3
Structured Query Language - SQL
Benefits of a standard relational language
– reduced training costs
– increased productivity
– increased application portability
– extended application longevity
– reduced dependence on single vendor
– enhanced cross-system communication
Monash University 2004
4
Structured Query Language - SQL
Disadvantages of a standard relational language
– stifle creativity and innovation
• standardisation – the natural enemy of variety
– may not meet all user needs
– not ideal as a result of being a set of compromises
between many industry parties
– difficult to change
– vendor features can limit portability and offset other
advantages
Monash University 2004
5
The SQL Environment
Concepts
– catalog
– schema
– data definition language (DDL)
– data manipulation language (DML)
– data control language (DCL)
Monash University 2004
6
The SQL Environment
Catalog
– a set of schemas which, when put together, constitute a
description of a database
– describes any object that is part of a database
– a DBMS may manage many catalogs
Schema
– a structure containing descriptions of objects created by
users
• base tables, views, domains, constraints, character
sets, triggers, roles, …
Monash University 2004
7
The SQL Environment
Data definition language (DDL)
– commands used to define a database
– used to create, change and remove objects from a
database and establish constraints
– generally restricted to one or more DBAs to protect
database from unexpected changes
– DDL statements include
• CREATE, ALTER, DROP
• In MySQL we also need the USE statement
Monash University 2004
8
The SQL Environment
Data manipulation language (DML)
– considered by many as the core SQL commands
– commands used to maintain and query a database
– used to update, insert, change and query data
– can be interactive or embedded in programs
• embedded SQL gives programmer more control over
report timing, interface appearance, error handling
and database security
– some common DML statements include
• INSERT, DELETE, SELECT, SHOW, DESCRIBE
Monash University 2004
9
The SQL Environment
DML allows us to perform the following relational operations
– select
• selects a subset of rows in a table satisfying a
selection criteria
– project
• selects only certain columns from a table
– join
• combines data from two or more tables based on one
or more common values
Monash University 2004
10
The SQL Environment
Data control language (DCL)
– commands used to control a database
– used to administer privileges and the committing of data
– controls granting and revoking of access, and retrieving
and storing transactions that could affect database
– generally restricted to DBAs to protect database from
unexpected changes and unauthorised access
– DCL statements include
• GRANT, REVOKE, LOAD, BACKUP, ROLLBACK
Monash University 2004
11
The SQL Environment – Data Types
Each DBMS has a defined list of data types it can handle
All have equivalents for structured data types
– numeric, string and date/time variables
Many allow for unstructured data types
– graphic, image, spatial
When creating a table, the data type for each attribute must
be specified
Choice of data type depends on
– data values that need to be stored
– expected uses of data
Monash University 2004
12
MySQL Datatypes
MySQL
Length
Oracle
VB6
TINYINT
-128 to 127 characters
integer
TINYINT UNSIGNED
0 to 255 characters
byte
SMALLINT
-32,768 to 32,767 characters
integer
SMALLINT UNSIGNED
0 to 65, 535 characters
MEDIUMINT
-8,388,608 to 8,388,607
characters
NUMBER
long
MEDIUMINT UNSIGNED
0 to 16,777,215 characters
INT
-2,147,483,647 to
2,147,483,647 characters
INT UNSIGNED
0 to 4,294,967,295 characters
double
BIGINT
64 Bit
N/A
Monash University 2004
13
MySQL Datatypes
MySQL
Length
FLOAT
32 bit floating point
DOUBLE
64 bit floating point
DECIMAL
variable floating point
CHAR
1 to 255 characters - fixed
VARCHAR
1 to 255 characters - variable
TINYTEXT
1 to 255 characters - variable
TEXT
1 to 65,535 characters
MEDIUMTEXT
1 to 16,777,215 characters
LONGTEXT
1 to 4,294,967,295 characters
Monash University 2004
Oracle
NUMBER
VB6
single
double
CHAR
VARCHAR2
string
LONG
N/A
14
MySQL Datatypes
MySQL
Length
all BLOB types
1 to 4,294,967,295 bytes
DATE
Date without time
DATETIME
Date and time
TIMESTAMP
Date and time
TIME
Time
YEAR
Year
ENUM
Enumeration of value set
SET
Set of values
Oracle
VB6
LOB
Variant
DATE
Date and
time value
integer
Monash University 2004
ENUM
string
15
Data Definition Language (DDL)
Three DDL CREATE commands are included in SQL-92
and later versions of standard
– CREATE DATABASE (or CREATE SCHEMA in Oracle)
• used to define portion of database owned by user
• contains tables, views, domains, constraints, …
– CREATE TABLE
• defines table and its columns
• dependent on a schema
– CREATE VIEW
• defines logical table from one or more tables or views
Monash University 2004
16
Data Definition Language (DDL)
Other DDL CREATE commands are also included in SQL92 and later versions of standard
– CREATE CHARACTER SET
– CREATE COLLATION
– CREATE TRANSLATION
– CREATE ASSERTION
– CREATE DOMAIN
Other statements such as CREATE INDEX are
performance-related and not part of standard
Monash University 2004
17
Data Definition Language (DDL)
The DROP command is used to reverse CREATE
commands
– DROP DATABASE (or DROP SCHEMA in Oracle)
• removes entire database and related schemas
– DROP TABLE
• removes table and related columns and constraints
from database
– DROP VIEW
• removes logical table from database schema
Other DROP commands can remove keys, indexes, users, ..
Monash University 2004
18
Data Definition Language (DDL)
The ALTER command is used to change objects created
using the CREATE command
– ALTER DATABASE (or ALTER SCHEMA in Oracle)
• changes DB characteristics – character sets, collations
– ALTER TABLE, ALTER COLUMN
• changes table definitions by altering column
specifications
– ALTER VIEW
• changes specifications of logical table
Can include ALTER, ADD, DROP keywords
Monash University 2004
19
Creating a Database
At the mysql> prompt enter the command and the name of
the database you want to create
– mysql> CREATE DATABASE menagerie;
Creating a database in MySQL does not automatically
select it for use
Select a database for use with the use command and the
name of the database you want to use
– mysql> USE menagerie;
Monash University 2004
20
Creating Tables
Once a database has been selected you can create tables
Creating tables requires that you define
– column names
– data types and lengths
– column and table keys and constraints
Although MySQL does not necessarily require you to specify
keys and constraints, they should be defined if known
Monash University 2004
21
Creating Tables
Use the CREATE TABLE statement to define the layout of
the table
CREATE TABLE pet
(name VARCHAR(20), owner VARCHAR(20), species
VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
You can specify more detail about your table by defining
keys and constraints
Monash University 2004
22
Creating Tables
CREATE TABLE person
(person_id SMALLINT UNSIGNED NOT NULL
AUTO_INCREMENT, name VARCHAR(60) NOT NULL,
PRIMARY KEY (person_id));
CREATE TABLE shirt
(shirt_id SMALLINT UNSIGNED NOT NULL
AUTO_INCREMENT, style ENUM('t-shirt', 'polo', 'dress')
NOT NULL, color ENUM('red', 'blue', 'orange', 'white',
'black') NOT NULL, owner SMALLINT UNSIGNED NOT
NULL REFERENCES person(person_id), PRIMARY KEY
(shirt_id));
Monash University 2004
23
Altering Tables
Use the ALTER TABLE statement to re-define the layout of
the table
– add/remove/rename columns, constraints, keys, types, …
ALTER TABLE person
ADD (address VARCHAR(60) NOT NULL);
ALTER TABLE person
MODIFY (name VARCHAR(40) NOT NULL);
Monash University 2004
24
Deleting a Database
Use the DROP DATABASE command to remove a
database and all related tables and schemas
Enter the command and the name of the database
DROP DATABASE menagerie;
You can add the optional keywords IF EXISTS to prevent
errors occurring for non-existent databases
DROP DATABASE IF EXISTS menagerie;
Use this command with caution!
Monash University 2004
25
Deleting a Table
Use the DROP TABLE command to remove a table and all
its related definitions
Enter the command and the name of the table
DROP TABLE pet;
You can add the optional keywords IF EXISTS to prevent
errors occurring for non-existent tables
DROP TABLE IF EXISTS pet;
You can use this command to remove one or more tables
Monash University 2004
26
References
Bordoloi, B. and Bock, D., (2004), Oracle SQL, Pearson
Education Inc., Upper Saddle River, NJ, USA.
Hillyer, M., (2004), Visual Basic / MySQL Datatypes, last
accessed 10th September 2004 at
http://dev.mysql.com/tech-resources/articles/visual-basicdatatypes.html
Hoffer, J.A., Prescott, M.B. and McFadden, F.R., (2005),
Modern Database Management, (7th edn.), Pearson
Education Inc., Upper Saddle River, NJ, USA.
Monash University 2004
27
References
Kroenke, D.M., (2004), Database Processing: Fundamentals,
Design and Implementation, (9th edn.), Pearson Education
Inc., Upper Saddle River, NJ, USA.
MySQL Reference Manual, (2004), last accessed 10th
September 2004 at http://dev.mysql.com/doc/mysql/
Monash University 2004
28