Transcript Chapter 9 SQL and RDBMS

Part C. SQL
Chapter 9 SQL and RDBMS
Copyright 2005 Radian Publishing Co.
Contents
Chapter 9 SQL and RDBMS
9.4 Creating a Table
9.1 What is SQL
• 9.1 A. DDL
• 9.1 B. DML
• 9.1 C. DCL
9.5 Creating a Table with Constraints
9.6 Filling Table with Data
9.7 Other DDL Commands
9.2 SQL and RDBMS
• 9.2 A. MS Access 97, 2003 and
Visual FoxPro
• 9.2 B. MySQL
9.3 Creating a Database
2/26
Copyright 2005 Radian Publishing Co.
Chapter 9 SQL and RDBMS
SQL stands for Structured Query Language.
A relational database management system (RDBMS) fully supports
SQL.
3/26
Copyright 2005 Radian Publishing Co.
9.1 What is SQL
Set-oriented means that SQL processes sets of data in groups.
Non-procedural means that SQL does not care about how the result is
obtained, but it stresses on what result will be obtained.
SQL commands can be classified into three types:
• Data Definition Language (DDL) commands
• Data Manipulation Language (DML) commands
• Data Control Language (DCL) commands
4/26
Copyright 2005 Radian Publishing Co.
9.1 A. DDL
Data Definition Language (DDL) commands are used to create, alter
and delete tables, views and indexes, including
•
•
•
•
•
•
•
•
•
5/26
CREATE DATABASE
DROP DATABASE
CREATE TABLE
DROP TABLE
ALTER TABLE
CREATE INDEX
DROP INDEX
CREATE VIEW
DROP VIEW
Copyright 2005 Radian Publishing Co.
9.1 B. DML
(1/2)
Data Manipulation Language (DML) commands are used to update,
insert, modify and query the data in the database, including
•
•
•
•
6/26
SELECT
INSERT
UPDATE
DELETE
Copyright 2005 Radian Publishing Co.
9.1 B. DML
(2/2)
The reasons for writing programs to issue SQL commands are
1.
2.
3.
4.
7/26
meeting specific needs of an organisation
providing interface for users who do not know SQL
handling errors
enforce database security
Copyright 2005 Radian Publishing Co.
9.1 C. DCL
Data Control Language (DCL) commands help DBA to control and
maintain the database, including GRANT, ALTER USER etc.
DBA stands for database Administrator
(see Chapter 3)
8/26
Copyright 2005 Radian Publishing Co.
9.2 SQL and RDBMS
The SQL standards are promoted by two standards organisations: the
American National Standards Institute (ANSI) and International
Standards Organisation (ISO). In the textbook, ANSI-92 (launched in
1992) will be used.
Proprietary Extensions mean including extension to the standards, like
ANSI-92.
9/26
Copyright 2005 Radian Publishing Co.
9.2 A. MS Access 97, 2003 and Visual FoxPro
MS Access® 97, MS Access® 2003 and MS Visual FoxPro® 5.0 are
software products of Microsoft.
10/26
Copyright 2005 Radian Publishing Co.
9.2 B. MySQL
MySQL is an open source RDBMS product, meaning that it is possible
for anyone to use and modify the software.
MySQL always works in the client-server mode, even in a standalone
computer.
Fig.9.1 Versions of MySQL
11/26
Copyright 2005 Radian Publishing Co.
9.3 Creating a Database
Fig.9.3 Handling of database files by different DBMS
12/26
Copyright 2005 Radian Publishing Co.
(1/2)
9.3 Creating a Database
In MS Access, related tables are stored in a single file.
In Visual FoxPro, database container is optional.
The SQL command to create database:
CREATE DATABASE dBName;
MySQL command to show database:
SHOW DATABASES;
MySQL command to open a database:
USE dBName;
13/26
Copyright 2005 Radian Publishing Co.
(2/2)
9.4 Creating a Table
(1/3)
The basic syntax for creating a new table is
CREATE TABLE TableName1
(FieldName1 FieldType [(FieldWidth [, Precision])] [NOT NULL]
[PRIMARY KEY] [UNIQUE]
[, FieldName2 ...]);
14/26
Copyright 2005 Radian Publishing Co.
9.4 Creating a Table
(2/3)
Fig.9.4 Table to be created
Fig.9.5 Structure of the
table to be created
15/26
Copyright 2005 Radian Publishing Co.
9.4 Creating a Table
(3/3)
The SQL statement for creating the table BRASSITEM
16/26
Copyright 2005 Radian Publishing Co.
9.5 Creating a Table with Constraints
(1/2)
Some DBMS enforces constraints to insure data integrity. The constraints
include
1. the field value is unique (keywords UNIQUE or PRIMARY KEY).
2. the field value is non-empty (keywords NOT NULL or PRIMARY KEY).
3. providing default field value (keyword DEFAULT)
4. generating a number for a numeric field when new record is created
(keyword AUTO_INCREMENT)
5. make sure that a foreign key will be updated if the primary key is updated
(keyword FOREIGN KEY, CHECK, CASCADE, RESTRICT, ON DELETE
and ON UPDATE)
17/26
Copyright 2005 Radian Publishing Co.
9.5 Creating a Table with Constraints
MySQL command to show the structure of the table:
SHOW FIELDS FROM TableName;
18/26
Copyright 2005 Radian Publishing Co.
(2/2)
9.6 Filling Table with Data
(1/3)
The basic syntax for inserting a new record is:
INSERT INTO TableName [(FieldName1 [, FieldName2 ...])]
VALUES (Expression1 [, Expression2 ...]);
19/26
Copyright 2005 Radian Publishing Co.
9.6 Filling Table with Data
(2/3)
If all the fields will be filled with values, you may skip entering the field
names in the Insert statement.
20/26
Copyright 2005 Radian Publishing Co.
9.6 Filling Table with Data
(3/3)
The date format in ANSI-92 is YYYY-MM-DD. Dates should be
enclosed in quotation marks.
For example '1991-4-1' represents the 1st day in April of 1991.
This format applies to MySQL, MS Access 97 and MS Access 2003,
but not in Visual FoxPro.
In Visual FoxPro, however, the default date format is MM-DD-YYYY,
and braces are used instead of quotation marks.
For example {4-1-1991}
21/26
Copyright 2005 Radian Publishing Co.
9.7 Other DDL Commands
The basic syntax for creating an index is:
CREATE INDEX IndexName
ON TableName (FieldName [ASC|DESC], ...);
22/26
Copyright 2005 Radian Publishing Co.
(1/5)
9.7 Other DDL Commands
(2/5)
The syntax for changing the structure of a table is:
ALTER TABLE TableName1
ADD | ALTER (MODIFY|CHANGE) | DROP [COLUMN] FieldName1
FieldType [(FieldWidth [, Precision])];
23/26
Copyright 2005 Radian Publishing Co.
9.7 Other DDL Commands
(3/5)
In MS Access and FoxPro, ALTER COLUMN is used to change the
size and data type of a field. But, you cannot change a field name.
In MySQL, MODIFY COLUMN is used to change the size and data type
of a field. CHANGE COLUMN is used to change a field name.
24/26
Copyright 2005 Radian Publishing Co.
9.7 Other DDL Commands
The SQL command to delete a table:
DROP TABLE TableName;
25/26
Copyright 2005 Radian Publishing Co.
(4/5)
9.7 Other DDL Commands
The SQL command to delete database:
DROP DATABASE DBName;
26/26
Copyright 2005 Radian Publishing Co.
(5/5)