Transcript Slide 1

Tonight’s Lecture
chapter 3
Using MySQL Reference Manual to
Get Help
• Can access online
• http://dev.mysql.com/doc/refman/5.0/en/
A Guide to MySQL
2
Describing a Table
A Guide to MySQL
3
In Oracle
DESC table_name
example: DESC h_book
DDL
• Stands for Data Definition Language
– create statements
– drop statements
– alter statements
Creating a Database
• Must create a database before creating tables
• Use CREATE DATABASE command
• Include database name
A Guide to MySQL
6
Changing the Default Database
• Default database: database to which all subsequent
commands pertain
• USE command, followed by database name:
– Changes the default database
– Execute at the start of every session
A Guide to MySQL
7
You’re changing your default database by selecting your user
database from the drop down in PhpMyAdmin
Creating a Table
• Describe the layout of each table in the database
• Use CREATE TABLE command
• TABLE is followed by the table name
• Follow this with the names and data types of the
columns in the table
• Data types define type and size of data
A Guide to MySQL
9
Table and Column Name
Restrictions
• Names cannot exceed 18 characters
• Must start with a letter
• Can contain letters, numbers, and underscores (_)
• Cannot contain spaces
A Guide to MySQL
10
Avoid using reserved words as names
of your database objects
Reserved words can differ from database platform to
database platform
MySQL reserved word examples
- select
- numeric
- database
- group
Implementation of Nulls
• Use NOT NULL clause in CREATE TABLE
command to exclude the use of nulls in a column
• Default is to allow null values
• If a column is defined as NOT NULL, system will
reject any attempt to store a null value there
A Guide to MySQL
12
CREATE TABLE a_marina
(
MARINA_NUM char(4) NOT NULL,
NAME char(20) NULL,
ADDRESS char(15) NULL,
CITY char(15) NULL,
STATE char(2) NULL,
ZIP char(5) NULL,
PRIMARY KEY (MARINA_NUM)
)
Field
MARINA_NUM
NAME
ADDRESS
CITY
STATE
ZIP
Type
char(4)
char(20)
char(15)
char(15)
char(2)
char(5)
What will this SQL statement do?
Null
NO
YES
YES
YES
YES
YES
Key
PRI
Default Extra
CREATE TABLE a_marina
(
MARINA_NUM char(4) NOT NULL,
NAME char(20),
ADDRESS char(15),
CITY char(15),
STATE char(2),
ZIP char(5),
PRIMARY KEY (MARINA_NUM)
)
Field
MARINA_NUM
NAME
ADDRESS
CITY
STATE
ZIP
Type
char(4)
char(20)
char(15)
char(15)
char(2)
char(5)
What will this SQL statement do?
Null
NO
YES
YES
YES
YES
YES
Key
PRI
Default Extra
CREATE TABLE a_marina
(
MARINA_NUM char(4) NOT NULL,
NAME char(20),
ADDRESS char(15),
CITY char(15),
STATE char(2),
ZIP char(5)
)
Field
MARINA_NUM
NAME
ADDRESS
CITY
STATE
ZIP
Type
char(4)
char(20)
char(15)
char(15)
char(2)
char(5)
What will this SQL statement do?
Null
NO
YES
YES
YES
YES
YES
Key
Default Extra
CREATE TABLE a_marina
(
MARINA_NUM char(4) NOT NULL,
NAME char(20),
ADDRESS char(15),
CITY char(15),
STATE char(2),
ZIP
)
What will this SQL statement do?
ERROR
We did not specify a datatype for the column ZIP
Write the SQL that will create an A_OWNER table
with the following structure:
Field
OWNER_NUM
LAST_NAME
FIRST_NAME
ADDRESS
CITY
STATE
ZIP
Type
char(4)
char(50)
char(20)
char(15)
char(15)
char(2)
char(5)
Null
NO
YES
YES
YES
YES
YES
YES
Key
PRI
CREATE TABLE a_owner
(
OWNER_NUM char(4) NOT NULL,
LAST_NAME char(50) NULL,
FIRST_NAME char(20) NULL,
ADDRESS char(15) NULL,
CITY char(15) NULL,
STATE char(2) NULL,
ZIP char(5) NULL,
PRIMARY KEY (OWNER_NUM)
)
Default Extra
Write the SQL that will create an EMPLOYEES table
with the following structure:
Field
employee_id
name
manager_id
Type
varchar(10)
varchar(100)
varchar(10)
Null
YES
YES
YES
CREATE TABLE employees
(
employee_id varchar(10) NULL,
name varchar(100) NULL,
manager_id varchar(10) NULL
)
Key Default Extra
Write the SQL that will create an P_ORDER_LINE
table with the following structure:
Field
ORDER_NUM
PART_NUM
NUM_ORDERED
QUOTED_PRICE
Type
char(5)
char(4)
decimal(3,0)
decimal(6,2)
Null
NO
NO
YES
YES
Key
PRI
PRI
Default Extra
CREATE TABLE p_order_line
(
ORDER_NUM char(5) NOT NULL,
PART_NUM char(4) NOT NULL,
NUM_ORDERED decimal(3,0) NULL,
QUOTED_PRICE decimal(6,2) NULL,
PRIMARY KEY (ORDER_NUM,PART_NUM)
)
Write the SQL that will create an H_INVENTORY
table with the following structure:
Field
BOOK_CODE
BRANCH_NUM
ON_HAND
Type
char(4)
decimal(2,0)
decimal(2,0)
Null
NO
NO
YES
Key
PRI
PRI
Default
Extra
0
CREATE TABLE h_inventory
(
BOOK_CODE char(4) NOT NULL,
BRANCH_NUM decimal(2,0) NOT NULL default 0,
ON_HAND decimal(2,0) NULL,
PRIMARY KEY (BOOK_CODE,BRANCH_NUM)
)
Write the SQL that will create an H_BOOK table with
the following structure:
Field
BOOK_CODE
TITLE
PUBLISHER_CODE
TYPE
PRICE
PAPERBACK
Type
char(4)
char(40)
char(3)
char(3)
decimal(4,2)
char(1)
Null
NO
YES
YES
YES
YES
YES
Key
PRI
CREATE TABLE h_book
(
BOOK_CODE char(4) NOT NULL,
TITLE char(40) NULL,
PUBLISHER_CODE char(3) NULL,
TYPE char(3) NULL,
PRICE decimal(4,2) NULL,
PAPERBACK char(1) NULL,
PRIMARY KEY (BOOK_CODE)
)
Default Extra
Write the SQL that will create an P_REP table with
the following structure:
Field
REP_NUM
LAST_NAME
FIRST_NAME
STREET
CITY
STATE
ZIP
COMMISSION
RATE
Type
char(2)
char(15)
char(15)
char(15)
char(15)
char(2)
char(5)
decimal(7,2)
decimal(3,2)
Null
NO
YES
YES
YES
YES
YES
YES
YES
YES
CREATE TABLE p_rep
(
REP_NUM char(2) NOT NULL,
LAST_NAME char(15) NULL,
FIRST_NAME char(15) NULL,
STREET char(15) NULL,
CITY char(15) NULL,
STATE char(2) NULL,
ZIP char(5) NULL,
COMMISSION decimal(7,2) NULL,
RATE decimal(3,2) NULL,
PRIMARY KEY (REP_NUM)
)
Key
PRI
Default Extra
Dropping a Table
• Can correct errors by dropping (deleting) a table and
starting over
• Useful when table is created before errors are
discovered
• Command is followed by the table to be dropped and
a semicolon
• Any data in table also deleted
A Guide to MySQL
23
drop table a_owner
be careful with drop statements
What is DDL?
Adding Rows to a Table
• INSERT command:
– INSERT INTO followed by table name
– VALUES command followed by specific values in
parentheses
– Values for character columns in single quotation marks
A Guide to MySQL
26
The Insert Command
A Guide to MySQL
27