-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmysql_training.sql
87 lines (73 loc) · 1.92 KB
/
mysql_training.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
/**
* Author: Allen
* Created: 2019-04-02
*/
create database `mysql_training` default character set utf8 COLLATE utf8_general_ci;
use mysql_training;
create table `mt_test`(
`id` int(11) unsigned not null auto_increment comment '自增标识ID',
primary key(`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';
-- 试验 sakila ,MySQL 官网测试数据库
-- SHOW PROFILE
-- 在会话(连接)级别开启
set profiling=1;
select * from sakila.
show profiles;
show profile from query 1;
set @query_id=15;
select state, sum(duration) as Total_R,
round(100*sum(duration)/
(select sum(duration) from information_schema.profiling
where query_id=@query_id),2) as Pct_R,
count(*) as Calls,
sum(duration)/count(8) as 'R/Call'
from information_schema.profiling
where query_id=@query_id
group by state
order by Total_R desc;
-- 存储过程-循环
DELIMITER //
CREATE PROCEDURE proc5()
BEGIN
DECLARE var INT;
SET var=0;
WHILE var<100 DO
INSERT INTO hit_counter_rand VALUES (var+1,0);
SET var=var+1;
END WHILE ;
END;
//
DELIMITER ;
DELIMITER //
CREATE PROCEDURE proc_inster()
BEGIN
DECLARE var INT;
SET var=0;
WHILE var<1000 DO
update hit_counter_rand set cnt=cnt+1 where slot=floor(rand()*100);
SET var=var+1;
END WHILE;
END;
//
DELIMITER;
-- 触发器
DELIMITER //
create trigger table_name_filed_operation before inster on table_name for each row
BEGIN
set new.filed+crc=crc32(new.filed);
end;
//
DELIMITER ;
-- MySQL 对GIS 空间数据的支持
CREATE TABLE mt_tb_geo(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(128) NOT NULL,
pnt POINT NOT NULL,
SPATIAL INDEX `spatIdx` (`pnt`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 创建表时创建空间索引
INSERT INTO `mt_tb_geo` VALUES(
NULL,
'a test string',
POINTFROMTEXT('POINT(15 20)'));