《Mysql入門MySQL學習筆記之數據定義表約束,分頁方法總結》要點：
本文介紹了Mysql入門MySQL學習筆記之數據定義表約束,分頁方法總結，希望對您有用。如果有疑問，可以聯系我們。
MYSQL數據庫本文實例講述了MySQL學習筆記之數據定義表約束,分頁方法.分享給大家供大家參考,具體如下：
MYSQL數據庫1. primary key 主鍵
MYSQL數據庫特點：主鍵是用于唯一標識一條記錄的約束,一張表最多只能有一個主鍵,不能為空也不能重復
MYSQL數據庫
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
MYSQL數據庫2. auto_increament 自增長
MYSQL數據庫
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 |
+----+-------+
MYSQL數據庫3. unique 唯一約束
MYSQL數據庫特點：表的某列值不能重復,可以添加重復的NULL
MYSQL數據庫
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'
MYSQL數據庫允許插入null,并且可以多個
MYSQL數據庫
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 |
+----+-------+
MYSQL數據庫4. not null
MYSQL數據庫mysql表的列默認情況下可以為null,如果不允許某列為空則可以使用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
MYSQL數據庫5. foreign key 外鍵
MYSQL數據庫從理論上說先建立主表,再建立從表
MYSQL數據庫雇員表：
MYSQL數據庫
create table dept(id int primary key , name varchar(32));
MYSQL數據庫部門表：
MYSQL數據庫
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)
MYSQL數據庫總結：
MYSQL數據庫① 外鍵只能指向主表的主見列或者unique
② 外鍵的數據類型應該與它指向的列類型一致
③ 外鍵的值：NULL 或者 指向列中存在的值
④ 外鍵可以指向本表的主鍵列或者unique
MYSQL數據庫mysql 不支持check
MYSQL數據庫
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數據庫mysql 分頁
MYSQL數據庫基本語法：
MYSQL數據庫select * from 表明 where 條件 limit 從第幾條取,取出幾條
mysql 是從第0條開始取數據
MYSQL數據庫
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)
MYSQL數據庫按照語文成績排序,查處第3條到第5條
MYSQL數據庫
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)
MYSQL數據庫擴展,分頁:pageNow , pageSize
MYSQL數據庫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常用函數大匯總》
MYSQL數據庫希望本文所述對大家MySQL數據庫計有所幫助.
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5192.html
