MySQL Development Statements.

mysqldb.sql

SELECT * FROM city LIMIT 10;
SELECT 1;
SELECT 'Some Thing' as SexyThing;

SELECT 2+2 as adding;

SELECT 2+2, 'foo', 5*5, CONCAT('Mr ', 'Boombastic') as MrMan;

SELECT @a, @b, @c;

SELECT @var := 'foo';
SELECT @var;

SELECT @var := CONCAT('a','b','1');

SELECT @var;

SET @newvar := 20;
SELECT @newvar;

SET @firstname := 'David', @lastname := 'Johnson';

SELECT @firstname, @lastname;
SELECT CONCAT(@firstname, ' ', @lastname) as fullname;

SET @foo = 'bar';
SELECT @foo;

SELECT @@autocommit, @@basedir;

SELECT @@query_cache_size;

SET GLOBAL query_cache_size = 64 * 1024 * 1024;

SELECT 5 + 12 * (2 + 2);

SELECT 10 DIV 3 as result, 10 % 3 as modulus;

SET @test = 'some new value';

SELECT @test LIKE '%new%';

SELECT @test LIKE '_ome%';

SELECT 10 BETWEEN 5 AND 9;

SELECT NAME, Population FROM Country WHERE Population 
and Population < 1000;

SELECT PI() + 0.00000000000 AS PI;

SELECT POW(5, 10);

SELECT CONCAT_WS(' ', 'B', 3, CURDATE());

SELECT MID('MySQL', 2);

SELECT SUBSTRING_INDEX('[email protected]', '@', -1);

SELECT '1946-05-12' + INTERVAL 5 DAY;

SELECT '1946-02-12' + INTERVAL '5-3' YEAR_MONTH;

SELECT DATE_FORMAT('1946-02-12', '%W, %M %D, %Y') as '1946 Formatted';

SELECT DAYNAME('2020-12-25');

SELECT EXTRACT(MONTH FROM '2020-12-15');

# Converts Date from one format to another.
SELECT STR_TO_DATE('DECEMBER 25th, 2020', '%M %D, %Y');

SELECT DATEDIFF('2020-12-25', '2020-12-20');

SELECT DAY(LAST_DAY('2020-12-25'));

#Control Flow Functions

SELECT IF (1, 'Yay for truth', 'boo for false');

SELECT IF (0, 'Yay for truth', 'boo for false');

SELECT IF (15 > 12, 'Yay for truth', 'boo for false') as 'Greater than';

SELECT ELT(2, 25, 13*847, '2020-12-20' + INTERVAL 5 DAY);

SELECT ELT(3, 25, 13*847, '2020-12-20' + INTERVAL 5 DAY);

SELECT CASE 'B' WHEN 'A' THEN 'First' When 'B' THEN 'Second' 
ELSE 'nothing' END;

SELECT CASE WHEN 'a' THEN 'String' WHEN 5 
	THEN 'Number' ELSE 'Nothing' END;
    
# Null Functions

SELECT ISNULL(NULL);

SELECT ISNULL(1);

SELECT NULL IS NULL;

SELECT CASE WHEN NULL IS NULL THEN 'Null has crept in' END;

#Unknown was a more primitive data-type than NULL
#It is completely undefined
SELECT NULL IS UNKNOWN;

SELECT IFNULL(5, 'Alternate value');

SELECT IFNULL(NULL, 'Alternate value');

#SYSTEM FUNCTIONS

SELECT VERSION();

SELECT DATABASE();

SELECT CURRENT_USER();

# Database Query 

SELECT * FROM world.City LIMIT 5;

show tables;


SELECT Name, Population, CountryCode FROM City LIMIT 5;

SELECT Name, ROUND(GNP/Population*1000000, 2) AS 
'GNP per person (dollars)', 25 FROM Country LIMIT 5;

# This gives a way of finding out the total number of rows returned.
SELECT SQL_CALC_FOUND_ROWS * FROM City LIMIT 5;
SELECT FOUND_ROWS();

# Describe the City
DESC City;
DESCRIBE City;

SELECT * FROM City WHERE Name = 'san jose';

SELECT * FROM City WHERE CountryCode = 'che';

SELECT Name, CountryCode, Population FROM City WHERE 
Population > 800000;

#Complex WHERE Clauses.

SELECT Name, Population, LifeExpectancy FROM Country WHERE 
Population <= 50000 AND LifeExpectancy >= 75;

#Painful way
SELECT Name, CountryCode, Population FROM City WHERE CountryCode = 'LIE' 
OR CountryCode = 'MCO' OR CountryCode = 'SHN' OR CountryCode = 'SMR';

#Easy way, IN is an implicit OR comparison

SELECT Name, CountryCode, Population FROM City WHERE CountryCode IN('LIE',
'MCO','SHN','SMR');

#Tuple, Row, Vector, Set.  The Tuple method is an implicit AND.

SET @first := 5, @second := 10;

SELECT @first, @second;

SELECT (@first, @second) = (5,10);

SELECT * FROM CountryLanguage WHERE CountryCode = 'USA' AND Language = 'Spanish';

SELECT * FROM CountryLanguage WHERE (CountryCode, Language) = ('USA', 'Spanish');

#Both Tuples and IN

SELECT * FROM CountryLanguage WHERE (CountryCode = 'USA' AND Language = 'Spanish') 
OR (CountryCode = 'CAN' AND Language = 'French');

SELECT * FROM CountryLanguage WHERE (CountryCode, Language) IN 
(('USA', 'Spanish'), ('CAN', 'French'));

#Sorting Results ORDER BY

SELECT * FROM City WHERE CountryCode = 'prt' ORDER BY Name;

SELECT * FROM City WHERE CountryCode = 'prt' ORDER BY District;

SELECT * FROM City WHERE CountryCode = 'prt' ORDER BY District, Name;

#Note:  MySQL Does not require that a Ordered statement be in the SELECT statement.
# Some RDBMS do require this.

SELECT Name FROM City WHERE CountryCode = 'prt' ORDER BY District;

SELECT Name FROM City WHERE CountryCode = 'prt' ORDER BY District DESC;

SELECT Name FROM Country ORDER BY IF(Code = 'USA', 1, 2), NAME LIMIT 10;

# When USA is encountered, it is sorted as a 1, top of the list.
# When other columsn are encountered, they are given a 2.  They are then sorted by their name.

SELECT Name FROM Country ORDER BY 
	CASE Code
		WHEN 'USA' THEN 1
        WHEN 'CAN' THEN 2
        WHEN 'MEX' THEN 3
        ELSE 4 
	END,
Name LIMIT 10; 

#Using LIMIT

SELECT * FROM City ORDER BY Population DESC LIMIT 3;

#SKIP first 20

SELECT * FROM City ORDER BY Population DESC LIMIT 20, 5;

#DISTINCT

SELECT DISTINCT Continent FROM Country;

SELECT Name, CountryCode FROM City WHERE Name = 'san jose';

SELECT DISTINCT Name, CountryCode FROM CITY WHERE Name = 'san jose'; 

SELECT COUNT(Continent) FROM Country;

SELECT COUNT(DISTINCT Continent) FROM Country;

#Aggregation Queries

SELECT COUNT(*) FROM Country;

SELECT COUNT(Capital) FROM Country;

# Does not include null values.

SELECT COUNT(*) FROM Country WHERE Continent = 'Asia';

SELECT SUM(Population) FROM Country WHERE Continent = 'Asia';

SELECT COUNT(*), SUM(Population), AVG(Population) FROM Country 
	WHERE Continent = 'Asia';

# Non-aggregated values do not work.

SELECT Name, MAX(Population) from Country;

#Sub-aggregation query:  GROUP BY

SELECT Continent, SUM(Population) AS TotalPop FROM Country GROUP BY Continent;

SELECT Continent, Region, SUM(Population) AS TotalPop FROM Country GROUP BY Continent, Region;

#The sorted Data type is an ENUM with ASIA as a 1, Europe as a 2, and so forth.

SELECT Continent, Region, SUM(Population) AS TotalPop 
	FROM Country GROUP BY CAST(Continent AS CHAR), Region;
    
SELECT Continent, Region, SUM(Population) AS TotalPop 
	FROM Country GROUP BY CAST(Continent AS CHAR), Region ORDER BY TotalPop DESC;
    
SELECT Continent, SUM(Population) AS TotalPop FROM Country GROUP BY Continent ORDER BY TotalPop DESC;

#This is a logical error.
SELECT Continent, Name, SUM(Population) AS TotalPop FROM Country GROUP BY Continent;

#Filter Derived Columns with HAVING

SELECT COUNT(DISTINCT District) FROM City;

SELECT District, COUNT(*) AS CityCount FROM City 
	GROUP BY District HAVING CityCount >= 30;

SELECT District, COUNT(*) AS CityCount FROM City 
	GROUP BY District HAVING CityCount >= 30 
    ORDER BY CityCount DESC;

#MultiLevel Sums using WITH ROLLUP

SELECT Continent, SUM(Population) AS TotalPop FROM Country 
	GROUP BY Continent WITH ROLLUP;

#Generates messy null values
SELECT Continent, Region, SUM(Population) AS TotalPop FROM Country 
	GROUP BY Continent, Region WITH ROLLUP;
   
#This uses IFNULL to generate Good data.
SELECT IFNULL(Continent, 'All Continent Population') as ContinentName, 
	IFNULL(Region, REPEAT('-', 25)) as RegionName, 
    SUM(Population) AS TotalPop 
    FROM Country 
	GROUP BY Continent, Region WITH ROLLUP;
    
# SELECT <column list>
# FROM <table specification>
# WHERE <selection criteria>
# GROUP BY <grouping criteria>
# HAVING <selection criteria using derived data>
# ORDER BY <sorting criteria>
# LIMIT [# skipped rows,] #rows to receieve]

CREATE DATABASE demo1;

SHOW CREATE DATABASE demo1;

CREATE DATABASE demo2 CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

SHOW CREATE DATABASE demo2;

SHOW CHARACTER SET;
SHOW COLLATION;

#CREATING A TABLE

-- CREATE TABLE <tablename> (
-- 	<columnname><datatype>[<column options>]
--     [,<columnname><datatype>[<column options>]
-- )

USE demo2;

CREATE TABLE people1 (
	ID TINYINT,
    NAME CHAR(10));	
    
SHOW CREATE TABLE people1;

CREATE TABLE people2 (
	ID TINYINT,
    NAME CHAR(10))
ENGINE MYISAM CHARACTER SET = UTF8;

CREATE TABLE people3 (
	ID TINYINT,
    NAME CHAR(10))
    COMMENT = 'This Comment is gold';	
    
CREATE TABLE people4 (
	ID TINYINT NOT NULL DEFAULT 0 COMMENT 'unique id',
    NAME CHAR(10) NOT NULL DEFAULT '' COMMENT 'DESCRIPTION')
    COMMENT = 'Default sets of characters';	
    
CREATE TABLE people5 (
	ID TINYINT UNSIGNED NOT NULL,
    NAME CHAR(10));
    
#Autoincrementation:  Only primary keys can have auto-increment.

CREATE TABLE people6 (
	ID TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    NAME CHAR(10));
    
INSERT INTO people6 (Name) 
	VALUES ('Ann'), ('Andy'), ('Betty'), ('Bob');
    
SELECT * FROM people6;

# ALTER TABLE 
-- Altering a table involves copying the original table, creating a new one,
-- Copying the contents over, and then destroying the original.  
-- This can take a while on large table structures, and it must have 
-- At least enough disk space to hold both tables, the original and the duplicate.

-- ALTER TABLE <tablename> <alter specification [,<alter specification>]

SHOW CREATE TABLE people6;

ALTER TABLE people6 AUTO_INCREMENT 10 ENGINE = MYISAM;

SHOW CREATE TABLE people6;

ALTER TABLE people6 AUTO_INCREMENT 5 ENGINE = INNODB;

#Alter table by adding or removing columns.

-- ADD [COLUMN] <columnname> <data type> [<column options>]
-- [FIRST | AFTER <colname>]

-- DROP [COLUMN] <columnname>

ALTER TABLE people6 ADD birthdate DATE, ADD badcol char (5) AFTER ID;

SHOW CREATE TABLE people6;

-- MODIFY [COLUMN] <columnname> <complete new column definition> 
-- [FIRST | AFTER <colname>]

-- Change [COLUMN] <oldcolumnname> <newcolumnname> 
-- <complete new column definition> [FIRST | AFTER <colname>]
-- 

ALTER TABLE people6 ADD intcol INT, ADD charcol CHAR(10), 
	ADD datecol DATE;
    
ALTER TABLE people6 ALTER intcol SET DEFAULT 10,
MODIFY charcol CHAR(20) DEFAULT 'unknown',
CHANGE datecol deathdate DATE DEFAULT NULL AFTER birthdate;

SHOW CREATE TABLE people6;

# Drop TABLE
# DROP TABLE [IF EXISTS] <tablename [, <tablename>]

# ALl dropped tables must be named specifically

SHOW TABLES;

DROP TABLE people1, people2, people3, people4;

DROP TABLE IF EXISTS people2;

SHOW WARNINGS;

# DROP DATABASE
# DROP DATABASE [IF EXISTS] <databasename>

DROP DATABASE IF EXISTS demo2;

#Datatypes INT
# TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
# BIGINT is used internally for arithmatic operations.
# SIGNED allows negatives, but halves the maximum.
# ZEROFILL puts 0s in places where it is less than the minimal display

#DECIMAL is for precise mathematics.

#ENUM Stores a string as an integer.  Can only select one.

-- Continent ENUM(
-- 	'Asia','Europe','North America',
--     'Africa','Oceania','Antartica',
--     'South America'
-- );
-- 
-- Pref ENUM('Great!', 'No way.', 'Don\'t care');

# SET stores the string as a bit.  Like a checkbox, can select multiple.

-- Favs SET('Chocolate','Vanilla','Mint');
-- 
-- INSERT table (Favs) VALUES ('Mint,Vanilla');
-- 
-- SELECT WHERE FIND_IN_SET('Mint', Favs);
-- 
-- SELECT WHERE FAVS & 4;

#Binary String.  Sequence of bytes.  Photos, data, music, movies, programs.

#Temporal Date Types

SHOW ENGINES;

SELECT DATABASE();

CREATE DATABASE foo;
DROP DATABASE IF EXISTS demo1;
USE foo;

CREATE TABLE people2 (
	ID TINYINT,
    NAME CHAR(10))
ENGINE BLACKHOLE CHARACTER SET = UTF8;

INSERT INTO people2 VALUES(1,'foo'), (2,'boo'), (3, 'tree'), (4, 'hour');

SHOW CREATE TABLE people2;

SELECT * FROM people2;
DROP TABLE people2;

DROP DATABASE foo;

USE world;

#Exporting data.

SELECT * INTO OUTFILE 'countryexport.txt' FROM COUNTRY;
SELECT * FROM Country INTO OUTFILE 'countryexport.txt';
#CSV

#Change this for non-windows machines
SELECT * FROM Country INTO OUTFILE 'C:\\tmp\\countryexportcsv.txt' 
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';
    
SELECT * FROM Country INTO OUTFILE '/home/ubuntu/workspace/countryexportcsv.txt' 
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';    
    
#Command line:
# mysql -u root -p -BNe "SELECT * FROM City WHERE CountryCode = 'che'" 
# world > swisscities.txt

 #mysqldump --help > mysqlhelp.txt
 # myqsldump [OPTIONS] database [tables]
 # mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3]
 # mysqldump [OPTIONS] --all-databases
 
 # mysqldump -u root -p --tab=/home/ubuntu/workspace world city country
 
 #PREPARED STATEMENTS
 
 #PREPARE <name> FROM "<text of statement>"
 #PREPARE <name> FROM @varContainingStatement
 
 #PREPARE CountryCities FROM "SELECT * FROM City WHERE CountryCode = ?"
 
 #SET @stmt := "SELECT * FROM City WHERE CountryCode = ?"
 #PREPARE CountryCities FROM @stmt
 
 #EXECUTE <name> [USING <@varlist>]
 #DROP PREPARE <name>
 #DEALLOCATE PREPARE <name>
 
PREPARE CountryCities FROM "SELECT * FROM City WHERE CountryCode = ?";
SET @ccode := 'che';
EXECUTE CountryCities USING @ccode;
SET @ccode := 'nor';
EXECUTE CountryCities USING @ccode;
#Notice, the change in the response time, the second command is much faster. 
 
SET @stmt := "SELECT * FROM City WHERE CountryCode = ?";
PREPARE CountryCities2 FROM @stmt;
SET @ccode := 'che';
EXECUTE CountryCities2 USING @ccode;
SET @ccode := 'chn';

 SET @stmt := "SELECT Name, Population, District FROM City WHERE 
 CountryCode = ? LIMIT ?, ?";
 
 PREPARE FlexCity FROM @stmt;
 
 SET @ccode := 'usa', @skipcols := 0, @getcols := 5;
 
 EXECUTE FlexCity USING @ccode, @skipcols, @getcols;

#Admin style commands
 
SHOW COLUMNS FROM City like '%i%';
 
SHOW COLUMNS FROM City WHERE Type like '%i%';
 
 #When to use special quotes.
 # Default is a reserved word, so attempting to use it requires 
 # back-ticking it to escape it.
 
SHOW COLUMNS FROM City WHERE `Default` is null; 

DESC City;

# Information Scheme

USE information_schema;

SHOW TABLES;

DESC Tables;

SELECT Table_name FROM Tables WHERE Table_schema = 'world';

SELECT Table_name, Engine FROM Tables WHERE Table_schema = 'world';

SELECT Table_name, Engine, COUNT(*) AS TableCount 
FROM Tables GROUP BY Table_schema, Engine;

#DML SELECT, INSERT, UPDATE, DELETE
#DDL CREATE DATABASE, ENGINES, ETC.
#DSL USER ACOUNTS, PRIVILEGES, ETC.

# Warning vs. Error

# Warning tells us something bad happened, but the system allowed it.
# Error tells us something bad would have happened, but the system prevented it.

#sql_mode variable lets us override the default.

# Multiple warnings can be generated by a single command.

# Execution of new command clears previous warning messages.

#SQL Mode:  One kind changes syntax, this is a bad idea.
# The other kind will change warnings to error.

SET sql_mode := '';

SELECT 4 || 5;

SET sql_mode := PIPES_AS_CONCAT;

SELECT 4 || 5;

SELECT "Hello, World!";

SET sql_mode := 'ANSI';

#VERY bad idea.

SELECT "Hello, World!";

SET sql_mode := @@global.sql_mode;

SET sql_mode := '';

USE World;

CREATE TABLE ints (i tinyint(4));

#Generate a warning.

INSERT INTO ints VALUES (23), (43), (221), (97);

SELECT * FROM ints;

SET sql_mode = 'TRADITIONAL';

INSERT INTO ints VALUES (23), (43), (221), (97);

ALTER TABLE ints MODIFY COLUMN i INT(4);

DESC ints;

INSERT INTO ints VALUES (23), (43), (221), (97);

SELECT * FROM ints;

#Error Codes:  < 2000 Server errors
# Client >= 2000 - 2999 Client Error
# SQL State Code (String - general, but ANSI)
# Error message text.  

# NOTE Warning

# DROP TABLE IF EXISTS badtablename;

# Tells you the reason why no action taken.  (false alarm).

SET @@sql_notes = 0;

#System error message.

# SELECT * from City into outfile '/foo/city.txt';
# ERROR 1 (HY000) Can't create/write file to file '/foo/city.txt' (Errcode: 2)

# HY0000 is the catch-all error code.
# the errcode 2 is a system level error, not generated by MySQL but by the system itself.
# Code value will vary among systems
# perror 2
# it is a shell command

#\! perror 2;
# Linux only

#Original insert syntax

#INSERT [INTO] <tablename> SET <column>=<value>[, <column>=<value>]

CREATE TABLE people (
	id INT UNSIGNED NOT NULL,
    name CHAR (10) NOT NULL,
    city CHAR (15) NOT NULL,
    bdate DATE NOT NULL);
    
INSERT INTO people VALUES (1, 'Al', 'New York', '1982-10-08');
INSERT INTO people VALUES (2, 'Alice', 'Los Angeles', '1983-08-10');

INSERT INTO people VALUES (3, 'Beth', 'Chicago', '1983-08-10'),
	(4, 'Bill', 'Dallas', '1986-12-14'),
    (5, 'Cathy', 'Seattle', '1990-02-14');

INSERT INTO people VALUES (3+3, CONCAT('B','y','ron'), CONCAT('San',
IF(MOD(DAY(CURDATE()),2),'Jose','Francisco')), CURDATE() - INTERVAL 25 YEAR);

SELECT * FROM people;

#Error
INSERT INTO people (id, name) VALUES (8, 'Dana');

CREATE TABLE people3 (
	id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name CHAR(10) NOT NULL,
    city CHAR (15) DEFAULT 'Bedford',
    bdate DATE DEFAULT '1980-01-01');

DESC people3;
    
INSERT INTO people3 (id, name) VALUES (1, 'Andy');     

INSERT INTO people3 VALUES (NULL, 'Andie', NULL, NULL);

SELECT * FROM people3;

INSERT INTO people3 VALUES (NULL, 'Sandie', DEFAULT, DEFAULT);

SELECT * FROM people3;

INSERT INTO people3 (name) VALUES ('Jim'); 

INSERT INTO people3 (id, name) VALUES (DEFAULT, 'Mr. Foo');

SELECT * FROM people3;

SHOW CREATE TABLE people3;

# ALTER TABLE people3 AUTO_INCREMENT = 100;

# LAST_INSERT_ID Function

INSERT INTO people3 (name) VALUES ('Sally');

SELECT * FROM people3;

SELECT LAST_INSERT_ID();

SET @lastID := LAST_INSERT_ID();

SELECT @lastID;

INSERT INTO people3 (name) VALUES ('Sam'), ('Dean'), ('Joan'), ('Judy');

SELECT LAST_INSERT_ID();

#DELETE FROM <tablename> [WHERE <condition>] 
#[ORDER BY <criteria>] [LIMIT <value>]

CREATE TABLE city2 LIKE city; 
INSERT city2 SELECT * FROM city;

CREATE TABLE city3 LIKE city; 
INSERT city3 SELECT * FROM city;

SELECT COUNT(*) FROM city2;

#This command deletes everything.
# In safe mode, this will not work as it requires a WHERE clause to work.
DELETE FROM city2;

SELECT Name, District FROM city3 WHERE id = 4011;
DELETE FROM city3 WHERE id = 4011;

SELECT * FROM city3 WHERE countrycode = 'CHE';
DELETE FROM city3 WHERE countrycode = 'CHE';

# For performance reasons, rows should be targetted with a WHERE 
# Clause on the primary key, this makes look-ups much faster.

SELECT * FROM city3 WHERE countrycode = 'CHE';

SELECT COUNT(*) FROM city2;
DELETE FROM city2 WHERE id <= 1000 LIMIT 100;
DELETE FROM city2 WHERE id <= 1000 LIMIT 100;
DELETE FROM city2 WHERE id <= 1000 LIMIT 100;
DELETE FROM city2 WHERE id <= 1000 LIMIT 100;
DELETE FROM city2 WHERE id <= 1000 LIMIT 100;
DELETE FROM city2 WHERE id <= 1000 LIMIT 100;
DELETE FROM city2 WHERE id <= 1000 LIMIT 100;
DELETE FROM city2 WHERE id <= 1000 LIMIT 100;
DELETE FROM city2 WHERE id <= 1000 LIMIT 100;
DELETE FROM city2 WHERE id <= 1000 LIMIT 100;
SELECT COUNT(*) FROM city2;

DROP table city2;

CREATE TABLE city2 LIKE city; 
INSERT city2 SELECT * FROM city;

SELECT * FROM city2 WHERE countrycode = 'NLD' ORDER BY population;

DELETE FROM city2 WHERE countrycode = 'NLD' ORDER BY population LIMIT 10;

# DELETE Result

DELETE FROM city2 WHERE countrycode = 'GBR';

SELECT ROW_COUNT();

# Updates

# UPDATE <tablename> SET <column> = <value> [, <column> = <value>...]
# [WHERE <condition>]
# [ORDER BY <criteria>] [LIMIT <value>]

SELECT * FROM people3;

UPDATE people3 SET bdate = bdate + INTERVAL 10 DAY WHERE id >= 1;

SELECT * FROM people3;

UPDATE people3 SET bdate = bdate + INTERVAL 1 YEAR 
	WHERE name LIKE '%s%' AND id >= 1;
    
 SELECT * FROM city2 LIMIT 10;   
 
 UPDATE city2 SET population = population + 10000 LIMIT 5;
 
 UPDATE city2 SET population = IF(population > 100000, population - 1000, population) 
	LIMIT 5;

UPDATE people3 SET id = id + 1;

UPDATE people3 SET id = id + 1 ORDER BY id desc;

SELECT ROW_COUNT();

# NOT OFTEN USED COMMANDS
# REPLACE IS an alternate for INSERT with one difference
# when an error occurs, particularly a "Duplicate Key" error
# only new data will be inserted for new row, if none is specified, the default or null value will be used.
# so some existing data may be lost.

# REPLACE [INTO] <tablename> [(columnlist)] 
	# VALUE[S] (<valuelist>) [, (<valuelist>)...]
    
#Bettter option INSERT ON DUPLICATE KEY UPDATE

# INSERT [INTO] <tablename> [(<columnlist>)]
#	VALUE[S] (<valuelist>) [, (<valuelist>) ...]
#   [ON DUPLICATE KEY UPDATE <assignment>
#	[, <assignment>...]]

# ON DUPLICATE KEY UPDATE <col> = VALUES(<col>)
USE world;
SHOW TABLES;

CREATE TABLE country2 LIKE country; 
INSERT country2 SELECT * FROM country;


SELECT * FROM country2 WHERE code = 'NZL';

INSERT INTO country2 (code, name, population)
	VALUES ('NZL', 'Aotearoa', 4000000)
    ON DUPLICATE KEY UPDATE
    code = VALUES(code), 
    name = VALUES(name), 
    population = VALUES(population);
    
SELECT * FROM country2 WHERE code = 'NZL';

# TRUNCATE TABLE
# Removes all rows, but leaves the table structure intact.
# File system action, not a database action.  It cannot be rolled back.

# TRUNCATE [TABLE] <tablename>

# SAFE UPDATES MODE

SELECT @@sql_safe_updates;

SET sql_safe_updates := 1;

SELECT @@sql_safe_updates;

# LOAD DATA

LOAD DATA INFILE '/home/ubuntu/workspace/demo.txt' INTO TABLE foo;
# LINUX TAB DELIMINATED FILE.
# Nothing in file but data file, no header data.

CREATE TABLE Capitals
	SELECT id, ci.name AS Citname, co.name AS CountryName, 
    ROUND(ci.population/co.population*100, 2) AS PercentPopulation
	FROM city ci JOIN country co ON ci.id = co.capital;

SELECT * FROM Capitals;

CREATE TABLE BalticCountries (PRIMARY KEY (code)) ENGINE = InnoDB
	SELECT code, name, population, surfacearea, gnp FROM country
    WHERE region = 'Baltic Countries';
    
SELECT * FROM BalticCountries;

DESC BalticCountries;

CREATE TABLE Continents (id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT)
	SELECT CAST(Continent AS CHAR) AS Name,
    SUM(Population) AS Population,
    SUM(SurfaceArea) AS SurfaceArea,
    SUM(GNP) AS GNP
    FROM Country GROUP BY Continent;
    
SELECT * FROM Continents;

CREATE TABLE citycopy2 LIKE city;

DESC citycopy2;
SHOW CREATE TABLE citycopy2;

-- Table is unpopulated
INSERT INTO citycopy2 SELECT * FROM city;
-- Table is now populated

# Temporary Table
# Table can only be seen by current connection.
# Automatically dropped when logged off.
# Can use any storage engine.
# Can cause problems when used with the Memory engine, RAM 
# may run out if too many temporary tables are created.
# DROP TEMPORARY TABLE 

CREATE TABLE testcountries (primary key (code)) engine INNODB
	SELECT code, name, population * 1.1 FROM country
	WHERE region = 'nordic countries';
    
SELECT * FROM testcountries;

ALTER TABLE testcountries
	CHANGE `population * 1.1` population INT UNSIGNED NOT NULL,
    ADD INDEX (population);
    
SELECT * FROM testcountries;

DESC testcountries;

#subqueries are inefficient
#not all joins make sense
# Sometimes using multiple steps to create a temporary table and then query it.
# Table structures should be designed for anticipated queries.

SELECT id, City.Name FROM City JOIN Country on id = Capital LIMIT 10;

CREATE TABLE capitals SELECT ci.name, co.name
	FROM city ci JOIN country co ON id = capital;

CREATE TABLE capitals SELECT ci.name, co.name AS Country
	FROM city AS ci JOIN country AS co ON id = capital;
    
DROP TABLE capitals;

SELECT * FROM capitals;

SELECT c1.name, c2.name
	FROM country AS c1 JOIN test.country AS c2
    ON c1.code = c2.code
    WHERE c1.name <> c2.name;
    
SELECT c1.IndepYear, c2.Name
	FROM Country AS c1 JOIN Country AS c2
    ON c2.IndepYear = c1.IndepYear
	WHERE c1.Name = 'Qatar';
    
SELECT c1.continent, c1.name, c1.population
	FROM Country c1 WHERE c1.population = (
    SELECT MAX(c2.population) FROM Country c2
    WHERE c2.continent = c1.continent)
AND c1.population > 0;

# Subqueries, Select statements nested inside a statement
# Outer statement is not limited to SELECT
# Shape of the subquery result limits where it can used.
# subquery may be correlated with outer query.
# subqueries may be less efficient than Joins.

# Subqueries can be used anywhere except in a limit.  (Limits cannot use
# Expressions, and a subquery is an expression.)
# Inner query goes first, then used as input to outer query
# unless the subquery is correlated

# SELECT <sql> FROM <tbl> WHERE
# SELECT <col> FROM <sql> WHERE
# SELECT <col> FROM <tbl> WHERE <sql>
# SELECT <col FROM <tbl> ORDER BY <sql opn>

-- Result Set Shapes

-- Table - Multiple columns, multiple rows
-- Row -- Multiple columns, at most one row
-- Column -- one column, potentially many rows.
-- Scalar -- one column, at most one row

CREATE TEMPORARY TABLE clauses (
	`Position` varchar(100), 
    `Scalar` tinyint(1),
    `Row` tinyint(1),
    `Column` tinyint(1),
    `Table` tinyint(1)
);

ALTER TABLE clauses ADD COLUMN `Row` tinyint(1);

INSERT INTO clauses (`Position`, `Scalar`, `Row`, `Column`, `Table`) 
	VALUES ('Select', 1, 0, 0, 0), 
    ('From', 1, 1, 1, 1),
    ('=, >, <...', 1, 1, 0, 0),
    ('In, Any', 0, 0, 1, 1),
    ('Exists', 1, 1, 1, 1);

SELECT * FROM clauses;

# How can we guarantee at most one row?
# Useful for forcing scalar or row result shape

# Use a select without a FROM (trivial) e.g. SELECT 1;
# USE LIMIT 1 (trivial), but will be ambiguous in multiple row;
# SELECT FROM a single row table (trivial)
# USE = on Primary or Unique Key in WHERE (only one row or zero)
# Use aggregation with no GROUP BY (shows the sum of function)

SELECT * FROM City WHERE CountryCode = (
	SELECT Code FROM Country WHERE Name = 'Switzerland');
    
SELECT Name, (
	SELECT COUNT(*) 
    FROM City 
    WHERE CountryCode = Code) AS CityCount
FROM Country WHERE Continent = 'South America'
ORDER BY CityCount DESC;

# Table SubQueries

SELECT Continent, SUM(Population) AS ContPop 
	FROM Country GROUP BY Continent;
    
SELECT AVG(ContPop) FROM (
	SELECT Continent, SUM(Population) AS ContPop 
    FROM Country GROUP BY Continent) AS derived;

#Columnar Subqueries

SELECT * FROM City WHERE Population > 3000000 AND CountryCode IN (
	SELECT Code FROM Country WHERE Continent = 'South America')
    ORDER BY Population DESC;
    
SELECT Continent FROM Country WHERE Code IN (
	SELECT CountryCode FROM CountryLanguage
    WHERE (Language, IsOfficial) = ('Spanish', 'T'))
    GROUP BY Continent;
    
# Row SubQueries (Pretty rare)

SELECT Name, Continent, IndepYear FROM Country
	WHERE (Continent, IndepYear) = (
    SELECT Continent, IndepYear FROM Country WHERE Name = 'Burundi');

# Exists can only accept a subquery, only tests for a non-empty result set.
# Does not return anything.
# Correlated -> any related rows in the inner query to the outer query.
# NOT EXISTS used to find exceptions.

# Find cities that are named after their country.
SELECT Name FROM Country WHERE EXISTS (
	SELECT * FROM City WHERE City.Name = Country.Name AND CountryCode = Code);

#List codes, names, and population where city doesn't have a code
 SELECT Code, Name, Population FROM Country 
	WHERE NOT EXISTS (
		SELECT * FROM City WHERE CountryCode = Code);
 
 #Correlated vs. non-correlated subquery.
 # A non-correlated subquery requires no additional input
 # From outside. 
 
 #Non-correlated
 SELECT * FROM City WHERE CountryCode = (
	SELECT Code FROM Country WHERE Name = 'Switzerland');
   
SELECT Code FROM Country WHERE Name = 'Switzerland';
   
#Correlated

SELECT Name, (
	SELECT COUNT(*) FROM City WHERE CountryCode = Code) AS CityCount
    FROM Country WHERE Continent = 'South America';

#Error    
SELECT COUNT(*) FROM City WHERE CountryCode = Code;
#test query
SELECT COUNT(*) FROM City WHERE CountryCode = 'ECU';

# Nested Subqueries 
# Break down query into small steps
# Building block approach, build query from innermose subquery
# Simple way to test component queries -- keep in mind which correlated queries are needed.

# Building Queries in Layers

# What is the largest population from a city?

SELECT MAX(Population) FROM City;

# Get cityCode (Scalar) from City with max population

SELECT CountryCode FROM City WHERE Population =
	(SELECT MAX(Population) FROM City);
 
# Get Country with the largest population in a city.

SELECT Name FROM Country WHERE Code = (
	SELECT CountryCode FROM City WHERE Population = (
		SELECT MAX(Population) FROM City));

SELECT Name FROM Country WHERE Code = (
		SELECT CountryCode FROM City ORDER BY Population DESC LIMIT 1);

# Prototyping a new complex query
# Alternative - use a join whenever possible.

# Queries requiring derived tables
# -- Alternative - temporary or summary tables

# Queries that require a base table using derived aggregation
# -- Alternative - temporary or summary table

# The FROM clause determines teh table we select rows in a query
# Can create a virtual table by combining rows from existing tables stored in the database
# This combined table is called a join.
# JOIN is both the derived table and the operation that creates it.

CREATE TEMPORARY TABLE m (Name CHAR(10), HHid TINYINT(4));
CREATE TEMPORARY TABLE w (Name CHAR(10), HHid TINYINT(4));

INSERT INTO m (Name, HHid) 
	VALUES ('Ed', 6), 
	('Ted', 2),
    ('Al', 3),
    ('Bob', 1);
    
INSERT INTO w (Name, HHid) 
	VALUES ('Sally', 4),
    ('Carol', 1),
    ('Alice', 2),
    ('Ann', 7),
    ('Linda', 5);
    
# Bad query
# Generates one table from the rows.
# Generates a CROSS JOIN.

SELECT * FROM m,w;

# Generates an INNER JOIN

SELECT * FROM m, w WHERE m.HHID = w.HHid;

# Same thing but with explicit joins.

SELECT * FROM m JOIN w ON m.HHid = w.HHid;

SELECT * FROM m JOIN w ON m.HHid = w.HHid WHERE m.name = 'Bob';

SELECT * FROM m JOIN w ON m.HHid = w.HHid AND m.name = 'Bob';

# OUTER JOIN, 

SELECT * FROM m LEFT JOIN w ON m.HHid = w.HHid;

SELECT * FROM m RIGHT JOIN w ON m.HHid = w.HHid;

SELECT * FROM m RIGHT JOIN w ON m.HHid = w.HHid WHERE m.HHid IS NULL;

#LIMITATION OF A TEMPORARY TABLE, this statement will not work.
SELECT *, (
	SELECT * FROM m AS temp1, w AS 
    temp2 WHERE temp1.HHid = temp2.HHid AND temp2.HHid IS NULL) AS singleLadies 
FROM m, w WHERE m.HHid = w.HHid AND m.HHid IS NULL;

# Possible condition to test.
# This first matches household ids and then filters the null.

SELECT * FROM m LEFT JOIN w ON m.HHid = w.HHid WHERE w.HHid IS NULL;

# IMPOSSIBLE CONDITION TO TEST.
# This tries to both find where a household id matches and is null, which is an impossible condition.

SELECT * FROM m LEFT JOIN w ON m.HHid = w.HHid AND w.HHid IS NULL;

# INNER JOIN, (comma join)
# FROM <tbl1>, <tbl2> WHERE <join criteria>

#Formal case FROM <tbl1> JOIN <tbl2> ON <criteria>

# SPECIAL:  FROM <tbl1> JOIN <tbl2> USING (<col(s)>)
# Assumes an equals comparison
# Consolidates the set.

SELECT co.Name, ci.Name, 100 * ci.Population/co.Population AS PopPercent
	FROM Country AS co, City AS ci WHERE Code = CountryCode AND Code = 'CHE';

SELECT co.Name, ci.Name, 100 * ci.Population/co.Population AS PopPercent
	FROM Country AS co, City AS ci WHERE Capital = ID 
    AND Continent = 'South America' ORDER BY PopPercent DESC;
    

SELECT Name FROM Country JOIN CountryLanguage
		ON Code = CountryCode
        WHERE (Language, IsOfficial) = ('Spanish', 'T');

SELECT co.Name, COUNT(*) AS CityCount
	FROM Country co JOIN City ON Code = CountryCode
    WHERE Continent = 'South America'
    GROUP BY co.Name ORDER BY CityCount DESC;
    
 SELECT co.Name, GROUP_CONCAT(Language ORDER BY Language) AS Languages
	FROM Country AS co JOIN CountryLanguage ON Code = CountryCode
    WHERE Continent = 'South America'
    GROUP BY co.Name;

SELECT co.Name, co.Population CountryPop, SUM(ci.Population) AS Urban,
	co.Population - SUM(ci.Population) AS Rural
    FROM Country AS co JOIN City AS ci ON Code = CountryCode
    WHERE Continent = 'South America'
    GROUP BY co.Name;
    
SELECT Name FROM Country JOIN CountryLanguage
	ON Code = CountryCode WHERE (Language, IsOfficial) = ('Spanish', 'T')
    LIMIT 3;
    
SELECT Name FROM Country JOIN CountryLanguage
	ON Code = CountryCode AND (Language, IsOfficial) = ('Spanish', 'T')
    LIMIT 3;
    
SELECT co.Name, COUNT(*) AS CityCount
	FROM Country co JOIN City ON Code = CountryCode
	WHERE Continent = 'South America'
    GROUP BY co.Name ORDER BY CityCount DESC;
    
SELECT co.Name, COUNT(*) AS CityCount
	FROM Country co JOIN City ON Code = CountryCode
	AND Continent = 'South America'
    GROUP BY co.Name ORDER BY CityCount DESC;     
    
SELECT co.Name, COUNT(*) AS CityCount
	FROM Country co JOIN City ON Code = CountryCode
	AND Continent = 'South America'
    GROUP BY co.Name HAVING CityCount > 20 ORDER BY CityCount DESC;     

# The query has to be repeated to be used in an on clause because no intermediate
# results exist.

SELECT co.Name, ci.Name, 100 * ci.Population / co.Population AS PopPercent
	FROM Country AS co JOIN City AS ci ON Code = CountryCode AND Code = 'Che'
    AND 100 * ci.Population / co.Population > 2;
    
SELECT Language, IsOfficial, Name, ci.CountryCode FROM
	CountryLanguage JOIN City AS ci USING (CountryCode) WHERE Name = 'San Jose';
    
# Natural Join, very, very rare

# Outer Join, used to preserve all the rows in one table in a result even if no corresponding rows exist 
# in the other table

SELECT Name FROM Country LEFT JOIN CountryLanguage 
	ON Code = CountryCode WHERE Language IS NULL;

# Nothing
SELECT Name FROM Country RIGHT JOIN CountryLanguage 
	ON Code = CountryCode WHERE Language IS NULL;
    
SELECT c1.IndepYear, c2.Name FROM Country AS c1 JOIN Country AS c2
	ON c2.IndepYear = c1.IndepYear AND c1.Name = 'Jamaica';
    
SELECT c2.Name, ROUND(c2.Population / c1.Population, 2) AS 'x NYC'
	FROM City c1 JOIN City c2
    ON c1.Name = 'New York' AND c2.Population > c1.Population
    ORDER BY `x NYC` DESC;
    
 SELECT c2.Name, co.Name AS Country,
	ROUND(c2.Population/c1.Population, 2) 'x NYC'
    FROM City AS c1, City AS c2, Country AS co
    WHERE c1.Name = 'New York' AND c2.Population >= c1.Population
    AND Code = c2.CountryCode ORDER BY `x NYC` DESC;
    
SELECT ROUND(AVG(ContSUM)) 'Average Continent Population' FROM (
	SELECT SUM(Population) ContSum FROM Country GROUP BY Continent) x;

SELECT co.Continent, Name, SurfaceArea
	FROM Country AS co JOIN (
		SELECT Continent, MAX(SurfaceArea) AS Biggest
		FROM Country GROUP BY Continent) AS cm
	ON (co.Continent, SurfaceArea) = (cm.Continent, Biggest)
    ORDER BY co.SurfaceArea DESC;
    
# Views:  Database object based on a SELECT query that is used as if it were a table.

CREATE VIEW NordicCountries AS
	SELECT Code, Name, Population FROM Country
    WHERE Region = 'Nordic Countries';
    
EXPLAIN NordicCountries;    
SHOW CREATE TABLE NordicCountries;

SHOW FULL TABLES;

CREATE VIEW BigNordic AS 
	SELECT * FROM NordicCountries
    WHERE Population> 100000;
    
SHOW FULL TABLES;

CREATE VIEW v_Continents AS 
	SELECT Continent AS Name, COUNT(*) AS Countries,
    SUM(Population) AS Population, SUM(SurfaceArea) AS SurfaceArea
    FROM Country GROUP BY Continent;
    
CREATE VIEW v_BalticCountries (column1,column2,column3) AS
	SELECT Code, Name, Population FROM Country
    WHERE Region = 'Baltic Countries';
    
DESCRIBE v_BalticCountries;

CREATE VIEW NordicDensities AS
	SELECT Code, Name, Population/SurfaceArea
    FROM Country WHERE Region = 'Nordic Countries';
    
DESCRIBE NordicDensities;

CREATE VIEW NordicDensities2 AS
	SELECT Code, Name, Population/SurfaceArea AS Density
    FROM Country WHERE Region = 'Nordic Countries';
    
DESCRIBE NordicDensities2;

CREATE VIEW NordicCapitals AS
	SELECT Code, co.Name AS CountryName,
    ci.Name AS Capital, 100*ci.Population/co.Population AS PercentPop
    FROM Country co JOIN City ci ON Capital = ID
    WHERE Region = 'Nordic Countries';
    
# Updating Views:  Views can be updated if there is a one-to-one relationship
# With the underlying data model.  Aggregated columns do not work.

SELECT * FROM NordicCountries;

UPDATE NordicCountries SET Population = Population + 100 WHERE Code = 'FRO';

SELECT * FROM Country WHERE Code = 'FRO';

DELETE FROM NordicCountries WHERE Code = 'SJM';

# INSERTS INTO TABLES:  No calculated rows, columns not exposed to the view must have a default value,
# no redundant columns.

# View Consistency

CREATE VIEW NordicCountries2 AS
	SELECT Code, Name, Population FROM Country
    WHERE Region = 'Nordic Countries'
    WITH CHECK OPTION;
    
CREATE TABLE demo1 (id int, name char(10));
CREATE VIEW vdemo1 AS SELECT name FROM demo1;

CHECK TABLE vdemo1;

CREATE OR REPLACE VIEW NordicCountries AS
	SELECT Code, Name, Population FROM Country
    WHERE Region = 'Nordic Countries'
    WITH CHECK OPTION;
    
SHOW TABLE STATUS LIKE 'NordicCountries';

SHOW FULL TABLES;

DROP TABLE Capitals;

CREATE TABLE Capitals (
	id INT UNSIGNED UNIQUE,
    name  VARCHAR(52) NOT NULL,
    capital VARCHAR(35)
);

INSERT INTO Capitals SELECT id, co.name, ci.name
	FROM Country AS co LEFT JOIN city as ci ON capital = id;
    
SELECT * FROM Capitals;

CREATE TABLE newcity LIKE city;
INSERT INTO newcity SELECT * FROM city;

INSERT INTO newcity (name, countrycode, district, population)
	SELECT name, countrycode, district, population FROM newcity;
    
INSERT INTO newcity (name, countrycode, district, population)
	SELECT * FROM (SELECT name, countrycode, district, population FROM newcity)
    AS x;
    
ALTER TABLE Capitals ADD cities SMALLINT unsigned;

UPDATE Capitals SET cities = (
	SELECT COUNT(*) FROM city WHERE countrycode = (
		SELECT countrycode FROM city WHERE city.id = capitals.id));
        
SELECT * FROM Capitals;

# DELETE <delete tables> FROM <join tables> [WHERE <criteria>]
# DELETE FROM <delete tables> USING <joint tables> [WHERE <criteria>]

DELETE ci, cl
	FROM City ci JOIN Country co ON ci.CountryCode = Code
    JOIN CountryLanguage cl on cl.CountryCode = Code
    WHERE Code = 'CHE';
    
CREATE TABLE city1 (
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	Name CHAR(35) NOT NULL DEFAULT '',
	CountryCode CHAR(3) NOT NULL DEFAULT '',
	District CHAR(20) NOT NULL DEFAULT '',
	Population INT UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TEMPORARY TABLE city03 (
	ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Name CHAR(35) NOT NULL DEFAULT '',
	CountryCode CHAR(3) NOT NULL DEFAULT '',
	District CHAR(20) NOT NULL DEFAULT '',
	Population INT UNSIGNED NOT NULL DEFAULT '0',
	INDEX (CountryCode)
) ENGINE=InnoDB;

DESC city03;

SHOW INDEX FROM city03;

CREATE TABLE people2 (
	firstname VARCHAR(15),
    lastname VARCHAR(20),
    address VARCHAR(50),
    city VARCHAR(25),
    state CHAR(2),
    zip CHAR(5),
    INDEX fullname (lastname, firstname)
);

# Atomicity -- All command steps are executed successfully or cancelled as a Unit.
# Consistency -- Leave the database in a state consistent with before transaction began.
# Isolation -- Transactions on one thread are not affected by transactions happening on others.
# Durability -- committed changes are not lost.

SELECT @@autocommit;

SET @@autocommit = OFF;
SET @@autocommit = ON;

START TRANSACTION;
-- BEGIN or BEGIN WORK are aliases of START TRANSACTION.
#BEGIN WORK;
#BEGIN;
-- Do some code

SELECT IF(1, COMMIT, ROLLBACK);

-- SAVEPOINT Basics.  
-- Savepoint has a unique name on the thread.
-- We can rollback to a savepoint without going all the way back to the beginning.
-- All savepoints are cleared on COMMIT or ROLLBACK.

# ISOLATION LEVEL
# READ UNCOMMITTED (Allows Dirty Reads, or uncommitted changes)
# READ COMMITTED (Allows you to only read committed changes)
# REPEATABLE READ (Stores multiple reads)
# SERIALIZABLE (Puts a read-lock on the rows that have been read, 
# so no one can use them until the change has been committed or rolled back).

# ISOLATION is per Session.  MySQL Defaults to Repeatable reads.

# TX_ISOLATION

SELECT @@tx_isolation; 
SET tx_isolation := '';

# CREATE FUNCTION <database>.<name> ([<parameter defs>]) RETURNS <data type>
# <statement containing RETURN command>
# For portability, we normally create a seperate database that holds all 
# these different pieces.

CREATE FUNCTION world.rndb (original FLOAT, basis FLOAT) RETURNS FLOAT
	RETURN TRUNCATE(original/basis + .5, 0) * basis;

SELECT world.rndb(27.87, .25);
SELECT world.rndb(27.88, .25);

USE world;

SELECT Name, world.rndb(Population, 5000) FROM City 
	WHERE CountryCode = 'CHE';

# CREATE STORED PROCEDURE

# CREATE PROCEDURE <name> ([<parameter defs>]) <statement>
# Procedures go into the database they are manipulating, so they belong
# in the same database.

CREATE PROCEDURE CityCount (ccode CHAR(3))
	SELECT co.Name, COUNT(*) Cities
    FROM City ci JOIN Country co ON Code = CountryCode
    WHERE Code = ccode;

# Invoking procedure

CALL CityCount('USA');
CALL CityCount('CAN');

SHOW CREATE FUNCTION world.rndb;
SHOW CREATE PROCEDURE world.CityCount;
SHOW FUNCTION STATUS LIKE 'rndb';
SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
WHERE routine_name = 'rndb';

SELECT * FROM mysql.proc WHERE name = 'rndb';

# DROP PROCEDURE world.showdb;

# BEGIN .... END;

DELIMITER $$

CREATE FUNCTION AddTax (amount DEC(6,2), taxrate DEC (4,2))
	RETURNS DEC (7,2)
BEGIN
	RETURN ROUND(amount * (1 + taxrate/100), 2);
END$$

DELIMITER ;

# PARAMTER TYPES:
# IN -- Same as function parameters, expression evaluated and value is passed.

SELECT AddTax(22, 48);

-- Better for PHP because $$ has a reserved definition in php.
DELIMITER //
CREATE PROCEDURE CountryInfo (ccode CHAR(3))
BEGIN
	SELECT Name, Population, Continent FROM Country
		WHERE Code = ccode;
	SELECT * FROM City WHERE CountryCode = ccode;
END//

DELIMITER ;

DELIMITER //
CREATE PROCEDURE CountryInfo2 (IN ccode CHAR(3))
BEGIN
	SELECT Name, Population, Continent FROM Country
		WHERE Code = ccode;
	SELECT * FROM City WHERE CountryCode = ccode;
    SELECT * FROM CountryLanguage WHERE CountryCode = ccode;
END//

DELIMITER ;

CALL CountryInfo2('CHE');

CALL CountryInfo2(CONCAT('L','U','X'));

SET @codestring := 'NOR';

CALL CountryInfo2(@codestring);

# Pass by reference

DELIMITER //
CREATE PROCEDURE AddTax (INOUT amount DEC (6,2), taxrate DEC (4,2))
	BEGIN
		SET amount = ROUND(amount * (1 + taxrate/100), 2);
	END //
    
DELIMITER ;

SET @total := 24.72;

CALL AddTax(@total, 8.25);
SELECT @total;

-- New Value is 26.76

-- OUTPUT only parameter.

-- CREATE local variable using DECLARE in the BEGIN block.  Only exist
-- FROM the begin block, also in nested blocks of BEGIN, unless overriden
-- By another local variable with same name.

-- DECLARE <name> [, <name>...] <type> [DEFAULT <value>]

-- MUST OCCUR before anything else in the begin block.
-- Can declare many variables of same type and value 
-- but other types or values must be on seperate lines.

DELIMITER //
CREATE FUNCTION poppercent (ccode CHAR(3)) RETURNS DECIMAL (4,2)
	BEGIN
		DECLARE cpop, totalpop BIGINT;
        SELECT population FROM country WHERE code = ccode INTO cpop;
        SELECT SUM(population) FROM country INTO totalpop;
        RETURN cpop/totalpop * 100;
	END//

DELIMITER ;

SELECT poppercent ('CHN');
SELECT poppercent ('USA');

-- IF <condition> THEN <statementlist>
	-- [ELSEIF <condition> THEN <statementlist>]
    -- [ELSE <statementlist>]
-- END IF;

-- CASE <referencevalue>
	-- WHEN <comparisonvalue> THEN <statementlist>
    -- [WHEN <comparisonvalue> THEN <statementlist>]
    -- [ELSE <statementlist>]
-- END CASE;

-- Binary comparison of reference and comparison.
-- First exact match executes.  Error is thrown if no blocks match.
-- Use ELSE to avoid that.

-- CASE
-- WHEN <condition> THEN <statementlist>
-- [WHEN <condition> THEN <statementlist>]
-- [ELSE <statementlist>]
-- END CASE;

-- Same as an if statement, but it will give an error if nothing works.

DELIMITER //
CREATE FUNCTION AddTaxTipIf (amount DEC (6,2), taxrate DEC (4,2))
	RETURNS DEC (7, 2)
BEGIN
	IF amount < 10 THEN
		SET amount = amount * (1 + taxrate/100) + 2;
	ELSEIF amount > 100 THEN
		SET amount = amount * (1 + (taxrate + 16)/100);
	ELSE
		SET amount = amount * (1 + (taxrate + 20)/100);
	END IF;
    RETURN ROUND(amount,2);
END //

DELIMITER ;

SELECT AddTaxTipIf(1000, 4);
SELECT AddTaxTipIf(5, 10);
SELECT AddTaxTipIf(20, 10);

DELIMITER //
CREATE PROCEDURE TestIfNotUsed (amount TINYINT)
BEGIN
	IF amount > 10 THEN
		SELECT * FROM City WHERE CountryCode = 'CHE';
	END IF;
END //

DELIMITER ;

CALL TestIfNotUsed(20);
CALL TestIfNotUsed(4);

DELIMITER //
CREATE FUNCTION AddTaxTipCase (amount DEC (6,2), taxrate DEC (4,2))
	RETURNS DEC (7, 2)
BEGIN
	CASE WHEN amount < 10 THEN
		SET amount = amount * (1 + taxrate/100) + 2;
	WHEN amount > 100 THEN
		SET amount = amount * (1 + (taxrate + 16)/100);
	ELSE
		SET amount = amount * (1 + (taxrate + 20)/100);
	END CASE;
    RETURN ROUND(amount,2);
END //

DELIMITER ;

DELIMITER //
CREATE PROCEDURE TestCaseNotUsed (amount TINYINT)
BEGIN
	CASE WHEN amount > 10 THEN
		SELECT * FROM City WHERE CountryCode = 'CHE';
	END CASE;
END //

DELIMITER ;

CALL TestCaseNotUsed(20);
CALL TestCaseNotUsed(4);

DELIMITER //
CREATE PROCEDURE TestCaseCompare (letter CHAR(1))
BEGIN
	CASE WHEN 'A' THEN
		SELECT * FROM City WHERE CountryCode = 'CHE';
	ELSE
		BEGIN
        END;
	END CASE;
END //

DELIMITER ;

CALL TestCaseCompare('A');
CALL TestCaseCompare('a');

-- Loop Block
-- [<label>:]LOOP
-- <statementlist>
-- END LOOP[<label>];

-- as shown, infinite loop, nest at least one if block that will use the 
-- LEAVE command.  Leave command must have a label.

-- REPEAT BLOCK
-- [<label>:]REPEAT
-- 	<statementlist>
-- UNTIL <condition>
-- END REPEAT[<label>];

-- Executes at least once, exits at the end

-- WHILE Block

-- [<label>:]WHILE <condition> DO
-- <statementlist>
-- END WHILE[<label>];

-- If the condition, the loop repeats.

DELIMITER //
CREATE PROCEDURE accumulate (upperlimit TINYINT UNSIGNED)
BEGIN
	DECLARE counter TINYINT UNSIGNED DEFAULT 0;
    DECLARE sumvalue INT UNSIGNED DEFAULT 0;
    testloop: LOOP
		SET counter = counter + 1;
        SET sumvalue = sumvalue + counter;
        IF counter >= upperlimit THEN
			LEAVE testloop;
		END IF;
	END LOOP testloop;
    SELECT sumvalue;
END//

DELIMITER ;

CALL accumulate (10);

DELIMITER //
CREATE PROCEDURE accumulateRepeat (upperlimit TINYINT UNSIGNED)
BEGIN
	DECLARE counter TINYINT UNSIGNED DEFAULT 0;
    DECLARE sumvalue INT UNSIGNED DEFAULT 0;
    REPEAT
		SET counter = counter + 1;
        SET sumvalue = sumvalue + counter;
	UNTIL counter >= upperlimit END REPEAT;
    SELECT sumvalue;
END//

DELIMITER ;

CALL accumulateRepeat (10);
CALL accumulateRepeat (0);

DROP PROCEDURE IF EXISTS accumulateRepeat;

DELIMITER //
CREATE PROCEDURE accumulateRepeat (upperlimit TINYINT UNSIGNED)
main: BEGIN
	DECLARE counter TINYINT UNSIGNED DEFAULT 0;
    DECLARE sumvalue INT UNSIGNED DEFAULT 0;
    IF upperlimit < 1 THEN
		SELECT 'Parameter value must be greater than zero.' AS UserError;
        LEAVE main;
	END IF;
    REPEAT
		SET counter = counter + 1;
        SET sumvalue = sumvalue + counter;
	UNTIL counter >= upperlimit END REPEAT;
    SELECT sumvalue;
END//

DELIMITER ;

CALL accumulateRepeat (10);
CALL accumulateRepeat (0);

DELIMITER //
CREATE PROCEDURE accumulateWhile (upperlimit TINYINT UNSIGNED)
main: BEGIN
	DECLARE counter TINYINT UNSIGNED DEFAULT 0;
    DECLARE sumvalue INT UNSIGNED DEFAULT 0;
    WHILE counter < upperlimit DO
		SET counter = counter + 1;
        SET sumvalue = sumvalue + counter;
	END WHILE;
    SELECT sumvalue;
END//

DELIMITER ;

CALL accumulateWhile (10);
CALL accumulateWhile (0);

DELIMITER //
CREATE PROCEDURE accumulateIterate (upperlimit TINYINT UNSIGNED)
main: BEGIN
	DECLARE counter TINYINT UNSIGNED DEFAULT 0;
    DECLARE sumvalue INT UNSIGNED DEFAULT 0;
    testloop: LOOP
    	SET counter = counter + 1;
        SET sumvalue = sumvalue + counter;
        IF counter < upperlimit THEN
			ITERATE testloop;
		END IF;
        LEAVE testloop;
	END LOOP testloop;
	SELECT sumvalue;
END//

DELIMITER ;

CALL accumulateIterate(123);

DELIMITER //
CREATE PROCEDURE evenNumbers (upperlimit TINYINT UNSIGNED)
main: BEGIN
	DECLARE counter TINYINT UNSIGNED DEFAULT 0;
    evenloop: WHILE counter < upperlimit DO
    	SET counter = counter + 1;
        IF MOD(counter, 2) THEN
			ITERATE evenloop;
		END IF;
        SELECT CONCAT(counter, ' is an even integer <= ', upperlimit)
        AS result;
        END WHILE;
END//

DELIMITER ;

CALL evenNumbers(36);

DROP PROCEDURE IF EXISTS TestCaseNotUsed;

DELIMITER //

CREATE PROCEDURE TestCaseNotUsed
	(amount TINYINT, OUT successflag TINYINT)
BEGIN
	DECLARE case_invalid_amount CONDITION FOR 1339;
    DECLARE EXIT HANDLER FOR case_invalid_amount
		BEGIN
			SET successflag = 0;
            SELECT 'Parameter value must be > 10' as UserError,
				successflag AS Success;
		END;
	CASE WHEN amount > 10 THEN
		SET successflag = 1;
        SELECT 'We have this amount in value' as UserSuccess,
			successflag AS Success;
	END CASE;
END//
DELIMITER ;

CALL TestCaseNotUsed(5, @flag);
SELECT @flag;

DELIMITER //
CREATE FUNCTION findFunction (testname CHAR (64), dbname CHAR(64))
	RETURNS TINYINT
BEGIN
	DECLARE result TINYINT DEFAULT 0;
	DECLARE curr_func_name CHAR (64);
	DECLARE no_more_funcs TINYINT DEFAULT 0;
	DECLARE dbfuncs CURSOR FOR
		SELECT routine_name FROM information_schema.routines
			WHERE routine_schema = dbname AND routine_type = 'function';
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_funcs = 1;
	OPEN dbfuncs;
	checknames: LOOP
		FETCH dbfuncs INTO curr_func_name;
		IF no_more_funcs THEN
			LEAVE checknames;
		END IF;
		IF curr_func_name = testname THEN
			SET RESULT = 1;
			LEAVE checknames;
		END IF;
	END LOOP checknames;
	RETURN result;
END//

DELIMITER ;

SELECT findfunction ('findfunction', 'world');
SELECT findfunction ('foo', 'world');

-- Cursors are closed implicitly when the begin block ends
-- Handlers cannot distinguish between cursors.
-- Cursor cannot be reopened unless it has been closed.
-- Receiving variables should have different names from the columns in SELECT
-- Cursors are read-only in MySQL
-- Single-fetched.  only one row available.
-- successive, fetch next row only.  Can't go back or jump over.
-- Asensitive, server decides whether RAM/disk, not programmer.
-- Temporary.

-- TRIGGERS are database objects (procedures) associated with a table.
-- Procedure is automatically invoked by the execution of a DML command on the table.
-- Acts as part of the command that invoked it.

-- Triggers are with a table, procedures are global.
-- Procedures are stored in proc table, triggers are seperate.
-- Not called directly like a procedure.
-- No parameters.

-- Special syntax for accessing table columns.

-- Data validation, populating derived columns, change tracking and activity logging,
-- data integrity and consistency.  Archiving deleted rows.  Emulating cascading foreign key constraints

-- CREATE [DEFINER = <definer>]
-- TRIGGER <name> <timing> <operation> ON <table>
-- FOR EACH ROW
-- <statement>

-- Timing options:  BEFORE | AFTER
-- Operations:  INSERT | UPDATE | DELETE
-- Statement can be compound.
-- Naming conventions: <table>_<t><o>

CREATE TABLE birthdays (name char(10), bdate DATE, bmonth tinyint(4));

CREATE TRIGGER birthdays_bi BEFORE INSERT ON birthdays
	FOR EACH ROW
    SET NEW.bmonth = MONTH(NEW.bdate);

-- New is the prefix or qualified new row, OLD.

INSERT INTO birthdays (name, bdate) VALUES
	('Ann', '1980-12-04'), ('Andy', '1982-04-12');
    
SELECT * FROM birthdays;

DELIMITER //
CREATE TRIGGER birthdays_bu BEFORE UPDATE ON birthdays
FOR EACH ROW
BEGIN
	IF NEW.bdate <> OLD.bdate THEN
		SET NEW.bmonth = MONTH(NEW.bdate);
	END IF;
END//

DELIMITER ;

CREATE TABLE deletedbirthdays (name char(10), bdate DATE);

CREATE TRIGGER birthdays_ad AFTER DELETE ON birthdays
FOR EACH ROW
INSERT INTO deletedbirthdays VALUES (OLD.name, OLD.bdate);    

DELETE FROM birthdays;

SELECT * FROM deletedbirthdays;

SHOW CREATE TRIGGER birthdays_bi;

-- Triggers should be made from a specialized account.

SHOW TRIGGERS WHERE `Trigger` = 'birthdays_bi';
desc information_schema.triggers;

SELECT * FROM information_schema.triggers WHERE trigger_name = 'birthdays_bi';

# DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

DROP TRIGGER IF EXISTS birthdays_bi;

-- Definer Details
-- Defaults to account of current user.
-- if user is SUPER, can specify another account, which is best practice.
-- Account must have TRIGGER privilege
-- Must also have SELECT privilege if OLD or NEW keyword is used.
-- Likewise other privileges are dictated by statements used.

-- Recursive trigger chains not allowed.
-- Can't used prepared statements.  Prepared statements are on a thread.
-- Can't perform commit or rollback.
-- Do not use @variables in triggers.  They are session-based and will leak memory because they cannot be removed.

-- Events run on a schedule, similar to a unix cron job or scheduled task in Windows.
-- Runs in its own session, can be one-time or recurring.

-- Basic event syntax.  Name must be unique within the database.

-- CREATE [DEFINED = <definer>] EVENT [IF NOT EXISTS] <name>
-- ON SCHEDULE <schedule>
-- [ON COMPLETION [NOT] PRESERVE]
-- [ENABLE | DISABLE | DISABLE ON SLAVE]
-- DO <statement>

-- ONE TIME events
-- ON SCHEDULE AT <timestamp expression>
-- Immediate, specific time, now or specific time plus interval.

-- Recurring Events
-- EVERY <interval>
-- [STARTS <timestamp expression>]
-- [ENDS <timestamp expression>]

-- If an event fails, errors are logged in teh error log.

CREATE EVENT `cityoptimizer` ON SCHEDULE EVERY 15 MINUTE
	ON COMPLETION PRESERVE ENABLE
    DO OPTIMIZE TABLE city;

SHOW CREATE EVENT cityoptimizer;

SHOW EVENTS;

SELECT * FROM information_schema.events;

SELECT * FROM mysql.event;

-- An index is a seperate table that maintains row order for a column or combinatino of columns of a table.

-- Contents are already sorted by the data type of each column
-- Improves speed of retrieval for queries that use it.
-- Can also impose a uniqueness constraints.  (Primary, Unique)
-- Index slows down data manipulation operations because it is dynamically maintained.
-- Too many indexes are bad.
-- Index statistics require periodic updating if data changes frequently.

-- Primary Key (Unique row identifier).  Unique (When uniqueness constraints, can use nulls).
-- Non-unique are used when ordering is needed.  -- Fulltext (word searches).  Spatial (index on piont, line, area data).
-- Foreign key is not a type of index, it is a constraint that uses a non-unique index.

show tables;

CREATE TABLE city4 (
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    NAME CHAR(35) NOT NULL DEFAULT '',
    CountryCode CHAR(3) NOT NULL DEFAULT '',
    DISTRICT CHAR(20) NOT NULL DEFAULT '',
    Population INT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (ID)
) ENGINE = INNODB;

DESC city4;

DROP TABLE city4;

CREATE TABLE city4 (
	ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    NAME CHAR(35) NOT NULL DEFAULT '',
    CountryCode CHAR(3) NOT NULL DEFAULT '',
    DISTRICT CHAR(20) NOT NULL DEFAULT '',
    Population INT UNSIGNED NOT NULL DEFAULT '0',
    INDEX (CountryCode)
) ENGINE = INNODB;

DESC city4;

SHOW INDEX FROM city4;

# DROP INDEX 

create table people4 (
	firstname VARCHAR(15),
    lastname VARCHAR(20),
    address VARCHAR(50),
    city VARCHAR(25),
    state CHAR(2),
    zip CHAR(5),
    INDEX fullname (lastname, firstname)
);

-- Prefix length

-- 

CREATE TABLE countryPrefix (
	Code char(3) NOT NULL DEFAULT '',
    Name char(52) NOT NULL DEFAULT '',
    SurfaceArea float(10,2) NOT NULL DEFAULT '0.00',
    Population int (11) NOT NULL DEFAULT '0',
    LifeExpectancy float (3,1) DEFAULT NULL,
    PRIMARY KEY (Code),
    INDEX (Name (15))
) ENGINE=InnoDB;

SHOW INDEX FROM countryPrefix;
    
-- Covering Index (compound) 
-- Fulltext Indexes

CREATE TABLE books (
	id int(11) NOT NULL AUTO_INCREMENT,
    author varchar(35) DEFAULT NULL,
    title VARCHAR(200) DEFAULT NULL,
    PRIMARY KEY (id),
    FULLTEXT KEY title (title),
    FULLTEXT KEY author (author)
) ENGINE=InnoDB;
    
SHOW INDEX FROM books;

SELECT * FROM books WHERE MATCH (title) AGAINST ('moon');

-- SELECT * FROM books WHERE MATCH (title) AGAINST ('moon earth');

SELECT MATCH(title) AGAINST ('moon earth') SCORE, Title FROM books
	WHERE MATCH (title) AGAINST ('moon earth');
    
CREATE INDEX city_indx ON city (CountryCode);

SELECT Code, Name, Continent, Population, GovernmentForm
	FROM Country PROCEDURE ANALYSE();
    
SELECT Code, Name, Continent, Population, GovernmentForm
FROM Country PROCEDURE ANALYSE (10, 256);

SELECT * FROM Country WHERE Name = 'Switzerland';

SELECT * FROM Country WHERE Code = 'CHE';

-- Suboptimal query.  239 results it has to search
EXPLAIN SELECT * FROM Country WHERE Name = 'Switzerland';

-- Optimal query 1 row looked up.
EXPLAIN SELECT * FROM Country WHERE Code = 'CHE';

-- Covering indexes.

SELECT Code, Name FROM Country WHERE Code LIKE 'Z%';
SELECT Code FROM Country WHERE Code LIKE 'Z%';

-- First one looks up three rows.
EXPLAIN SELECT Code, Name FROM Country WHERE Code LIKE 'Z%';

-- Using index, locating both indexes and where clause.
EXPLAIN SELECT Code FROM Country WHERE Code LIKE 'Z%';

SELECT co.Name, ci.Name
FROM Country co JOIN City ci ON Code = CountryCode
WHERE Code = 'CHE';

EXPLAIN SELECT co.Name, ci.Name
FROM Country co JOIN City ci ON Code = CountryCode
WHERE Code = 'CHE';

ALTER TABLE City ADD INDEX (CountryCode);

-- Index Test.  Looking for least variable character string length available.

SELECT COUNT(DISTINCT(col)),
       COUNT(DISTINCT(LEFT(col,n)))
       FROM table;