--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