【05 - MySQL数据库服务语句应用】

MySQL数据库服务语句应用

一、数据库服务语句应用

1、SQL语句概述

SQL，英文全称为Structured Query Language，中文意思是结构化查询语言（属于编程语言）；
它是一种对关系数据库中的数据进行定义和操作的语言，是大多数关系数据库管理系统所支持的工业标准语言。
在使用SQL语句时，也会用到几种常用的标准：SQL 89 /SQL 92 /SQL 99 /SQL 03

2、SQL语句分类

DDL语句

DDL Data Definition Language（数据定义语言）
概念介绍：
负责管理数据库的基础数据（不会对表的内容修改），比如增删库、增删表、增删索引、增删用户等；

涉及语句：
CREATE（创建）、ALTER（修改）、DROP（删除）等；

相关具体的DDL负责的操作行为，可以执行以下命令进行查看：
mysql> ? Data Definition;
# 查看获取DDL语言的操作行为

DCL语句

DCL Data Control Language（数据控制语言）
概念介绍：
主要用来定义访问权限和安全级别

涉及语句：
GRANT（用户授权）、REVOKE（权限回收）、COMMIT（提交）、ROLLBACK（回滚）

相关具体的DCL负责的操作行为，可以执行以下命令进行查看：
mysql> ? Account Management;
# 查看获取DCL语言的操作行为

DML语句

DML Data Manipulation Language（数据操作语言）
概念介绍：
主要针对数据库里的表里的数据进行操作，用来定义数据库记录（数据）；

涉及语句：
SELECT（查）、INSERT（增）、DELETE（删）、UPDATE（改）

相关具体的DML负责的操作行为，可以执行以下命令进行查看：
mysql> ? Data Manipulation;
# 查看获取DML语言的操作行为

DQL语句

DQL Data Query Language（数据查询语言）
概念介绍：
主要用来查询记录（数据）

涉及语句：
SELECT（查）

基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块

二、数据库字符编码设置

1、字符编码概述

在使用数据库服务时，有时在数据库表中输入中文信息条目时，默认经常会遇到字符乱码问题，输入中文信息为什么会出现乱码：

系统字符编码概念解释：

计算机，不能直接存储⽂字，存储的是编码。计算机只能处理⼆进制的数据。a -
对于其它数据，⽐如： 0-9、 a-z、 A-Z，这些字符，我们可以定义⼀套规则来表示。
假如： A-110表示， B-111表示等。

ASCII码：00000000 = 0-255 00000001 a 00000010 b
美国发布的，⽤1个字节(8位⼆进制)来表示⼀个字符，共可以表示2^8=256个字符。
美国的国家语⾔是英语，只要能表示0-9、 a-z、 A-Z、特殊符号。

ANSI编码：
每个国家为了显示本国的语⾔，都对ASCII码进⾏了扩展。
⽤2个字节(16位⼆进制)来表示⼀个汉字，共可以表示2^16＝ 65536个汉字。

中国的ANSI编码是GB2312编码(简体)，对6763汉字进⾏编码，含600多特殊字符。另外还有GBK(简体)。
⽇本的ANSI编码是JIS编码。
台湾的ANSI编码是BIG5编码（繁体）

GBK：
对GB2312进⾏了扩展，⽤来显示罕⻅的、古汉语的汉字。现在已经收录了2.1万左右。并提供了1890个汉字码 位。 K的含义就是“扩展”。

Unicode编码(统⼀编码)： 4个字节 a - 4字节 4k
⽤4个字节(32位⼆进制)来表示⼀个字符，想法不错，但效率太低。例如，字⺟A⽤ASCII表示的话⼀个字节就够， 可⽤Unicode编码的话，得⽤4个字节表示，造成了空间的极⼤浪费。

 A的Unicode编码是：0000 0000 0000 00000000 0000 0100 0000
UTF-8(Unicode Transform Format)编码： 你 11111100000 好 00000011111
根据字符的不同，选择其编码的⻓度。⽐如：⼀个字符A⽤1个字节表示，⼀个汉字⽤2个字节表示。 毫⽆疑问，开发中，都⽤UTF-8编码吧，准没错。

中⽂能够使⽤的字符集两种： 第⼀种： UTF-8。 UTF-8是国际通⽤字库，⾥⾯涵盖了所有地球上所有⼈类的语⾔⽂字，⽐如阿拉伯⽂、汉 语、⻦语…… 第⼆种： GBK（对GB2312进⾏了扩展）。 gb2312 是国标，是中国的字库，⾥⾯仅涵盖了汉字和⼀些常⽤外 ⽂，⽐如⽇⽂⽚假名，和常⻅的符号。 字库规模： UTF-8（字很全） > gb2312（只有汉字）

2、数据库服务字符编码设置

1.查看数据库默认字符编码信息

查看数据库服务可以设置的字符集
mysql > show charset;
一般数据库服务中，常规使用的字符集编码为utf8、utf8mb4 

查看数据库服务默认字符编码
mysql > show variables like "%character%";

默认情况下，数据库存储数据与数据库服务端识别的字符编码均为：utf8mb4编码。

说明：数据库服务最新8.0版本的字符集信息为utf8mb4，早期数据库服务版本字符集编码为latin1；

特殊知识点说明：在数据库服务应用中 utf8和utf8mb4之间有什么区别？

区别说明	解释说明
两种字符集的字符存储量不同
utf8	最多存储3字节长度字符 张-3字节
utf8mb4	最多存储4字节长度字符（表情字符emoji） 张-4字节

说明：企业生产环境中，建议客户端与服务端字符集要统一;

模拟存储中文数据信息数据库中，查看中文字符的显示情况：

# 创建新的数据库信息
mysql > create database fxx;
Query OK, 1 row affected (0.00 sec)

# 常看新的数据库编码
mysql> show create database fxx;
+----------+---------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                   |
+----------+---------------------------------------------------------------------------------------------------+
| fxx      | CREATE DATABASE `fxx` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 创建新的数据库表信息
mysql > use fxx;
Database changed
mysql > create table a(name char(10));
Query OK, 0 rows affected (0.01 sec)

# 向数据库表中插入数据
mysql > insert into a values("风潇潇");
Query OK, 1 row affected, 1 warning (0.00 sec)

# 查看数据库中表的信息
mysql > select * from a;
+--------+
| name |
+--------+
| ???     |
+--------+
1 row in set (0.00 sec)
 

2.修改数据库服务字符编码

全局编辑修改服务配置文件参数信息
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
-- 设置服务端字符集编码为utf8mb4


局部调整字符编码（库 表）
1、创建数据库时单独设置字符集，默认后续库中创建的表也是这个字符集，存在库表的继承关系
mysql > create database fxx charset utf8；
# 查看创建的数据库字符集信息
mysql> show create database xiaoQ;

2、数据库服务建表时单独设置字符集：
# 创建数据库中表时单独设置字符集
mysql> use fxx;
mysql> create table t1 (id int) charset gbk;

# 查看创建的数据表字符集信息
mysql> show create table t1;

show variables like '%char%';
在设置字符编码信息时，各个字符编码参数最好统一

3.数据库服务字符编码信息调整方法

# 假设数据库表原有字符集为gbk，并且已经存储数据了，需要将表和数据字符集进行调整转换utf8mb4
# 方法一：
mysql > alter table t1 charset utf8mb4;   
-- 不严谨的方法，只会影响之后存储的数据，不会修改之前存储的数据

# 方法二：
·锁表逻辑导出数据（例如：mysqldump）
·重新创建数据空表（设置目标字符集）
·导入备份数据信息
-- 严谨的方法，可以影响之后存储的数据，也会修改之前存储的数据
-- 字符集转换是可以的，但是必须保证修改后的字符集是修改前的严格超集（包含）

3、数据库中字符设置参数信息说明

序号	参数信息	解释说明
01	character_set_client	用来设置客户端使用的字符集
02	character_set_connection	用来设置连接数据库时的字符集 如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置。
03	character_set_database	用来设置默认创建数据库的编码格式 如果在创建数据库时没有设置编码格式，就按照这个格式设置。
04	character_set_filesystem	文件系统的编码格式，把操作系统上的文件名转化成此字符集 即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换
05	character_set_results	数据库给客户端返回时使用的编码格式，如果没有指明，使用服务器默认的编码格式。
06	character_set_server	服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为定义。
07	character_set_system	数据库系统使用的编码格式，这个值一直是utf8 不需要设置，它是为存储系统元数据的编码格式。
08	character_sets_dir	这个变量是字符集安装的目录。

4、字符编码校对规则（排序规则）

排序规则，就是指字符比较时按照字符编码还是直接用二进制数据比较，以及是否区分大小写。
主要可以根据校对规则定义或设置的不同：在查询数据信息时，影响数据信息的查询输出和排序效果；
其中utf8mb4字符集中，常用的排序规则有utf8mb4_unicode_ci、utf8mb4_general_ci、utf8mb4_bin：

排序规则前缀是字符集编码，中间是排序规则名称，后缀有特殊意义如下（常用的）：

排序规则后缀	解释说明
_ci	不区分大小写，Case-insensitive的缩写
_cs	区分大小写，Case-sensitive的缩写
_ai	不区分重音，Accent-insensitive的缩写
_as	区分重音，Accent-sensitive的缩写
_bin	采用二进制方式存储数据信息

utf8mb4_unicode_ci是基于标准Unicode来排序和比较，能够在各种语言之间精确排序。且在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。但是在绝大多数情况下不会发生此类复杂比较。

utf8mb4_general_ci没有实现Unicode排序规则，在遇到某些特殊字符情况下，排序结果可能不一致。但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。

utf8mb4_bin将字符串的每个字符用二进制数据编译存储，区分大小写，而且可以存二进制的内容。

综合来说，utf8mb4_unicode_ci比较准确，utf8mb4_general_ci速度较快。utf8mb4_unicode_ci对于特殊字符的处理，在中文、英文应用中不会使用到，除非你的应用有德语、法语、俄语等，则需要使用utf8mb4_unicode_ci，否则一般选用utf8mb4_general_ci就可以了。

对库/表设置校对规则

对库设置校对规则
   create database fxx charset utf8 collate utf8_general_mysql500_ci;
对表设置校对规则
   create table t1 (id int) charset utf8 collate utf8_german2_ci;
   
   演示校对规则不同的效果：
   create table t1(info char(3)) charset utf8mb4 collate utf8mb4_0900_ai_ci;
   create table t2(info char(3)) charset utf8mb4 collate utf8mb4_0900_as_cs;
   create table t3(info char(3)) charset utf8mb4 collate utf8mb4_bin;

   insert into t1 values('a'),('A'),('b'),('B'),('c'),('C');
   insert into t2 values('a'),('A'),('b'),('B'),('c'),('C');
   insert into t3 values('a'),('A'),('b'),('B'),('c'),('C');
   
   校对规则会影响查询信息结果：
   mysql> select * from t1 where info='a';
   +------+
   | info |
   +------+
   | a    |
   | A    |
   +------+
   2 rows in set (0.00 sec)
   
   mysql> select * from t2 where info='a';
   +------+
   | info |
   +------+
   | a    |
   +------+
   1 row in set (0.00 sec)
   
   mysql> select * from t3 where info='a';
   +------+
   | info |
   +------+
   | a    |
   +------+
   1 row in set (0.00 sec)
 
   校对规则会影响数据排序效果（排序规则）
   mysql> select * from t1 order by info;
   +------+
   | info |
   +------+
   | a    |
   | A    |
   | b    |
   | B    |
   | c    |
   | C    |
   +------+
   6 rows in set (0.00 sec)
   
   mysql> select * from t2 order by info;  # 基于info排序
   +------+
   | info |
   +------+
   | a    |
   | A    |
   | b    |
   | B    |
   | c    |
   | C    |
   +------+
   6 rows in set (0.00 sec)
   
   mysql> select * from t3 order by info;
   +------+
   | info |
   +------+
   | A    |
   | B    |
   | C    |
   | a    |
   | b    |
   | c    |
   +------+
   6 rows in set (0.00 sec)

三、数据库数据类型介绍

tinyint(m)   1个字节 范围(-128~127)
int(m)		4个字节 范围(-2147483648~2147483647)
bigint(m)    8个字节 范围(+-9.22*10的18次方)
   
浮点类型：
float(m,d)   单精度浮点型 8位精度(4字节) m总个数，d小数位
   
字符串类型：
char(n)	固定长度，最多255个字符
varchar(n)	固定长度，最多65535个字符
longtext	可变长度，最多2的32次方-1个字符  存储评论信息
    
特殊数据类型：
enum    枚举类型
https://m.php.cn/article/460317.html

数据类型从数据存储底层机制来看，主要和内存中如何存储数据信息有关；

在数据库服务中，每一个常量、变量和参数都有数据类型，数据类型用来指定数据的存储格式、约束和有效范围

1、数据类型表

类型	类型细化	说明
数字类型
整型（数字/整数）	int	普通整型数字
	tinyint	微小整型数字
	bigint	超大整型数字
浮点（数字/小数）	float	单精度浮点数
	double	双精度浮点数
	decimal	定点数
字符串类型
字符（字符/符号/整数）	char（8）	定长字符类型
	varchar（8）	变长字符类型
	enum	枚举类型
	set	集合类型
	text	大文本类型
时间类型
	date	日期类型
	time	时间类型
	datetime	日期时间类型（1000~9999）占8字节
	timestamp	时间戳类型（1970~2038）格林威治时间 占4字节
二进制类型
json类型

2、数据类型区别

类别	数据类型细分	差异区别
整数类型	tinyint	占用1字节 有符号取值 -128~127 无符号取值 0 ~ 255（最大3位数）
	int	占用4字节 有符号取值 -2147483648 ~ 2147483647 无符号取值 0 ~ 4294967295（最大10位数）
	BIGINT	占用8字节 … 0~2^64-1（最大20位数）

说明：9位数是亿，10位数是十亿，13位数是万亿，14位数是兆，19位数是万兆，20位数是京；

3、字符类型区别

类别	数据类型细分	差异区别
字符类型	char(n)	表示定长的字符串类型，n表示可以存储字符的字节上限（n取值 0~255）
	varchar(n)	表示变长的字符串类型，n表示可以存储字符的字节上限（n取值 0~65535）

详细的数据类型知识参考链接：https://m.php.cn/article/460317.html

4、数据类型约束与属性

在数据库服务中进行数据存储时，类似于在一个execl表中存储数据一样，如果没有对表的字段进行约束和限制，是可以随意存储数据的；

但是，在数据表的某些字段上，是有特殊含义的，如果随意进行存储数据，会造成存储信息的混乱，因此引入了约束与属性概念；

通过数据类型设置的约束与属性，可以让数据库服务限制人类录入的数据信息，从而避免录入数据信息混乱的局面；

并且，通过数据类型的约束与属性设置，还可以避免数据信息输入的重复与输入数据信息不能为空；

常见的约束定义

序号	约束方法	解释说明
01	PK（primary key）	表示主键约束，非空且唯一（表中只能有一个列为主键）
02	UK（unique key）	表示唯一约束 （可以为空，非空时必须为唯一）
03	NN（not null）	表示非空约束 （可重复 但必须非空）
04	FK（foreign key）	表示外键约束，多表之间关联使用

常见的属性定义

序号	属性信息	解释说明
01	default	设定默认数据信息，可以实现自动填充
02	auto_increment	设定数值信息自增，可以实现数值编号自增填充（一般配合主键使用）
02	comment	设定数据注释信息
03	unsigned	设定数值信息非负，可以实现数值信息列不能出现负数信息

外键应用说明

数据库外键约束说明--FK 
   外键也称之为外键约束: foreign key
   外键: 外面的键, 一张表的一个字段(非主键)指向另外一个表的主键, 那么该字段就称之为外键.</u>
   外键所在的表称之为子表(附表); 外键所指向的主键所在的表称之为父表(主表)

设置添加外键

实现将一个表的字段与另外一张表的主键进行关联(实体与实体之间的联系)，具体增加外键有两种形式：

方式一：在创建表的时候就增加外键: 在表字段之后使用foreign key

# 创建外键语法格式
foreign key(外键字段) references 主表(主键);

# 创建外键关联的父表
create table class(
id int primary key auto_increment,
name varchar(10) not null comment "班级名字，不能为空",
room varchar(10) comment '教室：允许为空'
) charset utf8;

# 创建子表使用外键
create table student(
id int primary key auto_increment,
number char(10) not null unique comment "学号：不能重复",
name varchar(10) not null comment "姓名",
c_id int,
foreign key(c_id) references class(id) 
) charset utf8;
-- 增加外键：c_id是外键字段，class是引用表（父表），id是引用字段（主键）

方式二：在创建表之后增加外键: 指定外键名字

# 创建外键语法格式
alter table 表名 add constraint 外键名 foreign key(外键字段) references 父表(主键字段)

# 创建没有外键信息的表
create table t_foreign(
id int primary key auto_increment,
c_id int
)charset utf8;

此时表中（t_foreign）没有创建外键：

# 在没有外键的表中添加外键
alter table t_foreign add constraint class_foreign foreign key(c_id) references class(id);
此时表中（t_foreign）显示了外键信息：

外键增加条件: 外键字段必须与引用表(父表主键)的数据类型严格保持一致 

设置删除外键

外键不能被修改，只能先删除后再新增；

# 删除外键语法格式
alter table 表名 drop foreign key 外键名;

# 删除表（t_foreign）中外键信息
alter table t_foreign drop foreign key class_foreign;

删除外键只能删除外键部分，不能删除外键创建的索引部分,所以desc t_foreign;查看不到，需要使用show create table t_foreign;

外键作用说明

外键也称之为外键约束，主要作用在于对数据进行约束：

约束01：外键对子表的数据写操作约束（增加和更新）

如果子表中插入的数据所对应的外键在父表不存在，创建不能成功.

mysql> select * from class;
Empty set (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student values(null,'2023110001','xiaoQ',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`oldboy`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`))

约束02：外键对父表也有数据约束

当父表操作一个记录，但是该记录被子表所引用的时候，那么父表的操作将会被限制(更新: 主键和删除)

mysql> insert into class values (1,'Linux80','001'),(2,'Linux81','002');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into student values(null,'2023110001','xiaoQ',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------------+-------+------+
| id | number     | name  | c_id |
+----+------------+-------+------+
|  2 | 2023110001 | xiaoQ |    1 |
+----+------------+-------+------+
1 row in set (0.00 sec)

mysql> select * from class;
+----+---------+------+
| id | name    | room |
+----+---------+------+
|  1 | Linux80 | 001  |
|  2 | Linux81 | 002  |
+----+---------+------+
2 rows in set (0.00 sec)

mysql> delete from class where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`oldboy`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`))
-- 删除父表数据，数据被字表所引用，所以不能更新或者删除父行记录

四、数据库数据模式概念

SQL_mode

在数据库服务应用过程中存在SQL_mode概念（SQL模式），规范SQL执行行为和数据的准确性，能够符合数据录入常识和执行结果意义

例如：日期信息不能出现 0000-00-00 信息，月份只能是1-12，日期只能是1-31，一旦违反常识便会报错；

例如：在进行数据运算时，除法运算时，除数不能为0；

例如：当定义数据类型为char(10)，不能超过字符长度，超过长度就报错；

例如：设置only_full_group_by（5.7以后的特性），禁止进行分组查询时，出现聚合信息1对多的显示输出；

获取SQLmode设置的默认信息：

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SQL_mode配置参数信息解释说明

序号	模式参数配置	解释说明
01	ONLY_FULL_GROUP_BY	对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。
02	STRICT_TRANS_TABLES	在该模式下，如果一个值不能插入到一个事物表中，则中断当前的操作，对非事物表不做限制
03	NO_ZERO_IN_DATE	在严格模式下，不允许日期和月份为零
04	NO_ZERO_DATE	设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
05	ERROR_FOR_DIVISION_BY_ZERO	在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL
06	NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
07	NO_AUTO_VALUE_ON_ZERO	该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，该列又是自增长的，那么这个选项就有用了。
08	NO_AUTO_CREATE_USER	禁止GRANT创建密码为空的用户
09	PIPES_AS_CONCAT	将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
10	ANSI_QUOTES	启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

注意：当进行数据库服务版本升级时，可能之前版本数据信息不能满足新版本数据库服务的SQL_mode信息设定，需要暂时设置SQLmode为空

mysql> set global sql_mode='';
-- 配置完毕后，可以重新登录数据库服务进行检查确认

SQL_MODE ：完善数据录入的合理性；
  作用：在数据迁移时（低版本迁移到高版本），需要关闭SQL_mode
  如何关闭：
  set global sql_mode='';
  临时操作