MySQL進修筆記之數據界說表束縛,分頁辦法總結。本站提示廣大學習愛好者：（MySQL進修筆記之數據界說表束縛,分頁辦法總結）文章只能為提供參考，不一定能成為您想要的結果。以下是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
願望本文所述對年夜家MySQL數據庫計有所贊助。
