vanets.vuse.vanderbilt.edu
Download
Report
Transcript vanets.vuse.vanderbilt.edu
CS 292 Special topics on
Big Data
Yuan Xue
([email protected])
Part I Relational Database
(SQL)
Yuan Xue
([email protected])
Creating and Using a Relational Database
Steps in creating and using a (relational) database
1. Design schema (using DDL – data definition language)
2. Initialization: “Bulk load” initial data
3. Operation: execute queries and modifications (using DML – data
manipulation language)
Meta-data: database definition
Data
Data
SQL Introduction
Programming
language for data management in a relational
database management system(RDBMS)
Both Data Definition Language (DDL) and Data Manipulation
Language (DML)
DDL: create, drop table
DML: query (select), insert, update and delete data from table
Standardized
and supported by all major commercial database
systems
One of the major reasons for commercial success of RDBMS
Interactive
programs
via GUI or command line, or embedded in
Data Definition in SQL
CREATE in SQL
CREATE SCHEMA MiniTwitter;
CREATE TABLE
(ID
Name
…
PRIMARY KEY
FOREIGN KEY
FOREIGN KEY
MiniTwitter.User
VARCHAR(20)
NOT NULL,
VARCHAR(20)
NOT NULL,
(ID)
(ID) REFERENCE Follow(Followee)
(ID) REFERENCE Follow(Follower)
);
Follow
User
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
Cathy123
Alice00
2012.11.1.2.6.6
Bob2013
Alice00
2012.11.1.2.6.7
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
CREATE in SQL
CREATE TABLE MiniTwitter.Tweet
(ID
VARCHAR(20)
Timestamp
TIMESTAMP
…
PRIMARY KEY (ID)
NOT NULL,
NOT NULL,
);
Data types in SQL
Numeric
Character, or String
CHAR, VARCHAR
Bit-string
INT, FLOAT, DEC
BIT, BLOB (binary large object)
Boolean
Date, Time
DATE, TIME, TIMESTAMP
Tweet
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
CREATE in SQL
CREATE TABLE MiniTwitter.Follow
(Followee
VARCHAR(20) NOT NULL,
Follower
VARCHAR(20) NOT NULL,
Timestamp
TIMESTAMP
NOT NULL,
PRIMARY KEY (Followee, Follower)
);
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
Cathy123
Alice00
2012.11.1.2.6.6
Bob2013
Alice00
2012.11.1.2.6.7
Data Manipulation in SQL
SELECT in SQL
SELECT-FROM-WHERE structure of basic SQL queries
SELECT A1, A2,…,An
FROM
R1, R2,…,Rm
WHERE Condition;
Attribute to return
Relationship/table
Conditional expression
Example
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
Retrieve the timestamp and content
of all tweets whose author is
“alice00”
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
Example
SELECT Timestamp, Content
FROM
Tweet
WHERE Author = ‘Alice00’;
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
Select condition
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
Example
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
Retrieve the content of all tweets
whose author is followed by
“Alice00”
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
Example
Select-project-join query
SELECT content
FROM
Tweet, Follow
WHERE Follower = ‘Alice00’ AND
Author = Follower;
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
Join condition
Select condition
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
Example
Qualify the attribute name to prevent ambiguity Retrieve the timestamp and content
of all tweets whose author is
followed by “Alice00”
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
Example
SELECT Tweet.Timestamp, Content
FROM
Tweet, Follow
WHERE Follower = ‘Alice00’ AND
Author = Follower;
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
Join condition
Select condition
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
Aggregate Functions in SQL
Aggregate function: summarize information from multiple tuples
Basic aggregate operations in SQL
COUNT, SUM, MAX, MIN, AVG
Retrieve the number of people that
“Alice00” is following
Retrieve the number of people
who are following “Alice00”
Aggregate Functions in SQL
Aggregate function: summarize information from multiple tuples
Basic aggregate operations in SQL
COUNT, SUM, MAX, MIN, AVG
Retrieve the number of people that
“Alice00” is following
SELECT COUNT(*)
FROM
Follow
WHERE Follower = ‘Alice00’;
Retrieve the number of people
who are following “Alice00”
SELECT COUNT(*)
FROM
Follow
WHERE Followee = ‘Alice00’;
Nested SQL query
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
Show the names of users with
more than 10 followers
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
Nested SQL query
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
SELECT Name
FROM
User
WHERE (SELECT COUNT (*)
FROM Follow
WHERE Followee = ID)>10);
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
GROUP in SQL
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
Show the names of all users with
the number of tweets from them
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
GROUP in SQL
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
SELECT User.Name, COUNT(Tweet.ID)
FROM
User, Tweet
WHERE User.ID = Tweet.Author
GROUP BY Tweet.Author;
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
ORDER BY in SQL
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
Show the names of users who
follow “Alice00” based on the time
of “following” relationship
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
ORDER BY in SQL
User
ID
Name
Email
Password
Alice00
Alice
alice00@gmail
.com
Aadf1234
Bob2013
Bob
bob13@gmail.
com
qwer6789
Cathy123
Cathy
cath@vandy
Tyuoa~!@
Tweet
SELECT User.Name,
FROM
User, Follow
WHERE User.ID = Follow.Follower
AND Follow.Followee = “Alice00”
ORDER BY Follow.Timestamp;
Follow
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
ID
Timestamp
Author
Content
0001
2013.12.20.11
.20.2
Alice00
Hello
0002
2013.12.20.11
.23.6
Bob2013
Nice weather
Cathy123
Alice00
2012.11.1.2.6.6
0003
2014.1.6.1.25.
2
Alice00
@Bob
Not sure..
Bob2013
Alice00
2012.11.1.2.6.7
INSERT in SQL
INSERT INTO Follow
VALUES (“Cathy123”, “Bob2013”, 2013.12.1.2.3.3);
Follow
Follow
Followee
Follower
Timestamp
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
Alice00
Cathy123
2012.11.1.2.3.3
Cathy123
Alice00
2012.11.1.2.6.6
Cathy123
Alice00
2012.11.1.2.6.6
Bob2013
Alice00
2012.11.1.2.6.7
Bob2013
Alice00
2012.11.1.2.6.7
Cathy123
Bob2013
2013.12.1.2.3.3
DELETE in SQL
DELETE FROM Follow
WHERE Followee = ‘Alice00’ AND FOllower = ‘Bob2013’;
Follow
Follow
Followee
Follower
Timestamp
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
Alice00
Cathy123
2012.11.1.2.3.3
Cathy123
Alice00
2012.11.1.2.6.6
Cathy123
Alice00
2012.11.1.2.6.6
Bob2013
Alice00
2012.11.1.2.6.7
Bob2013
Alice00
2012.11.1.2.6.7
UPDATE in SQL
UPDATE Follow
SET Timestamp = ‘2013.1.1.3.6.6’
WHERE Followee = ‘Alice00’ AND Follower = ‘Bob2013’;
Follow
Follow
Followee
Follower
Timestamp
Followee
Follower
Timestamp
Alice00
Bob2013
2011.1.1.3.6.6
Alice00
Bob2013
2013.1.1.3.6.6
Bob2013
Cathy123
2012.10.2.6.7.7
Bob2013
Cathy123
2012.10.2.6.7.7
Alice00
Cathy123
2012.11.1.2.3.3
Alice00
Cathy123
2012.11.1.2.3.3
Cathy123
Alice00
2012.11.1.2.6.6
Cathy123
Alice00
2012.11.1.2.6.6
Bob2013
Alice00
2012.11.1.2.6.7
Bob2013
Alice00
2012.11.1.2.6.7
More on SQL
Drop table
Outer Join
Indexes, Constraints, Views, Triggers, Transactions, Authorization
Substring Pattern Matching and Arithmetic Operators
Check out:
http://cse.unl.edu/~sscott/ShowFiles/SQL/CheatSheet/SQLCheatSheet.html