================== 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)