Mysql re-up

customersdump.sql

-- TODO add data for customers here

findFkConstraints.sql

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';
  
  

foreignkeyupdates.sql

alter TABLE orders DROP FOREIGN KEY orders_ibfk_1;
ALTER TABLE orders ADD FOREIGN KEY(customer_id) references customers(id) ON UPDATE CASCADE ON DELETE NO ACTION;

Joins.sql

-- delete a customer
-- bcos of constraint
-- first turn off foreign_key_checks
SET foreign_key_checks = 0;
-- dorp customer(s)
SET foreign_key_checks = 1;

-- INNER JOIN
-- fetch details of customers and orders they made
-- wont show details of orders of customer 11
-- whicj no longer exists
select first_name, last_name, order_date, amount
from customers
join orders
on customers.id = orders.customer_id
ORDER BY customers.last_name;

-- LEFT JOIN
-- Returns all from first table
-- Pretty much appends info from second_table to first_table
-- customers c is shorthand
-- so late ron i can write c.id :)
select first_name, last_name, order_date, amount
from customers c
left join orders o
on c.id = o.customer_id;

-- appending where order_date is NULL
-- to d last query 
-- wull yield all users with no orders

/*
RIGHT JOIN
all from second table
in our case
all orders with details of customer
*/
select first_name, last_name, order_date, amount
from customers c
right  join orders o 
on c.id = o.customer_id;

/*
FULL JOIN
MySQL doenst provide FULL JOIN outta d box
but its fairly easy to conjure
just UNION a left Nad rigt join
*/

select first_name, last_name, order_date, amount
from customers c
left join orders o
on c.id = o.customer_id
union
select first_name, last_name, order_date, amount
from customers c
right join orders o
on c.id = o.customer_id;

loadfromfile.sql

-- secure_file_priv config
[mysqld]
secure-file-priv = ""
-- you mayhave to use root
-- or grant FILE privileges to user
-- restrat mysql service
-- brew services restart mysql for mac

-- to find your mysql cnf
-- especially as home brew uses non -defult
sudo fs_usage | grep my.cnf

-- qit qnd retsrt mysql to see which files are being accessed
-- load files from file
load data infile '/Users/ispoa/Workspace/sql/datadump.sql' into TABLE orders FIELDS TERMINATED BY ',';
-- load data into selected columns
load data infile '/Users/ispoa/Workspace/sql/datadump.sql' into TABLE orders FIELDS TERMINATED BY ',' LINES STARTING BY '01' (order_date, amount, customer_id);

ordersdump.sql

-- null,200,6
-- 0space1 else its gonna be counted as data
-- i wamma verson this so I set my data to start with '0 1'
-- load file with 
2018-05-11 23:11:08, 1200, 7
2009-05-25 02:34:00, 500, 5
2014-02-05 11:54:01, 2300, 6

tables.sql

-- create tables for join exercise
create table customers (
    first_name varchar(255),
    last_name varchar(255) not null,
    email varchar(255) not null,
    address varchar(255),
    city varchar(255),
    state varchar(255),
    zip varchar(6),
  -- id int not null auto_increment primary key,
  );
  
  -- summary of table
  show tables;
  describe customers;
  
  -- update table, add column
  alter table customers add column id int not null;
  
  -- add primary key
  alter table customers add primary key(id);
  
  -- alter column
  alter table customers change column id id int not null auto_increment;
  
  -- order seems to be a reserve word
  create table orders (
    order_id int not null,
    order_date datetime not null default CURRENT_TIMESTAMP,
    amount int(20) NOT NULL,
    customer_id int not null,
    primary key(order_id),
    foreign key(customer_id) references customer(id)
  );
  
  -- bulk inserts
  insert into customers
    (first_name, last_name, email, address, city, state, zip)
  values
    ('Homer', 'Simpson', 'homes@fox,net', 'Springfield Zoo', 'Springfield', 'BA','102910'),
    ('Marge', 'Simpson', 'marge.simpson@fox,net', 'Springfield Homes', 'Springfield', 'BA', '102910'),
    (null,'BArt', 'bart@fox,net', 'Springfield Zoo', 'Springfield','BA', '102910')

users.sql

-- create user
CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';

/*
grant user privileges
user may not be able to log in without any privileges
*/
GRANT ALL PRIVILEGES ON `testdb\_%`. * TO 'test'@'localhost';

-- enable changes
FLUSH PRIVILEGES;

-- check privileges for curr user

SHOW GRANTS FOR CURRENT_USER();

-- revoking privileges
REVOKE ALL PRIVILEGES ON db.hostname FROM `test`@'localhost';