您的位置: 首页 > IT文章 > 表操作

表操作

分类: IT文章 • 2024-01-03 21:35:06

1 数据类型

　　整形

　　浮点型

　　字符类型（char varchar）

　　日期类型

　　枚举与集合

2 约束条件

　　primary key

　　unique key

　　not null

　　foreign key

创建表的完整语法

　　语法：

　　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 colunm 'name' at row 1
2 约束条件初识》》》 null 与 not 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 各种号码等

验证整形字段有无符号及范围
    create table t1(x tinyint); 　　#默认有符号
    insert into t1 values (128),(-129);    #严格模式会保存 添加不进去
    
    create table t2 （x tinyint unsigned）;　　#unsigned去符号的
    insert into t2 values (-1),(256);
    
    create table t3(x int unsigned);
    insert into t3 values (4194967296);

强调：对于整形来说，数据类型后的宽度不是存储限制，而是显示限制，所以在创建表时，如果字段采用的是整形类型，完全无需指定显示宽度，默认的显示宽度，足够显示完整当初存放的数据
整形类型，其实没有必要指定显示宽度，使用默认的就ok

int 默认有符号 4个字节
bigint 默认有符号 8个字节

用 zerofill测试整数类型的显示宽度
　　create table t7(x int(3) zerofill);
　　insert into t7 values

 　　-> (1),
    -> (11),
    -> (111),
    -> (1111);
MariaDB [db1]> select * from t7;
+------+
| x    |
+------+
|  001 |
|  011 |
|  111 |
| 1111 | #超过宽度限制仍然可以存
+------+

严格模式补充

我们刚刚在上面设置了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(255,30));

insert into t9 values (1.1111111111111);
insert into t10 values(1.111111111111111111);
insert into t11 vlues(1.1111111111111111111);

字符类型

分类
    char(定长）
    carchar(变长）
作用：姓名 地址 描述类信息

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

#验证存储限制
insert into t10 values('hello');
insert into t11 values('hello');
#验证存储长度
insert into t10 values('a'); #'a'
insert into t11 values('a')；#'a'

select * from t10
select * from t11    #无法查看真正的结果

select char_length(name) from t10 
select char_length(name) from t11  #任然无法查看真正的结果

'''首先应该肯定的是在硬盘上存的绝对是真正的数据，但显示的时候mysql会自动将末尾的空格去掉''''
#如果不想让 mysql帮你做自动去除末尾空格的操作，需要再添加一个模式

set global sql_mode ='strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH';
#退出客户端重新登录
select char_length(x) from t10 ; #4
select char_length(x) from t11; #1

#针对char类型 mysql 在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动去掉末尾的空格

char 与var char 的使用区别

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_tim 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) 标识该字段为该表的主键，可以唯一的标识记录

FOREING KEY (FK) 标识该字段为该表的外键

NOT NULL 标识该字段不能为空

UNIQUE KEY (UK) 标识该字段的值是唯一的

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

DEFAULT 为该字段设置默认值

UNSIGEND 无符号

ZEROFILL 使用0填充

not null +detault

create table user(id int , name char(16));
insert into user values(1,null); #可以修改

later 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 valules(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;　　//desc 查看表结构
insert into t11 values(1),(1); #报错
insert into t11 vallues(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(name) values('jason'),('jason'),('jason');  #id 字段自动从1 开始递增
    #注意：auto_increment通常都是加在主键上 并且只能给设置为key的字段加

补充：

delete from tb1;
强调：上面的这条命令确实可以将表里的所有记录都删掉，但不会讲id重置为0，所以收该命令根本不是用来清空表的，delete 是用来删除表中某一些符合条件的记录
delete from tb1 where id >10;

如果要清空表，使用truncate tb1;
作用：将整张表重置 ，id重新从0 开始记录

表操作

相关推荐