Mysql class notes:

Purchased a class frmo www.eduonix.com for $19. Supposedly going to teach me everything there is to know about mysql.

Lessons learned:

Commands:

mysql> show engines
shows the list of supported engines.
mysql_install_db
Configure necessary directory structures
/usr/bin/mysqladmin -u root password ‘new-password’
Sets password for root user

Overview:

Standard overview of the class.

Chapter 1: db generics

  • What is a db
  • Table example
  • Create Read Update Delete (crud) = DML (data manipulation language)
  • Overview of db models.
  • server process is mysqld

Chapter 2: intro:

  • Engines:

    • Default:

      • MyISAM used to be default
        • Good for low insert/updates
        • Good for very high select rates.
        • Uses table level locking.
      • InnoDB is the current default.
        • Uses row level locking
        • Foreign key functionality.
    • Reasons for selection:

      • Amount of data
      • Speed
      • Functionality
      • Max number of rows
      • Data integrity
    • Examples:

      create table table1 (i int) engine = innodb;
      create table table2 (i int) engine = csv;  # interesting.
      
  • Data types:

    • Main categories:
      • Numeric
        • tinyint ~ boolian
        • float
      • Strings
        • varchar
        • enum: a list
      • Date/time
        • date
        • datetime
        • timestamp ~ epoch

Chapter 3: Getting started w/mysql:

  • Installation: mysql installation on windows appears borked. Got it running but it’s not quite as smooth as the tutorial even though it’s the same version.
  • Linux was already installed.
    • mysql-server
    • ubunut: need mysql-client
  • xampp:
    • graphical interface for mysql, phymyadmin, and others.
    • downloaded/installed on linux, windows. had some battles.
      • Both systems were runnign previously installed mysql. Doesn’t work.
      • Windows tries t use the same port as skype.
      • Start process in linux is /opt/lampp/lammp [start|stop]
  • If I decide I like phymyadmin, it’s available from epel.
  • phymyadmin
    • http://locahost/phpmyadmin
    • When creating users to log in via phpmyadmin, ensure localhost is defined in the host entry.
    • Looks like a gui front end. wasn’t overly impressed.
  • mysql workbench
    • This one is nice. Provides EER docuemntation and can do all the things

Chapter 3.5: Redo

Reloaded nap a few times until I got it right. Mirrored the mysql repos on my spacewalk server, then built nap a few times until I got everything running.

phpmyadmin took a few times to get right. The default httpd config file was wrong, permissions on the /var/lib/mysql directory were wrong. After getting all that working, I have the kickstart post script fully functional.

Chapter 4: Normalization:

  • Seems his narratives are out of order. He’s talking about doing stuff in the future that we’ve already done.
  • Relational model rules:
    • Each column value must be a single value.
    • All values of a given column must be the same data type.
    • Each column name must be uniquue
    • column order is insignificant.
    • No two rows in a relation can be identical
    • Order of the rows is insignificant.
  • Functinal dependency: describes a relationship between columns within a single realtion
    • A column is dependent on another if one value can be used to determine the value of another.
  • Common mistakes:
    • Tables with too many fields or fields that don’t relate to each other.
    • Too many tables with similar data.
    • Repeated rows
    • Using comma sepaarate values or multiple values in a single row
    • Poor naming conventinos
    • Poor or no planning
    • Non-normalized data.
  • Normalization:
    • The process of organizing fields and tables to minimize redundancy and dependency.
    • Can involve dividing larger tables into smaller tables with relationships
    • Objective is to isolate data so that actions in a field can be made in one table which then propogates through the rest of the needed tables using properly defined relationships.
  • Forms:
    • 1NF:
      • No repeating rows or duplicate fields
      • Each row contains only one value
      • Each row/record is unique
      • Each row is identified through a primnary key
    • 2NF
      • Already in 1NF
      • All non-key fields depend on all components of primary key
      • No partial dependencies.
    • 3NF
      • Alread in 2NF
      • Every non-prime attribute must depend n the primary key
      • eg: addresses should be in ther own table, not in the customers table.
  • Design process:
    • Define your purpose: What is the reason for the application?
    • Determine your tables
    • Determine your fields/columns
    • Determine relationships
    • Create an EER diagram
    • Refine as needed.
  • databaes mapping:
    • products
      • id: (int(11), auto_increment, primary_key)
      • name (varchar(100))
      • description (text)
      • price (varchar(20))
      • category: (int(11), foreign_key -> products_categories id)
      • image (varchar(100)
    • products_categories
      • id: (int(11), auto_increment, primary_key)
      • name (varchar(100))
      • description (varchar(100))
      • image (varchar(100))
    • customers
      • id: (int(11), auto_increment, primary_key)
      • first (varchar(100))
      • last (varchar(100))
      • email (varchar(100))
      • password (varchar(100))
      • avatar (varchar(100))
      • join_date (timestamp, default- current_date)
    • customer_addresses
      • id: (int(11), auto_increment, primary_key)
      • custmoer: (int(11) foreign key to customers:id)
      • address (varchar(100))
      • address2 (varchar(100))
      • city (varchar(100))
      • state (varchar(100))
      • zipcode (varchar(100))
    • reviews
      • id: (int(11), auto_increment, primary_key)
      • customer: (int(11), foreign key to customers:id)
      • title (varchar(100))
      • body (text)
      • rating (int(2))
      • product: (int(11), foreign key to products:id)
      • review_date (timestamp, default - current_date)
    • pages
      • id: (int(11), auto_increment, primary_key)
      • title (varchar(100))
      • body (TEXT)
      • category: (int(11), foreign key to page_categories:id)
      • create_date (timestamp, default - current_date)
    • page_categories
      • id: (int(11), auto_increment, primary_key)
      • name (varchar(100))
      • descripton (varchar(100))
      • image (varchar(100))

Chapter 5: Creating the database:

  • First section was simply typing the above into myphpadmin. I think the command line would have been easier.
  • Author suggests exporting the empty database, once the schema’s created, in order to have the empty

Chapter 6: SQL fundamentals:

  • Objectives:

    • union
    • concat
    • count
  • Hae to correct the sql ‘cause I didn’t follow the same naming convetion. BAD student! BAD!:

    INSERT INTO products
    (name,description,price,category,image)
    VALUES
    ("Surface Tablet","Microsoft Surface Pro with 128GB Memory","499.99",2,"images/surface.jpg"),
    ("Nextbook Tablet","Nextbook 7 inch Tablet 8GB Memory","99.99",2,"images/nextbook.jpg"),
    ("Philips BTM2180/37 Micro Music System","Wireless music streaming via Bluetooth","149.99",2,"images/stereo.jpg"),
    ("Memorex Boombox","Memorex MP3142 - Boombox","45.99",2,"images/boombox.jpg"),
    ("Mossy Oak Infinity Seat Cover","High quality, heavy Mossy Oak Infinity Seat Cover","19.99",6,"images/seatcover1.jpg"),
    ("Remington Floor Mat Set","Remington Best Fashion Carpet Floor Mat Set Tan 4pc","29.99",6,"images/floormat1.jpg"),
    ("Rawlings RBG36 Baseball Glove","Rawlings RBG36 Baseball Glove 12 inch","29.99",4,"images/glove1.jpg"),
    ("Franklin Pro Batting Gloves","Franklin Sports MLB Shok-Sorb Pro Batting Glove Pair","19.99",4,"images/glove2.jpg"),
    ("Golds Gym Trainer 420","NEW Gold's Gym Trainer 420 Treadmill with Bonus Puzzle Mat","377.00",4,"images/treadmill1.jpg"),
    ("Weslo Cadence G 5.9 Treadmill","Weslo Cadence G 5.9 Treadmill","287.00",4,"images/treadmill2.jpg"),
    ("Golds Gym Trainer 315 Treadmill","Gold's Gym Trainer 315 New Model Treadmill","258.00",4,"images/treadmill3.jpg");
    
    INSERT INTO customers
    (first,last,email,password,avatar)
    VALUES
    ("Sandy","Williams","sandy@gmail.com","1234","images/sandy.jpg"),
    ("Ben","Barro","ben@gmail.com","1234","images/ben.jpg"),
    ("William","Terry","william@gmail.com","1234","images/william.jpg"),
    ("Sarah","Thompson","sarah@gmail.com","1234","images/sarah.jpg"),
    ("Donald","Duck","donald@gmail.com","1234","images/donald.jpg"),
    ("Beth","McAdams","beth@gmail.com","1234","images/beth.jpg"),
    ("Harry","Henderson","harry@gmail.com","1234","images/harry.jpg");
    
    
    INSERT INTO customer_addresses
    (customer,address,address2,city,state,zip)
    VALUES
    (1,"10 Main st",NULL,"Boston","MA","01929"),
    (2,"20 Main st","Apt 3","New York","NY","01929"),
    (3,"30 Main st",NULL,"Seaside Heights","NJ","01929"),
    (4,"40 Main st","Unit 10","Baltimore","MD","01929"),
    (5,"50 Main st","Apt 18","Miami","FL","01929"),
    (6,"60 Main st",NULL,"Chicago","IL","01929"),
    (7,"70 Main st",NULL,"Sacramento","CA","01929");
    
    INSERT INTO reviews
    (title,body,rating,customer,product)
    VALUES
    ("Great Product","This product was great",9,1,2),
    ("Okay Product","This product was okay",6,1,3),
    ("Decent Product","This product was decent",8,3,4),
    ("Bad Product","This product was really bad",2,5,4)