• Getting Started
• Basics
• Arrays
• Loop/Branch
• Functions
• OOP
• Libraries
• Files
• MySQL
• SQL
• PHP/MySQL
• Regex
• Email
• Date/Time
• Web
• Forms
•  
•  
•  

Understanding Structured Query Language (SQL)

• Retrieving Data Using SELECT

• Adding Data Using INSERT

• Editing Data Using UPDATE

• Removing Data Using DELETE

• Performing Advanced Queries Using JOIN

• LAB: Creating Select, Insert, Update and Delete SQL Statements

Retrieving Data Using SELECT

• What is SQL?

  
  

  SQL stands for Structured Query Language.
  It is a standardized way of communicating with a database

• What is the SELECT command used for?

  
  

  The SELECT command is used to read data from the database

• How important is this command?

  
  

  This is by far the most widely used command in SQL.
  It is essential that you gain an understanding of its use.

• What will this command do to help build a website?

  
  

  Mastery of the SELECT statement will give you the ability to
  present meaningful answers to user queries on your website

• How do you get a readout of the entire table?

  
  

  SELECT * FROM name_of_table

• How do you get only certain columns?

  
  

  Names of columns: SELECT col1, col2, [etc.] FROM name_of_table
  You can also use MySQL functions such as AVG(), COUNT(), MIN(), MAX() and SUM()

• What goes after the "FROM" clause?

  
  

  The table name, or an alias: SELECT * FROM name_of_table AS alias

• What does the "WHERE" clause do and what is the syntax?

  
  

  The "WHERE" clause acts like a filter and gives you a subset of rows from the table.
  SELECT * FROM table_name WHERE column = value
  Other operators can be used: > => < =< etc.
  Combine clauses using "AND" / "OR"

• What if you only know part of the text you want to search for?

  
  

  SELECT * FROM table_name WHERE column LIKE "%fragment%"
  Wildcards: % = 1 or more characters, _ = 1 character, [abc] = these characters

• What are some other clauses which might be of use?

  
  

  ORDER BY field ASC | DESC -- performs a sort
  GROUP BY field -- consolidates similar rows
  LIMIT 20 -- limits the results to 20 rows
  OFFSET 50 -- starts at row 50

Adding Data Using INSERT

• How do you insert a row of data into a table?

  
  

  INSERT INTO table (col1, col2, etc)
  VALUES (value1, value2, etc)

• Is the order of the data to be inserted important?

  
  

  The order of the data MUST match the order of the corresponding columns!

• How about multiple rows?

  
  

  Repeat items in () after VALUES
  INSERT INTO table (col1, col2, etc) VALUES
  (value1, value2, etc),
  (value1, value2, etc),
  (value1, value2, etc),etc

• Do you always have to insert values into all columns?

  
  

  No ... however if you do not include data for a column which cannot be NULL,
  you will trigger an error message

• What about NOT NULL columns?

  
  

  Columns marked NOT NULL must have a value, otherwise an error message is triggered

• What about defaults assigned when you create the table?

  
  

  Columns with defaults will be filled automatically if you do not provide data when performing INSERT

• What happens if there is a constraint?

  
  

  You cannot insert a row into the child table before having the related value inserted into the parent table

Editing Data Using UPDATE

• How do you change the information in a row of data in a table?

  
  

  UPDATE table
  SET `col1`= 'value-1',`col2`= 'value-2', etc
  WHERE `colX`= 'some value'

• Can you update more than one row at a time?

  
  

  Yes

• Can you update more than one column at a time?

  
  

  Yes: up to all columns in the row

• What happens if you update the column containing the primary key?

  
  

  This will work only if you do not change to an already existing primary key value.
  Otherwise you will see this error: Duplicate entry "nnn" for key "PRIMARY"

• What happens when you try to update a row where there is a constraint?

  
  

  If the ON UPDATE constraint is set to "CASCADE" changes in the parent will affect the child table(s) as well.
  If you try to change the key in the child table, you can only use values set by the relationship from the parent table, otherwise an error will be generated: Cannot add or update a child row:
  a foreign key constraint fails

Removing Data Using DELETE

• How do you delete a row of data in a table?

  
  

  DELETE FROM `table`
  WHERE `colX`= 'some value'

• Can you delete more than one row at a time?

  
  

  Yes. This also represents a danger when developing PHP code to delete:
  make sure you do not accidentally delete the entire table!

• What happens when you try to delete a row where there is a constraint?

  
  

  If the ON DELETE constraint is set to "CASCADE" deleting a parent row
  will cause related rows in the child table(s) to be deleted as well.
  If the ON DELETE constraint is set to RESTRICT, you cannot delete a row in the parent
  until related rows in the child table(s) have first been deleted.

Performing Advanced Queries Using JOIN

• Why would you want to use the JOIN clause?

  
  

  The JOIN clause is used with the SELECT statement to produce a
  single consolidated result from two or more tables.
  Using JOIN is important when you want to give your customers information which is stored across tables.

• What are the conditions required for a JOIN?

  
  

  Each table added to the view needs to have keys in common

• What are the most common types of JOIN?

  
  

  There are many types of JOIN.
  The most common is the [INNER] JOIN.
  Also used, but less frequently: LEFT JOIN, RIGHT JOIN, CROSS JOIN and others.
  We focus only on JOIN in this course

• How do you join two tables?

  
  

  SELECT * FROM table1
  JOIN table2 ON table1.key = table2.key

• Can you use a WHERE clause with a JOIN?

  
  

  Yes: just add the WHERE clause at the end

• What is meant by an error message indicating a column in the WHERE clause is ambiguous?

  
  

  You need to add the table name to the column name using a "."

• How do you join more than two tables?

  
  

  SELECT * FROM table1
  JOIN table2 ON table2.key = table1.key
  JOIN table3 ON table3.key = table2.key

• What if you only want certain columns?

  
  

  SELECT table1.col1, table1.col2, table2.col1, table2.col2, table3.col1, table3.col2
  FROM table1 AS a
  JOIN table2 AS b ON b.key = a.key
  JOIN table3 AS c ON c.key = b.key

• Are there any shortcuts when representing table or column names?

  
  

  SELECT * FROM table1 AS a
  JOIN table2 AS b ON b.key = a.key
  JOIN table3 AS c ON c.key = b.key

• What about using other clauses and functions?

  
  

  You can add any desired functions after SELECT key word,
  and also other clauses at the end, such as ORDER BY, GROUP BY, etc.

LAB: Creating Select, Insert, Update and Delete SQL Statements

• What is the purpose of the lab?

  
  

  In this lab you will be defining a series of SQL statements
  which will be used in later labs

• Create a table "members" ...

  
  

  Name	Type	Size	Null	And ...
  user_id	int	8	N	Primary Key, Auto Increment
  name	varchar	256	N	Collation: latin1_general_cs
  address	varchar	256	N
  city	varchar	64	N
  state_province	char	32	N
  postal_code	char	10	N
  country	char	3	N
  phone	char	16	Y
  balance	decimal	10,2	Y
  email	varchar	250	N	unique index
  password	varchar	16	N	index

• Import data ....

  
  

  • Select the "members" table
  • Select "Import"
  • Click "Choose File"
  • From the WorkingFiles folder for this chapter select sql_members.csv
  • Make sure "Format" indicates "CSV"
  • Click "Go"

• Create SQL statements ...

  
  

  • Lookup one member if you know the email address
  • Add a new member
  • Remove a member if you know the email address
  • Update member information if you know the email address
  • All products sorted by title with a limit of 20 and an offset of 40
  • All products on special
  • A count of all products
  • All products where you know only part of the title or description
  • For one member: all purchases including product details

• Verify results ...

  
  

  Select "SQL" and type in your statements
  Be sure to save these statements in a temporary file
  as they will be used in other labs!