MySQL数据库（库表管理）

大白鲨555
126 阅读24分钟

前言

近几年来，MySQL 的发展很快，在内核层的变化也很大，同时伴随着云原生的发展，MySQL 也是云上数据库的主流产品。

图片

几乎所有大型互联网业务场景中，MySQL 都是业务架构的核心组件之一。

广泛的应用也推动了 MySQL 在过去十年的高速发展，MySQL 社区相继推出了 5.6、5.7、8.0 版本，从性能、可扩展性、安全性、稳定性、可维护性、易用性等维度都有了非常大的发展。

MySQL具有功能强大、可靠性强和开源许可的优点。Web开发人员最大限度地使用MySQL，将其应用在很多场景中，并且从个人网站到大型的企业应用，它都表现得足够灵活。

MySQL 提供了合理的默认值，基本不需要进行任何调整。任何一个团队都能更好地处理模式更改、合规性问题和分片。

极客能使用先进工具来大规模运行MySQL，确保这个世界上数据的安全，也将确保全球顶级网站和其他数据驱动的系统在其业务峰值时性能的卓越。

MySQL的数据类型

主要包括以下五大类：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型：Date、DateTime、TimeStamp、Time、Year

其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

类型详细介绍跳转地址www.cnblogs.com/-xlp/p/8617…

常用数据类型

类型含义
tinyint(n)1个字节，范围(-128~127)
smallint(n)2个字节，范围(-32768~32767)
mediumint(n)3个字节，范围(-8388608~8388607)
int(n)4个字节(32个比特位)，整数型，范围(-2147483648~2147483647)
bigint(n)8个字节，整数型，范围(+-9.22*10的18次方)
float(m,d)单精度浮点，8位精度，4字节32位。m数字总个数，d小数位
double(m,d)双精度浮点，16位精度，8字节64位 。m总个数，d小数位
char固定长度的字符类型
varchar可变长度的字符类型
text文本
image图片
decimal(5,2)5个有效长度数字，小数点后面有2位（例如123.56）
  • int(N)
    int(N)中的N不是限制字段取值范围的，int的取值范围是固定的（0至4294967295）或（-2147483648至2147483647）。N这个值是为了zerofill在字段中的值不够时补零的。
    int默认是signed（有符号），取值范围（-2147483648至2147483647）。如果加了unsigned（ 无符号）参数那么取值范围就为（0至4294967295）。
  • float(m,d)
    设一个字段定义为float(6,3)，表示6个有效长度数字，小数点后面有3位。如果插入一个数123.45678，实际数据库里存的是123.457，但总个数还以实际为准，即6位。如果插入数12.123456，存储的是12.123，如果插入12.12，存储的是12.120。
    整数部分最大是3位，如果插入1234.56，会插入失败。
  • char与varchar
    CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

下表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：

值CHAR(4)存储需求VARCHAR(4)存储需求
''' '4个字节''1个字节
'ab''ab '4个字节'ab '3个字节
'abcd''abcd'4个字节'abcd'5个字节
'abcdefgh''abcd'4个字节'abcd'5个字节

字节大小：

  • char无论是否有值，都会占用固定长度的字节大小，保存在磁盘上都是4字节。
  • varchar在保存字符时，默认会加一个隐藏的结束符，因此结束符会多算一个字节。

优劣比较：

  • varchar比char节省磁盘空间。
  • 但varchar类型的数据读写速度比char慢，因为char是连续的磁盘空间，e而varchar在多次增删改查中会产生一些磁盘空间碎片。

SQL

SQL中的名词介绍

  • 数据库：database
  • 表：table，行：row 列：column
  • 索引：index
  • 视图：view
  • 存储过程：procedure
  • 存储函数：function
  • 触发器：trigger
  • 事件调度器：event scheduler，任务计划
  • 用户：user
  • 权限：privilege

数据库对象和命名：
数据库的组件(对象)：

  • 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等。

命名规则：

  • 必须以字母开头，后续可以包括字母，数字和三个特殊字符（# _ $）
  • 不要使用MySQL的保留字，如table、select、show、databases。
  • 数据库名、表名、用户名区分大小写

SQL语言分类

结构化查询语言(Structured Query Language)简称SQL。

SQL语言通常分为五类：

DDL(数据定义语言)：create、alter、drop等；
DQL(数据查询语言)：select等；
DML(数据操纵语言)：insert、delete 、update等；
TCL(事务控制语言)：commit、rollback等；
DCL(数据控制语言)：grant、revoke等。

  • DDL：数据定义语言（Data Definition Language），用于创建数据库对象，如库、表、索引等。
    如：CREATE，DROP，ALTER 等。
  • DML：数据操纵语言（Data Manipulation Language），用于对表中的数据进行管理。
    如： SELECT、UPDATE、INSERT、DELETE 等。
  • DQL：数据查询语言（ Data Query Languag ），用于从数据表中查找符合条件的数据记录。
    如： SELECT
  • DCL：数据控制语言（Data Control Language），用于设置或者更改数据库用户或角色权限
    如： GRANT，REVOKE
  • TCL：事务控制语言（Transaction Control Language），用于管理数据库中的事务。 TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面。
    如： COMMIT，ROLLBACK，SAVEPOINT

SQL语言规范

  • 在数据库系统中，SQL 语句不区分大小写，建议用大写。
  • SQL语句可单行或多行书写，默认以 " ; " 结尾。
  • 关键词不能跨多行或简写。
  • 用空格和TAB 缩进来提高语句的可读性。
  • 子句通常位于独立行，便于编辑，提高可读性。

查看数据库结构

mysql中的命令默认不区分大小写。表名和库名区分大小写。
在linux系统中的客户端，命令后一定要加分号。

查看当前服务器中的数据库

 SHOW DATABASES;     大小写不区分，分号“;"表示结束

查看数据库.png

查看数据库中包含的表

 方法一：
USE 数据库名;      #切换库
SHOW TABLES;      #查看库中的表

 方法二：
show tables from 数据库名；    #直接查看某个库中的表

切换到库.png

直接查看表.png

查看表的结构（字段）

 方法一：
USE 数据库名;       #切换库
DESCRIBE 表名;     #查看指定表的结构
方法二：
DESCRIBE [数据库名.]表名;
可缩写成：DESC 数据库名.表名;

use.png

缩写.png

创建及删除数据库和表

创建新的数据库

 CREATE DATABASE 数据库名;

例： creat database LOL;

新建表.png

创建新的表

语句格式：

 CREATE TABLE 表名(字段1 数据类型,字段2 数据类型[,...] [,PRIMARY KEY (主键名)]);

 主键一般选择能代表唯一性的字段，不允许取空值(NULL)，值也不允许重复，主键字段的值是唯一的。一个表只能有一个主键。

创建新表.png

查看新建表.png

USE LOL;                  #切换LOL库
CREATE TABLE class (id int NOT NULL,name char(10) NOT NULL,score decimal (5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id));        #新建表class

第一个字段名id，数据类型是int整数型，NOT NULL不许为空值。
第二个字段名name，数据类型是char固定长度的字符，长度是10个字节。
第三个字段名score，数据类型是decimal (5,2)，表示5个有效数字、其中小数点后2位。
第四个字段名passwd，DEFAULT '' 表示默认值是无值（不是空值null）。
PRIMARY KEY (id)，表示id字段是主键。
DESC class;    #查看class表的结构

查看表结构.png

删除指定的数据表

 DROP TABLE [数据库名.]表名;

 eg：DROP TABLE school;

删表.png

删除指定的数据库

 DROP DATABASE 数据库名;

eg：DROP DATABASE djq;

删除指定库.png

管理表中的数据记录（表数据的增删改查）

向数据表中插入新的数据记录（增insert）

格式：

 方法一：
为所有字段插入值
insert into 表名 values (所有字段的值);   #每个字段值用逗号相隔；

 方法二：
为指定字段插入值
INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值，...);    
#注意字段的属性not null，则必须为该字段插入值

插入数据并查看.png

eg：为表中所有字段插入值，此种方式密码会以明文显示。
INSERT INTO class01 VALUES(2, '张三丰', 10.1，123456);
#为指定字段插入值
INSERT INTO class01 (id, name, passwd) values (1, '张无忌', PASSWORD('123456'));
#PASSWORD ('123456')：查询数据记录时，密码字串以加密形式显示。若不使用PASSWORD()，查询时以明文显示。.
#下面这条命令不生效，因为name字段设置了not null，不允许为空值。
INSERT INTO class01 (id, score, passwd) values (3, 1.1, 123123);
SELECT * FROM class01;    #查询表的数据记录

查看hero表结构.png

为指定字段插入数据.png

密码加密.png

查询表数据记录（查select）

格式：

 SELECT * FROM 表名;   #查询表中所有记录

 按条件查找数据记录，且只显示指定字段的值
SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];

 表中的记录默认是横向展示的，当字段很多时显示很乱，可以将";"换成"\G"，改成纵向展示每条记录。
select * from 表名\G

 mysql的分页语句：
select * from 表名 limit 2;
select * from 表名 limit 2,3;   #显示第2行后的前3行（即显示第3~5行）
select * from 表名 limit 19,11; #查看第20行到第30行的记录

select * from 表名 id>=10 and id<=20; #查看id值在10到20之间的数据记录

eg: SELECT * FROM class;     #查看表中所有数据记录
SELECT id,name,skill FROM class WHERE id=2;   #查看id为2的数据记录，显示id、name、skill三个字段
SELECT * FROM class\G         #以列表方式竖向显示每个字段
SELECT * FROM class limit 2;     #只显示前2行
select * from class limit 2,3;   #显示第2行之后的前3行（即显示第3~5行）

查询id为2.png

纵向显示.png

limit.png

修改/更新数据表中的数据记录（改update）

修改字段一般要加上条件，不然会把所有行都修改了。

格式：

 UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

eg: UPDATE class SET passwd= PASSWORD(' ') WHERE name='张无忌';
#修改name为“张无忌”的数据记录，将其密码修改为以密文显示。
UPDATE class SET score=9,passwd=' ' WHERE id=1;
#修改id为1的数据记录，将score字段值改为9，passwd字段改为无值。
#建议使用唯一性比较好的字段作为指定条件，因为名称可能有重复，但id不会重复。

修改密码为空值.png

修改id2的密码.png

在数据表中删除指定的数据记录（删delete）

删除数据记录时，一定要加条件，否则会将整个表都删除

 DELETE FROM 表名 [WHERE 条件表达式];     #删除一定要加条件，不然会删除整个表

eg: DELETE FROM class WHERE id=2;     #删除id字段值为2的数据记录

删除id为2.png

修改表名和表结构

修改表名和表结构时，都要使用ALTER 语句！

修改表名 rename

 ALTER TABLE 旧表名 RENAME 新表名

eg： ALTER TABLE class RENAME wudang;   #将class的表名修改为wudang

修改表名.png

扩展表结构（增加字段） add

 ALTER TABLE 表名 ADD 字段名 数据类型;
eg：ALTER TABLE class ADD race varchar(20) not null default '种族未知';
#增加“race"字段，不允许为空值，默认值为“种族未知”。
#default '种族未知'：表示此字段设置默认值为"种族未知"，可与NOT NULL配合使用。

增加表字段.png

修改字段名，添加唯一健 change

CHANGE可修改字段名、数据类型、约束等所有项。

 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 [数据类型] [约束];

 eg：ALTER TABLE wudang CHANGE score height int unique key;
#将score字段名修改为height，数据类型修改为int整数型，并添加唯一键约束。

修改字段名.png

删除字段 drop

 ALTER TABLE 表名 DROP 字段名;

eg: ALTER TABLE wudang DROP name;     #删除name字段

删除name字段.png

添加主键约束 add primary key

方法1：创建表时设置主键
create table 表名(字段1 XXX, 字段2 XXX, ....primary key(字段));

 create table 表名(字段1 XXX primary key, . . ..);   #将主键作为字段1的属性

方法2：在现有表中添加主键
ALTER TABLE 表名 add primary key(字段名);

eg: ALTER TABLE emei add primary key(id);   #将id字段添加为主键

创建表时设置主键.png

添加主键.png

主键primary key 和 唯一键unique key：

共同点：字段的值都是唯一的，不允许有重复值。

不同点：

  • 自定义的表中只能有一个主键，但是可以有多个唯一键。
  • 主键字段中不允许有null值，唯一键允许有null值。

（系统的表中可以有多个主键）

MYSQL常见的约束

  • 主键约束（primary key） PK
  • 自增长约束（auto_increment）
  • 非空约束（not null）
  • 唯一性约束（unique）
  • 默认值约束（default）
  • 零填充约束（zerofill）
  • 外键约束（foreign key）FK

一件创建：主键约束、自增长约束、非空约束、唯一性约束、默认约束、零填充约束。

 use xuezhong;          #切换到xuezhong库
create table if not exists beiliang (               #创建表beiliang
id int(4) zerofill primary key auto_increment, #对id字段设置零填充约束、主键约束、自增长约束
name varchar(10) not null default '匿名',        #对name字段设置非空约束、默认值约束
height int(18) not null unique key,             #对cardid字段设置非空约束、唯一键约束
hobby varchar(50));

Snipaste_2022-10-23_18-22-13.png

----- if not exists:
 表示检测要创建的表是否已存在，如果不存在就继续创建。如果存在就忽略，不进行创建。
------ int(4) zerofill：
零填充约束，表示若数值不满4位数，则前面用“0”填充到4位，例0001，0012。
#int(11) zerofill：表示若数值不满11位数，则前面用“0”填充到11位，例如00000001234。
------ primary key：
 表示此字段有主键约束，此字段数据不可以重复且不允许为NULL，一张表中只能有一个主键。
------ auto_increment: 
 表示此字段为自增长字段，即每条记录自动递增1。
 一个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一索引，以避免序号重复。
 如果不指定则默认从1开始递增；
 自增长字段数据不可以重复；
 自增长字段必须是主键；
 如果添加的记录数据没有指定此字段的值，那么添加失败也会自动递增一次。
 auto_increment约束的字段只能是整数型。
------ unique key：
 表示此字段有唯一键约束，此字段数据不可以重复；一张表中只能有一个主键，但是一张表中可以有多个唯一键。
------ not null：
 表示此字段不允许为NULL。
-------- default '匿名'：
 表示该字段的默认值为“匿名”。
复制代码

插入数据记录：

  • 因为id字段设置了自增长，如果不指定id字段值，则默认从1开始递增；

int(4) zerofill ，设置了零填充约束，如果数值不满4位数，则前面用“0”填充到4位，所以下面案例中的id值为0001。

INSERT INTO beiliang(name,height,hobby) values('老黄',181,'fangniu');
复制代码

Snipaste_2022-10-23_18-26-27.png

  • 再次插入数据记录，不指定id字段值，则id值会自增1，此时为0002。
INSERT INTO beiliang(name,height,hobby) values('老白',182,'fangyang');
复制代码

Snipaste_2022-10-23_18-27-52.png

  • 自增长，如果添加失败也会自动递增一次，数值会被占用。
 下面两条命令会执行失败，因为cardid设置了唯一键不能重复。但执行失败，id值也会自增，0003、0004这两个数值会被占用。
 INSERT INTO beiliang(name,height,hobby) values('老黑',182,'fangyang');
 INSERT INTO beiliang(name,height,hobby) values('老绿',182,'fangyang');
 
 下面这条命令执行成功后，id值是0005，因为0003和0004被执行失败的命令占用了。
 INSERT INTO beiliang(name,height,hobby) values('老蓝',183,'fangyang');
复制代码

Snipaste_2022-10-23_18-31-59.png

  • 插入数据记录，指定id值为7。

则后面插入的数据就会从7开始自增1，即增长为8。

 INSERT INTO beiliang values(7,'老红',185,'fangzhu');     #指定id值为7
 
 INSERT INTO beiliang(name,height,hobby) values('老橙',188,'fangniao');   #后面插入的数据id值是8
复制代码

Snipaste_2022-10-23_18-36-17.png

  • cardid字段设置了唯一键约束，如果字段值重复，则会数据会插入失败。
 cardid重复，数据会添加失败
 INSERT INTO beiliang(name,height,hobby) values('老绿',188,'fangniao');
复制代码

Snipaste_2022-10-23_18-38-29.png

  • name字段设置了不允许为NULL，且默认值为“匿名”，则插入数据时如果不指定name字段的值，该字段会自动插入默认值“匿名”。
 INSERT INTO beiliang(height,hobby) values(188,'fangniao');
复制代码

Snipaste_2022-10-23_18-40-25.png

Myksql数据表的高级操作

克隆表，将数据表的数据记录生成到新的表中

先克隆表结构，再导入表数据。

格式：

 create table 新表名 like 旧表名;  #通过LIKE方法，复制旧表的结构生成新表
insert into 新表名 select * from 旧表名;  #再将旧表数据导入新表

 eg : create table daoqi like liyue;    #通过liyue表克隆一个daoqi表，这样只克隆表结构，没有表数据
insert into daoqi select * from liyue;    #将liyue的表数据导入daoqi中

Snipaste_2022-10-23_19-49-20.png

Snipaste_2022-10-23_19-50-58.png

创建新表的同时，导入旧表数据。

格式：

 create table 新表名 (select * from 旧表名);

 eg : create table xumi (select * from liyue);   #创建xumi表的同时，导入liyue表的数据

Snipaste_2022-10-23_19-52-24.png

查看表结构的三种方法

 方法一：  desc 表名;     #以表格形式显示表结构

 方法二：  show create table 表名;     #以创建表的命令形式显示表结构

 方法三：  desc 表名\G #如果字段较多，也可以使用"desc 表名\G"的方式竖向显示表结构

Snipaste_2022-10-23_19-53-36.png Snipaste_2022-10-23_19-54-28.png

Snipaste_2022-10-23_19-55-47.png

清空表，删除表内的所有数据

方法一：delete

 delete from 表名;

#DELETE清空表后，返回的结果内有删除的记录条目；
DELETE 工作时是一行一行的删除记录数据的，删除时速度较慢；
如果表中有自增长字段，使用"DELETE FROM"删除所有记录后，新添加的记录会从原来最大的记录ID后面继续自增写入记录。

 #delete删除所有数据后，新添加的记录id字段值会在原有的10基础上递增1，即增长为11。
insert into beiliang(name,height) values('世子',178);

Snipaste_2022-10-23_20-03-34.png

方法二：truncate

truncate table 表名;    #相当于格式化，速度很快

 #TRUNCATE清空表后，没有返回被删除的条目。
TRUNCATE 工作时是将表结构按原样重新建立，因此在速度方面 TRUNCATE 会比DELETE清空表快。
使用TRUNCATE TABLE清空表内数据后，ID会从1开始重新记录。

 #truncate删除所有数据后，新添加的记录ID会从1开始重新记录。
insert into beiliang(name,height) values('世子',178);

Snipaste_2022-10-23_20-06-03.png

delete from 表名;
一行一行的删除数据记录，删除效率较慢，执行完后会返回删除的记录条目数；删完后再插入数据记录，自增字段仍然会以原来的最大记录自增

truncate table 表名;
直接重建表，清空速度比delete更快，执行完后不会返回记录条目数；清空表后再插入数据，自增字段会重新从1开始递增

创建临时表 TEMPORARY

  • 临时表创建成功之后，使用"SHOW TABLES"命令是看不到创建的临时表的，临时表会在连接退出后被销毁。
  • 如果在退出连接之前，也可以执行增删改查等操作，比如使用"DROP TABLE"语句手动直接删除临时表。
  • 临时表一般用于测试使用，只有当前连接的用户才能看到，相同用户换个终端登录也看不到。退出当前连接（退出数据库）之后临时表会被销毁。

 CREATE TEMPORARY TABLE 表名(字段1 数据类型,字段2 数据类型[, ...] [, PRIMARY KEY (主键名)]);

eg ：  create temporary table zhidong (     #创建临时表zhidong
id int(4) zerofill primary key,
name varchar(10) not null,
height int(18) not null unique key,
hobby varchar(50));

 show tables;   #使用"show tables"查看不到临时表
insert into zhidong values (1,'世子',199,'bailan'); #向临时表中插入数据
select * from zhidong;     #查看临时表的数据，可以查看到
quit     #退出mysql

 #退出数据库后重新进入，查看临时表数据，提示表不存在。临时表会在连接退出后被销毁。
select * from zhidong;

Snipaste_2022-10-23_20-15-53.png

Snipaste_2022-10-23_20-17-26.png

创建外键约束，保证数据的完整性和一致性

外键的定义： 如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。

外键的作用：

  • 为了一张表记录的数据不要太过冗余。
  • 保持数据的一致性、完整性。

主键表和外键表的理解：

  1. 以公共关键字作主键的表为主键表（父表、主表）
  2. 以公共关键字作外键的表为外键表（从表、外表）

注意：

  • 与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。
  • 添加外键时必须关联主键（即主表先要添加主键，之后从表再添加外键）

创建主键表和外键表

创建主键表和外键表：

 #创建主表class
 create table class (cid int,cname varchar(10));

 #创建从表student
 create table student (id int,name varchar(10),age int,classid int);

 #为主表class的cid字段添加一个主键约束。constraint为主键创建别名，主键名建议以“PK_”开头。
 alter table class ADD constraint PK_CID primary key (cid);

 #为从表student的classid字段添加外键，并将student 表的classid字段和class 表的cid字段建立外键关联。外键名建议以"FK_”开头。
 #references关联主键表中的字段。
 alter table student ADD constraint FK_CLASSID foreign key (classid) references class(cid);

 #添加外键时必须关联主键（即先要添加主键，再添加外键）
 #添加主键和外键时，即使不设置别名，系统也会自动创建一个别名。
复制代码

Snipaste_2022-10-23_20-31-33.png

为两个表插入数据：

 INSERT INTO class values(1,'画画');          #为主表插入数据

 INSERT INTO student values(1,'爱丽丝',18,1);   #为从表插入数据

 INSERT INTO student values(2,'德古拉',19,2);   #这条数据会插入失败，因为主表的cid字段没有为2的值
复制代码

Snipaste_2022-10-23_20-37-32.png

删除主键表中的数据记录

如果想删除主键表中的某条记录，必须先删除外键表中相关联的字段记录。

 #删除数据记录时，要先删从表再删主表。也就是说删除主键表的记录时，必须先删除其他与之关联的表中的记录。
 delete from student where classid=1;       //先删从表的数据
 delete from class where cid=1;             //再删主表的数据
复制代码

Snipaste_2022-10-23_20-40-59.png

查看和删除外键约束

第一步删除外键约束，第二步删除别名。

 #查看外键约束
 show create table student;
 desc student;
 #删除外键约束。
 alter table student drop foreign key FK_CLASSID;   //先删除外键约束
 alter table student drop key FK_CLASSID;           //再删除键（即删除别名）
复制代码

Snipaste_2022-10-23_20-43-52.png

Snipaste_2022-10-23_20-44-22.png

拓展（外键）

目前很多公司都不考虑加入外键了，很多人工作中也不怎么使用外键了，毕竟每次做DELETE 或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦,测试数据极为不方便。
在笔者看来，在相应的场景下，如果使用的一个功能弊大于利，可能就不会选择了。所以是否使用外键取决相应的场景，也不可去完全抛弃外键。

为什么会很少使用外键呢？
目前使用外键确实存在一些不便，这也是大多数人不使用的原因所在。我具体总结了会存在这么几个原因：
1、增加数据库压力
外键等于把数据的一致性事务实现，全部交给数据库服务器完成，并且有了外键，当做一些涉及外键字段的增、删、更新操作之后，需要触发相关操作去检查，而不得不消耗资源。
2、死锁问题
若是高并发大流量事务场景，使用外键还可能容易造成死锁。
3、开发不方便
有外键时，无论开发还是维护，需要手工维护数据时，都不太方便，要考虑级联因素。
4、数据刷库
实际开发过程中，免不了要经常刷库，导入或删除数据，外键的存在会让表删除或插入失败（表数据变更有顺序要求）。
5、外键维护
随着项目的迭代，表之间的关系也会发生变化，比如有个外键需要删除；或是要新增一个外键，但由于现有数据不准，导致外键加不上去，久而久之，ER图也不能完整的显示表关系，这ER图看还是不看？
外键真的就没有用了吗？
存在即合理，每一个产品，每一个功能既然存在，在某些地方也是存在优势的。笔者总结了一下外键的几个优势点：
1、数据库的一致性
由数据库自身保证数据一致性、完整性会更可靠，程序很难100％保证数据的一致性、完整性，而用外键即使在数据库服务器当机或者出现其他问题的时候，也能够最大限度的保证数据的一致性和完整性。
2、ER图可靠性
有主外键的数据库设计可以增加ER图的可读性（也有特别的公司产品特别注重的这一方面）。
总结
1、如是单机且低并发，也不需要性能调优，再或者不能用程序保证数据的一致性，完整性，可以使用外键，如学校的，保证数据的一致性与完整，基数也不大。
2、如果为了高并发，分布式，使系统性能更优，以及更好维护，则不要去使用外键。

概述

  • 查看数据库和表：

  1. show databases;

  2. use 库名;

  3. show tables;

  4. desc 表名;

  5. desc 表名\G

  6. show create table 表名;

  • 创建及删除数据库和表：

  1. create database 库名;

  2. create table 表名(字段1 数据类型 [属性], 字段2 .....);

  3. DROP TABLE [数据库名.]表名;

  4. DROP DATABASE 数据库名;

  • 表数据的增删改查：

  1. insert into 表名 values (所有字段的值);

  2. insert into 表名 (指定字段名称) values(字段的值);

  3. delete from 表名 [where ...];

  4. truncate table 表名;     #删除表中所有数据。相当于格式化，速度很快

  5. update 表名 set 字段=值[, 字段2=....] [where ...];

  6. select 字段1[,字段2....] from 表名 [where ...];

  7. select * from 表名;

  • 修改表名和表结构：

  1. alter table 表名 RENAME/ADD/CHANGE/DROP 字段名 数据类型 属性;

  2. alter table 表名 ADD 键名(字段); //给指定字段添加键约束 注：零填充约束、非空约束、默认约束、自增长约束不能通过ADD方式添加，可以在建表时设置，或者CHANGE修改字段时添加。

  • 添加主键字段的三种方法： 创建表时指定主键字段的两种方式：

  1. create table 表名 (字段1 XXX,字段2 xxx,...,primary key(字段));
  2. create table 表名 (字段1 XXX primary key, ....); 在现有表中添加主键：
  3. alter table 表名 ADD primary key(字段);

主键和唯一键：
主键：primary key
唯一键：unique key
共同点： 字段的值都是唯一性，不允许有重复的值
不同点：一个表中 只能有1个主键，但是可以有多个唯一键
主键字段中不允许有null值，唯一键是允许有null

avatar
文章
阅读
粉丝