1.创建用户，并赋予权限
grant all privileges on hr.* to 'user2' @ '192.168.1.222 | %';
//其中"hr.*"代表数据库，"user2"代表用户名，"192.168.1.222 "代表能够访问的主机号
p("grant all privileges on hr.* to 'user2' @ 192.168.1.222".matches("grant all privileges on (([a-zA-Z]\\w+)|\\*).(\\*|([a-zA-Z]\\w+)) to '(\\w+)' @ ((\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3})|%)"));
grant all privileges on *.* to 'root' @ '%';
//创建超级用户权限，"*.*"代表数据库里的所有数据，"root"代表超级用户名称，"%"代表在所有主机上都可用访问数据库
identified by '123456';
//创建用户密码,"123456"代表密码
p("identified by '123456'".matches("identified by '(\\w{6,20})'"));
grant select,update,insert,delete,create,drop on *.* to 'root';?????
//限制用户权限
regex_purview: grant select "^grant (((select|update|delete|insert|create|drop),){0,5}(select|update|delete|insert|create|drop)) to \\'[a-z]{0,4}\\'$";
2.修改用户密码
set password = password("123546");
set password for 'user2' @ '%' = password('123546');
p("set password for 'user2' @ '192.168.1.222' = password('1235467')".matches("set password for '(\\w+)' @ '((\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3})|%)' = password\\('([\\w]{6,20})'\\)"));
//删除用户
drop user 'user2';
p("drop user 'user2'".matches("drop user '(\\w+)'"));
3 数据库管理
创建
create database hr;
regex_create: create database "(^[a-zA-Z]\w+$)|*";
删除
drop database test1;
regex_drop_database: drop database "(^[a-zA-Z]\w+$)|*";
4 表结构管理
创建
列数据类型
字符类型，必须指定长度，长度不能超出1024 char
整型 长度小于10 int
浮点类型 decimal(int,int) 100 2
decimal(4,2)
只能存储总位数小于等于4，而且小数位数不能大于2
解决：这种形式由三种情况出现：1.没有小数点出现，正则表达式为：[1-9]\\d{0,3}；
2.最多有一位小数点出现，正则表达式为：[1-9]\\d{0,2}.\\d?[1-9]
3.最多有两位小数点出现，正则表达式为：[1-9]?\\d?.\\d?[0-9] 测试：05.1(报错) 2.20(正常返回true)
4：特殊情况：0.0 ，或者0.00
5.不能出现的情况为：02.3，005，06，36522，654.26，65.134，1.13546，4687.2
完整的正则表达式为：p("2.10".matches("([1-9]\\d{0,3}|[1-9]\\d{0,2}.\\d?[1-9]|[1-9]?\\d?.\\d?[0-9]|0.00|0.0)"));
日期类型 只能存储格式为’yyyy-mm-dd hh:mi:ss’的日期 1970-12-31 00:00:00---2999-1-1 00:00:00
create table table1(c1 char(10) not null,c2 int(6),c3 decimal(4,2),c4 date);
not null-?代表该列不能有空值存在
解决方案：把这段时间分个时间段：1.1970-12-31 00:00:00 至 1970-12-31 23:59:60
2.1971-1-1 00:00:00 至 1999-12-31 23:59:60
3.2000-1-1 00:00:00 至 2998-12-31 23:59:60
4.2999-1-1 00:00:00
对应的正则表达式为：1.p("1970-12-31 23:59:60".matches("1970-12-31 ([0-1]?\\d|2[0-3]):([0-5]?\\d):([0-5]?\\d|60)"));
2.p("1998-12-1 12:10:10".matches("(197[1-9]|19[8-9]\\d)-(0?[1-9]|1[0-2])-(0?[1-9]|[1-2]\\d|3[0-1]) ([0-1]?\\d|2[0-3]):([0-5]?\\d):([0-5]?\\d|60)"));
3.p("2008-06-07 20:00:60".matches("(2\\d[0-8]\\d|299[0-8])-(0?[1-9]|1[0-2])-(0?[1-9]|[1-2]\\d|3[0-1]) ([0-1]?\\d|2[0-3]):([0-5]?\\d):([0-5]?\\d|60)"));
4.p("2999-1-1 00:00:00".matches("2999-(0?1)-(0?1) 00:00:00"));
5.p("'2009-10-24 14:40:50'".matches("'((2\\d[0-8]\\d|299[0-8]|197[1-9]|19[8-9]\\d)-(0?[1-9]|1[0-2])-(0?[1-9]|[1-2]\\d|3[0-1]) ([0-1]?\\d|2[0-3]):([0-5]?\\d):([0-5]?\\d|60))'"));
//用于年份的正则表达式
String str1 = "'(1970-12-31 ([0-1]?\\d|2[0-3]):([0-5]?\\d):([0-5]?\\d|60))'";
String str2 = "'((2\\d[0-8]\\d|299[0-8]|197[1-9]|19[8-9]\\d)-(0?[1-9]|1[0-2])-(0?[1-9]|[1-2]\\d|3[0-1]) ([0-1]?\\d|2[0-3]):([0-5]?\\d):([0-5]?\\d|60))'";
String str3 = "'(2999-1-1 00:00:00)'";
//用于浮点类型 decimal(int,int) 100 2 decimal(4,2)
String str4 = "([1-9]\\d{0,3}|[1-9]\\d{0,2}.\\d?[1-9]|[1-9]?\\d?.\\d?[0-9]|0.00|0.0)";
//插入一行
String str5 = "\\('([A-Za-z]\\w{0,9})',(\\d{0,6}),"+str4+",((" + str2+")|("+str1+")|("+str3+"))\\)";
//选择某一行
String str6 = "('([A-Za-z]\\w{0,9})')|(\\d{0,6})|("+str4+")|((" + str2+")|("+str1+")|("+str3+"))";
p("create table new_table('name10',123456,56.32,'2009-10-24 15:26:30');".matches("create table ([A-Za-z]\\w*)\\('([A-Za-z]\\w{0,9})',(\\d{0,6}),"+str4+",((" + str2+")|("+str1+")|("+str3+"))\\);"));
表名不能重复，列名不能重复
修改表名
alter table table1 rename to table2;
p("alter table table1 rename to table2;".matches("alter table ([A-Za-z]\\w*) rename to ([A-Za-z]\\w*);"));
列名值是否非空
alter table table2 modify c1 not null;
p("alter table table2 modify c1 not null;".matches("alter table ([A-Za-z]\\w*) modify ([A-Za-z]\\w*) not null;"));
删除指定列
alter table table2 drop column(c2,c3);
alter table table2 drop c2;
p("alter table table2 drop column(c2,c3,c4);".matches("alter table ([A-Za-z]\\w*) drop (([A-Za-z]\\w*)|column\\(((([A-Za-z]\\w*),)*)(([A-Za-z]\\w*)?)\\));"));
添加列
alter table table2 add column c5 date;
p("alter table table2 add column c5 123456;".matches("alter table ([A-Za-z]\\w*) add column ([A-Za-z]\\w*) ('([A-Za-z]\\w{0,9})'|(\\d{0,6})|("+str4+")|((" + str2+")|("+str1+")|("+str3+")));"));
删除
drop table table2;
p("drop table table2;".matches("drop table ([A-Za-z]\\w*);"));
5 表数据管理
插入
insert into table1 values('abcd',1000,12.34,'1998-12-1 12:10:10');
insert into table1 values('abcd',1000,12.34,'1998-12-1 12:10:10'),('abcd',1000,12.34,'1998-12-1 12:10:10');
//插入一行
p("insert into table1 values('abcd',1000,12.34,'1998-12-1 12:10:10');".matches("insert into table1 values\\('([A-Za-z]\\w{0,9})',(\\d{0,6}),"+str4+",((" + str2+")|("+str1+")|("+str3+"))\\);" ) );
//插入多行
String str5 = "\\('([A-Za-z]\\w{0,9})',(\\d{0,6}),"+str4+",((" + str2+")|("+str1+")|("+str3+"))\\)";
p("insert into table1 values('abcd',1000,12.34,'1998-12-1 12:10:10'),('abcd',1000,12.34,'1998-12-1 12:10:10');".matches("insert into table1 values((("+str5+"),)*)("+str5+")?;" ) );
insert into table1(c2) values(1000);
//插入一列
p("insert into table1(c2) values(1000);".matches("insert into ([A-Za-z]\\w*)\\([A-Za-z]\\w*\\) values\\(("+str6+")\\);" ));
删除
delete from table1;
p("delete from table1;".matches("delete from ([A-Za-z]\\w*);" ));
delete from departments where location_id=1700;
p("delete from departments where location_id=1700;".matches("delete from ([A-Za-z]\\w*) where ([A-Za-z]\\w*)=([\\w]+);" ));
delete from employees where salary > 3000 and department_id=170;
delete from employees where salary > 3000 or department_id=170;
p("delete from departments where department_id=170 or salary > 3000;".matches("delete from ([A-Za-z]\\w*) where (((([A-Za-z]\\w*) > ([\\d]+))?)|((([A-Za-z]\\w*)=([\\w]+))?)) (and|or) (((([A-Za-z]\\w*) > ([\\d]+))?)|((([A-Za-z]\\w*)=([\\w]+))?));" ));
更新
update departments set dapartment_name='abcd'; all lines
String str6 = "('([A-Za-z]\\w{0,9})')|(\\d{0,6})|("+str4+")|((" + str2+")|("+str1+")|("+str3+"))";
p("update departments set dapartment_name='2009-10-25 17:45:23';".matches("update ([A-Za-z]\\w*) set ([A-Za-z]\\w*)=("+str6+");" ) );
update departments set department_name='abcd' where department_id=130;
p("update departments set department_name='abcd' where department_id=130;".matches("update departments set ([A-Za-z]\\w*)=("+str6+") where ([A-Za-z]\\w*)=([\\w]+);"));
查询
SELECT * FROM departments;
SELECT last_name FROM employees;
SELECT last_name,salary FROM employees;
p("select last_name,salary FROM employees;".matches("select (\\*|((([A-Za-z]\\w*),)*)([A-Za-z]\\w*)?) (from|FROM|From) ([A-Za-z]\\w*);" ) );
SELECT first_name FROM employees where salary > 3000;
p("select last_name,salary FROM employees where salary > 3000;".matches("(select|SELECT) (\\*|((([A-Za-z]\\w*),)*)([A-Za-z]\\w*)?) (from|
