MySQL Notes

--Create a database
	CREATE SCHEMA name;
	CREATE DATABASE name;

--Creating tables
	CREATE TABLE [IF NOT EXISTS] name (
	col-name datatype [col-options],
	:
	col-name datatype [col-options],
	[constraint-1],
	:
	[constraint-n]
	);
		example:
		CREATE TABLE `staff` (
		`name` VARCHAR(12),
		`is_sick` TINYINT(1),
		`staff_id` INT(11),
		`salary` DECIMAL(5,2)
		);
--Contents between [] are optional

--Numerical Data Types (Integers)
	SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
	--Use 2 Bytes of memory, signed range: -32768 to 32767
	MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
	--Use 3 Bytes of memory.
	INT[(M)] [UNSIGNED] [ZEROFILL]
	--Same as INTEGER[(M)] [UNSIGNED] [ZEROFILL]
	--Use 4 Bytes of memory.
	BIGINT[(M)] [UNSIGNED] [ZEROFILL]
	--Use 8 Bytes of memory.

-- Numerical Data Types (Fixed Point)
	DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
	NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
	FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

--Numerical Data Types (Float)
	Float(p)  --IEEE754
	DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
	FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

--String Types
	CHAR[(M)]
		--A fixed-length (M is the length, 0 ~ 255) string that is always rightpadded with spaces 
		  --to the specified length when stored.
		--if you store 'A' to CHAR(5), it will actually be 'A ' inside the memory. But when you 
		  --retrieve the value, the trailing spaces will
		--be removed automatically (this behaviour can be turned on or off).
	VARCHAR(M)
		--A variable-length string.
		--The range of M is 0 to 65,535.
	--CHAR is faster, but occupies more memory
	--VARCHAR is slower, but occupies less memory
			--Example
			CREATE TABLE `staff` (
			`first_name` VARCHAR(12),
			`last_name` CHAR(12)
			);
			insert into `staff` values ('Daryl', 'Sarah');

	
-- Date and Time
		--Example 
		CREATE TABLE `staff` (
		`first_name` VARCHAR(12),
		`last_name` CHAR(12),
		`recruit_date` DATE,
		`last_login` DATETIME
		);	


--Column options
		NOT NULL:
	--values of this column cannot be null.
		UNIQUE:
	--each value must be unique (candidate key on a single attribute)
		DEFAULT
	--Default value for this column if not specified by the user.
	AUTO_INCREMENT = baseValue
		--Must be applied to a key column (primary key, unique key) a value (usually max(col) + 1)
		  --is automatically inserted when data is added.		
		--You can also manually provide values to override this behaviour.			
		--Example
			CREATE TABLE Persons (
			id INT UNIQUE NOT NULL AUTO_INCREMENT,
			lastname VARCHAR(255) NOT NULL,
			firstname VARCHAR(255),
			age INT DEFAULT 12,
			city VARCHAR(255)
			) AUTO_INCREMENT = 5;

		
--Tuple Manipulation(INSERT, UPDATE, DELETE)
	--INSERT
		INSERT INTO tablename (col1, col2, ⋯)
		VALUES (val1, val2, ⋯),
		:
		(val1,val2,val3);
		--Example
		INSERT INTO `Employee`
		(`ID`, `Name`, `Salary`)
		VALUES (2, 'Mary', 26000);
		
		INSERT INTO Employee
		(Name, ID)
		VALUES ('Mary', 2);
	
		INSERT INTO Employee
		VALUES (2, 'Mary', 26000),
		(3, 'Max', 19000);		
	--UPDATE
		UPDATE table-name
		SET col1 = val1 [,col2 = val2⋯]
		[WHERE condition]
		--Example 
		UPDATE Employee
		SET
		Salary = 15000,
		Name = ‘Jane’
		WHERE ID = 4;
	
		UPDATE Employee
		SET Salary =
		Salary * 1.05;
	--DELETE
		DELETE FROM
		table-name
		[WHERE condition]
	--If no condition is given then ALL rows are deleted.
		--Example
		DELETE FROM
		Employee WHERE
		Salary > 20000;
	
		DELETE FROM
		Employee;

--Table Constraints
	--syntax OF CONSTRAINTS 
		CONSTRAINT name TYPE details;
	--MySQL provides following constraint types
		PRIMARY KEY	--PRIMARY KEY also automatically adds UNIQUE and NOT NULL to the relevant 
					  --column definition					
		UNIQUE
		FOREIGN KEY
		INDEX
	
	--Syntax of Foreign Key
		CONSTRAINT name
		FOREIGN KEY
		(col1, col2, ...)
		REFERENCES
		table-name
		(col1, col2, ...)
		[ON UPDATE ref_opt
		ON DELETE ref_opt]
			--Example 
		 	CREATE TABLE staff (
			staffNo CHAR(6) PRIMARY KEY,
			fName VARCHAR(20),
			branchNo CHAR(4),
			CONSTRAINT FK_staff_branchNo FOREIGN KEY
			(branchNo)
			REFERENCES
			branch (branchNo)
			);
					--Binary Keyword
		
--Altering Tables
	--Add column:
	ALTER TABLE table_name
		ADD column_name datatype [options like UNIQUE ⋯];
	--Drop column
	ALTER TABLE table_name DROP COLUMN column_name;
	--Modify column name and definition
	ALTER TABLE table_name
		CHANGE COLUMN
		col_name new_col_name datatype [col_options];
	--Modify column definition only
	ALTER TABLE table_name
		MODIFY COLUMN
		column_name datatype [col_options];
			--Examples
			ALTER TABLE staff ADD `lName` VARCHAR(20) NOT NULL;
			ALTER TABLE staff DROP COLUMN `lName`;
			ALTER TABLE staff CHANGE COLUMN `fName`
			`first_name` VARCHAR(20) NOT NULL;
			ALTER TABLE staff MODIFY COLUMN
			`first_name` VARCHAR(40) NOT NULL;
		
	--Adding Constraints
	ALTER TABLE table-name
		ADD CONSTRAINT name definition;
			--Examples 
			ALTER TABLE branch
			ADD CONSTRAINT ck_branch UNIQUE (street);
			ALTER TABLE staff ADD CONSTRAINT fk_staff_staff
			FOREIGN KEY (branchNo) REFERENCES branch (branchNo);

	--Removing Constraints
		--To remove a constraint
		ALTER TABLE table-name
		DROP INDEX name | DROP FOREIGN KEY name | DROP PRIMARY KEY
			--Example
			ALTER TABLE staff DROP PRIMARY KEY;
			ALTER TABLE staff DROP FOREIGN KEY fk_staff_staff;
			ALTER TABLE branch DROP INDEX ck_branch;
	--Deleting Tables
		DROP TABLE [IF EXISTS] table-name1, table-name2⋯;
	
	
--SQL Select
	--To filter columns of a table 
		SELECT col1[,col2⋯] FROM table-name;
	--DISTINCT and ALL
		Using DISTINCT after the SELECT keyword removes duplicates 
		Using ALL retains duplicates.
	--Expressions in SELECT
		SELECT a, b, a+b AS sum
		FROM dup_test;
		--You can put simple expressions in Select statements;
	--Where 
		SELECT * FROM table-name
		WHERE predicate;
			    SELECT * FROM grade
				WHERE mark < 60;
			--Asterisk (*) meansgettingall columns of that table.
	--Word Search
		--LIKE
			--The ‘%’ character can represent any number of characters, including none
			--The following example will return “Cryptography Engineering” and “Cryptonomicon”
			  --but not “Applied Cryptography”
			bookNameLIKE'crypt%'
			--The ‘_’ character represents exactly one character
			--The following example will return “Clouds” but not “Cloud” or “cloud computing”
			bookNameLIKE'cloud_'
		    --Dealing with Date and Time(comparable);
			
	--Select and Cartesian Product
		--Cartesian product of two tables can be obtained by using:
			SELECT *FROM Table1, Table2;
		--This can be resolved by referencing columns with the table name:
			TableName.ColumnName
				--Example:
						SELECT
						First, Last, Mark
						FROM Student, Grade
						WHERE
						(Student.ID = Grade.ID)
						AND(Mark >= 40);
						----------------
						SELECT ... FROM Student, Grade WHERE
						(Student.ID = Grade.ID)AND ...
	--Select from Multiple Tables
		SELECT * FROM
		Student, Grade, Course
		WHERE
		Student.ID = Grade.ID AND
		Course.Code= Grade.Code	
	--Aliases
		--Column alias
		SELECT column[AS] new-col-name
		--Table alias
		SELECT *FROM table[AS] new-table-name
		--You cannot use a column alias in a WHERE clause:	
		--Example
			SELECT
			E.ID AS empID,
			E.Name, W.Department FROM
			Employee E,
			WorksInW
			WHERE
			E.ID = W.ID;
		
	--Subqueries		
		SELECT Name FROM Employee
		WHERE Dept =
		(SELECT Dept FROM Employee
		WHERE Name = 'Andy')
		---
		--Options for handling sets
		--IN: checks to see if a value is in a set
			SELECT columns FROM tables
				WHERE col IN set;
			--Example
			SELECT id FROM student
			WHERE id IN('S103', 'S104');
		--EXISTS: checks to see if a set is empty
				SELECT columns
				FROM tables
				WHERE EXISTS set;
				--Example
				SELECT * FROM
				Employee AS E1
				WHERE EXISTS
				(SELECT * FROM Employee AS E2
				WHERE E1.Name = E2.Manager);		
		--ALL/ANY: checks to see if a relationship holds for every/one member of a set
		--NOT: can be used with any of the above 4
	
--Joins
	--Joins
		--CROSS JOIN: returns all pairs of rows from A and B, the same as Cartesian product.
		
		--INNER JOIN: returns pairs of rows satisfying a condition.
		
		--NATURAL JOIN: returns pairs of rows with common VALUES in identically named columns.
		
		--OUTER JOIN: returns pairs of rows satisfying a CONDITION (as INNER JOIN), BUT ALSO 
		  --handles NULLs.		
		
		--INNER JOIN
			--INNER JOIN specifies a condition that pairs if rows must satisfy.
			SELECT * FROM A INNER JOIN B
				ON condition
				--Example
				SELECT * FROM
				Student INNER JOIN Enrollment
				USING (ID)
		--OUTER JOIN
			SELECT cols FROM
			table1 type OUTER JOIN table2
			ON condition;
			--Where type is one of LEFT, RIGHT or FULL. (L4 p13)
		--ORDER BY
			SELECT columns FROM tables
			WHERE condition
			ORDER BY cols [ASC | DESC]  (DESC down|ASC arise)

			--SQL SELECT OVERVIEW 
			SELECT
			[DISTINCT | ALL] column-list
			FROM table-names
			[WHERE condition]
			[GROUP BY column-list]
			[HAVING condition]
			[ORDER BY column-list] ([]optional)

--Aggregate Functions(聚集函数)
	--You can also use aggregate functions to compute summaries of data in a table.
		COUNT: --The number OF rows
		SUM: -- The sum of the entries in the column.
		AVG: -- The average entry in a column.
		MIN, MAX: -- The minimum/maximum entries in a column.
		--Most aggregate functions (except COUNt(*))work on a-single column of numerical data.
		
		COUNT
		SELECT 
			COUNT(*) AS COUNT
			FROM Grades;
		
		SELECT 
			COUNT(Code) AS COUNT
			FROM Grades;
		
		SELECT
			COUNT(DISTINCT Code)
				AS COUNT
			FROM Grades;
		
		SUM, MIN.MAX AND AVG
		SELECT
			SUM(Mark) AS Total
			FROM Grades;
		
		SELECT
			MAX(Mark) AS Best
			FROM Grades;
		
		SELECT
			AVG(Mark) AS Mean
			FROM Grades;
		
		Combining Aggregate Funcitons
		SELECT 
			MAX(Mark) - MIN(Mark)
				AS Range_of_Marks
			FROM Grades;
-- GROUP BY and HAVING (Used with aggregate functions)
	--GROUP BY
	--Sometimes we want to apply aggregare functions to groups of rows
		SELECT column_set1 FROM tables
			WHERE predicate 
			GROUP BY column_set2

		SELECT Name,
			AVG(Mark) AS Average
			FROM Grades
			GROUP BY Name;
	--HAVING
	SELECT Name, 
		AVG(Mark) AS Average
		FROM Grades
		GROUP BY Name
		HAVING 
			AVG(Mark) >= 40;
	-- WHERE and HAVING
		--WHERE refers to the rows of tables, so cannot make use of aggregate functions.
		--HAVING refers to the group of rows, and so cannot use columns or aggregate functions 
		  --that does
		 --not exist after the step of column selection
		-- Think of a query being processed as follows:
			--1. Tables are joined
			--2. WHERE clauses
			--3. GROUP BY clauses and aggregates
			--4. Column selection
			--5. HAVING clauses
			--6. ORDER BY
-- SQL SELECT OVERVIEW
	SELECT 
		[DISTINCT | ALL] column-list
		FROM table-names
			[WHERE condition]
			[GROUP BY column-list]
			[HAVING condition]
			[ORDER BY column-list]
									([] optional,| or)

--SET OPERATIONS (UNION, INTERSECT, EXCEPT)
	-- UNION, INTERSECT and EXCEPT
		--These treat the tables as sets and are the usual set operators of 
		  --union intersection and difference
		--Only UNION is supported in MySQL. The other two can be simulated with subqueries.
	--UNION: Example
		--1. The average for each student:
			SELECT Name, AVG(Mark) AS Average
				FROM Grades
				GROUP BY Name;
		--2. The average overall:
			SELECT `Total` AS Name,
				AVG(Mark) AS Average
			FROM Grades;
		--UNION
			SELECT Name,
				AVG(Mark) AS Average
				FROM Grades
				GROUP BY Name
			UNION
			SELECT 
				`Total` AS Name,
				AVG(Mark) AS Average
				FROM Grades;


--Missing Information (Dealing with nulls in SQL; Making use of default values)
	--Case 1: We know that there is a value, but dont know what it is.
	
	--Case 2: There is no value at all that makes any sense.
	
	--Two main methods:
		--1. NULLs can be used as markers to show that information is missing.
		--2. A default value can be used to represent the missing value.
	
	--NULL
		--NULL Represents a state for an attribute that is currently unknown or is not applicable 
		  --for this tuple
			--NULLs are a way to deal with incomplete or exceptional data.
			--NULL is a placeholder for missing or unknown value of an attribute.
			  --(IF IS NOT IFSELF A VALUE)
			--E.g. A new staff is just added, but hasnt been decided which branch he belongs to.
		
		--Codd proposed to distinguish two types of NULLs:
			-- A-marks: data Applicable but not known(for example, someone's age)
			-- I-marks: data is Inapplicable
			  --(telephone number for someone who doesnot have a telephone)
		
		--Problems with NULLs
			--Problems extending relational algebra operations to NULLs:
				--Selection operation: if we check tuples for "Mark > 40" and for some tuple
				 --Mark is NULL, do we include it ?
			--Comparing tuples in two relations: are two tuples(with NULLs) and the same or not?

		--Additional problems for SQL:
			--NULLs treated as duplicates?
			--Inclusion of NULLs in count, sum, average? If yes, how?
			--Arithmetic operations behaviors with argument NULL?
		
		--SQL NULLs in Conditions
			--WHERE clause of SQL SELECT uses three-valued logic: only tuples where the 
			  --condition evaluates to true are returned.
		
		--SQL NULLs in Arithmetic
			--Arithmetic operations applied to NULLs result in NULLS.
		
		--SQL NULLs in Aggregation
			-->>>>>>>
		
		--SQL NULLs in GROUP BY
			--NULLs are treated as equivalents in GROUP BY clauses.
	
	--Default Values: Example
		--Default values are
			--"Unknown" for name
			--"-1" for Weight and quantity
		-- -1 is used for Wgt and Qty as it is not sensible