4.4 外键约束
- foreign key
- 相关概念：
- 外键约束：
- 外键字段：含有外键约束的字段
- 外键值：外键当中的每一个值
- 被引用的表为父表 ， 另一个表子子表
- 顺序：
- 创建表的顺序：先父后子
- 删除表的顺序：先子后父
- 删除数据的顺序：先子后父
- 插入数据的顺序：先父后子
- 格式： foreign key (外键字段) references 父表名(父表字段)；
5、存储引擎
- 概念：mysql中特有的术语，其他数据库中没有。是存储/组织数据的方式，不同的存取引擎
- mysql支持的存储引擎：
- MEMORY
- MRG_MYISAM
- CSV
- FEDERATED
- PERFORMANCE_SCHEMA
- MyISAM
- InnoDB
- BLACKHOLE
- ARCHIVE
- 格式：
- 添加：在建表的时候的‘）’外面使用ENGINE进行指定引擎
【案例】：查看建表语句 show create table t_student ; +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t_student | 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 | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.04 sec) 其中的ENGINE=InnoDB AUTO_INCREMENT=11 是引擎指定语句，可以在建表的时候进行修改 其中的DEFAULT CHARSET=utf8 是字符编码方式 ， 也可以进行修改 mysql默认的存储引擎是InnoDB，默认的字符编码方式是utf8 ；
- 添加存储引擎：
【案例】：创建一个含有主键为id；字段为id、name；引擎为InnoDB 编码方式为utf8的商品表 drop table if exists t_shop ; create table t_shop ( id int primary key , name varchar(255) ) engine = InnoDB default charset = gbk ; gbk : 可以存储中文 ;
- 查看mysql支持的存储引擎：
【案例】：查看mysql支持的存储引擎 show engines \G ; 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.05 sec) ERROR: No query specified
- mysql常用的存储引擎：MyISAM、InnoDB、MEMORY
- MyISAM：
- InnoDB：
- MEMORY：
