Création et alimentation de bases de données SQL

Conception de bases de données
 !"#$!%&
17 février 2017

 4
4
  !!"5
!"''!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!6
$!"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!6
(!) !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!7
*!+,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!8
-!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!9
.!/0 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!10
1!/0!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!11
#$%! &'' %!( '& !
!" 12
!/0!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!12
$!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!12
(!23!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!13
*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!13
&'' %%!   !!"14
!"3!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!14
$!24"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!14
(!/0  4"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!15
')!*! & !+,% & $! - ." /

 17
))!017
&'!&.&&.!!%%&17
 19
119
 20
 21
 26
 ! 27
"# 28
$ % 29
 30
!&
')!*! & !+,% & $! - ." 2

I -  I
   &     $  %'%  '  !&   3$  4&  '%$
'%%'56!%.)!! 4&67!. 8$ %!9
!$.!:% '&
&'  
)4   
'!&4&;&.&"&. 6 <% '& !
!& 3'&' .& 9%3
))5)52)56
    &    . 6$    =  !.  '    '!'%  '  &    
'!%% !$% '&:& 6.'!&..&>!
#.!%'!4&!&%+
        !    !  $  !&       $ )  )4
"  '!&  .    &'' %    !,          !  $
.! =  $8&$."
>%'.!%%+CREATE DROP ALTER
!:!$!& $)"
'!&! &!,. !&  &?. !
&! "
>%'.!%%+GRANT REVOKE
( '& !!($!& ($)2
"'!&.).)$=  !$% <,!&&'' !!
(&!'& !$&>4&!,!&6!. !
..&= &. !'!& !' !=  !$% <,!&
&'' !
>%'.!%%+INSERT UPDATE DELETE SELECT
!:. !$!& $ 6 
"'!& !. !8  !!&& !%! . !
!"
>%'.!%%+COMMIT ROLLBACK
Fondamental : Référence SQL : SQL-99 complete, really
()(*+,,,-()(*+,,,.
https://mariadb.com/kb/en/sql-99)+
1 - https://mariadb.com/kb/en/sql-99/
')!*! & !+,% & $! - ." @

    7
%!9 ! 8'A! .&.).)&.#(
1!"BCDE$& <4&! !%&>!6 &''  !+
#(4&&.& )&F&"BCDD
#(4&G/
#(F%G
#(/
!.4& !! .&$!% BCHI&5
J '=#'!&!GHI'& & 8&  !'=#BCHD"
    8
5HI!&5HD"+- !=!  
5HC!&5B"+% ! !% &
5C/!&5/"+> !6!. !%,&F'!$!' !
 !$ &. !$. !$.
5CC!&52"+#!&. !&(.!&.)'!.&!&6!%'!.&
!.K"&
5/EE2+> !L(
5/EEI+% ! !% &'!&L(!%%"
5/EEH+% ! !% &'!&!%%"
9   8  :;)
!& 8&= %'% !$3..'!&!. 6!. !
 3F%.  %'% !8&  !  8$
. %'% !'&8 ?!%
$'/  !
0
Objectifs
12%!
'
  ' & 4& '% . 67!. 8!,
.!%'!  &   #  '%  !%%      !    .)%$     !$  
.! =  $8&
''+.!'&;8. .! .!.*6
+'33
. ' !&'!9%
M/E% N
J!! !&) % .%4&= .!7! 
J% .%. '&!%$4& '%=  ? => '
&>  % .%  8.    %;%  !%  J  % .%  .!%'!  & . ' !
!&
')!*! & !+,% & $! - ." O