Cap 2.

Instructiuni SQL de administrare a structurii bazelor de date1Subseturi de instructiuni:

DDLData Definition Language - configurare infrastructura (BD, tabele)

DMLData Manipulation Language - utilizarea informatiilor din BD

DCLData Control Language - accesibilitate, securitate

InfoAcademy2 Data Definition Language

Operatii cu:

A-Baze de date (BD)

B-Tabele InfoAcademy3A.Operatii cu BD:

1.Crearea de BD

CREATE DATABASE nume_BD;

sau CREATE SCHEMA nume_BD;

CREATE DATABASE IF NOT EXISTS magazin CHARACTER SET latin1 COLLATE latin1_general_ci;InfoAcademy42. Stabilirea BD curente (de lucru)

SELECT * FROM magazin.useri;

sau

USE magazin;SELECT * FROM useri;

- toate referintele ulterioare la nume de tabele, daca nu contin numele bazei de date vor fi considerate ca apartin acestei baze de date;

- unele instructiuni opereaza automat pe aceasta baza de date.

Exemplu:SHOW TABLES; - va afisa tabelele bazei de date curente

InfoAcademy53. Stergerea de BD

DROP DATABASE IF EXISTS magazin;- operatie ireversibila

DROP SCHEMA IF EXISTS magazin;InfoAcademy64. Vizualizarea BD existente

SHOW DATABASES;

SHOW DATABASES LIKE m%;

- returneaza toate BD care incep cu m; - % inlocuieste zero sau mai multe caractere ; - _ inlocuieste un singur caracter. Ex: magazi_.

5. Vizualizarea parametrilor unei BD

SHOW CREATE DATABASE magazin;

InfoAcademy76. Modificarea parametrilor unei BD

ALTER DATABASE magazin CHARACTER SET utf8;

ALTER DATABASE magazin COLLATE utf8_romanian_ci;

ALTER DATABASE magazin CHARACTER SET utf8 COLLATE utf8_romanian_ci.

InfoAcademy8R E C A P I T U L A R E

Crearea BD;Utilizarea;Adresa absoluta, adresa relativa;Modificarea parametrilor BD;Stergerea BD;Vizualizarea listei cu BD existente pe server;Vizualizarea parametrilor unei BD.

Exercitiul 2.01InfoAcademy9B. Operatii cu tabele

Notiuni preliminare:

Ce presupune crearea unei tabele

- numele tabelei;- formatul de stocare - storage engine;- definirea coloanelor;- definirea indecsilor (optional).InfoAcademy10Storage engine

Se refera la:- modalitatea de stocare a datelor (HDD / RAM);- facilitatile oferite TR / RI;- performante;

Sunt utilizate frecvent in MySQL:

- InnoDB(default)RI / TRHDDviteza mica, siguranta mare- MyISAMHDDviteza mica, siguranta mare- MEMORYRAMviteza mare, siguranta micaSHOW ENGINES;- lista cu storage engine acceptate de serverInfoAcademy11Tipuri de date MySQL (introducere)

- Numerice

- intregi- TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT- in virgula fixa- DECIMAL- in virgula mobila- FLOAT si DOUBLE

- Data/timp- DATE, TIME, DATETIME, YEAR si TIMESTAMP

- Sir de octei - BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB si LONGBLOB

- Sir de caractere - CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT si LONGTEXT

- Seturi de valori discrete - ENUM si SETInfoAcademy12Definitii de coloane

numele coloanei - regulile enunate la capitolul privind denumirea;

t ipul de date al coloanei - unul dintre tipurile de date enumerate anterior;

eventuale detalii suplimentare - asociate tipului de date al coloanei (ex: numr de zecimale pentru DECIMAL, lungime maxima pentru VARCHAR, valori posibile pentru ENUM/SET etc.);

eventuali modificatori ai coloanei - ex: NULL, DEFAULT, UNSIGNED;

NULL - indica absenta unei valori, nu reprezinta valoare propriu zisa.

NOT NULL - col nu permite NULLInfoAcademy13Clauza DEFAULT

- stabilita explicitEx: Culoare VARCHAR(50) NOT NULL DEFAULT 'rou'

- stabilita automat, de ctre server, atunci cand in definiia coloanei nu este prezenta clauza DEFAULT. Daca definiia coloanei permite NULL, valoarea implicita va fi automat NULL;

- absenta - in modul non-strict, serverul va insera automat o valoare din oficiu pe acea coloana, aleasa in funcie de tipul de date al coloanei (coloanele de tip numeric vor primi valoarea zero, cele de tip sir de caractere - irul vid "", cele de tip ENUM prima valoare din mulimea de valori permise etc). In modul strict va fi generata o eroare.

Exercitiul 2.02

InfoAcademy141. Crearea de tabele

I.- specificnd explicit proprietile tabelei si lista definiiilor de coloane;II.- copiind structura unei tabele deja existente;III- pornind de la rezultatul unei interogri.

I.CREATE TABLE nume_tabela(definitie_coloana_l, definitie_coloana2,...)

CREATE TABLE Magazie(Produs VARCHAR(50),Categorie VARCHAR(20),U/M VARCHAR(10),Pret DECIMAL (5,2)) ENGINE InnoDB;InfoAcademy15II.CREATE TABLE tabela_noua LIKE nume_tabela_veche CREATE TABLE Depozit_Buc LIKE Magazie;

Sunt copiate structura si indecsii tabelei, NU si cheile externe si inregistrarile

III.CREATE TABLE nume_tabela SELECT coloane FR0M alte_tabele

CREATE TABLE Lactate SELECT produs, U/M, pret FROM magazie WHERE categorie='lactate'

Poate extrage coloane si inregistrari din mai multe tabele, nu sunt copiai indecii si alti parametri ai tabelei.

In toate situatiile este recomandabil sa folosim clauza: IF NOT EXIST

InfoAcademy16Crearea unei tabele de tip TEMPORARY

La crearea unei tabele se poate specifica clauza TEMPORARY.

Rezultatul este crearea unei tabele ce difer de una obinuita prin urmtoarele caracteristici principale:

-exista atta timp cat conexiunea clientului care a creat-o este inca activa. Serverul va terge automat tabela la inchiderea conexiunii;- este vizibila numai pentru clientul care a creat-o. Mai muli clieni pot crea tabele temporare cu nume identice;

InfoAcademy17Crearea unei tabele de tip TEMPORARY (continuare)

CREATE TEMPORARY TABLE produse1 (Nume VARCHAR(100), Pret INT);CREATE TEMPORARY TABLE produse2 like produse1;CREATE TEMPORARY TABLE produse3 SELECT * FROM produse1;

O tabela temporara poate primi acelai nume cu una obinuita deja existent. In acest caz, cea existenta va fi mascata", utilizatorul nemaiavand acces la ea pana cand cea temporara dispare. O eventuala instruciune DROP TABLE va aciona in astfel de cazuri asupra tabelei temporare, neafectand-o pe cea persistenta.

Exercitiul 2.03InfoAcademy18Vizualizarea listei de tabele:SHOW TABLES;

SHOW TABLES FROM magazin;SHOW TABLES FROM magazin LIKE '%user%';

Exercitiul 2.04

InfoAcademy1919Vizualizarea caracteristicilor tabelei:Exista diferite instruciuni ce pot fi folosite pentru a determina structura si caracteristicile unei tabele:SHOW TABLE STATUS. Afieaz informaii despre tabelele non-temporare din baza de date curenta. Cu clauza suplimentara LIKE se pot filtra numele tabelelor listate.

DESCRIBE NumeTabela sau SHOW COLUMNS FROM NumeTabela. Instruciunea afieaz lista coloanelor tabelei impreuna cu caracteristicile lor (nume, tip de date, indeci, acceptare NULL, valoare default).

SHOW CREATE TABLE NumeTabela. Afieaz instruciunea exacta ce poate fi folosita pentru a crea o tabela identica cu cea curenta. Exercitiul 2.05InfoAcademy20202. Stergerea sau golirea unei tabele

DROP TABLE IF EXISTS produse;

DELETE FROM produse; de regula selectiv (clauza WHERE)TRUNCATE produse; sterge toate inregistrarile

Exercitiul 2.06

InfoAcademy21213. Modificarea de tabele

a)-Redenumire

ALTER TABLE nume_tabela RENAME nume_nou;

RENAME TABLE tab_1 TO tab_x [, tab_2 TO tab_y, ... ] ; - permite mai multe redenumiri intr-o singura instructiune;

b)-Schimbarea de caracteristici

ALTER TABLE produse ENGINE MyISAM;

c)-Stergerea de coloane

ALTER TABLE produse DROP C0LUMN denumire;

InfoAcademy2222d)-Modificarea definitiilor de coloane

ALTER TABLE Persoane ADD COLUMN definitie_coloana [pozitie];

ALTER TABLE people ALTER COLUMN an_nastere SET DEFAULT year(curdate());

ALTER TABLE people ALTER COLUMN an_nastere DROP DEFAULT;

ALTER TABLE NumeTabela CHANGE NumeColoana DefinitieNouaColoana;* definitie noua coloana include si numele, chiar daca nu se schimbaEx:ALTER TABLE people CHANGE email mail VARCHAR(200); - schimba denumirea

ALTER TABLE people CHANGE email email VARCHAR(250); - pastreaza denumirea, schimba doar dimensiunea pentru tipul de dateInfoAcademy2323e)-Adaugarea de coloane

O coloana poate fi adugata:

ALTER TABLE produse ADD COLUMN definitie_coloana [poziie];

- la sfritul listei de coloane, in cazul in care nu se specifica poziia de adugare- la inceput, daca poziia este FIRST- imediat dupa coloana specificata, daca poziia este de forma AFTER nume_coloana

Exercitiul 2.07InfoAcademy24244.Importul de date din fisiere de tip .TXT

LOAD DATA LOCAL INFILE 'cale...' INTO TABLE tablenameOptionale[CHARACTER SET charset_name][FIELD TERMINATED BY ', '][ENCLOSED BY ' ' ][LINES TERMINATED BY '\r\n' - pentru WIN respectiv '\n' - pentru linux (implicit)] [IGNORE n LINES](Col1, Col2, ... , Colx) sau (@a,@b, ... , @x)Daca nu stim daca fisierul este WIN sau LINUX il deschidem cu notepad si winpad. Daca fisierele sunt identice fisierul este in WIN, altfel LINUX.Exista statii pe care functioneaza diferit. Faceti invers ('\r\n' in loc de '\n').InfoAcademy2525Recapitulare:

Alegerea STORAGE ENGINE afecteaza?Care sunt cele trei moduri de creare a unei tabele?

Care sunt principalele caracteristici ale unei tabele temporare?

Avem o tabela: Persoane. Cum facem sa stergem toate inregistrarile din ea?

Ce facem ca sa aflam numele unei tabele cand nu-l stim exact?Cum adaugam o coloana la o tabela existenta? Cum o modificam?

Cum creem o baza de date? Cum o stergem?

Enumerati principalele tipuri de date.InfoAcademy26Uctiune stergeti o baza de date?26