金沙官网线上MySQL学习笔记之数据定义表约束,分

1. primary key 主键

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 
mysql> insert into user3 ;Query OK, 1 row affected mysql> insert into user3 ;Query OK, 1 row affected mysql> select * from user3;+----+-------+| id | name |+----+-------+| 3 | NULL || 4 | NULL || 1 | name3 |+----+-------+

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL索引操作技巧汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》

从理论上说先建立主表,再建立从表

mysql 不支持check

create table user1(id int primary key,name varchar;mysql> insert into user1 values;Query OK, 1 row affected mysql> insert into user1 values;ERROR 1062 : Duplicate entry '1' for key 'PRIMARY'mysql> insert into user1 ;ERROR 1364 : Field 'id' doesn't have a default value

总结:

select * from 表明 where 条件 [group by … having … order by …]limit 从第几条取,取出几条select * from 表明 where 条件 [group by … having … order by …]limit *pageSize, pageSize

特点:表的某列值不能重复,可以添加重复的NULL

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 mysql> select * from student limit 2,2;+------+------+---------+---------+------+| id | name | chinese | english | math |+------+------+---------+---------+------+| 3 | 王五 | 87 | 78 | 77 || 4 | 李一 | 88 | 98 | 90 |+------+------+---------+---------+------+2 rows in set 

希望本文所述对大家MySQL数据库计有所帮助。

mysql> create table user2(id int primary key auto_increment,name varchar;mysql> insert into user2  values ;Query OK, 1 row affected mysql> insert into user2  values ;Query OK, 1 row affected mysql> insert into user2  values ;Query OK, 1 row affected mysql> select * from user2;+----+-------+| id | name |+----+-------+| 1 | name1 || 2 | name2 || 3 | name3 |+----+-------+

特点:主键是用于唯一标识一条记录的约束,一张表最多只能有一个主键,不能为空也不能重复

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

create table emp(id int primary key ,name varchar,deptid int,constraint myforeignkey foreign key references dept;mysql> select * from dept;+----+-------+| id | name |+----+-------+| 1 | name1 |+----+-------+1 row in set mysql> insert into emp values;Query OK, 1 row affected mysql> insert into emp values;ERROR 1062 : Duplicate entry '1' for key 'PRIMARY'mysql> insert into emp values;ERROR 1062 : Duplicate entry '1' for key 'PRIMARY'mysql> insert into emp values;Query OK, 1 row affected mysql> select * from emp;+----+------+--------+| id | name | deptid |+----+------+--------+| 1 | aaa | 1 || 2 | aaa | NULL |+----+------+--------+2 rows in set 

允许插入null,并且可以多个

扩展,分页:pageNow , pageSize

create table user3(id int primary key auto_increment,name varchar;mysql> create table user3(id int primary key auto_increment,name varchar;Query OK, 0 rows affected mysql> insert into user3  values ;Query OK, 1 row affected mysql> insert into user3  values ;ERROR 1062 : Duplicate entry 'name3' for key 'name'

本文实例讲述了MySQL学习笔记之数据定义表约束,分页方法。分享给大家供大家参考,具体如下:

4. not null

① 外键只能指向主表的主见列或者unique② 外键的数据类型应该与它指向的列类型一致③ 外键的值:NULL 或者 指向列中存在的值④ 外键可以指向本表的主键列或者unique

create table user4 (id int primary key auto_increment,name varchar;mysql> insert into user4 ;ERROR 1048 : Column 'name' cannot be null

2. auto_increament 自增长

按照语文成绩排序,查处第3条到第5条

mysql 分页

3. unique 唯一约束

create table user99);mysql> create table user99);Query OK, 0 rows affected mysql> insert into user99 values;Query OK, 1 row affected mysql> select * from user99;+------+| age |+------+| 99 |+------+

金沙官网线上,select * from 表明 where 条件 limit 从第几条取,取出几条mysql 是从第0条开始取数据

mysql表的列默认情况下可以为null,如果不允许某列为空则可以使用not null说明

5. foreign key 外键

本文由金沙官网线上发布于数据库,转载请注明出处:金沙官网线上MySQL学习笔记之数据定义表约束,分

您可能还会对下面的文章感兴趣: