表相关

1. 创建表

• 格式：

create table 库名.表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件]
);
约束条件：是在数据类型之外对字段附加的额外的限制
注：1、最后一个字段不能加逗号；
2、同一张表内，字段名不能相同；
3、宽度和约束条件可选，字段名和类型是必须的

2.数据类型

2.1 整型 int

整型：默认是有符号的
create table t3(x tinyint); #小整数值
注：修改sql_mode为严格模式，必须重启客户端才能生效
    set global sql_mode="strict_trans_tables";
    select @@sql_mode;
create table t4(x tinyint unsigned);
# 强调：(1)整型类型后面的宽度限制的根本不是存储宽度，限制的是显示宽度
(2)int的存储宽度是4个Bytes，即32个bit，即2**32
(3)create table t5(id int(1));  int宽度不用指定

2.2 浮点型 float

float(255,30)                         double(255,30)                        
decimal(65,30) 
注：括号内数值代表所有位数和小数位，精度从低到高
create table t8(x float(255,30));
create table t9(x double(255,30));
create table t10(x decimal(65,30));

2.3 日期类型

year 1999
date 1999-11-11
time 08:30:00
获取当前时间datetime/timestamp 1999-11-11 08:30:00
方式一：
create table t1(x datetime not null default now()); # 需要指定传入空值时默认取当前时间
insert into t1 values();
方式二：
create table t2(x timestamp);
insert into t2 values();
-----------------------------------------
datetime/timestamp 区别：
(1)DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
(2)DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），
如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间
(3)DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置

2.4 字符串类型

• char类型：定长，浪费空间，存取速度快

范围：
     字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
存储：
    存储char类型的值时，会往右填充空格来满足长度
检索：
     在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式
注：set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; #设置后char的空字符数也会显示出来
select char_length(x) from t5; 显示字符数

• varchar类型：可变长，精准，节省空间，存取速度慢

 存储：
    varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来
前缀： 
    varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，
正好符合mysql对row的最大字节限制，即已经足够使用）
检索：尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

• 想同点：宽度指的都是最大存储的字符个数，超过了都无法正常存储
• 测试：

    length：查看字节数
    char_length:查看字符数

2.5 枚举与集合类型

1、字段的值只能在给定范围中选择，如单选框，多选框
2、enum 单选    只能在给定的范围内选一个值，如性别 sex 男male/女female
3、set 多选   在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...
---------------------------------------
例：
建表：
create table t1(
    name varchar(15),
    sex enum('male','female','unkown'),
    hobbies set('read','music','yinshi','play')
添加内容：
insert into emp values('zhangming','female','read,play');

3. 表的完整性约束

3.0 check 约束

注："P_Id" 列必须只包含大于 0 的整数
1,表已存在创建列时;
ALTER TABLE Persons
ADD CHECK (P_Id>0)

2,创建表时；
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
Address varchar(255),
CHECK (P_Id>0)
)

3.1 not null与default

1、null 表示字段的值可以为空，not null 不能为空；
2、default指定默认值，如果字段没有指定值，显示默认值
例：
sex enum('male','female') not null default 'male'
3，撤销default
ALTER TABLE Persons
ALTER City DROP DEFAULT
# 将persons表中的city列的DEFAULT约束去掉

3.2 unique key

unique 标识该字段的值唯一
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);

#指定一个集体唯一
create table service(
    ip varchar(15),
    port int,
    unique key(ip,port)  
);
insert into service values
('1.1.1.1',3306),
('1.1.1.1',3306);

撤销唯一约束：
ALTER TABLE department1
DROP INDEX name

3.3 primary key

站在约束角度看primary key=not null unique  #不能为空且唯一
-------------------------------------
以后但凡建表，必须注意：
1、必须有且只有一个主键（PRI)，
2、通常是id字段被设置为主键，
3、在表结构中唯一一个字段设置，unique可以有多个字段设置，
作用：mysql 会建树形结构，把数据分散到数据结构中，基于树形结构（索引），提升查找效率
例：
create table t5(
    id int primary key auto_increment
);
#自增长（auto_increment)，可以传空 insert into t5 values(null);
#将表中字段设为主键：
alter table 表名 add primary key（字段名）

#设置字段为不为空
alter table 表名 modify 字段名 char(15) not null;

3.4 auto_increment 自增长

(1)不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
(2)插入内容时指定id 
insert into student values(4,'asb','female');
-----------------------------------------
注：
对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长；
delete from student;

应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，从1开始增长
truncate student;

3.5 foreign key

• 定义：限制关联表的某一个字段的值必是来自于被关联表的某一个字段

1、被关联的字段必须是一个key，通常是id字段
2、创建表时：必须先建立被关联的表，才能建立关联表
create table dep(
    id int primary key auto_increment,
    dname varchar(20),
    info varchar(50)
);

create table emp(
    id int primary key auto_increment,
    name varchar(15),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)  #emp的（dep_id）和dep(id)建关联
    on update cascade
    on delete cascade
);
3、插入记录时：必须先往被关联的表插入记录，才能往关联表中插入记录
insert into dep(dname,info) values
('IT','技术能力有限部门xxx'),
('Sale','文化程度不高'),
('HR','招不到人部门');

insert into emp(name,age,dep_id) values
('egon',18,1),
('alex',28,2),
('wsj',38,2),
('lxx',30,1),
('xiaohou',18,3);
4、删除时应该先删除关联表emp中的记录，再删除被关联表对应的记录
-----------------------------------------
注：mul:外键类型     PRI：主键类型

• 寻找表关系方法

emp        dep
1、先站在左表的角度：去找左表emp的多条记录能否对应右表dep的一条记录
    翻译：多个员工能否属于一个部门

2、然后站在右表的角度：去找右表dep的多条记录能否对应左表emp的一条记录
    翻译：多个部门能否拥有同一名员工

多对一：结果的判断
1、如果只有单向的多对一成立，那么最终的关系就是多对一
2、在emp表新增一个字段dep_id, 该字段外键关联dep(id)

多对多：结果的判断
1、双向的多对一就是多对多
2、需要建立第三张表，有一个字段值fk左表，一个字段的值fk右表