Transcript MySQL and SQL - Paolo Coletti

Northwind2003 database
11 Aug 2014
1
Sakila database
11 Aug 2014
2
MySQL server

Relational database management program






Administer relational database
Update database information
Extract information through queries
Server-client architecture
User authentication
Competitors: Access, Oracle, PostgreSQL
11 Aug 2014
3
MySQL client

Personal program to access MySQL


HeidiSQL

11 Aug 2014
Command line interface
Preconfigured portable
version from
course website
4
Starting HeidiSQL
1.
2.
3.
Uncompress it on your Desktop
Connect to alcor.inf.unibz.it
Type your unibz username and password

To use it from outside unibz LAN, you need before to
log in to VPN here https://vpn.scientificnet.org .

Manually configuring HeidiSQL (not needed)
Installing MySQL server on your computer (useful but not
necessary)

11 Aug 2014
5
Using HeidiSQL
Database structure in the left window
 Commands executed and errors in the log window
below
 Queries can be written in the Query tab
and executed pressing F9 or
 Highlighting some commands and pressing F1
activates the SQL help

11 Aug 2014
6
Using HeidiSQL


USE {database}; or click in the left window
You must have appropriate privileges:






GRANT
GRANT
GRANT
GRANT
GRANT
SELECT
INSERT
UPDATE
DELETE
ALTER
No way to undo your changes!!!

ROLLBACK; command does not work on most MySQL tables
11 Aug 2014
7
Selection query



A question which produces a temporary table
SELECT fields FROM table;
SELECT fields FROM table WHERE condition ORDER
BY field ASC|DESC;




Mathematical operations
AND, OR, NOT and parentheses
Virtual field: expression AS name
Views


CREATE VIEW name AS selection query;
DROP VIEW name;
11 Aug 2014
8
Selection query











mathematical operators + - * / and comparisons = < > <= >= <>
ROUND( ), ABS( ), EXP( ), SQRT( ), LOG( )
condition1 AND condition2, condition1 OR condition2, NOT condition
field BETWEEN value1 AND value2
IS NULL, IS NOT NULL
field IN (list)
field LIKE expression containing % or _
CURDATE(), DATE_ADD(date, INTERVAL number DAY|MONTH|YEAR)
YEAR(date), MONTH(date), DAY(date)
DATEDIFF(date2,date1)
To get difference in years:

Approximate: ROUND(DATEDIFF(date2,date1)/365.25)

Exact: YEAR(date2) - YEAR(date1) - ( DATE_FORMAT( date2, '%m%d' ) <
DATE_FORMAT( date1, '%m%d' ) )
11 Aug 2014
9
Joins

Inner join


SELECT fields FROM table1 INNER JOIN table2 ON field1 =
field2;
 Names containing spaces must be enclosed by grave
accent `
 Values enclosed instead by apostrophe ’
 Ambiguous field names use table.field
table AS shortname




from now on you must use nickname
Difference between WHERE and ON
Cross join
Multiple inner joins
11 Aug 2014
10
Summary query

aggregates the records based on a GROUP BY
instruction and returns one record per distinct value
of GROUP BY fields


If no GROUP BY is inserted, it aggregates everything
SELECT function(field), grouping fields FROM tables
with inner joins GROUP BY field;
 function is an aggregating function:


11 Aug 2014
Sum(field), Avg(field), Max(field), Min(field)
Count(*), Count(DISTINCT field)
11
Summary query

Conditions




WHERE condition filters (and is written) before
aggregation and thus must be used on fields
which disappears after aggregation
HAVING condition filters (and is written) after
aggregation and thus must be used on
aggregating functions
fields which exist before and after aggregation, such as
grouping fields, can be used in both conditions equivalently
Do not select extra fields in summary queries!
11 Aug 2014
12
Modifying data

Using HeidiSQL graphical interface

TRUNCATE table;
DELETE FROM table WHERE condition;
UPDATE table SET field = value WHERE condition;
INSERT INTO table (fields) VALUES (values), …, (values);

Exporting text data






Fields’ delimiter
Values’ encloser
Importing text data

11 Aug 2014
Prepare structure before
13
Field types








INT -2 billions to 2 billions
TINYINT 0 or 1
DECIMAL(total number of digits, number of decimal digits)
FLOAT non-exact real number
CHAR (number of characters) fixed length text
VARCHAR(maximum number of characters) variable length text
TEXT very long text up to 60,000 characters
ENUM( value , … , value )



DATE time is supposed to be midnight, DATETIME
Options



Pay attention to numbers
NOT NULL, AUTO_INCREMENT, UNIQUE, DEFAULT default value, INDEX
Primary key automatically has UNIQUE and NOT NULL and is an index
Numerical codes are not numbers!
11 Aug 2014
14
Table

CREATE TABLE table (
field field-type options, …,
PRIMARY KEY (field),
INDEX (field),
CHECK (condition)
);

DROP TABLE table;
ALTER TABLE table ADD field field-type options;
ALTER TABLE table DROP field;
ALTER TABLE table ADD PRIMARY KEY field;
ALTER TABLE table DROP PRIMARY KEY;

Using MySQL graphical interface





11 Aug 2014
Steal code from another table
15
Home exercise: Your library

Create a blank database
◦ Build the tables
 Start from the tables on the “1” side
 Put appropriate types
 Put appropriate primary key
◦ Check the structure
◦ Fill in the tables
16
Home exercise : Your library

Queries
◦ Build a query to show book title, author surname,
publishing year and author birth date
◦ Build a query to show book title, author surname and
publishing date only for German and French authors.
 Do not rewrite a new one. Modify easily the previous one
adding another field with a condition and hiding it
◦ Build a query to show book title, author surname and
publishing date only for those book published before
1930.
 Do not rewrite a new one. Modify the first one.
17
Home exercise : Your library

Exercises
◦ Is there the book TITLE?
◦ Is there a book TITLE published after 1/1/2000?
◦ Which books AUTHOR has published?

Improvements
◦ Put NOT NULL for appropriate fields
◦ Insert check constraints for appropriate fields
◦ Build a structure for predefined values lists where
appropriate
18
Home exercise : Your library

Advanced queries. Build a query which:
◦ shows book title, author surname and publishing year
only for those authors born before a date invented by
you.
◦ shows book title, author surname and the age of the
author when the book was published.
◦ counts the books for every author.
◦ calculates the average publishing year of each author
◦ calculates the average publishing year of each author
considering only German and English authors.
19
Home exercise : students and exams

Build a blank database
◦ Build the tables
 Start from the tables on the “1” side
 Put appropriate types
 Put appropriate primary keys
◦ Check the structure
◦ Fill in the tables
20
Home exercise : students and exams

Queries
◦ Build a query which displays, for every student,
the list of his passed exams.
◦ Build a query which displays the list of passed
exams and students’ last names, considering
only who got a laude.
◦ Build a query which displays the student number
(ordered from lowest to highest) of students
who passed computer science.
21
Home exercise : students and exams
◦ Expand the database inserting information
about professors and their exams
 Many to many relation  another extra table
◦ Put NOT NULL for appropriate fields
◦ Insert check constraints for appropriate fields
◦ Build a structure for predefined values lists
where appropriate
22
Home exercise : students and exams

Advanced queries. Build a query which:
◦ shows the list of courses of professor Coletti.
◦ shows the list of exams of professor Coletti with the
average grade that students obtain in the exam
(considering, obviously, only the passed attempts)
◦ shows the list of professor Coletti’s exams with the
number of students who got more that 24
23