表的相关操作

表的相关操作

一. 表介绍

表就相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段.类似于excel表中第一行的标题

id	name	age	gender
1	jkey	18	male
2	liu	28	female
3	song	38	male
4	auto	44	male

其中 id,name,qq,age 称为字段名,其余的为数据,一行数据就是一条记录

二. 创建表的完整语法

2.1 完整语法

create table 表名(
	字段1 类型(宽度) 约束条件,
    字段2 类型(宽度) 约束条件,
    字段3 类型(宽度) 约束条件,
    ...
);

注意:三点

1. 同一张表中字段名不能重复
2. 宽度和约束条件是可选的,而字段名和字段类型是必须要写的.
	约束条件也可以写多个: 字段名1 类型(宽度) 约束条件1 约束条件2...
	create table t1(id);  # 报错
3. 最后一行指令不能有逗号
create table t1(
	id int,
	name char,  # 报错 最后一个字段后面不能有逗号
);

2.2 补充小知识点

- 宽度 -
注意: 一般情况下指的是对存储数据的限制
1. char不指定 宽度, 默认宽度为1
create table t1(name char);
insert into t1 values('jkey');

2. 关键字NULL 没有宽度的限制.
insert into t1 values(null);

针对不同的版本会出现不同的效果
	5.6 版本 默认没有开启严格模式, 规定只能存一个字符,如果你给了超过1个字符,那么会自动帮你截取.
	5.7 版本及以上默认开启了严格模式,规定存几个,就只能存这规定内的个数,超出了就报错(Data too long for column 'name' at row 1.)

- 那严格模式要不要开呢?? -
mysql 5.7 版本以上的默认开启严格模式
使用数据库的准则:
	能尽量少的让数据库干活就尽量少,不要给数据库增加额外的压力.
	当没有开启严格模式,遇到宽度超出时,会进行额外的截取操作,而这种操作本来数据库是不用做的,这就会导致给数据库增加了额外的压力.

- 宽度和约束条件到底是什么关系? -
	宽度是用来限制数据的存储
	约束条件是在宽度的基础上增加的额外的约束.

三. 数据库模式

3.1 模式种类

• ONLY_FULL_GROUP_BY:

对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中

• NO_AUTO_VALUE_ON_ZERO:

该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

• TRICT_TRANS_TABLES:

在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制

• NO_ZERO_IN_DATE:

在严格模式下,不允许日期和月份为零

• NO_ZERO_DATE:

设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。

• ERROR_FOR_DIVISION_BY_ZERO:

在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL

• NO_AUTO_CREATE_USER:

禁止GRANT创建密码为空的用户

• NO_ENGINE_SUBSTITUTION:

如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

• PIPES_AS_CONCAT:

将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似

• ANSI_QUOTES:

启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

将sql_mode设置为严格模式

• set global sql_mode="strict_trans_tables";

查看当前客户端的sql_mode模式;

• select @@sql_mode;

• 注意:严格模式和普通模式的区别.

  • 严格模式超出范围就会报错,而普通模式则取到范围的最大值,或者最小值

ps:修改sql_mode为严格模式,必须重启客户端才能生效

# 了解3种模式
"""
普通模式: no_engine_substitution
严格模式: strict_trans_tables
取消剔除char空格模式: pad_char_to_full_length

注意: 模式的的修该方式是替换.

查看模式2种方式:
show variable like '%mode';
select @@sql_mode;

修改模式的2种方式:
set session sql_mode=模式;
set global sql_mode=模式;

sql_mode介绍更多信息: https://www.cnblogs.com/ryxiong-blog/articles/10910349.html

四. 数据类型

1. 介绍

存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的.

2. 数值类型

2.1 整型

medium /ˈmiːdiəm/ 中等

分类: tinyint(1字节) smallint(2字节) mediumint(3字节) int或integer(4字节) bigint(8字节)

作用: 存储年龄、等级、id、号码等等

默认显示宽度: 11

默认符号类型: 有符号

Copy注意!!!: 只有整型括号里面的数字不是表示限制位数. 
"""
前提: 非严格模式下
如果数字没有超出括号内指定的长度, 那么默认用空格填充至括号内的长度.
如果数字超出括号内指定的长度, 那么有几位就存几位.(但是还是要遵守最大范围.)
"""

举例: int的存储宽度是4个Bytes，即32个bit，即2**32
    无符号最大值为：4294967296-1
    有符号最大值：2147483648-1

    有符号和无符号的最大数字需要的显示宽度均为10，而针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的.
    
# 无符号约束条件指定(默认不指定有符号)
unsigned  /ʌn'saɪnd/ 无符号
create table 表名(id 类型(宽度) unsigned);

# 用0填充宽度(默认不指定用空格填充)
create table 表名(id 类型(宽度) zerofill);

验证:

Copy# ============ tinyint(1字节): 2 ** 8 = 256 ============ 
# 无符号: 0 ~ 255
drop table t1;
create table t1(id tinyint(2) unsigned);
insert into t1 values(-1),(0),(255),(256);
select * from t1;


# 有符号: -128 ~ 127
drop table t1;
create table t1(id tinyint(2));
insert into t1 values(-129),(-128),(127),(128);
select * from t1;



# ============ smallint(2字节): 2 ** 16 = 65536 ============ 
# 无符号: 0 ~ 65535
drop table t1;
create table t1(id smallint(2) unsigned);
insert into t1 values(-1),(0),(65535),(65536);
select * from t1;
 
# 有符号: -32768 ~ 32767
drop table t1;
create table t1(id smallint(2));
insert into t1 values(-32769),(-32768),(32767),(32768);
select * from t1;



# ============ mediumint(3字节): 2 ** 24 = 16777216 ============ 
# 无符号: 0 ~ 16777215
drop table t1;
create table t1(id mediumint(2) unsigned);
insert into t1 values(-1),(0),(16777215),(16777216);
select * from t1;

# 有符号: -8388608 ~ 8388607
drop table t1;
create table t1(id mediumint(2));
insert into t1 values(-8388609),(-8388608),(8388607),(8388608);
select * from t1;



# ============ int或integer(4字节): 2 ** 32 = 4294967296 ============ 
# 无符号: 0 ~ 4294967295
drop table t1;
create table t1(id int(6) unsigned);
insert into t1 values(-1),(0),(4294967295),(4294967296);
select * from t1;

# 有符号: -2147483648 ~ 2147483647
drop table t1;
create table t1(id int(6));
insert into t1 values(-2147483649),(-2147483648),(2147483647),(2147483648);
select * from t1;



# ============ bigint(8字节): 2 ** 64 = 18446744073709551616 ============ 
# 无符号: 0 ~ 18446744073709551615
drop table t1;
create table t1(id bigint(6) unsigned);
insert into t1 values(-1),(0),(18446744073709551615),(18446744073709551616);
select * from t1;

# 有符号: -9223372036854775808 ~ 9223372036854775807
drop table t1;
create table t1(id bigint(6));
insert into t1 values(-9223372036854775809),(-9223372036854775808),(9223372036854775807),(9223372036854775808);
select * from t1;

# ============ 用zerofill测试整数类型的显示宽度 ============ 
# 无符号: 0 ~ 4294967295 
# 有符号: -2147483648 ~ 2147483647
# 以上都是10位数
drop table t1;
create table t1(id int(6) zerofill);
insert into t1 values(-12345),(12345),(123456),(9223372036854775808);
select * from t1;


# 总结: 针对整型字段 括号内无需指定宽度 因为它默认的宽度以及足够显示所有的数据了#

2.2 浮点型

medium /ˈmiːdiəm/ 中等

分类: float double decimel

作用: 存储身高、体重、薪资等等

Copy# 存储限制
'''
255类型: 代表所有的数总共的位数是255位, 小数位占到了30位
65类型: 代表所有的位数总共是65位, 小数位占30位
'''
float(255,30)   # 总共255位 小数部分占30位
double(255,30)  # 总共255位 小数部分占30位
decimal(65,30)  # 总共65位 小数部分占30位

# 精度: float < double < decimal

精确度验证:

Copy# =============== float(255, 30) ===============
drop table t1;
create table t1(id float(255, 30));
insert into t1 values(1.012345678901234567890123456789);
select * from t1;
des 

# =============== double(255, 30) ===============
drop table t1;
create table t1(id double(255, 30));
insert into t1 values(1.012345678901234567890123456789);
select * from t1;


# =============== decimal(65, 30) ===============
drop table t1;
create table t1(id decimal(65, 30));
insert into t1 values(1.012345678901234567890123456789);
select * from t1;

3. 字符串类型

3.1 char和varchar介绍

char默认没有指定宽度默认为： 1

varchar没有默认必须指定指定宽度.

Copy"""
char: 定长
	char(4)	 数据超过四个字符直接报错 不够四个字符空格补全
	
varchar: 变长
	varchar(4)  数据超过四个字符直接报错 不够几个存几个
"""
# 修改sql_mode=pad_char_to_full_length让MySQL不要做自动剔除操作. 使用char_length就可以统计没有被剔除的char字段长度.
select char_length(字段) from 表名;


set session sql_mode='no_engine_substitution,pad_char_to_full_length';
# ================ char ================
drop table t1;
create table t1(name char(4));
insert into t1 values('a');
select * from t1;
select char_length(name) from t1;     # 输出4(1个字符 + 3个空格)

# ================ varchar ================
drop table t1;
create table t1(name varchar(4));
insert into t1 values('a');
select * from t1;
select char_length(name) from t1;    # 输出1(1个字符)

'''
首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的. 只是mysql在查寻时的时候默认会将右边的空格会默认去掉.
'''

3.2 char类型和varchar类型比较

Copy# char类型：定长，简单粗暴，浪费空间，存取速度快
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
存储：
    存储char类型的值时，会往右填充空格来满足长度
    例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储

检索：
    在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）
    
空间节省情况:
    当存得时候, 字符数超过了或者刚刚等于指定得宽度 , char类型更加节省空间.(提示: 在非严格模式下)        


# varchar类型：变长，精准，节省空间，存取速度慢
字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
存储：
    varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来
    强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
    如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
    如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）

检索：
    尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
    
空间节省情况: 
    当存得时候, 字符数小于指定得宽度varchar类型, 更加节省空间, 但是大多数情况下varchar更加节省空间.
     使用varchar得真正的地方: 基于网络IO得情况, varchar更好. 
        因为mysql客户端提交命令通过网络到达mysqld服务端执行这条命令, 虽然char查询速度比varchar在一定程度上要快, 但是它们都基于网络IO. 
        举个例子: 
            char查询需要1秒    网络IO5秒 
            varchar查询需要2秒 网络IO4秒 
            这时char查询在这种情况下单纯的说char的查询速度块, 似乎和通过varchar查询数据反馈给客户端并没有任何的差别. 一共都是6秒.
            
            
# 到底如何取舍可以根据一下几个方面考虑:
1、对于MyISAM表,尽量使用Char: 
    对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间;
2、对于InnoDB表,尽量使用varchar: 
    因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分(所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针)，所以使用char类型不见得会比使用varchar类型好。事实上,因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利;
3、存储很短的信息，尽量使用Char: 
    比如门牌号码101, 20....这样很短的信息应该用char,因为varchar还要 占个byte用于存储信息长度，本来打算节约存储的现在得不偿失。
4、固定长度的数据, 尽量使用Char: 
    比如使用uuid作为主键,那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。
5、十分频繁改变的字段列, 尽量使用varchar:
    因为varchar每次存储都要 有额外的计算,得到长度等工作,如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。       

3.3 模拟char(5) 和 varchar(5) 在存储方面得不同

Copychar(5): egon |axx  |lxx  |fm   |

varchar(5): 1-2bytes+egon|1-2bytes+axx|1-2bytes+lxx|1-2bytes+fm|

4. 日期类型

4.1 日期类型的基本用法介绍

datetime/timestamp: 年月日时分秒 2020-5-4 11:11:11

date: 年月日 2020-5-4

time: 时分秒11:11:11

Year: 2020

Copy# 时间的3种插入格式:
'''
1. 使用now()函数截取当前时间
2. 使用纯数字紧挨顺序写入 --> 080808
3. 使用自定义格式插值 --> 要用双引号引起来'08:08:08'
'''

drop table student;
create table student(
    id int,
    name varchar(16),
    born_year year,
    brith date,
    study_time time,
    reg_time datetime
);
insert into student values(1, 'egon', '2020', '1998-07-15', '08:08:08', '2020-11-11 11:11:11');
insert into student values(2, 'tank', 2022, 19980707, 080808, now());
select * from student;

4.2 datetime字段 与 timestamp字段区别

详细解析网址: https://www.cnblogs.com/linhaifeng/articles/7233411.html#_label2

Copy# 区别
"""
<1> timestamp字段 比  datetime字段 的空间利用率更高, 存取速度快(最关键一点)
<2> timestamp字段 比 datetime字段 建表的时候更方便一点
<3> timestamp字段不能为空, 默认指定了当前时间, 不插值默认获取当前时间
<4> timestamp缺点, 时间上可能不够用
"""
    
# 使用timestamp字段同时也实现datetime字段的默认功能
drop table t1;
create table t1(x timestamp not null default now());
insert into t1 values();
select * from t1;


# datetime字段 和 timestamp字段的应用场景
'''
用户注册, 提交作业任务等等
'''

5. 枚举类型与集合类型

Copy"""
枚举enum(‘a’, ‘b’, ‘c’) : 多选一
    enum --> enumerate
集合set(‘a’, ‘b’, ‘c’)  : 可选多, 可选单    
"""
Copydrop table user;
create table user(
    id int,
    name varchar(16),
    sex enum('male', 'female'),
    hobbies set('read', 'play', 'jump')
);
insert into user values
    (1, 'egon', 'male', 'read'),
    (2, 'tank', 'female', 'play, jump'); # 注意: 集合类型中只能由逗号格开.
select * from user;    

6. 总结

Copy# 类型的概念是创建表时对字段的限制与归类.

# 数值类型: 非严格模式下
"""
整型: tinyint(1字节), smallint(2字节), mediumint(3字节), int or integer(4字节), bigint(8字节)
    <1> 宽度的指定并不会限制数字类型的显示, 限制数字类型显示是遵循最大范围. 所以说整型不需要指定宽度, 没有意义, 因为默认宽度就会包容当前遵循的最大范围. 
    <2> 在范围大于宽度的指定时, 就会以该范围显示.在范围小于宽度的指定时, 默认会以空格补全.
    <3> 约束条件: unsigned zerofull
	
浮点型: float(255, 30), double(255, 30), decimal(65, 30)
    <1> 前面的指定的时一共能显示的宽度, 后面指定的是在前面总显示宽度的基础之上会拿出30为来显示小数部分
    <2> 精度: float < double < decimal
"""

# 字符串类型
"""
设置模式不让其剔除char的空格: pad_char_to_full_length. 设置完毕就可以使用char_length(字段)函数进行查看了.

char: 定长. 由宽度存取字符的个数. 默认不指定宽度为1个字符
    <1> 如果存储数据时, 存储的字符个数刚刚等于或者超出(非严格模式下)指定的宽度就用char. 更加节省空间
    
    
varchar: 变长. 由1~2Bytes头部 + 数据. (2 ** 16 =65535, 头部最多可以对65535个数的字符进行描述). 必须自己制定宽度, 没有默认宽度.
    <1> 如果存储数据时, 存储的字符个数不等于或者远远低于指定的宽度就用varchar. 更加节省空间
    <2> IO情况下使用varchar是必然的. 
"""

# 日期类型
"""
datatime/timestamp: 年月日 时分秒
data 年月日
time 时分秒 
year 年

插入时间的三种方式: 
    <1> now()
    <2> 紧紧连接的数字: 080808 表示 08:08:08
    <3> 字符串: '08:08:08'
    
timestamp除了时间上可能不够用. 一般都用timestamp取代datetime    
"""

# 枚举  & 集合
"""
枚举: 多选一. eum(1, 2, 3)
集合: 多选多, 多选一. set(1, 2, 3)
"""

五. 表完整性约束

1.介绍

约束条件与数据类型的宽度一样,都是可选参数

作用: 用于 保证数据的完整性和一致性

主要分为:

null  -- 标识该字段允许为空
not null  -- 标识该字段不允许为空

default  -- 为该字段设置默认值

primary key -- 将该字段设置为该表的主键,可以唯一的标识记录
foreign key  -- 将该字段设置为表的外键

auto_increment  -- 标识该字段的值是一个自动增长(一般加于主键)

unsigned  -- 标识该字段是一个无符号的 搭配数值类型
zerofill  -- 标识该字段内的值用0填充 搭配字符

2.null 和 not null 以及 default

2.1 null

null: 被指定的字段允许为空,当没有指定默认约束条件就是null

例如:

create table t1(name varchar(11));
insert into t1 values();
select * from t1;  # null
insert into t1 values(null);
select * from t1;
insert into t1 values(not null);
select * from t1;

2.2 not null

not null : 被指定的字段的值不允许为空

create table t2(name varchar(11) not null);
insert into t2 values();
select * from t2;
'''查询结果
+------+
| name |
+------+
|      |
+------+
'''
insert into t2 values(null);  -- ERROR 1048 (23000): Column 'name' cannot be null
select * from t2;
insert into t2 values('jkey');
select * from t2;

2.3 default

default: 被指定的字段在插入的值没有指定的话默认填入default设置的值

注意: 当已经制定了约束条件 not null 时,default后面就不能指定null作为默认值

例如:

create table t3(name varchar(11) not null default 'jkey');
insert into t3 values();  -- 可以插入空的
select * from t3;
insert into t3 values(null);  -- 报错 不允许插入null

create table t4(name varchar(11) not null default null);  -- 报错ERROR 1067 (42000): Invalid default value for 'name'

2.4 小结

null 可以为空. values(),values(null),values("值")
not null 不可以为空. values('值'),values('not null')
default: 当与not null连用. values不指定值会采用default后面的默认值. 但是需要注意的是default后面这个时候不能指定null.

3.unique

unique: 唯一

俩种唯一的情况

3.1单列唯一

一个字段唯一

create table t4(id int unique,name varchar(11));
desc t4;

insert into t4 values(1,'jkey'),(1,'liu');  -- 报错id为1的重复了
insert into t4 values(1,'jkey'),(2,'liu');
select * from t4;

3.2 联合唯一

多个字段联合以后唯一

-- 多个字段联合唯一: host + post
create table t5(
	id int,
    host varchar(11),
    port int,
    username varchar(16),
    unique(host,port)
);
desc t5;

insert into t5 values(1, '127.0.0.1', 8080, 'root');
insert into t5 values(2, '127.0.0.1', 8081, 'user1');
insert into t5 values(2, '127.0.0.1', 8081, 'user2');  -- 报错,'127.0.0.1', 8081 ip+端口重复了
select * from t5;

4.primary key

4.1 主键

primary key 在mysql中被称之为主键,键就是索引,为了优化查询效率的数据结构

4.2 primary key 介绍

单单从约束效果上来看primary key 等价于 not null + unique. 表示非空且唯一

例如

create table t6(id int primary key);
insert into t6 values(null);  -- 报错 id 字段不能为空
insert into t6 values(1),(1);  -- 报错 id=1 重复
insert into t6 values(1),(3);
select * from t6;

它除了有约束条件以外,它还是innodb存储引擎组织数据的依据

innodb 存储引擎在创建表的时候必须要有primary key.

因为它类似于书的目录,能够提升查询效率,并且它也是建表的依据.

<1> 一张表有且只有一个主键,如果你没有设置主键那么会从上往下遍历直到遇到一个非空且唯一的字段,该字段自动升级为主键

例如

create table t7(
	id int,
    name varchar(11),
    age int null unique,
    addr varchar(11) not null unique
);
desc t7;  -- addr 为 PRI 主键

<2> 如果表中没有主键也没有其他任何的非空且唯一的字段,那么innodb会采用自己内部提供的一个隐藏字段当作主键,隐藏意味着你无法查看和使用快速查询

<3> 一张表中通常都应该有一个主键字段 并且 通常将id/sid/uid分辨率高的字段设置为主键

主键也有单个主键和联合主键

-- 单个字段主键
create table t8(
	id int primary key,
    name varchar(16)
);
desc t8;
-- 联合字段主键
create table t9(
	host varchar(16),
    port int,
    primary key(host, port)
);
desc t9;

强调: 一张表一定要有一个主键,最好为id字段

5.auto_increment

5.1 auto_increment 自增介绍

一般该约束条件使用在主键上,方便建表,为没有给该字段添加记录时,自动开始自增

例如:

create table t10(
	id int primary key auto_increment,
    name varchar(11)
);
insert into t10 values('jkey'),('liu'),('song');  -- 报错 t10没指定插入的字段名称,应该要插入俩个数据,但是只插入了一个
insert into t10(name) values('jkey'),('liu'),('song');
select * from t10;

强调: 所有现在我们直到一张表.应该将id设置为一个primary key 和 auto_increment的字段

5.2 补充: 删除表的记录的2种方式介绍

与自增的字段,在用delete删除后,再插入值,该字段会在之前的id的基础上继续自增

delete from 表名;

所有我们要想要清空表,也就是删除表的记录和自增属性应该使用 truncate

truncate 表名;

5.3 了解知识: auto_increment修改及创建表时可以指定起始数值

在创建完表后,修改自增字段的起始值

alter table t10 auto_increment=4;

创建表时指定auto_increment的初始值

注意: 初始值的设置为表选项,应该放在括号外

create table student(
	id int primary key auto_increment,
    name varchar(16),
    gender enum('male','female') default 'male'
) auto_increment=4;