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說明

?

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

從理論上說先建立主表，再建立從表

僱員表：

?

1	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

更多關於MySQL相關內容感興趣的讀者可檢視本站專題：《MySQL索引操作技巧彙總》、《MySQL日誌操作技巧大全》、《MySQL事務操作技巧彙總》、《MySQL儲存過程技巧大全》、《MySQL資料庫鎖相關技巧彙總》及《MySQL常用函式大彙總》

希望本文所述對大家MySQL資料庫計有所幫助。