主键约束,外键约束
主键约束(primary key,简称PK)的相关术语:
主键约束:一种约束
主键字段:该字段上添加了主键约束
主键值:主键字段的每一个值都叫主键值
1.什么是主键约束(重点需掌握)
主键值是每一行记录的唯一标识。任何一张表都应该有主键,没有主键,表无效。一张表中只能添加一个主键约束。主键值建议使用:int、bigint、char等类型。
不建议使用:varchar来做主键。主键值一般都是数字,一般都是定长的!
主键的特征:not null + unique(主键值不能是NULL,同时也不能重复!)
1.2使用列级约束给一张表添加主键约束
drop table if exists t_vip;
// 1个字段做主键,叫做:单一主键
create table t_vip(
id int primary key, //列级约束
name varchar(255)
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
mysql> select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+
2 rows in set (0.00 sec)
//错误:主键值不能重复
insert into t_vip(id,name) values(2,'wangwu');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
//错误:主键值不能为NULL
insert into t_vip(name) values('zhaoliu');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
1.2.1使用表级约束添加主键
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
primary key(id) // 表级约束
);
insert into t_vip(id,name) values(1,'zhangsan');
mysql> select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
//错误
insert into t_vip(id,name) values(1,'lisi');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
- 在mysql当中,有一种机制,可以帮助我们自动维护一个主键值 :auto_increment
drop table if exists t_vip;
create table t_vip(
id int primary key auto_increment, //auto_increment表示自增,从1开始,以1递增!
name varchar(255)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
| 3 | zhangsan |
| 4 | zhangsan |
| 5 | zhangsan |
| 6 | zhangsan |
| 7 | zhangsan |
| 8 | zhangsan |
+----+----------+
8 rows in set (0.00 sec)
1.2.2给多个字段联合起来添加主键约束(不建议使用)
drop table if exists t_vip;
// id和name联合起来做主键:复合主键
create table t_vip(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
mysql> select * from t_vip;
+----+----------+------------------+
| id | name | email |
+----+----------+------------------+
| 1 | lisi | lisi@123.com |
| 1 | zhangsan | zhangsan@123.com |
+----+----------+------------------+
2 rows in set (0.00 sec)
//错误:不能重复
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'
在实际开发中不建议使用:复合主键。建议使用单一主键!
因为主键值存在的意义就是每一行记录的唯一标识,只要意义达到即可,单一主键可以做到。复合主键比较复杂,不建议使用!
主键除了单一主键和复合主键之外,还可以这样进行分类:
自然主键:主键值是一个自然数,和业务没关系。
业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键值。这就是业务主键。
- 在实际开发中使用业务主键多,还是使用自然主键多一些?
自然主键使用比较多,因为主键只要做到不重复就行,不需要有意义。
业务主键不好,因为主键一旦和业务挂钩,那么当业务发生变动的时候,
可能会影响到主键值,所以业务主键不建议使用。尽量使用自然主键。
什么是外键约束?(非常重要)
外键约束(foreign key,简称FK)
外键约束涉及到的相关术语
外键约束:一种约束
外键字段:添加了外键约束的字段
外键值:外键字段中的每一个值
语法:foreign key(a表字段) reference b表(字段)
需求:
请设计数据库表,来描述班级和学生的信息
第一种方案:班级和学生存储在一张表中。这个方案会出现班级信息全部一样,数据冗余,浪费空间。
第二种方案:班级一张表,学生一张表。采用这种设计,两个表之间没有关系,所以有了外键约束(a表引用b表中的一个字段)。学生表和班级表通过一个外键字段产生联系。添加外键约束到a表后,a表受到b表的约束,外键字段只能添加b表中有的值。
a表是子表,b表是父表。删除表的时候,先删子表,再删父表;创建表则先创建父表,再创建子表;删除数据的时候先删子表,再删父表;插入数据的时候先插入父表,再插入子表。
思考:子表中的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗?
- 不一定是主键,但至少具有unique约束(唯一性)。
外键可以为NULL吗?
- 外键值可以为NULL。
存储引擎(了解)
什么是存储引擎?存储引擎有什么用?
存储引擎是MySQL中特有的一个术语,其它数据库中没有。(Oracle中有,但是不叫这个名字)
实际上存储引擎是一个表存储/组织数据的方式。
不同的存储引擎,表存储数据的方式不同。
怎么给表添加/指定“存储引擎”?
show create table t_student;//展示出当时创建这张表的语句
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_student | CREATE TABLE `t_student` (
`no` int(11) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以在建表的时候给表指定存储引擎
CREATE TABLE `t_student` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cno` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `cno` (`cno`),
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
在建表的时候可以在最后小括号的")"的右边使用:
ENGINE来指定存储引擎。
CHARSET来指定这张表的字符编码方式。
mysql默认的存储引擎是:InnoDB
mysql默认的字符编码方式是:utf8
//默认编码方式取决于安装mysql数据库时指定的字符编码方式
建表时指定存储引擎,以及字符编码方式。
create table 表名(
字段1 数据类型 (列级约束),
字段2 数据类型
)engine=InnoDB default charset=gbk;
怎么查看mysql支持哪些存储引擎?
mysql版本不一样,支持的存储引擎也不一样
show engines \G
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 5. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 8. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)
mysql常用的存储引擎
- MyISAM存储引擎
它管理的表具有以下特征:
使用三个文件表示每个表:
格式文件 — 存储表结构的定义(mytable.frm)
数据文件 — 存储表行的内容(mytable.MYD)
索引文件 — 存储表上索引(mytable.MYI):索引是一本书的目录,缩小扫描范围,提高查询效率的一种机制。
可被转换为压缩、只读表来节省空间
提示:
对于一张表来说,只要是主键,
或者加有unique约束的字段上会自动创建索引。
MyISAM存储引擎特点:
可被转换为压缩、只读表来节省空间
这是这种存储引擎的优势。
MyISAM不支持事务机制,安全性低。
- InnoDB存储引擎
这是mysql默认的存储引擎,同时也是一个重量级的存储引擎。
InnoDB支持事务,支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是:非常安全。
它管理的表具有下列主要特征:
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容(表空间是一个逻辑名称。表空间存储数据+索引。)
– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
– 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本(MVCC)和行级锁定
– 支持外键及引用的完整性,包括级联删除和更新
InnoDB最大的特点就是支持事务:
以保证数据的安全。效率不是很高,并且也不能压缩,不能转换为只读,
不能很好的节省存储空间。
- MEMORY存储引擎
使用MEMORY引擎的表,其数据存储在内存中,且行的长度固定,这两个特点使得MEMORY存储引擎非常快
memory存储引擎管理的表具有如下特征:
–在数据库目录内,每个表均以.fr格式的文件表示。
–表数据及索引存储在内存中,目的就是快,查询快。
–表级锁机制。
–不能包含TEXT或BLOB字段,因为内存不够大。
MEMORY存储引擎又被叫做HEAP引擎(堆引擎)。
MEMORY存储引擎优点:查询效率最高,因为不需要和硬盘进行交互。
MEMORY存储引擎缺点,不安全,关机之后数据就会消失。因为数据和索引存储在内存当中。