MySQL的基本知识

最新推荐文章于 2024-10-27 19:15:39 发布

顾以沫

最新推荐文章于 2024-10-27 19:15:39 发布

阅读量1.5k

点赞数 36

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_51933701/article/details/141275972

版权

## 注意都为英文格式文中中英文混杂

1.什么是SQL？

SQL（Structured Query Language）是一种用于存储、检索、操作和管理关系数据库系统（RDBMS）中的数据的标准编程语言。

2.SQL的通用语法

1.SQL语句可以写单行，可以写多行，通常以；结尾。

2.可以使用空格和缩进来增加可读性。

3.MySQL的语句通常不区分大小写，但是关键字建议使用大写。

4.MySQL注释一般有三种方式：

使用--后跟空格进行单行注释，

使用/*开始并*/结束进行多行注释

而在某些特定环境（如SQL*Plus）中还可以使用#进行单行注释

3.SQL语句的分类：

数据定义语言（DDL, Data Definition Language）：用于定义或修改数据库对象的结构，如表、视图、索引等。常见的DDL语句包括CREATE、ALTER、DROP、TRUNCATE等。
数据操纵语言（DML, Data Manipulation Language）：用于处理数据库中的数据，如增加、删除、更新和查询数据。DML的主要语句包括INSERT、UPDATE、DELETE和SELECT。
数据控制语言（DCL, Data Control Language）：用于定义访问权限和安全级别，控制对数据库中数据的访问。DCL语句包括GRANT和REVOKE，用于授权和回收用户的数据库权限。
数据查询语言（DQL, Data Query Language）：实际上，DQL并不是SQL标准的一个明确分类，但通常将SELECT语句归为DQL，因为它专门用于查询数据库中的数据。

##DDL：操作数据库、表

操作数据库（CRUD）：

C（create）：创建

create database db1 创建数据库db1（默认是utf-8）

create database if not exists db1 判断db1是否存在不存在就创建

create database db3 character set gbk 指定字符集

create database if not exists db1 character set gbk

R（retrieve）：查询

show databases 查询所有数据库名称

show create database mysql（数据库名字）查看数据库对应的字符集

U（update）：修改

alter database db1 character set utf8 修改数据库的字符集

D（delete）：删除

drop database db1 删除数据库

drop database if exists db1 判断数据库是否存在存在就删除

使用数据库

select database() 查询当前使用的数据库

use db1

操作表（CRUD）：

C（create）：创建

create table 表名(

列名：数据类型1,

列名：数据类型2,

...

列名：数据类型n

);

varchar: 可变长度的字符串，用于存储文本数据。  
char: 固定长度的字符串，如果存储的字符串长度小于指定的长度，则会在末尾填充空格以达到指定长度。  
int: 整数类型，用于存储整数值。  
bigint: 大整数类型，能够存储比int更大的整数值。  
decimal(m,n): 定点数类型，m是总位数（精度），n是小数点后的位数（标度），用于存储精确的小数。  
float: 浮点数类型，用于存储近似的小数值。  
double(5,2): 双精度浮点数类型，与float类似，但具有更高的精度。  
date: 日期类型，仅包含年、月、日信息。  yyyy-MM-dd
time: 时间类型，仅包含时、分、秒信息，可能还包括毫秒。HH-mm-ss 
datetime: 日期和时间类型，结合了date和time的值，用于存储日期和时间。yyyy-MM-dd HH-mm-ss   
timestamp: 时间戳类型，与datetime类似，但具有时区支持，并且通常具有自动更新特性。  如果未赋值或赋值为null 则填充当前时间
boolean: 布尔类型，用于存储真值（true）或假值（false）。注意：并非所有数据库都直接支持boolean类型，有时会用tinyint(1)等类型来模拟。  
blob: 二进制大对象，用于存储大量二进制数据，如图片、音频、视频等。  
text: 文本大对象，用于存储大量文本数据。  
enum: 枚举类型，允许从预定义的值列表中选择一个值。  
set: 集合类型，允许从预定义的值列表中选择零个或多个值。  
json: JSON数据类型，用于存储JSON格式的数据，便于在数据库中直接存储和操作复杂的数据结构。

R（retrieve）：查询

show tables 查询某个数据库中所有表的名称

desc（查询表结构//降序）表名查看表结构

U（update）：修改

alter table 表名 rename to 新表名修改表名

show create table 表名查看表的字符集

alter table 表名 character set utf8 修改表的字符集

alter table 表名 add 列名数据类型添加一列

修改列名称类型

alter table 表名 change 原列名新列名数据类型

alter table 表名 modify 列名数据类型（只修改数据类型）

alter table 表名 drop 列名删除列

D（delete）：删除

drop table db1 删除表

drop table if exists db1 判断数据库是否存在存在就删除

使用数据库

select database() 查询当前使用的数据库

use db1

##DML：增删改表中的数据

1.添加数据

insert into 表名（列名1，列名2...列名n）values（值1，值2...值n）

列名值要一一对应

如果表名后，不定义列名，则默认给所有列添加值,

insert into 表名 values（值1,值2,...值n);

2.删除数据

delete from 表名 [ where 条件]

如果删除整张表中的记录则不建议使用 delete from 表名

truncate table 表名先删除表再创建一张相同的表

3. 修改数据

update 表名 set 列名1 =值1 ，列名2=值2. . .[where 条件]

##DQL：查询表中的记录

select * from 表名

查询的语法

select 
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组之后的条件限定
order by
    排序
limit
    分页

基础查询

多个字段查询计算列起别名

select name,age from student

select * from student

SELECT NAME, math, english, math+ IFNULL（english,0) AS 总分 FROM student;

SELECT NAME,math 数学, english 英语, math+,IFNULL(english,0) 总分FRoMstudent；

去重(distinct)

select distinct address from student

select distinct name,address from student

条件查询

where后跟的句子

比较运算符（=、<>、>、<、>=、<=）：用于比较两个值的大小或是否相等。
IN：用于指定某个字段的值必须匹配列表中的任意一个值。
BETWEEN：用于选取介于两个值之间的数据（包括边界值）。
LIKE：用于在WHERE子句中搜索列中的指定模式，通常与通配符（%和_）一起使用。
- _单个任意字符
- %任意多个字符
IS NULL/IS NOT NULL：用于判断字段是否为空值。
逻辑运算符（AND、OR、NOT）

排序查询

SQL中的排序查询主要通过ORDER BY子句来实现。这个子句允许你根据一个或多个列对结果集进行排序。默认情况下，排序是升序的（ASC），也可以指定降序（DESC）来逆转排序顺序。

SELECT column1, column2, ...  
FROM table_name  
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

假设有一个名为Employees的表，包含ID, Name, 和 Salary列。

--示例 1：按工资升序排序
SELECT ID, Name, Salary  
FROM Employees  
ORDER BY Salary ASC;
--示例 2：按工资降序排序
SELECT ID, Name, Salary  
FROM Employees  
ORDER BY Salary DESC;
--示例 3：按名字升序，工资降序排序
SELECT ID, Name, Salary  
FROM Employees  
ORDER BY Name ASC, Salary DESC;

聚合函数

COUNT()：计算行数。会排除null值
SUM()：计算数值列中值的总和。
AVG()：计算数值列中值的平均值。
MAX()：找出某列的最大值。
MIN()：找出某列的最小值。

示例：基于一个假设的Orders表，它包含OrderID, CustomerID, OrderDate, 和 Amount等列。

-- 查询示例：计算总订单数、总订单金额、平均订单金额、最大和最小订单金额  
SELECT  
    -- 计算订单总数（忽略NULL值，因为COUNT(*)会计算所有行）  
    COUNT(*) AS TotalNumberOfOrders,  
    -- 计算所有订单的总金额  
    SUM(Amount) AS TotalAmount,  
    -- 计算所有订单的平均金额（如果Amount有NULL值，这些值在计算平均值时会被忽略）  
    AVG(Amount) AS AverageAmount,  
    -- 找出最大的订单金额  
    MAX(Amount) AS MaxAmount,  
    -- 找出最小的订单金额  
    MIN(Amount) AS MinAmount  
FROM  
    Orders;  
-- 注释：此查询从Orders表中检索了关于订单的一些聚合信息，  
-- 包括订单总数、总金额、平均金额、最大金额和最小金额。

分组查询

SQL语句中GROUP BY子句和HAVING子句说明

语法：

GROUP BY 分组字段；

注意：

分组之后查询的字段：分组字段、聚合函数
where和having的区别？
- where：在分组之前进行限定，如果不满足条件，则不参与分组。
- having：在分组之后进行限定，如果不满足结果，则不会被查询出来。
- 进一步说明：where后不可以跟聚合函数，而having可以进行聚合函数的判断。

-- 假设Orders表结构：OrderID, CustomerID, OrderDate, Amount  
  
-- 示例 1：按CustomerID分组，计算每个客户的订单总金额  
SELECT CustomerID, SUM(Amount) AS TotalAmountPerCustomer  
FROM Orders  
GROUP BY CustomerID  
ORDER BY TotalAmountPerCustomer DESC;  
  
-- 示例 2：按年份分组，计算每年的订单总数  
SELECT YEAR(OrderDate) AS OrderYear, COUNT(*) AS NumberOfOrdersPerYear  
FROM Orders  
GROUP BY YEAR(OrderDate)  
ORDER BY OrderYear;  
  
-- 示例 3：先按CustomerID分组，然后在每个客户内部按年份分组，计算每个客户每年的订单金额  
SELECT CustomerID, YEAR(OrderDate) AS OrderYear, SUM(Amount) AS TotalAmountPerYearPerCustomer  
FROM Orders  
GROUP BY CustomerID, YEAR(OrderDate)  
ORDER BY CustomerID, OrderYear;  
  
-- 示例 4：结合HAVING子句，筛选出订单总金额超过1000的客户  
SELECT CustomerID, SUM(Amount) AS TotalAmountPerCustomer  
FROM Orders  
GROUP BY CustomerID  
HAVING SUM(Amount) > 1000  
ORDER BY TotalAmountPerCustomer DESC;  
  
-- 示例 5：结合WHERE子句和GROUP BY，先过滤出特定日期的订单，然后按客户分组计算总金额  
SELECT CustomerID, SUM(Amount) AS TotalAmountForSpecificDate  
FROM Orders  
WHERE YEAR(OrderDate) = 2023 AND MONTH(OrderDate) = 1  
GROUP BY CustomerID  
ORDER BY TotalAmountForSpecificDate DESC;

分页查询

1.语法：1imit开始的索引l，每页查询的条数；

2.公式：开始的索引=,（当前的页码-1）*每页显示的条数

--每页显示3条记录
SELECTFROMstudentLIMIT0,3;--第1页
SELECTFROM student LIMIT3,3;--第2页
SELECTFROMstudentLIMIT6,3;--第3页

##DCL：

##约束

约束（Constraints）是用来确保数据库表中数据的准确性和可靠性的规则。

NOT NULL 约束：确保列不能有 NULL 值。
UNIQUE 约束：确保所有值在列中是唯一的。
PRIMARY KEY 约束：一种特殊的唯一约束，表中每行都必须有一个唯一的标识符。一个表只能有一个主键，并且主键列不能有 NULL 值。
FOREIGN KEY 约束：用于在两个表之间创建链接。外键（Foreign Key）是一个表中的字段，它是另一个表的主键（Primary Key）的引用。
CHECK 约束：确保列中的值满足特定条件。
DEFAULT 约束：为列指定默认值。如果在插入行时未指定该列的值，则会使用默认值。

非空约束

非空约束（NOT NULL Constraint）是SQL中用于确保表中某一列不接受NULL值的一种数据完整性约束。当一个列被定义为非空时，任何尝试向该列插入NULL值或者更新现有记录将该列设置为NULL的操作都会被数据库系统拒绝，并通常返回一个错误消息。

CREATE TABLE Employees (  
    ID INT PRIMARY KEY,  
    FirstName VARCHAR(50) NOT NULL,  
    LastName VARCHAR(50) NOT NULL,  
    Email VARCHAR(100)  
);

数据库设计

软件的研发步骤

数据库概念设计

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。

建立数据库中的表结构以及表与表之间的关联关系的过程。

有哪些表？表里有哪些字段？表和表之间有什么关系？

数据库的设计理念

需求分析（数据是什么？数据具有哪些属性？数据与属性的特点是什么）

逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）

物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）,

维护设计（1.对新的需求进行建表；2.表优化）

表之间的关系

一对一（1:1）关系：
- 在这种关系中，表A中的每一行在表B中最多只能对应一行，反之亦然。这种关系通常不是很常见，因为可以通过合并两个表来避免这种关系。但是，当两个实体之间存在明显的界限，但又需要保持各自的独立性时，可能会使用到一对一关系。
- 例如，学生和他们的学生ID卡之间可能是一对一关系，每个学生都有一张唯一的ID卡。
一对多（1:N）关系：
- 在这种关系中，表A中的一行可以与表B中的多行相关联，但表B中的每一行只能与表A中的一行相关联。这是最常见的关系类型。
- 例如，一个班级（表A）可以有多个学生（表B），但每个学生只能属于一个班级。
多对多（M:N）关系：
- 在这种关系中，表A中的多行可以与表B中的多行相关联。这种关系需要一个额外的表（称为关联表或中间表）来存储两个表之间的关系。
- 例如，学生和课程之间是多对多关系，因为一个学生可以注册多门课程，而一门课程也可以被多个学生注册。为了表示这种关系，我们需要一个额外的表（例如，注册表），其中包含学生ID和课程ID作为外键。

多表查询

多表查询，也称为关联查询，是指同时查询两个或两个以上的表以获取所需的数据。

内连接（INNER JOIN）：
- 内连接返回两个表中满足连接条件的记录。如果表中有至少一个匹配，则返回行。
- 语法示例：SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 表1.关联字段 = 表2.关联字段 WHERE 其他条件;
- 示例：查询员工及其所在部门的名称，假设员工表为employees，部门表为departments，关联字段为员工表中的department_id和部门表中的id。
外连接（OUTER JOIN）：
- 外连接分为左外连接（LEFT OUTER JOIN）和右外连接（RIGHT OUTER JOIN）。左外连接返回左表的所有记录以及右表中匹配的记录；右外连接则返回右表的所有记录以及左表中匹配的记录。
- 左外连接语法示例：SELECT 字段列表 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.关联字段 = 表2.关联字段 WHERE 其他条件;
- 右外连接语法与左外连接类似，只是将LEFT OUTER JOIN替换为RIGHT OUTER JOIN。
交叉连接（CROSS JOIN）：
- 交叉连接返回两个表中所有行的笛卡尔积。如果第一个表有X行，第二个表有Y行，那么结果集将有X*Y行。
- 语法示例：SELECT 字段列表 FROM 表1 CROSS JOIN 表2 WHERE 条件;（注意：交叉连接通常不使用ON子句，而是直接在WHERE子句中指定条件，但这不是必须的）
自连接（SELF JOIN）：
- 自连接是指表与其自身进行连接。这通常用于比较表中的行或查找表中的层级关系（如员工与其上级的关系）。
- 语法示例：SELECT 别名A.列名1, 别名B.列名2 FROM 表名别名A JOIN 表名别名B ON 别名A.关联字段 = 别名B.关联字段 WHERE 其他条件;
子查询（Subquery）：
- 子查询是嵌套在其他SQL查询中的查询。子查询可以出现在SELECT、FROM或WHERE子句中。
- 语法示例：SELECT 字段列表 FROM 表名 WHERE 列名 = (SELECT 子查询的字段 FROM 子查询的表 WHERE 子查询的条件);

事务

数据库的事务（Transaction）是数据库管理系统（DBMS）执行过程中的一个重要概念，它指的是作为单个逻辑工作单元执行的一系列操作，这些操作要么全部执行，要么全部不执行，以保持数据的一致性和完整性。

一、定义与概念

数据库事务是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

二、特性（ACID属性）

数据库事务通常具有四个基本特性，即ACID属性：

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。例如，在转账操作中，将张三的余额减少和将李四的余额增加这两个操作必须同时成功或同时失败。
一致性（Consistency）：事务的执行必须使数据库从一个一致性状态转换到另一个一致性状态。一致性确保了数据的正确性和完整性。例如，在转账业务中，如果因为某种原因转账操作没有完成，那么数据库中的账户余额应该保持不变，以避免数据不一致。
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。隔离性通过锁定机制或其他并发控制技术来实现，以确保事务的独立性。
持久性（Durability）：一旦事务被提交，它对数据库中所做的修改就是永久性的，即使发生系统故障也不会丢失。持久性是通过DBMS的日志机制来实现的，它记录了事务对数据库所做的所有修改，并在系统发生故障时能够恢复这些修改。

三、事务类型

根据事务的启动和结束方式，可以将事务分为显式事务、隐式事务和自动事务三种类型：

显式事务：显式事务需要用户显式地定义事务的开始和结束。在SQL中，通常使用BEGIN TRANSACTION或START TRANSACTION语句来开始一个事务，使用COMMIT语句来提交事务，使用ROLLBACK语句来回滚事务。
隐式事务：隐式事务是指当用户执行数据操作语句时，系统自动开始一个事务，并在用户执行下一条数据操作语句时自动提交前一个事务。在某些数据库管理系统中，可以通过设置自动提交模式来启用隐式事务。
自动事务：自动事务是指数据库管理系统能够自动地开启和结束事务。在事务执行过程中，如果没有出现异常，则事务自动提交；如果出现异常，则事务自动回滚。这种事务类型通常用于简化事务处理过程，但可能不适用于所有场景。

四、事务的作用与优势

数据库事务在数据库管理中具有重要作用，它提供了以下优势：

数据一致性：通过事务的ACID属性，确保数据的一致性和完整性。
并发控制：在并发环境下，通过隔离性机制防止多个事务之间的干扰和冲突。
恢复能力：通过日志机制和持久性属性，确保在系统故障时能够恢复数据到一致状态。
简化操作：将复杂的数据库操作封装成一个独立的事务单元，便于管理和维护。

可以通过show engines;语句来查看mysql支持的存储引擎

#步骤一：开启事务（可选）
start transaction;
#步骤二：编写事务中的sql语句（insert、update、delete）
#这里实现一下"李二给王五转账"的事务过程
update t_account set balance = 50 where vname = "李二";
update t_account set balance = 130 where vname = "王五";
#步骤三：结束事务
commit; #提交事务
# rollback; #回滚事务:就是事务不执行，回滚到事务执行前的状态