MySQL数据库管理优化及高阶语句（用户，权限，索引，事务，存储引擎，备份，视图，查询，存储过程）

2023-12-17 16:31:19

数据库用户管理

新建用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
//'用户名'：指定将创建的用户名.
'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录
可用通配符%
'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
若使用加密密码，需要先使用SELECT PASSWORD('密码')；获取密文，再在语句中添PASSWORD '密文'；
若省略“IDENTIFIED BY"部分，则用户的密码将为空(不建议使用)

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';
SELECT PASSWORD('abc123');
CREATE USER 'user2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

查看用户信息

创建后的用户保存在mysql 数据库的user表里

USE mysql;
SELECT User,authentication_string,Host from user;

重命名指定

RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

删除用户

DROP USER 'lisi'@'localhost' ;

修改当前用户密码

SET PASSWORD = PASSWORD('abc123');

修改其他用户密码

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('abc123T');

忘记root密码的解决办法

修改/etc/my.cnf 配置文件，免密登陆mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables    #添加，使登录mysql不使用授权表

systemctl restart mysqld
mysql    #直接登录

然后使用SQL语句修改密码
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root';

FLUSH PRIVILEGES;
quit
mysql -u root -pabc123
PS:最后再把/etc/my.cnf 配置文件里的skip-grant-tables 删除，并重启mysql服务

数据库用户提权

授权

grant 提权
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select, insert, 
update”。使用"all"表示所有权限，可授权执行任何操作。

#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。
例如，使用“kgc.*"表示授权操作的对象为school数据库中的所有表。

#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.xyw.com"、“192.168.80.%”等。

#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。
在新建用户时，若省略“IDENTIFIED BY"部分，则用户的密码将为空。

#允许用户zhangsan在本地查询school数据库中所有表的数据记录，
但禁止查询其他数据库中的表的记录。
GRANT select ON school.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'abc123';

#允许用户lisi在所有终端远程连接mysql，并拥有所有权限。
GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';


GRANT ALL PRIVILEGES ON kgc.* TO 'lisi'@'192.168.10.2' IDENTIFIED BY 'abc123';

授权用户权限是all privilege。这个all privilege都有哪些权限?all privilege权限如下:
insert(插入数据)
select (查询数据)
update (更新表的数据)
delete(删除表中数据)
create (创建库，表)
drop(删除库，表)
refernces
index(建立索引)
alter(更改表属性)
create temp orary tableslock tables (锁表)
execute
create view (创建视图)
show view(显示视图)
create routine(创建存储过程)
alter routine(修改存储过程)
event(事件)
trigger on(创建触发器)

flush privileges;    #刷新权限
quit

mysql -u zhangsan -pabc123
use xyw;
show tables; .
select * from yyy;

查看权限

mysql -u root -pabc123
SHOW GRANTS FOR 用户名@来源地址;

SHOW GRANTS FOR 'lisi'@'%';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址;

REVOKE ALL ON *.* FROM 'lisi'@'%';

SHOW GRANTS FOR 'lisi'@'%';
#USAGE权限只能用于数据库登陆，不能执行任何操作; USAGE权限不能被回收，即REVOKE不能删除用户。
flush privileges;

MySQL索引

索引是一种用于提高数据库查询性能的数据结构。在 MySQL 中，索引是对数据库表中一列或多列的值进行排序的一种结构。它类似于书籍的目录，可以加速数据库的数据检索过程。

以下是一些关于 MySQL 索引的重要概念：

  1. 索引的优点：

  • 提高查询速度：通过减小服务器需要扫描的数据量，从而加快数据的检索速度。

  • 唯一性约束：可以通过创建唯一索引来确保表中某列的唯一性。

  1. 创建索引的准则：

  • 在经常需要搜索的列上创建索引，可以加快搜索速度。

  • 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列。

  1. 索引的分类：

  • 主键索引：一张表只能有一个主键索引，不允许重复，不允许为 NULL。

  • 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可以有多个唯一索引。

  • 普通索引：基本索引类型，没有唯一性要求。

  • 全文索引：用于全文搜索的索引类型。

  1. 索引结构：

  • 常用的索引结构有 B-Tree、B+Tree、Hash 等。

  • B-Tree 和 B+Tree 是 MySQL 中常见的索引结构，适用于范围查询和排序操作。

  1. 使用注意事项：

  • 虽然索引可以提高查询性能，但过多的索引可能导致写操作性能下降。

  • 需要权衡查询和写操作的需求，避免创建过多冗余或不必要的索引。

示例：在创建表时添加索引

CREATE TABLE your_table_name (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

-- 在 name 列上创建索引
CREATE INDEX idx_name ON your_table_name(name);

上述代码中，CREATE INDEX 语句用于在 your_table_name 表的 name 列上创建索引。请根据实际需求选择合适的列和索引类型。

索引是一种帮助系统更快的查询查找的信息的数据结构，索引是一种优化。

索引的作用

  1. 加速查询操作： 索引通过预先排序和组织数据，使得数据库系统能够快速定位和检索特定值或数据范围。这样可以避免扫描整个表的操作，大幅度提高了查询效率。

  2. 减少数据检索时间： 对于大型数据集，使用索引可以大幅减少数据库系统需要扫描的数据量。这样可以显著提高查询效率，尤其是在复杂的查询条件下。

  3. 优化连接操作： 在连接查询（JOIN）时，索引能够快速定位连接列的匹配数据，提高连接操作的速度，尤其是对于大型数据表的连接操作。

  4. 支持唯一性约束： 可以创建唯一索引，确保某些列或列组合的值是唯一的，保证数据的完整性和唯一性。

  5. 支持排序和分组： 对于排序和分组操作，索引可以加速这些操作，减少系统的负载，提高排序和分组的效率。

  6. 优化范围查询和排序： 某些类型的索引（如 B+ 树索引）能够更好地支持范围查询和排序操作，提高相关查询的性能。

  7. 加速数据写入： 在某些情况下，合适的索引可以提高数据写入的速度，尤其是对于已排序的索引列。

总体而言，索引可以极大地提高数据库的查询效率和性能，但需要注意的是，不合理的索引设计或者过多的索引可能会增加写操作的负担，影响数据库的更新性能。因此，在设计索引时需要综合考虑查询的频率、字段的选择以及系统的整体性能需求。

索引的分类与创建

1. 普通索引

直接创建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

#(列名(length))：length是可选项。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾。

修改表方式创建

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

创建表的时候指定索引

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));
2. 多列索引
CREATE INDEX index_name ON table_name (column1, column2, ...);

多列索引适用于需要同时考虑多个列的查询。例如：

CREATE INDEX idx_name ON orders (customer_id, order_date);
3. 唯一索引

与普通索引类似，但区别是唯一索引列的每个值都唯一。 唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

直接创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

修改表的方式创建

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

创建表时指定

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));
4. 主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。 一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。

创建表的时候指定

CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

修改表的方式创键

ALTER TABLE 表名 ADD PRIMARY KEY (列名);
5. 组合索引（单列索引与多列索引)

可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为select语句的 where条件是依次从左往右执行的，所以在使用select 语句查询时where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

例:create table amd1 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));
show create table amd1;
insert into amd1 values(1,'zhangsan','123123');
nsert into amd1 values(1,'zhangsan','123123');

组合索引创建的字段顺序是其触发索引的查询顺序
例如：
--+
| test3 | CREATE TABLE "test3" (
  "id" int(11) NOT NULL,
  "name" varchar(50) DEFAULT NULL,
  "age" int(5) DEFAULT NULL,
  KEY "index_idname" ("id","name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
对以上的表进行select 
select id,name from test3;            #会触发组合索引
而：
select name,id from test3;            #按照索引从左到右检索的顺序，则不会触发组合索引
6.全文索引

直接创建索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

修改表的方式创建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名)); 
#数据类型可以为 CHAR、VARCHAR 或者 TEXT

使用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

索引的使用

1. 查询优化

索引可以加速SELECT语句的执行，特别是在WHERE子句中包含索引列的情况下。

SELECT * FROM employees WHERE last_name = 'Smith';
2. 排序和分组

如果你经常对某一列进行排序或分组操作，为该列创建索引可以提高性能。

SELECT department, AVG(salary) FROM employees GROUP BY department;
3. 避免全表扫描

索引可以帮助避免全表扫描，提高查询效率。

SELECT * FROM products WHERE category_id = 5;
4. 覆盖索引

如果一个查询只需要索引列的数据，而不需要从表中读取其他列的数据，那么这个查询可以称为覆盖索引。

SELECT product_name FROM products WHERE category_id = 5;

查看索引

show index from 表名;
show index from 表名\G； 竖向显示表索引信息
show keys from 表名;
show keys from 表名\G;

各字段的含义如下：

Table    表的名称
Non_unique    如果索引内容唯一，则为 0；如果可以不唯一，则为 1。
Key_name    索引的名称。
Seq_in_index    索引中的列序号，从 1 开始。 limit 2,3
Column_name    列名称。
Collation    列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）。
Cardinality    索引中唯一值数目的估计值。
Sub_part    如果列只是被部分地编入索引，则为被编入索引的字符的数目(zhangsan)。如果整列被编入索引，则为 NULL。
Packed    指示关键字如何被压缩。如果没有被压缩，则为 NULL。
Null    如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index_type    用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment    备注。

小结

索引分为： ① 普通索引 ：针对所有字段，没有特殊的需求/规则 ② 唯一索引 : 针对唯一性的字段，仅允许出现一次空值 ③ 组合索引 (多列/多字段组合形式的索引) ④ 全文索引（varchar char text）MySQL为了优化对文本内容搜索的一种机制 ⑤ 主键索引 ：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引

创建索引： ① 在创建表的时候，直接指定index ② alter修改表结构的时候，进行add 添加index ③ 直接创建索引index PS:主键索引——》直接创建主键即可

删除索引

直接删除索引

DROP INDEX 索引名 ON 表名;

修改表的方式删除索引

ALTER TABLE 表名 DROP INDEX 索引名;

删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;

MySQL事务

MySQL 事务是一组SQL语句，它们作为一个不可分割的工作单元一起执行，要么全部执行成功，要么全部回滚到初始状态，以保持数据库的一致性和完整性。事务通常用于处理需要一致性和原子性的数据库操作。

  • 事务是一种机制，一个操作序列，包含了一组数据库操作指令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。

  • 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。

  • 事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。

  • 事务是通过事务的整体性以保证数据的一致性。

事务的四个特性（ACID）：

  • 原子性（Atomicity）： 事务是一个原子操作，要么全部执行成功，要么全部失败回滚。不存在部分执行的情况。

  • 一致性（Consistency）： 事务将数据库从一种一致性状态转移到另一种一致性状态。在事务开始和结束时，数据库的完整性约束没有被破坏。

  • 隔离性（Isolation）： 多个事务可以并发执行，但它们之间是相互隔离的，一个事务的执行不应该影响其他事务的执行。隔离性通过使用锁机制来实现。

  • 持久性（Durability）： 一旦事务提交，对数据库的修改就是永久性的，即使在系统故障的情况下也会保留。

事务的控制语句：

  • BEGIN 或 START TRANSACTION： 标志着事务的开始。

  • COMMIT 或 COMMIT WORK： 提交事务，使得对数据库的修改永久生效。

  • ROLLBACK 或 ROLLBACK WORK： 回滚事务，撤销对数据库的修改，将数据库恢复到事务开始前的状态。

  • SAVEPOINT ： 设置保存点，允许在事务中的某个位置回滚。

  • SAVEPOINT S1：使用 SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个 SAVEPOINT；“S1”代表回滚点名称。

  • ROLLBACK TO [SAVEPOINT] S1：把事务回滚到标记点。

示例：

-- 开始事务
START TRANSACTION;

-- SQL语句1
INSERT INTO table1 (column1, column2) VALUES (value1, value2);

-- SQL语句2
UPDATE table2 SET column3 = value3 WHERE column4 = value4;

-- 判断条件
IF condition THEN
    -- SQL语句3
    DELETE FROM table3 WHERE column5 = value5;
ELSE
    -- SQL语句4
    UPDATE table4 SET column6 = value6 WHERE column7 = value7;
END IF;

-- 提交事务
COMMIT;

在上述示例中，START TRANSACTION 标志着事务的开始，COMMIT 表示事务的提交。如果出现错误或满足某个条件，可以使用 ROLLBACK 进行回滚。

create database SCHOOL;
use SCHOOL;
create table info(  
id int(10) primary key not null,  
name varchar(40),  
money double  
);

insert into info values(1,'A',1000);  
insert into info values(2,'B',1000); 
select * from info;


1、测试提交事务
begin;
update info set money= money - 100 where name='A';
select * from info;

commit;
quit

mysql -u root -p
use SCHOOL;
select * from info;


2、测试回滚事务
begin;
update info set money= money + 100 where name='A';
select * from info;

rollback;
quit
mysql -u root -p
use SCHOOL;
select * from info;


3、测试多点回滚
begin;你
update info set money= money + 100 where name='A';
select * from info;
SAVEPOINT S1;
update info set money= money + 100 where name='B';
select * from info;
SAVEPOINT S2;
insert into info values(3,'C',1000);

select * from info;
ROLLBACK TO S1;
select * from info;

4、使用 set 设置控制事务
SET AUTOCOMMIT=0;                        #禁止自动提交
SET AUTOCOMMIT=1;                        #开启自动提交，Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT';        #查看Mysql中的AUTOCOMMIT值

如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit。
当然无论开启与否，begin; commit|rollback; 都是独立的事务。

事务的隔离级别：

MySQL支持多种事务隔离级别，包括读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。通过 SET TRANSACTION ISOLATION LEVEL 语句可以设置事务的隔离级别。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

不同的隔离级别提供了不同的并发控制机制，允许开发者根据应用程序的需求选择合适的级别。

  • read uncommitted（ : 读取尚未提交的数据 ：不解决脏读 允许脏读，其他事务只要修改了数未提交读）据，即使未提交，本事务也能看到修改后的数据值。也就是可能读取到其他会话中未提交事务修改的数居。

  • read committed（提交读）：读取已经提交的数据 ：可以解决脏读 只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别〈不重复读)。

  • repeatable read（可重复度）：重读读取：可以解决脏读 和 不可重复读 —mysql默认 可重复读。无论其他事务是否修改并提交了数据，在这个事务中看到的数据值始终不受其他事务影响

  • serializable：串行化：可以解决 脏读 不可重复读 和 虚读—相当于锁表 完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

mysql默认的事务处理级别是 repeatable read ，而Oracle和SQL Server是 read committed 。

事务隔离级别的作用范围

分为两种：

全局级：对所有的会话有效

会话级：只对当前的会话有效

查询和设置 MySQL 数据库的事务隔离级别

查询全局事务隔离级别：
SHOW GLOBAL VARIABLES LIKE '%isolation%';
SELECT @@global.tx_isolation;

这两条语句用于查询全局（整个 MySQL 服务器）事务隔离级别的配置。SHOW GLOBAL VARIABLES 语句将显示所有全局变量中包含 "isolation" 的配置，而 SELECT @@global.tx_isolation; 将返回当前全局事务隔离级别的值。

查询会话事务隔离级别：
SHOW SESSION VARIABLES LIKE '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

这三条语句用于查询当前会话（当前连接）的事务隔离级别的配置。SHOW SESSION VARIABLES 语句将显示所有会话变量中包含 "isolation" 的配置，而 SELECT @@session.tx_isolation; 和 SELECT @@tx_isolation; 将返回当前会话事务隔离级别的值。

设置全局事务隔离级别：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

这条语句用于设置全局事务隔离级别为 READ COMMITTED。这将影响所有后续连接到 MySQL 服务器的会话，除非它们在连接时显式设置了不同的隔离级别。

设置会话事务隔离级别：
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

这条语句用于设置当前会话的事务隔离级别为 READ COMMITTED。这只影响当前连接的会话，不会影响其他连接的隔离级别。

请注意，在设置事务隔离级别时，确保了解所选择级别的影响和适用场景，因为不同的隔离级别在性能和数据一致性方面有不同的权衡。

自动提交模式：

MySQL默认采用自动提交模式（autocommit），每个SQL语句都会自动成为一个事务，当执行成功时，自动提交事务。可以使用 SET autocommit = 0; 禁用自动提交，需要手动使用 COMMIT 或 ROLLBACK 进行事务的处理。

SET autocommit = 0; -- 禁用自动提交

-- SQL语句1
INSERT INTO table1 (column1, column2) VALUES (value1, value2);

-- SQL语句2
UPDATE table2 SET column3 = value3 WHERE column4 = value4;

-- 手动提交事务
COMMIT;

MySQL事务提供了一种强大的机制，确保了数据的一致性和可靠性，特别适用于需要执行一系列操作的复杂业务逻辑。

mysql备份

  1. 物理备份：

  • 冷备份（脱机备份）：在关闭数据库的情况下进行，通过直接打包数据库文件来实现。备份速度较快，恢复时也比较简单。

  • 热备份（联机备份）：数据库处于运行状态，依赖于数据库管理系统的一些机制。适用于需要实时备份且不能关闭数据库的场景。

  1. 逻辑备份：
  • 使用专用备份工具如mydump或mysqlhotcopy。逻辑备份会导出数据库的逻辑结构和数据，相比物理备份更容易阅读和编辑。
  1. MySQL完全备份：
  • 完全备份是对整个数据库的备份，包括数据库结构和文件结构。通过完全备份可以还原整个数据库。
  1. 增量备份：
  • 在完全备份的基础上，只备份自上次备份以来发生变化的数据。这有助于减少备份时间和存储空间的使用。
  1. 数据库完全备份分类：

  • 物理冷备份与恢复：关闭MySQL数据库，使用命令如tar直接打包数据库文件夹。

  • 其他备份方法：使用专用工具等方式进行备份。

备份的重要性： 在生产环境中，数据的安全性至关重要。任何数据的丢失都可能带来严重的后果，可能是由程序错误、人为操作错误或运算错误引起的。因此，定期进行备份并确保备份的可靠性是数据库管理的基本任务之一。

请根据你的具体需求和环境选择合适的备份方式，并定期测试和验证备份以确保其可靠性。

mysql视图

MySQL视图是一种虚拟表，它是从一个或多个基本表（或视图）的查询结果导出的表。视图只包含定义，而不包含实际的数据。它允许用户通过查询视图来访问基本表的数据，同时隐藏了实际表的复杂性和结构。

视图：优化操作+安全方案

作用场景：针对不同的人（权限身份），提供不同结果集的“表”（以表格的形式展示）

使用范围：

select * from info; #展示的部分是info表 select * from view_name; #展示的一张或多张表

功能：

  • 简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性 本质而言视图是一种select(结果集的呈现)

  • 视图适合多表连接浏览时使用，不适合增删改操作，而存储过程适合于使用较频繁的SQL语句，这样可以提高执行效率!

视图和表的区别与联系

区别：

  1. 物理存储：

  • 表是实际存储数据的对象，它包含了真实的记录和字段，并占据物理存储空间。

  • 视图是虚拟的，它不存储实际的数据记录，只是一个基于查询结果的虚拟表结构。

  1. 数据内容：

  • 表包含实际的数据记录，通过插入、更新、删除等操作可以直接修改表中的数据。

  • 视图仅包含查询语句定义的数据结果，不能直接修改视图中的数据，但可以通过修改视图所基于的表来影响视图。

  1. 更新操作：

  • 表支持所有的数据操作，包括插入、更新、删除等。

  • 视图有一些限制，例如，如果视图中包含了聚合函数、GROUP BY 子句等，可能无法直接进行更新。

  1. 实时性：

  • 表中的数据是实时的，任何时刻对表的查询都会返回当前存储的数据。

  • 视图的数据是动态生成的，即在查询时计算，因此视图的内容可以根据底层表的变化而变化。

联系：

  1. 基于关系：

  • 视图通常是基于一个或多个表的查询结果构建的，它可以是对表的子集、连接、聚合等操作的结果。

  • 表是数据库中最基本的数据存储结构。

  1. 使用场景：

  • 表用于存储和管理实际的数据，是数据库的核心。

  • 视图用于简化复杂的查询，提供更高层次的抽象，增强数据安全性，并且在某些情况下提供性能优化。

  1. 权限控制：

  • 表可以有独立的权限设置，对表的操作（如SELECT、INSERT、UPDATE、DELETE）可以进行细粒度的控制。

  • 视图的权限通常与其基础表相关联，用户对视图的权限由其对底层表的权限决定。

在实际数据库设计和应用中，表和视图通常是相辅相成的，通过合理地使用它们，可以更好地满足数据库操作的需求。

以下是MySQL中创建和使用视图的基本语法：

创建视图

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table
WHERE condition;

  • CREATE VIEW view_name: 定义并创建一个视图，view_name是视图的名称。

  • AS SELECT column1, column2, ... FROM table WHERE condition;: 定义视图的查询语句，可以包含列的选择、表的关联和条件。

示例：

CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 1;

多表创建视图

假设有两个表 A 和 B，它们都有一个共同的字段 ID。我们将创建一个视图，将这两个表连接起来。

-- 创建多表视图
CREATE VIEW AB AS
SELECT A.*, B.*
FROM A
JOIN B ON A.ID = B.ID;

上述 SQL 语句中，我们使用 CREATE VIEW 命令创建一个名为 AB 的视图。在 SELECT 语句中，使用 JOIN 命令连接了表 A 和 B，通过 ON 子句指定它们共同的字段 ID。A.* 和 B.* 表示选择了表 A 和 B 的所有列。

之后，可以通过查询视图 AB 来获取连接后的数据：

-- 查询多表视图
SELECT * FROM AB;

这样就能够通过 AB 视图方便地查询和管理表 A 和 B 的数据，而无需直接操作这两个表。当基础表的数据发生变化时，视图也会相应地反映这些变化。

查看视图与原表结构

select * from view_name；

desc 原表

使用视图

一旦创建了视图，可以像使用普通表一样查询它：

SELECT * FROM employee_view;

更新视图

可以通过更新视图来影响基础表：

CREATE OR REPLACE VIEW employee_view AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 2;

  1. CREATE OR REPLACE VIEW: 这是创建或替换视图的语法。如果视图已经存在，则使用 OR REPLACE 可以更新视图的定义，而不是创建一个新的视图。

  2. employee_view: 这是要创建或替换的视图的名称，即视图的标识符。

  3. AS: 表示接下来是视图的定义。

  4. SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 2;: 这是定义视图的查询语句。该视图名为 employee_view，选择了 employees 表中的 employee_id、first_name 和 last_name 列，且仅包含 department_id 等于 2 的行。

这个视图的作用是创建一个虚拟表，其中包含了 employees 表中 department_id 等于 2 的员工的 employee_id、first_name 和 last_name 列的数据。当查询 employee_view 视图时，实际上会执行这个 SELECT 语句，返回符合条件的数据。如果该视图已存在，执行这个语句将会更新视图的定义。

通过修改视图修改原表

在 SQL 中，通过修改视图来更新原表的数据是一种常见的操作。视图是基于表的查询结果，因此通过修改视图，你实际上是在修改其基础表的数据。然而，需要注意的是，并非所有的视图都支持直接的更新操作，这取决于视图的定义。

在 MySQL 中，一些基本的规则可以帮助你了解如何通过修改视图来修改原表：

  1. 简单视图的更新：

  • 如果视图是简单（由单个表生成的）且包含以下条件，那么通常可以直接更新视图，从而更新原表：

    • SELECT 语句中没有使用聚合函数。

    • SELECT 语句中没有使用 DISTINCT。

    • SELECT 语句中没有使用 GROUP BY 或 HAVING。

  1. 可更新性：
  • 使用 CREATE VIEW 语句创建视图时，可以使用 WITH CHECK OPTION 来确保视图是可更新的。这会阻止对视图执行不符合视图定义的修改。
  1. 多表视图的更新：

  • 多表视图的更新可能更为复杂，因为修改可能会涉及到多个表。

  • 更新多表视图时，需要确保视图的定义允许进行更新，同时要符合底层表的外键和唯一性约束。

示例（简单视图的更新）：

-- 创建一个简单视图
CREATE VIEW my_view AS
SELECT id, name, salary
FROM employees
WHERE department_id = 1 WITH CHECK OPTION;

-- 更新视图中的数据，从而更新原表
UPDATE my_view
SET salary = salary * 1.1
WHERE id = 101;

请注意，具体的更新操作可能受到数据库引擎和表的结构等因素的影响。在进行任何更新操作之前，请确保理解数据库的结构和约束，并在测试环境中进行测试。

删除视图

DROP VIEW IF EXISTS employee_view;

这将删除名为 employee_view 的视图。

使用视图可以简化复杂查询、提高安全性，并提供更高层次的抽象，使得数据库操作更为灵活。

NULL值

在数据库中，NULL是一个特殊的值，用于表示缺失的信息、未知的数据或不适用的数据。以下是有关NULL值的详细说明：

含义：

  • NULL表示一个字段或变量没有被赋予任何值，或者该值是未知的。

用途：

  • 表示缺失的数据，例如，某个字段没有被填写。

  • 表示未知的数据，例如，某个字段的值尚未确定。

  • 在关系数据库中，用于处理缺失或未知数据的三值逻辑（True、False、NULL）。

不同于空字符串或零：

  • NULL与空字符串 '' 或数字0是不同的。NULL是一个没有值的状态，而空字符串或零是具体的值。

在SQL中的表示：

  • 在SQL查询中，NULL值可以使用关键字NULL表示。

    SELECT column_name FROM table_name WHERE column_name IS NULL;

与比较运算符的交互：

  • 在SQL中，要检查一个值是否为NULL，应使用IS NULL或IS NOT NULL，而不是使用等号（=）。

    SELECT column_name FROM table_name WHERE column_name IS NULL;

处理NULL值：

  • 在SQL查询中，可以使用COALESCE、IFNULL、CASE等函数来处理NULL值，将其替换为特定的值。

  • 在一些数据库系统中，可以使用NVL、ISNULL等函数来实现类似的功能。

    SELECT column_name, COALESCE(column_name, 'N/A') AS new_column FROM table_name;

在表格中的表示：

  • 在表中，可以允许字段包含NULL值，这意味着该字段可以不填充数据。

    CREATE TABLE example_table (
  id INT,
  name VARCHAR(255),
  age INT
);

INSERT INTO example_table (id, name, age) VALUES (1, 'John', NULL);

索引和NULL：

  • 在某些情况下，对包含NULL值的列进行索引可能会导致一些查询性能的问题，因为NULL值可能会导致索引树的不连续性。

总的来说，NULL值是一种用于表示缺失或未知数据的标准数据库概念，能够提供对数据的更灵活处理。在编写SQL查询时，需要注意如何处理和比较NULL值，以确保正确的查询结果。

NULL与空值的区别

在数据库中，NULL和空值（Empty Value）是两个不同的概念，尽管它们在某些上下文中可能被混淆。以下是它们之间的主要区别：

  1. NULL：

  • 含义：NULL表示缺失、未知或不适用的数据。

  • 表示：在数据库中，NULL是一个特殊的值，用于表示字段或变量没有被赋予任何值。

  • 在SQL中的表示：在SQL查询中，NULL值可以使用关键字NULL表示。

    SELECT column_name FROM table_name WHERE column_name IS NULL;
  • 与比较运算符的交互：在SQL中，要检查一个值是否为NULL，应使用IS NULL或IS NOT NULL，而不是使用等号（=）。
SELECT column_name FROM table_name WHERE column_name IS NULL;
  1. 空值（Empty Value）：

  • 含义：空值是指字段或变量包含一个空的数据值，例如空字符串 '' 或空的日期。

  • 表示：空值是一个具体的值，通常表示某个字段确实具有一个值，只是这个值为空。

  • 在SQL中的表示：在SQL查询中，可以使用等号（=）来检查一个值是否为空。

    SELECT column_name FROM table_name WHERE column_name = '';
  • 与比较运算符的交互：空值可以使用等号进行比较，例如 = '' 表示检查一个字符串是否为空。
SELECT column_name FROM table_name WHERE column_name = '';

?总体而言，NULL通常用于表示缺失或未知的数据状态，而空值用于表示具体的空数据值。在编写SQL查询时，需要根据具体的需求选择使用IS NULL/IS NOT NULL或等号来处理NULL值和空值。

验证区别

插入一条记录，分数字段输入null，显示出来就是null 查询null值：select * from 表名 where 列名 is null;

将表中其中一条数据修改为空值'' 查询null值：select * from 表名 where 列名 is not null;

多表查询

多表查询是在关系型数据库中常见的操作，它涉及到从多个表中检索数据并将其组合以满足特定的查询需求。以下是一个详细的例子，涵盖了多表查询的不同方面：

假设我们有两个表，一个是 employees 表，包含员工的基本信息，另一个是 departments 表，包含部门的信息。这两个表可以通过共同的字段 department_id 进行关联。

  1. 创建表格：
-- 员工表
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department_id INT,
  salary DECIMAL(10, 2)
);

-- 部门表
CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);
  1. 插入数据：
-- 插入员工数据
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES
  (1, 'John', 'Doe', 1, 50000.00),
  (2, 'Jane', 'Smith', 2, 60000.00),
  (3, 'Bob', 'Johnson', 1, 55000.00);

-- 插入部门数据
INSERT INTO departments (department_id, department_name)
VALUES
  (1, 'HR'),
  (2, 'IT');
  1. 简单多表查询：
-- 查询员工及其所属部门的信息
SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

这个查询使用了 JOIN 关键字，连接了 employees 和 departments 表，通过 department_id 字段进行关联，并检索了员工的基本信息以及其所属部门的名称。

  1. 多表查询与条件：
-- 查询IT部门的员工信息
SELECT e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'IT';

在这个例子中，添加了一个条件，仅检索部门名称为 'IT' 的员工信息。

  1. 多表查询与聚合：
-- 查询每个部门的平均工资
SELECT d.department_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

在这个查询中，使用了 GROUP BY 子句，按部门分组计算了平均工资。

多表查询的复杂程度会根据业务需求而变化，但基本的关联、筛选、聚合等操作可以通过多表查询来实现。需要注意的是，多表查询的性能可能受到表的大小、索引的使用、查询语句的复杂度等因素的影响。在实际应用中，根据具体情况进行优化是很重要的。

内连接与外连接

内连接（INNER JOIN）和外连接（OUTER JOIN）是SQL中用于合并两个或多个表的不同方式。它们的主要区别在于结果集合中包含的数据类型。

内连接（INNER JOIN）：交集

  • 定义：内连接会基于两个表之间的共同列值进行合并，只返回在两个表中都存在匹配值的行。

  • 结果：仅返回符合连接条件的行，不包括不匹配的行。

  • 语法：在SQL中，内连接使用INNER JOIN关键字，并且指定连接条件，例如：

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
  • 效果：返回的结果集合中包含了两个表之间共同满足条件的数据行。

外连接（OUTER JOIN）：并集

  • 定义：外连接允许返回两个表中的匹配和不匹配的数据行。

  • 结果：包括符合连接条件的行，以及左表、右表或两个表中的所有行（根据所选的外连接类型）。

  • 语法：外连接有几种类型：

  • 左外连接（LEFT OUTER JOIN）：返回左表中所有行，以及与右表匹配的行。

  • 右外连接（RIGHT OUTER JOIN）：返回右表中所有行，以及与左表匹配的行。

  • 全外连接（FULL OUTER JOIN）：返回两个表中所有行。

  • 示例：

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.column = table2.column;
SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.column = table2.column;
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;
  • 效果：根据所选择的外连接类型，返回符合条件的行和额外的不匹配行。

综上所述，内连接仅返回两个表之间符合条件的数据行，而外连接则返回符合条件的行以及根据连接类型可能存在的不匹配行。

子查询

子查询是嵌套在主查询内部的查询，用于从数据库中检索数据或进行其他操作。子查询可以出现在 SELECT、FROM、WHERE 子句等多个位置，并且可以根据需要返回单个值、单行、或多行结果。以下是有关子查询的一些重要概念：

1. SELECT 子查询：

子查询可以用于 SELECT 语句中的列列表、FROM 子句中的表、或者 WHERE 子句中的条件。例如：

SELECT column1, (SELECT column2 FROM table2 WHERE condition) AS subquery_result
FROM table1;

2. FROM 子查询：

子查询可以用作 FROM 子句中的表，充当临时的虚拟表。这样的子查询称为派生表（Derived Table）。示例：

SELECT *
FROM (SELECT column1 FROM table1 WHERE condition) AS derived_table;

3. WHERE 子查询：

子查询经常用于 WHERE 子句中，用于过滤满足特定条件的行。例如：

SELECT column1
FROM table1
WHERE column2 = (SELECT column3 FROM table2 WHERE condition);

4. 子查询的类型：

  • 单行子查询：子查询返回单个值。用于比较运算符中，如 =, >, < 等。

  • 多行子查询：子查询返回多行结果。用于 IN, ANY, ALL 等运算符中。

  • 多列子查询：子查询返回多个列，可以与主查询中的多个列进行比较。

5. 子查询的限制：

  • 子查询的结果集大小必须符合主查询的要求。

  • 子查询应该返回标量值、单行、或者单列，以便与主查询进行比较。

示例：

单行子查询：
SELECT column1
FROM table1
WHERE column2 = (SELECT column3 FROM table2 WHERE condition);
多行子查询：
SELECT column1
FROM table1
WHERE column2 IN (SELECT column3 FROM table2 WHERE condition);
EXISTS 子查询：
SELECT column1
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE condition);

这查询语句使用了 EXISTS 关键字，该关键字用于检查子查询是否返回结果，如果返回结果，则主查询中的条件成立。

解析如下：

  1. 主查询：
SELECT column1
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE condition);

  • SELECT column1: 主查询选择表 table1 中的 column1 列。

  • FROM table1: 从表 table1 中检索数据。

  • WHERE EXISTS (SELECT 1 FROM table2 WHERE condition): 使用 EXISTS 关键字，检查子查询的结果是否存在。子查询是 SELECT 1 FROM table2 WHERE condition，它的目的是检查在 table2 中是否存在满足特定条件的任何行。

  1. 子查询：
SELECT 1 FROM table2 WHERE condition;

  • SELECT 1: 子查询选择常量值 1。

  • FROM table2: 从表 table2 中检索数据。

  • WHERE condition: 应用于 table2 的条件。

  1. 作用：

  • 如果子查询返回至少一行结果，EXISTS 返回 TRUE，主查询中的条件成立，从而选择 table1 中的 column1 列。

  • 如果子查询没有返回任何行，EXISTS 返回 FALSE，主查询中的条件不成立，主查询将不返回任何行。

这种结构通常用于检查两个表之间的关系或确保某些条件在另一个表中得到满足。

总体而言，子查询是 SQL 查询语句中的重要工具，用于构建更复杂、灵活的查询逻辑。

mysql存储引擎

  • MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式

  • mysql中常用的存储引擎：MyISAM InnoDB

  • MySQL数据库中的组件，负责执行实际的数据I/O操作 MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

MyISAM

MyISAM 是 MySQL 中的一个存储引擎，它是较早的存储引擎之一，拥有一些特定的特性和限制。以下是关于 MyISAM 存储引擎的详细说明：

特性：

  1. 表级锁定： MyISAM 使用表级锁定，这意味着在对表进行读写时，会锁定整个表，而不是行级别的锁。这可能导致在高并发情况下出现性能瓶颈，因为多个会话无法同时操作同一表。

  2. 不支持事务： MyISAM 不支持事务，因此不具备 ACID（原子性、一致性、隔离性和持久性）属性。如果需要事务支持，不建议使用 MyISAM。

  3. 表格存储结构： 数据存储在三种文件中：.frm 文件存储表结构，.MYD 存储表数据，.MYI 存储索引。

  4. 全文索引： MyISAM 提供了全文索引功能，使得在文本字段上进行全文搜索效果较好。

  5. 压缩表支持： MyISAM 支持压缩表格，可以显著减小磁盘占用空间。

  6. 读取性能较高： 对于读取密集型操作（如 SELECT），MyISAM 可能会比 InnoDB 等支持事务的引擎快，因为它不需要考虑事务的一致性。

限制：

  1. 表级锁定： 这是 MyISAM 存储引擎的一个主要限制，在高并发环境下可能导致性能问题，特别是在有大量写入操作的情况下。

  2. 不支持事务： 如果需要事务安全性和数据一致性，MyISAM 不是最佳选择，因为它不提供事务支持。

  3. 崩溃恢复： MyISAM 对数据库的崩溃恢复支持相对较弱，可能导致在数据库崩溃时数据完整性问题。

  4. 不支持外键： MyISAM 不支持外键约束，这意味着不能使用外键来保持数据的引用完整性。

  5. 不适合高并发写入操作： 由于表级锁定，对于有大量并发写入操作的应用，可能不适合使用 MyISAM，因为写入操作可能被锁定而导致阻塞。

总的来说，MyISAM 对于某些特定的应用场景可能仍然有其用武之地，尤其是对于读取频繁但写入不频繁的应用。但是，考虑到它的一些限制，特别是在需要事务支持和高并发写入的应用中，更推荐使用支持事务的存储引擎，如 InnoDB。

MyISAM 表支持 3 种不同的存储格式

静态表（Static）：

  • 静态表是 MyISAM 表的默认存储格式。在静态表中，数据的存储是固定长度的，表中的每一行都占用相同数量的空间。这有助于提高读取性能，因为可以通过直接计算偏移量来访问行。但是，静态表的缺点是不能存储可变长度的数据，例如 VARCHAR 类型的字段。

动态表（Dynamic）：

  • 动态表是一种允许可变长度数据的存储格式。在动态表中，不同的行可以占用不同的存储空间，这样就可以灵活地存储可变长度的数据类型，如 VARCHAR。由于动态表具有灵活性，但相对于静态表，可能在读取性能上稍微慢一些。

压缩表（Compressed）：

  • 压缩表是 MyISAM 表的另一种存储格式，它使用了数据压缩技术，可以显著减小表的存储空间。压缩表适用于那些对存储空间有较高要求的应用场景，但需要注意的是，在压缩表中写入和查询的性能可能相对较低，因为需要进行数据的压缩和解压缩操作。

MyISAM适用的生产场景

  1. 读取密集型应用： 如果应用以读取为主，而写入操作相对较少，那么 MyISAM 的表级锁定可能不会成为性能瓶颈，而且 MyISAM 在读取性能方面可能会比其他一些存储引擎更高效。

  2. 非关键业务数据： 对于一些非关键业务数据，例如日志记录、历史记录等，MyISAM 可以是一个较为合适的选择，因为它在这些场景下可能提供较好的性能。

  3. 全文搜索应用： MyISAM 提供了全文索引的支持，适用于需要进行全文搜索的应用场景。如果你的应用需要频繁进行全文搜索，MyISAM 的全文索引功能可能更适合你的需求。

  4. 数据仓库： 对于数据仓库或者一些只读的报表生成系统，MyISAM 的表级锁定可能不会对性能产生明显影响，而其压缩表格的特性可以减小存储空间占用。

  5. 简单的查询和报表： 如果应用主要是进行一些简单的查询和报表生成，而不需要事务支持和复杂的数据一致性，MyISAM 可能会满足需求。

MyIsam：适合于单方向的任务场景、同时并发量不高、对于事务要求不高的场景

InnoDB

InnoDB 是 MySQL 中最常用的存储引擎之一，它提供了许多高级的特性，特别适用于需要事务支持和高并发性能的应用。以下是关于 InnoDB 存储引擎的详细说明：

特性：

  1. 事务支持（ACID）：
  • InnoDB 支持事务，确保了数据库操作的原子性、一致性、隔离性和持久性（ACID 特性）。这使得 InnoDB 成为处理涉及多个操作的复杂业务逻辑的理想选择。
  1. 行级锁定：
  • InnoDB 使用行级锁定，相比于 MyISAM 的表级锁定，提供了更好的并发性能。多个事务可以同时操作同一表的不同行，减小了锁冲突的概率。
  1. 外键约束：
  • InnoDB 支持外键约束，确保表与表之间的数据完整性。这在关系数据库设计中非常重要，允许定义和维护表之间的关联关系。
  1. 崩溃恢复：
  • InnoDB 提供了强大的崩溃恢复能力，保证在数据库发生崩溃或者停电等情况下，数据库能够在重新启动后恢复到一致的状态。
  1. 自动增量主键：
  • InnoDB 支持自动增量主键，使得创建表时可以方便地定义一个具有自增特性的主键列，简化了表的设计和数据插入。
  1. 支持热备份：
  • InnoDB 支持在线热备份，可以在数据库运行的同时备份数据。这有助于提高数据库的可用性和可靠性。
  1. MVCC（多版本并发控制）：
  • InnoDB 使用 MVCC 来实现事务的隔离级别，确保一个事务在读取数据时不受其他事务的干扰。这有效地解决了不可重复读和幻读等问题。
  1. 缓冲池和自适应哈希索引：
  • InnoDB 使用缓冲池来缓存数据和索引，提高读取性能。此外，它还使用自适应哈希索引来加速一些特定的查询。

限制：

尽管 InnoDB 是一个强大的存储引擎，但也有一些限制和适用场景需要注意：

  1. 内存消耗： InnoDB 在内存上的消耗相对较高，特别是在大规模的写入操作或高并发的情况下，需要足够的内存来维护事务日志和缓冲池。

  2. 复杂性： InnoDB 的实现相对较为复杂，可能在某些情况下导致性能问题。在一些特定场景下，MyISAM 或其他存储引擎可能更为适用。

  3. 表空间管理： InnoDB 表空间的管理可能需要定期进行维护，以避免空间碎片和性能下降。

总体来说，InnoDB 是 MySQL 中最受欢迎和广泛使用的存储引擎之一，特别适用于需要事务支持和高并发性能的企业级应用。在选择存储引擎时，需根据应用的需求权衡各种存储引擎的特性和限制。

死锁

MyISAM :表级锁定 innodb :行级锁定

当两个请求分别访问/读取2行记录，同时又需要读取对方的记录数据，因为（行锁的限制）而造成了阻塞的现象

怎么解决死锁 show

企业选择存储引擎依据 业务场景如果并发量大，什么并发量大，读写的并发量大，那我们建议使用innoDB 如果单独的写入或是插入单独的查询，那我们建议使用没有INNODB

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的 需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景

? 支持的字段和数据类型

?所有引擎都支持通用的数据类型

?但不是所有的引擎都支持其它的字段类型，如二进制对象

? 锁定类型:不同的存储引擎支持不同级别的锁定

?表锁定: MyISAM 支持

?行锁定: InnoDB 支持

索引的支持

?建立索引在搜索和恢复数据库中的数据时能显著提高性能

?不同的存储弓|擎提供不同的制作索引的技术

?有些存储弓|擎根本不支持索引

事务处理的支持

?提高在向表中更新和插入信息期间的可靠性

?可根据企业业务是否要支持事务选择存储引擎

查看存储引擎操作

  • 查看系统支持的存储引擎：show engines;

  • 查看表使用的存储引擎：

    方法一：show table status from 库名 where name='表名'\G；

    方法二：use 库名; show create table 表名;

  • 修改存储引擎：

    方法一通过 alter table 修改：use 库名; alter table 表名 engine=MyISAM;

    方法二 通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务：

quit
vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB


systemctl restart mysqld.service
修改完记得重启mysql服务
#注意：此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已经存在的表不会有变更。

方法三 通过 create table 创建表时指定存储引擎：

use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;

例：mysql -u root -p
use SCHOOL;
create table hellolic (name varchar(10),age char(4)) engine=myisam;

?

存储过程

MySQL 存储过程是一组为了完成特定任务的SQL语句集合，经过预编译和优化后存储在数据库中，可以被多次调用。存储过程通常包含条件判断、循环、变量声明等结构，使其更灵活和强大。

创建存储过程：

DELIMITER //
CREATE PROCEDURE procedure_name(IN parameter1 datatype1, OUT parameter2 datatype2)
BEGIN
    -- SQL statements
END //
DELIMITER ;

  • DELIMITER //: 修改语句分隔符，因为存储过程包含多个 SQL 语句。

  • CREATE PROCEDURE procedure_name: 创建存储过程并指定名称。

  • (IN parameter1 datatype1, OUT parameter2 datatype2): 定义存储过程的参数，可以包含输入参数（IN）和输出参数（OUT）。

  • BEGIN ... END: 存储过程的主体，包含要执行的 SQL 语句。

  • //: 重新设置语句分隔符。

存储过程的调用：

CALL procedure_name(value1, @output_value);

  • CALL procedure_name: 调用存储过程。

  • value1: 提供给存储过程的输入参数的值。

  • @output_value: 存储过程的输出参数的值。

示例存储过程：

下面是一个简单的存储过程示例，它接受两个参数，计算它们的和，并将结果存储在输出参数中：

DELIMITER //
CREATE PROCEDURE CalculateSum(IN a INT, IN b INT, OUT result INT)
BEGIN
    SET result = a + b;
END //
DELIMITER ;

调用该存储过程：

CALL CalculateSum(3, 5, @sum);
SELECT @sum AS SumResult;

存储过程的变量和流程控制：

存储过程支持变量的声明和使用，以及条件判断和循环等流程控制结构，使得存储过程能够完成更复杂的任务。

DELIMITER //
CREATE PROCEDURE ExampleProcedure()
BEGIN
    DECLARE counter INT DEFAULT 0;

    WHILE counter < 5 DO
        -- SQL statements
        SET counter = counter + 1;
    END WHILE;

    IF counter = 5 THEN
        -- SQL statements
    ELSE
        -- SQL statements
    END IF;

END //
DELIMITER ;

这个例子演示了存储过程中的变量声明、循环和条件判断结构。实际存储过程的内容会根据任务的复杂性而变化。

MySQL 存储过程提供了一种在数据库层面执行逻辑的方法，可以提高性能并减少网络开销，特别是对于复杂的数据库操作。

使用存储过程的优点

MySQL 存储过程具有一些优点，使其在特定情境下成为有用的工具：

提高性能：

  • 存储过程在数据库中进行预编译和优化，因此执行速度较快。

  • 减少了大量的网络通信开销，因为整个存储过程在服务器端执行。

封装和重用：

  • 存储过程允许将一系列SQL语句封装到一个单一的单元中，使得代码更模块化和可维护。

  • 存储过程可以被多次调用，提高了代码的重用性。

安全性：

  • 存储过程可以使用参数，防止SQL注入攻击。

  • 对于存储过程的执行，可以使用数据库用户权限进行控制，提高了安全性。

减少网络流量：

  • 存储过程在数据库服务器上执行，只需将结果传送给客户端，减少了在网络上传输数据的开销。

  • 尤其对于复杂的业务逻辑，减少了客户端与数据库之间的通信次数。

事务管理：

  • 存储过程可以包含多个SQL语句，这些语句可以在一个事务中执行，保证了数据的一致性和完整性。

减少客户端负担：

  • 客户端只需调用存储过程，而无需了解存储过程内部的实现细节，减轻了客户端的负担。

提高可维护性：

  • 存储过程允许在数据库中存储和管理业务逻辑，而不是分散在应用程序代码中，提高了整个系统的可维护性。

批量处理数据：

  • 存储过程可以用于批量处理大量数据，减少了逐条记录处理的开销。

需要注意的是，存储过程并非在所有情况下都是最佳选择，使用时需要根据具体的应用场景来权衡其优点和缺点。对于简单查询和短小的业务逻辑，可能存储过程并不是必需的。

文章来源:https://blog.csdn.net/qq_51545656/article/details/135041937
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。