wap-klub
Junior 2
Data inscrierii: 12/Noi/2010
Mesaje: 34
|
|
 Limbajul MySQL-functii si comenzi |
 |
MySQL are o interfata SQL extraordinar de complexa, dar si intuitiva si usor de invatat. Capitolul asta se ocupa cu prezentarea detaliata a unor comenzi diverse (cum se folosesc ele ai aflat in Utilizare MySQL), modele 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 kitul 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 utilizând caracterele wildcard:%(orice numar de caractere) si _ (un solitar caracter).
expr LIKE pat [ESCAPE ‘escape-char’]
mysql> select ‘jay greenspan’ like ‘jay%’;
‘jay greenspan’ like ‘jay%’
1
REGEXP
Face o cautare întrebuințând o formă 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 întâiul 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 utilizate 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 }
|
|
