Lecture 22 - More SQL

Today's Agenda

Reminders

  • CP7 due today
  • Homework 5 (Pokedex) is due on Wednesday

Database manipulation (create/insert)

Full-stack website organization

A Full stack website consists of three layers.

  1. Web Browser (client): HTML, CSS, JS
  2. Web Server: PHP
  3. Database Server: SQL
  • The web server makes requests of the database server much like our javascript used AJAX
  • Unlike our asynchonous AJAX calls, our PHP code will only be accessing our databases in a synchronous manner.

Running a MySQL server on Cloud9

  1. Open a new terminal (Window menu or Alt-T)
  2. Type in mysql-ctl start to start the MySQL server
  3. Type in mysql-ctl cli to begin entering SQL commands

Running a PHPMyAdmin Cloud9

  1. Open a new terminal (Window menu or Alt-T)
  2. Type in phpmyadmin-ctl install to install (first time only)
  3. Navigate to https://<your workspacename>.c9users.io/phpmyadmin with your Cloud 9 user name and a blank password.

Databases

Each MySQL server can have multiple databases.

CREATE DATABASE database_name; creates a new database

SHOW DATABASES; displays a list of the databases

USE database_name; selects a database for use

CREATE TABLE

CREATE TABLE is used to create a new table.

Syntax:


CREATE TABLE table_name(
  column1 datatype,
  column2 datatype,
  column3 datatype,
  .....
  columnN datatype,
  PRIMARY KEY(one or more columns)
);

SQL (template)

CREATE TABLE example


CREATE TABLE todos(
  task_num INT,
  task_name VARCHAR(255),
  due_date DATETIME,
  task_type VARCHAR(255),
  PRIMARY KEY(task_num)
);

SQL (example)

Data Types

A full list of data types are here.

Data Type Desription
CHAR(size) Holds a fixed length string. The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) Holds a variable length string. The maximum size is specified in parenthesis. Can store up to 255 characters, if you have more, use TEXT.
TINYTEXT Holds a string with a maximum length of 256 characters
TEXT Holds a string with a maximum length of 65,535 characters
INT(size), TINYINT(size), BIGINT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d), DOUBLE(size, d) A small or large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DATE(), DATETIME() A date or time and date format.

Other table commands

SHOW TABLES; displays a list of the tables

DESCRIBE table_name; display the columns of the table

DROP TABLE table_name; deletes a table

DROP TABLE IF EXISTS table_name; deletes a table only if it exists, which prevents an error if it doesn't

INSERT

INSERT is used to insert a new record into an existing table.

Syntax:


INSERT INTO TABLE_NAME (column1, column2,...columnN)
  VALUES (value1,value2,...valueN);

SQL (template)

Example:


INSERT INTO CUSTOMERS (id,name,age,address,salary)
  VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00);

INSERT INTO CUSTOMERS (id,name,age,address,salary)
  VALUES (2, 'Khilan', 25, 'Delhi', 1500.00);

INSERT INTO CUSTOMERS (id,name,age,address,salary)
  VALUES (3, 'kaushik', 23, 'Kota', 2000.00);

SQL (example)

INSERTing many rows

You can insert many rows at once by doing this: 


INSERT INTO CUSTOMERS (id,name,age,address,salary) VALUES
 (1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
 (2, 'Khilan', 25, 'Delhi', 1500.00),
 (3, 'kaushik', 23, 'Kota', 2000.00);

SQL (example)

UPDATE


UPDATE table
  SET col1 = val1, col2 = val2, ...
  WHERE condition;

SQL (template)


UPDATE grades
  SET grade = 4.0
  WHERE netId = "kmthayer";

SQL (example)

UPDATE changes all rows where the condition is true.

DELETE


DELETE FROM table
  WHERE condition;

SQL (template)


DELETE FROM grades
  WHERE netId = "kmthayer";

SQL (example)

Deletes the specified records based on the results of the WHERE clause

CAUTION: if you omit the WHERE clause all records will be deleted!!!