本文实例讲述了MySQL学习笔记之数据定义表约束，分页方法。分享给大家供大家参考，具体如下：
1. primary key 主键
特点：主键是用于唯一标识一条记录的约束，一张表最多只能有一个主键，不能为空也不能重复
|
1
2
3
4
5
6
7
|
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 自增长
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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
|
1
2
3
4
5
6
7
|
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，并且可以多个
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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说明
|
1
2
3
|
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)); |
部门表：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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
|
1
2
3
4
5
6
7
8
9
10
11
|
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条开始取数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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条
|
1
2
3
4
5
6
7
8
|
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数据库计有所帮助。
