MySQL is a Relational Database Management Systems (RDMS) and is a component on the LAMP application stack. (Linux - Apache - MySQL - PHP/Python/Perl) MySQL is a very popular Oracle backed open-sourced Structured Query Language. It is also implemented by various database-driven applications.

• MySQL is a relational database management system. Queries are written in the SQL language.
• MySQL is a database management system that manages many different database systems.
• One of the functions of a DBMS is to backup a database to a file which can be imported. When a database is imported it is recreated.
• MySQLWorkbench is a GUI for interfacing with the database.
• The Command line interface can also be used to work with a database
• MySQLWorkbench is basically a GUI for showing the commands that are run in the background.

Data refers to a series of facts or statistics. Types of data include non-digital data and digital data. There is an ever increasing amount of data: every minute from tweets, google searches, YouTube videos, LinkedIn, weather forecasts…
A database is a collection of related data organised so data can be easily accessed, managed and updated.
A relational database (such as Microsoft SQL Server, Oracle, MySql, IBM DB2) consists of a set of tables for storing data where a table is a collection of related data where a table is a collection of related data. Each table has a unique name and may relate to one or more other tables in the database through common values. There are also non-relational (NoSQL) databases such as mongoDB, casandra, neo4j and redis.

A table in a database is a collection of rows (records or tuples) and columns (fields or attributes). Tables are also known as entities or relations. A column contains data representing a specific characteristics of the records in the table.

A database schema represents the logical configuation of a database and defines how the data and relationships between data is stored. There are two types of schema:

• physical schema which defines how data is physically stored on a storage system in terms of files and indices
• logical schema defines the logical constraints that apply to the stored data, the tables in the database and the relationships between them.
• The logical schema is designed before the database is created and does not contain any data.

Database Management System (DMBS)

• A DMBS is software for creating and managing databases.
• Interacts with users, databases and other systems to store, retrieve and process data
• provides a centralised view of data that can be accessed by multiple users from multiple locations in a controlled manner
• can limit data the user sees and how that end user can view the data.
• provides many views of a single database schema for different users
• provides data independance so users and application programs don’t need to know where or how data is stored
• complete transparency over changes in data storage
• CRUD functions:
  • CREATE
  • READ
  • UPDATE
  • DELETE
• Data Storage management functions
• Security functions
• Backup and recovery functions
• Transaction management (such as debiting a customer a/c, updating the shipping table, updating the product table and crediting the store a/c.)
• Data Integrity (for example not being able to add a doctorID for a patient in the patient table where the doctorID doesn’t already exist in the doctor table)
• Concurrency

Advantages of DBMS include:

• Data integrity
• Enforcement of standards
• Backup and Recovery
• Controls redundancy - a centralised DBMS eliminates duplication. Data need only be stored once and cane be accessed by many users.

Disadvantages of DBMS include:

• Complexity
• Size
• Performance
• Impact of failure
• MySQL is a database management system
• See Database Management System Tutorial on https://www.tutorialspoint.com

SQL

• Structured Query Language
• standard Relational Database Language.
• ANSI/ISO standard but different databases may use their own proprietary extensions on top of the standard SQL.

SQL can be be used to:

• Create a new databases
• Create tables in a database
• CRUD functions:
  • Insert data into a database
  • Read data from a database
  • Update data in a database
  • Delete data from a database
• Manage transactions
• Manage concurrency
• Backup and Recovery
• Manage Users

Note that SQL is a language while MySQL is a database management system.

Working with Databases:

• SHOW DATABASES; to list all the existing databases currently managed by Mysql
• USE <database_name> to select the database <database_name> to be used.
• grant all on <database_name> to <mysql_user>@<client_host> to grant all permissions on the database ‘database_name’ to the ‘mysql_user’ and ‘host_server’.
• DROP <database_name> to drop / delete the database

Database Connections

• mysql -h host_name -u user_name -p: The ‘host_name’ refers to the name of the host where the MySQL server is running.
• QUIT or \q to disconnect from the server.

Connecting to MySQL database using Python

• pip install mysql -connector -python to enable installation of the MySQL Python connector on any operating system including Linux, Unix, MacOS and Windows.

• Can also use the pymysql module (which is the the one I used for the applied database project).

Whatever module you use you generally need to define a connection function to connect to the MySQL server. This connect() function needs specifications for 4 parameters, host, user, password and the database or (db). The function checks if the connection has been successful and if not an error message is printed. The connection to the database must be closed once finished.

Creating, showing and selecting to use a Database:

• CREATE DATABASE <database>;'

• SHOW DATABASES; to see what databases currently exist on the server shows all the databases currently managed by MySQL

• USE <database>;

Importing databases:

One function of a DBMS is to backup a database to a file. A database that was previously exported to a file can be imported. When a database in imported, it is recreated.

Working with Tables

• SHOW TABLES to show tables in the selected database

Creating Tables using SQL See section 3.3.2 Creating a Table

    CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Aside on MySQL data types:

• Data-types
• Numeric data types
• See MySQL Reference Manual Chapter 11 Data Types.

Here are some.

Numeric Data Type Syntax

• Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
• Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC
• Floating-Point Types (Approximate Value) - FLOAT, DOUBLE
• Bit-Value Type - BIT
• Numeric Type Attributes
• Out-of-Range and Overflow Handling

Date and Time Data Type Syntax

• The DATE, DATETIME, and TIMESTAMP Types
• The TIME Type
• The YEAR Type
• Automatic Initialization and Updating for TIMESTAMP and DATETIME
• Fractional Seconds in Time Values
• Conversion Between Date and Time Types
• 2-Digit Years in Dates

String Data Type Syntax

• CHAR is a fixed length character variable
• VARCHAR Types is a string of variable length
• TEXT for long form text strings
• TINYTEXT for short strings of information
• LONGTEXT for extremely long text strings.
• The BINARY and VARBINARY Types
• BLOB are for binary strings treated as numeric values used to store datafiles such as images and videos
• The ENUM Type for string objects. It’s value is selected from a list of values.
• The SET type is a string object enabling storage of zero or more values from a list of predefined values when the list is created.

The ENUM type is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.

There are also Spatial Data Types and JSON data types.

Creating Tables - CREATE TABLE <table>;

   CREATE TABLE <table> (
   <column1><datatype>
   <column2><datatype>
   <column3><datatype>
   );

Decide what tables you need and what columns should be in each table.
Specify the layout of each table

(see also ALTER TABLE statement)

Primary Keys: Uniquely identifying rows in a table.

• A primary key constraint is used to uniquely identify each row / record in a table.
• A primary key must contain unique, non-NULL values
• Only one primary key per table
• A primary key can be a single or multiple fields

Describing Tables with DESCRIBE <table>;'

Describe describes the table structure. The show columns statement provides information similar to the describe statement. See section Show Columns statement.

• Field: the column name
• Type: the column data type
• Null: yes or no
• **Key **: whether the column is indexed.
  • PRI: if the column is a primary key or one of the columns in a mult-index primary key
  • UNI: If the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values as shown in the Null field.)
  • MUL: if the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column
• Default: The default value for the column. This is NULL if the column has an explicit default of NULL, or if the column definition includes no DEFAULT clause.
• Extra:
  • often blank if no additional information about a given column
  • auto_increment for columns that have the AUTO_INCREMENT attribute.
  • on update CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns that have the ON UPDATE CURRENT_TIMESTAMP attribute

The SELECT statement for getting data from a database table:

• SELECT <field_list> FROM <table_name> to select list values from a table

• To get all attributes use SELECT * FROM <table>;

• SELECT <field_list> FROM <table_name> WHERE <condition>;

    SELECT <columns> 
    FROM <table>;

• SELECT for getting information FROM a table WHERE …

WHERE operators

• equal to: =
• not equal to: <> or !=
• greater than >,
• less than <
• greater than or equal to >=,
• less than or equal to <=,
• BETWEEN an inclusive range
• LIKE to search for a pattern
• IN result is in a set of multiple specified values

WHERE age >= 20 AND age <= 30; is same as WHERE age BETWEEN 20 and 30;

LIKE to search for a specified string-like pattern

• Match a string with a pattern on a per character basis.
• returns a 1 if there is a pattern and 0 if not.
• % represents 0 or more characters
• _ a single character

IN can be used instead of multiple ORs.

Can use IN to determine if a specified value matches any value in a set of values, or returned by a sub-query.

WHERE age in (12,13,14,15) is same as

    WHERE age = 12
    OR age = 13
    OR age = 14
    OR age =15`

SELECT <field_list> FROM <table_name> 
    WHERE <condition>;` 

SELECT <field_list> FROM <table_name> 
    WHERE <expression | column_1> in (value1, value2,...);` 

NB! Operator Precedence

• Note operator precendence and be careful when using AND and OR statements.
• Use parentheses when combining AND, OR operators.

SELECT name, age FROM person 
WHERE sex="m" AND (name LIKE "S%" OR name LIKE "A%");

The LIMIT clause to constrain number of rows returned by SELECT statement.

• Can use a single number, for example LIMIT 3 returns the first three matching results or two numbers to specify the starting point and the number of results to return.
• For example LIMIT 0,3; returns the first 3 results matching while LIMIT 3,3; returns the three results starting from the 4th result.

DISTINCT

• SELECT DISTINCT statement to return only distinct / unique values from a list

SELECT DISTINCT <field_list>
FROM <table_name>

See Distinct Optimization

ORDER BY

• ASC for ascending - the default
• DESC for descending
• YEAR() get year from date
• DAY() get day from date
• MONTH() get month from date.

SELECT <field_list> FROM <table_name> 
ORDER BY <field_name>[ASC | DESC]

MySQL FUNCTIONS AND PROCEDURES

MySQL can also manipulate data before storing or retrieving it. A function is a piece of code that performs an operation and returns a result. Some functions accept parameters while others don’t.

Why use functions?

• Functions can be used to maintain business logic ,for example a mobile app and Web sites can use the same business logic when working with the same database, or regional websites that work from the same database.

Built in Functions and Operators:

• String Functions
• Numeric Functions
• Date & Time functions
• Aggregate Functions
• MySQL Information functions
• MySQL Control flow functions

String Functions and Operators

• UPPER(): convert to uppercase

• STRCMP(): compare two strings. Returns 0 if 2 strings match, -1 if string1 < string2, returns 1 of string1 is > string 2.

• ASCII(): Return numeric value of left-most character, the first character in the string

• REPLACE(string, from_string, to_string): Replace all occurences of a specified substring(from_string) within a string with a new substring (to_string)

• SUBST(string, start, length): return the substring as specified from a string, starting at the start position within the string, the number of characters extracted being the length.

• CHAR_LENGTH() returns the number of characters in an argument

• CONCAT() return concatenated string and many more. See

Numeric functions and operators

• SQRT() returns the square root of a number
• ROUND(number, decimals) rounds a number to the number of decimals specified.
• ABS() Return the absolute value
• POWER() Return the argument raised to the specified power
• TRUNCATE() Truncate to specified number of decimal places

DATE and TIME FUNCTIONS

• MONTH() to get Month number from date
• DAY() to get Day from date
• YEAR() to get year from data
• MONTHNAME();
• DATEDIFF() : subtract two dates to get the number of days between them
• DATE_FORMAT(): format date as specified such as “%m%d%y”
• DAYNAME()
• DAYOFMONTH()
• DAYOFYEAR()
• DAYOFWEEK()
• MAKEDATE()
• MINUTE()
• NOW()

Aggregate (GROUP BY) Functions

An aggregate function performs a calculation on a set of values and returns a single value.

GROUP BY functions

The GROUP BY statement is often used with aggregate functions to group the results by one or more columns.

    SELECT student_name, AVG(test_score)
       FROM student
       GROUP BY student_name

HAVING and WHERE clauses

• The HAVING clause can be used with GROUP BY to filter groups based on certain conditions

• without including the GROUP BY clause, the HAVING clause behaves like the WHERE clause

• The HAVING clause applies a filter condition to each group of rows

• The WHERE clause applies the filter condition to each individual row

• AVG()

• MIN()

• MAX()

• SUM()

• COUNT()

Also STD, VARIANCE and more. See section 12.20.1 )

MYSQL Information Functions

• DATABASE(): return the default(current) database name
• USER(): the user name and host provided by the client

MYSQL CONTROL FLOW FUNCTIONS

• CASE
• IF()
• IFNULL()
• NULLIF()

IF

See Control Flow Functions

SELECT.. IF.. FROM..

• IF(condition, value_if_true, value_if_false) where the condition is the value to test.

CASE WHEN

SELECT ..CASE WHEN... FROM..

    CASE WHEN condition 1 THEN result 1
        WHEN condition 2 THEN result 2
        WHEN condition 3 THEN result 3
        ELSE result
    END

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

The return type of a CASE expression result is the aggregated type of all result values.

MySQL Stored routines.

• A stored routine is a user-written code that extends the functionality of MySQL.
• can be used to perform the same database operations across multiple client applications in different languages or on different platforms.
• ensures security as applications can only access stored routines and not tables

Stored routines keep business rules consistent. Stored routines ensure security by having the applications access to the routines only and not the tables. This way all transactions can be logged. Reduction of duplication.

Advantages of Stored Routines:

• Speed: performance of application accessing the database is increased as stored procedures are compiled and stored in the database
• Traffic: no need to send multiple lengthy SQL statements - the application has to send just the name and parameters of the stored routine.

Disadvantages:

• Complexity: not designed for complex business logic like other languages.
• Difficult to debug. (MySQL does not allow you debug stored procedures)
• performance (a DBMS not well-desgined for logical operations)

While they can increase the performance of an application accessing the database, moving too much from the application side to the database side can impact the database performance.

MySQL Stored Functions:

• A stored function is a special type of stored routine that returns a single value.
• used to encapsulate common formulae or business rules that are reusable among sql staements or stored routines.
• Functions take 0 or more input parameters and return a single value

See Section 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements

Example:

CREATE FUNCTION <function_name> (<num1 integer, num2 integer>)

RETURNS integer
DETERMINISTIC
BEGIN
    RETURN num1 + num2
END

• You must give the function a function name.
• Specify the parameters and their type.
• A function always has a return value so you need to specify that.
• Specify if the function is deterministic or non-deterministic.
  • Non-deterministic modify data and have update, insert or delete statements.
  • Deterministic functions do not.

The actual code for the function is written between BEGIN and END statements.

CREATE FUNCTION add2Nums (num1 integer, num2 integer)

RETURNS integer
DETERMINISTIC
BEGIN
    RETURN num1 + num2
END

Example:

CREATE FUNCTION discount(age INT(11))
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
    IF age < 16 THEN
        RETURN "0%";
    ELSEIF age < 26 THEN
        RETURN "10%";    
    ELSEIF age < 40 THEN
        RETURN "20%"; 
    ELSEIF age < 60 THEN
        RETURN "30%";   
    ELSE 
        RETURN "40%";
    END IF;
END
  

To call the function, do so using select and the function name.

SELECT name, age, discount(age) "Discount"
FROM person;

Something that is done over and over can be put into a function.

Functions can be used on relations. pass in the field from the table. SELECT name, age, discount(age) "Discount" FROM person

STORED PROCEDURES.

Another type of stored routine is a stored procedure.

• A stored procedure is similar to function as it allow reuse of code
• Both stored functions and stored procedures can hide details where the logic can be encapsulated into the function and therefore doesn’t need to be rewritten each time the same query arises.
• Both stored functions and stored procedures implement business logic as the code is updated in one place.

Stored Function vs Stored Procedure.

Functions:

• return a single value
• only SELECT statement
• cannot use stored procedures
• does not support transactions

Procedures:

• return 0 or more values
• and can manipulate the data using CRUD functions ( SELECT, INSERT, UPDATE and DELETE),
• procedures can use stored functions
• procedures supports transactions.
• Procedures are created similarly to functions using the keyword procedure.
• To call the procedure use the call keyword and pass in the required parameters.

See CREATE PROCEDURE and CREATE FUNCTION Statements

• Both functions and procedures are used to store logic that is repeatedly useD and avoids inconsistencies.
• Instead of having to write out a potentially complex query, a procedure can be called.
• The delimiter must be changed before writing a function or procedure by entering delimiter // before writing the function. // after the END statement.
• The delimiter must be changed back to a semi-colon after the function is written by entering delimiter ;
• DETERMINISTIC means the table is not changed.
• use select statement with functions even when not applying to a table.

delimiter //
CREATE PROCEDURE  make_milage(mk VARCHAR(20), ml(INT(11))
DETERMINISTIC
BEGIN
    SELECT * FROM CAR
    WHERE make LIKE mk
    AND milage < ml
    ORDER BY milage;
END
//
delimiter ;

To call the procedure:

call make_milage("Toyota", 200000);

Finding Functions and Procedures

Functions and procedures can be found in the routines table in the information schema database.

select routine_name, routine_type from information_schema.routines
    WHERE routine_name IN ("add2Nums","discount","make_milage");

To see what is in a Function or a Procedure

SHOW CREATE FUNCTION <function-name>;

To see the code of a function or a procedure use the show create function or show create procedure commands. Gives a non-user friendly view of the code.

To make changes or delete a function:

• To delete a function or procedure, use the DROP command.
• To make a change to a stored procedure or function, you should first drop it and create it again with the updated code.

DROP FUNCTION  <function-name>;

See Section 13.7.4.1 CREATE FUNCTION Syntax for User-Defined Functions:

CREATE FUNCTION

Normalisation - how to have many connected tables in a database

Normalization is the process of organizing the columns (attributes) and tables (relations) of a relational database to minimize data redundancy.

• A large table (with a large number of attributes rather than a large number of rows) is basically broken down into several smaller tables.
• A database is defined as a collection of related data organised in a way where the data can be easily accessed managed and updated.
• Normalisation takes place when the database is being set up.
• Normalisation makes it easier to update a database and makes it less prone to anomalies.
• A database that is not normalised cannot be easily managed and result in update or deletion anomalies.
• A normalised database would have more than one table with links between the tables instead of all the data stored in a single table.
• reference data from another table rather than duplicating the same data.

Normalisation is a more realistic database structure where there are many inter-related tables. A Normalised database reduces the potential for anomalies in the data.

The SHOW CREATE TABLE command will show the foreign keys that link to other tables. Information which is stored over several tables can be retrieved by joining the tables using the left or inner join commands.

Foreign Keys

When creating tables, the link between the tables is defined by the foreign key.

• A foreign key is a constraint on the table, on what can be inserted into a table.
• ensures referential integrity and keeps the database consistent
• SHOW CREATE TABLE command will show the foreign keys.
• Tables are joined using the foreign keys.

In the doctor patient example, only values that exist in the doctorID column of the doctor table can be added as a foreign key to the patient table. This is called Referential integrity and ensures that the database is consistent.

Joining data from tables using foreign keys.

• SELECT from one table, then JOIN to another table using the foreign key.
• SHOW CREATE TABLE will show any foreign keys referencing another table.
• rows are joined based on the foreign key.
• SELECT clause contains the fields that will be displayed.
• FROM: select from one table only, not both table
• JOIN to another table.
• use alias names instead of the full table name to make the query less verbose.

Note although the SELECT clause is at the start of the query, it is really the last part of the query as it just prints out what you want to have returned and the query starts at the FROM clause.

INNER JOIN

An inner join is a filter clause which matches each row in one table with every row in the other table, thus enabling to query only those rows that have corresponding columns from both tables.

• returns rows from two tables only where the JOIN condition is met

• if the JOIN condition is not met, then nothing is returned from either table.

• with an inner join, it does not matter which table you join to which

• only join tables on the foreign keys.

• to get data from two tables that are not directly joined to each other, look for the foreign keys in the database. Then you can join tables that are not directly connected.

• tables can be joined either way on the foreign key constraint but always join tables on the keys.

LEFT JOIN

• Returns rows from two tables when the JOIN condition is met
• If the JOIN condition is not met
  • rows from the left (first) table are returned and
  • NULL is returned instead of the rows from the second table

For example an inner join will join each row in the doctor table to patient table but only where the doctor id is the same as the doctor id in the doctor table.

• with a left join, the order of tables does matter as all rows from the first table will be returned and therefore if a different table is first, the results will be different.

A RIGHT JOIN is the same as a left join except the table manipulation is in reverse order. It matches each row from the second table with each row from the first table on the join condition.

SHOW CREATE TABLE

The show create table shows the same information that the describe table command shows but with some more information. It also shows the exact code used for the creation of that table.

show create table shows the foreign key constraint which is not shown in the describe command. With foreign key constraints we can only reference something if it already exists.

`show create table salaries;`

  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `emp_no` (`emp_no`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

CONSTRAINT 'salaries_ibfk_1' FOREIGN KEY ('emp_no') REFERENCES 'employees' ('emp_n')`

This foreign key constraint in the salaries table means that an employee number (emp_no) cannot be put into the salaries table if it doesn’t already exist in the employees table. This ensures the tables are consistent and preserves referential integrity. When referencing something, it must already exist.

• The data is separated into different tables to prevent deletion anomalies.

• The constraint means you cannot add a new employee to salaries table unless their emp_no exists in the employees table.

• The Show create table command shows these constraints better than the describe tables command.

• You can also use the reverse engineer functionality in MySQLWorkbench to see if tables are connected. It will show if there is a foreign key coming from a table to see if they are connected.

• A primary key can be a composite key

• Note that the foreign keys do not have to have the exact same name.

(MySQLWorkbench > Database > ReverseEngineer to see a diagram)

• It is best practice to use a short alias name for a table name when selecting data from more than one table. This is necessary when the tables have the same column name as otherwise mysql would not know which table is being referred to. Therefore always prefix the column name with the table it comes from. Define the abbreviation for the table the first time it is mentioned directly.

• When joining tables, if a table is not joined on a foreign key as it should be and the query returns something, what is returned might not make any sense so always join on the foreign key.

• The select clause goes at the start of the query to state which columns are to be returned.

• the query really only starts after the from clause.

• the alias can be used in the select clause even before the table it refers to is first mentioned.

select pt.*, dt.* from patient_table pt 
inner join doctor_table dt
on pt.doctorID = dt.doctorid; 

• If there is a field in both tables with the same name, then you need to use the prefix aliases for the tables so that mysql knows which table you are referring to.

• use left join to show every row from the first table even if there is no associated entry in the second table.

• to show all details of all doctors from one table with the details of their patients from the patients_table, use a left join as this will return all rows from the first table with a null value for rows in the second table where the condition is not met.

• an inner join would omit the rows where the condition is not met

• A table may not have a foreign key pointing out but there may be a foreign key pointing in from another table which can be used for joining tables.

• In some cases a left join will return the same results as an inner join, depending on the data in the table)

• If you only want to see rows from one table that have related data in another table use an inner join

• If you want to see everything from one table, whether or not there is related data in another table use a left join. This will return NULL value for rows from the second table where the conditions are not met.

• A query with joins can be used in a stored function or procedure.

CRUD (Create, Insert, Read, Update Delete)

INSERT

INSERT inserts new rows into an existing table. Inserting into a table requires the INSERT privilege for the table

INSERT INTO <table> VALUES (value1, value2, valueN);

If inserting a value for all fields, then the values must be supplied in the correct order of the columns in the table.

INSERT INTO <table> (column1, column2, column) 
VALUES (value1, value2, valueN);

• If you are inserting data into a table where not all fields are being supplied, then the fields and corresponding values must be supplied. Default values are given to the missing columns.

• Provide a parenthesized list of comma-separated column names following the table name. The number of columns in the source table must match the number of columns to be inserted.

• The values entered must be in the correct order.

• A primary key may be set to auto-increment.

• There may be default values for some fields.

• If there are default values for fields not being supplied with a value, this will be taken care of by MySQL.

• The DESCRIBE function will show the order of the columns in the table as well as details of all columns, including the type of dat for each field, the default values if any, whether a primary key is auto_increment, whether NULL values are allowed or not.

• However just because a default is set to NULL does not mean the missing field will be updated. The NULL column shows whether null is allowed or not. If not the NULL field for this column will be set to NO, even where the default value is NULL.

• An error (Cannot add or update a child row: a foreign key constraint fails) will result if you try to insert a row with a foreign key in a table if the value for the foreign key does not exist in the other table. (Cannot add a bus registration number into the driver table if the bus reg doesn’t exist on the bus table.)

 describe person;

For this table, a value must always be supplied for the name field as Null is set to NO.

The primary key (personID) is set to auto_increment.

UPDATE

UPDATE ... SET ...

Update mondifies rows in a table.

UPDATE <table> SET column1 = value1, columnN, valueN;

UPDATE <table> SET column1 = value1, columnN, valueN 
WHERE condition;

• UPDATE updates columns of existing rows in the named table with new values. The SET clause indicates which columns to modify and the values they should be given.
• Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value.
• Without a WHERE clause, all rows are updated.
• A WHERE clause specifies the conditions that identify which rows to update.
• The ORDER BY the order in which the rows should be updated.
• The LIMIT clause places a limit on the number of rows that can be updated.

update person
set age =30
where personID =9;

Without the WHERE clause, all rows would be updated with the new value for the age field.

Functions and operators can be applied when updating:

update person set age = age +1;

This updates the age fields in all row to the current age plus 1.

UPDATING based on IF conditions

UPDATE person SET name=concat(if(sex="M","Mr.","Ms."),name);

UPDATING based on CASE:

• a CASE allows many updates in one statement instead of several smaller queries.

Sometimes it is better to break the query into smaller queries but often better to use a single query. case allows you to do updates in one go instead of doing smaller queries.

update person
set age = 
case
    when age is null then 18
    else age +1
end;

DELETE.

• DELETE removes rows from a table.
• The DELETE statement deletes rows and returns the number of deleted rows.
• A DELETE statement can start with a WITH clause
• DELETE privilege on a table are needed to delete rows from it
• Even if all the rows are deleted, the table still exists.
• A row can be deleted if no other table is referencing the field.
• a row cannot be deleted if a field is referenced elsewhere.

DELETE FROM <table>;

DELETE FROM <table>; 
WHERE condition;

DELETE FROM <TABLE>; without a where clause would delete all rows.

delete from person where sex="M" and isStudent AND age >20; would only delete rows matching the where condition.

SHOW CREATE TABLE

The SHOW CREATE TABLE command will show if there are any restrictions on delete.

For example:

CONSTRAINT 'driver_ibfk_1' FOREIGN KEY ('busReg) REFERENCES 'bus' ('reg) ON DELETE CASCADE`

• ON DELETE CASCADE
• ON DELETE SET NULL

 CONSTRAINT `driver_ibfk_1` FOREIGN KEY (`busReg`) REFERENCES `bus` (`reg`) `
ON DELETE SET NULL

Note that the default constraint is ON DELETE RESTRICT if it doesn’t say ON DELETE CASCADE or ON DELETE SET NULL.

REFERENTIAL INTEGRITY:

• The bus table does not have a foreign key, but you cannot delete a row from this table if it is referenced in another table which links to it by a foreign key. The driver table has a foreign key that references the bus table.

• The delete command will result in an error such as Cannot delete or update a parent row: a foreign key constraint fails ... as you cannot associate a driver with a bus if the bus is not in the bus table already.

• You cannot insert a row with a busReg into the driver table if the bus does not already exist on the bus table. This will result in an error “Cannot add or update a child row: a foreign key constraint fails”

If a table’s foreign key references a column in another table, and the foreign key constraint is set to ON DELETE CASCADE, this means that if a row in the first table is deleted and the value of the field is used in the other table, then this row should also be deleted from the other table.

ON DELETE RESTRICT means that if a row in the first table is deleted and the value of the field is used in the other table, that this row should not be deleted from the table as the other table is restricting the delete.

ON DELETE SET NULL means that if a row in the table is deleted and the value of the field is used in the other table, that the foreign in the other table which was referencing the field to be deleted from the other table should be set to NULL, so now the field is no longer referenced and therefore can be deleted from the bus table.

If deleting a row from a table which isn’t referenced by any other table, the row will simply be deleted.

How are tables in a database related?

The SHOW CREATE TABlE query will show any foreign keys that exist in a table. While one table may not contain any foreign keys itself, another table may have a foreign key which points in to that table.

Can also use the Reverse Engineer EER diagram which will show any connections between tables and the direction if using MySQL Workbench.

Sub-Queries

• another way to read data from a table
• Instead of taking the result of a query and putting the actual values into another query, instead use a subquery that returns the value to the outer query.
• whenever data comes from more than one table, the tables must be joined somehow on the foreign key.
• another way to get the information from tables is using a sub-query which is a query within a query.
• subqueries can be used instead of doing an inner join in some cases.
• As brackets take precedence over other operators, a query within brackets will be done first and then the result returned from the subquery used in the outer query.
• It is better to do a query dynamically like this instead of hardcoding values into the query. Therefore the query can be used again even if the data has changed.
• The result of the inner query gets replaced as the argument in the outer query
• Sometimes a subquery is better than using an inner join
• there can be more than one query nested within another query. The innermost query is run first. The subquery gets replaced with the results of the subquery and so on out to the outer most query.
• The result of the inner query gets replaced as the argument in the outer query.

DESCRIBE <table>;

• The Type shows the type of data for each Field as well as the maximum number of characters allowed.

• The Key column shows which field(s) is the primary key

describe subject;

describe teacher;

5 rows in set (0.00 sec)

SELECT * FROM;

• select * from teacher to show all data in the teacher table
• select * from subject to show all data in the subject table

SELECT * FROM teacher;

SELECT * FROM subject;

The WHERE condition with select

select name 
from subject 
where onleavingcert =1;

gets all subjects on the leaving cert

The select * from ... where condition;

select name, experience 
from teacher 
where level ="L";

This query shows the details of all teachers qualified to teach to leaving cert.

SELECT ... FROM ... WHERE .. NOT LIKE " ";

select * from subject 
where teacher NOT LIKE "Mr.%";

Shows details of all subjects taught by teachers whose title is not “Mr.”

SELECT ... FROM ... WHERE ... AND ...;

select * from teacher
where month(dob) IN(1,2,3) 
AND level = "J";

This shows all teachers born in the specified months and that can teach to junior cert only.

which gives the same results as:

select * from teacher 
where month(dob) between 1 and 3 
AND level = "J";

SELECT DISTINCT ... FROM ...;

select distinct monthname(dob)
from teacher;

Shows all unique month names that teachers were born in

ORDER BY

SELECT ... FROM ... ORDER BY...

select * from teacher
order by experience desc, level;

Shows all details of teachers sorted by experience first, then level.

SELECT ... FROM ... WHERE... ORDER BY...;

select * from subject 
where name like "__l%" or name like"___l%" 
order by name;

Shows all details of all subjects whose 3rd or 4th letter is “l”, sorted by name.

SELECT ... FROM ... WHERE ... IN ( , , , ) ORDER BY ... ;

select * from teacher 
where experience in (10,15,20,25,30,35,40,45,50,55,60)
order by dob;

Shows the names of all teachers with the mentioned number of years experience, sorted from youngest to oldest.

select * from car;

SELECT ... FROM ... WHERE ... AND ( ...OR...);

select registration, mileage from car 
where mileage > 130000 AND (colour= "Silver" or colour ="Black");

Shows details for all cars with mileage greater than 130000 and colour is silver or black.

 select * from person;

Applying AVERAGE

SELECT ... AVG() FROM ... WHERE ... AND ...;

Apply the average to the rows selected where the conditions are met.

SELECT ROUND(AVG(AGE)) FROM PERSON WHERE SEX ="M" AND AGE <=24;

SELECT ROUND(AVG(AGE)) FROM PERSON WHERE SEX ="M" AND AGE >=24;

SELECT ... FROM ... WHERE ... LIKE (' ');

select * from person where name like ('___');

Shows details for persons with exactly 3 letters in their name.

SELECT DISTINCT ... FROM ...;

select distinct(make) from car;

SELECT ... FROM ... WHERE ... LIKE " " ORDER BY ...;

select * from car 
where registration like "%-G-%" 
order by mileage asc;

Shows all cars registered in Galway, sorted by mileage in ascending order

SELECT ... FROM ... WHERE ... NOT IN .. ORDER BY ... LIMIT;

The limit clause

select * from person 
where month(dob) not in(1,2,3) 
order by month(dob) 
limit 4;

Shows all people not born in first 3 months of year, sorted by month order. Only show the first few records return

describe employees;

describe salaries;

The primary key on the salaries table is made up of two composite keys as there could be multiple instances of the employee number and the from_date but you would only have one instance of each emp_no and from_date combination.

select * from employees
limit 5;

select emp_no, first_name, upper(last_name) as last_name from employees limit 5;

STRING FUNCTIONS: length(), substr(), concat(), format()

select * from employees 
order by length(last_name), last_name, length(first_name), first_name 
limit 10;

This sorts based on the length of last_name, Alphabetical order of last_name, length of first_name, alphabetical order of first name.

The string function char_length() returns the length of a string measured in characters where a multibyte character counts as a single character.

Show all details of the first 10 employees returned from the database and an extra column called Initials that shows the employee’s initials.

concat() and substr()

select *, concat(substr(first_name,1,1), substr(last_name,1,1)) as Initials from employees limit 5;

The concat and substr string functions are used here to display a new column with the initials of employees. An alias is used to name the new column Initials.

The concat function could be used to create an email address using the first name and last name.

 select concat(first_name, '.',last_name,"@company.com") as email 
 from employees limit 5;

The format function.

format()

select format(max(mileage),0)
from car 
group by make;

Multiple ANDs

Using multiple ANDs here but as there are no ORs being used, brackets are not required for operator precedence .

select * from employees where gender = "F" 
and year(birth_date) between 1950 and 1959 
and hire_date >= "1988-09-1" 
and hire_date <="1991-02-28";

Aggregate GROUP BY functions

GROUP BY

AVG(), MAX()

select * from person;

select round(avg(age)) from person where sex="M";

Count, group by.

SELECT ... , COUNT(*) FROM ... GROUP BY ...;

Does a count based on the group by

select monthname(dob), count(*)from person 
group by monthname(dob);

Shows the number of people born in each month

SELECT AVG() AS ... FROM ... GROUP BY ... LIMIT...;

select emp_no, round(avg(salary),2) as average_salary 
from salaries group by emp_no 
limit 5;

select emp_no, round(max(salary),2) as average_salary 
from salaries 
group by emp_no 
limit 5;

The WHERE clause

SELECT ... FROM ... WHERE ... AND ... GROUP BY...;

 select emp_no,round(avg(salary),2) from salaries 
 where emp_no in (10001, 10021, 10033, 10087) 
 and salary > 80000 
 group by emp_no;

This shows the average salaries for these 4 employees, but only including salaries > 80,000 in the average salary calculation.

The HAVING clause

SELECT ... FROM ... GROUP BY ... HAVING AVG()>...;

SELECT EMP_NO, ROUND(AVG(SALARY) )
FROM SALARIES 
GROUP BY EMP_NO
HAVING AVG(SALARY) >90000;

+——–+————-+

This query shows the average salaries for employees but only showing the average salaries which are over 90000.

select monthname(birth_date), count(*) 
from employees
 group by monthname(birth_date);

CONTROL FLOW FUNCTIONS

Name Description

• CASE : Case operator
• IF() : If/else construct
• IFNULL() : Null if/else construct
• NULLIF() : Return NULL if expr1 = expr2

The IF clause

select *,IF(salary>50000,"big","small") as salary_type 
from salaries 
order by from_date 
limit 5;

A CASE statement allows for more options than an IF statement which only allows one.

An alias can be used for the names of the column for printing as otherwise the whole case when statement will show as the column name. However the alias does not change the actual table.

SELECT *, IF(MILEAGE>500000,"30%","") Discount 
FROM car;

Note as is not required when using an alias to rename a column that is returned. It will not actually change the table.

IF

select emp_no as ID, if (gender="M","Mr.","Ms.") as Title, first_name as Name, last_name as Surname, gender 
from employees 
order by emp_no limit 5;

CASE WHEN

select emp_no, max(salary), 
case 
    when max(salary) < 40000 then "30%" 
    when max(salary) < 60000 then "40%" 
    when max(salary) < 80000 then "50%" 
    else "60%" 
END as "Tax bracket" 
from salaries 
group by emp_no 
order by max(salary) 
limit 10;

select *, 
case 
    when engineSize <=1.0 then "Small" 
    when engineSize between 1.1 and 1.3 then "Medium" 
    when engineSize > 1.3 then "Large" 
END as size 
from car;

CASE WHEN

select first_name,  birth_date, 
case 
    when month(birth_date)in(1,2,3) then "q1 baby" 
    when month(birth_date)in (4,5,6) then "q2 baby" 
    when month(birth_date) in (7,8,9) then "q3 baby" 
    else "q4 baby" 
end as birthQ 
from employees limit 5;

select name, isStudent,age, 
case  
    when isStudent=1 and age >23 then "mature student" 
    when isStudent=1 and age <=23 then "ordinary student"
    else "Not" 
    end as "student status" 
    from person limit 5;

select *, 
case 
    when age < 20 then "under20" 
    when age < 30 then "under30" 
    when age < 40 then "under40" 
    else "over40" 
    end as "age-cat" 
    from person;

(use the correct order when writing the case when.)

DATE AND TIME FUNCTIONS.

DATEDIFF()

SELECT *, if(DATEDIFF(to_DATE,from_DATE) <365, "under 1 year","over 1 year") as Time 
FROM SALARIES 
LIMIT 5;

DATE and TIME FORMAT functions

date_format(date, format)

A sample of the specifiers here:

Specifier Description

• %a : Abbreviated weekday name (Sun..Sat)
• %b : Abbreviated month name (Jan..Dec)
• %c : Month, numeric (0..12)
• %D : Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
• %d : Day of the month, numeric (00..31)
• %H : Hour (00..23)
• %h: Hour (01..12)
• %M: Month name (January..December)
• %m: Month, numeric (00..12)
• %p: AM or PM
• %S: Seconds (00..59)
• %T: Time, 24-hour (hh:mm:ss)
• %W: Weekday name (Sunday..Saturday)
• %Y: Year, numeric, four digits
• %y: Year, numeric (two digits)

The format strings should be in quotes.

select NAME, date_format(dob, "%a, %b %D %Y") FROM PERSON;

select first_name as first, gender as G 
from employees limit 3;

FUNCTIONS AND PROCEDURES

• When writing functions and procedures, the delimiter must be changed to // before writing a function or procedure. This allows the ; to be used as part of the code and not to signify the end of a statement.

• Before writing the function or procedure, write it as a query.

• A function is deterministic if it doesn’t chanhe any data.

select *, round(datediff(hire_date, birth_date)/365,1) as age 
from employees limit 5;

Using a function show all columns from the employees table, and a column entitled “Age” which is the age the employee was when he or she was hired. The age should be rounded to 1 digit after the decimal place.

DELIMITER //
CREATE FUNCTION getAges(hd date, bd date)
    -> RETURNS varchar(5)
    -> DETERMINISTIC
    -> BEGIN
    -> RETURN format(datediff(hd,bd)/365,1);
    -> END
    -> //

delimiter ;
select *, getAges(hire_date, birth_date) as Age_at_hiring
from employees
limit 5;

This procedure takes two parameters, one for a year and the other a month and returns all employees hired in specified year and month.

delimiter //
mysql> CREATE PROCEDURE hires(y integer, m integer)
    -> DETERMINISTIC
    -> BEGIN
    -> SELECT * FROM employees
    -> WHERE year(hire_date)=y AND month(hire_date)=m;
    -> END
    -> //

This procedure returns all employees hired in the specified year if the month parameter is NULL.

delimiter //
mysql> create procedure hires2(y integer, m integer)
    -> DETERMINISTIC 
    -> BEGIN
    -> IF m is NULL THEN
    -> SELECT * FROM EMPLOYEES WHERE YEAR(hire_date)=y;
    -> else
    -> select * from employees where year(hire_date) = y and month(hire_date)=m;
    -> end if;
    -> end
    -> //

mysql> delimiter ;
call hires2(1995,null);

call hires2(1995,3);

To check if a parameter is NULL IF M IS NULL THEN To check if a parameter is not NULL IF M IS NOT NULL THEN.

 select * from person;

Functions

describe person;

This function determines if a person is a student based on age and isStudent status.

delimiter //
create function st(a int(11) iss TINYINT(11))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
if iss and a< 23 then
    return "Ordinary";
elseif iss and     a >=23 then 
    return "Mature";
else 
    return "";
end if;
end
//

delimiter ;

To call the function, pass in the columns from the persons tables, which takes the values for each field and passes them as the parameters to the function.

select st(16,0);

select *, st(age, isStudent) as "Student Type" from person;

CREATE PROCEDURE

Write a procedure that accepts two parameters. A comparison operator < > = and an Age. If the comparison operator is >, the procedure returns all details of a person older than the age.

passing in a string of one or a varchar with the ‘>’, ‘<. or ‘=’ operator. make age the same data type as the age in the table

Note that the return keyword is not allowed in a procedure. It is allowed in a function. Using select. Can put the if statement in parentheses but not required.

delimiter //
create procedure comp(c varchar(1) , a int(11))
deterministic
begin   
    if (c = "<") then 
        select * from person where age < a;
    elseif (x = ">") then
        select * from person where age > a;
    else
        select * from person where age = a;
    end if;
end 
//

delimiter;

call comp("<",3);

Use drop procedure <procedure name> to delete a procedure and create it again if a change to the code is required.

drop procedure comp once the procedure is written, change the delimiter back to ;. Then the procedure can be called from memory to be used. call <procedure name>(arguments)

Normalisation

describe doctor_table;

describe patient_table;

show create table patient_table

CREATE TABLE `patient_table` (
  `ppsn` varchar(10) NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `surname` varchar(50) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `doctorID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ppsn`),
  KEY `doctorID` (`doctorID`),
  CONSTRAINT `patient_table_ibfk_1` FOREIGN KEY (`doctorID`) REFERENCES `doctor_table` (`doctorid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

show create table doctor_table;

CREATE TABLE `doctor_table` (
  `doctorID` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `phone` int(11) DEFAULT NULL,
  PRIMARY KEY (`doctorID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The doctor id in the patient table is a foreign key referring the doctorid in the doctor table. Therefore any integers we put in as doctor id in the patient table must already exist in the doctor table doctor id column.

select * from doctor_table;

select * from patient_table;

String functions.

• SUBSTR()

select substr(name,5) 
from doctor_table;

Here the substr function selects from position 5 so strips out the title to return just the name.

select substr(name,5) 
from doctor_table;

INNER JOIN

select pt.*, dt.* from patient_table pt 
inner join doctor_table dt
on pt.doctorID = dt.doctorid; 

This query shows all details of all patients with associated doctors.

SELECT ... FROM ... INNER JOIN .. ON ...=... WHERE... ORDER BY...;

SELECT dt.name, dt.phone, pt.ppsn, pt.first_name, pt.surname
FROM doctor_table dt
INNER JOIN patient_table pt
on pt.DoctorID = dt.Doctorid
where dt.name = "Dr. Jones"
order by pt.surname;

This query returns the details of patients treated by a particular doctor.

DISTINCT

Select distinct() from ... inner join ... on ... = ... ;

select distinct(dt.name)
from doctor_table dt
inner join patient_table pt
on dt.DoctorId = pt.Doctorid;

This query returns the unique names of doctors who are treating patients.

LEFT JOIN

NULL

 select pt.ppsn, pt.surname, pt.first_name, dt.name 
 from patient_table pt  
 left join doctor_table dt
  on pt.Doctorid= dt.doctorid;

This query returns null for the doctor name where a patient does not have a doctor. Where the doctorID is NULL, the patient isn’t currently being treated by a doctor

LEFT JOIN vs INNER JOIN

• INNER JOIN only returns rows where the condition is met while LEFT JOIN returns all rows from the first table with a NULL for the field where the condition is NOT met.
• INNER JOIN excludes rows where the condition is not met.
• To show all details of all doctors from one table with the details of their patients from the patients_table, use a left join as this will return all rows from the first table with a null value for rows in the second table where the condition is not met.
• an inner join would omit the rows where the condition is not met

LEFT JOIN

select pt.first_name, pt.surname, dt.name 
from patient_table pt 
left join doctor_table dt 
on pt.doctorid = dt.doctorid;

INNER JOIN

select pt.first_name, pt.surname, dt.name 
from patient_table pt 
inner  join doctor_table dt 
on pt.doctorid = dt.doctorid;

select dt.*, pt.surname 
from doctor_table as dt
left join patient_table pt
on dt.DoctorID = pt.DoctorID
order by dt.name, pt.surname;

select pt.first_name, pt.surname, dt.name from patient_table pt
inner join doctor_table dt 
on pt.doctorid = dt.doctorid 
where pt.first_name ="Alan";

LEFT JOIN

left join to return every row from the patient_table even if no doctor associated.

select pt.ppsn, pt.first_name, pt.surname, dt.name, dt.phone 
from patient_table pt 
left join doctor_table dt 
on pt.doctorid = dt.doctorid;

returns all patients from the patient_table with details of doctor they are attending if any.

SHOW CREATE TABLE;

show create table manufacturer;

manufacturer CREATE TABLE `manufacturer` (  
    `manu_code` varchar(3) NOT NULL,
    `manu_name` varchar(200) NOT NULL,
    `manu_details` varchar(400) DEFAULT NULL,
    PRIMARY KEY (`manu_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

show create table vehicles;

vehicle CREATE TABLE `vehicle` (
  `reg` varchar(20) NOT NULL,
  `manu_code` varchar(3) NOT NULL,
  `mileage` int(11) DEFAULT NULL,
  `price` decimal(8,2) NOT NULL,
  `colour` varchar(20) NOT NULL,
  `fuel` enum('petrol','diesel') DEFAULT NULL,
  PRIMARY KEY (`reg`),
  KEY `vehicle_ibfk_1` (`manu_code`),
  CONSTRAINT `vehicle_ibfk_1` FOREIGN KEY (`manu_code`) REFERENCES `manufacturer` (`manu_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1