Tohuwabohu excorcism

User Tools

Site Tools

Trace: • prometheus • gerrit • tests • git • cloud • ansible • mysql • nginx
database:mysql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
database:mysql [2023/11/01 07:13] – removed - external edit (Unknown date) 127.0.0.1database:mysql [2023/11/02 11:04] (current) – skipidar
Line 1: Line 1:
 +==== Mysql basic commands ====
  
 +===Fallpits ===
 +  * Quotes <WRAP>
 +|" | don't use it, it is disabled in some databases|
 +|' | wrap Strings|
 +|` | wrap table names or columns|
 +</WRAP>
 +  * 
 +
 +
 +=== Details ===
 +The Details about MySQL are {{ http://www.w3schools.com/sql/sql_intro.asp | here}}.
 +
 +
 +
 +=== Sample Database ===
 +The tool, which generated the picture is called {{ http://www.mysql.com/downloads/workbench/ | MySQL Workbench }}
 +
 +
 +=== Querying ===
 +
 +==Selecting columns and rows==
 +
 +Select Columns "Title,Salesrank" from the album databse, with the salesrank more than 10000:
 +
 +<sxh sql>
 +SELECT  Title,Salesrank FROM album WHERE 10000;
 +</sxh>
 +
 +
 +
 +==Destinct==
 +
 +Merges the duplicates
 +
 +Select all Labels from the album database:
 +
 +<sxh sql>
 +SELECT DISTINCT Label  FROM album WHERE 1;
 +</sxh>
 + 
 +
 +
 +
 +== Select LIKE, Between, IN, AND, OR, NOT ==
 +
 +<sxh sql>
 +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');
 +
 +
 +</sxh>
 +
 +== Select parenthesis grouping ==
 +
 +<sxh sql>
 +SELECT * FROM Customers
 +WHERE ( Country = 'Spain' AND CustomerName LIKE 'G%')
 +OR ( Country = 'Mexico')
 +</sxh>
 +
 +== ORDER BY - sorting==
 +
 +Select all Titles and Prices ordered by the price, ascending | descending 
 +
 +<sxh sql>
 +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;
 +</sxh>
 + 
 +
 +
 +
 +Select all Titles and Prices ordered by the price, ascending | descending 
 +
 +<sxh sql>
 +SELECT Title, Price FROM album WHERE 1 ORDER BY Price ASC
 +SELECT Title, Price FROM album WHERE 1 ORDER BY Price DESC
 +</sxh>
 + 
 +
 +
 +== Insert  ==
 +
 +<sxh sql>
 +
 +--- 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');
 +</sxh>
 +
 +
 +== 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**?
 +<code>
 ++---------+---------+---------+---------+
 +|    =    |  TRUE   |  FALSE  | UNKNOWN |
 ++---------+---------+---------+---------+
 +| TRUE    | TRUE    | FALSE   | UNKNOWN |
 +| FALSE   | FALSE   | TRUE    | UNKNOWN |
 +| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
 ++---------+---------+---------+---------+
 +
 +</code>
 +
 +That means a query comparing null with = returns nothing.
 +<sxh sql>
 +--- 
 +--- 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
 +</sxh>
 +
 +
 +== Update ==
 +
 +<sxh sql>
 +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';
 +
 +</sxh>
 +
 +
 +== Delete==
 +
 +<sxh sql>
 +
 +--- delete with where
 +DELETE FROM Customers
 +WHERE Country == 'Burkinafaso'
 +
 +
 +--- remove all
 +Delete from Customers
 +
 +</sxh>
 +
 +
 +== DROP==
 +
 +<sxh sql>
 +
 +--- delete with where
 +DROP TABLE Customers;
 +--- You have made changes to the database.
 +
 +</sxh>
 +
 +
 +== Select TOP==
 +
 +<sxh sql>
 +
 +--- 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;
 +
 +</sxh>
 +
 +
 +
 +
 +== Nested Queries ==
 +
 +
 +<sxh sql>
 +SELECT TOP 5 * FROM
 + (SELECT * FROM Customers ORDER BY CustomerName ASC)
 +
 +</sxh>
 +
 +<code>
 +CustomerName Country
 +
 +Alfreds Futterkiste Germany 
 +Ana Trujillo Emparedados y helados Mexico 
 +Antonio Moreno Taquería Mexico 
 +Around the Horn UK 
 +Berglunds snabbköp Sweden 
 +</code>
 +
 +
 +
 +
 +
 +== MIN / MAX ==
 +
 +
 +<sxh sql>
 +SELECT MIN(PostalCode) FROM Customers
 +WHERE Country = "Germany"
 +
 +SELECT MAX(PostalCode) FROM Customers
 +WHERE Country = "Germany"
 +</sxh>
 +
 +
 +== Count ==
 +
 +<sxh sql>
 +
 +--- 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;
 +
 +
 +</sxh>
 +
 +
 +
 +== SUM ==
 +
 +<sxh sql>
 +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;
 +
 +</sxh>
 +
 +
 +== AVG Average ==
 +
 +<sxh sql>
 +SELECT AVG(Price)
 +FROM Products;
 +
 +--- below average
 +SELECT * FROM Products
 +WHERE price < (SELECT AVG(price) FROM Products);
 +
 +</sxh>
 +
 +
 +== Like Wildcards ==
 +
 +<sxh sql>
 +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]%';
 +
 +</sxh>
 +
 +
 +
 +== IN or NOT IN ==
 +
 +<sxh sql>
 +SELECT * FROM Customers
 +WHERE Country NOT IN ('Germany', 'France', 'UK');
 +
 +SELECT * FROM Customers
 +WHERE CustomerID IN (SELECT CustomerID FROM Orders);
 +
 +</sxh>
 +
 +
 +==  BETWEEN ==
 +
 +<sxh sql>
 +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;
 +
 +</sxh>
 +
 +
 +==  Alias ==
 +
 +<sxh sql>
 +
 +--- "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";
 +
 +</sxh>
 +
 +
 +Aliases to shorten table names, to shorten them
 +
 +<sxh sql>
 +
 +--- 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;
 +</sxh>
 +
 +
 +
 +
 +==  Having ==
 +
 +
 +The ''HAVING'' clause was added to SQL because the ''WHERE'' keyword cannot be used with aggregate functions.
 +
 +
 +
 +
 +
 +<sxh sql>
 +--- 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
 +
 +</sxh>
 +
 +
 +==  Exist ==
 +
 +
 +<sxh sql>
 +--- 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);
 +
 +</sxh>
 +
 +
 +
 +==  ANY, ALL==
 +
 +The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
 +
 +
 +
 +<sxh sql>
 +
 +--- product name with productID having quantity=10 somewhere
 +SELECT ProductName
 +FROM Products
 +WHERE ProductID = ANY
 +  (SELECT ProductID
 +  FROM OrderDetails
 +  WHERE Quantity = 10);
 +
 +</sxh>
 +
 +
 +
 +
 +==  ANY, ALL==
 +
 +The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
 +
 +
 +<sxh sql>
 +
 +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;
 +
 +</sxh>
 +
 +
 +
 +
 +==   CASE WHEN ==
 +
 +Case expression
 +
 +<sxh sql>
 +
 +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;
 +
 +</sxh>
 +
 +
 +
 +
 +== 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:  |
 +
 +<sxh sql>
 +
 +--- 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;
 +
 +</sxh>
 +
 +
 +
 +
 +
 +== Stored Procedure Example ==
 +
 +
 +<sxh sql>
 +
 +--- 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';
 +
 +</sxh>
 +
 +
 +
 +====  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
 +
 +{{https://s3.eu-central-1.amazonaws.com/alf-digital-wiki-pics/sharex/2hwjHojm7D.png?490x85}}
 +
 +
 +==  JOIN (same as INNER JOIN) ==
 +
 +Ignores null values in Orders and Customers tables
 +
 +<sxh sql>
 +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);
 +</sxh>
 +
 +
 +== FULL (OUTER) Join ==
 +
 +Takes null CustomerIDs from both tables.
 +
 +Also shows Customers without Orders.
 +
 +And Orders without Customers.
 +
 +<sxh sql>
 +
 +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
 +
 +</sxh>
 +
 +
 +== LEFT Join ==
 +
 +<sxh sql>
 +
 +SELECT * FROM Orders AS o
 +LEFT JOIN Customers AS c
 + ON c.CustomerID = o.CustomerID
 + WHERE OrderID > 10441
 +</sxh>
 +
 +== RIGHT Join ==
 +
 +<sxh sql>
 +
 +SELECT * FROM Orders AS o
 +RIGHT JOIN Customers AS c
 + ON c.CustomerID = o.CustomerID
 + WHERE OrderID > 10441
 +</sxh>
 +
 +== Self Join ==
 +
 +<sxh sql>
 +
 +-- 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;
 +
 +</sxh>
 +
 +
 +
 +====  Unions ====
 +
 +==  UNION / UNION ALL ==
 +
 +
 +<sxh sql>
 +
 +-- 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;
 +
 +</sxh>
 +
 +
 +
 +
 +
 +When putting together Customer, Supplier you can remember who is who
 +
 +by adding a **AS Role** column.
 +
 +It will contain values Customer / Supplier
 +
 +<sxh sql>
 +
 +SELECT 'Customer' AS Role, ContactName, City, Country
 +FROM Customers
 +UNION
 +SELECT 'Supplier', ContactName, City, Country
 +FROM Suppliers
 +
 +</sxh>
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +==== Aufgaben ====
 +
 +==0. ==
 +Waehle die DB aus, liste die tabellen.
 +<sxh sql>
 +use mydatabase
 +show tables
 +</sxh>
 +
 +==1.==
 +Führen Sie Titel, Preis und Verkaufsrang aller Alben des Künstlers Sting auf. Sortieren Sie die Liste aufsteigend nach dem Verkaufsrang.
 +
 +<sxh sql>
 +SELECT title, price, salesrank WHERE Artist="Sting" ORDER BY Salesrank ASC;
 +</sxh>
 +
 +
 +==2.==
 +Führen Sie die ersten 5 Tracks und den Albumtitel der Alben auf, denen kein Label zugeordnet ist.
 +
 +<sxh sql>
 +SELECT track.trackno, track.tracktitle, album.title FROM
 +track INNER JOIN
 +album ON
 +track.fromalbum = album.asin
 +WHERE album.label IS NULL
 +LIMIT 5;
 +</sxh>
 +
 +==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.
 +
 +<sxh sql>
 +SELECT album.label, AVG(album.Salesrank) As AvgAlbumSalesrank, COUNT(ASIN) AS AlbumsCnt 
 +FROM album
 +WHERE 1
 +GROUP BY Label
 +HAVING COUNT(ASIN)>=50
 +</sxh>
 +
 +==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.
 +
 +<sxh sql>
 +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;
 +</sxh>
 +
 +
 +==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.
 +
 +<sxh sql>
 +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;
 +</sxh>
 +
 +
 +==6.==
 +Führen Sie die Namen der ListMania-Listen an, die (mindestens) zwei Alben enthalten, die auch von Amazon als ähnlich bezeichnet werden.
 +
 +<sxh sql>
 +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
 +</sxh>
 +
 +
 +==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.
 +
 +<sxh sql>
 +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)
 +</sxh>
 +
 +==8.==
 +Listen Sie die IDs aller Kunden, die im Durchschnitt schlechter bewerten als der Gesamtdurchschnitt der Kundenbewertungen.
 +
 +<sxh sql>
 +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)
 +</sxh>
 +
 +==9.==
 +Ich habe eine Tabelle mit Zeiten und Zustaenden in der folgenden Form:
 +<code>
 +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
 +</code>
 +
 +Wie findet man die max. configurations fuer jedes Date-Device Paar?
 +<code>
 +Date        Device  Configuration
 +01.01.2012  8193    22375
 +01.01.2012  7191    32335
 +</code>
 +
 +**Antwort:** \\
 +"Group By" Kann mehrere Spalten nehmen und die Passenden operationen fuer jede Kombination dieser Spalten ausfuehren.
 +
 +<sxh sql>
 +select `date`,`Device`,max(Configuration) as MaxConfig from logData
 +group by `date`,`Device`
 +</sxh>
 +==== Mysql advanced commands ====
 +
 +=== flush root pass ===
 +
 +First Stop mysql service.
 +
 +<code>
 +# /etc/init.d/mysqld stop
 +</code>
 +
 +Start MySQL server without password:
 +<code>
 +# mysqld_safe   – -skip-grant-tables &
 +</code>
 +
 +Connect to mysql server using mysql client:
 +<code>
 +# mysql -u root
 +</code>
 +Setup new MySQL root user password
 +
 +<code>
 +mysql> use mysql;
 +mysql> update user set password=PASSWORD(“NEW-PASSWORD”) where User=’root’;
 +mysql> flush privileges;
 +mysql> quit
 +</code>
 +
 +Finally restart MySQL Server and test it.
 +
 +<code>
 +# /etc/init.d/mysqld restart
 +# mysql -u root -p
 +</code>
 +
 +
 +=== restrict to localhost ===
 +
 +open 
 +<code>
 +/etc/mysql/my.cnf
 +</code>
 +add
 +<code>
 +bind-address            = 127.0.0.1
 +</code>
 +
 +
 +=== import of csv file ===
 +<code>
 +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`)
 +</code>