时间:2021-07-01 10:21:17 帮助过:12人阅读
- ============设置唯一约束 UNIQUE===============<span style="color: #000000">
- 方法一:
- create table department1(
- id int,
- name varchar(</span>20<span style="color: #000000">) unique,
- comment varchar(</span>100<span style="color: #000000">)
- );
- 方法二:
- create table department2(
- id int,
- name varchar(</span>20<span style="color: #000000">),
- comment varchar(</span>100<span style="color: #000000">),
- constraint uk_name unique(name)
- );
- mysql</span>> insert into department1 values(1,<span style="color: #800000">‘</span><span style="color: #800000">IT</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">技术</span><span style="color: #800000">‘</span><span style="color: #000000">);
- Query OK, </span>1 row affected (0.00<span style="color: #000000"> sec)
- mysql</span>> insert into department1 values(1,<span style="color: #800000">‘</span><span style="color: #800000">IT</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">技术</span><span style="color: #800000">‘</span><span style="color: #000000">);
- ERROR </span>1062 (23000): Duplicate entry <span style="color: #800000">‘</span><span style="color: #800000">IT</span><span style="color: #800000">‘</span> <span style="color: #0000ff">for</span> key <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>
not null+unique的化学反应
- mysql> create table t1(id int <span style="color: #0000ff">not</span><span style="color: #000000"> null unique);
- Query OK, 0 rows affected (</span>0.02<span style="color: #000000"> sec)
- mysql</span>><span style="color: #000000"> desc t1;
- </span>+-------+---------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+---------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- +-------+---------+------+-----+---------+-------+<span style="color: #000000">
- row </span><span style="color: #0000ff">in</span> set (0.00 sec)
联合唯一
- <span style="color: #000000">create table service(
- id int primary key auto_increment,
- name varchar(</span>20<span style="color: #000000">),
- host varchar(</span>15) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
- port int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
- unique(host,port) </span><span style="color: #008000">#</span><span style="color: #008000">联合唯一</span>
- <span style="color: #000000">);
- mysql</span>><span style="color: #000000"> insert into service values
- </span>-> (1,<span style="color: #800000">‘</span><span style="color: #800000">nginx</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">192.168.0.10</span><span style="color: #800000">‘</span>,80<span style="color: #000000">),
- </span>-> (2,<span style="color: #800000">‘</span><span style="color: #800000">haproxy</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">192.168.0.20</span><span style="color: #800000">‘</span>,80<span style="color: #000000">),
- </span>-> (3,<span style="color: #800000">‘</span><span style="color: #800000">mysql</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">192.168.0.30</span><span style="color: #800000">‘</span>,3306<span style="color: #000000">)
- </span>-><span style="color: #000000"> ;
- Query OK, </span>3 rows affected (0.01<span style="color: #000000"> sec)
- Records: </span>3<span style="color: #000000"> Duplicates: 0 Warnings: 0
- mysql</span>> insert into service(name,host,port) values(<span style="color: #800000">‘</span><span style="color: #800000">nginx</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">192.168.0.10</span><span style="color: #800000">‘</span>,80<span style="color: #000000">);
- ERROR </span>1062 (23000): Duplicate entry <span style="color: #800000">‘</span><span style="color: #800000">192.168.0.10-80</span><span style="color: #800000">‘</span> <span style="color: #0000ff">for</span> key <span style="color: #800000">‘</span><span style="color: #800000">host</span><span style="color: #800000">‘</span>
primary key字段的值不为空且唯一
一个表中可以:
单列做主键
多列做主键(复合主键)
但一个表内只能有一个主键primary key
单列主键
- ============单列做主键===============
- <span style="color: #008000">#</span><span style="color: #008000">方法一:not null+unique</span>
- <span style="color: #000000">create table department1(
- id int </span><span style="color: #0000ff">not</span> null unique, <span style="color: #008000">#</span><span style="color: #008000">主键</span>
- name varchar(20) <span style="color: #0000ff">not</span><span style="color: #000000"> null unique,
- comment varchar(</span>100<span style="color: #000000">)
- );
- mysql</span>><span style="color: #000000"> desc department1;
- </span>+---------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+--------------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- | name | varchar(20) | NO | UNI | NULL | |
- | comment | varchar(100) | YES | | NULL | |
- +---------+--------------+------+-----+---------+-------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.01<span style="color: #000000"> sec)
- </span><span style="color: #008000">#</span><span style="color: #008000">方法二:在某一个字段后用primary key</span>
- <span style="color: #000000">create table department2(
- id int primary key, </span><span style="color: #008000">#</span><span style="color: #008000">主键</span>
- name varchar(20<span style="color: #000000">),
- comment varchar(</span>100<span style="color: #000000">)
- );
- mysql</span>><span style="color: #000000"> desc department2;
- </span>+---------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+--------------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | comment | varchar(100) | YES | | NULL | |
- +---------+--------------+------+-----+---------+-------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
- </span><span style="color: #008000">#</span><span style="color: #008000">方法三:在所有字段后单独定义primary key</span>
- <span style="color: #000000">create table department3(
- id int,
- name varchar(</span>20<span style="color: #000000">),
- comment varchar(</span>100<span style="color: #000000">),
- constraint pk_name primary key(id); </span><span style="color: #008000">#</span><span style="color: #008000">创建主键并为其命名pk_name</span>
- <span style="color: #000000">
- mysql</span>><span style="color: #000000"> desc department3;
- </span>+---------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+--------------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | comment | varchar(100) | YES | | NULL | |
- +---------+--------------+------+-----+---------+-------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.01 sec)
多列主键
- ==================多列做主键================<span style="color: #000000">
- create table service(
- ip varchar(</span>15<span style="color: #000000">),
- port char(</span>5<span style="color: #000000">),
- service_name varchar(</span>10) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
- primary key(ip,port)
- );
- mysql</span>><span style="color: #000000"> desc service;
- </span>+--------------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------------+-------------+------+-----+---------+-------+
- | ip | varchar(15) | NO | PRI | NULL | |
- | port | char(5) | NO | PRI | NULL | |
- | service_name | varchar(10) | NO | | NULL | |
- +--------------+-------------+------+-----+---------+-------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
- mysql</span>><span style="color: #000000"> insert into service values
- </span>-> (<span style="color: #800000">‘</span><span style="color: #800000">172.16.45.10</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">3306</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">mysqld</span><span style="color: #800000">‘</span><span style="color: #000000">),
- </span>-> (<span style="color: #800000">‘</span><span style="color: #800000">172.16.45.11</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">3306</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">mariadb</span><span style="color: #800000">‘</span><span style="color: #000000">)
- </span>-><span style="color: #000000"> ;
- Query OK, </span>2 rows affected (0.00<span style="color: #000000"> sec)
- Records: </span>2<span style="color: #000000"> Duplicates: 0 Warnings: 0
- mysql</span>> insert into service values (<span style="color: #800000">‘</span><span style="color: #800000">172.16.45.10</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">3306</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">nginx</span><span style="color: #800000">‘</span><span style="color: #000000">);
- ERROR </span>1062 (23000): Duplicate entry <span style="color: #800000">‘</span><span style="color: #800000">172.16.45.10-3306</span><span style="color: #800000">‘</span> <span style="color: #0000ff">for</span> key <span style="color: #800000">‘</span><span style="color: #800000">PRIMARY</span><span style="color: #800000">‘</span>
约束字段为自动增长,被约束的字段必须同时被key约束
View Code
- <span style="color: #008000">#</span><span style="color: #008000">不指定id,则自动增长</span>
- <span style="color: #000000">create table student(
- id int primary key auto_increment,
- name varchar(</span>20<span style="color: #000000">),
- sex enum(</span><span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) default <span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span><span style="color: #000000">
- );
- mysql</span>><span style="color: #000000"> desc student;
- </span>+-------+-----------------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-----------------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | name | varchar(20) | YES | | NULL | |
- | sex | enum(<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) | YES | | male | |
- +-------+-----------------------+------+-----+---------+----------------+<span style="color: #000000">
- mysql</span>><span style="color: #000000"> insert into student(name) values
- </span>-> (<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">),
- </span>-> (<span style="color: #800000">‘</span><span style="color: #800000">alex</span><span style="color: #800000">‘</span><span style="color: #000000">)
- </span>-><span style="color: #000000"> ;
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
- </span>+----+------+------+
- | id | name | sex |
- +----+------+------+
- | 1 | egon | male |
- | 2 | alex | male |
- +----+------+------+
- <span style="color: #008000">#</span><span style="color: #008000">也可以指定id</span>
- mysql> insert into student values(4,<span style="color: #800000">‘</span><span style="color: #800000">asb</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span><span style="color: #000000">);
- Query OK, </span>1 row affected (0.00<span style="color: #000000"> sec)
- mysql</span>> insert into student values(7,<span style="color: #800000">‘</span><span style="color: #800000">wsb</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span><span style="color: #000000">);
- Query OK, </span>1 row affected (0.00<span style="color: #000000"> sec)
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
- </span>+----+------+--------+
- | id | name | sex |
- +----+------+--------+
- | 1 | egon | male |
- | 2 | alex | male |
- | 4 | asb | female |
- | 7 | wsb | female |
- +----+------+--------+
- <span style="color: #008000">#</span><span style="color: #008000">对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长</span>
- mysql> delete <span style="color: #0000ff">from</span><span style="color: #000000"> student;
- Query OK, </span>4 rows affected (0.00<span style="color: #000000"> sec)
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
- Empty set (</span>0.00<span style="color: #000000"> sec)
- mysql</span>> insert into student(name) values(<span style="color: #800000">‘</span><span style="color: #800000">ysb</span><span style="color: #800000">‘</span><span style="color: #000000">);
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
- </span>+----+------+------+
- | id | name | sex |
- +----+------+------+
- | 8 | ysb | male |
- +----+------+------+
- <span style="color: #008000">#</span><span style="color: #008000">应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它</span>
- mysql><span style="color: #000000"> truncate student;
- Query OK, 0 rows affected (</span>0.01<span style="color: #000000"> sec)
- mysql</span>> insert into student(name) values(<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">);
- Query OK, </span>1 row affected (0.01<span style="color: #000000"> sec)
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
- </span>+----+------+------+
- | id | name | sex |
- +----+------+------+
- | 1 | egon | male |
- +----+------+------+<span style="color: #000000">
- row </span><span style="color: #0000ff">in</span> set (0.00 sec)
步长increment与起始偏移量offset:auto_increment_increment,auto_increment_offset
- <span style="color: #008000">#</span><span style="color: #008000">在创建完表后,修改自增字段的起始值</span>
- mysql><span style="color: #000000"> create table student(
- </span>-><span style="color: #000000"> id int primary key auto_increment,
- </span>-> name varchar(20<span style="color: #000000">),
- </span>-> sex enum(<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) default <span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>
- -><span style="color: #000000"> );
- mysql</span>> alter table student auto_increment=3<span style="color: #000000">;
- mysql</span>><span style="color: #000000"> show create table student;
- .......
- ENGINE</span>=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=<span style="color: #000000">utf8
- mysql</span>> insert into student(name) values(<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">);
- Query OK, </span>1 row affected (0.01<span style="color: #000000"> sec)
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
- </span>+----+------+------+
- | id | name | sex |
- +----+------+------+
- | 3 | egon | male |
- +----+------+------+<span style="color: #000000">
- row </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
- mysql</span>><span style="color: #000000"> show create table student;
- .......
- ENGINE</span>=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=<span style="color: #000000">utf8
- </span><span style="color: #008000">#</span><span style="color: #008000">也可以创建表时指定auto_increment的初始值,注意初始值的设置为表选项,应该放到括号外</span>
- <span style="color: #000000">create table student(
- id int primary key auto_increment,
- name varchar(</span>20<span style="color: #000000">),
- sex enum(</span><span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) default <span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span><span style="color: #000000">
- )auto_increment</span>=3<span style="color: #000000">;
- </span><span style="color: #008000">#</span><span style="color: #008000">设置步长</span>
- <span style="color: #000000">sqlserver:自增步长
- 基于表级别
- create table t1(
- id int。。。
- )engine</span>=innodb,auto_increment=2 步长=2 default charset=<span style="color: #000000">utf8
- mysql自增的步长:
- show session variables like </span><span style="color: #800000">‘</span><span style="color: #800000">auto_inc%</span><span style="color: #800000">‘</span><span style="color: #000000">;
- </span><span style="color: #008000">#</span><span style="color: #008000">基于会话级别</span>
- set session auth_increment_increment=2 <span style="color: #008000">#</span><span style="color: #008000">修改会话级别的步长</span>
- <span style="color: #008000">#</span><span style="color: #008000">基于全局级别的</span>
- set <span style="color: #0000ff">global</span> auth_increment_increment=2 <span style="color: #008000">#</span><span style="color: #008000">修改全局级别的步长(所有会话都生效)</span>
- <span style="color: #008000">#</span><span style="color: #008000">!!!注意了注意了注意了!!!</span>
- If the value of auto_increment_offset <span style="color: #0000ff">is</span> greater than that of auto_increment_increment, the value of auto_increment_offset <span style="color: #0000ff">is</span><span style="color: #000000"> ignored.
- 翻译:如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值会被忽略
- 比如:设置auto_increment_offset</span>=3,auto_increment_increment=2<span style="color: #000000">
- mysql</span>> set <span style="color: #0000ff">global</span> auto_increment_increment=5<span style="color: #000000">;
- Query OK, 0 rows affected (</span>0.00<span style="color: #000000"> sec)
- mysql</span>> set <span style="color: #0000ff">global</span> auto_increment_offset=3<span style="color: #000000">;
- Query OK, 0 rows affected (</span>0.00<span style="color: #000000"> sec)
- mysql</span>> show variables like <span style="color: #800000">‘</span><span style="color: #800000">auto_incre%</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">需要退出重新登录</span>
- +--------------------------+-------+
- | Variable_name | Value |
- +--------------------------+-------+
- | auto_increment_increment | 1 |
- | auto_increment_offset | 1 |
- +--------------------------+-------+<span style="color: #000000">
- create table student(
- id int primary key auto_increment,
- name varchar(</span>20<span style="color: #000000">),
- sex enum(</span><span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) default <span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span><span style="color: #000000">
- );
- mysql</span>> insert into student(name) values(<span style="color: #800000">‘</span><span style="color: #800000">egon1</span><span style="color: #800000">‘</span>),(<span style="color: #800000">‘</span><span style="color: #800000">egon2</span><span style="color: #800000">‘</span>),(<span style="color: #800000">‘</span><span style="color: #800000">egon3</span><span style="color: #800000">‘</span><span style="color: #000000">);
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
- </span>+----+-------+------+
- | id | name | sex |
- +----+-------+------+
- | 3 | egon1 | male |
- | 8 | egon2 | male |
- | 13 | egon3 | male |
- +----+-------+------+
员工信息表有三个字段:工号 姓名 部门
公司有3个部门,但是有1个亿的员工,那意味着部门这个字段需要重复存储,部门名字越长,越浪费
解决方法:
我们完全可以定义一个部门表
然后让员工信息表关联该表,如何关联,即foreign key
示范
- <span style="color: #008000">#</span><span style="color: #008000">表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一</span>
- <span style="color: #000000">create table department(
- id int primary key,
- name varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null
- )engine</span>=<span style="color: #000000">innodb;
- </span><span style="color: #008000">#</span><span style="color: #008000">dpt_id外键,关联父表(department主键id),同步更新,同步删除</span>
- <span style="color: #000000">create table employee(
- id int primary key,
- name varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
- dpt_id int,
- constraint fk_name foreign key(dpt_id)
- references department(id)
- on delete cascade
- on update cascade
- )engine</span>=<span style="color: #000000">innodb;
- </span><span style="color: #008000">#</span><span style="color: #008000">先往父表department中插入记录</span>
- <span style="color: #000000">insert into department values
- (</span>1,<span style="color: #800000">‘</span><span style="color: #800000">欧德博爱技术有限事业部</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span>2,<span style="color: #800000">‘</span><span style="color: #800000">艾利克斯人力资源部</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span>3,<span style="color: #800000">‘</span><span style="color: #800000">销售部</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span><span style="color: #008000">#</span><span style="color: #008000">再往子表employee中插入记录</span>
- <span style="color: #000000">insert into employee values
- (</span>1,<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,1<span style="color: #000000">),
- (</span>2,<span style="color: #800000">‘</span><span style="color: #800000">alex1</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
- (</span>3,<span style="color: #800000">‘</span><span style="color: #800000">alex2</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
- (</span>4,<span style="color: #800000">‘</span><span style="color: #800000">alex3</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
- (</span>5,<span style="color: #800000">‘</span><span style="color: #800000">李坦克</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
- (</span>6,<span style="color: #800000">‘</span><span style="color: #800000">刘飞机</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
- (</span>7,<span style="color: #800000">‘</span><span style="color: #800000">张火箭</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
- (</span>8,<span style="color: #800000">‘</span><span style="color: #800000">林子弹</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
- (</span>9,<span style="color: #800000">‘</span><span style="color: #800000">加特林</span><span style="color: #800000">‘</span>,3<span style="color: #000000">)
- ;
- </span><span style="color: #008000">#</span><span style="color: #008000">删父表department,子表employee中对应的记录跟着删</span>
- mysql> delete <span style="color: #0000ff">from</span> department where id=3<span style="color: #000000">;
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> employee;
- </span>+----+-------+--------+
- | id | name | dpt_id |
- +----+-------+--------+
- | 1 | egon | 1 |
- | 2 | alex1 | 2 |
- | 3 | alex2 | 2 |
- | 4 | alex3 | 2 |
- +----+-------+--------+
- <span style="color: #008000">#</span><span style="color: #008000">更新父表department,子表employee中对应的记录跟着改</span>
- mysql> update department set id=22222 where id=2<span style="color: #000000">;
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> employee;
- </span>+----+-------+--------+
- | id | name | dpt_id |
- +----+-------+--------+
- | 1 | egon | 1 |
- | 3 | alex2 | 22222 |
- | 4 | alex3 | 22222 |
- | 5 | alex1 | 22222 |
- +----+-------+--------+
辅助理解
- <span style="color: #000000">表1 foreign key 表2
- 则表1的多条记录对应表2的一条记录,即多对一
- 利用foreign key的原理我们可以制作两张表的多对多,一对一关系
- 多对多:
- 表1的多条记录可以对应表2的一条记录
- 表2的多条记录也可以对应表1的一条记录
- 一对一:
- 表1的一条记录唯一对应表2的一条记录,反之亦然
- 分析时,我们先从按照上面的基本原理去套,然后再翻译成真实的意义,就很好理解了</span>
- <span style="color: #008000">#</span><span style="color: #008000">一对多或称为多对一</span>
- <span style="color: #000000">三张表:出版社,作者信息,书
- 一对多(或多对一):一个出版社可以出版多本书
- 关联方式:foreign key</span>
View Code
- =====================多对一=====================<span style="color: #000000">
- create table press(
- id int primary key auto_increment,
- name varchar(</span>20<span style="color: #000000">)
- );
- create table book(
- id int primary key auto_increment,
- name varchar(</span>20<span style="color: #000000">),
- press_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
- foreign key(press_id) references press(id)
- on delete cascade
- on update cascade
- );
- insert into press(name) values
- (</span><span style="color: #800000">‘</span><span style="color: #800000">北京工业地雷出版社</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">人民音乐不好听出版社</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">知识产权没有用出版社</span><span style="color: #800000">‘</span><span style="color: #000000">)
- ;
- insert into book(name,press_id) values
- (</span><span style="color: #800000">‘</span><span style="color: #800000">九阳神功</span><span style="color: #800000">‘</span>,1<span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">九阴真经</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">九阴白骨爪</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">独孤九剑</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">降龙十巴掌</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">葵花宝典</span><span style="color: #800000">‘</span>,3);
其它例子
- 单张表:用户表+相亲关系表,相当于:用户表+相亲关系表+<span style="color: #000000">用户表
- 多张表:用户表</span>+用户与主机关系表+<span style="color: #000000">主机表
- 中间那一张存放关系的表,对外关联的字段可以联合唯一</span>
- <span style="color: #008000">#</span><span style="color: #008000">一对一</span>
- <span style="color: #000000">两张表:学生表和客户表
- 一对一:一个学生是一个客户,一个客户有可能变成一个学校,即一对一的关系
- 关联方式:foreign key</span>+unique
其他例子
- <span style="color: #000000">例一:一个用户只有一个博客
- 用户表:
- id name
- egon
- alex
- wupeiqi
- 博客表
- fk</span>+<span style="color: #000000">unique
- id url name_id
- xxxx </span>1<span style="color: #000000">
- yyyy </span>3<span style="color: #000000">
- zzz </span>2<span style="color: #000000">
- 例二:一个管理员唯一对应一个用户
- 用户表:
- id user password
- egon xxxx
- alex yyyy
- 管理员表:
- fk</span>+<span style="color: #000000">unique
- id user_id password
- </span>1<span style="color: #000000"> xxxxx
- </span>2 yyyyy
练习:账号信息表,用户组,主机表,主机组
View Code
- <span style="color: #008000">#</span><span style="color: #008000">用户表</span>
- <span style="color: #000000">create table user(
- id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null unique auto_increment,
- username varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
- password varchar(</span>50) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
- primary key(username,password)
- );
- insert into user(username,password) values
- (</span><span style="color: #800000">‘</span><span style="color: #800000">root</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">123</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">456</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">alex</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">alex3714</span><span style="color: #800000">‘</span><span style="color: #000000">)
- ;
- </span><span style="color: #008000">#</span><span style="color: #008000">用户组表</span>
- <span style="color: #000000">create table usergroup(
- id int primary key auto_increment,
- groupname varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null unique
- );
- insert into usergroup(groupname) values
- (</span><span style="color: #800000">‘</span><span style="color: #800000">IT</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">Sale</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">Finance</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">boss</span><span style="color: #800000">‘</span><span style="color: #000000">)
- ;
- </span><span style="color: #008000">#</span><span style="color: #008000">主机表</span>
- <span style="color: #000000">create table host(
- id int primary key auto_increment,
- ip char(</span>15) <span style="color: #0000ff">not</span> null unique default <span style="color: #800000">‘</span><span style="color: #800000">127.0.0.1</span><span style="color: #800000">‘</span><span style="color: #000000">
- );
- insert into host(ip) values
- (</span><span style="color: #800000">‘</span><span style="color: #800000">172.16.45.2</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">172.16.31.10</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">172.16.45.3</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">172.16.31.11</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">172.10.45.3</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">172.10.45.4</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">172.10.45.5</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.1.20</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.1.21</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.1.22</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.2.23</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.2.223</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.2.24</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.3.22</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.3.23</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.3.24</span><span style="color: #800000">‘</span><span style="color: #000000">)
- ;
- </span><span style="color: #008000">#</span><span style="color: #008000">业务线表</span>
- <span style="color: #000000">create table business(
- id int primary key auto_increment,
- business varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null unique
- );
- insert into business(business) values
- (</span><span style="color: #800000">‘</span><span style="color: #800000">轻松贷</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">随便花</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">大富翁</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">穷一生</span><span style="color: #800000">‘</span><span style="color: #000000">)
- ;
- </span><span style="color: #008000">#</span><span style="color: #008000">建关系:user与usergroup</span>
- <span style="color: #000000">
- create table user2usergroup(
- id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null unique auto_increment,
- user_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
- group_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
- primary key(user_id,group_id),
- foreign key(user_id) references user(id),
- foreign key(group_id) references usergroup(id)
- );
- insert into user2usergroup(user_id,group_id) values
- (</span>1,1<span style="color: #000000">),
- (</span>1,2<span style="color: #000000">),
- (</span>1,3<span style="color: #000000">),
- (</span>1,4<span style="color: #000000">),
- (</span>2,3<span style="color: #000000">),
- (</span>2,4<span style="color: #000000">),
- (</span>3,4<span style="color: #000000">)
- ;
- </span><span style="color: #008000">#</span><span style="color: #008000">建关系:host与business</span>
- <span style="color: #000000">
- create table host2business(
- id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null unique auto_increment,
- host_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
- business_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
- primary key(host_id,business_id),
- foreign key(host_id) references host(id),
- foreign key(business_id) references business(id)
- );
- insert into host2business(host_id,business_id) values
- (</span>1,1<span style="color: #000000">),
- (</span>1,2<span style="color: #000000">),
- (</span>1,3<span style="color: #000000">),
- (</span>2,2<span style="color: #000000">),
- (</span>2,3<span style="color: #000000">),
- (</span>3,4<span style="color: #000000">)
- ;
- </span><span style="color: #008000">#</span><span style="color: #008000">建关系:user与host</span>
- <span style="color: #000000">
- create table user2host(
- id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null unique auto_increment,
- user_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
- host_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
- primary key(user_id,host_id),
- foreign key(user_id) references user(id),
- foreign key(host_id) references host(id)
- );
- insert into user2host(user_id,host_id) values
- (</span>1,1<span style="color: #000000">),
- (</span>1,2<span style="color: #000000">),
- (</span>1,3<span style="color: #000000">),
- (</span>1,4<span style="color: #000000">),
- (</span>1,5<span style="color: #000000">),
- (</span>1,6<span style="color: #000000">),
- (</span>1,7<span style="color: #000000">),
- (</span>1,8<span style="color: #000000">),
- (</span>1,9<span style="color: #000000">),
- (</span>1,10<span style="color: #000000">),
- (</span>1,11<span style="color: #000000">),
- (</span>1,12<span style="color: #000000">),
- (</span>1,13<span style="color: #000000">),
- (</span>1,14<span style="color: #000000">),
- (</span>1,15<span style="color: #000000">),
- (</span>1,16<span style="color: #000000">),
- (</span>2,2<span style="color: #000000">),
- (</span>2,3<span style="color: #000000">),
- (</span>2,4<span style="color: #000000">),
- (</span>2,5<span style="color: #000000">),
- (</span>3,10<span style="color: #000000">),
- (</span>3,11<span style="color: #000000">),
- (</span>3,12);
作业:
MySQL四-2:完整性约束
标签:资源 charset 没有 not des == 解决方法 mariadb 表示