表的完整性约束

阅读目录

　　not noll

　　unique

　　primary key

　　foreign key

概览

　　为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

　　约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

# NOT NULL ：非空约束，指定某列不能为空； 
# UNIQUE : 唯一约束，指定某列或者几列组合不能重复
# PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
# FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

NOT NULL

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

mysql> create table t12 (id int not null);
Query OK, 0 rows affected (0.02 sec)

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

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

#不能向id列插入空元素。 
mysql> insert into t12 values (null);
ERROR 1048 (23000): Column 'id' cannot be null

mysql> insert into t12 values (1);
Query OK, 1 row affected (0.01 sec)

not null示例

DEFAULT

我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

mysql> create table t13 (id1 int not null,id2 int not null default 222);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t13;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | NO   |     | NULL    |       |
| id2   | int(11) | NO   |     | 222     |       |
+-------+---------+------+-----+---------+-------+
rows in set (0.01 sec)

# 只向id1字段添加值，会发现id2字段会使用默认值填充
mysql> insert into t13 (id1) values (111);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
+-----+-----+
row in set (0.00 sec)

# id1字段不能为空，所以不能单独向id2字段填充值；
mysql> insert into t13 (id2) values (223);
ERROR 1364 (HY000): Field 'id1' doesn't have a default value

# 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值
mysql> insert into t13 (id1,id2) values (112,223);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
| 112 | 223 |
+-----+-----+
rows in set (0.00 sec)

not null + default 示例

设置严格模式：
    不支持对not null字段插入null值
    不支持对自增长字段插入”值
    不支持text字段有默认值

直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

配置文件添加(永久失效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

not null不生效

UNIQUE

唯一约束，指定某列或者几列组合不能重复

方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
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'

unique示例

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的结合

create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

联合唯一

PRIMARY KEY

主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。
主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。

1.单字段主键

============单列做主键===============
#方法一：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)

#方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
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)

# 方法四：给已经建成的表添加主键约束
mysql> create table department4(
    -> id int,
    -> name varchar(20),
    -> comment varchar(100));
Query OK, 0 rows affected (0.01 sec)

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

mysql> alter table department4 modify id int primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc department4;
+---------+--------------+------+-----+---------+-------+
| 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)

单字段主键

2.多字段主键

==================多列做主键================
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    |       |
+--------------+-------------+------+-----+---------+-------+
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'

多字段主键

AUTO_INCREMENT

约束字段为自动增长，被约束的字段必须同时被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 |
+----+------+------+
row in set (0.00 sec)

设置auto_increment

了解知识

#在创建完表后，修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male'
    -> );

mysql> alter table student auto_increment=3;

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

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

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

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8


#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;




#设置步长
sqlserver：自增步长
    基于表级别
    create table t1（
        id int。。。
    ）engine=innodb,auto_increment=2 步长=2 default charset=utf8

mysql自增的步长：
    show session variables like 'auto_inc%';
    
    #基于会话级别
    set session auth_increment_increment=2 #修改会话级别的步长

    #基于全局级别的
    set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）


#！！！注意了注意了注意了！！！
If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋
比如：设置auto_increment_offset=3，auto_increment_increment=2




mysql> set global auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set global auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_incre%'; #需要退出重新登录
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+



create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
mysql> select * from student;
+----+-------+------+
| id | name  | sex  |
+----+-------+------+
|  3 | egon1 | male |
|  8 | egon2 | male |
| 13 | egon3 | male |
+----+-------+------+

步长:auto_increment_increment,起始偏移量:auto_increment_offset

offset偏移量

FOREIKEY

多表：

假设我们要描述所有公司的员工，需要描述的属性有这些：工号姓名部门

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费

解决方法：我们完全可以定义一个部门表然后让员工信息表关联该表，如何关联，即foreign key

mysql> create table departments (dep_id int(4),dep_name varchar(11));
Query OK, 0 rows affected (0.02 sec)

mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

# 创建外键不成功
mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
ERROR 1215 (HY000): Cannot add foreign key 

# 设置dep_id非空，仍然不能成功创建外键
mysql> alter table departments modify dep_id int(4) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | NO   |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
ERROR 1215 (HY000): Cannot add foreign key constraint

# 当设置字段为unique唯一字段时，设置该字段为外键成功
mysql> alter table departments modify dep_id int(4) unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc departments;                                                                                                       +----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  | UNI | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
rows in set (0.01 sec)

mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
Query OK, 0 rows affected (0.02 sec)

创造外键的条件

#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

#dpt_id外键，关联父表（department主键id），同步更新，同步删除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
foreign key(dpt_id)
references department(id)
on delete cascade  # 级连删除
on update cascade # 级连更新
)engine=innodb;


#先往父表department中插入记录
insert into department values
(1,'教质部'),
(2,'技术部'),
(3,'人力资源部');


#再往子表employee中插入记录
insert into employee values
(1,'yuan',1),
(2,'nezha',2),
(3,'egon',2),
(4,'alex',2),
(5,'wusir',3),
(6,'李沁洋',3),
(7,'皮卡丘',3),
(8,'程咬金',3),
(9,'程咬银',3)
;


#删父表department，子表employee中对应的记录跟着删
mysql> delete from department where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      3 |
|  6 | 李沁洋    |      3 |
|  7 | 皮卡丘    |      3 |
|  8 | 程咬金    |      3 |
|  9 | 程咬银    |      3 |
+----+-----------+--------+
rows in set (0.00 sec)


#更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=2 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      2 |
|  6 | 李沁洋    |      2 |
|  7 | 皮卡丘    |      2 |
|  8 | 程咬金    |      2 |
|  9 | 程咬银    |      2 |
+----+-----------+--------+
rows in set (0.00 sec)

外键操作示例

. cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录 

   . set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null  

   . No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  

   . Restrict方式
同no action, 都是立即检查外键约束

   . Set default方式
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

on delete(了解)

总结:

# 什么是约束
# not null    不能为空的
# unique      唯一 = 不能重复
# primary key 主键 = 不能为空 且 不能重复
# foreign key 外键约束

# 为什么要约束
# 是因为一个表中的数据要想完整规范,就必须对一些字段有基础的约束
# 一般情况下,我们都会根据程序的需求个特点对数据库进行约束
# 在你的开发生涯中,你应该对数据有一个基础的判断

# 在mysql中不区分大小写
# 一个关键字 :NULL null关键字 表示 空(相当于PYTHON中的None)


# 创建表
# create table 表名 (字段名 数据类型(宽度) 约束条件,)

# 约束一 not null
# create table t (id int not null,
#                 sex enum('male','female') not null default 'male')

# 约束二 unique 唯一,允许多个字段为null
# 设置某一个字段的内容必须是唯一的
# create table t3 (id int unique);   # 你设置了唯一,就不能插入两个相同的内容 测试:除了null之外
# create table t3 (id int not null unique);  # 你设置了唯一+非空,就不能插入两个相同的内容,也不能插入NULL,就相当于设置了一个主键primary key
# create table t3 (id int, name char(12), unique(id),unique(name));
# create table t4 (pid int, pname char(12),ip char(15),port int,unique(ip,port));
# create table t4 (pid int, pname char(12),
                # ip char(15) not null,
                # port int not null ,
                # unique(ip,port));

# 记录每一个程序的信息
# pid   程序名     ip地址          端口号
# 111   pycharm
# 123   pycharm
# 234   mysqld    192.168.11.44    3306
# 234   mysqld    192.168.11.22    3306
# 235   kugou     192.168.11.44    8000

# 两个或者多个字段的内容 = 联合唯一

# 约束三
#     主键 每一张表只能有一个主键
#     primary key = not null + unique
# create table t5 (id int primary key);
# 能设置多个主键么 ? 不能
# create table t5 (id int primary key,name char(12) primary key);
#     这样直接的报错
# 能不能设置多个非空 + 唯一 ? 能
# create table t6 (id int not null unique,name char(12) not null unique);
#     但是第二个会自动的编程唯一的
# 联合主键
# create table t7 (pid int, pname char(12),
#                 ip char(15),
#                 port int,
#                 primary key(ip,port));

# auto_increment 自增
# create table t8 (id int unique auto_increment,name char(12));
# create table t9 (id int primary key auto_increment,name char(12));
# 对于自增id来说,删除数据并不会影响自增
# 设置为自增,用户最好不要自己插入这个字段

# 约束四 外键
# 部门id  部门名称 部门办公室号
# create table department (id int unique,dep_name char(20),dep_num int);
# alter table department modify id int unique;

# 员工id   name  年龄  工资  部门id(外键)
# create table employee (id int,name char(12),age int,salary int,dep_id int,
#                        foreign key(dep_id) references department(id));

# insert into department (id,dep_name) values
# (1,'教质部'),
# (2,'技术部'),
# (3,'人力资源部');


# 如果添加了外键约束,外键是employee(dep_id),那么employee(dep_id)和department (ide)都会受到约束
# update department set id = 3 where id = 2;


# create table employee2 (id int,name char(12),age int,salary int,dep_id int,
#                        foreign key(dep_id) references department(id) on delete cascade on update cascade );

# insert into department (id,dep_name) values
# (3,'人力资源部');
#
# insert into employee2 (id,name,dep_id) values
# (1,'yuan',1),
# (2,'nezha',2),
# (3,'egon',2),
# (4,'alex',2),
# (5,'wusir',3),
# (6,'李沁洋',3),
# (7,'皮卡丘',3),
# (8,'程咬金',3),
# (9,'程咬银',3)
# ;


# 约束 4个
# not null     # 不允许为空
    # default  # 设置默认值
# unique       # 唯一,不能约束null
               # 联合唯一
    # auto_increment # 自增 必须在表中是一个主键,至少是一个unique,对于id来说,删除数据并不会影响自增

# primary key  # 主键 = not null + unique (同一张表不能有两个主键)
               # 联合主键
# foreign key  # 本表中的字段关联另一张表中的"唯一"字段 ,本表中的字段是 外键,外表中的字段必须唯一/主键

# create table 表名 (
#     字段名1 字段类型(宽度) not null default 默认值,
#     字段名2 字段类型(宽度) not null unique,
#     字段名3 字段类型(宽度) primary key,
#     字段名4 int(宽度) unique auto_increment,
# )


# create table 表名 (
#     字段名1 字段类型(宽度) not null,
#     字段名2 字段类型(宽度) not null,
#     字段名3 字段类型(宽度),
#     字段名4 int(宽度),
#     unique(字段名2),
#     primary key(字段名3),
#     unique(字段名4) auto increment,
# )

# create table 表名 (
#     字段名1 字段类型(宽度) not null,
#     字段名2 字段类型(宽度) not null,
#     字段名3 字段类型(宽度),
#     字段名4 int(宽度) auto increment,
#     unique(字段名1,字段名2),
#     primary key(字段名3,字段名4),
# );

# create table 表名(
#     字段名1 字段类型(宽度) not null,
#     字段名2 字段类型(宽度) not null,
#     外键名 字段类型(宽度),
#     foreign key (外建) references 外表(外表中的字段)
#     on delete cascade
#     on update cascade,
#     primary key(字段名1)
# )

# 添加主键
# alter table 表名 modify 字段名 类型(宽度) primary key;
# +--------------------+-------------+---------------------+
# | 名字               | 年龄        | 月薪                |
# +--------------------+-------------+---------------------+
# | 姓名 ; egon        | 薪资 : 18   | 薪资 : 7300.33      |
# | 姓名 ; alex        | 薪资 : 78   | 薪资 : 1000000.31   |
# | 姓名 ; wupeiqi     | 薪资 : 81   | 薪资 : 8300.00      |
# | 姓名 ; yuanhao     | 薪资 : 73   | 薪资 : 3500.00      |
# | 姓名 ; liwenzhou   | 薪资 : 28   | 薪资 : 2100.00      |
# | 姓名 ; 歪歪        | 薪资 : 48   | 薪资 : 3000.13      |
# | 姓名 ; 丫丫        | 薪资 : 38   | 薪资 : 2000.35      |
# | 姓名 ; 丁丁        | 薪资 : 18   | 薪资 : 1000.37      |
# | 姓名 ; 星星        | 薪资 : 18   | 薪资 : 3000.29      |
# | 姓名 ; 格格        | 薪资 : 28   | 薪资 : 4000.33      |
# | 姓名 ; 张野        | 薪资 : 28   | 薪资 : 10000.13     |
# | 姓名 ; 程咬金      | 薪资 : 18   | 薪资 : 20000.00     |
# | 姓名 ; 程咬银      | 薪资 : 18   | 薪资 : 19000.00     |
# | 姓名 ; 程咬铜      | 薪资 : 18   | 薪资 : 18000.00     |
# | 姓名 ; 程咬铁      | 薪资 : 18   | 薪资 : 17000.00     |
# +--------------------+-------------+---------------------+

外键的例子:

# 班级表
create table class(cid int auto_increment,caption char(32) not null,primary key(cid));
insert into class values(1,'三年二班'),(2,'一年三班'),(3,'三年一班')
insert into class values(2,'一年三班')
insert into class values(3,'三年一班')

# 学生表
create table student(sid int primary key auto_increment,sname char(32) not null,gender enum('女','男') default '女',class_id int not null,foreign key(class_id) references class(cid) on delete cascade on update cascade);
insert into student values(1,'钢弹','女',1),(2,'铁锤','女',1),(3,'山炮','男',2);
# insert into student values(2,'铁锤','女',1);
# insert into student values(3,'山炮','男',2);

#老师表
create table teacher(tid int auto_increment,tname char(32) not null,primary key(tid));
insert into teacher values(1,'波多'),(2,'苍空'),(3,'饭岛');
# insert into teacher values(1,'苍空');
# insert into teacher values(1,'饭岛');

#课程表
create table course(cid int primary key auto_increment,cname char(32) not null,tearch_id int not null,foreign key(tearch_id) references teacher(tid) on delete cascade on update cascade);
insert into course values(1,'生物',1),(2,'体育',1),(3,'物理',2);
# insert into course values(1,'体育',1);
# insert into course values(1,'物理',2);

#成绩表
create table score (sid int primary key auto_increment,student_id int not null,corse_id int not null,number int not null,foreign key(student_id) references student(sid) on delete cascade on update cascade,foreign key(corse_id) references course(cid) on delete cascade on update cascade);
insert into score values(1,1,1,60),(2,1,2,59),(3,2,2,100);
# insert into score values(2,1,2,59);
# insert into score values(3,2,2,100);

create table score1 (sid int primary key auto_increment,student_id int unique,corse_id int not null,number int not null,foreign key(student_id) references student(sid) on delete cascade on update cascade,foreign key(corse_id) references course(cid) on delete cascade on update cascade);

insert into score1 values(1,1,1,60),(2,1,2,59),(3,2,2,100);

posted @ 2019-05-27 00:25 7411 阅读(408) 评论(0) 收藏举报

刷新页面返回顶部

LLBFWH