Pokrocile mySQL - kde ?

Zdravim,

rad bych se podival ne nejaky pokrocily tutorial hodne MySQL. Tim myslim trosku netradicni a slozitejsi dotazy, nez ktere se vetsinou probiraji v diskusnich forech ...

Spojovani pres 2-3 tabulky atd me moc nezajima.


Nasel jsem si obecne knize o SQL cosi, ale nevim jestli se to tak da pouzivat i v MYSQL.

#Ukazka #

SELECT BeefRecipes.RecipeTitle

FROM (SELECT Recipes.RecipeID, Recipes.RecipeTitle

FROM (Recipes

INNER JOIN Recipe_Ingredients

ON Recipes.RecipeID = Recipe_Ingredients.RecipeID)

INNER JOIN Ingredients

ON Ingredients.IngredientID = Recipe_Ingredients.IngredientID

WHERE Ingredients.IngredientName = 'Beef') AS BeefRecipes

INNER JOIN (SELECT Recipe_Ingredients.RecipeID

FROM Recipe_Ingredients

INNER JOIN Ingredients

ON Ingredients.IngredientID = Recipe_Ingredients.IngredientID

WHERE Ingredients.IngredientName = 'Garlic') AS GarlicRecipes

ON BeefRecipes.RecipeID = GarlicRecipes.RecipeID

A podobne ... myslim tim selectovanim ve fromu , ve wheru a takove netradicni veci.

Zatim jsem hledal neuspesne, kdyby mel nekdo nejaky link ..

znam, nic noveho pod sluncem.

Predstavoval bych si nejaky komplexni rozbor SELECTU

kam vsude se da narvat subselect nebo spojeni tabulky

do casti pred FROM, ZA , DO WHERU a udelat tim jakou temp table atd. .. ;-)
enco takoveho .. klidne v jazyce anglickem

Uz jsem lehce zagooglil a nasel asi 5 stranek tak na to juknu vcetne dokumentace ..diky

Neni o co ... porad je to totez dokola, me fascinuje ta knizka SQL - tvorba dotazu od Hernandeze, myslel jsem ze jsem v MySQL uz nekde na stupni pokrocilejsim ale kdyz to tak vidim tak mi jde hlava kolem.

napr.


All_Tourneys_Match_Results:

SELECT Tournaments.TourneyID, Tournaments.TourneyDate, Tournaments.TourneyLocation,
TM.MatchID, TM.GameNumber, TM.Winner
FROM Tournaments LEFT JOIN
(SELECT Tourney_Matches.TourneyID, Tourney_Matches.MatchID, Match_Games.GameNumber,
Teams.TeamName AS Winner
FROM Tourney_Matches
INNER JOIN (Teams
INNER JOIN Match_Games
ON Teams.TeamID = Match_Games.WinningTeamID)
ON Tourney_Matches.MatchID = Match_Games.MatchID) AS TM
ON Tournaments.TourneyID = TM.TourneyID
Matches_Not_Played_Yet:

SELECT Tourney_Matches.MatchID, Tourney_Matches.TourneyID,
Teams.TeamName AS OddLaneTeam, Teams_1.TeamName AS EvenLaneTeam
FROM Teams AS Teams_1
INNER JOIN (Teams
INNER JOIN (Tourney_Matches LEFT JOIN Match_Games
ON Tourney_Matches.MatchID = Match_Games.MatchID)
ON Teams.TeamID = Tourney_Matches.OddLaneTeamID)
ON Teams_1.TeamID = Tourney_Matches.EvenLaneTeamID
WHERE Match_Games.MatchID Is Null
Tourney_Not_Yet_Played:

SELECT Tournaments.TourneyID, Tournaments.TourneyDate,
Tournaments.TourneyLocation
FROM Tournaments LEFT JOIN Tourney_Matches
ON Tournaments.TourneyID = Tourney_Matches.TourneyID
WHERE Tourney_Matches.MatchID Is Null


Pak jsou tu celkem zajimave veci jako JOIN v DELETE a UPDATE

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

mysql> DELETE cds
-> FROM cds, artists
-> WHERE (cds.artistID = artists.artistID)
-> AND (cds.artistID = '3');

To DELETE from both tables:

mysql> DELETE cds, artist
-> FROM cds, artist
-> WHERE (cds.artistID = artists.artistID)
-> AND (cds.artistID = '3');
===========
mysql> UPDATE cds
-> LEFT JOIN artists
-> USING (artistID)
-> SET cds.artists = artists.Artist
-> WHERE (cds.cdID = '2');

vice link


SQL pouzivam dost casto hlavne klasicke subselecty a UNIONY, ale na spojene dotazy a subselecty zanorene v casti za FROM a vytvareni jakych si tempTable s tim sem se tedy jeste nesetkal


===

Kdo by mel zajem muze mrknout jeste sem -> nikdo mi na to neodpovedel a uz je to forum mimo dosah ;)

http://diskuse.jakpsatweb.cz/index.php?action=vthread&forum=9&topic=58 723