【sqlday6 数据库存储引擎主键约束】

最新推荐文章于 2022-11-06 00:20:59 发布

ddl12131

最新推荐文章于 2022-11-06 00:20:59 发布

阅读量170

点赞数

文章标签：数据库 mysql 服务器

本文链接：https://blog.csdn.net/ddl12131/article/details/121946297

版权

本文详细介绍了如何在MySQL中添加主键约束，区分单一主键和复合主键，并讨论了主键的作用和不同存储引擎的选择。此外，还通过实例演示了外键约束的使用，以及MyISAM、InnoDB和MEMORY等常见存储引擎的特点与应用场景。

摘要由CSDN通过智能技术生成

任务59：053 主键约束
怎么给一张表加主键约束呢？
Drop table if exists t_user;
Create table t_user(
Id int primary key,
Username varchar(255),
Email varchar(255));
Insert into t_user(id, username, email) values(1,’zs’,’123’);手动
Insert into t_user(id,username,email) values(1, ‘1lisi’,’123’);
Insert into t_user(id,username,email) values(1,’zs’,’123’);
mysql> insert into t_user(id,username,email) values(1,‘zs’,‘123’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,username,email) values(1,‘1lisi’,‘123’);
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’
mysql> insert into t_user(id,username,email) values(1,‘zs’,‘zs’);
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’
mysql> select * from t_user;
±—±---------±------+
| Id | Username | Email |
±—±---------±------+
| 1 | zs | 123 |
±—±---------±------+
1 row in set (0.00 sec)
mysql> insert into t_user(id,username,email) values(2,‘ls’,‘ls@123.com’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user(id,username,email) values(3,‘ww’,‘www@123.com’);
Query OK, 1 row affected (0.00 sec)

主键相关的术语;
主键约束:primary key
主键字段：id字段添加primary key
主键值：id字段中的每一个值都是主键值
主键有什么用？
表的设计三范式
第一范式要求任意一张表都应该有主键
主键的作用：
主键的作用：主键值是这行记录在这张表当中唯一标识号
（身份证号码）
主键的分类
根据主键字段的字段数量来划分
单一主键
符合主键（多个字段联合起来添加一个主键约束）
根据主键性质来划分
自然主键
业务主键：主键值和系统的业务挂钩：例如：拿着
例如:拿着银行卡的卡号做主键，身份证号
业务变-主键值变
One table one建

054 主键值自增.flv
使用表级约束方式定义主键：
Drop table if exists t_user;
Create table t_user(
Id int,
Username varchar(255),
Primary key(id)
);
Insert into t_user(id, username) values(1,’zs’);
Insert into t_user(id, username) values(2,’ls’);
Insert into t_user(id,username) values(4,’cs’);
Select * from t_user;

mysql> drop table if exists t_user;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_user(id int primary key, username varchar(255),email varchar(255),email varchar(255));
ERROR 1060 (42S21): Duplicate column name ‘email’
mysql> create table t_user(id int primary key, username varchar(255),email varchar(255));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_user(id,username,emil) values(1,‘zs’,‘zs@123.com’);
ERROR 1054 (42S22): Unknown column ‘emil’ in ‘field list’
mysql> insert into t_user(id,username,email) values(1,‘zs’,‘zs@123.com’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
±—±---------±-----------+
| id | username | email |
±—±---------±-----------+
| 1 | zs | zs@123.com |
±—±---------±-----------+
1 row in set (0.00 sec)

mysql> insert into t_user(id,username,email) values(2,‘ls’,‘ls@123.com’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,username,email) values(3,‘ww’,‘ww@123.com’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
±—±---------±-----------+
| id | username | email |
±—±---------±-----------+
| 1 | zs | zs@123.com |
| 2 | ls | ls@123.com |
| 3 | ww | ww@123.com |
±—±---------±-----------+
3 rows in set (0.00 sec)

mysql> Drop table if exists t_user;
Query OK, 0 rows affected (0.01 sec)

mysql> Create table t_user(
-> Id int,
-> Username varchar(255),
-> Primary key(id)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> Insert into t_user(id, username) values(1,’zs’);
ERROR 1054 (42S22): Unknown column ‘’zs’’ in ‘field list’
mysql> insert into t_user)id,username) values(1,‘zs’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)id,username) values(1,‘zs’)’ at line 1
mysql> insert into t_user(id,username) values(1,‘zs’);
Query OK, 1 row affected (0.00 sec)

演示复合主键

Create table t_user(id int,
Username varchar(255),
Password varchar(255),
Primary key(id,username));
mysql提供主键自增
Drop table if exists t_user;
Create table t_user(id int primary key auto_increment,//id 字段
自动维护一个自增的数字，从1开始递增
Username varchar(255));
Insert into t_user(username) values(‘a’);
Insert into t_user(username) values(‘b’);

Insert into t_user(username) values(‘c’);

Insert into t_user(username) values(‘d’);

Insert into t_user(username) values(‘e’);

054 主键值自增.flv
mysql> Drop table if exists t_user;
Query OK, 0 rows affected (0.02 sec)

mysql> Create table t_user(id int primary key auto_increment,Username varchar(255));
Query OK, 0 rows affected (0.02 sec)

mysql> Insert into t_user(username) values(‘a’);
ERROR 1054 (42S22): Unknown column ‘‘a’’ in ‘field list’
mysql> Insert into t_user(username) values(‘a’);
Query OK, 1 row affected (0.00 sec)

mysql> Insert into t_user(username) values(‘b’);
Query OK, 1 row affected (0.00 sec)

mysql> Insert into t_user(username) values(‘c’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tables;
ERROR 1146 (42S02): Table ‘bjpowernode.tables’ doesn’t exist
mysql> select * from t_user;
±—±---------+
| id | Username |
±—±---------+
| 1 | a |
| 2 | b |
| 3 | c |
±—±---------+
Navicat for mysql–栏位id ,name
提示：oracle 当中也提供了一个自增机制，叫做：序列（sequence）对象

http://vip.17baishi.com/my/course/19191

任务61：055 外键约束.flv
关于外键约束的相关术语：
外键约束 foreign key
外键字段：添加有外键约束的字段
外键值外键字段中的每一个值
业务背景：
请设计数据表，用来维护学生和班级的信息
第一种方案：一张表存储所有数据
No name classno classname
1 zs 101 北京大兴区经济技术开发李庄二中高三1班
2 zs1 101 北京大兴区经济技术开发李庄二中高三1班
3 zs2 102 北京大兴区经济技术开发李庄二中高三1班
4 zs3 102 北京大兴区经济技术开发李庄二中高三1班
5 zs4 102 北京大兴区经济技术开发李庄二中高三1班
缺点：冗余
第二种方案：两张表（班级表和学生表）
T_class 班级表
Cno cname
101 北京大兴区经济技术开发李庄二中高三1班
102 zs1 101 北京大兴区经济技术开发李庄二中高三1班
T_student 学生表
Eno(pk) sname cno(该字段)
1 zs1 103
2 zs2 104
3 zs3 103
4 zs4 103
5 zs5 103
将以上表的建表语句写出来：
T_student中classno字段引用t_class表中的cno字段，此时t_student表叫做子表,t_class表叫做父表

顺序要求：
删除数据的时候，先删除子表，再删除父表
添加数据的时候,先添加父表再添加子表
删除表的时候先删除子表在删除父表

mysql> Drop table if exists t_student;

Drop table if exists t_class;

Create table t_class(cno int,cname varchar(255), primary key(cno));
Create table t_student(eno int,ename varchar(255),classno int, foreign key(classno) references t_class(cno));
mysql> drop table if exists t_student;
Query OK, 0 rows affected (0.01 sec)

mysql> Create table t_student(eno int,ename varchar(255),classno int, foreign key(classno) references t_class(cno));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_class values(101,‘xxxxxxxxx’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_class values(102,‘yyyyyyyyy’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student values(1,‘zs1’,101);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_class values(2,‘zs2’,101);
ERROR 1136 (21S01): Column count doesn’t match value count at row 1
mysql> insert into t_class values(2,‘zs2’,101);
ERROR 1136 (21S01): Column count doesn’t match value count at row 1
mysql> insert into t_student values(2,‘zs2’,101);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student values(3,‘zs3’,102);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student values(4,‘zs4’,102);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_student values(5,‘zs5’,102);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student values(6,‘zs6’,102);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> select * from t_class;
±----±----------+
| cno | cname |
±----±----------+
| 101 | xxxxxxxxx |
| 102 | yyyyyyyyy |
±----±----------+
2 rows in set (0.00 sec)

mysql> select * from t_student;
±-----±------±--------+
| eno | ename | classno |
±-----±------±--------+
| 1 | zs1 | 101 |
| 2 | zs2 | 101 |
| 3 | zs3 | 102 |
| 4 | zs4 | 102 |
| 5 | zs5 | 102 |
| 6 | zs6 | 102 |
±-----±------±--------+
6 rows in set (0.00 sec)

mysql> insert into t_student values(7,‘lisi’,103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (bjpowernode.t_student, CONSTRAINT t_student_ibfk_1 FOREIGN KEY (classno) REFERENCES t_class (cno))
mysql> insert into t_student(sno,sname) values(6,‘zs6’);
ERROR 1054 (42S22): Unknown column ‘sno’ in ‘field list’
外键值可以是NULL
外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少具体unique约束

任务62：056 存储引擎.flv
了解
存储引擎的使用
mysql> show engines\g
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
9 rows in set (0.01 sec)

mysql> use bjpowernode;
Database changed
mysql> show create table emp;
±------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp | CREATE TABLE emp (
EMPNO int(4) NOT NULL,
ENAME varchar(10) DEFAULT NULL,
JOB varchar(9) DEFAULT NULL,
MGR int(4) DEFAULT NULL,
HIREDATE date DEFAULT NULL,
SAL double(7,2) DEFAULT NULL,
COMM double(7,2) DEFAULT NULL,
DEPTNO int(2) DEFAULT NULL,
PRIMARY KEY (EMPNO)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
±------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

新建一个表
Create table t_x(id int);
Show create table t_x;
mysql> Create table t_x(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> Show create table t_x;
±------±----------------------------------------------------------------------------------------+
| Table | Create Table |
±------±----------------------------------------------------------------------------------------+
| t_x | CREATE TABLE t_x (
id int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
±------±----------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
存储引擎
完整的建表语句
Create table t_x(id int(11) DEFAULT NULL) engine=innodb default charset=utf8;
//飘号
注意：在mysql 凡是标识符可以用飘号括起来的，别用不通用
建表可以指定存储引擎，也可以指定字符集
Mysql默认使用的存储引擎是Innob方式
默认使用的字符集是UTF8
什么是存储引擎呢
存储引擎这个名字只有mysql中存在，在Oracle有相应的机制但不叫存储引擎
Mysql支持很多存储引擎，每一个存储引擎都是对应了一种不同的存储方式
每个存储引擎都有自己的缺点，需要在合适的时机选择合适的存储引擎
任务63：057 常见的存储引擎有哪些.flv
2.3查看当前mysql支持的存储引擎
show engines \g
mysql> show engines \g
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
9 rows in set (0.00 sec)
*任务64：058 MyISAM存储引擎.flv
常见的存储引擎
MyISAM存储引擎
Engine：MyISAM
Support:yes
Comment:MYISAM storage engine
transactionL:no
XA:no
Savepoints:no
MyISAM这种存储引擎不支持事务
MyISAM是最为常用引擎，但是不是默认的
Select * from emp;
搞定J2EE -----BAIDU ENIGINE_索引提高效率
mysql> select * from emp where ename = ‘smith’;
±------±------±------±-----±-----------±-------±-----±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±------±------±-----±-----------±-------±-----±-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
±------±------±------±-----±-----------±-------±-----±-------+
1 row in set (0.00 sec)
3种类型的文件
mysql> use mysql;
Database changed
mysql> show tables;
存结构表的格式
±--------------------------+
| Tables_in_mysql |
±--------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
±--------------------------+
28 rows in set (0.00 sec)
Myisam采用三个文件组织一张表
Xxx.frm(存储格式的文件)
Xxx.MYD(存储表种数据的文件)
Xxx.MYI(存储表中索引的文件)
优点：可被压缩节省空间并且可以转换为只读表提高检索效率

任务65：059 InnoDB存储引擎.flv
InnoDB存储引擎
Engine：InnoDB
Support:DEFEAULT
Comment:supports transactions,row_level locking, and foreign keys
Transaction:yes
XA:yes
Savepoints:yes
优点:支持事务，行级表，外键。这种存储引擎数据的安全得到保障
存储在表空间，没办法压缩
表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读
这种InnoDB存储引擎在MYsql数据库崩溃之后提供自动恢复机制。
InnoDB支持级联删除和级联更新
Mysql删除父表，外键有父的都删掉

任务66：060 MEMORY存储引擎.flv

Engine：MEMORY
Support:DEFEAULT
Comment:hash based， stored in memory, useful for temporary tables
Transaction:no
XA:no
Savepoints:no
缺点：不支持事务。数据容易丢失。因为所有数据和索引都是
存储在内存当中的。
优点：查询速度最快。
以前叫做HEPA 引擎