程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL進修筆記之數據界說表束縛,分頁辦法總結

MySQL進修筆記之數據界說表束縛,分頁辦法總結

編輯:MySQL綜合教程

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數據庫計有所贊助。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved