MySQL 5.0.  
 


    ,     MySQL.              Windows.  ,     MySQL,     ,        SQL,        .  -   PHP, Perl  Java           MySQL   .   ,    ,    ,         .





 

MySQL 5.0.  








MySQL       ()   .         .            .

    ,    MySQL.   ,           Windows.  ,     MySQL,  ,        .   ,         SQL.  -   PHP, Perl  Java          MySQL   .          .  ,         ,    ,     .     .

 1. .        MySQL,           .  ,     ,     MySQL,       .

 2.      SQL.   SQL-,       .   ,     , ,    .

 3.     SQL.    :        ,     ,     ,          .

 4.      -.    ,     MySQL    PHP, Perl  Java.          ,    ,     ,    -,   .

 5.   .       MySQL     ,           .

 6. .          MySQL.

  ,          ,   -  .


   

   ,          AlexanderZhadaev@sigmaplus.mcdir.ru      www.sigmaplus.mcdir.ru (http://www.sigmaplus.mcdir.ru/) (       ,         ).



 


 

 ,         gurski@minsk.piter.com ( ,  ).

     !

   http://www.piter.com (http://www.piter.com/)       .




 1





            :  ,    MySQL.         .




1.1.   MySQL


MySQL     ,   MySQL AB (www.mysql.com (http://www.mysql.com/)). MySQL  - :   MySQL     ,      .    MySQL,      .

MySQL      .          (http://dev.mysql.com/downloads/ (http://dev.mysql.com/downloads/))     .    MySQL,      .    MySQL            MySQL AB.

MySQL   .         ,    Windows, Linux, Mac OS, Solaris, HP-UX  .        MySQL    Windows.

MySQL     (API),      MySQL   ,    C/C++, Eiffel, Java, Perl, PHP, Python, Tcl, ODBC, NET  Visual Studio.   4  ,      MySQL  PHP-, Perl  Java-.

MySQL    : ,  , ,  (-   MySQL-,    60. ,   5 ).

MySQL             (. 5).

MySQL    ,       .       MySQL        .     .




1.2.      



    ,     .    ,   ,    , ,    .







      ,   .          .

 ,      (. 1.1).


 1.1. Customers ()




          .

       ,      . ,       id  rating  ,    name, phone  address  .

 ,           .         ,         . ,     533    ,    name ()    ,  phone ()  313-48-48  ..

          .




 


      ,        .           (primary key).

      ,      .  ,       ,         ,          ,    .

      ,     . ,   Customers () (.  1.1)   id ().       ,   (   ). ,   Customers  name ()     ,      ;     name ()  phone ()    .         (  ),   (   ,     ),      (      ,   ).

         .




  .  


        .            ,        ()  .          (,         ,     ),    (       ),    (     ,       )   .

  ,     . ,     A  B,     .       A     ,        B.    ,    A,   ,  . ,    ,         B,           B.         A      B,    .    A   ,   B  .

   ,      .

    .

     .         A       B.

           (foreign key)    ,       ( ,         ).

 ,     ,  ,        Customers () (. 1.2).


 1.2. Orders ()




  Orders     customer_id (),        Customers ().  ,    Orders       Customers. ,     1012    customer_id ()  533:  ,   1012    .

 product_id  Orders            id ()  Products ().  ,  Orders       Customers  Products.

    .

    ,          ,  . ,      ,    Passports (),        Customers ().

,     ,    .         (,       Passports),   (      ),     (     ,    ,       ,  ,      ,   ).

        ,     ,          .         ,         .

   .

            . ,        ,       Orders ()  Products ()       Items ( ),   product_ id (    Products), qty (     )  order_id (    Orders).    product_id  qty   Orders .  ,  Items       Orders  Products     Items       .

 ,        ,     (  )      .    ,    ,    ,   ,    .       : ,  ,         ?         ,  ,   ,     ,           .

,         .




 


 ,   ,     .

  ,        ,       ( ).

  ,           .         . ,    Customers ()       533 (          ),         ,           1012  1014.

  ,         ,      .  ,           .    ,       (NULL),       . ,   Orders ()  ,    customer_id  999.       Customers  Orders:   ,    ,        customer_id     NULL,   ,      ,   .

    ,    ,      .           .

              .      .             ,             .     .

 (RESTRICT):            ,             . ,         Customers (),       ,      Orders (),         .

 / (CASCADE):               ;                     .

,        Customers ()       ,       Orders ().

 (SET NULL):                 ,   ,    (NULL). ,        Customers ()      ,       Orders ()   customer_id ()   NULL.

  MySQL            .

       .     ,      ,   .          ,   .                .        , ,    ,    . ,         :               ,    -       ,        .

,          ,      .




1.3.   


   (    ,   )   .      ,      ,         .

      .



    .

  ,    ,      .        .   (, , )   ,         .

,         ,            ,    ,        ,      ,          ,  ..          .



  ,    .               .

  ,    :  ,        ,      .       ,     .

           ,           .            ,    .

       :            . ,   ,   . 1,    ,     ,  ,   .

 : ,  (  ),   , ,   ,    .

 : , , ,  ,     ,   .

 :  ,   ,    ,      ,  ,  ,   ,     (  ,         ).



.

       .           (,             ,        ).

           .        .

     ,    ,          ,       .

  ,    ,       . , ,  address ()   Customers () (. . 1.1).       ,     .         ,   ..,     Customers      address     city (), street (), building ()  ..

     ,                       .

 ,   ,      ,            .    ,        ,                .

,   ,   . 1,         ,   ,      (  ,              ,       ).  ,              ,       ,      .          ,      .  ,            .         ,   ,       ,     ,   ,    .      .





 A      B,    A       B,  ,         B   A  .


     ,                 .

,     . 1,    ,      ,           .  ,           ,      .       ,         ,        .

         ,   ,     ,          .

,      ,          MySQL.   MySQL      ,     ,   .




1.4.    MySQL



    ,    MySQL     .       .




 MySQL


  ,  MySQL      -. Windows- MySQL    - http://dev.mysql.com/downloads/mysql/5.0.html (http://dev.mysql.com/downloads/mysql/5.0.html)    :

Windows downloads       MySQL  32-  : Windows Vista/Server 2003/XP/Millennium Edition/2000/98/95;

Windows x64 downloads       MySQL  64-  : Windows Vista x64/Server 2003 x64/ XP x64.

        :

Essentials      .    (Configuration Wizard);

ZIP/Setup.EXE   ,   ,   .,    ;

Without installer   ,           .

     . ,   ,    .       MySQL     .

      ,     ,        MySQL GUI Tools (http://dev.mysql.com/downloads/gui-tools/5.0.html (http://dev.mysql.com/downloads/gui-tools/5.0.html)),   :

MySQL Administrator   , , , /  MySQL,    ;

MySQL Query Browser   ,       ;

MySQL Migration Toolkit       MySQL      (Oracle, Microsoft SQL Server, Microsoft Access, Sybase  .).





     ,    MySQL Administrator  MySQL Query Browser,           .


   .      MySQL 5.0.




  MySQL


    Windows-  MySQL,   .

1.   (Setup Wizard):

      MySQL,       mysql-essential-5.0.xxx-win32.msi.;

      MySQL,      Setup.exe   ,     .

2.     (.1.1)   Next ().




.1.1.    



3.   (.1.2):

Typical ()      MySQL:     ;

Complete ()     MySQL,       ;

Custom ()      ,      MySQL,   ,   .




.1.2.   



   Custom (),      .   Next ().

     Typical ()  Complete (),    .

     Custom (),     (.1.3).




.1.3.   



         - ,     ,               (




  ,




   ).      ,    ,     .

    ,   Change ().    (.1.4)       Folder name ( )    Look in ( )      ,      ,     .   OK.




.1.4.   



   ,   Next ().

4.       (.1.5).    ,   Back ().    ,      Install ().




.1.5.  



5.        (.1.6).          Next ().




.1.6.  



6.    ,   Configure the MySQL Server now (  MySQL ).   Finish () (.1.7).




.1.7.   MySQL      MySQL.




  MySQL


     MySQL     (Configuration Wizard).   ,     Configure the MySQL Server now (  MySQL ).      ,           ? MySQL ? MySQL Server 5.0 ? MySQL Server Instance Config Wizard.

   MySQL       .

1.     (.1.8)   Next ().




.1.8.    



2.    (.1.9):

Detailed Configuration (   )                  ;

Standard Configuration (   )       ,       .




.1.9.   



   Detailed Configuration (   ),           .

  Next ().

    Standard Configuration (   ),   38 .

3.  MySQL,    ,       (.1.10):

Developer Machine ( )      ,    MySQL     .       ( ,  ,  );

Server Machine ()   MySQL   ,       (,  -).     ;

Dedicated MySQL Server Machine ( MySQL-)      ,     MySQL.    .




.1.10.   



    MySQL    ,    Developer Machine ( ).   Next ().

4.     (.1.11)    ,       .     MySQL  InnoDB  MylSAM.  InnoDB             (       )    .  MylSAM    ,         .




.1.11.           :



Multifunctional Database (  )    ,       InnoDB,    MylSAM;

Transactional Database Only (  )  ,        InnoDB (     ).         ;

Non-Transactional Database Only (  )  ,       (MylSAM  .).     -    .

    ,     ,    Multifunctional Database.   Next ().

    Non-Transactional Database Only (  ),    .

5.    Multifunctional Database (  )  Transactional Database Only (  ),        ,       InnoDB (.1.12).                .




.1.12.       InnoDB



    .       Next ().

   ,       ().               .        




      Windows   .     Next ().

6.   /,     (.1.13):

Decision Support (DSS)/OLAP (   /  )        20;    100  ;

Online Transaction Processing (OLTP) (   /    )    500  ;

Manual Setting ( )         .

  Next ().




.1.13.     



7.          (.1.14).




.1.14.    



         MySQL   ,      Enable TCP/IP Networking ( TCP/IP-).

      Port Number ( )   (   3306).   Add firewall exception for this port (     )        Windows.        ( ?   ?  Windows ?  ?  ).           Add firewall exception for this port (     ).

     ,   Enable Strict Mode (  ).                  (         ).    .

      MySQL   ,      Enable TCP/IP Networking ( TCP/IP-).       Port Number ( )   (   3306).   Add firewall exception for this port (     )        Windows.        ( ?   ?  Windows ?  ?  ).

  Next ().

8.   ( ),        (.1.15).




.1.15.    



       ( ,   ),    ,    ,    .   ()         ,   KOI8-R, CP-866 ( DOS), CP-1251 ( Windows), UTF-8 (Unicode)  . ,     :

Standard Character Set   Latin1;

Best Support For Multilingualism (  )  Unicode (UTF-8);

Manual Selected Default Character Set / Collation (       )      .

    ,       ,    Best Support For Multilingualism (  ).   ,      ,         .

  Next ().

9.   Windows,     MySQL (.1.16).




.1.16.   Windows



    MySQL   Windows,   Install As Windows Service (   Windows).               Windows,   Launch the MySQL Server automatically (   MySQL).

   Include Bin Directory in Windows PATH (  bin   Windows PATH),                  (    bin       Path).



!

    Install As Windows Service (   Windows),   .            root     MySQL.


       .

  Next ().

10.    MySQL (.1.17).




.1.17.   



   New root password (  root)  Confirm ()   root (         MySQL).

    root      .     Enable root access from remote machines (  root    ).        root    .

     ,   Create An Anonymous Account (  ).    ,      .

  Next ().

11.       Execute () (.1.18).




.1.18.  MySQL



12.     Finish ().

      my.ini,   ,    MySQL.

     MySQL   Windows,       .       .          root  ,        (   ,       ).          MySQL.

      MySQL (       MySQL).       ,      .




 MySQL GUI Tools


    MySQL,   .

1.    MySQL GUI Tools (Setup Wizard),      mysql-gui-tools-5.0xxx-win32.msi.

2.     (.1.19)   Next ().




.1.19.    



3.    ,    I accept the terms in the license agreement (    ) (.1.20).   Next ().




.1.20.  



4.      (.1.21),   Change ().




.1.21.   



       (. .1.4)        Folder name ( )    Look in ( )     ,      ,     .   OK.

     Next ().

5.    (.1.22):

Complete ()     ;

Custom ()   .




.1.22.   



       ,   Complete ().       -   ,   Custom ().   Next ().

     Complete (),   .

6.     Custom (),      (.1.23).          - ,     ,               (




  ,




   ).




.1.23.   



   ,   Next ().

7.        (.1.24).    ,   Back ().    ,      Install ().




.1.24.  



8.        (. .1.6).          Next ().      Finish ().

,  MySQL .      MySQL    .       .




1.5.    MySQL



    ,     MySQL    .     MySQL     Windows,        .          (.      MySQL   )      MySQL Administrator (.      MySQL   MySQL Administrator).

        (.       )      MySQL Query Browser (.       MySQL Query Browser).




    MySQL   


  MySQL      :

     mysqld-nt.exe,    bin ,    MySQL.

    Windows.     ,     ,           cmd    OK.       (.1.25).




.1.25.   



    

mysqld-nt

   Enter.  MySQL  .

       bin        Path,          ,      , :

C: \Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt

            ,  mysqld-nt 

mysqld-nt console



!

    MySQL      root,         (        root  ).


   root,         :

mysqladmin -u root password <>

( C: \Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin -u root password <>,     bin        Path   )    Enter.

 ,      root,    ,      :

mysqladmin -u root -p password < >

   Enter password ( )       Enter.

,     MySQL,  

mysqladmin -u root -p shutdown

     Enter password ( )    root.   Enter.  MySQL  .

     MySQL      MySQL Administrator.




    MySQL   MySQL Administrator


   MySQL     MySQL Administrator,   .

1.  MySQL Administrator ( ?   ? MySQL ? MySQL Administrator).        (.1.26).




.1.26.     MySQL



2.  Ctrl ,  ,    Skip (),         Cancel ().      MySQL Administrator (.1.27).




.1.27.   MySQL Administrator



3.   MySQL Administrator      Service Control ( ).

4.  MySQL      Windows,   Start Service ( ),     , .     :

1)   Configure Service ( ).     Install new Service (  )   ;

2)          OK;

3)  Config Filename (  )      my.ini (.1.28),  C: \Program Files\ MySQL\MySQL Server 5.0\my.ini.    ,    ;     ,    ;

4)  Path to binary (   )     mysqld-nt.exe,  C: \Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt;

5)  Apply Changes ( );

6)   Start/Stop Service (/ ).




.1.28.  Configure Service 5.   Start Service ( ).  MySQL  .





!

    MySQL      root,         (        root  ).    MySQL Administrator   root ,           mysqladmin (.      MySQL   ).


   MySQL   MySQL Administrator,   .

1.  MySQL Administrator ( ?   ? MySQL ? MySQL Administrator).        (. .1.26).

2.        :

Server Host ( )  localhost ( );

Port ()  ,     (   3306);

Usename ( )  root;

Password ()   root.   OK.

3.   MySQL Administrator      Service Control ( ).

4.      Stop Service ( ).  MySQL  .

    ,    MySQL     Windows.




    MySQL   


  MySQL     Windows     (.    MySQL)     MySQL Administrator (.      MySQL   MySQL Administrator),            .

   ,   ,      ,          , ,         .      (.1.29)     .




.1.29.  MySQL   



       MySQL (          MySQL Administrator).        :  ,     .

    MySQL ,    .     ,   .




     


    MySQL   ,   .

1.    Windows.     ,     ,           cmd    OK.

2.   (. .1.25)  

mysql -h < > -u < > -p

( < >   ,    )    Enter.    Enter password ( )   .

     MySQL,     ,   (localhost)   , 

mysql -u root -p

         mysql> (.1.30).        :  ,    ,     .




.1.30.    MySQL   



   ,      

exit

   Enter.

     MySQL    MySQL Query Browser.




     MySQL Query Browser


 MySQL Query Browser    ,     SQL.  ,   .          MySQL Query Browser,        .

1.  MySQL Query Browser ( ?   ? MySQL ? MySQL Query Browser).        (.1.31).




.1.31.     MySQL



2.        :

Server Host ( )  ,     MySQL;

Port ()  ,     (   330 6);

Username ( )  ;

Password ()  ;

Default Schema (  )   ,      (    ,     ).

3.  OK.       ,        Yes ()     .

         MySQL Query Browser (.1.32).          :  ,    ,     .




.1.32.   MySQL Query Browser





!

,      MySQL Query Browser   SQL-,    .       ,     (, Arial  Book Antiqua).      MySQL Query Browser   Tools ()    Options ().    Options ()      General Options ( )       Code Font ( )     .   Apply ().


   ,    MySQL Query Browser.

      MySQL     .




1.6. 


      MySQL    MySQL Administrator  MySQL Query Browser.          MySQL,       .   ,          .

,        MySQL.    .    ,   ,           .




 2

     SQL



    ,       MySQL,    ,    ,    .    SQL     ,        .

  SQL-          MySQL.         .     ,   MySQL AB:    mysql    MySQL Query Browser.

       .  MySQL Query Browser     :    ,     (  SQL- UPDATE),   ,        (         ),    ,      .       MySQL Query Browser,      ,      http://dev.mysql.com/doc/query-browser/ru/index.html (http://dev.mysql.com/doc/query-browser/ru/index.html).

  ,   SQL-  MySQL Query Browser    ,        SQL-.




2.1.  SQL-


   SQL-,      MySQL (  ,    1).     ,   SQL-      .

    ,    SQL-           Enter     .       ,       

SET NAMES cp866;

       .2.1.




.2.1.     



 SET NAMES           .    ,     ( mysql)   CP-866 (    Windows),             .

          SQL-.     ,           (.2.2).




.2.2.  SQL-   



 mysql      ( .2.3     SHOW DATABASES).          ,     Enter      ,     .       ,  \c (.2.3).




.2.3.  



   MySQL Query Browser,            UTF-8      .   MySQL Query Browser         (,    SQL-, .2.4).       ,     (  ,     ).      .




.2.4.  SQL-  MySQL Query Browser



        SQL-,    .2.4.   (      )    ,     - .    ,      Execute,     ,    Ctrl+Enter.         ,            .

,     SQL-,        .      ,        .




2.2.   


    ,      ,      ,   ,          MySQL.

   ,  

CREATE DATABASE <  >;

, 

CREATE DATABASE SalesDept;

     SalesDept ( ).

   -          ,   ,    MySQL,           (character set) /   ()  :

CREATE DATABASE <  >

CHARACTER SET < >

COLLATE <  >;

,        ,     CP-1251,          :

CREATE DATABASE SalesDept

CHARACTER SET cp1251 COLLATE cp1251_general_ci;





     MySQL ,   SHOW CHARACTER SET;          SHOW COLLATION;.      LIKE: ,        CP-1251,   SHOW COLLATION LIKE %1251%;.  _ci (case insensitive)     ,         ,  _cs (case sensitive)  ,  _bin (binary)        .         (    ,        )  - http://www.collation-charts.org/mysql60/ (http://www.collation-charts.org/mysql60/).


,     ,        ,       .

  /            

ALTER DATABASE <  >

CHARACTER SET < >

COLLATE <  >;

  ,       ,  ;   ,       .

       ,  

DROP DATABASE <  >;



!

      ,           ,   .        .


  ,     MySQL,           

USE <  >;

,:

USE SalesDept;

          ,          . ,     Customers ()   SalesDept ( )   SalesDept.Customers   Customers.   ,         ,           .                .

    ,     MySQL,  

SHOW DATABASES;

         ,         .

INFORMATION_SCHEMA    ,          ,         : , ,    ,  ,  ,   .          ,          :  ,    ,    MySQL.

mysql    ,    MySQL.           ,    .

test    ,         .

,    ,       :  CREATE DATABASE (), ALTER DATABASE (), DROP DATABASE (), USE (   )  SHOW DATABASES (   ).      .    ,    -          .




2.3.   



    ,  ,    ,             .        .




 


  ,  ,    2.1.


 2.1.   

CREATE TABLE < >

(<  1> <  1> [<  1>],

<  2> <  2> [<  2>],



[<     >])

[<  >];



  ,       ,       (  2.1      ).         ,       ;      .

   ,      CREATE TABLE   ,     ,    (  ) .

,     ,     1.     ,      SalesDept ( )      .    : Customers (), Products ()  Orders ().   2.2     Customers.


 2.2.    Customers

CREATE TABLE Customers

(id SERIAL,

name VARCHAR(100),

phone VARCHAR(20),

address VARCHAR(150),

rating INT,

PRIMARY KEY (id))

ENGINE InnoDB CHARACTER SET utf8;



    : -,   , -,    ,      (.  . 1.1   1).

id   .      SERIAL,     .   SERIAL   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.  ,        (BIGINT)  (UNSIGNED) ,          (NOT NULL UNIQUE).             ,   MySQL        (AUTO_INCREMENT).





NULL   ,    .      NULL,  ,          (    NULL  ).    NULL    ()   0.  NULL   :      NULL,      NULL. ,   1 = 1    (TRUE),    NULL = NULL    (NULL),        .


Nam   , phone     address  .      VARCHAR,      .          .

Rating  .  INT ,       .

-,  ,   id    ,       PRIMARY KEY (id).

-,        .  ENGINE   .  Customers    InnoDB,            (           ).  CHARACTER SET        .         name, phone  address,         UTF-8,          Customers.

 ,   ,    Products (),    2.3.


 2.3.    Products

CREATE TABLE Products

(id SERIAL,

description VARCHAR(100),

details TEXT,

price DECIMAL(8,2),

PRIMARY KEY (id))

ENGINE InnoDB CHARACTER SET utf8;



        Customers          .  id ( )  description ( )  Products     .  details ()   TEXT.       VARCHAR,      :        VARCHAR  65 535    ,        TEXT  .   TEXT         ,          .

 price ()   DECIMAL,        ,      .      :          ,        .  ,           (6 = 82)       .

, ,       Orders (),    2.4.


 2.4.    Orders

CREATE TABLE Orders

(id SERIAL,

date DATE,

product_id BIGINT UNSIGNED NOT NULL,

qty INT UNSIGNED,

amount DECIMAL(10,2),

customer_id BIGINT UNSIGNED,

PRIMARY KEY (id),

FOREIGN KEY (product_id) REFERENCES Products (id)

ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY (customer_id) REFERENCES Customers (id)

ON DELETE RESTRICT ON UPDATE CASCADE)

ENGINE InnoDB CHARACTER SET utf8;



  Orders    :  product_id ()      Products,   customer_id ()     Customers (.  . 1.2   1).          ,  product_id  customer_id    BIGINT UNSIGNED.

,       (      1),    MySQL,       . ,  FOREIGN KEY (customer_id) REFERENCES Customers (id) ,    customer_id       id  Customers    (NULL),    .   product_id          NOT NULL,       .           (      1).  ON DELETE RESTRICT ,      ,       ,      ,     - .  ON UPDATE CASCADE ,        Customers      Products       Orders.





 ,   Orders     ,       Customers  Products    ,         Orders. ,           ,         ALTER TABLE,        .


          .      ,       .      MySQL     ,          , ,           .


   MySQL

   ,          .  MySQL       , /    ().  ,       (spatial) ,       .

   .

BIT[(<>)].

 ,    .     ,     .

TINYINT.

      -128  127,  (   UNSIGNED)  0  255.

BOOL  BOOLEAN.

     TINYINT(1) (       , .  ).        (TRUE),     (FALSE).

SMALLINT.

      -32 768  32 767,  (   UNSIGNED)  0  65 535.

MEDIUMINT.

      -8 388 608  8 388 607,  (   UNSIGNED)  0  16 777 215.

INT  INTEGER.

      -2 147 483 648  2 147 483 647,  (   UNSIGNED)  0  4 294 967 295.

BIGINT.

      -9 223 372 036 854 775 808  9 223 372 036 854 775 807,  (   UNSIGNED)  0  18 446 744 073 70 9 551 615.

SERIAL.

  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE (    ,     ;  NULL ).          .   UNSIGNED  AUTO_INCREMENT     ,   NOT NULL  UNIQUE     .





    ,  BOOL (BOOLEAN)  SERIAL,       ,      ZEROFILL:      ,       . ,      INT(5) ZEROFILL,   1234567  12345   ,   123   00123.    BIT     ,      .


FLOAT.

       -3,402823466


  -1,175494351


  1,175494351


  3,402823466


 (   0)    7   (     ).

DOUBLE, DOUBLE PRECISION  REAL.

       -1,7976931348623157


  -2,2250738585072014


   2,2250738585072014


  1,7976931348623157


(   0)    15   (     ).

FLOAT(<>).

    0  24      FLOAT,    25  53   DOUBLE.

DECIMAL, DEC, NUMERIC  FIXED.

 ()    .    65     30     (   10    0   ).





   ()  ,  FLOAT(<>),       ,               . ,       FLOAT(7,5),  ,           (2 = 75)            5    .          255    30,          ,        ,        7 (  FLOAT)  15 (  DOUBLE)  ,       .          65    30.      ,   , , 10  0.         .


    ,   ,      :

UNSIGNED    ,      (  -) .           ,  BIT, BOOL (BOOLEAN)  SERIAL.       UNSIGNED      .

ZEROFILL    ,       .            ,            . ,     DOUBLE(10,5) ZEROFILL,   12.23   0012.23000.  ,     ,    UNSIGNED.   ZEROFILL        ,  BIT, BOOL (BOOLEAN)  SERIAL.

AUTO_INCREMENT        .  ,       (NULL)        ,     (     ,          ).           ,  BIT  DECIMAL (DEC, NUMERIC, FIXED).          ,          (         ).

   ,      .

 ,     / ,        .

DATE.

   YYYY-MM-DD,    0000-01-01  9999-12-31.

DATETIME.

     YYYY-MM-DD HH:MM:SS    00000101 00:00:00  9999-12-31 23:59:59.

TIMESTAMP.

    YYYY-MM-DD HH:MM:SS    1970-01-01 00:00:00     2038.           TIMESTAMP        (          ).  ,        ,   TIMESTAMP   DEFAULT CURRENT_TIMESTAMP.

        TIMESTAMP,         .        -      TIMESTAMP,   /       NULL,      .

TIME.

   HH:MM:SS    -838:59:59  838:59:59.

YEAR, YEAR(2), YEAR(4).

   YYYY  YY (    ,   YYYY).     1901  2155,    YYYY,   70 ( 1970.)  69 ( 2069.),    YY.

,  MySQL        .           ,  2007@12@31 23%59%59,   ,  20071231235959.  ,                  , MySQL       / .

   ,   .

,    ,       .

CHAR(< >)  NATIONAL CHAR(< >).

   .        ,      .       0  255.     ,       1 .

  NATIONAL CHAR   CHAR ,      NATIONAL CHAR   UTF-8,         CHAR    ,  MySQL.

VARCHAR(<  >)  NATIONAL VARCHAR(<  >).

   ,      .        0  65 535,    65 535          CHAR, VARCHAR, BINARY  VARBINARY.  ,         (    1 ,   KOI8-R, CP-866  CP-1251),    ,     ,    65 535.      UTF-8 (   MySQL   3   ),    ,     ,    21 844 (   ,    ).

  NATIONAL VARCHAR   VARCHAR ,      NATIONAL VARCHAR   UTF-8,         VARCHAR    ,  MySQL.

BINARY(< >)

 ()   .     CHAR,     ,  ,        ,   .

VARBINARY(<  >)

 ()   .     VARCHAR,     ,  .

TINYBLOB

 ()   .    255 .

TINYTEXT

   .    255  ( !).





 ,     TINYTEXT, TEXT, MEDIUMTEXT  LONGTEXT      ,   .    (  KOI8-R, CP-866  CP-1251)        .            ,   . ,   UTF-8       1   ,     2   ,      ,      ,     ,        .


BLOB[(<  >)].

 ()   .     ,     65 535 .    ,       TINYBLOB, BLOB, MEDIUMBLOB  LONGBLOB:      ,      .

TEXT[(<  >)].

   .     ,     65 535 .    ,       TINYTEXT, TEXT, MEDIUMTEXT  LONGTEXT:      ,      .

MEDIUMBLOB.

 ()   .    16 777 215 .

MEDIUMTEXT.

   .    16 777 215 .

LONGBLOB.

 ()   .      4 294 967 295  (4 ),         MySQL    .

LONGTEXT.

   .      4 294 967 295  (4 ),         MySQL    .

ENUM('< 1>', '< 2>',).

,       . ,     ENUM('a','b'),        a, b  NULL (    ,           ;                  ).       65 535 .

SET('< 1>', '< 2>',).

,        (   ). ,     SET('a','b'),       ( ), a, b, a,b  NULL.       64 .      .           ,        ,      . ,    a,b,a,b  b,a      a,b.

  ,   MySQL         .  ,     CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM  SET     CHARACTER SET < > / COLLATE <  > (             ).

,       CP-1251,        Customers ()  UTF-8,  name ()    :



name VARCHAR(100)

CHARACTER SET cp1251 COLLATE cp1251_general_ci



     ,   ,      (           ).       ,   ,     (.    ). ,         ,   ,      MySQL.

,    ,      ,   ,     :  UNSIGNED, ZEROFILL  AUTO_INCREMENT      CHARACTER SET  COLLATE   .    ,     .

 

          .

 NOT NULL.

  ,         (NULL).

     product_id ()  Orders () (.  2.4),    



product_id BIGINT UNSIGNED NOT NULL



      ,         .

     NOT NULL, ,  , NULL           .   ,   NULL,       DEFAULT <>,   .        NOT NULL,                  ,    MySQL   ,      (         ).

NULL.

  ,       (NULL).           TIMESTAMP,       .      ,        NOT NULL.

DEFAULT <>.

       ,  ,           .       ;      TIMESTAMP,            CURRENT_TIMESTAMP (   ).          TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUM-TEXT, LONGBLOB  LONGTEXT,     ,     AUTO_INCREMENT.  ,       (NULL),      NOT NULL.

,     phone ()  Customers ()   ,   ,      :



phone VARCHAR(20) DEFAULT ''



COMMENT ' '.

      255 . ,    rating ()  Customers ()    :



rating INT COMMENT ' '



  ,       UNIQUE  PRIMARY KEY,               .           .        .


   

      ,         (.  2.12.4).     :

[CONSTRAINT < >] PRIMARY KEY (< >).

    (       1).        ,      . ,    ,    NOT NULL.   CONSTRAINT     ,            PRIMARY.

         TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB  LONGTEXT,        ;         ,     .    :



PRIMARY KEY (id)



        Customers (), Orders ()  Products () (.  2.22.4).         id   Products,       description ()  details (),      Products      :



PRIMARY KEY (description,details(10))



         description      details  10 .

INDEX [< >] (< >).

    .     ,          ,    (       6). ,         ,     Customers () (.  2.2)   



INDEX (name)



  ,        TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB  LONGTEXT        ,     .

    .      ,   .

   INDEX       KEY.



[CONSTRAINT < >] UNIQUE [< >] (< >)



     .        :    ,    ,   .  ,      ,        .     (NULL):     ( )   ,           NULL. , 



UNIQUE (address.phone)



    Customers ()  ,    ,      ,    ,    ,      (   phone      NULL),   ,    ,     .

    TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB  LONGTEXT        ,     .

       .    ,     ,     .

   UNIQUE       UNIQUE INDEX  UNIQUE KEY.

 FULLTEXT [< >] (< >).

     .          ( CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT  LONGTEXT)    .       :      ,    ,     ,     .

       c  MyISAM (.    ).         MATCH AGAINST,        3.

    .      ,   .

   FULLTEXT       FULLTEXT INDEX  FULLTEXT KEY.

SPATIAL [< >] (< >).

        ,      .

[CONSTRAINT <  >].



FOREIGN KEY [< >] (< >)

REFERENCES <  >

(<     >)

[<   >]



    (      1).   ,        ()    .       c  InnoDB (  ,       InnoDB),      FOREIGN KEY .

,   ,   ,        .        ,        .    TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB  LONGTEXT      .

        .      ,    .    ,                   (          1).          :

ON DELETE CASCADE       (            );

ON DELETE SET NULL          ;

ON DELETE RECTRICT  ON DELETE NO ACTION ( MySQL    )             .

         ,     ON DELETE RECTRICT.

            :

ON UPDATE CASCADE         (                    );

ON UPDATE SET NULL          ;

ON UPDATE RECTRICT  ON UPDATE NO ACTION ( MySQL    )                 .

         ,     ON UPDATE RECTRICT.

      ,            .




  .


   .

   ,     (https://www.litres.ru/viktor-golcman/mysql-5-0-biblioteka-programmista/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


