Introduction to MySQL & SQL

1. Introduction to MySQL & SQL

2. MySQL • Relational Database Management System • Competitors • Oracle • IBM DB2 • MS SQL-Server • These offer more facilities (better SQL-98 compliance) but MySQL is generally fastest

3. Reasons to use MySQL • Price – Free or low cost • Stability and speed • Ease of use • Features

4. Choice of Database Engines (Table types - 1) • Default - MyIsam • Tables up to 4GB – also supports merge tables • Max 64 keys per table • Full text searching • Very Fast • Not transaction safe • InnoDB – New in MySQL 4 • No maximum size (tables can span multiple files) • Transaction Safe • Row-level locking • Foreign key integrity support

5. Choice of Database Engines (Table types - 2) • Heap • Loaded wholly into memory • Extremely fast • Lost if there is a power failure! • Others • ISAM – deprecated – replaced by MyIsam • BDB – also transaction safe

6. Using MySQL

7. SQL • Structured Query Language • Designed for non-expert users • Reads like English – 1st version called Structured English Query Language = reason some people say “sequel” • Create, Drop, Show, Use, Describe, Alter, Insert, Delete, Update, Select

8. Using MySQL • MySQL databases can be accessed with a range of tools. • MYSQL utility programs • Web servers using technologies such as PHP, ASP or JSP • Programs (using SQL libraries) • Programs like MS-Access and Excel (Via ODBC)

9. MySQL Tools • Mysql - best learning tool as code can be reused in PHP and elsewhere • MySQLCC – Windows/Mac/Linux native client • PHPmySQL – web based client – included with XAMPP • Many other 3rd party tools

10. MySQL monitor • MySQL Monitor Mysql – h <server> -u <user> -p <password> -h Servername if not local device -u username – ‘root’ set up as default -p password – default is no password Mysql –u root –p Each user can have different permissions

11. Database tools • Create <database>; • Drop <database>; • Show databases; • Use <database>; • Describe <database>;

12. Creating Tables - 1 • Create table <tablename> (table definition) [Primary Key (<col>) Index index_name (<col>)[type = table_type]; Create table book ( title varchar(80), author varchar(40) );

13. Common MySQL String Data types • CHAR(length) – fixed-length character data, n characters long - Maximum length = 255 bytes • VARCHAR(maxlength) – variable length character data, maximum 255 bytes • LONGTEXT up to 4Gb. • Others are available • Tables made without the use of VARCHAR are faster but use more disk space

14. Common MySQL Numeric types • DECIMAL – general purpose numeric data type • INTEGER types – INT +/ 4 Billion /TINYINT – 1 byte, SMALLINT – 2 bytes… • FLOAT/DOUBLE – floating point • All numeric types may be SIGNED or UNSIGNED

15. Others • ENUM – Enumerated type Fruit enum (‘Apple’ , ’Orange’ , ’Banana’) • DATE – fixed-length date/time in YYYY-MM-DD form • DATETIME • TIME

16. Column Attributes • Auto_increment • Default Value • Not Null • Primary Key • Unsigned • Zerofill

17. More Create Table Create table users ( Userid int not null auto_increment primary key, FirstName varchar(30) not null, JobTitle varchar 20, Sex enum(‘Male’, ‘Female’), Age tinyint unsigned ) Type = InnoDB ;

18. Changing and Removing Tables • ALTER TABLE statement allows you to change column specifications: • ALTER TABLE BOOK ADDCOLUMN (ISBN VARCHAR(15)) [Before/After<col>/First/Last]; • Also rename column CHANGE <oldcol><newcol> • Drop columns - DROP COLUMN <col>, • Rename table - RENAME <new Name>; • DROP TABLE statement allows you to delete tables and all their data: • DROP TABLE CUSTOMERS;

19. Info commands • Show tables; • Describe <tablename>; • Show index from <tablename>; • Show table status; • Show Create table;

20. Insert Statement • Adds data to a table • Inserting into a table • INSERT INTO CUSTOMER VALUES (001, ‘RACC’, ‘Parkshot.’, ‘Richmond’, ‘UK’, ‘TW9 4AA’); • Inserting a record that has some null attributes requires identifying the fields that get data • INSERT INTO PRODUCT (PRODUCT_ID, PRODUCTDESC, PRODUCTCOLOUR, PRICE, NO_IN_STOCK) VALUES (1002, ‘Table’, ‘Brown’, 175, 8);

21. Delete Statement • Removes rows from a table • Delete certain rows • DELETE FROM CUSTOMER WHERE COUNTRY = ‘France’; • Delete all rows • DELETE FROM CUSTOMER;

22. Update Statement • Modifies data in existing rows UPDATE PRODUCT SET PRICE = 775 WHERE PRODUCT_ID = 7;

23. SELECT Statement • Used for queries on single or multiple tables • Clauses of the SELECT statement: • SELECT • List the columns (and expressions) that should be returned from the query • FROM • Indicate the table(s) or view(s) from which data will be obtained • WHERE • Indicate the conditions under which a row will be included in the result • GROUP BY • Indicate categorization of results • HAVING • Indicate the conditions under which a category (group) will be included • ORDER BY • Sorts the result according to specified criteria

24. SELECT Examples • Select all fields from all records Select * from Customer; • Find products with a price of less than £275 SELECT PRODUCT_NAME, PRICE FROM PRODUCT WHERE PRICE < 275;

25. SELECT Example Using a Function • Using the COUNT function to find totals SELECT COUNT(*) FROM ORDER_LINE WHERE ORDER_ID = 1004;

26. SELECT Example – Boolean Operators • AND, OR, and NOT Operators for customizing conditions in WHERE clause SELECT PRODUCT_DESCRIPTION, PRODUCT_COLOUR, PRICE FROM PRODUCT WHERE (PRODUCT_DESCRIPTION LIKE ‘%Desk’ OR PRODUCT_DESCRIPTION LIKE ‘%Table’) AND PRICE > 300; Note: the LIKE operator allows you to compare strings using wildcards. For example, the % wildcard in ‘%Desk’ indicates that all strings that have any number of characters preceding the word “Desk” will be allowed

27. SELECT Example – Sorting Results with the ORDER BY Clause • Sort the results first by COUNTRY, and within a country by CUSTOMER_NAME SELECT CUSTOMER_NAME, CITY, COUNTRY FROM CUSTOMER WHERE Country IN (‘UK’, ‘FR’, ‘DE’, ‘NL’) ORDER BY COUNTRY, CUSTOMER_NAME;

28. SELECT Example – Categorizing Results Using the GROUP BY Clause SELECT COUNTRY, COUNT(COUNTRY) FROM CUSTOMER GROUP BY COUNTRY;

29. Advanced SQL

30. Objectives • Definition of terms • Write multiple table SQL queries • Define and use two types of joins • Establish referential integrity in SQL

31. Processing Multiple Tables – Basic Joins • Join – An operation that causes two or more tables with a common field to be combined into a single table or view • Computer science makes this very complicated • If fact it’s quite easy Select employee.name, department.name from employee, department Where employee.departmentID = department.departmentID; • This is called an Equijoin or Inner join – the joining condition is based on equality between values in the common columns and can also be written as: Select employee.name, department.name from employee join/or Inner Join/ or Cross join department Where employee.departmentID = department.departmentID;

32. Left and Right joins • What if we want to join two tables and the matching data is missing from one? Select employee.name From employee, assignment On employee.employeeID = Assignment.EmployeeID; Will only show records where there is a match Select employee.name From employee left join assignment On employee.employeeID = Assignment.EmployeeID; Will show all records in ‘Left’ Table and their matches (if any) Right joins are simply left joins with the tables listed in the other order! These are also called left and right Outer joins

33. The following slides create tables for this enterprise data model

34. These tables are used in queries that follow

35. Join involves multiple tables in FROM clause WHERE clause performs the equality check for common columns of the two tables Inner Join Example • For each customer who placed an order, what is the customer’s name and order number? SELECT CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM CUSTOMER, ORDER WHERE CUSTOMER.CUSTOMER_ID = ORDER.CUSTOMER_ID;

36. Results

37. LEFT OUTER JOIN syntax with ON keyword instead of WHERE  causes customer data to appear even if there is no corresponding order data Outer Join Example • List the customer name, ID number, and order number for all customers. Include customer information even for customers that have not placed an order SELECT CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM CUSTOMER, LEFT JOIN ORDER ON CUSTOMER.CUSTOMER_ID = ORDER.CUSTOMER_ID;

38. Four tables involved in this join Each pair of tables requires an equality-check condition in the WHERE clause, matching primary keys against foreign keys Multiple Table Join Example • Assemble all information necessary to create an invoice for order number 1006 SELECT CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_ADDRESS, CITY, STATE, POSTAL_CODE, ORDER.ORDER_ID, ORDER_DATE, QUANTITY, PRODUCT_NAME, UNIT_PRICE, (QUANTITY * UNIT_PRICE) FROM CUSTOMER, ORDER, ORDER_LINE, PRODUCT WHERE CUSTOMER.CUSTOMER_ID = ORDER_LINE.CUSTOMER_ID AND ORDER.ORDER_ID = ORDER_LINE.ORDER_ID AND ORDER_LINE.PRODUCT_ID = PRODUCT_PRODUCT_ID AND ORDER.ORDER_ID = 1006;

39. From CUSTOMER_T table From PRODUCT_T table From ORDER_T table Figure 8-2 – Results from a four-table join

40. Transaction Integrity • Transaction = A discrete unit of work that must be completely processed or not processed at all • May involve multiple updates • If any update fails, then all other updates must be cancelled • SQL commands for transactions • BEGIN TRANSACTION/END TRANSACTION • Marks boundaries of a transaction • COMMIT • Makes all updates permanent • ROLLBACK • Cancels updates since the last COMMIT

41. Figure 8-5: An SQL Transaction sequence (in pseudocode)

42. Embedded and Dynamic SQL • Embedded SQL • Including hard-coded SQL statements in a program written in another language such as C or Java • Dynamic SQL • Ability for an application program to generate SQL code on the fly, as the application is running