Web Programming

INSERT INTO names (first, last)

VALUES ('Roger','Dupuis'),(Çhristine','Dupont');

INSEET INTO addresses (streeetID, street, city)

SELECT nameID, '43 Riverview', 'Calgary' FROM names WHERE first = 'Roger' and last ='Dupuis';

To match the name with the right address: the nameID from names table will correspond to the streetID field from the addresses table.

Mysql > SOURCE  filepath/scriptname.sql

This will re-create all the commands describe in the script.

• DROP DATABASE IF EXISTS  databasename;

• SELECT colonne1, colonne2
• WHERE colname LIKE 'A%'
• ORDER BY colonne ASC (which is by default or DESC)
• LIMIT 2, 10;
  • Will return all row of the table where the value in colname starts with the letter A; if we want ending by A then we would use %A instead of A%
  • Will order them by value of colonne
  • Will return 10 rows  starting at the third row of the result (first row is 0); the first argument (2) is optional. To get the last 10, then use DESC

UPDATE  table_name SET  col1='value1' , col2='value2' WHERE…  ;

Or

SELECT col1 , col1 * col1 as square from table_name;

Mathematical operations : + - * /

DIV is integer division

%  is modulus operation which give the remainder of integer division

DROP TABLE table_name;     will remove  the table_name

SELECT DISTINCT  lastName from names;

Will show only unique lastname

SELECT  COUNT  (*) from names;

Will show how many rows in the table names.

SELECT  COUNT (lastName)  FROM names GROUP BY lastNames;

Will give the count of each last name

SELECT  lastName COUNT (lastName)  FROM names GROUP BY lastNames;

Will show the last name with the count of them.

SELECT firstName, LastName, NameID  FROM namestable  JOIN  addresstable ON namestable.ID  =  addresstable.ID;

         leftTable JOIN rightTable  ON leftTable.key = rightTable.key

INNER  JOIN  (or just JOIN)  - returns only the rows from both tables where  there is a match

LEFT JOIN  - returns all rows in the leftTable , but only those in the rightTable where there is a match

RIGHT JOIN  - returns all rows in the rightTable , but only those in the leftTable where there is a match

FULL JOIN  - returns all rows in both table, matching every row in the leftTable with every rows in the rightTable (not very useful). It looks like the OUTER JOIN which is not supported in MySQL.

The full join table must have NOT NULL entry for both ID.

DELETE FROM table_name  WHERE col1 = value;   it delete a specific row in a table

           VIEW   it is a saved query

CREATE  VIEW  queryname  AS  SELECT name 

FROM table_name

LEFT JOIN  colors ON names.ID  = colors,nameID;

To see that view:  SHOW TABLES;    the view is saved as a table

To use this view:  SELECT  *  FROM queryname;

To remove that view :  DROP VIEW queryname;

COMMENT   in MySQL:

Multi line  start with  /*  and end with   */

Single line start with  --