Last active
August 29, 2015 13:57
-
-
Save mathildathompson/9456844 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
##Loading data from a CSV File | |
#Slow | |
#Memory expensive | |
#Buggy | |
##DATABASE | |
#A different program; | |
#Create a string that describes what you want and send it to the database; | |
#You could have the database on a different machine/different country, all you need to send to it is a query string; | |
#SQL invented in the 1970s; | |
#SQLite3(light weight solution, not good for a webserver that has billions of requests per minute) | |
#PostgresQL(proffessional database, very optimised); | |
#SQL and PostgresQL are interchangable; | |
#SQL is a relational database; | |
#No SQL (such as MONGODB and CouchDB) they are NOT relational, they are giant hashes of JSON data; | |
#SQlite lives inside all iPhones and android devices; | |
#SQL is case insensitive; | |
CREATE TABLE person( | |
id INTEGER PRIMARY KEY, | |
first_name TEXT, | |
last_name TEXT, | |
age INTEGER | |
) | |
#Each row of the table represents a single person; | |
#A record is a single row of the table; | |
#We can read from the database, write to the database, update the database, and delete records from it; | |
#Any database needs to be able to: create, read, update and delete => CRUD; | |
#To create: INSERT | |
#To read: SELECT | |
#Update: UPDATE | |
#Delete: DELETE | |
#person_pet table is relational table, relating a particular person with a particual pet; | |
sqlite3 test.db < pets.sql #running the .sql file in the test.db; | |
sqlite3 test.db | |
CREATE TABLE test (id); | |
.schema #tells you about the database; | |
##INERTING DATA | |
INSERT INTO person(id, first_name, last_name, age) VALUES ( | |
DROP TABLE test; #To drop a table | |
#SELECT | |
SELECT * from person; #select everything | |
SELECT first_name FROM person; | |
SELECT name, breed FROM pet; | |
#DELETE | |
/* make sure there's dead pets */ | |
SELECT name, age FROM pet WHERE dead = 1; | |
/* aww poor robot */ | |
DELETE FROM pet WHERE dead = 1; | |
#UPDATE | |
#You want to change some columns of the record; | |
#UPDATE person SET first_name = "Hilarious Guy" | |
UPDATE person SET first_name = "Hilarious Guy" | |
WHERE first_name = "Zed"; | |
UPDATE pet SET name = "Fancy Pants" | |
WHERE id=0; #This is the safer way of doing it! | |
##RELATIONSHIPS | |
#ONE PERSON HAS MANY PETS; | |
#ONE TO MANY, MANY TO MANY; | |
#person_pet table, a person can have many pets and a pet can have many persons; | |
#The person_pet table does not have an id, we do not need it; | |
#sqlite3 Gem provides the ability to for the webserver to talk to the database; | |
##TERMINAL INTERFACES | |
#To get into sqlite: type sqlite3 into terminal; | |
#To quit .quit or ctrl + d; | |
#To create multiple files | |
mkdir{public,public/css.views} | |
#which sqlite3 (shows you where it is on the machine) | |
\dt #shows all of the tables in the database; | |
##GERRYS datbase lesson | |
CRUD |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment