Bazat e te dhenave Elemente kryesore te MySQL-se

Leksion 12 1

2

Cfare jane te dhenat?

 Te dhena (Data nga Datum) jane cdo informacion qe na duhet ta ruajme.  Emra te njerezve  Numra telefoni  Adresa  Etj.

Cfare eshte nje Baze te dhenash (Database)?

Nje database eshte nje koleksion i organizuar informacioni, zakonisht ne forme dixhitale.

Shembuj databazave qe mund te ndeshen ne jeten • • • • • e perditeshme: Nje numerator telefonik Sistemi i rezervimit te avioneve Sistemi i regjistrimit te automjeteve Sistemi i kartave te baseball-it Skedaret ne hard drive-n e kompjuterit tuaj. Cdo rekord ne nje databaze perbehet nga elemente te rendesishme te informacionit per nje 3 artikull te vecante.

Cfare eshte nje Baze te dhenash (Database)?

Fields (individual elements of information) 4 Title: MySQL/PHP Author: Jay Greenspan, Brad Bulger Source: http://www.oreilly.com/ Subject: Database Driven Web Sites Title …………………… Author …………………….

Source …………………….

Subject …………………….

Title …………………….

Author …………………….

Source …………………….

Subject …………………….

DataBase ( a collection of records) Records (a collection of fields)

5

Cfare eshte nje Sistem Manaxhimi i Bazave te te dhenave (Database Management System - DBMS)?

 Ruan dhe nxjerr informacion nga nje Database (psh: roli i nje nepunesi te nje zyre).

 Nje DBMS perfaqeson realitetin. Informacioni duhet te modelohet sipas jete reale.  Ne nje koleksion te kartave te baseball-it, informacioni ne nje karte eshte per nje lojtar.  Kartat grupohen ne Ekipe.  Ekipet grupohen ne Liga

6

Probleme qe ndeshen kur nuk perdoren Databazat relacionale.

 Te dhena te teperta, qe cojne ne paqendrueshmeri te te dhenave.  Sa me shume qe te shtoni te dhena te njejta , aq me shume mundesi keni per te bere gabime.  Nese nje e dhene ndryshon (psh: adrese e re) duhet te ndryshoni te dhenat ne shume vende.

7

Rregullat kryesore te Databazave Relacionale

 Databazat Relacionale permbajne me shume se nje tabele.  Tabelat jane grupime logjike te te dhenave te ngjashme.  Tabelat perbehen nga Rreshta(rekordet) dhe Kolona (fushat)  Te gjitha kolonat ne nje tabele lidhen me fushen celes.

8

Arsyeja

 Shumica e aplikacioneve kane shume tabela qe lidhen me tabelat e tjera, per te paraqitur informacionin ne menyre efikase.  Perdorimi i shume tabelave na lejon qe te manipulojme te dhenat ne menyre me efikase.  Nese keni shume tabela ju nevojiten celesa primare unike dhe celesa te jashtem.

Celesat Primare dhe te Jashtem

 Celesat primare (Primary Key) dhe te jashtem (Foreign Key ) perdoren per te vendosur relacionet ndermjet tabelave.  Celesi Primar ne tabelen A duhet te jete Unik  Celesi Primar ne tabelen A lidhet me nje fushe ne tabelen B. 9  Celesi i Jashtem ne tabelen B nuk do te jete unik, pervec rastit kur kemi relacion NJE-ME NJE

10

Shembull i celesave primare dhe te jashtem

Table A - Customers

Customer Key - (PK) Name Company Street City State Zip Phone

Table B - Orders

Order Key (PK) Customer Key (FK) Item Price

11

Cfare eshte MySQL?

Cfare eshte MySQL?

• Databaza open source me popullore: > Performance e larte > Besueshmeri e larte > Lehtesi ne perdorim • Perdoret ne shumicen e websiteve me te kerkuara ne bote > Yahoo, Google, YouTube, ...

• Ekzekutohet ne te gjitha platformat OS 12

13

Baza te SQL-se: Koncepte dhe Terminologji

14

Cfare eshte SQL-ja?

SQL (Structured Query Language) eshte nje gjuhe per te nxjerre dhe per te manipuluar te dhenat ne nje databaze > Data definition (DDL) > Data manipulation (DML) > Data control (DCL)

Terminologji ne SQL

• Tabele 15 > > Nje bashkesi rreshtash Analog me nje “skedar” • Rresht > Analog me nje rekord ne nje “skedar” • Kolone > Nje kolone eshte analoge me nje fushe te nje rekordi > Cdo kolone ne nje rresht te dhene ka nje vlere te vetme • Celes Primar > Nje ose me shume kolona qe kane vlera unike brenda nje tabele dhe mund te perdoren per te identifikuar nje rresht te asaj tabele

16

Baza te SQL-se: Databazat

17

Krijimi i nje databaze

mysql> CREATE DATABASE mydb; Query OK, 1 row affected (0.01 sec)

18

Caktimi i nje databaze default

mysql> USE mydb; Database changed 17

Fshirja e nje databaze

mysql> DROP DATABASE temp_db; Query OK, 0 rows affected (0.01 sec) mysql> DROP DATABASE IF EXISTS temp_db; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | mysql | test | | +--------------------+ | 19 4 rows in set (0.00 sec) 18

20

SQL Basics: Baza te SQL-se: Tabelat

Krijimi i nje tabele

21 mysql> CREATE TABLE person ( -> person_id SMALLINT UNSIGNED NOT NULL, -> first_name VARCHAR(45) NOT NULL, -> last_name VARCHAR(45) NOT NULL, -> PRIMARY KEY (person_id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.14 sec) mysql> SHOW TABLES; +----------------+ | Tables_in_mydb | +----------------+ | person | +----------------+ 1 row in set (0.00 sec) 20

Fshirja e tabelave

mysql> SHOW TABLES; +-------------------+ | Tables_in_temp_db | +-------------------+ | temp_table | +-------------------+ 1 row in set (0.00 sec) mysql> DROP TABLE temp_table; Query OK, 0 rows affected (0.06 sec) mysql> DROP TABLE IF EXISTS temp_table; Query OK, 0 rows affected, 1 warning (0.12 sec) mysql> SHOW TABLES; 22 Empty set (0.00 sec) 24

23

Baza te SQL-se: Fushat

24

Perkufizime te fushave:

• Cdo fushe ka  Emrin e fushes  Tipin e te dhenave  Modifikuesin e fushes –kushtezimin (constraint) 27

Tipet e te dhenave te fushave - Integer

• • TINYINT > 1 byte, -128 to 127 (signed), 0 to 255 (unsigned) SMALLINT > 2 bytes, -32768 to 32767 (signed), 0 to 65535 • • • (unsigned) MEDIUMINT > 3 bytes INT > 4 bytes BIGINT > 8 bytes 25 31

Tipet e te dhenave te fushave

• • • TINYINT > 1 byte, -128 to 127 (me shenje), 0 to 255 (pa shenje) FLOAT > vlera me presje dhjetore me precision te thjeshte DOUBLE > vlera me presje dhjetore me precision te dyfishte • DECIMAL > • BIT vlera decimal > vlera bit ( b'0101‘) 26 31

Tipet e te dhenave te fushave

• CHAR > Stringje me gjatesi fikse deri ne 255 karaktere • VARCHAR > Stringje me gjatesi variabel deri ne 255 karaktere • • • DATE, TIME, YEAR DATETIME, TIMESTAMP ENUM, SET > Bashkesi vlerash te paracaktuara 27 31

Modifikuesit e fushes

• NULL ose NOT NULL > Tregon nese fusha mund te kete vlere null apo jo • DEFAULT > Cakton nje vlere default nese nuk eshte specifikuar ndonje vlere gjate shtimit te rekordit te ri • AUTO_INCREMENT 28 > MySQL gjeneron nje numer ne menyre automatike (duke by i shtuar 1 vleres se meparshme) > Perdoret per krijimin e celesit primar • CHARACTER SET > Specifikon bashkesine e karaktereve per vlerat string 31

29

Baza te SQL-se: INSERT/UPDATE/DELETE

30

Shtimi i nje rekordi te vetem

mysql> INSERT INTO person (person_id, first_name, last_name, age) -> VALUES (1, 'sang', 'shin', 88); Query OK, 1 row affected (0.10 sec) mysql> SELECT * FROM person; +-----------+------------+-----------+-----+ | person_id | first_name | last_name | age | +-----------+------------+-----------+-----+ | 1 | sang | shin | 88 | +-----------+------------+-----------+-----+ 1 row in set (0.00 sec)

31

Shtimi i disa rekordeve njeheresh

mysql> INSERT INTO person (person_id, first_name, last_name, age) -> VALUES -> (2, 'kelly', 'jones', 22), -> (3, 'jack', 'kennedy', 56), -> (4, 'paul', 'kennedy', 34), -> (5, 'daniel', 'song', 24), -> (6, 'nichole', 'scott', 9); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 34

32

Fshirja e rekordeve

mysql> DELETE FROM person WHERE age < 10; Query OK, 1 row affected (0.07 sec) 35

Modifikimi i rekordeve

mysql> UPDATE person SET age = 88 -> WHERE age = 99 OR first_name = 'paul'; Query OK, 1 row affected, 2 warnings (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 2 33 | | mysql> SELECT * FROM person; +-----------+------------+-----------+-----+ | person_id | first_name | last_name | age | | | +-----------+------------+-----------+-----+ 1 | sang | shin | 88 | 2 | kelly | jones | 22 | 3 | jack 4 | paul | kennedy | kennedy | 56 | | 88 | +-----------+------------+-----------+-----+ 4 rows in set (0.00 sec) 36

34

Baza te SQL-se: SELECT

35

Nxjerrja e disa fushave ne menyre selektive

mysql> SELECT last_name, age FROM person; +-----------+-----+ | last_name | age | +-----------+-----+ | shin | 88 | | jones | 22 | | kennedy | 56 | | kennedy | 34 | | song | 24 | +-----------+-----+ 5 rows in set (0.00 sec)

Nxjerrja e informacionit me WHERE

mysql> SELECT first_name, age FROM person -> WHERE age > 50; +------------+-----+ | first_name | age | +------------+-----+ | sang | 88 | | jack | 56 | +------------+-----+ 2 rows in set (0.00 sec) mysql> SELECT first_name, last_name, age FROM person -> WHERE age < 50 AND first_name LIKE '%niel'; +------------+-----------+-----+ | first_name | last_name | age | +------------+-----------+-----+ | daniel | song | 24 | 36 +------------+-----------+-----+ 1 row in set (0.00 sec) 39

37

Nxjerrja e rekordeve sipas rradhes

mysql> SELECT last_name, age FROM person -> ORDER BY age ASC; +-----------+-----+ | last_name | age | +-----------+-----+ | jones | 22 | | song | 24 | | kennedy | 34 | | kennedy | 56 | | shin | 88 | +-----------+-----+ 5 rows in set (0.00 sec) mysql> SELECT * FROM person -> ORDER BY age DESC; +-----------+------------+-----------+-----+ | person_id | first_name | last_name | age | +-----------+------------+-----------+-----+ | 1 | sang | shin | 88 | | 3 | jack | kennedy | 56 | | 4 | paul | kennedy | 34 | | 5 | daniel | song | 24 | | 2 | kelly | jones | 22 |

38

Nxjerrja e nje numri te kufizuar rekordesh

mysql> SELECT * from person -> ORDER BY age DESC -> LIMIT 3 ; +-----------+------------+-----------+-----+ | person_id | first_name | last_name | age | +-----------+------------+-----------+-----+ | 1 | sang | shin | 88 | | 3 | jack | kennedy | 56 | | 4 | paul | kennedy | 34 | +-----------+------------+-----------+-----+ 3 rows in set (0.00 sec)

39

Komanda baze ne SQL: Funksionet Aritmetike

Veprimet Aritmetike

mysql> SELECT 3 + 6; +-------+ | 3 + 6 | +-------+ | 9 | +-------+ 1 row in set (0.00 sec) mysql> SELECT 45 * (1+2); +------------+ | 45 * (1+2) | +------------+ | 135 | +------------+ 40 1 row in set (0.00 sec)

COUNT, AVG, SUM

mysql> SELECT COUNT(age) FROM person; +------------+ | | COUNT(age) | +------------+ 5 | +------------+ 1 row in set (0.04 sec) 41 mysql> SELECT AVG(age) from person; +----------+ | AVG(age) | +----------+ | 44.8000 | +----------+ 1 row in set (0.00 sec) mysql> SELECT SUM(age) FROM person; +----------+ | SUM(age) | | +----------+ 224 | +----------+ 1 row in set (0.00 sec) 44

MIN, MAX

mysql> SELECT MIN(age) +----------+ | MIN(age) | | +----------+ 22 | +----------+ 1 row in set (0.00 sec) FROM person; 42 mysql> SELECT MAX(age) FROM person; +----------+ | MAX(age) | | +----------+ 88 | +----------+ 1 row in set (0.00 sec) 45

43

Pyetje?