数据库之表操作

一、创建表的完整语法

create table(

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

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

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

);

★注意：

1.在统一张表中，字段名不能相同

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

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

Ⅰ.宽度指的是对存储数据的限制

create table userinfo(name char);

insert into userinfo values('jason');

1.没有安全模式的数据库版本，能够存放数据但是只会存进去一个j

2.最新数据库版本直接报错提示无法存储：Data too long for column 'name' at row 1（严格模式）

Ⅱ.约束条件的基本使用

null 与 not null

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

insert into t1 values(1,'j');　　#正常存储

insert into t1 values(2,null);　　#报错

★★ 总结：类型与约束条件区别

类型：限制字段必须以什么样的数据类型存储

约束条件：约束条件是在类型之外添加一种额外的限制

二、基本数据类型

1、整型

分类：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

作用：存储年龄，等级，id，各种号码等

类型存储范围，如下图：

验证整型字段有无符号及范围：

create table t1(x tinyint);

insert into t1 values(128),(-129);

 查询到的结果是，插入了127，-128，那么证明了是默认有符号的

create table t2(x tinyint unsigned); #使用无符号

insert into t2 values(-1),(256)

 查询到的结果是，插入了0,255

 

那么提出疑问：类型后面的宽度能否改变字段存储的大小限制呢？

我们来测试一下：

create table t4(x int(8));

insert into t4 values(4686516535684);

得出结论：显示时，不够8位就用空格填充，入过超出8位就正常显示

我们可以让它显示时，不够8位用0填充：

create table t5(x int(8) unsignde zerofill);

insert into t5 values(3)

★★ 强调：对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制，所以在创建表时，如果字段采用的是整型类型，完全无需指定显示宽度，默认的显示宽度足够完整存放数据

补充：严格强调模式

我们在刚刚上面设置了char，tinyint，存储数据时超过他们的最大存储长度，发现数据也能正常存储进去，只是mysql帮我们自动截取了最大长度。但在实际情况下，我们应该尽量减少数据库的操作，缓解数据库的压力，让它仅仅只管理数据即可，这样情况下就需要设置安全模式

#修改安全模式
set session # 只在当前操作界面有效
ser global #全局有效

set global sql_mode = 'STRICT_TRANS_TABLES'
#修改完之后推出当前客户端重新登录即可

2.浮点型

分类：float、double、decima

应用场景：身高，体重，薪资

字段特点特点(5,3)前一位表示所有的位数，后一位表示小数个数

三者最大整数位和小数位对比：

#存储限制
float(255,30)
double(255,30)
decimal(65,30)

create table t7(x float(255,30))
create table t8(x float(255,30))
create table t9(x float(255,30))

精准度：

float < double < decimal

3.字符类型

分类：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';
'''

#退出客户端重新登录

char与varchar的使用区别

1.name char(5)

缺点：浪费空间

优点：存取速度快

‘egon ’ ‘jerry’ ‘alex ’ ‘zzj  ’ ‘b    ’

2.name varchar(5)

缺点：存取速度慢

优点：节省空间

1bytes+egon 1bytes+jerry 1bytes+alex

4.时间类型

分类：data：2019-08-19

   time：11:11:11

   Datatime：2019-08-19 11:11:11

   Year：2019

 

create table student(
id int,
name char(16),
born_year year,
birth_time time,
reg_time datatime
);
insert into student values(1,'jason','2019','2019-08-19','11:11:11','2018-11-11 11:11:11');

 

5.枚举与集合类型

分类：枚举enun 多选一

　　    集合set 多选多 也可以选单个

create table user(
id int,
name char(16),
gender enum('male','female','others')
);
insert into user values(1,'jsaon','gay') #报错
insert into user values(2,'tank','female') #正确

create table teacher(
id int,
name char(16),
gender enum('male','female','other')
hobby set('read','sleep','sing','work')
);
insert into teacher values(1,'egon','male','read,work');

6.约束条件

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,'jsaon'),(1,'egon') #报错
insert into user1 values(1,'jsaon'),(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 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.联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键

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

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

create table t13(
id int primary key auto_increment,
name char(16)
);
insert into t13('jason'),('jerry'),('tank'); #id字段自动从1开始递增

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

补充：

delete from tb1;

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

delete from tb1 where id > 10; #删除多条

如果要清空表，使用truncate tb1;

作用：将整张表重置，id重新从0开始记录