Η Γλώσσα SQL

1. Η Γλώσσα SQL Δαμιανός Χατζηαντωνίου Τμ. Διοικητικής Επιστήμης & Τεχνολογίας Οικονομικό Πανεπιστήμιο Αθηνών

2. Η γλώσσα SQL • Η “standard” γλώσσα για σχεσιακές βάσεις δεδομένων. • αρχικά Sequel στην IBM ως μέρος του System R, • τώρα SQL (Stuctured Query Language) • SQL--89, SQL--92, SQL-99 • Είναι γλώσσα δηλωτική (declarative) – ορίζουμε το ΤΙ, όχι το ΠΩΣ

3. Η γλώσσα SQL • Θέματα SQL: • Γλώσσα Ορισμού Δεδομένων (Data Definition Lang) • Γλώσσα Χειρισμού Δεδομένων (Data Manipulation Lang) • Ορισμό Όψεων (Views) • Ακεραιότητα (Integrity Constraints) • Διαδικαστική SQL - Λογικοί Δρομείς (Cursors) • Ενσωματωμένη Γλώσσα SQL (Embedded SQL) • Stored Procedures • Triggers (Εναύσματα) • οι διαφάνειες αυτού του κεφαλαίου περιέχουν στοιχεία από τις αντίστοιχες διαφάνειες της κυρίας Ευαγγελίας Πιτουρά του Πανεπιστημίου Ιωαννίνων.

4. Η γλώσσα SQL SQL αποτελείται από: DDL(Data Definition Language) - ορισμός, δημιουργία, τροποποίηση και διαγραφή σχήματος. DML (Data Manipulation Language) - ορισμός, δημιουργία, τροποποίηση, διαγραφή και επιλογή δεδομένων (γλώσσα αιτημάτων – query language). Προδιαγραφές ασφάλειας - χρήστες και δικαιώματα.

5. Γλώσσα Χειρισμού Δεδομένων • Θέματα: • Βασική δομή (select-from-where) • Συναθροιστικές συναρτήσεις (aggregation) • Εμφωλευμένα υποαιτήματα (nested subqueries)

6. Βασική Δομή ονόματα γνωρισμάτων ονόματα σχέσεων συνθήκη Βασική Δομή Μια χαρακτηριστική ερώτηση σε SQL έχει την εξής μορφή: select Α1, Α2, .., Αn from R1, R2, … Rm where P Ισοδύναμο του: π A1, A2, .., An (σ P (R1 x R2 x … Rm))

7. Βασική Δομή select Α1, Α2, .., Αn from R1, R2, … Rm where P πA1, A2, .., An(σ P (R1 x R2 x … Rm)) select αντιστοιχεί στην πράξη της προβολής (projection) της σχεσιακής άλγεβρας (προσοχή: όχι στην πράξη selection). Ποια γνωρίσματα θέλουμε να υπάρχουν στο τελικό αποτέλεσμα της ερώτησης.

8. Βασική Δομή select Α1, Α2, .., Αn from R1, R2, … Rm where P πA1, A2, .., An(σ P (R1 x R2 x … Rm)) from αντιστοιχεί στην πράξη του καρτεσιανού γινομένου της σχεσιακής άλγεβρας. Ποιες σχέσεις θα χρησιμοποιηθούν για τον υπολογισμό του αποτελέσματος.

9. Βασική Δομή select Α1, Α2, .., Αn from R1, R2, … Rm where P πA1, A2, .., An(σ P (R1 x R2 x … Rm)) whereαντιστοιχεί στη συνθήκη της πράξης της επιλογής (selection) στη σχεσιακή άλγεβρα. Το κατηγόρημα (predicate)P έχει γνωρίσματα των σχέσεων που εμφανίζονται στο from.

10. Βασική Δομή • Όταν δεν υπάρχει το where, το P θεωρείται ότι ισχύει (true). • ΠΡΟΣΟΧΗ: Δε γίνεται απαλοιφή των διπλών εμφανίσεων.Με άλλα λόγια δεν έχει συνολοθεωρητική ερμηνεία, όπως η σχεσιακή άλγεβρα.

11. Παράδειγμα 1 Ταινία ΤίτλοςΈτος Διάρκεια Είδος Παίζει ΌνομαΤίτλος Έτος Ηθοποιός Όνομα Διεύθυνση Έτος-Γέννησης Σύζυγος-Ηθοποιού

12. Παράδειγμα 2 Code Name SSN Name Works-in Sex Employee Department Salary MgrSSN Budget Assigned Hours Location Id Project Description

13. Παράδειγμα 2 Σχεσιακή σχεδίαση: Employee (SSN, Name, Salary, Sex, DeptCode) Dept (Code, Name, Budget, MgrSSN) Assigned (SSN, Id, Hours) Project (Id, Description, Location)

14. Select Select Παράδειγμα: δείξε όλα τα στοιχεία των υπαλλήλων select SSN, Name, Sex, Salary, DeptCode fromEmployee

15. Select Select Παράδειγμα: Ονόματα όλων των ηθοποιών που έχουν παίξει σε ταινίες select Όνομα from Παίζει

16. Select Select Παράδειγμα: δείξε τους μισθούς των υπαλλήλων select Salary fromEmployee • Η SQL επιτρέπει πολλαπλές εμφανίσεις της ίδιας πλειάδας σε μια σχέση (π.χ. ίδιοι μισθοί). Μια σχέση στην SQL είναι ένα πολυσύνολο (multiset) ή θύλακας (bag) => δεν υπάρχει απαλοιφή διπλοτιμών. Αυτό είναι πολύ χρήσιμο στις περισσότερες περιπτώσεις (π.χ. ?)

17. Select • Για να έχουμε απαλοιφή διπλοτιμών (duplicate elimination) θα πρέπει να το ζητήσουμε χρησιμοποιώντας το distinct. Απαλοιφή διπλών εμφανίσεων select distinct Salary fromEmployee

18. Select Επιλογή όλων των γνωρισμάτων select * from Employee select SSN, Name, Sex, Salary, DeptCode fromEmployee

19. Select Αριθμητικές πράξεις (+, -, *, /) ή εφαρμογή συναρτήσεων (π.χ. date ή string functions - αργότερα) ανάμεσα σε σταθερές ή γνωρίσματα πλειάδων select Τίτλος, Έτος, Διάρκεια / 60, Είδος from Ταινία Επιστρέφει μια σχέση ίδια με τη σχέση Ταινία μόνο που το γνώρισμα διάρκεια μας δίνει τις ώρες (έχει διαιρεθεί με το 60) select Name, Salary * 340.75 fromEmployee

20. Where Where Παράδειγμα: Τον τίτλο όλων των ταινιών που γυρίστηκαν μετά το 1995 και είναι ασπρόμαυρες select Τίτλος from Ταινία where Έτος > 1995 andΕίδος = ''Ασπρόμαυρη''

21. Where Where Παράδειγμα: Τοόνομα και το μισθό όλων των γυναικών στο τμήμα 23 selectName, Salary fromEmployee whereSex = ‘F’and DeptCode = 23

22. Where συνθήκητου where Λογικοί τελεστές: and, or, not Τελεστές σύγκρισης: <, <=, >, >=, =, <>, between, not between(ανάμεσα σε αριθμητικέςεκφράσεις,συμβολο-σειρές [strings], και ειδικούς τύπους).

23. Where Παράδειγμα χρήσης του between : select Τίτλος from Ταινία where Έτος between 1990 and 1995 αντί του select Τίτλος from Ταινία where Έτος >= 1990 and Έτος <= 1995

24. From From Παράδειγμα: Δείξε το SSN των υπαλλήλων μαζί με το SSN του διευθυντή του τμήματος στο οποίο δουλεύει selectSSN, MgrSSN fromEmployee, Dept whereDeptCode = Code ΟΜΩΣ: Αν θέλαμε και το όνομα του τμήματος στο οποίο δουλεύει?

25. From From Παράδειγμα: Δείξε το SSN των υπαλλήλων μαζί με το όνομα του τμήματος στο οποίο δουλεύει selectSSN, Name fromEmployee, Dept whereDeptCode = Code Ποιό Name ??? Από ποιόν πίνακα ???

26. Βασική Δομή • Όταν το ίδιο γνώρισμα εμφανίζεται στο σχήμα περισσότερων από μια σχέσεων, τότε διάκριση βάση του συμβολισμού: • <όνομα-σχέσης>.<όνομα-γνωρίσματος> selectSSN, Dept.Name fromEmployee, Dept whereDeptCode = Code

27. From Παράδειγμα φυσικής σύζευξης: Το‘ονομα των ηθοποιών που παίζουν σε ασπρόμαυρες ταινίες select distinct Όνομα from Παίζει, Ταινία where Παίζει.Τίτλος = Ταινία.Τίτλος andΠαίζει.Έτος = Ταινία.Έτος and Είδος = “Ασπρόμαυρη”

28. From From Παράδειγμα: Δείξε το όνομα των υπαλλήλων που δουλεύουν στο τμήμα «Marketing» selectEmployee.Name fromEmployee, Dept whereDeptCode = Code and Dept.Name = ‘Marketing’

29. From From Παράδειγμα: Δείξε το όνομα των υπαλλήλων που δουλεύουν σε κάποιο project στο ‘Μαρούσι’ selectName fromEmployee, Assigned, Project where Employee.SSN = Assigned.SSN and Project.Id=Assigned.Id and Project.Location=‘Μαρούσι’

30. From Ταινία (Τίτλος, Έτος, Διάρκεια, Είδος) Παίζει(Όνομα, Τίτλος, Έτος) Ηθοποιός(Όνομα, Διεύθυνση, Έτος-Γέννησης, Σύζυγος-Ηθοποιού) Παραδείγματα • Έγχρωμες ταινίες με διάρκεια μέχρι 80 λεπτά • Οι ηθοποιοί που γεννήθηκαν μετά το 1935 και έπαιξαν σε ασπρόμαυρες ταινίες πριν το 1945

31. Πράξεις με Συμβολοσειρές (Strings) Πράξεις με Συμβολοσειρές Η πιο συνηθισμένη πράξη είναι ταίριασμα προτύπων (pattern matching): % ταιριάζει οποιαδήποτε συμβολοσειρά _ ταιριάζει οποιοδήποτε χαρακτήρα Γίνεται διάκριση ανάμεσα σε κεφαλαία και μικρά Σύγκριση χρησιμοποιώντας το like, not like

32. Πράξεις με Συμβολοσειρές Παράδειγμα: Δείξε όλα τα IDs των projects που έχουν στην περιγραφή τους το string «Αττική οδός» select ID fromProject whereDescription like“%Αττική οδός%” Πολλές ακόμα πράξεις διαθέσιμεςγια pattern matching – κυττάξτε στο on line help για την Transact-SQL.

33. Πράξεις με Συμβολοσειρές Παράδειγμα: Οι τίτλοι όλων των ταινιών που περιέχουν τη λέξη Θάλασσα select distinctΤίτλος from Ταινία where Τίτλος like“%Θάλασσα%”

34. Διάταξη των Πλειάδων Διάταξητων Γραμμών του αποτελέσματος Χρήση του order by ώστε οι πλειάδες στο αποτέλεσμα να είναι ταξινομημένες με βάση τo αντίστοιχο γνώρισμα select Ταινία, Έτος from Παίζει where Όνομα = “Robert De Niro” order byΈτος selectName, Salary from Employee whereDeptCode = 311 order bySalary

35. Διάταξη των Πλειάδων Default: αύξουσα διάταξη, αλλά και άμεσα χρησιμοποιώντας το asc (αύξουσα) ή το desc (φθήνουσα). Επίσης, ταξινόμηση με βάση πολλά γνωρίσματα. Παράδειγμα: select * from Ταινία order by Έτος desc, Τίτλος asc Παράδειγμα: select * fromEmployee order byDeptCodeasc, Salarydesc Η ταξινόμηση είναι δαπανηρή λειτουργία.

36. Αλλαγή Ονόματος Αλλαγή Ονόματος Τα ονόματα των γνωρισμάτων στο αποτέλεσμα είναι αυτά των σχέσεων στην ερώτηση. Δυνατότητα αλλαγής του ονόματος τόσο μιας σχέσης όσο και ενός γνωρίσματος: <παλιό-όνομα> as <νέο-όνομα> To as μπορεί να εμφανίζεται στο select ή στο from (aliases)

37. Αλλαγή Ονόματος Για παράδειγμα: select Τίτλος, Έτος, Διάρκεια / 60 asΏρες-Διάρκεια, Είδος from Ταινία

38. Αλλαγή Ονόματος Χρήσιμο όταν (α) όταν έχουμε αριθμητικές εκφράσεις στο select και δεν έχουν όνομα, (β) όταν θέλουμε να αλλάξουμε το όνομα του γνωρίσματος στο αποτέλεσμα. (γ) δυο σχέσεις του from έχουν γνωρίσματα με το ίδιο όνομα, (δ) όταν έχουμε self-joins

39. Πράξεις Συνόλων Πράξεις Συνόλων • Πράξεις: • union • intersection • except • εφαρμόζονται σε συμβατές σχέσεις (same arity, domain).

40. Πράξεις Συνόλων Γενική Σύνταξη: ( select from where ) union/intersection/except ( select from where)

41. Πράξεις Συνόλων Όνομα-Υποκαταστήματος Πόλη Σύνολο Όνομα-Υποκαταστήματος Αριθμός-Λογαριασμού Ποσό Όνομα-ΠελάτηΑριθμός-Λογαριασμού Όνομα-Πελάτη Οδός Πόλη Όνομα-ΠελάτηΑριθμός-Δανείου Όνομα-Υποκαταστήματος Αριθμός-Δανείου Ποσό Λογαριασμός Υποκατάστημα Δάνειο Δανειζόμενος Πελάτης Καταθέτης

42. Πράξεις Συνόλων Παράδειγμα union: Τα ονόματα όλων των πελατών που έχουν καταθέσεις ή/και έχουν πάρει δάνειο ( select Όνομα-Πελάτη from Καταθέτης ) union ( select Όνομα-Πελάτη from Δανειζόμενος )

43. Πράξεις Συνόλων Σε αυτές τις πράξεις έχουμε απαλοιφή διπλών εμφανίσεων, εκτός αν χρησιμοποιηθεί το union all Μέγιστος αριθμός πολλαπλών εμφανίσεων;

44. Πράξεις Συνόλων Παράδειγμα intersect: Τα ονόματα όλων των πελατών που έχουν καταθέσεις και έχουν πάρει δάνειο ( select Όνομα-Πελάτη from Καταθέτης ) intersect ( select Όνομα-Πελάτη from Δανειζόμενος ) Αντίστοιχα υπάρχει το intersect all Μέγιστος αριθμός πολλαπλών εμφανίσεων;

45. Πράξεις Συνόλων Παράδειγμα except: Τα ονόματα όλων των πελατών που έχουν καταθέσεις και δεν έχουν πάρει δάνειο ( select Όνομα-Πελάτη from Καταθέτης ) except ( select Όνομα-Πελάτη from Δανειζόμενος ) Αντίστοιχα υπάρχει το exceptall Μέγιστος αριθμός πολλαπλών εμφανίσεων;

46. Πράξεις Συνόλων Παράδειγμα except: Τo SSN των υπαλλήλων που δεν είναι διευθυντές τμήματος. ( selectSSN from Employee) except ( select MgrSSN from Dept) Αντίστοιχα υπάρχει το exceptall Μέγιστος αριθμός πολλαπλών εμφανίσεων;

47. Μεταβλητές Πλειάδων (tuple variables) Μεταβλητές Πλειάδων (tuple variables) Μια μεταβλητή πλειάδας μπορεί να οριστεί στο from χρησιμοποιώντας το as: select distinct Όνομα from Παίζει asΠ, Ταινία asΤ whereΠ.Τίτλος =Τ.Τίτλος andΠ.Έτος =Τ.Έτος and Είδος = “Ασπρόμαυρη” H ιδέα είναι ότι η μεταβλητή «τρέχει» επί του πίνακα γραμμή-γραμμή

48. Μεταβλητές Πλειάδων • Οι μεταβλητές πλειάδων είναι ιδιαίτερα χρήσιμες όταν θέλουμε να συγκρίνουμε δυο πλειάδες της ίδιας σχέσης (self-join). Παράδειγμα: Τα ονόματα όλων των ταινιών που έχουν διάρκεια μεγαλύτερη τουλάχιστον από μία ταινία που γυρίστηκε το 1995 select distinct Τ.Τίτλος from Ταινία as S, Ταινία as T where T.Διάρκεια > S. Διάρκεια and S.Έτος = 1995

49. Μεταβλητές Πλειάδων • Οι μεταβλητές πλειάδων είναι ιδιαίτερα χρήσιμες όταν θέλουμε να συγκρίνουμε δυο πλειάδες της ίδιας σχέσης (self-join). Παράδειγμα: Για κάθε τμήμα, δείξε τα τμήματα που έχουν μεγαλύτερο budget από αυτό select D.Code, G.Code from DeptasD, Dept asG where D.Code < G.Code υπολογισμός?

50. Η τιμή null Η τιμή null Χρήση της λέξης κλειδί is null (isnot null) σε μια συνθήκη για να ελέγξουμε αν μια τιμή είναι null. select Αριθμός-Δανείου from Δάνειο where Ποσό is null