andreony
[admin]
 Din: bucharest
Inregistrat: acum 18 ani
Postari: 1062
|
|
MySQL
• Elemente introductive baze de date
Aproape toate aplicatiile au nevoie de mijloace prin care sa stocheze sau sa prelucreze
date. Lucurul cu fisierele, desi ofera un acces mai rapid, au anumite lipsuri, dintre
care cel mai important este accesul simultan la acelasi fisier.
O baza de date este o colectie de date care este organizata incat continutul poate fi cu
usurinta accesat si manipulat. Prin aceasta organizare bazele de date sunt printre cele
mai viabile solutii pentru stocarea datelor.
Un DBMS (Database Management System) reprezinta software –ul folosit pentru a
stoca, culege si modifica datele dintr-o baza de date.
O baza de date aranjeaza datele in tabele, ce cuprind linii si coloane. O inregistrare
reprezinta o linie din tabel, ce contine mai multe coloane.
Presupunand ca doriti sa stocati informatii cu privire la ultima vizita a utilizatorilor pe
site, puteti construi o tabela de forma :
User ID UserName UserCountry Page Last Access
unreal John Wayne Romania Index.php 2004-03-22
tournament John Travolta France Forum.php 2004-03-28
unreal John Wayne Romania Index.php 2004-03-29
O astfel de abordare este total neeficienta, pentru ca anumite informatii despre
utilizator se stocheaza la fiecare vizita. Din aceasta cauza, multe informatii sunt
redundante.
Astfel a aparut conceptul de normalizare, ce cuprinde 5 forme normale, din care 3
sunt foarte importante.
Cele 3 reguli de normalizare se pot reduce la :
- eliminarea informatiilor redundante prin creerea de tabele individuale
- crearea unui tabel separat pentru toate informatiile ce au legatura intre ele
- specificarea unei camp unic (cheie primara) pentru fiecare tabel
Prin simpla impartire a tabelei in 2 subtabele, considerand ca userid –ul este cheie
primara, avem o baza de date normalizata :
Tabela 1.
User ID UserName UserCountry
unreal John Wayne Romania
tournament John Travolta France
unreal John Wayne Romania
Tabela 2.
User ID Page Last Access
unreal index.php 2004-03-22
tournament forum.php 2004-03-28
unreal index.php 2004-03-29
Trecerea prin formele normale 1, 2 si 3
Presupunem ca dorim sa facem o lista a CD-urilor pe care le avem in biblioraft.
Campurile importante sunt : Titlu CD, Formatie, Casa Producatoare, Melodii.
Titlu CD Formatie Casa Producatoare Melodii
O entitate se afla in prima forma normala cand toate atributele sale au o singura
valoare.
Daca ne uitam in coloanele tabelei noastre, observam ca avem o serie de melodii in
coloana cu acelasi nume.
Ce reprezinta coloana Melodii ? Reprezinta date ce trebuie grupate in alta tabela, ce
trebuie sa aiba o legatura logica cu tabela ce cuprinde informatii despre CD.
Astfel tabela noastra se imparte in 2 :
Tabela 1. – cd -uri
Titlu CD Formatie Casa Producatoare
Tabela 2. - melodii
Numele Durata
Fiecare entitate trebuie sa aiba un identificator unic. Astfel, tabelele noastre devin :
Tabela 1.
CD ID Titlu CD Formatie Casa Producatoare
Tabela 2.
ID Melo Nume Durata
Relatiile descriu modul in care 2 sau mai multe tabele se leaga intre ele.
Pentru exemplul nostru : Un CD are mai multe melodii. Sau invers, Mai multe melodii
sunt cuprinse pe un CD.
Relatia este de tipul 1 la mai multe (one-to-many).
O entitate se afla in a doua forma normala daca se afla deja in prima forma si toate
atributele ce nu o identifica sunt dependente total de identificatorul unic al
inregistrarii.
Daca un atribut nu depinde total de indentificatorul unic, locul lui este in alta tabela.
In exemplul nostru, Westlife poate fi formatia de care apartin 2 CD –uri. Prin urmare,
Formatie apartine altei tablele.Noua structura va fi :
Tabela 1.
CD ID Titlu CD Casa Producatoare
Tabela 2.
ID Melo Nume Durata
Tabela 3.
ID Formatie Formatie
In continuare, tabelele noastre nu sunt in forma normala 2. Casa Producatoare are
eticheta pe mai multe CD –uri. Prin urmare ea face parte din alta tabela.
Baza de date va fi :
Tabela 1.
CD ID Titlu CD
Tabela 2.
ID Melo Nume Durata
Tabela 3.
ID Formatie Formatie
Tabela 4.
ID Casa Casa Producatoare
O entitate se afla in a 3 –a forma normala daca este deja in forma 2 si nici un atribut
ce nu identifica entitatea nu este dependent de un alt atribut ce nu identifica
entitatea.
Tabela 4.
ID Casa Casa Producatoare Adresa Localitate Cod Postal
Daca dorim sa vedem datele despre Casa Producatoare vom avea probleme cu Localitate
si Codul Postal. Localitatea depinde de codul Postal.
Tabela 4.
ID Casa Casa Producatoare
Tabela 5.
Cod Postal Localitate
Instalarea MySQL :
�� ��
Sintaxa SQL :
SQL (Structured Query Language) este un limbaj universal adoptat de mai toate
SGBD –urile actuale.
• SELECT este folosit pentru a regasi inregistrari sau coloane din inregistrari.
SELECT
[ALL | DISTINCT]
select_expression,...
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name}
[ORDER BY {col_name | formula | position}
[ASC | DESC] ,...]
[LIMIT [offset,] row_count | row_count OFFSET offset]
EX:
SELECT * FROM UTILIZATORI WHERE USERNAME=’GIGI’;
SELECT 1+1;
SELECT USERNAME, PASSWORD FROM UTILIZATORI WHERE USERID<16;
• INSERT este folosit pentru adaugarea de noi inregistrari.
INSERT [INTO] tbl_name(coloane) VALUES (val1,val2,..)
EX:
INSERT INTO UTILIZATORI(username,password)
VALUES (‘gigi’,’1234’);
INSERT INTO UTILIZATORI VALUES (‘12’,’gigi’,’1234’);
ATENTIE : Daca nu se specifica numele coloanelor tabelei, se
presupune ca la VALUES acoperim cu valori toate coloanele, in
ordinea in care sunt definite in tabela.
• UPDATE actualizeaza inregistrarile din tabele.
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
EX :
UPDATE persondata SET age=age+1;
UPDATE persondata SET age=age*2, age=age+1;
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
UPDATE persondata SET age=age+1 WHERE age<=40;
• DELETE sterge inregistrari din tabela.
DELETE FROM tbl_name
[WHERE where_definition][LIMIT row_count]
EX :
DELETE FROM somelog WHERE user = 'jcole' LIMIT 1
• CREATE creaza structura unei tabele.
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
EX:
CREATE TABLE `info_stud` (
`matricol` int(5) NOT NULL default '0',
`nume` varchar(40) NOT NULL default '',
`initiala` varchar(6) NOT NULL default '',
`prenume` varchar(50) NOT NULL default '',
`an` int(1) NOT NULL default '0',
`forma` char(2) NOT NULL default '')
• ALTER TABLE modifica structura unei tabele.
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER col_name ]
| CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| RENAME [TO] new_tbl_name
EX :
ALTER TABLE t1 CHANGE a b INTEGER;
ALTER TABLE t1 RENAME t2;
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT;
Tipuri de date in MySQL:
11 Column Types
MySQL supports a number of column types, which may be grouped into three categories:
numeric types, date and time types, and string (character) types. This chapter first gives
an overview of the column types available and summarizes the storage requirements for
each type, and then provides a more detailed description of the properties of the types in
each category. The overview is intentionally brief. The more detailed descriptions should
be consulted for additional information about particular column types, such as the
allowable formats in which you can specify values.
The column types supported by MySQL follow. Several of the type descriptions use these
code letters:
M
Indicates the maximum display size. The maximum legal display size is 255.
D
Applies to floating-point and fixed-point types and indicates the number of digits
following the decimal point. The maximum possible value is 30, but should be no
greater than M-2.
Square brackets (`[' and `]') indicate parts of type specifiers that are optional.
Note that if you specify ZEROFILL for a column, MySQL automatically adds the
UNSIGNED attribute to the column.
Warning: You should be aware that when you use subtraction between integer values
where one is of type UNSIGNED, the result will be unsigned! See section 12.7 Cast
Functions.
Numeric Types
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to
255.
BOOLEAN
These are synonyms for TINYINT(1). The BOOLEAN synonym was added in
version 4.1.0 In the future, full boolean type handling will be introduced in
accordance with SQL-99.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to
65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
A medium-size integer. The signed range is -8388608 to 8388607. The unsigned
range is 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The
unsigned range is 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This is a synonym for INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
• A large integer. The signed range is -9223372036854775808 to
9223372036854775807. The unsigned range is 0 to
18446744073709551615.
FLOAT(precision) [UNSIGNED] [ZEROFILL]
A floating-point number. precision can be from 0 to 24 for a single-precision
floating-point number and from 25 to 53 for a double-precision floating-point
number.
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number. Allowable values are -
1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and
2.2250738585072014E-308 to 1.7976931348623157E+308.
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
An unpacked fixed-point number. Behaves like a CHAR column: ``unpacked''
means the number is stored as a string, using one character for each digit of the
value. The decimal point and (for negative numbers) the `-' sign are not counted
in M, although space for them is reserved. If D is 0, values have no decimal point
or fractional part.
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
These are synonyms for DECIMAL. The FIXED alias was added in version 4.1.0 for
compatibility with other servers.
Date and Time Types
DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL
displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to
DATE columns using either strings or numbers. See section 11.2.1 The DATETIME,
DATE, and TIMESTAMP Types.
DATETIME
A date and time combination. The supported range is '1000-01-01 00:00:00'
to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD
HH:MM:SS' format, but allows you to assign values to DATETIME columns using
either strings or numbers. See section 11.2.1 The DATETIME, DATE, and TIMESTAMP
Types.
TIMESTAMP[(M)]
A timestamp. The range is '1970-01-01 00:00:00' to sometime in the year
2037. A TIMESTAMP column is useful for recording the date and time of an INSERT
or UPDATE operation. The first TIMESTAMP column in a table is automatically set to
the date and time of the most recent operation if you don't assign it a value
yourself. You can also set any TIMESTAMP column to the current date and time by
assigning it a NULL value.
String Types
CHAR(M) [BINARY | ASCII | UNICODE]
A fixed-length string that is always right-padded with spaces to the specified
length when stored. M represents the column length. The range of M is 0 to 255
characters (1 to 255 prior to MySQL Version 3.23). Trailing spaces are removed
when the value is retrieved.
VARCHAR(M) [BINARY]
A variable-length string. M represents the maximum column length. The range of M
is 0 to 255 characters (1 to 255 prior to MySQL Version 4.0.2). Note: Trailing
spaces are removed when the value is stored, which differs from the SQL-99
specification. VARCHAR values are sorted and compared in case-insensitive fashion
unless the BINARY keyword is given.
TINYBLOB
TINYTEXT
A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters. S
BLOB
TEXT
A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters..
MEDIUMBLOB
MEDIUMTEXT
A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1)
characters..
LONGBLOB
LONGTEXT
A BLOB or TEXT column with a maximum length of 4294967295 or 4GB (2^32 -
1) characters.
ENUM('value1','value2',...)
An enumeration. A string object that can have only one value, chosen from the list
of values 'value1', 'value2', ..., NULL or the special '' error value. An ENUM
can have a maximum of 65,535 distinct values. ENUM values are represented
internally as integers.
SET('value1','value2',...)
A set. A string object that can have zero or more values, each of which must be
chosen from the list of values 'value1', 'value2', ... A SET can have a
maximum of 64 members. SET values are represented internally as integers.
_______________________________________
------ eVoLuTiOn ------
|
|
