SQL IN FoxPro Structured Query Language – Limbaj de interogari ale bazelor de date SQL – este un limbaj general si standardizat pentru crearea interogarilor asupra tabelelor unei baze de date. El aparand in majoritatea sistemelor de gestiune a bazelor de date moderne. Visual FoxPro suporta urmatoarele comenzi SQL: SELECT - SQL Este cea mai importanta comanda de tip SQL, permitand realizarea unei interogari asupra unei baze de date FoxPro. ALTER TABLE - SQL Permite modificarea unei tabele. Se pot modifica numele, tipul,, precizia etc. pentru fiecare camp al unui table. CREATE CURSOR - SQL Creaza o tabela temporara. CREATE TABLE -SQL Creaza o tabela in modul programat, fara a fi nevoie de deschiderea unei casete de dialg. DELETE - SQL Marcheaza pentru stergere inregistrari din table. INSERT - SQL Adauga inregistrari la sfarsitul tabelei curente in modul programat. UPDATE - SQL Modifica inregistrari pe baza unei interogari SQL. INTRODUCERE Astfel pentru MANIPULAREA DATELOR sunt utilizate comenzile:
SELECT INSERT DELETE UPDATE
Extragerea datelor din BD Adaugarea de noi randuri/inregistrari in tabele Stergerea de inregistrari dintr-o tebela Modificarea valorilor unor atribute
Pentru DEFINREA BAZEI DE DATE
CREATE TABEL DROP TABLE ALTER TABLE CREATE VIEW CREATE CURSOR
Adaugarea unei noi tabele in BD Stergerea unei tabele din BD Modificarea structurii unui tabel Crearea unei tabele virtuale Crearea unei tabele temporare
1. CREAREA TABELELOR SI DECLARAREA RESTRICTIILOR Se efectueaza cu ajutorul comenzii CREATE TABLE iar pentru modificarea structurii tabelului cu ALTER TABLE 1
Syntaxa CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE] (FieldName1 FieldType [(nFieldWidth [, nPrecision])] [NULL | NOT NULL] [CHECK lExpression1 [ERROR cMessageText1]] [DEFAULT eExpression1] [PRIMARY KEY | UNIQUE] [REFERENCES TableName2 [TAG TagName1]] && Tablename2 tabela-parinte a legaturii [NOCPTRANS] [, FieldName2 ...] [, PRIMARY KEY eExpression2 TAG TagName2 |, UNIQUE eExpression3 TAG TagName3] && Se creaza un index candidat [, FOREIGN KEY eExpression4 TAG TagName4 [NODUP] && Se creaza un index regular REFERENCES TableName3 [TAG TagName5]] [, CHECK lExpression2 [ERROR cMessageText2]]) | FROM ARRAY ArrayName Exemplu: CREATE TABLE elevi ( cod N(4), nume C(20), pren C(20), clasa C(3), media N(5,2) ) sau CREATE DBF elevi ( cod N(4), nume C(20), pren C(20), clasa C(3), media N(5,2) )
2. UTILIZAREA INSTRUCTIUNILOR SQL PENTRU CREAREA BD SI A TABELELOR ******program care prezinta crearea si manipularea cu datele a unei BD prin intermediul SQL ******comenzilor CLOSE DATA ALL CLOSE TABLES ALL CLOSE ALL set default to home()+"\SQL_test\" set path to TIF DELETE DATABASE xxxxxx DELETE TABLES wait window "A fost distrusa BD xxxxxx" AT 20,60 timeout 3 CREATE DATABASE xxxxxx CREATE TABLE raioane (; nrai CHAR(2); PRIMARY KEY; CHECK(nrai=LTRIM(UPPER(nrai))); ERROR 'indicativul judetului se scrie cu majuscule !', ; raion CHAT(25) ; NOT NULL ; CHECK(raion=LTRIM(PROPER(raion))) ; 2
ERROR 'Prima literea din fiecare cuvint al' +chr(13)+; 'denumirii raionului este majuscula'+chr(13)+; 'restul literelor sint mici !',; regiune CHAR(15); DEFAULT 'Centru'; CHECK (INLIST(regiune,'Centru','Nord','Sud')); ERROR 'Regiunea poate avea doar o valoare din lista'+CHR(13)+; 'Centru,Nord,Sud'; ) ********** Tabelul 2 CREATE TABLE localitati (; codpost CHAR(5); PRIMARY KEY; CHECK(cospost=LTRIM(UPPER(codpost))); ERROR 'Codul postal se scrie fara spatii la inceput !', ; loc CHAT(25) ; NOT NULL ; CHECK(loc=LTRIM(PROPER(loc))) ; ERROR 'Prima literea din fiecare cuvint al' + CHR (13)+; 'denumirii localitatii este majuscula'+ CHR (13)+; 'restul literelor sint mici !',; nrai CHAR(2); DEFAULT 'IS',; FOREIGN KEY nrai TAG nrai REFERENCES raioane TAG nrai; ); ********** Tabelul 3 CREATE TABLE clienti (; codcl NUMBER(6); PRIMARY KEY; CHECK(codcl>1000),; ERROR 'Codul clientului trebuie sa fie mai mare decit 1000 !', ; dencl CHAR(30) ; CHECK(SUBSTR(dencl,1,1)=UPPER(SUBSTR(dencl,1,1))) ; ERROR 'Prima literea din denumirea clientului este'+ CHR (13)+; 'obligatoriu majuscula!' ,; codfiscal CHAR(9); NULL,; CHECK(SUBSTR(codfiscal,1,1)=UPPER(SUBSTR(codfiscal,1,1))) ; ERROR 'Prima literea din codul fiscal este'+ CHR (13)+; 'obligatoriu majuscula!' ,; adresa CHAR(40); NULL; CHECK(SUBSTR(adresa,1,1)=UPPER(SUBSTR(adresa,1,1))) ; ERROR 'Prima literea din adresa este'+ CHR (13)+; 'obligatoriu majuscula!' ,; codpost CHAR(5),; telefon CHAR(10) ; NULL, ; FOREIGN KEY codpost TAG codpost REFERENCES localitati TAG codpost; ); 3
********** Tabelul 4 CREATE TABLE persoane (; cnp CHAR(14); PRIMARY KEY; CHECK(cnp=LTRIM(UPPER(cnp))); ERROR 'Codul postal se scrie fara spatii la inceput !', ; nume CHAR(20) ; CHECK(nume= LTRIM(PROPER(nume))) ; ERROR 'Prima literea din fiecare cuvint al' + CHR (13)+; 'numelui persoanei este majuscula'+ CHR (13)+; 'restul literelor sint mici !',; prenume CHAR(20); CHECK(prenume= LTRIM(PROPER(prenume))) ; ERROR 'Prima literea din fiecare cuvint al' + CHR (13)+; 'prenumelui persoanei este majuscula'+ CHR (13)+; 'restul literelor sint mici !',; adresa CHAR(40); NULL; CHECK(SUBSTR(adresa,1,1)=UPPER(SUBSTR(adresa,1,1))) ; ERROR 'Prima literea din adresa este'+ CHR (13)+; 'obligatoriu majuscula!' ,; sex CHAR(1) DEFAULT 'B'; CHECK(INLIST(SEX,'F','B')); ERROR 'Atributul Sex poate avea valorile F pentru femei'+ CHR (13)+; 'si B pentru barabti' ,; codpost CHAR(5),; telacasa CHAR(10) NULL,; telbirou CHAR(10) NULL,; telmobil CHAR(10) NULL,; email CHAR(20) NULL,; FOREIGN KEY codpost TAG codpost REFERENCES localitati TAG codpost; ); ********** Tabelul 5 CREATE TABLE persclient (; cnp CHAR(14),; codcl NUMBER(6),; functie CAHR(25); CHECK(SUBSTR(functie,1,1)=UPPER(SUBSTR(functie,1,1))) ; ERROR 'Prima litera din functie se scrie cu majuscule !', ; PRIMARY KEY cnp+STR(codcl,6)+functie TAG primaru, ; FOREIGN KEY cnp TAG cnp REFERENCES persoane TAG cnp,; FOREIGN KEY codcl TAG codcl REFERENCES clienti TAG codcl; ) ********** Tabelul 6 CREATE TABLE produse (; codpr NUMBER(6); PRIMARY KEY; CHECK(codcl>0),; ERROR 'Codul produsului trebuie sa fie mai mare decit 0 !', ; denpr CHAR(30) ; 4
CHECK(SUBSTR(denpr,1,1)=UPPER(SUBSTR(denpr,1,1))) ; ERROR 'Prima literea din denumirea produsului este'+ CHR (13)+; 'obligatoriu majuscula!' ,; um CHAR(10),; grupa CHAR(15); CHECK(SUBSTR(grupa,1,1)=UPPER(SUBSTR(grupa,1,1))) ; ERROR 'Prima literea din grupa este'+ CHR (13)+; 'obligatoriu majuscula!' ,; procTVA NUMBER(3,2); DEFAULT 0.28,; imagine GENERAL; ) ********** Tabelul 7 CREATE TABLE gestiuni (; gestiune CHAR(4); PRIMARY KEY,; den_gest CHAR(25); CHECK(SUBSTR(den_gest,1,1)=UPPER(SUBSTR(den_gest,1,1))) ; ERROR 'Prima literea din denumirea produsului este'+ CHR (13)+; 'obligatoriu majuscula!' ,; adresa CHAR(40) ; NULL; CHECK(SUBSTR(adresa,1,1)=UPPER(SUBSTR(adresa,1,1))) ; ERROR 'Prima literea din adresa este'+ CHR (13)+; 'obligatoriu majuscula!' ,; codpost CHAR(5),; telefon CHAR(10); NULL,; cnp CHAR(14),; email CHAR(20) NULL,; FOREIGN KEY codpost TAG codpost REFERENCES localitati TAG codpost,; FOREIGN KEY cnp TAG cnp REFERENCES persoane TAG cnp; ) ********** Tabelul 8 CREATE TABLE facturi(; nrfact NUMBER(8); PRIMARY KEY,; datafact DATE; DEFAULT DATE(); CHECK(BETWEEN(datafact,{^2001/01/01},{^2010/12/31})) ; ERROR 'Data fact se afal in interv 1 ian 2001-31 decembrie 2010!', ; gestiune CHAR(4) NOT NULL,; codcl NUMBER(6),; obs CHAR(50) NULL,; FOREIGN KEY gestiune TAG gestiune REFERENCES gestiuni TAG gestiune,; FOREIGN KEY codcl TAG codcl REFERENCES clienti TAG codcl; ) ********** Tabelul 9 CREATE TABLE liniifact(; nrfact NUMBER(8),; linie NUMBER(2); 5
CHECK(linie>0),; ERROR 'Codul linie trebuie sa fie mai mare decit 0 !', ; codpr NUMBER(6),; cantitate NUMBER(10),; pretunit NUMBER(12),; PRIMARY KEY STR(nrfact,8)+STR(linie,2) TAG primaru, ; FOREIGN KEY nrfact TAG nrfact REFERENCES facturi TAG nrfact,; FOREIGN KEY codpr TAG codpr REFERENCES produse TAG codpr; ) ********** Tabelul 10 CREATE TABLE incasari(; codinc NUMBER(8); PRIMARY KEY,; datainc DATE; DEFAULT DATE(); CHECK(BETWEEN(datainc,{^2001/01/01},{^2010/12/31})) ; ERROR 'Data inc se afal in interv 1 ian 2001-31 decembrie 2010!', ; coddoc CHAR(4); CHECK(coddoc= UPPER (LTRIM (coddoc))); ERROR 'Codul documentului se scrie cu majuscule!', ; nrdoc CHAR(16),; datadoc DATE; DEFAULT DATE()-5; ) ********** Tabelul 11 CREATE TABLE incasfact(; codinc NUMBER(8),; nrfact NUMBER(8),; transa NUMBER(16) NOT NULL,; PRIMARY KEY STR(codinc,8)+STR(nrfact,8) TAG primaru, ; FOREIGN KEY codinc TAG codinc REFERENCES incasari TAG codinc,; FOREIGN KEY nrfact TAG nrfact REFERENCES facturi TAG nrfact; ) 3. MODIFICAREA STRUCTURII TABELELOR O proiectare buna in general creaza putine probleme ce tin de modificarea structurii BD. Ea de fapt ramine intacta. Cu toate acestea pot aparea unele necesitati de modificare a lungimii unui atribut, adaugarii unui atribut, stergerii unui atribut s.a. Pentru a modifica structura tabelelor de date este utilizata comanda ALTER TABLE. In cele ce urmeaza vom prezenta citeva formate de sintaxa a acestei comenzi si citeva exemple. ALTER TABLE TableName1 ADD | ALTER [COLUMN] FieldName1 FieldType [(nFieldWidth [, nPrecision])] [NULL | NOT NULL] [CHECK lExpression1 [ERROR cMessageText1]] [DEFAULT eExpression1] [PRIMARY KEY | UNIQUE] [REFERENCES TableName2 [TAG TagName1]] 6
[NOCPTRANS] [NOVALIDATE] sau ALTER TABLE TableName1 ALTER [COLUMN] FieldName2 [NULL | NOT NULL] [SET DEFAULT eExpression2] [SET CHECK lExpression2 [ERROR cMessageText2]] [DROP DEFAULT] [DROP CHECK] [NOVALIDATE] sau ALTER TABLE TableName1 [DROP [COLUMN] FieldName3] [SET CHECK lExpression3 [ERROR cMessageText3]] [DROP CHECK] [ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]] [DROP PRIMARY KEY] [ADD UNIQUE eExpression4 [TAG TagName3 [FOR lExpression5]]] [DROP UNIQUE TAG TagName4] [ADD FOREIGN KEY [eExpression5] TAG TagName4 [FOR lExpression6] REFERENCES TableName2 [TAG TagName5]] [DROP FOREIGN KEY TAG TagName6 [SAVE]] [RENAME COLUMN FieldName4 TO FieldName5] [NOVALIDATE] Exemple din HELP VFP. ALTER TABLE – SQL Command Examples Exemplu 1 adauga un camp numit fax in tabela customer si permite si valori nule in acest. Exemplu 2 face din campul cust_id cheie primara a tabelei customer. Exemplu 3 adauga campului quantity o regula de validare in tabela orders astfel incat valorile din acest camp trebuie sa aiba valori nenule. Exemplu 4 adauga o relatie persistenta de tipul 1:n (one-to-many) intre tabelele customer si orders bazata pe cheia primara cust_id din tabela customer si noua cheia straina de indexare cust_id din tabela orders. Exemplu 5 sterge regula de validare a campului quantity din tabela orders. Exemplu 6 sterge relatia persistenta dintre tabelele customer si orders, dar pastreaza indexul cust_id al tabelei orders. Exemplu 7 adauga un camp numit fax2 in tabela customer si nu permite valori nule pentru acest camp. Noua structura a tabelei este afisata. Doua clauze ALTER COLUMN sunt utilizate pentru a permite campului sa contina valori nule si seteaza valoarea implicita la valoarea ‘null’. De remarcat ca sunt necesare clauze multiple ALTER COLUMN pentru a schimba mai mult de o proprietate a unui camp intr-o singura comanda ALTER TABLE. Noul camp este sters apoi din tabela pentru a aduce tabela la starea initiala. * Exemplu 1 SET PATH TO (HOME(2) + 'Data\') && seteaza calea catre tabele ALTER TABLE customer ADD COLUMN fax c(20) NULL * Exemplu 2 ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id
7
ALTER TABLE customer ALTER COLUMN cust_id c(5) PRIMARY KEY * Example 3 ALTER TABLE orders; ALTER COLUMN quantity SET CHECK quantity >= 0; ERROR "Quantities must be non-negative" * Example 4 ALTER TABLE orders; ADD FOREIGN KEY cust_id TAG cust_id REFERENCES customer * Example 5 ALTER TABLE orders ALTER COLUMN quantity DROP CHECK * Example 6 ALTER TABLE orders DROP FOREIGN KEY TAG cust_id SAVE * Example 7 CLEAR ALTER TABLE customer ADD COLUMN fax2 c(20) NOT NULL DISPLAY STRUCTURE ALTER TABLE customer; ALTER COLUMN fax2 NULL; ALTER COLUMN fax2 SET DEFAULT .NULL. ALTER TABLE customer DROP COLUMN fax2
In cazul exemplului de baza de date generata de noi Adaugarea atributului DataNast – adica data nasterii in tabela PERSOANE se realizeaza astfel ALTER TABLE PERSOANE ADD DataNast DATE Redenumirea unui atribut, il poate face doar VFP printere cele mai renumite SGBD-uir ALTER TABLE PERSOANE RENAME COLUMN DataNast TO DataNasterii Stergerea unui atribut, sa zicem pe cel pe care tocmai l-am adaugat se va face prin comanda ALTER TABLE PERSOANE DROP COLUMN DataNasterii Modificarea tipului/lungimii unui atribut ALTER TABLE PERSOANE ALTER COLUMN Prenume CHAR(21) ALTER TABLE PERSOANE ALTER COLUMN Nume CHAR(17) Adaugarea/modificarea valorii implicite ALTER TABLE PERSOANE ALTER COLUMN Sex SET DEFAULT ‘F’ Se definesc valorile prin Default, deoarece se presupune ca exista mai multe femei decat barbati. Interzicerea valorilor nule pentru atributul Sex se executa dupa comanda. ALTER TABLE PERSOANE ALTER COLUMN Sex NOT NULL Adaugarea/anularea restrictiilor se executa tot cu ajutorul ALTER 8
Restrictiile
– PRIMARY KEY – cheie primara – UNIQUE – unice – FOREIGN KEY – cheie straina – CHECK – de verificare a unei conditii.
De exemplu dezactivarea cheii primare se efectuiaza cu ajutorul comenzii ALTER TABLE PERSOANE DROP PRIMARY KEY Pentru restabilirea keiei ALTER TABLE PERSOANE ADD PRIMARY KEY (CNP) && numele campului in paranteze Atentie!!! NOVALIDATE – se permite operarea unor modificari ale structurii chiar daca acestea nu ar verifica integritatea datelor. 4.COMENZI DE ACTUALIZARE Trei sunt comenzile de actualizare – INSERT, UPDATE si DELETE A. Adaugarea unei linii/inscrieri Sintaxa INSERT INTO dbf_name [(fname1 [, fname2, ...])] VALUES (eExpression1 [, eExpression2, ...]) sau INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR Descriere: INSERT INTO dbf_name Specifica numele tabelei la care noua inregistrare este adaugata. dbf_name poate include si calea de directoare si poate fi chiar o expresie. Daca tabela specificata nu este deschisa in memorie, ea va fi deschisa exclusive intr-o noua zona de lucru si noile inregistrari adaugate la tabela. Noua zona de lucru nu este selectata; zona de lucru curenta ramane selectata. Daca tabela specificata este deschisa in memorie, comanda INSERT adauga noi inregistrari la tabela. Daca tabela este deschisa intr-o zona de lucru alta decat zona de lucru curenta, nu va fi selectata dupa ce inregistrarea este adaugata; zona de lucru curenta ramane selectata. [(fname1 [, fname2 [, ...]])] specifica numele campurilor din noua inregistrare in care valorile sunt inserate. VALUES (eExpression1 [, eExpression2 [, ...]]) specifica lista de valori inserate in noua inregistrare. Daca se omit numele de campuri, trebuie date valorile in ordinea in care apar in structura tabelei. Daca SET NULL este ON, comanda INSERT – SQL incearca sa insere valoarea null in orice camp nespecificat in clauza VALUES.
9
FROM ARRAY ArrayName Specifica tabloul de date din care vor fi preluate valorile inserate intr-o noua inregistrare. FROM MEMVAR specifica ca continutul variabilelor de memorie sunt inserate in campuri cu acelasi nume ca variabilele. Daca o variabila cu acelasi nume nu exista, campul este lasat gol. EXEMPLE:
INSERT INTO employee (emp_no, fname, lname, officeno) VALUES (3022, "John", "Smith", 2101) CLOSE DATABASES CLEAR OPEN DATABASE (HOME(2) + 'Data\testdata') USE Customer && deschide in memorie tabela customer * Distribuie inregistrarea curenta in variabile de memorie SCATTER MEMVAR * Copie structura tabelei curente in tabela din exemplu COPY STRUCTURE TO cust2 * Insera o inregistrare din variabilele de memorie INSERT INTO cust2 FROM MEMVAR SELECT CUST2 BROWSE * Inchide si sterge tabela data ca exemplu USE DELETE FILE cust2.dbf
Atentie!!! Ordinea valorilor din clauza VALUES trebuie sa fie identica cu cea declarata la crearea (sau modificarea structurii) tabelelor. Modificarea este posibila numai prin enumararea dupa numele tabelei, a atributelor ce vor primi valorile specificate
B. STERGEREA UNOR RANDURI / INREGISTRARI Marcarea pentru stergere. Sintaxa DELETE [Scope] [FOR lExpression1] [WHILE lExpression2] [IN nWorkArea | cTableAlias] [NOOPTIMIZE]
10