python数据库之完整性约束

SDUTACM 2020-05-05

阅读目录

一、介绍

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性
主要分为：

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT                  为该字段设置默认值

UNSIGNED                无符号
ZEROFILL                  使用0填充

说明：

1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum(‘male‘,‘female‘) not null default ‘male‘
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

二、not null与default

是否可空，null表示空，非字符串
not null - 不可空
null - 可空


default默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值（注意，varchar类型default要加""双引号）

语法：
create table 表名(
                    字段名 字段类型 defalut 默认值,
                    字段名 字段类型
);

代码示例：

==================not null====================
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into t1 values();         #可以插入空
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>


mysql> create table t2(id int not null);            #设置字段id不为空
Query OK, 0 rows affected (0.01 sec)

mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into t2 values();          #不能插入空
ERROR 1364 (HY000): Field ‘id‘ doesn‘t have a default value
mysql>


==================default====================
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
mysql> create table t3(id int default 1);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t4(id int not null default 3);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | 1       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> desc t4;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | 3       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> insert into t3 values();
Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t4;
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

mysql>

==================综合练习====================
mysql> create table student(
    -> id int not null,
    -> name varchar(20) not null,
    -> age int unsigned not null default 18,
    -> sex enum("male","female") default "male",
    -> hobby set("play","study","read","music") default "play,music"
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| id    | int(11)                            | NO   |     | NULL       |       |
| name  | varchar(20)                        | NO   |     | NULL       |       |
| age   | int(10) unsigned                   | NO   |     | 18         |       |
| sex   | enum(‘male‘,‘female‘)              | YES  |     | male       |       |
| hobby | set(‘play‘,‘study‘,‘read‘,‘music‘) | YES  |     | play,music |       |
+-------+------------------------------------+------+-----+------------+-------+
5 rows in set (0.01 sec)

mysql> insert into student(id,name) values(1,"egon");
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+-----+------+------------+
| id | name | age | sex  | hobby      |
+----+------+-----+------+------------+
|  1 | egon |  18 | male | play,music |
+----+------+-----+------+------------+
1 row in set (0.00 sec)

mysql>

三、unique

#============设置唯一约束 UNIQUE===============
#单列唯一
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二：
使用关键字constraint:
#语法：
constraint 约束名 unique(字段名)
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)                        
);


mysql> insert into department1 values(1,‘IT‘,‘技术‘);
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,‘IT‘,‘技术‘);
ERROR 1062 (23000): Duplicate entry ‘IT‘ for key ‘name‘

#联合唯一
例如：socket传输的ip和port
"""
ip和port
单个都可以重复 但是加在一起必须是唯一的
"""
create table t4(
    id int,
    ip char(16),
    port int,
    unique(ip,port)
);
insert into t4 values(1,‘127.0.0.1‘,8080);
insert into t4 values(2,‘127.0.0.1‘,8081);
insert into t4 values(3,‘127.0.0.2‘,8080);
insert into t4 values(4,‘127.0.0.1‘,8080);  #报错
#ot null+unique的化学反应==>primary key主键的效果
mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
row in set (0.00 sec)

not null+unique的化学反应

四、primary key主键

单列主键

设置方法：

============单列做主键===============
#方法一：创建表时在某一个字段类型后用not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

#方法二：创建表时在某一个字段类型后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)

#方法三：创建表时在所有字段后单独使用constraint约束关键字定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

1.单单从约束效果上来看primary key等价于not null + unique (非空且唯一)

2.主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表（类似于书的目录），一张表中必须有且只有一个主键primary key。

  • 一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键

示例：

mysql> create table t1(
    -> id int,
    -> name varchar(10),
    -> age int not null unique,    #升级为主键
    -> addr varchar(32) not null unique   #不会升级为主键，被标识为单列唯一
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | NO   | PRI | NULL    |       |
| addr  | varchar(32) | NO   | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>
  • 如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它 就无法提升查询速度
  • 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键

联合主键

#==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> (‘172.16.45.10‘,‘3306‘,‘mysqld‘),
    -> (‘172.16.45.11‘,‘3306‘,‘mariadb‘)
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values (‘172.16.45.10‘,‘3306‘,‘nginx‘);
ERROR 1062 (23000): Duplicate entry ‘172.16.45.10-3306‘ for key ‘PRIMARY‘

总结：

以后在创建表的时候id字段一定要加primary key

五、auto_increment自增

约束字段为自动增长，被约束的字段必须同时被key约束（通常用在主键id上进行自增）

# 注意auto_increment通常都是加在主键上的 不能给普通字段加

create table t9(
    id int primary key auto_increment,
    name char(16),
    cid int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

示例代码：

#不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum(‘male‘,‘female‘) default ‘male‘
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum(‘male‘,‘female‘) | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> (‘egon‘),
    -> (‘alex‘)
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+


#也可以指定id
mysql> insert into student values(4,‘asb‘,‘female‘);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,‘wsb‘,‘female‘);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values(‘ysb‘);
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values(‘egon‘);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
+----+------+------+
1 row in set (0.00 sec)

总结：

"""
以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候
id int primary key auto_increment
"""

六、表与表之间建立关系：foreign key(外键)以及级联更新和级联删除

"""
表与表之间最多只有四种关系
    一对多关系
        在MySQL的关系中没有多对一一说
        一对多 多对一 都叫一对多！！！
    多对多关系
    一对一关系
    没有关系
"""

一对多关系

foreign key
一对多表关系   外键字段建在多的一方
在创建表的时候 一定要先建被关联表 
在录入数据的时候 也必须先录入被关联表

以员工表与部门表为例：

‘‘‘
先站在员工表
        思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)
            不能！！！
            (不能直接得出结论 一定要两张表都考虑完全)
    再站在部门表
        思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)
            能！！！
    得出结论
        员工表与部门表示单向的一对多
        所以表关系就是一对多
‘‘‘

一对多关系示例：

# SQL语句建立表关系
mysql> create table dep(
    -> id int primary key auto_increment,
    -> dep_name varchar(16),
    -> dep_desc varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table emp(
    -> id int primary key auto_increment,
    -> name varchar(16),
    -> gender enum(‘male‘,‘female‘,‘others‘) default ‘male‘,
    -> dep_id int,
    -> foreign key(dep_id) references dep(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into dep(dep_name,dep_desc) values(‘sb教学部‘,‘教书育人‘),(‘外交部‘,‘多人外交‘),(‘nb技术部‘,‘技术能力有限 部门‘);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into emp(name,dep_id) values(‘jason‘,2),(‘egon‘,1),(‘tank‘,1),(‘kevin‘,3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+----+-------------+--------------------------+
| id | dep_name    | dep_desc                 |
+----+-------------+--------------------------+
|  1 | sb教学部    | 教书育人                 |
|  2 | 外交部      | 多人外交                 |
|  3 | nb技术部    | 技术能力有限部门         |
+----+-------------+--------------------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      2 |
|  2 | egon  | male   |      1 |
|  3 | tank  | male   |      1 |
|  4 | kevin | male   |      3 |
+----+-------+--------+--------+
4 rows in set (0.00 sec)

mysql>

依据上面一对多关系示例：修改操作

# 修改dep表里面的id字段（发现报错，因为加了外键）
mysql> update dep set id=200 where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`userinfo_db`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

# 删除dep表里面的数据（发现报错，因为加了外键）
mysql> delete from dep;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`userinfo_db`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql>

#如果想要修改的话部门dep某一个id，要先讲emp员工表中有属于这个部门id的先删掉，或改到其他部门，再去修改dep中的id，例如想要修改部门dep中的id3改为300。
mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  2 | egon  | male   |      1 |
|  4 | kevin | male   |      3 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)

mysql> select * from dep;
+----+-------------+--------------------------+
| id | dep_name    | dep_desc                 |
+----+-------------+--------------------------+
|  1 | sb教学部    | 教书育人                 |
|  3 | nb技术部    | 技术能力有限部门         |
+----+-------------+--------------------------+
2 rows in set (0.00 sec)

mysql> update dep set id=300 where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`userinfo_db`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql> delete from emp where id=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;
+----+------+--------+--------+
| id | name | gender | dep_id |
+----+------+--------+--------+
|  2 | egon | male   |      1 |
+----+------+--------+--------+
1 row in set (0.00 sec)

mysql> update dep set id=300 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dep;
+-----+-------------+--------------------------+
| id  | dep_name    | dep_desc                 |
+-----+-------------+--------------------------+
|   1 | sb教学部    | 教书育人                 |
| 300 | nb技术部    | 技术能力有限部门         |
+-----+-------------+--------------------------+
2 rows in set (0.00 sec)

mysql>

总结：上面修改操作太过繁琐

真正做到数据之间有关系，级联更新和级联删除

更新就同步更新
删除就同步删除

"""
级联更新   >>>   同步更新
级联删除   >>>   同步删除
"""
mysql> create table dep(
    -> id int primary key auto_increment,
    ->     dep_name char(16),
    ->     dep_desc char(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table emp(
    -> id int primary key auto_increment,
    ->     name char(16),
    ->     gender enum(‘male‘,‘female‘,‘others‘) default ‘male‘,
    ->     dep_id int,
    ->     foreign key(dep_id) references dep(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into dep(dep_name,dep_desc) values(‘sb教学部‘,‘教书育人‘),(‘外交部‘,‘多人外交‘),(‘nb技术部‘,‘技术能力有限 部门‘);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into emp(name,dep_id) values(‘jason‘,2),(‘egon‘,1),(‘tank‘,1),(‘kevin‘,3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+----+-------------+--------------------------+
| id | dep_name    | dep_desc                 |
+----+-------------+--------------------------+
|  1 | sb教学部    | 教书育人                 |
|  2 | 外交部      | 多人外交                 |
|  3 | nb技术部    | 技术能力有限部门         |
+----+-------------+--------------------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      2 |
|  2 | egon  | male   |      1 |
|  3 | tank  | male   |      1 |
|  4 | kevin | male   |      3 |
+----+-------+--------+--------+
4 rows in set (0.00 sec)

mysql> update dep set id=300 where id=3;    #修改dep部门表中的id，emp中会同步更新
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dep;
+-----+-------------+--------------------------+
| id  | dep_name    | dep_desc                 |
+-----+-------------+--------------------------+
|   1 | sb教学部    | 教书育人                 |
|   2 | 外交部      | 多人外交                 |
| 300 | nb技术部    | 技术能力有限部门         |
+-----+-------------+--------------------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      2 |
|  2 | egon  | male   |      1 |
|  3 | tank  | male   |      1 |
|  4 | kevin | male   |    300 |
+----+-------+--------+--------+
4 rows in set (0.00 sec)

mysql>

多对多关系（通过建立中间表存放两张表数据之间的关系）

多对多关系举例：

"""
图书表和作者表
"""

如果还是通过一对多的方式建立这两张表（互相一对多想法）：

create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int,
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);
"""
#按照上述的方式创建 一个都别想成功！！！
#因为建立book表时，要先建立被关联表author，建立author表要先建立被关联表book，so，两个表一个也建不成。

多对多关系，我们通过建立中间表用来存储两张表数据之间的关系

注意：两个表本身并无关联，两个表和中间表有关联

mysql> create table book(
    -> id int primary key auto_increment,
    -> title varchar(32),
    -> price int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table author(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> age int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table book2author(
    -> id int primary key auto_increment,
    -> author_id int,
    -> book_id int,
    -> foreign key(author_id) references author(id)
    -> on update cascade     # 同步更新
    -> on delete cascade,     # 同步删除，注意这里有两个外键，中间用逗号隔开
    -> foreign key(book_id) references book(id)
    -> on update cascade     # 同步更新
    -> on delete cascade      # 同步删除
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into book(title,price) values("降龙十八掌",999),("九阴真经",888),("九阳神功",666);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into author(name,age) values("egon",23),("tank",19),("jason",18);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into book2author(author_id,book_id) values(1,1),(1,2),(2,2),(2,3),(3,3),(3,1);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from book;
+----+-----------------+-------+
| id | title           | price |
+----+-----------------+-------+
|  1 | 降龙十八掌      |   999 |
|  2 | 九阴真经        |   888 |
|  3 | 九阳神功        |   666 |
+----+-----------------+-------+
3 rows in set (0.00 sec)

mysql> select * from author;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | egon  |   23 |
|  2 | tank  |   19 |
|  3 | jason |   18 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> select * from book2author;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         2 |       2 |
|  4 |         2 |       3 |
|  5 |         3 |       3 |
|  6 |         3 |       1 |
+----+-----------+---------+
6 rows in set (0.00 sec)

mysql> update book set id=100 where id=1;     #修改book表中id
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from book;
+-----+-----------------+-------+
| id  | title           | price |
+-----+-----------------+-------+
|   2 | 九阴真经        |   888 |
|   3 | 九阳神功        |   666 |
| 100 | 降龙十八掌      |   999 |
+-----+-----------------+-------+
3 rows in set (0.00 sec)

mysql> select * from author;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | egon  |   23 |
|  2 | tank  |   19 |
|  3 | jason |   18 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> select * from book2author;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |     100 |
|  2 |         1 |       2 |
|  3 |         2 |       2 |
|  4 |         2 |       3 |
|  5 |         3 |       3 |
|  6 |         3 |     100 |
+----+-----------+---------+
6 rows in set (0.00 sec)

mysql>

一对一关系

"""
id name age addr phone hobby email........
如果一个表的字段特别多 每次查询又不是所有的字段都能用得到
将表一分为二  
    用户表
        用户表
            id name age
        用户详情表
            id addr phone hobby email........
    
    站在用户表
        一个用户能否对应多个用户详情   不能！！！
    站在详情表
        一个详情能否属于多个用户      不能！！！
    结论:单向的一对多都不成立 那么这个时候两者之间的表关系
        就是一对一
        或者没有关系(好判断)

客户表和学生表
    在你们报名之前你们是客户端
    报名之后是学生(期间有一些客户不会报名)
"""

一对一示例：

一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中
#作者详情表
create table authordetail(
    id int primary key auto_increment,
    phone int,
    addr varchar(64)
);
#作者信息表
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,    #作者详情id字段为什么要唯一，因为，如果不唯一，那么随便insert一个作者，作者详情都可以互串，不合理。每个作者只能对应一个详情
    foreign key(authordetail_id) references authordetail(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
)

总结：

"""
表关系的建立需要用到foreign key
    一对多
        外键字段建在多的一方
    多对多
        自己开设第三张存储
    一对一
        建在任意一方都可以 但是推荐你建在查询频率较高的表中

判断表之间关系的方式
    换位思考！！！
        员工与部门
    
        图书与作者
    
        作者与作者详情
"""
SDUTACM

SDUTACM

相关推荐

MySQL对字段的删除和添加

修改表名语法：alter table 旧表名 renmae 新表名； 下图客园看出表的名字有stey 改成了steys。

MissFuTT / 0评论 2020-06-25

Python学习第95天（MySQL数据表操作）

　　昨天已经完成了在数据库中创建库和创建表，下面将详细介绍一下，针对穿件完成后的database之后，我们use database，然后create table table_name（filed type1，。。。。。field1 type[完整性约束条件

勇往直前 / 0评论 2020-06-01

mysql增删改字段，重命名替换字段

ALTER TABLE 表 {ADD ADD{COLUMN 字段类型 [ (字长)] [NOT NULL] [CONSTRAINT 索引 ] |. field 指定在表内增加或删除的字段。size字段的字符长度。了解更多有关如何构造本索引的信息，请看

贤冰 / 0评论 2020-05-28

MySQL增删改查

　　　　　　 VALUES（值1，值2，…表示数据已经成功插入。若不指定字段名，则添加的值的顺序应和字段在表中的顺序完全一致。可见id为8的记录已被成功删除。

mycosmos / 0评论 2020-05-06

Mysql

ALTER TABLE demo02 ADD `password` VARCHAR NOT NULL;ALTER TABLE demo02 CHANGE `name` username VARCHAR NOT NULL. ALTER TABLE demo0

xiaojiang0 / 0评论 2020-04-25

mysql数据库

　　alter table 表名 drop column 字段名；　　alter table 旧表名 rename to 新表名；

zjx0 / 0评论 2020-04-21

MySQL - 操作表数据

-- 部分字段插入: insert into 表名 values ;

cyhgogogo / 0评论 2020-02-11

mysql中的alter子句修改表和表字段信息

MySQL中提供了alter子句用于修改表和表字段信息。使用alter子句修改表信息主要有两个用途。alter table yanggb comment ‘yanggb is 666‘;alter table yanggb modify column ya

mycosmos / 0评论 2020-01-25

mysql常用技巧

数据库是按照数据结构，存储和管理数据的仓库。关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等书序概念和方法来处理数据库中的数据。mysql 是最流行的关系型数据库管理系统。打开制定的数据库 use 库名;查看表结构 desc 表名;　　字符串

helencoder / 0评论 2020-01-23

mysql使用时间字段索引

索引可以提高查询效率，平时按照时间查询经常遇到，所以就会给时间添加索引，但是添加索引怎么用呢？SELECT * FROM test_table WHERE create_time >= str_to_date

achiverhai / 0评论 2019-12-30

MySQL第七课

ORDER BY 字段名1[ASC/DESC] ,字段名2[ASC/DESC];SELECT 字段名1，字段名2,....FROM 表名 LIMIT [OFFSET,] 记录数；ALTER TABLE 表名 ADD CONSTRAINT FK_ID fo

贤冰 / 0评论 2020-01-02

mysql第二天

alter table users change 旧字段 新字段 类型(长度);index 发现创建索引有两种方式 1.alter table 2.create index ?alter table users id int unsigned not nu

azhou / 0评论 2020-01-09

Mysql常用命令

alter table 表名 drop 字段名;insert into 表名 values (...) 或者 insert into 表名 values

tanyhuan / 0评论 2020-01-08

MySQL——添加、更新与删除数据

通常情况下，向数据表中添加的新记录应该包含表所有字段，即为该表中的所有字段添加数据，为表中所有字段添加数据的INSERT语句有两种。INSERT INTO 表名(字段名1，字段名2，……在MySQL数据库中，还有一种方式可以用来删除表中所有的记录，这种方式

hevenue / 0评论 2020-01-02

mysql索引设计原则

一性索引的值是唯?一的，可以更更快速的通过该索引来确定某条记录。用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。立索引，可以提?目不不是越多越好。用磁盘空间，索引越多，需要的磁盘空间就越?用数据量量少的索引。经常需要order by、group

逍遥斩舞 / 0评论 2019-12-21

数据库学习之mysql数据库

MySQL Community Server（社区版）：该版本完全免费，但是官方不提供技术支持。MySQL Enterprise Server（企业版）：该版本能够以很高的性价比为企业提供数据仓库应用，支持 ACID 事物处理，提供完整的提交、回滚、崩溃恢

happinessaflower / 0评论 2019-12-16

字段约束，索引，主外键

mysql>alter table库.表执行动作；修改表名alter table源表名rename[to]新表名；设置在表中字段上的，作用是约束如何给字段赋值。例1：create index aaa on studb.t2;drop index索引名

翡翠谷 / 0评论 2019-12-10

MySQL_数据表命令

表的选项用于描述如何春促引擎、字符集等选项engine=存储引擎类型default charset=字符集类型。modify 字段名1 数据类型 after 字段名;show tables;show tables from 数据库名;

cyhgogogo / 0评论 2019-12-07

MySQL基本操作知识回顾

tinyint 0~255 ----年龄smallint ±32w ----员工数int ±21亿

林大鹏 / 0评论 2019-11-30

02 MySQL之数据表的基本操作

字段名1, 数据类型 [列级别约束条件] [默认值],主键约束要求主键列的数据唯一，并且不允许为空。主键分为两种类型：单子段主键和多字段联合主键。外键用来在两个表的数据之间建立链接，可以是一列或者多列。外键的作用是保持数据的一致性 和 完整性。一个表中可以

点滴技术生活 / 0评论 2019-11-12

数据表操作

数据表操作--1.创建表--基本语法 CREATE TABLE 表名[CHARACTER SET XXX];--创建一个员工表employeemysql>CREATE TABLE employee; /* 约束: PRIMARY KEY :能够唯一

txt / 0评论 2019-11-07

mysql比较常用到的一些操作

在服务器界面如: :~# 执行:mysqldump -u 用户名 -p 数据库名 数据表名 > 导出的文件名和路径。show index from 数据库表名;ALTER TABLE 数据库表名 ADD 添加的字段名称 添加的字段类型(长度);

wensonlee / 0评论 2019-11-02

mysql对字段的操作

　　alter　table　表名　add　字段名+数据类型,　add　字段名+数据类型;　　alter　table　表名　modify　字段+新的数据类型　after　id;

阿亮 / 0评论 2019-11-01

mysql总结

: limit (n-1)*数量 ,数量四、多表联合查询

阿亮 / 0评论 2019-10-29

数据表的基本操作

字段名n 数据类型 [ 约束条件 ] ）;2.查看数据表在MySQL中，查看数据表的方式有3种，具体如下：.查看数据库中所有的数据表。在MySQL中，删除字段的基本语法格式如下所示： ALTER TABLE 表名 DROP 字段名 ;

txt / 0评论 2019-10-27

mysql学习记录

　　show create table 表名;　　‘prod_id‘ varchar not null,千万不要空着它！　　ALTER TABLE Mall_products MODIFY ID varchar;ALTER TABLE Mall_produ

tangjianft / 0评论 2019-10-25

MySql-增删改查语句

02.连接命令：mysql -h[主机地址] -u[用户名] -p[用户密码] 03.创建数据库：create database [库名] 04.显示所有数据库: show databases; 05.打开数据库:use [库名] 06.当前选

tangjianft / 0评论 2019-10-25

MySQL数据库增删改查等常用命令介绍

MySQL可以说是最常用的小型数据库，加上现在越来越流行的分布式架构，哪怕是一般的中大型项目也可以用MySQL来进行部署。数据库的操作最常用的就是增删改查，还有一些切换数据库等操作。以下命令不加说明的，均是MySQL模式下命令，命令行模式下的命令要设置好系

tangjianft / 0评论 2019-10-25

MySQL数据表合并（两表字段相同）以及数据去重(抄)

统计重复数据select count(*) as repeat_count,name from test_01 group by name having repeat_count > 1;1. 使用DISTINCT关键字过滤重复数据select di

阿亮 / 0评论 2019-10-24

数据库设计经验分享

避免冗余字段 千万不要认为冗余字段能够使数据表更有弹性、更灵活。事实上修改表结构仅需几分钟，真正的麻烦还是来自于为新字段添加相应的业务逻辑。同时，唯一索引的用途前面提到了，恰当的使用它们，避免意外的发生。事务、触发器与存储过程 哦，事务处理其实是程序中要做

冷月醉雪 / 0评论 2015-04-28

【巨杉数据库Sequoiadb】给表增加字段，设置默认值无效

MySQL关联SequoiaDB，给表添加字段并设置字段默认值，完成后查看表中字段默认值不生效，附件截图。 因当前版本(3.0) sequoiasql-mysql对alter支持不够完善，可暂时通过以下方法规避此问题： 1.将默认值设置为NULL，如:

ZoeYen / 0评论 2019-09-07

MySQL数据库、数据表的基本操作及查询数据

数据库存储引擎数据库存储引擎是数据库底层软件组成，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。存储引擎比较|功能|MyISAM|Memory|InnoDB|Archive||---|---|---|---|---||存储限制|256TB|

crazybrick / 0评论 2019-03-31

mysql常用数据库操作命令总结

往指定位置后面插入字段：alter table 表名 add 字段名 数据类型 after 指定的字段名。模糊查询：select * from 表名 where 列名 like'值' 值：%a% a% %a. 排序与受

康慧欣 / 0评论 2019-07-31

mysql sql语句（一）

alter table service_testcase add concurrency varchar not null ; 该字段不可为空

shuirongc / 0评论 2015-10-23

MySQL数据库添加一个字段

2、添加一个字段设置默认值

Gexrior / 0评论 2014-03-14

Mysql的基本语句操作

本篇文章主要介绍一下Mysql的基本操作。创建数据库 ->create database + 数据库名称查看数据库 ->show databases删除数据库 ->drop database + 数据库名称使用数据库 -&

lsg00 / 0评论 2019-06-30

常用的Mysql语句

查询指定字段的数据:select字段1,字段2fromtable_name;Order语句是查询的顺序,如:orderbyiddesc,顺序有两种:desc倒序,asc，Where和order语句也可用于查询select与删除delete

hithyc / 0评论 2011-07-23

关于SQlite

SQLite，是一款轻型的数据库，是遵守ACID的关联式数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流

yongyoumengxiang / 0评论 2012-07-08

Mysql基本操作

添加数据:Insertinto表名[(字段1,字段2,…

蚂蚁爱吃糖 / 0评论 2011-08-01

关系型数据库（Oracle与MySQL优缺点、使用区别）

把所有出现的一个单引号替换成两个单引号。

九天银河技术 / 0评论 2019-01-17

SpringData JPA 在解析实体类字段时驼峰自动添加下划线问题

用下划线转换驼峰名，如 authorId ，转换成 author_id 。这样就遇到了一个问题：实体中驼峰命名的列名转换成下划线后，在MySQL中执行时找不到字段。

阿亮 / 0评论 2018-10-16

一个MySQL表真的最多只能够有24个字段吗？反正我不信

随着业务需求的不断变化，我们发现需要往视频数据表里面新增很多字段，如：是否需要购买才能够观看、是否VIP就可以看、是否免费、有几个人购买了等等，我们不断的往数据表里面添加各种各样的字段，最后发现字段越来越多，这个时候我们会担心是否会有性能问题，下面我们就围

chenxiangis / 0评论 2017-04-26

MySQL常用语句

今天天气很好，大晴天，心情也好好的。就将MySQL常用的语句总结一下，记录在随笔里，也顺便分享分享。日后，这篇随笔我将会持续更新，作为我自己的MySQL语句大全。　　 字段名1，数据类型 [列级别约束条件] [默认值],两个地方添加：1、列约束条件位

debugjoker / 0评论 2017-03-02

MySQL和Oracle的添加字段的处理差别

昨天在微信群中有个朋友也是无意中问了一下，说数据库中的表字段想保持一种相对规范的顺序，怎么办？要知道Oracle中这个操作就比较纠结了，因为是按照追加的方式来处理的。没法在已有的字段1，字段2中间添加一个字段3。但是MySQL却可以，这个方面MySQL看起

aifeng / 0评论 2016-11-15

MySQL数据库InnoDB存储引擎在线加字段实现原理详解

腾讯互娱内部维护了一个MySQL分支，基于官方5.5.24，实现了类似于Oracle 11g的快速加字段功能，这个分支我们内部称为TMySQL。该功能通过扩展存储格式来实现，原理类似于Oracle 11g，以下介绍下其实现原理。需要在innodb中实现类似

helloworlddm / 0评论 2014-11-21

MySQL - 在线修改表结构

在使用MySQL的时候，经常需要修改表结构，修改方法也很简单，直接使用alter table进行修改。alter table table1 add comments varchar not Null;alter table table1 add id in

davidwma / 0评论 2019-04-26

Python SqlAlchemy动态添加数据表字段实例解析

本文研究的主要是Python SqlAlchemy动态添加数据表字段，具体如下。我们知道使用SqlAlchemy创建类来映射数据表，类属性等于数据库字段，但有时候要在我们创建表的时候，不确定数据表字段数量，遇到这种情况，应如何解决？可以使用SqlAlche

mywpython / 0评论 2018-02-07

有关 MySQL InnoDB 在索引中自动添加主键的问题

但是这个自动添加的字段、Server 层是不知道的、所以 MySQL 优化器并不知道这个字段的存在、那么如果你有一个查询：。所以最终 MySQL优化器 可能选择 idx2(d,b) 做过滤然后排序 a 字段、或者直接用PK扫描避免排序。而如果我们定义表结构

weiguoxin / 0评论 2013-05-07

通过数据库向Django模型添加字段的示例

策略就是现在数据库里加入字段，然后同步Django的模型以包含新字段。然而 这里有一个鸡生蛋蛋生鸡的问题 ,由于要想了解新增列的SQL语句，你需要使用Django的 manage.py sqlall命令进行查看 ,而这又需要字段已经在模型里存在了。重新启动

load00 / 0评论 2019-04-21