day43學習整理-資料庫基礎

"""
1、字段的修改、添加、删除
2、多表關系(外鍵)
3、單表詳細操作：增删改，查(各種條件)
"""

字段操作

create table tf1(
    id int primary key auto_increment,
    x int,
    y int
);

# 修改
alter table tf1 modify x char(4) default '';
alter table tf1 change y m char(4) default '';

# 增加
mysql>: alter table 表名 add 字段名 類型[(長度) 限制];  # 末尾
eg>: alter table tf1 add z int unsigned;

mysql>: alter table 表名 add 字段名 類型[(寬度) 限制] first;  # 首位
eg>: alter table tf1 add a int unsigned first;

mysql>: alter table 表名 add 字段名 類型[(寬度) 限制] after 舊字段名;  # 某字段後
eg>: alter table tf1 add xx int unsigned after x;

mysql>: alter table 表名 drop 字段名;  # 删除字段
eg>: alter table tf1 drop a;

多表關系

"""
一對一：丈夫-妻子，使用者-身份證，作者-作者詳情
一對多：部門-員工，班級-學生，書-出版社
多對多：老師-班級，課程-學生，出版社-作者
"""

# 書 - 出版社 - 作者 - 作者詳情 外鍵分布
# 外鍵是 建立表與表關聯 的字段，通常 一個表的外鍵 是 另一個表的主鍵（唯一鍵也可以）

# 一對一：外鍵在任何一方都可以，此時外鍵要設定 唯一鍵
"""
作者(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 Bom 2
3 Bob 3

作者詳情(author_detail)： id，info，address
1 Tom_info
2 Bom_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
作者與出版社關系表：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, detail_id
# 作者詳情(author_detail)： id，info，address

# 1、外鍵的 字段名 可以自定義(名字随意)，通常命名規範(關聯表_關聯字段)

# 2、外鍵要通過 foreign key 文法建立表與表之間的關聯

# 3、foreign key(所在表的外鍵字段) references 關聯表(關聯字段)
# eg：foreign key(detail_id) references author_detail(id)

# 4、級聯關系
#   級聯更新 on update cascade
#   級聯删除 on delete 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 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)
);

# 必須先建立被關聯表資料，有關聯表外鍵關聯的記錄後，關聯表才可以建立資料
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>: 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;

# 假設：書與作者也是 一對多 關系，一個作者可以出版多本書
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
    
    # 建立與作者 一對多 的外鍵關聯
    author_id int,  
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);

多對多

# 多對多：一定要建立第三張表(關系表)，每一個外鍵值不唯一，看可以多個外鍵建立聯合唯一

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';

轉載于:https://www.cnblogs.com/Wunsch/p/11580512.html

day43學習整理-資料庫基礎

字段操作

多表關系

外鍵

一對一：無級聯關系

一對一：有級聯關系

一對多

多對多

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述