重修课程day40(mysql四之表与表关联和表操作)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了重修课程day40(mysql四之表与表关联和表操作),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含19863字,纯文字阅读大概需要29分钟。
内容图文
一 表于表之间的关联
foregin key:设置外键表于表之间建立关联。
多对一关联:
创建步骤,应该先创建好被关联的那一张表,然后再去创建关联的那一张表。
关联表的多条对应着被关联的那张表的一条记录,而被关联的那一张表的多条记录不能对于着关联表的一条记录。属于单向关联
一对一关联:
创建步骤:还是先创建被关联的那一张表,然后在去创建关联的那一张表。
在多对一的基础上加上一条唯一的约束,关联表只能对应被关联表的一条记录。被关联表也只能对于关联表的一条记录
多对多关联:
创建步骤:首先先要创建出两张需要关联的表,然后在用第三张表将它们关联起来
关联表的多条记录对应着被关联表的一条记录,而被关联表的多条记录也同时对应着关联表的一条记录。
foregin key(关联的字段1) references 需要关联的那张表表名(关联的字段2):表名后面加的一般都是表的主键。
使用的好处是:限制了关联表的外键的传入的值,只能在被关联表的主键范围内。
坏处是:如果被关联表的主键关联上关联表的外键,就不能随便的删除和修改表关联表里面的数据。
解决方案1:先将关联表相对应的记录给删除掉,这样才能够对被关联表进行删除和修改。这样做是非常麻烦的,以为不知到有多少条记录相对应,这样删除的话是非常麻烦的,不建议用。
解决方案2:在关联上被关联表之后,后面加上:
on delete cascade:如果被关联表的该条数据删除掉,那么关联表与该记录相对应的记录也会变删除。
on update cascade:如果被关联表的该条数据被修改,那么关联表与该记录相对应的记录也会被修改。
#先创建被关联的表 create table dep( id int primary key auto_increment, dep_name char(20) not null unique, dep_comment varchar(50) )auto_increment=200; insert into dep(dep_name,dep_comment) values (‘IT‘,‘xxxxxxxxxx‘), (‘Sale‘,‘yhyyyyyyy‘), (‘Operation‘,‘asdfadfadsf‘), (‘HR‘,‘asfasdfasdfasdfasdf‘) ; #再创表去关联上面的表 create table emp( id int primary key auto_increment, name char(6) not null, sex enum(‘male‘,‘female‘) not null default ‘male‘, dep_id int, foreign key(dep_id) references dep(id) on delete cascade on update cascade ); insert into emp(name,sex,dep_id) values (‘egon‘,‘male‘,200), (‘alex‘,‘male‘,200), (‘yh‘,‘female‘,203), (‘evia‘,‘female‘,200), (‘wpq‘,‘male‘,202) ; insert into emp(name,sex,dep_id) values (‘alex1‘,‘male‘,250); #解散一个部门 #未指定同步更新、同步删除的参数时,需要这么删除 delete from emp where dep_id=200; delete from dep where id=200; #指定后 mysql> select * from dep; +-----+-----------+---------------------+ | id | dep_name | dep_comment | +-----+-----------+---------------------+ | 200 | IT | xxxxxxxxxx | | 201 | Sale | yhyyyyyyy | | 202 | Operation | asdfadfadsf | | 203 | HR | asfasdfasdfasdfasdf | +-----+-----------+---------------------+ 4 rows in set (0.00 sec) mysql> select * from emp; +----+------+--------+--------+ | id | name | sex | dep_id | +----+------+--------+--------+ | 1 | egon | male | 200 | | 2 | alex | male | 200 | | 3 | yh | female | 203 | | 4 | evia | female | 200 | | 5 | wpq | male | 202 | +----+------+--------+--------+ 5 rows in set (0.00 sec) mysql> delete from dep where id=200; Query OK, 1 row affected (0.06 sec) mysql> select * from emp; +----+------+--------+--------+ | id | name | sex | dep_id | +----+------+--------+--------+ | 3 | yh | female | 203 | | 5 | wpq | male | 202 | +----+------+--------+--------+ 2 rows in set (0.00 sec) mysql> select * from dep; +-----+-----------+---------------------+ | id | dep_name | dep_comment | +-----+-----------+---------------------+ | 201 | Sale | yhyyyyyyy | | 202 | Operation | asdfadfadsf | | 203 | HR | asfasdfasdfasdfasdf | +-----+-----------+---------------------+ 3 rows in set (0.00 sec) mysql> select * from emp; +----+------+--------+--------+ | id | name | sex | dep_id | +----+------+--------+--------+ | 3 | yh | female | 203 | | 5 | wpq | male | 202 | +----+------+--------+--------+ 2 rows in set (0.00 sec) mysql> update dep set id=2002 where id=202; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dep; +------+-----------+---------------------+ | id | dep_name | dep_comment | +------+-----------+---------------------+ | 201 | Sale | yhyyyyyyy | | 203 | HR | asfasdfasdfasdfasdf | | 2002 | Operation | asdfadfadsf | +------+-----------+---------------------+ 3 rows in set (0.00 sec) mysql> select * from emp; +----+------+--------+--------+ | id | name | sex | dep_id | +----+------+--------+--------+ | 3 | yh | female | 203 | | 5 | wpq | male | 2002 | +----+------+--------+--------+ 2 rows in set (0.00 sec) #多对多的创建方法: mysql> use DAY43; Database changed mysql> CREATe table user_1(id int primary key auto_increment, -> name char(10) not null, -> password char(30) not null); Query OK, 0 rows affected (0.28 sec) mysql> mysql> insert into user_1(name,password) values(‘fang‘,‘dsads454234‘), -> (‘jie‘,‘dsfsfd65465‘), -> (‘yi‘,‘dfsdf43543‘), -> (‘dong‘,‘dsfdsh5445‘); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> mysql> create table role_1(id int PRIMARY key auto_increment, -> name char(10) not null, -> quanxian char(3) not null); Query OK, 0 rows affected (0.25 sec) mysql> mysql> insert into role_1(name,quanxian)values(‘gl‘,‘rwx‘),(‘r1‘,‘rw‘),(‘r2‘,‘rx‘),(‘r3‘,‘r‘); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> mysql> create table user_role_1(id int primary key auto_increment, -> user_id int, -> foreign key(user_id) references user(id) -> on update cascade on delete CASCADE , -> role_id int, -> foreign key(role_id) references role(id) -> on UPDATE cascade on delete CASCADE); Query OK, 0 rows affected (0.24 sec) mysql> mysql> mysql> insert into user_role(user_id,role_id)values(1,1),(1,2),(1,4),(2,3),(2,4),(3,2),(3,4),(4,1); Query OK, 8 rows affected (0.10 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> SELECT * from user_1; +----+------+-------------+ | id | name | password | +----+------+-------------+ | 1 | fang | dsads454234 | | 3 | jie | dsfsfd65465 | | 5 | yi | dfsdf43543 | | 7 | dong | dsfdsh5445 | +----+------+-------------+ 4 rows in set (0.00 sec) mysql> SELECT * from role_1; +----+------+----------+ | id | name | quanxian | +----+------+----------+ | 1 | gl | rwx | | 3 | r1 | rw | | 5 | r2 | rx | | 7 | r3 | r | +----+------+----------+ 4 rows in set (0.00 sec) mysql> SELECT * from user_role_1; Empty set (0.00 sec) mysql> desc user_role_1; +---------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | YES | MUL | NULL | | | role_id | int(11) | YES | MUL | NULL | | +---------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> show create table user_role_1; +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user_role_1 | CREATE TABLE `user_role_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `role_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `role_id` (`role_id`), CONSTRAINT `user_role_1_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `user_role_1_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 一对一的创建方法: mysql> use day43; Database changed mysql> create table customer_1(id int primary key auto_increment, -> name char(10) not null, -> phone char(12) not null unique, -> qq char(10) UNIQUE); Query OK, 0 rows affected (0.33 sec) mysql> mysql> mysql> insert into customer_1(name,phone,qq)values(‘fang‘,‘15435789257‘,‘1343578983‘), -> (‘yan‘,‘15578983325‘,‘4357898332‘), -> (‘dong‘,‘14757898337‘,‘475785832‘), -> (‘jie‘,‘1557858857‘,‘57875782‘), -> (‘lei‘,‘1555785257‘,‘78525783‘), -> (‘yi‘,‘15590034557‘,‘13468550‘), -> (‘haiyan‘,‘1853387597‘,‘65442365‘), -> (‘hui‘,‘15934233557‘,‘932923743‘), -> (‘jia‘,‘15256784455‘,‘426557824‘); Query OK, 9 rows affected (0.05 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> mysql> create table student_1(id int primary key auto_increment, -> name char(10) not null, -> age int not null, -> sex enum(‘male‘,‘female‘) not null default ‘male‘, -> c_id int,foreign key(c_id) references customer(id) -> on update cascade on delete cascade); Query OK, 0 rows affected (0.23 sec) mysql> mysql> mysql> insert into student_1(name,age,sex,c_id)VALUES(‘fang‘,18,‘male‘,1), -> (‘hui‘,17,‘female‘,8), -> (‘dong‘,22,‘male‘,3), -> (‘yan‘,20,‘female‘,2), -> (‘haiyan‘,18,‘female‘,7), -> (‘jie‘,21,‘male‘,4), -> (‘lei‘,21,‘male‘,5), -> (‘jia‘,19,‘female‘,8); Query OK, 8 rows affected (0.03 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * from customer_1; +----+--------+-------------+------------+ | id | name | phone | qq | +----+--------+-------------+------------+ | 1 | fang | 15435789257 | 1343578983 | | 3 | yan | 15578983325 | 4357898332 | | 5 | dong | 14757898337 | 475785832 | | 7 | jie | 1557858857 | 57875782 | | 9 | lei | 1555785257 | 78525783 | | 11 | yi | 15590034557 | 13468550 | | 13 | haiyan | 1853387597 | 65442365 | | 15 | hui | 15934233557 | 932923743 | | 17 | jia | 15256784455 | 426557824 | +----+--------+-------------+------------+ 9 rows in set (0.00 sec) mysql> SELECT * from student_1; +----+--------+-----+--------+------+ | id | name | age | sex | c_id | +----+--------+-----+--------+------+ | 1 | fang | 18 | male | 1 | | 3 | hui | 17 | female | 8 | | 5 | dong | 22 | male | 3 | | 7 | yan | 20 | female | 2 | | 9 | haiyan | 18 | female | 7 | | 11 | jie | 21 | male | 4 | | 13 | lei | 21 | male | 5 | | 15 | jia | 19 | female | 8 | +----+--------+-----+--------+------+ 8 rows in set (0.00 sec) mysql> show CREATE TABLE student_1; +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student_1 | CREATE TABLE `student_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(10) NOT NULL, `age` int(11) NOT NULL, `sex` enum(‘male‘,‘female‘) NOT NULL DEFAULT ‘male‘, `c_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c_id` (`c_id`), CONSTRAINT `student_1_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `customer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
二 表的操作
alter table 表名 rename 新表名:修改一个表名。
mysql> alter table t1 rename yi; Query OK, 0 rows affected mysql> show tables; +-----------------+ | Tables_in_day43 | +-----------------+ | customer | | customer_1 | | role | | role_1 | | student | | student_1 | | user | | user_1 | | user_role | | user_role_1 | | yi | +-----------------+ 11 rows in set
增减字段:
alter table 表名 add 字段 数据类型[约束条件],add 字段 数据类型[约束条件]; :同时添加几个字段
alter table 表名 add 字段 数据类型[约束条件] first; #将字段添加到第一个位置
alter table 表名 add 字段 数据类型[约束条件] after 字段名; :将字段添加到耨个字段的前面
mysql> create table t1(name char(5)); Query OK, 0 rows affected mysql> alter table t1 add age int(3),add sex char(6); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `name` char(5) DEFAULT NULL, `age` int(3) DEFAULT NULL, `sex` char(6) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set mysql> alter table t1 add id int primary key auto_increment first; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add class char(6) after age; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(5) DEFAULT NULL, `age` int(3) DEFAULT NULL, `class` char(6) DEFAULT NULL, `sex` char(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
删除字段:
alter table 表名 drop 字段名;
| t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(5) DEFAULT NULL, `age` int(3) DEFAULT NULL, `class` char(6) DEFAULT NULL, `sex` char(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set mysql> alter table t1 drop -> class; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(5) DEFAULT NULL, `age` int(3) DEFAULT NULL, `sex` char(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
修改字段
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
三 表的复制
create table 新的表名 select * from 旧表名; :复制表的结构和记录
create table 新的表名 select 字段名。。。 from 旧表名; :复制表结构和某些记录
create table 新的表名 select * from 旧表名 where 不成立条件; :不复制表的记录,只复制表的结构
复制是根据创建的新的表,里面的表结构和记录是另外一张表的查看的结果。
mysql> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(5) DEFAULT NULL, `age` int(3) DEFAULT NULL, `sex` char(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set mysql> insert into t1(name,age,sex)values(‘fang‘,18,‘male‘),(‘jie‘,19,‘male‘),(‘hai‘,17,‘female‘),(‘yan‘,17,‘female‘); Query OK, 4 rows affected Records: 4 Duplicates: 0 Warnings: 0 mysql> create table t2 select * from t1; Query OK, 4 rows affected Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t2; +----+------+-----+--------+ | id | name | age | sex | +----+------+-----+--------+ | 1 | fang | 18 | male | | 2 | jie | 19 | male | | 3 | hai | 17 | female | | 4 | yan | 17 | female | +----+------+-----+--------+ 4 rows in set mysql> create table t3 select name,sex from t1; Query OK, 4 rows affected Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t3; +------+--------+ | name | sex | +------+--------+ | fang | male | | jie | male | | hai | female | | yan | female | +------+--------+ 4 rows in set mysql> create table t4 select * from t1 where 1=2; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t4; Empty set mysql> show create table t4; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t4 | CREATE TABLE `t4` ( `id` int(11) NOT NULL DEFAULT ‘0‘, `name` char(5) DEFAULT NULL, `age` int(3) DEFAULT NULL, `sex` char(6) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
原文:http://www.cnblogs.com/fangjie0410/p/7725262.html
内容总结
以上是互联网集市为您收集整理的重修课程day40(mysql四之表与表关联和表操作)全部内容,希望文章能够帮你解决重修课程day40(mysql四之表与表关联和表操作)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。