Day03-数据库服务管理语句
课后作业：
1、实现数据库服务多实例被systemd管理
[root@db01 ~]# cd /etc/systemd/system/
[root@db01 system]# ll
总用量 12
drwxr-xr-x. 2 root root 32 2024-02-18 16:33 getty.target.wants
drwxr-xr-x. 2 root root 171 2024-07-26 11:25 multi-user.target.wants
-rw-r--r-- 1 root root 330 2024-07-26 11:45 mysqld56.service
-rw-r--r-- 1 root root 330 2024-07-26 11:49 mysqld57.service
-rw-r--r-- 1 root root 328 2024-07-29 19:54 mysqld.service
[root@db01 system]# cp mysqld.service mysqld3307.service
[root@db01 system]# cp mysqld.service mysqld3308.service
[root@db01 system]# cp mysqld.service mysqld3309.service
[root@db01 system]# vim mysqld3307.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/data/my.cnf
LimitNOFILE = 5000
[root@db01 ~]# ll /data/3307/data/my.cnf
-rw-r--r-- 1 root root 141 2024-07-29 16:21 /data/3307/data/my.cnf
# 同理3308和3309的配置文件也这么编写
[root@db01 system]# vim mysqld3308.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/data/my.cnf
LimitNOFILE = 5000
[root@db01 system]# vim mysqld3309.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/data/my.cnf
LimitNOFILE = 5000
[root@db01 system]# systemctl daemon-reload
[root@db01 system]# systemctl start mysqld3307.service
[root@db01 system]# systemctl start mysqld3308.service
[root@db01 system]# systemctl start mysqld3309.service
[root@db01 system]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 151 [::]:3307 [::]:*
LISTEN 0 151 [::]:3308 [::]:*
LISTEN 0 151 [::]:3309 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 70 [::]:33060 [::]:*
[root@db01 system]# ps -ef|grep mysql
mysql 1359 1 2 13:42 ? 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/3307/data/my.cnf
mysql 1412 1 11 13:42 ? 00:00:03 /usr/local/mysql/bin/mysqld --defaults-file=/data/3308/data/my.cnf
mysql 1463 1 12 13:42 ? 00:00:03 /usr/local/mysql/bin/mysqld --defaults-file=/data/3309/data/my.cnf
root 1517 1174 0 13:42 pts/0 00:00:00 grep --color=auto mysql
[root@db01 system]# netstat -lntup |grep mysql
tcp6 0 0 :::3307 :::* LISTEN 1359/mysqld
tcp6 0 0 :::3308 :::* LISTEN 1412/mysqld
tcp6 0 0 :::3309 :::* LISTEN 1463/mysqld
tcp6 0 0 :::33060 :::* LISTEN 1359/mysqld
# PS：手速别太快
2、部署数据库服务多个版本服务实例：5.6.40 5.7.20 8.0.26
# 第一个历程：安装多个版本程序
省略。。。。。
# 第二个历程：进行初始化操作
[root@db01 ~]# rm -rf /data/3356/data/*
[root@db01 ~]# rm -rf /data/3357/data/*
[root@db01 ~]# rm -rf /data/3306/data/*
[root@db01 ~]# /usr/local/mysql56/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql56/ --datadir=/data/3356/data/
[root@db01 ~]# /usr/local/mysql57/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57/ --datadir=/data/3357/data/
[root@db01 ~]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/3306/data/
# 第三个历程：编写多实例配置文件
vim /etc/my56.cnf
[mysqld]
server_id=3356
port=3356
user=mysql
basedir=/usr/local/mysql56/
datadir=/data/3356/data
socket=/tmp/mysql3356.sock
vim /etc/my57.cnf
[mysqld]
port=3357
server_id=3357
user=mysql
basedir=/usr/local/mysql57
datadir=/data/3357/data
socket=/tmp/mysql3357.sock
innodb_fast_shutdown=0
vim /etc/my80.cnf
[mysqld]
port=3306
server_id=3306
user=mysql
basedir=/usr/local/mysql/
datadir=/data/3306/data
socket=/tmp/mysql3306.sock
innodb_fast_shutdown=0
[root@db01 ~]# cat /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my80.cnf
LimitNOFILE = 5000
# 56、57的systemd配置文件同理
# 第四个历程：启动服务程序
[root@db01 ~]# systemctl daemon-reload
[root@db01 ~]# systemctl start mysqld.service
[root@db01 ~]# systemctl start mysqld56.service
[root@db01 ~]# systemctl start mysqld57.service
[root@db01 ~]# ps -ef|grep mysql
mysql 1359 1 0 13:42 ? 00:00:35 /usr/local/mysql/bin/mysqld --defaults-file=/data/3307/data/my.cnf
mysql 1412 1 0 13:42 ? 00:00:40 /usr/local/mysql/bin/mysqld --defaults-file=/data/3308/data/my.cnf
mysql 1463 1 0 13:42 ? 00:00:37 /usr/local/mysql/bin/mysqld --defaults-file=/data/3309/data/my.cnf
mysql 2059 1 1 15:10 ? 00:00:03 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my80.cnf
mysql 2165 1 36 15:14 ? 00:00:02 /usr/local/mysql56/bin/mysqld --defaults-file=/etc/my56.cnf
mysql 2193 1 37 15:14 ? 00:00:01 /usr/local/mysql57/bin/mysqld --defaults-file=/etc/my57.cnf
root 2223 1235 0 15:15 pts/1 00:00:00 grep --color=auto mysql
[root@db01 ~]# netstat -lntup|grep mysql
tcp6 0 0 :::3306 :::* LISTEN 2059/mysqld
tcp6 0 0 :::3307 :::* LISTEN 1359/mysqld
tcp6 0 0 :::3308 :::* LISTEN 1412/mysqld
tcp6 0 0 :::3309 :::* LISTEN 1463/mysqld
tcp6 0 0 :::3356 :::* LISTEN 2165/mysqld
tcp6 0 0 :::3357 :::* LISTEN 2193/mysqld
tcp6 0 0 :::33060 :::* LISTEN 1359/mysqld
[root@db01 ~]# mysql -S /tmp/mysql3356.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.46 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3356 |
+-------------+
1 row in set (0.00 sec)
mysql>
课程知识内容概述:
1、数据库服务语句分类
1.1 什么是SQL语句
SQL，英文全称为Structured Query Language，中文意思是结构化查询语言（属于编程语言）；
它是一种对关系数据库中的数据进行定义和操作的语言，是大多数关系数据库管理系统所支持的工业标准语言。
1.2 SQL规范标准
在使用SQL语句时，也会用到几种常用的标准：SQL 89 / SQL 92（常用） /SQL 99 /SQL 03
1.3 SQL语句分类
在企业实际应用过程，还会根据SQL语言操作的方式，细化为四种类型：
- DDL Data Definition Language（数据定义语言）
概念介绍：
负责管理数据库的基础数据（不会对表的内容修改），比如增删库、增删表、增删索引、增删用户等；
涉及语句：
CREATE（创建）、ALTER（修改）、DROP（删除）等；
关注人群：
运维人员和开发人员都要熟悉。
相关具体的DDL负责的操作行为，可以执行以下命令进行查看：
mysql> ? Data Definition;
-- 查看获取DDL语言的操作行为
- DCL Data Control Language（数据控制语言）
概念介绍：
主要用来定义访问权限和安全级别
涉及语句：
GRANT（用户授权）、REVOKE（权限回收）、COMMIT（提交）、ROLLBACK（回滚）
关注人群：
运维人员需要熟练
相关具体的DCL负责的操作行为，可以执行以下命令进行查看：
mysql> ? Account Management
-- 查看获取DCL语言的操作行为
- DML Data Manipulation Language（数据操作语言）
概念介绍：
主要针对数据库里的表里的数据进行操作，用来定义数据库记录（数据）；
涉及语句：
SELECT（查）、INSERT（增）、DELETE（删）、UPDATE（改）
关注人群：
开发人员要熟练，运维人员熟悉即可
相关具体的DML负责的操作行为，可以执行以下命令进行查看：
mysql> ? Data Manipulation
-- 查看获取DML语言的操作行为
- DQL Data Query Language（数据查询语言）
概念介绍：
主要用来查询记录（数据）
涉及语句：
SELECT（查）
基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块
关注人群：
运维人员和开发人员都要熟练。
2、数据库服务字符设置
2.1 为什么要有字符编码设置（避免中文乱码）–为什么中文会乱码？
控制 – 纸条 – 打点（010101010） 001 电脑关闭 101 显示信息
开发人员 – 英文写好代码 – 字典-编码（ASCII） 二进制
ASCII：https://zhuanlan.zhihu.com/p/554933865
1字节=8bit 256个二进制组合 10 26 26 xxx
ANSI编码：可以支持更多的国家语言字符信息
2字节=16bit GB2312 65536种二进制组合 10 26 26 xxx 65536-256=63000~ 63000汉字
4字节=32bit Unicode编码 4字节
utf8编码 生僻字 -4字节 A - 1字节
GBK
2.2 数据库中常用的字符编码以及区别
utf8 ： 不支持表情符号 emoji 最多存储3字节长度字符
utf8mb4 ： 支持表情符号 emoji 最多存储4字节长度字符
mysql> create database xiaoQ;
Query OK, 1 row affected (0.01 sec)
mysql> show create database xiaoQ;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| xiaoQ | CREATE DATABASE `xiaoQ` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use xiaoQ;
Database changed
mysql> create table t1(name char(5));
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`name` char(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t1 values('老男孩');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t1;
+-------+
| name |
+-------+
| 老璠 |
+-------+
1 row in set (0.00 sec)
mysql>
mysql> create database xiaoQ;
Query OK, 1 row affected (0.01 sec)
mysql> show create database xiaoQ;
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| xiaoQ | CREATE DATABASE `xiaoQ` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use xiaoQ;
Database changed
mysql> create table t1(name char(10));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t1 values('老男孩');
Query OK, 1 row affected (0.07 sec)
mysql> select * from t1;
+-----------+
| name |
+-----------+
| 老男孩 |
+-----------+
1 row in set (0.00 sec)
mysql>
2.3 数据库中如何设置字符编码
查看数据库字符编码信息：
— 查看可以设置字符编码有什么
mysql> show charset;
— 查看目前的字符编码设置
mysql> show variables like '%char%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/mysql-8.0.26-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.07 sec)
- 全局调整字符编码
[root@db01 ~]# vim /etc/my80.cnf
[mysqld]
character-set-server=utf8
-- 设置服务端字符集编码为utf8
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysql -S /tmp/mysql3306.sock
mysql> show variables like '%char%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb3 | # 此处
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb3 | # 此处
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/mysql-8.0.26-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql>
# 编辑修改客户配置文件参数信息
[root@xiaoQ ~]# vim /etc/my.cnf.d/client.cnf
-- client.cnf是客户端配置文件信息
[client]
default-character-set=utf8mb4
[root@db01 ~]# cat /etc/my80.cnf
[client]
default-character-set=gbk
socket=/tmp/mysql3306.sock
[mysqld]
port=3306
server_id=3306
user=mysql
basedir=/usr/local/mysql/
datadir=/data/3306/data
socket=/tmp/mysql3306.sock
innodb_fast_shutdown=0
character-set-server=gbk
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysql --defaults-file=/etc/my80.cnf
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like "%char%";
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | gbk |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | gbk |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/mysql-8.0.26-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql>
- 局部调整字符编码（库 表） 10库 utf8 1gbk 10表
mysql> create database xiaoB charset utf8;
mysql> create table t2(id int) charset gbk;
mysql> show variables like '%char%';
# PS：在设置字符编码信息时，各个字符编码参数最好统一
数据库中字符设置参数信息说明：
| 序号 | 参数信息 | 解释说明 |
|---|---|---|
| 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 | 这个变量是字符集安装的目录。 |
根据客户端请求数据库服务端字符集转换流程，可以更好的理解以上字符编码配置参数：
01 mysql Server收到请求时将请求数据从 character_set_client 转换为 character_set_connection；
02 进行内部操作前将请求数据从 character_set_connection 转换为内部操作字符集；
- 使用每个数据字段的 CHARACTER SET 设定值；
- 若上述值不存在，则使用对应数据表的字符集设定值；
- 若上述值不存在，则使用对应数据库的字符集设定值；
- 若上述值不存在，则使用 character_set_server 设定值；
03 最后将操作结果从内部操作字符集转换为 character_set_results
2.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 xiaoA 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 |
+------+
| 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)
学生问题：如果一个数据库里面的表字符集最开始设置错误了，导致存储的数据是乱码，如何恢复？
修改表的字符编码方法一：
mysql> alter table t1 charset gbk;
-- 不严谨的方法，只会影响之后存储的数据，不会修改之前存储的数据
方法二：
·锁表逻辑导出数据（例如：mysqldump）
·重新创建数据空表（设置目标字符集）
·导入备份数据信息
-- 严谨的方法，可以影响之后存储的数据，也会修改之前存储的数据
-- 字符集转换是可以的，但是必须保证修改后的字符集是修改前的严格超集（包含）
3、数据库数据类型介绍（用于限制用户输入信息）
哪些数据类型：https://m.php.cn/article/460317.html
整数类型：
| MySQL数据类型 | 含义（有符号） |
|---|---|
| tinyint(m) | 1个字节 范围(-128~127) |
| int(m) | 4个字节 范围(-2147483648~2147483647) |
| bigint(m) | 8个字节 范围(±9.22*10的18次方) |
浮点类型：
| MySQL数据类型 | 含义 |
|---|---|
| float(m,d) | 单精度浮点型 8位精度(4字节) m总个数，d小数位 |
字符串类型：
| MySQL数据类型 | 含义 |
|---|---|
| char(n) | 固定长度，最多255个字符 |
| varchar(n) | 固定长度，最多65535个字符 |
| longtext | 可变长度，最多2的32次方-1个字符 存储评论信息 |
特殊数据类型：
| MySQL数据类型 | 含义 |
|---|---|
| enum | 枚举类型 |
4、数据库属性约束设置（约束用户输入的信息）
约束：
| 序号 | 约束方法 | 解释说明 |
|---|---|---|
| 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
外键: 外面的键, 一张表的一个字段(非主键)指向另外一个表的主键, 那么该字段就称之为外键.
外键所在的表称之为子表(附表); 外键所指向的主键所在的表称之为父表(主表)
01 外键设置说明
添加外键：方式一
# 创建外键语法格式
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是引用字段（主键）
# 查看外键信息是否配置成功：
mysql> desc student;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| number | char(10) | NO | UNI | NULL | |
| name | varchar(10) | NO | | NULL | |
| c_id | int | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
添加外键：方式二
# 创建外键语法格式
alter table 表名 add constraint 外键名 foreign key(外键字段) references 父表(主键字段)
# 创建没有外键信息的表
create table t_foreign(
id int primary key auto_increment,
c_id int
)charset utf8;
# 在没有外键的表中添加外键
alter table t_foreign add constraint class_foreign foreign key(c_id) references class(id);
外键增加条件: 外键字段必须与引用表(父表主键)的数据类型严格保持一致
删除外键
外键不能被修改，只能先删除后再新增；
# 删除外键语法格式
alter table 表名 drop foreign key 外键名;
# 删除表（t_foreign）中外键信息
alter table t_foreign drop foreign key class_foreign;
查看外键
mysql> desc t_foreign;
mysql> show create table t_foreign;
约束01：外键对子表的数据写操作约束（增加和更新）
如果子表中插入的数据所对应的外键在父表不存在，创建不能成功.
mysql> select * from class;
Empty set (0.01 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.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from class;
+----+---------+------+
| id | name | room |
+----+---------+------+
| 1 | Linux80 | 001 |
| 2 | Linux81 | 002 |
+----+---------+------+
2 rows in set (0.00 sec)
mysql> insert into student values(null,'2023110001','xiaoQ',1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------------+-------+------+
| id | number | name | c_id |
+----+------------+-------+------+
| 2 | 2023110001 | xiaoQ | 1 |
+----+------------+-------+------+
1 row in set (0.01 sec)
mysql> select * from class;
+----+---------+------+
| id | name | room |
+----+---------+------+
| 1 | Linux80 | 001 |
| 2 | Linux81 | 002 |
+----+---------+------+
2 rows in set (0.00 sec)
mysql> insert into student values(null,'2023110002','xiaoA',2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------------+-------+------+
| id | number | name | c_id |
+----+------------+-------+------+
| 2 | 2023110001 | xiaoQ | 1 |
| 3 | 2023110002 | xiaoA | 2 |
+----+------------+-------+------+
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`))
-- 删除父表数据，数据被字表所引用，所以不能更新或者删除父行记录
mysql> delete from student where id=2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------------+-------+------+
| id | number | name | c_id |
+----+------------+-------+------+
| 3 | 2023110002 | xiaoA | 2 |
+----+------------+-------+------+
1 row in set (0.00 sec)
mysql> delete from class where id=1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from class;
+----+---------+------+
| id | name | room |
+----+---------+------+
| 2 | Linux81 | 002 |
+----+---------+------+
1 row in set (0.00 sec)
知识补充：数据库SQL_MODE作用说明
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
如何关闭SQL_mode
# 当进行数据库服务版本升级时，可能之前版本数据信息不能满足新版本数据库服务的SQL_mode信息设定，需要暂时设置SQLmode为空
mysql> set global sql_mode='';
-- 配置完毕后，可以重新登录数据库服务进行检查确认
5、数据库语句获取帮助
在数据库服务中，SQL语句涉及到的语句非常的多，在实际应用过程中也未必都能记住，因此就需要掌握获取帮助的方法；
# 获取帮助信息_基本帮助信息
mysql > \h
# 获取帮助信息_语句分类帮助
mysql > help contents
mysql > ? contents
# 获取帮助信息_具体语句帮助
mysql > ? create
mysql > ? create database
6、数据库操作库的语句（DDL）
创建库
mysql > create database oldboy;
mysql > create schema oldboy;
-- 创建新的数据库
mysql > create database oldboy character set utf8mb4;
mysql > create database oldboy charset utf8 collate utf8_general_mysql500_ci;
-- 创建新的数据库，并修改调整默认的字符编码
查看库
mysql > show databases;
-- 查看是否已经创建好
mysql > show create database oldboy;
-- 查看指定数据库如何创建出来的
mysql > show databases like '%xiao%';
-- 检索查看指定的数据库信息
删除库
mysql > drop database test;
mysql > drop schema test;
-- 删除数据库信息（在生产环境一定慎用）
修改库
mysql > alter database test charset utf8mb4;
mysql > alter database test charset utf8 collate utf8_general_mysql500_ci;
-- 修改数据库服务字符集编码信息与字符编码排序规则信息
切换库
mysql> use oldboy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
-- 在已有数据库之间进行切换
mysql> select database();
+------------+
| database() |
+------------+
| oldboy |
+------------+
1 row in set (0.00 sec)
-- 查看当前所在数据库信息
数据定义语句定义数据库规范说明：
- 创建数据库名称规范：要和业务有关，不要有大写字母（为了多平台兼容），不要数字开头，不要含有系统关键字信息；
- 创建数据库明确字符：创建数据库时明确（显示）的设置字符集信息，为了避免跨平台兼容性与不同版本兼容性问题；
- 删除数据库操作慎用：在对数据库进行删除操作时，一定要经过严格审计后再进行操作，并且数据库普通用户不能有drop权限；
7、数据库操作表的语句（DDL）
创建表
创建表的基本语法格式：
create table <表名> (
<字段名1> <类型1>约束 属性 ,
…
<字段名n> <类型n>);
参考命令：
CREATE TABLE `student` (
`id` int NOT NULL COMMENT '学号信息',
`name` varchar(45) NOT NULL COMMENT '学生名',
`age` tinyint unsigned NOT NULL COMMENT '学生年龄',
`gender` enum('M','F','N') NOT NULL DEFAULT 'N' COMMENT '学生性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'
实际操作：
# 切换数据库环境
mysql > use xiaoq;
mysql > select database();
-- 查看是否切换数据库成功
# 创建数据表信息
mysql > create table stu1(
id int(10) not null,
name varchar(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL
);
# 查看数据表信息
mysql > show tables；
mysql > desc stu1;
# 获取创建表语句
mysql> show create table stu1\G;
*************************** 1. row ***************************
Table: stu1
Create Table: CREATE TABLE `stu1` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL,
`age` tinyint NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
修改表
- 修改表名称（审计）
# 修改数据表名称信息
mysql > rename table stu1 to stu2;
或者
mysql > alter table stu2 rename stu3;
-- 利用上面种方式均可修改表名称信息
mysql > show tables;
-- 查看表名称信息是否修改
- 修改表字符编码
# 修改数据表编码信息
mysql > alter table stu1 charset utf8mb4;
-- 修改表结构中字符集编码信息
mysql > show create table stu1;
-- 查看表字符编码信息情况
- 修改数据表结构信息：
# 具体实际操作过程（添加新的表结构字段）
mysql > alter table stu1 add column telno char(11) not null unique key comment '手机号';
-- 在学生表中，添加新的表结构字段列（追加字段列-单列操作）
mysql > alter table stu1 add column wechat varchar(64) not null unique key comment '微信号' after age;
-- 在学生表中，添加新的表结构字段列（插入字段列-单列操作）
mysql > alter table stu1 add column sid int not null unique key comment '微信号' first;
-- 在学生表中，添加新的表结构字段列（插入首行列-单列操作）
mysql > desc stu1；
-- 查看表结构字段信息变化
# 具体实际操作过程（删除已有表结构字段）
mysql > alter table stu1 drop column sid;
-- 在学生表中，删除已有表结构字段列（删除指定字段列-单列操作）
# 具体实际操作过程（修改已有表结构字段）
mysql > alter table stu1 modify name varchar(64);
-- 在学生表中，修改已有表结构字段列（修改表结构数据类型）
mysql > alter table stu1 modify name varchar(64) not null comment '学生名';
-- 在学生表中，修改已有表结构字段列，最后带有保持原有配置的属性信息，否则其他属性信息会被还原为默认
mysql > alter table stu1 change name stuname varchar(64) not null comment '学生名';
或者
mysql > alter table stu1 change column name stuname varchar(64) not null comment '学生名';
-- 在学生表中，修改已有表结构字段列（修改表结构字段名称）
mysql > alter table stu1 modify name varchar(64) not null unique comment '学生名称';
-- 在学生表中，修改已有表结构字段列（修改表结构属性信息）了解即可
mysql > alter table stu1 drop index `name`;
-- 在学生表中，修改已有表结构字段列（删除表结构属性信息）了解即可
mysql > desc stu1；
-- 查看表结构字段信息变化
删除表
# 数据表删除命令语法
mysql > drop table <表名>；
# 具体实际操作过程
mysql > drop table stu1;
-- 删除操作过程，会将定义的表结构和表中数据内容一并删除
mysql > truncate table stu1;
-- 删除操作过程，只是清空表中数据内容，但保留定义的表结构信息
mysql> delete table stu1;
-- 删除操作过程，只是清空表中数据内容，但保留定义的表结构信息
查询表
mysql > desc stu1;
-- 查看表结构（常用命令）
mysql> show create table stu1\G;
-- 查看表的创建命令
数据定义语句定义数据表规范说明：
-
创建数据表名称规范：要和业务有关（含库前缀），不要有大写字母，不要数字开头，不要含有系统关键字信息，名称不要太长；
-
创建数据表属性规范：属性信息显示设置，引擎选择InnoDB，字符集选择utf8/utf8mb4，表信息添加注释；
-
创建数据列属性规范：名称要有意义，不要含有系统关键字信息，名称不要太长；
-
创建数据类型的规范：数据类型选择合适的、足够的、简短的；
-
创建数据约束的规范：每个表中必须都要有主键，最好是和业务无关列，实现自增功能，建议每个列都非空（避免索引失效）/加注释
-
删除数据表操作规范：对于普通用户不能具有删表操作，需要操作删表时需要严格审核
-
修改数据表结构规范：在数据库8.0之前，修改数据表结构需要在业务不繁忙时进行，否则会产生严重的锁
如果出现紧急修改表结构信息需求时，可以使用工具进行调整，比如使用：pt-osc、gh-ost，从而降低对业务影响
8、数据库操作数据语句（DML）
增-insert
添加命令语法格式：
# 数据表数据插入命令语法
mysql> insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]；
-- 属于表内容信息变更操作，需要按照表结构预先定义好的字段信息插入
插入数据信息
# 创建数据库表
CREATE TABLE `oldboy`.`stu1` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT '主键列',
`name` VARCHAR(10) NOT NULL,
`age` TINYINT UNSIGNED NOT NULL,
`dept` ENUM('Linux', 'net', 'go') NOT NULL DEFAULT 'Linux',
PRIMARY KEY (`id`));
# 具体实际操作过程
mysql> desc stu1;
mysql> insert into stu1(id,name,age,dept) values(1,'oldboy',35,'net sec');
-- 插入单行信息标准方法（信息输入不要重复，且特定信息不要为空）
mysql> insert into stu1(id,name,age,dept) values(0,'oldboy',35,'net');
mysql> insert into stu1(id,name,age,dept) values(null,'oldboy',35,'go');
-- 插入单行信息标准方法（自增列信息可以填入0或null，表示默认实现自增效果）
mysql> insert into stu1 values(2,'oldgirl',25,'Linux');
-- 插入单行信息可以不含有表字段信息
mysql> insert into stu1 values(0,'littlegirl',2,'net'),(0,'littleboy',1,'Linux');
-- 插入多行信息可以不含有表字段信息
mysql> insert into stu1(name,age) values('oldboy',35);
-- 插入当行信息可以只含部分字段信息，但是省略字段信息必须具有自增特性 或 可以为空 或有默认值输入
mysql> insert into stu1 values(0,'老男孩',32,'go,Linux,net');
-- 插入中文信息
# 检查信息是否插入成功
mysql> select * from stu1;
删-delete
改-update
作业：
01 如果创建了多个数据库，或者数据库中创建了多个表，如何批量删除？
02 删除表数据的方法 drop truncate delete 三种方式的详细区别？（面试题）
扫一扫
1402
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
