标签 sql 下的文章

说下MySQL的索引

2024-08-17

MySQL的索引是在数据库表的一列或多列上构建的数据结构，可以帮助快速查询、排序和过滤数据。MySQL中常见的索引类型有：

普通索引：最基本的索引类型，没有唯一性的限制。
唯一索引：确保索引列的每个值都是唯一的。
主键索引：特殊的唯一索引，用于唯一标识表中的每行记录。
全文索引：用于全文搜索，仅MyISAM和InnoDB引擎支持。
组合索引：多个列组合成一个索引。

创建索引的SQL语法：




-- 创建普通索引
CREATE INDEX index_name ON table_name(column_name);
 
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(column_name);
 
-- 创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
 
-- 创建全文索引
CREATE FULLTEXT INDEX index_name ON table_name(column_name);
 
-- 创建组合索引
CREATE INDEX index_name ON table_name(column1, column2, ...);

查询表中的索引：




SHOW INDEX FROM table_name;

删除索引：




-- 删除普通索引或唯一索引
DROP INDEX index_name ON table_name;
 
-- 删除主键索引
ALTER TABLE table_name DROP PRIMARY KEY;

请注意，索引可以提高查询速度，但也会降低写操作的速度，因为索引也需要维护。在使用索引时应根据实际情况权衡利弊。

MySql的事务和锁详解

2024-08-17

MySQL中的事务和锁是保障数据一致性和完整性的重要机制。

事务:

事务是一组操作的序列，这组操作要么全部成功，要么全部不做。事务主要用于确保数据的一致性和完整性。

在MySQL中，可以使用以下命令来控制事务：




START TRANSACTION; -- 开始一个事务
 
-- 进行一系列操作，例如：
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column1=value1 WHERE some_column=some_value;
DELETE FROM table_name WHERE some_column=some_value;
 
COMMIT; -- 提交事务，使得操作成为永久的
 
-- 如果需要撤销事务中的操作，可以使用：
ROLLBACK; -- 回滚事务

锁:

锁是控制并发操作时数据访问的一种机制。

在MySQL中，锁可以防止多个事务同时修改相同的数据，保持数据的一致性和可串性。

锁的类型有很多，如行锁、表锁、乐观锁、悲观锁等。

例如，可以使用以下命令来显式地为表加上锁：




LOCK TABLES table_name READ; -- 为表加上读锁，其他事务可以读取表，但不能修改它
 
-- 或者
 
LOCK TABLES table_name WRITE; -- 为表加上写锁，其他事务不能对表进行任何操作
 
-- 完成操作后，使用以下命令解锁：
UNLOCK TABLES;

在InnoDB存储引擎中，默认使用行级锁，并且支持多种锁的算法，如记录锁、间隙锁、next-key锁等，以及在行级锁上的扩展，如意向锁。




SELECT * FROM table_name WHERE ... FOR UPDATE; -- 加上写锁，阻止其他事务进行写操作

以上是MySQL中事务和锁的基本概念和使用方法。在实际应用中，应根据具体的业务需求和并发场景选择合适的锁策略，以保障数据的一致性和性能的适当性。

MySQL—一条查询SQL语句的完整执行流程

2024-08-17

一条MySQL查询SQL语句的完整执行流程大致如下：

客户端发送查询SQL语句到服务器。
服务器接收到查询后，检查查询缓存，如果命中缓存，则直接返回缓存中的结果集。
如果查询缓存没有命中，服务器会解析SQL语句，验证语法、语义等，并生成执行计划。
根据执行计划，服务器调用存储引擎API执行查询。
服务器将查询结果返回给客户端。

以下是一个简单的查询示例：




SELECT * FROM users WHERE id = 1;

在执行这条SQL语句时，MySQL服务器可能会执行上述步骤。请注意，实际的执行流程可能会根据查询类型（如是否涉及事务、是否有索引、表数据量等）和服务器配置有所不同。

MySQL 字符集概念与原理及如何配置字符集 - 超详细图文详解

2024-08-17

在MySQL中，字符集用于确定数据库中存储的字符的编码方式。MySQL提供了多种字符集供选择，以支持不同语言的字符存储。

查看当前MySQL支持的所有字符集：




SHOW CHARACTER SET;

查看某个字符集的详细信息：




SHOW CHARACTER SET LIKE 'utf8';

设置数据库字符集：
在创建数据库时，可以指定默认字符集和排序规则：




CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

设置表字符集：
在创建表时，可以指定字符集和排序规则：




CREATE TABLE mytable (
    id INT PRIMARY KEY,
    text VARCHAR(255)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

设置列字符集：
在创建列时，可以指定字符集和排序规则：




CREATE TABLE mytable (
    id INT PRIMARY KEY,
    text VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

查看字符集和排序规则设置：
可以通过以下SQL查询当前数据库、表或列的字符集和排序规则：




-- 当前数据库
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'mydb';
 
-- 当前表
SELECT TABLE_SCHEMA, TABLE_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'mytable';
 
-- 当前列
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'mytable' AND COLUMN_NAME = 'text';

修改字符集和排序规则：
如果需要修改现有数据库、表或列的字符集和排序规则，可以使用以下语句：




-- 修改数据库字符集
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
-- 修改表字符集
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
-- 修改列字符集
ALTER TABLE mytable MODIFY text VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

配置文件设置字符集：
在my.cnf或my.ini配置文件中，可以设置默认的字符集和排序规则：




[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

连接字符集设置：
在连接MySQL时，可以指定字符集：




mysql -u username -p --default-character-set=utf8mb4

以上是配置MySQL字符集的基本方法，确保在数据库操作中正确设置字符集，以避免字符编码问题。

MySQL中交集、并集、差集

2024-08-17

在MySQL中，可以使用INTERSECT、UNION和EXCEPT（在标准的SQL中是这样的，但在MySQL中，差集操作通常使用MINUS）来进行交集、并集和差集的查询。但是，MySQL不直接支持EXCEPT或MINUS，而是使用NOT EXISTS子句来表达差集。

交集（INTERSECT）：




SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;

并集（UNION）：




SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

差集（NOT EXISTS）：




SELECT column_name(s) FROM table1 t1
WHERE NOT EXISTS (
  SELECT 1 FROM table2 t2 WHERE t1.column_name = t2.column_name
);

请注意，在MySQL中，UNION默认去除重复行，如果不想去除重复行，可以使用UNION ALL。对于INTERSECT，MySQL也没有直接支持，通常需要自连接或者使用变量来模拟。

MySQL的卸载与安装（Linux）

2024-08-17

在Linux系统中，卸载MySQL通常可以通过系统的包管理器来完成。以Debian系的Linux发行版（如Ubuntu）为例，卸载MySQL的命令如下：




sudo apt-remove --purge mysql-server mysql-client mysql-common
sudo apt-autoremove
sudo apt-get remove dbconfig-mysql
sudo rm -rf /etc/mysql /var/lib/mysql
sudo rm -rf /var/lib/mysql-files /var/lib/mysql-keyring

安装MySQL，可以使用以下步骤：

更新包列表：




sudo apt-get update

安装MySQL服务器：




sudo apt-get install mysql-server

安装MySQL客户端：




sudo apt-get install mysql-client

安装完成后，运行安全安装脚本：




sudo mysql_secure_installation

启动MySQL服务：




sudo service mysql start

登录MySQL验证安装：




mysql -u root -p

输入root用户的密码后，如果可以进入MySQL命令行界面，表示安装成功。

【MySQL】查询数据，对结果进行排序（关键字：ORDER BY）

2024-08-17

在MySQL中，使用ORDER BY子句可以对查询结果进行排序。ORDER BY子句可以指定一个或多个列，根据这些列进行升序（ASC）或降序（DESC）排序。

以下是一个简单的例子，假设我们有一个名为students的表，它有id, name, 和 grade 列，我们想按照成绩的降序排序来获取学生的信息：




SELECT id, name, grade
FROM students
ORDER BY grade DESC;

如果我们想要按照名字的字母顺序和成绩的降序进行排序，可以这样写：




SELECT id, name, grade
FROM students
ORDER BY name ASC, grade DESC;

在这个例子中，首先按name进行升序排序，如果有多个学生具有相同的名字，则按照grade进行降序排序。

【MySQL】事务二

2024-08-17

在MySQL中，可以使用以下代码来创建一个简单的事务示例：




-- 开始一个事务
START TRANSACTION;
 
-- 尝试从一个账户转账到另一个账户
-- 假设有两个账户表account1和account2，以及相应的转账操作
UPDATE account1 SET balance = balance - 100 WHERE id = 1;
UPDATE account2 SET balance = balance + 100 WHERE id = 2;
 
-- 如果转账操作成功，则提交事务
COMMIT;
 
-- 如果在转账过程中出现任何错误，则回滚事务
ROLLBACK;

这个例子演示了如何在MySQL中开始一个事务，执行一系列操作，并根据操作的结果决定是提交还是回滚事务。在实际应用中，你需要根据业务逻辑来决定何时开始事务，并在操作成功完成后提交事务，或者在操作过程中遇到任何错误时回滚事务。

mysql 添加索引

2024-08-17

MySQL 添加索引可以通过以下几种不同的方法实现：

使用 CREATE INDEX 语句：可以通过创建索引的语法来添加索引。例如，要在某个表的某列上创建索引，可以使用以下语句：
```
CREATE INDEX index_name ON table_name (column_name);
```
这将在表 table\_name 的 column\_name 列上创建名为 index\_name 的索引。
使用 ALTER TABLE 语句：如果表已经存在，可以使用 ALTER TABLE 语句来添加索引。例如，要在某个表的某列上添加索引，可以使用以下语句：
```
ALTER TABLE table_name ADD INDEX index_name (column_name);
```
这将在表 table\_name 的 column\_name 列上添加名为 index\_name 的索引。
使用 CREATE TABLE 语句：当创建表时，可以在表的定义中直接添加索引。例如，以下是在创建表时添加索引的示例：
```
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...,
    INDEX index_name (column_name)
);
```
这将在表 table\_name 的 column\_name 列上创建名为 index\_name 的索引。

需要注意的是，索引的创建需要根据实际情况和查询需求来决定。不合理的索引设计或者过多的索引可能会导致性能下降。在决定添加索引时，需要考虑到查询频率、数据量、数据更新等因素。在添加索引之前，可以先使用 EXPLAIN 语句来分析查询语句的执行计划，以确定是否需要添加索引。