1 / 43

Bazat e te dhenave Elemente kryesore te MySQL-se

Bazat e te dhenave Elemente kryesore te MySQL-se. Leksion 12. 1. 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)?.

hera
Download Presentation

Bazat e te dhenave Elemente kryesore te MySQL-se

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Bazat e tedhenaveElementekryesorete MySQL-se Leksion 12 1

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

  3. CfareeshtenjeBazetedhenash (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 artikull te vecante.

  4. Title ……………………. Title: MySQL/PHP Title …………………… Author ……………………. Author: Jay Greenspan, Brad Bulger Author ……………………. Source ……………………. Source: http://www.oreilly.com/ Source ……………………. Subject ……………………. Subject: Database Driven Web Sites Subject ……………………. Cfare eshte nje Baze te dhenash (Database)? DataBase ( a collection of records) Records(a collection of fields) Fields(individual elements of information)

  5. CfareeshtenjeSistemManaxhimiiBazavetetedhenave (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. ProblemeqendeshenkurnukperdorenDatabazatrelacionale. • 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. RregullatkryesoreteDatabazaveRelacionale • 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.

  9. CelesatPrimaredheteJashtem • 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. • Celesi i Jashtem ne tabelen B nuk do te jete unik, pervec rastit kur kemi relacion NJE-ME-NJE

  10. Shembullicelesaveprimaredhetejashtem

  11. CfareeshteMySQL?

  12. CfareeshteMySQL? • 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

  13. Bazate SQL-se: KonceptedheTerminologji

  14. Cfareeshte 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)

  15. Terminologji ne SQL • Tabele >Njebashkesirreshtash > Analog me nje “skedar” • Rresht > Analog me njerekord ne nje “skedar” • Kolone >Njekoloneeshteanaloge me njefushetenjerekordi >Cdokolone ne njerreshttedhene ka njevleretevetme • Celes Primar >Njeose me shumekolonaqekanevleraunikebrenda njetabeledhemundteperdoren per teidentifikuar njerreshtteasajtabele

  16. Bazate SQL-se: Databazat

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

  18. Caktimiinjedatabaze default mysql> USE mydb; Database changed 17

  19. Fshirja e njedatabaze 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 | +--------------------+ 4 rows in set (0.00 sec) 18

  20. SQLBasics: Bazate SQL-se: Tabelat

  21. Krijimiinjetabele 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

  22. 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; Empty set (0.00 sec) 24

  23. Bazate SQL-se: Fushat

  24. Perkufizimetefushave: • Cdo fushe ka • Emrin e fushes • Tipin e tedhenave • Modifikuesin e fushes –kushtezimin (constraint) 27

  25. Tipet e tedhenavetefushave - 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 31

  26. Tipet e tedhenavetefushave • • TINYINT • > 1 byte, -128 to 127 (me shenje), 0 to 255 (pa shenje) • • FLOAT • > vlera me presjedhjetore me precision tethjeshte • •DOUBLE • >vlera me presjedhjetore me precision tedyfishte • •DECIMAL • >vlera decimal • • BIT • >vlera bit ( b'0101‘) 31

  27. Tipet e tedhenavetefushave • • CHAR • >Stringje me gjatesifiksederi ne 255 karaktere • • VARCHAR • >Stringje me gjatesivariabelderi ne 255 karaktere • • DATE, TIME, YEAR • • DATETIME, TIMESTAMP • • ENUM, SET • > Bashkesivlerashteparacaktuara 31

  28. Modifikuesit e fushes • NULL ose NOT NULL >Tregonnesefushamundteketevlere null apojo • DEFAULT >Caktonnjevlere default nesenukeshtespecifikuar ndonjevleregjateshtimitterekorditteri • AUTO_INCREMENT >MySQLgjeneronnjenumer ne menyreautomatike (duke by ishtuar 1 vleres se meparshme) >Perdoret per krijimin e celesitprimar • CHARACTER SET >Specifikonbashkesine e karaktereve per vlerat string 31

  29. Bazate SQL-se: INSERT/UPDATE/DELETE

  30. Shtimiinjerekorditevetem 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. Shtimiidisarekordevenjeheresh 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

  33. Modifikimiirekordeve 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 mysql> SELECT * FROM person; +-----------+------------+-----------+-----+ | person_id | first_name | last_name | age |+-----------+------------+-----------+-----+ | 1 | sang | shin | 88 | | 2 | kelly | jones | 22 | | 3 | jack | kennedy | 56 | | 4 | paul | kennedy | 88 | +-----------+------------+-----------+-----+ 4 rows in set (0.00 sec) 36

  34. Bazate SQL-se: SELECT

  35. Nxjerrja e disafushave 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)

  36. 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 | +------------+-----------+-----+ 1 row in set (0.00 sec) 39

  37. Nxjerrja e rekordevesipasrradhes 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 njenumritekufizuar 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. Komandabaze ne SQL: FunksionetAritmetike

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

  41. COUNT, AVG, SUM mysql> SELECT COUNT(age) FROM person;+------------+ | COUNT(age) |+------------+ | 5 | +------------+ 1 row in set (0.04 sec) 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

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

  43. Pyetje?

More Related