-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdb.sql
More file actions
120 lines (106 loc) · 3.8 KB
/
db.sql
File metadata and controls
120 lines (106 loc) · 3.8 KB
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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
CREATE DATABASE u CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `user` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL COMMENT '姓名',
`sex` varchar(2) NOT NULL COMMENT '性别',
`age` int(3) NOT NULL COMMENT '年龄',
`phone` varchar(11) NOT NULL DEFAULT '0' COMMENT '手机',
`deliveryaddress` varchar(200) DEFAULT NULL COMMENT '收货地址',
`adddate` int(11) NOT NULL COMMENT '添加时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
`user_id` bigint(20) DEFAULT NULL,
`province` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`id` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
`id` bigint(20) NOT NULL DEFAULT '0',
`name` varchar(255) DEFAULT NULL COMMENT '菜单名',
`parent_id` bigint(20) DEFAULT NULL COMMENT '父id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of menu
-- ----------------------------
INSERT INTO `menu` VALUES ('1', 'menu', '0');
INSERT INTO `menu` VALUES ('2', 'submenu', '1');
DROP TABLE IF EXISTS `card`;
CREATE TABLE `card` (
`user_id` bigint(20) NOT NULL DEFAULT '0',
`card_no` varchar(255) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of card
-- ----------------------------
INSERT INTO `card` VALUES ('1', 'this is cardno');
-- ----------------------------
-- Function structure for `fristPinyin`
-- ----------------------------
DROP FUNCTION IF EXISTS `fristPinyin`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `fristPinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE V_RETURN VARCHAR(255);
SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
RETURN V_RETURN;
END
;;
DELIMITER ;
CREATE TABLE if not exists `statistics` (
`usertotal` bigint(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--触发器测试
DROP TRIGGER IF EXISTS tri_countUserTotal;
CREATE TRIGGER tri_countUserTotal AFTER
INSERT ON USER
FOR EACH ROW BEGIN DECLARE c int;
SET c =
(SELECT count(*)
FROM USER);
UPDATE STATISTICS
SET usertotal = c; END;
update statistics set usertotal=0;
CREATE DEFINER=`root`@`%` PROCEDURE `insert_touser`(in start int(10),in max_num int(10))
begin
declare i int default 0;
-- set autocommit =0 把autocommit设置成0 不自动提交，循环完统一提交
set autocommit = 0;
repeat
set i = i + 1;
insert into user(name,phone,adddate) values ((start+i),'test',UNIX_TIMESTAMP(NOW()));
until i = max_num
end repeat;
commit;
end
-- 添加1w个用户
call insert_touser(1,10000);
-- 锁表
lock tables user read;
lock tables user write;
unlock tables;
-- 添加card的外键约束
ALTER TABLE `card` DROP FOREIGN KEY `user_id_fk`;
ALTER TABLE `card` ADD CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
-- 测试onetoone关系表
CREATE TABLE `t_crud_demo` (
`id` bigint(20) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
`sub_id` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_crud_demosub` (
`id` bigint(20) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `user`
ADD COLUMN `is_admin` int NULL COMMENT '是否是管理员1是管理员，0不是管理员' AFTER `adddate`;