Limbajul MySQL-functii si comenzi

MySQL are o interfata SQL foarte complexa, dar si intuitiva si usor de invatat. Capitolul acesta se ocupa cu prezentarea detaliata a unor comenzi diverse (cum se folosesc ele ai aflat in Utilizare MySQL), tipuri de date si functii de care vei avea nevoie ca sa folosesti MySQL eficient.

Structura limbajului folosit

Siruri de caractere
Un sir de caractere (string) este o secventa de caractere aflata intre apostrofuri sau ghilimele ('un sir' sau "un sir"). Intr-un sir de caractere, unele secvente au un inteles special. Aceste secvente incep cu '\'. MySQL recunoste urmatoarele secvente de escape: 

\0
0 (NULL)
\'
Apostrof
\"
Ghilimele.
\b , \\
Backslash
\n
Linie noua
\r
Carriage return
\t
Tab.
\z
Control-Z (sfarsit de fisier)
\%
Procent `%'
\_
Undersore `_'
Daca vrei sa incluzi ghilimele si apostrofuri in siruri de caractere: 
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

Numere
Numerele intregi sunt reprezentate ca o secventa de cifre, iar float-urile folosesc '.' ca separator decimal.

Valoarea NULL
Valoarea NULL inseamna 'Nici o informatie' si difera de valoarea 0 pentru intregi si de sirul gol pentru siruri de caractere.

Numele folosite pentru baze de date, tabele, indecsi, coloane si alias-uri
Un nume poate contine caractere alfanumerice din setul curent de cacactere (implicit ISO-8859-1 Latin1). Un nume poate incepe cu orice caracter valid in nume. Un nume poate incepe si cu un numar, dar nu poate fi compus numai din numere. Nu poti folosi '.' in nume deoarece e folosit pentru a referi coloanele din tabele. sensibilitatea la majuscule sau minuscule pentru numele alese depinde de sistemul de operare (case sensitive in Unix si case insensitive in Windows). Oricum, in Windows nu poti referi aceeasi baza de date in aceeasi interogare cu un nume in cazuri diferite. Urmatoare interogare nu merge:
mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Numele de coloane sunt case insensitive. Alias-urile sunt case sensitive.

Variabile definite de utilizator
Se pot defini cu @nume_variabila. Ele nu trebuiesc initializate, contin de la inceput valoare NULL si pot stoca un integ, un numar real, un float sau un sir de caractere.
Poti seta o variabila la o anumita valoare cu:
SET @variable= { integer expression | real expression | string expression }[,@variable= ...].
sau cu: 

select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

Comentarii
MySQL suporta cometariile #pana la sfarsit de linie, --pana la sfarsit de linie si /*linii multiple*/. 

mysql> select 1+1;     # Comentariu pana la sfarsitul linie
mysql> select 1+1;     -- Comentariu pana la sfarsitul liniei
mysql> select 1 /* comentariu in-line */ + 1;
mysql> select 1+
/*
comentariu
multi-linii
*/
1;

Cuvinte rezervate

action add aggregate all
alter after and as
asc avg avg_row_length auto_increment
between bigint bit binary
blob bool both by
cascade case char character
change check checksum column
columns comment constraint create
cross current_date current_time current_timestamp
data database databases date
datetime day day_hour day_minute
day_second dayofmonth dayofweek dayofyear
dec decimal default delayed
delay_key_write delete desc describe
distinct distinctrow double drop
end else escape escaped
enclosed enum explain exists
fields file first float
float4 float8 flush foreign
from for full function
global grant grants group
having heap high_priority hour
hour_minute hour_second hosts identified
ignore in index infile
inner insert insert_id int
integer interval int1 int2
int3 int4 int8 into
if is isam join
key keys kill last_insert_id
leading left length like
lines limit load local
lock logs long longblob
longtext low_priority max max_rows
match mediumblob mediumtext mediumint
middleint min_rows minute minute_second
modify month monthname myisam
natural numeric no not
null on optimize option
optionally or order outer
outfile pack_keys partial password
precision primary procedure process
processlist privileges read real
references reload regexp rename
replace restrict returns revoke
rlike row rows second
select set show shutdown
smallint soname sql_big_tables sql_big_selects
sql_low_priority_updates sql_log_off sql_log_update sql_select_limit
sql_small_result sql_big_result sql_warnings straight_join
starting status string table
tables temporary terminated text
then time timestamp tinyblob
tinytext tinyint trailing to
type use using unique
unlock unsigned update usage
values varchar variables varying
varbinary with write when
where year year_month zerofill
Urmatoarele cuvinte pot fi folosite si pentru nume de tabele, coloane etc.:
ACTION BIT DATE ENUM NO TEXT TIME TIMESTAMP

Functii

Functii pentru compararea de siruri
LIKE
Cauta un 'pattern' intr-o expresie folosind caracterele wildcard:%(orice numar de caractere) si _ (un singur caracter).
expr LIKE pat [ESCAPE ‘escape-char’] 

mysql> select ‘jay greenspan’	like ‘jay%’;
‘jay greenspan’ like ‘jay%’
1

REGEXP
Face o cautare folosind o expresie string si o expresie regulara. Returneaza 1 (a gasit) sau 0(n-a gasit).
expr REGEXP pat 
mysql> select name from guestbook where name regexp ‘^j.*g’;

STRCMP
STRCMP(expr1,expr2)
Returneaza 0 daca sirurile sunt egale, -1 daca primul sir e mai mic si 1 daca sirul 2 e mai mic ca sirul 1.

Functii pentru controlul programului
IFNULL
IFNULL(expr1,expr2)
Daca expr1 nu e NULL, returneaza expr1, altfel ret. expr2.
mysql> select ifnull(1/0, ‘exp 1 is null’);

+--------------------------------+
| ifnull(1/0,   ‘exp 1 is null’) |
+--------------------------------+
|   exp 1 is null                |
+--------------------------------+
1 row	in set (0.00 sec)

IF
IF(expr1,expr2,expr3)
mysql> select if(name like ‘jay%’, ‘Yes’, ‘No’) as ‘Jay Names’ -> from guestbook;

Functii matematice
ABS (modulul unui numar)
SIGN (semnul:-1 negativ, 1 pozitiv, 0 egal cu 0)
MOD (rstul impartirii)
FLOOR (cea mai mare valoare intreaga mai mica ca un numar)
CEILING (cea mai mica valoare intreaga mai mare ca un numar)
ROUND (un numar rotunjit la o valoare intreaga)
TRUNCATE (trunceaza un numar la un anumit numar de zecimale) 

mysql> select	truncate(8.53,0),	truncate(8.43,0),	truncate(8.534,2);

|truncate(8.53,0)|truncate(8.43,0)|truncate(8.534,2)|
|        8       |         8      |        8.53     |
1 row in	set (0.05 sec)

EXP (exponentiala unui numar)
LOG (logaritm)
LOG10 (logaritm zecimal)
POW(X,Y) (X la puterea Y)
SQRT (radacina patrata)
PI (aproximarea lui PI)
COS (cosinus)
SIN (sinus)
TAN (tangenta)
ACOS (arccosinus)
ASIN (arcsinus)
ATAN (arctangenta)
COT (cotangenta)
RAND (valoare la intamplare intre 0 si 1.0)
LEAST (cel mai mic argument) 
mysql> select	least(2,7,9,1);

|least(2,7,9,1)|
|       1      |
1 row in set (0.00	sec)

GREATEST (cea mai mare valoare dintre argumente)
DEGREES (converteste din radiani in grade)
RADIANS (converteste din grade in radiani)

Functii pentru siruri de caractere
ASCII(str) (codul ASCII) 

mysql> select ascii(‘\n’);

| ascii(‘\n’)|
|    10      |
1 row in set (0.00 sec)

ORD(str)
CONV(N,from_base,to_base)
BIN(N) (converteste N in baza 2)
OCT(N) (converteste N in baza 8)
HEX(N) (converteste N in baza 16)
CHAR(N1,N2,...) (sirul de caractere format din reprezentarile ASCII ale argumentelor intregi)
CONCAT(str1,str2,...) (concateneaza doua siruri de cacactere)
LENGTH (lungimea unui sir)
LOCATE(substr,str [,pos]) (cauta pozitia unui subsir intr-un sir) 
mysql> select locate(‘s’, ‘mysql functions’) as example1,
-> locate(‘s’, ‘mysql functions’,4) as example2;
|example1|example2|
|   3    |    15  |
1 row in set (0.00 sec)

INSTR(str,substr) (pozitia subsirului in sir)
LPAD(str,len,padstr) (sirul padstr e adaugat la stanga lui srt)
RPAD
LEFT(str,len) (primele 'len' caractere din str)
RIGHT(str,len)
SUBSTRING(str,pos[,len]) sau
MID(str,pos,len) 
mysql> select	mid(‘mysqlfunctions’,6,8);
| mid(‘mysqlfunctions’,6,8)|
| function                 |
1 row in set (0.00 sec)

SUBSTRING_INDEX(str,delim,count) 
mysql> select substring_index(‘mysqlfunctionsmysql’, ‘fu’,	1);

| substring_index(‘mysqlfunctions’, ‘fu’, 1) |
| mysql                                      |
1 row in set (0.00 sec)

mysql> select substring_index(‘mysqlfunctionsmysql’, ‘fu’,	-1);

| substring_index(‘mysqlfunctionsmysql’, ‘fu’, -1)  |
| nctionsmysql                                      |
1 row in set (0.00 sec)

LTRIM
RTRIM
TRIM
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
REPLACE(str,from_str,to_str)
SOUNDEX(str) (sirul in format SOUNDEX)
SPACE(N) (un spatiu de N pozitii)
REPEAT(str,count) (repeta str de 'count' ori)
REVERSE (inverseaza un sir)
INSERT(str,pos,len,newstr) 
mysql> select	insert(‘mysqlfunctions’, 6,2,’FU’);
+                                  +
|insert(‘mysqlfunctions’, 6,2,’FU’)|
+                                  +
|mysqlFUnctions                    |
+                                  +
1 row in set (0.44 sec)

FIELD(str,str1,str2,str3,...) (pozitia str in cadrul argumentelor)
LCASE (lowercase)
UCASE (uppercase)
LOAD_FILE (continutul unui fiser ca sir

Functii pentru data calendaristica si timp
DAYOFWEEK 

mysql> select	dayofweek(‘2001-01-01’);
+                        +
| dayofweek(‘2001-01-01’)|
|          2             |
1 row in set (0.33 sec)

WEEKDAY
DAYOFMONTH
DAYOFYEAR
MONTHNAME
QUARTER (semestrul din care face parte data)
WEEK(date [, first]) (first=0-saptamana incepe duminica, first=1-saptamana incepe luni)
YEAR
YEARWEEK (anul si saptamana in format YYYYWW)
HOUR
MINUTE
SECOND
PERIOD_ADD(P,N) (adauga N luni la perioda P)
PERIOD_DIFF(P1,P2)
DATE_ADD
DATE_SUB(date,INTERVAL expr type)
TO_DAYS
FROM_DAYS
DATE_FORMAT 
Specificatori pentru DATE_FORMAT
%M	Luna (January÷December)
%W	Ziua (Sunday÷ Saturday)
%D	Numarul zilei(1st, 2nd, 3rd, etc.)
%Y	Anul AAAA
%y	Anul AA
%a	Numele abreviat al zilei(Sun..Sat)
%d	Numarul zilei lunii(00..31)
%e	Numarul zilei lunii(0..31)
%m	Numarul lunii(01..12)
%c	Numarul lunii(1..12)
%b	Numele lunii(Jan..Dec)
%j	Numarul zilei in an(001..366)
%H	Ora (00..23)
%k	Ora (0..23)
%h	Ora (01..12)
%I	Ora (01..12)
%l	Ora (1..12)
%i	Minutele(00..59)
%r	Timpul, 12-ore (hh:mm:ss [AP]M)
%T	Timpul, 24-ore (hh:mm:ss)
%S	Secundele(00..59)
%s	Secundele (00..59)
%p	AM sau PM
%w	Ziua saptamanii (0=Sunday..6=Saturday)
%U	Saptamana (0..53)
%u	Saptamana (0..53)
%V	Saptamana (1..53)
%v	Saptamana (1..53)
%X	Anul pentru saptamana
%x	Anul pentru saptamana
%%	Caracterul %
Exemplu:
mysql> select date_format(‘2001-01-01’,	‘%W %M %d, %Y’);

| date_format(‘2001-01-01’, ‘%W %M	%d, %Y’) |
| Monday January 01, 2001                    |
1 row in set (0.00 sec)

TIME_FORMAT
CURDATE
CURTIME
NOW (data si ora curenta YYYY-MM-DD HH:MM:SS)

Alte functii diverse
Database (numele bazei de date curente)
User (numele utilizatorului curent)
VERSION (versiunea MySQL)
LAST_INSERT_ID (ultima valoare inserata intr-un camp 'auto_increment')

Sintaxa completa a celor mai folosite comenzi MySQL

SELECT 

SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
        [WHERE where_definition]
        [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
        [HAVING where_definition]
        [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
        [LIMIT [offset,] rows]
        [PROCEDURE procedure_name]
        [FOR UPDATE | LOCK IN SHARE MODE]]

INSERT 

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
sau  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
sau  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression, ...

ALTER TABLE 

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  sau    ADD [COLUMN] (create_definition, create_definition,...)
  sau    ADD INDEX [index_name] (index_col_name,...)
  sau    ADD PRIMARY KEY (index_col_name,...)
  sau    ADD UNIQUE [index_name] (index_col_name,...)
  sau    ADD FULLTEXT [index_name] (index_col_name,...)
  sau	 ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  sau    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  sau    CHANGE [COLUMN] old_col_name create_definition
  sau    MODIFY [COLUMN] create_definition
  sau    DROP [COLUMN] col_name
  sau    DROP PRIMARY KEY
  sau    DROP INDEX index_name
  sau    DISABLE KEYS
  sau    ENABLE KEYS
  sau    RENAME [TO] new_tbl_name
  sau    ORDER BY col
  sau    table_options

UPDATE 

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1, [col_name2=expr2, ...]
    [WHERE where_definition]
    [LIMIT #]

LOAD DATA INFILE 

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

DELETE 

DELETE [LOW_PRIORITY | QUICK] FROM table_name
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT rows]
sau
DELETE [LOW_PRIORITY | QUICK] table_name[.*] [table_name[.*] ...] FROM
table-references [WHERE where_definition]

CREATE INDEX 

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... )

CREATE TABLE 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  sau    PRIMARY KEY (index_col_name,...)
  sau    KEY [index_name] (index_col_name,...)
  sau    INDEX [index_name] (index_col_name,...)
  sau    UNIQUE [INDEX] [index_name] (index_col_name,...)
  sau    FULLTEXT [INDEX] [index_name] (index_col_name,...)
  sau    [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  sau    CHECK (expr)

type:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  sau    SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  sau    MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  sau    INT[(length)] [UNSIGNED] [ZEROFILL]
  sau    INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  sau    BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  sau    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  sau    DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  sau    FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  sau    DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  sau    NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  sau    CHAR(length) [BINARY]
  sau    VARCHAR(length) [BINARY]
  sau    DATE
  sau    TIME
  sau    TIMESTAMP
  sau    DATETIME
  sau    TINYBLOB
  sau    BLOB
  sau    MEDIUMBLOB
  sau    LONGBLOB
  sau    TINYTEXT
  sau    TEXT
  sau    MEDIUMTEXT
  sau    LONGTEXT
  sau    ENUM(value1,value2,value3,...)
  sau    SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
	TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MYISAM }
sau	AUTO_INCREMENT = #
sau	AVG_ROW_LENGTH = #
sau	CHECKSUM = {0 | 1}
sau	COMMENT = "string"
sau	MAX_ROWS = #
sau	MIN_ROWS = #
sau	PACK_KEYS = {0 | 1}
sau	PASSWORD = "string"
sau	DELAY_KEY_WRITE = {0 | 1}
sau      ROW_FORMAT= { default | dynamic | fixed | compressed }
sau	RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
sau	UNION = (table_name,[table_name...])
sau      DATA DIRECTORY="directsauy"
sau      INDEX DIRECTORY="directsauy"

select_statement:
	[IGNORE | REPLACE] SELECT ...  (Some legal select statement)

CREATE DATABASE 

CREATE DATABASE [IF NOT EXISTS] db_name

DROP DATABASE 

DROP DATABASE [IF EXISTS] db_name

DROP INDEX 

DROP INDEX index_name ON tbl_name

DROP TABLE 

DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]

RENAME TABLE 

RENAME TABLE tbl_name TO new_table_name[, tbl_name2 TO new_table_name2,...]

JOIN 

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
InapoiInainte
Home Introducere Instalarea Utilizare MySQL Limbajul MySQL Administarea MySQL Configurarea MySQL MySQL si PHP Quiz MySQL Linkuri utile Contacteaza-ma..