MySQL学习笔记之数据定义表约束,分页方法总结
本文实例讲述了MySQL学习笔记之数据定义表约束，分页方法。分享给大家供大家参考，具体如下：
1.primarykey主键
特点：主键是用于唯一标识一条记录的约束，一张表最多只能有一个主键，不能为空也不能重复
createtableuser1(idintprimarykey,namevarchar(32));
mysql>insertintouser1values(1,'hb');
QueryOK,1rowaffected(0.10sec)
mysql>insertintouser1values(1,'hb');
ERROR1062(23000):Duplicateentry'1'forkey'PRIMARY'
mysql>insertintouser1(name)values('hb');
ERROR1364(HY000):Field'id'doesn'thaveadefaultvalue
2.auto_increament自增长
mysql>createtableuser2(idintprimarykeyauto_increment,namevarchar(34));
mysql>insertintouser2(name)values("name1");
QueryOK,1rowaffected(0.09sec)
mysql>insertintouser2(name)values("name2");
QueryOK,1rowaffected(0.05sec)
mysql>insertintouser2(name)values("name3");
QueryOK,1rowaffected(0.13sec)
mysql>select*fromuser2;
+----+-------+
|id|name|
+----+-------+
|1|name1|
|2|name2|
|3|name3|
+----+-------+
3.unique唯一约束
特点：表的某列值不能重复，可以添加重复的NULL
createtableuser3(idintprimarykeyauto_increment,namevarchar(34)unique);
mysql>createtableuser3(idintprimarykeyauto_increment,namevarchar(34)unique);
QueryOK,0rowsaffected(0.39sec)
mysql>insertintouser3(name)values("name3");
QueryOK,1rowaffected(0.11sec)
mysql>insertintouser3(name)values("name3");
ERROR1062(23000):Duplicateentry'name3'forkey'name'
允许插入null，并且可以多个
mysql>insertintouser3(name)values(null); QueryOK,1rowaffected(0.12sec) mysql>insertintouser3(name)values(null); QueryOK,1rowaffected(0.12sec) mysql>select*fromuser3; +----+-------+ |id|name| +----+-------+ |3|NULL| |4|NULL| |1|name3| +----+-------+
4.notnull
mysql表的列默认情况下可以为null,如果不允许某列为空则可以使用notnull说明
createtableuser4(idintprimarykeyauto_increment,namevarchar(32)notnull); mysql>insertintouser4(name)values(null); ERROR1048(23000):Column'name'cannotbenull
5.foreignkey外键
从理论上说先建立主表，再建立从表
雇员表：
createtabledept(idintprimarykey,namevarchar(32));
部门表：
createtableemp( idintprimarykey, namevarchar(32), deptidint, constraintmyforeignkeyforeignkey(deptid)referencesdept(id) ); mysql>select*fromdept; +----+-------+ |id|name| +----+-------+ |1|name1| +----+-------+ 1rowinset(0.00sec) mysql>insertintoempvalues(1,'aaa',1); QueryOK,1rowaffected(0.22sec) mysql>insertintoempvalues(1,'aaa',2); ERROR1062(23000):Duplicateentry'1'forkey'PRIMARY' mysql>insertintoempvalues(1,'aaa',null); ERROR1062(23000):Duplicateentry'1'forkey'PRIMARY' mysql>insertintoempvalues(2,'aaa',null); QueryOK,1rowaffected(0.13sec) mysql>select*fromemp; +----+------+--------+ |id|name|deptid| +----+------+--------+ |1|aaa|1| |2|aaa|NULL| +----+------+--------+ 2rowsinset(0.00sec)
总结：
①外键只能指向主表的主见列或者unique
②外键的数据类型应该与它指向的列类型一致
③外键的值：NULL或者指向列中存在的值
④外键可以指向本表的主键列或者unique
mysql不支持check
createtableuser99(ageintcheck(age>13)); mysql>createtableuser99(ageintcheck(age>13)); QueryOK,0rowsaffected(0.19sec) mysql>insertintouser99values(99); QueryOK,1rowaffected(0.04sec) mysql>select*fromuser99; +------+ |age| +------+ |99| +------+
mysql分页
基本语法：
select*from表明where条件limit从第几条取,取出几条
mysql是从第0条开始取数据
mysql>select*fromstudent; +------+--------+---------+---------+------+ |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| +------+--------+---------+---------+------+ 7rowsinset(0.05sec) mysql>select*fromstudentlimit2,2; +------+------+---------+---------+------+ |id|name|chinese|english|math| +------+------+---------+---------+------+ |3|王五|87|78|77| |4|李一|88|98|90| +------+------+---------+---------+------+ 2rowsinset(0.00sec)
按照语文成绩排序，查处第3条到第5条
mysql>select*fromstudentorderbychinesedesclimit3,2; +------+--------+---------+---------+------+ |id|name|chinese|english|math| +------+--------+---------+---------+------+ |5|李来财|82|84|67| |7|张小明|75|65|30| +------+--------+---------+---------+------+ 2rowsinset(0.00sec)
扩展，分页:pageNow,pageSize
select*from表明where条件[groupby…having…orderby…]limit从第几条取,取出几条
select*from表明where条件[groupby…having…orderby…]limit(pageNow-1)*pageSize,pageSize
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL索引操作技巧汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》
希望本文所述对大家MySQL数据库计有所帮助。