【2020Python修炼记】MySQL之 表相关操作
【目录】
一 存储引擎介绍
二 表介绍
三 创建表
四 查看表
五 修改表
六 复制表
七 删除表
八 表数据类型
1、介绍
2、数值类型
3、日期类型
4、字符串类型
5、枚举类型与集合类型
九 表完整性约束
1、介绍
2、not null与default
3、 unique
4、primary key
5、auto_increment
6、foreign key
十 表的关系
一 、存储引擎介绍
1、什么是存储引擎
日常生活中文件格式有很多中,并且针对不同的文件格式会有对应不同存储方式和处理机制(txt,pdf,word,mp4...)
针对不同的数据应该有对应的不同的处理机制来存储
什么是存储引擎数据库中的表也应该有不同的类型,表的类型不同,会对应mysql不同的存取机制,表类型又称为存储引擎。 存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方 法。因为在关系型数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和 操作此表的类型) 在Oracle 和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。 而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据 自己的需要编写自己的存储引擎。
2、MySQL主要存储引擎
是MySQL5.5版本及之后默认的存储引擎
存储数据更加的安全
myisam
是MySQL5.5版本之前默认的存储引擎
速度要比Innodb更快 但是我们更加注重的是数据的安全
memory
内存引擎(数据全部存放在内存中) 断电数据丢失
blackhole
无论存什么,都立刻消失(黑洞)
mysql支持的存储引擎-详解show engines\G #查看所有支持的存储引擎 show variables like 'storage_engine%'; #查看正在使用的存储引擎 (MySQL 一般是使用 InnoDB) # ===============> #InnoDB 存储引擎 支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其特点是行锁设计、支持外键, 并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。 InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。 从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。 此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。 InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别, 同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。 除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。 对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的, 如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。 InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。 如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。 #MyISAM 存储引擎 不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。 数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。 用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗? 此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。 #NDB 存储引擎 2003 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是, 其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。 NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上), 因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。 由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。 #Memory 存储引擎 正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。(在重启mysql或者重启机器后,表内数据清空) 它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。 Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。 #Infobright 存储引擎 第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。 其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。 #NTSE 存储引擎 网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。 #BLACKHOLE 黑洞存储引擎,可以应用于主备复制中的分发主库。 往表内插入任何数据,都相当于丢入黑洞,表内永远不存记录。 MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。 如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。
3、SQL语句 查看/使用 存储引擎
# 1 查看所有的存储引擎
show engines\G; #查看所有支持的存储引擎
show variables like 'storage_engine%'; #查看正在使用的存储引擎 (MySQL 一般是使用 InnoDB)# 2 不同的存储引擎在存储表的时候 异同点
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;# 存数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
# 3 使用存储引擎
==1 方法1:建表时指定
create table innodb_t1(id int,name char)engine=innodb;
==2 方法2:在配置文件中指定默认的存储引擎
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1
二 、表介绍
表相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段。
三 、创建表
# 语法 create table 表名( 字段名1 类型(宽度) 约束条件, 字段名2 类型(宽度) 约束条件, 字段名3 类型(宽度) 约束条件 )
create table 表名( 字段1 类型[(宽度) 约束条件], 字段2 类型[(宽度) 约束条件] ); #最后一个字段后面一定不能加逗号 #注意: 1. 在同一张表中,字段名是不能相同 2. 宽度和约束条件可选 3. 字段名和类型是必须要有的 4. 表中最后一个字段后面一定不能加逗号 # -------------------------------------------》 create database db1 charset utf8; # 需要先创建数据库,再建表 use db1; # 然后 选择所要使用的数据库 create table t1(id int,name char); # 建表至少要有一个字段 create table innodb_t1(id int,name char) engine=innodb; # 指定存储引擎 create table t3(name varchar(10) unique); # 设置单列唯一,则该字段不能有重复的值 create table t6( id int primary key auto_increment, # 将id字段 设为自增主键 name varchar(5) # 最后一个字段,一定不能加逗号 ); create table t2(x int not null default 111); # 设置表t2的x字段不为空,若为空 则取默认值 111 create table shirts ( name varchar(40), size enum('x-small', 'small', 'medium', 'large', 'x-large') ); # 枚举类型的字段 create table user ( name varchar(16), hobbies set("read","chou","drink","tang") ); # 集合类型的字段 show tables; #查看db1库下所有表名 desc t1; # 查看 表 t1 的结构 select * from t1; #显示表 t1的全部数据,刚创建的表 是一张空表 select id from t1; insert into t1 values (1,'cc'),(2,'mili'),(3,'mela'); # 给表里已有字段 添加数据内容(与新增字段要区分噢) select * from t1; #显示表 t1的全部数据 insert into t1(id) values (4),(5); # 指定字段,新增数据 select * from t1; #显示表 t1的全部数据 insert into author2book(author_id,book_id) values (1,1), # 依次匹配关系表的字段 (1,2), (1,3) ;
【注意】
1 在同一张表中字段名不能重复
2 宽度和约束条件是可选的(可写可不写) 而字段名和字段类型是必须的
约束条件写的话 也支持写多个
字段名1 类型(宽度) 约束条件1 约束条件2...,
create table t5(id); 报错3 最后一行不能有逗号
create table t6(
id int,
name char,
); 会报错
【补充】
# 宽度
一般情况下指的是对存储数据的限制
create table t7(name char); 默认宽度是1
insert into t7 values('jason');
insert into t7 values(null); 关键字NULL【针对不同的版本会出现不同的效果】
5.6版本默认没有开启严格模式,规定只能存一个字符。你给了多个字符,那么我会自动帮你截取。
5.7版本及以上或者开启了严格模式,那么规定只能存几个,就不能超,一旦超出范围立刻报错 Data too long for ....
【严格模式到底开不开呢?】
MySQL5.7之后的版本默认都是开启严格模式的。【使用数据库的准则】
能尽量少的让数据库干活就尽量少,不要给数据库增加额外的压力【约束条件 null not null不能插入null】????
create table t8(id int,name char not null);【宽度和约束条件到底是什么关系】
宽度是用来限制数据的存储
约束条件是在宽度的基础之上增加的额外的约束
四 、查看表结构
show tables; # 查看所在的库里的所有表 show create table t1; # 查看指定的表的详细结构 # 与 describe t1; 的打印格式不同,内容是一样的 show create table t1\G; #查看表详细结构,可加\G describe t1; # 查看 表 t1 的结构(结果为表格形式) desc t1; # 查看 表 t1 的结构(简写) select * from t1; # 查看表的所有字段以及记录(查看表的数据) # select * from 表名; select name from t1; # 查看表的name字段下的数据 # select 字段名 from 表名;
五 、修改表 ALTER TABLE
https://www.runoob.com/mysql/mysql-alter.html
# MySQL对大小写是不敏感的 #1 修改表名 alter table 表名 rename 新表名; #2 增加字段 alter table 表名 add 字段名 字段类型(宽度) 约束条件; alter table 表名 add 字段名 字段类型(宽度) 约束条件 first; alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名; #3 删除字段 alter table 表名 drop 字段名; #4 修改字段 alter table 表名 modify 字段名 字段类型(宽度) 约束条件; alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
# 修改表名 rename # alter table 原表名 rename 新表名; alter table t1 rename tt1; # 表名 命名要规范 # 修改字段 modify / change # alter table 表名 modify 目标字段名 字段的新类型; # alter table 表名 change 目标字段名 字段的新名字 字段的新类型; alter table t1 modify id tinyint; # 修改字段的类型 create table t3(id int default 1); alter table t3 modify id int not null default 1; # 修改字段不为空 alter table t1 change id ID tinyint; # 修改字段名 字段类型 # 修改字段名字,一定要重新指定字段类型 alter table t1 change id ID tinyint,change name NAME char(4); # 一次修改多个字段名的属性 # 增加字段 add / add … after # alter table 表名 add 字段名 数据类型 [完整性约束条件…]; alter table t1 add gender char(4); # 默认在表的末尾新增字段 alter table t1 add gender char(4) first; # 在表的第一位置新增字段 alter table t1 add level int after ID; # 在指定字段后面,新增字段 # 删除字段 drop # alter table 表名 drop 字段名; alter table t1 drop gender;
--1 使用 ALTER 来修改字段的默认值,尝试以下实例: mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | 1000 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) -- 2 你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值,如下实例: mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) Changing a Table Type: -- 3 修改存储引擎:修改为myisam alter table tableName engine=myisam; -- 4 删除外键约束:keyName是外键别名 alter table tableName drop foreign key keyName; -- 5 修改字段的相对位置:这里name1为想要修改的字段,type1为该字段原来类型,first和after二选一,这应该显而易见,first放在第一位,after放在name2字段后面. alter table tableName modify name1 type1 first|after name2;
六 、复制表
https://www.runoob.com/mysql/mysql-clone-tables.html
""" 我们sql语句查询的结果其实也是一张虚拟表 """ create table 表名 select * from 旧表; 不能复制主键 外键 ... create table new_dep2 select * from dep where id>3;
==复制表的结构和指定字段内容 # create table 新表 select 目标字段1,目标字段2,目标字段3 from 原表; select user(); create table t2 select user,host,password from mysql.user; 【分析如下:】 (1) 选择所需字段 select user(); select user,host,password from mysql.user; ===最终结果为 虚拟表, ===虚拟表--查询结果按照一定表格式显示,但是不存在于硬盘里 (2)将虚拟表存入具体的一张表(存入硬盘文件) create table t2 select user,host,password from mysql.user; ==只复制表的结构 (1)增加使得查询为空的条件 # create table 新表 select 目标字段1,目标字段2,目标字段3 from 原表 where 一个结果为False的任意条件;
create table t3 select user,host,password from mysql.user where 1!=1;
# 这样查询结果为空,因为没有满足条件的数据;但是有表结构,是一张空表 (2)使用 like create table t4 like 复制目标表的表名; create table t4 like t1;
七 、删除表
# 删除整张表(移除表,包括表结构和记录) drop table t1; # drop table 表名; # 删除表的数据内容(清空表数据,表结构还在) delete from t1; # delete一条一条地删除记录。对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长 # =应该用truncate清空表。# 比起delete一条一条地删除记录,truncate是直接清空表,且自增字段会清空重新从1记录,在删除大表时用它 truncate t1;
补充
MySQL临时表
--MySQL 临时表在我们需要保存一些临时数据时是非常有用的。 临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。 也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。 使用 SHOW TABLES命令显示数据表列表时,你将无法看到 创建的临时表。 -- 创建临时表 mysql> CREATE TEMPORARY TABLE SalesSummary ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO SalesSummary -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> ('cucumber', 100.25, 90, 2); --查看临时表 mysql> SELECT * FROM SalesSummary; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber | 100.25 | 90.00 | 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec) mysql> DROP TABLE SalesSummary; -- 手动删除临时表 mysql> SELECT * FROM SalesSummary; ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist --用查询直接创建临时表的方式: CREATE TEMPORARY TABLE 临时表名 AS ( SELECT * FROM 旧的表名 LIMIT 0,10000 );
八 、数据类型
=1、数字类型 ==1、整型 整型类型的存储宽度,不同整型类型是不一样的,且有默认值,不需要指定存储宽度。 表里的字段类型 int(11) 里面的数字 是整型数字的显示宽度限制,不是存储宽度限制。整型的存储宽度 是固定的。 整数类型:TINYINT SMALLINT MEDIUMINT INT BIGINT 作用:存储年龄,等级,id,各种号码等 【栗子】 mysql> create table t5(id tinyint) mysql> desc t4; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> insert t4 values(128); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> insert t4 values(127); Query OK, 1 row affected (0.05 sec) mysql> select * from t4; +------+ | id | +------+ | 127 | +------+ 1 row in set (0.00 sec) ==2、浮点类型 定点数类型 DEC (等同于DECIMAL) 浮点类型:FLOAT DOUBLE 作用:存储薪资、身高、体重、体质参数等 =2、字符串 char (定长-字符长度) varchar (变长-字符长度) # InnoDB存储引擎:建议使用VARCHAR类型 # char类型:定长,简单粗暴,浪费空间,存取速度快 字符长度范围:0 - 255(一个中文是一个字符,是utf8编码的3个字节) 存储: 存储char类型的值时,会往右填充空格来满足长度 例如:指定长度为10,存 > 10个字符则报错,存 < 10个字符则用空格填充直到凑够10个字符存储 检索: 在检索或者说查询时,查出的结果会自动删除尾部的空格,除非我们打开pad_char_to_full_length SQL模式(SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';) # varchar类型:变长,精准,节省空间,存取速度慢 字符长度范围:0 - 65535(如果大于21845会提示用其他类型 。 mysql行最大限制为65535字节,字符编码为utf - 8 参考: https: // dev.mysql.com / doc / refman / 5.7 / en / column - count - limit.html) 存储: varchar类型存储数据的真实内容,不会用空格填充,如果'ab ', 尾部的空格也会被存起来 强调: varchar类型会在真实数据前加1 - 2Bytes的前缀,该前缀用来表示真实数据的bytes字节数(1 - 2Bytes最大表示65535个数字,正好符合mysql对row的最大字节限制,即已经足够使用) 如果真实的数据 < 255bytes则需要1Bytes的前缀(1Bytes = 8bit 2 ** 8最大表示的数字为255) 如果真实的数据 > 255bytes则需要2Bytes的前缀(2Bytes = 16bit 2 ** 16最大表示的数字为65535) 检索: 尾部有空格会保存下来,在检索或者说查询时,也会正常显示包含空格在内的内容 -------------》精简笔记 char 定长,不够则补全空格 看起来特点: 浪费空间 读取速度快 varchar 变长,预留1-2bytes来存储真实数据的长度 看起来特点: 节省空间 读取速度慢 ps:在存储的数据量刚好达到存储宽度限制时,其实varchar更费空间 总结:大多数情况下存储的数据量都达不到宽度限制,所以大多数情况下varchar更省空间 但省空间不是关键,关键是省空间 会带来io效率的提升,进而提升了查询效率 ab |abc |abcd | 1bytes+ab|1bytes+abc|1bytes+abcd| 【举例】 create table t11(x char(5)); create table t12(x varchar(5)); insert t11 values("我擦嘞 "); -- "我擦嘞 " insert t12 values("我擦嘞 "); -- "我擦嘞 " t11=>字符个数 5 字节个数 11 t12=>字符个数 4 字节个数 10 set sql_mode="pad_char_to_full_length"; select char_length(x) from t11; select char_length(x) from t12; select length(x) from t11; select length(x) from t12; =3、时间类型 year date time datetime timestamp 注意: 1. 单独插入时间时,需要以字符串的形式,按照对应的格式插入 2. 插入年份时,尽量使用4位值 3. 插入两位年份时,<=69,以20开头,比如50, 结果2050 >=70,以19开头,比如71,结果1971 # ====================== YEAR YYYY(1901/2155) DATE YYYY-MM-DD(1000-01-01/9999-12-31) TIME HH:MM:SS('-838:59:59'/'838:59:59') DATETIME YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59 Y) TIMESTAMP YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时) 【举例】 create table t8(y year,t time,d date,dt datetime,ts timestamp); insert t8 values(now(),now(),now(),now(),now()); create table student( id int, name char(10), born_year year, bitrh date, reg_time datetime ); insert student values (1,"wangjing","1911","1911-11-11","1911-11-11 11:11:11"), (2,"lxx","1988","1988-11-11","1988-11-11 11:11:11"); insert student values (3,"wangjing","1911","19111111","19111111111111"); # 注意:timestamp应该用于记录更新时间 create table t9( id int, name varchar(16), -- update_time datetime not null default now() on update now(), update_time timestamp, reg_time datetime not null default now() ); insert into t9(id,name) values(1,"egon"); # 测试效果 mysql> select * from t9; +------+------+---------------------+---------------------+ | id | name | update_time | reg_time | +------+------+---------------------+---------------------+ | 1 | egon | 2020-09-01 16:45:51 | 2020-09-01 16:45:51 | +------+------+---------------------+---------------------+ 1 row in set (0.00 sec) mysql> update t9 set name="EGON" where id=1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t9; +------+------+---------------------+---------------------+ | id | name | update_time | reg_time | +------+------+---------------------+---------------------+ | 1 | EGON | 2020-09-01 16:46:50 | 2020-09-01 16:45:51 | +------+------+---------------------+---------------------+ 1 row in set (0.00 sec) =4、枚举类型与集合类型 枚举类型enum("a","b","c","d") 多选1 enum 单选 只能在给定的范围内选一个值,如性别 sex 男male/女female 集合类型set("a","b","c","d") 多选 set 多选 在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3...) 【举例】 create table shirts ( name VARCHAR(40), size ENUM('x-small', 'small', 'medium', 'large', 'x-large') ); INSERT INTO shirts(name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small'); create table user ( name VARCHAR(16), hobbies set("read","chou","drink","tang") ); insert user values("lxx","tang,chou"); insert user values("hxx","tangchou");
1、整型
TINYINT SMALLINT MEDUIMINT INT BIGINT
作用
""" 以TINYINT 是否有符号 默认情况下是带符号的 超出会如何 超出限制只存最大可接受值 """ create table t9(id tinyint); insert into t9 values(-129),(256); # 约束条件之unsigned 无符号 create table t10(id tinyint unsigned); create table t11(id int); # int默认也是带符号的 # 整型默认情况下都是带有符号的 # 针对整型 括号内的宽度到底是干嘛的 create table t12(id int(8)); insert into t12 values(123456789); """ 特例:只有整型括号里面的数字不是表示限制位数 id int(8) 如果数字没有超出8位 那么默认用空格填充至8位 如果数字超出了8位 那么有几位就存几位(但是还是要遵守最大范围) """ create table t13(id int(8) unsigned zerofill); # 用0填充至8位 # 总结: 针对整型字段 括号内无需指定宽度 因为它默认的宽度以及足够显示所有的数据了
# 如何查看严格模式 show variables like "%mode"; 模糊匹配/查询 关键字 like %:匹配任意多个字符 _:匹配任意单个字符 # 修改严格模式 set session 只在当前窗口有效 set global 全局有效 set global sql_mode = 'STRICT_TRANS_TABLES'; 修改完之后 重新进入服务端即可
2、浮点型
FLOAT、DOUBLE、DECIMAL
作用
# 存储限制 float(255,30) # 总共255位 小数部分占30位 double(255,30) # 总共255位 小数部分占30位 decimal(65,30) # 总共65位 小数部分占30位 # 精确度验证 create table t15(id float(255,30)); create table t16(id double(255,30)); create table t17(id decimal(65,30)); """你们在前期不要给我用反向键 所有的命令全部手敲!!!增加熟练度""" insert into t15 values(1.111111111111111111111111111111); insert into t16 values(1.111111111111111111111111111111); insert into t17 values(1.111111111111111111111111111111); float < double < decimal # 要结合实际应用场景 三者都能使用
3、日期类型
date:年月日 2020-5-4
datetime:年月日时分秒 2020-5-4 11:11:11
time:时分秒11:11:11
create table student( id int, name varchar(16), born_year year, birth date, study_time time, reg_time datetime ); insert into student values(1,'egon','1880','1880-11-11','11:11:11','2020-11-11 11:11:11');
4、字符串类型
""" char 定长 char(4) 数据超过四个字符直接报错 不够四个字符空格补全 varchar 变长 varchar(4) 数据超过四个字符直接报错 不够有几个存几个 """ create table t18(name char(4)); create table t19(name varchar(4)); insert into t18 values('a'); insert into t19 values('a'); # 介绍一个小方法 char_length统计字段长度 select char_length(name) from t18; select char_length(name) from t19; """ 首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的 但是在显示的时候MySQL会自动将多余的空格剔除 """ # 再次修改sql_mode 让MySQL不要做自动剔除操作 set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
""" char 缺点:浪费空间 优点:存取都很简单 直接按照固定的字符存取数据即可 jason egon alex wusir tank 存按照五个字符存 取也直接按照五个字符取 varchar 优点:节省空间 缺点:存取较为麻烦 1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank 存的时候需要制作报头 取的时候也需要先读取报头 之后才能读取真实数据 以前基本上都是用的char 其实现在用varchar的也挺多 """ 补充: 进来公司之后你完全不需要考虑字段类型和字段名 因为产品经理给你发的邮件上已经全部指明了
5、枚举类型与集合类型
分类:
"""
枚举(enum) :多选一
集合(set): 多选多
"""
create table user( id int, name char(16), gender enum('male','female','others') ); insert into user values(1,'jason','male'); 正常 insert into user values(2,'egon','xxxxooo'); 报错 # 枚举字段 后期在存数据的时候只能从枚举里面选择一个存储 create table teacher( id int, name char(16), gender enum('male','female','others'), hobby set('read','DBJ','hecha') ); insert into teacher values(1,'jason','male','read'); 正常 insert into teacher values(2,'egon','female','DBJ,hecha'); 正常 insert into teacher values(3,'tank','others','生蚝'); 报错 # 集合可以只写一个 但是不能写没有列举的
补充:
MySQL 检查数据长度,可用 SQL 语言来查看:
select length(字段名) from 表名
九 、表完整性约束
约束条件与数据类型的宽度一样,都是可选参数 作用:用于保证数据的完整性和一致性 主要分为: PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录 FOREIGN KEY (FK) 标识该字段为该表的外键 NOT NULL 标识该字段不能为空 UNIQUE KEY (UK) 标识该字段的值是唯一的 AUTO_INCREMENT 标识该字段的值自动增长(整数类型,而且为主键) DEFAULT 为该字段设置默认值 UNSIGNED 无符号 ZEROFILL 使用0填充 说明: 1. 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值 2. 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值 sex enum('male','female') not null default 'male' age int unsigned NOT NULL default 20 必须为正值(无符号) 不允许为空 默认是20 3. 是否是key 主键 primary key 外键 foreign key 索引 (index,unique...) # -------------------------------------------------------- =1 not null [default 默认值] #不为空/默认值 null表示空,非字符串 (不声明是否为空,则默认可以为空) not null - 不可空 null - 可空 # --------------》 create table t1(id int); #id字段默认可以插入空 insert into t1 values(); #可以插入空 create table t1(x int not null); # 设置表t1的x字段不为空 insert into t1 values(); #不能插入空 # ERROR 1364 (HY000): Field 'id' doesn't have a default value #设置id字段有默认值后,则无论id字段是null还是not null,都可以插入空,插入空默认填入default指定的默认值 create table t2(x int not null default 111); # 设置表t2的x字段不为空,若为空 则取默认值 111 insert into t2 values(); =2 unique # 单列唯一 # 方法一: 表名(字段名 字段类型 unique) create table t3(name varchar(10) unique); # 设置单列唯一,则该字段不能有重复的值 insert into t3 values('egon'); insert t3 values('mili'); mysql> insert into t3 values("egon"); # ERROR 1062 (23000): Duplicate entry 'egon' for key 'name' # 方法二:constraint uk_name unique(字段名) create table department2( id int, name varchar(20), comment varchar(100), constraint uk_name unique(name) ); # 联合唯一 create table server( id int, name varchar(10), ip varchar(15), port int, unique(ip,port), # 设置 ip 和 port,联合唯一 unique(name) # 设置 name 为 单列唯一 ); # 验证 insert into server values (1,"web1","10.10.0.11",8080); insert into server values (2,"web2","10.10.0.11",8081); mysql> insert into server values(4,"web4","10.10.0.11",8081); # ERROR 1062 (23000): Duplicate entry '10.10.0.11-8081' for key 'ip' =3 not null 和 unique 的化学反应=>会被识别成表的主键 create table t4(id int,name varchar(10) not null unique); # 设置字段 name 单列唯一,且不为空==》 主键 create table t5(id int,name varchar(10) unique); # 设置字段 name单列唯一 =4 主键 primary key =在查询时,尽量使用主键字段为查询依据 =在建表时,自己设置主键字段,一般以 id 作为主键 =主键的约束效果:不为空,且唯一 / not null+unique =主键primary key是innodb存储引擎组织数据的依据,innodb称之为索引组织表, 一张innodb表中必须有且只有一个主键,但是该主键可以是联合主键 # 单列做主键 # =方法一 not null+unique create table department1( id int not null unique, #主键 comment varchar(100) ); # =方法二 在某一个字段后用primary key,也可加上 自增 auto_increment create table t6( id int primary key auto_increment, # id 作为主键,且自增 name varchar(5) ); insert into t6(name) values ("egon"),("tom"),("to1"),("to2"); # =方法三 在所有字段后单独定义primary key create table department3( id int, name varchar(20), comment varchar(100), constraint pk_name primary key(id)); #创建主键并为其命名pk_name # 联合主键(了解) create table t7( id int, name varchar(5), primary key(id,name) # 联合主键 ); =5 外键 foreign key (在虚拟表字段 用 MUL 表示外键) =先创建 被关联的表(外键所指向的表,又被称为父表),再创建 包含外键的表(又被称为子表) 例如--- 设定 A表关联B表,外键字段设在A表;则在创建表时,先创建 B表(父表)并插入数据,再创建 A表(子表) 插入数据 =外键一般设置在字段多的一方,关联少的一方 =表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一 =级联更新(更新列(表头标题为横向的时候)),指的是有关联的字段名包含的所有记录 同步更新(都变为同样的记录); 级联删除(删除行(表头标题为横向的时候)),则是包含关联字段的所有记录 同步删除 【栗子】 #删父表department,子表employee中对应的记录跟着删 mysql> delete from department where id=3; # 与之关联的employee表中,dpt_id为3的记录行 都被删除 mysql> select * from employee; +----+-------+--------+ | id | name | dpt_id | +----+-------+--------+ | 1 | egon | 1 | | 2 | alex1 | 2 | | 3 | alex2 | 2 | | 4 | alex3 | 2 | +----+-------+--------+ #更新父表department,子表employee中对应的记录跟着改 mysql> update department set id=22222 where id=2; # 修改id,则与之关联的表中 与原id相同的id 也会跟着改 mysql> select * from employee; +----+-------+--------+ | id | name | dpt_id | +----+-------+--------+ | 1 | egon | 1 | | 3 | alex2 | 22222 | | 4 | alex3 | 22222 | | 5 | alex1 | 22222 | +----+-------+--------+ =6 auto_increment (1) create table t6( id int primary key auto_increment, # id 作为主键,且自增 name varchar(5) ); # 约束字段为自动增长,被约束的字段必须同时被key约束 # 插入字段数据时如果不指定id,则id会自动增长;也可以指定id。 (2) # 清空表数据 # =对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长 delete from t1; # 删除表的数据内容,表结构还在 # =应该用truncate清空表。# 比起delete一条一条地删除记录,truncate是直接清空表,且自增字段会清空重新从1记录,在删除大表时用它 truncate t1; (3) 自定义“自增” # =1 设定 自增起始值 #在创建完表后,修改自增字段的起始值 create table t6( id int primary key auto_increment, # id 作为主键,且自增 name varchar(5) ); alter table t6 auto_increment=3; #也可以创建表时指定auto_increment的初始值,注意初始值的设置为表选项,应该放到括号外 create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' )auto_increment=3; # =2 设定自增步长 # sqlserver:自增步长 # 基于表级别 create table t1( id int。。。 )engine = innodb, auto_increment = 2 步长 = 2 default charset = utf8 # mysql自增的步长: show session variables like 'auto_inc%'; # 基于会话级别 set session auth_increment_increment = 2 # 修改会话级别的步长 # 基于全局级别的 set global auth_increment_increment=2 # 修改全局级别的步长(所有会话都生效) # 注意: auto_increment_offset 的值 需要小于 auth_increment_increment 的值, 否则 auto_increment_offset 的值会被忽略
1、介绍
约束条件与数据类型的宽度一样,都是可选参数
作用:用于保证数据的完整性和一致性 主要分为:
PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长(整数类型,而且为主键)
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
说明:
1. 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值
2. 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,
此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 #必须为正值(无符号) 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)
2、not null与default
是否可空,null表示空,非字符串 not null - 不可空 null - 可空
默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
create table tb1( nid int not null defalut 2, num int not null )
3、 unique
============设置唯一约束 UNIQUE=============== 方法一: create table department1( id int, name varchar(20) unique, comment varchar(100) ); 方法二: create table department2( id int, name varchar(20), comment varchar(100), constraint uk_name unique(name) ); mysql> insert into department1 values(1,'IT','技术'); Query OK, 1 row affected (0.00 sec) mysql> insert into department1 values(1,'IT','技术'); ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
mysql> create table t1(id int not null unique); Query OK, 0 rows affected (0.02 sec) mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)
create table service( id int primary key auto_increment, name varchar(20), host varchar(15) not null, port int not null, unique(host,port) #联合唯一 ); mysql> insert into service values -> (1,'nginx','192.168.0.10',80), -> (2,'haproxy','192.168.0.20',80), -> (3,'mysql','192.168.0.30',3306) -> ; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80); ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
4、primary key
从约束角度看primary key字段的值不为空且唯一,那我们直接使用not null+unique不就可以了吗,要它干什么?
主键primary key是innodb存储引擎组织数据的依据,innodb称之为索引组织表,一张表中必须有且只有一个主键。
一个表中可以:
单列做主键 多列做主键(复合主键)
============单列做主键=============== #方法一:not null+unique create table department1( id int not null unique, #主键 name varchar(20) not null unique, comment varchar(100) ); mysql> desc department1; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | UNI | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.01 sec) #方法二:在某一个字段后用primary key create table department2( id int primary key, #主键 name varchar(20), comment varchar(100) ); mysql> desc department2; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.00 sec) #方法三:在所有字段后单独定义primary key create table department3( id int, name varchar(20), comment varchar(100), constraint pk_name primary key(id); #创建主键并为其命名pk_name mysql> desc department3; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.01 sec)
==================多列做主键================ create table service( ip varchar(15), port char(5), service_name varchar(10) not null, primary key(ip,port) ); mysql> desc service; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ip | varchar(15) | NO | PRI | NULL | | | port | char(5) | NO | PRI | NULL | | | service_name | varchar(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into service values -> ('172.16.45.10','3306','mysqld'), -> ('172.16.45.11','3306','mariadb') -> ; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into service values ('172.16.45.10','3306','nginx'); ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
5、auto_increment
约束字段为自动增长,被约束的字段必须同时被key约束
#不指定id,则自动增长 create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> desc student; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+----------------+ mysql> insert into student(name) values -> ('egon'), -> ('alex') -> ; mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | | 2 | alex | male | +----+------+------+ #也可以指定id mysql> insert into student values(4,'asb','female'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(7,'wsb','female'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+------+--------+ | id | name | sex | +----+------+--------+ | 1 | egon | male | | 2 | alex | male | | 4 | asb | female | | 7 | wsb | female | +----+------+--------+ #对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长 mysql> delete from student; Query OK, 4 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> insert into student(name) values('ysb'); mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 8 | ysb | male | +----+------+------+ #应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它 mysql> truncate student; Query OK, 0 rows affected (0.01 sec) mysql> insert into student(name) values('egon'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | +----+------+------+ 1 row in set (0.00 sec)
6、foreign key
外键就是用来帮助我们建立表与表之间关系的
foreign key
十、 表的关系
=1、如何找出两张表之间的关系? ---一句话---》左顾右盼,再下定论 分析步骤: #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id) #2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id) #3、总结: #多对一: 如果只有步骤1成立,则是左表多对一右表 如果只有步骤2成立,则是右表多对一左表 #多对多 如果步骤1和2同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 #一对一: 如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然。这种情况很简单,就是在左表foreign key右表的基础上,将左表的外键字段设置成unique即可 =2、建立表之间的关系 ==1、多对一(一对多)----- foreign key foreign key(press_id) references press(id) on delete cascade on update cascade # foreign key(press_id) references press(id) #设置 press_id 为外键字段, # on delete cascade # 设置级联删除 # on update cascade # 设置级联更新 【栗子1】 表:出版社,书 一对多(或多对一):一个出版社可以出版多本书 关联方式:foreign key create table book ( id int primary key auto_increment, name varchar(10), press_id int not null, foreign key(press_id) references press(id) on delete cascade on update cascade ); insert into press(name) values ('北京工业地雷出版社'), ('人民音乐不好听出版社'), ('知识产权没有用出版社'); insert into book(name,press_id) values ('九阳神功',1), ('九阴真经',2), ('九阴白骨爪',2), ('独孤九剑',3), ('降龙十巴掌',2), ('葵花宝典',3); ==2、多对多-----foreign key+一张新的表 A2B constraint fk_author foreign key(book_id) references book(id) on delete cascade on update cascade, primary key(author_id,book_id) # 联合主键 【栗子2】 表:作者信息,书 多对多:一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多 关联方式:foreign key+一张新的表 A2B create table author( id int primary key auto_increment, name varchar(20) ); # 再建一张表,用于存放关系 # 这张表就存放 作者表 与 书表 的关系,即查询二者的关系查这表就可以了 create table author2book( id int not null unique auto_increment, author_id int not null, book_id int not null, constraint fk_author foreign key(book_id) references book(id) on delete cascade on update cascade, primary key(author_id,book_id) # 联合主键 ); insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq'); insert into author2book(author_id,book_id) values (1,1), # 依次匹配关系表的字段 (1,2), (1,3) ; ==3、一对一 ----- foreign key+unique seat_id int unique, #该字段一定要是唯一的 foreign key(seat_id) references seat(id) on delete cascade on update cascade # 外键的字段一定要保证unique 【栗子】 两张表:座位表和客户表 一对一:一个座位只能坐一个客户,一个客户只能有一个座位 关联方式:foreign key+unique create table seat( id int primary key auto_increment, area varchar(10) ); create table client( id int primary key auto_increment, name varchar(10), seat_id int unique, #外键的字段一定要保证unique foreign key(seat_id) references seat(id) on delete cascade on update cascade );
表与表之间最多只有四种关系:
1、一对多关系(在MySQL的关系中没有/多对一/一说,一对多 多对一 都叫一对多)
2、多对多关系
3、一对一关系
4、没有关系
""" 判断表与表之间关系的时候 前期不熟悉的情况下 一定要按照我给你的建议 换位思考 分别站在两张表的角度考虑 员工表与部门表为例 先站在员工表 思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据) 不能!!! (不能直接得出结论 一定要两张表都考虑完全) 再站在部门表 思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据) 能!!! 得出结论 员工表与部门表示单向的一对多 所以表关系就是一对多 """ foreign key 1 一对多表关系 外键字段建在多的一方 2 在创建表的时候 一定要先建被关联表 3 在录入数据的时候 也必须先录入被关联表 # SQL语句建立表关系 create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) ); insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门'); insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3); # 修改dep表里面的id字段 update dep set id=200 where id=2; 不行 # 删除dep表里面的数据 delete from dep; 不行 # 1 先删除教学部对应的员工数据 之后再删除部门 操作太过繁琐 # 2 真正做到数据之间有关系 更新就同步更新 删除就同步删除 """ 级联更新 >>> 同步更新 级联删除 >>> 同步删除 """ create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门'); insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
""" 图书表和作者表 """ create table book( id int primary key auto_increment, title varchar(32), price int, author_id int, foreign key(author_id) references author(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); create table author( id int primary key auto_increment, name varchar(32), age int, book_id int, foreign key(book_id) references book(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); """ 按照上述的方式创建 一个都别想成功!!! 其实我们只是想记录书籍和作者的关系 针对多对多字段表关系 不能在两张原有的表中创建外键 需要你单独再开设一张 专门用来存储两张表数据之间的关系 """ create table book( id int primary key auto_increment, title varchar(32), price int ); create table author( id int primary key auto_increment, name varchar(32), age int ); create table book2author( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade # 同步更新 on delete cascade, # 同步删除 foreign key(book_id) references book(id) on update cascade # 同步更新 on delete cascade # 同步删除 );
""" id name age addr phone hobby email........ 如果一个表的字段特别多 每次查询又不是所有的字段都能用得到 将表一分为二 用户表 用户表 id name age 用户详情表 id addr phone hobby email........ 站在用户表 一个用户能否对应多个用户详情 不能!!! 站在详情表 一个详情能否属于多个用户 不能!!! 结论:单向的一对多都不成立 那么这个时候两者之间的表关系 就是一对一 或者没有关系(好判断) 客户表和学生表 在你们报名之前你们是客户端 报名之后是学生(期间有一些客户不会报名) """ 一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中 create table authordetail( id int primary key auto_increment, phone int, addr varchar(64) ); create table author( id int primary key auto_increment, name varchar(32), age int, authordetail_id int unique, foreign key(authordetail_id) references authordetail(id) on update cascade # 同步更新 on delete cascade # 同步删除 )
总结:
""" 表关系的建立需要用到foreign key-------- 一对多 外键字段建在多的一方 多对多 自己开设第三张存储 一对一 建在任意一方都可以 但是推荐你建在查询频率较高的表中 判断表之间关系的方式-------- 换位思考!!! 员工与部门 图书与作者 作者与作者详情 """
参考资料:
🐱不负韶华,只争朝夕🍚