Tohuwabohu excorcism

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
==== 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>