MySQL自增列的步长问题

唯一索引和联合唯一
外键的变种
SQL数据行的增删改查
视图
触发器
函数
存储过程
事务
游标
动态执行SQL(防SQL注入)

1.MySQL自增列的步长问题： 1.基于会话级别（单次登陆状态下）：
show session variables like ‘auto_inc%‘;
#查看自增长的默认步数，一般为1
set session auto_increment_increment=2;
#设置自增长会话步长为2 set session auto_increment_offset=10; #设置默认自增长初始值 2.基于全局级别（所有用户生效）： show global variables like ‘auto_inc%‘; #查看全局变量中的自增长的默认步长 set global auto_increment_increment=2; #设置全局会话步长 set global auto_increment_offset=10; #设置全局自增长初始值

2.唯一索引和联合唯一： create table t1( id int, num int, name char
unique uql (num) #唯一索引
unique uql (num, name) #联合唯一 );
唯一索引和主键的共同点： 1.都有加速查找的功能； 2.都是唯一，不能重复
唯一索引和主键的不同点： 主键既不能重复也不能为空；而唯一索引不能重复，但是
可以有值为空，比如联合索引中可以设置一个值为null

3.外键的变种： 1.一对一：博客用户表 2.一对多：百合网相亲记录表 3.多对多：用户主机关系表

4.SQL数据行的增删改查：
增： insert into test(name, age) values(‘name‘, 18); insert into test(name, age)
values(‘name1‘, 18),(‘name2‘, 18); #一次性插入多个值 insert into test(name, age)
select name,age from test1; #把某张表中的数据插入
删： delete from test; delete from test where id>2
and name=‘name1‘;
改： updata test set name=‘name2‘,age=19 where id>12 and name=‘name1‘;
查： select * from test; select id,name from test where id>2; select name,age,
123 from test; select
name as rname from test; select * from test
where id in (1,3,5,7); select * from test
where id in (select id from test1); select * from test
where id between 5 and 9; #闭区间，左右都可以取到
通配符 select * from test
where name like ‘name%‘; %匹配无数字符；_匹配一个字符分页 select * from test
limit 10; 取前十条 select * from test
limit 0,10; 表示从0开始，取0后面的10条 select * from test
limit 10 offset 20; 表示从20开始，取20后的前10条
排序 select * from test
order by id desc; id从大到小排列 select * from test
order by id asc; id从小到大排列 select * from test order by age desc, id asc; 多个不同排序 select * from test order by desc limit 10; 取后十条
分组(聚合函数：count,max,min,sum,avg求平均值) select max(id),id from test group by sex; 如果遇到相同的sex，只会取最大id的 select count(id),id from test group by sex; 计数 select count(id) as count,id form test group by sex; select count(id),id from test group by sex having count(id)>2;
对于聚合函数结果进行二次筛选时，必须使用having
连表操作： #左右连表 join select * from
test1,test2 where test1.id = test2.part_id; select * from test1
left join test2 on test1.id = test2.part_id; test1左边会全部显示
select * from test
right
join
test2 on test1.id = test2.part_id; test1右边会全部显示
select * from test

innder
join
test2 on test1.id = test2.part_id; 会把出现null的那一行隐藏 #上下连表 union select id,name from test1 union #
自动去重 select id,name from test2;
select id,name from test1
union all #不去重
select id,name from test2;
转储mysql文件： mysqldump -uroot test1 > test1.sql -p #数据表结构+数据 mysqldump -uroot -d test1 > test1.sql -p #只有数据表结构
导入mysql文件： create databases test1; mysqldump -uroot -d test1 < test1.sql -p;
临时表 select id from (select id from test where num>60) as B;
添加条件 select min(num),min(num)+1,
case when num<10 then 0 else min(num) end from score

5.视图：
#创建
create view as view1 select * from test where id>10; #视图是一个临时表 #
视图是虚拟出来的，不是物理表，因此不能插入数据 #修改 alter view 视图名称 as SQL #删除 drop view 视图名称；

6.触发器： #插入前 create trigger t1
BEFORE INSERT on student for EACH ROW BEGIN INSERT into teacher(tname) values(NEW.sname)
; END #插入后 after insert #删除前 before delete #删除后 after delete #更新前 before update #更新后 after update #由于默认；结束，因此不会执行end，所以要执行触发器之前要
先修改终止符
delimiter //
create trigger t1 BEFORE INSERT on student for EACH ROW
BEGIN
INSERT into teacher(tname) values(sname);
END //
delimiter ; #创建时自动插入： drop trigger t1; #结束上一个触发器 delimiter // create trigger t1 BEFORE INSERT on student for EACH ROW BEGIN INSERT into teacher(tname) values(
NEW.sname); END // delimiter ; insert into student(gender,class_if,sname) values(‘女‘,1,‘abc‘)

7.函数：
#自定义函数(有返回值) #创建函数 delimiter \\ create function f1( i1 int, i2 int) returns int BEGIN declare num int; 声明一个变量类型是整数 set num = i1 + i2; return(num); END \\ delimiter ; #运行函数 select f1(1,100); 内置函数：时间重点

8.SQL存储过程：
1.简单存储过程 delimiter // create PROCEDURE p1() BEGIN     select * from student;     insert into teacher(tname) values(‘ct‘); END delimiter ; #调用存储过程
call p1;
cursor.callproc(‘p1‘)
2.传参数(in,out,inout) delimiter // create PROCEDURE p2(
in n1 int,
in n2 int ) BEGIN     select * from student where sid>n1; END #调用
call p2(12,2);
cursor.callproc(‘p2‘,(12,2)) delimiter // create PROCEDURE p2(     in n1 int,
out n2 int #out伪装返回值 ) BEGIN set n2 = 123123;     select * from student where sid>n1; END #调用
set @vi = 0   #创建了一个session级的变量叫做v1,可以在外部接收
call p2(12,@v1)
select @v1; 接收变量
cursor.execute(‘select @_p2_0,@_p2_1‘) #pymysql中接收存储过程变量存储过程的特性：     a.可传参（in out inout）     b.pymysql 为什么有结果值又有out伪造的返回值：     out的作用：用于标识存储过程的执行结果，如1为失败，2为成功，3为局部成功

9.事务： delimiter // create procedure p4( out status int ) BEGIN 1.声明如果出现异常则执行{ set status = 1; rollback; #回滚 } 开始事务 –a账户减少100 –b账户增加100 commit; 结束 set status = 2; #如果这里的事务执行顺利，会得到变量等于2，不会执行回滚 END // delimiter ; delimiter \\ create PROCESDURE p1( out p_return_code tinyint ) BEGIN
declare exit handler for sqlexception #这样代码的意思是如果没有顺利执行，就执行下面的代码 BEGIN — ERROR set p_return_code = 1; rollback; END; START TRANSACTION; DELETE from tb1; insert into tb2(name) values(‘seven‘); COMMIT; –SUCCESS set p_return_code = 0; END\\ delimiter ; #正确的返回0，错误的返回1

10.游标： delimiter // create procedure p6() begin declare row_id int; –自定义变量1 declare row_num varchar(50); –自定义变量2 declare done INT DEFAULT FALSE; declare my_cursor CURSOR FOR select id,num from A; declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; open my_cursor; #打开游标 xxoo: LOOP #开始循环 fetch my_cursor into row_id,row_num; #取一行数据赋值给row_id和row_num if done then leave xxoo; END IF; insert into teacher(tname) values(ssname); end loop xxoo; #终止循环 close my cursor; #关闭游标 end // delimter;

11.动态执行SQL(防SQL注入)： delimiter // create procefure p7( in tpl varchar(255), in arg int ) begin 1.预检测某个东西，SQL语句合法化 2.SQL = 格式化 tp+arg 3.执行SQL语句 set @x0 = arg; #声明变量
PREPARE(准备)
XXX(变量)
FROM ‘select * from student where sid > ?‘;
EXECUTE(执行)
xxx USING @arg(替换上面的?);
DEALLOCATE prepare prod;(执行已经格式化完成的SQL语句) end // delimter; call p7(‘select * from tb where id > ?‘,9) delimiter \\ CREATE PROCEDURE p8 ( in nid int ) BEGIN set @nid = nid; PREPARE prod FROM ‘select * from student where sid > ?‘; EXECUTE prod USING @nid; DEALLOCATE prepare prod; END\\ delimiter ;