Lecture 12: Database Basics, Designing Databases

• database:
  • a collection of information / data
  • goal: store (insert), manage (edit), and retrieve (search) data
  • has an interface that allows users to interact with the data
• SQL vs NoSQL
  • SQL databases
    • tabular and flat
    • relational
  • NoSQL databases
    • not tabular and can be nested
    • allows for flexible structure
    • non-relational databases
• SQL Databases
  • Primary key: A field or set of fields that uniquely identify each record in a table
  • Foreign key: A field or set of fields that uniquely identify a record in another table
  • data type
    • numeric: int, float, decimal, etc.
    • strings: char, varchar, etc.
    • date&time: date, datetime, time, year, etc.
    • Boolean: BIT (1 or 0)
  • relationship type between tables
    • one-to-one: country - current president
    • one-to-many: team - members
    • many-to-many: classes - students
  • normalize or not normalize
    • Do normalize fields that can be its own “entity” and shows up in multiple rows
    • Don’t normalize any field that has can be infinite and would need to be updated constantly (like year, price)

Lecture 13: Intro to SQL, Retrieving Data from DB

• Structured Query Language — SQL

  • used by most database
  • not case-sensitive commands

• retrieving data

  SELECT -- columns to retrieve from DB
         -- separate columns with commas
         -- * means all columns
  FROM  -- tables to retrieve from DB, separate tables with commas
  WHERE  -- conditions while retrieving data
  ORDER BY  -- sort data by specified columns
  				  -- ASC: ascending default
  					-- DESC: descending
  -- Operators in WHERE
  = -- equal to
  <>  -- not equal to
  > < >= <=
  IS NULL
  IS NOT NULL
  BETWEEN a AND b  -- greater than a and less than b (inclusive)
  LIKE  -- search for pattern often use with wildcards %
  AND / OR -- combine conditions
  
  JOIN  -- combine records from multiple tables
  			-- based on table relationships
  SELECT columns  -- resolve name conflicts: prefix with table names
  FROM left_table
  JOIN right_table
  	ON left_table.fk = right_table.pk;
  
  SELECT tracks.name AS track_name  -- aliasing
  FROM tracks;
  

Lecture 14: Data Manipulation in SQL

• add

  INSERT INTO table_name(col1, col2, ...)
  VALUES (val1, val2, ...);
  

• update

  UPDATE table_name
  SET col = val
  WHERE condition; -- specify which records to update
  						 -- without where clause, all records will be updated
  

• delete

  DELETE FROM table_name
  WHERE condition;
  -- without where clause, all records will be deleted
  

• views

  • virtual tables created from SQL queries

    • contain rows and columns
    • can be queried like tables

  • reason to use

    • security: only make certain rows and columns available
    • simplicity: no need to keep joining tables for every query

    CREATE OR REPLACE VIEW view_name AS
    	SELECT cols FROM tab;
    
    DROP VIEW view_name;
    

  • aggregate functions

    • count() count number of rows
    • MIN() / MAX() find column's min / max value
    • AVG() calculate column's average value
    • SUM sum column's total values
    • RAND() generate random number between 0 and 1
      • use with ORDER BY to SELECT records in random order
    • CHAR_LENGTH() return length of given string
    • CONCAT() concatenate strings

    SELECT COUNT(*)
    FROM albums
    WHERE artist_id = 8;
    
    SELECT MIN(milliseconds), MAX(milliseconds)
    FROM tracks;
    
    SELECT AVG(milliseconds)
    FROM tracks
    WHERE album_id = 3;
    
    SELECT SUM(milliseconds)
    FROM tracks
    WHERE genre_id = 3;
    
    SELECT *
    FROM tracks
    ORDER BY RAND();
    
    SELECT name, CHAR_LENGTH(name) AS name_length
    FROM tracks;
    
    SELECT CONCAT(name, ' was composed by ', composer)
    FROM tracks;
    

    • GROUP BY group results by data in specified column

      -- show artists and number of albums for each artist
      SELECT album_id, name, COUNT()*
      FROM albums
      JOIN artists
      	ON album.artist_id = artists.artist_id
      GROUP BY artists.artist_id;
      
      -- find shortest track for each album
      SELECT album_id, MIN(milliseconds)
      FROM tracks
      GROUP BY album_id;
      

Lecture 15: Intro to PHP

• PHP: Hypertext Preprocessor

• open-source server-side scripting language

  • code / script executed by server's PHP interpreter
  • client browsers only see generated HTML code

• created for web development

• syntax

  // all PHP code goes between opening and closing tags
  <?php
  	// comment
  	# comment
  	/*comment*/
  	# every line must end with ;
  	# variable name start with letter or underscore, case-sensitive
  	$variable = "hello world!";
  	echo "print" . $variable;
  	# superglobals (built-in)
  	$_SERVER  // array with server and environment information
  	$_GET  // array of HTTP GET variables
  	$_POST  // array of HTTP POST variables
  	echo $_SERVER['HTTP_HOST'];
  	echo $_SERVER['HTTP_USER_AGENT'];
  	echo $_GET['track_id'];
  	echo $_POST['track_name'];
  ?>
  

• HTTP POST Method

  • when
    • security is important
    • sending large amount of data
    • usually used to insert/update data

  <form action="" method="POST">
  	Username:
  	<input type="text" name="username">
  	<button type="submit">Submit</button>
  </form>
  <?php
  	echo $_POST['username'];
  ?>
  

• HTTP GET Method: submits all form data through URL

  • when
    • handling non-sensitive data
    • sending small amount of data
    • users can bookmark data
    • usually used to retrieve data

  <form action="" method="GET">
  	Username:
  	<input type="text" name="username">
  	<button type="submit">Submit</button>
  </form>
  <?php
  	echo $_GET['username'];
  ?>
  

Lecture 16: PHP MySQLi