数据库操作基础

目录

1.数据库与数据表的概念

2.MySql的内部基础结构

 3.MySql的基本操作

3.1 MySql对数据库的基本操作

3.1.1 创建数据库

3.1.2 删除数据库

3.1.3 查看数据库

3.1.4 使用数据库

3.2 MySQL数据类型

4.SQL的常用语法

4.1 SQL的分类

 4.2 SQL中对表操作的常用语法

4.2.1 创建表

4.2.2 删除表

4.2.3 插入数据

4.2.3.1 按照指定列插入

4.2.3.2 为所有列插入数据：

 4.2.3.3 使用一个表的数据填充另一个表

4.2.4 查询数据

4.2.4.1 语法结构

4.2.4.2 SELECT结合子句的作用解释：

1）WHERE

2）GROUP BY

3）HAVING<条件>

4）ORDER BY

5）LIMIT

6）DISTINCT

4.2.5 修改数据 

 4.2.5 删除数据

5.MySQL事务

5.1 事务特性

5.2 事务执行流程

 未完待续，小曹连载！

---

1.数据库与数据表的概念

数据库：DataBase，是一个用来存储和管理数据的仓库。在数据库中的数据都以二维表的形式存在。

数据表：数据库中用来管理和存储数据的形式，由行和列组成的二维表

2.MySql的内部基础结构

数据库管理系统根据架构可分为两类：一类是基于共享文件系统的数据库管理系统，例如 Microsoft Access、FileMaker、SQLite，主要用于桌面应用，不适合用于高端或更关键的应用；另一类是基于客户端-服务器的数据库管理系统，例如 MySQL、Oracle和SQL Server等数据库。

主要以MySQl为例，MySql的内部基础结构如下

其内部结构主要可以分为三层，MySQL Server层、存储引擎层和文件系统层。

MySQL Server层：又分为连接层和SQL层；应用程序通过接口连接MySql时，最先处理的是连接  层，connection pool属于连接层，他的作用是将用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求缓存下来，下次可以直接使用已经建立好的连接，提升服务器性能；SQL层是MySql的核心，MySql的核心服务都在该层实现，所以该层包括多个部分，如Management&Services&Utilities、SQL Interface、Parser、Optimize、Cache&Buffers。

存储引擎层:主要负责MySql中数据的存储与提取。存储引擎层是MySql区别其他数据库最核心的一点。Pluggable Storage Engines属于存储引擎层

文件系统层:将数据库的数据存储在操作系统的文件系统上，并完成与存储引擎的交互

 3.MySql的基本操作

3.1 MySql对数据库的基本操作

对数据库的基本操作主要就是三种：创建、删除、查看以及使用

3.1.1 创建数据库

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET<字符集名>]
[[DEAFULT] COLLATE <校对规则名>]

注意：MySql不允许在同一系统下创建两个相同名称的数据库

3.1.2 删除数据库

DROP DATABASE [IF EXISTS] <数据库名>

3.1.3 查看数据库

SHOW DATABASE [LIKE '数据库名']

3.1.4 使用数据库

USE DATABASENAME

3.2 MySQL数据类型

MYSQL数据类型主要分为数值型、日期/时间类型、字符串类型、二进制类型，主要对应如下图

4.SQL的常用语法

SQL：Structured Query Language 结构化查询语言

4.1 SQL的分类

• 数据定义语言(DDL)：创建或删除数据库以及表，包含DROP、CREATE、ALTER
• 数据操作语言(DML)：对表进行变更操作，包含INSERT、UPDATE、DELETE
• 数据查询语言(DQL)：查询表中的记录，包含INSERT
• 数据控制语言(DCL)：确认或取消对数据库中数据进行变更，还可以对数据库中的用户授权，包含GRANT、REVOKE、COMMIT、ROLLBACK

 4.2 SQL中对表操作的常用语法

书写规则

1.语句要以;结尾

2.关键字不区分大小写，但是按照阿里编程规范：关键字大写，数据库名、表名、列名小写

3.字符串以及日期使用英文单引号括起来，如'abc'、'2023-07-19'

4.数字不需要符号标识

4.2.1 创建表

CREATE TABLE table_name(
    column1 datatype [约束条件],
    column1 datatype [约束条件],
    column1 datatype [约束条件],
    ......
    columnN datatype [约束条件],
    PRIMARY KEY(one or more columns)
);

在创建表时，可以在定义完列以及列的类型后，对某个字段还可进行其他的定义。来对该字段进行约束，比如

NOT NULL：约束在插入数据时该字段不能为空

AUTO_INCREMENT：将某个字段的值设置为自动增长

DEFAULT：设置字段的默认值

PRIMARY KEY：指明表的主键

4.2.2 删除表

DROP TABLE table_name;

4.2.3 插入数据

4.2.3.1 按照指定列插入

在为列中添加数据数据时（为指定列添加数据），会使用的语法为:

INSERT INTO table_name (column1,column2,...columnN)
VALUES (value1,value2,...valueN)

设置需要插入数据的列名以及每列所对应的值即可

4.2.3.2 为所有列插入数据：

INSERT INTO table_name VALUES (value1,value2,...valueN)

但是需要注意是用此方法插入数据时需要保证插入的值的顺序和表中个列的顺序相等

了解具体语法：

INSERT
[TOP (EXPRESSION) [PERCENT]]
[INTO]
{<Object>}
{[(column_list)] [<OUTPUT Caulse>]
  {VALUES({DEFAULT|NULL|EXPRESSION[,...N]})}}

其中第二行指定将插入的随机行的数目；第四行表示表名；<OUTPUT Caulse>将插入行作为插入操作的一部分返回，这种方法列出了表的列名，顺序与原表的结构不一致，可以按照自己的设定增加行

 4.2.3.3 使用一个表的数据填充另一个表

INSERT INTO temp_table [(column1,column2,...columnN)]
SELECT column1,column2,...columnN
FROM sec_table_name
[WHERE condition]

select语句从sec_table_name表中获取数据，使用insert语句填充到temp_table表中

 在MYSQL中使用下列语法来填充

CREATE TABLE table (SELECT * FROM table2)

4.2.4 查询数据

4.2.4.1 语法结构

使用select查询表中符合条件的数据，查询出的表是一张临时表，查询语句的所有语法如下：

SELECT [ALL|DISTINCT]

[TOP 表达式 [PERCENT][WITH TIES]]

<列名表>

[INTO 新表名]

[FROM {<表名列表>}[,...n]]

[WHERE <条件>]

[GROUP BY [ALL] 分组列表[,...n]]

[WITH {CUBE|ROLLUP}]

[HAVING<条件>]

[ORDER BY 排序列表[ASC|DESC]]

[COMPUTE {{AVG|COUNT|MAX|MIN|SUM}(表达式)}[,...n]]

[BY 表达式[,...n]]

在使用过程中，根据某个条件查找某张表中地某些数据，简单的查找语句为：

SELECT column1,olumn2,...columnN
FROM table
WHERE conditions;

在对所有列进行查询时，column1,olumn2,...columnN可以被*替换，代表该临时表显示原表的所有列；如果不使用where语句，则代表没有筛选条件，原表中地所有数据都会被选取

4.2.4.2 SELECT结合子句的作用解释：

1）WHERE

用来指明筛选条件，只有满足条件的数据才会被选取。在条件中主要使用>、<、=、BETWEEN等比较运算符；使用AND、OR等来指定多个条件；使用LIKE进行模糊匹配查询。

与LIKE进行模糊匹配查询的通配符：

通配符	说明
百分号(%)	代表0个、一个或多个任意的字符
下划线(_)	代表单个字符或者数字
方括号([])	表示匹配方括号中的任意一个字符。 例如，LIKE 'a[bc]d' 可以匹配"abd"、"acd"等。也可以使用连字符(-)表示范围。例如，[0-9]表示从0-9的任意数字
脱字符（^）	表示不匹配脱字符后面的字符。 例如，LIKE 'a[^bc]d' 可以匹配"aed"、"afd"等，但不匹配"abd"、"acd"等。

2）GROUP BY

结合聚合函数，根据一个或多个列对结果集进行分组。借助该子句，可以对结果集进行再加工。

方法	作用
SUM()	对指定字段的值进行求和
COUNT()	计算某个分组内数据的条数
AVG()	对指定字段的值求平均值
MIN()	对指定字段求最小值
MAX()	对指定字段求最大值

3）HAVING<条件>

通常和 GROUP BY 子句联合使用，可不写，对产生的结果集进行条件筛选。

4）ORDER BY

按照某个字段对结果集进行排序。DESC按照降序排列，ASC按照升序排列。默认为升序排列

5）LIMIT

限制查询结果返回的条数。LIMIT 是 MySQL 中的一个特殊关键字，用于指定查询结果从哪条记录开始显示，一共显示多少条记录。

6）DISTINCT

去除结果集中所有重复的记录，仅保留唯一的一条记录。

4.2.5 修改数据 

UPDATE table_name
SET column1 = value,column2 = value,...columnN = value
WHERE [condition]

修改数据表中现有的记录，通常与where子句一起使用，筛选满足条件的记录

 4.2.5 删除数据

DELETE FROM table_name
WHERE [condition]

DELETE语句用于删除数据表中现有的记录。通常情况下，DELETE命令与WHERE子句一起使用，以删除满足条件的记录；如果不使用WHERE子句，则会删除表中的所有记录。

5.MySQL事务

数据库的事务(Transaction)是一组数据库操作命令，这些操作作为一个整体向系统提交或撤销操作命令，即要么全部执行，要么全部不执行。

5.1 事务特性

事务具有4个特性，原子性(Atomicity)、一致性(Consistency)、隔离性(Isolaction)、持久性(Durability)、通常称为ACID。

1. 原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部不执行，不会出现部分执行的情况。

2. 一致性（Consistency）：事务执行前后，数据库的状态必须保持一致。即事务执行后，数据库从一个一致的状态转变为另一个一致的状态。

3. 隔离性（Isolation）：多个事务同时执行时，每个事务都应该感觉不到其他事务的存在，即每个事务的操作应该与其他事务隔离开来，互不干扰。

4. 持久性（Durability）：事务一旦提交，其所做的修改将永久保存在数据库中，即使发生系统故障也不会丢失。

5.2 事务执行流程

事务的基本操作是：

1. 开始事务（BEGIN或START TRANSACTION）
2. 提交事务（COMMIT）
3. 回滚事务（ROLLBACK）

BEGIN;-- 事务开始

SQL语句

COMMIT; -- 事务提交

ROLLBACK; --事务回滚

开始事务标志着一个新的事务的开始，提交事务表示事务的结束并将所有的修改保存到数据库中，回滚事务则撤销当前事务的所有操作，将数据库恢复到事务开始之前的状态。

MySQL中支持事务的存储引擎有InnoDB和BDB，其中InnoDB存储引擎通过UNDO Log回滚日志和REDO Log重做日志实现。MyISAM存储引擎不支持事务

 未完待续，小曹连载！