!

Introducere (sau mic ghid practic) în MySQL

Traducere de Leonte Carmen – carmen@infoiasi.ro

!

"#$%&!#'()&*+! )%)!(,*(-.$!*! ).&,*/-#$,$! ).!01234! )./)#'./!5*/'+)&'&$'!(,).!#',$! %$!(*'&$!#,$'6!

,$%($#&)7! -&)+)8'6! *! 9'8'! %)5(+'! /$! /'&$! (,).! (,*:,'5-+! 51%;+! <! #+)$.&=! 01%;+! >,$#-.*%#-&! -.$*,)! (,).!

'%*#)$,$'!#-!.*&)-.$'!/$!5*.)&*,!&$,5).'+?!$%&$6!/$!@'(&6!-.!(,*:,'5!).&$,'#&)7!#',$!($,5)&$!-&)+)8'&*,-+-)!

#*.$#&',$'!+'!-.!51%;+!%$,7$,6!,-+',$'!#*5$.8)+*,!%)!7)8-'+)8',$'!,$8-+&'&$+*,=!01%;+!(*'&$!@)!/$!'%$5$.$'!

@*+*%)&!%)!).!7',)'.&'!:,-(-,)+*,!/$!#*5$.8)!>9'&#A?B!(+'%$8)!#*5$.8)+$!).&,C-.!@)%)$,!).)&)'+!($.&,-!'!%*+)#)&'!

(,*:,'5-+-)!%'!+$!$D$#-&$!-+&$,)*,=!!

"59$+$!(*%)9)+)&'&)!/$!'#*($,),$!'!(,*:,'5-+-)!%-.&!(,$8$.&'&$!).!#$$'!#$!-,5$'8'=!!

E$.&,-!'!7)8-'+)8'!#'&$7'!*(&)-.)!'%):-,'&$!/$!51%;+!'($+'&)!-&)+)&',-+!#-!*(F)-.$'!GCCA$+(HB!

!

shell> mysql --help

!

! I'()&*+-+!(,$%-(-.$!*!).%&'+',$!*(&)5J!'!(,*:,'5-+-)!%)!$D)%&$.&'!-.-)!%$,7$,!51%;+!/)%(*.)9)+!+'!

#',$! 7'! (-&$&)! #*.$#&'=! K'#'! '#$%&! +-#,-! .-! 7'! ,$-%$%&$6! '($+'&)! +'! '/5).)%&,'&*,-+! /$! (,*:,'5=!K'#'! %$!

).&'5(+'!%'!@)&)!#A)',!K7%=!).%)7'!'#$+'6!'&-.#)!7)!%$!(,*(-.$!%)!#*.%-+&',$'!'+&*,!@,':5$.&$!/$!%($#)'+)&'&$!

/).!'#$%&!5'.-'+=!!

L*&!')#)!$%&$!/$%#,)%! ).&,$:-+!(,*#$%! '+!#*.%&,-),))! %)!5'.)(-+',))!-.$)!9'8$!/$!/'&$=!K'#'!%-.&$&)!

).&$,$%'&!/*',!).!'##$%',$'!-.$)'!/$M'!$D)%&$.&$6!'7$&)!(*%)9)+)&'&$'!/$!'!*5)&$!'#$+$!%-9#'()&*+$!#',$!/$%#,)-!

).!'5'.-.&!#,$',$'!9'8$)!/$!/'&$!%)!'!&'9$+$+*,!)5(+)#)&$=!

2)6! ($.&,-! #'! '#$%&! #'()&*+! $%&$! &*&-%)! -.-+! G$5).'5$.&$H! /$%#,)(&)7!5-+&$! '+&$! /$&'+))! (*&! @)! /$!

'%$5$.$'! ):.*,'&$=! I*.%-+&'&)! &*&-%)! %$#7$.&$+$! ,$+$7'.&$! '+$! 5'.-'+-+-)! ($.&,-! '#-5-+'$'! 5')! 5-+&*,!

).@*,5'&))!+$:'&$!/$!.*&)-.)+$!).&'+.)&$=!

!

N=O!I*.$#&',$'!%)!/$C#*.$#&',$'!/$!+'!%$,7$,B!

!

! E$.&,-!'!7'!#*.$#&'!+'!%$,7$,6!&,$9-)$6!/$!,$:-+'6!%'!@*+*%)&)!-.!.-5$!/$!-&)+)8'&*,!51%;+!).%*&)&!/$!

#$+$!5')!5-+&$!*,)!/$!*!(',*+'=!K'#'!%$,7$,-+!,-+$'8'!($!-.!'+&!#*5(-&$,!/$#'&!#$+!/$!($!#'$!7C'&)!+*:'&!#'!

&,$9-)!%($#)@)#'&!%)!.-5$+$!G:'8/$)H=!

! "($+'&)! '/5).)%&,'&*,-+! ($.&,-! '!7'! ).@*,5'!#',$! %-.&!(','5$&,)!*(&)5)! @*+*%)&)! ($.&,-! #*.$#&',$!

>#',$!$%&$!G:'8/'H6!.-5$+$!/$!-&)+)8'&*,6!(',*+'?=!P/'&'!%&)-&)6!',!&,$9-)!%'!7'!,$-%$'%#'!#*.$#&',$'!(,).B!

!

shell> mysql -h host -u user -p

Enter password: ********

!

QQQQQQQQ!,$(,$8).&'!(',*+'B!'#$'%&'!*!).&,*/-#$&)!).!5*5$.&-+!).!#',$!(,*:,'5-+!'@)%$'8'B!

! R

!

shell> mysql -h host -u user -p

Enter password: ********

!

K'#'! '#$%&! +-#,-! 7'! ,$-%)&! -,5$'8'! %'! 7)8-'+)8'&)! #'&$7'! ).@*,5'&))! ).&,*/-#&)7$! -,5'&$! /$! *!

G).7)&'&)$H!51%;+=!!

!

shell> mysql -h host -u user -p

Enter password: ********

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 459 to server version: 3.22.20a-log

Type 'help' for help.

mysql>

!

E,*5(&$,-+!&$!'.-.&'!#'!$%&$!:'&'!).!'#$%&!5*5$.&!%'C&)!(,)5$'%#'!#*5$.8)=!S.$+$!).%&'+',)!51%;+!

($,5)&!-&)+)8'&*,)+*,!%)!*!#*.$#&',$!'.*.)5'!>@','!#'!.-5$+$!/$!-&)+)8'&*,!%'!@)$!%($#)@)#'&?6!,-+',$'!@'#'./-C

%$!($!#'+#-+'&*,-+!:'8/'=!!

K'#'! '#$%&'! $%&$! %)! #'8-+! #'+#-+'&*,-+-)! K7%=6! ',! &,$9-)! #'6! ).%'+',$'! %'! @)$! (*%)9)+'! (,).! %)5(+'!

'($+',$!'!51%;+C+-)!!@','!'+&$!5$.&)-.)=!

!

shell> mysql

!

K-('!#$!'&)!,$-%)&!#*.$#&',$'!7'!(-&$&)!/$#*.$#&'!*,)#'./!/*',!&'%&'./!3STL!/-('!).7)&'&)'!51%;+=!

!

mysql> QUIT

Bye

!

"#$+'%)!+-#,-!)+!(-&$&)!*9&).$!'('%'./!&'%&$+$!I*.&,*+CK=!

! UD$5(+$+$! #$! 7*,! -,5'! 7'! 7*,! $+-#)/'! &$*,)'! #*.$#&',))! +'! %$,7$,=! S.! (*%)9)+! )./)#)-! '+! '#$%&-)!

/$5$,%!)+!(*'&$!#*.%&)&-)!(,*5(&$,-+!51%;+=!

3.2 Comenzi de intrare!

!

! V$'%):-,'&)C7'!/$! #*.$#&',$'! +'! %$,7$,!/-('! ).&,-#&)-.)+$! '.&$,)*',$=!K$*#'5/'&'!.-!7$&)! %$+$#&'!

.)#)!*!9'8'!/$!/'&$!%(,$!'!*!5'.)(-+'!(,*(,)-C8)%6!/',!7'!-,5'=!T.!'#$%&!%&'/)-!$!@*',&$!)5(*,&'.&!%'!'@+'&)!

#'&$! #$7'! /$%(,$! @*,5-+',$'! #*5$.8)+*,! ($.&,-! '! (-&$'! @'#$! '(*)! -.! %'+&! /),$#&! %(,$! #,$',$'! &'9$+-+-)6!

).&,*/-#$,$'! /'&$+*,6! %'-!/-('! #'86! '.-+',$'! '+&*,! /'&$=!"#$%&! %-9#'()&*+! $.-5$,'! *! %$,)$! /$! (,).#)())! /$!

! N

9'8'!+$:'&$!/$!#*5$.8)+$!/$!).&,',$6!(,).!@*+*%),$'!-.*,!+).))!/$!#*5'./'!>($!#',$!+$!(-&$&)!).#$,#'!($.&,-!'!

7'! @'5)+)',)8'! #-! 5*/-+! /$! +-#,-! 51%;+?=! T'&'! *! #*5'./'! %)5(+'6! #',$! %*+)#)&'! %$,7$,-+-)! %'! 7'! )./)#$!

.-5',-+!$D$5(+',-+-)!%)!/'&'!#-,$.&'=!L'%&'&)C*!/-('!$D$5(+)@)#',)+$!-,5'&*',$!%)!'('%'&)!&'%&'!U.&$,B!

!

mysql> SELECT VERSION(), CURRENT_DATE;

+--------------+--------------+

| VERSION() | CURRENT_DATE |

+--------------+--------------+

| 3.22.20a-log | 1999-03-19 |

+--------------+--------------+

1 row in set (0.01 sec)

mysql>

!

!!!!!!!!L*'&$!'#$%&$!+).))!/$!#*5'./'!%-:$,$'8'!#)&$7'!'%($#&$!(,*(,))!(,*:,'5-+-)!51%;+B!

!*!#*5'./'!#*.%&'!/$!*9)#$)!/).!G).7)&'&)'!H!51%;+6!-,5'&'!/$!GWHC/',! $D)%&'! %)!*! %$,)$!/$!$D#$(&))B#)./!

@*+*%),$'!'#$%&$)'!.-!$%&$!.$#$%','6$D$5(+-!3STL=X*5!'.$D'!%)!'+&$!(*%)9)+$!#),#-5%&'.&$!6!#$7'!5')!&),8)-=!

!).! 5*5$.&-+! ).! #',$! &'%&'&)! *! #*5'./'6! 51%;+C-+! *! &,'.%5)&$! %$,7$,C+-)! %(,$! $D$#-&)$! %)! '@)%$'8'!

,$8-+&'&$+$6!,$+-)./!'(*)!%),-+!5$%'M$+*,!).7)&'&)$!%(,$!'!7'!'&$.&)*.'!#'!$%&$!(,$:'&)&!($.&,-!*!'+&'!#*5'./'W!

!!51%;+!'@)%$'8'!%)!#*5$.8)!%-9! @*,5'!&'9$+','!>($!+).))!%)!#*+*'.$?=!E,)5'!+).)$!#*.&).$!$&)#A$&$!($.&,-!

#*+*'.$6!)',!-,5'&*',$+$!,$(,$8).&'!,$8-+&'&$+$!+'!#*5$.8)=!T.!#$$'!#$!(,)7$%&$!#*+*'.$+$6!$&)#A$&$+$!'#$%&*,'!

)./)#'!/$!,$:-+'!.-5$+$!#*+*'.$+*,!'+$%$!/).!&'9$+$+$!9'8$)!/$!/'&$=!K'#'!-,5',)&)!%'!*9&).$&)!7)8-'+)8',$'!

/*',!-.$)! $D(,$%))! %)!.-! '!-.$)! ).&,$:)! #*+*'.$!/).! &'9$+! >#-5!','&'! $D$5(+-+! -,5'&*,?!51%;+!7'!$&)#A$&'!

#*+*'.'!@*+*%)./!$D(,$%)'!).!%).$=!

!!(,*:,'5-+!(*'&$!)./)#'!).!*,)#$!5*5$.&!.-5',-+!+).))+*,!5*/)@)#'&$!%)!#$!'.-5$!/).!+).)'!/$!#*5'./'!'!

@*%&!$D$#-&'&6!#$$'!#$6!@),$%&$!#*.@$,'!*!)5':).$!/$!'.%'59+-!'%-(,'!($,@*,5'&$+*,!%$,7$,-+-)=!

"#$%&$! 7'+*,)! .-! %-.&! &*&-%)! @*',&$! (,$#)%$6! ($! /$! *! (',&$! $+$! :A)/'./-C%$! /-('! &)5(-+! #-,$.&!

:$.$,'+! %)! .-! /-('! #$+! ,$'+! '+! #'+#-+'&*,-+-)! )',! ($! /$! '+&'! (',&$! %-.&! '@$#&'&$! /$! -.))! @'#&*,)! #',$! &).! /$!

#'('#)&'&$'! %$,7$,-+-)! %)! +'&$.&'! ,$&$+$)=! I-7).&$+$! #A$)$! (*&! @)! ).&,*/-%$! ).! *,)#',$! /).&,$! #'%-&$=!

S,5'&*',$+$!+).))!/$!#*5$.8)!*!/$5*%&,$'8'B!

!

mysql> SELECT VERSION(), CURRENT_DATE;

mysql> select version(), current_date;

mysql> SeLeCt vErSiOn(), current_DATE;

!

! T'&'!*!'+&'!%$#7$.&'=!U'!/$5*.%&,$'8'!#'!(-&$&)!@*+*%)!51%;+!#'!($!-.!#'+#-+'&*,!.-5$,)#B!

!

mysql> SELECT SIN(PI()/4), (4+1)*5;

! Y

+-------------+---------+

| SIN(PI()/4) | (4+1)*5 |

+-------------+---------+

| 0.707107 | 25 |

+-------------+---------+

! I*5'./'!'.&$,)*','!@'#$!(',&$!/).!#'&$:*,)'!#$+*,!%#-,&$!G/$!*!%).:-,'!+).)$H6!/',!(*&!@)!).&,*/-%$!

5')!5-+&$!#$,).&$!#*.%$#-&)7!($!'#$$'%)!+).)$!/$!#*5'./'=!L*&!#$!&,$9-)$!%'!@'#$&)!$%&$!%'!.-!-)&'&)!%'!&'%&'&)!

GWH!/-('!@)$#',$!/).&$!$+$=!

!

mysql> SELECT VERSION(); SELECT NOW();

+--------------+

| VERSION() |

+--------------+

| 3.22.20a-log |

+--------------+

+---------------------+

| NOW() |

+---------------------+

| 1999-03-19 00:15:33 |

+---------------------+

!

! P!#*5'./'!.-!&$,9-)$!).&,*/-%'!.$'(','&!!($!*!%).:-,'!+).)$6!'%&@$+!).#'&!'#$+!&)(!/$!#*5$.8)!#',$!

/$('%$%#! +-.:)5$'! %&'./',/6! .-! %-.&! *! (,*9+$5'=! E,*:,'5-+! %$! 7'! :A)/'! ($.&,-! '! /$#)/$! +-.:)5$'!

#*5$.8)+*,! /-('! 5$.&)-.$'! GWH! %)! .-! /-('! #'('#)&'&$! +).)$)! ).! %).$=! I-! '+&$! #-7).&$6! 51%;+C-+! '##$(&'!

).&,*/-#$,$'!#*5$.8)+*,!).!%)%&$5!+)9$,6!/',!.-!+$!7'!%)!$D$#-&'!/$#'&!/-('!,$#-.*'%&$,$'!GWH=!

! T'&'!*!#*5'./'!%)5(+'!).&,*/-%'!($!5')!5-+&$!+).))B!

!

mysql> SELECT

-> USER()

-> ,

-> CURRENT_DATE;

+--------------------+--------------+

| USER() | CURRENT_DATE |

+--------------------+--------------+

| joesmith@localhost | 1999-03-18 |

+--------------------+--------------+

!

! Z

T.!'#$%&!$D$5(+-!*9%$,7'&)!#-5!(,*5(&$,-+!5*/)@)#'!51%;+[! ).! C[! ).!5*5$.&-+! ).!#',$! &,$#$! +'!

-,5'&*',$'!+).)$=!E,).!'#$%&!(,*#$/$-!51%;+C-+!)./)#'!@'(&-+!#'!.-!'!,$#-.*%#-&!).#'!*!#*5'./'!@).'+)8'&'!%)!

'%&$'(&'! ,$%&-+! '#$%&$)'=!I*.%)/$,'&)C+! /,$(&! -.! (,)$&$.! ($.&,-! #'! $+! 7'! @)! '#$+'! #',$! 7'! 7'! '&,':$! '&$.&)'=!

"%&@$+!7$&)!%&)!#',$!$%&$!-,5'&*,-+!('%!'%&$(&'&!/$!(,*:,'5!/$! +'!K7%=!K'#'!7'! ,'8:'./)&)! ).!(,)7).&'!-.$)!

#*5$.8)!&'%&'&)!\#B!

!

mysql> SELECT

-> USER()

-> \c

mysql>

!

! 2)!')#)!*9%$,7'&)!(,*5(&$,-+]!V$7).$!+'!#*5'./'!).)&)'+'!/-('!#$!'&)!&'%&'&!\#!)./)#'./!#'!(,*:,'5-+!

$%&$!:'&'!($.&,-!*!.*-'!#*5'./'=!

! T.!-,5'&*,-+!&'9$+!%-.&!$7)/$.&)'&$!#'&$7'!/).&,$!(*%)9)+$+$!5$%'M$C(,*5(&$,!%)!).&$+$%-,)+$!+*,B!

!

mysql> Gata pentru o noua comanda

-> Asteapta o noua linie dintr-o comanda multipla

'> Asteapta un nou rand colectand un sir inceput cu (‘'’).

"> Asteptand pentru un nou rand colectand un sir inceput cu (‘"’).

!

! P!#*5'./'!%)5(+'!G%).:+$!+).$H!7'!@)!).!5*/!'##)/$.&'+!($,#$(-&'!#'!@))./!-.'!5-+&)(+'!/'#'!$%&$!

*5)%'!(,$#)8',$'!GWH=!T.!'#$%&!#'8!(,*5(&$,-+!7'!'.-.&'!C[6!&'%&'&)!GWH!%)!(,*:,'5-+!7'!$D$#-&'!#*5'./'=!

!

mysql> SELECT USER()

->

!

! K'#'!7'!#*.@,-.&'&)!#-!'#$'%&'!%)&-'&)$!>/'#'!%-.&$&)!%):-,)!#'!'&)!).&,*/-%!*!#*5'./'!/',!%).:-,-+!

,'%(-.%!($!#',$C+!(,)5)&)!$%&$!C[?6!5')!5-+&!#'!%):-,!51%;+!'%&$'(&'!(&,$#)8',$'!GWH=!!

!

mysql> SELECT USER()

-> ;

+--------------------+

| USER() |

+--------------------+

| joesmith@localhost |

+--------------------+

! ^

_[!$%&$!@*+*%)&'!).!#'8-+!%),-,)+*,=! T.!51%;+!&$,5$.))!-.-)!%),!%-.&!).&,*/-%)!).&,$!`!%'-!H!>$D$5(+-!

`A$++*a! %)! G:**/91$H?=!I'./! (,*5&$,-+! 7'! )./)#'! `[! %'-! G[! ).%$'5.'! #'! '&)! ).#+-%! ).! #*5'./'! -.! %),! #$!

).#$($!#-!`!%'-!H!/',!'&)!*5)%!,$($&',$'!%$5.-+-)!:,'@)#!/).!@).'+-+!%),-+-)!GWH=!T.!#'8-+!).!#',$!$%&$!7*,9'!/$!

-.!%),!5-+&)(+-!($!5')!5-+&$!,'./-,)!&*&-+!$%&$!).!*,/).$6!/',!/$!#$+$!5')!5-+&$!*,)!(,*5(&$,-+!',$!,*+-+!/$!'!

7'!'7$,&)8'!'%-(,'!!#','#&$,-+-)!*5)%B!

!

mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;

">

! K'#'!'&)!).&,*/-%!'#$%&'!#*5'./'!&)(!2U4UIL6!&'%&'&)!UbLUV!%)!'%&$(&'&)!,$8-+&'&-+6!7$&)!7$/$'!#'!

.-! ',$! %'! %$! ).&'5(+$!.)5)#=! T.! +*#! %'!7'! ).&,$9'&)! /$! #$!/-,$'8'! '&'&! *9%$,7'&)! )./)#)-+!G[!7'! %-:$,$'8'!

@'(&-+!#'!%),-+!'! @*%&! ).#A$)'&=!I$!@'#$&)! ).!'#$'%&'!%)&-'&)$c!E-&$&)! '.-+'!#*5'./'W!*,)#-56!*(&)-.$'! \#!.-!

$%&$!(,$@$,'9)+'!).!'#$%&!#'86!($.&,-!#'!51%;+!)+!7'!).&$,(,$&'!/,$(&!(',&$!'!%),-+-)!'@+'&!).!/$,-+',$=!

! K'#'!).!%#A)596!).&,*/-#$&)!#','#&$,-+!:,'@)#!#$!#*.@),5'!).#A$)$,$'!%),-+-)!(-&$&)!&'%&'!-+&$,)*,!\#B!

!

mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;

"> "\c

mysql>

! E,*5(&$,-+!7'!'@)%'!/$!'%&'!/'&'!51%;+[!)./)#'./!#'!$%&$!:'&'!($.&,-!*!.*-'!#*5'./'=!

! !

! 3.3 Crearea si manipularea unei baze de date

!

! "#-5!#'!7C'&)! ).%-%)&!5*/'+)&'&)+$!/$!).&,*/-#$,$!'!#*5$.8)+*,!$%&$!&)5(-+!%'!'##$%'&)!*!9'8'!/$!

/'&$=!

!!!!!!!!!!!!!2'!(,$%-(-.$5!#'!'7$&)!#)&$7'!!'.)5'+$!/$!#*5('.)$!>5)#'!7*'%&,'!5$.'M$,)$?!%)!#'!7C',!%'!&).$&)!*!

5)#'!$7)/$.&'!'!5')!5-+&*,!&)(-,)!/$!/'&$!).!#$$'!#$!+$!(,)7$%&$=!

!!!!!!!!!!!!!E-&$&)! %'! @'#$&)! '#$%&! +-#,-! #,)./! *! %$,)$! /$! &'9$+$! ($.&,-! ('%&,',$'! >5*.)&*,)8',$'?! %)! ,$%($#&)7!

#*5(+$&',$'!).@*,5'&))+*,!/*,)&$=!X$&)!(-&$'!,'%(-./$!'%&@$+!+'!*!:'5'!+',:'!/$!).&,$9',)!/$%(,$!'.)5'+$+$!

7*'%&,$!/$!#'%'6!/*',!'##$%)./!).@*,5'&))+$!%&*#'&$!).!&'9$+$=!

!!!!!!!"#$'%&'!%$#&)-.$!7'!).7'&'!#-5!%'!(,*#$/'&)!($.&,-B!

!

d!I,$',$'!-.$)!9'8$!/$!/'&$!

d!I,$',$'!-.-)!&'9$+!

d!T.&,*/-#$,$'!/'&$+*,!).!&'9$+!

d!"##$%',$'!).@*,5'&))+*,!($!5')!5-+&$!#')!

d!e*+*%),$'!5')!5-+&*,!&'9$+$!#*.%$#-&)7!

!!!!!!!!!!!!!

! f

g'8'!/$!/'&$!'!5'.'M$,)$)!7'! @)!-.'!%)5(+'!>'5!+-'&C*! ).!5*/!/$+)9$,'&! !'%'?! 6!/',6!7'!)+-%&,'!#-!

%-##$%!&)(-,)!%)5)+',$!/$!&'9$+$!#-!'#*($,),$!).!%('&)-+!,$'+=!

K$!$D$5(+-!*!'%&@$+!/$!9'8'!/$!/'&$!',!(-&$'!@)!-&)+'!-.-)!@$,5)$,!($.&,-!'!&).$!$7)/$.&'!%$(&$+-+-)6!

%'-6!-.-)!7$&$,).',!($.&,-!,$:)%&,$+$!(',).&)+*,!%')=!

P,:'.)8',$'!/'&$+*,!($.&,-!5)#'!5$.'M$,)$!(,$%-(-.$!*!%$,)$!/$!#*5$.8)!%)!/'&$!%&'./',/!#',$!(*&!

@)!(,$+-'&$!/$!%)&$C-+!01234=!

E-&$&)!@*+*%)!@)$!@*,5'&-+!&',!!%'-!8)(!=!

E$.&,-!'!'@+'!9'8$+$!/$!/'&$!$D)%&$.&$!($!%$,7$,!'($+'&)!*(&)-.$'!2hPiB!

! !

+----------+

| Database |

+----------+

| mysql |

| test |

| tmp |

+----------+

!

! 4)%&'!9'8$+*,!/$!/'&$!7'!@)!(,*9'9)+!'+&'!($!#'+#-+'&*,-+!($,%*.'+W!/',6!5')!5-+&!#'!%):-,!51%;+!%)!

&$%&!'-!&*'&$!%'.%$+$!%'!%$!.-5$,$!(,).&,$!#$+$+'+&$=!

! g'8'!/$!/'&$!51%;+!$%&$!%*+)#)&'&'!)./$*%$9)!($.&,-!#'!/$%#,)$!*!%$,)$!/$!@'#)+)&'&)!-&)+)8'&*,-+-)=!

! g'8'! /$! /'&$! C&$%&! $! (,$@$,'&'! #'! %-(*,&! (,'#&)#6! #'! @*')$! /$! +-#,-! ($.&,-! /)7$,%$! ).#$,#',)!

(,$+)5).',$=!

! ]!K'#'!.-!9$.$@)#)'&)!/$!'7'.&'M-+!2hPi!K"L"g"2U2!.-!(-&$&)!7)8-'+)8'!&*'&$!9'8$+$!/$!/'&$=!

! ]!K'#'!$D)%&'!9'8'!&$%&!).#$,#'&)!%C*!'##$%'&)!'%&@$+B!

!

mysql> USE test

Database changed

!

! ]! P9%$,7'&)! #'! S2U6! #'! /$'+&@$+6! -&)+)&',-+! 3STL! .-! .$#$%)&'! GWH! >K'#'! ).%'! '&)! @).'+)8'&! /$M'!

#$,$,$'6!#*5'./'!#-!'#$%&!%$5.!*,&*:,'@)#6!.-!$%&$!.$7*)$!%'C+!%&$,:$&)6!.-!7'!'@$#&'!%&,-#&-,'!#-!.)5)#?=!

! e'#)+)&'&$'!S2U!$!-.'!%($#)'+'!/).&,C-.!(-.#&!/$!7$/$,$B!.$#$%)&'!*!#*5'./'!%)5(+'!>O!+).)$?=!

! E-&$&)! @*+*%)! %)! 9'8'! /$! /'&$! &$%&! >/$! 7,$5$! #$! '&)! '##$%'&C*?! ($.&,-! $D$5(+$+$! -,5'&*',$6! /',6!

$D)%&'!-.!).#*7$.)$.&6!(-&$&)!'7$'!%-,(,)8'!#'!/'&$+$!/).!&'9$+$!%'!@)$!5*/)@)#'&$6!*,)#$!'+&!-&)+)8'&*,!#-!'##$%!

+'!9'8'!/$!/'&$!',!(-&$'C*!@'#$=!K).!'#$%&!5*&)7!',!&,$9-)!%'!#$,$&)!'/5).)%&,'&*,-+-)!51%;+!*!9'8'!($!#*.&!

(,*(,)-=!2'!(,$%-(-.$5!#'!*!7$&)!.-5)B!5$.'M$,)$=!"/5).)%&,'&*,-+!&,$9-)$!%'!$D$#-&$!*!+).)$!/$!#*5'./'!

/$!@*,5'B!

!

mysql> GRANT ALL ON menagerie.* TO your_mysql_name;

! j

!

! !!-./$!1*-,k51%;+k.'5$!$%&$!#A)',!.-5$+$!/$!-&)+)8'&*,!'%*#)'&!K7%=!!!

!

3.3.1 Crearea si selectarea unei baze de date

!

! K'#'!'/5).)%&,'&*,-+!'!#,$'&!9'8'!#-!($,5)%)-.$'!K7%=!(-&$&)! ).#$($!%'!*!@*+*%)&)=! T.!#'8!#*.&,',!

(-&$&)!%C*!#*.%&,-)&)!%).:-,)B!

!

mysql> CREATE DATABASE menagerie;

!

! 2-9!SbTl!!/$.-5),)+$!9'8$+*,!/$!/'&$!%-.&!%$.%)9)+$!/).!(-#&-+!/$!7$/$,$!'+!(,*#$%',))!>.-!$%&$!%)!

#'8-+!#-7).&$+*,!#A$)$!234?!'=)=!7'!&,$9-)!%'!'7$&)!:,)M'!#-5!%$+$#&'&)!9'8'6!@*+*%)&)!5$.:$,)$6!.-!0$.'M$,)$!

%'-!0Ub"mUVTU=!"#$+'%)! +-#,-!$%&$!7'+'9)+! %)!($&,-!.-5$+$! &'9$+$+*,=! >2-9!iTbKPi2!'#$%&$! ,$%&,)#&))!

#'/?=!

! I,$',$'! -.$)! 9'8$! .-! ).%$'5.'! '-&*5'&! %)! %$+$#&',$'! $)W! &,$9-)$! %'! @'#$&)! '%&'! %$(','&=! E$.&,-!

'##$%',$'!(,*(,)-C8)%'!'!9'8$)!5$.'M$,)$!@*+*%)&)!#*5'./'B!

!

mysql> USE menagerie

Database changed

!

! E,*#$%-+! #,$',))!9'8$)! ',$! +*#!*! %).:-,'!/'&'W! ).! %A)59!*!(-&$&)! %$+$#&'!*,)!/$! #'&$!*,)! ).#$($&)!*!

%$/).&'!51%;+=!E-&$&)!'##$%'!(,).&,C*!#*5'./'!S2U6!#-5!'&)!(-&-&!7$/$'!'.&$,)*,=!"+&@$+6!(-&$&)!'($+'!+'!*!

+).)$!/$!#*5'./'!).!#'8-+!).!#',$'!'($+'&)!51%;+C-+=!

! 2($#)@)#'&)! /*',! .-5$+$! 9'8$)! /-('! @)$#',$! /).&,$! (','5$&,)! /$! #*5$.&',$! ($! #',$! )C'&)! (-&$'!

@-,.)8'=!UD$5(+-B!

!

shell> mysql -h host -u user -p menagerie

Enter password: ********

!

! P9%$,7'&)!#'!5$.'M$,)$!.-!$%&$!%)!(',*+'6!).!+).)'!/$!#*5'./'=!

! K'#'!7,$&)!%'!,$/),$#&)*.'&)!(',*+'!/),$#&!($! +).)'!/$!#*5'./'!/-('!*(&)-.$'!<(6!(-&$&)!%C*!@'#$&)!

/),$#&!@','!%('&))6!+)&$,$!).&$,5$/)',$!>$D=6!C(51('%%n*,/6!.-!<(!51('%%n*,/?=!

! P,)#-5!%)&-',$'!(',*+$)!($!'#$)'%)!+).)$!/$!#*5'./'!.-!$%&$!,$#*5'./'9)+'6!($.&,-!#'!*!$D(-.$&)!

'%&@$+!-.$)!$7$.&-'+$!5*/)@)#',)!>/$!#'&,$!'+&)!-&)+)8'&*,)!+*:'&)!).!'#$+'%)!#'+#-+'&*,?=!

!

3.3.2 Crearea unui tabel

!

! o

! I,$',$'!9'8$)!/$!/'&$!$%&$!(',&$'!-%*','6!/',!).!'#$%&!5*5$.&!%$+$#&'./!*(&)-.$'!2hPi!L"g4U2!

7$&)!*9%$,7'!#'!$%&$!:*'+'B!

!

mysql> SHOW TABLES;

Empty set (0.00 sec)

!

! K$! ')#)! ).#$($! G:,$-+H6! %'! /$#)/$&)! %&,-#&-,'! 9'8$)!K7%=! /$! /'&$6! /$! #$! &'9$+$! '7$&)! .$7*)$! %)! #$!

#*+*'.$!7*,!#*.&).$=!

! X$&)!/*,)!-.!&'9$+!#',$!%'!#*.&).'!#'&$!*!).,$:)%&,',$!($.&,-!*,)#',$!/).&,$!'.)5'+$+$!K7%=!T+!(-&$5!

.-5)!($&!>'.)5'+$!/$!#*5('.)$?!%)!7'!&,$9-)!%'!#*.&).'6!#$+!(-&).6!.-5$+$!@)$#',-)!'.)5'+=!E$.&,-!#'!/*',!

.-5$+$!).!%).$!.-!$%&$!@*',&$!).&$,$%'.&6!&'9$+-+!',!&,$9-)!%'!).#+-/'!%)!'+&$!).@*,5'&))?=!

! K$! $D$5(+-6! /'#'! .-! %-.&$&)! %).:-,-+! /).! @'5)+)$! #$! /$&).$! '.)5'+$! /$! #*5('.)$! '&)! (-&$'!

).,$:)%&,'6! /$! ()+/'6! (*%$%*,))! '#$%&*,! '.)5'+$=! "&)! (-&$'! /$'%$5$.$'! %'! ).#$,#'&)! %'! ).,$:)%&,'&)! #'&$7'!

,$@$,).&$!/$%#,)(&)7$!%-##).&$!'%-(,'!%($#)$)!%)!%$D-+-)!'.)5'+-+-)=!

K',!#-5!,'5'.$!#-!7',%&'c!",!(-&$'!(,$8$.&'!).&$,$%!/',!$%&$!(,$@$,'9)+!%'!.-!'($+'&)!+'!-.!&'9$+!

($.&,-! '#$'%&'! *(&)-.$=! X',%&'! %$! %#A)59'%! */'&'! #-! &,$#$,$'! '.)+*,6! #$$'! #$! ).%$'5.'! #'! ',! &,$9-)! %'!

,$'#&-'+)8'&)!/$%&-+!/$!/$%!9'8'!/$!/'&$=!T.!%#A)596!'&)!(-&$'!).,$:)%&,'6!7'+*,)!@)D$6!#-5!',!@)!/'&'!.'%&$,))=!

"%&@$+6!*,)#'./!7$&)!'7$'!.$7*)$!%'!'@+'&)!7',%&'!7$&)!@'#$!/*',!/)@$,$.&'!/).&,$!/'&'!#-,$.&'!%)!#$!).,$:)%&,'&'=!!

! 01%;+!'%):-,'!%)!@'#)+)&'&)!($.&,-!#'+#-+$+$!',)&5$&)#$!'%&@$+!).#'&!.C',!&,$9-)!%'!@)$!(,$'!/)@)#)+=!

! 2&*#',$'!/'&$+*,!/$!.'%&$,$!).!+*#-+!7',%&$)!(,*(,)-!8)%$!(,$8).&'!%)!'+&$!'7'.&'M$6!(,$#-5B!

" E-&$&)! @*+*%)! 9'8'! ($.&,-! %',#).)! #'! :$.$,',$'! -.*,! %$5.'+$! ($.&,-! '! (,$).&'5().'! 8)+$+$!

*.*5'%&)#$! '+$! '.)5'+$+*,!/$! #*5('.)$! >/'#'! '#$'%&'!*(&)-.$!7)! %$!(',$! -.'! -%*,!.')7'!.-!

-)&'&)!#'!(-&$&)!@*+*%)!).!5*/!'.'+*:!9'8'! ).!'@'#$,)W!($.&,-!'!7'!,$'5).&)!/$!()+/'!8)-'!-.-)!

#+)$.&!%'-!'%*#)'&!%)!')!&,'.%5)&$!-,',)+$!/$!,):*',$?=!

" E-&$&)! #'+#-+'! 7',%&'! %)! ,$+'&)*.'./! #-! '+&$! /'&$! /$#'&! #$'! #-,$.&'=! K$! $D$5(+-6! /'#'!

).,$:)%&,'&)! 8)-'! /$#$%-+-)! (-&$&)! #'+#-+'! #'&)! '.)! '7$'! '.)5'+-+! /$! #*5('.)$! ('.'! +'! '#$%&!

5*5$.&=!

! X'!(-&$&)!:)./)! +'!*!5-+&)&-/).$!/$!'+&$!&)(-,)!/$!).@*,5'&))!($!#',$!+$C'&)!(-&$'!).&,*/-#$!).&,C-.!

&'9$+!($&!6!/',6!#$+$!'5).&)&$!().'!'#-5!%).&!5')!5-+&!/$#)&!%-@)#)$.&$!($.&,-!$D$5(+)@)#',$B!.-5$6!(*%$%*,6!

%($#)$6!%$D6!/'&'!/$!.'%&$,$!%)!/'&'!/$#$%-+-)=!

! e*+*%)&)!*(&)-.$'!IVU"LU!L"g4U!($.&,-!'!%($#)@)#'!(','5$&,))!&'9$+-+-)=!

!

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

->species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

!

X',#A',!$%&$!*!'+$:$,$!@$,)#)&'!($.&,-!#*+*'.$+$!'@$,$.&$!b-5$+-)6!E*%$%*,-+-)6!%)!2($#)$)6!($.&,-!

#'!7'+*,)+$!+*,!7*,!7',)'!).!+-.:)5$=!4-.:)5$'!'#$%&*,!#*+*'.$!.-!&,$9-)$!%'!@)$!'#$)'%)!%)!.-!.$'(','&!/$!Rp=!

! Op

E-&$&)! '+$:$! *,)#$! +-.:)5$! ).&,$! O! %)! ORZW! *@$,&'! 5')! 5-+&! /$#'&! :$.$,*'%'=! K'#'! '&)! *(&'&! :,$%)&! %)! %$!

/*7$/$%&$!5')!&',8)-!#'!'&)!@)!'7-&!.$7*)$!/$!-.!#'5(!5')!+',:!51%;+!7'!:$.$,'!*(&)-.$'!"4LUV!L"g4U=!

! T'&'!#'&$7'!/).&,$!7'+*,)+$!#$!(*&!@)!'+$%$!($.&,-!)./)#'&*,))!%$D-+-)!).!G,$:)%&,$+$H!@)$#',-)!'.)5'+B!

G5H!6!G@H!C!G5'+$H6!G@$5'+$H=!K$%):-,!#$'!5')!%)5(+'!*(&)-.$!$%&$!(,)5'=!!

! K'&$+$!/$!.'%&$,$!%)!,$%($#&)7!/$!/$#$%!%$!).,$:)%&,$'8'!(,).!).&$,5$/)-+!*(&)-.))!K"LU=!"#-5!#'!

'&)!#,$'&!&'9$+-+6!2hPi!L"g4U2!',!&,$9-)!%'!,$+)$@$8$!#'&$7'!*(&)-.)!/$!)$%),$B!

!

mysql> SHOW TABLES;

+---------------------+

| Tables in menagerie |

+---------------------+

| pet |

+---------------------+

!

! E$.&,-!'!7$,)@)#'!/'#'!&'9$+-+!'!@*%&!#,$'&!%'-!.-!/-('!#-5!7C'&)!/*,)&!@*+*%)&)!#*5'./'!KU2IVTgU!

>'#$)'%)!#*5'./'!$%&$!7'+'9)+'!%)!#'./!'&)!-)&'&!/$!$D$5(+-!.-5$+$!-.$)!#*+*'.$?=!

!

mysql> DESCRIBE pet;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| name | varchar(20) | YES | | NULL | |

| owner | varchar(20) | YES | | NULL | |

| species | varchar(20) | YES | | NULL | |

| sex | char(1) | YES | | NULL | |

| birth | date | YES | | NULL | |

| death | date | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

!

3.3.3 Incarcarea datelor de pe suport fizic

!

! K-('!#$!'&)!#,$'&!&'9$+-+6!&,$9-)$!%'C+!G(*(-+'&)H=!UD(,$%))+$!4P"K!K"L"!%)!Tb2UVL!%-.&!&*&!#$!

7'!&,$9-)$=!

! 2'!(,$%-(-.$5!#'!).,$:)%&,',)+$!K7%=!(*&!@)!/$%#,)%$!'%&@$+B!

!

nume posesor specie sex data nasterii data decesului

e+-@@1!! ! h',*+/!! ! #'&!! @!! OooNCpRCpY!

I+'n%!! ! qn$.!! ! #'&!! 5!! OooYCpNCOf!

! OO

g-@@1!! ! h',*+/!! ! /*:!! @!! OojoCpZCON!

e'.:!! ! g$..1!! ! /*:!! 5!! OoopCpjCRf!

g*n%$,!!!!!!!!!!!!!!!!!K)'.$!! ! /*:!! 5!! OoojCpjCNO!! OooZCpfCRo!

IA),(1!! ! qn$.!! ! 9),/!! @!! OoojCpoCOO!

iA)%&+$,!! qn$.!! ! 9),/!! ! OoofCORCpo!

2+)5!! ! g$..1!! ! %.'r$!! 5!! Ooo^CpYCRo!

!

! T.!5*5$.&-+!).!#',$!'7$&)!/$C'!@'#$!#-!-.!&'9$+!G).!'+9H!#$+!5')!%)5(+-!5)M+*#!($.&,-!'C+!(*(-+'!

$%&$!'#$+'!/$!'!#,$'!-.!@)%)$,!G&D&H!'+*#'./!#'&$!-.!%),!($.&,-!@)$#',$!/).&,$!'.)5'+$+$!/$!#*5('.)$6!%)!'(*)!

).#',#'&)!#*.&).-&-+!/).!@)%)$,!).!&'9$+!(,).&,C*!%).:-,'!+).)$!/$!#*5'./'=!

! E-&$&)!#,$'!-.!@)%)$,!&$D&!G($&=&D&H!#*.&).'./!*!).,$:)%&,',$!($!@)$#',$!,'./6!'7'./!/)@$,)&$+$!7'+*,)!

%$(','&$!(,).!%('&))6!%)!/$,-+'&$!).!'#$)'%)!*,/).$!).!#',$!#*+*'.$+$!'-!@*%&!+)%&'&$!).!IVU"LU!L"g4U=!

! E$.&,-!7'+*,)+$!+)(%'!>%$D!.$#-.*%#-&6!%'-!/'&'!/$#$%-+-)!).!#'8-+!'.)5'+$+*,!'@+'&$!).#'! ).!7)'&'?!

(-&$&)!'($+'!+'!*(&)-.$'!bS44=!E$.&,-!,$(,$8$.&',$'!'#$%&*,'!).!@)%)$,-+!&$D&!@*+*%)&)!\b=!K$!$D$5(+-6!($.&,-!

iA)%&+$,!*!).,$:)%&,',$!7'!','&'!'%&@$+B!

!

nume posesor specie sex data nasterii data decesului

i)%&+$,! ! qn$.! ! 9),/! \b! OoofCORCpo! \b!

!

E$.&,-!'!&,'.%#,)$!&$D&-+!/).!@)%)$,-+!G($&=&D&H!).!&'9$+!&'%&'&)!#*5'./'B!

!

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

!

! P,)!/$!#)&$!*,)!7,$&)!%'!'/'-:'&)!).,$:)%&,',)!*(&'&)!($.&,-! Tb2UVL=I$+!5')!%)5(+-!5)M+*#!$%&$!%'!

%-(+)5$.&'&)! ! ).@*,5'&))+$! ($.&,-! @)$#',$! #*+*'.'6! ).! '#$$'%)! *,/).$! ).! #',$! '#$%&$'! '-! @*%&! +)%&'&$! ).!

IVU"LU!L"g4U=2'!(,$%-(-.$5!#'!K)'.'!(,)5$%&$!-.!.*-!A'5%&$,!.-5)&!E-@9'++=!

! b*-'!).,$:)%&,',$!@*+*%)./!-&)+)&',-+!Tb2UVL!7'!','&'!'%&@$+B!!

!

mysql> INSERT INTO pet

->VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

!

! P9%$,7'&)!@'(&-+!#'!).%),-),$'!7'+*,)+*,!%$!@'#$!).&,$!:A)+)5$+$=I-!'#$+'%)!-&)+)&',!Tb2UVL!

E-&$&)!).%$,'!7'+*,)+$!+)(%'=b-!*(&'&)!($.&,-!\b!#-5!'&)!@'#-&!#-!4P"K!K"L"=!

! K).! '#$%&! $D$5(+-! '&)! (-&-&! *9%$,7'! #'! $%&$! (,$@$,'9)+! %'! ).#',#'&)! ).)&)'+! ).,$:)%&,',$'! @*+*%)./!

-&)+)&',-+!Tb2UVL!/$#)&!4P"K!K"L"=!

! !!

! OR

3.3.4 Refacerea informatiilor dintr-un tabel!

!

! P(&)-.$'!2U4UIL!$%&$!@*+*%)&'!($.&,-!'!G$D&,':$H!).@*,5'&))+$!/).&,C-.!&'9$+=T.!@*,5'!%'!:$.$,'+'!

#*5'./'!','&'!'%&@$+B!

!

SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy

!

! GnA'&C&*!%$+$#&H!)./)#'!#$$'!#$!7,$&)!%'!7)8-'+)8'&)="#$'%&'!(*'&$!@)!/*',!-.!.-5',!/$!#*+*'.$!%'-!

&*'&$=!

! GnA)#AC&'9+$H!)./)#'!&'9$+-+!/).!#',$!7$&)!$D&,':$!).@*,5'&)'=!

!!!!!!! P&).$'!GihUVUH!$%&$!@'#-+&'&)7'=!

! G#*./)&)*.%C&*! %'&)%@1H! %($#)@)#'! #*./)&))+,$! ! ($! #',$! +).))+$! &,$9-)$! %'! +$! )./$(+).$'%#'! ! ($.&,-! '!

(-&$'!@)!G$D&,'%$H=!

!

3.3.4.Selectarea tuturor informatiilor

!

! !T'&'!@*,5'!#$'!5')!%)5(+'!/$!%$+$#&',$!'!*,)#',-)!&'9$+B!

mysql> SELECT * FROM pet;

+----------+--------+---------+------+------------+------------+

| name | owner | species | sex | birth | death |

+----------+--------+---------+------+------------+------------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Fang | Benny | dog | m | 1990-08-27 | NULL |

| Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 |

| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |

| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

| Slim | Benny | snake | m | 1996-04-29 | NULL |

| Puffball | Diane | hamster | f | 1999-03-30 | NULL |

+----------+--------+---------+------+------------+------------+

! !"#$'%&'!5*/'+)&'&$!$%&$!)./)#'&'!).!#'8-+!).!#',$!7,$&)!%'!,$7$/$&)!).&,$:-+!&'9$+!6!/$!$D=6!/-('!#$!

&*#5')!'&)!).,$:)%&,'&!%$&-+!).)&)'+!/$!/'&$=!

! E*&! '(',$'! 6! %$! ).&)5(+'6! $,*,)! ).! @)%)$,-+!K7%=!K$! $D$5(+-6!g*n%$,! '(',$! '! @)! .'%#-&!/-('!/'&'!

/$#$%-+-)!%'-!#*.%-+&'&)C7'!).@*,5'&))+$!).!*,):).'+!6%)!/$%#*($,)&)!#'!'.-+!.'%&$,))!$,'!/$!@'(&!Oojo!).!+*#!/$!

Oooj=!

! ON

! UD)%&'!#$+!(-&).!/*-'!%*+-&))!($.&,-!'!,$5$/)'!:,$%$'+'B!

!L'%&'&)! UKTL! ($.&,-! @)%)$,-+! G($&&D&H! ($.&,-! '! #*,$#&'! $,*',$'6! :*+)&)! &'9$+-+! %)! ,$).#',#'&)C+! @*+*%)./!

KU4ULU!%)!4P"K!K"L"=!

! P,)#-5!/'#'!(,*#$/'&)!'%&@$+!&,$9-)$!/$'%$5$.$'!%'!,$).&,*/-#$&)!).,$:)%&,',)+$!($.&,-!E-@@9'++=!

!V$@'#$&)!/*',!).,$:)%&,',$'!#-!(,*9+$5$!(,).&,C*!#*5'./'!SEK"LU=!

!

mysql> SET AUTOCOMMIT=1; #

mysql> DELETE FROM pet;

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";

!

! I-5!'5!','&'&!$%&$!%)5(+-!%'!$D&,':$5!s!%$+$#&'5!).&,$:-+!&'9$+=!K',6!).!5*/!.*,5'+6!.-!7$&)!'7$'!

.$7*)$!%'!@'#$&)!'%&'6!%)!.)#)!.-!$%&$!)./)#'&!5')!'+$%!#'!).!&)5(!'#$%&'!%$!$D&)./$=!

! T.! %#A)596! 7'! 7$&)! '@+'! ).! %)&-'&)'! /$! '! ,$8*+7'! *! %).:-,'! (,*9+$5'6! #'8! ).! #',$! 7'! &,$9-)! %'!

%($#)@)#'&)!#'&$7'!(,*9+$5$!)5(+)#'&$=!

! 2'!-,5'5!#'&$7'!#*5$.8)!/$!%$+$#&)$!'!-.*,!).@*,5'&))!/$%(,$!'.)5'+$+$!K7%=!

!

N=N=Y=R!2$+$#&',$'!-.*,!+).))!/).!&'9$+!

!

! E-&$&)!%$+$#&'!/*',!'.-5)&$!+).))!/).!).&,$:-+!&'9$+=!K$!$D$5(+-6!/'#'!7,$&)!%'!7$,)@)#'&)!#*,$#&-,'!

($!#',$!'&)!@'#-&C*!).!(,)7).&'!/'&$)!/$!.'%&$,$!'!+-)!g*n%$,6!%$+$#&'&)!).,$:)%&,',$'!'%&@$+B!

!

mysql> SELECT * FROM pet WHERE name = "Bowser";

+--------+-------+---------+------+------------+------------+

| name | owner | species | sex | birth | death |

+--------+-------+---------+------+------------+------------+

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+--------+-------+---------+------+------------+------------+

!

! T$%),$'!#*.@),5'!#*,$#&-,'!@'#-&'=!

! K'&$+$! %-.&! ).%$.%)9)+$! /).! (-.#&! /$! 7$/$,$'! '+! ).,$:)%&,',))! '%&@$+! ).#'&! (-&$&)! %#,)$! .-5$+$B!

G9*n%$,H6!GgPi2UVH!$&#=!V$8-+&'&-+!%*+)#)&',))! #'! @)! '#$+'%)=!E-&$&)! %($#)@)#'!#*./)&))+$!($!*,)#',$!/).&,$!

#*+*'.$6! .-! /*',! ($! '#$$'! '! .-5$+*,=!K$! $D$5(+-6! /'#'! 7,$&)! %'! %$+$#&'&)! '.)5'+$+$! .'%#-&$! /-('! Oooj!

%$+$#&'&)!#*+*'.'!G/'&'!/$!.'%&$,$HB!

!

mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";

+----------+-------+---------+------+------------+-------+

! OY

| name | owner | species | sex | birth | death |

+----------+-------+---------+------+------------+-------+

| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |

| Puffball | Diane | hamster | f | 1999-03-30 | NULL |

+----------+-------+---------+------+------------+-------+

!

! E-&$&)!/$!'%$5$.$'!#*59).'!#*./)&))+$6!/$!$D$5(+-!($.&,-!'!+*#'+)8'!@$5$+$+$!<!#').$B!

!

mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";

+-------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+-------+--------+---------+------+------------+-------+

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

!

! 2*+)#)&',$'!'.&$,)*','!@*+*%$%&$!*($,'&*,-+!+*:)#!"bK=!UD)%&'!%)!-.!*($,'&*,!PVB!

!

mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";

+----------+-------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+----------+-------+---------+------+------------+-------+

| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |

| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

| Slim | Benny | snake | m | 1996-04-29 | NULL |

+----------+-------+---------+------+------------+-------+

! "bK!%)!PV!(*&!@)!+'!,'./-+!+*,!5)D'&)=!K'#'!'%&'!/*,)&)6!$!(,$@$,'9)+!%'!@*+*%)&)!(','.&$8$+$!($.&,-!'!

)./)#'!5*/-+!).!#',$!7,$&)!%'!@)$!:,-('&$!#*./)&))+$=!

!

mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")

-> OR (species = "dog" AND sex = "f");

+-------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

!

! OZ

3.3.4.3 Selectarea anumitor coloane

!

! K'#'! .-! /*,)&)! 7)8-'+)8',$'! &-&-,*,! +).))+*,! /).! &'9$+! (,$#)8'&)! /*',! #*+*'.$+$! ).! #',$! %-.&$&)!

).&$,$%'&6!%$(','&$!(,).!7),:-+$=!

! K$!$D$5(+-6!/'#'!7,$&)!%'!%&)&)!/'&$+$!/$!.'%&$,$!'+$!'.)5'+$+*,!/$&).-&$!%$+$#&'&)!#*+*'.$+$!.-5$+-)!

%)!,$%($#&)7!/'&$)!/$!.'%&$,$=!

!

mysql> SELECT name, birth FROM pet;

+----------+------------+

| name | birth |

+----------+------------+

| Fluffy | 1993-02-04 |

| Claws | 1994-03-17 |

| Buffy | 1989-05-13 |

| Fang | 1990-08-27 |

| Bowser | 1989-08-31 |

| Chirpy | 1998-09-11 |

| Whistler | 1997-12-09 |

| Slim | 1996-04-29 |

| Puffball | 1999-03-30 |

+----------+------------+

!

K'#'!7,$&)!%'!'@+'&)!(*%$%*,))!@)$#',-)!'.)5'+6!&'%&'&)B!

!

mysql> SELECT owner FROM pet;

+--------+

| owner |

+--------+

| Harold |

| Gwen |

| Harold |

| Benny |

| Diane |

| Gwen |

| Gwen |

| Benny |

| Diane |

+--------+

! O^

!

! T.!*,)#$!#'86!*9%$,7'&)!5*/'+)&'&$'!).#',$!#*5'./'!$D&,':$!(-,!%)!%)5(+-!#'5(-+!(*%$%*,-+-)!/).!

@)$#',$! ).,$:)%&,',$! %)! '%&@$+! 7$&)! 7$/$'! #'! -.$+$! .-5$! '(',! /$! 5')! 5-+&$! *,)=! E$.&,-! '! 5)5'! @).'+-+6!

$D&,':$&)!@)$#',$!).,$:)%&,',$!@).'+'!-.)#'!/*',!*/'&'!(,).!'/'-:',$'!#-7'.&-+-)!#A$)$!KT2LTbILB!

!

mysql> SELECT DISTINCT owner FROM pet;

+--------+

| owner |

+--------+

| Benny |

| Diane |

| Gwen |

| Harold |

+--------+

!

! E-&$&)!@*+*%)!*!#+'-8'!Gwhere”!%'!,$'+)8'&)!*!#*59).',$!).&,$!%$+$#&)'!,'./-,)+*,!%)!#$'!'!#*+*'.$+*,=!

K$!$D$5(+-6!%$+$#&'&)!/'&$+$!/$!.'%&$,$!/*',!'!#').)+*,!%)!()%)#)+*,B!

!

mysql> SELECT name, species, birth FROM pet

-> WHERE species = "dog" OR species = "cat";

+--------+---------+------------+

| name | species | birth |

+--------+---------+------------+

| Fluffy | cat | 1993-02-04 |

| Claws | cat | 1994-03-17 |

| Buffy | dog | 1989-05-13 |

| Fang | dog | 1990-08-27 |

| Bowser | dog | 1989-08-31 |

+--------+---------+------------+

!

3.3.4.4 Sortarea liniilor

!

! L,$9-)$! %'! @)! *9%$,7'&! ).! $D$5(+$+$! '.&$,)*',$! #'! +).))+$! ,$8-+&'&$! .-! %-.&! /)%(-%$! ).&,C*! *,/).$!

'.-5$=!!

! P,)#-56!/$!#$+$!5')!5-+&$!*,)!$%&$!5')!-%*,!%'!).,$:)%&,$8)!#'./!+).))+$!%-.&!%*,&'&$!/-('!-.!'.-5)&!

).&$+$%=!"%&@$+!@*+*%)&)!#+'-8'!PVKUV!gt=!

! T'&'!/'&$+$!/$!.'%&$,$!/)%(-%$!#,*.*+*:)#B!

!

! Of

mysql> SELECT name, birth FROM pet ORDER BY birth;

+----------+------------+

| name | birth |

+----------+------------+

| Buffy | 1989-05-13 |

| Bowser | 1989-08-31 |

| Fang | 1990-08-27 |

| Fluffy | 1993-02-04 |

| Claws | 1994-03-17 |

| Slim | 1996-04-29 |

| Whistler | 1997-12-09 |

| Chirpy | 1998-09-11 |

| Puffball | 1999-03-30 |

+----------+------------+

!

! E$.&,-!%*,&',$'!).7$,%'!>/$%#,$%#',*',$?!'/-:'&)!#-7'.&-+!#A$)$!KU2I!+'!.-5$+$!#*+*'.$)!($!#',$!

*!%$+$#&'&)B!

!

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

+----------+------------+

| name | birth |

+----------+------------+

| Puffball | 1999-03-30 |

| Chirpy | 1998-09-11 |

| Whistler | 1997-12-09 |

| Slim | 1996-04-29 |

| Claws | 1994-03-17 |

| Fluffy | 1993-02-04 |

| Fang | 1990-08-27 |

| Bowser | 1989-08-31 |

| Buffy | 1989-05-13 |

+----------+------------+

!

! E-&$&)!%*,&'!5')!5-+&$!#*+*'.$!#*.%$#-&)7$=!UD$5(+-+!/-('!%($#)$6!'(*)!/-('!/'&'!.'%&$,))!@'#'./!

'.-5)&$!(,$#)8',)!(,$#-5!.-5$+$!#$+-)!5')!&'.',!'.)5'+B!

!

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth

DESC;

! Oj

+----------+---------+------------+

| name | species | birth |

+----------+---------+------------+

| Chirpy | bird | 1998-09-11 |

| Whistler | bird | 1997-12-09 |

| Claws | cat | 1994-03-17 |

| Fluffy | cat | 1993-02-04 |

| Fang | dog | 1990-08-27 |

| Bowser | dog | 1989-08-31 |

| Buffy | dog | 1989-05-13 |

| Puffball | hamster | 1999-03-30 |

| Slim | snake | 1996-04-29 |

+----------+---------+------------+

!

P9%$,7'&)!#'!KU2I!%$!'(+)#'!/*',!#*+*'.$+*,!'+!#'+*,!.-5$!'!@*%&!%($#)@)#'&=!I$+$+'+&$!,'5'.!.$%#A)59'&$=!

UDB!7'+*,)+$!%($#)$)!%-.&!).#'!$.-5$,'&$!).!*,/).$!#,$%#'&*',$=!

3.3.4.5 Calculul datelor!

!

! 01%;+!'%):-,'!#'&$7'!@'#)+)&'&)!($!#',$!+$!(-&$&)!@*+*%)!($.&,-!/)7$,%$!#'+#-+$!'%-(,'!/'&$+*,B!!

! UDB!#'+#-+-+!7',%&$+*,!%'-!$D&,':$,$'!-.*,!(',&)!$,*.'&$!'!/'&$+*,=!E$.&,-!'!/$&$,5).'!#$!7',%&'!',$!

@)$#',$!'.)5'+6!#'+#-+'&)!/)@$,$.&'!/).&,$!/'&'!#-,$.&'! %)! #$'! ).,$:)%&,'&'!'(*)! %#'/$&)!O6!/'#'!/'&'! #-,$.&'!

$%&$!%)&-'&'!).!#'+$./',!).').&$'!/'&$+*,!/$!.'%&$,$=!!

!

mysql> SELECT name, birth, CURRENT_DATE,

-> (YEAR(CURRENT_DATE)-YEAR(birth))

-> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))

-> AS age

-> FROM pet;

+----------+------------+--------------+------+

| name | birth | CURRENT_DATE | age |

+----------+------------+--------------+------+

| Fluffy | 1993-02-04 | 2001-08-29 | 8 |

| Claws | 1994-03-17 | 2001-08-29 | 7 |

| Buffy | 1989-05-13 | 2001-08-29 | 12 |

| Fang | 1990-08-27 | 2001-08-29 | 11 |

| Bowser | 1989-08-31 | 2001-08-29 | 11 |

| Chirpy | 1998-09-11 | 2001-08-29 | 2 |

! Oo

| Whistler | 1997-12-09 | 2001-08-29 | 3 |

| Slim | 1996-04-29 | 2001-08-29 | 5 |

| Puffball | 1999-03-30 | 2001-08-29 | 2 |

+----------+------------+--------------+------+

!

")#)! tU"V>?! ).,$:)%&,$'8'! '.-+! /).! /'&'! /$! .'%&$,$6! )',! VTqhL! #$+$+'+&$! #).#)! #','#&$,$!

,$(,$8$.&'./! +-.'6! ,$%($#&)7! 8)-'=! V$8-+&'&-+! (*'&$! @)! %#'.'&! #-! 5')! 5-+&'! -%-,).&'! /'#'! +).))+$! '-! @*%&!

).,$:)%&,'&$! ).&,C*! *,/).$! (,$%&'9)+)&'=! "#$%&! +-#,-! %$! *9&).$! '/'-:'./! #+'-8'! PVKUV! gt! %(,$! '! %*,&'!

).,$:)%&,',)+$!@).'+$!/-('!.-5$=!E$.&,-!*!).,$:)%&,',$!@).'+'!/-('!7',%&'!$%&$!/$!'M-.%!%'!*(&'&)!($.&,-!*!'+&'!

#+'-8'!PVKUV!gt=!

!

mysql> SELECT name, birth, CURRENT_DATE,

-> (YEAR(CURRENT_DATE)-YEAR(birth))

-> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))

-> AS age

-> FROM pet ORDER BY name;

+----------+------------+--------------+------+

| name | birth | CURRENT_DATE | age |

+----------+------------+--------------+------+

| Bowser | 1989-08-31 | 2001-08-29 | 11 |.170 MySQL Technical Reference

for Version 4.1.0-alpha

| Buffy | 1989-05-13 | 2001-08-29 | 12 |

| Chirpy | 1998-09-11 | 2001-08-29 | 2 |

| Claws | 1994-03-17 | 2001-08-29 | 7 |

| Fang | 1990-08-27 | 2001-08-29 | 11 |

| Fluffy | 1993-02-04 | 2001-08-29 | 8 |

| Puffball | 1999-03-30 | 2001-08-29 | 2 |

| Slim | 1996-04-29 | 2001-08-29 | 5 |

| Whistler | 1997-12-09 | 2001-08-29 | 3 |

+----------+------------+--------------+------+

mysql> SELECT name, birth, CURRENT_DATE,

-> (YEAR(CURRENT_DATE)-YEAR(birth))

-> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))

-> AS age

-> FROM pet ORDER BY age;

+----------+------------+--------------+------+

| name | birth | CURRENT_DATE | age |

! Rp

+----------+------------+--------------+------+

| Chirpy | 1998-09-11 | 2001-08-29 | 2 |

| Puffball | 1999-03-30 | 2001-08-29 | 2 |

| Whistler | 1997-12-09 | 2001-08-29 | 3 |

| Slim | 1996-04-29 | 2001-08-29 | 5 |

| Claws | 1994-03-17 | 2001-08-29 | 7 |

| Fluffy | 1993-02-04 | 2001-08-29 | 8 |

| Fang | 1990-08-27 | 2001-08-29 | 11 |

| Bowser | 1989-08-31 | 2001-08-29 | 11 |

| Buffy | 1989-05-13 | 2001-08-29 | 12 |

+----------+------------+--------------+------+

! P! @*,5-+',$! %)5)+','! (*'&$! @)! @*+*%)&'! ($.&,-! '! /$&$,5).'! 7',%&'! ).! 5*5$.&-+! /$#$%-+-)=! I',$!

'.-5$!%-.&!'.)5'+$+$!'@+'&$!).!%)&-'&)'!/'&'!%$!*9%$,7'!%)5(+-!7$,)@)#'./!/'#'!7'+*',$'!G/$'&AH!$%&$!%'-!.-!

bS44=!E$.&,-!#$+$!#',$!.-!%-.&!@'#$&)!/)@$,$.&'!/).&,$!/'&'!/$#$%-+-)!%)!#$'!'!.'%&$,))=!

!

mysql> SELECT name, birth, death,

-> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))

-> AS age

-> FROM pet WHERE death IS NOT NULL ORDER BY age;

+--------+------------+------------+------+

| name | birth | death | age |

+--------+------------+------------+------+

| Bowser | 1989-08-31 | 1995-07-29 | 5 |

+--------+------------+------------+------+

!

! K',6!/'#'!7,$&)!%'!'@+'&)6!/$!$D$5(+-!#$!'.)5'+$!G)%)!%',9'&*,$%#!8)-'H!+-.'!7))&*',$c!E$.&,-!'%&@$+!

/$! &)(-,)! /$! #'+#-+$6!(','5$&,)! #'! '.-+! %'-! 8)-'!/$7).! ),$+$7'.&)W! &*&! #$! &,$9-)$! %'! @'#$&)!$%&$! %'! $D&,':$&)!

7'+*,)+$!,$@$,)&*',$!+'!+-.'!/).!#*+*'.'!/'&$)!/$!.'%&$,$=!

! 01%;+! @-,.)8$'8'! #'&$7'! (*%)9)+)&'&)! /$! '%&@$+! /$! %$+$#&',)! G(',&)'+$HB! tU"V>?6! 0PbLh>?6!

K"tPe0PbLh>?=!T.!$D$5(+-+!.*%&,-!+-.'!0PbLh>?!$%&$!*(&)-.$'!(*&,)7)&'=!

! 2(,$! '!7$/'! #*.#,$&! #-5! @-.#&)*.$'8'! ,-+'&)! *! #*5'./'!%)5(+'! #',$! ).#+-/$!/$*(*&,)7'!7'+*,)+$!

/'&$+*,!/$!.'%&$,$!>9),&A?!,$%($#&)7!'+$!+-.))!>5*.&A?=!

!

mysql> SELECT name, birth, MONTH(birth) FROM pet;

+----------+------------+--------------+

| name | birth | MONTH(birth) |

+----------+------------+--------------+

! RO

| Fluffy | 1993-02-04 | 2 |

| Claws | 1994-03-17 | 3 |

| Buffy | 1989-05-13 | 5 |

| Fang | 1990-08-27 | 8 |

| Bowser | 1989-08-31 | 8 |

| Chirpy | 1998-09-11 | 9 |

| Whistler | 1997-12-09 | 12 |

| Slim | 1996-04-29 | 4 |

| Puffball | 1999-03-30 | 3 |

+----------+------------+--------------+

!

K$'%$5$.$'! $%&$! -%*','! #'-&',$'! '.)5'+$+*,! #',$! '-! 8)-'! /$! .'%&$,$! ).! +-.'! #$! -,5$'8'=!

E,$%-(-.$5!#'!+-.'!#-,$.&'!$%&$!"(,)+)$=!"&-.#)!7'+*',$'!+-.))!$%&$!Y!%)!K7%=!#'-&'&)!'.)5'+$!.'%#-&$!).!0')!

>+-.'!Z?!'%&@$+B!

!

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

+-------+------------+

| name | birth |

+-------+------------+

| Buffy | 1989-05-13 |

+-------+------------+

!

K$%):-,! $D)%&'! *! 5)#'! #*5(+)#'&)$! /'#'! +-.'! #-,$.&'! $%&$! K$#$59,)$=! b-! (-&$&)! '/'-:'! O! +'!

.-5',-+!+-.))!>OR?!%)!'(*)!%'!#'-&'&)!'.)5'+$!.'%#-&$!).!+-.'!ON6!/$*',$#$!.-!$D)%&'!*!'%$5$.$'!+-.'=!T.!+*#!

7$&)!#'-&'!'.)5'+$!.'%#-&$!).!T'.-',)$!>+-.'!O?=!

E-&$&)!#A)',!%'!%#,)$&)!#*5'./'!'%&@$+!).#'&!$'!7'!@-.#&)*.'!)./)@$,$.&!/$!+-.'!#-,$.&'=!T.!'#$%&!@$+!.-!&,$9-)$!

%'!@*+*%)&)!.-5',-+!'@$,$.&!-.$)!+-.)!*',$#',$!).!#*5'./'=!K"LUk"KK>?!7'!($,5)&$!%'!'/'-:'&)!*!+-.'!+'!

7'+*',$'!+-)!bPi>?6!'(*)!$D&,':$&)!+-.'!#-!'M-&*,-+!@-.#&)$)!0PbLh>?6!'(*)!,$8-+&'&-+!7'!@)!+-.'!).!#',$!%'!

#'-&'&)!8)+$+$!/$!.'%&$,$B!

!

mysql> SELECT name, birth FROM pet

-> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

P!'+&'!#'+$!/$!'!,$'+)8'!'#$)'%)!&$5'!$%&$!%'!'/'-:'&)!O!($.&,-!'!'M-.:$!+'!+-.'!-,5'&*',$!/-('!#$'!

#-,$.&'!>@*+*%)./!@-.#&)'!0PK>?!($.&,-!'!,*&-.M)!7'+*',$'!+-.))!+'!p!/'#'!$'!$%&$!OR?B!

!

mysql> SELECT name, birth FROM pet

-> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

! RR

!

L,$9-)$! .*&'&! @'(&-+! #'! @-.#&)'!0PbLh>?! ).&*',#$! -.! .-5',! ).&,$! O! %)! OR=! T',! 0PK>#$7'6OR?!

).&*',#$!-.!.-5',! ).&,$!p!%)!OO=!"%'/',!).#,$5$.&',$'!&,$9-)$!%'!@)$!@-.#&)'!0PK>?6!'+&@$+!'5!&,$#$!/$! +'!

b*)$59,)$!>OO?!+'!T'.-',)$!>O?=!

! !

! Lucrul cu valori NULL

X'+*',$'! bS44! (*'&$! @)! %-,(,).8'&*',$! ('.'! 7'! *9)%.-)&)! #-! $'=! I*.#$(&-'+6! bS44! ).%$'5.'!

7'+*',$!+)(%'!%'-!.$#-.*%#-&'!%)!$%&$!&,'&'&'!*',$#-5!/)@$,)&!/$#'&!'+&$!7'+*,)=!E$.&,-!'!&$%&'!7'+*',$'!bS44!

.-!(-&$&)!@*+*%)!*($,'&*,)!',)&5$&)#)!/$!#*5(','&)$!#-5!',!@)!u6v!%'-!v[=!E$.&,-!'!/$5*.%&,'!'#$'%&'!($.&,-!

K7%=!).%)7'6!).#$,#'&)!-,5'&*',$'!#*5'./'B!

!

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

+----------+-----------+----------+----------+

| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |

+----------+-----------+----------+----------+

| NULL | NULL | NULL | NULL |

+----------+-----------+----------+----------+

!

U7)/$.&!.-!*9&).$&)!,$8-+&'&$!%'&)%@'#'&*',$!/).!'#$'%&'!#*5(','&)$=!e*+*%)&)!*($,'&*,))!T2!bS44!%)!

T2!bPL!bS44!).!+*#B!

!

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;

+-----------+---------------+

| 1 IS NULL | 1 IS NOT NULL |

+-----------+---------------+

| 0 | 1 |

+-----------+---------------+

!

P9%$,7'&)! #'! ).!01%;+6! p! %'-!bS44! ).%$'5.'! @'+%! %)! *,)#$! '+&#$7'! ).%$5.'! '/$7','&=!X'+*',$'!

%&'./',/!($.&,-!'/$7',!/).&,C*!*($,'&)$!9**+$'.'!$%&$!O=!

! "#$%&! &,'&'5$.&! %($#)'+! '+! +-)! bS44! $%&$! 5*&)7-+! ($.&,-! ',$6! ).! %$#&)-.$'! (,$#$/$.&'6! '! @*%&!

.$#$%','!/$&$,5).',$'!'.)5'+$+*,!#$!.-!5')!%-.&!).!7)'&'!@*+*%)./!/$'&A!T2!bPL!bS44!).!+*#!/$!/$'&A!v[!

bS44=!

! K*-'!7'+*,)!bS44!%-.&!#*.%)/$,'&$!$:'+$!).&,C-.!qVPSE!gt=!

! "&-.#)! #'./! -&)+)8'&)! -.! qVPSE! gt6! 7'+*,)+$! bS44! %-.&! (,$8$.&'&$! (,)5$+$! #'./! -&)+)8'&)!

qVPSE!gt!w!"2I!%)!-+&)5$+$!/'#'!@*+*%)&)!qVPSE!gt!w!KU2I=!

! RN

! b*&'&)! #'! ).&,$! 01%;+! Y=p=R! <! Y=p=Op6! 7'+*,)+$! bS44! $,'-! %*,&'&$! ).#*,$#&6! $+$! @))./! (,)5$+$!

)./)@$,$.&!/$!/),$#&)'!/$!%*,&',$=!

!

! Cautare dupa caz (forma)

01%;+!'%):-,'!%&'9/',/!%;+!/-('!#'8!>@*,5'?!#'&!%)!*!@*,5'!/$!#'-&',$!/-('!#'8!9'8'&$!($!$D(,$%))!

%)5)+',$!#$+*,!@*+*%)&$!/$!#'&,$!-&)+)&'&)+$!SbTl!#-5!',!@)!7)6!:,$(!%)!%$/=!

! I'-&',$!/-('!#'8!%;+!7'!($,5)&$!%'!@*+*%)&)!`ka!($.&,-!'!'%*#)'!-.!%).:-,!#','#&$,!*',$#',$!%)!`xa!

($.&,-!'!'%*#)'!-.!.-5',!',9)&,',!/$!#','#&$,$!>).#+-%)7!#','#&$,$+$!p?=!

! T.!01%;+6!&)(-,)+$!%;+!%-.&!).%$.%)9)+$!+'!#'8!).!5*/-+!%&'./',/=!I'&$7'!$D$5(+$!%-.&!','&'&$!')#)=!

P9%$,7'&)! #'!K7%=! .-! @*+*%)&)! u! %'-! v[! '&-.#)! #'./! -&)+)8'&)! &)(-,)! %;+W! -&)+)8'&)! *($,'&*,))! /$! #*5(','&)$!

4TyU!%'-!bPL!4TyU!).!+*#=!

! E$.&,-!'!:'%)!.-5$!#',$!).#$(!#-!`9aB!

!

mysql> SELECT * FROM pet WHERE name LIKE "b%";

+--------+--------+---------+------+------------+------------+

| name | owner | species | sex | birth | death |

+--------+--------+---------+------+------------+------------+

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

!

E$.&,-!'!:'%)!.-5$!#$!%$!&$,5).'!#-!`@1aB!

!

mysql> SELECT * FROM pet WHERE name LIKE "%fy";

+--------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+--------+--------+---------+------+------------+-------+

!

E$.&,-!'!:'%)!.-5$!#$!#*.&).!+)&$,'!`naB!

!

mysql> SELECT * FROM pet WHERE name LIKE "%w%";

+----------+-------+---------+------+------------+------------+

| name | owner | species | sex | birth | death |

+----------+-------+---------+------+------------+------------+

! RY

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

+----------+-------+---------+------+------------+------------+

!

E$.&,-!'!:'%)!.-5$!#$!#*.&).!$D'#&!#).#)!#','#&$,$6!@*+*%)&)!#','#&$,-+!&)(!`kaB!

!

mysql> SELECT * FROM pet WHERE name LIKE "_____";

+-------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

!

"+&$!&)(-,)!/$!#'-&',)!/-('!#'8!'%):-,'&$!/$!01%;+!@*+*%$%#!$D(,$%))!$D&).%$!>,$:-+'&$?=!"-.#)!#'./!&$%&'&)!

($.&,-! '#$%&! &)(! /$! #'8! *! '%*#)$,$6! @*+*%)&)! *($,'&*,))! VUqUlE! %*!bPL!VUqUlE! >%'-!V4TyU! %*!bPL!

V4TyU6!#',$!%-.&!%).*.)5)?=!

! S.$+$!#','#&$,)%&)#)!'+$!$D(,$%))+*,!$D&).%$!%-.&B!

" `=a!"%*#)'8'!-.!%).:-,!#','#&$,!*',$#',$=!

" P! #+'%'! /$! #','#&$,$! `zw{a! '%*#)'8'! *,)#$! #','#&$,! /).! ).&$,)*,-+! (','.&$8$+*,=! K$!

$D$5(+-6!`z'9#{a!'%*#)'8'!#','#&$,$+$!`'a6a9a!%'-!`#a=!E$.&,-!'!'%*#)'!-.!%),!/$!#','#&$,$6!

@*+*%)&)!%$5.-+!GCH!>/'%A?=!`z'C8{a!'%*#)'8'!*,)#$!+)&$,'!5)#'!6!)',!`zp==o{a!*,)#$!#)@,'=!

" `Qa! '%*#)'8'! 8$,*! %'-! 5')! 5-+&$! ).%&'.&$! '+$! +-#,-+-)#$+! (,$#$/'=! K$! $D$5(+-6! `DQa!

'%*#)'8'! *,)#$! .-5',! /$! `Da! #','#&$,$6! `zp==o{a! '%*#)'8'! *,)#$! .-5',! /$! #)@,$6! )',! `Qa!

'%*#)'8'!*,)#$!.-5',!/$!*,)#',$!@$+=!

" L)(-+!$%&$!'%*#)'&!/'#'!'(',$!*,)-./$!).!7'+*',$'!#',$!$%&$!&$%&'&'=!L)(-,)+$!%;+!'%*#)'8'!

/*',!/'#'!$+$!'%*#)'8'!).&$':'!7'+*',$=!

" E$.&,-! '! '.#*,'! -.! &)(! '%&@$+! ).#'&! %'! &$9-)'%#'! '%*#)'&! #-! ).#$(-&-+! %'-! @).'+-+! 7'+*,))!

&$%&'&$6!@*+*%)&)!`|a!+'!).#$(-&!%'-!`}a!+'!%@',%)&-+!&)(-+-)=!

E$.&,-! '! /$5*.%&,'! #-5! @-.#&)*.$'8'! $D(,$%))+$! $D&).%$6! #*5$.8)+$! 4TyU! ','&'&$! '.&$,)*,! %-.&!

,$%#,)%$!')#)!($.&,-!'!@*+*%)!VUqUlE=!

! E$.&,-!'!:'%)!.-5$!#$!).#$(!#-!`9a6!@*+*%)&)!`|a!($.&,-!'!'%*#)'!).#$(-&-+!.-5$+-)B!

! !

mysql> SELECT * FROM pet WHERE name REGEXP "^b";

+--------+--------+---------+------+------------+------------+

| name | owner | species | sex | birth | death |

! RZ

+--------+--------+---------+------+------------+------------+

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

!

T.').&$!/$!01%;+!7$,%)-.$'!N=RN=Y6!VUqUlE!$%&$!%$.%)9)+!+'!#'86!%)!#*5'./'!/$!5')!).').&$!.-!7'!

).&*',#$!.)#)!-.!,'./=!E$.&,-!'!'%*#)'!'&'&!+)&$,$!5)#)!#'&!%)!5',)6!$D$5(+-!`9a6!@*+*%)&)!'#$'%&'!#*5'./'!).!

+*#B!

!

mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";

!

K$! +'!01%;+!N=RN=Y! ).#*+*6! ($.&,-!'! @*,&'!*!#*5(','&)$!VUqUlE!%'! @)$! %$.%)9)+'! +'! #'86! @*+*%)&)!

#-7'.&-+! #A$)$!gTb"Vt! ($.&,-! '! #*.7$,&)! -.! %),! ).&,C-.! %),! 9).',=! S,5'&*',$'! #*5'./'! 7'! '%*#)'! /*',!

+)&$,'!5)#'!`9a!+'!).#$(-&-+!-.-)!.-5$B!

!

mysql> SELECT * FROM pet WHERE name REGEXP BINARY "^b";

!

E$.&,-!'!:'%)!.-5$!#$!%$!&$,5).'!#-!`@1a6!@*+*%)&)!`}a!($.&,-!'!'%*#)'!@).'+-+!-.-)!.-5$B!

!

mysql> SELECT * FROM pet WHERE name REGEXP "fy$";

+--------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+--------+--------+---------+------+------------+-------+

!

! E$.&,-!'!:'%)!.-5$!#$!#*.&).!+)&$,'!5)#'!%'-!5',$!`na6!@*+*%)&)!'#$%&'!#*5'./'B!

!

mysql> SELECT * FROM pet WHERE name REGEXP "w";

+----------+-------+---------+------+------------+------------+

| name | owner | species | sex | birth | death |

+----------+-------+---------+------+------------+------------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

+----------+-------+---------+------+------------+------------+

!

! R^

K$*',$#$!*!$D(,$%)$!,$:-+','!&)(!'%*#)'8'!/'#'!$'!'(',$!*,)-./$!).!7'+*',$6!.-!$%&$!.$#$%',!#'!).!

#*5'./'! (,$#$/$.&'! %'! @)$! ).&,*/-%)! %($#)@)#'&*,)! /$! .)#)! *! (',&$! '! &)(-+-)! ($.&,-! '! *9&).$! *! '%*#)$,$! #-!

).&,$':'!7'+*',$!'%'!#-5!',!@)!@*%&!/'#'!'&)!@)!@*+*%)&!-.!&)(!%;+=!

! E$.&,-!'!:'%)!-.!.-5$!#$!#*.&).$!$D'#&!#).#)!#','#&$,$6!@*+*%)&)!`|a!%)!`}a!#'!%'!'%*#)'&)!).#$(-&-+!%)!

%@',%)&-+!.-5$+-)6!#)!#).#)!).%&'.&$!/$!`=a!).&,$B!

!

mysql> SELECT * FROM pet WHERE name REGEXP "^.....$";

+-------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

!

! "&)!(-&$'!/$'%$5$.$'!%#,)$!#*5'./'!'.&$,)*','!@*+*%)./!`~.�a6!*($,'&*,-+!G,$($&'&C.C*,)H=!

!

mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$";

+-------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

!

Numararea randurilor

g'8$+$!/$!/'&$!%-.&!'/$%$'!@*+*%)&$!($.&,-'!,'%(-./$!).&,$9',))B!GI'&!/$!/$%!'(',$!-.!'.-5)&!&)(!/$!

/'&'! ).&,C-.! &'9$+cH! K$! $D$5(+-6! /'#'! /*,)&)! %'! '@+'&)! #'&$! '.)5'+$! '7$&)6! %'-! #'&$! '.)5'+$! ',$! @)$#',$!

(,*(,)$&',6!%'-!(*'&$!/*,)&)!%'!$@$#&-'&)!/)@$,)&$!&)(-,)!/$!,$#$.%'5'.&!+'!'.)5'+$+$!K7%=!

! I'+#-+',$'! .-5',-+-)! &*&'+! /$! '.)5'+$! ($! #',$! +$! '7$&)! $%&$! '#$+'%)! +-#,-! #'! %)! #-5! '&)! ).&,$9'B!

GI'&$!,'./-,)!%-.&!).&,C-.!&'9$+!/$!'.)5'+$cH!/$*',$#$!$D)%&'!*!%).:-,'!).,$:)%&,',$!($.&,-!@)$#',$!'.)5'+=!

e-.#&)'!IPSbL>?! .-5','! .-5',-+! /$! ,$8-+&'&$! #',$! .-! %-.&!bS446! '%&@$+! ).#'&! #*5'./'! /$! '! .-5','!

'.)5'+$+$!K7%=!X'!','&'!/-('!#-5!-,5$'8'B!

!

mysql> SELECT COUNT(*) FROM pet;

+----------+

| COUNT(*) |

+----------+

! Rf

| 9 |

+----------+

!

0')!/$7,$5$!'&)!*9&).-&!.-5$+$!($,%*'.$+*,!#$!/$&).!'.)5'+$=!E-&$&)!@*+*%)!IPSbL>?!/'#'!/*,)&)!

%'!'@+'&)!#'&$!'.)5'+$!',$!@)$#',$!(,*(,)$&',!).!(',&$B!

!

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

+--------+----------+

| owner | COUNT(*) |

+--------+----------+

| Benny | 2 |

| Diane | 2 |

| Gwen | 3 |

| Harold | 2 |

+--------+----------+

!

P9%$,7'&)!#-5!$%&$!-&)+)8'&!qVPSE!gt!($.&,-!'!:,-('!+'!-.!+*#!&*'&$!).,$:)%&,',)+$!($.&,-!@)$#',$!

(,*(,)$&',=!e','!$+!'&)!*9&).$!/*',!-.!5$%'M!/$!$,*',$B!

!

mysql> SELECT owner, COUNT(owner) FROM pet;

ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)

with no GROUP columns is illegal if there is no GROUP BY clause

!

IPSbL>?! %)! qVPSE! gt! %-.&! @*+*%)&*',$! ($.&,-! '! 7'! #','#&$,)8'! /'&$+$! ).! /)@$,)&$! 5*/-,)=!

S,5'&*',$+$!$D$5(+$!','&'!/)@$,)&$!#')!/$!'!$@$#&-'!*($,'&))!/$!,$#$.%'5'.&!+'!'.)5'+$+$!K7%=!

! b-5',-+!/$!'.)5'+$!).!@-.#&)$/$!%($#)$B!

!

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;

+---------+----------+

| species | COUNT(*) |

+---------+----------+

| bird | 2 |

| cat | 2 |

| dog | 3 |

| hamster | 1 |

| snake | 1 |

+---------+----------+

!

! Rj

! b-5',-+!/$!'.)5'+$!).!@-.#&)$!/$!%$DB!

!

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;

+------+----------+

| sex | COUNT(*) |

+------+----------+

| NULL | 1 |

| f | 4 |

| m | 4 |

+------+----------+

!

>4'!'#$%&!,$8-+&'&6!bS44!)./)#'!%$D!.$#-.*%#-&=?!

! b-5',-+!/$!'.)5'+$!).!#*59).'&)$!/$!%($#)$!%)!%$DB!

!

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

+---------+------+----------+

| species | sex | COUNT(*) |

+---------+------+----------+

| bird | NULL | 1 |

| bird | f | 1 |

| cat | f | 1 |

| cat | m | 1 |

| dog | f | 1 |

| dog | m | 2 |

| hamster | f | 1 |

| snake | m | 1 |

+---------+------+----------+

!

b-!$%&$!.$7*)$! %'! $D&,':$&)! ).&,$:-+! &'9$+! '&-.#)! #'./! @*+*%)&)!IPSbL>?=!K$! $D$5(+-6!#*5'./'!

'.&$,)*','6!#'./!$%&$!$@$#&-'&'!($!#').)!%)!()%)#)6!','&'!'%&@$+B!

!

mysql> SELECT species, sex, COUNT(*) FROM pet

-> WHERE species = "dog" OR species = "cat"

-> GROUP BY species, sex;

+---------+------+----------+

| species | sex | COUNT(*) |

+---------+------+----------+

| cat | f | 1 |

! Ro

| cat | m | 1 |

| dog | f | 1 |

| dog | m | 2 |

+---------+------+----------+

!

! 2'-6!/'#'!/*,)&)!/*',!.-5',-+!/$!'.)5'+$!#-!%$D-+!#-.*%#-&B!

!

mysql> SELECT species, sex, COUNT(*) FROM pet

-> WHERE sex IS NOT NULL

-> GROUP BY species, sex;

+---------+------+----------+

| species | sex | COUNT(*) |

+---------+------+----------+

| bird | f | 1 |

| cat | f | 1 |

| cat | m | 1 |

| dog | f | 1 |

| dog | m | 2 |

| hamster | f | 1 |

| snake | m | 1 |

+---------+------+----------+

!

3.3.4.9 Folosirea mai multor tabele

!

! L'9$+-+!G(#&H!7'!&).$!$7)/$.&'!'.)5'+$+*,!/$!#*5('.)$!($!#',$!+$!'7$&)=!K'#'!7,$&)!%'!).,$:)%&,'&)!%)!

'+&$! `).@*,5'&))a! /$%(,$! $+$6! $7$.)5$.&$! )5(*,&'.&$! (,$#-5! .'%&$,$'! (-)+*,! %'-! 7)8)&$+$! +'! 7$&$,).',6! 7$&)!

'7$'!.$7*)$!/$!-.!'+&!&'9$+=!

! I-5!',!&,$9-)!%'!','&$c!",!&,$9-)!%'!).#+-/'B!

" b-5$+$!'.)5'+-+-)!%(,$!'!%&)!-./$!(+'%'&)!$7$.)5$.&-+=!

" K'&'6!%(,$!'!%&)!#'./!%C'!($&,$#-&=!

" S.!('%'M!C!#'5(!).!#',$!%'C+!/$%#,)$&)=!

" S.!#'5(!($!&)(-,)!/$!$7$.)5$.&$6!%(,$!'!+$!*,/*.'=!

! L*'&$! '#$%&$'! @))./!*(-%$6! *(&)-.$'!IVU"LU!L"g4U!($.&,-! &'9$+-+! $7$.)5$.&$+*,! ',! &,$9-)! %'!

','&$!#'!'%'B!

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,

-> type VARCHAR(15), remark VARCHAR(255));

!

! Np

K'&!@))./!&'9$+-+!).)&)'+!G($&H!7'!@)!5')!-%*,!%'!).&,*/-#$&)!/'&$+$!).)&)'+$!).!#$+!/$C'+!/*)+$'!#,$')./!

-.!&$D&!'!#',-)!/'&$!7*,!@)!/$+)5)&'&$!(,).!#'&$!-.!&'9B!!

!

name date type remark

Fluffy 1995-05-15 litter 4 kittens, 3 female, 1 male

Buffy 1993-06-23 litter 5 puppies, 2 female, 3 male

Buffy 1994-06-19 litter 3 puppies, 3 female

Chirpy 1999-03-21 vet ciocul indreptat

Slim 1997-08-03 vet coasta rupta

Bowser 1991-10-12 kennel

Fang 1991-10-12 kennel

Fang 1998-08-28 birthday o noua jucarie de plastic

Claws 1998-03-17 birthday o noua zgarda

Whistler 1998-12-09 birthday zi de nastere

!

T.#',#'&)!).,$:)%&,',)+$!'%&@$+B!

!

mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event;

!

P,)$.&'./-C7'! /-('! #-.*%&).&$+$! '#-5-+'&$! +'! &'9$+-+! '.&$,)*,! 7$&)! ,$-%)! %'! $@$#&-'&)! /)7$,%$!

*($,'&)-.)!%)!5*/)@)#',)!).!&'9$+-+!$7$.)5$.&$+*,=!E,).#)())+$!%-.&!'#$+$'%)=!

! K',!).!#'8-+!).!#',$!/'&$+$!).&,*/-%$!%-.&!).%-@)#)$.&$!($.&,-!'!,'%(-./$!'.-5)&*,!).&,$9',)6!#$!',!

&,$9-)!%'!@'#$&)c!

! 2'! (,$%-(-.$56! #$! 7',%&'! '7$'! @)$#',$! '.)5'+! ).!5*5$.&-+! ).! #',$! '! '7-&! .*-C.'%#-&)! L'9$+-+!

$7$.)5$.&$+*,! )./)#'! /'&$+$! ).! #',$! '#$%&! +-#,-! %'! ($&,$#-&6! /',! ($.&,-! '! #'+#-+'! 7',%&'! G5'5$)H6! '7$&)6!

.$7*)$!%)!/$!/'&'!/$!.'%&$,$=!

! E$.&,-!#'!'#$'%&'!$!%&*#'&'!).!&'9$+-+!G($&H!'7$&)!.$7*)$!/$!'59$+$!&'9$+$!).!'#$+'%)!&)5(6!($.&,-!

-,5'&*',$'!#*5'./'B!

!

mysql> SELECT pet.name,

-> (TO_DAYS(date) - TO_DAYS(birth))/365 AS age,

-> remark

-> FROM pet, event

-> WHERE pet.name = event.name AND type = "litter";.178 MySQL Technical

Reference for Version 4.1.0-alpha

+--------+------+-----------------------------+

| name | age | remark |

+--------+------+-----------------------------+

! NO

| Fluffy | 2.27 | 4 kittens, 3 female, 1 male |

| Buffy | 4.12 | 5 puppies, 2 female, 3 male |

| Buffy | 5.10 | 3 puppies, 3 female |

+--------+------+-----------------------------+

!

L,$9-)$!%-9+).)'&$!#'&$7'!+-#,-,)!).!+$:'&-,'!#-!'#$'%&'!#*5'./'B!

" I+'-8'!eVP0!,-+$'8'!'59$+$!&'9$+$!($.&,-!#'!#$,$,$'!7'!$D&,':$!/'&$!/).!'5'./*-'W!

" I'./! %-.&! #*59).'&$! ).! @*,5'&))! /).!5')!5-+&$! &'9$+$6! &,$9-)$! %($#)@)#'&!5*/-+! ).! #',$!

).,$:)%&,',)+$!/).&,C-.-+!(*&! @)! #-(+'&$! #-! #$+$!/).! '+! /*)+$'=!"#$%&! +-#,-! ',! &,$9-)! %'! @)$!

-%*,!6!($.&,-!#'!6!.-C)!'%'6!'59$+$!&'9$+$!(*%$/'!*!#*+*'.'!'!.-5$+-)=!2)!).!#'8-+!.*%&,-6!

%$!7*,!#-(+'!/'&$+$!/).!/*-'!&'9$+$!($!9'8'!7'+*,)+*,!.-5$+-)6!(,).!#'+-8'!ihUVU=!

" L*#5')! ($.&,-! #'! #*+*'.'! .-5$+-)! %$! '@+'! ).! '59$+$! &'9$+$6! #'./! *! @*+*%)&)! &,$9-)$! %'!

%($#)@)#'&)!&'9$+-+=!

! b-!).&*&/$'-.'!'7$&)!.$7*)$!/$!/*-'!&'9$+$!/)@$,)&$!($.&,-!'!@'#$!#*59).',)=!P,)#$!&'9$+!(*'&$!@)!

#*59).'&!#-!$+!).%-%)=!

! K$! $D$5(+-6! ($.&,-! '! :'%)! (*%)9)+$! ($,$#A)! (,).&,$! '.)5'+$+$! /-5.$'7*'%&,'! (-&$&)! #*59).'!

*,)$.&'&)7!@$5$+$+$!%)!5'%#-+))!).%#,)%)!).!'#$+'%)!&'9$+6!/$!$D$5(+-!/-('!%($#)$B!

!

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species

-> FROM pet AS p1, pet AS p2

-> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";

+--------+------+--------+------+---------+

| name | sex | name | sex | species |

+--------+------+--------+------+---------+

| Fluffy | f | Claws | m | cat |

| Buffy | f | Fang | m | dog |

| Buffy | f | Bowser | m | dog |

+--------+------+--------+------+---------+

!

3.4 Obtinerea informatiilor despre bazele de date si tabele

! I$!@'#$&)!/'#'!-)&'&)!.-5$+$!-.$)!9'8$!/$!/'&$!%'-!-.-)!&'9$+6!%'-!#',$!$!%&,-#&-,'!'#$%&-)'c!

! 01234! ,$8*+7'! '#$'%&'! (,*9+$5'! (,).! #'&$7'! &)(-,)! /$! #*5$.8)! '7'./! /,$(&! %#*(! *9&).$,$'!

).@*,5'&))+*,!/$%(,$!9'8'!%)!&'9$+$+$!#*5(*.$.&$=!

! XC'&)!@'5)+)',)8'&!/$M'!#-!2hPi!K"L"g"2U26!#',$!!%).&!9'8$+$!/$!/'&$!5'.)(-+'&$!($!%$,7$,=!

! E$.&,-!'!'@+'!#$!9'8'!/$!/'&$!,-+$'8'!).&,C-.!'.-5$!5*5$.&6!*(&'&)!($.&,-!@-.#&)'!K"L"g"2U>?B!

!

! NR

mysql> SELECT DATABASE();

+------------+

| DATABASE() |

+------------+

| menagerie |

+------------+

!

! K'#'!.C'&)!%$+$#&'&!.)#)!*!9'8'!/$!/'&$!).#'6!,$8-+&'&-+!7'!@)!.-+=!

! E$.&,-!'!'@+'!#$!&'9$+$!#*.&).$!9'8'!/$!/'&$!%$+$#&'&'!@*+*%)&)!#*5'./'B!

!

mysql> SHOW TABLES;

+---------------------+

| Tables in menagerie |

+---------------------+

| event |

| pet |

+---------------------+

! !

K'#'! 7,$&)! ).@*,5'&))! /$%(,$! %&,-#&-,'! &'9$+-+-)6! $! '9%*+-&! .$#$%','! #*5'./'!KU2IVTgU6! 7'! 7'!

@-,.)8'!).@*,5'&))!/$!%(,$!@)$#',$!/).&,$!#*+*'.$+$!&'9$+-+-)B!

!

mysql> DESCRIBE pet;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| name | varchar(20) | YES | | NULL | |

| owner | varchar(20) | YES | | NULL | |

| species | varchar(20) | YES | | NULL | |

| sex | char(1) | YES | | NULL | |

| birth | date | YES | | NULL | |

| death | date | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

!

! e)$+/!C!)./)#'!.-5$+$!#*+*'.$)W!L1($!C!&)(-+!/'&$+*,W!

! b-++! C! /'#'! #*+*'.'! ).#+-/$! %)! 7'+*,)! .-+$6!y$1! C! /'#'! #*+*'.'! $%&$! )./$D'&'! %)!K$@'-+&! 7'+*,)+$!

(,$%&'9)+)&$!).!%)%&$5-+!/$!*($,',$=!

! K'#'!$D)%&'!)./)#)!).!&'9$+6!2hPi!TbKUl!eVP0!&9+C.'5$!7'!(,*#-,'!).@*,5'&))!/$%(,$!$+$=!

!

! NN

! 3.5 Exemple de comenzi

!

! T'&'!#'&$7'!$D$5(+$!/$%(,$!#-5!(-&$&)!,$8*+7'!#'&$7'!(,*9+$5$!#-,$.&$!(,).!01234=!

! I'&$7'! /).&,$! $D$5(+$! @*+*%$%#! &'9$+-+! %A*(! ($.&,-! '! ).,$:)%&,'! (,$&-+! @)$#',-)! ',&)#*+! >)&$5?! ).!

#'8-+!'.-5)&*,!#*5$,#)'.&!>/$'+$,%?=!2'!(,$%-(-.$5!#'!@)$#',$!#*5$,#)'.&!',$!#'&$!-.!%).:-,!(,$&!@)D!($.&,-!

@)$#',$!(,*/-%=!"&-.#)!7*5!#*.%)/$,'!!#'!>',&)#+$6!/$'+$,%?!$%&$!#A$)$!(,)5','!($.&,-!).,$:)%&,',)B!

! 4'.%'&)!+).)'!/$!#*5'./'!51%;+!%)!%$+$#&'&)!*!9'8'!/$!/'&$B!

! E-&$&)!#,$'!-.!&'9$+!$D$5(+-!'%&@$+B!

!

CREATE TABLE shop (

article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,

dealer CHAR(20) DEFAULT '' NOT NULL,

price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,

PRIMARY KEY(article, dealer));

INSERT INTO shop VALUES

(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),

(3,'D',1.25),(4,'D',19.95);

!

! "#-5!&*&-+!$%&$!Py=!g'8'!$D$5(+$!','&'!'%&@$+B!

!

mysql> SELECT * FROM shop;

+---------+--------+-------+

| article | dealer | price |

+---------+--------+-------+

| 0001 | A | 3.45 |

| 0001 | B | 3.99 |

| 0002 | A | 10.99 |

| 0003 | B | 1.45 |

| 0003 | C | 1.69 |

| 0003 | D | 1.25 |

| 0004 | D | 19.95 |

+---------+--------+-------+

!

! NY

3.5.1 Valoarea maxima pentru o coloana

! GI',$!$%&$!#$+!5')!5',$!.-5',cH!

!

SELECT MAX(article) AS article FROM shop

+---------+

| article |

+---------+

| 4 |

+---------+

!

3.5.2. Linia continand maxima unei anume coloane.

!

Gq'%)&)!.-5',-+6!/$'+$,-+6!%)!(,$&-+!#$+-)!5')!%#-5(!',&)#*+=H!

! T.!234!C!oo!>%)!51;%+!7$,%=!YO?!%$!,$8*+7'!,$($/$!(,).&,C*!%-9#*5'./'B!

!

SELECT article, dealer, price

FROM shop

WHERE price=(SELECT MAX(price) FROM shop)

!

T.!7$,%)-.$'!01234!/$!('.'!+'!Y=O!7$&)!*9&).$!,$8-+&'&-+!).!R!$&'($B!

O= P9&).$&)!7'+*',$'!5'D)5'!'!!(,$&-+-)!#-!2U4UIL=!

R= e*+*%)./!7'+*',$'!,$8-+&'&'!*!).#+-/$&)!).B!

!

SELECT article, dealer, price

FROM shop

WHERE price=19.95

!

! P! '+&'! (*%)9)+'! %*+-&)$! #*.%&'! ).! %*,&',$'! &-&-,*,! ,'./-,)+*,! >+).))+*,?! /$%#,$%#'&*,! /-('! 7'+*',$'!

(,$&-+-)!%)!%$+$#&'&)!/*',!(,)5'!@*+*%)./!#+'-8'!4T0TLB!

!

SELECT article, dealer, price

FROM shop

ORDER BY price DESC

LIMIT 1

!

! bPL"B!K'#'!$D)%&'!5')!5-+&$!7'+*,)!5'D)5$!>'#$+'%)!(,$&?!(,*:,'5-+!7'!)./)#'!/*',!-.'!/).&,$!

$+$=!

! !

! NZ

3.5.3 Maxima coloanei per grup

!

! GI',$!$%&$!7'+*',$'!5'D)5'!'!(,$&-+-)!/-('!',&)#*+cH!

!

SELECT article, MAX(price) AS price

FROM shop

GROUP BY article

+---------+-------+

| article | price |

+---------+-------+

| 0001 | 3.99 |

| 0002 | 10.99 |

| 0003 | 1.69 |

| 0004 | 19.95 |

+---------+-------+

!

3.5.4 Un rand cu un camp care cauta automat maxinmul dintr-un grup anume

GE$&,-!@)$#',$!',&)#*+6!:'%)&)!/)%&,)9-)&*,-+!#-!(,$&-+!#$+!5')!,)/)#'&=H!

!

SELECT article, dealer, price

FROM shop s1

WHERE price=(SELECT MAX(s2.price)

FROM shop s2

WHERE s1.article = s2.article);

!

T.!7$,%)-.$'!01234!/).').&$'!+-)!Y=O!$%&$!9).$!%'!-,5'&)!#)&)7'!('%)B!

O= P9&).$&)!+)%&$+$!>(,$&!5'D!s!',&)#*+?=!

R= E$.&,-!*,)#',$!',&)#*+!(,$+-'&)!+).)'!#*,$%(-.8'&*',$!#',$!',$!%&*#'&'!(,$&-+!5'D)5=!

! E-&$&)!@'#$!'%&'!%)5(+-!#-!-.!&'9$+!!(,*7)8*,)-B!

!

CREATE TEMPORARY TABLE tmp (

article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,

price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);

LOCK TABLES shop read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT shop.article, dealer, shop.price FROM shop, tmp

WHERE shop.article=tmp.article AND shop.price=tmp.price.

! N^

UNLOCK TABLES;

DROP TABLE tmp;

!

K'#'!.-!@*+*%)&)!-.!&'9$+!LU0EPV"Vt6!&,$9-)$!%'!9+*#'&)!&'9$+-+!`&5(a=!

! GE*'&$!@)!,$'+)8'&!#-!*!%).:-,'!#*5'./'cH!

! K'6!/',!/*',!@*+*%)./!-.!&,-#!$@)#)$.&!($!#',$!TC+!.-5$%#!&,-#-+!G0"lCIPbI"LHB!

!

SELECT article,

SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,

0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price

FROM shop

GROUP BY article;

+---------+--------+-------+

| article | dealer | price |

+---------+--------+-------+

| 0001 | B | 3.99 |

| 0002 | A | 10.99 |

| 0003 | C | 1.69 |

| 0004 | D | 19.95 |

+---------+--------+-------+

!

! S+&)5-+!$D$5(+-!(*'&$!@)6!/%):-,6!@'#-&6!).#'!%)!5')!$@)#)$.&!(,).!/)7)8',$'!#*+*'.$)!).!#+)$.&=!

3.5.5 Folosirea variabilelor utilizatorului!

!

! E-&$&)! @*+*%)! ! 7',)'9)+$+$! 01234! %(,$! '! 7'! ,$'5).&)! ,$8-+&'&$+$! @','! '! &,$9-)! %'! +$! %&*#'&)! ).!

7',)'9)+$!&$5(*,',$!).!#+)$.&=!

! K$!$D$5(+-6!($.&,-!'!:'%)!',&)#*+$+$!#-!#$+!5')!5',$!%'-!#$+!5')!5)#!(,$&!(-&$&)!@'#$!'%&@$+B!

!

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;

mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

+---------+--------+-------+

| article | dealer | price |

+---------+--------+-------+

| 0003 | D | 1.25 |

| 0004 | D | 19.95 |

+---------+--------+-------+

! Nf

3.5.6 Folosirea cheilor straine

!

! T.!#'8-+!01234!N=RN=YY!%)!5')!/$(',&$6!&'9$+$+$!T..Kg!%-(*,&'!7$,)@)#',$'!#*.&,'.:$,)+*,!#A$)+*,!

%&,').$=!K$!@'(&!.-!'7$&)!.$7*)$!/$!#A$)!%&,').$!($.&,-!'!-.)!/*-'!&'9$+$=!2).:-,-+!+-#,-!($!#',$!01234!.-!

)+!@'#$!).!5*/!#-,$.&!>).!'+&$!&)(-,)!/$!&'9$+$!).'@','!/$!T..*Kg?6!$%&$!'#$+'!/$!'!7$,)@)#'6!GIhUIyH6!/'#'!

#A$)+$!($!#',$! +$!@*+*%)&)!$D)%&'! ).! &'9$+!%)!.-!%&$,:$!'-&*5'&!,'./-,)!/).! &'9$+!#-!#A$)!%&,').$!(,$/$@).)&$=!

K'#'!@*+*%)&)!#A$)+$!K7%=!).!5*/!.*,5'+!'&-.#)!&*&-+!7'!@-.&)*.'!($,@$#&B!

!

CREATE TABLE person (

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

name CHAR(60) NOT NULL,

PRIMARY KEY (id)

);

CREATE TABLE shirt (

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

style ENUM('t-shirt', 'polo', 'dress') NOT NULL,

color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,

owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),

PRIMARY KEY (id)

);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

INSERT INTO shirt VALUES

(NULL, 'polo', 'blue', LAST_INSERT_ID()),

(NULL, 'dress', 'white', LAST_INSERT_ID()),

(NULL, 't-shirt', 'blue', LAST_INSERT_ID());

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirt VALUES

(NULL, 'dress', 'orange', LAST_INSERT_ID()),

(NULL, 'polo', 'red', LAST_INSERT_ID()),

(NULL, 'dress', 'blue', LAST_INSERT_ID()),

(NULL, 't-shirt', 'white', LAST_INSERT_ID());

SELECT * FROM person;

+----+---------------------+

| id | name |

+----+---------------------+

| 1 | Antonio Paz |

| 2 | Lilliana Angelovska |

+----+---------------------+

! Nj

SELECT * FROM shirt;

+----+---------+--------+-------+

| id | style | color | owner |

+----+---------+--------+-------+

| 1 | polo | blue | 1 |

| 2 | dress | white | 1 |

| 3 | t-shirt | blue | 1 |

| 4 | dress | orange | 2 |

| 5 | polo | red | 2 |

| 6 | dress | blue | 2 |

| 7 | t-shirt | white | 2 |

+----+---------+--------+-------+

SELECT s.* FROM person p, shirt s

WHERE p.name LIKE 'Lilliana%'

AND s.owner = p.id

AND s.color <> 'white';

+----+-------+--------+-------+

| id | style | color | owner |

+----+-------+--------+-------+

| 4 | dress | orange | 2 |

| 5 | polo | red | 2 |

| 6 | dress | blue | 2 |

+----+-------+--------+-------+

!

N=Z=f!I'-&',$'!/-('!/*-'!#A$)!

!

01234! .-! $%&$! ).#'! *(&)5)8'&! ($.&,-! #'-&',$'! /-('! /*-'! #A$)! #*59).'&$! ).! @-.#&)$! /$! PV!

>#'-&',$'!/-('!*!%).:-,'!#A$)$!#-!/)@$,)&$!(',&)!PV!$%&$!).!%#A)59!@-.&)*.'+'?B!

!

SELECT field1_index, field2_index FROM test_table WHERE field1_index =

'1' OR field2_index = '1'

!

UD(+)#'&)'!',!@)!'#$$'!#'!.-!'5!'7-&!).#'!&)5(!).#'!&)5(!%'!(-.$5!+'!(-.#&!*!#'+$!!

(+'-8)9)+'!($.&,-!'!,$'+)8'!'#$%&!+-#,-!>7',)'.&'!"bK!).!%#A)59!'!@*%&!@).'+)8'&'!%)!@-.&)*.$'8'!$D#$+$.&?=!

! E$.&,-!5*5$.&!(-&$&)!,$8*+7'!(,*9+$5$+$!/$!'#$%&!&)(!@*+*%)./!-.!&'9$+!LU0EPV"Vt=!"#$%&!:$.!

/$!*(&)5)8',$!$%&$!(,$@$,'9)+'!%)!).!#'8-+!).!#',$!'&)!@*+*%)&!#*5$.8)!#*5(+)#'&$!-./$!%$,7$,-+!234!'!,$-%)&!

*(&)5)8',)+$!/',!.-!).!*,/).$'!+*,!).)&)'+'=!

!

! No

CREATE TEMPORARY TABLE tmp

SELECT field1_index, field2_index FROM test_table WHERE field1_index =

'1';

INSERT INTO tmp

SELECT field1_index, field2_index FROM test_table WHERE field2_index =

'1';

SELECT * from tmp;

DROP TABLE tmp;

!

! S,5'&*,-+! 5)M+*#! (,).! #',$! (-&$&)! ,$8*+7'! (,*9+$5$! %)5)+',$! $%&$! /$! @'(&! *! ,$-.)-.$! '! /*-'!

#*5$.8)=!

!

3.5.8 Numerotarea accesarilor zilnice

! !

! T.! #$+$! #$! -,5$'8'! %$! #,$)*.$'8'! /$%(,$! #-5! (-&$&)! @*+*%)! :,-(-+! (,).#)('+! /$! @-.#&))! ($.&,-! '!

#'+#-+'!@,$#7$.&'!8)+$+*,!/).!+-.'!).!#',$!-.!-&)+)8'&*,!'##$%$'8'!*!(':).'!i$9B!

!

!

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,

day INT(2) UNSIGNED ZEROFILL);

INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),

(2000,2,23),(2000,2,23);.Chapter 3: Tutorial Introduction 185

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1

GROUP BY year,month;

!

I',$!).&*',#$B!

!

+------+-------+------+

| year | month | days |

+------+-------+------+

| 2000 | 01 | 3 |

| 2000 | 02 | 2 |

+------+-------+------+

!

N=Z=o!e*+*%),$'!"SLPkTbIVU0UbL!

!

"&,)9-&-+!"SLPkTbIVU0UbL!(*'&$!@)!@*+*%)&!($.&,-!'!:$.$,'!*!)/$.&)&'&$!-.)#'!.*)+*,!+).))B!

! Yp

CREATE TABLE animals (

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (id)

);

INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),

("lax"),("whale");

SELECT * FROM animals;

!

I$!).&*',#$B!

!

+----+---------+

| id | name |

+----+---------+

| 1 | dog |

| 2 | cat |

| 3 | penguin |

| 4 | lax |

| 5 | whale |

+----+---------+

!

E-&$&)!,$#-($,'!#A$)'!"SLPkTbIVU0UbL!($!#',$!'&)!@*+*%)&C*!(,).!@-.#&)'!4"2LkTb2UVLkTK>?!

234!%'-!(,).!@-.#&)'!51%;+k).%$,&k)/>?!"ET=!b*&'B!($.&,-!).%$,',$'!5-+&)(+'!>5')!5-+&!/$!/*-'!+).))!@-.#&)'!

4"2LkTb2UVLkTK>?s51%;+k).%$,&k)/>?! 7'! ,$#-($,'! #A$)'! '-&*).#,$5$.&! /).! (,)5'! +).)$! ).%$,'&'=! "#$%&!

+-#,-!7'!($,5)&$!).%$,',)+*,!5-+&)(+$!%'!@)$!,$(,*/-%$!%)!($!'+&$!%$,7$,$=!

E$.&,-!01T2"0!%)!Kg!(-&$&)!%($#)@)#'!"SLPkTbIVU0UbL!($!#*+*'.'!%$#-./','!(,).&,C*!#A$)$!

($!5')!5-+&$!#A$)=!!

T.! '#$%&! #'8! 7'+*',$'! :$.$,'&'! ($.&,-! '-&*! ).#,$5$.&',$'! #*+*'.$)! $%&$! #'+#-+'&'! #'!

0"l>'-&*k).#,$5$.&k#*+-5.?�O?!ihUVU!(,$@)Du:)7$.C(,$@)D=!"#$%&!#'+#-+!7',!(-&$'!@)!/$!@*+*%!).!#'8!

.#'!/*,)&)!).&,*/-#$,$'!-.*,!/'&$!).!:,-(-,)!(,$%&'9)+)&$B!

!

CREATE TABLE animals (

grp ENUM('fish','mammal','bird') NOT NULL,

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (grp,id)

);

INSERT INTO animals (grp,name) VALUES("mammal","dog"),("mammal","cat"),

! YO

("bird","penguin"),("fish","lax"),("mammal","whale");

SELECT * FROM animals ORDER BY grp,id;

+--------+----+---------+

| grp | id | name |

+--------+----+---------+

| fish | 1 | lax |

| mammal | 1 | dog |

| mammal | 2 | cat |

| mammal | 3 | whale |

| bird | 1 | penguin |

+--------+----+---------+

!

! P9%$,7'&)!#'!).!'#$%&!#'8!7'+*',$'!"SLPkTbIVU0UbL!7'!@)!,$'#&)7'&'!/'#'!,$.-.&'&)!+'!+).)'!#-!

7'+*',$'!5'D)5'!"SLPkTbIVU0UbL!/).!:,-(=!

!

3.6 Folosirea MySQL in Batch mode (grupat)

!

T.! %$#&)-.)+$! '.&$,)*',$! '&)! @*+*%)&! (,*:,'5-+!01234! ).&$,'#&)7!($.&,-! '! ).&,*/-#$! #*5$.8)! %)! '!

7)8-'+)8'!,$8-+&'&$+$=!!

E-&$&)!/$'%$5$.$'!,-+'!01234!).!9'&#A!5*/$=!E$.&,-!'!@'#$!'%&'!&,$9-)$!%'!!

).&,*/-#$&)!#*5$.8)+$!($!#',$!+$!/*,)&)!).&,C-.!@)%)$,6!%)!#*5'./'&)!(,*:,'5-+-)!%'!+$!#)&$'%#'=!

!

shell> mysql < batch-file

!

! K'#'! ,-+'&)! 01234C-+! @*+*%)./! @$,$%&,$+$! %)! '7$&)! #'&$7'! #','#&$,$! %($#)'+$! ).! @)%)$,6! #',$! 7'!

#'-8$'8'!(,*9+$5$!(-&$&)!&'%&'B!

!

dos> mysql -e "source batch-file"

!

! K'#'!&,$9-)$!%'!%($@)#'&)!(','5$&,)!/$!#*.$#&',$!+'!+).)'!/$!#*5'./'6!'#$'%&'!/).!-,5'!',!&,$9-)!%'!

','&$!#'5!'%'B!

!

shell> mysql -h host -u user -p < batch-file

Enter password: ********

!

! I'./! @*+*%)&)!01234C-+! %-9!'#$'%&'!@*,5'!#,$'&)!-.! @)%)$,! /*#-5$.&!%)! @).'+)8'&)!-+&$,)*,! &$D&-+6!

/'#'!7,$&)!#'!'#$%&'!%'!#*.&).-$!#A)',!/'#'!$D)%&'!$,*,)!&,$9-)$!%'!@*+*%)&)!*(&)-.$'!+).)$!/$!#*5'./'!CC@*,#$!

! YR

! K$!#$!%'!@*+*%)&)!-.!@)%)$,!&$D&c!T'&'!#'&$7'!5*&)7$=!!

" K'#'!,-+'&)!*!#*5'./'!).!5*/!,$($&'&!>8)+.)#6!%'(&'5'.'+?!#*.%&,-)&)!-.!@)%)$,!/*#-5$.&!%)!7$&)!

@)!%#-&)&)!/$!,$&'%&',$'!#*5$.8))!/$!@)$#',$!/'&'=!

" E-&$&)! #,$'!.*)! #*5$.8)! %)5)+',$!(*,.)./!/$! +'! #$+$!/$M'! $D)%&$.&$! #*())./! %)! $/)&'./! @)%)$,$!

&$D&=!

" 0*/-+! 9'&#A! (*%&$! %$,7)! /$'%$5$.$'! 5*5$.&-+! /$,-+',))! -.$)! #*5$.8)6! ).! %($#)'+! '! #$+*,!

5-+&)(+$! %'-! '! %$#7$.&$+*,! /$! #*5$.8)=! K'#'! @'#$&)! 7,$*! :,$%$'+'6! .-! 7'! &,$9-)! %'! ,$&'%&'&)!

&*&-+=!K*',!(,$+-#,'&)!&$D&-+!%'!#*,$#&$8$!$,*',$'!%)!#*5'./'&)!'(*)!(,*:,'5-+-)!%'!*!$D$#-&$=!

" K'#'! *! #*5'./'! #*./-#$! +'! 5')! 5-+&$! ,$8-+&'&$! +$! (-&$&)! ,-+'6! @*+*%)./! -.! (,*:,'5! /$!

(':).',$=!

!

shell> mysql < batch-file | more

!

E-&$&)!#*.%$5.'!,$8-+&'&-+!).&,C-.!@)%)$,!($.&,-!'!(-&$'!@)!(,*#$%'&!-+&$,)*,B!

!

shell> mysql < batch-file > mysql.out

!

! E-&$&)!/)%&,)9-)!/*#-5$.&$+$!%)!'+&*,!($,%*'.$=!

" S.$+$! %)&-'&))! .-! ($,5)&!5*/-+! ).&$,'#&)7=!K$! $D$5(+-! #'./! ,-+'&)! *! #*5./'! /).&,C-.!cron

job=!

V$8-+&'&-+!$%&$!/)@$,)&!#'./!,-+'&)!01234!).!9'&#A!5*/$!/$#'&!).!#$+!).&$,'#&)76!/$!$D$5(+-6!,$8-+&'&-+!/).!

2U4UIL!KT2LTbIL!%($#)$%!eVP0!($&!','&'!'%&@$+!).!5$/)-+!).&$,'#&)7B!

!

+---------+

| species |

+---------+

| bird |

| cat |

| dog |

| hamster |

| snake |

+---------+

!

! YN

K',6!'%&@$+!#'./!,-+'&)!).!9'&#A!5*/$B!

!

species

bird

cat

dog

hamster

snake

!

! K'#'!7,$&)!%'!&,$#$&)!+'!5*/-+!).&$,'#&)7!).!#'/,-+!9'&#A!5*/$!@*+*%)&)!mysql –t.

E$.&,-!#'6!#*5$.8)+$!($!#',$!+$!$D$#-&'&)!%'!&,)5)&'!-.!$#*-!@*+*%)&)!mysql –vvv.!

! E-&$&)!/$!'%$5$.$'!@*+*%)!@)%)$,-+!&$D&!($.&,-!+).)'!/$!#*5'./'!(*,.)./!/$!+'!#*5./'!).)&)'+'B!

!

mysql> source filename;

!

3.7 Comenzi din proiecte gemene

!

! 4'!".'+1&)r$,.'!%)!4$.&-%6!'5!'#&)7'&!%)%&$5$+$!%)!#'5(-,)+$!($.&,-!-.!(,*)$#&!'5(+-!/$!#$,#$&',$=!!

"#$%&! (,*)$#&! $%&$! /$! @'(&! *! #*+'9*,',$! ).&,$! T.%&)&-&$!*@!U.7),*.5$.&'+!0$/)#).$! '&!y',*+).%r'!

T.%&)&-&$&!2&*#rA*+5!%)!!2$#&)*.!*.!I+).)#'+!V$%$',#A!).!":).:!

'./!E%1#A*+*:1!'&!&A$!S.)7$,%)&1!/).!2*-&A$,.!I'+)@*,.)'=!

! E,*)$#&$+$!)5(+)#'!-.!(,*#$%!/$!5*.)&*,)8',$!).!#'/,-+!#',-)'!:$5$.))!/).!2-$/)'!%-.&!).&$,7)$7'&)!

&$+$@*.)#=!I$)!#',$!)./$(+).$,%#!'.-5)&$!#,)&$,))!&,$#!).&,C*!@'8'!%-($,)*','!'!(,*:,'5-+-)=!")#)!#$)!#',$!7*,!

%'! (',&)#)($! %-.&! (-%)! %-9! *9%$,7'&)'! -.$)! $#A)($! /$! 5$/)#)=! I*.%-+&'&))+$! ).#+-/$! $D'5$.$! .$-,*C

@)8)*+*:)#$6!&$%&$!/$!+'9*,'&*,6!$.#$@'+*:,'5$6!$7)/$.&$!(%)A*+):)#$6!#-+$:'./-C%$! &*&*/'&'!/$%(,$!)%&*,)#-+!

@'5)+)$)=!

! 0')! 5-+&$! ).@*,5'&))! /$%(,$! %&-/))+$! '%-(,$'! :$5$.)+*,! +$! (-&$&)! :'%)! +'B!

A&&(Bssnnn=)55=r)=%$sLiTbsLiTbSyi=hL0!

! S+&)5'!(',&$!'!(,*)$#&-+-)!$%&$!*,:'.)8'&'!#-!'M-&*,-+!-.$)!).&$,@$&$!n$9!%#,)%'!).!E$,+!%)!01%;+=!

! T.!@)$#',$!.*'(&$!!&*'&$!/'&$+$!%-.&!%&*#'&$!).&,C*!9'8'!/$!/'&$!51%;+=!

!

3.7.1 Localizarea gemenilor nedistribuiti

!

S,5'&*',$'!#*5'./'!$%&$!@*+*%)&'!%(,$!'CT!%$+$#&'!($!#$)!#$!-,5$'8'!'!).&,'!).! @'8'!%-($,)*','!'!

(,*)$#&-+-)B!

!

SELECT

CONCAT(p1.id, p1.tvab) + 0 AS tvid,

! YY

CONCAT(p1.christian_name, " ", p1.surname) AS Name,

p1.postal_code AS Code,

p1.city AS City,

pg.abrev AS Area,

IF(td.participation = "Aborted", "A", " ") AS A,

p1.dead AS dead1,

l.event AS event1,

td.suspect AS tsuspect1,

id.suspect AS isuspect1,

td.severe AS tsevere1,

id.severe AS isevere1,

p2.dead AS dead2,

l2.event AS event2,

h2.nurse AS nurse2,

h2.doctor AS doctor2,

td2.suspect AS tsuspect2,

id2.suspect AS isuspect2,

td2.severe AS tsevere2,

id2.severe AS isevere2,

l.finish_date

FROM

twin_project AS tp

/* For Twin 1 */

LEFT JOIN twin_data AS td ON tp.id = td.id

AND tp.tvab = td.tvab

LEFT JOIN informant_data AS id ON tp.id = id.id

AND tp.tvab = id.tvab

LEFT JOIN harmony AS h ON tp.id = h.id

AND tp.tvab = h.tvab

LEFT JOIN lentus AS l ON tp.id = l.id

AND tp.tvab = l.tvab

/* For Twin 2 */

LEFT JOIN twin_data AS td2 ON p2.id = td2.id

AND p2.tvab = td2.tvab

LEFT JOIN informant_data AS id2 ON p2.id = id2.id

AND p2.tvab = id2.tvab

LEFT JOIN harmony AS h2 ON p2.id = h2.id

AND p2.tvab = h2.tvab

! YZ

LEFT JOIN lentus AS l2 ON p2.id = l2.id

AND p2.tvab = l2.tvab,

person_data AS p1,

person_data AS p2,

postal_groups AS pg

WHERE

/* p1 gets main twin and p2 gets his/her twin. */

/* ptvab is a field inverted from tvab */

p1.id = tp.id AND p1.tvab = tp.tvab AND

p2.id = p1.id AND p2.ptvab = p1.tvab AND

/* Just the sceening survey */

tp.survey_no = 5 AND

/* Skip if partner died before 65 but allow emigration (dead=9) */

(p2.dead = 0 OR p2.dead = 9 OR

(p2.dead = 1 AND

(p2.death_date = 0 OR

(((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)

>= 65))))

AND

(

/* Twin is suspect */

(td.future_contact = 'Yes' AND td.suspect = 2) OR

/* Twin is suspect - Informant is Blessed */

(td.future_contact = 'Yes' AND td.suspect = 1

AND id.suspect = 1) OR

/* No twin - Informant is Blessed */

(ISNULL(td.suspect) AND id.suspect = 1

AND id.future_contact = 'Yes') OR

/* Twin broken off - Informant is Blessed */

(td.participation = 'Aborted'

AND id.suspect = 1 AND id.future_contact = 'Yes') OR

/* Twin broken off - No inform - Have partner */

(td.participation = 'Aborted' AND ISNULL(id.suspect)

AND p2.dead = 0))

AND

l.event = 'Finished'

/* Get at area code */

AND SUBSTRING(p1.postal_code, 1, 2) = pg.code

! Y^

/* Not already distributed */

AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)

/* Has not refused or been aborted */

AND NOT (h.status = 'Refused' OR h.status = 'Aborted'

OR h.status = 'Died' OR h.status = 'Other')

ORDER BY

tvid;

! !

! I'&$7'!$D(+)#'&))B!

!

CONCAT(p1.id, p1.tvab) + 0 AS tvid

!

! K*,)5!%*,&',$'!#A$)+*,!)/!%)!&7'9!).!*,/).$!.-5$,)#'=!"/-:'./!p!,$8-+&'&-+-)!/$&$,5).'!01%;+C-+!

%'!,$#-.*'%#'!,$8-+&'&-+!/,$(&!.-5',=!

!

#*+*'.'!)/!! "#$'%&'!)/$.&)@)#'!*!($,$#A$!/$!:$5$.)=!"#$'%&'!$%&$!*!#A$)$!).!&*'&$!&'9$+$+$=!

#*+*'.'!&7'9! "#$'%&'!$%&$!-.!:$'5'.!).&,C*!($,$#A$=!",$!*!7'+*',$!/$!O!%'-!R=!

!

#*+-5.!(&7'9! "#$'%&'!$%&$!).7$,%'!+-)!&7'9=!"&-.#)!#'./!&7'9!$%&$!O!'#$'%&'!$%&$R6!%)!!

7)#$7$,%'=!UD)%&'!($.&,-!'!$#*.*5)%)!&)5(-+!/$!,$/'#&',$!%)!($.&,-!'!#*.@$,)!01%;+C-+-)!

(*%)9)+)&'&$'!/$!'!*(&)5)8'!'#$'%&'!#*5'./'=!

!

! "#$'%&!#*5'./'!/$5*.%&,$'8'6!(,).&,$!'+&$+$6!#-5!%'!#'-&'&)!-.!&'9$+!/).!'#$+'%)!&'9$+!@*+*%)./!*!

#*59).',$! >(O! %'-! (R?=! T.! $D$5(+-6! '#$'%&'! $%&! @*+*%)&'! ($.&,-! '! 7$,)@)#'! '&-.#)! #'./! (',&$.$,-+! -.-)!

:$'5'.!.'!5-,)&!).').&$'!7',%&$)!/$!^Z=!K'#'!$%&$!'%'6!'&-.#)!,'./-+!.-!$%&$!)./)#'&!).!,$8-+&'&=!

! L*&!#$!$%&$!5')!$D)%&'! &*'&$! &'9$+$!#-!*! ).@*,5'&)$!/$! &)(!:$5$.)=!"7$5!*!#A$)$!($.&,-! )/6! &7'9!

>&*'&$!&'9$+-,)+$?!#'&!%)!)/6!(&7'9!>($,%*.k/'&'?!($.&,$!'!@'#$!#*5$.8)+$!5')!,'()/$=!

! E$! #'+#-+'&*,-+! .*%&,-! >Sb!S+&,'2E"VI!Rpp0A8?! '#$'%&'! #*5'./'! ).&*',#$!OZpCRpp! ,'./-,)! %)!

/-,$'8'!5')!(-&).!/$!*!%$#-./'=!

! b-5',-+!#-,$.&!/$!).,$:)%&,',)!/).!&'9$+$+$!@*+*%)&$!5')!%-%B!

Table Rows

person_data 71074

lentus 5291

twin_project 5286

twin_data 2012

informant_data 663

harmony 381

postal_groups 100

! Yf

!

3.7.2 Vizualizarea unui tabel dupa “perechi de gemeni”

!

! e)$#',$!).&$,7)$7',$!%$!&$,5).'!#-!-.!#*/!`$7$.&a=!!

! I*5'./'! ','&'&'! ')#)! $%&$! @*+*%)&'! ($.&,-! '! '@)%'! -.! &'9$+! #-! (,)7),$! +'! ($,$#A)+$! /$! :$5$.)!

#*59).'&$!).!@-.&)$!/$!`$7$.&a=!

! "#$'%&'!)./)#'!/).!#'&$!($,$#A)!'59))!:$5$.)!'-!@).'+)8'&!(,*:,'5-+!%)!).!#'&$!($,$#A)!-.!:$'5'.!'!

&$,5).'&!)',!#$+'+'+&!'!@*%&!,$@-8'&6!%='=5=/=!

!

SELECT

t1.event,

t2.event,

COUNT(*)

FROM

lentus AS t1,

lentus AS t2,

twin_project AS tp

WHERE

/* We are looking at one pair at a time */

t1.id = tp.id

AND t1.tvab=tp.tvab

AND t1.id = t2.id

/* Just the sceening survey */

AND tp.survey_no = 5

/* This makes each pair only appear once */

AND t1.tvab='1' AND t2.tvab='2'

GROUP BY

t1.event, t2.event;

!

3.8 Utilizarea MySQL cu Apache

!

! UD)%&'! (,*:,'5$! #$! 7'! ($,5)&! %'! '-&$.&)@)#'&)! -&)+)8'&*,))! /).&,C*! 9'8'! /$! /'&$! 51%;+! %)! %'! 7'!

($,5)&'!+*:',$'!@)%)$,$+*,!K7%=!).&,C-.!&'9$+!51%;+=!

! E-&$&)! 5*/)@)#'! @*,5'&-+! /$! +*:',$! "('#A$! ($.&,-! '! @)! #)&)&! #-! -%-,).&'! /$! 51%;+! 5*/)@)#'./!

-,5'&*',$'!+).)$!).!@)%)$,-+!/$!#*.@):-,'&)$!"('#A$B!

LogFormat \

"\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \

\"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

! Yj

!

T.!01234!(-&$&)!@'#$!'#$+'%)!+-#,-!/-('!#-5!-,5$'8'B!

!

LOAD DATA INFILE '/local/access_log' INTO TABLE table_name

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'