【数据库学习笔记】基础篇

已于 2023-07-20 11:51:32 修改
阅读量2.4k 收藏 1
点赞数
文章标签： 数据库 学习 笔记
于 2023-06-13 11:22:31 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ToBlair_/article/details/131067155
版权
本文详细介绍了MySQL数据库的基础知识，包括数据库概念、MySQL的下载与配置、数据模型、SQL语言的分类（DDL、DML、DQL和DCL）、函数、约束、多表查询以及事务处理。此外，还提到了MySQL图形化界面的使用，如DataGrip和Navicat，以及如何解决配置和连接中的常见问题。
摘要由CSDN通过智能技术生成

文章目录

MySQL概述

数据库相关概念

名称英文/简称简介例子
数据库DataBase（DB）数据存储的仓库
数据库管理系统Database Management System（DBMS）操纵和管理数据库的大型软件MySQL、Oracle
SQLStructured Query Language操作关系型数据库的编程语言，定义了操作关系型数据库的一套标准

主流的关系型数据库管理系统

  • Oracle
  • MySQL（开源免费）
  • SQL Sever
  • PostgreSQL

MySQL图形化界面

  • DataGrip
  • Navicat
  • Sqlyog

MySQL的下载与配置

  1. 下载MySQL
    下载地址
    选择DOWNLOADS => MySQL Community Server，dmg文件下载
    在这里插入图片描述
    双击安装包，进行安装
    安装成功后，可在系统偏好设置中看到MySQL
    在这里插入图片描述
  2. 配置MySQL
  • 首先打开系统偏好设置 -> MySQL 把它打开
  • 配置环境变量
export PATH=${PATH}:/usr/local/mysql/bin

问题1：配置MySQL开发环境，找不到.bash_profile
启动终端Terminal，进入当前用户的home目录：
cd ~
创建.bash_profile：
touch .bash_profile
编辑.bash_profile文件：
open -e .bash_profile
更新刚配置的环境变量：
source .bash_profile
验证配置是否成功，查看输出是否有刚刚的配置
echo $PATH
问题2：Mac和Idea 终端关闭后，环境变量失效，每次都需source ～/.bash_profile
问题：在bash_profile中配置环境变量后，只有在终端执行bash_profile，别名才会生效，每次重启iTerm2后命令就会失效source ~/.bash_profile
解决办法：
将bash_profile中别名和环境变量配置写在zshrc中,这样就解决了iTerm2重启后别名alias失效的问题
原理：
其实这个原理还是，只有每次重启的时候在终端执行bash_profile:source ~/.bash_profile，别名才会生效。因为当iTerm2一旦启动，就会自动执行zshrc文件，因此zshrc中的配置都是全局配置。
！注意使用的shell版本:
zsh对应的是zshrc
bash对应的是basic

  1. 启动MySQL（客户端连接）
mysql -u root -p
-u root：指定用户名登陆
-p：使用密码登录

在这里插入图片描述

  • 登录成功后，可以通过下面的命令修改密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('新密码');
  1. 配置DataGrip
  • 打开Datagrip，进入注册界面，选择“免费试用”或激活
  • 创建new project -> “mysql-database”
  • 添加数据源 -> MySQL
    在这里插入图片描述
  • 填写：host，port：3306，user：root，password，并下载驱动
    在这里插入图片描述

使用DataGrip连接MySQL时报错Access denied for user 'root'@'localhost'
问题描述：使用数据库连接工具(如：dataGrip)或者使用 msyql 命令行连接 mysql 8.0 的本地 server 时，报错：Access denied for user 'root'@'localhost' ，而在终端输入该密码没有错误。
解决方法：
在终端输入mysql_secure_installation
要求输入当前密码，输入的是DataGrip报错的密码，验证成功，继续下一步。
是否更改密码，输入Y
是否移除匿名用户，输入Y。
是否禁止root的远程连接，输入n。
是否删除test数据库，输入n。
是否现在重新加载权限表，输入Y。
重启系统
此时再测试链接，成功
在这里插入图片描述

数据模型

  • 关系型数据库（RDBMS）：
    建立在关系模型的基础上，由多张相互连接的二维表组成的数据库
    • 使用表存储数据
    • 使用SQL管理和操作数据

一个数据库服务器中可以创建多个数据库，一个数据库中可以创建多个表
在这里插入图片描述

SQL

SQL通用语法

  1. 可以单行/多行书写，以分号结尾
  2. 可以使用空格/缩进
    在这里插入图片描述

SQL分类

分类全称说明
DDLData Definition Language数据库定义语言，定义数据库对象（数据库、表、字段）
DMLData Manipulation Language数据操作语言，用来对数据库表中的数据进行增删改
DQLData Query Language数据查询语言，用来查询数据库中表的记录
DCLData Control Language数据库控制语言，用来管理数据库用户、控制数据库的访问权限

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 表名;
  • 创建
    CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段2注释],
	... ... 
	字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];
  • 修改
    • 修改表名
      ALTER TABLE 表名 RENAME TO 新表名;
  • 删除
    # 删除表
DROP TABLE [IF EXISTS] 表名;
# 删除指定表，并重新创建该表（删除表时，表中的数据也会被删除）
TRUNCATE TABLE 表名;
  • 表的数据类型
    • 数值类型
      • 整型：TINYINT（1bytes）、SMALLINT（2）、MEDIUMINT（3）、INT/INTEGER（4byte）、BIGINT（8）
      • 浮点型：FLOAT（4）、DOUBLE（8）、DECIMAL（M精度、D标度）
      • 无符号型：UNSIGNED
    • 字符串类型
      • CHAR定长字符串（性能好）、VARCHAR变长字符串
      • 二进制数据：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
      • 文本数据：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
    • 日期时间类型
      • DATA：YYYY-MM-DD
      • TIME：HH：MM：SS
      • YEAR：YYYY
      • DATETIME：YYYY-MM-DD HH：MM：SS
      • TIMESTAMP
  1. DDL字段操作
  • 添加字段
    ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
  • 修改字段
    # 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
  • 删除字段
    ALTER TABLE 表名 DROP 字段名;

DDL总结

在这里插入图片描述

DML数据操作语言

  1. 添加数据INSERT
    // 给指定字段添加数据
INSERT INTO 表名 (字段1, ...) VALUES (值1, ...);
// 给所有字段添加数据
INSERT INTO 表名 VALUES (值1, ...);
// 批量添加数据
INSERT INTO 表名 (字段1, ...) VALUES (值1, ...), (值1, ...), ...;
INSERT INTO 表名 VALUES (值1, ...), (值1, ...), ...;
    注意
    • 插入世纪数据值的顺序要与字段的顺序一一对应
    • 字符串和日期类型数据应该放在引号中
    • 插入数据的大小和类型要求应该符合规定
  2. 修改数据UPDATE
    UNPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件];
    注意：如果修改语句的条件没有，则会修改整张表的所有数据！！！
  3. 删除数据DELETE
    DELETE FROM 表名 [WHERE 条件];
    注意：不能删除某一个字段的值（要用UPDATE）
    在这里插入图片描述

DQL数据查询语言

基本查询

  1. 查询多个字段
  2. 设置别名
  3. 去除重复记录

条件查询

在这里插入图片描述

聚合函数

将一列数据作为一个整体，进行纵向计算
在这里插入图片描述

分组查询


在这里插入图片描述

排序查询

在这里插入图片描述

分页查询

在这里插入图片描述

DQL语句的执行顺序

在这里插入图片描述

小结

在这里插入图片描述

DCL

DCL管理用户

在这里插入图片描述
注意：主机名为%代表任意主机

DCL管理权限

在这里插入图片描述
语句：
在这里插入图片描述

DCL小结

在这里插入图片描述

函数

字符串函数

在这里插入图片描述

数据类型转换函数cast()
CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

  • 语法：CAST (expression AS data_type)

    • expression：任何有效的SQServer表达式。
    • AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
    • data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

  • 可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

    • 二进制，同带binary前缀的效果 : BINARY
    • 字符型，可带参数 : CHAR()
    • 日期 : DATE
    • 时间: TIME
    • 日期时间型 : DATETIME
    • 浮点数 : DECIMAL
    • 整数 : SIGNED
    • 无符号整数 : UNSIGNED

非空返回值函数coalesce()

  • 用途：

    1. 将空值(null值)替换成其他值；
    2. 返回第一个非空值

  • 表达式：
    COALESCE(expression_1, expression_2, ...,expression_n)
    依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

  • SQL实例
    select coalesce(success_cnt, 1) from tableA
    当success_cnt 为null值的时候，将返回1，否则将返回success_cnt的真实值。

数值函数

在这里插入图片描述

日期函数

函数描述
NOW()返回当前的日期和时间
CURDATE()返回当前的日期
CURTIME()返回当前的时间
DATE()提取日期或日期/时间表达式的日期部分
EXTRACT()返回日期/时间的单独部分
DATE_ADD()向日期添加指定的时间间隔
DATE_SUB()从日期减去指定的时间间隔
DATEDIFF()返回两个日期之间的天数
DATE_FORMAT()用不同的格式显示日期/时间

流程函数

在这里插入图片描述

函数小结

在这里插入图片描述

约束

概述

在这里插入图片描述

约束演示

在这里插入图片描述

外键约束

在这里插入图片描述

语法

在这里插入图片描述
在这里插入图片描述

约束小结

在这里插入图片描述

多表查询

多表关系

  • 一对多（多对一）
    在这里插入图片描述
  • 多对多
    在这里插入图片描述
  • 一对一
    在这里插入图片描述
    外键为唯一，是一对一与多对一的关键区别所在

多表查询概述

从多张表中查询数据
笛卡尔积： 笛卡尔乘积是在数学中，集合A和集合B所有组合的情况。多表查询中，需要消除无效的笛卡尔积。

  • 多表查询的分类
    在这里插入图片描述
    多表查询是离不开外键约束的，以员工表和部门表为例：

内连接

内连接查询的是两表之间交集
此例子中查找出的是符合条件的员工和部门条目
在这里插入图片描述

外连接

左外连接查找的是所有员工表的条目与外键对应的部门表名（存在没有部门的悬空人员）
右外连接查找的是所有部门的条目与外键对应的员工信息（存在没有人员的悬空部门）

自连接

自连接查询时必须要取别名！！！
自连接可以是内连接、外连接，根据需要选择

联合查询 - union，union all

查询的字段必须要保持列数和字段类型一致
在这里插入图片描述

  • union直接合并所有结果
  • union all对合并结果去重

子查询（嵌套查询）

在这里插入图片描述

标量子查询

在这里插入图片描述

列子查询

在这里插入图片描述

行子查询

在这里插入图片描述
在这里插入图片描述

表子查询

在这里插入图片描述

在这里插入图片描述

with as简化子查询

WITH AS短语，也叫做子查询部分（subquery factoring），是用来定义一个SQL片断，该SQL片断会被整个SQL语句所用到。这个语句算是公用表表达式（CTE）。当我们书写一些结构相对复杂的SQL语句时，可能某个子查询在多个层级多个地方存在重复使用的情况，这个时候我们可以使用 with as 语句将其独立出来，极大提高SQL可读性，简化SQL。

-- with table_name as(子查询语句) 其他sql
with temp as (
    select * from xxx
)
select * from temp;

如果定义了 with 子句，但其后没有跟 select 查询，则会报错（没有使用没关系，其后必须有 select），因此不允许单独使用。

如果要定义多个临时子查询：

WITH t1 AS (
        SELECT *
        FROM abc
    ),
    t2 AS (
        SELECT *
        FROM efg
    )
SELECT *
FROM t1, t2

前面的 with 子句定义的查询可以后边 with 子句中使用，但一个 with 子句内部不能嵌套 with 子句。

with t1 as (select * from abc),
t2 as (select t1.id from t1)
select * from t2

它只能在一条sql中使用。

多表查询小结

在这里插入图片描述

事务

事务简介

事务是一组操作的集合，是一个不可分割的工作单位。要么同时成功，要么同时失败。
在这里插入图片描述
如果需要完成一组事务，就需要手动地开启事务、提交事务、回滚事务。

事务操作

默认每一条SQL语句都是一个事务，都会自动提交。

  • 方式1
  • 方式2
    在这里插入图片描述

事务的四大特性（ACID）

  1. 原子性（Atomicity）：
  2. 一致性（Consistency）：
  3. 隔离性（Isolation）：
  4. 持久性（Durability）：
    在这里插入图片描述

并发事务问题

在这里插入图片描述

脏读

在这里插入图片描述

不可重复读

不可重复读

幻读

在这里插入图片描述

事务隔离级别

事务隔离级别是为了解决并发事务带来的脏读、不可重复读、幻读等问题
从上到下，数据安全性越来越高，隔离级别越来越高，性能越来越差
在这里插入图片描述
MySQL默认的的可重复读，而其他数据库（Redis）可能是Read Committed

  • 查看、设置事务的隔离级别
    在这里插入图片描述
    session（会话级别）针对当前客户端会话窗口有效，global针对所有会话窗口有效
    **串行化：**只有一个事务对其操作完成才能执行另一个事务

事务小结

在这里插入图片描述

基础篇小结

在这里插入图片描述

零碎小知识

sql分区（partition）

partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录。
partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。
partition by与group by不同之处在于前者返回的是分组里的每一条数据，并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。

常用的函数：

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...) 求分组后的总数
max() over(partition by ... order by ...) 求分组后的最大值
min() over(partition by ... order by ...) 求分组后的最小值
sum() over(partition by ... order by ...) 求分组后的总和
avg() over(partition by ... order by ...) 求分组后的平均值
first_value() over(partition by ... order by ...) 求分组后的第一个值
last_value() over(partition by ... order by ...) 求分组后的最后一个值
lag() over(partition by ... order by ...) 取出分组后前n行数据
lead() over(partition by ... order by ...) 取出分组后后n行数据

一、rank()
rank() over(partition by A order by B)
是按照A进行分组，分组里面的数据按照B进行排序，over即在什么之上，rank()即跳跃排序(比如存在两个第一名，接下来就是第三名) 举例：

select 课程, 学生ID, 分数, rank() over (partition by 课程 order by 分数 desc) as 排名 from 成绩表

查询结果：

课程学生ID分数排名
语文1991
语文5991
语文7893
语文9794

二、row_number()
row_number() over(partition by A order by B)
row_number(): 如果有两个第一名时，只返回一个结果。 举例：

select 课程, 学生ID, 分数, row_number() over (partition by 课程 order by 分数 desc) as 排名 from 成绩表

查询结果：

课程学生ID分数排名
语文1991
语文5992
语文7893
语文9794

三、dense_rank()
dense_rank() over(partition by A order by B)
dense_rank(): 连续排序(如果有两个第一名时，接下来仍然是第二名) 举例：

select 课程, 学生ID, 分数, rank() over (partition by 课程 order by 分数 desc) as 排名 from 成绩表

查询结果：

课程学生ID分数排名
语文1991
语文5991
语文7892
语文9793
ToBlair_
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
数据库系统操作基础篇学习笔记
11-13
以下是对数据库系统操作基础篇学习笔记的详细解释，主要涵盖数据库的基本操作、表的操作以及数据的增删改查。 1. **数据库操作**： - **查询所有数据库**：使用`show databases;`命令可以列出MySQL服务器上的所有...
【Java学习笔记】JDBC基础篇
07-28
jdbc基础篇教程md版：这份markdown格式的教程详细介绍了jdbc的基础概念、配置方法以及如何通过jdbc进行数据库的增删改查（crud）操作。教程内容涵盖了jdbc驱动的加载、数据库连接的建立、sql语句的执行以及结果集的...
数据库学习笔记
亦梦亦醒乐逍遥的博客
03-08 6642
数据库知识一栏，配有ppt，但是中间从查询优化到并发控制没时间补了。
数据库笔记（基础篇）
hmoji
01-19 2069
文章目录数据库（基础篇）关系数据库基本概念关系的完整性实体完整性参照完整性用户自定义完整性关系代数传统集合运算符专门关系运算符关系数据库标准语言SQL模式的定义与删除定义模式删除模式基本表的定义、删除和修改定义基本表数据类型模式与表修改基本表删除基本表索引的建立和删除建立索引修改索引删除索引数据字典数据查询单表查询连接查询嵌套查询集合查询基于派生表的查询数据更新插入数据修改数据修改数据视图定义视图删除视图查询视图更新视图数据库的安全性授权授予收回收据库角色审计数据库的完整性实体完整性参照完整性定义参照完整性
mysql数据库学习（基础篇）
qianbao55的博客
03-23 3426
mysql数据库学习（基础） 题记：将分为入门（基础、操作）、进阶（底层实现原理学习、优化）进行学习笔记总结。本文为第一阶段，基础部分。 文章目录mysql数据库学习（基础）SQL（重点）SQL通用语法DDL数据库操作：DMLDQLDCL（简略）函数（略）：约束：外键约束：多表查询事务并发事务问题 定义： 数据库概念（DataBase，DB） 存储数据的仓库，数据是有组织的进行存储。 数据库管理系统（DBMS） 操纵和管理数据库的大型软件 linux 命令行打开数据库： service mysql star
数据库入门笔记（基础篇）
conquer_jsj的博客
05-06 2614
概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。目的：保证数据库中的数据的正确，有效性和完整性。分类：注意：约束时作用于表中字段上的，可以在创建表/修改表的时候添加约束。外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。SQL语句中嵌套select语句，称为嵌套查询，又称为子查询。子查询外部的语句可以是insert/update/delete/select的任何一个。
mysql8.0学习笔记基础篇
weixin_42120795的博客
03-13 1114
mysql8.0基础学习笔记
数据库学习的先导必备基础知识-笔记-01章：数据库概述
Noctiflorous_s的博客
08-18 909
数据库先导必备知识-笔记-01章-关系型、非关系型数据库的通俗举例以及关系型数据库的设计原则。
谷粒商城基础篇学习笔记
m0_74908430的博客
05-17 742
普通接口：使用interface关键字定义，可以包含抽象方法和默认方法。注解接口：使用@interface关键字定义，只能包含抽象方法和默认方法（Java 8+），抽象方法的默认返回类型是void，并且可以指定默认值。实现ConstraintValidator接口。
oracle学习笔记
qq_44620773的博客
03-17 1772
orcal基础知识点
软件测试工程师学习笔记11 - 数据库篇
Ayar的博客
03-20 770
软件测试工程师学习笔记 -11一、入门必读二、Linux三、数据库1.MySQL基础1） sql语言的注释2）mysql中常用数据类型3）表、字段、记录4）数据库中的常用命令4）字段的约束5）总结 一、入门必读 二、Linux 三、数据库        数据库：Database。是按照数据结构来组织、存储和管理数据的仓库。 1.MySQL基础 1） sql语言的注释 行注释：-- 进行一行代码注释 使用 ctrl + /进行代码行注释，
oracel数据库学习笔记
01-17
这篇学习笔记将带你走进Oracle的世界，通过基本的SQL语句学习，让你逐步掌握这个系统的操作与应用。 一、Oracle数据库简介 Oracle数据库是由甲骨文公司开发的，适用于各种规模的企业级应用。它支持多种操作系统平台...
mysql 数据库基础笔记
09-11
这篇笔记主要涵盖了MySQL数据库的基础操作，对于初学者来说非常有帮助。以下是一些关键知识点的详细解释： 1. **安装与目录导航**： 在Windows环境下，MySQL的可执行文件通常位于安装目录的`bin`子目录下。你可以...
spring事务详细讲解-深入浅出
小电玩
09-08 481
Spring 事务是本身就是对数据库的事务的支持，没有数据库的事务 Spring 是本身无法实现事务的。Spring只提供了统一事务管理接口，具体的实现还是由各数据库自己实现。在使用 Spring 事务时，可以通过注解或编程方式将需要进行事务管理的方法和代码块标记为事务性操作，当这些操作被执行时，Spring 会负责开启时根据当前环境中设置的隔离级别，调整数据库隔离级别。
【提效工具】AI工作流的1-10个实际落地场景
万物皆有灵
09-09 999
在这个快速发展的数字时代，人工智能（AI）正在以惊人的速度改变我们的工作方式。无论你是开发者、教育工作者还是内容创作者，AI工作流提示词助手都能为你提供无与伦比的支持。今天，我们将深入探讨，20个实际落地场景，帮助你更好地理解如何利用这些工具提升工作效率和创造力。💡。
PLSQL-将一份excel数据导入到一张物理表(Oracle)
最新发布
qq_38294275的博客
09-09 490
在Oracle中，FOR UPDATE子句用于SELECT语句中，以便在检索数据的同时锁定这些数据，防止其他会话在此期间修改它们。这个子句通常用于需要确保数据一致性的情况，特别是在多用户环境中，当多个会话可能同时尝试修改相同的数据时。研究了半天，看网上说的可以用：Tools → ODBC Importer ，然后再选择csv啥的即可，一打开我的PLSQL，啊，空白的啊，啥都没有…最后的最后，记得插入的数据/excel文件，要在第一列新增一列，空白也行/ID也行，总之要再插入一列，这样数据就不会错位。
jmeter之setUP、tearDown线程组
回家吃月饼的学习交流地
09-07 552
jmeter之setUP、tearDown线程组的使用
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值