MySQL學(xué)習(xí)筆記之?dāng)?shù)據(jù)定義表約束,分頁(yè)方法總結(jié)_MySQL

本文實(shí)例講述了mysql學(xué)習(xí)筆記之?dāng)?shù)據(jù)定義表約束,分頁(yè)方法。分享給大家供大家參考,具體如下:

1. primary key 主鍵

特點(diǎn):主鍵是用于唯一標(biāo)識(shí)一條記錄的約束,一張表最多只能有一個(gè)主鍵,不能為空也不能重復(fù)

 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 自增長(zhǎng)

 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 唯一約束

特點(diǎn):表的某列值不能重復(fù),可以添加重復(fù)的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,并且可以多個(gè)

 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表的列默認(rèn)情況下可以為null,如果不允許某列為空則可以使用not null說(shuō)明

 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 外鍵

從理論上說(shuō)先建立主表,再建立從表

雇員表:

 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)  

總結(jié):

① 外鍵只能指向主表的主見(jiàn)列或者unique
② 外鍵的數(shù)據(jù)類型應(yīng)該與它指向的列類型一致
③ 外鍵的值: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 分頁(yè)

基本語(yǔ)法:

select * from 表明 where 條件 limit 從第幾條取,取出幾條
mysql 是從第0條開(kāi)始取數(shù)據(jù)

 mysql> select * from student; +------+--------+---------+---------+------+ | id  | name  | chinese | english | math | +------+--------+---------+---------+------+ |  1 | 張小明   |   89 |   78 |  90 | |  2 | 李進(jìn)    |   67 |   98 |  56 | |  3 | 王五    |   87 |   78 |  77 | |  4 | 李一   |   88 |   98 |  90 | |  5 | 李來(lái)財(cái)    |   82 |   84 |  67 | |  6 | 張進(jìn)寶   |   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)  

按照語(yǔ)文成績(jī)排序,查處第3條到第5條

 mysql> select * from student order by chinese desc limit 3,2; +------+--------+---------+---------+------+ | id  | name  | chinese | english | math | +------+--------+---------+---------+------+ |  5 | 李來(lái)財(cái)    |   82 |   84 |  67 | |  7 | 張小明   |   75 |   65 |  30 | +------+--------+---------+---------+------+ 2 rows in set (0.00 sec)  

擴(kuò)展,分頁(yè):pageNow , pageSize

select * from 表明 where 條件 [group by … having … order by …]limit 從第幾條取,取出幾條
select * from 表明 where 條件 [group by … having … order by …]limit (pageNow-1)*pageSize, pageSize

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題:《MySQL索引操作技巧匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》、《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊8 分享