10 SQL1 Crearea BD Si Actualizare
-
Upload
zavateciprian -
Category
Documents
-
view
254 -
download
0
description
Transcript of 10 SQL1 Crearea BD Si Actualizare
![Page 1: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/1.jpg)
SQL (1)Prezentare generală. Tipuri de date. Crearea tabelelor.
Declararea restricţiilor. Actualizarea tabelelor
Universitatea Al.I. Cuza Iași Facultatea de Economie și Administrarea AfacerilorDepartamentul de Contabilitate, Informatică economică și Statistică
Marin Fotache
![Page 2: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/2.jpg)
Tutoriale video
10a_Creare (sub) scheme si tabele PostgreSQL
http://1drv.ms/1oCrcin 10b_(sub)scheme_PostgreSQLhttp://1drv.ms/1oCrohD
![Page 3: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/3.jpg)
SQL - Structured Query Language
• Este cel mai răspândit limbaj de lucru cu bazele de date
• Bazat pe algebra relaţională• Folosit de:
• Administratorii BD: crearea tabelelor, declararearea restricţiilor
• Dezvoltatorii de aplicaţii (script-uri, proceduri, funcţii etc.)• Neinformaticieni – extragerea de informaţii ad-hoc, din
bazele de date
• Adulat - “Intergalactic Dataspeak” (M.Stonebraker)
• Detestat – vezi NoSQL, studenţii de la CIG
![Page 4: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/4.jpg)
Origini/contribuţii
Anii ‘70, gândit ca limbaj de lucru pentru baze de date gestionate cu System R (IBM)
◦ Nu Edgar Codd, fondatorul modelului relaţional, ◦ ci Donald Chamberlain şi Raymond Boyce
Bazat nu pe calcul relaţional (cum a propus Codd, ci pe algebra relaţională
![Page 5: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/5.jpg)
Organisme de standardizare SQL
ISO – International Organisation for Standardization, ANSI – American National Standard Institute, AFNOR – Association Francaise de Normalisation, OMG – Operational Management Committee, DBSSG – Database Systems Study Group, PRIS-TG – Predictable Real-time Information Management Task Group
. ..
. ..
X 3 H4 X 3 H7 X 3 T3 X 3 H2
P R IS -T G
D B S S G
O M C
X 3
A N S I A F N O R ...
IS O Internaţional
Naţional
Comitetul de stardarde acreditate
(Franţa)
Sisteme de prelucrare a informaţiilor
Comitetul pentru Managementul Operaţional
Comitete tehnice, grupuri de studiu
Grupuri pe activităţi
IRDS OIM ODP SQL
![Page 6: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/6.jpg)
Standarde SQL
ANSI-1986, ISO-1987 SQL-89 (SQL1) SQL2 sau SQL-92 – 600 pagini
◦ Entry – intrare, de bază (SQL-89 corectat)
◦ Intermediate – intermediar
◦ Full – deplin.
SQL:1999 – 2000 pagini SQL:2003 SQL:2008 SQL: 2011
![Page 7: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/7.jpg)
Orientări SQL:1999, SQL:2003, SQL:2008,
SQL:2011Definirea şi gestionarea obiectelor complexe şi persistente: Generalizare şi specializare Moşteniri multiple Polimorfism Încapsulare Tipuri de date definite de utilizator Expresii privind interogări recursive şi
instrumente adecvate de administrare a datelor
![Page 8: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/8.jpg)
SQL furnizeaza suport pentru:
Definirea datelor Consultarea BD Manipularea datelor din bază Controlul accesului Partajarea bazei între mai mulţi utilizatori
ai acesteia Menţinerea intergrităţii BD
![Page 9: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/9.jpg)
Atuuri ale SQL
Independenţa de producător Portabilitate Este standardizat Bazat pe modelul relaţional Limbaj de nivel înalt Răspunsuri la înterogări simple, ad-hoc Suport programatic pentru accesul la BD Permite multiple imagini asupra datelor
bazei Permite definirea dinamică a datelor Suport pentru arhitecturi client-server
![Page 10: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/10.jpg)
Comenzi SQL
![Page 11: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/11.jpg)
Câteva tipuri de date SQL
• SMALLINT: numere întregi de lungime redusă• INTEGER sau INT: întregi (9 poziţii, 32 biţi)• BIGINT: numere întregi mari• NUMERIC(p,s) sau DECIMAL(p,s)• FLOAT: virgulă mobilă (20 poziţii ptr. mantisă),• CHAR(n) sau CHARACTER(n): (max. 240),• VARCHAR(n) sau CHAR VARYING(n): şir de
caractere de lungime variabilă (max. 254),• DATE: dată calendaristică,• TIME: ora etc.,• TIMESTAMP: an, lună, zi, oră, minutul,
secundă, plus o fracţiune de secundă• INTERVAL dintre două date/momente
![Page 12: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/12.jpg)
Tipuri de date numerice în PostgreSQL
Tip de date Descriere Interval de valori
smallint întregi de mici dimensiuni De la -32768 la +32767
integer Întregi De la -2147483648 la +2147483647
bigint întregi de mari dimensiuni De la -9223372036854775808 la 9223372036854775807
decimal numere cu parte întreagă şi parte fracţionară
Până la 131072 cifre pentru partea întreagă; până la 16383 cifre pentru partea fracţionară;
numeric numere cu parte întreagă şi parte fracţionară
Până la 131072 cifre pentru partea întreagă; până la 16383 cifre pentru partea fracţionară;
real numere cu parte întreagă şi parte fracţionară Precizie de 6 cifre fracţionare
double precision
numere cu parte întreagă şi parte fracţionară Precizie de 15 cifre fracţionare
serial Numere întregi autoincremenate De la 1 la 2147483647
bigserial Numere întregi mari autoincremenate De la 1 la 9223372036854775807
![Page 13: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/13.jpg)
Alte tipuri de date în PostgreSQLTip de date Descriere
boolean Valori logice (true/false)
character varying [ (n) ] Şir de caractere de lungime variabilă limitată
character [ (n) ] Şir de caractere de lungime fixă
date Dată calendaristică (year, month, day)
interval [ fields ] [ (p) ] Interval de timp
money Mărimi monetare
text Şir de caractere de lungime variabilă nelimitată
time [ (p) ] [ without time zone ] Ora din zi (fără zonă temporală)
time [ (p) ] with time zone Ora din zi, inclusiv zona temporală
timestamp [ (p) ] [ without time zone ]
Dată calendaristică şi oră din zi (fără zonă temporală)
timestamp [ (p) ] with time zone Dată calendaristică şi oră din zi, inclusiv zonă temporală
![Page 14: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/14.jpg)
Crearea tabelelor şi declararea atributelor
CREATE TABLE facturi (NrFact NUMERIC(8),DataFact DATE
DEFAULT CURRENT_DATE,CodCl NUMERIC(6) DEFAULT 1001,Obs VARCHAR(50) )
![Page 15: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/15.jpg)
Valori nenule
CREATE TABLE facturi (NrFact NUMERIC(8) NOT NULL,DataFact DATE
DEFAULT CURRENT_DATE NOT NULL,
CodCl NUMERIC (6) DEFAULT 1001 NOT NULL,
Obs VARCHAR(50) )
![Page 16: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/16.jpg)
Cheie primară/unicitate (1)
CREATE TABLE facturi (NrFact NUMERIC(8) NOT NULL PRIMARY KEY,DataFact DATE DEFAULT CURRENT_DATE
NOT NULL,CodCl NUMERIC(6) DEFAULT 1001 NOT NULL,Obs VARCHAR(50) ) ;
CREATE TABLE judete (Jud CHAR(2) PRIMARY KEY,Judet VARCHAR(25) NOT NULL UNIQUE, Regiune VARCHAR (15) ) ;
![Page 17: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/17.jpg)
Cheie primară/unicitate (2)
CREATE TABLE liniifact (NrFact NUMERIC(8) NOT NULL,Linie SMALLINT NOT NULL, CodPr NUMERIC(6) NOT NULL,Cantitate NUMERIC(10) NOT NULL,PretUnit NUMBER (12),
PRIMARY KEY (NrFact, Linie), UNIQUE (NrFact, CodPr) )
![Page 18: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/18.jpg)
Restricţii referenţiale (1)
CREATE TABLE liniifact (
NrFact NUMERIC(8) NOT NULL,
Linie SMALLINT NOT NULL,
CodPr NUMERIC(6) NOT NULL,
Cantitate NUMERIC(10) NOT NULL,
PretUnit NUMBER (12),
PRIMARY KEY (NrFact, Linie),
UNIQUE (NrFact, CodPr),
FOREIGN KEY NrFact
REFERENCES facturi (NrFact),
FOREIGN KEY CodPr
REFERENCES produse (CodPr) )
![Page 19: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/19.jpg)
Restricţii referenţiale (2)
CREATE TABLE liniifact (NrFact NUMERIC(8) NOT NULL
REFERENCES facturi (NrFact),Linie SMALLINT NOT NULL, CodPr NUMERIC(6) NOT NULL
REFERENCE produse (CodPr),Cantitate NUMERIC(10) NOT NULL,PretUnit NUMBER (12),
PRIMARY KEY (NrFact, Linie), UNIQUE (NrFact, CodPr) )
![Page 20: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/20.jpg)
Restricţii referenţiale (3)
CREATE TABLE liniifact (NrFact NUMERIC(8) NOT NULL,Linie SMALLINT NOT NULL, CodPr NUMERIC(6) NOT NULL,Cantitate NUMERIC(10) NOT NULL,PretUnit NUMBER (12),
PRIMARY KEY (NrFact, Linie), UNIQUE (NrFact, CodPr),FOREIGN KEY NrFact REFERENCES facturi (NrFact)
ON DELETE RESTRICT ON UPDATE CASCADE,FOREIGN KEY CodPr REFERENCES produse (CodPr)
ON DELETE RESTRICT ON UPDATE CASCADE )
![Page 21: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/21.jpg)
Restricţii utilizator (1)
CREATE TABLE facturi (NrFact NUMERIC(8) NOT NULL PRIMARY KEY,DataFact DATE DEFAULT CURRENT_DATE
NOT NULLCHECK (DataFact >= DATE '2011-08-
01' AND DataFact <= DATE'2015-12-
31'),CodCl NUMERIC(6) DEFAULT 1001 NOT NULL
REFERENCES clienti(CodCl) ON DELETE RESTRICT ON UPDATE CASCADE,
Obs VARCHAR(50) )
![Page 22: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/22.jpg)
Restricţii utilizator (2)
CREATE TABLE LINIIFACT (
NrFact NUMERIC(8) NOT NULL,
Linie SMALLINT NOT NULL CHECK (Linie > 0),
CodPr NUMERIC(6) NOT NULL,
Cantitate NUMERIC(10) NOT NULL,
PretUnit NUMBER (12),
PRIMARY KEY (NrFact, Linie),
UNIQUE (NrFact, CodPr),
FOREIGN KEY NrFact REFERENCES facturi (NrFact)
ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY CodPr REFERENCES produse (CodPr)
ON DELETE RESTRICT ON UPDATE CASCADE )
![Page 23: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/23.jpg)
Script PostgreSQL- creare tabele (1)
DROP TABLE incasfact ;
DROP TABLE incasari ;
DROP TABLE liniifact ;
DROP TABLE facturi ;
DROP TABLE produse ;
DROP TABLE persclienti ;
DROP TABLE persoane ;
DROP TABLE clienti ;
DROP TABLE coduri_postale ;
DROP TABLE judete ;
![Page 24: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/24.jpg)
Script PostgreSQL- creare tabele (2)
CREATE TABLE judete ( jud CHAR(2) CONSTRAINT pk_judete PRIMARY KEY CONSTRAINT ck_jud
CHECK (jud=LTRIM(UPPER(jud))), judet VARCHAR(25) CONSTRAINT un_judet UNIQUE CONSTRAINT nn_judet NOT NULL CONSTRAINT ck_judet
CHECK (judet=LTRIM(INITCAP(judet))), regiune VARCHAR(15) DEFAULT 'Moldova' CONSTRAINT ck_regiune
CHECK (regiune IN ('Banat', 'Transilvania', 'Dobrogea',
'Oltenia', 'Muntenia', 'Moldova'))
) ;
![Page 25: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/25.jpg)
Script PostgreSQL- creare tabele (3)
CREATE TABLE coduri_postale ( codpost CHAR(6) CONSTRAINT pk_cp PRIMARY KEY CONSTRAINT ck_codpost
CHECK (codpost=LTRIM(codpost)), loc VARCHAR(25) CONSTRAINT nn_loc NOT NULL CONSTRAINT ck_loc
CHECK (loc=LTRIM(INITCAP(loc))), jud CHAR(2) DEFAULT 'IS' CONSTRAINT fk_cp_jud REFERENCES
judete(jud) ) ;
![Page 26: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/26.jpg)
Script PostgreSQL- creare tabele (4)
CREATE TABLE clienti ( codcl NUMBER(6) CONSTRAINT pk_clienti PRIMARY
KEY CONSTRAINT ck_codcl CHECK (codcl > 1000), dencl VARCHAR (30)
CONSTRAINT ck_dencl CHECK(SUBSTR(dencl,1,1) =
UPPER(SUBSTR(dencl,1,1))), codfiscal CHAR(9) CONSTRAINT ck_codfiscal
CHECK (SUBSTR(codfiscal,1,1) = UPPER(SUBSTR(codfiscal,1,1))),
adresa VARCHAR(40) CONSTRAINT ck_adresa_clienti CHECK (SUBSTR(adresa,1,1) =
UPPER(SUBSTR(adresa,1,1))), codpost CHAR(6) CONSTRAINT fk_clienti_cp
REFERENCES coduri_postale(codpost), telefon VARCHAR2(10) ) ;
![Page 27: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/27.jpg)
Script PostgreSQL - creare tabele (5)
CREATE TABLE produse ( codpr NUMBER(6) CONSTRAINT pk_produse PRIMARY KEY CONSTRAINT ck_codpr CHECK (codpr > 0), denpr VARCHAR(30) CONSTRAINT ck_denpr
CHECK (SUBSTR(denpr,1,1) = UPPER(SUBSTR(denpr,1,1))),
um VARCHAR(10), grupa VARCHAR(15) CHECK (SUBSTR(grupa,1,1)
=UPPER(SUBSTR(grupa,1,1))),
procTVA NUMBER(2,2) DEFAULT .24 ) ;
![Page 28: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/28.jpg)
Script PostgreSQL- creare tabele (6)
CREATE TABLE facturi ( nrfact NUMBER(8) CONSTRAINT pk_facturi PRIMARY KEY, datafact DATE DEFAULT SYSDATE CONSTRAINT ck_datafact
CHECK (datafact >= DATE‘2012-08-01’ AND datafact <= TO_DATE('31/12/2019',
'DD/MM/YYYY')),codcl NUMBER(6) CONSTRAINT fk_facturi_clienti
REFERENCES clienti(codcl) , Obs VARCHAR2(50) ) ;
![Page 29: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/29.jpg)
Script PostgreSQL- creare tabele (7)
CREATE TABLE liniifact ( nrfact NUMBER(8) CONSTRAINT fk_liniifact_facturi
REFERENCES facturi(nrfact), linie NUMBER(2) CONSTRAINT ck_linie CHECK (linie >
0), codpr NUMBER(6)
CONSTRAINT fk_liniifact_produse REFERENCES produse(codpr),
cantitate NUMBER(10), pretunit NUMBER (12), CONSTRAINT pk_liniifact
PRIMARY KEY (nrfact,linie) ) ;
![Page 30: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/30.jpg)
Modificarea structurii (1)
Adăugarea unui nou atribut ALTER TABLE PERSOANE ADD DataNast DATE
Modificarea tipului/lungimii unui atribut ALTER TABLE PERSOANE MODIFY (Nume
VARCHAR2(21))
Adăugarea/modificarea valorii implicite ALTER TABLE PERSOANE MODIFY (Sex DEFAULT 'F') ALTER TABLE PERSOANE MODIFY (Sex DEFAULT
NULL)
Adăugarea/anularea restricţiilor ALTER TABLE PERSOANE DROP PRIMARY KEY ALTER TABLE PERSOANE ADD PRIMARY KEY (CNP)
![Page 31: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/31.jpg)
Ştergerea tabelelor
DROP <nume tabelă> < comportament la ştergere >
unde:
< comportament la ştergere > : : = = RESTRICT | CASCADE
![Page 32: 10 SQL1 Crearea BD Si Actualizare](https://reader036.fdocumente.com/reader036/viewer/2022062305/55cf8e40550346703b902cd6/html5/thumbnails/32.jpg)
Actualizarea tabelelor Adăugarea unei linii INSERT INTO tabelă [(atribut1, atribut2, ….)]
VALUES (valoare_atribut1, valoare_atribut2, ….)
Ştergerea liniilor DELETE FROM nume-tabelă WHERE predicat
DELETE FROM FACTURI WHERE NrFact = 1122
Modificarea valorilor unor atribute UPDATE tabelă SET atribut1 = expresie1
[, atribut2= expresie2 ….] WHERE predicat
UPDATE CLIENTI SET Telefon = ‘032-313131’ WHERE CodCl = 1001 ;
UPDATE PRODUSE SET ProcTVA = .19 ;