advertisement

Mysql rab2-student

57 %
43 %
advertisement
Information about Mysql rab2-student
Technology

Published on March 8, 2014

Author: santoshmishra25

Source: slideshare.net

advertisement

Web Applications Development Lecture 1 My SQL Robin Boswell

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

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

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…

Getting started: connecting to the server mysql –u 0123435 –h scomp-wasp –D012345 -p UserId Hostname Database

Initial commands • show database; # list available databases • use databasename; – E.g. use 0123456; • show tables; • exit

SQL • Data definition language – Creating tables • Data manipulation language – Reading, writing and updating tables

Data Definition commands

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

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

Data Types Another String type: Enum CREATE TABLE Driver ( Title ENUM (‘Mr’, ‘Mrs’, ‘Ms’), DriverID CHAR(6), Name VARCHAR(20), Points INT );

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’ );

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)

Constraints Table definitions can include Constraints Constraints implement Data Integrity Recall: • Data Integrity ensures data is correct

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

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.

Foreign Keys Staff StaffCode S001 S002 S003 StaffName Taylor Gibbon Russell Department DCode D001 D002 D003 D004 DName Classics Philosophy History Economics Dept* D003 D003 D002

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 it’s 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

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.

Integrity constraints in MySQL MySQL 5.0 implements primary key integrity and referential integrity on foreign keys. MySQL 5.0 doesn’t 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

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?

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

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

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

Data Manipulation commands • • • • SELECT INSERT APPEND DELETE

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

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

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

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

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

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’);

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

Update UPDATE TABLE SET … WHERE… Example -- From now on , all male drivers will be -- called Boggis UPDATE Driver SET Name = ‘Boggis’ WHERE Title = ‘Mr’;

Delete DELETE FROM TABLE WHERE… Example -- Delete all male drivers DELETE FROM Driver WHERE Title = ‘Mr’;

Selecting from multiple tables

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

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

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

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

Other features of the Select command Ordering and Grouping

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

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

Aggregate Operators • COUNT • SUM • AVG counts records adds values calculates average value

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

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

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

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

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

Mysql rab2-student - Documents - docslide.us

1. Web Applications Development Lecture 1 My SQLRobin Boswell 2. Contents • What is MySQL?– Comparison with Access• Running MySQL in the…
Read more