什么是数据库?
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建,访问,管理,搜索和复制所保存的数据。
我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。
所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
数据库模型结构
数据库模型根据数据模型来划分,而数据库管理系统(Database Management System)有自己的数据结构,目前比较成熟的在数据库系统中的数据模型有:层次模型、网状模型和关系模型。它们之间的根本区别在于数据之间联系的表示方式不同(即记录型之间的联系方式不同)。
层次模型以“树结构”表示数据之间的联系。
网状模型是以“图结构”来表示数据之间的联系。
关系模型是用“二维表”(或称为关系)来表示数据之间的联系的。
MySQL数据类型
MySQL数据类型
数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 Bytes | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 Bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 Bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 Bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 Bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小 |
日期和时间类型
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 |
TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值,时间戳 |
字符串类型
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
DDL
MySQL 创建数据表
创建 MySQL 数据表需要以下信息:
- 表名
- 表字段名
- 定义每个表字段的数据类型
语法
以下为创建 MySQL 数据表的 SQL 通用语法:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
参数说明
table_name
是你要创建的表的名称。column1
,column2
, ... 是表中的列名。datatype
是每个列的数据类型。
以下是一个具体的实例,创建一个用户表 users:
实例
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
birthdate DATE,
);
实例解析
id
: 用户 id,整数类型,自增长,作为主键。username
: 用户名,变长字符串,不允许为空。email
: 用户邮箱,变长字符串,不允许为空。birthdate
: 用户的生日,日期类型。
MySQL 删除数据表
MySQL中删除数据表是非常容易操作的,但是你在进行删除表操作时要非常小心,因为执行删除命令后所有数据都会消失。
语法
以下为删除 MySQL 数据表的通用语法:
DROP TABLE table_name ; -- 直接删除表,不检查是否存在
或
DROP TABLE [IF EXISTS] table_name;
参数说明
table_name
是要删除的表的名称。IF EXISTS
是一个可选的子句,表示如果表存在才执行删除操作,避免因为表不存在而引发错误。
DML
MySQL 插入数据
MySQL 表中使用 INSERT INTO 语句来插入数据。
你可以通过 mysql> 命令提示窗口中向数据表中插入数据,或者通过PHP脚本来插入数据。
语法
以下为向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"。
MySQL 查询数据
MySQL 数据库使用 SELECT 语句来查询数据。
你可以通过 mysql> 命令提示窗口中在数据库中查询数据,或者通过 PHP 脚本来查询数据。
语法
以下为在 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
是一个可选的子句,用于限制返回的行数。
MySQL UPDATE 更新
如果我们需要修改或更新 MySQL 中的数据,我们可以使用 UPDATE 命令来操作。
语法
以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
参数说明
table_name
是你要更新数据的表的名称。column1
,column2
, ... 是你要更新的列的名称。value1
,value2
, ... 是新的值,用于替换旧的值。WHERE condition
是一个可选的子句,用于指定更新的行。如果省略WHERE
子句,将更新表中的所有行。
MySQL DELETE 语句
你可以使用 DELETE FROM 命令来删除 MySQL 数据表中的记录。
你可以在 mysql> 命令提示符或 PHP 脚本中执行该命令。
语法
以下是 DELETE 语句从 MySQL 数据表中删除数据的通用语法:
DELETE FROM table_name
WHERE condition;
参数说明:
table_name
是你要删除数据的表的名称。WHERE condition
是一个可选的子句,用于指定删除的行。如果省略WHERE
子句,将删除表中的所有行。
DQL
MySQL WHERE 子句
我们知道从 MySQL 表中使用 SELECT 语句来读取数据。
如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句中。
WHERE 子句用于在 MySQL 中过滤查询结果,只返回满足特定条件的行。
语法
以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
参数说明
column1
,column2
, ... 是你要选择的列的名称,如果使用*
表示选择所有列。table_name
是你要从中查询数据的表的名称。WHERE condition
是用于指定过滤条件的子句。
操作符列表
操作符 | 描述 | 实例 |
---|---|---|
= | 等号,检测两个值是否相等,如果相等返回true | (A = B) 返回false。 |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true | (A != B) 返回 true。 |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
<= | 小于等于号,检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
MySQL LIKE 子句
我们知道在 MySQL 中使用 SELECT 命令来读取数据, 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。
WHERE 子句中可以使用等号 = 来设定获取数据的条件,如 "runoob_author = 'RUNOOB.COM'"。
但是有时候我们需要获取 runoob_author 字段含有 "COM" 字符的所有记录,这时我们就需要在 WHERE 子句中使用 LIKE 子句。
LIKE 子句是在 MySQL 中用于在 WHERE 子句中进行模糊匹配的关键字。它通常与通配符一起使用,用于搜索符合某种模式的字符串。
LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。
语法
以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法:
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;
参数说明
column1
,column2
, ... 是你要选择的列的名称,如果使用*
表示选择所有列。table_name
是你要从中查询数据的表的名称。column_name
是你要应用LIKE
子句的列的名称。pattern
是用于匹配的模式,可以包含通配符。
MySQL ORDER BY(排序) 语句
我们知道从 MySQL 表中使用 SELECT 语句来读取数据。
如果我们需要对读取的数据进行排序,我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果。
MySQL ORDER BY(排序) 语句可以按照一个或多个列的值进行升序(ASC)或降序(DESC)排序。
语法
以下是 SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
参数说明
column1
,column2
, ... 是你要选择的列的名称,如果使用*
表示选择所有列。table_name
是你要从中查询数据的表的名称。ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...
是用于指定排序顺序的子句。ASC
表示升序(默认),DESC
表示降序。
MySQL GROUP BY 语句
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具,尤其在处理大量数据时,它能够提供有用的汇总信息。
GROUP BY 语法
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;
参数说明
column1
:指定分组的列。aggregate_function(column2)
:对分组后的每个组执行的聚合函数。table_name
:要查询的表名。condition
:可选,用于筛选结果的条件。
多表查询
你可以在 SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类:
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
- LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
- RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
INNER JOIN
INNER JOIN 返回两个表中满足连接条件的匹配行,以下是 INNER JOIN 语句的基本语法:
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
参数说明
column1
,column2
, ... 是你要选择的列的名称,如果使用*
表示选择所有列。table1
,table2
是要连接的两个表的名称。table1.column_name = table2.column_name
是连接条件,指定了两个表中用于匹配的列。
LEFT JOIN
LEFT JOIN 返回左表的所有行,并包括右表中匹配的行,如果右表中没有匹配的行,将返回 NULL 值,以下是 LEFT JOIN 语句的基本语法:
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
RIGHT JOIN
RIGHT JOIN 返回右表的所有行,并包括左表中匹配的行,如果左表中没有匹配的行,将返回 NULL 值,以下是 RIGHT JOIN 语句的基本语法::
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
事务
特征
一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。
-
原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
-
一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-
隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
-
持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
注意
在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。