Post on 15-Jan-2016
description
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
Tutoriale video
10a_Creare (sub) scheme si tabele PostgreSQL
http://1drv.ms/1oCrcin 10b_(sub)scheme_PostgreSQLhttp://1drv.ms/1oCrohD
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
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ă
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
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
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
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
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
Comenzi SQL
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
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
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ă
Crearea tabelelor şi declararea atributelor
CREATE TABLE facturi (NrFact NUMERIC(8),DataFact DATE
DEFAULT CURRENT_DATE,CodCl NUMERIC(6) DEFAULT 1001,Obs VARCHAR(50) )
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) )
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) ) ;
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) )
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) )
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) )
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 )
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) )
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 )
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 ;
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'))
) ;
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) ) ;
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) ) ;
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 ) ;
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) ) ;
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) ) ;
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)
Ştergerea tabelelor
DROP <nume tabelă> < comportament la ştergere >
unde:
< comportament la ştergere > : : = = RESTRICT | CASCADE
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 ;