MySQL表创建及外键
模板数据下载https://github.com/datacharmer/test_db
1. Employees数据库安装
(1)Employees数据库介绍
Employees用于学习和测试的数据库，大约160M
(1)Employees安装
官方文档安装
下载地址 https://github.com/datacharmer/test_db
简单安装
mysql < employees.sql
2. 表(Table)
(1)表介绍
表是关系数据库的核心
表=关系
表是记录的集合
Mysql默认存储引擎都是基于行存储
(2)表是数据的集合
select * from table_name limit 1;
集合是无序的。从表中随机取出一条数据，结果无确定性。
只有通过order by 排序之后取出的数据才是确定的。
(3)创建表
官方文档表创建的语法
(1)临时表创建
create database temp;
use temp;
createtemporary tabletemp_a(a int);
"root@localhost:mysql.sock>show create table temp_a\G;
1. row
Table: temp_a
Create Table: CREATE TEMPORARY TABLE temp_a (
a int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
默认存储引擎是InnoDB
insert into temp_a values(123);
select * from temp_a;
注意：临时表是session级别的。当前用户退出或者其他用户登录上来是看不到这张表的
如果临时表和普通表同名时,当前用户只能看到同名的临时表
(2)临时表的存储引擎
show variables like "default%tmp%";
"root@localhost:mysql.sock>show variables like "default%tmp%";
+----------------------------+--------+
| Variable_name | Value|
+----------------------------+--------+
| default_tmp_storage_engine | InnoDB |
+----------------------------+--------+
1 row in set (0.00 sec)
临时表的存储位置
system ls -l /tmp/
"root@localhost:mysql.sock>system ls -l /data/mysql_data/ | grep ib
-rw-r-----. 1 mysql mysql 744 Feb 24 15:48 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Mar 12 14:02 ibdata1
-rw-r-----. 1 mysql mysql 4294967296 Mar 12 13:51 ib_logfile0
-rw-r-----. 1 mysql mysql 4294967296 Feb 24 15:43 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Mar 12 14:04 ibtmp1
其中ibtmp1就是表结构对应的位置
查看临时表大小
show variables like "innodb_temp%";
(3)查看表结构
有3中方法
show create table temp_a \G;
desc temp_a\G;
show table status like"temp_a"\G;
(4)alter table
alter table temp_a add column b char(10);
alter table temp_a dropcolumn b ;
注意：当表记录非常大时，alter table 会耗时，影响性能。
具体查看官网文档 ONLINE DDL
3. 外键索引
(1)外键的介绍
官方文档
CREATE TABLE product (
category INT NOT NULL,
id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category,> ENGINE=INNODB;
CREATE TABLE customer (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE product_order (
no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
INDEX (customer_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category,> ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (customer_id) REFERENCES customer(id) ) ENGINE=INNODB;
(2)外键操作
表结构来自MySQL官方文档
create table parent(
id int not null,
primary key(id)
)engine=innodb;
create table child (
id int,
parent_id INT,
index par_ind (parent_id),
foreign key (parent_id)
references parent(id)
on delete cascade on update cascade
) engine=innodb;
(1)我们插入一条数据，id=1，parent_id=1
insert into child values(1,1);
报错信息如下：
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (temp.child, CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE ON UPDATE CASCADE)
insert into parent values(1);
insert into child values(1,1);
外键约束,可以让数据进行一致性更新,但是会有一定的性能损耗 ,线上业务使用不多。通常上述级联更新和删除都是由应用层业务逻辑进行判断并实现。
页:
[1]