Introducere în limbajele SQL s, i PHP(Informatica Aplicata 1 - Cursul 6)

Victor BUCATA

Facultatea de Inginerie Electrica - U.P.B.

Partea I

Limbajul SQL

Ce este SQL?

I Structured Query Language (Limbaj structurat de interogare).

I limbaj standard folosit pentru gestiunea bazei de date, pentrucomunicarea cu ea.

I pronuntare: es-q-l sau precum cuvântul englezesc "sequel".

I limbaj neprocedural, declarativ (un limbaj procedural foloseste oserie de instructiuni executate secvential).

I se spune calculatorului ce rezultate se doresc fara a spune cum seobtin.

I SQL este adesea folosit în combinatie cu un limbaj procedural sauorientat spre obiecte (ex: MySQL+PHP).

Istoric al limbajului SQL

I Sfârsitul anilor ’70 - cercetatori de la IBM dezvolta System/R bazatpe lucrarile Dr. E. F. Codd.

I Oracle dezvolta Relational Software.

I Relational Technology dezvolta Ingres.

I 1982 - IBM SQL/DS.I standardizari (utile pentru portabilitate):

I 1986 - standardul ANSII 1987 - standardul ISOI 1989 - standardul SQL-89I 1992 - standardul SQL-92 (SQL 2) - 600 de paginiI 1999 - standardul SQL-99 (SQL 3)I 2003 - standardul SQL:2003I 2006 - standardul SQL:2006 (interoperabilitate cu XML)I 2008 - standardul SQL:2008I 2011 - standardul SQL:2011

I 2013 - Oracle Database 12c

Structura unui sistem SQL

I Structura de tip client/server

Sisteme SQL

I Clienti în linia de comanda

I Clienti cu interfata grafica

I Clienti interfata bazata pe web

Sisteme SQL:

I Access: produs de Microsoft, nu are client SQL; clientul, interfata simotorul pe acelasi calculator

I SQL Server: produs de Microsoft, clientul (iSQL sau QueryAnalyzer) ruleaza ca aplicatie în linia de comanda într-un nucleu decomenzi Microsoft Windows

I MySQL: sub licenta libera (locul 2 în clasamentul SGBDR gratuite cautilizare) cumparat în 2010 de Oracle prin achizitia SunMicrosystems care la rândul ei l-a cumparat de la o firma suedeza.Client în linia de comanda sub diferite sisteme de operare

I Oracle: clienti iSQL*Plus, SQL*Plus, SQL Worksheet

I Sybase: client iSQL

Conventii de sintaxa SQL

I Fiecare instructiune începe cu o comanda, de obicei sub forma unuisingur cuvânt

I Fiecare instructiune se termina cu punct si virgula (;)

I O instructiune se poate scrie pe mai multe linii

I Instructiunile sunt organizate într-o serie de clauze care trebuie saapara într-o anumita ordine

I Nu se face diferenta între majuscule si minuscule - case-insensitive(cu exceptia numelor de tabele)

I Virgulele sunt folosite pentru separarea articolelor dintr-o lista

Conventii de sintaxa SQL (continuare)

I Sirurile de caractere se pun între ghilimele

I Numele obiectelor bazei de date sunt formate numai din litere, cifresi liniute de subliniere

I Exista un set de cuvinte rezervate

I Un comentariu pe o singura linie începe cu doua liniute dedespartire (- -)

I Un comentariu pe mai multe linii se include între /* si */

Categorii de instructiuni SQL

I Limbajul de definire a datelor (DDL - Data Definition Language):CREATE, ALTER, DROP

I Limbajul de interogare a datelor (DQL - Data Query Language):SELECT

I Limbajul de manipulare a datelor (DML - Data ManipulationLanguage): INSERT, UPDATE, DELETE

I Limbajul pentru controlul datelor (DCL - Data Control Language):GRANT, ALTER

I Comenzile pentru controlul tranzactiilor (Transaction ControlCommands)

Crearea si manipularea tabelelor

Se foloseste instructiunea CREATE TABLE:

I numele noului tabel

I numele si definitiile coloanelor separate prin virgule

Exemplu:

CREATE TABLE Angajati(id_angajati INT(11) NOT NULL AUTO_INCREMENT,nume_ang VARCHAR(20),prenume_ang VARCHAR(20),adresa_ang VARCHAR(40),tel_ang VARCHAR(10),varsta_ang INT(2),id_functie INT(3));

Chei primare

CREATE TABLE Angajati(id_angajati INT(11) NOT NULL PRIMARY KEY,....);

sau:

ALTER TABLE AngajatiADD CONSTRAINT PRIMARY KEY (id_angajati);

Chei externe (straine)

CREATE TABLE Angajati(...id_functie INT(4) NOT NULL

REFERENCES Posturi(id_posturi););

sau:

ALTER TABLE AngajatiADD CONSTRAINTFOREIGN KEY (id_functie)

REFERENCES Posturi(id_posturi);

Actualizarea definitiilor tabelelor - instructiunile ALTER siDROP

Modificarea tabelelor:

ALTER TABLE AngajatiADD email_ang VARCHAR(25);

sau:

ALTER TABLE AngajatiDROP COLUMN spor_noapte;

Stergerea tabelelor:

DROP TABLE AngajatiCopie;

Inserarea datelor - instructiunea INSERT

I pentru a insera o singura înregistrare completa

I pentru a insera o singura înregistrare partiala

I pentru a insera rezultatele unei interogari

Exemple:

INSERT INTO AngajatiVALUES ("Cristea", "Dan", "Str. Uverturii,

nr.22", NULL, 23, NULL);

sau:

INSERT INTO Angajati (nume_ang, prenume_ang,adresa_ang, tel_ang, varsta_ang, id_functie)VALUES ("Cristea", "Dan",

"Str. Uverturii, nr.22", NULL, 23, NULL);

Actualizarea si stergerea datelor - instructiunile UPDATE siDELETE

Se foloseste instructiunea UPDATE, formata din trei componente:

I tabelul care urmeaza a fi actualizat

I numele coloanelor si noile valori ale acestora

I conditia de filtrare care determina înregistrarile ce urmeaza a fiactualizate

Pentru a sterge date (înregistrari) dintr-un tabel se foloseste instructiuneaDELETE. Exemplu:

DELETE FROM AngajatiWHERE id_angajati=8;

Pentru a sterge anumite coloane se foloseste instructiunea UPDATEatribuind valori NULL. Daca se omite clauza WHERE, operatiile deactualizare/stergere vor fi aplicate tuturor rândurilor din tabel.

Exemplu instructiunea UPDATE

UPDATE AngajatiSET tel_ang = "07112233"WHERE id_angajati = 7;

Instructiunea SELECT

Regasirea datelor - Instructiunea SELECT (face parte din categoria DQL- Data Query Language)

I SELECT este instructiunea SQL cea mai folosita.

I e nevoie de doua informatii: ce se selecteaza + de unde seselecteaza

Exemplul 1 - selectarea unei coloane

SELECT nume_studentFROM Studenti;

Exemplul 2 - selectarea mai multor coloane

SELECT nume_studenti, prenume_studenti, varstaFROM Studenti;

Exemplul 3 - selectarea tuturor coloanelor

Pentru a selecta toate coloanele se foloseste semnul asterisc (*)

SELECT *FROM Studenti;

Exemplul 4 - sortarea datelor regasite

Instructiunile SQL sunt alcatuite din clauze, unele obligatorii (de exempluFROM în instructiunea SELECT), altele optionale.Pentru a sorta datele se foloseste clauza ORDER BY.

SELECT nume_studenti, prenume_studentiFROM StudentiORDER BY nume_studenti;

Exemplul 5 - sortarea în functie de mai multe coloane

SELECT id, nume_studenti, prenume_studenti,varsta

FROM StudentiORDER BY varsta, nume_studenti;

Exemplul 6 - sortarea în functie de pozitia coloanei

SELECT id, nume_studenti, prenume_studenti,varsta

FROM StudentiORDER BY 4, 2;

Exemplul 7 - specificarea directiei de sortare

Pentru ordine descrescatoare se foloseste cuvântul cheie DESC.

SELECT id, nume_studenti, prenume_studentiFROM StudentiORDER BY nume_studenti DESC;

Filtrarea datelor: utilizarea clauzei WHERE

I Se folosesc criterii de cautare (adica "conditii de filtrare").

I Clauza ORDER BY trebuie plasata dupa WHERE.

I Operatorii clauzei WHERE: =,<>,!=,<,<=,>,>= ,BETWEEN,IS NULL

I când o coloana nu contine nicio valoare, se spune ca include ovaloare NULL (cu totul altfel decât un câmp care contine 0 sauspatiu)

Exemplul 8 - clauza WHERE

SELECT nume_studenti, prenume_studenti, varstaFROM StudentiWHERE varsta = 20;

Exemplul 9 - operatorii clauzei WHERE

SELECT id, nume_studenti, prenume_studenti,grupa

FROM StudentiWHERE grupa <> "112A";

Exemplul 10 - operatorii clauzei WHERE

SELECT id, nume_studenti, prenume_studenti,varsta

FROM StudentiWHERE varsta BETWEEN 21 AND 23;

Exemplul 11 - filtrarea avansata (operatorul AND)

SELECT nume_studenti, prenume_studentiFROM StudentiWHERE grupa = "111A" AND varsta > 21;

Exemplul 12 - filtrarea avansata (operatorul OR)

SELECT nume_studenti, prenume_studenti, grupa,varsta

FROM StudentiWHERE grupa = "111A" OR varsta < 21;

Exemplul 13 - filtrarea avansata (operatorul IN)

SELECT nume_studenti, prenume_studenti, varstaFROM StudentiWHERE varsta IN (20, 21, 22);

Exemplul 14 - filtrarea avansata (operatorul NOT)

SELECT nume_studenti, prenume_studenti, varstaFROM StudentiWHERE NOT varsta = 20;

Exemplul 15 - filtrarea avansata (operatorul LIKE)

I caracterul de înlocuire (oricâte): procent(%)

I caracterul de înlocuire (un singur caracter): liniuta de subliniere (_)

SELECT nume_studenti, prenume_studenti, grupaFROM StudentiWHERE prenume_studenti LIKE "S%";

Exemplul 16 - filtrarea avansata (operatorul LIKE)

SELECT nume_studenti, prenume_studenti, varsta,grupa

FROM StudentiWHERE grupa LIKE "11_ _";

Exemplul 17 - alias-uri

Un alias este un nume alternativ al unui câmp sau al unei valori. Seatribuie prin intermediul cuvântului cheie AS.

SELECT nume_studenti,varsta AS "VARSTA STUDENTULUI"

FROM Studenti;

Exemplul 18 - efectuarea calculelor matematice

SELECT nume_studenti,2010-varsta AS "AN NASTERE"

FROM Studenti;

Functii pentru manipularea datelor

I functii pentru manipularea sirurilor de caractere (eliminarea saucompletarea cu valori, conversia în majuscule sau minuscule)I LENGTH()I LOWER()I UPPER()I RTRIM() - elimina spatiile albe de la dreapta sirului

I pentru operatii matematiceI ABS()I COS()I EXP()I PI()I SQRT()

I de tip data si ora

I functii de sistem

Exemplul 19 - functii pentru manipularea datelor

SELECT CONCAT(UPPER(nume_studenti), " ",prenume_studenti)AS NUME

FROM Studenti;

Exemplul 20 - functii matematice

SELECT PI(), SQRT(625), SIN(PI()/2);

Sintetizarea datelor

Utilizarea functiilor agregat

I lucreaza cu seturi de înregistrari pentru a calcula si returna osingura valoare

I AVG() - media valorilor dintr-o coloana

I COUNT() - numarul de înregistrari dintr-un tabel sau de înregistraricare satisfac un anumit criteriu

I MAX(), MIN()

I SUM()

Exemplul 21 - functia AVG()

SELECT AVG(varsta) AS "Varsta medie"FROM Studenti;

Exemplul 22 - functia COUNT()

SELECT COUNT(nume_studenti)AS Numarul_studentilor

FROM Studenti;

Exemplul 23 - functia MAX()

SELECT MAX(varsta) AS Varsta_maximaFROM Studenti;

Exemplul 24 - argumentul DISTINCT

SELECT AVG(DISTINCT varsta) AS "Varsta medie"FROM Studenti;

Gruparea datelor

I Grupurile permit împartirea datelor în seturi logice pentru a puteaefectua calcule de sinteza cu fiecare grup.

I Se foloseste clauza GROUP BY plasata dupa clauzele WHERE siORDER BY.

I Grupurile pot fi filtrate cu clauza HAVING (spre deosebire de clauzaWHERE care filtreaza înregistrari, HAVING filtreaza grupuri).

Exemplul 25 - Gruparea datelor

SELECT grupa, SUM(restante) AS Restante_grupaFROM StudentiGROUP BY grupa;

Exemplul 26 - clauza HAVING

SELECT grupa, SUM(restante) AS Restante_grupaFROM StudentiGROUP BY grupaHAVING SUM(restante) >= 5;

Exemplul 27 - sub-interogari

SQL permite crearea sub-interogarilor, adica a interogarilor care suntînglobate în alte interogari.

SELECT nume_ang, prenume_ang, tel_angFROM AngajatiWHERE id_functie IN (SELECT id_functieFROM PosturiWHERE sal_min >= 4000);

Exemplul 27 - sub-interogari

Exemplul 28 - sub-interogari (crearea de câmpuri cu valoarecalculata)

SELECT den_post, (SELECT COUNT(*)FROM AngajatiWHERE Angajati.id_functie = Posturi.id_posturi) AS Nr_posturiFROM Posturi;

Exemplul 29 - unirea tabelelor

SELECT nume_ang, prenume_ang, den_post, sal_minFROM Angajati, PosturiWHERE Angajati.id_functie = Posturi.id_posturi;

Exemplul 30

SELECT nume_ang, prenume_ang, den_post, sal_minFROM Angajati, Posturi;

Exemplul 31 - uniune interioara

SELECT nume_ang, prenume_ang, den_post, sal_minFROM Angajati INNER JOIN PosturiON Angajati.id_functie = Posturi.id_posturi;

Exemplul 32 - uniune exterioara

Se foloseste RIGHT sau LEFT pentru a specifica tabelul din careurmeaza a se include toate rândurile.

SELECT nume_ang, prenume_ang, den_postFROM Angajati RIGHT OUTER JOIN PosturiON Angajati.id_functie = Posturi.id_posturi;

Exemplul 33 - combinarea interogarilor

SELECT nume_ang, prenume_ang, varsta_angFROM AngajatiWHERE varsta_ang > 40UNIONSELECT nume_ang, prenume_ang, varsta_angFROM Angajati, PosturiWHERE Angajati.id_functie = Posturi.id_posturi

AND spor_noapte > 15;

Exemplul 33 - combinarea interogarilor

Caracteristici avansate: Gestiunea prelucrarii tranzactiilor

Grupuri de operatii SQL ce se executa în totalitate sau deloc. Exemplu înMySQL:

START TRANSACTION...

Comanda SQL ROLLBACK este folosita pentru anularea instructiunilor.Pentru a crea un punct de salvare se foloseste instructiunea SAVEPOINT.

Caracteristici avansate: Securitatea bazelor de date

I autorizarea si autentificarea utilizatorilor

I securitatea este gestionata prin intermediul instructiunilor SQL:GRANT sau REVOKE

Bibliografie SQL

I https://www.w3schools.com/sql/

I https://www.tutorialspoint.com/sql/

I http://www.youtube.com/watch?v=4Z9KEBexzcM/

Partea II

Limbajul PHP

Prezentarea limbajului PHP

I învatarea PHP = pasul natural urmator dupa învatarea limbajuluiHTML

I se integreaza cu limbajul de marcare HTML

I Ierarhia celor mai folosite limbaje de programare:

Pozitie Limbaj Avantaje Dezavantaje

1 Java Portabilitate, popularitate Mai putin rapid decât limbajele originale compilate2 C Viteza Gestiunea dificila a memoriei3 C++ Viteza mai vechi/stângaci decât alte limbaje OO4 PHP Potrivit pt. programare web, documentatie ok Prea multe moduri de a face acelasi lucru5 VB - Merge doar în Windows6 Python Excelent de lizibil si de bine proiectat -7 C# Puternic si destul de rapid Doar pentru Windows8 JS Singurul mod pt. aplicatii web pe partea client Se foloseste doar cu un browser web9 Perl Procesare de test si administrarea de sistem Prea multe moduri de a face acelasi lucru

10 Ruby Limbajul cel mai mult orientat pe obiecte Viteza redusa

Prezentarea limbajului PHP

I dedicat dezvoltarii de pagini web dinamice

I nu ruleaza pe client (browser) ci pe server

I limbaj de script (interpetat, nu compilat)

I echivalentul PHP de la Microsoft este ASP

I permite aplicatii simple dinamice dar si aplicatii complexe ce contininteractiuni cu bazele de date

I cel mai adesea este folosit în conjunctie cu MySQL

I sintaxa similara limbajelor C, Java, Perl

I începând cu versiunea 5 contine caracteristici ale programarii OO(Orientate pe Obiecte)

Istoricul limbajului PHP

I creat în 1994 de Rasmus Lerdorf pentru a-si gestiona mai usorpaginile web personale

I initial PHP provenea de la Personal Home Page Tools

I 1995 - versiunea 2

I 1998 - versiunea 3 (de atunci PHP provine de la HyperTextPreprocessor ilustrând faptul ca PHP genereaza pagini webdinamice).

I 2000 - versiunea 4

I 2007 - versiunea 5

I 2013 - versiunea 5.5

I 3 decembrie 2015 - versiunea 7.0

Avantajele limbajului PHP

I este gratuit si Open Source

I nu depinde de platforma

I la fel de puternic ca mediile platite

I adaptat pentru traficul generat pe un server web

I este dedicat dezvoltarii web

I are o documentatie bogata

I comunitate de dezvoltare activa imensa

I este adesea folosit cu serverul web Apache care este deasemeneagratuit dar si robust

Ce este o pagina web statica?

Ce este o pagina web dinamica?

Diagrama operatiunilor pentru o pagina web dinamica

Ce este necesar pentru a putea folosi PHP?

I server web (Apache este folosit în 75% din cazuri, IIS (InternetInformation Server) de la Microsoft este folosit în 20% din cazuri

I preprocesorul PHP

I sistemul de gestiune a bazelor de date (MySQL)

Se recomanda instalarea componentelor concomitent, automat, cu kitulde instalare WAMP SERVER 2(http://www.wampserver.com/en/).Mai este nevoie de un editor de text (de ex. Notepad++ sau SublimeText).

Configurare

Fisierele de configurare sunt:

I httpd.conf pentru serverul Apache

I php.ini pentru limbajul PHP

I my.ini pentru MySQL

Configurare (continuare)

Trebuie sa ne asiguram ca în fisierul php.ini exista linia:

error_reporting = E_ALL

Si ca utilizatorul root din MySQL are setata o parola:

use mysql;update userset Password =

PASSWORD ("scrieti aici parola dorita")where user = "root";flush PRIVILEGES;quit;

Configurare (continuare)

Deasemenea în cazul în care serverul nu poate fi pus online trebuiespecificat un alt port decât portul 80 (setat implicit) în fisierul httpd.conf.Mai jos am setat acest port la 8080 în fisierul httpd.conf:

Listen 8080

Elementele limbajului

I Exemplele pentru sintaxa de baza le gasiti la adresahttp://itee.elth.pub.ro/~vbucata/ia/cursuri/ex/index.php

I Apoi alte câteva exemple:

I Exemplul 38 - Formulare

I Exemplul 39 - Cookieuri

I Fis, iere sesiune

I Exemplul 40 - PHP+MySQL

Exemplul 38 - formulare

Fis, ierul ex38.php:

1 <html>2 <head>3 <title>Formular</title>4 <body>5 <form action="ex38b.php" method="post">6 utilizator:<input type="text" name="

utilizator" value=""/>7 </body>8 </html>

Fis, ierul ex38b.php:

1 <?php2 $utilizator=$_POST[’utilizator’];3 echo $utilizator;4 ?>

Exemplul 39 - cookie-uri

1 <?php2 setcookie(’test’,45,time()+(60*60*24*7));3 ?>4 <?php5 $var1=0;6 if (isset($_COOKIE[’test’]))7 {8 $var1=$_COOKIE[’test’];9 }

10 echo $var1;11 ?>

Fis, iere sesiune

I ofera o sigurant,a mai mare decât cookie-urile

I sunt fis, iere ce sunt salvate pe serverul web s, i în care se poate stocaoricâta informat,ie se dores, te

I se folosesc cookie-uri pentru a vedea unde sunt stocate fis, ierelesesiune

I nu se poate vedea informat,ia ci doar adauga

I în cazul unui numar mare de utilizatori trebuie prevazute masuri desecuritate

PHP+MySQL

Pentru interactiunea cu baza de date sunt necesari cinci pasi:

1 se creeaza conexiunea la sistemul de baze de date

2 se selecteaza o baza de date

3 se executa o instructiune SQL

4 se folosesc datele întoarse

5 se închide conexiunea

script MySQL

Se scriu urmatoarele comenzi MySQL:

create database db1;use db1;create table studenti(

idstudent int auto_increment not null,primary key(idstudent),nume varchar(25) not null,prenume varchar(25) not null,annastere smallint(4) not null

);insert into studenti(nume, prenume, annastere)values ("Ion","Ion",1994),("Gheorghe","Gheorghe",1993);

Exemplul 40 - PHP cu MySQL

1 <?php $conexiune=mysql_connect("localhost","root","scrieti aici parola");2 if(!$conexiune){3 die("Eroare la conexiunea cu MySQL");4 }5 $bd_select=mysql_select_db("db1",$conexiune);6 if(!$bd_select){7 die("Eroare la deschiderea bazei de date");8 }9 $rezultat=mysql_query("select * from studenti",$conexiune);

10 if(!$rezultat){11 die("Instructiunea MySQL nu poate fi executata");12 }13 while($inregistrare=mysql_fetch_array($rezultat)){14 echo $inregistrare[0]." ".$inregistrare[1]."<br>";1516 }17 mysql_close($conexiune); ?>

Bibliografie PHP

I http://www.newthinktank.com/2014/08/php-programming/

I https://www.youtube.com/watch?v=7TF00hJI78Y

I http://www.newthinktank.com/2014/09/php-mysql-tutorial/

Întrebari ?

Va mult,umesc pentru atent,ie!