MySQL知识点复习

天生不爱笑

已于 2024-01-03 23:37:57 修改

阅读量1k

点赞数 20

文章标签： mysql 数据库

于 2024-01-02 23:02:17 首次发布

本文链接：https://blog.csdn.net/weixin_51621120/article/details/135349527

版权

什么是MySQL

MySQL是关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS（Relational Database Management System：关系型数据库管理系统）应用软件之一。

数据库是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的API用于访问、创建、管理、搜索和复制所保存的数据。

所谓关系型数据库是建立在关系模型基础上的数据库，借助集合代数等数学概念和方法处理数据库中的数据。

RDBMS即关系型数据库管理系统的特点：

1.数据以表格的形式出现

2.每行为各种记录名称

3.每列为记录名称所对应的数据域

4.许多行和列组成一个表单

5.若干的表单组成database

RDBMS术语

数据库：数据库是一些关联表的集合

数据表：表是数据的矩阵，在一个数据库中的表看起来像一个电子表格

列：一列（数据元素）包含了相同类型的数据

行：一行（元组，或记录）是一组相关的数据

冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性

主键：主键是唯一的，一个数据表中只能有一个主键，可以使用主键来查询数据

外键：外键用来关联两个数据表

复合键：复合键（组合键）将多个列作为一个索引建，一般用于复合索引

索引：使用索引可快速查询数据库中特定的信息，索引是数据库表中一列或多列的值进行排列的一种结构

参照完整性：参照完整性要求关系中不允许引用不存在的实体，实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

表头（header）：每一列的名称

列（col）：具有相同数据类型的数据的集合

行（row）：每一行用来描述某条记录的具体信息

值（value）：行的具体信息，每个值必须与该列的数据类型相同

键（key）：键的值在当前列中具有唯一性

Linux系统安装MySQL

检查是否已经安装MySQL

rpm -qa | grep mysql 检测系统是否自带安装 MySQL

若已经安装，可以选择进行卸载

rpm -e mysql　　                        // 普通删除模式
rpm -e --nodeps mysql　　               // 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除

安装MySQL

下载wget命令

yum -y install wget

在线下载MySQL

wget https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

安装MySQL

rpm -ivh mysql57-community-release-el7-8.noarch.rpm

安装MySQL服务

1.进入yum.repos.d目录

cd /etc/yum.repos.d/
2.安装MySQL服务

yum -y install mysql-server
3.启动MySQL

systemctl start mysqld
4.获取MySQL临时秘钥

grep 'temporary password' /var/log/mysqld.log
5.使用临时秘钥进行登录

mysql -uroot -p
6.修改MySQL登录密码

set global validate_password_policy=LOW;        把MySQL的密码校验强度改为低风险

set global validate_password_length=5;                修改MySQL的密码长度

ALTER USER 'root'@'localhost' IDENTIFIED BY 'admin';         修改MySQL密码

exit; 退出mysql

若之前安装过MySQL，遇到公钥问题如下

可执行下列命令：

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

再次执行

yum -y install mysql-server

查看MySQL服务是否启动

ps -ef|grep mysql

登录MySQL

mysql -uroot -p{密码}

如我的密码是#lO0tmLwq1Dd 我登录时就是 mysql -u root -p#lO0tmLwq1Dd

允许远程访问MySQL

1、登录MySQL

mysql -uroot -padmin

2、切换到MySQL数据

use mysql;

3、查看user表

select Host,User from user;

root用户只允许Localhost主机登录，现在将修改为任意地址可以访问

执行命令

update user set Host='%' where User='root'; 修改为允许任何地址访问

flush privileges; 刷新权限

sudo systemctl disable firewalld 关闭Cenots的防火墙（需要重启虚拟机）

后续可以使用Navicat通过该虚拟机的IP地址连接该虚拟机的MySQL数据库。

查看MySQL的运行状态

systemctl status mysqld

systemctl start mariadb                  #启动MariaDB
systemctl stop mariadb                   #停止MariaDB
systemctl restart mariadb                #重启MariaDB
systemctl enable mariadb                 #设置开机启动

MySQL命令

数据库命令以 ; 作为结束标志。

展示数据库信息

show databases;

数据库的三种退出方式

exit 直接退出

quit 直接退出

按下Ctrl+D进行退出

创建数据库

create database 数据库名；

创建数据库时，还可设置指定字符集和排列规则

数据库已经存在，执行 CREATE DATABASE 将导致错误。

为了避免这种情况，你可以在 CREATE DATABASE 语句中添加 IF NOT EXISTS 子句：

CREATE DATABASE IF NOT EXISTS mydatabase;

删除数据库

DROP DATABASE <database_name>; -- 直接删除数据库，不检查是否存在
或
DROP DATABASE [IF EXISTS] <database_name>;

IF EXISTS 是一个可选的子句，表示如果数据库存在才执行删除操作，避免因为数据库不存在而引发错误。

选择数据库

use databasename；选择名为databasename的数据库

创建数据表

创建 MySQL 数据表需要以下信息：

表名
表字段名
定义每个表字段的数据类型

以下为创建 MySQL 数据表的 SQL 通用语法：
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);
参数说明：

table_name 是你要创建的表的名称。
column1, column2, ... 是表中的列名。
datatype 是每个列的数据类型。

具体命令如下：

在创建表时指定数据引擎，字符集和排序规则等，可以使用 CHARACTER SET 和 COLLATE 子句：

CREATE TABLE mytable (
id INT PRIMARY KEY,
name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

以上代码创建一个使用 utf8mb4 字符集和 utf8mb4_general_ci 排序规则的表。

删除数据表

DROP TABLE table_name ; -- 直接删除表，不检查是否存在
或
DROP TABLE [IF EXISTS] table_name;

参数说明：

table_name 是要删除的表的名称。
IF EXISTS 是一个可选的子句，表示如果表存在才执行删除操作，避免因为表不存在而引发错误。

MySQL插入数据

MySQL 表中使用 INSERT INTO 语句来插入数据。

向MySQL数据表插入数据通用的 INSERT INTO SQL语法：
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
参数说明：

table_name 是你要插入数据的表的名称。
column1, column2, column3, ... 是表中的列名。
value1, value2, value3, ... 是要插入的具体数值。

如果数据是字符型，必须使用单引号 ' 或者双引号 "，如： 'value1', "value1"。

如在users表中插入一条数据

INSERT INTO xiaoxiang1 (username, email, birthdate, is_active)
VALUES ('xiaoxiang', 'xiaoxiang@qq.com', '2000-09-19', true);

查看插入的数据

select * from xiaoxiang1;

查询数据

MySQL 数据库使用 SELECT 语句来查询数据。

以下为在 MySQL 数据库中查询数据通用的 SELECT 语法：
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];
参数说明：

column1, column2, ... 是你想要选择的列的名称，如果使用 * 表示选择所有列。
table_name 是你要从中查询数据的表的名称。
WHERE condition 是一个可选的子句，用于指定过滤条件，只返回符合条件的行。
ORDER BY column_name [ASC | DESC] 是一个可选的子句，用于指定结果集的排序顺序，默认是升序（ASC）。
LIMIT number 是一个可选的子句，用于限制返回的行数。

-- 选择所有列的所有行
SELECT * FROM users;

-- 选择特定列的所有行
SELECT username, email FROM users;

-- 添加 WHERE 子句，选择满足条件的行
SELECT * FROM users WHERE is_active = TRUE;

-- 添加 ORDER BY 子句，按照某列的升序排序
SELECT * FROM users ORDER BY birthdate;

-- 添加 ORDER BY 子句，按照某列的降序排序
SELECT * FROM users ORDER BY birthdate DESC;

-- 添加 LIMIT 子句，限制返回的行数
SELECT * FROM users LIMIT 10;

WHERE子句

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

WHERE 子句用于在 MySQL 中过滤查询结果，只返回满足特定条件的行。

如在xiaoxiang1中查找username为xiaoxiang的数据，sql语句如下：

select * from xiaoxiang1 where username = 'xiaoxiang'；

where判断条件

等于条件

SELECT * FROM users WHERE username = 'xiaoxiang';

不等于条件

SELECT * FROM users WHERE username != 'xiaoxiang';

大于条件

SELECT * FROM products WHERE price > 50.00;

小于条件

SELECT * FROM orders WHERE order_date < '2023-01-01';

大于等于条件

SELECT * FROM employees WHERE salary >= 50000;

小于等于条件

SELECT * FROM students WHERE age <= 21;

组合条件（AND、OR）

SELECT * FROM products WHERE category = 'Electronics' AND price > 100.00;

SELECT * FROM orders WHERE order_date >= '2023-01-01' OR total_amount > 1000.00;

模糊匹配条件（like）

SELECT * FROM customers WHERE first_name LIKE 'J%';

IN条件

SELECT * FROM countries WHERE country_code IN ('US', 'CA', 'MX');

NOT条件

SELECT * FROM products WHERE NOT category = 'Clothing';

BETWEEN条件

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

IS NULL条件

SELECT * FROM employees WHERE department IS NULL;

IS NOT NULL条件

SELECT * FROM customers WHERE email IS NOT NULL;

UPDATE更新

修改或更新 MySQL 中的数据，我们可以使用 UPDATE 命令来操作。

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
参数说明：

table_name 是你要更新数据的表的名称。
column1, column2, ... 是你要更新的列的名称。
value1, value2, ... 是新的值，用于替换旧的值。
WHERE condition 是一个可选的子句，用于指定更新的行。如果省略 WHERE 子句，将更新表中的所有行。

数据更新例子：

update xiaoxiang1 set email = '666@qq.com' where id= 1 ；

更新单个列的值

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

更新多列的值

UPDATE orders
SET status = 'Shipped', ship_date = '2023-03-01'
WHERE order_id = 1001;

使用表达式进行更新

UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';

以上 SQL 语句将每个属于 'Electronics' 类别的产品的价格都增加了 10%。

更新符合条件的所有行

UPDATE students
SET status = 'Graduated';

以上 SQL 语句将所有学生的状态更新为 'Graduated'。

更新使用子查询的子句

UPDATE customers
SET total_purchases = (
SELECT SUM(amount)
FROM orders
WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';

以上 SQL 语句通过子查询计算每个 'Premium' 类型客户的总购买金额，并将该值更新到 total_purchases 列中

DELETE删除

DELETE FROM 命令来删除 MySQL 数据表中的记录。

DELETE FROM table_name
WHERE condition;
参数说明：

table_name 是你要删除数据的表的名称。
WHERE condition 是一个可选的子句，用于指定删除的行。如果省略 WHERE 子句，将删除表中的所有行。

删除所有符合条件的行

DELETE FROM students
WHERE graduation_year = 2021;

以上 SQL 语句删除了 students 表中所有 graduation_year 为 2021 的学生的记录。

删除所有行

DELETE FROM orders;

以上 SQL 语句删除了 orders 表中的所有记录，但表结构保持不变。

使用子查询删除符合条件的行

DELETE FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_date < '2023-01-01'
);

以上 SQL 语句通过子查询删除了 orders 表中在 '2023-01-01' 之前下的订单对应的客户。

LIKE模糊匹配

LIKE 子句是在 MySQL 中用于在 WHERE 子句中进行模糊匹配的关键字。它通常与通配符一起使用，用于搜索符合某种模式的字符串。

LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

select * from xiaoxiang1 where username like ‘x%’ ;

百分号通配符 %：

% 通配符表示零个或多个字符。例如，'x%' 匹配以字母 'x' 开头的任何字符串。

select * from xiaoxiang1 where username like '_i%';

下划线通配符 _：

_ 通配符表示一个字符。例如，'_r%' 匹配第二个字母为 'r' 的任何字符串。

select * from xiaoxiang1 where username like 'xiaoxi%n_';

组合使用 % 和 _：

不区分大小写的匹配：

select * from xiaoxiang1 where username like 'xiao%' collate utf8mb4_general_ci;

以上 SQL 语句将选择姓氏以 'smi' 开头的所有员工，不区分大小写。

LIKE 子句提供了强大的模糊搜索能力，可以根据不同的模式和需求进行定制。在使用时，请确保理解通配符的含义，并根据实际情况进行匹配。

UNION操作符

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行。

UNION 操作符必须由两个或多个 SELECT 语句组成，每个 SELECT 语句的列数和对应位置的数据类型必须相同。

基本的 UNION 操作：
SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;
以上 SQL 语句将选择客户表和供应商表中所有城市的唯一值，并按城市名称升序排序。

使用过滤条件的 UNION：
SELECT product_name FROM products WHERE category = 'Electronics'
UNION
SELECT product_name FROM products WHERE category = 'Clothing'
ORDER BY product_name;
以上 SQL 语句将选择电子产品和服装类别的产品名称，并按产品名称升序排序。

UNION 操作中的列数和数据类型必须相同：
SELECT first_name, last_name FROM employees
UNION
SELECT department_name, NULL FROM departments
ORDER BY first_name;
以上 SQL 语句中，departments 表中的 department_name 列被映射到了 employees 表中的 last_name 列，但是列数和数据类型必须相同。

使用 UNION ALL 不去除重复行：
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
ORDER BY city;
以上 SQL 语句使用 UNION ALL 将客户表和供应商表中的所有城市合并在一起，不去除重复行。

ORDER BY(排序) 语句

ORDER BY(排序) 语句可以按照一个或多个列的值进行升序（ASC）或降序（DESC）排序。

按照id升序排列

select * from xiaoxiang1 order by id ASC;

按照id降序排列

select * from xiaoxiang1 order by id DESC;

1. 单列排序：
SELECT * FROM products
ORDER BY product_name ASC;
以上 SQL 语句将选择产品表 products 中的所有产品，并按产品名称升序 ASC 排序。

2. 多列排序：
SELECT * FROM employees
ORDER BY department_id ASC, hire_date DESC;
以上 SQL 语句将选择员工表 employees 中的所有员工，并先按部门 ID 升序 ASC 排序，然后在相同部门中按雇佣日期降序 DESC 排序。

3. 使用数字表示列的位置：
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 1 ASC;
以上 SQL 语句将选择员工表 employees 中的名字和工资列，并按第三列（salary）降序 DESC 排序，然后按第一列（first_name）升序 ASC 排序。

4. 使用表达式排序：
SELECT product_name, price * discount_rate AS discounted_price
FROM products
ORDER BY discounted_price DESC;
以上 SQL 语句将选择产品表 products 中的产品名称和根据折扣率计算的折扣后价格，并按折扣后价格降序 DESC 排序。

5. 使用 NULLS FIRST 或 NULLS LAST 处理 NULL 值：
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS LAST;
以上 SQL 语句将选择产品表 products 中的产品名称和价格，并按价格降序 DESC 排序，将 NULL 值排在最后。

GROUP BY（分组）语句

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具，尤其在处理大量数据时，它能够提供有用的汇总信息。

select birthdate from xiaoxiang1 group by birthdate;

以birthdate将xiaoxiang1表中数据进行分类

将已有数据表导入MySQL

所用数据表文件：https://www.alipan.com/s/H9tYagvwvJLhttps://www.alipan.com/s/H9tYagvwvJL

1、将数据表文件存入/home目录下

2、在Linux中执行导入命令（MySQL服务中执行）

source /home/test.sql

查看导入的表数据

show tables;

select * from runoob_tbl;

select * from tcount_tbl;

连接的使用

使用JOIN进行联合多表查询，JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

上述代码相当于

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

LEFT JOIN，该语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据，即使在右侧表 tcount_tbl中没有对应的 runoob_author 字段值。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即使在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

NULL值处理

MySQL提供了三大运算符用于处理NULL值:

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

使用 <=> 操作符进行 NULL 比较：

<=> 操作符是 MySQL 中用于比较两个表达式是否相等的特殊操作符，对于 NULL 值的比较也会返回 TRUE。它可以用于处理 NULL 值的等值比较。

正则表达式

MySQL 可以通过 LIKE ...% 来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配， MySQL 中使用 REGEXP 和 RLIKE操作符来进行正则表达式匹配。

正则表达式匹配的字符类

.：匹配任意单个字符。
^：匹配字符串的开始。
$：匹配字符串的结束。
*：匹配零个或多个前面的元素。
+：匹配一个或多个前面的元素。
?：匹配零个或一个前面的元素。
[abc]：匹配字符集中的任意一个字符。
[^abc]：匹配除了字符集中的任意一个字符以外的字符。
[a-z]：匹配范围内的任意一个小写字母。
\d：匹配一个数字字符。
\w：匹配一个字母数字字符（包括下划线）。
\s：匹配一个空白字符。

使用 REGEXP 进行模式匹配

REGEXP 是用于进行正则表达式匹配的运算符。

REGEXP 用于检查一个字符串是否匹配指定的正则表达式模式，以下是 REGEXP 运算符的基本语法：
SELECT column1, column2, ...
FROM table_name
WHERE column_name REGEXP 'pattern';
参数说明：

column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
table_name 是你要从中查询数据的表的名称。
column_name 是你要进行正则表达式匹配的列的名称。
'pattern' 是一个正则表达式模式。

查找 name 字段中以 'st' 为开头的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找 name 字段中以 'ok' 为结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找 name 字段中包含 'mar' 字符串的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找 name 字段中以元音字符开头或以 'ok' 字符串结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
选择订单表中描述中包含 "item" 后跟一个或多个数字的记录。
SELECT * FROM orders WHERE order_description REGEXP 'item[0-9]+';
使用 BINARY 关键字，使得匹配区分大小写：
SELECT * FROM products WHERE product_name REGEXP BINARY 'apple';
使用 OR 进行多个匹配条件，以下将选择姓氏为 "Smith" 或 "Johnson" 的员工记录：
SELECT * FROM employees WHERE last_name REGEXP 'Smith|Johnson';

使用 RLIKE 进行模式匹配

RLIKE 是 MySQL 中用于进行正则表达式匹配的运算符，与 REGEXP 是一样的，RLIKE 和 REGEXP 可以互换使用，没有区别。

以下是使用 RLIKE 进行正则表达式匹配的基本语法：
SELECT column1, column2, ...
FROM table_name
WHERE column_name RLIKE 'pattern';
参数说明：

column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
table_name 是你要从中查询数据的表的名称。
column_name 是你要进行正则表达式匹配的列的名称。
'pattern' 是一个正则表达式模式。
SELECT * FROM products WHERE product_name RLIKE '^[0-9]';
以上 SQL 语句选择产品名称以数字开头的所有产品。

MySQL事务

事务的四个条件：原子性、一致性、隔离性、持久性

事务的控制语句：

BEGIN 或 START TRANSACTION 显式地开启一个事务

COMMIT 等价于 COMMIT WORK 提交事务，使已对数据库进行的修改成为永久性

ROLLBACK 等价于 ROLLBACK WORK 回滚会结束用户的事务，撤销正在进行的所有未提交的修改

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和SERIALIZABLE。

事务举例：