MySQL >>> 表的操作一

表的操作

创建表的完整语法：

　　　create table 表名(

　　　 字段名1 字段类型[(宽度) 约束条件]，

　　　 字段名1 字段类型[(宽度) 约束条件]，

　　　 字段名1 字段类型[(宽度) 约束条件]

　　　　　　　　　　　  );

　　　　　　　注：1. 同一张表中，字段名不能相同

　　　　　　　　　     2. 宽度和约束条件是可选的，字段名和字段类型是必须的

　　　　　　　　　 　3. 最后一个字段后面不能加逗号

　　　　　　#######################################

　　　　　　补充：

　　　　　　　　　　宽度是对存储数据库的限制；如

　　　　　　　　　　　　　　create table t1(name char);    # char 后面不写宽度默认为 1

　　　　　　　　　　　　　　insert into t1 values('jason');

　　　　　　　　　　　　　　select * from t1;

　　　　　　　　　　　　　　结果如下：

　　　　　　　　　　　　　　　　　　　　

　　　　　　　　　　　　　　　　在没有 严格模式 的情况下，数据的确能够存放进去，但是只会存进去一个 j 

　　　　　　　　　　　　　　　　　　  而最新的数据库版本直接报错：Data too long for column 'name' at row 1

　　　　　　　　字段类型与约束条件：
  　　　　　　　　　　　　字段类型约束的存储数据的类型
 　　　　　　　　　　　　        约束条件是基于字段类型之上的额外约束

 　　　　　　　　###########################################

1. 数据(字段)类型

　　

　　建表的时候，字段都有对应的数据类型；如下图：

　　　　

 　　上图只是整型与浮点型，实际上还有其他的数据类型；接下来一一展示；

 

1.1 整型

　　

　　整型可分为 tinyint 、smallint 、mediumint 、int 、bigint    默认都是带正负号

　　我们可以使用整型来存储年龄、等级、ID等各种数字相关的

 　　　　　　ps：对于整型来说，数据类型后的宽度 并不是存储限制，而是显示限制；

　　 　　　　　　  所以在创建表时，如果字段采用的是整型类型，完全无需指定显示宽度；

　　 　　　　　　  默认就是展示最大宽度，足够显示完整当初存放的数据

　　　　　　　　   如：int(8)：数字不够 8 位默认用空格填充，够8位或者8位以上，有多少位显示多少位；但是也不能超出 int 最大范围

　　　　　　　　

1.2 浮点型

　　

　　浮点型可分为 float 、double 、decimal 

　　我们可以使用浮点型来表示 身高、体重、薪资等

　　float(255,30)           总共255位小数位占30位       精度上一般够用了
       double(255,30)       总共255位小数位占30位
       decimal(65,30)        总共65位小数位占30位
   
  　　 　　精确度： float < double < decimal

　　　　   通常情况下会将数字在数据库存储上变成 字符串 来方便存储,不需要考虑精确度带来的问题

 

 1.3 字符类型

　　

　　字符类型主要有 char（定长） 、varchar（变长）

　　用来表示 姓名、地址 等描述性信息

　　create table 表1(name char(4))    # 超出四个字符报错，不够四个字符空格补全
　　create table 表2(name varchar(4))    # 超出四个字符报错，不够四个有几个就存几个 

　　　　　　#########################################

　　　　　　在查看存储的数据的时候我们无法确定是否是由空格补齐，因为在显示的时候 mysql 会自动将末尾的空格取掉

　　　　　　如果不想让 mysql 帮你做自动去除末尾空格的操作，需要再添加一个模式：

　　　　　　　　　　set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
　　　　　　　   　　退出客户端重新登陆

　　　　　　###########################################

 

　　char 与 varchar 的区别：*************

　　　　name char(5)      django中如果你想用char需要你自己定义
　　　　　　  缺点：浪费空间
 　　　　　    优点：存取速度都快
                       格式：egon alex lxx  jxx  txx      # 5个位一组存储

　　　　name varchar(5)     django默认只有varchar类型

　　　　　　 缺点：存取速度慢     相较 char 而言
 　　　　　　优点：节省空间
　　　　　　 格式：1bytes+egon 1bytes+alex 1bytes+lxx  1bytes+jxx  1bytes+txx    # 1个报头再加上实际数据

 

 

1.4 时间类型

　　

　　　　主要有以下四种：

　　　　datatime：年月日 时分秒

 　　　  date：年月日

　　　　time：时分秒

　　　　year：年

 

　　　　如创建表 及 插入数据的时候：

　　　　　　　　 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');

 

 

1.5 枚举与集合类型

　　

　　关键字：

　　　　枚举 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')    # 集合也可以只存一个

 

 

2. 约束条件

 

2.1  not null 

　　

　　标识该字段不能为空

　　如：create table t1(id int, name char(4) not null);

　　　　insert into t1 values(1, null);   # 会报错，表示不能插入为空

 

2.2 unsigned

　　

　　无负号，用在整型数据类型

　　如：create table t1(x tinyint);
　　　　insert into t1 values(128),(-129);  

 

　　　　　　可以插入负数，但是超出范围只会显示范围内最大数值

 

　　　　　　

 

　　　　create table t2(x tinyint unsigned);
　　　　insert into t2 values(-1),(256);

 

　　　　　　负数不能插入，直接从0开始；并且超出的数值只会存储为该整型上限

 

　　　　　　

 

 2.3  zerofill

　　

　　使用 0 填充

　　应用：create table t4(x int(8));
　　　　　insert into t4 values(4294967296123);

　　　　　　　

 

　　　　　create table t5(x int(8) unsigned zerofill);
　　　　　insert into t5 values(4294967296123);

　　　　　　　　显示时，不够8位用0填充，如果超出8位则正常显示

　　　　　　　

 

 　　　　　create table t6(x int(8) unsigned zerofill);
　　　　　 insert into t6 values(333);

　　　　　　　　显示时，不够8位用0填充，如果超出8位则正常显示

 　　　　　　　

 

2.4 default 

　　

　　默认值

　　应用：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');   # 成功

 　　　　　　　　

 

2.5 unique (key)  key可不写

　　

　　标识该字段的值是唯一的

　　单列唯一：create table user1(
 　　　　　　　　　　　　　　　　id int unique,    # 限制 id 是唯一的，不能重复
  　　　　　　　　　　　　　　　　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)   # 限制 ip + port 组合在一起时唯一的
　　　　　　　　　　　　　　　　);
　　　　　　　insert into server values(1,'127.0.0.1',8080);  # 第一次正常插入数据
　　　　　　　insert into server values(2,'127.0.0.1',8080);  # 报错 ip + port 与第一条数据重复
　　　　　　　insert into server values(1,'127.0.0.1',8081);  # 正常插入数据

 

 2.6 primary key

　　

　　标识该字段为该表的主键，可以 唯一 的标识记录

 　  单从约束角度来说 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. 当你没有设置主键的时候，会自上往下寻找非空且唯一的约束字段自动将其升级为主键字段
    　　　　　　2. 当你的表中没有任何约束字段的时候， Innodb会使用内部隐藏一个主键字段；但无法利用该主键字段加快查询
   　　　　　　　　通常情况下 每张表都必须有一个能够唯一标识数据的编号字段
  　　　　　　　　 id 这个id字段一般也是主键字段

　　　　#########################################

 　　

　　联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键

　　如：create table t18(
 　　　　　　　　　　　　ip char(16),
  　　　　　　　　　　　   port int,
 　　　　　　　　　　　    primary key(ip,port)
　　　　　　　　　　　);
　　　　desc t18;

 

　　　　　　

 

2.7 auto_increment

　　

　　标识该字段的值自动增长（整数类型，而且为主键）

　　应用：主键 id 作为数据的编号，每次最好能自动递增

 　　　　create table t13(
 　　　　　　　　　　　　id int primary key auto_increment,
  　　　　　　　　　　　　name char(16)
　　　　　　　　　　　　);
　　　　insert into t13(name) values('jason'),('jason'),('jason');  

 

　　　　　　　 id字段自动从1开始递增

 

　　　　　　　　

　　　　注意：auto_increment 通常都是加在主键上，并且只能给设置为 key 的字段加

 　　　　　　　通常都是跟primary key联合使用   create table t30(id int primary key auto_increment);

　　　　　　　

　　　　　　　主键设置成自增之后 id永远是增加 不会因为你数据的删除而重置
 　　　　　　  如果你想重置id，只能将表清空     --->   truncate 表名