10.    (
This presentation is the property of its rightful owner.
Sponsored Links
1 / 27

Лекция 10. Запросы определения данных ( DDL ). Свойства столбцов и таблиц. PowerPoint PPT Presentation


  • 126 Views
  • Uploaded on
  • Presentation posted in: General

Лекция 10. Запросы определения данных ( DDL ). Свойства столбцов и таблиц.

Download Presentation

Лекция 10. Запросы определения данных ( DDL ). Свойства столбцов и таблиц.

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


10 ddl

10. (DDL). .

. SQL , , . . . .

. . . . , .


10 ddl

, . , , , , .

, Access, . , .

, . :

CREAT VIEW < >

[(< > ,[< > ]...)] AS < SELECT>

, , SELECT. :

DROP VIEW < >.


10 ddl

. . :

1. . , , () .

2. . .

3. . , . .

4. . .

5. . , , .

, .


10 ddl

:

CREAT DOMAIN < > [AS] [DEFAULT < >] [CHECK (< >)]

, . :

CREAT DOMAIN sex_type AS CHAR (VALUE IN (M, F));

IN , IN (SELECT s_type FROM STypes).

ALTER DOMAIN. DROP DOMAIN, : DROP DOMAIN < > [RESTRICT|CASCADE] CASCADE , , , .


10 ddl

:

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

. , . , , NOT NULL. CONSTRAINT , PRIMARY.

TEXT BLOB, ; , .

, :


10 ddl

PRIMARYKEY (< 1>, < 2>);

INDEX [< >] (< >).

. , , .

, TEXT BLOB , .

( . , . INDEX KEY.


10 ddl

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

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

TEXT BLOB , . . , , .

UNIQUE UNIQUE INDEX UNIQUE KEY.


10 ddl

FULLTEXT [< >] (< >).

. ( CHAR TEXT) . : , , , .

MyISAM. . . . AGAINST. . , .

[CONSTRAINT < >]

FOREIGN KEY [< >] (< >)

REFERENCES < > (< >

[< >]

. , () . InnoDB ( , InnoDB), FOREIGN KEY .


10 ddl

, , , . , .

TEXT BLOB . . , .

, .


10 ddl

:

ON DELETE CASCADE ( );

ON DELETE SET NULL ;

ON DELETE RECTRICT ON DELETE NO ACTION ( MySQL ) .

, ON DELETE RECTRICT.


10 ddl

:

ON UPDATE CASCADE ( );

ON DELETE SET NULL ;

ON UPDATE RESTRICT . , ON UPDATE RESTRICT.

, .


10 ddl

FOREIGN KEY (product_id) REFERENCES Products (id)

ON DELETE RECTRICT ON UPDATE CASCADE

, product_id () , id () Products (). Products, , id Products product_id .


10 ddl

:

CREAT TABLE < > (< > > [NOT NULL] [,< > < > [NOT NULL]] ... )

.

. NOT NULL , . :

CREAT TABLE ( CHAR(5) NOT NULL, R(8), VARCHAR(20) NOT NULL, DECIMAL(8,2));


10 ddl

:

ALTER TABLE < > ( {ADD, MODIFY, DROP} < > [< >] [NOT NULL] [ADD, MODIFY, DROP } < > [< >] [NOT NULL]]...)

(ADD), (MODIFY) (DROP) .

ALTER TABLE , CREAT TABLE, . :

ALTER TABLE (ADD VARCHAR(20));

:

DROP TABLE < >;


10 ddl

, , . ASSERTION. :

CREATE TABLE < >

{< > < > [NOT NULL] [UNIQUE] [DEFAULT < >] [ (< >)] [...]}

PRIMARY KEY (< >), ]

{ [UNIQUE (< >), ][...]}

{[FOREIGN KEY (< >)] REFERENCES < > [< >] (PARTIAL| FULL) [ON UPDATE <>] [ON DELETE < >] [,]} {[(< >)] [,...]}.


10 ddl

PRIMARY KEY . UNIQUE () . PRIMARY KEY REFERENCES . . SQL : CASCADE, SET NULL, SET DEFAULT, NO ACTION ( ). . CONSTRAINT < >, ALTER TABLE. :

CREATE TABLE ( INTEGER PRIMARY KEY, INTEGER, DATE, VARCHAR(255))

CONSTRAINT FOREIGN KEY () REFERENCES ON UPDATE CASCADE ON DELET CASCADE;


10 ddl

:

ALTER TABLE < >

[ADD < > < > [NOT NULL] [UNIQUE] [DEFAULT < >] [ (< >)]]

[DROP < > [RESTRICT|CASCADE]]

[ADD CONSTRAINT < > PRIMARY KEY | UNIQUE | FOREIGN KEY | ]

[DROP CONSTRAINT <> [RESTRICT|CASCADE]]

[ALTER SET DEFAULT <>]

[ALTER DROP DEFAULT]


10 ddl

:

NOT NULL

, (NULL). product_id () Orders (),

product_id BIGINT UNSIGNED NOT NULL

, .

NOT NULL, , ,NULL . , NULL, DEFAULT <>, . NOT NULL, , , .


10 ddl

NULL.

, . TIMESTAMP, . , NOT NULL.

DEFAULT <>.

, , . ; TIMESTAMP, CURRENT_TIMESTAMP( ). BLOB TEXT ( ), , AUTO_INCREMENT. , (NULL), NOT NULL.

, phone () , , :

phone VARCHAR (20) DEFAULT ' '


10 ddl

COMMENT ''.

255 . , rating () :

rating INT COMMENT ' '


10 ddl

. , , :

ENGINE < >.

. MySQL , . InnoBD MyISAM.

ENGINE .


10 ddl

InnoBD

InnoBD , . , InnoBD .


10 ddl

MyISAM

MyISAM , , , . MyISAM : .


10 ddl

MySQL Multifunctional Database ( ) Transactional Database Only ( ), ( ), InnoBD.

, MyISAM ENGINE MyISAM. Non-Transactional Database Only ( ), MyISAM, InnoBD .


10 ddl

AUTO_INCREMENT < >.

, AUTO_INCREMENT, , . , 1000, AUTO_INCREMENT 1000.

CHARACTER SET < >.

.

, , . , , MySQL.


10 ddl

COLLATE < >.

, . , , .

CHECKSUM.

MyISAM, .

COMMENT ' '.

60 . , , COMMENT ' '.


10 ddl

REATE 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;


  • Login