目 录
一、讲义部分
数据库的增删改查已经介绍完毕，今天从表的详细操作开始讲解
表操作 今日内容 1.数据类型 建表的时候，字段都有对应的数据类型 整型 浮点型 字符类型(char与varchar) 日期类型 枚举与集合 2.约束条件 primary key unique key not null 详细内容 创建表的完整语法 #语法： create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); #注意： 1. 在同一张表中，字段名不能相同 2. 宽度和约束条件可选，字段名和类型是必须的 3. 最后一个字段后不能加逗号！ # 补充： # 1.宽度指的是对存储数据的限制 create table userinfo(name char); insert into userinfo values('jason'); """ 1.没有安全模式的数据库版本，能够存放数据但是只会存进去一个j 2.最新数据库版本直接报错提示无法存储:Data too long for column 'name' at row 1 """ # 2.约束条件初识>>> null 与 nut null create table t1(id int,name char not null); insert into t1 values(1,'j'); # 正常存储 insert into t1 values(2,null); # 报错 # 总结 类型与约束条件区别 # 类型：限制字段必须以什么样的数据类型存储 # 约束条件：约束条件是在类型之外添加一种额外的限制 基本数据类型 整型 分类：TINYINT SMALLINT MEDIUMINT INT BIGINT 作用：存储年龄，等级，id，各种号码等 类型存储范围：参考图片http://www.cnblogs.com/linhaifeng/articles/7233411.html 验证整型字段有无符号及范围 create table t1(x tinyint); insert into t1 values(128),(-129); create table t2(x tinyint unsigned); insert into t2 values(-1),(256); create table t3(x int unsigned); insert into t3 values(4294967296); 疑问:类型后面的宽度能否改变字段存储的大小限制 create table t4(x int(8)); insert into t4 values(4294967296123); # 显示时，不够8位用0填充，如果超出8位则正常显示 create table t5(x int(8) unsigned zerofill); insert into t5 values(4294967296123); # create table t6(id int(10) unsigned); # create table t7(id int(11)); 强调：对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制，所以在创建表时，如果字段采用的是整型类型，完全无需指定显示宽度， 默认的显示宽度，足够显示完整当初存放的数据 严格模式补充 我们刚刚在上面设置了char,tinyint,存储数据时超过它们的最大存储长度，发现数据也能正常存储进去，只是mysql帮我们自动截取了最大长度。但在实际情况下，我们应该尽量减少数据库的操作，缓解数据库的压力，让它仅仅只管理数据即可，这样的情况下就需要设置安全模式 show variables like "%mode%"; # 查看数据库配置中变量名包含mode的配置参数 # 修改安全模式 set session # 只在当前操作界面有效 set global # 全局有效 set global sql_mode ='STRICT_TRANS_TABLES' # 修改完之后退出当前客户端重新登陆即可 浮点型 分类:FLOAT DOUBLE decimal 应用场景:身高，体重，薪资 字段限制特点(5,3)前一位表示所有的位数，后一位表示小数个数 三者最大整数位和小数位对比: # 存储限制 float(255,30) double(255,30) decimal(255,30) # 精确度验证 create table t9(x float(255,30)); create table t10(x double(255,30)); create table t11(x decimal(65,30)); insert into t9 values(1.111111111111111111111111111111); insert into t10 values(1.111111111111111111111111111111); insert into t11 values(1.111111111111111111111111111111); 字符类型 分类 char(定长) varchar(变长) 作用:姓名，地址，描述类信息 create table t10(name char(4)) # 超出四个字符报错，不够四个字符空格补全 create table t11(name varchar(4)) # 超出四个字符报错，不够四个有几个就存几个 # 验证存储限制 insert into t12 values('hello'); insert into t13 values('hello'); # 验证存储长度 insert into t12 values('a'); #'a ' insert into t13 values('a'); #'a' select * from t12 select * from t13 # 无法查看真正的结果 select char_length(name) from t12 select char_length(name) from t13 # 仍然无法查看到真正的结果 """首先应该肯定的是在硬盘上存的绝对是真正的数据，但显示的时候mysql会自动将末尾的空格取掉""" # 如果不想让mysql帮你做自动去除末尾空格的操作，需要再添加一个模式 set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; # 退出客户端重新登陆 select char_length(x) from t12; #4 select char_length(y) from t13; #1 # 针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动取掉末尾的空格 """ char与varchar的使用区别 """ name char(5) # 缺点：浪费空间 # 优点：存取速度都快 egon alex lxx jxx txx name varchar(5) # 缺点：存取速度慢 # 优点：节省空间 1bytes+egon 1bytes+alex 1bytes+lxx 1bytes+jxx 1bytes+txx 时间类型 分类 date：2019-05-01 time：11:11:11 Datetime: 2019-01-02 11:11:11 Year:2019 测试 create table student( id int, name char(16), born_year year, birth date, study_time time, reg_time datetime ); insert into student values(1,'egon','2019','2019-05-09','11:11:00','2019-11-11 11:11:11'); 枚举与集合类型 分类 枚举enum 多选一 集合set 多选多 测试 create table user( id int, name char(16), gender enum('male','female','others') ); insert into user values(1,'jason','xxx') # 报错 insert into user values(2,'egon','female') # 正确！ create table teacher( id int, name char(16), gender enum('male','female','others'), hobby set('read','sleep','sanna','dbj') ); insert into teacher values(1,'egon','male','read,sleep,dbj') # 集合也可以只存一个 约束条件 """ PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录 FOREIGN KEY (FK) 标识该字段为该表的外键 NOT NULL 标识该字段不能为空 UNIQUE KEY (UK) 标识该字段的值是唯一的 AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键） DEFAULT 为该字段设置默认值 UNSIGNED 无符号 ZEROFILL 使用0填充 """ not null+default create table user( id int, name char(16) ); insert into user values(1,null) # 可以修改 alter table user modify name char(16) not null; insert into user(name,id) values(null,2); # 报错 插入数据可以在表名后面指定插入数据对应的字段 create table student( id int, name char(16) not null, gender enum('male','female','others') default 'male' ) insert into student(id,name) values(1,'jason') # 成功 unique # 单列唯一 create table user1( id int unique, name char(16) ); insert into user1 values(1,'jason'),(1,'egon') # 报错 insert into user1 values(1,'jason'),(2,'egon') # 成功 # 联合唯一 create table server( id int, ip char(16), port int, unique(ip,port) ) insert into server values(1,'127.0.0.1',8080); insert into server values(2,'127.0.0.1',8080); # 报错 insert into server values(1,'127.0.0.1',8081); primary key+auto_increment # 单从约束角度来说primary key就等价于not null unique create table t11(id int primary key); desc t11; insert into t11 values(1),(1); # 报错 insert into t11 values(1),(2); # 除了约束之外，它还是innodb引擎组织数据的依据，提升查询效率 """ 强调: 1.一张表中必须有且只有一个主键，如果你没有设置主键，那么会从上到下搜索直到遇到一个非空且唯一的字段自动将其设置为主键 """ create table t12( id int, name char(16), age int not null unique, addr char(16) not null unique )engine=innodb; desc t12; """ 2.如果表里面没有指定任何的可以设置为主键的字段，那么innodb会采用自己默认的一个隐藏字段作为主键，隐藏意味着你在查询的时候无法根据这个主键字段加速查询了 索引：类似于书的目录，没有主键就相当于一页一页翻着查 3.一张表中通常都应该有一个id字段，并且通常将改id字段作成主键 """ # 联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键 create table t18( ip char(16), port int, primary key(ip,port) ); desc t18; # 主键id作为数据的编号，每次最好能自动递增 create table t13( id int primary key auto_increment, name char(16) ); insert into t13('jason'),('jason'),('jason'); # id字段自动从1开始递增 # 注意：auto_increment通常都是加在主键上，并且只能给设置为key的字段加 补充： delete from tb1; 强调：上面的这条命令确实可以将表里的所有记录都删掉，但不会将id重置为0， 所以收该条命令根本不是用来清空表的，delete是用来删除表中某一些符合条件的记录 delete from tb1 where id > 10; 如果要清空表，使用truncate tb1； 作用：将整张表重置,id重新从0开始记录
整型数据类型介绍图：
二、笔记部分
存储引擎 不同的数据应该有不同的处理机制 mysql存储引擎 Innodb:默认的存储引擎 查询速度较myisam慢 但是更安全 myisam:mysql老版本用的存储引擎 memory:内存引擎(数据全部存在内存中) blackhole:无论存什么 都立马消失(黑洞) 研究一下每个存储引擎存取数据的特点 show engines; 数据类型 整型 浮点型 字符类型 日期类型 枚举与集合类型 约束 not null unique default primary key ... 创建表的完整语法 create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); 注意: 1.字段名和字段类型是必须的 中括号内的参数都是可选参数 2.同一张表中字段名不能重复 3.最后一个字段后面不能加逗号 create table t6( id int, name char, ); 宽度: 使用数据库的准则:能尽量让它少干活就尽量少干活 对存储数据的限制 char(1) 只能存一个字符 如果超了 mysql会自动帮你截取 1.插入的时候 mysql自动截取 2.会直接报错(mysql严格模式) alter table t5 modify name char not null; not null该字段不能插空 类型和中括号内的约束 类型约束的是数据的存储类型 而约束是基于类型之上的额外限制 字段类型 整型 SMALLINT TINYINT INT BIGINT TINYINT 默认是否有符号 默认是带有符号的(-128,127) 超出限制会如何 超出之后只会存最大值或者最小值 create table t6(id TINYINT); not null 不能为空 unsigned 无正负符号 zerofill 0填充多余的位数 char后面的数字是用来限制存储数据的长度的 特例:只有整型后面的数字不是用来限制存储数据的长度 而是用来控制展示的数据的位数 int(8) 够/超8位有几位存几位,不够8位空格填充 修改约束条件 不够8位的情况下 用0填充 强调：**对于整型来说，数据类型后的宽度并不是存储限制， 而是显示限制，所以在创建表时， 如果字段采用的是整型类型，完全无需指定显示宽度， 默认的显示宽度，足够显示完整当初存放的数据 只要是整型 都不需要指定宽度 因为有默认的宽度 足够显示对应的数据 模糊匹配 like %匹配任意多个字符 _匹配任意一个字符 set session 临时有效 只在你当前操作的窗口有效 set global 全局有效 终生有效 set global sql_mode = 'STRICT_TRANS_TABLES'; 设置完之后 你只需要重新退出客户端再次进入即可 浮点型 float(255,30) 总共255位 小数部分占30位 double(255,30) 总共255位 小数部分占30位 decimal(65,30) 总共65位 小数部分占30位 create table t12(id FLOAT(255,30)); create table t13(id DOUBLE(255,30)); create table t14(id DECIMAL(65,30)); insert into t12 values(1.111111111111111111111111111111); insert into t13 values(1.111111111111111111111111111111); insert into t14 values(1.111111111111111111111111111111); 精确度 float < double < decimal 字符类型 char(4) # 最大只能存四个字符 超出来会直接报错 如果少了 会自动用空格填充 varchar(4) # 最大只能存四个字符 超出来会直接报错 如果少了 有几个存几个 create table t15(name char(4)); create table t16(name varchar(4)); char_length() mysql在存储char类型字段的时候 硬盘上确确实实存的是固定长度的数据 但是再取出来的那一瞬间 mysql会自动将填充的空格去除 可以通过严格模式 来修改该机制 让其不做自动去除处理 char与varchar的区别 char定长 1.浪费空间 2.存取速度快 varchar变长 1.节省空间 2.存取速度慢(较于char比较慢) 存的时候 需要给数据讲一个记录长度的报头 取的时候 需要先读取报头才能读取真实数据 char(4) varchar(4) 取的时候方便 取的时候比较繁琐了 无法知道数据到底多长 直接按固定的长度取即可 日期类型 date datetime year time 枚举与集合类型 枚举(enum) 限制某个字段能够存储的数据内容 集合(set) 限制某个字段能够存储的数据内容 约束条件 not null 不能为空 default 给某个字段设置默认值(当用户写了的时候用用户的,当用户没有写就用默认值) create table t17(id int,name char(16) default 'jason'); 往表中插入数据的时候 可以指定字段进行插入 不需要全部都插 insert into t17(name,id) values('egon',2); unique 唯一 单列唯一 限制某一个字段是唯一的 联合唯一(在语句的最后 用括号的形式 表示哪几个字段组合的结果是唯一的) ip port create table server( id int, ip char(16), port int, unique(ip,port) ) primary key 主键 限制效果跟 not null + unique 组合效果一致 非空且唯一 create table t18(id int primary key); primary key也是innodb引擎查询必备的索引 索引你就把当成书的目录 innodb引擎在创建表的时候 必须要有一个主键 当你没有指定主键的时候 1.会将非空切唯一的字段自动升级成主键 2.当你的表中没有任何的约束条件 innodb会采用自己的内部默认的一个主键字段 该主键字段你在查询时候是无法使用的 查询数据的速度就会很慢 类似于一页一页的翻书 create table t19( id int, name char(16), age int not null unique, addr char(16) not null unique ); 主键字段到底设置给谁呢??? 通常每张表里面都应该有一个id字段 并且应该将id设置为表的主键字段 联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键!!! !!!!!!!!!!!!!!!!!ps:innodb引擎中一张表有且只有一个主键!!!!!!!!!!!!!!!!!!!!!!! create table t20( ip char(16), port int, primary key(ip,port) ); desc t20; 主键字段应该具备自动递增的特点 每次添加数据 不需要用户手动输入 auto_increment 自动递增 create table t21(id int primary key auto_increment,name varchar(16)); create table t22(id int primary key,name varchar(16)); delete from 仅仅是删除数据 不会重置主键 truncate 初始化表 会重置主键
三、理解记忆重点（待整理）
存储引擎
''' 存储引擎 不同的数据应该有不同的处理机制 mysql存储引擎 Innodb:默认的存储引擎 查询速度较myisam慢 但是更安全 myisam:mysql老版本用的存储引擎 memory:内存引擎(数据全部存在内存中) blackhole:无论存什么 都立马消失(黑洞) 研究一下每个存储引擎存取数据的特点 show engines; '''
图示：
四、最后总结（思路）
''' 存储引擎 Innodb 目前5.1之后MySQL版本默认的存储引擎 支持事务,行锁,外键 由于上面的支持 数据更安全 建表的时候innodb会产生两个文件 一个是表结构文件 一个是存储数据文件 MyIsam 5.1版本之前的MySQL的默认存储引擎 查询速度较于Innodb要快 建表的时候会产生三个文件 一个是表结构文件 一个是索引文件 索引你先把它理解成是书的目录,能够帮助你更快的查询数据 一个是存储数据文件 memory 将数据存放于内存中 建表的时候都仅仅只有一个表结构文件 blackhole 任何写入的数据都会消失 建表的时候都仅仅只有一个表结构文件 创建表的完整语法 create table 表名( 字段名1 字段类型[(宽度) 约束条件], 字段名2 字段类型[(宽度) 约束条件], 字段名3 字段类型[(宽度) 约束条件], 字段名4 字段类型[(宽度) 约束条件] ); 注意: 1.同一张表内字段名不能重复 2.字段名和字段类型都是必须的 中括号内的都是可选的 3.最后一个字段语句后不能有逗号 宽度 限制数据 字段类型与约束条件 字段类型约束的存储数据的类型 约束条件是基于字段类型之上的额外约束 1.字段类型 整型 smallint tinyint int bigint 默认都是带正负号 tinyint 1bytes int 4bytes int(8):数字不够8位 默认用空格填充,够8位或者8位以上,有多少位显示多少位,但是也不能超出int最大范围 注意:只有整型比较特殊,括号内的数字不是用来限制存储宽度,而是用来 限制显示宽度 在定义整型字段的时候不需要指定宽度,默认就是展示最大宽度 zerofill 用0填充不足的位数 浮点型 float(255,30) 总共255位小数位占30位 double(255,30) 总共255位小数位占30位 decimal(65,30) 总共65位小数位占30位 精确度 float < double < decimal 通常情况下会将数字在数据库存储上变成字符串来方便存储 不需要考虑精确度带来的问题 字符类型 char(4) 定长:最大存储四个字符,超出报错,不足默认用空格占位 varchar(4) 变长:最大存储四个字符,超出报错,不足有几个存几个 char Vs varchar char(django中如果你想用char需要你自己定义) 存取速度快 浪费硬盘空间 varchar(django默认只有varchar类型) 存取速度慢(相对于char确实慢了一点点) 节省硬盘空间 日期类型 date datetime year time 枚举与集合类型 枚举:多选一(男女性别,婚否...) 集合:多选多但是也可以多选一(爱好...) 如果你写的信息不在选择范围内会直接报错 2.约束条件 not null 非空 default 默认值 gender enum('male','female','others') default 'male' unique 唯一:不能出现重复 单列唯一:在同一张表中某个字段的信息不能重复 id int unique 联合唯一 ip int port int unique(ip,port) primary key:主键 限制侧面上 就等价于 not null + unique 非空且唯一 Innodb存储引擎在建表的时候 要求表必须有且只有一个主键 1.当你没有设置主键的时候 会自上往下寻找非空且唯一的约束字段自动将其升级为主键字段 2.当你的表中没有任何约束字段的时候 Innodb会使用内部隐藏一个主键字段 无法利用该主键字段加快查询 通常情况下 每张表都必须有一个能够唯一标识数据的编号字段 id 这个id字段一般也是主键字段 auto_increment 自动递增 该约束条件只能加载被设置成key的字段上,不能单独使用 通常都是跟primary key联合使用 create table t30(id int auto_increment); 主键设置成自增之后 id永远是增加 不会因为你数据的删除而重置 如果你想重置id 只能将表清空 truncate 表名 '''