THE NEW BOSTON TUTORIAL NOTES:

SQL is used to store a database of related information in tables (i.e. a table for customer information, a table for products being sold, etc.)
f
****SQL LANGUAGE IS IN ALL CAPS (except for table names - these are lowercase!).**** (this is standard best practice and expected).

EVERY TABLE MUST HAVE A PRIMARY KEY WHICH IS UNIQUE FOR REFERENCING (usually called the 'id' column).

When running multiple queries at one time, each must have a semicolon ';' at the end of each command.  When only running one query command, then the semicolon is optional.

SQL is whitespace insensitive.

SQL runs queries from the inside out. (i.e. whatever is in () parens is run first and then runs queries outside of the parens.

--------

Note: make sure you are on the database or table path you are making the command for!


COMMANDS:

SHOW Command:

-SHOW DATABASES
-SHOW TABLES
-SHOW COLUMNS FROM customers (this shows the names of the columns for the table 'customers' in the database).  Note: make sure you have the database containing the table selected before you run this command.

SELECT Command:

(selects the information or data that you want)
Syntax: SELECT [information] FROM [tablename]
Ex:
-SELECT city FROM customers (selects the city column from the table customers and displays them).

*You don't need multiple SELECT statements for more than one piece of information:
You can retrieve more than one piece of information with the SELECT command statement:
separate them with a comma ','.
*Do not put a comma after the last piece of information!
Ex.:
-SELECT name, zip, address FROM customers

This displays the three columns with one SELECT statement.

To retrieve all data (use the 'wild card statement' - asteriks - symbol):
SELECT * FROM customers;
-shows all columns and rows from the customer data table. 

**Only SELECT data in the table that you need to work with, do not select all data and then hide what you don't need (it will slow down the query and the website when called this way). Common newbie mistake.

------------------------------------------------

(TUTORIAL 7)

LIMIT cont'd: 

LIMIT: use to limit the number of results shown from a query.
Ex:
SELECT name, zip FROM customers LIMIT 5;


For a range that doesn't start at the beginning (add a second number separated by a comma):
syntax:
starting point number, how many rows/data you want to retrieve.

Ex:
SELECT name FROM customers LIMIT 5, 10;

This will select row 6 (counter starts at 0), and display 10 rows of the information starting at row 6.

--------------

Tutorial 8: Sorting Results


FULLY QUALIFIED NAMES:

Not necessary to use (only for better readability)

use dot separator to specify the table you're pulling data from:

Ex. SELECT customers.address FROM customers

Useful reason to do it: enables user to easily identify which table they are working with if selecting a row that is named the same thing:
i.e. buyers.address or sellers.address (not necessary, but makes it easier to see which table you're affecting).

----

SORTING:

use keyword ORDER BY then criteria:

Ex. SELECT name FROM customers ORDER BY name;

ordered by id # even though the id # was not retrieved:
Ex. SELECT name, address FROM customers ORDER BY id;

Retrieve multiple columns and sort:

Ex. SELECT state, city, name FROM customers ORDER BY state, name;  

//this orders the first selection (state) first, and then within the state it sorts the names of it in order.

------------

Tutorial 9: Sort in Reverse Order-

add the keyword DESC ('descending' meaning high to low) (the default is ASC - ascending - no need to write this).

SELECT name, zip FROM customers SORT BY zip DESC;

//data is sorted by zip code, and the highest is first instead of the lowest.

To select a range or highest/lowest from an ordered list use LIMIT:

Ex. SELECT name, id FROM customers ORDER BY id DESC LIMIT 1;

//this selects the first name with the highest id # only.

-------------------

Tutorial 10: FILTERING


Select data from a row with a specific value in it.  Use WHERE keyword:

Note: FILTERS come after selecting the item and the table to select from (after the FROM [table] statement).

Ex. SELECT id, name FROM customers WHERE id=54;

//this returns the name and id information from the entry with an id of 54.

To Exclude certain results, use the not equal to operator ('!='):

Ex.: SELECT id, name FROM customers WHERE id !=54;
//this returns ids and names of ids that do not equal 54 (all except id# 54).

Can also use < or > to get a range of ids:

Ex: SELECT id, name FROM customers WHERE id < 54;
//returns data from rows where id is less than 54.
Note: can also use <= or >=.

Between test:

Use the BETWEEN keyword with AND to separate the two values:

SELECT id, name FROM customers WHERE id BETWEEN 25 AND 30;
Note: the values are included, so this will show ids 25 through 30, instead of 26 to 29.

NOTE: When working with text or alpha characters and WHERE, you need to surround them in quotes:

Ex: SELECT state FROM customers WHERE state='CA';

--------------

TUTORIAL 11: Advanced FILTERING AND and OR


Use AND to specify two values to test for and return the result with both etc.:

Ex: SELECT name, state, city FROM customers WHERE state='CA' AND city='Hollywood';

Using OR:

SELECT name, state, city FROM customers WHERE city='Boston' OR STATE='CA';
//will return results with city of Boston or state of CA (not necessarily both as with AND).

***When using multiple filters (like AND OR) in a query, make sure to use parentheses to group your criteria to prevent SQL from misinterpreting it (because of left to right associativity):

SELECT id, name, city FROM customers WHERE (id=1 OR id=2) AND city='Raleigh';

//SQL needs the parentheses so it doesn't combine id=2 AND city='Raleigh' to give you undesired results).

---------------

Tutorial 12: IN or NOT IN

Used when you want to query multiple values using the OR filter, instead of writing each one after OR, just use the keyword IN (short for includes) with parens for the values to test for:

Ex: SELECT name, state FROM customers WHERE state IN ('CA','NC','NY');
//this selects the entries that have a state value of either CA, NC, or NY, without having to write multiple OR keywords for each one.


Use NOT IN keyword to exclude results desired:

Ex: SELECT name, state FROM customers WHERE state IN ('CA','NC','NY');
//returns list without these states.

Note: NOT keyword reverses any test it is in front of.

-----------

Tutorial 13: How Search Engines Work


WILDCARDS:

% sign is a wild card that means 'anything'

using the LIKE keyword to specify values to test for with WHERE:

Ex:
SELECT name FROM items WHERE name LIKE 'new%';
//this returns results from the name column that start with the characters 'new' and are then followed by anything (including spaces).  

to search for a term value anywhere in the order of the value chain, use two % before and after the value to search for:

Ex: SELECT name FROM items WHERE name LIKE '%new%';
//returns results with a name containing 'new' anywhere in the name (not just at the beginning).

SELECT name FROM items WHERE name LIKE 'h%d';
//this returns results with a name that starts with 'h' and ends with 'd' (any characters can be in between the characters).  Note: NOT Case Sensitive.

To search multiple columns:
use the AND or OR operators, depending on what you want the search to return.

SELECT title FROM pages WHERE my_col LIKE %$param1% AND another_col LIKE %$param2%;
Both clauses have to match for a record to be returned. Alternatively:

SELECT title FROM pages WHERE my_col LIKE %$param1% OR another_col LIKE %$param2%;
If either clause matches then the record will be returned.

-------------------

Tutorial 14: More Wildcards

Using the '_' (underscore) wildcard:
A wildcard that can only match one character. 


SELECT name FROM items WHERE name LIKE '_boxes of frogs';
//returns results that match the string with only one character in front of 'boxes'.

--------------------

Tutorial 15: Regular Expressions

Keyword: REGEXP
Used to search for more complex patterns outside of the wildcards. with this option you do not need to use % or _ wildcards to find a set of characters anywhere in a value name, use the keyword REGEXP with the value in quotes: 

Ex: SELECT name FROM items WHERE name REGEXP 'new';
//this selects all name entries with 'new' in them.

----

Using the DOT (.).  This indicates that any single character can be in it's place (space, letter, number etc):   

Ex.: SELECT name FROM items WHERE name REGEXP '.boxes';
//returns values that have 'boxes' in them and the character before that can be anything (a space, number, or letter). 

----

Use | for OR command:

Ex: SELECT name FROM items WHERE name REGEXP 'gold|car'; 
//returns results with either gold or car in them.
note: the character combination is searched not necessarily as a separate word (i.e. 'car' can return 'carpet' since the characater set is in it.

----

Using a Set for multiple OR commands:
include a set of characters to search for in [] brackets.  runs a set of OR statements in one line:

Note: no commas are used in between the characters!

Ex: SELECT name FROM items WHERE name REGEXP '[12345] boxes of frogs';
//this returns the character set boxes of frogs which is preceeded by a space and any one of the numbers in the brackets.

if you want to test for results for two numbers before the string 'boxes of frogs' then use :
 
SELECT name FROM items WHERE name REGEXP '[0-9]{2,} boxes of frogs' 
or 
SELECT name FROM items WHERE name REGEXP '[0-9][0-9]+ boxes of frogs' 
//they are the same
Note: can use '-' for a range of characters, i.e. [a-z], or [1-10], etc.

You can Negate the character set in brackets by using the '^' sign:
SELECT name FROM items WHERE name REGEXP '[^12345] boxes of frogs';
//returns results that contain boxes of frogs but are not preceeded by 1-5 etc.

---------------------------------------------------

Tutorial 16: Custom Column creation

Use CONCAT fuction (concatonates) to temporarily join two columns together to create a temp new column.
You can concatenate existing columns and strings in quotes:

Ex. SELECT CONCAT(city, ', ', state) FROM customers;
//returns a new temp column containing the city separated by , and a space and the state

---

To name the new column use the keyword AS:

Syntax: SELECT [custom column] AS [name of new column]...

Ex: SELECT CONCAT(city, ', ', state) AS new_address FROM customers;
//

---

To make a custom column (using a function on an existing column) with new values:

SELECT name, cost, cost-1 AS sale_price FROM items ;

//this creates a new custom column with the cost values subtracted by 1 and then names it sale_price.  
Note: the new column was created when you take an existing column and perform  function on it to get new results (this creates a temp column that you can then rename with AS).  

-------------------------------------------------

Tutorial 17: Functions

Many functions (look them up online).  Examples of how to use functions in SQL:
Functions take a parameter (the column they are operating on), and create a custom column with the new values.

UPPER(columnname) - changes values of columns to uppercase letter and creates a custom column.

Ex: SELECT name, UPPER(name) FROM customers;

SQRT(columnname) - takes the square root of the column selected in parameters and creates a custom column:

Ex: SELECT cost, SQRT(cost) FROM items;

---

AGGREGATE FUNCTIONS:

Takes the values from an entire column and returns a single result value (i.e. the average, sum, etc.)

SELECT AVG(cost) FROM items;
//returns the average of the values from the column 'cost'.

To get average from a specific set of data (i.e. from a user) use WHERE keyword:
SELECT AVG(cost) FROM items WHERE seller_id=6;
  

SELECT SUM(bids) FROM items;
;returns the sum of the values from the column 'bids'.

------------------------------------------

Tutorial 18: Aggregate functions (cont'd)


COUNT() - returns a total of selected values
Ex. COUNT(*) returns the total number of rows or items in a table.

SELECT COUNT(name) FROM items WHERE seller_id=6;
//this returns the number of items from the column name that have a seller_id value of 6.

MAX() - selects highest value of the list
MIN() - selects lowest value of the list

----

Using multiple aggregate functions together:
tip: put each function on a new line and name each new custom column with 'AS' keyword for referencing it later.
Ex:
SELECT COUNT(*) AS item_count,
MAX(cost) AS max, 
AVG(cost) AS avg 
FROM items WHERE seller_id=12;

--------------------------------------------------

Tutorial 19: GROUP BY


Use GROUP BY keyword to run the function on a specified list:
Ex:
SELECT seller_id, COUNT(*) FROM items GROUP BY seller_id;
//this returns a column with the total number of items grouped by seller id (instead of having to write WHERE for each seller id).  

HAVING (like the 'WHERE' of groups)

***Tip: Use 'HAVING' filter for GROUP BY groups and 'WHERE' filter when dealing with specific rows.

SELECT seller_id, COUNT(*) FROM items GROUP BY seller_id HAVING COUNT(*) > 3;
//this returns sellers and number of items for each with a count of greater than 3.

-You can order group results with the 'ORDER BY' statement:

SELECT seller_id, COUNT(*) AS item_count 
FROM items 
GROUP BY seller_id 
HAVING COUNT(*) > 3 
ORDER BY item_count DESC;
//orders the new column created called item_count in descending order (highest to lowest).  

-------------------------------------------------

Tutorial 20: Subqueries

A query inside another query.

Ex.:
SELECT AVG(cost) FROM items (this query is = to the average of the cost column number)


Without a subquery:
SELECT name, cost FROM items WHERE cost>(463) ORDER BY cost DESC;

With a subquery:
SELECT name, cost 
FROM items 
WHERE cost>(SELECT AVG(cost) FROM items)
ORDER BY cost DESC;

//this creates a subquery containing the value of 463 (the average cost) instead of using the number from the result -- this way you can do all the work in one query.  Think of the subquery as a variable that holds the resulting value.
Note: SQL runs the inside (sub)queries first and then the outside queries.

---------------------------------------------------

Tutorial 21: Practical example of Subqueries

Find best price of an item:
1) find sellers selling items

ex: SELECT seller_id FROM items WHERE name LIKE '%boxes of frogs';

2) find cheapest of this list using MIN function and a subquery using the query above to find a list of the items

SELECT seller_id FROM items WHERE name LIKE '%boxes of frogs';

SELECT name, MIN(cost) 
FROM items 
WHERE name 
LIKE '%boxes of frogs' 
AND seller_id 
IN(SELECT seller_id FROM items WHERE name LIKE '%boxes of frogs'); 

--------------------------------------------------

Tutorial 22: How to Join Tables

This function makes a temporary table combining two existing tables.  It works when two tables have a common value being represented (i.e. idinfirsttable = othertable_id value, etc.)

SELECT [name of columns from each table you want to use - use fully qualified ids with dot] 
FROM [what tables info is coming from separated with commas - no comma after last one]
WHERE [how tables are related; what do they have in common]
ORDER BY [optional parameter to order by]

Ex:
SELECT customers.id, customers.name, items.name, items.cost 
FROM customers, items
WHERE customers.id=seller_id //(or items.id)
ORDER BY customers.id

--------------------------------------------------

Tutorial 23: Outer Joins

using AS to give a table a nickname

Ex:
SELECT i.seller_id, i.name, c.id
FROM customers AS c, items AS i  //<--name columns here.//
WHERE i.seller_id=c.id;

---
INNER JOIN: Combining two existing tables based on related data

OUTER JOIN: used to force all rows of one of the tables even if the related data is not present.
two versions 
LEFT OUTER JOIN = force all rows on the left table-the first one typed, or 
RIGHT OUTER JOIN = force all rows on the right table - the one typed on the right side of the OUTER JOIN command.
Use ON keyword to replace WHERE for the relationship 


Ex:
SELECT customers.name, items.name 
FROM customers 
LEFT OUTER JOIN items //<--forces all names in customers to be shown even if there is not an item name associated by seller_id//   
ON customers.id=seller_id //relationship of the tables//

-----------------------------------------------------

Tutorial 24: UNION

Run multiple queries and a single result set.

Note: columns being selected must be the same!

UNION also deletes duplicates gathered.  To prevent this from happening, use UNION ALL.

Ex:
for two separate queries with separate results:
SELECT name, cost, bids FROM items WHERE bids>190;
SELECT name, cost, bids FROM items WHERE cost>1000;

To get one result, type keyword UNION in between the queries.

Ex:
SELECT name, cost, bids FROM items WHERE bids>190;
UNION
SELECT name, cost, bids FROM items WHERE cost>1000;

This is useful for complex filtering situations using different tables etc.  Using UNION makes this easier and simpler to manage.

-------------------------------------------------

Tutorial 25: Full Text Searching

USE THIS OVER LIKE AND REGEXP ESP IN HUGE DATABASES!

Note: Large data sets without FULLTEXT index is much faster to load data into a table than to load data into a table which has an existing FULLTEXT index. Therefore create the index after loading data.

Useful for search engines, etc.

use ADD FULLTEXT() function.  Parameter takes column name with text/strings, do not choose a column with integers.

To initiate full text searching:
ALTER TABLE items ADD FULLTEXT(name)
//this enables search function on the column 

Match(what column to search in)
Against(what you want to search for)

Ex:
SELECT name, cost FROM items WHERE Match(name) Against('baby');
//this searches the name column for the text 'baby'

The reason to use this over LIKE is SQL ranks the results in Full Text Searching in a smart way based on a lot of criteria.  It is a lot faster and more effective than using REGEXP or LIKE. 

Customizing the search more:

IN BOOLEAN MODE Feature:
symbols you use with it:
+  this means make sure the proceeding word is included
-  this means make sure the proceeding word isn't included

Ex:
SELECT name, cost FROM items WHERE Match(name) Against('+baby -coat' IN BOOLEAN MODE);
//this returns search results including 'baby' but excluding results that contain 'coat'.

--------------------------------------------------

EDITING DATABASES ---


Tutorial 26: INSERT INTO

Used to create and insert a new row into a table.

Syntax:
INSERT INTO [name of table you want to insert the row into]
VALUES(the values for each row - number of values must match the number of columns and must be in the correct order)

Ex:
INSERT INTO items VALUES('101','bacon','9.95','1','0');


**Alot of times the table structure or details will change - use this alternative method for those situations (this enables the programmer to specify values out of order into specified columns):

*specify the column names in parens after the table name - they do not have to be in order:

INSERT INTO items(id,name,cost,seller_id,bids) VALUES('102','fish','7.99','1','0');

INSERT INTO items(id,cost,name) VALUES('103','7.77','beef');
//this inserts the values into the specified columns of the new row in the table.  any other columns unspecified that exist will be given a default value of 0.


--------------------------------------------------

Tutorial 27: INSERTING MULTIPLE ROWS

Note: you may have to insert values for tables that do not accept a default value of 'null'

create multiple parens for each row to insert the data into (good to put each on a new line in the code):

INSERT INTO items(id,name,cost,seller_id,bids) VALUES
('104','beef','7.99','1','0'), //<--comma after each row
('105','jelly','4.50','1','0'),
('106','ham','9.95','1','0');  //<---no comma after last row//


INSERT DATA FROM ANOTHER TABLE:

INSERT INTO items(id,name,cost,seller_id,bids) SELECT id,name,cost,seller_id,bids FROM othertable;

----------------------------------------------------

Tutorial 28: Update and Delete 

Edit items in the database:

use keywords UPDATE (edit), SET (change)

syntax: 
UPDATE [what row to update in] SET [column=value you want to change it to] WHERE [where to edit]
Ex:
UPDATE items SET name='puddinghammock' WHERE id=106;

To edit multiple columns, separate each with a comma:
Ex:
UPDATE items SET name='bacon', bids=66 WHERE id=106;
//changes the name and bids to values specified

----

To DELETE a row:

DELETE FROM [what table to delete row from] WHERE [what row]

DELETE FROM items WHERE id=106;
//this deletes the row in items table with an id of 106.

-----------------------------------------------------

Tutorial 29: CREATE TABLE

use CREATE TABLE keyword

CREATE TABLE [nameoftable] ([column name] [data type] (max storage size of data-optional), [additional rows], PRIMARY KEY(column name)
)

varchar data type = strings

Ex:

CREATE TABLE users(
id int,
username varchar(30), <--max size of 30 characters//
password varchar(20),
PRIMARY KEY(id)
)

----------------------------------------------------

Tutorial 30: NOT NULL and AUTOINCREMENT


Use NOT NULL keyword for values that cannot be left empty

Ex (username needs to have a value):
username varchar(30) NOT NULL;
//this will prevent the user from leaving the field blank and return an error

for the PRIMARY KEY column:
auto increment it by one each time a new row is created with key word 'AUTO_INCREMENT'

id int NOT NULL AUTO_INCREMENT 

To RESET AUTO INCREMENT 
-ALTER TABLE tablename AUTO_INCREMENT = 1

-----

Ex of Full Table creation:

CREATE TABLE bacon(
id int NOT NULL AUTO_INCREMENT,
username varchar(30) NOT NULL,
password varchar(20) NOT NULL,
PRIMARY KEY(id)
)

---------------------------------------------------

Tutorial 31: ALTER, DROP, RENAME Tables

To add a column to a table use ADD:

ALTER TABLE table ADD samplecolumn datatype(maxsize);
Ex:
ALTER TABLE bacon ADD name2 varchar(10);

To remove the column use DROP COLUMN:
Ex.
ALTER TABLE bacon DROP COLUMN name2

----

To add a column without changing the current table (to save as a backup), make a new table which copies the data and then add the column to that copy of the table.

to delete a table use DROP TABLE:

Ex.: DROP TABLE tablename;

-----

How to Rename a Table using RENAME TABLE and TO keywords:

Ex:
RENAME TABLE customers TO users;

Side Note: phpmyadmin can take some time to update the left sidebar information.

----------------------------------------------------

Tutorial 32: VIEWS

*Views do not store any or additional data;
Views are like temporary tables or snapshots of an existing table - they don't contain real data, they hold and present data from other existing tables for review.

To create a VIEW use the keywords CREATE VIEW [name the view] AS [data that you want to select to show in the VIEW].
Ex:
CREATE VIEW mostbids AS
SELECT id, name, bids FROM items ORDER BY bids DESC LIMIT 10;

**A view is a snapshot of the current corresponding table - so if the table it was created from changes, then the View will be automatically updated with the corresponding values. (could be useful for showing a top 10 list of data that you don't have to update all the time).

You can also combine columns in a table into a view (like for an address)

ex:
CREATE VIEW mailingaddress AS 
SELECT Concat(city,", ",state)AS address FROM customers;

Note: you can run queries on VIEWS just like on Tables.  

---------------------------------------------------

Tutorial 33: Final Tutorial

To Backup database

1) click on the database in PHPmyadmin
2) Click Export 
3) use quick export and select SQL Format
4) click go

To Restore, choose Import and import the sql file created.

Be sure to Create the table structure properly (number of columns/rows, data types with max size)

**Never retrieve more data than necessary in queries

common newbie mistake: using SELECT * and then picking out the desired information.  
*You do not want to use SELECT * unless absolutely necessary.

***FULLTEXT is better than LIKE

----------------------------------------------------
===================================================

DELIMITER:

Used to enable the passing of an entire procedure (begin...end) Block to the server.  the ; is interpreted automatically, but put after END, it's unclear whether END is a statement in the procedure, or the final part of the block.  

Redefine the delimiter with the keyword DELIMITER:

Ex: DELIMITER [define it]

DELIMITER $$ <--redefines the delimiter to '$$'

Place it after the END in a procedure:

BEGIN
   code
END 
$$

DELIMITER ; //<--sets default delimiter back to ;

Note: avoid assigning it to the backslash character ('\')





--------------------------------------

ADDITIONAL SUBJECTS TO RESEARCH:

TRIGGERS
CURSORS
STORED PROCEDURES

=========================================

On the Job scenario:

-work with several tables combining them using JOIN operators the most and they are huge. Syntax is rather simple but the logic is not.


==================================================

TO ACCESS THE DATABASE USING PHP:

Can put the php code in the body of the index.php

Assign a variable to connect to store the function of connecting to the database:

$conn = mysqli_connect(Servername,Username,Password,NameofDatabase);

Use mysqli_query($conn, 'query to execute');
//note: you can assign the second argument a variable
i.e. $query = "SELECT * FROM users;"









 






















 



 









 












 






















===================================================