MySQL自增列的步長問題

標籤：open   格式   insert   values   sql注入   標識   平均值   顯示   關閉   

MySQL自增列的步長問題

1. 唯一索引和聯合唯一
2. 外鍵的變種
3. SQL資料行的增刪改查
4. 視圖
5. 觸發器
6. 函數
7. 預存程序
8. 事務
9. 遊標
10. 動態執行SQL(防SQL注入)

   1.MySQL自增列的步長問題：1.基於會話層級（單次登陸狀態下）：show session variables like ‘auto_inc%‘;    #查看自增長的預設步數，一般為1set session auto_increment_increment=2;    #設定自增長會話步長為2set 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連表操作：#左右連表 joinselect * 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的那一行隱藏#上下連表 unionselect id,name from test1union    # 自動去重select id,name from test2;select id,name from test1union 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 ROWBEGININSERT 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 ROWBEGININSERT 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(‘女‘,1,‘abc‘) 7.函數： #自訂函數(有傳回值)#建立函數delimiter \\create function f1(    i1 int,    i2 int)returns intBEGIN    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‘);ENDdelimiter ;#調用預存程序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 ;   

MySQL自增列的步長問題