3.数据类型3.1.1tinyint类型在MySQL中整型可以指定是有符号的和无符号的默认有符号的可以通过UNSIGNED来说明某个字段是无符号的无符号案例注意尽量不使用unsigned,对于int类型可能存放不下的数据int unsigned同样可能存放不下与其如此还不如不设计时将int类型提升为bigint类型3.1.2bit类型bit使用的注意事项bit字段在显示时是按照ASCI码对应的值显示。如果我们有这样的值只存放0或1这时可以定义bit(1),这样可以节省空间如果我们有这样的值只存放0或1这是可以定义bit(1)。这样可以节省空间。3.1.3小数类型1float小数 float42表示的范围是-99.99~99.99,MySQL在保存只时会进行四舍五入如果定义的是float(4,2)usigned这时因为把它指定为无符号的数范围时0-99.992decimaldecimal(5,2)表示范围是 -999.99~999.99decimal(5,2) unsigned 表示的范围 0~999.99decimal和float很像但是有区别float和decimal表示的精度不一样说明float表示的精度大约是7位decimal整数最大位数m为65支持小数最大为数是d是30。如果d被省略默认为0如果m被省略默认是10注如果希望小数精度高推荐使用decimal。3.2字符串类型3.2.1char语法char(L) :固定长度字符串L是可以存储的长度单位为字符最大长度值可以为255案例说明char(2) 表示可以存放两个字符可以是字符或汉字但是不能超过2个最多只能是2553.2.2 varchar语法varchar(L):可变长度字符串L表示字符长度最大长度65535个字节案例说明关于varchar(len),len到底有多大这个len值和表的编码密切相关varchar长度可以指定为0-65535之间的值但是又1-3个字节用于记录数据大小所以说有效字节数是65532。当我们的表的编码是utf8mb4时varchar(n)的参数最大值是65532/416383因为utf8mb4中一个字符占3个字节如果编码是gbkvarchar(n)的参数n最大时65532/2 32766因为gbk中一个字符占用2个字节。3.2.3char和varchar比较实际存储char(4)Varchar(4)char占用字节Varchar占用字节abcdabcdabcd4*3124*3113AAA4*3121*314AbcdeXX数据超过长度数据超过长度如何选择定长或变长字符串如果数据确定长度都一样就使用定长char,比如身份证手机号md5如果数据长度有变化就使用变长varchar,比如名字地址但是你要保证最长的能存进的进去。定义的磁盘空间比较浪费但是效率高。变长的磁盘空间比较节省但是效率低。定长的意义时直接开辟号对应的空间。变长的意义时在不超过自定义范围内的情况下用多少开辟多少。3.2.4日期和时间类型常用的日期有如下三个date:日期‘yyyy-mm-dd’,占用三字节datetime时间日期格式‘yyyy-mm-dd HH:ii:ss 表示范围从1000到9999,占用八字节timestamp时间戳从1970年开始的yyyy-mm-dd HH:ii:ss格式和datetime完全一致占用四字节3.2.5 enum和set语法enum:枚举单选类型enum(选项1’选项2,’选项3‘ ...);该设定只是提供了若干个选项的值最终一个单元格中实际只存储了其中一个值而且处于效率考虑这些值实际存储的是数字因为这些选项的每个选项值依次对应如下数字1,2,3...最多65535个当我们添加枚举值时也可以添加对应的数字编号set集合多选类型set(’选项值1‘’选项值2‘’选项值3‘...);该设定只是提供了若干个选项值最终一个单元格中设计可存储了其中任意多个值而且出于效率考虑这些值实际存储的是数字因为这些选项的每个选项值依次对应如下数字12481632......最多64个注不建议在添加枚举值集合值得时候采用数字得方式因为不利于阅读。案例有一个调查表votes需要调查人得喜好比如登山游泳篮球武术中去选择可以多选男女[单选]插入数据有如下数据想查找所有喜欢登山的人使用如下查询语句不能查询出所有爱好为登山的人集合查询使用find_in_set函数find_in_set(sub,str_list)如果sub在str_list中则返回下标如果不在返回0str_list用逗号分隔的字符串4.表的约束真正约束字段的是数据类型但是数据类型约束很单一需要有一些额外的约束更好的保证数据的合法性从业务逻辑角度保证数据的正确性。比如有一个字段是email,要求是唯一的。表的约束很多这里主要介绍如下几个null/not null,default,comment,zerofill,primary key,auto_increment,unique key。4.1空属性两个值null(默认的)和not null(不为空)数据库默认字段基本都是字段为空但是实际开发时尽可能保证字段不为空因为数据为空没办法参与运算。案例创建一个班级表包含班级名和班级所在的教室。站在正常的业务逻辑中如果班级没有名字你不知道你在哪个班级如果教室名字可以为空就不知道在哪上课所以我们在设计数据库表的时候一定要在表中进行限制满足上面条件的数据就不能插入到表中。这就是“约束”。//插入数据时没有给教室数据插入失败4.2默认值默认值某一种数据会经常性地出现某个具体地值可以在一开始就指定好在需要真实数据的时候用户可以选择性地使用默认值默认值的生效数据在插入的时候不给该字段赋值就使用默认值注意只有设置了default的列才可以在插入值得时候对列进行省略4.3列描述列描述comment,没有实际含义专门用来描述字段会根据表创建语句保存用来给程序员或DBA来进行了解。注意not null 和default一般不需要同时出现因为default本身有默认值不会为空通过desc查看不到注释信息通过show可以看到4.4zerofill刚开始学习数据库时很多人对数字类型后面得长度很迷茫。通过show看看tt3表得创建语句可以看到int(10),这个代表什么意思呢整型不是4字节码这个10又代表什么呢其实没有zerofill这个属性括号内的数字是毫无意义的a和b列就是前面插入的数据如下可以看到int(10),这个代表什么意思呢整型不是4字节码这个10又代表什么呢其实没有zerofill这个属性括号内的数字是毫无意义的。a和b列就是前面插入的数据如上但是对列添加了zerofill属性后显示的结果就有所不同了修改了tt3表的属性对a列添加了zerofill属性再进行查找返回如下结果这次可以看到a的值由原来的1编程00001,这就是zerofill属性的作用如果宽度小于设定的宽度这里设置的是5自动填充0。要注意的是这只是最后显示的结果。在Mysql中实际存储的还是1.为什么是这样呢我们可以用hex函数来证明。可以看出数据库内部存储的还是100001只是设置了zerofill属性后的一种格式化输出而已。4.5主键主键primary key用来唯一的约束该字段里面的数据不能重复不能为空一张表中最多只能有一个主键主键所在的列通常是整数类型。主键约束主键对应的字段中不能重复一旦重复操作失败当表创建好以后但是没有主键的时候可以在追加主键alter table 表名 add primary key(字段列表)删除主键alter table 表名 drop primary key;复合主键在创建表的时候在所有字段之后使用primary key(主键字段列表)来创建主键如果有多个字段作为主键可以使用复合主键。4.6自增长auto_increment:当对应的字段不给值会自动地被系统触发系统会从当前字段中已经有地最大值1操作,得到一个新地不同的值。通常和主键搭配使用作为逻辑主键自增长任何一个字段要做自增长前提是本身是一个索引key——栏有值自增长字段必须是整数一张表最多只能有一个自增长在插入后获取上次插入的AUTO_INCREMENT的值批量插入获取的是第一个值索引在关系数据库中索引是一种单独的物理的对数据库表中一列或多列的值进行排序的一种存储结构它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值然后顺指针找到包含该值的行这样可以使应用于表的SQL语句执行得更快可以快速访问数据库表中得特定信息。4.7唯一键一张表中有往往有很多字段需要唯一性数据不能重复但是一张表中只能有一个主键唯一键就可以解决表中有多个字段需要唯一性约束的问题。唯一键的本质和主键差不多唯一键允许为空而且可以多个为空空字段不做唯一性比较。关于唯一键和主键的区别我们可以简单理解成主键更多的是标识唯一性的而唯一键更多的是保证在业务上不要和别的信息出现重复看着好像没有什么区别举个例子比如在公司我们需要一个员工管理系统系统中有一个员工表员工表中有两列信息一个身份证号码一个员工号码我们可以选择身份号码作为主键。而我们设计员工工号的时候需要一种约束而所有员工工号都不能重复。具体指的是在公司的业务上不能重复我们设计表的时候需要这个约束那么就可以将员工工号设计成唯一键。一般而言我们建议将主键设计成为和当前业务无关的字段这样当业务调整的时候我们可以尽量不会对主键做过大的调整。4.8外键外键用于定义主表和从表之间的关系外键约束主要定义在从表上主表则必须是有主键约束或unique约束。当定义外键后要求外键列数据必须在主表的主键列存在或为null。语法foreign key (字段名) refereneces 主表列如果将班级表中的数据都设计在每个学生表的后面那就会出现数据冗余所以我们只要设计成让stu-class_id和myclass-id形成关联的关系 外键约束先创建主键表再创建从表正常插入数据插入一个班级号为30的学生因为没有这个班级所以插入不成功插入班级id为null,比如来了一个学生目前还没有分配班级如何理解外键约束在MySQL中外键约束用于在两张表之间建立关联保证数据的引用完整性。实例插入学生信息中具体的班级但是该班级却没有在班级表中。比如这个学校年级中只有一班和二班但是在上课的学生当中却有3班的学生。上述明显是有问题的因为两张表在业务上是有相关性的但是在业务上没有建立约束关系那么就有可能出现问题。解决方案就是通过外键完成。建立外键的本质其实就是把相关性交给mysql去审核提前告诉mysql表之间的约束关系那么当用户插入不符合业务逻辑的数据的时候mysql不允许你插入。例子有一个商店的数据记录客户及购物情况有三个表组成商品goods商品编号goods_id,商品名称goods_name,单价unitprice,商品类别category,供应商provider客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex身份证card_id)购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)要求每个表的主外键客户的姓名不能为空值邮箱不能重复客户的性别男女mysql create database if not exists bit32mall default character set utf8mb4; Query OK, 1 row affected (0.00 sec) mysql show database; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near database at line 1 mysql show database; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near database at line 1 mysql show databases; -------------------- | Database | -------------------- | information_schema | | bit32mall | | db1 | | db2 | | db3 | | db5 | | index_test | | mysql | | performance_schema | | test01 | | test02 | | test03 | | test2 | | test5 | | user_db | -------------------- 15 rows in set (0.00 sec) mysql use bit32mall; Database changed mysql create table if not exists goods - ( - goods_id int primary key auto_increment comment 商品编号, - goods_name varchar(32) not null comment 商品名称, - unitprice int not null default 0 comment 单价单位分, - category varchar(12) comment 商品分类, - provider carchar(64) not null comment 供应商名称 - ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near carchar(64) not null comment 供应商名称 ) at line 11 mysql create table if not exists goods( - goods_id int primary key auto_increment comment 商品编号, - unitprice int not null default 0 comment 单价单位分, - category varchar(12) comment 商品分类, - provider varchar(64) not null comment 供应商名称 - ); Query OK, 0 rows affected (0.03 sec) mysql create table if not exists customer( - customer_id int primary key auto_increment comment 客户编号, - name varchar(32) not null comment 客户姓名, - address varchar(256) comment 客户地址, - email varchar(64) unique key comment 电子邮箱, - sex enum(男,女) not null comment 性别, - card_id char(18) unique key comment 身份证 - ); Query OK, 0 rows affected (0.04 sec) mysql create table if not exists purchase( - order_id int primary key auto_increment comment 订单号, - customer_id int comment 客户编号, - goods_id int comment 商品编号, - nums int default 0 comment 购买数量, - foreign key (customer_id) references customer(customer_id), - foreign key (goods_id) references goods(goods_id) - ); Query OK, 0 rows affected (0.04 sec)注本文更偏向于数据库数据类型与表的约束的复习练习和笔记操作过程更偏向于会用就行。