database:mysql

Mysql basic commands

Fallpits

Quotes

“ don't use it, it is disabled in some databases

' wrap Strings

` wrap table names or columns

Details

The Details about MySQL are here.

Sample Database

The tool, which generated the picture is called MySQL Workbench

Querying

Selecting columns and rows

Select Columns “Title,Salesrank” from the album databse, with the salesrank more than 10000:

        
              SELECT  Title,Salesrank FROM album WHERE 10000;

Destinct

Merges the duplicates

Select all Labels from the album database:

        
              SELECT DISTINCT Label  FROM album WHERE 1;

Select LIKE, Between, IN, AND, OR, NOT

        
              SELECT DISTINCT Country FROM Customers
 
WHERE Country LIKE "M%" 
OR Country LIKE "S%"
OR PostalCode BETWEEN 2000 AND 3000
OR City IN ('London', 'Paris')
 
SELECT * FROM Customers
WHERE ( CustomerName LIKE 'G%' OR CustomerName LIKE 'S%')
AND NOT Country = 'Spain';
 
SELECT * FROM Customers
WHERE ( CustomerName LIKE 'G%' OR CustomerName LIKE 'S%')
AND NOT Country IN ('Spain', 'USA');

Select parenthesis grouping

        
              SELECT * FROM Customers
WHERE ( Country = 'Spain' AND CustomerName LIKE 'G%')
OR ( Country = 'Mexico')

ORDER BY - sorting

Select all Titles and Prices ordered by the price, ascending | descending

        
              SELECT Title, Price FROM album WHERE 1 
ORDER BY Price ASC
 
SELECT Title, Price FROM album WHERE 1 
ORDER BY Price DESC
 
SELECT * FROM Customers
ORDER BY Country, CustomerName;
 
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

Select all Titles and Prices ordered by the price, ascending | descending

        
              SELECT Title, Price FROM album WHERE 1 ORDER BY Price ASC
SELECT Title, Price FROM album WHERE 1 ORDER BY Price DESC

Insert

        
              --- 92  Cardinal    Tom B. Erichsen Skagen 21   Stavanger   4006    Norway
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('AALfreddo','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
 
--- 92  Cardinal    null    null    Stavanger   null    Norway
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
 
--- insert many groups with parenthesis
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

NULL comparison

To handle NULLs correctly, SQL provides two special comparison operators: IS NULL and IS NOT NULL.

They return only true or false and are the best practice for incorporating NULL values into your queries.

See how those evaluate to unknown?

+---------+---------+---------+---------+
|    =    |  TRUE   |  FALSE  | UNKNOWN |
+---------+---------+---------+---------+
| TRUE    | TRUE    | FALSE   | UNKNOWN |
| FALSE   | FALSE   | TRUE    | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
+---------+---------+---------+---------+

That means a query comparing null with = returns nothing.

        
              --- 
--- no result
SELECT * FROM Customers WHERE Country = NULL
 
--- comparing with IS works
--- Number of Records: 2
SELECT * FROM Customers WHERE Country IS NULL
 
-- invert via NOT
SELECT * FROM Customers WHERE Country IS NOT NULL

Update

        
              UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
 
UPDATE Customers
SET Country='Burkinafaso'
WHERE Country IS NULL;
 
UPDATE Customers
SET ContactName='Juan';

Delete

        
              --- delete with where
DELETE FROM Customers
WHERE Country == 'Burkinafaso'
 
--- remove all
Delete from Customers

DROP

        
              --- delete with where
DROP TABLE Customers;
--- You have made changes to the database.

Select TOP

        
              --- top 3 or LIMIT
SELECT TOP 3 * FROM Customers;
SELECT TOP 10 * FROM Customers ORDER BY CustomerID
 
SELECT TOP 50 Percent * FROM Customers;
 
SELECT * FROM Customers ORDER BY CustomerID DESC LIMIT 10;

Nested Queries

        
              SELECT TOP 5 * FROM
 (SELECT * FROM Customers ORDER BY CustomerName ASC)

CustomerName	Country

Alfreds Futterkiste 	Germany 
Ana Trujillo Emparedados y helados 	Mexico 
Antonio Moreno Taquería 	Mexico 
Around the Horn 	UK 
Berglunds snabbköp 	Sweden

MIN / MAX

        
              SELECT MIN(PostalCode) FROM Customers
WHERE Country = "Germany"
 
SELECT MAX(PostalCode) FROM Customers
WHERE Country = "Germany"

Count

        
              --- count PostalCodes from Germany in this DB
SELECT Count(PostalCode) 
FROM Customers
WHERE Country = "Germany"
 
--- count how many DISTINCT countries do we have in db
--- as nested query
Select COUNT(Country) FROM
( SELECT DISTINCT Country FROM Customers)
 
--- more efficient 
SELECT COUNT(DISTINCT Price) FROM Products;
SELECT COUNT(DISTINCT Country) FROM Customers;

SUM

        
              SELECT SUM(Quantity)
FROM OrderDetails;
 
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProdictId = 11;
 
--- use expressions in SUM to figure out the total revenue from all sold products cross all orders
SELECT SUM(Price * Quantity)
FROM OrderDetails
LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID;

AVG Average

        
              SELECT AVG(Price)
FROM Products;
 
--- below average
SELECT * FROM Products
WHERE price < (SELECT AVG(price) FROM Products);

Like Wildcards

        
              SELECT * FROM Customers
WHERE city LIKE 'L_nd__';
 
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
 
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';
 
--- min 2 chars starts with a
SELECT * FROM Customers
WHERE CustomerName LIKE 'a_%';
 
--- any of the chards in the set: starts with b,s,p
SELECT * FROM Customers
WHERE CustomerName LIKE '[bsp]%';
 
SELECT * FROM Customers
WHERE CustomerName LIKE '[a-f]%';

IN or NOT IN

        
              SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
 
SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

BETWEEN

        
              SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
 
--- alphabetically sorted
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;

Alias

        
              --- "MyIDAlias With empty space"    Customer
--- 1   Alfreds Futterkiste 
SELECT TOP 1 CustomerID AS "MyIDAlias With empty space", CustomerName AS Customer
FROM Customers;
 
--- let the DB concat address
--- Address
--- Obere Str. 57, 12209, Berlin, Germany
SELECT CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers
WHERE CustomerName="Alfreds Futterkiste";

Aliases to shorten table names, to shorten them

        
              --- long
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;
 
-- short
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

Having

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

        
              --- The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers
 
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
 
--- The following SQL statement lists the number of employees having less than 20 sales
SELECT e.EmployeeID, LastName, FirstName, COUNT(o.OrderID) As Sales  FROM [Employees] AS e
LEFT JOIN "Orders" AS o
ON o.EmployeeID = e.EmployeeID
GROUP BY e.EmployeeID
HAVING COUNT(o.OrderID) < 20
ORDER BY Sales  DESC, FirstName DESC, LastName  ASC

Exist

        
              --- suppliers with products of price under 20
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
 
--- employees with orders
SELECT LastName
FROM Employees
WHERE NOT EXISTS (SELECT OrderID FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

ANY, ALL

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.

        
              --- product name with productID having quantity=10 somewhere
SELECT ProductName
FROM Products
WHERE ProductID = ANY
  (SELECT ProductID
  FROM OrderDetails
  WHERE Quantity = 10);

ANY, ALL

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.

        
              SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

CASE WHEN

Case expression

        
              SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

`IFNULL(COLUMN, DEFAULTVALUE) → COLUMN_VALUE or DEFAULT`	The IFNULL() function returns a specified value if the expression is NULL.
`ISNULL(COLUMN, DEFAULTVALUE) → BOOL`	Return boolean.
`COALESCE(val1, val2, …., val_n)`	Return the first non-null value in a list:

        
              --- fall back to 0 for NULL "UnitsOnOrder"
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;
 
--- using boolean ISNULL
SELECT OrderID, 
CASE
    WHEN ISNULL(AmountDue) THEN "Paid in full"
    WHEN DATE(DueDate) < date(NOW()) THEN "Order is past due"
    ELSE CONCAT("Order is due on ", CONVERT(VARCHAR, DueDate, 121))
END
FROM OrderDetails;

Stored Procedure Example

        
              --- procedure
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
 
--- execute
EXEC SelectAllCustomers;
 
--- procedure with args
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;
 
--- exec
EXEC SelectAllCustomers @City = 'London';

Joins

(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

JOIN (same as INNER JOIN)

Ignores null values in Orders and Customers tables

        
              SELECT * FROM Orders AS o
JOIN Customers AS c
 ON c.CustomerID = o.CustomerID
 WHERE OrderID > 10441
 
-- join 3 tables together
--- Achtung - there is only 1 SELECT but multiple joins
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders 
      INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
      INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

FULL (OUTER) Join

Takes null CustomerIDs from both tables.

Also shows Customers without Orders.

And Orders without Customers.

        
              SELECT * FROM Orders AS o
FULL JOIN Customers AS c
 ON c.CustomerID = o.CustomerID
 WHERE OrderID > 10441
 
--- same as FULL OUTER
SELECT * FROM Orders AS o
FULL OUTER JOIN Customers AS c
 ON c.CustomerID = o.CustomerID
 WHERE OrderID > 10441

LEFT Join

        
              SELECT * FROM Orders AS o
LEFT JOIN Customers AS c
 ON c.CustomerID = o.CustomerID
 WHERE OrderID > 10441

RIGHT Join

        
              SELECT * FROM Orders AS o
RIGHT JOIN Customers AS c
 ON c.CustomerID = o.CustomerID
 WHERE OrderID > 10441

Self Join

        
              -- show customer pairs from the same city
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City 
ORDER BY A.City;
 
-- show the customers, who are in the same city, like "Rancho grande"
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.CustomerName = "Rancho grande"
AND A.City = B.City 
ORDER BY A.City;

Unions

UNION / UNION ALL

        
              -- all cities, distinct union
 
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
 
-- UNION ALL means, cities are NOT unique
 
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

When putting together Customer, Supplier you can remember who is who

by adding a AS Role column.

It will contain values Customer / Supplier

        
              SELECT 'Customer' AS Role, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers

Aufgaben

0.

Waehle die DB aus, liste die tabellen.

        
              use mydatabase
show tables

1.

Führen Sie Titel, Preis und Verkaufsrang aller Alben des Künstlers Sting auf. Sortieren Sie die Liste aufsteigend nach dem Verkaufsrang.

        
              SELECT title, price, salesrank WHERE Artist="Sting" ORDER BY Salesrank ASC;

2.

Führen Sie die ersten 5 Tracks und den Albumtitel der Alben auf, denen kein Label zugeordnet ist.

        
              SELECT track.trackno, track.tracktitle, album.title FROM
track INNER JOIN
album ON
track.fromalbum = album.asin
WHERE album.label IS NULL
LIMIT 5;

3.

Listen Sie für jedes Label den durchschnittlichen Verkaufsrang der von ihm verlegten Alben auf. Es sollten nur Label aufgeführt werden, die mindestens 50 Alben verlegen.

        
              SELECT album.label, AVG(album.Salesrank) As AvgAlbumSalesrank, COUNT(ASIN) AS AlbumsCnt 
FROM album
WHERE 1
GROUP BY Label
HAVING COUNT(ASIN)>=50

4.

Führen Sie neben Titel und Künstler die minimale und maximale Kundenbewertung von Alben auf, die zu einem Album von Sting direkt ähnlich sind.

        
              SELECT Artists.artist, Artists.title,Ratings.minRating, Ratings.maxRating  FROM
 
(SELECT * FROM
     
    (SELECT similardest  FROM
        (SELECT * FROM album WHERE Artist="Sting") AS StingAlbums
        LEFT JOIN albumsimilar 
        ON StingAlbums.asin=albumsimilar.SIMILARSOURCE
    ) As SimilarIds
     
LEFT JOIN album
ON album.asin=SimilarIds.similardest) As Artists
 
LEFT JOIN
 
(SELECT album.artist, MAX(rating) As maxRating, MIN(rating) As minRating FROM
customerreview INNER JOIN
album ON
album.ASIN=customerreview.ALBUM
GROUP BY Artist) AS Ratings
 
ON Artists.artist = Ratings.artist;

5.

Listen Sie pro Kunden seine Anzahl von Bewertungen und seine durchschnittliche Bewertung auf. Kunden, die auch eine Bewertung eines Sting-Albums durchgeführt haben, sollen in dem Ergebnis nicht auftreten.

        
              CREATE VIEW StingRaters
AS
SELECT DISTINCT customerreview.customerid FROM
customerreview
LEFT JOIN album 
ON album.ASIN=customerreview.album
WHERE album.artist="Sting";
 
SELECT customerreview.customerid, COUNT(customerreview.rating), AVG(customerreview.rating) 
FROM customerreview
WHERE customerreview.customerid 
NOT IN (SELECT * FROM StingRaters)
GROUP BY customerreview.customerid
 
Drop VIEW Stingraters;

6.

Führen Sie die Namen der ListMania-Listen an, die (mindestens) zwei Alben enthalten, die auch von Amazon als ähnlich bezeichnet werden.

        
              SELECT Listname 
FROM
 
(SELECT DISTINCT BothJoined.LISTID1 AS LISTID
FROM
    (SELECT FirstJoin.LISTID1, FirstJoin.ALBUM1, FirstJoin.SIMILARSOURCE, FirstJoin.SIMILARDEST, listmaniaentry.LISTID AS LISTID2, listmaniaentry.ALBUM AS ALBUM2 
    FROM
 
        (SELECT l1.`LISTID` AS LISTID1, l1.`ALBUM` AS ALBUM1, albumsimilar.SIMILARSOURCE, albumsimilar.SIMILARDEST
        FROM listmaniaentry AS l1
        LEFT JOIN albumsimilar
        ON l1.ALBUM=albumsimilar.SIMILARSOURCE) AS FirstJoin
 
    LEFT JOIN 
        listmaniaentry
    ON FirstJoin.SIMILARDEST=listmaniaentry.ALBUM) AS BothJoined
WHERE LISTID1=LISTID2 ) ListIds
 
NATURAL JOIN Listmanialist

7.

Führen Sie die Titel und Künstler aller Alben auf, die zwar von Kunden bewertet wurden,für die jedoch keine Track-Informationen in der Datenbank gespeichert sind.

        
              SELECT * FROM
(SELECT album.asin, album.title, album.artist 
FROM album 
WHERE album.asin NOT IN (SELECT FROMALBUM FROM track) ) AS WithTrackInfo
WHERE WithTrackInfo.asin IN (SELECT ALBUM FROM customerreview WHERE RATING IS NOT NULL)

8.

Listen Sie die IDs aller Kunden, die im Durchschnitt schlechter bewerten als der Gesamtdurchschnitt der Kundenbewertungen.

        
              SELECT AvgCustomerRating.CUSTOMERID
FROM
(SELECT CUSTOMERID, AVG(rating) AS AvgRating
FROM customerreview 
GROUP BY Customerid) AS AvgCustomerRating
WHERE AvgCustomerRating.AvgRating < (SELECT AVG(customerreview.rating) FROM customerreview WHERE 1)

9.

Ich habe eine Tabelle mit Zeiten und Zustaenden in der folgenden Form:

Date        Time    Device  Configuration
01.01.2012  12.00   8193    12345
01.01.2012  12.15   8193    12345
01.01.2012  12.35   8193    22375
01.01.2012  12.37   7191    32335

Wie findet man die max. configurations fuer jedes Date-Device Paar?

Date        Device  Configuration
01.01.2012  8193    22375
01.01.2012  7191    32335

Antwort:
“Group By” Kann mehrere Spalten nehmen und die Passenden operationen fuer jede Kombination dieser Spalten ausfuehren.

        
              select `date`,`Device`,max(Configuration) as MaxConfig from logData
group by `date`,`Device`

Mysql advanced commands

flush root pass

First Stop mysql service.

# /etc/init.d/mysqld stop

Start MySQL server without password:

# mysqld_safe   – -skip-grant-tables &

Connect to mysql server using mysql client:

# mysql -u root

Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD(“NEW-PASSWORD”) where User=’root’;
mysql> flush privileges;
mysql> quit

Finally restart MySQL Server and test it.

# /etc/init.d/mysqld restart
# mysql -u root -p

restrict to localhost

open

/etc/mysql/my.cnf

add

bind-address            = 127.0.0.1

import of csv file

LOAD DATA LOCAL INFILE 'D:\\1PROJEKTE\\1Papierstaus\\DATA\\LVV\\2012-09-11_printing-context.lvl.txt.csv'
INTO TABLE `tbl_name`
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(`SystemTemperatur`, `Geraete-Nr.`, `Aktives-Setup`, `Firmware-Version`, `b4Ticket-Version`, `Fahrzeug-Nr.`, `Fahrer-Nr.`, `Transition`, `Datum`, `Zeitstempel`, `Logdatei`, `printingState`, `printedLines`, `linesToPrint`, `printError`, `cutterJam`, `paperInTray`, `cavity1Jam`, `cavity2Jam`, `paperAtEntry`, `coverClosed`, `coverLocked`, `temperatureCritical`, `bufferUnderrun`, `timeSinceLastPrint`, `analogPaperInTray`, `analogCavity1Jam`, `analogCavity2Jam`, `analogCoverLocked`, `analogPaperAtEntry`, `analogCoverClosed`, `analogCutterJam`, `analog24V`, `analogV`)

“	don't use it, it is disabled in some databases
'	wrap Strings
`	wrap table names or columns

−Table of Contents

Mysql basic commands

Fallpits

Details

Sample Database

Querying

Selecting columns and rows

Destinct

Select LIKE, Between, IN, AND, OR, NOT

Select parenthesis grouping

ORDER BY - sorting

Insert

NULL comparison

Update

Delete

DROP

Select TOP

Nested Queries

MIN / MAX

Count

SUM

AVG Average

Like Wildcards

IN or NOT IN

BETWEEN

Alias

Having

Exist

ANY, ALL

ANY, ALL

CASE WHEN

IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

Stored Procedure Example

Joins

JOIN (same as INNER JOIN)

FULL (OUTER) Join

LEFT Join

RIGHT Join

Self Join

Unions

UNION / UNION ALL

Aufgaben

0.

1.

2.

3.

4.

5.

6.

7.

8.

9.

Mysql advanced commands

flush root pass

restrict to localhost

import of csv file