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数据库计有所帮助。