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 唯一限制的特徵

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表的列預設值

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

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

r

總結：

① 外鍵只能指向主表的主見列或unique

② 外鍵的資料型別應該與它指向的列型別一致

③ 外鍵的值：NULL 或指向列中存在的值

④ 外鍵可以指向本表的主鍵欄位或unique

mysql 不支援check

create table dept(id int primary key , name varchar(32));

sql 分頁紙 片語

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)

sql 分頁詞 3取,取出幾條

mysql 是從第0條開始取數據

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 |
+------+

按照語文成績排序，查處第3條到第5條

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)

pagespage 頁

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