Mysql 数据库（四）

一表于表之间的关联

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

posted @ 2017-10-25 19:25 陌文欲阅读(119) 评论(0) 编辑收藏举报

刷新页面返回顶部

bsxq

Mysql 数据库（四）

公告