项目二 操作数据库与数据表

已于 2024-09-28 18:36:32 修改
阅读量706 收藏 7
点赞数 13
分类专栏： MYSQL数据库技术项目教程 文章标签： 数据库
于 2024-09-28 16:39:28 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zzy780702534/article/details/142490091
版权

项目二 操作数据库与数据表

文章目录

  • 项目二 操作数据库与数据表
  • 1.数据库文件和存储引擎
    • 1.1-MySQL数据库文件
    • 1.2-系统数据库
    • 1.3-MySQL的存储引擎
  • 2.创建和操作数据库
    • 2.1-创建数据库
    • 2.2-查看数据库
    • 2.3-选择数据库
    • 2.4-删除数据库
  • 3.MySQL数据类型
    • 3.1-整数类型
    • 3.2-浮点数类型和定点数类型
    • 3.3-日期与时间类型
    • 3.4-字符型类型
  • 4.创建和操作数据表
    • 4.1-使用Navicat创建表
    • 4.2-使用SQL语句创建表
    • 4.3-查看数据表
    • 4.4-修改数据表
    • 4.5-复制数据表
    • 4.6-删除数据表
  • 5.实现数据的完整性
    • 5.1-PRIMARY KEY 约束
    • 5.2-CHECK 约束
    • 5.3-NOT NULL 约束
    • 5.4-FOREIGN KEY 约束
    • 5.5-UNIQUE 约束
    • 5.6-DEFAULT 约束

1.数据库文件和存储引擎

1.1-MySQL数据库文件

1.数据库文件

  MySQL数据库文件包括MySQL所创建的数据库文件和MySQL存储引擎创建的数据库文件。由MySQL所创建的数据库文件扩展名为”.ibd”, 用于存储数据库中数据表的框架结构，MySQL的数据库文件名与数据库中的表名相同，每个表都对应有一个同名的”.ibd”文件，它与操作系统和存储引擎无关。
   除必要的 ibd 文件外，MySQL的不同存储引擎会创建各自不同的数据库文件。当存储引擎为 MyISAM 时，表文件的扩展名为“MYD”和“.MYI”。其中，MYD (My Data)文件为表数据文件，MYI(My Index)文件为索引文件;扩展名为“1og”的文件用于存储数据表的日志文件。当存储引擎为InnoDB 时，采用表空间来管理数据，其数据库文件包括ibdata1、ibdata2、ibd 和日志文件。其中 ibdata1、ibdata2 是系统表空间 MySQL 数据库文件，存储 InnoDB 系统信息和用户数据表数据及索引，为所有表共用;ibd 文件表示单表表空间文件，每个表使用一个表空间文件，存储用户数据表数据和索引；日志文件则是用ib_logfile0、ib_logfile1 文件名存放。

2.数据库对象

​   数据库中的数据按不同的形式组织在一起，构成了不同的数据库对象。当用户连接到数据库服务器后，看到的是这些逻辑对象，而不是存放在物理磁盘上的文件。一个数据库对象在磁盘上没有对应的文件。

MySQL数据库对象主要包括以下几方面：

  1. 表: MySQL最主要的数据库对象，是由行和列组成的二维表，作为存放和操作数据的一种逻辑结构。
  2. 视图:从一个或多个基表中创建的虚拟表，数据库中只存放视图的定义，数据仍然存放在基表中。
  3. 索引:提供加快检索数据的方式，是对数据表某些列的数据进行排序的一种结构。
  4. 同义词:在架构范围内为存在于本地或远程服务器上的其他数据库对象提供备用名称的一种技术手段。
  5. 存储过程: 一组经过预编译的SQL语句集合，用于完成特定功能。
  6. 触发器: 能够被某些操作激发并自动触发执行的一种特殊的存储过程。
  7. 规则:用来限制表列数据范围、保障数据完整性的一种手段。
  8. 默认值:在用户没有给出具体数据时，系统所自动生成的数值。
  9. 约束:用来保障数据的一致性与完整性的简便方法。

1.2-系统数据库

  MySQL的数据库包括系统数据库和用户数据库。用户数据库是由用户创建的数据库；为用户特定的应用系统提供数据服务；系统数据库是由MSOL安装程序自动创建的数据库，用于存放和管理用户权限和其他数据库的信息，包括数据库名、数据库中的对象及访问权限等信息。

MySQL系统数据库说明

系统数据库名说 明
information_schema信息数据库，用于保存MSQL服务器所维护的所有数据库的信息，包括数据库名、数据库的表、表中列的数据类型与访问权限等。此数据库中的表均为视图，因此在用户或安装目录下无对应数据文件
mysqI核心数据库，用于存储 MySQL 服务的系统信息表，包括授权系统表、系统对象信息表、日志系统表、服务器端辅助系统表等。此数据库中的表默认情况下多为 InnoDB引擎
performance_schema用于收集数据库服务器的性能参数。此数据库中所有表的存储引警为performance_schema，用户不能创建存储引警为performance_schema的表。默认情况下该数据库为关闭状态
sakila样本数据库,是 MySQL 官方提供的一个模拟 DVD 租赁信息管理的数据库，提供了一个标准模式，可作为书中例子，教程、文章、样品,等等，对学习测试来说是个不错的选择
sys一些涉及系统配置内容的数据库
world供查询世界主要国家，城市和语言信息的数据库

在这里插入图片描述
)

1.3-MySQL的存储引擎

​    存储引擎就是数据的存储技术。针对不同的处理要求，对数据采用不同的存储机制、索引技巧、读/写锁定水平等，在关系数据库中数据是以表的形式进行存储的，因此存储引擎即为表的类型。
   数据库的存储引擎决定了数据表在计算机中的存储方式，DBMS 使用数据存储引擎进行创建、查询、修改数据。MySQL 数据库提供多种存储引擎，用户可选择合适的存储引擎，获得额外的速度或者功能，从而改善应用的整体性能。MySQL的核心就是存储引擎。

1.查看MySQL支持的存储引擎
使用 SQL语句可以查询MySQL支持的存储引擎,其语法格式如下：

SHOW ENGINES;

   【例1】査看 MySQL 服务器系统支持的存储引擎。

SHOW ENGINES;

运行结果如图：
在这里插入图片描述

  Engine 表示存储引擎名称；Suppon 参数表示 MySOL 是否支持该类引擎；Comment 参数表示对该引擎的说明；Transactions参数表示是否支持事务处理；XA 参数表示是否支持分布式交易处理的 XA 规范；Savepoints 参数表示是否支持保存点，以便事务回滚到保存点。
  从查询结果集可以看出，服务器支持的存储引擎包括 MRGMYISAM、MyISM、BLACKHOLE、CSV、MEMORY、ARCHIVE、InnoDB、PERFORMANCE SCHEMA，其中 InnoDB 为默认存储引擎，只有 InnoDB 支持事务处理、分布式处理和支持保存点。

使用 SHOW VARIABLES语句可以查询系统默认的存储引擎。

其语法格式如下:
      SHOW VARIABLES LIKE ‘default storage engine’;

査看 MySOL 服务器系统支持的默认存储引擎

SHOW VARIABLES LIKE 'default storage engine';

2.MySQL中常用的存储引擎

（1） InnoDB存储引擎

   InnoDB 是 MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎，用来处理大量短期(short-lived)事务。ImnnoDB 的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行，MySOL一般优先考虑InnoDB引擎。

InnoDB存储引擎的主要特性如下:

  （1）InnoDB 具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)。InnoDB 锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。在SQL查询中，可以自由地将 InnoDB 类型的表和其他 MySOL的表类型混合起来。
  （2）InnoDB 是为处理巨大数据量的最大性能设计的，被用在众多需要高性能的大型数据库站点上:
  （3）InnoDB 存储引擎完全与 MySQL 服务器整合，InnoDB 存储引擎为在主内存中绍存数据和索引而维持它自己的缓冲池。InnoDB 将它的表和索引存放在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘文件)，InnoDB 表文件大小不受限制。
  （4）InnoDB 支持外键完整性约束，存储表中的数据时，每个表的存储都按主键顺序存放，如果没有在表定义时指定主键，InnoDB 会为每一行生成一个6字节的 ROWID 列并以此作为主键。
  （5）InnoDB 不创建目录，使用 InnoDB 存储引擎时,MySQL 将在 MySQL 数据目录下创建一个名为ibdatal的10MB大小的自动扩展数据文件，以及两个名为ib_logfle0 和ib_logfile1 且大小为 5MB 的日志文件。

（2） MyISAM存储引擎

   在 MySQL5.1 及之前的版本中，MyISAM 是默认的存储引擎。MyISAM 具有很多特性，包括全文索引、压缩、空间函数等，被广泛应用在 Web和数据仓储应用环境下，但不支持事物和等级锁，崩溃后无法安全恢复。MyISAM存储引擎设计简单，数据以紧密格式存储，对只读的数据性能较好。

MyISAM存储引擎的主要特性如下:

  （1）每个MyISAM表最大支持的索引数是64，且每个索引最大的列数是16，BLOB和 TEXT列可以被索引，NULL 被允许在索引列中。
  （2）每个表都有一个AUTO INCREMENT的内部列，当INSERT 和 UPDATE 操作的时候该列被更新。AUTOINCREMENT列的更新比InnoDB类型的AUTOINCREMENI更快。
  （3）可以把数据文件和索引文件放在不同目录。
  （4）每个字符列可以有不同的字符集。

（3） Memory存储引擎

  Memory 存储引擎将表中的数据存储到内存中，不需要进行磁盘 IO，且支持 Hash 索引，因此其查询速度非常快，主要适用于目标数据较小而且被非常频繁地访问的情况。Memory 表的结构在重启后还会保留，但所存储的数据都会丢失，同时 Memory 表是表级锁，因此并发写入时性能较低。

（4） CSV存储引擎

  CSV 存储引擎可将普通的CSV 文件(逗号分隔值的文件)作为MySOL的表来处理。但这种表不支持索引。CSV引擎可以在数据库运行时拷贝文件，将xcel电子表格软件中的数据存储为 CSV 文件，并复制到 MySOL 的数据目录中就可以在 MySOL 中打开。同样,如果将数据写入一个CSV引擎表,其他的外部程序也可以直接从表的数据文件中读取CSV格式的数据，因而CSV引擎可以作为数据交换的机制。

2.创建和操作数据库

2.1-创建数据库

1.使用Navicat创建数据库

使用Navicat工具，创建名为db_shop的数据库。

操作步骤如下:

  （1） 启动Navicat工具，右击已连接的服务器节点"mysql"，选择新建数据库命令。
  （2） 单击“确定"按钮，打开“新建数据库”对话框，在对话框中输入数据库的逻辑名称"db_shop"，字符集选择“utf8mb3”，排序规则选择“utf8mb3_croatian_ci"。
  （3） 单击“确定”按钮，完成"db_shop" 数据库的创建。创建完成后，刷新Navicat“对象资源管理器”，可以查看到名为“db_shop" 数据库

在这里插入图片描述

对于数据库的命名，最好能遵循下面的规则:

  （1）不能以数字开头，一般由字母、数字和下划线组成，不允许有空格，可以是英文单词、 英文短语或相应缩写；
  （2）不允许是MySQL关键字，如add、all、alter、by等，尽管允许用中文命名，但不推荐使用；
  （3）长度最好不超过128位；
  （4）MySQL语法中，不区分大小写；
  （5）不能与其他数据库同名。

2.使用SQL语句创建数据库

  创建数据库，实际上就是在数据库服务器中划分出一块空间，用来存储相应的数据库对象。在MySQL中，创建数据库可以使用SQL语句，

其基本语法如下：
      CREATE DATABASE 数据库名
      [DEFAULT] CHARACTER SET 编码方式
      |[DEFAULT] COLLATE排序规则;

语法说明如下:

  • CREATE DATABASE是SQL语言中用于创建数据库的命令;
  • 数据库名:表示创建数据库的名称，该名称在数据库服务器中是唯一的;
  • [DEFAULT] CHARACATER SET:指定数据库的字符集名称;
  • [DEFAULT] COLLATE: 指定数据库的排序规则名称。

  【例2】使用 SOL语句创建一个名为 db_shop 的数据库。

CREATE DATABASE db_shop1;

运行结果如图：

在这里插入图片描述

  【例3】使用 SQL 语句创建名为 db_shop 的数据库，设置默认字符集为 gb2312设置排序规则为 gb2312_chinese_ci。

CREATE DATABASE db_shop2 CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;

运行结果如图：
在这里插入图片描述

2.2-查看数据库

  为了检验数据库是否创建成功，可以使用SQL语句来查看数据库服务器中的数据库列表。

  【例4】使用SQL语句查看数据库服务器中存在的数据库。

SHOW DATABASES;

运行结果如图：

在这里插入图片描述

2.3-选择数据库

  数据库管理系统中一般会存在多个数据库，因此，在操作数据库对象之前要先选择一个数据库。

选择数据库的语法形式如下:

USE 数据库名;

  【例5】使用SQL语句选择db_shop数据库。

USE db_shop

运行结果如图：

在这里插入图片描述

2.4-删除数据库

  ​ 如果删除某个数据库，该数据库里的所有表和数据也会全部被则除，并且系统在执行删除命令前不会有任何提示，因此，在执行此项操作时，一定要小心谨慎，不要误删。

1.使用SQL语句删除数据库

  删除数据库的关键字为 DROP DATABASE。其语法格式如下:

DROP DATABASE 数据库名;

  【例6】使用 SOL 语句删除数据库。

DROP DATABASE db_shop2;

运行结果如图：
在这里插入图片描述

2.使用Navicat工具删除数据库

  使用Navicat工具删除数据库，需要右击数据库名称，在弹出的快捷菜单中执行“删除数据库”命令即可。

在这里插入图片描述

3.MySQL数据类型

3.1-整数类型

  整数类型是数据库中最基本的数据类型，MySQL中支持的整数类型有：TINYINT、SMALLINT、MEDIUMINT、INTEGER、BIGINT

MySQL的整数类型

整数类型字节数无符号数的取值范围有符号数的取值范围
TINYINT10~255-128~127
SMALLINT20~65535-32768~32767
MEDIUMINT30~2^24-223~223 -1
INT40~2^32 -1-231~231 -1
BIGINT80~2^64 -1-263~263 -1

  从表中可以看出，TINYINT类型占用字节最少，只需要1个字节，因此其取值范围最小，无符号的 TINYINT 类型整数最大值为28-1，即255；有符号整数最大值为27-1，即127。

​ MySQL 支持数据类型的名称后面指定该类型的显示宽度。

其基本语法格式如下:

    数据类型(显示宽度)

其中：数据类型参数指的是数据类型名称；显示宽度指能够显示的最大数据长度字节数。如果不指定显示宽度，则MySQL为每一种类型指定默认的宽度值。若为某字段设定类型为INT(11)，则表示该数最大能够显示的数值个数为11位，但数据的取值范围仍为-231~231 -1 。

3.2-浮点数类型和定点数类型

​   在MySQL中，使用浮点数和定点数来表示小数。浮点数类型包括单精度浮点数(FLOAT)和双精度浮点数(DOUBLE)，定点数类型是 DECIMAL; 浮点数在数据库中存放的是近似值，定点数存放的是精确值。下表列举了浮点数类型和定点数类型所对应的存储字节数和取值范围。

浮点数类型和定点数类型对应的字节数和取值范围

类型字节数负数的取值范围非负数的取值范围
FLOAT4-3.402823466E+38~-1.175494351E-380或1.175494351E-38~3.402823466E+38
DOUBLE81.7976931348623157E+308~-2.2250738585072014E-3080和2.2250738585072014E-308~1.7976931348623157E+308
DECIMAL(M,D)或DEC(M,D)M+2同DOUBLE型同DOUBLE型

  
​   从表中可以看出，DECIMAL型的取值范围与DOUBLE型相同，但是DECIMAL型的有效取值范围由 M 和 D决定，其中 M 表示数据的长度，D 表示小数点后的长度，且DECIMAL 类型的存储字节数是 M+2。

​ MySQL中可以指定浮点数和定点数的精度

其基本语法格式如下:

    数据类型(M.D)

其中：M为精度，是数据的总长度，小数点不占位；D为标度，是小数点后面的长度。如DECIMAL(6,2)表示指定的数据类型为DECIMAL，数据长度是6，小数点后保留2位，1234.56 是符合该类型的小数。
   在向 MySQL 数据库中插入小数时，如果待插入值的精度高于指定的精度，系统会自动进行四舍五入。若不指定小数精度，则浮点数和定点数有其默认的精度，浮点数类型默认保存实际精度，这与操作系统和硬件的精度有关，而DECIMA1型的默认整数位为 10，小数位为 0，即默认为整数，也就是说整数是精度为0的定点数。
   注意：尽管指定小数精度的方法适用于浮点数和定点数，但在实际应用中，如果不是特别需要，浮点数的定义不建议使用小数精度法，以免影响数据库的迁移。

3.3-日期与时间类型

​   MySQL中提供了多种表示日期和时间的数据类型。其中YEAR类型表示年份,DATE类型表示日期，TIME类型表示时间,DATETIME和 TIMESTAMP表示日期时间。

类型字节数取值范围零值表示形式
YEAR11901~21550000
DATE41000-01-01~9999-12-310000:00:00
TIME3-838:59:59~838:59;5900:00:00
DATETIME81000-01-01 00:00:00~9999-12-31 23:59:590000-00-00 00:00:00
TIMESTAMP419700101080001~20380119111407000000000000000

  每种日期与时间类型都有一个有效范围。若插入的值超过了取值范围，则系统会提示错误。不同的日期与时间类型有不同的零值。
这些数据类型的主要区别如下：

  （1）如果要表示年月日，则通常用DATE 数据类型来表示
  （2）如果要表示年月日时分秒，则通常用 DATATIME 数据类型表示。
  （3）如果只表示分秒，则通常用 TIME 数据类型来表示。
  （4）如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP 数据类型来表示。TIMESTAMP值返回后显示“YYYYMM-DDHH:MM:SS”格式的字符串，显示宽度固定为19个字符，如果想要获得数字值，应在TIMESTAMP 列添加“0”
  （5）如果只是表示年份，则可以用 YEAR 数据类型来表示，它比 DATE 数据类型占有更少的空间。YEAR是4位格式。在4位格式中，允许的值是1901~2155和0000。
  可以使用任何常见格式指定DATETIME、DATE和TIMESTAMP的值,“YYYY-MM-DD HH:MM:SS”或“YY-MM-DD HH:MM:SS”格式的字符串，允许“不严格”语法：任何标点符号都可以用作日期部分或时间部分之间的间隔符。例如,“98-12-3111:30:45" “98.12.3111+30+45" “98/12/31 113045”和“98@12@31 113045”都是等价的。TIMESTAMP 数据类型有专有的自动更新特性。若定义一个字段为TIMESTAMP，则这个字段里的时间数据会随其他字段的修改自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。TIMESTAMP数据类型使用curenttimestamp( )，而DATETIME 数据类型使用 NOW( )来获取当前时间，输入NULL或无任何输入时，系统会输入系统当前日期与时间。

3.4-字符型类型

  字符串类型包括CHAR、VARCHAR、BLOB、TEXT、ENUM、SET等

1.CHAR类型和VARCHAR类型

  CHAR和VARCHAR类型都是用来表示字符串数据。不同的是CHAR类型占用的存储空间是固定，而VARCHAR类型存放可变长度的字符串。

定义CHAR和VARCHAR类型的方式如下：

    CHAR(M)或VARCHAR(M)

其中，M指定字符串的最大长度。例如,CHAR(5)就是指数据类型为 CHAR类型，其存储空间占用的字节数为5。

2.TEXT类型

  TEXT类型用于存储大文本数据，不能有默认值。包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT

TEXT类型

类型允许的长度存储空间
TINYTEXT0~255 字节值的长度+2 字节
TEXT0~65535字节值的长度+2 字节
MECDIUMTEXT0~167772150字节值的长度+3 字节
LONGTEXT0~4294967295字节值的长度+4字节

3.ENUM类型

  ENUM类型称为枚举类刑,又称为单选字符串类型。

定义ENUM的基本格式如下。

    属性名ENUM（＇值1＇,＇值2＇,…,＇值n ＇）

其中：属性名指的是字段的名称,(＇值1＇，＇值2＇…＇值n＇)称为枚举列表,ENUM类型的数据只能从枚举列表中选取，并且只能取一个值。列表中每个值都有一个顺序排列的编号，MySQL数据库中存入的是值对应的编号，而不是值。

使用ENUM类型应注意以下几点:
  （1）定义的选项值不能重复。
  （2）选项值必须是字符串文字。
  （3）每一个选项值都有一个索引，选项值列表的索引从1开头，NULL值的索引是NULL,这里的术语“索引”是指枚举值列表中的一个位置，它与表索引无关。

  当 MySQL与其他语言合作时，经常会出现插入值不正确的情况，这是由于用户在插入数据时没有明确指定选项值索引或选项值。

4.SET类型

  SET类型又称为集合类型，它的值可以有零个或多个。

其基本格式如下。

    属性名 SET（＇值1＇,＇值2＇,…，＇值n＇)

其中：属性名表示字段的名称，（＇值1＇，＇值2＇，…＇值n＇）称为集合列表,列表中每个值都个顺序排列的编号，MySQL 中存入的值是对应编号或多个编号的组合。当取集合中多个元素时，元素之间用逗号隔开。

使用SET类型时应注意以下几点：

  （1）定义的选项值不能重复，如果插入值中有重复值，则只取其中一个。
  （2）选项值必须是字符串文字。
  （3）插入值的顺序会按照选项值的顺序自动排列。

5.二进制类型

  当数据库中需要存储图片、声音等多媒体数据时，二进制类型是一个不错的选择。MySQL中提供的二进制类型包括 BINARY、VARBINARY、BIT、TINYBLOB、BLQG、MEDIUMBLOB和LONGBLOB

二进制类型

类型取值范围
BINARY(M)字节数为M,允许长度为0~M的定长二进制字符串
VARBINARY(M)允许长度为0~M的变长二进制字符串,字节数为值的长度加1
BIT(M)M位二进制数据，M最大值为64
TINYBLOB(M)可变长二进制数据,最多255个字节
BLOB(M)可变长二进制数据,最多(2^16-1)个字节
MEDIUMBLOB(M)可变长二进制数据,最多(2^24-1)个字节
LONGBLOB(M)可变长二进制数据,最多(2^32-1)个字节

​   BINARY 和 VARBINARY 类型，只包含 byte 串而非字符串,它们没有字符集的概念，排序和比较操作都是基于字节的数字值，以字节为单位计算长度，而不是以字符为单位计算长度。
​   BINARY采用左对齐方式存储，即小于指定长度时，会在右边填充0值，例如BINARY(3)列，插入“a\0”时，会变成“a\0\0”值存入。
  VARBINARY 则不用在右边填充0,在最大范围内，使用多少分配多少。VARBINARY 类型实际占用的空间为实际长度加1,这样，可以有效地节约系统的空间。
​   BLOB 类型是一种特殊的二进制类型。BLOB可以用来保存数据量很大的二进制数据如图片等。BLOB 类型包括TINYBLOB、BLOB、MEDIUMBLOB 和LONGBLOB。这种 BLOB 类型最大的区别就是能够保存的最大长度不同。LONGBLOB类型保存的长度最大，TINYBLOB 类型保存的长度最小。在数据库中存放体积较大的多媒体对象就是应用程序处理 BLOB 的典型例子。

​   注意：BLOB 类型与 TEXT 类型很相似,不同点在于 BLOB 类型用于存储二进制数据BLOB 类型数据是根据其二进制编码进行比较和排序的，而TEXT类型是以文本模式进行比较和排序的。

6.JSON类型

​   从MySQL 5.7.8版本开始，MySQL 新增了-种数据类型: JSON, 用于存储JSON数据。存储JSON类型的数据所需空间与LONGBLOB或LONGTEXT大致相同，大小受限于配置参数“max_allowed_packet"的值。
  JSON类型字段的插入值可分为数组和对象。JSON数组是一个由逗号分隔并包含在括号”[ ]“中的值列表，例如：

     [“abc”, 10, null, true, false]

JSON 对象是一组键值，由逗号分隔，包含在括号“(}”中，例如:
     {“kl”: “valuc”,“k2”: 10)

而且JSON数组和JSON对象允许嵌套，例如：

      [99, {“id”: “HK500”, “cost”: 75.99}, [“hot”, “cold”]]

      {“k1”: “value”,“k2”: [10,20]}
  

  在 MySQL 中，需要将JSON 值写成字符串的形式，在向JSON 类型的字段插入数据之前，系统会判断数据是否为JSON类型。

4.创建和操作数据表

4.1-使用Navicat创建表

  使用Navicat工具，在db_shop数据库中新建用户信息表，表名为userinfo

userinfo表结构

编号列名数据类型说明
1idINT(11)用户编号
2nameVARCHAR(10)用户姓名
3passwordVARCHAR(10)用户密码
4sexENUM(男’，'女)用户性别
5emailVARCHAR(20)用户邮箱
6remarkTEXT备注
7add_timeDATETIME注册时间

操作步骤如下：

  （1）打开Navicat窗口，双击连接窗格中“mysql”服务器，双击“db_shop”数据库，使其处于打开状态，在db_shop数据库下右击“表”节点，在弹出菜单中选择“新建表”选项。

  （2）在打开的表设计窗口中，输入表的列名、数据类型、长度、小数位数、注释等，并设置是否允许为空。

  （3）定义完所有列后，单击标准工具栏上的“保存”按钮，在弹出的对话框中输入表名为“userinfo”即可。

4.2-使用SQL语句创建表

  创建数据表，实际上是规定列属性和实现数据完整性约束的过程，基本语法形式如下：

CREATE [TEMPORARY] TABLE table_name(

	col_name1 data_type(m) [CONSTRAINTS],

	col_name2 data_type(m) [CONSTRAINTS],

	……
);

语法说明：

  • TEMPORARY：使用该关键字表示创建的表为临时表。
  • CREATE TABLE:创建数据表的关键字。
  • table_name：所要创建表的名称，col_name表示字段名称，data_type表示数据类型,m表示数据长度。
  • CONSTRAINTS表示保证数据完整性的约束条件。
  • 各字段之间使用逗号(,)进行分隔，语句的最后以分号（;）结束。

数据表命名应遵循以下原则:

  （1）长度最好不超过30个字符。
  （2）多个单词之间使用下画线“”分隔，不允许有空格。
  （3）不允许使用 MySOL关键字。
  （4）不允许与同一数据库中的其他数据表同名。
  

  【例7】使用 SQL 语句，在 db shop 数据库中新建商品信息表，表名为 goods，表结构如下表。
goods 表结构

字段数据类型约束说明
goods_idINT(11)主键、自增商品编号
goods_typeVARCHAR(30)非空商品类别
goods_nameVARCHAR(30)唯一商品类别
goods_priceDECIMAL(7, 2)无符号商品价格
goods_numINT(11)默认值为 0商品库存
goods_timeDATATIME入库时间

创建 goods 表的语句如下:

CREATE TABLE goods (
	goods_INT (11)PRIMARY KEYAUTO INCREMENT,
	goods_type VARCHAR (30) NOT NULL,
    goods_name VARCHAR (30) UNIQUE,
    goods_price DECIMAL (7,2) UNSIGNED,
    goods_num INT(11)DEFAULT 0，
	goods_time DATETIME
);

运行结果如图：

在这里插入图片描述

4.3-查看数据表

  ​ 关系数据库中，表以行和列的形式组织，数据存在于行和列相交的单元格中，一行数据表示一条唯一的记录，一列数据表示一个字段，唯一标识一行记录的属性称为主键。

1.用 SQL 语句查看数据表

​  创建数据库成功后，可以使用SHOW TABLES 语句查看数据库中的表。

  【例8】使用 SQL 语句査看 db_shop 数据库中的数据表。

操作步骤如下:

  （1）使用USE语句将 db_shop 设为当前数据库：

USE db_shop;

  （2）使用 SHOWTABLES 语句查看数据表

 SHOW TABLES;

运行结果如图：

在这里插入图片描述

2.查看数据表结构

  ​ 在向表中添加数据前，一般先需要查看表结构。MySQL中查看表结构的语句包括DESCRIBE 语句和 SHOW CREATE TABLE 语句。使用DESCRIBE语句可以查看表的基本定义。

其语法格式如下:

​   DESCRIBE 表名;
​   或
​   DESC 表名;

  【例9】使用 DESCRIBE语句査看goods的表结构。

DESCRIBE goods;

运行结果如图：

在这里插入图片描述

  
​   使用SHOW CREATE TABLE不仅可以査看表的详细定义，还可以査看表使用的默认存储引擎和字符编码。

其语法格式如下：

   SHOW CREATE TABLE 表名;

  【例10】使用 SHOW CREATE TABLE语句査看 goods的表结构。

SHOW CREATE TABLE goods;

运行结果如图：

在这里插入图片描述

4.4-修改数据表

​   当系统需求变更或设计之初考虑不周全等情况发生时，就需要对表的结构进行修改。修改表包括修改表名、修改字段名、修改字段数据类型、修改字段的排列位置、添加字段、删除字段、修改表的存储引擎和字符集等。在 MySQL中，可以使用图形工具和 SOL语白实现修改表的操作，其中图形方式与创建表的图形方式相同。

1.修改表名

  数据库系统通过表名来区分不同的表。

在 MySQL中，修改表名的语法格式如下：

  ALTER TABLE 原表名 RENAME [TO] 新表名;

  【例11】使用 SQL语句将数据库 db_shop 中的 goods表更名为 T_user 表。

ALTER TABLE goods  RENAME T_user;

运行结果如图：

在这里插入图片描述

2.修改字段

  修改字段包括修改字段名、字段数据类型等操作。在一个表中，字段名称是唯一的。

在 MySQL中，修改表中字段名的语法格式如下：

  ALTER TABLE 表名 CHANGE 原字段名 新字段名 新数据类型;

其中：原字段名为修改前的字段名；新字段名为修改后的字段名；新数据类型为修改后字段的数据类型。
  
【例12】在数据库db_shop中，使用 SQL语句将 T_uscr 表中名为goods_name的字段名称修改为good_name，长度改为可变20。

ALTER TABLE T_user CHANGE goods_name  good_name VARCHAR(20);

运行结果如图：

在这里插入图片描述

注意：在修改字段时，必须指定新字段名的数据类型，即使新字段的数据类型与原*型相同。

若只需要修改字段的数据类型

语法格式如下:
  ALTER TABLE 表名 MODIFY 字段名 新数据类型;

其中：表名为要修改的表的名称；字段名为待修改的字段名称;新数据类型为修改后的数据类型。

  【例13】在数据库 db_shop 中，使用 SQL语句将 T_user 表中 password 字段的类据类型修改为 VARBINARY，长度为 20。

ALTER TABLE T_user MODIFY goods_num VARCHAR(20);

运行结果如图：

在这里插入图片描述
  

注意：MODIFY 和 CHANGE都可以改变字段的数据类型，但 CHANGE 可以在改变字段数据类型的同时，改变字驶名。如果使用CHANGE修改字段数据类型，那么CHANGE 后面必须跟两个同样的字段名。

3.修改字段的排列位置

使用ALTER TABLE可以修改字段在表的排列位置。

其语法格式如下：

  ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;

其中：字段名1为待修改位置的字段名称;数据类型是字段名1的数据类型;参数FIRST表六将字段名1设置为表的第一个字段：AFTER 字段名2 表示将字段名1排列到字段名2之后。

  【例14】使用 SQL语句修改T_user 表中字段PASSWORD 排列位置到 sex字段之后。

ALTER TABLE T_user MODIFY goods_type VARCHAR(30) AFTER goods_time;

运行结果如图：

在这里插入图片描述

4.添加字段

  在 MySQL中，使用ALTER TABLE语句添加字段。

基本语法格式如下：

  ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件] [ FIRST I AFTER 已存在的字段名];

其中：字段名是需要添加的字段名称；数据类型是新增的字段的数据类型；完整性约束条件是可选参数；FIRST和AFTER也是可选参数，

  用于将增加的字段排列位置。当不指定位置时，新增字段默认为表的最后一个字段。

【例15】使用 SQL语句在T_user 表中增加字段user_tel，用于存放用户的联系方式,其数据类型为 VARCHAR(11)，添加到 sex 字段之后。

ALTER TABLE T_user ADD  user_tel  VARCHAR(30)  AFTER goods_time;
DESCRIBE T_user;

运行结果如图：

在这里插入图片描述

5.删除字段
  当字段设计冗余或是不再需要时，使用 ALTER TABLE 语句可以删除表中字段。

其语法格式如下:
  ALTER TABLE 表名 DROP 字段名;

【例16】使用 SQL语句删除T_user 表中的字段good_name。执行语句后，使用DESC查看T_user 表。

ALTER TABLE  T_user DROP  good_name;
DESCRIBE T_user;

运行结果如图：

在这里插入图片描述
  

6.修改表的存储引擎和字符集

  除实现字段的添加、删除和修改外，ALTER TABLE语句还能实现修改表的存储引擎和字符集。

其语法格式如下：

  ALTER TABLE 表名 ENGTINE = 存储引擎名;

  ALTER TABLE 表名 CHARSET = 字符集;

其中：存储引擎名为新的存储引擎的名称;字符集为新的字符集。

【例17】使用SQL语句修改T_userr表的存储引擎为 MyISAM，字符集为 utf8。执行语句后，使用SHOW CREATE TABLE语句查看T_user表。

ALTER TABLE T_user ENGINE = MyISAM;
ALTER TABLE T_user CHARSET = utf8mb3;
DESCRIBE T_user;

运行结果如图：

在这里插入图片描述

4.5-复制数据表

  在 MySQL 中，表的复制操作包括复制表结构和复制表中的数据。复制操作可以在同一个数据库中执行，也可以跨数据库实现。

1.复制表结构及数据

​ 复制数据表结构和相关数据到新表。

基本语法格式如下：

  CREATE TABLE [数据库名.]新表名 SELECT * FROM [数据库名.]源表名;

其中：新表名表示复制的目标表名称，新表名不能同数据库中已有的名称相同；源表名为复制表的名称；SELECT * FROM 表示查询符合

  条件的数据；数据库名是可选项，如果得双源表在同一个数据库中，则可以省略，如果在不同的数据库中，则需要加上数据库名。

  【例18】使用 SQL 语句复制 db_shop 数据库中 T_user表的结构和数据到 godsinfo5表，并复制t_user 表的结构和数据到 db_shop1数据库的 goods6表中。

CREATE TABLE godsinfo5 SELECT * FROM T_user;
CREATE TABLE db_shop1.goods6 SELECT * FROM db_shop.t_user;
USE db_shop1;
SHOW CREATE TABLE goods6;

运行结果如图：

在这里插入图片描述
  

2.复制表结构

复制表结构。

语法格式如下：

CREATE TABLE [数据库名.]新表名 SELECT * from [数据库名.]源表名 WHERE FALSE;
或
CREATE TABLE [数据库名.]新表名 LIKE [数据库名.]源表名;

  只复制表结构到新表的语法同复制结构和数据的语法相同，只是查询条件恒为FALSE。

  【例19】使用 SQL 语句复制 db shop 数据库的t user 表结构到 temp 表。

CREATE TABLE db_shop.godsinfo SELECT * FROM db_shop.t_user;
#或
CREATE TABLE db_shop.godsinfo LIKE db_shop.t_user;

运行结果如图：

在这里插入图片描述

3.复制表的部分字段及数据

复制表的部分字段及数据到新表。

语法格式如下：

CREATE TABLE [数据库名] 新表名 AS(SELECT 字段1,字段2,… FROM [数据库名.]源表名);

【例20】使用 SQL语句从数据库 db_shop 中复制godsinfo表中的 goods_id，goods_type到数据库db_shop 1的goods4中。

CREATE TABLE db_shop.godsinfo6 AS(
		SELECT 
						goods_id,goods_name
		FROM
						db_shop1.goods4
);

运行结果如图：

在这里插入图片描述

4.6-删除数据表

  删除数据表时，表的结构、数据、约東等都将被全部删除。在MySQL中，使用 DROPTABLE语句来删除表。

其语法格式如下：

  DROP TABLE 表名:

  若想同时删除多个表，只需要在 DROP TABLE 语句中列出多个表名，表名之间用逗号分隔。在删除表时，需要确保该表中的字段未被其他表关联，若有关联，则需要先删除关联表，否则删除表的操作将会失败。

  【例21】使用SQL语句同时删除数据库,dbshop中名为temp和tempuser 的表

DROP TABLE godsinfo4,godsinfo5;

运行结果如图：

在这里插入图片描述

5.实现数据的完整性

  ​数据完整性是指数据的准确性和逻辑一致性，它是为防止数据库中存在不符合语义规定的数据和因输入错误信息造成的无效数据或错误信息而提出的。

5.1-PRIMARY KEY 约束

​   PRIMARYKEY 约束又称为主键约束，是用来确保列的唯一性的约束。设置主键约束的列不能为空，主键约束可以由一列或多列组成，由多列组成的主键被称为联合主键，每个数据表中只能有一个主键约束。为表设置主键约束后，就不必担心表中出现重复行的问题了。通常主键的名称都会以pk_作为前缀，主键约束可以通过SQL语句来实现，也可以通过 Navicat等图形界面工具来创建。

1.在创建表时设置主键约束

​   在创建表时设置约束有两种方法：一种是列级约束；另一种是表级约束。列级约束指在列的定义中直接设置，表级约束指在表中定义列结束后再定义约束。除了非空约束和默认值约束必须在列级定义外，所有的约束均可以选择在列级和表级为列设置约束。主键约束在每个数据表中只有一个，在设置主键约束时，要先确定表中主键约束是单列主键约束还是联合主键约束。需要注意的是：用列级设置主键约束时，只能设置单列主键而不能设置联合主键。

  ​ 在 MySQL 中定义主键时，还可以定义主键自增长。当定义主键为自增长后，这个主键的值就不再需要用户输入数据了,而由数据库系统根据定义自动赋值。每增加一条记录主键会自动以相同的步长增长。

  在MySQL中，通过给字段添加AUTO_INCREMENT 属性来实现主键自增长。

其语法格式如下：
  column_name datatype AUTO INCREMENT

使用主键自增长约束需要注意以下几点：
（1）默认情况下，AUTO_INCREMENT 的初始值是1，每新增一条记录，字段值自动加1。
（2）一个表中只能有一个字段使用AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复(即为主键或主键的一部分)。
（3）AUTO_INCREMENT 约束的字段必须具备NOTNULL 属性。
（4）AUTO_INCREMENT 约束的字段只能是整数类型(TINYINT、SMALLINT、INTBIGINT 等)。
（5）AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束,如果达到上限，AUTO_INCREMENT 就会失效。

  （1）在列级设置主键约束

  列级主键约束是在表中列的后面直接使用 PRIMARY KEY关键字设置。具体的语法格式如下:

CREATE TABLE table_name
(
	column_namel datatype PRIMARY KEY,
	column_name2 datatype,
	column_name3 datatype
 	...
);

  （2）在表级设置主键约束

  表级主键约束指在创建表时，定义完所有列之后设置的约束。具体的语法格式如下：

CREATE TABLE table_name
(
	column_namel datatype,
	column_name2 datatype
	column_name3 datatype
 	 ...
    [CONSTRAINT constraint_name] PRIMARY KEY(column_namel, column_ name2, ...)
);

其中：constraint_name 为主键约束设置名称。如果省略了[CONSTRAINT constraint _name]，则主键约束的名称由系统自动生成。在PRIMARY KEY后面的括号里可以放置一个或多个用于设置主键约束的列，这些列之间用逗号隔开即可。

  【例22】使用SQL语句创建一个商品信息表，表结构如表2.9所示。要求分别在列级和表级为id 列设置主键约束。

商品信息表(productinfo)结构

编号列名数据类型中文释义
1idINT编号
2nameVARCHAR（20）名称
3priceDECIMAL（6,2）价格
4originVARCHAR（20）产地
5telVARCHAR（15）供应商联系方式
6remarkVARCHAR（50）备注

  (1)使用列级约束的方法设置主键约束。具体语句如下:

CREATE TABLE productinfo（
	id int PRIMARY KEY,
	name VARCHAR(20),
	price DECIMAL(6,2),
	origin VARCHAR(20),
	tel VARCHAR(15),
	remark VARCHAR(50)
）;

  (2)使用表级约束的方法设置主键约束。具体语句如下:

CREATE TABLE productinfo（
	id int PRIMARY KEY,
	name VARCHAR(20),
	price DECIMAL(6,2),
	origin VARCHAR(20),
	tel VARCHAR(15),
	remark VARCHAR(50),
	CONSTRAINT pk_id PRIMARY KEY(id)
）;

  ​ 这里，PRIMARY KEY(id)代表给id列设置了主键约束，在列级设置主键约束没有为主键约束定义名称，而在表级设置主键约束时为主键指定了名称pk_id。

2.在修改表时添加主键约束

  ​ 如果在创建数据表时忘记设置主键约束或者还没想好将哪个列设置成主键约束，则可以在修改表的时候加上。修改表时添加主键约束，使用 ALTERTABLE 语句完成。

其语法格式如下：

  ALTER TABLE table name
  ADD [CONSTRAINT constraint name] PRIMARY KEY(column _namel, column name2, )

其中：constraint_name 为主键名称;column_namel 和 column_name2 是要设置成主键的列,可以是一个到多个列，多个列之间用逗号隔开。

  【例23】使用 SQL 语句把godsinfo中的goods_price列设置为主键。假设商品信息表已经存在但是没有设置主键。

 ALTER TABLE godsinfo ADD CONSTRAINT goods_price PRIMARY KEY (goods_price);

运行结果如图：

在这里插入图片描述

3.删除主键约束

  当一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易得多。

删除主键约束的语法格式如下:

ALTER TABLE <数据表名> DROP PRIMARY KEY;

【例24】使用SOL语句删除数据库db_shop中 godsinfo表中的主键约束。

具体语句如下：

ALTER TABLE godsinfo DROP PRIMARY KEY;

运行结果如图：

在这里插入图片描述

4.使用 Navicat 设置主键约束

  【例25】在 Navicat图形工具中，把 db_shop 数据库中的商品信息表 goods 的goods_id 字段设置为主键。

操作步骤如下：

（1）在 Navicat 工具中，打开 goods 表设计器。

（2）选中“goods_id”列，单击工具栏“主键”按钮或右击“goods_id”列，在弹出的菜单中选择“主键”选项，若 goods id列的定义的最后“键”列出现一把钥匙，则设置主键成功。如果重复点击，则取消主键。当主键约束需要多列时，可以按住“CTRL”键选中多列，再单击“主键”按钮即可。

在这里插入图片描述

5.2-CHECK 约束

  ​ CHECK 约束又称检查约束，用来规定表中某列输入值的取值范围，以避免表中输入一些无效数据，例如:在商品信息表中，商品的价格一定是大于0的;人的性别一定是男或者女等。检查约束的作用就是为了确保数据表添加的数据是有效的，在添加之前对数据进行的一种检查。

1.在创建表时设置检查约束

  ​ 检查约束在一个数据表中可以有多个，但是每一列只能设置一个检查约束。虽然检查约束可以帮助数据表检査数据以确保数据的准确性，但是也不能给每个列都设置检查约束，这样会影响数据表中数据操作的效率。

​   在建表时可以同时将检查约束设置好，这样也省去了以后设置的麻烦。建表时添加检查约束的语法有两种形式，检查约束的关键字是CHECK。

  （1）设置列级检查约束。其语法格式如下：、

CREATE TABLE table_name
(
	column_name1 datatype [CONSTRAINT constraint_name] CHECK(expression),
    column_name2 datatype,
	column_name3 datatype,
    ...
);

其中：constraint_name 是检査约束的名称，通常以 ck_为前级，若省略 CONSTRAINT constraint_name ]语句，则约束名称由系统自动生成：CHECK是检查约束的关键字；expression是检查约束的表达式，允许是一个条件或多个条件。例如：设置该列的值大于10，表达式可以写成COLUMN_NAME>10；设置该列的值在10到20之间，表达式可以写成COLUMN_NAME > 10 and COLUMN_NAME < 20。

  （2）设置表级检查约束。其语法格式如下：

CREATE TABLE table_name
(
	column_namel datatype,
    column_name2 datatype,
    column_name3 datatype,
	...
    [CONSTRAINT constraint_name] CHECK(expression),
    [CONSTRAINT constraint_name] CHECK(expression),
    ...
);

  【例26】在创建商品信息表(productinfo)时，给商品价格列(price)添加检查约束,要求商品的价格都大于0元。下面使用添加检查约束的两种方法，分别在创建商品信息表时给商品价格列添加检查约束。

  （1）在列级设置检查约束的具体语句如下:

CREATE TABLE productinfo
(
	id  int PRIMARY KEY,
    name varchar(20),
	price decimal(6, 2)  CONSTRAINT ck_price CHECK (price>0),
	origin varchar(20),
    tel varchar(15),
	remark varchar(50)
);

  （2）在表级设置检查约束的具体语句如下:

CREATE TABLE productinfo
(
	id  int PRIMARY KEY,
    name varchar(20),
	price decimal(6, 2),
	origin varchar(20),
    tel varchar(15),
	remark varchar(50),
    
    CONSTRAINT ck_price CHECK (price>0)
);

执行上面的语句，同样为商品信息表的商品价格列(price)添加了检查约束。

2.在修改表时添加检查约束

  如果在创建表时没有直接添加检查约束，则可以在修改表的时候添加检查约束。在修改表时添加检查约束只能给没有添加检查约束的列添加。修改表时添加检查约束也是通过使用ALTER TABLE语句来完成的。

  其基本语法格式如下:

  ALTER TABLE table_name
  ADD [CONSTRAINT constraint_name] CHECK(expression);

  【例27】使用 SQL语句为数据库db_shop 中的godsinfo表中的productinfo字段添加检査约束，要求用户输入数据只能为男或者女。

USE	db_shop;
ALTER TABLE godsinfo ADD CHECK (goods_num='男' OR goods_num='女');

运行结果如图：

在这里插入图片描述

3.删除检查约束

​   检查约束同前面讲解过的其他约束一样，都是不能直接修改的。要想更改某一列的检查约束，先要删除该检查约束，然后再为其重新创建检查约束。删除检查约束的语法与删除其他的约束类似.

具体的语法格式如下：

ALTER TABLE table_name DROP COLUMN constraint_name;

其中：constraint_name 表示检査约束的名称。如果不知道检査约束的名称,可以通过 SHOWCREATE TABLE 表名语句査看。

  【例28】使用 SQL 语句删除为godsinfo中列(goods_num)添加的检查约束。

SHOW CREATE TABLE godsinfo;
ALTER TABLE godsinfo DROP COLUMN goods_num;

运行结果如图：

在这里插入图片描述

5.3-NOT NULL 约束

  ​ 非空(NOT NULL)约束是用来确保列中必须有输入值的一种手段，也可以理解成一种特殊的检查约束。非空约束经常与默认值约束连用，以避免非空约束的列在添加值时出现错误。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，则数据库系统就会报错。非空约束可以通过CREATE TABLE或 ALTERT ABLE语句实现。在表中某列的定义后加上关键字 NOT NULL作为限定词，来约束该列的取值不能为空。

1.在创建表时设置非空约束

非空约束只能在列级设置。创建表时可以使用NOTNULL关键字设置非空约束。

具体的语法格式如下：

CREATE TABLE_NAME table_name
(
	column_namel datatype NOT NULL,
    column name2 datatype NOT NULL,
    column name3 datatype
    ...
);

  添加非空约束就是在列的数据类型后面加上NOT NULL关键字。上面的语法中还有一个特点，就是没有给非空约束设置名称，其实非空约束是本任务学过的约束中唯一一个没有名称的约束。

  【例28】根据商品信息表(productinfo)结构的要求，使用SQL语句创建商品信息表，要求商品的名称不能为空，价格也不能为空。

具体语句如下:

CREATE TABLE productinfo 
(
	id INT PRIMARY KEY,
	NAME VARCHAR (20) NOT NULL,
	price DECIMAL (6, 2) NOT NULL,
	origin VARCHAR (20),
    teI VARCHAR (15),
	remark VARCHAR (50)
);

2.在修改表时添加非空约束

  非空约束比较特殊，与其他约束的添加方法大不相同，在修改表时添加非空约束与在数据表中修改列的定义是相似的.

  具体的语法格式如下：

  ALTER TABLE table_name;

  CHANGE COLUMN col_name col_name datatype NOT NULL;

其中： table_name是表名；col_name 是要加上非空约束的列名；datatype 是列的数据类型，如果不修改数据类型，则使用原来的数据类型；NOT NULL是非空约束的关键字。

  如果不修改数据类型，则使用原来的数据类型;NOTNULL是非空约束的关键字。

  【例29】使用SQL语句为商品信息表（productinfo）中的供应商联系方式列（tel）添加非空约束。

  ​ 在添加非空约束之前，首先要查看商品信息表中供应商联系方式列的数据类型，然再进行添加。查询后可以知道，tel列的数据类型是VARCHAR(15)。同时，如果在商品息表中的供应商联系方式列里已经存在了空的记录，那么需要将空的记录删除或更改成他信息，否则无法添加非空约束。

USE	db_shop;
ALTER TABLE godsinfo CHANGE COLUMN goods_type goods_type VARCHAR(15) NOT NULL;

运行结果如图：

在这里插入图片描述

3.剽除非空约束

​   非空约束的删除方法与其他约束不同，由于非空约束没有名称，因此不能够用之习的翻除约東的方法删除，没有设置非空约束的列用什么表示呢？答案是用NULL表示，即某个列要取消非空约束就意味着该列可以为空。

  具体的语法格式如下:

  ALTER TABLE table_name

  CHANGE COLUMN col_namecol_name datatype NULL;

4.使用 Navicat 管理非空约束

使用 Navicat 图形工具管理非空约束非常简单，打开设计表界面，“不是null”列勾选复选框表示不能为空，不勾选表示可以为空。

在这里插入图片描述
1727512163032)

5.4-FOREIGN KEY 约束

​   外键(FOREIGN KEY)约東是唯一一个与两个表相关的约束，它的主要用途是制约数据表中的数据，确保数据表中数据的有效性。外键约束是表的一个特殊字段，经常与主键约束一起使用，对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表(父表)，外键所在的表就是从表(子表)。

定义外键约束时，需要遵守下列规则:

（1）主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况,则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

（1）必须为主表定义主键。

（2）主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

（3）在主表的表名后面指定列名或列名的组合。这个列名或列名的组合必须是主表的主键或候选键。

（4）外键中列的数目必须和主表的主键中列的数目相同。

（5）外键中列的数据类型必须和主表的主键中对应列的数据类型相同。

1.在创建表时设置外键约束

  相对于其他的约束，外键约束的设置有些复杂，但它又是一个比较重要的约束。因此请读者认真学习设置外键约束的方法。在创建表时就可以添加外键约束，但有一个前提是与这个外键约束相关的那个数据表已经存在。

设置外键约束的语法格式如下:

CREATE TABLE table name
（
	col_namel  datatype,
	col_name2  datatype,
	col_name3  datatype,
	[CONSTRAINT constraint name] FOREIGN KEY(col name)
		REFERENCES referenced table name(ref col name)
):

其中：constraint_name是外键约束的名称，通常以fk_为前缀；col_name 是要设置成主键约束的列名；referenced_table_name是被引用的表名；ref_col_name是被引用的表中的列名；[CONSTRAINT constraint_name]允许省略，省略后外键约束的名称依然由系统自动
生成。

  【例30】使用 SOL 语句根据供应商信息表(supplierinfo)中的供应商编号为商品信息表(goodsinfo)中的供应商编号创建外键。两个表的结构分别如表所示。

供应商信息表(supplierinfo)结构

编号列名数据类型中文释义
1idINT供应商编号
2nameVARCHAR (20)供应商名称
3telVARCHAR (15)供应商联系方式
4remarkVARCHAR (50)供应商备注

商品信息表(goodsinfo)结构

编号列名数据类型中文释义
1idINT商品编号
2nameVARCHAR (20)商品名称
3priceDECIMAL(6，2)商品价格
4originVARCHAR (20)商品产地
5supplieridINT供应商编号
6remarkVARCHAR (50)商品备注

创建供应商信息表的语句如下(要求设置供应商编号字段为主键):

CREATE TABLE supplierinfo
( 
    id INT PRIMARY KEY,
	NAME VARCHAR (20),
	teI VARCHAR (15),
    remark VARCHAR (50)
 }

运行结果如图：

在这里插入图片描述

设置外键约束的语句如下：

CREATE TABLE goodsinfo
( 
		id INT PRIMARY KEY,
		NAME VARCHAR (20),
		price DECIMAL(6,2),
		origin VARCHAR (20),
		supplierid INT,
		remark VARCHAR (50),
		CONSTRAINT fk_product FOREIGN KEY(supplierid) REFERENCES supplierinfo(id)
 )

运行结果如图：

在这里插入图片描述

2.在修改表时设置外键约束

  如果已经创建了数据库表，但是忘记添加外键约束，那么只需要在修改表的语句中加上外键约束就可以了。

  在修改表时添加外键约束的语法格式如下:

  ALTER TABLE table_name

  ADD [CONSTRAINT constraint_name] FOREIGN KEY(col_name) REFERENCES referenced_table_name(ref col name);

注意：在添加外键约束前，需要确保表中要添加外键约束列的值全部符合引用表中对应的列值，否则就会出现添加外键约束失败的错误，通常情况会在表中还没有添加数据时为数据表添加外键约束。

  【例31】假设供应商信息表(supplierinfo)和商品信息表已经存在，使用SQL语句把商品信息表中供应商编号(supplierid)设置成供应商信息表中供应商编号(id)的外键。假设商品信息表中还没有数据,这样添加外键约束时不会出现错误。

具体的语句如下:

ALTER TABLE goodsinfo
ADD CONSTRAINT fk supperlierid FOREIGN KEY (supplierid)
REFERENCES supplierinfo (id);

3.删除外键约束

  当一个表中不需要外键约束时，就需要从表中将其删除。一旦删除外键，就会解除主表和从表间的关联关系。

  删除外键约束的语法格式如下:

  ALTER TABLE table_name DROP FOREIGN KEY fk_name;

其中，fk_name是外键约束的名称。

4.使用 Navicat 设置外键约束

  【例32】使用 Navicat 图形工具，为商品信息表(goodsinfo)的 supplierid 供应商编号字段创建外键，其主键为供应商信息(supplierinfo)中的供应商编号id字段。

操作步骤如下:

（1）在Navicat 中“db shop”数据库下，打开 goodsinfo 表设计器。

（2）在表设计器中单击“外键”选项卡,输入外键的名称和选择相应的属性值。

（3）单击工具栏“保存”按钮，完成表外键约束的设置。

在这里插入图片描述

  在上图中：“名”为外健名称：“字段”为 goodsinfo 表中引用韵数据列，即设置外键约束的列名：“被引用的模式”为设置被引用表所在的数据库名：“被引用的字段”为主键列名：“删除时”或“更新时”为拒绝主表修改或更新外键关联列。

  ​“删除时"和“更新时"两列有4个值可以选择：CASCADE、NO ACTION、RESTRICT、SETNULL，它们的区别如下：

  ​CASCADE：删除或更新父表的时候，子表会被删除或更新关联记录；

  ​SETNULL：删除或更新父表的时候，子表会将关联记录的外键字段所在列设为NULL，所以注意在设计子表时外键不能设NOTNULL;

  ​RESTRICT：如果想要删除父表的记录而在子表中有关联该父表的记录时，则不允许删除父表中的记录;

  ​NOACTION：同RESTRICT，也是首先检查外键。

5.5-UNIQUE 约束

​   唯一(UNIQUE)约束是指所有记录中字段的值不能重复出现。唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在个表中可以有多个，并且设置唯一约束的列是允许有空值的(虽然只能有一个空值)。例如在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

1.在创建表时设置唯一约束

  ​ 在创建表时可以直接为表中的列设置唯一约束。在创建表时设置唯一约束可以通过下面两种语法格式来完成，唯一约束的关键字是 UNIQUE。

  ​ （1）在列级设置唯一约束

  设置列级唯一约束的具体语法格式如下:

CREATE TABLE table_name
（
	column_namel datatype UNIQUE,
	column_name2 datatype,
	column_name3 datatype
	...
）;

  ​ （2）在表级设置唯一约束

  添加表级唯一约束是在所有列定义的后面直接添加。具体的语法格式如下：

CREATE TABLE table_name
（
	column_namel datatype,
	column_name2 datatype,
	column_name3 datatype
	...
	[CONSTRAINT constraint_name] UNIQUE(col_name1).
	[CONSTRAINT constraint_name] UNIQUE(col_name2).
	...
）;

其中：constraint_name 是唯一约束的名称，通常唯一约束以 uq_为前缀；若省略[ CONSTRAINT constraint_name ]语句，唯一约束的名称则由系统自动生成。

  一次可以给一到多列设置唯一约束。在表级设置唯一约束时，必须在 UNIQUE 关键字后面加上具体的列名。

  【例33】分别使用上面两种语法，在创建商品信息表(productinfo)时将商品名称(name)设置成唯一约束。

  （1）在列级设置唯一约束的具体语句如下:

CREATE TABLE productinfo
(
	id int PRIMARY KEY,
	name varchar(20) UNIQUE,
	price decimal(6, 2),
	origin varchar(20),
	tel varchar(15),
	remark varchar(50)
);

  （2）在表级设置唯一约束的具体语句如下:

CREATE TABLE productinfo
(
	id int PRIMARY KEY,
	name varchar(20),
	price decimal(6, 2),
	origin varchar(20),
	tel varchar(15),
	remark varchar(50)
);

  执行上面的语句，完成为商品信息表(productinfo)中的商品名称列(name)设置唯一约束。

2.在修改表时添加唯一约束

  虽然在创建表时设置唯一约束有两种方法，但是在修改表时添加唯一约東只有一种方法。可以对比之前学习过的几种约束，看看在修改表时添加唯一约束有什么变化。另外，在已经存在的表中添加唯一约束，要保证添加唯一约束的列中存放的值没有重复的。

在修改表时添加唯一约束的语法格式如下:

ALTER TABLE table_name ADD [CONSTRAINT constaint_name] UNTQUE(Col_name);

  执行上面的语句，为商品信息表中的tel列添加了一个名为 uq_productinfo_tel 的唯约束。

3.删除唯一约束

任何一种约束都可以删除，删除唯一约束的方法很简单，只要知道约束的名称就可以删除。

删除唯一约束的语法格式如下:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

  【例34】删除商品信息表(produetint)中供应商联系方式(tel)列的唯一约束。供应商联系方式(te)列的唯一约束是在例2.39中添加的，名称是uq productinfo_tel。删除该约束的具体语句如下:

ALTER TABLE productinfo DROP CONSTRAINT ug_productinfo_tel;

4.使用 Navicat 管理唯一约束

  由于唯一约束不需要设置任何表达式，因此它在Navicat 中的设置也非常简单。无论是在创建表的时候设置唯一约束，还是在修改表时添加唯一约束，都可以在表设计完成以后，切换到“索引”选项卡，在“名”一栏填写索引名称,“字段”栏选择字段名称，在“索引类型”栏选择“UNIQUE”选项即可。

  删除唯一约束则选择菜单栏的“删除索引”选项即可完成

在这里插入图片描述

5.6-DEFAULT 约束

默认值(DEFAULT)约束用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。

1、在创建表时设置默认值约束

创建表时可以使用 DEFAULT 关键字设置默认值约束。具体的语法格式如下:

CREATE TABLE tabe_name
(
	column_name1 datatype DEFAULT constant_expression,
    column_name2 datatype,
    column_name3 datatype
	...
);

其中，constant_expression 为该字段设置的默认值，如果是字符类型的,要用单引号括起来。

【例35】在创建商品信息表(productinfo)时将产地列(origin)设置一个默认值“重庆”约束。根据在创建表时设置默认值约束的语法，设置产地列的默认值“重庆”的具体语句如下:

CREATE TABLE productinfo
（
	id int PRIMARY KEY,
	name varchar(20),
	price decimal(6, 2),
	origin varchar(20) DEFAULT '重庆',
	tel varchar(15),
	remark varchar(50)
）;

2.在修改表时添加默认值约束

  在修改表时添加默认值约束的语法格式如下:

  ALTER TABLE table_name
  CHANGE COLUMN column_name column_name datatype DEFAULT constant_expression;

  【例 2.42】 给商品信息表(productinfo)中的备注列(remark)添加默认值约束，将其默认值设置成“保质期为1天”具体语句如下:
**
  ALTER TABLE productinfo
  CHANGE COLUMN remark remark varchar(50) DEFAULT ‘保质期为1 天’;**

3.删除默认值约束

  当一个表中的列不需要设置默认值时，就需要从表中将其删除。修改表时删除默认值约束的语法格式如下:
  ALTER TABLE table_name
  CHANGE COLUMN column_name column name datatype DEFAULT NULL;

4.使用 Navicat 工具管理默认值

  同唯一约束一样，默认值约束的设置、添加和删除也很简单。

袗亦
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

袗亦

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值