MySQL基础

MySQL数据库

数据库

数据库（软件）（人们通常用数据库这个术语代表使用的数据库软件。确切的说，数据库软件应该称为DBMS，即数据库管理系统）实现把用户意义下的抽象逻辑数据处理转换成计算机中的具体的物理数据的处理软件。
数据库（Database）：保存“某种有组织的方式储存的数据”的容器。
DBMS是访问和操控Database的软件。
DBMS分为关系型和非关系型。MySQL属于关系型DBMS，具有开源、易于维护、使用方便等优点。

SQL语言

SQL（Structured Query Language）即结构查询语言，是关系型DBMS中用户用于访问和操控数据库的高级非编程化语言

SQL通用语法：

  1. SQL语句可单行书写或多行书写，以分号结尾
  2. SQL语句可使用空格/缩进来增强语句可读性
  3. MySQL数据库的SQL语句不区分大小写，关键字建议用大写
  4. 注释：
单行注释：--注释内容或注释内容（MySQL特有）
多行注释：/*注释内容*/

SQL分为数据定义语言（DDL）、数据操作语言（DML）、数据查询语言（DQL）、数据控制语言（DCL）。

数据定义语言（DDL）

用于创建数据和修改数据库对象（如表和索引）的结构。

  1. DDL——数据库操作
  • 查询：
查询所有数据库——SHOW DATABASES;
查询当前数据库——SELECT DATABASE();
  • 创建：
​CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
  • 删除：
DROP DATABASE [IF EXISTS] 数据库名;
  • 使用：
USE 数据库名;
  1. DDL——表操作——查询
  • 查询当前数据库所有的表：
SHOW TABLES;
  • 查询表结构：
DESC 表名;
  • 查询指定表的建表语句：
SHOW CREATE TABLE 表名;
  1. DDL——表操作——创建
CREATE TABLE 表名 
(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段2注释],
	字段3 字段3类型 [COMMENT 字段3注释],
	~
	字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];
  1. DDL——表操作——数据类型
    数据类型主要分为三类：数字类型、字符串类型、日期和时间类型。
  • 数值类型
类型 大小 有符号（SIGNED）范围 无符号（UNSIGNED）范围 描述
TINYINT 1byte (-128,127) (0,255) 小整数值
SMALLINT 2byte (-32768,32767) (0,65535) 大整数值
MEDIUMINT 3byte (-8388608,8388607) (0,16777215) 大整数值
INT或INTEGER 4byte (-2147483648,2147483647) (0,4294967295) 大整数值
BIGINT 8byte (-263,263-1) (0,264-1) 极大整数值
FLOAT 4byte (-3.402823466E+38,3.402823466351E+38) 0和(1.175494351E-38,3.402823466E+38) 单精度浮点数值
DOUBLE 8byte 0和 双精度浮点数值
DECIMAL 依赖于m(精度)和(标度)的值 依赖于m(精度)和(标度)的值 小数值(精确定点数)
  • 字符串类型

类型 大小 描述

CHAR 0~255 定长字符串
VARCHAR 0~65535 变长字符串
TINYBLOB 0~255 不超过255个字符串的二进制数据
TINYTEXT 0~255 短文本字符串
BLOB 0~65535 二进制形式的长文本数据
TEXT 0~65535 长文本数据
MEDIUMBLOB 0~16777215 二进制形式的中等长度文本数据
MEDIUMTEXT 0~16777215 中等长度文本数据
LONGBLOB 0~4294967295 二进制形式的极大文本数据
LONGTEXT 0~4294967395 极大文本数据
  • 日期和时间类型
类型 大小 范围 格式 描述
DATE 3 1000-01-01至9999-12-31 YYY-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至2038-01-19 03:14:07 YYYY:MM:DD HH:MM:SS

数据操作语言（DML）

Data Manipulation Language包括INSERT、UPDATE、DELETE、等语句，对于数据库中的数据进行新增、修改和删除等操作。
添加数据（insert）、修改数据（update）、删除数据（delete）

  1. DML——添加数据
  • 给指定字段添加数据：
INSERT INTO 表名 (字段名1,字段名2,···) VALUES (值1,值2,···);
  • 给全部字段添加数据：
INSERT INTO 表名 VALUES (值1,值2,···);
  • 批量添加数据：
INSERT INTO 表名 (字段名1,字段名2,···) VALUES (值1,值2,···),(值1,值2,···), (值1,值2,···);
INSERT INTO 表名 VALUES (值1,值2,···),(值1,值2,···), (值1,值2,···);
  1. DML——修改数据
UPDATE 表名 SET 字段名1=值1,字段名2=值2,···[WHERE 条件];
  1. DML——删除数据
DELETE FROM 表名 [WHERE 条件];

数据查询语言（DQL）

数据查询语言，DQL（Data Query Language），用于对数据进行查询操作，查询数据库中表的记录。,日常生活中，查询的行为比增删改操作更多。其中分组查询常配合聚合函数使用，所以在讲解分组查询之前先讲解聚合函数。
查询关键词：SELECT

  1. DQL——语法：编写顺序
SELECT 字段列表 基本查询
FROM 表名列表 基本查询
WHERE 条件列表 条件查询
GROUP BY 分组字段列表 聚合函数
HAVING 分组后条件列表 分组查询
ORDER 排序字段列表 排序查询
LIMIT 分页参数 分页查询
  1. DQL——基本查询
  • 查询多个字段：
SELECT 字段1,字段2,字段3,.··· FROM 表名;
SELECT * FROM 表名;
  • 设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2] ··· FROM 表名;
  • 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
  1. DQL——条件查询
  • 语法：
SELECT 字段列表 FROM 表名 [WHERE 条件列表];
  • 条件：
比较运算符 功能
> 大于
>= 大于等于
< 小于
<= 小于等于
= 等于
<>或!= 不等于
BETWEEN...AND... 在某个范围之内（含最小、最大值）
IN(...) 在in之后的列表中的值，多选一
LIKE占位符 模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL 是NULL
逻辑运算符 功能
AND或&& 并且（多个条件同时成立）
OR或“” 或者（多个条件任意一个成立）
NOT或! 非，不是
  1. DQL——聚合函数

将一列数据作为一个总体，进行纵向计算。

  • 常见聚合函数
函数 功能
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
  • 语法
SELECT 聚合函数(字段列表) FROM 表名;
  1. DQL——分组查询
  • 语法：
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
  • WHERE和HAVING区别：

执行时机不同：WHERE是分组之前进行过滤，不满足WHERE条件，不参与分组；而HAVING是分组之后对结果进行过滤。
判断条件不同：WHERE不能对聚合函数进行判断，而HAVING可以。

  1. DQL——排序查询
  • 语法：
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
  • 排序方式：

升序（默认值）
DESC：降序
7. DQL——分页查询

  • 语法：
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
  1. DQL——执行顺序
FROM 表名列表
WHERE 条件列表
GROUP 分组字段列表
HAVING 分组后字段列表
SELECT 字段列表
ORDER BY 排序字段列表
LIMIT 分页参数

数据控制语言（DCL）

用于控制用户对数据的访问权限，例如通过GRANT和REVOKE语句。

  1. DCL——管理用户
  • 查询用户：
USE mysql;
SELECT * FROM user;
  • 创建用户：
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
  • 修改用户密码：
ALTER USER ‘用户名’@‘主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
  • 删除用户：
DROP USER ‘用户名’@‘主机名’;
  1. DCL——权限控制
  • 查询权限：
SHOW GRANTS FOR ‘用户名’@‘主机名’;
  • 授予权限：
GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;
  • 撤销权限：
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;

函数

一段可以直接被另一段程序调用的程序和代码。
MySQL中内置了很多字符串函数，常用的几个如下：

  1. 字符串函数
  2. 数值函数
  3. 日期函数
  4. 流程函数

流程函数是很常用的一类函数，可以在SQL语句中实现筛选，从而提高语句的效率。

约束

一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性。MySQL数据库通过约束(constraints)防止无效的数据进入到表中，以保护数据的实体完整性。

约束 描述
非空约束 限制该字段的数据不能为NULL
唯一约束 保证该字段的所有数据都是唯一、不重复的
主键约束 主键是一行数据的唯一标识、要求非空且唯一
默认约束 保存数据是，如果未指定该字段的值，则采用默认值
检查约束 保证字段值满足摸一个条件
外键约束 用来让两张表的数据之间建立连接，来保数据的一致性和完整性
外键约束：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
  • 添加外键：
CREATE TABLE 表名(
		字段名 数据类型,
		…
		[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
  • 删除外键：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  • 外键删除更新行为：

删除更新行为：

ALTER TABLE 表名 ADD CONSYRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

关联查询，指两个或更多个表一起完成查询操作。

  • 一对多（多对一）：

在多的一方建立外键，指向一的一方主键。

  • 多对多：

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

  • 一对一：

在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的（UNIQUE）。

  • 多表查询分类：
  1. 内连接：
    相当于查询A，B交集的部分。
  • 隐式内连接：
SELECT 字段列表 FROM 表1，表2 WHERE 条件···;
  • 显示内连接：
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件···;
  1. 外连接：
  • 左外连接：

查询左表所有数据，以及两张表交集部分数据。

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件···;
  • 右外连接：

查询右表所有数据，以及两张表交集部分数据。

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件···;
  1. 自连接：

当前表与自身表的连接查询，自连接必须使用表别名。

SELECT 字段列表 FROM 表A 别名A JOIN 表B 别名B ON 条件···;

事务

数据库中的事务是指对数据库执行的一批DML操作（INSERT、UPDATE、DELETE），在同一个事务当中，这些操作最终要么全部成功要么全部失败，不会存在部分成功的情况。在MySQL中，只有InnoDB引擎的数据库或表才支持事务。

  1. 事务操作：
  • 查看/设置事务提交方式：
SELECT @@AUTOCOMMIT;
SET @@AUTOCOMMIT = 0;
  • 提交事务：
COMMIT;
  • 回滚事务：
ROLLBACK;
  • 开启事务：
START TRANSACTION 或BEGIN;
  1. 事务四大特性ACID：

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致的状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中数据的改变就是永久的。
3. 并发事务问题：

多个并发事务在执行的过程中出现的问题，有以下三种情况：
脏读：一个事务读到另一个事务还没有提交的数据。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对用的数据行，但是在插入数据时，又发现这行数据已经存在了，好像出现了“幻影”。
4. 事务隔离等级：

为解决并发事务问题，设置了事务隔离等级，下表中从上到下隔离等级越来越高，但是性能越来越差，实际开发中，二者加以权衡使用。

隔离等级 脏读 不可重复读 幻读
Read uncommitted √ √ √
Read committed × √ √
Repeatable Read（默认） × × √
Serializable × × ×
posted @ 2025-04-24 22:34  呆住的豚豚  阅读(21)  评论(0)    收藏  举报