Plán na cvičenie

  • Trochu úvodnej teórie
  • Dátové typy
  • Základy SQL získavania údajov
  • Samostatná práca pri písaní SQL select dopytov

Intro

Pre odskusanie selektov je potrebna prax a priamy kontakt s nou.
Preto budeme zatial zo zaciatku pouzivat ukazkovu databazu Sakila pre MySQL .
Informacie o nej mozete najst na stranke https://dev.mysql.com/doc/sakila/en/sakila-introduction.html.

Pristupove udaje k testovacej databaze sa dozviete na cviceni. Prosim nesirte tieto pristupove udaje osobam, ktore nie su v tomto semestri zapisani na tomto predmete. Dakujem.


Literatura

Literaturu ohladne selektov mozete najst z Moodle:

Alebo aj kurz na W3Schools : SQL.


Úloha 1

Najprv si preštudujte informacie pod napisom "Trochu uvodu" až do konca a potom sa vráťte na vypracovanie tejto úlohy.

Vyskusajte si prikazy uvedene na strankach:

Odporucam taktiez si na svojej databaze vyskusat aj :


Úloha 2

Najprv si preštudujte informacie pod napisom "Trochu uvodu" až do konca a potom sa vráťte na vypracovanie tejto úlohy.

Vyskusajte si prikazy uvedene na strankach:


Úloha 3

Najprv si preštudujte informacie pod napisom "Trochu uvodu" až do konca a potom sa vráťte na vypracovanie tejto úlohy.

Vyskusajte si prikazy uvedene na strankach:


Trochu úvodu

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases
  • SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

  • SQL can execute queries against a database
  • SQL can retrieve data from a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

Using SQL in Your Web Site

To build a web site that shows data from a database, you will need:

  • An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
  • To use a server-side scripting language, like PHP or ASP
  • To use SQL to get the data you want
  • To use HTML / CSS

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables.

A table is a collection of related data entries and it consists of columns and rows.


Dátové typy

Dátový typ NULL

Dátový typ NULL nie je zhodný s hodnotou 0, lebo 0 je určitá numerická hodnota vyjadrujúca počet. Ale hodnota NULL predstavuje "nič", ktorá môže byť použita pri ktoromkoľvek dátovom type. Vyhľadávanie hodnôt s hodnotou NULL je uskutočnované pomocou operátora IS.

SELECT * FROM table_name WHERE column_name IS NULL;

SELECT * FROM table_name WHERE column_name IS NOT NULL;
    

Oracle Dátové typy

Sú uvedené v tomto .pdf súbore .

MySQL Dátové typy

In MySQL there are three main types : text, number, and Date/Time types.

Text types:

Data type Description
CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT Holds a string with a maximum length of 255 characters
TEXT Holds a string with a maximum length of 65,535 characters
BLOB For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.) Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.

Note: The values are sorted in the order you enter them.

You enter the possible values in this format: ENUM('X','Y','Z')

SET Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Number types:

Data type Description
TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d) A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d) A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.

Date types:

Data type Description
DATE() A date. Format: YYYY-MM-DD

Note: The supported range is from '1000-01-01' to '9999-12-31'

DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MI:SS

Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

TIMESTAMP() *A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MI:SS

Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC

TIME() A time. Format: HH:MI:SS

Note: The supported range is from '-838:59:59' to '838:59:59'

YEAR() A year in two-digit or four-digit format.

Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMISS, YYMMDDHHMISS, YYYYMMDD, or YYMMDD.


Syntax SELECT

SELECT column_name,column_name
FROM table_name;

SELECT * FROM table_name;

SELECT DISTINCT column_name
FROM table_name;

     

Podmienka WHERE

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

-- Operators
-- = 	Rovné
-- <> 	Neorné. V niektorích verziách SQL sa používa !=
-- > 	Väčšie ako
-- < 	Menšie ako
-- >= 	Väčšie alebo rovné ako
-- <= 	Menšie alebo rovné ako
-- BETWEEN 	z rozsahu
-- LIKE 	hľadanie podľa výrazu
-- IN 	výber z udaných hodnôt

     

Spájanie podmienok

SELECT * FROM Customers
WHERE condition AND condition;

SELECT * FROM Customers
WHERE condition OR condition;

SELECT * FROM Customers
WHERE condition
AND (condition OR condition);

     

Zoradenie údajov

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC;

SELECT column_name, column_name
FROM table_name
ORDER BY column_name DESC;

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC, column_name DESC;

     

Obmedzenie počtu zobrazených údajov

-- SQL Server / MS Access Syntax
	SELECT TOP number column_name(s)
	FROM table_name;

-- MySQL Syntax
	SELECT column_name(s)
	FROM table_name
	LIMIT number;

-- Oracle Syntax
	SELECT column_name(s)
	FROM table_name
	WHERE ROWNUM <= number;

     

Vyhľadávanie podľa vzoru

-- 	pattern - je retazec

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

SELECT column_name(s)
FROM table_name
WHERE column_name NOT LIKE pattern;

-- Špeciálne znaky
-- % 	nula alebo viac znakov
-- _ 	jeden znak
-- [charlist] [a-c] [abc] Množina znakov, z ktorej musí jedno obsahovať
-- [^charlist]	Množina znakov, z ktorej nesmie ani jedno obsahovať
-- [!charlist] Množina znakov, z ktorej nesmie ani jedno obsahovať

     

Podmienky IN a BETWEEN

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (value1,value2,...);

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

     

Aliasy

-- Alias stĺpca
SELECT column_name AS alias_name
FROM table_name;

-- Alias tabuľky
SELECT column_name(s)
FROM table_name AS alias_name;

     

Zretazenie

-- MySQL
SELECT concat(column_name,' ',column_name)
FROM table_name;

-- Oracle SQL
SELECT column_name || ' ' || column_name
FROM table_name;


     

Operácie v select klauzule

select column_name + column_name from table_name;
select column_name - column_name from table_name;
select column_name * column_name from table_name;
select column_name / column_name from table_name;
select column_name % column_name from table_name;

     

Agregačné funkcie

SELECT AVG(column_name) FROM table_name;

-- COUNT(column_name) vráti počet hodnôt ( NULL hodnoty nebudú započítané )
SELECT COUNT(column_name) FROM table_name; 

-- COUNT(*) vráti počet záznamov v tabuľke
SELECT COUNT(*) FROM table_name; 

SELECT MAX(column_name) FROM table_name;

SELECT MIN(column_name) FROM table_name;

SELECT SUM(column_name) FROM table_name;

     

Využitie agregačných funkcií - GROUP BY, HAVING

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name; 

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

     

CASE

-- Oracle SQL
SELECT column_name1,
   CASE column_name2 
   WHEN when_value THEN statement_value
   WHEN when_value THEN statement_value
   ELSE statement_value END
   FROM table_name;

-- MySQL
SELECT column_name1,
   CASE column_name2 
   WHEN when_value THEN statement_value
   WHEN when_value THEN statement_value
   ELSE statement_value END CASE
   FROM table_name;

     

Zdroj: http://www.w3schools.com/sql/