SQL Notes

## setup
Add alias to ~/bash_profile. mmysql will start MAMP mysql

alias mmysql='/Applications/MAMP/Library/bin/mysql'

	exit; 
	SHOW databases;
## create database
	CREATE DATABASE intrepid_detectives;
	USE intrepid_detectives;
	SHOW tables;

## create table

	CREATE TABLE investigations(
	-> title varchar(100),
	-> detective varchar(30),
	-> daysToSolve integer);
	
	EXPLAIN investigations;
Or use one of these,

	SHOW FIELDS FROM table / SHOW COLUMNS FROM table / DESCRIBE table / DESC table / EXPLAIN table
## inserting records

	INSERT INTO investigations VALUES (“Finnigan’s Fault”,”Corlotta McOwen”,4);

Inserting data not in order.

	INSERT INTO investigations(daysToSolve, title detective) VALUES (3, “The Missing Tree”, “Finch Hosky”);

Inserting data partially.

	INSERT INTO investigations(title, detective) VALUES(“A case of allergies”, “Carson Andres”);

## selecting

Display data

	SELECT * FROM investigations;

	SELECT title, detective FROM investigations;
Any order
	
	SELECT daysToSolve, title FROM investigations;
Out put will the same as select order.

## datatypes

varchar(100)

    Create TABLE person(name varchar(100));
    
text

	Create Table email (body text);     

Numbers
    
int, or integer

	CREATE TABLE person(age integer);

int unsigned can have from 0 to 4.2billon numbers.

BIGINT, SMALLINT, MEDIUMINT, TINYINT	
    
decimal (precision, scope) 

	precision: number of digit
	scope: number of digit after the decimal point.
	
	(10,0) 1234567890
	(5,2) 123.45
	CREATE TABLE student (avg_score decimal (6,2));

	CREATE TABLE student(id integer auto_increment);

auto_increment

	CREATE TABLE student(id integer auto_increment);
	
Date and time

	date 		'YYYY-MM-DD'
	time		'[H]HH:MM:SS'
	datetime	'YYYY-MM-DD HH:MM:SS'
	CREATE TABLE order(order_date date);
	
Booleans

tinyint(1)

	CREATE TABLE order(fulfilled boolean);

Default values

	CREATE TABLE order(
		coupon varchar(10) defaul "nodiscuont",
		customer_id integer default NULL,
		dateitme datetime default current_timestamp
		fulfilled boolean NOT NULL default 0
	);


## primary-key

Database concept #6

Every table should have a primary key, a column that uniquely identifies each row. It can never be null, and must be set on record creation and never changed.

	use intrepid_detectives;
	show tables;
	CREATE TABLE detectives(
	id INT NOT NULL auto_increment,
	name varchar(100),
	phone_number varchar(100),
	certificationDate date,
	CONSTRAINT detectives_pk PRIMARY KEY (id)
	);
	
CONSTRAINT is a rule on the table. And detectives_pk is the name of CONSTRAINT and pk stands for primary key.And this constraint is the primary key, and tell what column is primary key.

	EXPLAIN detectives;
	
	
Natural primary key(social security number) and surogate key(id). 
	
	INSERT INTO detectives(name, phone_number,certificationDate)
	VALUES("Carlotta McOwen", "12345678", "2002-03-23");
	
	SELECT * FROM detectives;
	
Add one more date.

Adding with id number.

	INSERT INTO detectives VALUES(5, "Luther Ellery", "212345", "2005-03-11");
	
This will add id of 5 into db.




### Resources
Discovering SQL by Alex Kriegel

Professor, Student and Course 

Script at: https://gist.github.com/3055655

Run with: mysql -u root -p < prof.sql