复习

'''
1.数据库的配置:my.ini
[mysqld]
[client]

2.数据库表的引擎:驱动数据的方式
innodb: 支持事物,键,锁 (安全性高)默认是innodb引擎
myisam: 效率高
3.数据类型
整型:整型不规定长度(长度由取值范围决),在范围内自定义了长度(小于范围),赋值如果超出定义长度,会自动按取值范围赋值,所以不需要自定义长度

浮点型:(总长度,小数位)
       decimal(3, 3) -0.999 ~ 0.999 (小数前的 0 不占位) # decimal(小数)
       decimal(5, 3) -99.999 ~ 99.999
      
字符串: 可变与不可变
        char: 不可变,优点: 定长高效
        varchar: 可变,优点: 省空间
        
时间:
    year: yyyy
    data: yyyy-MM-dd
    time: HH:mm:ss
    datetime: yyyy-MM-dd HH:mm:ss
    timestamp(时间戳): 不赋值时,才有系统当前时间
   
枚举与集合
       enum('a', 'b', 'c') # 枚举只能选一个
       set('a', 'b', 'c')  # 集合至多可选(0~n)个
       
4.约束条件
   auto_increment(自增): 必须给key,类型必须是整数,最多只能出现一次
   unique: 确保字段的唯一
   unique + auto_increment: 唯一自增
   primary key(主键): 一条数据的唯一标识       
'''

今日内容

'''
1. 字段的修改、添加、删除
2. 多表关系(外键)
3. 单表详细操作: 增删改
'''

字段操作

create table tf1(
           id int primary key auto_increment,
           x int, y int);
# 修改
alter table tf1 modify x char(4) default ''; #(修改 x 类型为char,不赋值则默认为空)
alter table tf1 change y z char(4) default ''; #(修改 y 字段 为 z 字段,类型为char,不赋值则默认为空)

# 增加
mysql>: alter table 表名 add 字段名 类型[(长度) 约束]; #在末尾增加
      : alter table tf1 add name char(10) not null;
      
mysql>: alter table 表名 add 字段名 类型[(长度) 约束] first; #在首位增加
      : alter table tf1 add age int default 0 first;
            
mysql>: alter table 表名 add 字段名 类型[(长度) 约束] after 旧字段名;
      : alter table tf1 add height int not null after x;
            
# 删除
mysql>: alter table 表名 drop 字段名;
      : alter table tf1 drop x;
        
# 注:字段操作关键字有 alter、drop、add  其它关键字看应用情况决定,如修改类型用(modify),修改字段用(change)

多表关系

'''
一对一:丈夫-妻子, 用户-身份证, 作者-作者详情
一对多:部门-员工, 班级-学生, 书-出版社
多对多:老师-班级, 课程-学生, 出版社-作者
'''

# 书 - 出版社 - 作者 - 作者详情   外键分布
# 外键是 建立表与表关联 的字段(外键是字段),通常 一个表的外键 是 另一个表的主键(唯一键也可以)

# 一对一:外键在任何一方都可以,此时 外键 要设置 唯一键(重点)
'''
作者(author): id, name, sex, age, mobile
作者详情(author_detail): id, info, address, author_id
------------------------------------------------------
作者(author): id, name, sex, age, mobile, detail_id
1 Tom 1
2 Bob 2
3 Bob 3

作者详情(author_detail): id, info, address
1 Tom_info
2 Bob_info
'''

# 一对多: 外键必须放在多的一方,此时外键值不唯一
'''
书(book): id, name, price, publish_id
1 西游记 1
2 东游记 2
3 西厢记 1
4 流浪记 1

出版社(publish): id, name, address, phone
1 老奶奶出版社
2 小奶奶出版社
'''

# 多对多: 一定要创建第三张表(关系表),每一个外键值不唯一,可以多个外键建立联合唯一
'''
作者(author): id, name, age
出版社(publish): id, name, address
作者与出版社关系表(author_publish): id, author_id, publish_id

id		author_id		publish_id
1			1				1
2			1				2
3			2				1
4			2				2
'''

外键

# 作者(author): id, name, sex, age, mobile, author_id
# 作者详情(author_detail): id, info, address

# 1. 外键的 字段名 可以自定义(名字随意),通常命名规范(关联表_关联字段)

# 2. 外键要通过 foreign key 语法建立表与表的关联

# 3. 建立外键的 sql 语法
   :foreign key(所在表的外键字段) references 关联表(关联字段)
   :foreign key(author_id) references author_detail(id)
    
# 4. 级联关系
#    级联更新 on update cascade
#	 级联删除 on update cascade
   
##############
# 重点:外键字段本身可以唯一或不唯一,但是外键关联的字段一定唯一

一对一:无级联关系

# 作者 详情(author_detail): id, info, address
create table author_detail(
           id int primary key auto_increment,
            info varchar(256),
            address varchar(256));
            
# 作者表: id, name, sex, age,mobile, detail_id
create table author(id int primary key auto_increment, name char(10), sex enum('男','女') default '男', age int default 0, mobile int, detail_id int unique not null,foreign key(detail_id) references author_detail(id));

# 必须先创建被关联表数据,有关联表外键关联的记录后,关联表才可以创建数据
mysql>:insert into author_detail(info,address) values('Tom_info', 'Tom_address');
     
      :insert into author(name, age int, detail_id) values('Tom',18, 1);      
      :insert into author_detail(info,address) values('Bob_info','Bob_address');
      :insert into author(name,age,detail_id) values('Bob',18, 2);
      
# 修改关联表 author
mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据,就可以修改
# 删除关联表 author
mysql>: delete from author where detail_id=3;  # 直接删除

# 修改被关联表 author_detail
mysql>: update author_detail set id=10 where id=1;  # 无法修改
# 删除被关联表 author_detail
mysql>: delete from author_detail where id=1;  # 无法删除

# 没有级联关系下:
# 增加:先增加被关联表记录,再增加关联表记录
# 删除:先删除关联表记录,再删除被关联表记录
# 更新:关联与被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定,可以修改)

一对一有级联关系

mysql>: drop table author;
mysql>: drop table author_detail;


# 作者详情(author_detail): id,info,address
create table author_detail(
	id int primary key auto_increment,
    info varchar(256),
    address varchar(256)
);

# 作者表id,name,sex,age,mobile, detail_id
create table author(
	id int primary key auto_increment,
    name varchar(64) not null,
    mobile char(11) unique not null,
    sex enum('男', '女') default '男',
    age int default 0,
    detail_id int unique not null,
    foreign key(detail_id) references author_detail(id)
    on update cascade 
    on delete cascade
);



# 必须先创建被关联表数据,有关联表外键关联的记录后,关联表才可以创建数据
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);  # 错误
mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);

# 修改关联表 author
mysql>: update author set detail_id=3 where detail_id=2;  # 失败,3详情不存在
mysql>: update author set detail_id=1 where detail_id=2;  # 失败,1详情已被关联
mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据,就可以修改
# 删除关联表 author
mysql>: delete from author where detail_id=3;  # 直接删除

# 修改被关联表 author_detail
mysql>: update author_detail set id=10 where id=1;  # 级联修改,同步关系关联表外键

# 删除被关联表 author_detail
mysql>: delete from author where detail_id=10;  # 可以删除对被关联表无影响
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 10);
mysql>: delete from author_detail where id=10;  # 可以删除,将关联表的记录级联删除掉

一对多

# 一对多:外键必须放在多的一方,此时外键值不唯一

# 出版社(publish): id,name,address,phone
create table publish(
	id int primary key auto_increment,
    name varchar(64),
    address varchar(256),
    phone char(20)
);

# 书(book):id,name,price,publish_id, author_id
create table book(
	id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5, 2) default 0,
    publish_id int,  # 一对多的外键不能设置唯一
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade
);

# 增:先增加被关联表(publish)的数据,再增加关联表(book)的数据
mysql>: insert into publish(name, address, phone) values
('人民出版社', '北京', '010-110'),
('西交大出版社', '西安', '010-119'),
('老男孩出版社', '上海', '010-120');

mysql>: insert into book(name, price, publish_id) values
('西游记', 6.66, 1),
('东游记', 8.66, 1),
('python从入门到入土', 2.66, 2),
('轮程序员修养之道', 3.66, 3),
('好好活着', 88.88, 3);
# 没有被关联的字段,插入依旧错误
mysql>: insert into book(name, price, publish_id) values ('打脸之道', 0.3, 4);  # 失败


# 更新:直接更新被关联表的(publish) 主键,关联表(book) 外键 会级联更新
mysql>: update publish set id=10 where id=1;
# 更新:直接更新关联表的(book) 外键,修改的值对应被关联表(publish) 主键 如果存在,可以更新成功,反之失败
mysql>: update book set publish_id=2 where id=4;  # 成功
mysql>: update book set publish_id=1 where id=4;  # 失败


# 删:
#	删被关联表,关联表会被级联删除
mysql>: delete from publish where id = 2;

#	删关联表,被关联表不会发生变化
mysql>: delete from book where publish_id = 3;

多对多

# 多对多:一定要创建第三张表(关系表),每一个外键值不唯一,看可以多个外键建立联合唯一

mysql>: drop table author;
mysql>: drop table author_detail;
mysql>: drop table book;
mysql>: drop table publish;

# 作者(author):id, name, age
create table author(
	id int primary key auto_increment,
    name varchar(64),
    age int unsigned default 0
);

# 出版社(publish):id, name, address
create table publish(
	id int primary key auto_increment,
    name varchar(64),
    address varchar(256)
);

# 作者与出版社关系表:id, author_id, publish_id
create table author_publish(
	id int primary key auto_increment,
    # 关系表一定有多个外键,关联着多张表
    # 关联作者表
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    # 关联出版社表
    publish_id int,
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade,
    # 建立两个字段的联合唯一
    unique(author_id, publish_id)
);

# 注:关系表 关联着 作者 和 出版社 两张表,在表结构上 作者 与 出版社 两表键没有任何关系


# 增:两张被关联表,没有前后关系,但关系表必须在两个表都提供数据后才能进行 关系匹配
mysql>: insert into author(name, age) values('ruakei', 67),('engo', 76),('Lxx', 3);
mysql>: insert into publish(name, address) values('老男孩出版社', '上海'),('小女孩出版社', '北京');

# 操作关系表:
mysql>: insert into author_publish(author_id, publish_id) values(1,1),(1,2),(2,1),(2,2),(3,1);

# 关系表操作:增、删、改,只要两张被关系表有提供对应的操作数据,都可以操作成功,且对两张被关系表没有影响


# 操作两张被关系表:
#		增:不会影响关系表
mysql>: insert into publish(name, address) values('西交大出版社', '西安');
#		改:关系表都会级联更新
mysql>: update publish set id=10 where id=1;
#		删:关系表都会级联删除
mysql>: delete from author where name='ruakei';