Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
- START TRANSACTION;
- SET time_zone = "+00:00";
- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
- /*!40101 SET NAMES utf8mb4 */;
- --
- -- База данных: `sectionirovanie`
- --
- DELIMITER $$
- --
- -- Процедуры
- --
- CREATE DEFINER=`root`@`localhost` PROCEDURE `chech_order` (`num` INT) BEGIN
- SELECT * FROM orders where idorders=num;
- END$$
- DELIMITER ;
- -- --------------------------------------------------------
- --
- -- Структура таблицы `client`
- --
- CREATE TABLE `client` (
- `idClient` int(11) NOT NULL,
- `FIO` varchar(140) NOT NULL,
- `typework` varchar(45) DEFAULT NULL,
- `oplata` int(11) DEFAULT NULL,
- `Adress` varchar(100) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- --
- -- Триггеры `client`
- --
- DELIMITER $$
- CREATE TRIGGER `check_user` BEFORE INSERT ON `client` FOR EACH ROW BEGIN
- IF new.adress is null THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Нельзя добавить пользователя без адреса';
- END IF;
- END
- $$
- DELIMITER ;
- -- --------------------------------------------------------
- --
- -- Структура таблицы `ispolnitel`
- --
- CREATE TABLE `ispolnitel` (
- `idIspolnitel` int(11) NOT NULL,
- `FIO` varchar(140) DEFAULT NULL,
- `kalificatiya` varchar(45) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- --------------------------------------------------------
- --
- -- Структура таблицы `orders`
- --
- CREATE TABLE `orders` (
- `idorders` int(11) NOT NULL,
- `client` int(11) DEFAULT NULL,
- `idispolnitel` int(11) DEFAULT NULL,
- `date` date NOT NULL,
- `usluga` int(11) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
- PARTITION BY RANGE (month(`date`))
- (
- PARTITION p_1 VALUES LESS THAN (5) ENGINE=InnoDB,
- PARTITION p_2 VALUES LESS THAN (9) ENGINE=InnoDB,
- PARTITION p_3 VALUES LESS THAN MAXVALUE ENGINE=InnoDB
- );
- --
- -- Дамп данных таблицы `orders`
- --
- INSERT INTO `orders` (`idorders`, `client`, `idispolnitel`, `date`, `usluga`) VALUES
- (2, 2, 3, '2020-02-01', 5),
- (3, 2, 3, '2020-01-01', 5),
- (4, 2, 3, '2020-05-01', 5),
- (1, 2, 3, '2020-11-01', 5),
- (5, 2, 3, '2020-12-01', 5),
- (6, 2, 3, '2020-11-01', 5);
- -- --------------------------------------------------------
- --
- -- Структура таблицы `usluga`
- --
- CREATE TABLE `usluga` (
- `idUsluga` int(11) NOT NULL,
- `Price` varchar(45) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- --
- -- Индексы сохранённых таблиц
- --
- --
- -- Индексы таблицы `client`
- --
- ALTER TABLE `client`
- ADD PRIMARY KEY (`idClient`);
- --
- -- Индексы таблицы `ispolnitel`
- --
- ALTER TABLE `ispolnitel`
- ADD PRIMARY KEY (`idIspolnitel`);
- --
- -- Индексы таблицы `orders`
- --
- ALTER TABLE `orders`
- ADD PRIMARY KEY (`idorders`,`date`);
- --
- -- Индексы таблицы `usluga`
- --
- ALTER TABLE `usluga`
- ADD PRIMARY KEY (`idUsluga`);
- COMMIT;
- /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
- /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
- /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement