MySQL Basics

phpMyAdmin

We will use MySQL in the phpMyAdmin environment.

Resources

Database Terminology

database
table
attributes
rows
relational model
database management system (DBMS)
SQL
constraints
primary key
entity-relationship (ER) modelling

A Simple Residential Database

Country Table

Country	ID	Primary Language	Population
Germany	1	German	82000000
US	2	English	295000000
Austria	3	German	8000000

Person Table

First Name	Last Name	ID	Country ID
Angela	Merkel	1	1
Wolfgang	Schüssel	2	3
George	Bush	3	2
Horst	Köhler	4	1

Entity-Relationship Model (ER)

entities - rectangles
attributes - ovals (primary keys are underlined)
relationships - diamonds (an end marked with an M indicates "Many" as opposed to nothing that indicates "One")

Creating Tables

CREATE TABLE country
(
  country varchar(30),
  id int(5) NOT NULL,
  primary_language varchar(30),
  population int(15),
  PRIMARY KEY (id)
);

CREATE TABLE person
(
  first_name varchar(25),
  last_name varchar(25),
  id int(5) AUTO_INCREMENT,
  country_id int(5) DEFAULT 1,
  PRIMARY KEY (id)
);

Inserting Data

INSERT INTO country VALUES ("Germany", 1, "German", 82000000);
INSERT INTO country SET country = "US", id = 2, primary_language = "English", population = 295000000;
INSERT INTO country VALUES ("Austria", 3, "German", 8000000);

INSERT INTO person (first_name, last_name, country_id) VALUES ("Angela", "Merkel", 1);
INSERT INTO person (first_name, last_name, country_id) VALUES ("Wolfgang", "Schussel", 3);
INSERT INTO person (first_name, last_name, country_id) VALUES ("George", "Bush", 2);
INSERT INTO person (first_name, last_name) VALUES ("Horst", "Kohler");

Deleting Databases and Tables

DROP DATABASE residential;
DROP TABLE country;

One Table Queries

SELECT * FROM country;
SELECT * FROM person LIMIT 2;
SELECT * FROM person LIMIT 1,2;
SELECT country, id FROM country;
SELECT * FROM country WHERE primary_language = "German";
SELECT * FROM country WHERE primary_language LIKE 'G%';
SELECT * FROM country WHERE primary_language = "German" AND id <= 1;
SELECT * FROM country WHERE primary_language <> "German" OR id <= 1;
SELECT * FROM country WHERE NOT (primary_language = "German");
SELECT * FROM country ORDER BY country;
SELECT * FROM person ORDER BY last_name, first_name;
SELECT * FROM country ORDER BY country ASC;
SELECT * FROM country ORDER BY country DESC;
SELECT primary_language, COUNT(*) FROM country GROUP BY primary_language;
SELECT AVG(population) FROM country;
SELECT MIN(population) FROM country;
SELECT MAX(population) FROM country;
SELECT SUM(population) FROM country;
SELECT primary_language, COUNT(*) FROM country GROUP BY primary_language ORDER BY primary_language;
SELECT primary_language, COUNT(*) FROM country GROUP BY primary_language HAVING COUNT(*) > 1;
SELECT primary_language, COUNT(*) FROM country WHERE primary_language = "German" GROUP BY primary_language HAVING COUNT(*) > 1 ORDER BY primary_language ;

Join Queries

SELECT country, first_name FROM country, person;
SELECT country.country, person.first_name FROM country, person;
SELECT country, first_name FROM country, person WHERE country.id = person.country_id;
SELECT country, first_name FROM country, person WHERE country.id = person.country_id ORDER BY country DESC, first_name DESC;

Deleting Data

DELETE FROM person WHERE last_name = 'Bush';

Updating Data

UPDATE country SET primary_language = 'Spanish' WHERE id = 2;

Laboratory

Here is the link to today's assignment. If you are a Montana State student, demonstrate it during today's lab period. If you are a University of Leipzig student, either demonstrate it during today's lab period or e-mail it to Sebastian (loewe AT uni-leipzig.de) no later than the end of the day.