這篇文章主要介紹了mysql學習筆記之數據定義表約束,分頁方法,結合實例形式總結分析了數據定義、主鍵、外鍵、自增長、約束等概念與用法,并給出了關于分頁的實例與相關操作技巧,需要的朋友可以參考下
本文實例講述了mysql學習筆記之數據定義表約束，分頁方法。分享給大家供大家參考，具體如下：
1. primary key 主鍵
特點：主鍵是用于唯一標識一條記錄的約束，一張表最多只能有一個主鍵，不能為空也不能重復
create?table?user1(id?int?primary?key,name?varchar(32)); mysql>?insert?into?user1?values(1,'hb'); Query?OK,?1?row?affected?(0.10?sec) mysql>?insert?into?user1?values(1,'hb'); ERROR?1062?(23000):?Duplicate?entry?'1'?for?key?'PRIMARY' mysql>?insert?into?user1?(name)?values('hb'); ERROR?1364?(HY000):?Field?'id'?doesn't?have?a?default?value
2. auto_increament 自增長
mysql>?create?table?user2(id?int?primary?key?auto_increment,name?varchar(34)); mysql>?insert?into?user2?(name?)?values?("name1"); Query?OK,?1?row?affected?(0.09?sec) mysql>?insert?into?user2?(name?)?values?("name2"); Query?OK,?1?row?affected?(0.05?sec) mysql>?insert?into?user2?(name?)?values?("name3"); Query?OK,?1?row?affected?(0.13?sec) mysql>?select?*?from?user2; +----+-------+ |?id?|?name?| +----+-------+ |?1?|?name1?| |?2?|?name2?| |?3?|?name3?| +----+-------+
3. unique 唯一約束
特點：表的某列值不能重復，可以添加重復的NULL
create?table?user3(id?int?primary?key?auto_increment,name?varchar(34)?unique); mysql>?create?table?user3(id?int?primary?key?auto_increment,name?varchar(34)?unique); Query?OK,?0?rows?affected?(0.39?sec) mysql>?insert?into?user3?(name?)?values?("name3"); Query?OK,?1?row?affected?(0.11?sec) mysql>?insert?into?user3?(name?)?values?("name3"); ERROR?1062?(23000):?Duplicate?entry?'name3'?for?key?'name'
允許插入null，并且可以多個
mysql>?insert?into?user3?(name?)?values?(null); Query?OK,?1?row?affected?(0.12?sec) mysql>?insert?into?user3?(name?)?values?(null); Query?OK,?1?row?affected?(0.12?sec) mysql>?select?*?from?user3; +----+-------+ |?id?|?name?| +----+-------+ |?3?|?NULL?| |?4?|?NULL?| |?1?|?name3?| +----+-------+
4. not null
mysql表的列默認情況下可以為null,如果不允許某列為空則可以使用not null說明
create?table?user4?(id?int?primary?key?auto_increment,name?varchar(32)?not?null); mysql>?insert?into?user4?(name)?values(null); ERROR?1048?(23000):?Column?'name'?cannot?be?null
5. foreign key 外鍵
從理論上說先建立主表，再建立從表
雇員表：
create?table?dept(id?int?primary?key?,?name?varchar(32));
部門表：
create?table?emp( id?int?primary?key?, name?varchar(32), deptid?int, constraint?myforeignkey?foreign?key(deptid)?references?dept(id) ); mysql>?select?*?from?dept; +----+-------+ |?id?|?name?| +----+-------+ |?1?|?name1?| +----+-------+ 1?row?in?set?(0.00?sec) mysql>?insert?into?emp?values(1,'aaa',1); Query?OK,?1?row?affected?(0.22?sec) mysql>?insert?into?emp?values(1,'aaa',2); ERROR?1062?(23000):?Duplicate?entry?'1'?for?key?'PRIMARY' mysql>?insert?into?emp?values(1,'aaa',null); ERROR?1062?(23000):?Duplicate?entry?'1'?for?key?'PRIMARY' mysql>?insert?into?emp?values(2,'aaa',null); Query?OK,?1?row?affected?(0.13?sec) mysql>?select?*?from?emp; +----+------+--------+ |?id?|?name?|?deptid?| +----+------+--------+ |?1?|?aaa?|???1?| |?2?|?aaa?|??NULL?| +----+------+--------+ 2?rows?in?set?(0.00?sec)
總結：
① 外鍵只能指向主表的主見列或者unique
② 外鍵的數據類型應該與它指向的列類型一致
③ 外鍵的值：NULL 或者 指向列中存在的值
④ 外鍵可以指向本表的主鍵列或者unique
mysql 不支持check
create?table?user99(age?int?check(age>13)); mysql>?create?table?user99(age?int?check(age>13)); Query?OK,?0?rows?affected?(0.19?sec) mysql>?insert?into?user99?values(99); Query?OK,?1?row?affected?(0.04?sec) mysql>?select?*?from?user99; +------+ |?age?| +------+ |??99?| +------+
mysql 分頁
基本語法：
select * from 表明 where 條件 limit 從第幾條取,取出幾條
mysql 是從第0條開始取數據
mysql>?select?*?from?student; +------+--------+---------+---------+------+ |?id??|?name??|?chinese?|?english?|?math?| +------+--------+---------+---------+------+ |??1?|?張小明???|???89?|???78?|??90?| |??2?|?李進????|???67?|???98?|??56?| |??3?|?王五????|???87?|???78?|??77?| |??4?|?李一???|???88?|???98?|??90?| |??5?|?李來財????|???82?|???84?|??67?| |??6?|?張進寶???|???55?|???85?|??45?| |??7?|?張小明???|???75?|???65?|??30?| +------+--------+---------+---------+------+ 7?rows?in?set?(0.05?sec) mysql>?select?*?from?student?limit?2,2; +------+------+---------+---------+------+ |?id??|?name?|?chinese?|?english?|?math?| +------+------+---------+---------+------+ |??3?|?王五???|???87?|???78?|??77?| |??4?|?李一??|???88?|???98?|??90?| +------+------+---------+---------+------+ 2?rows?in?set?(0.00?sec)
按照語文成績排序，查處第3條到第5條
mysql>?select?*?from?student?order?by?chinese?desc?limit?3,2; +------+--------+---------+---------+------+ |?id??|?name??|?chinese?|?english?|?math?| +------+--------+---------+---------+------+ |??5?|?李來財????|???82?|???84?|??67?| |??7?|?張小明???|???75?|???65?|??30?| +------+--------+---------+---------+------+ 2?rows?in?set?(0.00?sec)
擴展，分頁:pageNow , pageSize
select * from 表明 where 條件 [group by … having … order by …]limit 從第幾條取,取出幾條
select * from 表明 where 條件 [group by … having … order by …]limit (pageNow-1)*pageSize, pageSize
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
