# connect to mysql to load in files

mysql --local-infile -u dbuser -p

# use the class_examples database

USE class_examples;

# altering a table

SHOW TABLES;
DESCRIBE users;
ALTER TABLE users ADD middle char(1);
DESCRIBE users;
ALTER TABLE users DROP middle;
DESCRIBE users;

# inserting data

INSERT INTO users (email,password,first,last,reg_date) VALUES  ('harry@potter.com',sha1('goldensnitch'),'Harry','Potter',now());

INSERT INTO users (email,password,first,last,reg_date) VALUES  ('granger@dom.magic.gov',sha1('H0gw4rts1sC00l'),'Hermione','Granger',now());


# querying data from a table

SELECT count(*) FROM users;
SELECT * FROM users;
SELECT * FROM users WHERE last = 'Potter';
SELECT * FROM users WHERE email = 'harry@potter.com';
SELECT sha1('password');

# Create table for zipcodes

CREATE TABLE zips(
    zipcode varchar(5),
    type varchar(255),
    city varchar(255),
    state char(2),
    loc_type char(7),
    latitute double,
    longitude double,
    location varchar(255),
    decomissioned char(5),
    returns bigint,
    pop bigint,
    wages bigint,
    primary key (zipcode)
) Engine = InnoDB;

# Import data into table

LOAD DATA LOCAL INFILE '/home/<username>/zipcodes.csv' INTO TABLE zips FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

# Various queries

SELECT * FROM zips WHERE zipcode = '22903';
SELECT city,state,latitude,longitude FROM zips WHERE zipcode = '22903';
SELECT city,state,latitute,longitude FROM zips WHERE zipcode = '22903';
SELECT * FROM zips WHERE state = 'VA' AND latitute > 38;
SELECT count(*) FROM zips WHERE state = 'VA' AND latitute > 38;
SELECT count(*) AS total FROM zips WHERE state = 'VA' AND latitute > 38;
SELECT max(longitude) AS total FROM zips WHERE state = 'VA';
SELECT * FROM zips WHERE zipcode LIKE 'Zip%';
DELETE FROM zips WHERE zipcode = 'Zipco';
SELECT max(zipcode) FROM zips GROUP BY state;
SELECT max(zipcode),state FROM zips GROUP BY state;
SELECT * FROM zips WHERE city = 'Charlottesville' AND state = 'VA';
SELECT * FROM zips WHERE city = 'Charlottesville' AND state = 'VA' ORDER BY zipcode DESC;
SELECT * FROM zips WHERE city = 'Charlottesville' AND state = 'VA' ORDER BY zipcode DESC LIMIT 1;