The step problem of MySQL self-increment column

the step problem of MySQL self-increment column

1. Unique index and Federated unique
2. Variants of foreign keys
3. SQL data row additions and deletions change
4. View
5. Trigger
6. Function
7. Stored Procedures
8. Transaction
9. Cursor
10. Dynamic execution of SQL (anti-SQL injection)

1.MySQL self-increment column size problem:1. Based on session level (single sign-on status):Show session variables like ' auto_inc% '; #查看自增长的默认步数, typically 1set session auto_increment_increment=2; #设置自增长会话步长为2set session auto_increment_offset=10;    #设置默认自增长初始值2. Based on global level (all users are in effect): Show global variables like ' auto_inc% ';    #查看全局变量中的自增长的默认步长set Global auto_increment_increment=2;    #设置全局会话步长set Global auto_increment_offset=10; #设置全局自增长初始值2. Unique index and union unique:CREATE TABLE t1 (id int, num int, name Charunique UQL (num) #唯一索引 unique UQL (num, name) #联合唯一); unique indexes and primary keys in common:1. All have the function of accelerating the search; 2. are unique and cannot be repeated unique indexes and different points of primary key:The primary key cannot be duplicated or empty, and the unique index cannot be duplicated, butcan have a value of NULL, such as a value that can be set to NULL in a federated index3. Variants of foreign keys:1. One-to-one: Blog user table 2. One-to-many: Lily net dating record table 3. Many-to-many: User host relationship Table

4.SQL data row additions and deletions:Increase:INSERT into Test (name, age) VALUES (' name ', +); INSERT into Test (name, age)values (' name1 ', +), (' Name2 ' ); #一次性插入多个值insert into Test (name, age)Select Name,age from Test1; #把某张表中的数据插入Delete:Delete from Test;delete from test where id>2 andName= ' name1 ';Change:Updata Test set name= ' name2 ', age=19 where id>12 and name= ' name1 ';Check:SELECT * from Test;select id,name from test where Id>2;select name,age,123From Test;selectname as RnameFrom Test;select * FROM testwhere ID in (1,3,5,7); select * FROM Testwhere ID in (select ID from test1); select * FROM Testwhere ID between 5 and 9; #闭区间, can be taken from around wildcard charactersSELECT * FROM Testwhere name like ' name% '; % matches no numeric characters; _ Matches one character paging select * from TestLimit Ten; Take the first 10 select * FROM TestLimit 0,10; Indicates starting from 0, fetch 0 followed by 10 select * FROM TestLimit Ten offset; Indicates starting at 20, taking 20 after the first 10 SortSELECT * FROM TestORDER BY id DESC; ID from large to small arrange select * from TestORDER BY ID ASC;    ID from small to large arrange select * from Test ORDER BY age DESC, id ASC;    Multiple different sorts select * from Test ORDER by DESC limit 10; After taking 10 strips Grouping(Aggregate function: Count,max,min,sum,avg averaging) select Max (ID), ID from test group by sex;    If the same sex is encountered, only the maximum ID of select COUNT (ID) is taken, and the ID from test group by sex; Count select count (id) as Count,id form test GROUP by Sex;select count (ID), ID from test group by sex have count (ID) >2;when you filter the results of an aggregate function two times, you must use the havingtable Operation:#左右连表 Joinselect * FROMtest1,test2 where test1.id = test2.part_id;SELECT * FROM Test1Left joinTest2 on test1.id = test2.part_id; Test1 left will show allSELECT * FROM Test right Join test2 on test1.id = test2.part_id; Test1 to the right will show allSELECT * FROM Test  innder Join test2 on test1.id = test2.part_id; Will hide the line that appears null#上下连表 Unionselect id,name from Test1union # automatic de-weightSelect Id,name from Test2;Select Id,name from Test1UNION ALL # do not go heavy select Id,name from Test2;Dump mysql files:Mysqldump-uroot test1 > Test1.sql-p #数据表结构 + data mysqldump-uroot-d test1 > Test1.sql-p #只有数据表结构 To import a MySQL file:Create databases test1;mysqldump-uroot-d test1 < test1.sql-p;Temp TableSelect ID from (SELECT ID from test where num>60) as B;Add ConditionSelect min (num), min (num) +1,Case if num<10 then 0 else min (num) EndFrom score5. View:#创建CREATE VIEW as View1SELECT * FROM Test where id>10; #视图是一个临时表 #The view is virtual, not a physical table, so you cannot insert data#修改alter View name as sql# delete the drop view name;6. Trigger:#插入前create Trigger T1before INSERTOn student for each rowbegininsert to teacher (tname) VALUES (new.sname);end# after you insert after insert# Delete before before delete# removed after delete# update before before update# update after update# because of the default; end, so the end is not executed, so you want to execute the trigger beforeModify Terminator Firstdelimiter// CREATE trigger T1 before INSERT on student for each ROWBEGIN INSERT into teacher (tname) VALUES (sname); END // delimiter;#创建时自动插入: Drop trigger T1; #结束上一个触发器delimiter//create trigger T1 before INSERT on student for each rowbegininsert into teacher (tname) VALUES (New.sname); END//delimiter; INSERT into student (Gender,class_if,sname) VALUES (' Female ', 1, ' abc ')7. Functions: #自定义函数 (with return value)#创建函数delimiter \\create function F1 (i1 int, i2 int) returns intbegin declare num int;    Declares that a variable type is an integer set num = I1 + i2; return (NUM); END \\delimiter; #运行函数select F1 (1,100); built-in functions: Time focus8.SQL stored procedures:1. Simple Stored ProceduresDelimiter//create PROCEDURE p1 () BEGIN select * from student; Insert into teacher (tname) VALUES (' ct '); Enddelimiter; #调用存储过程Call P1;cursor.callproc (' p1 ')2. Transmission parameters (In,out,inout)Delimiter//create PROCEDURE P2 (inchN1 int,inchn2 int) BEGIN SELECT * FROM student where sid>n1; end# CallCall P2 (12,2);cursor.callproc (' P2 ', (12,2))Delimiter//create PROCEDURE p2 (in N1 int, outN2 int #out伪装返回值) BEGIN set n2 = 123123; SELECT * FROM student where sid>n1; end# CallSet @vi = 0 #创建了一个session级的变量叫做v1, can be received externallyCall P2 (@v1)Select @v1; Receive variablecursor.execute (' select @_p2_0,@_p2_1 ') #pymysql中接收存储过程变量Characteristics of the stored procedure: A. InOut (in Out) b.pymysql why there is a result value and an out-forged return value: The role of Out: used to identify the execution result of a stored procedure, such as 1 for failure, 2 for success, and 3 for partial success9. Transaction:Delimiter//create procedure P4 (out status int) BEGIN 1. Declares that execution {set status = 1 if an exception occurs;    Rollback    #回滚} start Transaction--a account to reduce the increase of--b account by a commit;    End Set status = 2; #如果这里的事务执行顺利, the resulting variable equals 2, does not perform a rollback end//delimiter;d elimiter \\create procesdure p1 (out P_return_code tinyint) BEGINdeclare exit handler for SqlException#这样代码的意思是如果没有顺利执行, execute the following code 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, wrong return 110. Cursors:Delimiter//create procedure P6 () begin declare row_id int;    --Custom Variable 1 declare row_num varchar (50);       ---Custom variable 2 declare done INT DEFAULT FALSE;    Declare my_cursor cursor FOR select id,num from A;   Declare CONTINUE HANDLER for don't FOUND SET done = TRUE;    Open my_cursor;    #打开游标 xxoo:loop #开始循环 fetch my_cursor into row_id,row_num;        #取一行数据赋值给row_id和row_num if do then leave Xxoo;        END IF;    Insert into teacher (Tname) values (ssname);    End Loop Xxoo;    #终止循环close my cursor; #关闭游标 End//delimter;11. Dynamically Execute SQL (anti-SQL injection):Delimiter//create procefure P7 (in TPL varchar (255), in ARG int) begin 1. Pre-detection of something, legalization of SQL statements 2.SQL = formatted Tp+arg   3. Execute SQL statement Set @x0 = arg; #声明变量PREPAREReadyXXXVariableFrom ' select * from student where Sid >? ';EXECUTEImplementationxxx USING @arg(replace the above?);deallocate prepare prod;(Executes the formatted SQL statement)  end//delimter; call p7 (' select * from TB where ID >? ', 9) delimiter \\   & Nbsp;        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;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&Nbsp;    end\\             Delimiter;   

MySQL self-increment column size problem