ホームページ > 記事 > データベース > MySQL の「L」を覚えていますか?

MySQL の「L」を覚えていますか?

Java后端技术全栈転載: 2023-08-24 15:45:26912ブラウズ

みなさん、こんにちは。今日は次のことをお話したいと思います。 MySQL のいくつかの「L」をまだ覚えていますか?

データベースの知識を学び始めると、まずさまざまな L (L は言語言語#) に触れることになります。 ## の最初の文字。

大まかに次の種類があります。

DQL は、

select

クエリステートメント (データクエリ言語) を指します。基本構造は By select 句、from 句、where などです。 <h3 data-tool="mdnice编辑器" style="font-weight: bold;font-size: 20px;line-height: 1.4;padding-top: 10px;margin-top: 10px;margin-bottom: 5px;"> <span style="display: none;"></span>#単一テーブルクエリ<span style="color: rgb(81, 81, 81);font-size: 1em;padding-left: 20px;border-left: 3px solid rgb(249, 191, 69);"></span><span style="display: none;">#<pre class="brush:php;toolbar:false;">-- 格式 SELECT selection_list /*要查询的列名称*/ FROM table_list /*要查询的表名称*/ WHERE condition /*行条件*/ GROUP BY grouping_columns /*对结果分组*/ HAVING condition /*分组后的行条件*/ ORDER BY sorting_columns /*对结果排序*/ LIMIT offset_start, row_count /*结果限定*/</pre></span> </h3> <h3 data-tool="mdnice编辑器" style="font-weight: bold;font-size: 20px;line-height: 1.4;padding-top: 10px;margin-top: 10px;margin-bottom: 5px;">#条件による指定された列のクエリ<span style="display: none;"></span><span style="color: rgb(81, 81, 81);font-size: 1em;padding-left: 20px;border-left: 3px solid rgb(249, 191, 69);">#<pre class="brush:php;toolbar:false;">SELECT t_man.Mname,t_man.Mage FROM t_man WHERE t_man.Mage > 30 -- --------------查询条件---------------------------- -- 1.逻辑运算符 -- NOT : 取反 WHERE NOT t_man.Mage > 30 -- AND : 逻辑与 WHERE t_man.Mage > 30 AND t_man.Mname LIKE &#39;_&#39; -- OR : 逻辑或 WHERE t_man.Mage > 30 OR t_man.Mname LIKE &#39;_&#39; -- 2.比较运算符 =、<>、!=、>、>=、!>、<、<=、!< -- 3.LIKE，用于模糊查询 -- % : 后面可以跟零个或多个字符 -- _ : 匹配任意单个字符 -- [ ] : 查询一定范围内的单个字符，包括两端数据 WHERE t_man.Mname LIKE &#39;[周李]%&#39; -- [^] [!]: 表示不在一定范围内的单个字符，包括两端数据 -- 4.BETWEEN between xx and xx WHERE t_man.Mage BETWEEN 30 AND 31 (等同于 t_man.Mage>=30 AND t_man.Mage<=31) not between xx and xx -- 5.is (not) null -- 在 where 子句中，需要用 is (not) null 判断空值，不能使用 = 判断空值 WHERE t_man.Mage is not null -- 6.in 多条件 WHERE t_man.Mage IN (30,31) -- 7.ALL SOME ANY -- Some 和 any 等效，all 是大于最大者，any 是小于最小者 WHERE t_man.Mage > ALL(SELECT t_man.Mage FROM t_man WHERE t_man.Mname LIKE &#39;张%&#39;) -- 8.exists 和 no exists WHERE exists (select * from t_man where t_man.Mid = 8001) -- 9.Group by 分组 SELECT AVG(t_man.Mage) FROM t_man GROUP BY t_man.Msex -- 10.Having 分组后条件 SELECT AVG(t_man.Mage) AS mk,t_man.Msex FROM t_man GROUP BY t_man.Msex HAVING mk > 30 -- 11.ORDER BY 排序 ASC,DESC SELECT * FROM t_man ORDER BY t_man.Mid ASC -- 12.DISTINCT 去重 SELECT DISTINCT(t_man.Msex) FROM t_man -- 13.LIMIT 分页（显示第一行数据） SELECT * FROM t_man LIMIT 0,1</pre></span><span style="display: none;"></span>#接続クエリ</h3> <h3 data-tool="mdnice编辑器" style="font-weight: bold;font-size: 20px;line-height: 1.4;padding-top: 10px;margin-top: 10px;margin-bottom: 5px;"> <span style="display: none;"></span><pre class="brush:php;toolbar:false;">-- 交叉连接（Cross Join），没有链接条件的表查询会出现笛卡儿积 SELECT * FROM t_man,t_dept SELECT * FROM t_man JOIN t_dept -- 内连接（inner Join 或 Join），两表中都有才显示，即两表的交集 SELECT * FROM t_man JOIN t_dept ON t_man.Mid = t_dept.Mid -- 左外连接（Left outer Join），以左边表为主，左表全部显示，没有对应的就显示空，即左并集 SELECT * FROM t_man LEFT JOIN t_dept ON t_man.Mid = t_dept.Mid -- 右外连接（Right outer Join），与左外连接相反 SELECT * FROM t_man RIGHT JOIN t_dept ON t_man.Mid = t_dept.Mid -- 全连接（Full outer Join），默认不支持，但也其他方式可以实现。 SELECT * FROM t_man LEFT JOIN t_dept ON t_man.Mid = t_dept.Mid UNION SELECT * FROM t_man RIGHT JOIN t_dept ON t_man.Mid = t_dept.Mid -- UNION ALL 与 UNION 区别是允许重复</pre><span style="color: rgb(81, 81, 81);font-size: 1em;padding-left: 20px;border-left: 3px solid rgb(249, 191, 69);">画像を使用してより視覚的に表現します:</span><span style="display: none;"></span> </h3>img<p data-tool="mdnice编辑器" style="margin-bottom: 20px;line-height: 1.8em;color: rgb(58, 58, 58);"></p> <figure data-tool="mdnice编辑器" style="margin-top: 10px;margin-bottom: 10px;display: flex;flex-direction: column;justify-content: center;align-items: center;"><img src="https://img.php.cn/upload/article/001/273/727/7177db6874c080d2958a1bc7aed9552c-0.png" alt="MySQL の「L」を覚えていますか?" ><figcaption style="max-width:90%"></figcaption>DML</figure><h2 data-tool="mdnice编辑器" style="font-weight: bold;font-size: 22px;line-height: 1.5em;margin-top: 2.2em;margin-bottom: 35px;"> <span style="display: none;">DML はデータ操作言語を指し、英語の正式名は # です。 ## データ操作言語</span>。データベース内のテーブルのレコードを更新するために使用されます。キーワード: 挿入、削除、更新などはデータに対する操作です。 <span style="display: inline-block;background-image: linear-gradient(rgb(255, 255, 255) 60%, rgb(255, 177, 27) 40%);background-position: initial;background-size: initial;background-repeat: initial;background-attachment: initial;background-origin: initial;background-clip: initial;color: rgb(81, 81, 81);padding: 2px 13px;margin-right: 3px;height: 50%;"></span> </h2> <p data-tool="mdnice编辑器" style="margin-bottom: 20px;line-height: 1.8em;color: rgb(58, 58, 58);">挿入<code style='font-size: 14px;border-radius: 4px;font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(155, 110, 35);background-color: rgb(255, 245, 227);padding: 3px;margin: 3px;'>

##挿入挿入##

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些列
insert into 表 values (值1,值2,值3...);  //向表中插入所有列

列の順序は、挿入された値の順序と一致します

列名のタイプは挿入された値と一致している必要があります
値を挿入するときは、最大長を超えることはできません

#値が文字列である場合、または日付を引用符で囲む必要がある場合''

#update

アップデート更新データ
update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;
注意：

列名的类型与修改的值要一致

修改值的时候不能超过最大长度

值如果是字符串或者日期要加’’

delete

delete删除数据
delete from 表名 [where 条件];
注意：

删除表中所有记录使用delete from 表名；还是truncate table 表名；？删除方式：delete一条一条的删除，不清空auto_increment（自增）记录数；truncate 直接删除表，重新建表，auto_increment讲置为0，重新开始。事务方面：delete删除的数据，如果在一个事务中可以找回；truncate 删除的数据找不回来。

DCL

DCL是数据控制语言（Data Control Language）的简称，它包含诸如GRANT之类的命令，并且主要涉及数据库系统的权限，权限和其他控件。
GRANT ：允许用户访问数据库的权限

REVOKE：撤消用户使用GRANT命令赋予的访问权限

DCL 语句主要是DBA 用来管理系统中的对象权限时所使用，一般的开发人员很少使用。

案例演示

下面通过一个例子来简单说明一下。创建一个数据库用户plf，具有对plf数据库中所有表的SELECT/INSERT 权限：
mysql> grant select,insert on plf.* to &#39;plf&#39;@&#39;%&#39; identified by &#39;123456&#39;;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye



[root@mysql ~]# mysql -uplf -p123456 -h 192.168.3.100
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.37 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type &#39;help;&#39; or &#39;\h&#39; for help. Type &#39;\c&#39; to clear the current input statement.

mysql> use mysql;
ERROR 1044 (42000): Access denied for user &#39;plf&#39;@&#39;%&#39; to database &#39;mysql&#39;
mysql> use plf
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
由于权限变更，需要将 plf 的权限变更，收回 INSERT，只能对数据进行 SELECT 操作,这时我们需要使用root账户进行上述操作：
mysql> revoke insert on plf.* from &#39;plf&#39;@&#39;%&#39;;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye





[root@mysql ~]# mysql -uplf -p123456 -h 192.168.3.100
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.37 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type &#39;help;&#39; or &#39;\h&#39; for help. Type &#39;\c&#39; to clear the current input statement.

mysql> use plf
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> show tables;
+---------------+
| Tables_in_plf |
+---------------+
| dept          |
| emp           |
| hk_info       |
| log_info      |
| user_info     |
+---------------+
5 rows in set (0.00 sec)

mysql> insert into dept values(7,&#39;plf&#39;);
ERROR 1142 (42000): INSERT command denied to user &#39;plf&#39;@&#39;192.168.3.100&#39; for table &#39;dept&#39;
mysql> select*from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
|      5 | fin      |
+--------+----------+
4 rows in set (0.00 sec)
以上例子中的grant和revoke分别授出和收回了用户plf的部分权限，达到了我们的目的。

DDL

DDL是数据定义语言（Data Definition Language）的简称，它处理数据库schemas和描述数据应如何驻留在数据库中。

CREATE：创建数据库及其对象（如表，索引，视图，存储过程，函数和触发器）

ALTER：改变现有数据库的结构

DROP：从数据库中删除对象

TRUNCATE：从表中删除所有记录，包括为记录分配的所有空间都将被删除

COMMENT：添加注释

RENAME：重命名对象

常用命令如下：
# 建表
CREATE TABLE sicimike  (
  id int(4) primary key auto_increment COMMENT &#39;主键ID&#39;,
  name varchar(10) unique,
  age int(3) default 0,
  identity_card varchar(18)
  # PRIMARY KEY (id) // 也可以通过这种方式设置主键
  # UNIQUE KEY (name) // 也可以通过这种方式设置唯一键
  # key/index (identity_card, col1...) // 也可以通过这种方式创建索引
) ENGINE = InnoDB;

# 设置主键
alter table sicimike add primary key(id);

# 删除主键
alter table sicimike drop primary key;

# 设置唯一键
alter table sicimike add unique key(column_name);

# 删除唯一键
alter table sicimike drop index column_name;

# 创建索引
alter table sicimike add [unique/fulltext/spatial] index/key index_name (identity_card[(len)] [asc/desc])[using btree/hash]
create [unique/fulltext/spatial] index index_name on sicimike(identity_card[(len)] [asc/desc])[using btree/hash]
example： alter table sicimike add index idx_na(name, age);

# 删除索引
alter table sicimike drop key/index identity_card;
drop index index_name on sicimike;

# 查看索引
show index from sicimike;

# 查看列
desc sicimike;

# 新增列
alter table sicimike add column column_name varchar(30);

# 删除列
alter table sicimike drop column column_name;

# 修改列名
alter table sicimike change column_name new_name varchar(30);

# 修改列属性
alter table sicimike modify column_name varchar(22);

# 查看建表信息
show create table sicimike;

# 添加表注释
alter table sicimike comment &#39;表注释&#39;;

# 添加字段注释
alter table sicimike modify column column_name varchar(10) comment &#39;姓名&#39;;
TCL

TCL是事务控制语言（Transaction Control Language）的简称，用于处理数据库中的事务

COMMIT: トランザクションをコミットします。

ROLLBACK: エラーが発生した場合にトランザクションをロールバックします

トランザクションの概念

トランザクションは、1 つ以上の SQL で構成されるトランザクションです。ステートメント実行ユニットは分割不可能な実行ユニットであり、すべての実行が成功するか、すべての実行が失敗します。いずれかの実行に失敗すると、トランザクションは開始前の状態にロールバックされます。トランザクションには 4 つの属性 (ACID) があります: アトミック性、一貫性、分離性、耐久性

アトミシティ (原子性): トランザクションが分割できない全体であることを意味します。すべての操作が実行されるか、何も実行されないかのどちらかです。実行されました。

整合性: トランザクションは、データベースをある整合性状態から別の整合性状態に変更する必要があります。

分離: トランザクションの実行が他のトランザクションによって干渉されないこと、つまり、トランザクション内の操作と使用されるデータが他の同時トランザクションから分離されていることを指します。同時実行性実行中の個々のトランザクションは、事前に相互に干渉することはできません。これは、同時実行性の高い環境では特に重要であり、トランザクション分離の程度は、選択した分離レベルに大きく関係します。

永続性: トランザクションがコミットされると、データベース内のデータへの変更は永続的になります。

注:

トランザクションはデータの追加、削除、変更操作にのみ有効です。テーブル定義やテーブル構造の変更にはトランザクションの概念はありません。、など。

#ストレージエンジンとトランザクション
ストレージエンジンは、データストレージで使用されるさまざまなテクノロジを指します。すべてではありません。ストレージエンジンはトランザクションをサポートします。Mysql で一般的に使用されるストレージエンジンのうち、トランザクションをサポートしているのは InnoDB だけであり、MyIsam と Memory はトランザクションをサポートしていません。
Mysql でストレージエンジンを表示する方法:
エンジンを表示;
在这里插入图片描述

事务的分类

事务有隐式事务和显示事务之分，区别在于有没有显式的事务开启和结束标记。

默认的INSERT、UPDATE、DELETE语句都是会自动提交的，事务提交后是不能够回滚的。这跟数据库的autocommit属性的设置有关，默认情况下都是开启自动提交的。

可通过SHOW VARIABLES;查看数据库的属性设置。

img
隐式事务举例：默认情况下的增删改
DELETE FROM table_a WHERE id = 1;
显示事务：在开启显示事务之前一定要关闭事务的自动提交
#关闭事务的自动提交
SET autocommit = 0;#该语句仅对当前事务有效
显示事务举例：具有明显的事物开启和结束标记，事务结束要么提交要么回滚
#开启事务
SET autocommit = 0;#有了这句代码就会开启事务
START TRANSACTION;#这句代码可省略
#事务的执行语句
UPDATE account
SET balance = 500
WHERE
 username = &#39;张无忌&#39;;
UPDATE account
SET balance = 1500
WHERE
 username = &#39;赵敏&#39;;
#事务提交
COMMIT;
#ROLLBACK;#事务回滚
事务的隔离级别

对于并发运行的多个事务，当这些事务访问相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

脏读：对于两个事务T1、T2，T1读取了已经被T2更新但还没有提交的数据之后，T2回滚了，则T1读取的数据就是无效的，这就是脏读。

不可重复读：对于两个事务T1、T2，T1读取数据后，T2更新了T1读取的数据，此时T1再次读取该数据时会和前一次不一样，这称为不可重复读。

幻读：对于两个事物T1、T2，T1读取一个表中的数据后，T2向该表中插入了新的数据，T1再次读取这个表时发现多出了新的数据，这称为幻读。

事务的隔离级别：一个事务与其他事务隔离的程度称为隔离级别，数据库中有多种隔离级别，不同的隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。

数据库的4种事务隔离级别：

注意：

可重复读是行锁，行锁只能保证事务中被操作的数据行不能被其他事务修改，但是无法保证其他事务向该表中插入新数据，因此无法解决幻读的问题；串行化则是表锁，表锁是说在一个事务中一旦操作了某个表，则在该事务提交或回滚之前所有对该表的增删改查操作都将等待，因此可以解决幻读的问题，但是效率低下。

Oracle支持READ COMMITTED和SERIALIZABLE两种隔离级别，默认为READ COMMITTED；Mysql支持上述4中事务隔离级别，默认为REPEATABLE READ(可重复读)

查看隔离级别：SELECT @@tx_isolation;

设置隔离级别：
#设置当前连接的隔离级别:SESSION可以省略
SET [SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
#设置全局的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;
事务的保存点

事务的保存点(SAVEPIONT)其实就是指定事务可回滚到的一个节点，有时我们并不一定要将整个事务的所有操作都进行回滚，而是只回滚该事务中的某一部分操作，这个时候就需要用到事务的保存点，一个事务中可以有多个保存点，多个保存点间根据名称区分。
SET autocommit = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 2;
SAVEPOINT a; #设置一个保存点
DELETE FROM account WHERE id = 3;
ROLLBACK TO a; #回滚到a保存点
好了，以上便是今天分享的MySQL中的各种L（Language）。