Transcript SQLx

Structured Query Language
SQL: An Introduction
SQL (Pronounced S.Q.L)
• The standard user and application program
interface to a relational database is the Structured
Query Language (SQL)
• SQL statements are used both for interactive
queries for information from a relational database
and for gathering data for reports
– SQL uses keywords to interact with databases
– It allows creating a database, populating, and querying
it
– In this slide we learn to apply MySQL Workbench to do
all of these
• You can also use MySQL client
Create a Database/Table
• The syntax to create a database :
CREATE DATABASE databaseName //Comment: SQL is case sensitive!
• The CREATE keyword is also used to create a table
CREATE TABLE tablename
//Capitalize keywords for clarity!
(
Column1 description,
//define each column
Column2 description,
// put a comma after each
…
)
Note: text written after // is comment, and is NOT part of the syntax!
Use MySQL to name the database
• Connect to MySQL // Read Chapter 4 Ullman
– Make sure you have permission to create database
CREATE DATABASE sitename;
// end each statement with a ;
USE sitename;
// tells MySQL that you want to use the database from here on
Create the users Table in the Database
CREATE TABLE user
(
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,
registration_date DATETIME NOT NULL,
PRIMARY KEY (user_id),
);
NOTE: The user table was designed in Chapter 4 Ullman’s book!
Confirm that the table exists
• SHOW TABLES;
• SHOW CCOLUMNS FROM users;
Populating the table
• Now that the table exists, insert records into it
• For this purpose we use the INSERT syntax:
• When we want only to enter values for certain columns:
INSERT INTO tablename
(column1, column2, …) VALUES (value1, value2, …);
// Columns not given a value will get NULL unless they have a default
// value. If they are NOT NULL and have no default there will be an error
• When we want to enter values for all columns use the following:
INSERT INTO tablename VALUES (value1, NULL, value2, value3, …)
Inserting multiple rows (records)
INSERT INTO tablename (column1, column2) VALUES (valueA,
valueB), (valueC, valueD), (valueE, valueF),
// this is not supported by all database systems (only by MySQL)
Insert real data into the users table
INSERT INTO users
(first_name, last_name, email, pass, registration_date,)
VALUES (‘Hassan’, ‘Babaie’, ‘[email protected]’, SHA1(‘mypassword’), NOW());
//or without naming columns
INSERT INTO users
VALUES (NULL, ‘Hassan’, ‘Babaie’, ‘[email protected]’, SHA1(‘mypassword’),
NOW());
// note: NULL is for the user_id (since all values must be provided!)
//Comments:
// note: put strings in “quotation marks”!
// char, varchar, and text are strings!
// NULL should not be quoted
// backslash escapes a mark, e.g., O’clock is written as ‘O\’clock’
//the SHA1() and NOW() are functions.
//the SHA1() encripts data to a 40 character long string, hence //CHAR(40) is used
for password
Joins
• Table joins are used to query data from two or
more tables, based on a relationship between
certain columns in these tables
• Tables in a database are often related to each
other Table
with
keys
Store_Information
store_name
Los Angeles
San Diego
Los Angeles
Boston
Sales
$1500
$250
$300
$700
Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-08-1999
Table Geography
Region_name
East
East
West
West
store_name
Boston
New York
Los Angeles
San Diego
• To get the sales information by region, we have to combine
the information from the two tables
• These two tables are linked by the common field,
"store_name“
• The following SQL will retrieve the sales data by the region.
SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM
Geography A1 JOIN Store_Information A2 ON
A1.store_name = A2.store_name GROUP BY A1.region_name
• Result:
REGION
SALES
East
$700
West
$2050
Join
• JOIN: Return rows when there is at least one
match in both tables
• LEFT JOIN: Return all rows from the left table,
even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right
table, even if there are no matches in the left
table
• FULL JOIN: Return rows when there is a match
in one of the tables
Views
• A Database View is a subset of the database sorted and displayed in a
particular way
• For each view, you can control which columns are displayed, what order
they are displayed in, how the data is sorted, and what types of records to
display
• A view consists of rows and columns just like a table
• The difference between a view and a table is that views are definitions
built on top of other tables (or views), and do not hold data themselves
• If a data item is changing in the underlying table, the same change is
reflected in the view
• A view can be built on top of a single table or multiple tables. It can also
be built on top of another view
View Customer Table
•
CREATE TABLE Customer
(First_Name varchar(100),
Last_Name
varchar(100),
Address
varchar(50),
City varchar(30),
Country
varchar(25),
Birth_Date
date NOT NULL)
•
Use the following syntax to create a view called V_Customer that contains only
the First_Name, Last_Name, and Country columns from this table
CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer
•
Now we have a view called V_Customer with the following structure:
View V_Customer
(First_Name char(100),
Last_Name char(100),
Country char(25))
Alter Table
• Alter Table - Once a table is created in the database,
you can use the Alter statement to change the
structure of the table
•
•
•
•
•
For example:
Add a column
Drop a column
Change a column name
Change the data type for a column
ALTER table Customer add Gender char(1)
ALTER table Customer drop Country
Drop Table
• Drop Table - Sometimes we may need to delete a
table in the database
• This allows to get rid of tables not needed
anymore and frees up database space
• Use the following command to delete the
Customer table.
DROP TABLE Customer
Truncate Table
• TRUNCATE TABLE - Sometimes we may need to delete all
of the data in a table
• One way of doing this is with DROP TABLE
• But what if we wish to simply get rid of the data but not the
table itself?
• For this, we can use the TRUNCATE TABLE command
• Use the following command to delete all data in Customer
table.
TRUNCATE TABLE Customer
Insert Into
• INSERT INTO Statement – Insert statement is
used to add one or more rows to a table
INSERT INTO Customer values ('4','Adams','Joe') –
adds one row
INSERT INTO Store_Information (store_name,
Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
Update
• UPDATE Statement - allows to update data in
an existing table
UPDATE Store_Information
SET Sales = 2000
WHERE store_name = "Los Angeles"
AND Date = "Jan-08-1999"
Delete
• DELETE Statement – allows to remove rows
from a table
DELETE FROM Store_Information
WHERE store_name = "Los Angeles"
Stored procedures
• A stored procedure is executable code that is
associated with, and generally stored in, the
database
• Stored procedures usually collect and customize
common operations, like inserting a row into a
table, gathering statistical information about
usage patterns, or encapsulating complex
business logic and calculations