MySQL知识总结-基础篇

最新推荐文章于 2024-08-07 23:56:32 发布

清色回忆

最新推荐文章于 2024-08-07 23:56:32 发布

阅读量108

点赞数

分类专栏： mysql 文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/Phantom_123/article/details/130839683

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章详细介绍了SQL的四种主要语句类型：DDL（创建、删除、修改数据库对象），DML（数据增删改），DQL（数据查询），DCL（权限控制）。此外，还涉及到了函数的使用，如字符串、数值函数，以及数据库约束如非空、唯一、主键、外键约束。最后提到了事务处理及其重要特性以及并发事务可能遇到的问题和隔离级别。

摘要由CSDN通过智能技术生成

SQL语句类型

DDL语句

定义数据库对象，例如库、表、字段、索引的创建、删除、修改
一般开头的关键字是create、drop、alter

# create、drop
create table if not exists [表名] (
    [字段名称] [字段数据类型(长度)] [not null default '' comment '']
);
drop tabel if exists [表名];

# alter
# 修改字段数据类型
alter table [表名] modify [新字段名称] [字段数据类型(长度)]
# 修改字段名称
alter table [表名] change [旧字段名称] [新字段名称] [新字段名称数据类型(长度)] 
# 修改表名
alter table [表名] rename to [表名]

DML语句

对数据库中的数据进行增删改
常用关键字：insert、update、delete

DQL语句

查询数据库中的数据
常用关键字：select

select语句的执行顺序

# sql语句的结构一般由下面的结构组成，其关键字后面语句对应的执行顺序依次是
# from -> where -> group by -> select -> order by -> limit
select  4
from 1
where 2
group by 3
order by 5
limit 6

DCL语句

创建数据库用户、控制数据库的访问权限

# 常用语句类型
# 创建用户，%表示任意
create user '用户名'@'主机' identified by '密码'
# 更新密码
alter user '用户名'@'主机' identified with mysql_native_password '新密码'
# 删除用户
drop user '用户名'@'主机'

# 权限控制
# 查看对应用户的权限
show grants for '用户名'@'主机'
/*
权限列表： select、update、process、alter、create user等
表名用*表示则为数据库中的所有表
*/
# 赋予对应用户权限
grant [权限列表] on [数据库].[表名] to '用户'@'主机'
# 撤回权限
revoke [权限列表] on [数据库].[表名] to '用户'@'主机'

函数

一段可以直接被另一段函数调用的程序或者代码

字符串函数

# 字符串连接
concat(s1, s2....)
# 转小写
lower(s1)
# 转大写
upper(str)
# 使用pad从str的左边或者右边开始填充，直接长度为n
/*
n < str.len: 会直接把str超过n的部分截断
*/
lpad(str, n, pad) -- 左边
rpad(str, n, pad)  --右边
# 去掉字符串前后的空格, 中间空格不会去掉！！！！
trim(str)
# 字符串截取
/*
下标是1开始的
*/
substring(str, pos)
substring(str from pos)
substring(str, start, len)

数值函数

ceil(x): 向上取整
floor(x): 向下取整
mod(x, y): x/y的值
rand(): 生成0 ~ 1之间的随机数
round(x, y): 对x四舍五入，保留y位小数

流程函数

if(value,true, false)
ifnull(value1, value2): value1不为null则返回value1，否则返回value1
case
when [判断条件] then [需要返回的值]
when [判断条件] then [需要返回的值]
else [需要返回的值] end

约束

用户限制存储在表中的数据
保证数据库中数据的正确性、有效性、完整性

非空约束

限制字段不能为null not null

唯一约束

保证该字段的数据都是唯一 unique

主键约束

一行数据的唯一标识，要求非空且唯一 primary key

默认约束

如果未指定该字段的值，则采用默认值 default

检查约束

1.保证字段满足某一个条件 check
2.检查约束用于在数据库中定义某个列或一组列的值必须满足的条件
3.MySQL数据库中没有直接支持检查约束（Check Constraint）的功能

外键约束

用来让两张表的数据之间建立连接，保证数据的完成性和一致性

alter table 表名 add constraint 外键名 foreign key (字段名) references 父表(字段)

alter table 表名 drop foreign key 外键名
# 约束类型
# 在MySQL中，可以使用ON DELETE和ON UPDATE子句来指定级联动作

set null: 设置空值约束在主表中的行被删除或更新时，会将子表中与之关联的外键列的值设置为NULL
cascade: 当主表中的数据发生变化时，级联约束会自动更新或删除子表中的相关数据。常见的级联动作包括CASCADE、SET NULL、SET DEFAULT等
no action
restrict: 禁止约束限制了对主表的修改操作，以确保不会破坏外键关联。如果尝试修改主表中的主键列值或删除主表中的某一行时，如果有相关的子表数据存在，则禁止约束会阻止操作的执行
set default: 设置默认值约束在主表中的行被删除或更新时，会将子表中与之关联的外键列的值设置为预定义的默认值

多表查询

内连接查询

只返回两个表中都存在的记录
隐式内连接：join = inner join
显示内连接：inner join

外连接

1.左外连接： left join = left outer join
返回左表所有的记录以及与右表匹配的记录，如果右表没有匹配的记录，那么右表的字段将显示为null
2.右外连接：right join = right outer join
返回右表所有的记录以及与左表匹配的记录，如果左表没有匹配的记录，那么左表的字段将显示为null
3.全连接：full join mysql8.0版本以上才支持，低版本需要通过union左连接和右连接的结果来组合构造
全连接返回两个表中的所有记录，如果某个表中没有与另一个表匹配的记录，对应的字段将显示为null

联合查询

union / union all
将多次查询的结果合并起来，形成一个新的查询结果

1.union会对合并后的结果去重，而union all不会对合并后的结果去重
2.列数以及字段类型都需要保持一致
3.去重原理是通过对比整行每个字段的数据来进行去重，只有当所有字段的值都相同的时候才会认为两行数据相同

子查询

子查询可以嵌套在select、from、where、having或者in等子句中，用于获取更复杂的查询结果

列子查询

子查询的结果需要主查询的每行进行一一对应
例子查询会增加查询的复杂度，可能会影响查询的性能

常用操作符：in, not in, any, some, all

SELECT o.order_id, o.customer_id, 
       (SELECT SUM(oi.quantity * oi.unit_price) 
        FROM order_items oi 
        WHERE oi.order_id = o.order_id) AS total_amount
FROM orders o;

行子查询

SELECT 列1, 列2
FROM 表1
WHERE 列1 IN (SELECT 列1 FROM 表2 WHERE 条件);

表子查询

SELECT 列1, 列2
FROM (SELECT 列1, 列2 FROM 表1 WHERE 条件) AS 子查询别名;

事务

1.事务是一组操作的集合，是一个不可分割的工作单位，事务会所有操作作为一个整体一起向系统提交或者撤销，
2 这些操作要么全部同时成功，要么全部同时失败
3.查看/设置事务提交方式
select @@autocommit;
1是自动提交 0是手动提交
set @@autocommit=0;
4.提交事务: commit
5.回滚事务: rollback
6.开启事务 start transaction 或者begin

事务四大特性

原子性：事务是不可分割的最小单元，要么全部成功，要么全部失败
事务里面的任何操作失败，事务都应该回滚到最初的状态，从而保证数据的一致性

一致性：事务完成时，必须使所有的数据都保持一致状态
要求数据库在事务开始和结束时保持一致的状态

隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响下的独立环境下运行
每个事务之间的操作是相互隔离的，保证事务的并发不会导致不一致的结果
假设有两个并发执行的事务，事务A和事务B，它们都要对同一个账户的余额进行修改。如果隔离性得到保证，那么在事务A执行期间，事务B应该无法访问或修改该账户的余额，直到事务A提交或回滚。

持久性：事务一旦提交或回滚，它对数据库中的数据改变就是永久的

并发事务问题

脏读：一个事务读到另一个事务没有提交的数据
不可重复读：一个事务先后读取同一条记录但是读取的数据不同，
幻读: 一个事务查询数据时发现数据不存在，但是在插入数据时又发现数据库中存在数据
更新丢失：两个事务同时修改同一条数据，其中一个事务的修改被另一个事务覆盖，导致更新的数据丢失

事务隔离级别

1.查看隔离级别
select @@tansaction_isolation
2.设置事务隔离级别
set [session|global] transaction isolation level [read uncommited | read commited | repeatable read | serializable]
3.事务隔离级别越高，数据越安全，但是性能越低

读未提交
读已提交：可以解决脏读
可重复读：可以解决脏读、不可重复读
串行：可以解决脏读、不可重复读、幻读