Transcript MySQL Chapter 3

3
A Guide to MySQL
Objectives
• Start MySQL and learn how to use the MySQL
Reference Manual
• Create a database
• Change (activate) a database
• Create tables using MySQL
• Create and run SQL commands in MySQL
A Guide to MySQL
2
Objectives (continued)
• Identify and use data types to define columns in
tables
• Understand and use nulls
• Add rows to tables
• View table data
• Correct errors in a database
A Guide to MySQL
3
Objectives (continued)
• Save SQL commands and results to a file
• Describe a table’s layout using MySQL
A Guide to MySQL
4
Introduction
• Structured Query Language (SQL):
– Popular and widely used language for retrieving and
manipulating database data
– Developed in mid-1970s under the name SEQUEL
– Renamed SQL in 1980
– Used by most DBMSs
A Guide to MySQL
5
Introduction to MySQL
• Starting MySQL
• Obtaining help
• Accessing the MySQL Reference Manual
A Guide to MySQL
6
Starting MySQL
• Windows XP
–
–
–
–
Click Start button
Point to All Programs
Point to MySQL on menu
Point to MySQL Server 4.1
– Click MySQL Command Line Client
• Must enter password in Command Line Client
window
A Guide to MySQL
7
Obtaining Help in MySQL
• Type \h at MySQL> prompt
• Type “help” followed by name of command
– help contents
– help union
A Guide to MySQL
8
A Guide to MySQL
9
Using MySQL Reference
Manual to Get Help
• Click MySQL Manual - Table of Contents on MySQL
4.1 submenu
• Can access online
A Guide to MySQL
10
A Guide to MySQL
11
Creating a Database
• Must create a database before creating tables
• Use CREATE DATABASE command
• Include database name
A Guide to MySQL
12
Creating a Database (continued)
A Guide to MySQL
13
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
14
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
15
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
16
Creating the REP Table
A Guide to MySQL
17
Entering Commands in MySQL
• Commands are free-format; no rules stating specific
words in specific positions
• Press ENTER to move to the next line in a command
• Indicate the end of a command by typing a semicolon
• Commands are not case sensitive
A Guide to MySQL
18
Running SQL Commands
A Guide to MySQL
19
Editing SQL Commands
• Statement history: stores most recently used
command
• Editing commands:
–
–
–
–
Use arrow keys to move up, down, left, and right
Use Ctrl+A to move to beginning of line
Use Ctrl+E to move to end of line
Use Backspace and Delete keys
A Guide to MySQL
20
Errors in SQL Commands
A Guide to MySQL
21
Editing MySQL Commands
• Press Up arrow key to go to top line
• Press Enter key to move to next line if line is correct
• Use Right and Left arrow keys to move to location of
error
• Press ENTER key when line is correct
• If Enter is not pressed on a line, line not part of the
revised command
A Guide to MySQL
22
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
Data Types
• For each table column, type of data must be defined
• Common data types:
– CHAR(n)
– VARCHAR(n)
– DATE
– DECIMAL(p,q)
– INT
– SMALLINT
A Guide to MySQL
24
Nulls
• A special value to represent situation when actual
value is not known for a column
• Can specify whether to allow nulls in the individual
columns
• Should not allow nulls for primary key columns
A Guide to MySQL
25
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
26
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
27
The Insert Command
A Guide to MySQL
28
Modifying the INSERT Command
• To add new rows modify previous INSERT command
• Use same editing techniques as those used to correct
errors
A Guide to MySQL
29
Adding Additional Rows
A Guide to MySQL
30
The INSERT Command with Nulls
• Use a special format of INSERT command to enter a
null value in a table
• Identify the names of the columns that accept nonnull values, then list only the non-null values after the
VALUES command
A Guide to MySQL
31
The INSERT Command with
Nulls
• Enter only non-null values
• Precisely indicate values you are entering by listing
the columns
A Guide to MySQL
32
The INSERT Command with
Nulls (continued)
A Guide to MySQL
33
Viewing Table Data
• Use SELECT command to display all the rows and
columns in a table
• SELECT * FROM followed by the name of the table
• Ends with a semicolon
A Guide to MySQL
34
Viewing Table Data (continued)
A Guide to MySQL
35
Viewing Table Data (continued)
A Guide to MySQL
36
Correcting Errors In the
Database
• UPDATE command is used to update a value in a
table
• DELETE command allows you to delete a record
• INSERT command allows you to add a record
A Guide to MySQL
37
Correcting Errors in the Database
• UPDATE: change the value in a table
• DELETE: delete a row from a table
A Guide to MySQL
38
Correcting Errors in the Database
(continued)
A Guide to MySQL
39
Correcting Errors in the Database
(continued)
A Guide to MySQL
40
Saving SQL Commands
• Allows you to use commands again without retyping
• Different methods for each SQL implementation you
are using
– Oracle SQL*Plus and SQL*Plus Worksheet use a
script file
– Access saves queries as objects
– MySQL uses an editor to save text files
A Guide to MySQL
41
Saving SQL Commands
• Script file:
–
–
–
–
File containing SQL commands
Use a text editor or word processor to create
Save with a .txt file name extension
Run in MySQL:
• SOURCE file name
• \. file name
– Include full path if file is in folder other than default
A Guide to MySQL
42
Creating the Remaining
Database Tables
• Execute appropriate CREATE TABLE and INSERT
commands
• Save these commands to a secondary storage device
A Guide to MySQL
43
Describing a Table
A Guide to MySQL
44
Summary
• Use MySQL Command Line Client window to enter
commands
• Type \h or help to obtain help at the mysql> prompt
• Use MySQL Reference Manual for more detailed
help
A Guide to MySQL
45
Summary (continued)
• Use the CREATE DATABASE command to create a
database
• Use the USE command to change the default database
• Use the CREATE TABLE command to create tables
• Use the DROP TABLE command to delete a table
A Guide to MySQL
46
Summary (continued)
• CHAR, VARCHAR, DATE, DECIMAL, INT and
SMALLINT data types
• Use INSERT command to add rows
• Use NOT Null clause to identify columns that cannot
have a null value
• Use SELECT command to view data in a table
A Guide to MySQL
47
Summary (continued)
• Use UPDATE command to change the value in a
column
• Use DELETE command to delete a row
• Use SHOW COLUMNS command to display a
table’s structure
A Guide to MySQL
48