Bazy danych, 1. ¢wiczenia
2007-10-02
1 Plan zaj¦¢
• wprowadzanie do baz danych,
• wprowadzenie do j¦zyka SQL (SELECT, INSERT, UPDATE, CREATE TABLE),
• przykªady zapyta«,
2 Przykªadowa baza danych
Biblioteka:
• ksi¡»ki (id, tytuª, autor),
• czytelnicy (pesel, imi¦, nazwisko),
• wypo»yczenia (id_ksi¡»ki, pesel, data_wypo»yczenia, data_zwrotu) Mo»na jeszcze troch¦ rozbi¢, np. autorów do osobnej tabeli, ale zakªadamy,
»e jeden autor napisaª co najwy»ej jedn¡ ksi¡»k¦.
3 Przykªady zapyta«
• wypisz wszystkich czytelników,
• wypisz wszystkie nazwiska (ró»ne),
• wypisz czytelników którzy wypo»yczyli chocia» jedn¡ ksi¡»k¦,
• wypisz czytelników którzy jeszcze nie zwrócili ksi¡»ek (z dat¡ wypo»ycz- nia),
• wypisz czytelników i liczb¦ ksi¡»ek które przeczytali,
• wypisz ksi¡»ki i liczb¦ ich wypo»ycze«
1
4 Skªadnia zapyta«
SELECT
[ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[FROM table_references [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' export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ] UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...)
[table_option ...]
create_definition:
column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...)
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
2
[index_name] (index_col_name,...) [reference_definition]
| CHECK (expr) column_definition:
col_name data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
3
