Python for Everybody Database Handout
/lectures3/Pythonlearn-15-Database-Handout.txt
Download and Install: http://sqlitebrowser.org/
Single Table SQL
CREATE TABLE "Users" ("name" TEXT, "email" TEXT)
INSERT INTO Users (name, email) VALUES ('Chuck', 'csev@umich.edu')
INSERT INTO Users (name, email) VALUES ('Colleen', 'cvl@umich.edu')
INSERT INTO Users (name, email) VALUES ('Ted', 'ted@umich.edu')
INSERT INTO Users (name, email) VALUES ('Sally', 'a1@umich.edu')
INSERT INTO Users (name, email) VALUES c('Ted', 'ted@umich.edu')
INSERT INTO Users (name, email) VALUES ('Kristen', 'kf@umich.edu')
DELETE FROM Users WHERE email='ted@umich.edu'
UPDATE Users SET name="Charles" WHERE email='csev@umich.edu'
SELECT * FROM Users
SELECT * FROM Users WHERE email='csev@umich.edu'
SELECT * FROM Users ORDER BY email
SELECT * FROM Users ORDER BY name DESC
Multi-Table SQL:
CREATE TABLE "Artist" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"name" TEXT)
CREATE TABLE "Album" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
artist_id INTEGER,
"title" TEXT)
CREATE TABLE "Genre" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"name" TEXT)
CREATE TABLE "Track" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
album_id INTEGER, genre_id INTEGER, len INTEGER, rating INTEGER,
"title" TEXT, "count" INTEGER)
INSERT INTO Artist (name) VALUES ('Led Zepplin')
INSERT INTO Artist (name) VALUES ('AC/DC')
INSERT INTO Genre (name) VALUES ('Rock') ;
INSERT INTO Genre (name) VALUES ('Metal');
INSERT INTO Album (title, artist_id) VALUES ('Who Made Who', 2);
INSERT INTO Album (title, artist_id) VALUES ('IV', 1);
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('Black Dog', 5, 297, 0, 2, 1) ;
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('Stairway', 5, 482, 0, 2, 1) ;
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('About to Rock', 5, 313, 0, 1, 2) ;
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('Who Made Who', 5, 207, 0, 1, 2) ;
SELECT Album.title, Artist.name FROM Album JOIN Artist
ON Album.artist_id = Artist.id
SELECT Album.title, Album.artist_id, Artist.id, Artist.name
FROM Album JOIN Artist ON Album.artist_id = Artist.id
SELECT Track.title, Track.genre_id, Genre.id, Genre.name
FROM Track JOIN Genre
SELECT Track.title, Genre.name FROM Track JOIN Genre
ON Track.genre_id = Genre.id
SELECT Track.title, Artist.name, Album.title, Genre.name
FROM Track JOIN Genre JOIN Album JOIN Artist
ON Track.genre_id = Genre.id AND Track.album_id = Album.id
AND Album.artist_id = Artist.id
Many-Many Relationship
CREATE TABLE User (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
name TEXT UNIQUE,
email TEXT
) ;
CREATE TABLE Course (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
title TEXT UNIQUE
) ;
CREATE TABLE Member (
user_id INTEGER,
course_id INTEGER,
role INTEGER,
PRIMARY KEY (user_id, course_id)
) ;
INSERT INTO User (name, email) VALUES ('Jane', 'jane@tsugi.org');
INSERT INTO User (name, email) VALUES ('Ed', 'ed@tsugi.org');
INSERT INTO User (name, email) VALUES ('Sue', 'sue@tsugi.org');
INSERT INTO Course (title) VALUES ('Python');
INSERT INTO Course (title) VALUES ('SQL');
INSERT INTO Course (title) VALUES ('PHP');
INSERT INTO Member (user_id, course_id, role) VALUES (1, 1, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 1, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 1, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (1, 2, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 2, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 3, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 3, 0);
SELECT User.name, Member.role, Course.title
FROM User JOIN Member JOIN Course
ON Member.user_id = User.id AND Member.course_id = Course.id
ORDER BY Course.title, Member.role DESC, User.name
“The amphibian!” cried Larry. “I wonder why——” “I did not see her,” Miss Serena replied to Sandy while she answered the older man’s question in the same breath. “But I saw a glimpse of dress just afterward.” Her expression showed confident assurance. “It’s time to find out what’s what!” he muttered. "De veras?" asked Cairness, sharply. He was of no mind to lose her like this, when he was so near his end. With his heart full of hope and joy, the Deacon bustled around to make every possible preparation for the journey. "We do," responded those kneeling at the altar. "Silence, Sergeant. Billings? Billings? The name of the Lieutenant-Colonel of the 200th Ind. happens to be McBiddle—one-armed man, good soldier. Billings? Billings? T. J. Billings? Is that your name?" "Pete," said Shorty solemnly as he finished trimming the switch, and replaced the knife in his pocket, "nobody's allowed to pick out his own daddy in this world. He just gits him. It's one o' the mysterious ways o' Providence. You've got me through one o' them mysterious ways o' Providence, and you can't git shet o' me. I'm goin' to lick you still harder for swearin' before your father, and sayin' disrespeckful words to him. And I'm goin' to lick you till you promise never to tech another card until I learn you you how to play, which'll be never. Come here, my son." "Leave me alone," Dodd said. "Just do me a favor. Leave me alone." "I cud m?ake something out of Boarzell." Should beauty forget now their nests have grown cold? Makes boil the rushing blood and thrills my very soul." "Because you gave those things up of your free will—they were made to give them up by force. You've no right to starve and deny other people as you have to starve and deny yourself." He rose to his feet. The kitchen was dark, with eddying sweeps of shadow in the corners which the firelight caressed—while a single star put faint ghostly romance into the window. "Oh, mother!" shrieked Margaret. "Fly!—to the abbey, and take sanctuary!" HoME大陆明星露点电影片段合集
ENTER NUMBET 0017
quanliba.com.cn
www.cejy.com.cn
lihe1.com.cn
www.tegui5.net.cn
mulu9.com.cn
bagao1.com.cn
www.geqie4.net.cn
baifu8.net.cn
dberry.com.cn
www.0a.org.cn