微信上可以做网站吗,网站建设公司做ppt吗,wordpress后台地址修改,做酒水网站有哪些概述
数据库(Database#xff0c;DB)是按照数据结构来组织、存储和管理数据的仓库#xff0c;其本身可被看作电子化的文件柜#xff0c;用户可以对文件中的数据进行增删改查等操作。
数据库系统是指在计算机系统中引入数据库后的系统#xff0c;除了数据库#xff0c;还…
概述
数据库(DatabaseDB)是按照数据结构来组织、存储和管理数据的仓库其本身可被看作电子化的文件柜用户可以对文件中的数据进行增删改查等操作。
数据库系统是指在计算机系统中引入数据库后的系统除了数据库还包括数据库管理系统(Database Management SystemDBMS)、数据库应用程序。 数据库技术的发展
人工管理阶段
数据不在计算机中长期保存没有专门的数据管理软件数据需要应用程序自己管理数据是面向应用程序的不同的应用程序之间无法共享数据数据不具有独立性完全依赖于应用程序
文件系统阶段
数据可以在计算机外存设备上长期保存可以对数据反复进行操作通过文件系统管理数据文件系统提供了文件管理功能和存取方法在一定程度上实现了数据独立性和共享性但非常的薄弱
数据库系统阶段
数据结构化数据共享数据独立性高数据统一管理与控制 三级模式
美国国家标准协会(American National Standards InstituteANSI)所属的标准计划与需求委员会(Standards Planning and Requirements CommitteeSPARC)在1971年公布的研究报告中提出了 ANSI-SPARC体系结构即三级模式结构(或三层体系结构)。
ANSI-SPARC最终没有成为正式标准但它仍然是理解数据库管理系统的基础。
三级模式是指数据库管理系统从三个层次来管理数据分别是外部层(External Level)、概念层(Conceptual Level)和内部层(Internal Level)。
这三个层次分别对应三种不同的模式分别是外模式、概念模式和内模式。
在外模式与概念模式之间以及概念模式与内模式之间还存在映像即二级映像。 外模式面向应用程序描述用户的数据视图内模式(又称物理模式、存储模式)面向物理上的数据库描述数据在磁盘中如何存储概念模式(又称为模式、逻辑模式)面向数据库设计人员描述数据的整体逻辑结构。
由于三级模式比较抽象为了好理解假设有一个电子表格来保存商品信息表如下 概念模式
概念模式类似于表格的列标题他描述了商品表中包含哪些信息。
概念模式在数据库中描述的信息还有很多如多张表之间的联系表中每一列的数据类型和长度等。
内模式
Excel表格另存为文件时可以选择保存的文件路径、保存类型等这些与存储想关的描述信息相当于内模式。
在数据库中内模式描述数据的物理结构和存储方式如堆文件、索引文件、散列(Hash)文件等。
外模式
在打开一个电子表格会显示表格中全部的数据将数据提供给用户时出于权限安全控制等因素考虑只允许用户看到一部分数据或不同用户看到不同的数据这样的需求就可以用视图来实现。视图和基本表的关系如下 基本表中的数据是实际储存在数据库中的而视图中的数据是查询或计算出来的。由此可见外模式可以为不同的用户需求创建不同的视图由于不同的用户需求不同数据显示方式也会多种多样。 一个数据库中会有多个外模式而概念模式和内模式则只有一个 二级映像
三级模式是数据的三个抽象级别每个级别关系的重点不同。为了使三级模式之间产生关联数据库管理系统在三级模式之间提供了二级映像功能。
二级映像是一种规则它规定了映像双方如何进行转换。通过二级映像体现了逻辑和物理两个层面的数据独立性。
逻辑独立性
外模式/概念模式映像体现了逻辑独立性。逻辑独立性是指当修改了概念模式不影响上一层的外模式。
例如将表库存和销量拆分到另一张表中此时概念模式发生了更改但可以通过改变外模式/概念模式的映像继续为用户提供原有视图。 由此可见逻辑独立性能够让使用视图的用户感觉不到基本表的改变。
物理独立性
概念模式/内模式映像体现了物理独立性。物理独立性是指修改了内模式不影响上层的概念模式和外模式。
例如在 Excel 中将 .xls 文件另存为 .xlsx 文件虽然文件格式变了但打开文件后显示的表格内容一般不会发生改变。
在数据中更换了更先进的存储结构或者创建索引来加快查询速度内模式会发生改变。此时只需要改变概念模式/内模式映像就不会影响到原有的概念模式。
物理独立性使得用户不必了解数据库内部的存储原理即可正常使用数据库来保存数据。数据库管理系统会自动将用户的操作转换成物理级数据库的操作。 数据库相关的人员
数据库系统涉及一些人员主要包括数据库管理员(Database Administrator,DBA)、应用程序员(Application Programmer)和最终用户(End User)。
数据库管理员
数据库管理员负责管理和维护数据库参与数据库的设计、测试和部暑。
应用程序员
应用程序员负责为最终用户设计和编写程序并进行调试和安装以便最终用户利用应用程序来对数据库进行存取操作。
最终用户
最终用户一般为非计算机专业人员通过应用程序访问数据库。 数据模型 数据建模
数据建模是对现实世界中的各类数据的抽象组织以确定数据库的管辖范围、数据的组织形式等。
数据建模大致分为3个阶段分别是概念建模阶段、逻辑建模阶段、物理建模阶段相应的产物分别是概念模型、逻辑模型和物理模型。 概念模型是现实世界到机器世界的中间层它将现实世界中的客观对象(如学生、班级、课程抽象成信息世界的数据。
逻辑模型是指数据的逻辑结构可以选择层次模型、网状模型或关系模型。
在完成逻辑模型后最后使用物理模型描述数据如何进行实际存储也就是将逻辑模型转换成计算机能够识别的模型。 概念模型的术语
在概念模型中可以通过一些术语来对现实世界进行抽象。
实体
实体(Entity)是指客观存在并可相互区分的事物如学生、班级、课程。
属性
属性(Attribute)是指实体所具有的某一特性一个实体可以由若干个属性来描述。
例如学生实体的属性有学号、学生姓名和学生性别。属性由两部分组成分别是属性名和属性值。
联系
联系(Relationship)是指实体与实体之间的联系有一对一、一对多、对多对三种情况。
例如每个学生都有一个学生证学生和学生证之间是一对一的联系一个班级有多个学生班级和学生是一对多的联系一个学生可以选修多门课程一门课程又可以被多个学生选修学生和课程之间就形成了多对多的联系。
实体型
实体型(EntityType)即实体类型通过实体名(如学生)及其属性名集合(如“学号、学生姓名、学生性别”)来抽象描述同类实体。
实体集
实体集(Entity Set)是指同一类型的实体集合如全校学生就是一个实体集。 E-R图
E-R图也称为实体-联系图(Entity Relationship Diagram)是一种用图形表示的实体联系模型由Peter Chen于1976年提出。
E-R图提供了表示实体型、属性和联系的方法用来描述现实世界的概念模型。其通用的表示方式如下:
实体 用矩形框表示将实体名写在框内。
属性 用椭圆框表示将属性名写在框内用连线将实体与属性连接。
联系 用菱形框表示将联系名写在框内用连线将相关的实体连接并在连线旁标注联系类型(一 0 对 一 “11”、一对多 “1n”、多对多 “nm”)。 关系模型
关系模型由IBM公司研究员 Edgar Frank Codd 于1970年发表的论文中提出经过多年的发展已经成为目前最常用、最重要的模型之一。在关系模型中有一些基本的概念。
关系
关系(Relation)一词与数学领域有关它是集合基础上的一个重要的概念用于反映元素之间的联系和性质。
从用户角度来看关系模型的数据结构是二维表即通过二维表来组织数据。一个关系对应一张二维表表中的数据包括实体本身的数据和实体间的联系。
属性
二维表中的列称为属性(Attribute)每个属性都有一个属性名。
元组
二维表中的每一行数据称为一个元组(Tuple)。 根据不同的习惯属性也可以称为字段(Field元组也可以称为记录(Record)。 域
域(Domain)是指属性的取值范围。
关系模式
关系模式(Relation Schema)是关系的描述通常可以简记为“关系名属性1属性2…属性n)。
键
在二维表中若要唯一标识某一条记录需要用到键又称为关键字、码。 关系模型的完整性
为了保证数据库中数据的正确性和相容性需要对关系模型进行完整性约束。完整性通常包括实体完整性、参照完整性和用户自定义完整性。
实体完整性
实体完整性要求关系中的主键不能重复且不能取空值。
空值是指不知道、不存在或无意义的值。
由于关系中的元组对应现实世界中互相之间可区分的个体这些个体使用主键来唯一标识若主键为空或重复则无法唯一标识每个个体。
参照完整性
参照完整性要求关系中的外键要么取空值要么取被参照关系中的某个元组的主键值。
例如通过学生所属的班级号可以找到对应的班级这就符合参照完整性而如果对应的班级被删除了学生通过班级号找不到班级就不符合参照完整性。
用户自定义完整性
用户自定义完整性是用户针对具体的应用环境定义的完整性约束条件由DBMS检查用户自定义的完整性 关系运算
关系模型可以使用关系代数(Relational Algebra)来进行关系运算。关系代数是一种抽象的查询语言是研究关系模型的数学工具。关系代数运算符主要包括并、差、交、笛卡儿积、选择、投影、连接和除。 并、差、交
并(Union)、差(Difference)、交(Intersection)运算要求参与运算的两个关系具有相同数量的属性其运算结果是一个具有相同数量属性的新关系。 笛卡尔积 选择、投影
选择(Selection)是在一个关系中将满足条件的元组找出来即水平方向筛选。
投影(Projection)是在一个关系中去掉不需要的属性保留需要的属性即垂直方向筛选。 连接
连接是在两个关系的笛卡儿积中选取属性间满足一定条件的元组。
由于笛卡儿积的结果可能会包含很多没有意义的元组所以相比之下连接运算更为实用。
常用的连接方式有等值连接(Equi-Join)和自然连接(Natural Join)。
自然连接是一种特殊的等值连接要求R和S必须有相同的属性组进行等值连接后再去除重复的属性组。 除
如果把笛卡儿积看作乘法运算则除法是笛卡儿积的逆运算。 R÷S1 表示查询学号为2的学生所选的课程R÷S2 表示查询学号为2和3的学生共同选择的课程。 SQL语言
SQL (Structured Query Language,结构化查询语言) 是一种数据库查询语言和程序设计语言主要用于管理数据库中的数据如存取数据、查询数据、更新数据等。
SQL是IBM公司于1975-1979年开发出来的在20世纪80年代SQL被美国国家标准学会ANSI和国际标准化组织International Organization for Standardization,ISO定义为关系数据库语言的标准。
SQL是由4部分组成的。
数据定义语言
数据库定义语言(Data Definition Language,DDL)主要用于定义数据库、表等。
例如CREATE语句用于创建数据库、数据表等ALTER语句用于修改表的定义等DROP语句用于删除数据库、删除表等。
数据操作语言
数据操作语言(Data Manipulation Language,DML)主要用于对数据库进行添加、修改和删除操作。
例如INSERT语句用于插入数据UPDATE语句用于修改数据DELETE语句用于删除数据。
数据查询语言
数据查询语言(Data Query Language,DQL)主要用于查询数据。
例如使用SELECT语句可以查询数据库中的一条数据或多条数据。
数据控制语言
数据控制语言(Data Control LanguageDCL)主要用于控制用户的访问权限。
例如GRANT语句用于给用户增加权限REVOKE语句用于收回用户的权限COMMIT语句用于提交事务ROLLBACK语句用于回滚事务。 安装MYSQL
下载地址 https://dev.mysql.com/downloads/mysql/
本文安装的是mysql5.7.22版作为演示。
0️⃣解压下载好的mysql压缩包我这里解压到C:\mysql5.7目录。
1️⃣打开命令提示符窗口在命令模式下切换到 MYSQL 安装目录下的bin目录
cd C:\mysql5.7\bin2️⃣输入安装命令
mysqld -install在安装MySQL时还有一些常见的问题需要注意
MySQL安装的服务名默认为 “MySQL”如果该名称已经存在则会安装失败提示The service already exists!
此时可能是系统中已经安装了MySQL可以通过如下命令进行卸载卸载后再进行安装。
mysqld -removeMySQL允许在安装或卸载时指定服务名称从而实现多个MySQL服务共存。
例如当需要同时安装MySQL5.7和8.0时分别指定不同的服务名称即可实现。
mysqld -install 服务名称mysqld -remove 服务名称MySQL服务默认监听3306端口如果该端口被其他服务占用会导致客户端无法连接服务器。
在命令行中可用 netstat -ano 命令查看端口占用情况。
为了获知该进程是哪一个程序执行 tasklist | findstr 4204” 命令
当前是 mysqld.exe 占用了3306端口说明MySQL服务正在工作。如果是其他程序占用了3306端口只需将对应的服务停止即可。
3️⃣使用文本编辑器创建配置文件 C:\mysql5.7\my.ini编写如下
[mysqld]
basedirC:\mysql5.7
datadirC:\mysql5.7\data
port3306basedir 表示MySQL的安装目录datadir 表示数据库文件的保存目录port 表示MySQL服务的端口号。
在没有配置文件的情况下MySQL会自动检测安装目录、数据文件目录。
但由于不同MySQL版本的路径可能有区别所以建议通过配置文件来指定。
另外Linux系统中通常使用 my.cnf 作为配置文件的文件名在Windows系统中也可以使用该文件名。
4️⃣初始化数据库
创建my.ini配置文件后数据库文件目录 C:\mysql5.7\data 还没有创建。接下来需要通过MySQL的初始化功能自动创建数据文件目录
mysqld --initialize-insecure“-initialize”表示初始化数据库“-insecure”表示忽略安全性。
当省略 “insecure” 时MySQL将自动为默认用户 “root” 生成一个随机的复杂密码而加上 “insecure” 时“root”用户的密码为空。由于自动生成的密码输人比较麻烦因此这里选择忽略安全性。 MySQL5.5和5.6版本中已经提供了data目录不需要初始化数据库。只有安装5.7和8.0版本时需要执行上述命令。 5️⃣管理MySQL服务
MySQL安装完成后需要启动服务进程否则客户端无法连接数据库。在前面的配置过程中已经将MySQL安装为Windows服务。
MySQL服务不仅可以通过Windows服务管理器启动还可以通过命令行来启动。
使用管理员身份打开命令提示符输人如下命令启动名称为 MySQL 的服务。
net start MySQL停止服务
net stop MySQL6️⃣登录MYSQL
在 MySQL 的 bin 目录中mysql.exe 是 MySQL 提供的命令行客户端工具用于访问数据库。
该程序不能直接双击运行需要打开命令行窗口执行 cd C:\mysql5.7\bin 命令切换工作目录然后执行如下命令登录MySQL服务器。
mysql -u root“mysql”表示运行当前目录下的 mysql.exe。
“-u root” 表示以root用户的身份登录其中“-u”和“root”之间的空格可以省略。
如果需要退出MySQL可以直接使用 exit 或 quit 命令。 命令行客户端工具还有一些常用选项。其中“-h” 用于指定登录的MySQL服务器地址域名或IP)如“-hlocalhost” 或 “-h 127.0.0.1” 表示登录本地服务器。选项“-P”必须用大写字母P用于指定连接的端口号如“-P3306”表示连接3306端口。 7️⃣设置密码
为了保护数据库的安全需要为登录MvSQL服务器的用户设置密码。
下面以设置root用户的密码为例登录MySQL后执行如下命令即可。
mysqlALTER USER rootlocalhost IDENTIFIED BY 123456;上述命令表示为 localhost 主机中的 root 用户设置密码密码为“123456”。
当设置密码后退出MySQL然后重新登录时就需要输入刚才设置的密码。
在登录有密码的用户时需要使用的命令如下。
mysql -uroot -p123456在上述命令中“-p123456” 表示使用密码 “123456” 进行登录。如果在登录时不希望密码被直接看到可以省略“-p”后面的密码然后按回车键会提示输入密码。
在设置密码后如果需要取消密码可以使用如下命令。
mysqlALTER USER root localhost IDENTIFIED BY 上述命令将密码设为空即可免密码登录。
8️⃣设置环境变量
在启动 MYSQL 客户端前确保命令提示符当前位于 C:\mysql5.7\bin 目录执行命令将 bin 目录添加到环境变量。
setx PATH %PATH%;C:\mysql5.7\bin执行上述命令后关闭当前命令行窗口重新打开一个新的命令行窗口即可生效。 数据库操作 创建数据库
创建数据库就是在数据库系统中划分一块存储数据的空间。
语法格式
CREATE DATABASE 数据库名称[库选项]CREATE DATABASE 表示创建数据库
数据库名称可以是字母、数字和下划线组成的任意字符串
“库选项” 用于设置此数据库的相关特性如字符集CHARSET校对集COLLATE。 语法内使用 “[ ]” 括起来的选项表示可选参数。 下面创建一个名称为 mydb 的数据库具体SQL语句与执行结果如下。
mysqlCREATE DATABASE mydb;
Query OK,1 row affected (0.00 sec)在创建数据库后MySQL.会在存储数据的data目录中创建一个与数据库同名的子目录即mydb同时会在mydb目录下生成一个 db.opt 文件保存数据库选项打开data\mydb\db.opt文件如下所示。
default-character-setlatin1
default-collation-Latin1——swedish_cimydb 数据库的默认字符集为 latin1校对集为 Latin1_swedish_ci。
创建的数据库已存在则程序会报错可以在创建数据库名称前添加 IN NOT EXISTS 。表示指定的数据库不存在时执行创建否则忽略此操作。 查看数据库
查看所有的数据库
SHOW DATABASESMySQL服务器已有4个数据库这些数据库都是MySQL安装时自动创建的。
-------------------
|information_schema |
|performance_schema |
|mysql |
|sys |
-------------------information_schema 和 performance_schema 数据库分别是 MySQL 服务器的数据字典保存所有数据表和库的结构信息和性能字典保存全局变量等的设置。
“mysql” 数据库主要负责MySQL服务器自己需要使用的控制和管理信息如用户的权限关系等。
sys 是系统数据库包括了存储过程、自定义函数等信息。
查看指定数据库的创建信息
SHOW CREATE DATABASE 数据库名称选择数据库
由于MySQL服务器中的数据需要存储到数据表中而数据表需要存储到对应的数据库下并且MYSQL服务器中又可以同时存在多个数据库因此在对数据和数据表进行操作前首先需要选择数据库。
USE 数据库名称登录 MYSQL 时选择数据库
mysql -u 用户名 -p 密码 数据库名删除数据库
DROP DATABASE 数据库名称;若删除数据库不存在mysql服务器会报错可以在数据库名前添加 IF EXISTS 来判断删除的数据库是否存在存在就删不存在就不执行删除操作。 数据表操作 创建数据表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名
(字段名 字段类型 [字段属性] …) [表选项]TEMPORARY 表示临时表仅在当前会话中可见并且在会话关闭时自动删除。
字段名指的是数据表的列名。
字段类型设置字段中保存的数据类型如时间日期类型等。
字段属性指的是字段的某些特殊约束条件。
表选项用于设置表的相关特性如存储引擎ENGINE、字符集CHARSET和校对集(COLLATE)。
在操作数据表之前应该使用 USE 数据库名 指定操作是在哪个数据库中进行否则会抛出 No database selected 错误。
例如创建名为 goods 的数据表
create table goods (id int comment 编号,name varchar(32) comment 商品名
);INT用于设置字段数据类型是整型。
VARCHAR(L)表示可变长度的字符串L表示字符数如VARCHAR(32)表示可变的字符数是32。
COMMENT用于在创建表时添加注释内容并将其保存到表结构中。 查看数据表
SHOW TABLES [LIKE 匹配模式];若不添加可选项 LIKE匹配模式表示查看当前数据库中的所有数据表
若添加则按照 匹配模式 查看数据表。其中匹配模式符有两种分别为 % 和 _。前者表示匹配一个或多个字符代表任意长度的字符串长度也可以为0后者仅可以匹配一个字符。 LIKE 后的匹配模式必须使用单引号或双引号包裹 查看数据表的相关信息
SHOW TABLES STATUS [FROM 数据库名] [LIKE 匹配模式];修改数据表
修改数据表名称
语法格式1
ALTER TABLE 旧表名 RENAME [TO|AS] 新表名;语法格式2
RENAME TABLE 旧表名1 TO 新表名1 [旧表名2 TO 新表名2] ...;修改表选项
ALTER TABLE 表名 表选项 值;查看表结构
查看数据表的字段信息
查看所有字段的信息
{DESCRIBE | DESC} 数据表名;查看指定字段信息
{DESCRIBE | DESC} 数据表名 字段名;查看数据的创建语句
SHOW CREATE TABLE 表名;查看数据表结构
语法格式1
SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];语法格式2
SHOW [FULL] COLUMNS FROM 数据库名.数据表名;可选项 FULL 表示显示详细内容在不添加的情况下查询结果与 DESC 的结果相同。
在添加 FULL 选项时此语句不仅可以查看到 DESC 语句查看的信息还可以查看到字段的权限、COMMENT字段的注释信息等。 修改表结构
修改字段名
ALTER TABLE 数据表名 CHANGE [COLUMN] 旧字段名 新字段名 字段类型 [字段属性];数据类型表示新字段名的数据类型不能为空即使与旧字段的数据类型相同也必须重新设置。 修改字段类型
ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名 新字段类型 [字段属性];修改字段的位置
ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名1 数据类型 [字段属性] [FIRST | AFTER 字段名2];新增字段
语法格式1新增一个字段并可指定其位置
ALTER TABLE 数据表名 ADD [COLUMN] 新字段名 字段类型 [FIRST | AFTER字段名];语法格式2同时新增多个字段
ALTER TABLE 数据表名 ADD [COLUMN] (新字段名1 字段类型1, 新字段名2 字段类型2,…);在不指定位置的情况下新增的字段默认添加到表的最后。另外同时新增多个字段时不能指定字段的位置。 删除字段
ALTER TABLE 数据表名 DROP [COLUMN] 字段名;删除数据表
DROP [TEMPORARY] TABLE [IF EXISTS] 数据表 1 [,数据表2] ...;数据操作 添加数据
为所有的字段添加数据
严格按照数据表结构(字段的位置)插入对应的值。
INSERT [INTO] 数据表名 {VALUES | VALUE} (值1 [,值2] ...);在MySQL中若创建的数据表未指定字符集则数据表及表中的字段将使用默认的字符集latin1。
因此若用户插入的数据中含有中文则会出现错误提示。
为为了解决以上中文插入的问题通常在创建数据表时添加表选项设置数据表的字符集。
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 (字段名 字段类型[字段属性] ...) [DEFAULT] {CHARACTER SET[CHARSET} [] utf8;为部分字段添加数据
除了为数据表中所有字段添加数据外还可以通过指定字段名的方式增加数据。
其中指定的字段名可以是数据表中全部的字段也可以是部分的字段。
INSERT [INTO] 数据表名(字段名1 [,字段名2 ...) {VALUES | VALUE} (值1[, 值2] ...);除此之外MySQL中还提供了另外一种使用 INSERT 语句为指定字段添加数据的方式。
INSERT [INTO] 数据表名 SET 字段名1 值1 [,字段名2 值2] ...;一次添加多行数据
INSERT [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (值列表)[,值列表] ...;在多数据插入时若一条数据插人失败则整个插人语句都会失败。 查询数据
查询表中全部数据
SELECT * FROM 数据表名;查询表中部分字段
SELECT (字段名1, 字段名2, 字段名3, ...) FROM 数据表名;简单条件查询数据
SELECT (字段名1, 字段名2, 字段名3, ...) FROM 数据表名 WHERE 字段名 值;修改数据
UPDATE 数据表名 SET 字段名1 值1[,字段名2 值2] [WHERE 条件表达式];若没有where条件那么表中对应的字段全部都会被修改 删除数据
DELETE FROM 数据表名 [WHERE 条件表达式];常用转义字符
转义字符含义转义字符含义\0空字符NULL\t制表符HT\r回车符CR\b退格BS\n换行符LE\’单引号\双引号\%%常用于LIKE条件\\反斜线\__常用于LIKE条件 数据类型 数字类型
整数类型
数据类型字节数无符号数的取值范围有符号数的取值范围TINYINT10~255- 128 ~ 127SMALLINT20~65 535- 32 768 ~ 32 767MEDIUMINT30~16 777 215- 8 388 608 ~ 8 388 607INT40~4 294 967 295- 2 147 483 648 ~ 2 147 483 647BIGINT80 ~ 18 446 744 073 709 551 615- 9 223 372 036 854 775 808 ~ 9 223 372 036 854 775 807
不同整数类型所占用的字节数和取值范围都是不同的。
占用字节数最小的是TINYINT占用字节数最大的是BIGINT。
不同整数类型的取值范围可以根据字节数计算出来。例如TINYINT 类型的整数占用1字节1字节是8位那么TINYINT 类型无符号数的最大值就是 2^8 - 1即255 有符号数的最大值就是 2^7 - 1即127。
若使用无符号数据类型需要在数据类型右边加上 UNSIGNED 关键字来修饰例如INT UNSIGNED 表示无符号 INT 类型。 ---------------------------------------------
|Field |TYPE |NUll|KEY |Default |Extra |
---------------------------------------------
|int_1 |int(11) |YES | |NULL | |
|int_2 |int(10) unsigned |YES | |NULL | |
---------------------------------------------数据类型右边使用小括号数字标注了显示宽度。默认情况下显示宽度是取值范围所能表示的最大宽度。对于有符号类型符号也占用一个宽度。
例如255的显示宽度为3一128的显示宽度为4。
❗显示宽度与取值范围无关若数值的位数小于显示宽度会填充空格若大于显示宽度则不影响显示结果。
为字段设置零填充(ZEROFILL)时若数值宽度小于显示宽度会在左侧填充0。
例如在 my_Int 表中设置零填充和宽度
CREATE TABLE my_Int (int_1 INT(3) ZEROFILL,int_2 TINYINT(6) ZEROFILL
);设置零填充后字段自动设为无符号类型这是因为负数不能使用负填充。 浮点数类型
数据类型字节数负数的取值范围非负数的取值范围FLOAT43.402 823 466E 38 1.175 494 351E380 和 1.175 494 351E383.402 823 466E38DOUBLE81.797 693 134 862 315 7E 308 ~ 2. 225 073 858 507 201 4E3080 和 2.225 073 858 507 201 4E3081.797 693 134 862 315 7E 308
列举的取值范围是理论上的极限值但根据不同的硬件或操作系统实际范围可能会小。
当浮点数类型使用 UNSIGNED 修饰为无符号时取值范围将不包含负数。
浮点数类型虽然取值范围很大但是精度并不高。FLOAT的精度为6位或7位DOUBLE 的精度大约为15位。如果超出精度可能会导致给定的数值与实际保存的数值不一致发生精度损失。
定点数类型
定点数类型(DECIMAL) 通过 DECIMAL(M,D) 设置位数和精度其中M表示数字总位数不包括“.”和“—”)最大值为65默认值为10D表示小数点后的位数最大值为30默认值为0。
例如DECIMAL(52) 表示的取值范围是 999.99999.99。
若小数部分超出范围会进行四舍五人并出现 Data truncated(数据截断) 警告。
若整数部分超出范围数据会插人失败提示 Out of range value(超出取值范围错误。
浮点数类型也可以设置位数和精度如 float(8,2)但仍有可能损失精度。在实际使用时应避免使用浮点数类型以免出现不能人为控制的问题。因此对于小数类型的设置推荐使用定点数类型并设置合理的范围可以使计算更为准确。
BIT类型
BIT(位) 类型用于存储二进制数据语法为BIT(M)M表示位数范围为1~64。 时间和日期类型
数据类型取值范围日期格式零 值YEAR1901 ~ 2155YYYY0000DATE1000-01-01 ~ 9999-12-3YYYY-MM-DD0000-00-00TIME838:59:59 ~ 838:59:59HH:MM:SS00:00:00DATETIME1000-01-01 00:00:00 ~ 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS0000-00-00 00:00:00TIMESTAMP1970-01-01 00:00:01 ~ 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS0000-00-00 00:00:00
YYYY表示年MM表示月DD表示日每种日期和时间类型的取值范围都是不同的。
❗如果插入的数值不合法系统会自动将对应的零值插人数据库中。
YEAR类型
YEAR 类型用于表示年份。
在 MySQL 中可以使用以下 3 种格式指定 YEAR 类型的值。
使用 4 位字符串或数字表示为 1901~2155 或 1901~2155。 例如输入 ‘2020’ 或 2020 插入到数据库中的值均为 2020。 使用两位字符串表示为 00~99其中00~69 的值会被转换为 2000~2069 的YEAR值70~99 的值会被转换为 1970~1999 的YEAR值。 例如输入 ‘20’插入到数据库中的值为 2020。 使用两位数字表示为 1~99 其中1~69 的值会被转换为 2001~2069 的YEAR值70~99的值会被转换为 1970~1999 的YEAR值。 例如输入 20 插入到数据库中的值为 2020。
❗当使用 YEAR 类型时一定要区分 0 和 0。因为字符串格式的 0 表示的 YEAR 值是 2000而数字格式的 0表示的 YEAR 值是 0000 。
DATE类型
DATE 类型用于表示日期值不包含时间部分。
在 MySQL 中可以使用以下 4 种格式指定 DATE 类型的值。
以 YYYY-MM-DD 或者 YYYYMMDD 字符串格式表示。 例如输入 ‘2020-01-21’ 或 ‘20200121’插入数据库中的日期都为 2020-01-21。 以 YY-MM-DD 或者 YYMMDD 字符串格式表示。YY表示的是年为 00~99其中 00~69 的值会被转换为2000~2069的值70~99 的值会被转换为 1970~1999 的值。 例如输入’20-01-21’或 ‘200121’插入数据库中的日期都为2020-01-21。 以 YY-MM-DD 或者 YYMMDD 数字格式表示。 例如输入 20-01-21 或 200121插入数据库中的日期都为 2020-01-21。 使用 CURRENT_DATE 或者 NOW() 输入当前系统日期。
通过 SELECT CURRENT_DATE 或 SELECT NOW() 可查看当前日期。
日期中的分隔符 - 还可以用 . / 等符号替代。
TIME类型
TIME类型用于表示时间值它的显示形式一般为 HH:MM:SS其中HH表示小时MM表示分SS表示秒。
在 MySQL 中可以使用以下 3 种格式指定 TIME 类型的值。
以 HHMMSS 字符串或者 HHMMSS 数字格式表示。 例如输入 345454 或 345454 插入数据库中的时间为 34:54:54(34小时54分54秒)。 以 D HH:MM:SS 字符串格式表示。其中D 表示日可以取 034 之间的值插入数据时小时的值等于(D×24HH)。 例如输入2 11:30:50插入数据库中的时间为 59:30:50输入 11:30:50插入数据库中的时间为113050。 使用 CURRENT_TIME 或 NOW()输入当前系统时间。
DATETIME类型
DATETIME 类型用于表示日期和时间它的显示形式为 YYYY-MM-DD HH:MM:SS其中YYYY表示年MM表示月DD表示日HH表示小时MM表示分SS表示秒。
在MySQL中可以使用以下 4 种格式指定 DATETIME 类型的值。 以 YYYY-MM-DD HH:MM:SS 或者 YYYYMMDDHHMMSS 字符串格式表示的日期和时间取值范围为1000-01-01 00:00:00~9999-12-31 23:59:59 。 例如输入 2014-01-22 09:01:23 或 20140122090123 插入数据库中的 DATETIME 值都为 2014-01-22 09:01:23。 以 YYYY-MM-DD HH:MM:SS 或者 YYMMDDHHMMSS 字符串格式表示的日期和时间其中 YY 表示年取值范围为 0099。与 DATE 类型中的 YY 相同0069 范围的值会被转换为 20002069范围的值70~99 范围的值会被转换为 1970~1999 范围。 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。 插人 20140122090123 或者 140122090123 插入数据库中的 DATETIME 值都为 2014-01-22 09:01:23。 使用 NOW() 来输入当前系统的日期和时间。
TIMESTAMP类型
TIMESTAMP(时间戳) 类型用于表示日期和时间它的显示形式与 DATETIME 相同、但取值范围比DATETIME小。
TIMESTAMP 类型与DATATIME类型不同的形式
使用 CURRENT_TIMESTAMP 来输入系统当前日期和时间。无任何输入或输入 NULL 时实际保存的是系统当前日期和时间。
在MySQL中TIMFSTAMP 字段默认情况下会自动设置 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 属性。
NOT NULL 表示非空约束该字段将不允许保存 NULL 值。DEFAULT表示默认约束当字段无任何输入时自动设置某个值作为默认值。此处设为CURRENT_TIMESTAMP 表示使用系统当前日期和时间作为默认值。ON UPDATE 用于当一条记录中的其他字段被 UPDATE 语句修改时自动更改该字段为某个值。此处设为 CURRENT_TIMESTAMP 表示每次修改时保存修改时的系统日期和时间。 若为 TIMESTAMP 字段手动设置 DEFAULT 属性时该字段将不会自动设置 ON UPDATE 属性。 字符串类型
数据类型类型说明CHAR固定长度字符串VARCHAR可变长度字符串TEXT大文本数据ENUM枚举类型SET字符串对象BINARY固定长度的二进制数据VARBINARY可变长度的二进制数据BLOB二进制大对象Binary Large Object
CHAR和VARCHAR类型
CHAR 和 VARCHAR 类型都用来保存字符串数据。不同的是VARCHAR 可以存储可变长度的字符串。
CHAR(M)VARCHAR(M)M指的是字符串的最大长度。为了对比 CHAR 和 VARCHAR 之间的区别下面以 CHAR(4) 和VARCHAR(4) 为例进行说明。
插入值CHAR(4) 存储需求VARCHAR(4) 存储需求‘’4字节1字节‘ab’4字节3字节‘abc’4字节4字节‘abcd’4字节5字节
对于 CHAR(4) 无论插入值的长度是多少所占用的存储空间都是4字节而 VARCHAR(4) 占用的字节数为实际长度加1。
TEXT类型
TEXT类型用于保存大文本数据例如文章内容、评论等比较长的文本。
数据类型存储范围数据类型存储范围TINYTEXT02^8 - 1 字节MEDIUMTEXT0~2^24-1 字节TEXT02^16 - 1 字节LONGTEXT02^32-1 字节
❗TEXT类型所能保存的最大字符数量取决于字符串实际占用的字节数。
ENUM类型
ENUM 类型又称为枚举类型。
ENUM(值1, 值2, 值3, ...,值n)(值1, 值2, 值3, ...,值n) 称为枚举列表ENUM类型的数据只能从枚举列表中取并且只能取一个。
在MySQL中枚举列表最多可以有 65535 个值每个值都有一个顺序编号实际保存在记录中的是顺序编号而不是列表中的值因此不必担心过长的值占用空间。但在使用 SELECT、INSERT 等语句进行操作时仍然使用列表中的值。
SET类型
SET类型用于保存字符串对象其定义格式与ENUM类型类似。
SET(值1, 值2, 值3, ...,值n)SET类型的列表中最多可以有 64 个值且列表中的每个值都有一个顺序编号为了节省空间实际保存在记录中的也是顺序编号但在使用 SELECT、INSERT 等语句进行操作时仍然要使用列表中的值。
SET类型与ENUM的区别在于它可以从列表中选择一个或多个值来保存多个值之间用逗号 , 分隔。 1️⃣ENUM 类型类似于单选框SET 类型类似于复选框。 2️⃣ENUM 和 SET 类型的优势在于规范数据本身限定只能插入规定的数据项节省了存储空间查询速度比 CHAR、VARCHAR 类型快。 3️⃣ENUM 和 SET 类型列表中的值都可以使用中文但必须设置支持中文的字符集例如 CREATE TABLE my_enum (gender ENUM(男, 女)) CHARSETGBK;。 4️⃣ENUM 和 SET 类型在填写列表、插入值、查找值等操作时都会自动忽略末尾的空格。 BINARY和VARBINARY类型
BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR不同的是它们所表示的是二进制数据。
BINARY(M)VARBINARY(M)M 是指二进制数据的最大字节长度。
BINARY 类型的长度是固定的如果数据的长度不足最大长度将在数据的后面用 \0 补齐最终达到指定长度。
例如指定数据类型为 BINARY(3) 当插入a 时实际存储的数据为 a\0\0 当插入 ab 时实际存储的数据为 ab\0 。
在查询 BINARY 类型时查询条件字符串也需要加上 \0 填充符否则查询不到该记录并且BINARY 和 VARBINARY 都区分大小写。
例如指定数据类型为 BINARY(3) 当查询 a 时WHERE条件值要输入 a\0\0否则查询不到该记录。
BLOB类型
BLOB类型用于保存数据量很大的二进制数据如图片、PDF文档等。
数据类型存储范围数据类型存储范围TINYBLOB02^8-1字节MEDIUMBLOB02^24-1字节BLOB02^16-1字节LONGBLOB02^32-1字节
BLOB 类型与 TEXT 类型很相似但 BLOB 类型数据是根据二进制编码进行比较和排序而 TEXT 类型数据是根据文本模式进行比较和排序。 JSON数据类型
MySQL从 5.7.8 版本开始提供了JSON 数据类型。
JSON是一种轻量级的数据交换格式由 JavaScript 语言发展而来其本质是一个字符串。
MySQL中 JSON 类型值常见的表现方式有两种分别为 JSON数组 和 JSON对象。
# 1. JSON 数组
[abc, 10, null, true, false]# 2.JSON对象
[k1, value, k2, 10]JSON数组中保存的数据可以是任意类型。
与直接使用 MySQL 字符串类型相比JSON 数据类型具有自动验证格式、优化存储格式的优点。
JSON 数据类型所需的空间大致与 LONGBLOB 或 LONGTEXT 相同且不能有默认值。
CREATE TABLE myjson (j1 JSON, j2 JSON);INSERT INTO myjson VALUES ({k1, value, k2, 10}, [run, sing]
);二进制方式比较字符串
若要在 CHAR、VARCHAR、TEXT 类型的字符串比较时严格区分大小写有多种实现方式。下面两种比较常见的方式。
使用 BINARY 关键字
在字段名或某个值的前面加上 BINARY 关键字可以将类型转换为二进制转换后进行比较就可以严格区分大小写和空格。
SELECT a A; #比较结果1相等
SELECT BINARY a A, a BINARY A; #比较结果0不相等
SELECT BINARY A A; #比较结果0不相等
SELECT BINARY A A, A BINARY A; #比较结果1相等设置字段的校对集
latin1、gbk、utf8 编码默认的校对集分别为 latin1_swedish_ci、gbk_chinese_ci、utf8_general_ci将其分别改为 latin1_bin、gbk_bin、utf8_bin 即可区分大小写但在比较时仍会忽略字符串末尾的空格。
# 1.创建表时设置字段的校对集
CREATE TABLE mychar (c1 CHAR(2) CHARACTER SET latin1 COLLATE latin1_bin,c2 CHAR(2) CHARACTER SET gbk COLLATE gbk_bin,c3 CHAR(2) CHARACTER SET utf8 COLLATE utf8_bin
);# 2.插入测试数据
INSERT INTO mychar VALUES(A, A, A)# 3.查询数据
SELECT c1 a, c2 a, c3 a FROM mychar; #结果0不想等
SELECT c1 A, c2 A, c3 A FROM mychar; #结果1想等
SELECT c1 A , c2 A , c3 A FROM mychar; #结果1想等注意
1️⃣CHAR 和 VARCHAR 类型在插入数据时若字符串末尾有空格CHAR 类型会自动去掉空格后保存而VARCHAR、TEXT 类型会保留空格。
2️⃣在使用 “” 等运算符对 CHAR 、VARCHAR 、TEXT 进行比较时字符串末尾的空格会被忽略。
例如使用 WHERE 查询 ‘a’ 字符串查询结果中可能包含 a 后面有空格的情况反之若查询条件字符串末尾有空格如 a )空格也会被忽略。
3️⃣由于默认情况下创建的数据库和表使用的校对集 (latin1_swedish_ci) 对大小写不敏感因此CHAR、VARCHAR、TEXT、ENUM、SET 类型都不区分大小写。
例如使用 WHERE 查询 ‘a’ 字符串则 “a” 和 “A” 都会被查询出来。而 BINARY、VARBINARY、BLOB类型区分大小写这是因为它们使用二进制方式保存数据。
4️⃣MySQL 默认规定一条记录的最大长度是 65535 字节一般来说字段分配的存储空间和额外开销加在一起不能超过 65535 字节如果超过了这个限制在SQL 严格模式下表会创建失败提示 Row size too large。但 TEXT 和 BLOB 类型字段的存储空间不受此限制它们只占用额外开销大约12字节)。
5️⃣在没有超过 65535 限制的情况下CHAR 字段的 M 最大值为255VARCHAR字段的 M 的最大值取决于字符集常用的字符集有 latin1(默认、gbk 和 utf8对应的M最大值分别为 65533、32766 和 21844若表中只有一个字段且设置了非空约束M 可达到最大值否则 M 的最大值会减小。
6️⃣从执行效率上来说TEXT 和 BLOB 不如 CHAR、VARCHAR 类型建议只有当需要保存大量数据时才选择使用 TEXT 或 BLOB 类型。 表的约束
为了防止数据表中插入错误的数据MySQL定义了一些维护数据库完整性的规则即表的约束。 默认约束
默认约束用于为数据表中的字段指定默认值即当在表中插人一条新记录时如果没有给这个字段赋值那么数据库系统会自动为这个字段插入默认值。
字段名 数据类型 DEFAULT 默认值;使用 ALTER TABLE 修改列属性即可添加或删除默认约束。 BLOB、TEXT 数据类型不支持默认约束。 非空约束
非空约束指的是字段的值不能为NULL。
字段名 数据类型 NOT NULL;为现有的表添加或删除非空约束的方式与默认约束类似使用ALTER TABLE修改列属性即可。 若目标列中已经保存了NULL值添加非空约束会失败提示 “Invalid use of NULL value”只要将NULL值改为其他值即可解决。 唯一约束
唯一约束用于保证数据表中字段的唯一性即表中字段的值不能重复出现。
#列级约束
字段名 数据类型 UNIQUE;#表级约束
UNIQUE(字段名1,字段名2, ...);列级约束定义在一个列上只对该列起约束作用表级约束是独立于列的定义可以应用在一个表的多个列上。 当表级约束仅建立在一个字段上时其作用效果与列级约束相同。 若为一个现有的表添加或删除唯一约束无法通过修改字段属性的方式操作而是按照索引的方式来操作。
添加唯一约束的完整语法
UNIQUE KEY 索引名(字段列表)添加唯一约束时创建索引用于加快查询速度。
索引名可以自己指定也可以省略MySQL会自动使用字段作为索引名。当需要对索引进行删除时需要指定这个索引名。
在表级唯一性约束创建时UNIQUE() 的字段列表中可以添加多个字段组成复合唯一键其特点是只有多个字段的值相同时才视为重复记录。 主键约束
为了快速查找表中的某条信息可以通过设置主键来实现。主键可以唯一标识表中的记录类似身份证用于标识人的身份一样。
主键约束通过 PRIMARY KEY 定义它相当于唯一约束和非空约束的组合要求被约束字段不允许重复也不允许出现 NULL 值每个表最多只允许含有一个主键。
# 列级约束
字段名 数据类型 PRIMARY KEY#表级约束
PRIMARY KEY字段名1,字段名2,…表级约束的字段若只有一个则为单字段主键与列级约束添加的效果相同若有多个则为复合主键。复合主键需要用多个字段来确定一条记录的唯一性类似于复合唯一键。
# 1.删除主键约束
mysqlALTER TABLE my_primary DROP PRIMARY KEY;# 2.删除id字段的非空约束
mysqlALTER TABLE my_primary MODIFY id INT UNSIGNED;# 3.添加主键约束
mysqlALTER TABLE my_primary ADD PRIMARY KEY(id);删除 id 字段的主键约束后该字段的非空约束并没有被同时删除。若需要删除 id 字段的非空约束执行第2步删除操作即可。 自动增长
为数据表设置主键约束后每次插入记录时都需要检查主键的值防止插入的值重复导致插入失败可以利用MySQL提供的自动增长功能来自动生成主键的值。
字段名 数据类型 AUTO_INCREMENT在使用 AUTO_INCREMENT 时需要注意以下4点 一个表中只能有一个自动增长字段该字段的数据类型是整数类型且必须定义为键如 UNIQUE KEY 、PRIMARY KEY。 若为自动增长字段插入 NULL、0、DEFAULT 或在插入时省略该字段则该字段就会使用自动增长值若插入的是一个具体值则不会使用自动增长值。 自动增长值从 1 开始自增每次加 1。若插入的值大于自动增长的值则下次插入的自动增长值会自动使用最大值加 1若插入的值小于自动增长值则不会对自动增长值产生影响。 使用 DELETE 删除记录时自动增长值不会减小或填补空缺。
查看自动增长值
SHOW CREATE TABLE 数据库名\G修改自动增长
ALTER TABLE 数据库名 AUTO_INCREMENT 值;删除自动增长
ALTER TABLE 数据库名 MODIFY 字段名 字段类型 [字段属性];添加自动增长
ALTER TABLE 数据库名 MODIFY 字段名 字段类型 [字段属性] AUTO_INCREMENT;通过 SHOW VARIABLES LIKE auto_increment%; 可以查看 MySQL 中用于维护自动增长的变量分别是 auto increment_ increment(默认值1) 和 auto_increment_offset(默认值为1)通过更改这两个变量可以改变自动增长的计算方式可以参考官方手册中的详细说明。 字符集与校对集
MySQL默认使用的字符集为latin1(ISO-8859-1的别名)这是一种单字节编码的字符集。
由于中文汉字属于多字节编码的字符若要保存中文需要设置成其他支持中文的字符集如GBK、UTF8。
校对集是字符之间的比较关系在比较、排序等操作时都会用到校对集。 字符集与校对集概述
字符集
字符(character)指计算机中保存的各种文字和符号包括各种国家的文字、标点符号、图形符号、数字等。
由于计算机采用二进制保存数据用户输入的字符将会按照一定的规则转换为二进制后保存这个过程就是字符编码(character encoding)将一系列字符的编码规则组合起来就形成了字符集(character setcharset)。
MySQL提供了各种字符集的支持通过 SHOW CHARACTER SET; 可以查看可用字符集。
常用字符集
字符集单字符最大长度支持的语言latin11字节西欧字符、希腊字符等gbk2字节简体和繁体中文、日文、韩文等utf83字节世界上大部分国家的文字
单字符占用的存储空间越多所支持的语言越多。
表中列举的字符集都向下兼容 ASCII(美国信息交换标准代码)若输入的字符在 ASCII 范围内(0x00~0x7F)则编码结果是完全相同的。 由于历史原因MySQL 中的 utf8 编码与标准的 UTF-8(RFC3629) 存在一些差别。标准的 UTF-8 规定一个字符最多使用 4 字节而 MySQL 中的 utf8 编码一个字符最多使用 3 字节这导致 UTF-8 中的一些特殊字符如emoji符号无法在 MySQL 的 utf8 编码中使用。为了解决这个问题MySQL从5.5.3版本开始新增了 utf8mb4 编码将一个字符扩展到4字节。因此如果要考虑支持 RFC3629 规范应使用 utf8mb4 编码。 校对集
MySQL中提供了许多校对集用于为不同字符集指定比较和排序规则。
例如latin1字符集默认的校对集为 latin1_swedish_ci。
校对集的名称由 _ 分隔的3部分组成开头是对应的字符集中间是国家名或 general结尾是 ci、cs或bin。其中ci 表示不区分大小写cs 表示区分大小写bin 表示以二进制方式比较。
通过 SHOW COLLATION; 可以查看 MySQL 可用的校对集。 字符集与校对集的设置
MySQL环境
使用 MySQL 命令 SHOW VARIABLES LIKE character%;可以查看与字符集相关的变量。
---------------------------------------------------------------
| Variable_name |Value |
---------------------------------------------------------------
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:\mysql5.7\share\charsets\ |
---------------------------------------------------------------上述结果表示当前会话(Session)使用的字符集在不同客户端环境中的输出结果可能不同。
这里所说的会话是指从客户端登录服务器到退出的整个过程。例如依次打开两个客户端并登录服务器就产生了两个会话不同客户端属于不同的会话。
不同的客户端可以指定不同的字符集环境配置服务器会按照不同的配置进行处理。
变量名说 明character_set_client客户端字符集character_set_connection客户端与服务器连接使用的字符集character_set_database默认数据库使用的字符集从5.7.6版本开始不推荐使用character_set_filesystem文件系统字符集character_set_results将查询结果如结果集或错误消息返回给客户端的字符集character_set_server服务器默认字符集character_set_system服务器用来存储标识符的字符集character_sets_dir安装字符集的目录
重点关注的变量是 character_set_client、 character_set_connection、character_set_results 和 character_set_server。
character_set_server 决定了新创建的数据库默认使用的字符集。数据库的字符集决定了数据表的默认字符集数据表的字符集决定了字段的默认字符集。
character_set_client、character_set_connection 和character_set_results 分别对应客户端、连接层和查询结果的字符集。
通常情况下这3个变量的值是相同的具体值由客户端的编码而定从而使客户端输入的字符和输出的查询结果都不会出现乱码。
通过 set 变量名 值; 命令可以更改变量的值。
set character_set_clientgbk;
set character_set_connectiongbk;
set character_set_resultsgbk;上述命令输入比较麻烦在 MySQL 中还可以通过 set names 字符集; 直接修改上述3个变量的值即set names gbk;。
注意 使用 set或 set names 修改字符集只对当前会话有效不影响其他会话且会话结束后下次会话仍然使用默认值。 与 character_set_connection、character_set_database、character_set_server 对应的校对集分别通过变量 collation_connection、collation_database、collation_server来指定。 若字段使用 utf8 字符集而客户端使用 gbk 字符集MySQL 会自动进行编码转换。由于 utf8 和 gbk 本质上是不同的字符集虽然大部分常见的字符可以转换成功但若遇到其中一个字符集中没有的特殊字符则可能会出现乱码。
数据库
在创建数据库时设定字符集和校对集。
[DEFAULT] CHARACTER SET [] charset_name
[DEFAULT] COLLATE [] collation_nameCHARACTER SET 用于指定字符集COLLATE 用于指定校对集。
若仅指定字符集表示使用该字符集的默认校对集若仅指定校对集表示使用该校对集对应的字符集。
示例
# 创建数据库指定字符集为 utf8使用默认校对集 utf8_general_ci
CREATE DATABASE 数据库名 CHARACTER SET utf8;
# 创建数据库指定字符集为 utf8校对集为 utf8_bin
CREATE DATABASE 数据库名 CHARACTER SET utf8 COLLATE utf8_bin;数据表
数据表的字符集与校对集在表选项中设定。
[DEFAULT] CHARACTER SET [] charset_name
[DEFAULT] COLLATE [] collation_name若没有为数据表指定字符集则自动使用数据库的字符集。
字段
字段的字符集与校对集在字段属性中设定。
[CHARACTER SET charset_name] [COLLATE collation_name]若没有为字段设定字符集与校对集则会自动使用数据表的字符集与校对集。
示例
CREATE TABLE 数据表名 (username VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin
);数据库设计
数据库设计要求设计人员对数据库有深入的理解才能设计出高质量的数据库。数据库设计一般分为 6 个阶段分别是需求分析、概念数据库设计、逻辑数据库设计、物理数据库设计、数据库实施、数据库运行和维护。
需求分析
需求分析阶段数据库设计人员需要分析用户的需求将分析结果记录下来形成需求分析报告。
在这个阶段中双方需要进行深入的沟通以避免理解不准确导致后续的工作出现问题。
在需求分析中有许多琐碎、耗时的工作常见的工作如下
收集数据解决冲突包括命名冲突(同名异义、异名同义、属性冲突、结构冲突为数据形成一些标准
概念数据库设计
在概念数据库设计阶段将对用户的需求进行综合、归纳、抽象形成概念模型。
概念模型使设计人员摆脱数据库系统的具体技术问题将精力集中在分析数据及数据之间联系等方面。一般通过绘制E-R图直观呈现数据库设计人员对用户需求的理解。
逻辑数据库设计
逻辑数据库设计面向数据库系统在概念数据库设计中完成 E-R 图等成果后将其转换为 DBMS 支持的数据模型(如关系模型)完成实体、属性和联系的转换。
在进行逻辑数据库设计时应遵循一些规范化理论如范式。不规范的设计可能会导致数据库出现大量冗余、插入异常、删除异常等问题。
物理数据库设计
物理数据库设计阶段需要确定数据库的存储结构、文件类型等。
通常DBMS为了保证其独立性与可移植性承担了大部分任务数据库设计人员只需要考虑硬件、操作系统的特性为数据表选择合适的存储引擎为字段选择合适的数据类型等以及评估磁盘空间需求等工作。
数据库实施
数据库实施就是将前面那些工作的成果实施起来比如使用SQL语句创建数据库、数据表编写与调试应用程序等。
数据库运行和维护
数据库运行和维护就是将数据库系统正式投入运行在运行后进行一些维护、调整、备份、升级等工作。 数据库设计范式
数据库设计对数据的存储性能、数据的操作都有很大的关系。为了避免不规范的数据库出现数据冗余造成插入、删除、更新操作异常等情况就要满足一定的规范化要求这就是范式(Normal Form)。
根据要求的程度不同范式有多种级别最常用的有第一范式(1NF)、第二范式(2NF) 和 第三范式(3NF)它们由 Edgar Frank Codd 于1971年相继提出。后来又有人提出了 Boyce-Codd 范式(BCNF)、第四范式(4NF) 和 第五范式(5NF)等。
一般来说数据库设计只需满足第三范式(3NF)就可以了。 第一范式
第一范式(1NF) 是指数据库表的每一列都是不可分割的基本数据项同一列中不能有多个值即实体中的某个属性不能有多个值或不能有重复的属性。
简而言之第一范式遵从原子性属性不可再分。
演示
❌不满足第一范式的情况。 表格中倾斜的字体为主键。
表1 的问题在于 “联系方式” 包含了多个值可以再细分表2 的问题在于 “手机号” 属性重复。
✔️为了满足第一范式应将用户和联系方式分成两个表保存两个表是一对多的联系如下图所示。 第二范式
第二范式(2NF) 是在第一范式的基础上建立起来的满足第二范式必须先满足第一范式。
第二范式要求实体的属性完全依赖于主键不能仅依赖主键的一部分(对于复合主键而言)。
简而言之第二范式遵从唯一性非主键字段需完全依赖主键。
演示
❌不满足第二范式的情况。 表格中倾斜的字体为主键。
在表6中用户编号和订单编号组成了复合主键付款状态完全依赖复合主键而用户名只依赖用户编号。
采用上述方式设计的用户表存在如下问题
插入异常如果一个用户没有下过订单则该用户无法插入删除异常如果删除一个用户所有的订单则该用户也会被删除更新异常由于用户名冗余修改一个用户时需要修改多条记录。如果稍有不慎漏改某些记录会出现更新异常。
✔️为了满足第二范式将复合主键移动到订单表中如下图所示。 第三范式
第三范式(3NF)是在第二范式的基础上建立起来的即满足第三范式必须先满足第二范式。
第三范式要求一个数据表中每一列数据都和主键直接相关而不能间接相关。
简而言之第三范式就是非主键字段不能相互依赖。
演示
❌不满足第三范式的情况。 表格中倾斜的字体为主键。
在表9中用户享受的折扣与用户等级相关两者存在依赖关系。
采用这种方式设计的用户表存在如下问题
插入异常新插入用户的等级如果在1、2之外其享受的折扣无处参考删除异常如果删除某个等级下所有的用户该等级对应的折扣也被删除更新异常如果修改某个用户的等级折扣也必须随之修改如果修改某个等级的折扣又因为折扣存在冗余容易发生漏改。
✔️为了满足第三范式将等级与折扣拆分到单独的表中如下图所示。 单表操作 复制表结构和数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 LIKE 旧表名;在上述语法中仅能从 旧表名 中复制一份相同的表结构但不会复制表中保存的数据。
复制已有的表数据
数据复制也可称为蠕虫复制是新增数据的一种方式它是从已有的数据中获取数据并且将获取到的数据插入到对应的数据表中实现成倍的增加。
此种方式获取数据与插入数据的表结构要相同否则可能会遇到插入不成功的情况。
INSERT [INTO] 数据表名1 [(字段列表)] SELECT [(字段列表)] FROM 数据表名2;❗若数据表中含有主键而主键又具有唯一性所以在数据复制时还要考虑主键冲突的问题。 临时表的使用
临时表指的是一种仅在当前会话中可见并在当前会话关闭时自动删除的数据表。它主要用于临时存储数据。
临时表的语法很简单只需在CREATE与TABLE关键宇中间添加 TEMPORARY 即可。
# 方式1创建临时表
CREATE TEMPORARY TABLE mydb.tem1 (id INT);#方式2创建临时表
CREATE TEMPORARY TABLE mydb.tem2 SELECT id, name FROM mydb.goods ;在上迷语句中创建临时表时指定的数据库可以是 MySQL 服务器中存在的数据库也可以是不存在的数据库。
若数据库不存在操作临时表时必须使用 数据库.临时表名 指定临时表所在的数据库。
临时表中数据的操作与普通表相同都可以进行 SELECT、INSERT、UPDATE 和 DELETE 操作。
SHOW TABLES 不能查看指定数据库下有哪些临时表并且临时表的表名必须使用 ALTER TABLE 修改而不能使用 RENAME TABLE … TO 修改。 解决主键冲突
在对数据表插人数据时若表中的主键含有实际的业务意义因此在插入数据时若不能确定对应的主键是否存在往往会出现主键冲突的情况。
例如mydb.my_goods 表经过数据复制以后再插入编号为 20 的商品信息(橡皮文具类用于修正书写错误具体SQL语句及执行结果如下。
INSERT INTO mydb.my_goods(id, name, content, keyword) VALUES (20, 橡皮, 用于修正书写错误, 文具);
ERROR 1062 (23000): Duplicate entry 20 for key PRIMARY 从上述的执行结果可知系统提示插入数据的主键发生冲突。若要解决这类问题MySQL中提供了两种方式分别为主键冲突更新和主键冲突替换。
主键冲突更新
主键冲突更新操作指的是当插人数据的过程中若发生主键冲突则插入数据操作利用更新的方式实现。
INSERT [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (值列表) ON DUPLICATE KEY UPDATE 字段名1 新值1 [,字段名2 新值2] ...;在 INSERT 语句后添加 ON DUPLICATE KEY UPDATE 可在发生主键冲突时更新此条记录中通过 字段名1 新值1 [,字段名2 新值2] ... 设置的字段名对应的新值。
例如修改以上发生主键冲突的插入语句。
INSERT INTO mydb.my_goods(id, name, content, keyword) VALUES (20, 橡皮, 用于修正书写错误, 文具) ON DUPLICATE KEY UPDATE name 橡皮, content 用于修正书写错误, keyword 文具;主键冲突替换
主键冲突替换操作指的是当插入数据的过程中若发生主键冲突则删除此条记录并重新插入。
REPLACE [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (值列表) [,(值列表)] ...;REPLACE 语句与 INSERT 语句的使用类似区别在于前者每执行一次就会发生两个操作(删除记录和插入记录)。
具体SQL语句及执行结果如下。
REPLACE INTO mydb.my_goods(id, name, content, keyword) VALUES (20, 橡皮, 用于修正书写错误, 文具);清空数据
TRUNCATE [TABLE] 表名;TRUNCATE 操作虽然与 DELETE 语句的使用非常相似但是两者在本质上有一定的区别具体如下。 实现方式不同 TRUNCATE 本质上先执行删除 (DROP) 数据表的操作然后再根据有效的表结构文件 (.frm) 重新创建数据表的方式来实现数据清空操作。DELETE 语句则是逐条地删除数据表中保存的记录。 执行效率不同 在针对大型数据表(如千万级的数据记录)时TRUNCATE 清空数据的实现方式决定了它比 DELETE 语句删除数据的方式执行效率更高。 对 AUTO_INCREMENT 的字段影响不同 TRUNCATE 清空数据后再次向表中添加数据自动增长字段会从默认的初始值重新开始。使用DELETE 语句删除表中的记录时则不影响自动增长值。 删除数据的范围不同 TRUNCATE 语句只能用于清空表中的所有记录而 DELETE 语句可通过 WHERE 指定删除满足条件的部分记录。 返回值含义不同 TRUNCATE 操作的返回值一般是无意义的而 DELETE 语句则会返回符合条件被删除的记录数。 所属SQL语言的不同组成部分 -DELETE语句属于DML数据操作语句而 TRUNCATE 通常被认为是 DDL 数据定义语句。 当删除的数据量很小时DELETE 的执行效率要比 TRUNCATE 高只有删除的数据量很大时才能看出 TRUNCATE 的执行效率比 DELETE 高。 去除重复记录
SELECT select选项 字段列表 FROM 数据表select选项 默认值为 All表示保存所有查询到的记录当设置为 DISTINCT 时表示去除重复记录只保留一条。
❗当查询记录的字段有多个时必须所有字段的值完全相同才被认为是重复记录。 排序
单字段排序
单字段排序指的是查询时仅按照一个指定字段进行升序或降序排序。
SELECT [* | (字段列表)] FROM 数据表名 ORDER BY 字段名 [ASC | DESC];ASC 表示升序DESC 表示降序而 ORDER BY 默认值为 ASC。
多字段排序
当在开发中需要根据多个条件对查询的数据进行排序时可以采用多字段排序。
SELECT [* | (字段列表)] FROM 数据表名 ORDER BY 字段名1 [ASC | DESC] [, 字段名2 [ASC | DESC]];多字段排序首先按照 字段名1 进行排序当字段1 的值相同时再按照 字段名2 进行排序依此类推。
数据表的字符集是 utf8当排序的字段为中文时默认不会按照中文拼音的顺序排序。那么在不改变数据表结构的情况下可以使用 CONVERT(字段名 USING gbk) 函数强制让指定的字段按中文排序。
在按照指定字段进行升序排列时如果某条记录的字段值为 NULL则系统会将 NULL 看作是最小的值从而将其显示在查询结果中的 第一条 记录的位置。 限量
对于一次性查询出的大量记录不仅不便于阅读查看MySQL中提供了一个关键字 LIMIT可以限定记录的数量也可以指定查询从哪一条记录开始。
SELECT [select选项] 字段列表 FROM 数据表名 [WHERE条件表达式] [ORDER BY 字段 ASC | DESC] LIMIT [OFFSET,] 记录数;记录数 表示限定获取的最大记录数量也就是说在记录数大于数据表符合要求的实际记录数量时以实际记录数为准。
当 LIMIT 后仅含有此参数时表示从第1条记录开始获取。
OFFSET 表示偏移量用于设置从哪条记录开始MySQL中默认第1条记录的偏移量值为0第2条记录的偏移量值为1依此类推。
排序后限量更新或删除数据
在MySQL中除了对查询记录进行排序和限量外对数据表中记录的更新与删除操作也可以进行排序和限量。
# 数据更新的排序与限量
UPDATE 数据表名 SET 字段 新值, ... [WHERE 条件表达式] ORDER BY 字段 ASC|DESC LIMIT 记录数;#数据删除的排序与限量
DELETE FROM 数据表名[WHERE 条件表达式] ORDER BY 字段 ASC|DESC LIMIT 记录数;UPDATE和 DELETE 操作中添加 ORDER BY 表示根据指定的字段按顺序更新或删除符合条件的记录。如果 UPDATE 和 DELETE 操作没有添加 WHERE 条件则可以使用 LIMIT 来限制更新和删除的数量。
例如为sh_goods表中价格最便宜的两种商品库存设置为500。
UPDATE sh_goods SET stock 500 ORDER BY price ASC LIMIT 2;别名
在MySQL中执行查询操作时可以根据具体情况为获取的字段设置别名。
SELECT 字段1 [AS] 别名1, 字段2 [AS] 别名2 [, ...] FROM 表名;AS 用于为其前面的字段、表达式、函数等设置别名。也可以省略AS使用空格代替。
在MySQL中还可以使用 AS 为数据表设置别名。
SELECT 表别名.字段 [, ...] FROM 表名 [AS] 表别名同样地在为数据表设置别名时AS也可以省略使用空格代替。
字段与表设置别名后在排序和分组中可以使用原来的字段名等也可以使用别名。表的别名主要在多表查询中使用。 聚合函数
常用的聚合函数
函 数 名描 述COUNT()返回参数字段的数量不统计为NULL的记录SUM()返回参数字段之和AVG()返回参数字段的平均值MAX()返回参数字段的最大值MIN()返回参数字段的最小值GROUP_CONCAT()返回符合条件的参数字段值的连接字符串JSON_ARRAYAGG()将符合条件的参数字段值作为单个JSON数组返回MySQL5.7.22新增JSON_OBJECTAGG()将符合条件的参数字段值作为单个JSON对象返回MySQL5.7.22新增
COUNT()、SUM()、AVG()、MAX()、MIN() 和 GROUP_CONCAT() 函数中可以在参数前添加DISTINCT表示对不重复的记录进行相关操作。
COUNT() 的参数设置为 * 时表示统计符合条件的所有记录包含NULL)。 分组 分组统计
在查询数据时在 WHERE 条件后添加 GROUP BY 即可根据指定的字段进行分组。
SELECT [select选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名;分组排序
在MySQL中默认情况下为分组操作的字段提供了升序排序的功能因此在分组时可以为指定的字段进行升序或降序排序。
SELECT [select选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名[ASC | DESC];GROUP BY 分组排序的实现不需要使用 ORDER BY直接在分组字段后添加 ASC(升序默认值可省略) 或 DESC(降序) 即可。 多分组统计
在对数据进行分组统计时MySQL中还支持数据按照某个字段进行分组后对已经分组的数据进行再次分组的操作以实现多分组统计。
SELECT [select选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名1 [ASC | DESC],[字段名2 [ASC |DESC]]...;查询出的数据首先按照字段1进行分组排序再将字段1相同的结果按照字段2进行分组排序依此类推。 回溯统计
回溯统计可以简单地理解为在根据指定字段分组后系统又自动对分组的字段向上进行了一次新的统计并产生一个新的统计数据且该数据对应的分组字段值为 NULL。
回溯统计的实现很简单只需要在 GROUP BY 字段 后添加 WITH ROLLUP 即可。
SELECT [select 选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名1 [ASC |DESC],[字段名2 [ASC | DESC]] ... WITH ROLLUP;下面查看 sh_goods 表中每个分类 category_id 下的商品数量为例演示回溯统计的使用。
SELECT category_id, COUNT(*) FROM sh_goods GROUP BY category_id WITH ROLLUP;
----------------------
| category_id | COUNT(*)|
----------------------
| 3 | 3 |
| 6 | 1 |
| NUll | 4 |
----------------------从上述执行结果可知在获取每种商品分类 category_id 下的商品数量后系统又自动对获取的数量进行了一次累加统计并且此累加的新数据(如4)对应的分组字段(如category_id)的值为NULL。此行的记录就是对category_id分组的一次回溯统计。
在了解了单字段的分组回溯统计后演示如何在MySQL中对多分组进行回溯统计。
SELECT score, comment_count, COUNT(*) FROM sh_goods GROUP BY score, comment_count WITH ROLLUP;
--------------------------------
| score | comment_count | COUNT(*) |
--------------------------------
| 2.5 | 200 | 1 |
| 2.5 | NUll | 1 |
| 3.9 | 500 | 2 |
| 3.9 | NUll | 2 |
| NUll | NUll | 3 |
--------------------------------在上述SQL语句中分组操作根据 GROUP BY 后的字段从前往后依次执行即先按 score 分组然后再按 comment_count 分组
数据分组后系统再进行回溯统计它与分组的操作正好相反从 GROUP BY 后最后一个指定的分组字段开始回溯统计并将结果上报然后根据上报结果依次向前一个分组的字段进行回溯统计即先回溯统计 comment_count 分组的结果再根据 comment_count 的回溯结果对 score 分组进行回溯统计。
从执行结果可看出score 值相同的情况下按 comment_count 分组后实现了 2 次回溯统计对应的分组字段 comment_count 含有 2 个 NULL 值统计结果分别进行了累加接着按上次的结果对score 分组字段进行了1次回溯统计对应的分组字段 score 中含有一个NULL值然后又对上一次的统计结果再次进行累加。
虽然回溯统计对数据的分析很有帮助但是MySQL的同一个查询语句中回溯统计(WITH ROLLUP) 与排序(ORDER BY) 仅能出现一个。 统计筛选
当对查询的数据执行分组操作时可以利用 HAVING 根据条件进行数据筛选与WHERE功能相同但是在实际运用时两者有一定的区别。 WHERE 操作是从数据表中获取数据将数据从磁盘存储到内存中而 HAVING 是对已存放到内存中的数据进行操作。 HAVING 位于GROUP BY 子句后而 WHERE 位于 GROUP BY 子句之前。 HAVING 关键字后可以使用聚合函数而 WHERE 则不可以。通常情况下HAVING 关键字与GROUP BY 一起使用对分组后的结果进行过滤。
SELECT [select选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名 [ASC | DESC], ...[WITH ROLLUP] HAVING 条件表达式;WHERE 条件之后的所有语句都是对内存中的数据进行操作。而 HAVING 则根据条件表达式对分组后的内容进行过滤。 运算符
在数据库操作中数据的 SELECT、UPDATE 和 DELETE 等操作都可以使用条件表达式用于获取、更新 或 删除 给定条件的数据。 算术运算符
算术运算符适用于数值类型的数据通常应用在 SELECT 查询结果的字段中在 WHERE 条件表达式中应用较少。
运算符描 述运算符描 述加运算/除运算-减运算%取模运算*乘运算
运算符两端的数据可以是真实的数据(如5)或数据表中的字段(如price)而参与运算的数据一般称之为操作数操作数与运算符组合在一起统称为表达式(如52)。
在MySQL中可以直接利用SELECT查看数据的运算结果。
无符号的加减乘法运算
在MySQL中若运算符 和 * 的操作数都是无符号整型则运算结果也是无符号整型。
例如商品表 sh_goods 中的 id 字段就是无符号整型下面对 sh_goods 表中前2条记录的 id 进行加1、减1 以及 乘2 操作。
SELECT id, id1, id-1, id*2 FROM sh_goods LIMIT ;
----------------------
| id | id1 | id-1 | id*2 |
----------------------
| 1 | 2 | 0 | 2 |
| 2 | 3 | 1 | 4 |
----------------------有符号的减法运算结果
MySQL中默认情况下运算符 的操作数若都为无符号整型则结果一定是无符号的整型、若操作数的差值为负数那么系统就会报错。
例如对 sh_ goods 表中前2条记录的id执行减3操作。
SELECT id-3 FROM sh_goods LIMIT 2;
ERROR 1690 (22003): BIGINI UNSIGNED value is out of range in (shop.sh_goods.id -3)从上述执行结果可知减法运算的结果已经超出了无符号 BIGINT 的最大范围。因此无论运算符 的操作数是否含有符号若要获得一个有符号的运算结果可以使用 CAST(... AS SIGNED 将无符号整型 id 强制转换为有符号的整型数据。
SELECT CAST(id AS SIGNED)-3 FROM sh_goods LIMIT 2;
----------------------
| CAST(id AS SIGNED)-3 |
----------------------
| -2 |
| -1 |
----------------------上述执行结果-2 和 1 中的符号 表示负号它是一个一元操作符仅有一个操作数如2。而运算符 表示减法运算时是一个二元操作符有两个操作数如 id-3 中的 id 和3。
含有精度的运算
算术运算除了可以对整数运算外还可以对浮点数进行运算。
在对浮点数进行加减运算时运算结果中的精度(小数点后的位数)等于参与运算的操作数的最大精度。
例如 1.21.4001.400 的精度最大为 3则运算结果的精度就为 3在对浮点数进行乘法运算时运算结果中的精度以参与运算的操作数的精度和为准。例如 1.2*1.4001.2 的精度为 11.400 的精度为3则运算结果中的精度就为4(13)。
/ 运算
运算符 / 在MySQL中用于除法操作且运算结果使用浮点数表示浮点数的精度等于被除数( / 运算符左侧的操作数) 的精度加上系统变量 div_precision_increment 设置的除法精度增长值读者可通过以下SQL语句查找其默认值。
SHOW VARIABLES LIKE div_precision_increment
--------------------------------
| Variable_name | Value |
--------------------------------
| div_precision_increment | 4 |
--------------------------------NULL参与算术运算
在算术运算中NULL是一个特殊的值它参与的算术运算结果均为NULL。
DIV与MOD运算符
在MySQL中运算符DIV 与 / 都能实现除法运算区别在于前者的除法运算结果会去掉小数部分只返回整数部分。
MySQL中的运算符 MOD 与 % 功能相同都用于取模运算。
❗取模运算结果的正负与被模数(%左边的操作数)的符号相同与模数(%右边的操作数)符号无关。
MySQL中还提供了很多进行数学运算的函数常用的数学函数如下。
运 算 符描 述CEIL(x)返回大于等于 x 的最小整数FLOOR(x)返回小于等于 x 的最大整数FORMAT(x,y)返回小数点后保留 y 位的 x(进行四舍五入)ROUND(x [,y])计算离x最近的整数若设置参数y与FORMAT(x,y)功能相同TRUNCATE(x,y)返回小数点后保留 y 位的 x(舍弃多余小数位不进行四舍五入)ABS(x)获取x的绝对值MOD(x,y)求模运算与x%y的功能相同PI()计算圆周率SQRT(×)求x的平方根POW(x,y)幂运算函数计算x的y次方与POWER(x,y)功能相同RAND()默认返回 0 到 1 之间的随机数包括0和1
RAND() 函数用于返回 0 到 1 之间的随机数若要获取指定区间 (min≤nummax) 内的随机数时,使用表达式 FLOOR(minRAND()*(max-min)) 获取。
若要获取相同的随机数可以为 RAND(函数添加整数参数。
RAND() 函数还可与 ORDER BY 结合使用用于随机获取指定的数据。
例如根据商品分类进行分组然后使用 ORDER BY RAND() 实现分组后记录的随机排序。 比较运算符
比较运算符是MySQL常用运算符之一通常应用在条件表达式中对结果进行限定。
MySQL中比较运算符的结果值有3种分别为1(TRUE表示为真)、0(FALSE表示为假) 或 NULL。
运 算 符描 述用于相等比较可以进行 NULL 值比较的相等运算符表示大于比较表示小于比较表示大于等于比较表示小于等于比较、!表示不等于比较BETWEEN … AND …比较一个数据是否在指定的闭区间范围内若在则返回1若不在则返回0NOT BETWEEN … AND …比较一个数据是否不在指定的闭区间范围内若不在则返回1若在则返回0IS比较一个数据是否是TRUE、FALSE 或 UNKNOWN若是则返回1否则返回0IS NOT比较一个数据是否不是TRUE、FALSE 或 UNKNOWN若不是则返回1否则返回0IS NULL比较一个数据是否是NULL若是则返回1否则返回0IS NOT NULL比较一个数据是否不是NULL若不是则返回1否则返回0LIKE ‘匹配模式’获取匹配到的数据NOT LIKE ‘匹配模式’获取匹配不到的数据
所有运算符都可以对数字和字符串进行比较若参与比较的操作数的数据类型不同则MySQL会自动将其转换为同类型的数据后再进行比较。
MySQL中比较运算符 、、、、、、! 在与NULL进行比较时结果均为NULL。
在MySQL中运算符 与 均可以用于比较数据是否相等两者的区别在于后者可以对 NULL 值进行比较。
在条件表达式中若需要对指定区间的数据进行判断时可使用 BETWEEN…AND… 实现。
BETWEEN 条件1 AND 条件2条件1 到 条件2 之间的范围(包含条件1和条件2)并且在设置时条件1必须小于等于条件2。 正则匹配查询
MySQL中查询数据时除了可以使用 LIKE 实现模糊查询还可以利用 REGEXP 关键字指定正则匹配模式轻松完成更为复杂的查询。
其中正则表达式的语法与其他编程语言相同可自行查看相关的资料进行学习这里不再赘述。
例如获取 sh_goods 表中描述字段内含有 人 或 必备 词语的商品 id、name 和 content 字段内容。
SELECT id, name, content, FROM sh_goods WHERE content REGEXP 人|必备;在上述SQL语句中REGEXP 关键字用于标识正则匹配人 | 必备 为正则匹配模式。其中符号 | 在正则中表示分隔符用于分隔多种条件在匹配时只要指定字段满足分隔符左右两边条件中的一个就表示匹配成功。
除了比较运算符外MySQL中还提供了很多进行比较运算的函数常用的比较函数如下。
函 数描 述IN()比较一个值是否在一组给定的集合内NOT IN()比较一个值是否不在一组给定的集合内GREATEST()返回最大的一个参数值至少两个参数LEAST()返回最小的一个参数值至少两个参数IS NULL()测试参数是否为空COALESCE()返回第一个非空参数INTERVAL()返回小于第一个参数的参数索引STRCMP()比较两个字符串
函数 GREATEST() 和 LEAST() 的参数至少有两个用于比较后返回一个最大或最小的参数值。
IN() 只要比较的字段或数据在给定的集合内那么比较结果就为真NOT IN() 正好与 IN() 的功能相反。 逻辑运算符
逻辑运算符也是MySQL常用运算符之一通常应用在条件表达式中的逻辑判断与比较运算符结合使用。
参与逻辑运算的操作数以及逻辑判断的结果只有3种分别为1(TRUE,表示为真)、0(FALSE,表示为假)或NULL。
运 算 符描 述AND 或 逻辑与。操作数全部为真则结果为1否则为0OR 或 II逻辑或。操作数中只要有一个为真则结果为1否则为0NOT 或 !逻辑非。操作数为0则结果为1操作数为1则结果为0XOR逻辑异或。操作数一个为真一个为假则结果为1若操作数全部为真或全部为假则结果为0
仅有逻辑非(NOT或!)是一元运算符其余均为二元运算符。另外NOT 和 ! 虽然功能相同但是在一个表达式中同时出现时先运算 !再运算 NOT 。
逻辑与
例查询 sh_goods 表中关键字为 “电子产品” 的5星商品
SELECT id, name, price FROM sh_goods WHERE keyword 电子产品 score 5;若使用“”连接多个相等比较的条件时可以使用a,b (x,y) 的方式简化条件表达式 (axby)的书写。
SELECT id, name, price FROM sh_goods WHERE (keyword, score) (电子产品, 5);❗在进行逻辑与操作时若操作数中含有NULL而另一个操作数若为1(真)则结果为 NULL若另一个操作数为0(假)则结果为0。
逻辑或
❗在进行逻辑或操作时若操作数中含有NULL而另一个操作数若为1(真)则结果为1若另一个操作数为0(假)则结果为NULL。
逻辑非
❗逻辑非的操作数仅有一个当操作数为0(假)时则运算结果为1当操作数为1(真)时则运算结果为0当操作数为NULL时运算结果为NULL。
逻辑异或
❗逻辑异或操作表示两个操作数同时都为1或0则结果为0若两个操作数一个为1一个为0则结果为1当操作数为NULL时则结果为NULL。 赋值运算符
MySQL中 是一个比较特殊的运算符既可以用于比较数据是否相等又可以表示赋值。
因此MySQL为了避免系统分不清楚运算符 表示赋值还是比较的含义特意增加一个符号 : 用于表示赋值运算。
在MySQL中INSERT…SET 和 UPDATE…SET 中出现的运算符 都会被认为是赋值运算符。因此建议除此之外的其他情况若需要赋值运算符推荐使用 :如为变量赋值。 位运算符
位运算符是针对二进制数的每一位进行运算的符号运算的结果类型为 BIGINT最大范围可以是64位。
运 算 符描 述示 例按位与SELECT b’1001’ b’1011’结果为 9|按位或SELECT b’1001’ | b’1011’结果为 11^按位异或SELECT b’1001’ ^ b’1011’结果为 2按位左移SELECT b’1001’ 2结果为 36按位右移SELECTb’1001’ 2结果为 2~按位取反SELECTb’1001’ b’1011’结果为2
MySQL 5.7 中参与位运算的数据只能是 BIGINT 类型(64位的整数)而在 MySQL 8.0 中则允许二进制字符串类型的参数如 BINARY、VARBINARY 和 BLOB。因此MySQL5.7中二进制类型字段的位运算可能在MySQL8.0中产生不同结果系统也会报相关的警告信息。
除了位运算符外MySQL中还提供了进行位运算的函数常用的运算相关函数如下。
函 数描 述BIT_COUNT(N)返回在参数N中设置的比特位二进制位为1的数量BIT_AND()按位返回与的结果BIT_OR()按位返回或的结果BIT_XOR()按位返回异或的结果 运算符优先级
运算符优先级可以理解为运算符在一个表达式中参与运算的先后顺序优先级别越高则越早参与运算优先级别越低则越晚参与运算。
运算符(从高到低)INTERVALBINARY, COLLATE! (一元负号)、~(一元按位取反)^*、/、DIV、%、MOD (相减运算符号)、 、| (比较运算符、、、、、、、!、IS、LIKE、REGEXP、IN BETWEEN、CASE、WHEN、THEN、ELSE NOTAND、XOROR、|| (赋值运算符)、:
同行的运算符具有相同的优先级除赋值运算符从右到左运算外其余相同级别的运算符在同一个表达式中出现时运算的顺序为从左到右依次进行。
若要提升运算符的优先级别可以使用圆括号 () 当表达式中同时出现多个圆括号时最内层的圆括号中的表达式优先级最高。 多表操作 联合查询
联合查询是多表查询的一种方式在保证多个SELECT语句的查询字段数相同的情况下合并多个查询的结果。
SELEC ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...];UNION 是实现联合查询的关键字ALL和 DISTINCT 是联合查询的选项。
其中ALL 表示保留所有的查询结果DISTINCT是默认值可以省略表示去除完全重复的记录。
SELECT 查询的字段个数 必须相同且联合查询的结果中只保留第一个 SELEC语句对应的字段名称即使 UNION 后 SELECT 查询的字段与第一个 SELECT 查询的字段表达含义或数据类型不同MySQL也仅会根据查询字段出现的顺序对结果进行合并。
若要对联合查询的记录进行排序等操作需要使用圆括号 () 包裹每一个 SELECT语句在 SELECT语句内 或 在联合查询的最后添加 ORDER BY 语句。并且若要排序生效必须在 ORDER BY 后添加LIMIT 限定联合查询排序的数量通常推荐使用大于表记录数的任意值。 交叉连接
交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积。
SELECT 查询字段 FROM 表1 CROSS JOIN 表2;CROSS JOIN 用于连接两个要查询的表通过该语句可以查询两个表中所有的数据组合。 内连接
内连接是一种常见的连接查询它根据匹配条件返回第1个表与第2个表 所有匹配成功的记录。
SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 匹配条件;ON 用于指定内连接的查询条件在不设置ON时与交叉连接 等价。此时可以使用 WHERE 完成条件的限定效果与ON一样。但由于 WHERE 是限定已全部查询出来的记录那么在数据量很大的情况下此操作会浪费很多性能。
自然连接查询是内连接中的一种特殊查询。它是指相互连接的表在物理上为同一个表但逻辑上分为两个表。 左外连接
左外连接是外连接查询中的一种也可以将其称为左连接。它用于返回连接关键字 左表中所有的记录以及右表中符合连接条件的记录。当左表的某行记录在右表中没有匹配的记录时右表中相关的记录将设为NULL。
SELECT 查询字段 FROM 表1 LEFT [OUTER] JOIN 表2 ON 匹配条件;关键字 LEFT [OUTER] JOIN 左边的表(表1)被称为左表也可称为主表关键字右边的表(表2被称为右表也可称为从表。其中OUTER在查询时可以省略。 右外连接
右外连接也是外连接查询中的一种可以将其称为右连接。它用于 返回连接关键字右表(主表) 中所有的记录以及左表(从表) 中符合连接条件的记录。当右表的某行记录在左表中没有匹配的记录时左表中相关的记录将设为NULL。
SELECT 查询字段 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 匹配条件;外连接是最常用的一种查询数据的方式分左外连接和右外连接。它与内连接的区别是内连接只能获取符合连接条件的记录而外连接不仅可以获取符合连接条件的记录还可以保留主表与从表不能匹配的记录。 USING关键字
在连接查询时若数据表连接的字段同名则连接时的匹配条件可以使用 USING 代替ON。
SELECT 查询字段 FROM 表1 [CROSS | INNER | LEFT | RIGHT] JOIN 表2 USING(同名的联结字段);多个同名的连接字段之间使用逗号分隔。
USING关键字在实际开发中并不常使用原因在于设计表的时候不能确定使用相同的字段名称保存对应的数据。 子查询
子查询可以理解为在一个SQL语句A(SELECT、INSERT、UPDATE等)中嵌入一个查询语句B作为执行的条件或查询的数据源(代替FROM后的数据表)那么B就是子查询语句它是一条完整的SELECT 语句能够独立地执行。
在含有子查询的语句中子查询必须书写在圆括号内。SQL语句首先会执行子查询中的语句然后再将返回的结果作为外层SQL语句的过滤条件当遇到同一个SQL语句中含有多层子查询时它们执行的顺序是从最里层的子查询开始执行。
子查询的划分方式有多种最常见的是以功能和位置进行划分。按子查询的功能可以分为标量子查询、列子查询、行子查询和表子查询。
按子查询出现的位置可以分为 WHERE子查询 和 FROM子查询。
标量子查询、列子查询 和 行子查询 都属于WHERE子查询而表子查询属于FROM子查询。 标量子查询
标量子查询指的是子查询返回的结果是一个数据即一行一列。
WHERE 条件判断 { | } (SELECT 字段名 FROM 数据源 [WHERE] [GROUP BY] [HAVING] [ORDER BY][LIMIT]);标量子查询利用比较运算符 或 判断子查询语句返回的数据是否与指定的条件相等或不等然后根据比较结果完成相关需求的操作。其中数据源表示一个符合二维表结构的数据如数据表。 列子查询
列子查询指的是子查询返回的结果是一个字段符合条件的所有数据即一列多行。
WHERE 条件判断 {IN | NOT IN} (SELECT 字段名 FROM 数据源 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]);列子查询利用比较运算函数 IN() 或 NOTIN()判断指定的条件是否在子查询语句返回的结果集中然后根据比较结果完成相关需求的操作。 行子查询
当子查询的结果是一条包含多个字段的记录(一行多列)时称为行子查询。
WHERE (指定字段名1, 指定字段名2, ...)
(SELECT 字段名1, 字段名2, ... FROM 数据源 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]);行子查询返回的一条记录与指定的条件进行比较比较的运算符通常使用 表示子查询的结果必须全部与指定的字段相等才满足WHERE指定的条件。除此之外还可以使用MySQL中其他的比较运算符如 等它们表示的含义如下表。
不同运算符的行比较描 述(a, b) (x, y)表达的含义等价于 (ax)AND(by)(a, b) (x, y)表达的含义等价于 (a x)AND(b y)(a, b) (x, y)表达的含义等价于 (a x)OR(b y)(a, b) (x, y)表达的含义等价于 (ax) OR ((ax)AND(by))(a, b) (x, y)表达的含义等价于 (ax) OR ((ax)AND(by))(a, b) (x, y)表达的含义等价于 (ax) OR ((ax)AND(by))(a, b) (x, y)表达的含义等价于 (ax) OR ((ax)AND(by))
从表中可知行在相等比较(或)时各条件之间是与的逻辑关系在不等比较或)时各条件之间是或的逻辑关系在进行其他方式比较时各条件之间的逻辑关系包含两种情况。 表子查询
表子查询指的是子查询的返回结果用于FROM数据源它是一个符合二维表结构的数据可以是一行一列、一列多行、一行多列 或 多行多列。
SELECT 字段列表 FROM (SELECT 语句) [AS] 别名 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT];FROM 后的数据源都是表名。因此当数据源是子查询时必须为其设置别名同时也是为了将查询结果作为一个表使用时可以进行条件判断、分组、排序以及限量等操作。 子查询关键字
在 WHERE 子查询中不仅可以使用比较运算符还可以使用MySQL提供的一些特定关键字如IN。除此之外常用的子查询关键字还有 EXISTS、ANY 和 ALL。
带EXISTS关键字的子查询
带 EXISTS 关键字的子查询返回的结果只有0和1两个值。其中0代表不成立1代表成立。
WHERE EXISTS (子查询语句);EXISTS关键字用于判断子查询语句是否有返回的结果若存在则返回1否则返回0。
带ANY关键字的子查询
ANY关键字的子查询时表示给定的判断条件只要符合ANY子查询结果中的任意一个就返回1否则返回0。
WHERE 表达式 比较运算符 ANY (子查询语句);当比较运算符为 时其执行的效果等价于 IN 关键字。 MySQL中还有一个关键字SOME,与ANY的功能完全相同。MySQL在设计时添加SOME的原因在于英文语法中虽然SOME和ANY的语法含义相同但是NOTANY和NOTSOME的含义区别很大。前者表示一点也不相当于NOTALL而后者仅用于否定部分内容。因此为了便于以英文为母语的开发者理解设计出了带SOME和ANY关键字的子查询。 带ALL关键字的子查询
ALL关键字的子查询时表示给定的判断条件只有全部符合ALL子查询的结果时才返回1否则返回0。
WHERE 表达式 比较运算符 ALL(子查询语句);带 ANY、SOME 或 ALL 关键字的子查询不能使用 比较运算符。另外若子查询结果与条件匹配时有NULL那么此条记录不参与匹配。 外键约束
在数据库设计时为了保证不同表中相同含义数据的一致性和完整性可为数据表添加外键约束。
例如员工表中添加了部门表中不存在的部门id此时就会出现数据信息保存不对等的情况若在员工表中将部门id设置为外键即可对相关的操作产生约束。例如员工表只能插人部门表中含有的记录id。 添加外键约束
外键指的是在一个表中引用另一个表中的一列或多列被引用的列应该具有主键约束或唯一性约束从而保证数据的一致性和完整性。其中被引用的表称为主表引用外键的表称为从表。
要想在创建数据表或修改数据结构时添加外键约束在相应的位置添加以下SQL语句即可。
[CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]MySQL可以通过 FOREIGN KEY...REFERENCES 关键字向数据表中添加外键约束。
可选关键字 CONSTRAINT 用于定义外键约束的名称 symbol如果省略MySQL 将会自动生成一个名字。
可选关键字 index_name 表示外键索引名称如果省略MySQL也会在建立外键时自动创建一个外键索引加快查询速度。
index_col_name, ... 表示从表中外键名称列表tbl_name 表示主表主表后的参数列表index_col_name, ...表示主键约束或唯一性约束字段。
ON DELETE 与 ON UPDATE 用于设置主表中的数据被删除或修改时从表对应数据的处理办法其后的各参数具体说明如下表所示。
参数名称功能描述RESTRICT默认值。拒绝主表删除或修改外键关联字段CASCADE主表中删除或更新记录时同时自动删除或更新从表中对应的记录SET NULL主表中删除或更新记录时使用NULL值替换从表中对应的记录不适用于 NOT NULL字段NO ACTION与默认值RESTRICT相同拒绝主表删除或修改外键关联字段SET DEFAULT设默认值但InnoDB目前不支持
建立外键关系的两个数据表的相关字段数据类型必须相似也就是要求字段的数据类型可以相互转换。
例如INT 和 TINYINT 类型的字段可以建立外键关系而 INT 和 CHAR 类型的字段则不可以建立外键关系。下面在mydb数据库中以员工表employees和部门表department为例演示如何在CREATE TABLE和ALTER TABLE时添加外键约束的两种方式。
CREATETABLE时添加外键约束
为从表创建外键约束时首先要保证数据库中已存在主表否则程序会报 不能添加外键约束 的错误。
# 1.在mydb数据库下创建主表
CREATE TABLE mydb.department (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 部门编号, name VARCHAR(50) NOT NULL COMMENT部门名称
) DEFAULT CHARSETutf8;# 2.在mydb数据库下创建从表添加外键约束
CREATE TABLE mydb.employees (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 员工编号, name VARCHAR(120) NOT NULL COMMENT 员工姓名, dept_id INT UNSIGNED NOT NULL COMMENT 部门分类编号,CONSTRAINT FK_ID FOREIGN KEY(dept_id) REFERENCES department (id) ON DELETE RESTRICT ON UPDATE CASCADE
) DEFAULT CHARSETutf8;在上述SQL语句中为 employees 表中的 dept_id 字段添加外键约束与主表 department 中的主键 id相关联。
同时利用 ON DELETE 指定从表此关联字段含有数据时拒绝主表 department 执行删除操作利用 ON UPDATE 设置主表 department 执行更新操作时从表 employees 中的相关字段也执行更新操作。
❗定义外键约束名称(如FK_ID)时不能加单引号和双引号。
ALTER TABLE时添加外键约束
对于已经创建的数据表则可以通过 ALTER TABLE 的方式添加外键约束。
例如若mydb数据库中已有两个数据表department和employeesemployees表在创建时未添加外键约束此时就可以通过以下的 ALTER TABLE 方式实现。
ALTER TABLE mydb.employees
ADD CONSTRAINT FK_ID FOREIGN KEY (dept_id) REFERENCES department (id)
ON DELETE RESTRICT ON UPDATE CASCADE;查看外键约束
在添加完外键约束后可以利用 DESC 查看数据表 employees 中添加了外键约束的字段信息。
DESC mydb.employees dept_id;
------------------------------------------------------
| Field | Type | NULL | Key | Default | Extra |
------------------------------------------------------
| dept_id | int(10) unsigned | NO | MUL | NULL | |
------------------------------------------------------添加了外键约束的 dept_id 字段的 Key(索引) 值为MUL表示非唯一性索引(MUL TIPLE KEY)值可以重复。由此可见在创建外键约束时MySQL会自动为没有索引的外键字段创建索引。
在MySQL中使用 SHOW CREATE TABLE 查看 employees 表的详细结构。
SHOW CREATE TABLE mydb.employees\G
*************1.row*************
Table: employees
Create Table: CREATE TABLE employees (id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 员工编号,name varchar(120) NOT NULL COMMENT 员工姓名,dept_id int(10) unsigned NOT NULL COMMENT 商品分类编号,PRIMARY KEY (id),
KEY FK_ID (dept_id),
CONSTRAINT FK_ID FOREIGN KEY (dept_id) REFERENCES department (id) ON UPDATE CASCADE) ENGINEInnoDB DEFAULT CHARSETutf8
1 row in set (0.00 sec)在上述结果中为 dept_id 字段添加外键约束后当 dept_id 没有索引时服务器就会自动为其创建与外键同名的索引。数据表的默认存储引擎为 InnoDB。
由于 ON DELETE RESTRICT 设置的拒绝主表的删除操作属于默认值因此显示表创建语句时省略了此设置。若设置为删除主表记录则从表对应字段设置为NULL即可在表的详细结构中显示到 ON DELETE SETNULL的设置。 关联表操作
实体之间具有一对一、一对多和多对多的联系而具有关联的表中的数据可以通过连接查询的方式获取并且在没有添加外键约束时关联表中的数据插入、更新和删除操作互不影响。但是对于添加了外键约束的关联表而言数据的插入、更新和删除操作就会受到一定的约束。
添加数据
一个具有外键约束的从表在插入数据时外键字段的值会受主表数据的约束保证从表插人的数据必须符合约束规范的要求。如从表外键字段不能插入主表中不存在的数据。
更新数据
对于建立外键约束的关联数据表来说若对主表进行更新操作从表将按照其建立外键约束时设置的ON UPDATE 参数自动执行相应的操作。例如当参数设置为 CASCADE 时如果主表发生更新则从表也会对相应的字段进行更新。
外键约束在使用时既有一定的优势同时又会带来一定的问题具体如下。
✔️优势 外键可节省开发量。 外键能约束数据有效性防止非法数据的插入。
❌劣势。 使用外键约束会带来额外的开销。 主表被锁定时会引发从表也被锁定。 删除主表的数据时需先删除从表的数据。 含有外键约束的从表字段不能修改表结构。
删除数据
对于已建立外键约束的关联数据表来说若要对主表执行删除操作从表将按照其建立外键约束时设置的 ON DELETE 参数自动执行相应的操作。例如当参数设置为 RESTRICT 时如果主表进行删除操作同时从表中的外键字段有关联记录就会阻止主表的删除操作。
关联表在删除操作时体用 DISTRICT 严格模式主表中每条记录的删除都要保证从表中没有相关记录的对应数据这会对开发造成很大的不便。因此对于添加外键约束的 ON DELETE 一般都使用SET NULL模式即删除主表记录时将从表中对应的记录设置为NULL同时要保证从表中对应的外键字段允许为空否则不允许设置该模式。 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;下面以解除员工表 employees 与部门表 department 之间的外键约束为例进行演示。
ALTER TABLE mydb.employees DROP FOREIGN KEY FK_ID;在删除employees表的外键约束后下面利用 DESC 查询 employees 表中删除了外键约束的字段信息。
DESC mydb.employees dept_id;
------------------------------------------------------
| Field | Type | NULL | Key | Default | Extra |
------------------------------------------------------
| dept_id | int(10) unsigned | NO | MUL | NULL | |
------------------------------------------------------从上述执行结果可知在删除 dept_id 的外键约束后dept_id 的 Key 值依然为 MUL这是由于删除外键约束时并不会自动删除系统创建的普通索引此时可以通过 SHOW CREATE TABLE 查看。
SHOW CREATE TABLE mydb.employees\G
*************1.row*************
Table: employees
Create Table: CREATE TABLE employees (id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 员工编号,name varchar(120) NOT NULL COMMENT 员工姓名,dept_id int(10) unsigned NOT NULL COMMENT 商品分类编号,PRIMARY KEY (id),
KEY FK_ID (dept_id),
CONSTRAINT FK_ID FOREIGN KEY (dept_id) REFERENCES department (id) ON UPDATE CASCADE) ENGINEInnoDB DEFAULT CHARSETutf8
1 row in set (0.00 sec)若要在删除外键约束后同时删除系统为外键创建的普通索引则需要通过手动删除索引的方式完成。
ALTER TABLE mydb.employees DROP KEY FK_ID;完成上述操作后可再次利用 DESC 查看已删除的外键字段的 Key 值为空SHOW CREATE TABLE时表中的外键约束以及普通索引全部已删除。 用户与权限概述
在正常的工作环境中为了保证数据库的安全数据库的管理员会对需要操作数据库的人员分配用户名、密码以及可操作的权限范围让其仅能在自己权限范围内操作。
用户是数据库的使用者和管理者MySQL通过用户的设置来控制数据库操作人员的访问与操作范围。
在安装MySQL时系统会自动安装一个名为 mysql 的数据库该数据库主要用于维护数据库的用户以及权限的控制和管理。
MySQL中的所有用户信息都保存在 mysql.user 数据表中。
使用 DESC 即可查看 user 表含有的45个字段下面以表格的形式列举 user 表中的一些常用字段。
字 段 名数 据 类 型默认值Hostchar(60)Userchar(60)Select_privenum(‘N’, ‘Y’)NInsert_privenum(‘N’, ‘Y’)NUpdate_privenum(‘N’, ‘Y’)NDelete_privenum(‘N’, ‘Y’)NCreate_privenum(‘N’, ‘Y’)NDrop_privenum(‘N’, ‘Y’)NReload_privenum(‘N’, ‘Y’)NShutdown_privenum(‘N’, ‘Y’)NProcess_privenum(‘N’, ‘Y’)NFile_privenum(‘N’, ‘Y’)NGrant_privenum(‘N’, ‘Y’)Nssl_tupeenum(‘’, ‘ANY’, ‘X509’, SPECIFIED)ssl_cipherblobNULLx509_issureblobNULLx509_subjectblobNULLmax_questionsint(11) unsigned0max_updatesint(11) unsigned0max_connectionsint(11) unsigned0max_user_connectionsint(11) unsigned0pluginchar(64)mysql_native_passwordauthentication_stringtextNULLpassword_expiredenum(‘N’, ‘Y’)Npassword_last_changedtimestampNULLpassword_lifetimesmallint(5) unsignedNULLaccount_lockedenum(‘N’, ‘Y’)N
在表中根据字段的功能可将其分为6类分别为客户端访问服务器的账号字段、验证用户身份的字段、安全连接的字段、资源限制的字段、权限字段以及账户是否锁定的字段。 账号字段
Host 和 User 字段共同组成的复合主键用于区分 MySQL 中的账户User 字段用于代表用户的名称Host 字段表示允许访问的客户端 IP 地址或主机地址当 Host 的值为 * 时表示所有客户端的用户都可以访问。
下面通过SELECT查询user表中默认用户的Host和User值具体SQL语句及执行结果如下。
SELECT host, user FROM mysql.user
---------------------------
| host | user |
---------------------------
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
---------------------------从上述执行结果可知在 user 表中除了默认的 root 超级用户外在MySQL5.7开始还额外地新增了两个用户 mysql.session 和 mysql.sys。前者用于用户身份验证后者用于系统模式对象的定义防止DBA(数据库管理员)重命名或删除root用户时发生错误。
默认情况下用户 mysql.session 和 mysql.sys 已被锁定使得数据库操作人员无法使用这两个用户通过客户端连接MySQL服务器。建议不要随意解锁和使用 mysql.session 和 mysql.sys 用户。 身份验证字段
在MySQL 5.7中mysql.user 表中已不再包含 Password 字段而是使用 plugin 和authentication_string 字段保存用户身份验证的信息。
其中plugin 字段用于指定用户的验证插件名称authentication_string 字段是根据 plugin 指定的插件算法对账户明文密码加密后的字符串。
下面通过SELECT 查询 user表中 root 用户默认的 plugin 和 authentication_string 值。
SELECT plugin, authentication_string FROM mysql.user WHERE user root
------------------------------------------------------------------
| plugin | authentication_string |
------------------------------------------------------------------
| mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
------------------------------------------------------------------MySQL中 root 用户的默认验证插件名为 mysql_native_password。
authentication_string 字段保存的则是一串不能看出具体含义的值相对于能够直接看懂的明文密码它是经过加密处理的暗码。
除此之外与身份验证的账号密码相关的字段还有 password_expired(密码是否过期)、password_last_changed(密码最后一次修改的时间)以及password_lifetime(密码的有效期)。 安全连接字段
在客户端与 MySQL 服务器连接时除了可以基于账户名以及密码的常规验证外还可以判断当前连接是否符合SSL安全协议与其相关的字段有以下几种。 ssl_type用于保存安全连接的类型它的可选值有(空、ANY(任意类型)、X509(X509证书)、SPECIFIED(规定的)4种。 ssl_cipher用于保存安全加密连接的特定密码。 x509_issuer保存由CA签发的有效的X509证书。 x509_subject保存包含主题的有效的X509证书。
通常标准的发行版MySQL默认未启用SSL加密连接可以通过以下SQL语句查看。
SHOW VARIABLES LIKE have_openssl;
--------------------------
| Variable_name | Value |
--------------------------
| have_openssl | DISABLED |
--------------------------have_openssl 的取值为 DISABLED 表示已禁用加密 资源限制字段
在 mysql.user 表中提供的以 max_ 开头的字段保存对用户可使用的服务器资源的限制用来防止用户登录MySQL服务器后的不法或不合规范的操作浪费服务器的资源各字段的具体含义如下所示。 max_questions保存每小时允许用户执行查询操作的最多次数。 max_updates保存每小时允许用户执行更新操作的最多次数。 max_connections保存每小时允许用户建立连接的最多次数。 max_user_connections保存允许单个用户同时建立连接的最多数量。
以上列举的用户资源限制字段默认值均为0表示对此用户没有任何的资源限制。 权限字段
在 mysql.user 表中提供的以 _priv 结尾的字段一共有29个这些字段保存了用户的全局权限如 Select_priv 查询权限、Insert_priv 插入权限、Update_priv 更新权限等。
其中user 表对应的权限字段的数据类型都是 ENUM 枚举类型取值只有N或Y两种。N表示该用户没有对应权限Y表示该用户有对应权限。为了保证数据库的安全这些字段的默认值都为N如果需要可以对其进行修改。 账户锁定字段
在mysql.user表中提供的 account_locked 字段用于保存当前用户是锁定还是解锁状态。该字段是一个枚举类型当其值为N时表示解锁此用户可以用于连接服务器当其值为Y时表示该用户已被锁定不能用于连接服务器使用。 用户管理 创建用户
由于MySQL中所有用户的信息都保存在 mysql.user 表中因此创建用户可以直接利用 root 用户登录MySQL服务器后向 mysql.user 表中插入记录但是在开发中为保证数据的安全并不推荐使用此方式创建用户。而是采用MySQL提供的 CREATE USER 和 GRANT 语句创建用户。其中GRANT语句在创建用户的同时还可以完成权限的设置。
使用 CREATE USER 语句每创建一个新用户都会在 mysql.user 表中添加一条记录同时服务器会自动修改相应的授权表。但需要注意的是该语句创建的新用户默认情况下没有任何权限需要使用GRANT进行授权。
CREATE USER [IF NOT EXISTS]
账户名 [用户身份验证选项] [, 账户名 [用户身份验证选项]] ...
[WITH 资源控制选项] [密码管理选项 | 账户锁定选项]CREATE USER 可以一次创建多个用户多个用户之间使用逗号分隔。其中账户名是由 用户名主机地址 组成的其余选项在创建用户时若未设置则使用默认值具体如下表所示。
选 项默 认 值用户身份验证选项由 default_authentication_plugin 系统变量定义的插件进行身份验证加密连接协议选项NONE资源控制选项N(表示无限制)密码管理选项PASSWORD EXPIRE DEFAULT用户锁定选项ACCOUNT UNLOCK
在上表中用户身份验证选项的设置仅适用于其前面的用户名可将其理解为某个用户的私有属性其余的选项对声明中的所有用户都有效可以将其理解为全局属性。 创建用户时用户名的设置不能超过32个字符且区分大小写但是主机地址不区分大小写。 创建最简单的用户
在创建用户时若不指定主机地址、密码以及相关的用户选项则表示此用户在访问 MySQL服务器时不限定客户端不需要密码并且没有任何限制。
CREATE USER test1;使用 SELECT查看 mysql 数据库下的 user 表查看创建的用户是否已添加到 user 表中保存。
SELECT host, user FROM mysql.user
---------------------------
| host | user |
---------------------------
| % | text1 |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
---------------------------在上述执行结果中host 的值为 % 时表示当前的用户可以在任何主机中连接 MySQL 服务器当其值为 localhost 时表示当前的用户只能从本地主机连接 MySQL 服务器。另外host 的值为空字符串)同样也可表示匹配所有客户端。 用户名和主机名在设置时若不包含空格、- 等特殊字符则可以省略引号。另外当创建的用户名称为空字符串时表示创建的是一个匿名用户即登录MySQL服务器时不需要输入用户名和密码这种操作会给MySQL服务器带来极大的安全隐患因此不推荐用户创建并使用匿名用户操作MySQL服务器。 创建含有密码的用户
若要在创建用户的同时完成用户密码的设置则可以选取用户身份验证选项的 IDENTIFIED BY 关键字。
CREATE USER test2localhost IDENTIFIED BY 123456;IDENTIFIED BY 后指定的是字符串形式的明文密码。
通过 SELECT 查询存储在 user 表中 test2 用户的密码。
SELECT plugin, authentication_string FROM mysql.user;
------------------------------------------------------------------
| plugin | authentication_string |
------------------------------------------------------------------
| mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
------------------------------------------------------------------创建用户时设置的明文密码 “123456”在 use r表中以默认的算法插性 mysql_native_password 将其转换为暗码。
除此之外在设置用户密码时还可以指定对密码加密的插件只需将 IDENTIFIED BY 明文密码 修改为指定的选项即可具体如下表所示。
选 项描 述IDENTIFIED WITH 验证插件名使用指定的身份验证插件对空凭证未设置用户密码进行加密IDENTIFIED WITH 验证插件名 BY 明文密码利用指定的身份验证插件对明文密码进行加密IDENTIFIED WITH 验证插件名 AS 哈希字符串指定身份验证的插件并存储哈希加密字符串IDENTIFIED BY PASSWORD 哈希字符串身份验证插件为默认并存储哈希加密字符串
下面利用 IDENTIFIED WITH验证插件名 BY ‘明文密码’ 修改以上创建test2用户的SQL语句。
CREATE USER test2localhost
IDENTIFIED WITH mysql_native_password BY 123456;在上述SQL语句中IDENTIFIED WITH关键字指明验证密码的插件名关键字BY后跟明文的密码。通过上述设置明文密码 “123456” 将采用指定的插件算法存储在user表中的对应字段中。
同时创建多个用户
MySQL中创建用户时还可以一次创建多个。
CREATE USER test2localhost IDENTIFIED BY 333333;test3localhost IDENTIFIED BY 444444;在一个CREATE USER 语句中可同时创建多个用户多个用户之间使用逗号分隔并且在创建每个用户时可以单独为其设置密码省略用户身份验证选项时表明此用户在登录服务器时可以免密登录但为了保证数据安全不推荐用户这样做。
设置用户可操作资源范围
在创建用户时可以添加 WITH 直接为用户指定可操作的资源范围如登录的用户在一小时内可以查询数据的次数等具体的参数选项如下表所示。
选 项描 述MAX_QUERIES_PER_HOUR在任何一个小时内允许此用户执行多少次查询MAX_UPDATES_PER_HOUR在任何一个小时内允许此用户执行多少次更新MAX_CONNECTIONS_PER_HOUR在任何一个小时内允许此用户执行多少次服务器连接MAX_USER_CONNECTIONS限制用户同时连接服务器的最大数量
在上表中MAX_USER_CONNECTIONS 选项的值为 0 时服务器将根据 max_userconnections 系统变量的值确定用户的同时连接数若此变量值也为0表示对该用户没有限制。另外MAX_QUERIES_PER_HOUR 选项不会计算从缓存中查询数据的次数。
例如创建一个名为test5的本地用户限制其每小时最多可以更新10次。
CREATE USER test5localhost IDENTIFIED BY 555555WITH MAX_UPDATES_PER_HOUR 10;创建完成后可以查看 user 表中的 max_updates 字段。
SELECT max_updates FROM user WHERE usertest5;
-------------
| max_updates |
-------------
| 10 |
-------------设置有密码期限的用户
在创建用户时不仅可以为用户设置密码还可以为密码设置有效时间具体参数如下表所示。
选 项描 述PASSWORD EXPIRE将密码标记为过期PASSWORD EXPIRE DEFAULT根据 default_password_lifetime 系统变量的指定设置密码的有效性PASSWORD EXPIRE NEVER密码永不过期PASSWORD EXPIRE INTERVAL n DAY将账户密码生存期设置为n天
PASSWORD EXPIRE 选项表示在创建用户时含有此选项的用户在登录后执行任何SQL语句操作前都需要重置用户的密码否则会给出一个 “在执行此语句之前必须使用 ALTER USER 语句重置密码” 的错误提示信息。
在重置用户密码时操作的用户必须要有全局的 CREATE USER 权限或 mysql 数据库的 UPDATE 特权。
下面创建一个名为 test6 的用户并设置其密码每 180 天更改一次。
CREATE USER test6localhost IDENTIFIED BY 666666
PASSWORD EXPIRE INTERVAL 180 DAY;为了确保MySQL客户端用户本身的安全通常情况下推荐每3~6个月变更一次数据库用户密码。
设置用户是否锁定
利用 ACCOUNT 关键字可以为创建的用户设置是否被锁定它有两个可选值分别为 LOCK(锁定)和UNLOCK(解锁)。被锁定的用户不能在客户端登录MySQL服务器。
下面创建一个锁定的用户test 7。具体SQL语句及执行结果如下。
CREATE USER test7localhost IDENTIFIED BY 777777
PASSWORD EXPIRE ACCOUNT LOCK;此时利用 SELECT 查看 mysql.user 表中名为 test7 的 account_ locked 字段若其值为Y则表示当前创建的用户已被锁定。
SELECT account_locked FROM mysql.user WHERE user test7;此时若利用 test7 在客户端登录MySQL服务器会报 “账户被锁定拒绝访问test7localhost” 用户的提示信息。 设置密码
在对MySQL中的用户进行管理时除了创建用户的同时设置密码外还可为没有密码的用户、密码过期的用户或为指定用户修改密码。
# 第1种语法
ALTER USER 账户名 IDENTIFIED BY 明文密码;
# 第2种语法
SET PASSWORD [FOR 账户名] 明文密码;在以上2种语法中ALTER USER是更改密码的首选SQL语句推荐使用。第2种语法可能会被记录到服务器的日志或客户端的历史文件中会有密码泄露的风险因此建议用户尽量少地使用此方式设置密码。
为指定用户设置密码
mysql.user 表中的 test1 用户是一个可在任何主机中连接MySQL服务器的用户但此用户在创建时未设置密码下面将 test1 的密码设置为123456。
ALTER USER test1% IDENTFIED BY 123456;为登录用户设置密码
若当前通过客户端连接 MySQL 服务器的用户是非匿名用户则可以使用 USER() 函数更改自己的密码而无须直接为自己的账户命名。
例如修改当前正在连接 MySQL 服务器的 root 用户密码将其设置为000000。
ALTER USER USER() IDENTFIED BY 000000;USER() 函数获取的是客户端提供的用户和主机地址它可能与当前通过 MySQL 服务器验证的用户与主机名不同此时可以利用 MySQL 提供 CURRENT USER() 函数获取。
SELECT CURRENT_USER();
----------------
| CURRENT_USER |
----------------
| rootlocalhost |
----------------mysqladmin修改用户密码
在MySQL的安装目录 bin 下还有一个 mysqladmin.exe 应用程序它通常用于执行一些管理性的工作以及显示服务器状态等。同时在 MySQL中也可以使用该命令修改用户的密码。
mysqladmin -u 用户名 [-h主机地址] -p password 新密码-u 用于指定待要修改的用户名通常情况下指的是 root 用户-h 用于指定对应的主机地址省略时默认值为 localhost-p 后面的 password 为关键字而不是待修改用户的原密码。
例如在命令行窗口中使用 mysqladmin 命令将 root 用户的密码修改为 123456。
mysqladmin -u root -p password 123456
Enter password: 在上述命令行中“-p password” 后指定的是 root 用户修改后的新密码而 “Enter password 提示输入的密码指的是 root 用户的旧密码只有此密码输入正确才能完成用户密码的修改。
值得一提的是通过 mysqladmin 完成用户密码的设置时会有两个安全警告提示。因此为了确保密码安全应以安全连接方式连接MySQL服务器。在开发中一般不推荐使用此种方式修改用户密码。
SET PASSWORD 与 PASSWORD() 函数
在前面为用户设置密码的语法中可以看出SET PASSWORD 在设置密码时一种是添加PASSWORD() 函数一种是不添加两者的区别在于含有 PASSWORD() 函数的密码根据old_passwords 系统变量值 (0 表示 mysql_native_password 插件2 表示 sha256_password插件对指定的明文密码加密并验证而不含有 PASSWORD() 函数的 SET PASSWORD 语句是将明文密码字符串传递给用户的验证插件并将插件返回的结果保存到mysql.user表的字段中。
root 密码丢失找回
如果忘记MySQL服务的root用户密码就不能通过以上设置密码的方式找回或重置。此时可以在MySQL的配置文件 my.ini 中添加 skip-grant-tables 选项然后重启 MySQL 服务后再次利用 root 用户登录就可以跳过密码的输入直接登录MySQL服务为 root 用户设置密码。但是此种方式存在非常大的安全风险。因此建议要慎重使用。 修改用户
用户创建完成后管理员可以通过 MySQL 提供的专门SQL语句修改用户的密码、身份验证的方式、资源限制、密码的属性以及账户的锁定和解锁的状态。
ALTER USER [IF EXISTS]
账户名 [用户身份验证选项], [账户名 [用户身份验证选项]] ...
[WITH 资源限制选项] [密码管理选项 | 账户锁定选项]ALTER USER 可以同时修改一个或多个用户多个用户之间使用逗号分隔并且语法中选项的可选值与创建用户时的选项完全相同。对于每个修改的用户都会更新其在 mysql.user 表中对应的字段值而未修改的字段仍然保留它原来的值。
修改验证插件
例如将 mysql.user 表中名为 test1 的用户验证插件修改为 sha256_password密码修改为111111并将密码设置为立刻过期。
ALTER USER test1IDENTIFIED WITH sha256_password BY 111111 PASSWORD EXPIRE;解锁用户
解锁 mysql.user 表中被锁定的用户 test7。
ALTER USER testlocalhost ACCOUNT UNLOCK;同时修改多个用户的资源限定
例如修改 mysql. user 表中的 test1 和 test2 用户限定单个用户最多可同时建立两个连接同时将test1 的验证插件修改为MySQL的默认值test2用户的密码修改为 222222。
ALTER USERtest1 IDENTFIED WITH mysql_native_password,test2localhost IDENTFIED BY 222222,WITH max_user_connections 2;为用户重命名
在利用 ALTER USER 修改用户时只能修改指定账户的相关选项如密码、验证插件、资源控制选项等而不能够为用户重新命名此时可以使用 MySQL 专门提供的RENAME USER语句实现。
RENAME USER 旧用户名1 TO 新用户名1 [, 旧用户名2 TO 新用户名2] ...RENAME USER 在为用户重命名时旧用户名与新用户名之间使用 TO 关键字连接同时为多个用户重命名时使用逗号进行分隔。 删除用户
DROP USER [IF EXISTS] 账户名 [, 账户名] ...;在 MySQL5.x 版本之后 DROP USER 语句可以同时删除一个或多个MySQL中的指定用户并会同时从授权表中删除账户对应的权限行。在 MySQL5.x 之前的版本中在删除用户前必须先回收用户的权限。其中账户名与创建用户的格式相同由 用户名主机地址 组成。
下面以删除 mysql.user 中test7用户为例进行演示。
DROP USER IF EXISTS test7;在不添加 IF EXISTS 关键字时若删除了一个不存在的用户则该语句的执行会发生错误在添加后会在删除不存在的用户时生成一个警告作为提示。其中在删除账户时如果省略主机地址则默认为“%”。
当 DROP USER 语句删除当前正在打开的用户时则该用户的会话不会被自动关闭。只有在该用户会话关闭后删除操作才会生效再次登录将会失败。另外利用已删除的用户登录服务器创建的数据库或对象不会因此删除操作而失效。 权限管理
在实际项目开发中为了保证数据的安全数据库管理员需要为不同层级的操作人员分配不同的权限限制登录MySQL服务器的用户只能在其权限范围内操作。同时管理员还可以根据不同的情况为用户增加权限或回收权限从而控制数据操作人员的权限。 授予权限
MySQL中的权限信息根据其作用范围分别存储在mysql数据库中的不同数据表中。当MySQL启动时会自动加载这些权限信息并将这些权限信息读取到内存中。与权限相关的数据表如下表所示。
数 据 表描 述user保存用户被授予的全局权限db保存用户被授予的数据库权限tables_priv保存用户被授予的表权限columns_priv保存用户被授予的列权限procs_priv保存用户被授予的存储过程权限proxies_priv保存用户被授予的代理权限
上表中保存权限的数据表根据权限的操作内容可将权限大致分为数据权限、结构权限以及管理权限(通常只有管理员有管理权限)。
列举MySQL中可以授予和取消的权限如下所示。
数据权限
权 限权 限 级 别描 述SELECT全局、数据库、表、列SELECTUPDATE全局、数据库、表、列UPDATEDELETE全局、数据库、表DELETEINSERT全局、数据库、表、列INSERTSHOW DATABASES全局SHOW DATABASESSHOW VIEW全局、数据库、表SHOW CREATE VIEWPROCESS全局SHOW PROCESSLIST
结构权限
权 限权 限 级 别描 述DROP全局、数据库、表允许删除数据库、表和视图CREATE全局、数据库、表创建数据库、表CREATE ROUTINE全局、数据库创建存储过程CREATE TABLESPACE全局允许创建、修改或删除表空间和日志文件组CREATE TEMPORARY TABLES全局、数据库CREATE TEMPORARY TABLECREATE VIEW全局、数据库、表允许创建或修改视图ALTER全局、数据库、表ALTER TABLEALTER ROUTINE全局、数据库、存储过程允许删除或修改存储过程INDEX全局、数据库、表允许创建或删除索引TRIGGER全局、数据库、表允许触发器的所有操作REFERENCES全局、数据库、表、列允许创建外键
管理权限
权 限权 限 级 别描 述SUPER全局允许使用其他管理操作如CHANGE MASTER TO等CREATE USER全局CREATE USER、DROP USER、 RENAME USER 和 REVOKEALL PRIVILEGESGRANT OPTION全局、数据库、表、存储过程、代理允许授予或删除用户权限RELOAD全局FLUSH操作PROXY与代理的用户权限相同REPLICATION CLIENT全局允许用户访问主服务器或从服务器REPLICATION SLAVE全局允许复制从服务器读取的主服务器二进制日志事件SHUTDOWN全局允许使用mysqladmin_shutdownLOCK TABLES全局、数据库允许在有SELECT表权限上使用 LOCK TABLES
MySQL提供的 GRANT 用于实现用户权限的授予。
GRANT
权限类型 [字段列表] [, 权限类型 [字段列表]] ...
ON [目标类型] 权限级别
TO 账户名 [用户身份验证选项] [, 账户名[用户身份验证选项]] ...
[REQUIRE 连接方式]
[WITH [GRANT OPTION | 资源控制选项}]权限类型指的就是上表中的权限列字段列表用于设置列权限ON 后的目标类型默认为 TABLE表示将全局、数据库、表或列中的某些权限授予给指定的用户。另外其值还可以是 FUNCTION(函数) 或 PROCEDURE(存储过程)。
权限级别用于定义全局权限、数据库权限和表权限添加GRANT OPTION 表示当前账户可以为其他账户进行授权。其余各参数均与 CREATE USER 中的用户选项相同。
下面首先使用 SHOW GRANTS [FOR账户] 查看一下 root 和 test1 用户被授权的情况。
SHOW GRANTS FOR rootlocalhost;
--------------------------------------------------------------------
|Grants for rootlocalhost |
--------------------------------------------------------------------
|GRANT ALL PRIVILEGES ON *.* TO rootlocalhost WITH GRANT OPTION |
|GRANT PROXY ON TO rootlocalhost WITH GRANT OPTION |
--------------------------------------------------------------------SHOW GRANTS FOR test1%;
------------------------------------------
|Grants for test1% |
------------------------------------------
|GRANT ALL PRIVILEGES ON *.* TO test% |
------------------------------------------在上述SQL语句中若在客户端使用的是 root 用户登录MySQL服务器则此处在查看root用户权限时,可以省略 FOR ‘root’localhost’。
在查询结果中 ALL PRIVILEGES 表示除 GRANT OPTION(授权权限) 和 PROXY(代理权限) 外的所有权限USAGE 表示没有任何权限。ON后的 *.* 表示全局级别的权限即MySQL服务器下的所有数据库下的所有表表示任何主机中的匿名用户。
在为用户授予权限时可以分为6个不同的级别具体的实现在于 ON 子句以及权限列表的不同如下表所示。
权 限 级 别实 现 语 法全局权限GRANT 权限列表 ON *.* TO 账户名 [WITH GRANT OPTION];数据库级权限GRANT 权限列表 ON 数据库名.* TO 账户名 [WITH GRANT OPTION];表级权限GRANT 权限列表 ON 数据库名.表名 TO 账户名 [WITH GRANT OPTION];列级权限GRANT 权限类型字段列表[, ...] ON 数据库名.表名 TO 账户名 [WITH GRANT OPTION];存储过程权限GRANT EXECUTE | ALTER ROUTINE | CREATE ROUTINE ON {[*.* | 数据库名.*] | PROCEDURE 数据库名.存储过程} TO 账户名 [WITH GRANT OPTION];代理权限GRANT PROXY ON 账户名 TO 账户名1 [, 账户名2 ] … [WITH GRANT OPTION];
需要注意的是要想使用GRANT语句为用户授权必须要拥有GRANT OPTION 权限且在启用read_only 系统变量时还必须要拥有 SUPER 权限。
下面以授予 test1 用户 shop.sh_goods 表的 SELECT 权限以及对name和price字段的插入权限为例。
GRANT SELECT, INSERT (name, price) ON shop.sh_goods TO test1%;SELECT 权限是表级权限(shop.sh_goods表)INSERT是列级权限(shop.sh_goods表中的name和price字段)。
在SQL语句执行成功后若要查看 test1 的权限可以到 mysql.tables_priv 中查看表权限到mysql.columns_priv 中查看列权限。
SELECT db, table_name, table_priv, column_priv FROM mysql.tables_priv WHERE user test1;
---------------------------------------
|db |table_name |table_priv |column_priv |
---------------------------------------
|shop |sh_goods |Select |Insert |
---------------------------------------SELECT db, table_name, column_priv, column_priv FROM mysql.tables_priv WHERE user test1;
----------------------------------------
|db |table_name |column_priv |column_priv |
----------------------------------------
|shop |sh_goods |name |Insert |
|shop |sh_goods |price |Insert |
----------------------------------------回收权限
在MySQL中为了保证数据库的安全需要将用户不必要的权限回收。
例如数据管理员发现某个用户不应该具有 DELETE 权限就应该及时将其收回。为此MySQL专门提供了一个 REVOKE 语句用于回收指定用户的权限。
REVOKE 权限类型 [(字段列表)] [, 权限类型[(字段列表)]] ...
ON [目标类型] 权限级别 FROM 账户名[, 账户名] ...权限类型、目标类型以及权限级别与授予权限 GRANT 的参数相同。
下面以删除test1用户对 shop.sh_goods 表的 name 和 price 字段的插入权限为例。
REVOKE INSERT (name, price) ON shop.sh_goods FROM test%;当用户的权限比较多并且想要一次性将其全部收回时使用上述语句就会比较麻烦为此可以使用以下的方式回收权限。
# 回收数据、结构、管理所有的权限以及可为其他用户授权的权限
REVOKE ALL [PRIVILEGES], GRANT OPTION FROM 账户名 [, 账户名] ...#回收用户的代理权限
REVOKE PROXY ON 账户名 FROM 账户名1 [, 账户名2] ...“ALL [PRIVILEGES]” 中的 PRIVILEGES 在使用时可以省略它表示除去GRANT OPTION(授予权限)和 PROXY(代理权限)外的所有权限。 刷新权限
刷新权限指的是从系统数据库mysql中的权限表中重新加载用户的权限。原因在于GRANT、CREAT EUSER 等操作会将服务器的缓存信息保存到内存中而 REVOKE、DROP USER 操作并不会同步到内存中因此可能会造成服务器内存的消耗所以在 REVOKE、DROP USER 后推荐读者使用MySQL 提供的 FLUSH PRIVILEGES 重新加载用户的权限。
FLUSH PRIVILEGES;另外刷新权限也可以利用msyqladmin命令完成。
# 方式1
msyqladmin -uroot -p reload#方式2
msyqladmin -uroot -p flush-privileges上述操作命令在执行时首先会提示输入root用户的密码。其中reload 与 flush-privileges 都可以用于重载权限信息。 视图
视图是从一个或多个表中导出来的表它是一种虚拟存在的表表的结构和数据都依赖于基本表。
通过视图不仅可以看到存放在基本表中的数据还可以像操作基本表一样对数据进行查询、添加、修改和删除。
与直接操作基本表相比视图具有如下优点。 简化查询语句。 通过视图可以简化查询语句简化用户的查询操作使查询更加快捷。日常开发中可以将经常使用的查询定义为视图从而避免大量重复的操作。 安全性。 通过视图可以更方便地进行权限控制能够使特定用户只能查询和修改他们所能见到的数据数据库中的其他数据则既看不到也取不到。 逻辑数据独立性。 视图可以屏蔽真实表结构变化带来的影响。例如当其他应用程序查询数据时若直接查询数据表一旦表结构发生改变查询的SQL语句就会发生改变应用程序也必须随之更改。但若为应用程序提供视图修改表结构后只需修改视图对应的SELECT语句就无须更改应用程序。 语法格式
CREATE [OR REPLACE] [ALGORITHM (UNDEFINED | MERGE | TEMPTABLE)]
[DEEINER { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]说明
CREATE表示创建视图的关键字。
OR REPLACE可选表示替换已有视图。
ALGORITHM可选表示视图算法会影响查询语句的解析方式它的取值有如下3个一般情况下使用UNDEFINED即可。 UNDEFINED默认由MySQL自动选择算法。 MERGE将select_statement和查询视图时的SELECT语句合并起来查询。 TEMPTABLE先将select_statement的查询结果存入临时表然后用临时表进行查询。
DEFINER可选表示定义视图的用户与安全控制有关默认为当前用户。
SQL SECURITY可选用于视图的安全控制它的取值有如下两个。 DEFINER(默认)由定义者指定的用户的权限来执行。 INVOKER由调用视图的用户的权限来执行。
view_name表示要创建的视图名称。
column_list可选用于指定视图中的各个列的名称。默认情况下与SELECT语句查询的列相同。
AS表示视图要执行的操作。
select_statement一个完整的查询语句表示从某些表或视图中查出某些满足条件的记录将这些记录导入视图中。
WITH CHECK OPTION可选用于视图数据操作时的检查条件。若省略此子句则不进行检查。它的取值有如下两个。 CASCADED(默认操作数据时要满足所有相关视图和表定义的条件。例如当在一个视图的基础上创建另一个视图时进行级联检查。 LOCAL操作数据时满足该视图本身定义的条件即可。
提示
在默认情况下新创建的视图保存在当前选择的数据库中。若要明确指定在某个数据库中创建视图在创建时应将名称指定为 数据库名.视图名。
在 SHOW TABLES 的查询结果中会包含已经创建的视图。
创建视图要求用户具有CREATE VIEW 权限以及查询涉及的列的 SELECT 权限。如果还有OR REPLACE 子句必须具有视图的 DROP 权限。
在同一个数据库中视图名称和已经存在的表名称不能相同为了区分建议在命名时添加 view_ 前缀或 _view 后缀。
视图创建后MySQL就会在数据库目录中创建一个 视图名.frm 文件。 创建视图
在多表上创建视图
除了在单表上创建视图还可以在两个或者两个以上的基本表上创建视图。下面通过案例演示在sh_goods 和 sh_goods_category 两张表上创建视图。
CREATE VIEW view_goods_cate AS
SELECT g.id, g.name, c.name category_name FROM sh_goods
LEFT JOIN sh_goods_category c
ON g.category_id c.id;当在创建视图时指定的 SELECT 语句涉及多张表的查询时创建的视图就是多表视图。
自定义列名称
通过创建视图的语法格式可知视图的列名称可以自定义。下面通过案例演示在创建视图时自定义列名称具体SQL语句和运行结果如下。
CREATE VIEW view_goods_promo (sn, title, promotion_price) AS
SELECT id, name, price * 0.8 FROM sh_goods;从上述结果可以看出在创建视图时自定义列名称的顺序与 AS 后 SELECT 字段列表的顺序一致即 sn 对应 idtitle 对应 namepromotion_price 对应 price * 0.8。
❗自定义列名称的数量必须与SELECT字段列表的数量一致若不一致MySQL会报错视图将无法创建。
视图安全控制
从创建视图的语法格式可知在创建视图时指定 DEFINER 和 SQL SECURITY 可以控制视图的安全。
下面通过案例演示它们的作用。
# 创建测试用户 shop_test
CREATE USER shop_test;# 创建第1个视图权限控制使用默认值
CREATE VIEW view_goods_t1 AS SELECT id, name FROM sh_goods LIMIT 1;# 创建第2个视图设置 DEFINER 为 shop_test 用户
CREATE DEFINER shop_test VIEW view_goods_t2 AS SELECT id, name FROM sh_goods LIMIT 1;# 创建第3个视图设置 SQL SECURITY 为 INVOKER
CREATE SQL SECURITY INVOKER VIEW view_goods_t3 AS SELECT id, name FROM sh_goods LIMIT 1;# 为shop_test用户赋予前面创建的3个视图的SELECT权限
GRANT SELECT ON view_goods_t1 TO shop_test;
GRANT SELECT ON view_goods_t2 TO shop_test;
GRANT SELECT ON view_goods_t3 TO shop_test;在上述操作创建的3个视图中view_goods_t1 的 DEFINER 为当前用户rootSQL SECURITY为DEFINERview_goods_t2 的 DEFINER 为 shop_test 用户SQL SECURITY 同样也是DEFINERview_goods_t3 的 DEFINER 为当前用户 root但SQL SECURITY 为 INVOKER。 查看视图
查看视图是指查看数据库中已经存在的视图的定义。查看视图必须要有SHOW VIEW的权限。
查看视图的字段信息
MySQL提供的 DESCRIBE(DESC) 视图名 语句不仅可以查看数据表的字段信息还可以查看视图的字段信息。
查看视图状态信息
MySQL提供的 SHOW TABLE STATUS 视图名 \G 语句不仅可以查看数据表的状态信息还可以查看视图的状态信息。
查看视图的创建语句
使用 SHOW CREATE VIEW 或 SHOW CREATE TABLE 视图名 \G 语句可以查看创建视图时的定义语句以及视图的字符编码。 修改视图
修改视图是指修改数据库中存在的视图的定义。当基本表中的某些字段发生变化时视图必须修改才能正常使用。在MySQL中修改视图的方式有两种具体如下。
替换已有的视图
通过 CREATE OR REPLACE VIEW 语句可以在创建视图时替换已有的同名视图如果视图不存在则创建一个视图。
# 创建视图
CREATE VIEW view_goods AS SELECT id, name, price FROM sh_goods;#修改以有视图
CREATE OR REPLACE VIEW view_goods AS SELECT id, name FROM sh_goods;使用 ALTER VIEW 语句修改视图
ALTER [ALGORITHM (UNDEFINED | MERGE | TEMPTABLE)]
[DEEINER { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]ALTER 后面内容的各部分子句与 CREATE VIEW 语句中的子句含义相同。 删除视图
DROP VIEW [IF EXISTS] 视图名1 [,视图名2]...数据操作
视图数据操作就是通过视图来查询、添加、修改、删除基本表中的数据。
视图是一个虚拟表不保存数据当通过视图来操作数据时实际操作的是基本表中的数据
使用 INSERT 语句可以通过视图向基本表中添加数据 。
使用 UPADTE 语句可以通过视图修改基本表中的数据。
使用 DELETE 语句可以通过视图删除基本表中的数据。
这些语句使用方法与前面讲解添加数据的方式一样只需将数据表名换成视图表名即可。
视图检查条件
在创建视图的语法格式中WITH CHECK OPTION 子句用于在视图数据操作时进行条件检查。
# 创建第一个视图
CREATE VIEW view_cate_t1 AS SELECT id, name, FROM sh_goods_category WHERE id 30;# 创建第二个视图使用CASCADED(级联)检查
CREATE VIEW view_cate_t2 AS SELECT id, name, FROM view_cate_t1 WHERE id 20
WITH CHECK OPTION; #相当于 WITH CASCADED CHECK OPTION# 插入数据id必须大于20小于30才可以插入成功# 创建第三个视图使用LOCAL(本视图非级联)检查
CREATE VIEW view_cate_t3 AS SELECT id, name, FROM view_cate_t1 WHERE id 20
WITH LOCAL CHECK OPTION; # 插入数据只需满足id大于20即可插入事务
在MySQL中事务就是针对数据库的一组操作它可以由一条或多条SQL语句组成且每个SQL语句是相互依赖的。只要在程序执行过程中有一条SQL语句执行失败或发生错误则其他语句都不会执行。也就是说事务的执行要么成功要么就返回到事务开始前的状态这就保证了同一事务操作的同步性和数据的完整性。
MySQL中的事务必须满足A、C、I、D这4个基本特性具体如下。 原子性 原子性(Atomicity)是指一个事务必须被视为一个不可分割的最小工作单元只有事务中所有的数据库操作都执行成功才算整个事务执行成功。 事务中如果有任何一个SQL语句执行失败已经执行成功的SQL语句也必须撤销数据库的状态退回到执行事务前的状态。 一致性 一致性(Consistency)是指在事务处理时无论执行成功还是失败都要保证数据库系统处于一致的状态保证数据库系统不会返回到一个未处理的事务中。 MySQL中的一致性主要由日志机制实现通过日志记录数据库的所有变化为事务恢复提供了跟踪记录。 隔离性 隔离性Isolation)是指当一个事务在执行时不会受到其他事务的影响。保证了未完成事务的所有操作与数据库系统的隔离直到事务完成为止才能看到事务的执行结果。 隔离性相关的技术有并发控制、可串行化、锁等。当多个用户并发访问数据库时数据库为每一个用户开启的事务不能被其他事务的操作数据所干扰多个并发事务之间要相互隔离。 持久性 持久性(Durability)是指事务一旦提交其对数据库的修改就是永久性的。需要注意的是事务的持久性不能做到百分百的持久只能从事务本身的角度来保证永久性而一些外部原因导致数据库发生故障如硬盘损坏那么所有提交的数据可能都会丢失。 基本操作
在默认情况下用户执行的每一条SQL语句都会被当成单独的事务自动提交。如果要将一组SQL语句作为一个事务则需要先执行以下语句显式地开启一个事务。
START TRANSACTION;上述语句执行后每一条SQL语句不再自动提交用户需要使用以下语句手动提交只有事务提交后其中的操作才会生效。
COMMIT;如果不想提交当前事务可以使用如下语句取消事务即回滚。
ROLLBACK;需要注意的是ROLLBACK 只能针对未提交的事务回滚已提交的事务无法回滚。当执行COMMIT或ROLLBACK后当前事务就会自动结束。
MySQL中的事务不允许嵌套若在执行 START TRANSACTION 语句前上一个事务还未提交会隐式地执行提交操作。
事务处理主要是针对数据表中数据的处理不包括创建或删除数据库、数据表修改表结构等操作而且执行这类操作时会隐式地提交事务。 事务的自动提交
MySQL默认是自动提交模式如果没有显式开启事务START TRANSACTION每一条SQL语句都会自动提交COMMIT。如果用户想要控制事务的自动提交方式可以通过更改AUTOCOMMIT变量来实现将其值设为1表示开启自动提交设为0表示关闭自动提交。若要查看当前会话的AUTOCOMMIT值使用如下语句。 SELECT autocommit;若要关闭当前会话的事务自动提交可以使用如下语句。
SET AUTOCOMMIT 0;事务的保存点
在回滚事务时事务内所有的操作都将撤销。而若希望只撤销一部分可以用保存点来实现。使用以下语句可以在事务中设置一个保存点。
SAVEPOINT 保存点名;在设置保存点后使用以下语句可以将事务回滚到指定保存点。
ROLLBACK TO SAVEPOINT 保存点名;使用时若不再需要一个保存点可以使用如下语句删除保存点。
RELEASE SAVEPOINT 保存点名;一个事务中可以创建多个保存点在提交事务后事务中的保存点就会被删除。另外在回滚到某个保存点后在该保存点之后创建过的保存点也会消失。 控制事务结束后的行为
事务的提交COMMIT和回滚ROLLBACK还有一些可选子句如下所示。
COMMIT [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [AND [NO] CHAIN] [[NO] RELEASE]
AND CHAIN 用于在当前事务结束时立即启动一个新事务并且新事务与刚结束的事务有相同的隔离级
RELEASE用于在终止当前事务后让服务器断开与客户端的连接。若添加NO则表示抑制 CHAIN 和 RELEASE 完成。 事务隔离级别
由于数据库是一个多用户的共享资源MySQL允许多线程并发访问因此用户可以通过不同的线程执行不同的事务。为了保证这些事务之间不受影响对事务设置隔离级是十分必要的。
查看隔离级别
# 查看全局隔离级
SELECT global.transaction_isolation;
# 查看当前会话中的隔离级
SELECT session.transaction_isolation;
# 查看下一个事务的隔离级
SELECT transaction_isolation;在以上语句中全局的隔离级影响的是所有连接MySQL的用户而当前会话的隔离级别只影响当前正在登录MySQL服务器的用户不会影响其他用户。而下一个事务的隔离级别仅对当前用户的下一个事务操作有影响。
在默认情况下上述3种方式返回的结果都是REPEATABLE-READ表示隔离级别为可重复读。
修改隔离级别
在MySQL中事务的隔离级别可以通过SET语句进行设置具体语法如下。
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL 参数值SET 后的 SESSION 表示当前会话GLOBAL表示全局若省略表示设置下一个事务的隔离级。
TRANSACTION 表示事务ISOLATION 表示隔离LEVEL 表示级别。参数值可以是 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 或 SERIALIZABLE 中的一种。
只读事务
默认情况下事务的访问模式为 READ WRITE(读/写模式)表示事务可以执行读(查询) 或写(更改、插入、删除等)操作。若开发需要可以将事务的访问模式设置为 READ ONLY(只读模式)禁止对表进行更改。
# 设置只读事务
SET [SESSION | GLOBAL] TRANSACTION READ ONLY
# 恢复成读写事务
SET [SESSION | GLOBAL] TRANSACTION READ WRITE 四种隔离级别
读取未提交
READ UNCOMMITTED 是事务中最低的级别在该级别下的事务可以读取到其他事务中未提交的数据这种读取的方式也被称为脏读Dirty Read。简而言之脏读是指一个事务读取了另外一个事务未提交的数据。
例如Alex 要给 Bill 转账100元购买商品Alex开启事务后转账但不提交事务通知Bill来查询如果Bill的隔离级别较低就会读取到Alex的事务中未提交的数据发现Alex确实给自己转了100元就给Alex发货。等Bill发货成功后Alex将事务回滚Bill就会受到损失这就是脏读造成的。
读取提交
READ COMMITTED 是大多数 DBMS (如 SQL Server、Oracle) 的默认隔离级但不包括MySQL。
在该隔离级下只能读取其他事务已经提交的数据避免了脏读数据的现象。但是在该隔离级别下会出现不可重复读NON-REPEATABLE READ的问题。
不可重复读是指在一个事务中多次查询的结果不一致原因是查询的过程中数据发生了改变。
例如在网站后台统计所有用户的总金额第1次查询Alex有900元为了验证查询结果第2次查询Alex有800元两次查询结果不同原因是第2次查询前Alex取出了100元。
可重复读
REPEATABLE READ 是MySQL的默认事务隔离级它解决了脏读和不可重复读的问题确保了同一事务的多个实例在并发读取数据时会看到同样的结果。但在理论上该隔离级会出现幻读PHANTOM READ)的现象。
幻读又被称为虚读是指在一个事务内两次查询中数据条数不一致幻读和不可重复读有些类似同样发生在两次查询过程中。不同的是幻读是由于其他事务做了插入记录的操作导致记录数有所增加。不过MySQL的InnoDB存储引擎通过多版本并发控制机制解决了幻读的问题。
例如在网站后台统计所有用户的总金额时当前只有两个用户总金额为2000元若在此时新增一个用户并且存入1000元再次统计时发现总金额变为3000元造成了幻读的情况。
可串行化
SERIALIZABLE 是最高级别的隔离级它在每个读的数据行上加锁使之不会发生冲突从而解决了脏读、不可重复读和幻读的问题。但是由于加锁可能导致超时Timeout) 和 锁竞争Lock Contention)现象因此 SERIALIZABLE 也是性能最低的一种隔离级。除非为了数据的稳定性需要强制减少并发的情况时才会选择此种隔离级。 数据库编程引擎索引锁分区等内容有时间在更新。。。
