Mysql rab2-student
Download as PPT, PDF0 likes341 views
This document provides an overview and introduction to MySQL. It begins with a comparison of MySQL to Microsoft Access, noting MySQL's open source nature, cross-platform availability, and emphasis on fast query processing. Basic commands for connecting to a MySQL server and exploring database and table structures are presented. The document then covers SQL data definition and manipulation languages, including creating and modifying tables, inserting and selecting data. Joins between tables and use of aggregate functions are also summarized. Overall, the document provides a high-level tour of MySQL's basic features and capabilities.
More Related Content
What's hot (17)
Similar to Mysql rab2-student (20)
Mysql rab2-student
- 1. Web Applications Development Lecture 1 My SQL Robin Boswell
- 2. Contents What is MySQL? Comparison with Access Running MySQL in the labs SQL Data Definition language Creating tables Data types Data manipulation language Select, Insert, This is mostly revision of CM2020
- 3. MySQL A popular OpenSource SQL Database management system > 10 million installations Developed and supported by MySQL AB www.mysql.com Emphasis on fast query processing Early versions lacked essential features Views, procedural code, support for relational integrity These are all present in version 5.0 onwards
- 4. Comparison with Access MySQL Access GUI: QBE, Simple table creation, drag & drop forms, Windows only Command line interface Non-standard SQL ANSI SQL Not particularly fast Fast Available on Windows, Linux, Macintosh
- 5. Getting started: connecting to the server mysql u 0123435 h scomp-wasp D012345 -p UserId Hostname Database
- 7. Initial commands show database; # list available databases use databasename; E.g. use 0123456; show tables; exit
- 8. SQL Data definition language Creating tables Data manipulation language Reading, writing and updating tables
- 10. Creating Tables CREATE TABLE name owner species sex birth death pet ( VARCHAR(20), VARCHAR(20), VARCHAR(20), CHAR(1), DATE, DATE);
- 11. Data Types See chapter 10 of the manual for more details on data types Strings CHAR(N), VARCHAR(N) CHARs are padded to length N VARCHARs are variable length N BLOB Large binary files, e.g. images TEXT(N) TINYTEXT TEXT MEDIUMTEXT LONGTEXT Long text strings, e.g. text typed by user into box
- 12. Data Types Another String type: Enum CREATE TABLE Driver ( Title ENUM (Mr, Mrs, Ms), DriverID CHAR(6), Name VARCHAR(20), Points INT );
- 13. Data Types DATE, DATETIME CREATE TABLE Driver ( Title ENUM (Mr, Mrs, Ms), DriverID CHAR(6), DateOfBirth DATE, -- YYYY-MM-DD, e.g. 1959-07-04 Name VARCHAR(20), ); CREATE TABLE Order ( OrderID CHAR(8), ProductID CHAR(8) Number INT Date DATETIME -- YYYY-MM-DD HH-MM-SS -- e.g. 2007-09-30 09-30-15 );
- 14. Data Types Numeric INT Integers FLOAT, DOUBLE Floating point numbers N.B. These are approximate values DECIMAL(P, S) # Precision, Scale Exact values Example: Suppose cost 5000, e.g., cost = 3289.75 Appropriate data-type for cost is: cost DECIMAL(6, 2)
- 15. Constraints Table definitions can include Constraints Constraints implement Data Integrity Recall: Data Integrity ensures data is correct
- 16. Column Constraints Primary keys can be implemented as column constraints CREATE TABLE Driver ( Title ENUM (Mr, Mrs, Ms), DriverID CHAR(6) PRIMARY KEY, Name VARCHAR(20) ); MySQL implements primary key integrity
- 17. Table constraints CREATE TABLE Driver ( Title ENUM (Mr, Mrs, Ms), DriverID CHAR(6), CONSTRAINT pkdriv PRIMARY KEY DriverID); CREATE TABLE Grades ( StudentID CHAR(10), ModuleID CHAR(7), Grade CHAR(1), CONSTRAINT pkgrade PRIMARY KEY (StudentID, ModuleID) ); A composite primary key must be declared as a table constraint, not as part of a column definition.
- 19. Declaring Foreign Keys CREATE TABLE Department ( Deptcode CHAR(4), Deptname VARCHAR(20), CONSTRAINT dep_con1 PRIMARY KEY (Deptcode) ); CREATE TABLE Staff ( Staffcode StaffName Dept ); Optional, if its the primary key CHAR(4), VARCHAR(20), CHAR(4) REFERENCES Department(Deptcode) CREATE TABLE Staff2 (-- An alternative way of declaring a FK Staffcode CHAR(4), StaffName VARCHAR(20), Dept CHAR(4), FOREIGN KEY (Dept) REFERENCES Department ); Can be multiple valued, to match composite primary key
- 20. Referential Integrity Staff StaffCode S001 S002 S003 S004 StaffName Taylor Gibbon Russell Penman Dept* D003 D003 D002 D005 Department DCode D001 D002 D003 D004 DName Classics Philosophy History Economics Referential integrity means that for every value of the foreign key there must be a matching value in the table it links to.
- 21. Integrity constraints in MySQL MySQL 5.0 implements primary key integrity and referential integrity on foreign keys. MySQL 5.0 doesnt implement any other forms of integrity checking CREATE TABLE Driver ( Title DriverID Name Points ); ENUM (Mr, Mrs, Ms), CHAR(6) PRIMARY KEY, VARCHAR(20), INT check (Points < 8) This will be ignored
- 22. Properties of FK links Staff StaffCode S1 S2 S3 StaffName Fred Bill Jim Dept* D1 D1 D2 Department DCode D1 D2 D3 DName Art Computing Business What happens to the Staff table if the Art department is closed, or changes its DCode?
- 23. Link Properties: On Delete, On Update Staff SID S1 S2 Name DID* D1 Fred D1 D2 NULL Bill Link properties S3 On Jim delete: Cascade On delete: Set Null On delete: Set Default Dept DID D1 Name Art D2 Computing
- 24. Link Properties: On Delete, On Update Staff SID S1 S2 Name DID* D1 D42 Fred D42 D1 D2 NULL Bill Link properties S3 Jim On update: Cascade On update: Set Null On delete: Set Default Dept DID Name D1 Art D42 D0x79fc D2 Computing
- 25. Setting link properties in SQL CREATE TABLE Department ( Deptcode CHAR(4), Deptname VARCHAR(20), CONSTRAINT dep_con1 PRIMARY KEY (Deptcode) ); CREATE TABLE Staff ( Staffcode StaffName Dept ); CHAR(4), VARCHAR(20), CHAR(4) FOREIGN KEY (Dept) REFERENCES Department ON DELETE SET NULL ON UPDATE SET NULL
- 27. The Select Command Marks Name Absolom Bloggs Carver Donald SELECT <field list> FROM <table list> WHERE <condition>; RDB Java 45 50 55 46 80 67 56 50 Pick columns Pick tables Pick rows Business Intranet 87 90 91 89 60 62 63 67 SELECT * FROM Marks WHERE Name = Bloggs OR Name = Donald Bloggs Donald 50 46 67 50 90 89 62 67 27
- 28. The Select Command Marks Name Absolom Bloggs Carver Donald SELECT <field list> FROM <table list> WHERE <condition>; RDB Java 45 50 55 46 80 67 56 50 Pick columns Pick tables Pick rows Business Intranet 87 90 91 89 60 62 63 67 SELECT Name, Java FROM Marks; Absolom Bloggs Carver Donald 80 67 56 50 28
- 29. The Select Command SELECT <field list> FROM <table list> WHERE <condition>; Marks Name Absolom Bloggs Carver Donald RDB Java 45 50 55 46 80 67 56 50 Pick columns Pick tables Pick rows Business Intranet 87 90 91 89 60 62 63 67 SELECT Name, Java FROM Marks WHERE Name = Carver; Carver 56 29
- 30. Regular Expressions Pattern Matching Use the operators LIKE or REGEXP in the WHERE field of SELECT LIKE is standard SQL (See manual section 3.3.4.7) REGEXP is an extra feature provided by My SQL (11.4.1) LIKE % represents any number of characters _ represents exactly one character SELECT * FROM Driver WHERE PostCode like AB% selects drivers whose post-code starts AB
- 31. Inserting, Modifying and Deleting Data Insert Load a record at a time Load Import a table from a file Update Change the value of a field Delete - Delete one or more records
- 32. INSERT CREATE TABLE Driver ( Title ENUM (Mr, Mrs, Ms), DriverID CHAR(6) PRIMARY KEY, Name VARCHAR(20) ); INSERT INTO DRIVER VALUES -- A complete row (Mr, D00123, Smith); INSERT INTO DRIVER (DriverID, Name) VALUES -- Specified values (D00124, Jones);
- 33. LOAD Load is similar to the import table feature in Access See section 12.2.5 for more details CREATE TABLE Driver ( DriverID CHAR(6) PRIMARY KEY, Title ENUM (Mr, Mrs, Ms), Name VARCHAR(20) ); File stored on client LOAD DATA LOCAL INFILE driver.txt -- Tab-separated fields INTO TABLE Driver ; D00001 D00002 D00003 D00004 Mr Mrs Mr Ms Boggis Boggis Ernie Tracy
- 34. Update UPDATE TABLE SET WHERE Example -- From now on , all male drivers will be -- called Boggis UPDATE Driver SET Name = Boggis WHERE Title = Mr;
- 35. Delete DELETE FROM TABLE WHERE Example -- Delete all male drivers DELETE FROM Driver WHERE Title = Mr;
- 36. Selecting from multiple tables
- 37. Cartesian Product in SQL Main Pudding MC1 Roast Beef MC2 Roast Lamb MC3 Chicken Tikka SELECT * FROM Main, Pudding; MC1 MC1 MC1 MC2 MC3 MC3 P1 Ice Cream P2 Apple Crumble Roast Beef Roast Beef Roast Lamb Roast Lamb Chicken Tikka Chicken Tikka P1 P2 P1 P2 P1 P2 Ice Cream Apple Crumble Ice Cream Apple Crumble Ice Cream Apple Crumble 38
- 38. Inner Join in My SQL Staff ID s_name Prj#* S1 Jones P1 S2 Carey P1 S3 Fuller P2 S4 Mack P2 Proj ID P1 P2 P3 p_name Aramis Athena Oracle These two statements are equivalent SELECT * FROM Staff, Proj WHERE Staff.Prj# = Proj.ID; SELECT * FROM Staff INNER JOIN Proj ON Staff.Prj# = Proj.ID; Staff.ID s_name Staff.Prj# Proj.ID S1 Jones P1 P1 S2 Carey P1 P1 S3 Fuller P2 P2 S4 Mack P2 P2 p_name Aramis Aramis Athena Athena 39
- 39. Natural Join in SQL Staff ID s_name Prj#* S1 Jones P1 S2 Carey P1 S3 Fuller P2 S4 Mack P2 Proj ID P1 P2 P3 p_name Aramis Athena Oracle SELECT Staff.ID, s_name, Staff.Prj#, p_name FROM Staff, Proj WHERE Staff.Prj# = Proj.ID Staff.ID s_name S1 Jones S2 Carey S3 Fuller S4 Mack Staff.Prj# P1 P1 P2 P2 p_name Aramis Aramis Athena Athena 40
- 40. Outer Joins Proj Staff ID s_name Prj#* S1 Jones P1 S2 Carey P1 S3 Fuller P2 S4 Mack P2 ID P1 P2 P3 p_name Aramis Athena Oracle Inner and Natural joins return only those rows which match on a given field Outer Join also returns rows from one table which have no match in the other table; SELECT * FROM Staff RIGHT JOIN Proj ON Staff.Prj# = Proj.ID; Staff.ID s_name Staff.Prj# Proj.ID S1 Jones P1 P1 S2 Carey P1 P1 S3 Fuller P2 P2 S4 Mack P2 P2 NULL NULL NULL P3 p_name Aramis Aramis Athena Athena 41 Oracle
- 41. Other features of the Select command Ordering and Grouping
- 42. Ordering Loan table: Loan# L0002 L0003 L0004 L0006 L0008 L0009 catno B0001 B0002 B0003 B0004 B0000 B0005 Memno M0001 M0001 M0001 M0002 M0002 M0003 SELECT Loan#, catno FROM Loan ORDER BY catno; LoanDate 05/10/97 05/12/97 05/12/97 13/12/97 16/01/98 18/08/99 DueDate 04/12/97 05/03/98 05/03/98 13/03/98 16/04/98 18/11/99 Loan# L0008 L0002 L0003 L0004 L0006 L0009 Fine 贈62.10 贈53.00 贈53.00 贈52.20 贈48.80 贈75.00 Catno B0000 B0001 B0002 B0003 B0004 B0005 43
- 43. Ordering on > 1 field Loan table: Loan# L0002 L0003 L0004 L0006 L0008 L0009 catno B0001 B0002 B0003 B0004 B0000 B0005 Memno M0001 M0001 M0001 M0002 M0002 M0003 LoanDate 05/10/97 05/12/97 05/12/97 13/12/97 16/01/98 18/08/99 SELECT Memno, Fine FROM Loan ORDER BY Memno, Fine; SELECT Memno, Fine FROM Loan ORDER BY Memno, Fine DESC; DueDate 04/12/97 05/03/98 05/03/98 13/03/98 16/04/98 18/11/99 Fine 贈62.10 贈53.00 贈53.00 贈52.20 贈48.80 贈75.00 Memno Fine M0001 贈53.00 M0001 贈53.00 M0001 贈62.10 M0002 贈48.80 M0002 贈52.20 M0003 贈75.00 Memno Fine M0001 贈62.10 M0001 贈53.00 M0001 贈53.00 M0002 贈52.20 M0002 贈48.80 M0003 贈75.00 44
- 44. Aggregate Operators COUNT SUM AVG counts records adds values calculates average value
- 45. Grouping Loan# Book# L0002 B0001 Memno M0001 L0003 B0002 M0001 L0004 B0003 L0006 B0004 L0008 B0000 M0001 M0002 M0002 How many loans does each member have? SELECT memno, COUNT(*) AS num_loans FROM Loan; Memno num_loans M0001 5 M0001 5 M0001 5 M0002 5 M0002 5 46
- 46. Grouping Loan# Book# L0002 B0001 Memno M0001 L0003 B0002 M0001 L0004 B0003 L0006 B0004 L0008 B0000 M0001 M0002 M0002 How many loans does each member have? SELECT memno, COUNT(*) AS num_loans FROM BY memno; GROUP Loan Memno num_loans M0001 3 M0002 2 One entry in results table for each different value of memno 47 Aggregates are evaluated separately for each group
- 47. More Grouping memno M0001 M0001 M0002 M0003 catno B0002 B0003 B0004 B0005 fine 贈53.00 贈53.00 贈52.20 贈75.00 What is the total fine paid by each member? SELECT memno, SUM(fine) AS total_fine FROM Loan GROUP BY memno ; memno M0001 M0002 M0003 total_fine 贈106.00 贈52.20 贈75.00
- 48. SQL Summary Data definition language Creating tables Setting data types Defining constraints Data manipulation language Reading, writing and updating records in tables For more details, see the MySQL manual