2 SELECT
2.1 SELECT – zdrojová tabulka
2.1.1 Tabulka:
+--------------+--------+------------+---------+-------+----------------+-----------+------+
| uzivatele_id | jmeno | prijmeni | pohlavi | PLAT | datum_narozeni | mesto | test |
+--------------+--------+------------+---------+-------+----------------+-----------+------+
| 1 | jana | krátka | f | 23500 | 1954-12-22 | Brno | 0 |
| 2 | Jana | Nováková | F | 42261 | 2016-11-11 | Brno | 1 |
| 3 | Sophia | Santiago | F | 20758 | 1962-11-16 | Brno | 8 |
| 4 | Jarek | Benes | M | 52483 | 2017-06-11 | Praha | 8 |
| 5 | Petr | Malý | M | 10000 | 2001-09-23 | Praha | 8 |
| 6 | Honza | Adamec | M | 29990 | 1977-07-27 | Praha | 7 |
| 7 | Petra | Adamcova | F | 23500 | 1982-03-16 | Olomouc | 6 |
| 8 | Zuzka | Bezva | F | 39752 | 2016-02-29 | Ostrava | 7 |
| 9 | Ivan | Sobota | M | 15274 | 1900-02-01 | Pardubice | 3 |
| 10 | Petr | Novak | M | 27679 | 2002-05-20 | Zlin | 2 |
| 11 | Jirka | Plavec | M | 23500 | 2017-12-30 | Opava | 1 |
| 12 | Iva | Nováková | F | 25371 | 2007-06-07 | Opava | 0 |
| 13 | Ivan | Kurzo | M | 10000 | 2018-02-22 | Ostrava | 7 |
| 14 | Petr | Novak | M | 21072 | 2011-06-15 | Pardubice | 7 |
| 15 | Iva | Nováková | F | 23181 | 1994-02-13 | Brno | 8 |
| 16 | Ela | Nová | F | 23500 | 1987-12-14 | Brno | 5 |
| 17 | Jan | Novák | M | 49213 | 1979-08-19 | Brno | 2 |
| 18 | petr | novak | M | 18638 | 1960-11-18 | Brno | 2 |
| 19 | Tom | Krejza | M | 21086 | 2017-09-24 | Brno | 3 |
| 20 | Ida | Krejzova | F | 54032 | 2017-04-03 | Brno | 5 |
| 21 | Pavel | | M | 23500 | 2014-03-14 | Brno | 7 |
| 22 | Jakub | Nedelka | M | 50900 | 2017-05-16 | Praha | 3 |
| 23 | Zuzka | Lesní | F | 49956 | 2001-09-23 | Brno | 2 |
| 24 | Zuzana | Jarní | F | 10000 | 2001-09-23 | Brno | 1 |
| 25 | Ela | Melera | F | 10000 | 1994-02-13 | Brno | 0 |
+--------------+--------+------------+---------+-------+----------------+-----------+------+
2.1.2 Co budeme chtít z tabulky získat? Proč?
2.2 SELECT – syntaxe
Syntaxe
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[MAX_STATEMENT_TIME = N]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
Začneme od základu se syntaxí a budeme přidávat
select <co> from <odkud>;
SELECT <co> FROM <odkud>;
Problémy:
SELECT *;
SELECT * FROM studenti;
2.3 Podklady k procvičení
SELECT * FROM tabulka
SELECT * FROM databáze.tabulka
SELECT sloupec1, sloupec 2, ... FROM tabulka 1, tabulka 2, ...
SELECT sloupec1, sloupec 2 AS název, ... FROM tabulka
SELECT ... FROM ... WHERE podmínka
SELECT ... FROM ... WHERE podmínka GROUP BY pole
SELECT ... FROM ... WHERE podmínka GROUP BY pole HAVING podmínka2
SELECT ... FROM ... WHERE podmínka ORDER BY pole1, pole2
SELECT ... FROM ... WHERE podmínka ORDER BY pole1, pole2 DESC / ASC
SELECT ... FROM ... WHERE podmínka LIMIT 10
SELECT DISTINCT pole1 FROM ...
SELECT DISTINCT pole1, pole2 FROM ...
podmínka1 AND podmínka2
podmínka1 OR podmínka2
operátory
> < <> != >= <=
pole = hodnota
hodnota <> hodnota
hodnota != hodnota
pole LIKE 'hodnota _ %'
pole IS NULL
pole IS NOT NULL
pole IN (pole1, hodnota2)
pole1 NOT IN (hodnota1, hodnota2)
SELECT ... FROM ... WHERE podmínka REGEXP hodnota
t … tabulka
SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE podmínka
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE podmínka
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
SELECT ... FROM t1 JOIN t2 USING(id) WHERE podmínka