Field operation
create table tf1( id int primary key auto_increment, x int, y int);# Modify alter table tf1 modify x char(4) default'';alter table tf1 change ym char(4) default'';# Add mysql>: alter table table name add field name type [(length) constraint]; # end eg>: alter table tf1 add z int unsigned;mysql>: alter table table name add field name type [(width) constraint] first; # first place eg>: alter table tf1 add a int unsigned first; mysql>: alter table table name add field name type [( Width) Constraint] after the old field name; # After a field eg>: alter table tf1 add xx int unsigned after x;mysql>: alter table table name drop field name; # Delete field eg>: alter table tf1 drop a;< /pre>Multi-table relationship
"""One-to-one: husband-wife, user-ID, author-author details one pair Multi: Department-Employee, Class-Student, Book-Publisher Many-to-Many: Teacher-Class, Course-Student, Publisher-Author """# Book- Publisher-Author-Author Details Foreign Key Distribution# Foreign key is Establish the fields associated with the table and the table, usually the foreign key of one table is the primary key of another table (unique key is also possible) # One-to-one: The foreign key can be on either side, and the foreign key should be set with a unique key """ Author (author): id, name, sex, age, mobile Author details (author_detail): id, info, address, author_id----------------------- -----------------------------Author: id, name, sex, age, mobile, detail_id1 Tom 12 Bo m 23 Bob 3 Author details (author_detail): id, info, address1 Tom_info2 Bom_info"""# One-to-many: The foreign key must be placed on the multiple side, and the foreign key value is not unique """ Book: id , Name, price, publish_id1 Journey to the West 12 East Journey 23 West Chamber 14 Wanderings 1 Press (publish): id, name, address, phone1 Grandma Press 2 Little Grandma Press """# Many-to-many: Be sure to create the first Three tables (relational tables), each foreign key value is not unique, see that multiple foreign keys can establish a union unique """ author (author): id, name, age publishing (publish): id, name, address author Relationship table with publisher: id, author_id, publish_idid author_id publish_id1 1 12 1 23 2 14 2 2"""foreign key
# Author (author): id, name, sex, age, mobile, detail_id# Author details (author_detail): id, info, address# 1. The field name of the foreign key can be customized (any name), usually the naming convention ( Association table_ association field)# 2. The foreign key should establish the association between the table and the table through the foreign key grammar# 3. foreign key (the foreign key field of the table where it is located) references the association table (association field)# eg: foreign key( detail_id) references author_detail(id)# 4. Cascade relationship# Cascade update on update cascade# Cascade delete on delete cascade# Important: The foreign key field itself can be unique or not, but the field associated with the foreign key must be uniqueOne-to-one: No cascade relationship
# Author details (author_detail): id, info, addresscrea te table author_detail( id int primary key auto_increment, info varchar(256), address varchar(256));# author table 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('Male','Female') default'Male', age int default 0, detail_id int unique not null, foreign key(detail_id) references author_detail(id) );# The data of the associated table must be created first. Only after the records associated with the foreign key of the associated table can the associated table create data 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);# modify the association table authormysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');mysql >: update author set detail_id=3 where detail_id=2; # If you have data that is not related to other data, you can modify it # Delete the related table authormysql>: delete from author where detail_id=3; # Direct delete# Modify the associated table author_detailmysql>: update author_detail set id=10 where id=1; # Cannot modify# Delete the associated table author_detailmysql>: delete from author_detail where id=1; # Cannot delete# Without cascading relationship: # Add: first add the associated table record, then add the associated table record # Delete: delete the associated table record first, and then delete the associated table record # Update: the association and the associated table can not complete the outside of the association Key and primary key data update-(if the associated table record is not bound, you can modify it)One-to-one: cascade relationship
mysql>: drop table author;mysql>: drop table author_detail;# Author details (author_detail): id, info, address create table author_detail( id int primary key auto_increment, info varchar(256), address varchar(256));# author table 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(' Male','Female') default'Male', age int default 0, detail_id int unique not null, foreign key(detail_id) references author_detail(id) on update cascade on delete cascade);# The associated table data must be created first, Only after there are records related to the foreign key of the related table can the related table create data mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1); # error 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);# Modify the association table authormysql>: update author set detail_id=3 where detail_id=2; # Failure, 3 details do not exist mysql>: update author set detail_id=1 where detail_id=2; # Failed, 1 details have been associated 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; # There is no other data Associated data, you can modify # delete the associated table authormysql>: delete from author where detail_id=3; # directly delete# modify the associated table author_detailmysql>: update author_detail set id=10 where id=1; # cascade modification, synchronization Foreign key of the relational table 13344556677', 10);mysql>: delete from author_detail where id=10; # Can be deleted, cascade delete the records of the associated tableone-to-many
# One-to-many: The foreign key must be placed on the side of the many, and the value of the foreign key is not unique. # Publish: id, name, address, phonecreate table publish( id int primary key auto_increment, name varchar(64), address varchar(256), phone char(20));# 书(book): id, name, price, publish_id, author_idcreate table book( id int primary key auto_increment, name varchar(64) not null , price decimal(5, 2) default 0, publish_id int, # One-to-many foreign key cannot set a unique foreign key(publish_id) references publish(id) on update cascade on delete cascade);# Increase: first increase the associated table (publish) data, then add the data of the association table (book) mysql>: insert into publish(name, address, phone) values('People's Publishing House','Beijing', '010-110'),('西Jiaotong University Press','Xi'an', '010-119'),('Old Boy Press','Shanghai', '010-120');mysql>: insert into book(name, price, publish_id) values( 'Journey to the West', 6.66, 1),('Journey to the East', 8.66, 1),('python from entry to soil', 2.66, 2),('the way of programmer training', 3.66, 3), ('好好活着', 88.88, 3);# There is no associated field, the insert is still wrong mysql>: insert into book(name, price, publish_id ) values ('Face Face', 0.3, 4); # Fail# Update: directly update the (publish) primary key of the associated table, and the foreign key of the associated table (book) will be updated in cascade mysql>: update publish set id= 10 where id=1;# Update: directly update the (book) foreign key of the associated table, the modified value corresponds to the primary key of the associated table (publish), if it exists, the update can be successful, otherwise it fails mysql>: update book set publish_id=2 where id=4; # Success mysql>: update book set publish_id=1 where id=4; # Failure# Delete: # Delete the associated table, the associated table will be cascaded deleted mysql>: delete from publish where id = 2;# Delete the associated table, the associated table will not change mysql>: delete from book where publish_id = 3;# Assumption: book and author are also one-to-many relationship, one author can publish Multiple books create table book( id int primary key auto_increment, name varchar(64) not null, price decimal(5, 2) default 0, publish_id int, # One-to-many foreign key cannot set unique foreign key(publish_id) references publish(id) on update cascade on delete cascade # Establish a one-to-many foreign key association with the author author_id int, foreign key(author_id) references author(id) on update cascade on delete cascade);Many-to-many
# Many-to-many: You must create a third table (relational table), each foreign key value is not unique, it can be multiple Foreign key to establish a union unique mysql>: drop table author; mysql>: drop table aut hor_detail;mysql>: drop table book;mysql>: drop table publish;# Author(author): id, name, agecreate table author( id int primary key auto_increment, name varchar(64), age int unsigned default 0);# Publisher (publish): id, name, addresscreate table publish( id int primary key auto_increment, name varchar(64), address varchar(256));# Author and publisher relationship table: id, author_id, publish_id create table author_publish( id int primary key auto_increment, # The relational table must have multiple foreign keys, which are associated with multiple tables. # Association author table author_id int, foreign key(author_id) references author(id) on update cascade on delete cascade, # Association publisher table publish_id int, foreign key(publish_id) references publish(id) on update cascade on delete cascade, # Establish a union of two fields unique(author_id, publish_id));# Note: The relationship table is related to the two tables of author and publisher. In the table structure, there is no relationship between the author and the publisher's two table keys. # Add: The two related tables have no context, but the relationship table must provide data in both tables before the relationship can be matched. mysql>: insert into author( name, age) values('ruakei', 67),('engo', 76),('Lxx', 3);mysql>: insert into publish(name, address) values('Old Boy Press', ' Shanghai'), ('Little Girl Press','Beijing');# Operation relation table: mysql>: insert into author_publish(author_id, publish_id) values(1,1),(1,2),(2,1),(2 ,2),(3,1);# Relational table operations: add, delete, modify, as long as the two relational tables provide corresponding operation data, the operation can be successful, and has no effect on the two relational tables# Operation Two relational tables: # Increase: will not affect the relational table mysql>: insert into publish(name, address) values('Xi’an Jiaotong University Publishing House','Xi’an'); # Change: The relational tables will be updated in cascade mysql> : update publish set id=10 where id=1;# Delete: Relational tables will be deleted in cascade mysql>: delete from author where name='ruakei';