bilibili

路线：

概念环境-数据定义语言-数据操作语言-数据查询语句-数据约束语法-多表设计和维护-高级多表查询-高级和新特性

概念环境

数据库概念

数据库是“按照数据结构来组织、存储和管理数据的仓库。 是一个长期存储在计算机内的、有组织的、可共享的、统一管理、的大量数据的集合文件。

分类：

• 非关系型数据库

  非关系型数据库并没有统一存储结构标准，现常见结构有键值、文档、JSON类型等，对高性能需求设计

  • 灵活的数据模型：支持多种数据模型，例如健值对、文档型、列族型和图形数据库。
  • 无固定的表结构：不需要预定义固定的表结构，适应非结构化或者半结构化数据。
  • 高性能读写：少关系型，注重高性能读写能力，适用于大规模数据和高并发访问模型。

• 关系型数据库

  数据按照类别进行存储，每个类别存储到一个容器（表）中，表和表之间可以建立关系可以进行关联操作，性能相对一般

  • 结构化数据模型：数据以表格形式存储。具有固定的结构，例如：学生和分数分别存储到不同的表
  • AC1D事务：通过强大的事务支持，保证放据的原子性、一致性、隔离性和特久性。例如：转账失败，钱不损失
  • 丰富查询语句：支持SQL语句，能够进行复杂的关联数据查询。例如：查询学生以及学生的分数
  • 数据一致性：数据的关系和约束确保数据的一致性和完整性。例如：存储学生数据，保证身份证号唯一且不为空

• 存储设计规则、E-R模型

  • 遵循ER模型

    E(Entity)代表实体类别，关系型数据库中一类数据对应数据库中的一张表存储 R(Relationship)表和表可以维护某种关系，可以通过关系进行多表操作

  • 模型解释 ·数据库中最大的存储单位为库下 ·每类数据存储一张表中，表存储到库中 ·表具有一些特性列，这些特性列定义数据在表中如何存储 ·表中的数据是按照行存储，一行即为一条记录

• 常见关系型数据库管理系统

  • oracle、MySQL、DB2、SQLite

• 安装

  • MySQL服务器监听端口3306

  • MySQL服务开机自启动，右键管理服务

    1 2 3

    #services.msc net stop MySQL net start MySQL

  • 添加环境变量 %JAVA_HOME%\bin

• 结构化查询语句(SQL)

  • 数据定义DDL-创建和修改盛放数据的容器，表

    mysql -u -p -h

    1 2 3 4 5 6

    mysql -u root -p123456 mysql -uroot -p123456 -h127.0.0.1 -P3306 #密文 C:\Users\DELL>mysql -u root -p Enter password: ******

    -u ：用于指定你要连接的 MySQL 数据库的用户名。 -p：表示密码，后面紧跟着密码，中间没有空格。如果你不希望在命令行中显示密码，可以不指定密码，直接 -p，然后在提示下手动输入密码。 -h ：用于指定 MySQL 服务器的主机名或者 IP 地址。如果 MySQL 在本地运行，可以用 localhost。 -P ：用于指定连接 MySQL 服务器的端口号，默认情况下是 3306。 ：是你要连接的数据库的名称。连接后会默认使用这个数据库。

    SQL注释：

    单行注释：#注释内容

    单行注释：– 注释内容 其中–后面的空格必须有

    多行注释：/* 注释内容 */

    版本和退出连接：

    – 查看版本，命令符号之间空格隔开，每条命令后使用；结束，否则不执行。切记！！

    mysql>select version();

    – 退出连接

    mysql> exit;

  • 数据操纵DML-表中添加修改删除

  • 数据查询DQL-表中数据多条件查询

  • 数据控制TCL-事务启动、提交、回滚

  • 事务控制DCL-账号创建、权限控制

数据定义语言

• 数据定义语言DDL

  • 数据的存储过程：创建库–定字段–创建表–插数据
  • DDL用于定义和管理数据库的结构，包括库、表、索引、视图等数据库对象的创建、修改和删除。 DDL不涉及对数据的操作，而是关注数据库的结构和元数据（容器）。
  • 关键字：
  • CREATE:用于创建数据库、表、素引、视图等。 ALTER:用于修改数据库对象的结构，如修改表结构、添加列、删除列等。 DROP:用于别除数据库对象，如别除表、删除素引等。

• SQL命名规定和规范

  • 标识符命名规定

    1.数据库名、表名不得超过30个字符，变量名限制为29个

    2.必须只能包含 A–Z, a–z, 0–9, _共63个字符，而且不能数字开头

    3.数据库名、表名、字段名等对象名中间不能包含空格

    4.同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名

    5.必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来https://dev.mysql.com/doc/refman/8.0/en/keywords.html

  • 标识命名规范

    1.注释应该清晰、简洁地解释 SQL 语句的意图、功能和影响。

    2.库、表、列名应该使用小写字母，并使用下划线（_)或驼峰命名法。

    3.库、表、字段名应该简洁明了，具有描述性，反映其所存储数据的含义。

    4.库名应于对应的程序名一致 例如：程序名为 EcommercePlatform 数据库名命名为 ecommerce_platform"

    5.表命名最好是遵循 “业务名称_表”的作用 例如：alipay_task 、 force_project、 trade_config

    6.列名应遵循“表实体_属性”的作用 例如：product_name 或 productName

• 创建库

  • 方式1：创建数据库，使用默认的字符集和排序方式

    CREATE DATABASE 数据库名；

  • 方式2：判断并创建默认字符集库（推荐）

    CREATE DATABASE IF NOT EXISTS 数据库名；

  • 方式3：创建指定字符集库或者排序方式

    CREATE DATABASE 数据库名 CHARACTER SET 字符集；

    CREATE DATABASE 数据库名 COLLATE 排序规则；

  • 方式4：创建指定字符集和排序规则库

    CREATE DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序规则；

  • MySQL8默认值(不同版本可能会有不同)：

    字符集：utf8mb4 是一种广泛支持各种语言字符的字符集。

    排序规则：utf8mb4_0900_ai_ci 是一种不区分大小写的排序规则

    # 查看默认字符集和排序方式命令

    SHOW VARIABLES LIKE ‘character_set_database’;

    SHOW VARIABLES LIKE ‘collation_database’;

  • 字符集和排序规则：

    字符集就是我们常说的编码格式，决定了数据如何编码存储！

    排序规则决定了如何比较和排序存储在数据库中的文本数据。

    常见字符集（Character Set）：

    1.utf8：早期版本的字符集，最多3字节存储一个字符，3字节无法覆盖全部unicode编码，有显示乱码可能。

    2.utfmb4（8+默认）：解决utf8的存储限制，使用4字节进行字符存储，可以覆盖更广 Unicode 编码，包括表情符号等等。

    常见排序规则（Collate）：

    1.utf8mb4_0900_ai_ci：UTF-8 的不区分大小写的排序规则((mysql8+的默认排序规则)。

    2.utf8mb4_0900_as_cs：UTF-8 的 Unicode 排序规则，区分大小写！。

• 库管理：查看和使用库

  • 查看当前所有库：SHOW DATABASES ；
  • 查看当前使用库：SELECT DATABASE()；
  • 查看指定库下所有表：SHOW TABLES FROM 数据库名；
  • 查看创建库的信息：SHOW CREATE DATABASE 数据库名;
  • 切换/选中库：USE 数据库名;
  • 注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，先use库

• 库管理：修改库

  • 修改库编码字符集

    ALTER DATABASE 数据库名 CHARACTER SET 字符集； # 修改字符集gbk utf8

    ALTER DATABASE 数据库名 COLLATE 排序方式; #修改排序方式

    ALTER DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序方式; # 修改字符集和排序方式

    注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

  • 删除库

    直接删除库：DROP DATABASE 数据库名;

    判断并删除库（推荐）：DROP DATABASE IF EXISTS 数据库名;

• 创建表

  • 1 2 3 4 5 6 7 8 9

    CREATE TABLE 表名 ( 列名 类型 [列可选约束], 列名 类型 [列可选约束] [COMMENT '列可选注释'], 列名 类型 [列可选约束] [COMMENT '列可选注释'], 列名 类型 [列可选约束] [COMMENT '列可选注释'], 列名 类型 [列可选约束] [COMMENT '列可选注释'], #列之间使用，分割 [列可选约束] ) [表可选约束] [COMMENT '表可选注释'];

  • 1 2 3 4 5 6 7 8 9

    CREATE TABLE [IF NOT EXISTS] 表名 ( 列名 类型 [列可选约束], 列名 类型 [列可选约束] [COMMENT '列可选注释'], 列名 类型 [列可选约束] [COMMENT '列可选注释'], 列名 类型 [列可选约束] [COMMENT '列可选注释'], 列名 类型 [列可选约束] [COMMENT '列可选注释'], #列之间使用，分割 [列可选约束] ) [表可选约束] [COMMENT '表可选注释'];

• 数据类型

  • 整数、浮点数、定点数、字符串、日期时间

  • 整数：MySQL支持SQL标准整数类型 INTEGER （或 INT ）和 SMALLINT 。作为标准的扩展，MySQL 还支持整数类型 TINYINT 、 MEDIUMINT 和 BIGINT 。

    类型	存储（字节）	最小值有符号	最小值无符号	最大值有符号	最大值无符号
    TINYINT	1	-128	0	127	255
    SMALLINT	2	-32768	0	32767	65535
    MEDIUMINT	3	-8388608	0	8388607	16777215
    INT	4	-2147483648	0	2147483647	4294967295
    BIGINT	8	-2^63	0	2^63-1	2^64-1

    注意：无符号==无负号，整数类型都可以添加unsigned 修饰符，添加以后对应列数据变成无负号类型，值从0开始！！

    1 2 3

    stu_age tinyint unsigned COMMENT '年龄字段，tinyint类型，无符号值从0开始', stu_age tinyint COMMENT '年龄字段，tinyint类型，无符号值从-128开始', # unsigned 必须紧贴类型后放置

  • 浮点数

    FLOAT 和 DOUBLE 类型表示近似的数值数据值。MySQL 使用 4 个字节表示单精度值，使用 8 个字节表示双精度值。

    **注意：**从 MySQL 8.0.17 开始，不推荐使用非标准 FLOAT(M,D) 语法 DOUBLE(M,D) ，未来版本中可能删除对它的支持。支持unsigned修饰，添加修饰，只保留正值范围负值不会迁移到正值！

    类型	存储（字节）	M（小数＋整数位数）	D（小位数）
    FLOAT(M,D)	4	M最大为24	D最大为8
    DOUBLE(M,D)	8	M最大为53	D最大为30

    1	stu_height float(4,1) unsigned COMMENT '身高，保留一位小数，多位会四舍五入';

  • 定点数

    DECIMAL 类型存储精确的数值数据值。当需要保持精确精度时，例如货币数据，商品价格等等！

    类型	存储（字节）	M（小数+整数位数）	D（小位数）
    BECIMAL(M,D)	动态计算	M最大为65	D最大为30

    **注意：**DECIMAL 类型的存储空间是可变的，它的存储大小受 DECIMAL 数据类型定义时指定的精度和规模影响。如果D小数位数为 0， DECIMAL 则值不包含小数点或小数部分。

    1	emp_salary DECIMAL(8,1) COMMENT '工资，保留一位小数，多位会四舍五入';

  • 字符串

    字符串(文本)	特点	长度	长度范围（字符）	存储空间
    CHAR(M)	固定长度	M	0 <= M <= 255	M*4个字节（utf8mb4）
    VARCHAR(M)	可变长度	M	MySql一行数据最多65535字节	(M*4+1) 个字节（utf8mb4）

    **注意：**CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。

    保存数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。 当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。

    VARCHAR(M) 定义时，必须指定长度M，否则报错。

    MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）；MySQL5.0版本以上，varchar(20)：指的是20字符。

    检索VARCHAR类型的字段数据时，会保留数据尾部的空格。

    假设使用单字节字符集，如 latin1

  • 插入值	CHAR(4)	所需存储	VARCHAR(4)	所需存储
    ''	' '	4 bytes	''	1 byte
    ‘ab’	‘ab '	4 bytes	‘ab’	3 bytes
    ‘abcd’	‘abcd’	4 bytes	‘abcd’	5 bytes

  • 文本类型

    在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT 类型。

    在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和 VARCHAR类型相同。

    文本字符串类型	特点	长度(字符)	存储范围（字节）	占用的存储空间
    TINYTEXT	小文本、可变长度	L	0 <= x <= 255	L + 2 个字节
    TEXT	文本、可变长度	L	0 <= x <= 65535	L + 2 个字节
    MEDIUMTEXT	中等文本、可变长度	L	0 <= x <= 16777215	L + 3 个字节
    LONGTEXT	大文本、可变长度	L	0 <= x<= 4294967295	L + 4 个字节(最大4g)

    短文本，固定长度使用char 例如：性别，手机号

    短文本，非固定长度使用varchar 例如：姓名，地址

    大文本，建议存储到文本文件，使用varchar记录文件地址，不使用TEXT，直接存储大文本，他性能非常较差！

    1 2 3

    CREATE TABLE 表名( tx TEXT );

  • 时间类型

  用于表示时态值的日期和时间数据类型为 DATE 、TIME、DATETIME、TIMESTAMP 和 YEAR 。每种类型都有一个有效值范围，换一种思路，可以理解时间类型就是特殊格式的字符串

  类型	名称	字节	日期格式	小值	最大值
  YEAR	年	1	YYYY或YY	1901	2155
  TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
  DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
  DATETIME	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
  TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00	2038-01-19 03:14:07

  **注意：**year类型赋00-99值对应年限，[00-69]对应[2000-2069],[70-99]对应[1970-1999],建议四位年值！

  默认情况下，时间需要主动赋予默认值和修改值！

  **扩展：**DATETIME和TIMESTAMP类型自动初始化和更新

  1 2 3 4 5 6

  # 方式1： 插入默认当前时间和修改自动更新当前时间 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP # 方式2： 插入默认当前时间 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP

• 修改表：

  • 修改表中的列（字段）

    1 2 3 4 5 6 7 8

    # 修改表，添加一列[可以指定X字段前或者后] ALTER TABLE 表名 ADD 字段名 字段类型 【FIRST|AFTER 字段名】; # 修改表，修改列名 ALTER TABLE 表名 CHANGE 原字段名 字段名 新字段类型 【FIRST|AFTER 字段名】; # 修改表，修改列类型 ALTER TABLE 表名 MODIFY 字段名 新字段类型 【FIRST|AFTER 字段名】; # 修改表，删除一列 ALTER TABLE 表名 DROP 字段名 ;

  • 修改表名

    1 2	# 修改表名 ALTER TABLE 表名 RENAME [TO] 新表名;

• 删除表

  • 删除数据表

    1 2	# 删除表 DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

  • 清空表数据

    1 2	# 清空表数据 TRUNCATE TABLE 表名;

数据操作语言

• 数据操纵语句DML

  • 增删改，DML 不影响库表结构，但是会真正影响数据库数据。

  • •INSERT：用于数据插入关键字。

    •UPDATE：用于数据修改关键字。

    •DELETE：用于数据删除关键字。

  • 数据操作最基本单位行

  • 插入数据–字符和日期型数据应包含在单引号中

    情况1：为表的一行所有字段（列）插入数据—值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

    1	INSERT INTO 表名 VALUES (value1,value2,....);

    情况2：为表的一行指定字段（列）插入数据—值列表中需要为表名后指定的列指定值，并且值的顺序和类型必须和指定的列顺序相同。

    1	INSERT INTO 表名(列名1,列名2.....） VALUES (value1,value2,....);

    情况3：同时插入多条记录—情况1、情况2都可以转成一次插入多表数据，只需要在values后面写多个(值1,值2…)即可，()代表一行。

    1 2 3

    INSERT INTO 表名 VALUES (value1,value2,....) ,...., (value1,value2,....); # 或者 INSERT INTO 表名(列名1,列名2.....) VALUES (value1,value2,....) ,...., (value1,value2,....);

  • 修改数据

    情况1：修改表中所有行数据（全表修改）—更新表中所有行的指定列数据。

    1 2	UPDATE table_name SET column1=value1, column2=value2, … , column=valuen

    情况2：修改表中符合条件行的数据（条件修改）

    1 2 3

    UPDATE table_name SET column1=value1, column2=value2, … , column=valuen [WHERE condition]

  • 删除数据

    情况1：删除表中所有行数据（全表删除）

    1	DELETE FROM table_name;

    情况2：删除表中符合条件行的数据（条件删除）

    1	DELETE FROM table_name [WHERE condition]

  • 条件且或 and or

数据查询语句（单表）

• 数据查询语句（Data Query Language）

  DQL 用于查询数据库数据操作。DQL 不影响库表结构，也不会影响原表的数据。

  DQL 会基于原表数据查询出一个虚拟表。

  •SELECT：用于查询数据的关键字。

• 单表查询和多表查询

  • 单表：select语法、where条件、运算符号、单行多行函数、分组和排序
  • 多表：多表合并语法–中间虚拟表–查询语句–虚拟表：多表数据合并语法

• 基础select语法（不指定条件）

  • 非表查询

    类似Java控制台输出，直接输出结果。快速输出计算结果和函数结果

    1 2 3

    SELECT 1; SELECT 9/2; SELECT VERSION();

  • 指定表

    指定表，查询表中的全部或者某些列。

    列和列之间使用[ , ] 分割，如果是全部列可以使用*替代

    1 2 3 4

    SELECT 列名1, 列名2, 列名3 FROM 表名； 或者 SELECT * FROM 表名； SELECT 表名.列名 , 表名.* FROM 表名；

  • 查询列起别名

    查询列可以起别名，as 可以省略。

    起别名的意义主要是简化列名或者对应后期Java数据属性等–自动映射设计类，要求列名和类名一致，起个别名方便映射

    如果别名想要区分大小写，可以添加双引号 例如：“Name”

    1 2 3

    SELECT 列名1 as 别名, 列名2, 列名3 as 别名 FROM 表名； 或者 SELECT 列名1 别名, 列名2, 列名3 别名 FROM 表名；

  • 去除重复行

    指定列值去重复行，可以指定单列或者多列，但是DISTINCT关键字只写一次且在前面。

    1	SELECT DISTINCT 列名 [,列名,列名] FROM 表名;

  • 查询常数

    SELECT 查询还可以对常数进行查询。就是在 SELECT 查询结果中增加固定的常数列。

    这列的取值是我们指定的，而不是从数据表中动态取出的。

    1	SELECT '尚硅谷' as corporation , 列名 , 列名… FROM 表名;

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

    CREATE TABLE `t_employee` ( `eid` INT NOT NULL COMMENT '员工编号', `ename` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名', `salary` DOUBLE NOT NULL COMMENT '薪资', `commission_pct` DECIMAL(3,2) DEFAULT NULL COMMENT '奖金比例', `birthday` DATE NOT NULL COMMENT '出生日期', # 枚举类型，二选一 `gender` ENUM('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '男' COMMENT '性别', `tel` CHAR(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号码', `email` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱', # set类型，可以选多个 `address` VARCHAR(150) DEFAULT NULL COMMENT '地址', `work_place` SET('北京','深圳','上海','武汉') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '北京' COMMENT '工作地点' ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

    • ENGINE=INNODB

      这部分指定了表将使用的存储引擎为InnoDB。InnoDB是MySQL的默认存储引擎（从MySQL 5.5.5版本开始），它支持事务处理（Transaction Processing）、行级锁定（Row-level Locking）和外键（Foreign Keys）等高级数据库功能。InnoDB表是存储在磁盘上的，但它也提供了内存中的缓存来加速访问。

    • SELECT NOW();

    1 2 3 4 5 6 7 8 9 10 11 12 13 14

    INSERT INTO `t_employee`(`eid`,`ename`,`salary`,`commission_pct`,`birthday`,`gender`,`tel`,`email`,`address`,`work_place`) VALUES (1,'孙洪亮',28000,'0.65','1980-10-08','男','13789098765','shl@atguigu.com','白庙村西街','北京,深圳'); # 查询所有员工信息，并且添加一列 etype,值固定为`总部` SELECT * , '总部' etype FROM t_employee; SELECT'总部' etype , * FROM t_employee; # * 通配符,必须放在第一位!! # 查询所有员工姓名，月薪和年薪（年薪等于月薪*12,结果列字段为 姓名 , 月薪 ， 年薪 ） SELECT ename 姓名,salary 月薪 , salary * 12 AS 年薪 FROM t_employee; # 查询所有员工姓名，月薪，每月奖金，每月总收入（结果列字段为 姓名 , 月薪 ， 奖金，月总 ） SELECT ename 姓名 , salary 月薪 , salary * IFNULL(commission_pct,0) 奖金 , salary + salary * IFNULL(commission_pct,0) 月总收入 FROM t_employee; # 因为,有些员工没有奖金, 奖金占比就是null, null 运算 任何值 = null # ifnull(列,为null你给与的默认值) 0

    null 运算 任何值 = null

    ifnull(列,为null你给与的默认值0)

  • 显示表结构

    1 2 3

    DESCRIBE employees; 或 DESC employees;

    Field：表示字段名称。

    Type：表示字段类型。

    Null：表示该列是否可以存储NULL值。

    Key：表示该列是否已编制索引。

    ​ PRI表示该列是表主键的一部分；

    ​ UNI表示该列是UNIQUE索引的一部分；

    ​ MUL表示在列中某个给定值允许出现多次。

    Default：表示该列是否有默认值，如果有，那么值是多少。

    Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等

    AUTO_INCREMENT：这是最常见的“Extra”信息之一。当列被设置为自增（AUTO_INCREMENT）时，每当向表中插入新行而不指定该列的值时，数据库会自动为该列生成一个唯一的数字，这个数字是递增的。这通常用于主键列，以确保每条记录都有一个唯一的标识符。

    ON UPDATE CURRENT_TIMESTAMP：这表示该列的值会在每次记录更新时自动设置为当前的日期和时间。这通常用于记录数据最后更新的时间戳。

  • 过滤数据（条件查询）

  where添加以后，就不是全表查询，先过滤条件，符合，再返回指定列。

  步骤1：先逐行where条件判断，得到综合结果true | false

  步骤2：再根据 select指定列，得到最终结果！

  1	SELECT 字段1,字段2 FROM 表名 WHERE 过滤条件;

  1 2	#查询工资高于8000且性别为女的员工信息 SELECT * FROM t_employee WHERE salary > 8000 AND gender = '女';

• 运算符的使用

  算数运算符：算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

  注意：运算表达式可以应用在select列位置或者where条件后！

  运算符	描述
  %, MOD	模运算符
  *	乘法运算符
  +	加法运算符
  -	减号运算符
  /	浮点除法
  DIV	整数除法

  注意：运算符优先级与我们之前学习一致，提高优先级可以使用()

  ​ * 如果有浮点，会保留浮点类型，参数都是整数结果整数

  ​ / DIV的区别，/ 浮点除法，DIV整除除法

  ​ 如果出现除以零的情况，通常会返回 NULL，而不是抛出错误

• 比较运算符

  • 比较运算符的结果为 1、0 、null , 1 代表true，0和null代表false

    操作适用于数字和字符串。根据需要，字符串会自动转换为数字，数字会自动转换为字符串,不需要考虑类型 ‘1’ = 1 为true

    名字	描述
    >	大于运算符
    >=	大于或等于运算符
    <	小于操作符号
    <=	小于或等于运算符
    <>, !=	不相等的运算符，（!= 非标准）
    =	相等运算符
    <=>	NULL 安全等于运算符，（非标准）
    IS NULL	NULL 值测试
    IS NOT NULL	NOT NULL 值测试
    BETWEEN … AND …	值是否在值范围内 （1,5）
    NOT BETWEEN … AND …	值是否不在值范围内
    IN()	值是否在一组值中 （1和5）
    NOT IN()	值是否不在一组值中
    LIKE	简单的模式匹配（模糊等于）
    NOT LIKE	否定简单的模式匹配

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

    SELECT '.01' = 0.01; -> 1 SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; -> 1, 1, 0 SELECT 1 <=> 1, NULL IS NULL, 1 IS NULL; -> 1, 1, 0 SELECT 1 = 1, NULL = NULL, 1 = NULL; -> 1, NULL, NULL # 如果双方都是字符串，就按照字符串比较，不会转成数字比较值 SELECT '.01' <> '0.01'; -> 1 SELECT .01 <> '0.01'; -> 0 SELECT 'zapp' <> 'zappp'; -> 1 # 表达式 expr BETWEEN min AND max # 等效于表达式 (min <= expr AND expr <= max) # 字符串就是按照ascii编码排序 #有字符串和数字，会自动值转化 mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1; -> 1, 0 mysql> SELECT 1 BETWEEN 2 AND 3; -> 0 mysql> SELECT 'b' BETWEEN 'a' AND 'c'; -> 1 mysql> SELECT 2 BETWEEN 2 AND '3'; -> 1 # expr IN (value,...) mysql> SELECT 2 IN (0,3,5,7); -> 0 mysql> SELECT 'wefwf' IN ('wee','wefwf','weg'); -> 1 # LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。 # LIKE运算符通常使用如下通配符： # "%"：匹配0个或多个字符。 # "_"：只能匹配一个字符。 # 第三个是_ _ _ SELECT 'aaa' LIKE '%' , 'aaa' LIKE '_' , 'aaa' LIKE '___' ; -> 1,0,1 SELECT 'abc' LIKE '_a%' , 'abc' LIKE 'a__' , 'abc' LIKE '%c' ; -> 0,1,1 # 多列一次对比 (a, b) = (x, y) 等同于 (a = x) AND (b = y) (a, b) <=> (x, y) 等同于 (a <=> x) AND (b <=> y) (a, b) <> (x, y) 等同于 (a <> x) OR (b <> y)

    LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。 LIKE运算符通常使用如下通配符： "%"：匹配0个或多个字符。 “_"：只能匹配一个字符。

• 逻辑运算符

  • 逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。MySQL将任何非零，非 NULL 值计算为 TRUE ！

    逻辑运算符	描述
    AND, &&	逻辑且
    NOT, !	否定值
    OR, ||	逻辑或
    XOR	逻辑异或 (一真一假)

    1 2 3 4 5 6

    SELECT NOT 10; -> 0 SELECT NOT 0; -> 1 SELECT 1 XOR 1; -> 0 SELECT 1 XOR 0; -> 1 SELECT 1 XOR 1 XOR 1; -> 1

    1 2 3 4 5 6

    # 查询薪资大于5000并且工作地点为'北京'的员工信息。 SELECT * FROM t_employee WHERE salary > 5000 AND work_place LIKE '%北京%'; # FIND_IN_SET('值',列名) -> 值是否出现: 出现 1 不出现0 ; SELECT ename , FIND_IN_SET('北京',work_place) FROM t_employee; SELECT * FROM t_employee WHERE salary > 5000 AND FIND_IN_SET('北京',work_place) = 1;

  • 运算符优先级

    

• 单行和多行函数

  • 单行函数：数值函数、日期函数、字符函数、流程函数、信息函数、时间函数—对一行中的某列操作函数，返回结果是单一值

  • 多行函数：聚合函数—对多行中的某列操作函数，返回结果是单一值

  • 数值函数

    函数	用法
    ABS(x)	返回x的绝对值
    SIGN(X)	返回X的符号。正数返回1，负数返回-1，0返回0
    PI()	返回圆周率的值
    CEIL(x)，CEILING(x)	返回大于或等于某个值的最小整数
    FLOOR(x)	返回小于或等于某个值的最大整数
    LEAST(e1,e2,e3…)	返回列表中的最小值
    GREATEST(e1,e2,e3…)	返回列表中的最大值
    MOD(x,y)	返回X除以Y后的余数
    RAND()	返回0~1的随机值
    RAND(x)	返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
    ROUND(x)	返回一个对x的值进行四舍五入后，最接近于X的整数
    ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
    TRUNCATE(x,y)	返回数字x截断为y位小数的结果
    SQRT(x)	返回x的平方根。当X的值为负数时，返回NULL

    1 2 3

    SELECT ABS(-5),CEIL(2.3), CEIL(-2.3), FLOOR(2.3), FLOOR(-2.3), TRUNCATE(RAND(),2),RAND(8),RAND(8), ROUND(2.3),ROUND(2.36,1),TRUNCATE(2.36,1); 5 3 -2 2 -3 0.53 0.15668530311126755 0.15668530311126755 2 2.4 2.3

  • 字符串函数

    函数	用法
    CHAR_LENGTH(s)	返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
    LENGTH(s)	返回字符串s的字节数，和字符集有关
    CONCAT(s1,s2,……,sn)	连接s1,s2,……,sn为一个字符串
    INSERT(str, idx, len, replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
    REPLACE(str, a, b)	用字符串b替换字符串str中所有出现的字符串a
    UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
    LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
    LEFT(str,n)	返回字符串str最左边的n个字符
    RIGHT(str,n)	返回字符串str最右边的n个字符
    TRIM(s)	去掉字符串s开始与结尾的空格
    SUBSTR(s,index,len)	返回从字符串s的index位置取len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同
    FIND_IN_SET(s1,s2)	返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串（只找第一个）
    REVERSE(s)	返回s反转后的字符串
    NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

    注意：MySQL中，字符串的位置是从1开始的。

  • 时间函数

    函数	用法
    CURDATE() ，CURRENT_DATE()	返回当前日期，只包含年、月、日
    CURTIME() ， CURRENT_TIME()	返回当前时间，只包含时、分、秒
    NOW() / SYSDATE()	返回当前系统日期和时间
    UTC_DATE()	返回UTC（世界标准时间）日期
    UTC_TIME()	返回UTC（世界标准时间）时间

    函数	用法
    YEAR(date) / MONTH(date) / DAY(date)	返回具体的日期值
    HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值
    MONTHNAME(date)	返回月份：January，…
    DAYNAME(date)	返回星期几：MONDAY，TUESDAY…..SUNDAY
    WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
    QUARTER(date)	返回日期对应的季度，范围为1～4
    WEEK(date) ， WEEKOFYEAR(date)	返回一年中的第几周
    DAYOFYEAR(date)	返回日期是一年中的第几天
    DAYOFMONTH(date)	返回日期位于所在月份的第几天
    DAYOFWEEK(date)	返回周几，注意：周日是1，周一是2，。。。周六是7

    1 2 3

    SELECT NOW(), CURDATE(), CURTIME(), UTC_DATE(),UTC_TIME(), YEAR(NOW()) , MONTH(NOW()) , WEEK(NOW()),WEEKDAY(NOW()) , DAYOFWEEK(NOW()),DAY(NOW()) , DAYOFMONTH(NOW()); 2024-08-02 14:30:07 2024-08-02 14:30:07 2024-08-02 06:30:07 2024 8 30 4 6 2 2

    函数	用法
    DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间 （加expr天）
    DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)	返回与date相差INTERVAL时间间隔的日期 （减expr天）

    函数	用法
    ADDTIME(time1,time2)	返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
    SUBTIME(time1,time2)	返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
    DATEDIFF(date1,date2)	返回date1 - date2的日期间隔天数
    TIMEDIFF(time1, time2)	返回time1 - time2的时间间隔
    FROM_DAYS(N)	返回从0000年1月1日起，N天以后的日期
    TO_DAYS(date)	返回日期date距离0000年1月1日的天数
    LAST_DAY(date)	返回date所在月份的最后一天的日期
    MAKEDATE(year,n)	针对给定年份与所在年份中的天数返回一个日期
    MAKETIME(hour,minute,second)	将给定的小时、分钟和秒组合成时间并返回

    1 2 3 4

    SELECT ADDDATE(NOW(),INTERVAL 1 MONTH) , ADDDATE(NOW(),INTERVAL -1 MONTH) , ADDTIME('10:10:10',20) , ABS(DATEDIFF(CURDATE(),'2024-11-11')) , TIMEDIFF('12:00:00','10:00:00'); 2024-09-02 14:40:34 2024-07-02 14:40:34 10:10:30 101 02:00:00

    函数	用法
    DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
    TIME_FORMAT(time,fmt)	按照字符串fmt格式化时间time值
    STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

    格式符	说明	格式符	说明
    %Y	4位数字表示年份	%y	表示两位数字表示年份
    %M	月名表示月份（January,….）	%m	两位数字表示月份（01,02,03。。。）
    %b	缩写的月名（Jan.，Feb.，….）	%c	数字表示月份（1,2,3,…）
    %D	英文后缀表示月中的天数（1st,2nd,3rd,…）	%d	两位数字表示月中的天数(01,02…)
    %e	数字形式表示月中的天数（1,2,3,4,5…..）
    %H	两位数字表示小数，24小时制（01,02..）	%h和%I	两位数字表示小时，12小时制（01,02..）
    %k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4….）
    %i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02…)
    %W	一周中的星期名称（Sunday…）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，..）
    %w	以数字表示周中的天数(0=Sunday,1=Monday….)
    %j	以3位数字表示年中的天数(001,002…)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
    %u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
    %T	24小时制	%r	12小时制
    %p	AM或PM	%%	表示%

    1 2 3 4 5

    SELECT NOW(),DATE_FORMAT(NOW(),'%Y年%m月%d日'),TIME_FORMAT(NOW(),'%H:%i:%s'); 2024-08-02 14:50:33 2024年08月02日 14:50:33 SELECT STR_TO_DATE('2024年04月20日' , '%Y年%m月%d日'); 2024-04-20

    1 2 3 4 5 6 7 8 9

    # 查询今天过生日的员工信息 # 2024-04-20 - 04-20 SELECT * FROM t_employee WHERE DAY(birthday) = DAY(NOW()) AND MONTH(birthday) = MONTH(NOW()); SELECT * FROM t_employee WHERE DATE_FORMAT(birthday,'%m-%d') = DATE_FORMAT(NOW(),'%m-%d'); # 查询员工姓名，工资，年龄(保留1位小数点)信息 SELECT ename, salary , ROUND(DATEDIFF(NOW(),birthday) / 365 , 1) AS age FROM t_employee; # 查询年龄在25-35之间的员工信息 SELECT * FROM t_employee WHERE ROUND(DATEDIFF(NOW(),birthday) / 365 , 1) BETWEEN 25 AND 35;

  • 流程函数

    IF 函数是一种条件函数，用于在 SQL 查询中执行基本的条件判断。

    1 2 3

    # 解释: 当 condition 成立时，返回 true_value，否则返回 false_value。 IF(condition, true_value, false_value)

    IFNULL函数是 MySQL 中的一个函数，用于处理 NULL 值。

    1 2 3

    # 解释: 当指定列column值为null, 取null_value的值作为结果。 IFNULL(column, null_value)

    CASE 表达式用于实现多条件判断，并根据条件的结果返回不同的值

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

    # 格式1 CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END [AS alias_name] # 这种形式中，WHEN 子句后面跟着一个条件，而不是一个具体的值。 # 格式2 CASE expr WHEN value1 THEN result1 WHEN value2 THEN result2 ... ELSE default_result END [AS alias_name] # 这种形式中，expr 是要比较的表达式或列名，然后逐个与 WHEN 子句中的值进行比较。

  • 聚合函数

    聚合函数作用于全部或者分组数据进行统计和计算，最终返回一条结果。

    聚合函数碰到null是如何处理的? 聚合函数时冷漠的,不计数,不处理,不关注!

    聚合函数之间是不能嵌套的!

    函数	用法
    AVG(列名)	计算某一列的平均值（数值类型）
    SUM(列名)	计算某一列的和（数值类型）
    MIN(列名)	计算某一列的最小值(任意类型)
    MAX(列名)	计算某一列的最大值(任意类型)
    COUNT(列名/*/1)	计算某一列或者行的记录数(任意类型) （列名：非空出现次数，*/1：行的次数）

• 高级查询处理：

  • 分组查询

    先将数据行，按照某一或者多特性列进行分组，最后查询每组的特性，分组查询的结果只能是分组特性列或者聚合函数！

    1 2 3 4

    SELECT 分组列, 分组列, 聚合函数 FROM table [WHERE condition] [GROUP BY 分组列,分组列… HAVING 分组后条件]

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

    # 查询每种性别的员工数量以及性别平均工资 SELECT gender , COUNT(*) ,AVG(salary) FROM t_employee GROUP BY gender; 男 14 11377.714285714286 女 13 11808.384615384615 # 查询生日年份、性别相同的人数和平均工资( year()) SELECT YEAR(birthday),gender,COUNT(*) , AVG(salary) FROM t_employee GROUP BY YEAR(birthday),gender; 1978 男 1 8567 1978 女 1 16788 ... 1990 女 2 13989 1995 女 1 5000 # 查询工资高于5000，每种性别的员工数量以及性别平均工资 [条件工资高于5000,分组前的筛选条件 where] SELECT gender , COUNT(*) ,AVG(salary) FROM t_employee WHERE salary > 5000 GROUP BY gender; # 查询平均工资高于11000的性别和性别人数 [按照性别分组,分组以后筛选平均工资大于5000的组](平均工资大于11000是分组后的条件 having) SELECT gender , COUNT(*) ,AVG(salary) FROM t_employee GROUP BY gender HAVING AVG(salary) > 11000; SELECT gender , COUNT(*) ,AVG(salary) AS av FROM t_employee GROUP BY gender HAVING av > 11000; # having是分组后的条件 where是分组前的条件 # having只能在group by后面出现 where 随时可以出现 # having比较一般(99.999%)都是聚合函数 where可以是任何参数的比较 # having可以使用select后面查询到的列的名称 || having在分组之后执行! 查询结果已经有了! 我们可以进行复用 # 而where不能复用select列 where早于select列名

  • 排序查询

    按照某一或者多特性列进行数据排序，不会影响结果条数，只是改变结果排序！

    1 2 3 4 5 6 7

    SELECT 列, 列, 函数 FROM table [WHERE condition] [ORDER BY 排序列 ASC|DESC, 排序列 ASC|DESC ……] # ASC为正序（默认值），DESC为倒序 # 多列排序，只有第一列相同，第二列才会生效以此类推…

    1 2 3 4 5

    # 按照工资倒序，如果工资相同，按照年龄正序排序查询员工信息(从小到大 生日从大到小) SELECT * FROM t_employee ORDER BY salary DESC , birthday DESC; # 查询有奖金的员工(where)，最终按照工资倒序显示员工信息 SELECT * FROM t_employee WHERE commission_pct IS NOT NULL ORDER BY salary DESC;

  • 数据切割（分页查询）

    将结果，进行分页切割，按照指定的区域一段一段的进行展示！

    例如：商品分页展示、查询工资前三的员工等

    limit 10; 但是数据库一共有5条数据! 只会返回5条 limit关键字真的影响数据, limit放在select语句的最后!

    1 2 3 4 5 6 7

    SELECT 列, 列, 函数 FROM table [WHERE condition] [LIMIT [位置偏移量,] 行数] # 位置偏移量：可选参数，位置偏移量，不写默认是0，代表不偏移； # 行数：指示返回的记录条数。

    1 2 3 4 5 6 7 8

    # 查询工资最高的员工信息 [排序(工资倒序) | 切割 1 ] SELECT * FROM t_employee ORDER BY salary DESC LIMIT 0 , 1; SELECT * FROM t_employee ORDER BY salary DESC LIMIT 1; # 查询工资第二高的员工信息 [排序(工资倒序) | 切割 1,1] # 2 3 4 limit 1 ,3 | 工资最少得三名员工 SELECT * FROM t_employee ORDER BY salary DESC LIMIT 1, 1; # 查询工资最高的女性员工信息 SELECT * FROM t_employee WHERE gender = '女' ORDER BY salary DESC LIMIT 1;

    分页显式公式**：（当前页数-1）*每页条数，每页条数**

    SELECT * FROM table

    LIMIT(PageNo - 1)*PageSize,PageSize

  • SELECT语句执行过程

  1 2 3 4 5 6 7 8 9

  SELECT ...,....,... FROM ...,...,.... WHERE 多表的连接条件 AND 不包含组函数的过滤条件 GROUP BY ...,... HAVING 包含组函数的过滤条件 ORDER BY ... ASC/DESC LIMIT ...,...

  SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…

  执行顺序

  FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> ORDER BY -> LIMIT

  标准 SQL 不允许在 WHERE 子句中引用列别名。这个限制是由于在评估 WHERE 子句时，列的值可能尚未确定。

  别名可以在查询的选择列表中为列指定不同的名称。您可以在 GROUP BY、ORDER BY 或 HAVING 子句中使用别名来引用列

  with rollup：sql查询的字句，用于生成数据汇总行

  1 2 3 4 5 6 7 8 9 10 11 12

  SELECT note,SUM(num) FROM books GROUP BY note WITH ROLLUP; SELECT IFNULL(note,'总量'),SUM(num) FROM books GROUP BY note WITH ROLLUP; cartoon 28 drama 22 medicine 40 novel 41 总量 131 # 找出书名中字数最多的一木书，不含空格 SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE (name,' ','')) DESC LIMIT 1;

视频P58 https://www.bilibili.com/video/BV1Cm421373b/?p=58&spm_id_from=pageDriver&vd_source=ad42090d7d6fcdfc144126ae0e2884ac

Spring DATA JPA

视频

官方文档, 参考3.1.12

• JPA：JPA是SUN公司官方提出的Java持久化规范。可以简化持久化操作，并整合ORM技术。
• Spring Data JPA：是在ORM操作和JPA规范的基础上封装出的一套应用框架，基于Hibernate之上构建可以让开发者更容易地实现对数据库的访问和操作内置了丰富的功能。
• Spring-boot-starter-data-jpa与spring-data-jpa

创建项目：spring initializr springboot3.3

查询依赖对应的版本，1

spring-boot-starter-data-jpa 3.3.2

jdbc

https://www.bilibili.com/video/BV1Tx421S7HZ/?vd_source=ad42090d7d6fcdfc144126ae0e2884ac

https://www.bilibili.com/video/BV1sK411B71e/?spm_id_from=333.999.0.0&vd_source=ad42090d7d6fcdfc144126ae0e2884ac