, , , , .
:
o CREATE
o DESC
o INSERT
o ALTER
o UPDATE
o DELETE
o SELECT
.
1) . . , ( Notepad++ .
2) ` ` . . , .
` ` - , , . , , - (, )
.
3) MySQL . , . ( , , , , ).
4) . INT UNSIGNED INT .
5) FOREIGN KEY (, PRIMARY KEY UNIQUE)
6) :
| -- | |
| /* */ |
7) .
MySQL
1) ( Windows 7 ).
2)
Mysql uroot ppassowrd
3)
SET NAMES cp866;
.
-
- (, , ) - (Philips, Bosh, Mulinex .). - .
:
( , , , , , );
( , , , );
( , , );
- ( , , (/);
( , , , ).
ü , -, - .
|
|
|
ü . .
ü - . -.
ü . , . .
IF NOT EXISTS, CREATE, , .
CREATE SCHEMA IF NOT EXISTS `-`;
USE , .
USE `-`;
4.3.2 -
CREATE TABLE IF NOT EXISTS `-` -- .
(
` ` INT UNSIGNED NOT NULL AUTO_INCREMENT, -- , , .
` ` VARCHAR(45) NULL, -- **, .. .
` ` ENUM('','') NOT NULL, -- .
PRIMARY KEY (` `) -- .
);
DESC `-`; -- DESC DESCRIBE. .
4.3.3
DROP TABLE IF EXISTS ``; -- , .
CREATE TABLE IF NOT EXISTS ``
(
` ` INT UNSIGNED NOT NULL AUTO_INCREMENT,
` ` VARCHAR(85) NOT NULL,
`` VARCHAR(55) NOT NULL,
`` FLOAT NULL,
PRIMARY KEY (` `)
);
ALTER.
. , .
ALTER TABLE `` ADD INDEX `ixFirm` (`` ASC); -- ASC , . ( DESC ). .
.
DESC ``; -- `` , . , Workbench .
4.3.4
DROP TABLE IF EXISTS ``;
CREATE TABLE IF NOT EXISTS ``
(
` ` INT UNSIGNED NOT NULL AUTO_INCREMENT,
` ` VARCHAR(80) NOT NULL,
` ` INT NULL, -- VARCHAR.
` ` VARCHAR(150) NULL,
PRIMARY KEY (` `),
INDEX `ixClient` (` ` ASC)
);
. , . VARCHAR, .. . , .
.
ALTER TABLE MODIFY ` ` VARCHAR(7) NOT NULL; -- .
.
DESC ;
c , .
|
|
|
DROP TABLE IF EXISTS ``;
CREATE TABLE IF NOT EXISTS ``
(
` ` INT UNSIGNED NOT NULL AUTO_INCREMENT,
` ` INT UNSIGNED NOT NULL,
` ` INT UNSIGNED NOT NULL,
`` INT NOT NULL,
` ` INT UNSIGNED NOT NULL,
` ` INT NOT NULL, -- , ALTER TABLE.
PRIMARY KEY (` `),
INDEX `FKShopCod` (` ` ASC), -- .
INDEX `FKGoodsCod` (` ` ASC),
INDEX `FKClient` (` ` ASC),
CONSTRAINT `FKShopCod` -- ,
FOREIGN KEY (` `) -- ,
REFERENCES `-` (` ` ), -- -.
CONSTRAINT `FKGoodsCod`
FOREIGN KEY (` `)
REFERENCES `` (` `),
CONSTRAINT `FKClient`
FOREIGN KEY (` `)
REFERENCES `` (` `)
);
: MySQL 5, InnoDB . , , , -. , . .
.
, , .
ALTER TABLE -- .
ADD COLUMN ` ` DATE NULL, -- DATE, (NULL).
DROP COLUMN ` `; -- .
.
DESC ;
4.3.5
DROP TABLE IF EXISTS ``;
CREATE TABLE IF NOT EXISTS ``
(
` ` INT UNSIGNED NOT NULL AUTO_INCREMENT,
` ` INT UNSIGNED NOT NULL,
` ` DATETIME NULL,
` ` INT UNSIGNED NOT NULL,
PRIMARY KEY (` `),
INDEX `FKOrder` (` ` ASC),
INDEX `FKClient2` (` ` ASC), -- ! !
CONSTRAINT `FKOrder`
FOREIGN KEY (` `)
REFERENCES `` (` `),
CONSTRAINT `FKClient2`
FOREIGN KEY (` `)
REFERENCES `` (` `)
);
, .
DESC ;
*: ; \G - . , .
DESC \G
.
4.3.6 -
INSERT INTO `-` (` `, ` `) -- , . .. , .
VALUES ('[email protected]',''),
('[email protected]',''),
('[email protected]',''),
('[email protected]',''),
('[email protected]','');
SELECT.
SELECT * FROM `-`;
4.3.7
INSERT INTO -- , . . , , , , .
VALUES (NULL,'','Braun',1500), -- .. , , . NULL.*
|
|
|
(NULL,'','Samsung',3500),
(NULL,'','Yamaha',999),
(NULL,'','Whirlpool',5000),
(NULL,'','Yamaha',999),
(NULL,'','Braun',1500),
(NULL,'','Samsung',5800),
(NULL,'R2-D2','Sony',150000);
*NULL . , . : NULL ≠ 0, NULL+1 = NULL.
.
SELECT * FROM ;
, . , :
INSERT INTO
VALUES (5,' ','Braun',800000);
:
5 . . .
4.3.8
INSERT INTO
VALUES (NULL, ' ..', '1234567', ', . 17-5'),
(NULL, ' ..', '8901234', ', . 22-1-2'),
(NULL, ' ..', '5678901', ', , . 42-123'),
(NULL, ' ..', '4145252', ', . 212-2'),
(NULL, ' ..', '9982989', ', . 12-44');
, , .
, , .
.
ALTER TABLE DROP INDEX `ixClient`;
, :
ALTER TABLE ADD CONSTRAINT `ixName` UNIQUE INDEX (` `);
, , .
INSERT INTO
VALUES (NULL, ' ..', '4440102', ', . 1-5');
, , . . . , , IGNORE. ( , .)
INSERT IGNORE INTO
VALUES (NULL, ' ..', '4440102', ', . 1-5'),
(NULL, ' ..', '4145252', ', . 212-2'),
(NULL, ' ..', '9982989', ', . 12-44');
.
SELECT * FROM ;
, .. .
( ) UPDATE:
UPDATE --
SET ` `= ' ..' -- .ѻ
WHERE ` ` = ' ..'; -- , ..
, :
UPDATE --
SET ` `= ' ..' -- .ѻ
WHERE ` ` = 5; -- , 5
.
DELETE FROM
WHERE ` ` = 8;
* DELETE FROM ;
4.3.9
, DATE. : . -- (--).
|
|
|
INSERT INTO (` `, ` `, ` `, ``, ` `)
VALUES (1,1,'2010-01-11',1,2),
(2,2,'2008-01-31',1,3),
(2,2,'2007-01-09',2,4),
(3,4,'2002-01-01',1,5),
(2,1,'2011-02-22',2,2),
(4,1,'2006-08-30',1,1),
(5,6,'2005-07-30',1,3),
(1,3,'2009-06-30',2,4),
(5,5,'2009-05-30',1,1);
4.3.10
MySQL (FOREING KEY), .. , ( , ).
, , . , . :
SELECT * FROM ;
. :
INSERT INTO (` `, ` `, ` `)
VALUES (28, '2010-01-11 20:00:00', 2),
(29, '2008-01-31 22:50:00', 3),
(30, '2007-02-10 20:00:00', 4),
(31, '2010-01-15 06:00:00', 5),
(32,NULL, 2); -- .. , .
