One, field operation
#Create a tf1 table with id as the primary key and increment, x and y are both int type create table tf1( id int primary key auto_increment, x int, y int); '''Modify''' 1) eg: alter table tf1 modify x char(4) default 'haha'; #Set the data type of x to char, and increase the default value'haha' 2 ) Eg: alter table tf1 change ym char(4) default 'hee hee'; # Modify the field name of y to m, and modify the data type to char, and increase the default value "'increase"' 1) alter table table name add new field type [length] constraint eg: alter table tf1 add z int unsigned; #add at the end2 ) alter table table name add new field type [length] constraint first; eg: alter table tf1 add m int unsigned first; #first add3 ) alter table table name add new field type [length] constraint after old field name; #Add after a field eg: alter table tf1 add n int unsigned after x; #Add after the x field "'delete'' drop delete table, field delete delete data alter table table name drop field name; #Delete field eg: alter table tf1 drop x; #Delete x field< /span>
2. Multi-table relationship
What is a foreign key?
? When establishing a multi-table association, usually the foreign key of one table is the primary key of another table (unique key is also possible)
one-to-one : Student table-student profile table (a student only corresponds to one student profile, and one student profile also corresponds to only one student)
One-to-many: Country table-student table (a student has only one country , A country can have multiple students)
Many-to-many: Student table-course table (a student can choose multiple courses, and a course can also have multiple students)< /p>
2.1 One-to-one
No cascade relationship: student-student profile
'''Student profile table''' # stu_msg:id stu_msg create table student_msg (id int primary key auto_increment, #id is the primary key and self-increasing stu_msg varchar(256)); '''student table''' #student:id name age msg_id create table student( id int primary key auto_increment, #id is the primary key And add name varchar(64) not null, #name is not empty age int not null, #age is not empty msg_id int uniq ue not null, #Set the profile id, which must be set as the unique key and not empty, so as to achieve one-to-one foreign key(msg_id) references student_msg(id) # msg_id is the foreign key of the student table, the id of the associated student information table ); ----------------- -------------------------- We have to create the student details table first, and then create the student table ***Remember: you must create the associated table first Data. Only after the records associated with the foreign key of the associated table can be created, the associated table can create data"'insert table field'' insert into student_msg(stu_msg) values('student msg haha'),('this is stu msg'); +----+------------- -----+ | id | stu_msg | +----+------------------+ | 1 | student msg haha | | 2 | this is stu msg | +----+------------- -----+ insert into student(name,age,msg_id) values('yjy',21,1),('wwb','23',2); + ----+------+-----+--------+ | id | name | age | msg_id | +---- +------+-----+--------+ | 1 | yjy | 21 | 1 | | 2 | wwb | 23 | 2 | +----+------+-----+--------+ '''Delete table field'' delete < span class="hljs-keyword">from student_msg where id = 1; #Can’t delete, because the student table is associated with delete from student where msg_id = 1; #Delete directly ***There is no cascade relationship*** : # Add: First add the associated table record, then add the associated table record# Delete: delete the associated table record first, then delete the associated table Record # Update: Associated and, the associated table cannot complete the associated foreign key and primary key data update-(if the associated table record is not bound, you can modify it) span> span>
There is a cascade relationship:< strong>Student-student profile
"'student profile table''' # stu_msg:id stu_msg create table student_msg( id int primary key auto_increment, #id is the primary key and self-increment stu_msg varchar(256)); '''student table''' #student:id name age msg_id create table student( id int primary key auto_increment, #id is the primary key and self-incremented name varchar(64) not null, #name is not empty age int not null, #age is not empty msg_id int unique not null , #Set the profile id, be sure to set the unique key not empty, so as to achieve one-to-one foreign key(msg_id) references student_msg(id) # msg_id is the foreign key of the student table, and the id of the associated student information table on update cascade < span class="hljs-comment">#Associated level update on delete cascade #Associated level delete); ***Remember: you must first create the associated table data, there are associated table foreign keys After the associated records, the association table can create data'''Modify the association table''' update student set msg_id=3 where msg_id=< span class="hljs-number">2; # failed, 3 details do not exist update student set msg_id=1 where msg_id=2; # failed, 1 details have been associated update student set msg_id=3 where msg_id=2; # If you have data that is not associated with other data, you can modify "'modify the associated table" 'update student_msg set id=10 where id=1; # Cascade modification, synchronization relationship association Table foreign key, the student information table is changed, the msg_id in the student table will also change where msg_id=2;# Can be deleted and has no effect on the associated table"'Delete the associated table Table''' mysql>: dele te from student_msg where msg=10; # Delete directly, the association table will be cascaded to delete< /span>< /span>
2.2 One-to-many
Country Table-Student Table
***One-to-many: The foreign key must be placed on the side of the many, at this time the foreign key value is not unique, which means that the foreign key must be placed in the student table
'''Create a country table''' #country:id,name,address create table country( id int primary key auto_increment, name varchar( 64) not null, address varchar(256)); +------- --+--------------+------+-----+---------+--------- -------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+--- ---+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | address | varchar(256) | YES | | NULL | | +---------+--------------+------+-----+ ---------+----------------+ '''Create student table''' #student: id,name,age,country_id create table student( id int primary key auto_increment, name varchar(64) not null, age int not null, country_id int, foreign key(country_id) references country(id) on update cascade on delete cascade) +------------+---------- ---+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+-- -------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | age | int(11) | NO | | NULL | | | country_id | int(11) | NO | MUL | NULL | | +------------+------------ -+------+-----+---------+----------------+ '''Increase''' First increase the data of the associated table (country), and then add the data of the associated table (student)#Insert the information of the associated table (country) insert into country(name,address) values('china','gansu'),('canada ','xixi'),('china','shanghai'); +----+--------+----------+ | id | name | address | +----+--- -----+----------+ | 1 | china | gansu | | 2 | canada | xixi | | 3 | china | shanghai | +----+--------+----------+ #Insert related table (student) information insert into student(name,age,country_id) values('yjy','21 ', 1),('wwb','23',3); insert into student(name,age,country_id) values('hhh','21', 5); # The failed field is not associated, and the insert is still wrong. "'Update'' #Update the associated table update country set id=10 where id=1; *** Update the country set id directly The (country) primary key of the association table and the foreign key of the association table (student) will be updated in cascade, (this is like updating the game in the background and does not affect the user's gaming experience) #Update association table update student set country=2 where id=3; # Update student successfully set country=4 where id=3; # Failed to update the association table directly ( student) foreign key, the modified value corresponds to the primary key of the associated table (country) if it exists, it can be updated successfully, otherwise it fails '''delete''' # Delete the associated table, the associated table will be cascaded delete delete from country where id = 2; # Delete the associated table, the associated table will not change delete from student where country = 3;< /span>< /span>
2.3 many-to-many
student table-course Table
***Remember it***
Many-to-many: Be sure to create a third table (relational table), each foreign key value is not unique, it seems that multiple foreign keys can establish a union unique
The two related tables have no context, but the relationship table must provide data in both tables before the relationship can be matched
#student table: id name age create table student( id int primary key auto_increment, name char(4) not null, age int default 0 ); #course table: id name create table course( id int primary key auto_increment, name varchar( 64)); +-------+-------------+------+-----+ ---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+- ------------+------+-----+---------+-------------- --+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+- ---------------+ #student_course relationship table: id, c_id, s_id create table s_c( id int primary key auto_increment, #Related Course Listc_id int, foreign key(c_id) references course(id) on update cascade on delete cascade, # Association student table s_id int, foreign key(s_id) references student(id) on update cascade on delete cascade, # Establish the union of two fields unique(c_id, s_id) ); +-------+---------+------+-----+---------+------ ----------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+ -----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | c_id | int(11) | YES | MUL | NULL | | | s_id | int(11 ) | YES | MUL | NULL | | +-------+---------+------+-----+--------- +----------------+ '''Note: The relationship table is related to the student table and the curriculum table. In the table structure The key between the student table and the curriculum table has no relationship, and only has a relationship with the third table. "'"'Relational table operations: add, delete, modify, as long as Two related tables provide corresponding operation data, both can be successfully operated, and have no effect on the two related tables''' '''增''' #insert into student(name,age) values('yjy',21),('wwb','23'); +----+------+-----+ | id | name | age | +----+------+--- --+ | 1 | yjy | 21 | | 2 | wwb | 23 | +----+------+-----+ #insert into course(name) values('language'),('mathematics'),('English'); +---- +--------+ | id | name | +----+--------+ | 1 | Language | | 2 | Mathematics| | 3 | English| +----+--------+ insert into course(name) values('sports'); # will not affect the relationship table#insert student_course information insert into s_c(c_id ,s_id) values(1,1),(3,2),(2,1),(2,3); ' ''改''' update course set id=10 where id=1; #Relation table Will cascade updates "'Delete"' delete from student where name='wwb' ; #Relational tables will be deleted in cascade span> span>
#Create a tf1 table with id as the primary key and increment, x and y are both int type create table tf1( id int primary key auto_increment , x int, y int); < span class="hljs-string">'''Modify''' 1) eg: alter table tf1 modify x char(4) default 'haha'; #Set the data type of x to char, and increase the default value'haha' 2) eg: alter table tf1 change ym char(4) default 'hee hee'; #Modify the field name of y to m, and modify the data type to char, and increase the default value'''increase'' 1) alter table table name add new field type [length] constraint eg: alter table tf1 add z int unsigned; # add at the end 2) alter table table name add new field type [length] constraint first; eg: alter table tf1 add m int unsigned first; #first add3) alter table table name add new field type [length] constraint after old field name; #Add after a field eg: alter table tf1 add n int unsigned after x; #Add "'Delete"' drop after the x fielddele te delete data alter table table name drop field name; # delete field eg: alter table tf1 drop x; # delete x field span>
2. Multi-table relationship h2>
What is a foreign key?
? When establishing a multi-table association, usually the foreign key of one table is the primary key of another table (unique key is also possible)
one-to-one : Student table-student profile table (a student only corresponds to one student profile, and one student profile also corresponds to only one student)
One-to-many: Country table-student table (a student has only one country , A country can have multiple students)
Many-to-many: Student table-course table (a student can choose multiple courses, and a course can also have multiple students)< /p>
2.1 One-to-one
No cascade relationship: student-student profile
'''Student profile table''' # stu_msg:id stu_msg create table student_msg (id int primary key auto_increment, #id is the primary key and self-increasing stu_msg varchar(256)); '''student table''' #student:id name age msg_id create table student( id int primary key auto_increment, #id is the primary key And add name varchar(64) not null, #name is not empty age int not null, #age is not empty msg_id int unique not null, #Set the profile id, which must be set as a unique key and not empty, so that one-to-one foreign key(msg_id) references student_msg can be realized (id) # msg_id is the foreign key of the student table, the id of the associated student information table ); ------------------- ------------------------ We must first create the student details table, and then create the student table ***Remember: you must first create the associated table data, Only after there are records associated with the foreign key of the associated table can the associated table create data"'insert table field'' insert into student_msg(stu_msg) values('student msg haha'),('this is stu msg'); +----+--------------- ---+ | id | stu_msg | +----+------------------+ | 1 | student msg haha | | 2 | this is stu msg | +----+--------------- ---+ insert into student(name,age,msg_id) values('yjy',21,1),('wwb','23',2); +-- --+------+-----+--------+ | id | name | age | msg_id | +----+---- --+-----+--------+ | 1 | yjy | 21 | 1 | | 2 | wwb | 23 | 2 | +--- -+------+-----+--------+ '''Delete table field'' delete from student_msg where id = 1; #cannot be deleted, because the student table is associated with delete from student where msg_id = 1; # directly deleted ***no cascade relationship***: # Add: first add the associated table record, then add the associated table record# Delete: delete the associated table record first, then delete the associated table record# Update: Associate and, the associated table cannot complete the associated foreign key and primary key data update-(if the associated table record is not bound, you can modify it)< /span>< /span>
There is a cascade relationship: Student-student profile
"'student profile table''' # stu_msg:id stu_msg create table student_msg( id int primary key auto_increment, #id is the primary key and self-increment stu_msg varchar(256)); '''student table''' #student:id name age msg_id create table student( id int primary key auto_increment, #id is the primary key and self-incremented name varchar(64) not null, #name Age int not null, #age is not empty msg_id int unique not null, < span class="hljs-comment">#Set the profile id. The unique key must be set and not empty, so that one-to-one foreign key(msg_id) references student_msg(id) # msg_id is the foreign key of the student table, and the id of the associated student information table on update cascade # Union-level update on delete cascade # Union-level delete); ***Keep in mind: you must first create the associated table data, there is a foreign key association of the associated table After the record, the association table can create data'''Modify the association table''' update student set msg_id=3 where msg_id=2; # failed, 3 details do not exist update student set msg_id=1 where msg_id=2; # failed, 1 details have been associated update student set msg_id=3 where msg_id=2; # If you have data that is not associated with other data, you can modify "'Modify the associated table'' update student_msg set id=10 where id=1; # Cascade modification, synchronization relationship association table Foreign key, the student information table is changed, the msg_id in the student table will also change msg_id=2;# Can be deleted and has no effect on the associated table"'Delete the associated table ''' mysql>: delete from student_msg where msg=10; # Delete directly, the association table will be deleted by cascade < /span>< /span>
2.2 One-to-many
Country Table-Student Table
***One-to-many: The foreign key must be placed on the side of the many, at this time the foreign key value is not unique, which means that the foreign key must be placed on the student table
'''Create a country table''' #country:id,name,address create table country( id int primary key auto_increment, name varchar( 64) not null, address varchar(256)); +------- --+--------------+------+-----+---------+--------- -------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+--- ---+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | address | varchar(256) | YES | | NULL | | +---------+--------------+------+-----+ ---------+----------------+ '''Create student table''' #student: id,name,age,country_id create table student( id int primary key auto_increment, name varchar(64) not null, age int not null, country_id int, foreign key(country_id) references country(id) on update cascade on delete cascade) +------------+---------- ---+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+-- -------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | age | int(11) | NO | | NULL | | | country_id | int(11) | NO | MUL | NULL | | +------------+------------ -+------+-----+---------+----------------+ '''Increase''' First increase the data of the associated table (country), and then add the data of the associated table (student)#Insert the information of the associated table (country) insert into country(name,address) values('china','gansu'),('canada ','xixi'),('china','shanghai'); +----+--------+----------+ | id | name | address | +----+--- -----+----------+ | 1 | china | gansu | | 2 | canada | xixi | | 3 | china | shanghai | +----+--------+----------+ #Insert related table (student) information insert into student(name,age,country_id) values('yjy','21 ', 1),('wwb','23',3); insert into student(name,age,country_id) values('hhh','21', 5); # The failed field is not associated, and the insert is still wrong. "'Update'' #Update the associated table update country set id=10 where id=1; *** Update the country set id directly The (country) primary key of the association table and the foreign key of the association table (student) will be updated in cascade, (this is like updating the game in the background and does not affect the user's gaming experience) #Update association table update student set country=2 where id=3; # Update student successfully set country=4 where id=3; # Failed to update the association table directly ( student) 外键，修改的值对应被关联表(country) 主键如果存在，可以更新成功，反之失败'''删除''' # 删被关联表，关联表会被级联删除delete from country where id = 2; # 删关联表，被关联表不会发生变化delete from student where country = 3;< /span>< /span>
2.3 多对多
学生表-课程表
***牢记***
多对多：一定要创建第三张表(关系表)，每一个外键值不唯一，看可以多个外键建立联合唯一
两张被关联表，没有前后关系，但关系表必须在两个表都提供数据后才能进行 关系匹配
#学生表：id name age create table student( id int primary key auto_increment, name char(4) not null, age int default 0 ); #课程表：id name create table course( id int primary key auto_increment, name varchar(64)); +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ #学生_课程关系表:id, c_id, s_id create table s_c( id int primary key auto_increment, #关联课程表 c_id int, foreign key(c_id) references course(id) on update cascade on delete cascade, # 关联学生表 s_id int, foreign key(s_id) references student(id) on update cascade on delete cascade, # 建立两个字段的联合唯一 unique(c_id, s_id) ); +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | c_id | int(11) | YES | MUL | NULL | | | s_id | int(11) | YES | MUL | NULL | | +-------+---------+------+-----+---------+----------------+ ‘‘‘注：关系表 关联着 学生表 和 课程表 两张表，在表结构上 学生表和课程表 两表之间的键 没有任何关系，和第三张表才有关系‘‘‘ ‘‘‘关系表操作：增、删、改，只要两张被关系表有提供对应的操作数据，都可以操作成功，且对两张被关系表没有影响‘‘‘ ‘‘‘增‘‘‘ #插入学生信息 insert into student(name,age) values(‘yjy‘,21),(‘wwb‘,‘23‘); +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | yjy | 21 | | 2 | wwb | 23 | +----+------+-----+ #插入课程信息 insert into course(name) values(‘语文‘),(‘数学‘),(‘英语‘); +----+--------+ | id | name | +----+--------+ | 1 | 语文 | | 2 | 数学 | | 3 | 英语 | +----+--------+ insert into course(name) values(‘体育‘); #不会影响关系表 #插入学生_课程信息 insert into s_c(c_id,s_id) values(1,1),(3,2),(2,1),(2,3); ‘‘‘改‘‘‘ update course set id=10 where id=1; #关系表都会级联更新 ‘‘‘删除‘‘‘ delete from student where name=‘wwb‘; #关系表都会级联删除
#创建一个tf1表，id为主键并且自增，x，y都为int型 create table tf1( id int primary key auto_increment, x int, y int); ‘‘‘修改‘‘‘ 1） eg：alter table tf1 modify x char(4) default ‘哈哈‘; #将x的数据类型设为char，并增加默认值‘哈哈‘ 2） eg：alter table tf1 change y m char(4) default ‘嘻嘻嘻‘; #将y的字段名修改为m,并将数据类型修改为char，并增加默认值 ‘‘‘增加‘‘‘ 1) alter table 表名 add 新字段 类型[长度] 约束 eg：alter table tf1 add z int unsigned; #末尾添加 2) alter table 表名 add 新字段 类型[长度] 约束 first； eg：alter table tf1 add m int unsigned first; #首位添加 3) alter table 表名 add 新字段 类型[长度] 约束 after 旧字段名； #某字段之后添加 eg：alter table tf1 add n int unsigned after x; #在x字段之后添加 ‘‘‘删除‘‘‘ drop 删除表、字段 delete 删除数据 alter table 表名 drop 字段名； #删除字段 eg：alter table tf1 drop x； #删除x字段
二、多表关系
什么是外键？
? 建立多表关联的时候，通常一个表的外键另是一个表的主键(唯一键也可以)
一对一：学生表-学生简介表（一个学生只对应一个学生简介，一个学生简介也只对应一个学生）
一对多： 国家表-学生表 （一个学生只有一个国家，一个国家可以有多个学生）
多对多：学生表-课程表（一个学生可以选择多门课程，一门课程也可以有多个学生来上）
2.1 一对一
无联级关系：学生-学生简介
‘‘‘学生简介表‘‘‘ # stu_msg:id stu_msg create table student_msg( id int primary key auto_increment, #id为主键且自增 stu_msg varchar(256)); ‘‘‘学生表‘‘‘ #student:id name age msg_id create table student( id int primary key auto_increment, #id为主键且自增 name varchar(64) not null, #name不为空 age int not null, #age不为空 msg_id int unique not null, #设置简介id，一定要设置为唯一键不为空，这样才能实现一对一 foreign key(msg_id) references student_msg(id) # msg_id 为student表的外键，关联的学生信息表的id ); ------------------------------------------- 我们要先要创建学生详情表，再创建学生表 ***牢记：必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据 ‘‘‘插入表字段‘‘‘ insert into student_msg(stu_msg) values(‘student msg haha‘),(‘this is stu msg‘); +----+------------------+ | id | stu_msg | +----+------------------+ | 1 | student msg haha | | 2 | this is stu msg | +----+------------------+ insert into student(name,age,msg_id) values(‘yjy‘,21,1),(‘wwb‘,‘23‘,2); +----+------+-----+--------+ | id | name | age | msg_id | +----+------+-----+--------+ | 1 | yjy | 21 | 1 | | 2 | wwb | 23 | 2 | +----+------+-----+--------+ ‘‘‘删除表字段‘‘‘ delete from student_msg where id = 1; #删除不了，因为被student表关联着 delete from student where msg_id = 1; #直接就删除了 ***没有级联关系下***： # 增加：先增加被关联表记录，再增加关联表记录 # 删除：先删除关联表记录，再删除被关联表记录 # 更新：关联与，被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定，可以修改)
有联级关系：学生-学生简介
‘‘‘学生简介表‘‘‘ # stu_msg:id stu_msg create table student_msg( id int primary key auto_increment, #id为主键且自增 stu_msg varchar(256)); ‘‘‘学生表‘‘‘ #student:id name age msg_id create table student( id int primary key auto_increment, #id为主键且自增 name varchar(64) not null, #name不为空 age int not null, #age不为空 msg_id int unique not null, #设置简介id，一定要设置为唯一键不为空，这样才能实现一对一 foreign key(msg_id) references student_msg(id) # msg_id 为student表的外键，关联的学生信息表的id on update cascade #联级更新 on delete cascade #联级删除 ); ***牢记：必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据 ‘‘‘修改关联表‘‘‘ update student set msg_id=3 where msg_id=2; # 失败，3详情不存在 update student set msg_id=1 where msg_id=2; # 失败，1详情已被关联 update student set msg_id=3 where msg_id=2; # 有未被其他数据关联的数据，就可以修改 ‘‘‘修改被关联表‘‘‘ update student_msg set id=10 where id=1; # 级联修改，同步关系关联表外键,学生信息表改了，学生表里面的msg_id也会变 ‘‘‘删除关联表‘‘‘ delete from student where msg_id=2;# 可以删除对被关联表无影响 ‘‘‘删除被关联表‘‘‘ mysql>: delete from student_msg where msg=10; # 直接删除，关联表会被级联删除
2.2 一对多
国家表-学生表
***一对多：外键必须放在多的一方，此时外键值不唯一，也就是说外键必须放在学生表
‘‘‘创建国家表‘‘‘ #country:id,name,address create table country( id int primary key auto_increment, name varchar(64) not null, address varchar(256)); +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | address | varchar(256) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ ‘‘‘创建学生表‘‘‘ #student：id，name,age,country_id create table student( id int primary key auto_increment, name varchar(64) not null, age int not null, country_id int, foreign key(country_id) references country(id) on update cascade on delete cascade ) +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | age | int(11) | NO | | NULL | | | country_id | int(11) | NO | MUL | NULL | | +------------+-------------+------+-----+---------+----------------+ ‘‘‘增‘‘‘ 先增加被关联表(country)的数据，再增加关联表(student)的数据 #插入被关联表(country)信息 insert into country(name,address) values(‘china‘,‘gansu‘),(‘canada‘,‘xixi‘),(‘china‘,‘shanghai‘); +----+--------+----------+ | id | name | address | +----+--------+----------+ | 1 | china | gansu | | 2 | canada | xixi | | 3 | china | shanghai | +----+--------+----------+ #插入关联表(student)信息 insert into student(name,age,country_id) values(‘yjy‘,‘21‘, 1),(‘wwb‘,‘23‘,3)； insert into student(name,age,country_id) values(‘hhh‘,‘21‘, 5)； # 失败 没有被关联的字段，插入依旧错误 ‘‘‘更新‘‘‘ #更新被关联表 update country set id=10 where id=1; *** 直接更新被关联表的(country) 主键，关联表(student) 外键 会级联更新，（这个就好比后台更新游戏，不影响用户的游戏体验） #更新关联表 update student set country=2 where id=3; # 成功 update student set country=4 where id=3; # 失败 ***直接更新关联表的(student) 外键，修改的值对应被关联表(country) 主键 如果存在，可以更新成功，反之失败 ‘‘‘删除‘‘‘ # 删被关联表，关联表会被级联删除 delete from country where id = 2; # 删关联表，被关联表不会发生变化 delete from student where country = 3;
2.3 多对多
学生表-课程表
***牢记***
多对多：一定要创建第三张表(关系表)，每一个外键值不唯一，看可以多个外键建立联合唯一
两张被关联表，没有前后关系，但关系表必须在两个表都提供数据后才能进行 关系匹配
#学生表：id name age create table student( id int primary key auto_increment, name char(4) not null, age int default 0 ); #课程表：id name create table course( id int primary key auto_increment, name varchar(64)); +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ #学生_课程关系表:id, c_id, s_id create table s_c( id int primary key auto_increment, #关联课程表 c_id int, foreign key(c_id) references course(id) on update cascade on delete cascade, # 关联学生表 s_id int, foreign key(s_id) references student(id) on update cascade on delete cascade, # 建立两个字段的联合唯一 unique(c_id, s_id) ); +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | c_id | int(11) | YES | MUL | NULL | | | s_id | int(11) | YES | MUL | NULL | | +-------+---------+------+-----+---------+----------------+ ‘‘‘注：关系表 关联着 学生表 和 课程表 两张表，在表结构上 学生表和课程表 两表之间的键 没有任何关系，和第三张表才有关系‘‘‘ ‘‘‘关系表操作：增、删、改，只要两张被关系表有提供对应的操作数据，都可以操作成功，且对两张被关系表没有影响‘‘‘ ‘‘‘增‘‘‘ #插入学生信息 insert into student(name,age) values(‘yjy‘,21),(‘wwb‘,‘23‘); +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | yjy | 21 | | 2 | wwb | 23 | +----+------+-----+ #插入课程信息 insert into course(name) values(‘语文‘),(‘数学‘),(‘英语‘); +----+--------+ | id | name | +----+--------+ | 1 | 语文 | | 2 | 数学 | | 3 | 英语 | +----+--------+ insert into course(name) values(‘体育‘); #不会影响关系表 #插入学生_课程信息 insert into s_c(c_id,s_id) values(1,1),(3,2),(2,1),(2,3); ‘‘‘改‘‘‘ update course set id=10 where id=1; #关系表都会级联更新 ‘‘‘删除‘‘‘ delete from student where name=‘wwb‘; #关系表都会级联删除
#创建一个tf1表，id为主键并且自增，x，y都为int型 create table tf1( id int primary key auto_increment, x int, y int); ‘‘‘修改‘‘‘ 1） eg：alter table tf1 modify x char(4) default ‘哈哈‘; #将x的数据类型设为char，并增加默认值‘哈哈‘ 2） eg：alter table tf1 change y m char(4) default ‘嘻嘻嘻‘; #将y的字段名修改为m,并将数据类型修改为char，并增加默认值 ‘‘‘增加‘‘‘ 1) alter table 表名 add 新字段 类型[长度] 约束 eg：alter table tf1 add z int unsigned; #末尾添加 2) alter table 表名 add 新字段 类型[长度] 约束 first； eg：alter table tf1 add m int unsigned first; #首位添加 3) alter table 表名 add 新字段 类型[长度] 约束 after 旧字段名； #某字段之后添加 eg：alter table tf1 add n int unsigned after x; #在x字段之后添加 ‘‘‘删除‘‘‘ drop 删除表、字段 delete 删除数据 alter table 表名 drop 字段名； #删除字段 eg：alter table tf1 drop x； #删除x字段
二、多表关系
什么是外键？
? 建立多表关联的时候，通常一个表的外键另是一个表的主键(唯一键也可以)
一对一：学生表-学生简介表（一个学生只对应一个学生简介，一个学生简介也只对应一个学生）
一对多： 国家表-学生表 （一个学生只有一个国家，一个国家可以有多个学生）
多对多：学生表-课程表（一个学生可以选择多门课程，一门课程也可以有多个学生来上）
2.1 一对一
无联级关系：学生-学生简介
‘‘‘学生简介表‘‘‘ # stu_msg:id stu_msg create table student_msg( id int primary key auto_increment, #id为主键且自增 stu_msg varchar(256)); ‘‘‘学生表‘‘‘ #student:id name age msg_id create table student( id int primary key auto_increment, #id为主键且自增 name varchar(64) not null, #name不为空 age int not null, #age不为空 msg_id int unique not null, #设置简介id，一定要设置为唯一键不为空，这样才能实现一对一 foreign key(msg_id) references student_msg(id) # msg_id 为student表的外键，关联的学生信息表的id ); ------------------------------------------- 我们要先要创建学生详情表，再创建学生表 ***牢记：必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据 ‘‘‘插入表字段‘‘‘ insert into student_msg(stu_msg) values(‘student msg haha‘),(‘this is stu msg‘); +----+------------------+ | id | stu_msg | +----+------------------+ | 1 | student msg haha | | 2 | this is stu msg | +----+------------------+ insert into student(name,age,msg_id) values(‘yjy‘,21,1),(‘wwb‘,‘23‘,2); +----+------+-----+--------+ | id | name | age | msg_id | +----+------+-----+--------+ | 1 | yjy | 21 | 1 | | 2 | wwb | 23 | 2 | +----+------+-----+--------+ ‘‘‘删除表字段‘‘‘ delete from student_msg where id = 1; #删除不了，因为被student表关联着 delete from student where msg_id = 1; #直接就删除了 ***没有级联关系下***： # 增加：先增加被关联表记录，再增加关联表记录 # 删除：先删除关联表记录，再删除被关联表记录 # 更新：关联与，被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定，可以修改)
有联级关系：学生-学生简介
‘‘‘学生简介表‘‘‘ # stu_msg:id stu_msg create table student_msg( id int primary key auto_increment, #id为主键且自增 stu_msg varchar(256)); ‘‘‘学生表‘‘‘ #student:id name age msg_id create table student( id int primary key auto_increment, #id为主键且自增 name varchar(64) not null, #name不为空 age int not null, #age不为空 msg_id int unique not null, #设置简介id，一定要设置为唯一键不为空，这样才能实现一对一 foreign key(msg_id) references student_msg(id) # msg_id 为student表的外键，关联的学生信息表的id on update cascade #联级更新 on delete cascade #联级删除 ); ***牢记：必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据 ‘‘‘修改关联表‘‘‘ update student set msg_id=3 where msg_id=2; # 失败，3详情不存在 update student set msg_id=1 where msg_id=2; # 失败，1详情已被关联 update student set msg_id=3 where msg_id=2; # 有未被其他数据关联的数据，就可以修改 ‘‘‘修改被关联表‘‘‘ update student_msg set id=10 where id=1; # 级联修改，同步关系关联表外键,学生信息表改了，学生表里面的msg_id也会变 ‘‘‘删除关联表‘‘‘ delete from student where msg_id=2;# 可以删除对被关联表无影响 ‘‘‘删除被关联表‘‘‘ mysql>: delete from student_msg where msg=10; # 直接删除，关联表会被级联删除
2.2 一对多
国家表-学生表
***一对多：外键必须放在多的一方，此时外键值不唯一，也就是说外键必须放在学生表
‘‘‘创建国家表‘‘‘ #country:id,name,address create table country( id int primary key auto_increment, name varchar(64) not null, address varchar(256)); +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | address | varchar(256) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ ‘‘‘创建学生表‘‘‘ #student：id，name,age,country_id create table student( id int primary key auto_increment, name varchar(64) not null, age int not null, country_id int, foreign key(country_id) references country(id) on update cascade on delete cascade ) +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | age | int(11) | NO | | NULL | | | country_id | int(11) | NO | MUL | NULL | | +------------+-------------+------+-----+---------+----------------+ ‘‘‘增‘‘‘ 先增加被关联表(country)的数据，再增加关联表(student)的数据 #插入被关联表(country)信息 insert into country(name,address) values(‘china‘,‘gansu‘),(‘canada‘,‘xixi‘),(‘china‘,‘shanghai‘); +----+--------+----------+ | id | name | address | +----+--------+----------+ | 1 | china | gansu | | 2 | canada | xixi | | 3 | china | shanghai | +----+--------+----------+ #插入关联表(student)信息 insert into student(name,age,country_id) values(‘yjy‘,‘21‘, 1),(‘wwb‘,‘23‘,3)； insert into student(name,age,country_id) values(‘hhh‘,‘21‘, 5)； # 失败 没有被关联的字段，插入依旧错误 ‘‘‘更新‘‘‘ #更新被关联表 update country set id=10 where id=1; *** 直接更新被关联表的(country) 主键，关联表(student) 外键 会级联更新，（这个就好比后台更新游戏，不影响用户的游戏体验） #更新关联表 update student set country=2 where id=3; # 成功 update student set country=4 where id=3; # 失败 ***直接更新关联表的(student) 外键，修改的值对应被关联表(country) 主键 如果存在，可以更新成功，反之失败 ‘‘‘删除‘‘‘ # 删被关联表，关联表会被级联删除 delete from country where id = 2; # 删关联表，被关联表不会发生变化 delete from student where country = 3;
2.3 多对多
学生表-课程表
***牢记***
多对多：一定要创建第三张表(关系表)，每一个外键值不唯一，看可以多个外键建立联合唯一
两张被关联表，没有前后关系，但关系表必须在两个表都提供数据后才能进行 关系匹配
#学生表：id name age create table student( id int primary key auto_increment, name char(4) not null, age int default 0 ); #课程表：id name create table course( id int primary key auto_increment, name varchar(64)); +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ #学生_课程关系表:id, c_id, s_id create table s_c( id int primary key auto_increment, #关联课程表 c_id int, foreign key(c_id) references course(id) on update cascade on delete cascade, # 关联学生表 s_id int, foreign key(s_id) references student(id) on update cascade on delete cascade, # 建立两个字段的联合唯一 unique(c_id, s_id) ); +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | c_id | int(11) | YES | MUL | NULL | | | s_id | int(11) | YES | MUL | NULL | | +-------+---------+------+-----+---------+----------------+ ‘‘‘注：关系表 关联着 学生表 和 课程表 两张表，在表结构上 学生表和课程表 两表之间的键 没有任何关系，和第三张表才有关系‘‘‘ ‘‘‘关系表操作：增、删、改，只要两张被关系表有提供对应的操作数据，都可以操作成功，且对两张被关系表没有影响‘‘‘ ‘‘‘增‘‘‘ #插入学生信息 insert into student(name,age) values(‘yjy‘,21),(‘wwb‘,‘23‘); +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | yjy | 21 | | 2 | wwb | 23 | +----+------+-----+ #插入课程信息 insert into course(name) values(‘语文‘),(‘数学‘),(‘英语‘); +----+--------+ | id | name | +----+--------+ | 1 | 语文 | | 2 | 数学 | | 3 | 英语 | +----+--------+ insert into course(name) values(‘体育‘); #不会影响关系表 #插入学生_课程信息 insert into s_c(c_id,s_id) values(1,1),(3,2),(2,1),(2,3); ‘‘‘改‘‘‘ update course set id=10 where id=1; #关系表都会级联更新 ‘‘‘删除‘‘‘ delete from student where name=‘wwb‘; #关系表都会级联删除
#创建一个tf1表，id为主键并且自增，x，y都为int型 create table tf1( id int primary key auto_increment, x int, y int); ‘‘‘修改‘‘‘ 1） eg：alter table tf1 modify x char(4) default ‘哈哈‘; #将x的数据类型设为char，并增加默认值‘哈哈‘ 2） eg：alter table tf1 change y m char(4) default ‘嘻嘻嘻‘; #将y的字段名修改为m,并将数据类型修改为char，并增加默认值 ‘‘‘增加‘‘‘ 1) alter table 表名 add 新字段 类型[长度] 约束 eg：alter table tf1 add z int unsigned; #末尾添加 2) alter table 表名 add 新字段 类型[长度] 约束 first； eg：alter table tf1 add m int unsigned first; #首位添加 3) alter table 表名 add 新字段 类型[长度] 约束 after 旧字段名； #某字段之后添加 eg：alter table tf1 add n int unsigned after x; #在x字段之后添加 ‘‘‘删除‘‘‘ drop 删除表、字段 delete 删除数据 alter table 表名 drop 字段名； #删除字段 eg：alter table tf1 drop x； #删除x字段
二、多表关系
什么是外键？
? 建立多表关联的时候，通常一个表的外键另是一个表的主键(唯一键也可以)
一对一：学生表-学生简介表（一个学生只对应一个学生简介，一个学生简介也只对应一个学生）
一对多： 国家表-学生表 （一个学生只有一个国家，一个国家可以有多个学生）
多对多：学生表-课程表（一个学生可以选择多门课程，一门课程也可以有多个学生来上）
2.1 一对一
无联级关系：学生-学生简介
‘‘‘学生简介表‘‘‘ # stu_msg:id stu_msg create table student_msg( id int primary key auto_increment, #id为主键且自增 stu_msg varchar(256)); ‘‘‘学生表‘‘‘ #student:id name age msg_id create table student( id int primary key auto_increment, #id为主键且自增 name varchar(64) not null, #name不为空 age int not null, #age不为空 msg_id int unique not null, #设置简介id，一定要设置为唯一键不为空，这样才能实现一对一 foreign key(msg_id) references student_msg(id) # msg_id 为student表的外键，关联的学生信息表的id ); ------------------------------------------- 我们要先要创建学生详情表，再创建学生表 ***牢记：必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据 ‘‘‘插入表字段‘‘‘ insert into student_msg(stu_msg) values(‘student msg haha‘),(‘this is stu msg‘); +----+------------------+ | id | stu_msg | +----+------------------+ | 1 | student msg haha | | 2 | this is stu msg | +----+------------------+ insert into student(name,age,msg_id) values(‘yjy‘,21,1),(‘wwb‘,‘23‘,2); +----+------+-----+--------+ | id | name | age | msg_id | +----+------+-----+--------+ | 1 | yjy | 21 | 1 | | 2 | wwb | 23 | 2 | +----+------+-----+--------+ ‘‘‘删除表字段‘‘‘ delete from student_msg where id = 1; #删除不了，因为被student表关联着 delete from student where msg_id = 1; #直接就删除了 ***没有级联关系下***： # 增加：先增加被关联表记录，再增加关联表记录 # 删除：先删除关联表记录，再删除被关联表记录 # 更新：关联与，被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定，可以修改)
有联级关系：学生-学生简介
‘‘‘学生简介表‘‘‘ # stu_msg:id stu_msg create table student_msg( id int primary key auto_increment, #id为主键且自增 stu_msg varchar(256)); ‘‘‘学生表‘‘‘ #student:id name age msg_id create table student( id int primary key auto_increment, #id为主键且自增 name varchar(64) not null, #name不为空 age int not null, #age不为空 msg_id int unique not null, #设置简介id，一定要设置为唯一键不为空，这样才能实现一对一 foreign key(msg_id) references student_msg(id) # msg_id 为student表的外键，关联的学生信息表的id on update cascade #联级更新 on delete cascade #联级删除 ); ***牢记：必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据 ‘‘‘修改关联表‘‘‘ update student set msg_id=3 where msg_id=2; # 失败，3详情不存在 update student set msg_id=1 where msg_id=2; # 失败，1详情已被关联 update student set msg_id=3 where msg_id=2; # 有未被其他数据关联的数据，就可以修改 ‘‘‘修改被关联表‘‘‘ update student_msg set id=10 where id=1; # 级联修改，同步关系关联表外键,学生信息表改了，学生表里面的msg_id也会变 ‘‘‘删除关联表‘‘‘ delete from student where msg_id=2;# 可以删除对被关联表无影响 ‘‘‘删除被关联表‘‘‘ mysql>: delete from student_msg where msg=10; # 直接删除，关联表会被级联删除
2.2 一对多
国家表-学生表
***一对多：外键必须放在多的一方，此时外键值不唯一，也就是说外键必须放在学生表
‘‘‘创建国家表‘‘‘ #country:id,name,address create table country( id int primary key auto_increment, name varchar(64) not null, address varchar(256)); +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | address | varchar(256) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ ‘‘‘创建学生表‘‘‘ #student：id，name,age,country_id create table student( id int primary key auto_increment, name varchar(64) not null, age int not null, country_id int, foreign key(country_id) references country(id) on update cascade on delete cascade ) +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | age | int(11) | NO | | NULL | | | country_id | int(11) | NO | MUL | NULL | | +------------+-------------+------+-----+---------+----------------+ ‘‘‘增‘‘‘ 先增加被关联表(country)的数据，再增加关联表(student)的数据 #插入被关联表(country)信息 insert into country(name,address) values(‘china‘,‘gansu‘),(‘canada‘,‘xixi‘),(‘china‘,‘shanghai‘); +----+--------+----------+ | id | name | address | +----+--------+----------+ | 1 | china | gansu | | 2 | canada | xixi | | 3 | china | shanghai | +----+--------+----------+ #插入关联表(student)信息 insert into student(name,age,country_id) values(‘yjy‘,‘21‘, 1),(‘wwb‘,‘23‘,3)； insert into student(name,age,country_id) values(‘hhh‘,‘21‘, 5)； # 失败 没有被关联的字段，插入依旧错误 ‘‘‘更新‘‘‘ #更新被关联表 update country set id=10 where id=1; *** 直接更新被关联表的(country) 主键，关联表(student) 外键 会级联更新，（这个就好比后台更新游戏，不影响用户的游戏体验） #更新关联表 update student set country=2 where id=3; # 成功 update student set country=4 where id=3; # 失败 ***直接更新关联表的(student) 外键，修改的值对应被关联表(country) 主键 如果存在，可以更新成功，反之失败 ‘‘‘删除‘‘‘ # 删被关联表，关联表会被级联删除 delete from country where id = 2; # 删关联表，被关联表不会发生变化 delete from student where country = 3;
2.3 多对多
学生表-课程表
***牢记***
多对多：一定要创建第三张表(关系表)，每一个外键值不唯一，看可以多个外键建立联合唯一
两张被关联表，没有前后关系，但关系表必须在两个表都提供数据后才能进行 关系匹配
#学生表：id name age create table student( id int primary key auto_increment, name char(4) not null, age int default 0 ); #课程表：id name create table course( id int primary key auto_increment, name varchar(64)); +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ #学生_课程关系表:id, c_id, s_id create table s_c( id int primary key auto_increment, #关联课程表 c_id int, foreign key(c_id) references course(id) on update cascade on delete cascade, # 关联学生表 s_id int, foreign key(s_id) references student(id) on update cascade on delete cascade, # 建立两个字段的联合唯一 unique(c_id, s_id) ); +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | c_id | int(11) | YES | MUL | NULL | | | s_id | int(11) | YES | MUL | NULL | | +-------+---------+------+-----+---------+----------------+ ‘‘‘注：关系表 关联着 学生表 和 课程表 两张表，在表结构上 学生表和课程表 两表之间的键 没有任何关系，和第三张表才有关系‘‘‘ ‘‘‘关系表操作：增、删、改，只要两张被关系表有提供对应的操作数据，都可以操作成功，且对两张被关系表没有影响‘‘‘ ‘‘‘增‘‘‘ #插入学生信息 insert into student(name,age) values(‘yjy‘,21),(‘wwb‘,‘23‘); +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | yjy | 21 | | 2 | wwb | 23 | +----+------+-----+ #插入课程信息 insert into course(name) values(‘语文‘),(‘数学‘),(‘英语‘); +----+--------+ | id | name | +----+--------+ | 1 | 语文 | | 2 | 数学 | | 3 | 英语 | +----+--------+ insert into course(name) values(‘体育‘); #不会影响关系表 #插入学生_课程信息 insert into s_c(c_id,s_id) values(1,1),(3,2),(2,1),(2,3); ‘‘‘改‘‘‘ update course set id=10 where id=1; #关系表都会级联更新 ‘‘‘删除‘‘‘ delete from student where name=‘wwb‘; #关系表都会级联删除
#创建一个tf1表，id为主键并且自增，x，y都为int型 create table tf1( id int primary key auto_increment, x int, y int); ‘‘‘修改‘‘‘ 1） eg：alter table tf1 modify x char(4) default ‘哈哈‘; #将x的数据类型设为char，并增加默认值‘哈哈‘ 2） eg：alter table tf1 change y m char(4) default ‘嘻嘻嘻‘; #将y的字段名修改为m,并将数据类型修改为char，并增加默认值 ‘‘‘增加‘‘‘ 1) alter table 表名 add 新字段 类型[长度] 约束 eg：alter table tf1 add z int unsigned; #末尾添加 2) alter table 表名 add 新字段 类型[长度] 约束 first； eg：alter table tf1 add m int unsigned first; #首位添加 3) alter table 表名 add 新字段 类型[长度] 约束 after 旧字段名； #某字段之后添加 eg：alter table tf1 add n int unsigned after x; #在x字段之后添加 ‘‘‘删除‘‘‘ drop 删除表、字段 delete 删除数据 alter table 表名 drop 字段名； #删除字段 eg：alter table tf1 drop x； #删除x字段
二、多表关系
什么是外键？
? 建立多表关联的时候，通常一个表的外键另是一个表的主键(唯一键也可以)
一对一：学生表-学生简介表（一个学生只对应一个学生简介，一个学生简介也只对应一个学生）
一对多： 国家表-学生表 （一个学生只有一个国家，一个国家可以有多个学生）
多对多：学生表-课程表（一个学生可以选择多门课程，一门课程也可以有多个学生来上）
2.1 一对一
无联级关系：学生-学生简介
‘‘‘学生简介表‘‘‘ # stu_msg:id stu_msg create table student_msg( id int primary key auto_increment, #id为主键且自增 stu_msg varchar(256)); ‘‘‘学生表‘‘‘ #student:id name age msg_id create table student( id int primary key auto_increment, #id为主键且自增 name varchar(64) not null, #name不为空 age int not null, #age不为空 msg_id int unique not null, #设置简介id，一定要设置为唯一键不为空，这样才能实现一对一 foreign key(msg_id) references student_msg(id) # msg_id 为student表的外键，关联的学生信息表的id ); ------------------------------------------- 我们要先要创建学生详情表，再创建学生表 ***牢记：必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据 ‘‘‘插入表字段‘‘‘ insert into student_msg(stu_msg) values(‘student msg haha‘),(‘this is stu msg‘); +----+------------------+ | id | stu_msg | +----+------------------+ | 1 | student msg haha | | 2 | this is stu msg | +----+------------------+ insert into student(name,age,msg_id) values(‘yjy‘,21,1),(‘wwb‘,‘23‘,2); +----+------+-----+--------+ | id | name | age | msg_id | +----+------+-----+--------+ | 1 | yjy | 21 | 1 | | 2 | wwb | 23 | 2 | +----+------+-----+--------+ ‘‘‘删除表字段‘‘‘ delete from student_msg where id = 1; #删除不了，因为被student表关联着 delete from student where msg_id = 1; #直接就删除了 ***没有级联关系下***： # 增加：先增加被关联表记录，再增加关联表记录 # 删除：先删除关联表记录，再删除被关联表记录 # 更新：关联与，被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定，可以修改)
有联级关系：学生-学生简介
‘‘‘学生简介表‘‘‘ # stu_msg:id stu_msg create table student_msg( id int primary key auto_increment, #id为主键且自增 stu_msg varchar(256)); ‘‘‘学生表‘‘‘ #student:id name age msg_id create table student( id int primary key auto_increment, #id为主键且自增 name varchar(64) not null, #name不为空 age int not null, #age不为空 msg_id int unique not null, #设置简介id，一定要设置为唯一键不为空，这样才能实现一对一 foreign key(msg_id) references student_msg(id) # msg_id 为student表的外键，关联的学生信息表的id on update cascade #联级更新 on delete cascade #联级删除 ); ***牢记：必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据 ‘‘‘修改关联表‘‘‘ update student set msg_id=3 where msg_id=2; # 失败，3详情不存在 update student set msg_id=1 where msg_id=2; # 失败，1详情已被关联 update student set msg_id=3 where msg_id=2; # 有未被其他数据关联的数据，就可以修改 ‘‘‘修改被关联表‘‘‘ update student_msg set id=10 where id=1; # 级联修改，同步关系关联表外键,学生信息表改了，学生表里面的msg_id也会变 ‘‘‘删除关联表‘‘‘ delete from student where msg_id=2;# 可以删除对被关联表无影响 ‘‘‘删除被关联表‘‘‘ mysql>: delete from student_msg where msg=10; # 直接删除，关联表会被级联删除
2.2 一对多
国家表-学生表
***一对多：外键必须放在多的一方，此时外键值不唯一，也就是说外键必须放在学生表
‘‘‘创建国家表‘‘‘ #country:id,name,address create table country( id int primary key auto_increment, name varchar(64) not null, address varchar(256)); +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | address | varchar(256) | YES | | NULL | | +---------+--------------+------+-----+---------+----------------+ ‘‘‘创建学生表‘‘‘ #student：id，name,age,country_id create table student( id int primary key auto_increment, name varchar(64) not null, age int not null, country_id int, foreign key(country_id) references country(id) on update cascade on delete cascade ) +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | age | int(11) | NO | | NULL | | | country_id | int(11) | NO | MUL | NULL | | +------------+-------------+------+-----+---------+----------------+ ‘‘‘增‘‘‘ 先增加被关联表(country)的数据，再增加关联表(student)的数据 #插入被关联表(country)信息 insert into country(name,address) values(‘china‘,‘gansu‘),(‘canada‘,‘xixi‘),(‘china‘,‘shanghai‘); +----+--------+----------+ | id | name | address | +----+--------+----------+ | 1 | china | gansu | | 2 | canada | xixi | | 3 | china | shanghai | +----+--------+----------+ #插入关联表(student)信息 insert into student(name,age,country_id) values(‘yjy‘,‘21‘, 1),(‘wwb‘,‘23‘,3)； insert into student(name,age,country_id) values(‘hhh‘,‘21‘, 5)； # 失败 没有被关联的字段，插入依旧错误 ‘‘‘更新‘‘‘ #更新被关联表 update country set id=10 where id=1; *** 直接更新被关联表的(country) 主键，关联表(student) 外键 会级联更新，（这个就好比后台更新游戏，不影响用户的游戏体验） #更新关联表 update student set country=2 where id=3; # 成功 update student set country=4 where id=3; # 失败 ***直接更新关联表的(student) 外键，修改的值对应被关联表(country) 主键 如果存在，可以更新成功，反之失败 ‘‘‘删除‘‘‘ # 删被关联表，关联表会被级联删除 delete from country where id = 2; # 删关联表，被关联表不会发生变化 delete from student where country = 3;
2.3 多对多
学生表-课程表
***牢记***
多对多：一定要创建第三张表(关系表)，每一个外键值不唯一，看可以多个外键建立联合唯一
两张被关联表，没有前后关系，但关系表必须在两个表都提供数据后才能进行 关系匹配
#学生表：id name age create table student( id int primary key auto_increment, name char(4) not null, age int default 0 ); #课程表：id name create table course( id int primary key auto_increment, name varchar(64)); +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ #学生_课程关系表:id, c_id, s_id create table s_c( id int primary key auto_increment, #关联课程表 c_id int, foreign key(c_id) references course(id) on update cascade on delete cascade, # 关联学生表 s_id int, foreign key(s_id) references student(id) on update cascade on delete cascade, # 建立两个字段的联合唯一 unique(c_id, s_id) ); +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | c_id | int(11) | YES | MUL | NULL | | | s_id | int(11) | YES | MUL | NULL | | +-------+---------+------+-----+---------+----------------+ ‘‘‘注：关系表 关联着 学生表 和 课程表 两张表，在表结构上 学生表和课程表 两表之间的键 没有任何关系，和第三张表才有关系‘‘‘ ‘‘‘关系表操作：增、删、改，只要两张被关系表有提供对应的操作数据，都可以操作成功，且对两张被关系表没有影响‘‘‘ ‘‘‘增‘‘‘ #插入学生信息 insert into student(name,age) values(‘yjy‘,21),(‘wwb‘,‘23‘); +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | yjy | 21 | | 2 | wwb | 23 | +----+------+-----+ #插入课程信息 insert into course(name) values(‘语文‘),(‘数学‘),(‘英语‘); +----+--------+ | id | name | +----+--------+ | 1 | 语文 | | 2 | 数学 | | 3 | 英语 | +----+--------+ insert into course(name) values(‘体育‘); #不会影响关系表 #插入学生_课程信息 insert into s_c(c_id,s_id) values(1,1),(3,2),(2,1),(2,3); ‘‘‘改‘‘‘ update course set id=10 where id=1; #关系表都会级联更新 ‘‘‘删除‘‘‘ delete from student where name=‘wwb‘; #关系表都会级联删除