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.

Written on May 22, 2013