.
.. 42-13
(, )
(, )
______________________ _____________________
_____________________________
2017.
24.
. , ( , -). . , .
3
ER- 3 UML ( : UML diagrams).
, .
5 , :
1. :
( integer(11));
( VARCHAR(11));
( VARCHAR(11));
( VARCHAR(11));
2. , :
( integer(11));
( integer(11));
( date);
( VARCHAR (11));
( VARCHAR (11));
3. , , , :
( integer(10));
( CHAR(11));
( CHAR(11));
( date);
( CHAR(11));
( CHAR(11))
( date);
4. , , :
( integer(10));
( VARCHAR(11));
( integer(11));
( integer(11));
( integer(11));
varchar(100) NULL);
5. :
|
|
( VARCHAR(10));
( integer(11));
( integer(11));
:
CREATE TABLE (
int(11) NOT NULL Primary key,
VARCHAR(11) NOT NULL,
VARCHAR(11) NULL,
VARCHAR(11) NULL);
CREATE TABLE (
int(10) NOT NULL,
int(11) NOT NULL Primary key,
date NULL,
VARCHAR(11) NULL,
VARCHAR(11) NULL);
CREATE TABLE (
int(10) NOT NULL,
CHAR(11) NULL,
CHAR(11) NULL,
date NULL Primary key,
CHAR(11) NULL,
CHAR(11) NULL,
date NULL);
CREATE TABLE (
int(10) NOT NULL,
VARCHAR(11) NULL Primary key,
int(11) NULL,
int(11) NULL,
int(11) NULL,
varchar(100) NULL);
CREATE TABLE (
VARCHAR(10) NOT NULL Primary key,
int(11) NULL,
int(10)NULL);
ALTER TABLE `` ADD FOREIGN KEY (``) REFERENCES `` (``) ON DELETE cascade;
ALTER TABLE `` ADD FOREIGN KEY (``) REFERENCES `` (``) ON DELETE cascade;
ALTER TABLE `` ADD FOREIGN KEY (``) REFERENCES `` (``) ON DELETE cascade;
ALTER TABLE `` ADD FOREIGN KEY (``) REFERENCES `` (``) ON DELETE cascade;
ALTER TABLE `` ADD FOREIGN KEY (``) REFERENCES `` (``) ON DELETE cascade;
.
. , . , , , .. . ( ): , , , , . , , , .
INSERT INTO `` (``, ``, ``, ``) VALUES ('12345671', '', '', '');
INSERT INTO `` (``, ``, ``, ``) VALUES ('13425683', '', '', '');
INSERT INTO `` (``, ``, ``, ``) VALUES ('12345602', '', '', '');
INSERT INTO `` (``, ``, ``, ``) VALUES ('12345605', '', '', '');
INSERT INTO `` (``, ``, ``, ``) VALUES ('12345608', '', '', '');
INSERT INTO `` (``, ``, ``, ``) VALUES ('12345622', '', '', '');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('12345679', '43659486', '2017-01-19', '', '');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('12345670', '54456539', '2017-01-03', '', '');
|
|
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('12345678', '65743597', '2017-01-18', '', '');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('75632746', '86047697', '2017-01-08', '', '');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('12345671', '87654576', '2017-01-11', '', '');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('13425683', '98067594', '2017-01-26', '', '');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('12345602', '43659757', '2017-01-20', '', '');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('12345605', '43659364', '2017-01-22', '', '');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('12345608', '43659686', '2017-01-15', '', '');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('12345622', '43659976', '2017-01-01', '', '');
INSERT INTO `` (``, ``, ``, ``, ``, ``, ``) VALUES ('43659486', '100', '100', '2017-01-01', '100', '100', '2017-02-01');
INSERT INTO `` (``, ``, ``, ``, ``, ``, ``) VALUES ('54456539', '200', '200', '2017-01-02', '200', '200', '2017-02-02');
INSERT INTO `` (``, ``, ``, ``, ``, ``, ``) VALUES ('65743597', '300', '300', '2017-01-03', '300', '300', '2017-02-03');
INSERT INTO `` (``, ``, ``, ``, ``, ``, ``) VALUES ('86047697', '400', '400', '2017-01-04', '400', '400', '2017-02-04');
INSERT INTO `` (``, ``, ``, ``, ``, ``, ``) VALUES ('87654576', '500', '500', '2017-01-05', '500', '500', '2017-02-05');
INSERT INTO `` (``, ``, ``, ``, ``, ``, ``) VALUES ('98067594', '600', '600', '2017-01-06', '600', '600', '2017-02-06');
INSERT INTO `` (``, ``, ``, ``, ``, ``, ``) VALUES ('43659757', '700', '700', '2017-01-07', '700', '700', '2017-02-07');
INSERT INTO `` (``, ``, ``, ``, ``, ``, ``) VALUES ('43659364', '800', '800', '2017-01-08', '800', '800', '2017-02-08');
INSERT INTO `` (``, ``, ``, ``, ``, ``, ``) VALUES ('43659686', '900', '900', '2017-01-08', '900', '900', '2017-02-09');
INSERT INTO `` (``, ``, ``, ``, ``, ``, ``) VALUES ('43659976', '1000', '1000', '2017-01-10', '1000', '1000', '2017-02-10');
INSERT INTO `` (``, ``, ``) VALUES ('S.O.S', '10', '43659486');
INSERT INTO `` (``, ``, ``) VALUES (' IP', '100', '54456539');
INSERT INTO `` (``, ``, ``) VALUES ('', '1499', '65743597');
INSERT INTO `` (``, ``, ``) VALUES ('', '500', '86047697');
INSERT INTO `` (``, ``, ``) VALUES ('', '200', '87654576');
INSERT INTO `` (``, ``, ``) VALUES ('', '0', '98067594');
|
|
INSERT INTO `` (``, ``, ``) VALUES ('', '100', '43659757');
INSERT INTO `` (``, ``, ``) VALUES ('', '150', '43659364');
INSERT INTO `` (``, ``, ``) VALUES ('', '0', '43659686');
INSERT INTO `` (``, ``, ``) VALUES ('', '0', '43659976');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('43659486', 'L', '100', '100', '100');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('54456539', 'M', '200', '200', '200');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('65743597', 'S', '300', '300', '300');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('86047697', 'X', '400', '400', '400');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('87654576', 'XL', '500', '500', '500');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('98067594', '2L', '600', '600', '600');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('43659757', '3L', '700', '700', '700');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('43659364', '4L', '800', '800', '800');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('43659686', '5L', '900', '900', '900');
INSERT INTO `` (``, ``, ``, ``, ``) VALUES ('43659976', '6L', '1000', '1000', '1000');
:
, , , , , ( , , - 400 900).
Select ``, ``, ``, ``
From ``
Where `` >=400 AND `` <=900;
, , , :
SELECT ``, ``, ``
FROM ``
WHERE 1
ORDER BY ASC, DESC;
, () , , , .
SELECT ``, ``, COUNT(*) FROM `` WHERE 1
GROUP BY ``;
, , , , , :
select u.``, ``, a.``, ``, ``, ``
from u
inner join a ON a.`` = u.``
PL/SQL, . INSERT, UPDATE DELETE, , .
DELIMITER :
|
|
DELIMITER //
CREATE TRIGGER `delete_trigger` BEFORE DELETE ON ``
FOR EACH ROW BEGIN
DELETE FROM `` WHERE ``=OLD.``;
END