4 mysqlCREATE TABLE myisamtable id INT NOT NULL

4 mysql>CREATE TABLE myisam_table ( -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(255), -> description TEXT CHARACTER SET utf 8, -> FULLTEXT (description) -> ENGINE = My. ISAM -> CHARACTER SET latin 1 COLLATE latin 1_general_ci; Query OK, 0 rows affected (0. 00 sec) mysql>CREATE TABLE all_users ( -> id INT NOT NULL AUTO_INCREMENT, -> name VARCHAR(255) NOT NULL DEFAULT '', -> email VARCHAR(255) NOT NULL DEFAULT '', -> INDEX(id) -> ENGINE = MERGE -> UNION = (old_users, new_users) -> INSERT_METHOD = LAST; Query OK, 0 rows affected (0. 01 sec)

5 mysql>CREATE TABLE memory_table ( -> id INT UNSIGNED AUTO_INCREMENT, -> value INT NOT NULL DEFAULT 0, -> name VARCHAR(255), -> PRIMARY KEY USING HASH (id), -> INDEX USING BTREE (value) -> ) ENGINE = MEMORY; Query OK, 0 rows affected (0. 00 sec) mysql>CREATE TABLE innodb_parent ( -> id INT NOT NULL DEFAULT 0, -> parent_name VARCHAR(255), -> UNIQUE (id) -> ) ENGINE = Inno. DB; Query OK, 0 rows affected (0. 02 sec) mysql>TABLE innodb_child ( -> id INT NOT NULL DEFAULT 0, -> parent_id INT, -> child_name VARCHAR(255), -> UNIQUE (id), -> INDEX (parent_id), -> FOREIGN KEY (parent_id) -> REFERENCES innodb_parent (id) -> ON UPDATE CASCADE -> ON DELETE CASCADE -> ) ENGINE = Inno. DB; Query OK, 0 rows affected (0. 05 sec)

mysql>CREATE TABLE trades (summ INT, debt INT NOT NULL DEFAULT 0); Query OK, 0 rows affected (0. 01 sec) mysql>DELIMITER $$ mysql>CREATE TRIGGER trades_bi -> BEFORE INSERT -> ON trades -> FOR EACH ROW -> BEGIN -> IF NEW. summ < 0 THEN -> SET NEW. debt = 1; -> END IF; -> END -> $$ Query OK, 0 rows affected (0. 00 sec) mysql>DELIMITER ; mysql>INSERT INTO trades VALUES (100), (200), (500), (-10), (-50); ERROR 1136 (21 S 01): Column count doesn't match value count at row 1 mysql>INSERT INTO trades (summ) VALUES (100), (200), (500), (-10), (-50); Query OK, 5 rows affected (0. 00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql>SELECT * FROM trades; +------+ | summ | debt | +------+ | 100 | | 200 | | 500 | | -10 | 1 | | -50 | 1 | +------+ 5 rows in set (0. 00 sec) mysql> 7

Использование процедур root@localhost>DELIMITER $$ root@localhost>CREATE PROCEDURE test. sort_values (INOUT var_value INT) -> SQL SECURITY INVOKER -> BEGIN -> DECLARE message VARCHAR(255); -> IF var_value > 0 THEN -> BEGIN -> INSERT INTO positive_values (value) VALUES (var_value); -> SET message = CONCAT(var_value, ' was positive; inserted into table positive_ relults'); -> END; -> ELSE -> BEGIN -> INSERT INTO positive_values (value) VALUES (var_value); -> SET message = CONCAT(var_value, ' was not positive; redirected to table posit ive_relults'); -> END IF; -> SELECT message AS 'message'; -> END; -> $$ Query OK, 0 rows affected (0. 00 sec) root@localhost>GRANT EXECUTE ON PROCEDURE test. sort_values TO 'ivan'@'192. 168. %' IDENTIFIED BY 'durak'$$ Query OK, 0 rows affected (0. 00 sec). . . 9

10 ivan>SET @a = 1; Query OK, 0 rows affected (0. 00 sec) ivan>call sort_values(@a); +---------------------------+ | message | +---------------------------+ | 1 was positive; inserted into table positive_relults | +---------------------------+ 1 row in set (0. 00 sec) Query OK, 0 rows affected (0. 00 sec) ivan>SET @b = -1; Query OK, 0 rows affected (0. 01 sec) ivan>CALL test. sort_values(@b); +------------------------------+ | message | +------------------------------+ | -1 was not positive; redirected to table positive_relults | +------------------------------+ 1 row in set (0. 00 sec) Query OK, 0 rows affected (0. 01 sec)

Использование функций root@localhost>SET @@global. log_bin_trust_function_creators = 1; Query OK, 0 rows affected (0. 00 sec) root@localhost>DELIMITER $$ root@localhost>CREATE FUNCTION daytime ( dt DATETIME) -> RETURNS varchar(255) -> NOT DETERMINISTIC -> BEGIN -> DECLARE d INT; -> DECLARE daytime VARCHAR(255); -> SET d = CAST(HOUR(dt) AS UNSIGNED); -> CASE d -> WHEN (d >= 0 AND d < 10) THEN SET daytime = 'night'; -> WHEN (d >= 10 AND d < 14) THEN SET daytime = 'mourning'; -> WHEN (d >= 14 AND d < 20) THEN SET daytime = 'day'; -> WHEN (d >= 20 AND d < 24) THEN SET daytime = 'evening'; -> ELSE SET daytime = 'unknown time'; -> END CASE; -> RETURN daytime; -> END -> $$ Query OK, 0 rows affected (0. 00 sec) 11

Использование функций root@localhost test>GRANT SELECT ON test. posts TO 'ivan'@'192. 168. %'; Query OK, 0 rows affected (0. 02 sec) root@localhost test>GRANT EXECUTE ON FUNCTION test. daytime TO 'ivan'@'192. 168. %'; Query OK, 0 rows affected (0. 00 sec). . . ivan>SELECT COUNT(id) AS `число действий`, daytime(act_time) AS `время суток` FROM actions -> GROUP BY `время суток` -> ORDER BY `число действий` DESC; +--------+-------+ | число действий | время суток | +--------+-------+ | 4820 | ночь | +--------+-------+ | 340 | день | +--------+-------+ | 280 | вечер | +--------+-------+ | 67 | утро | +--------+-------+ 4 rows in set (0. 01 sec) 12